Problem with bcp

Hi all,
I'm using bcp from the command line to backup and
restore the data in a table.
To backup, I run a command like this:
bcp TestDatabase.dbo.tblTest out C:\test.txt -n
-S TestServer -U sa -P pwd
and this works fine. However, when I try to restore
the data that I have backed up by doing this
bcp TestDatabase.dbo.tblTest in C:\test.txt -n
-S TestServer -U sa -P pwd
I get the following error:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near 'varchar'.
Does anyone have any idea what the problem might
be?
TIA,
--
Akin
aknak at aksoto dot idps dot co dot ukuse the -n switch so you get a native dump.
Then, you'll have no trouble restoring as long as the schema is the same.
Otherwise, you've got to use format files.
(BTW, as a side not, BCP is not a backup utility).
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> Hi all,
> I'm using bcp from the command line to backup and
> restore the data in a table.
> To backup, I run a command like this:
> bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> -S TestServer -U sa -P pwd
> and this works fine. However, when I try to restore
> the data that I have backed up by doing this
> bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> -S TestServer -U sa -P pwd
> I get the following error:
> SQLState = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near 'varchar'.
> Does anyone have any idea what the problem might
> be?
> TIA,
>
> --
> Akin
> aknak at aksoto dot idps dot co dot uk
>
>|||Hello James,
Thanks for your reply.
If you see the command line I typed out, you will see that I
*am* using the -n switch, but the error still occurs.
Any other ideas?
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> use the -n switch so you get a native dump.
> Then, you'll have no trouble restoring as long as the schema is the same.
> Otherwise, you've got to use format files.
> (BTW, as a side not, BCP is not a backup utility).
> James Hokes
>
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > Hi all,
> >
> > I'm using bcp from the command line to backup and
> > restore the data in a table.
> >
> > To backup, I run a command like this:
> >
> > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > and this works fine. However, when I try to restore
> > the data that I have backed up by doing this
> >
> > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > -S TestServer -U sa -P pwd
> >
> > I get the following error:
> >
> > SQLState = 37000, NativeError = 170
> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near 'varchar'.
> >
> > Does anyone have any idea what the problem might
> > be?
> >
> > TIA,
> >
> >
> > --
> > Akin
> >
> > aknak at aksoto dot idps dot co dot uk
> >
> >
> >
>|||Doh!
Thanks for not flaming - chalk one up to 'reading too fast, eh?'
Now I'm well and fully stumped.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> Hello James,
> Thanks for your reply.
> If you see the command line I typed out, you will see that I
> *am* using the -n switch, but the error still occurs.
> Any other ideas?
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > use the -n switch so you get a native dump.
> > Then, you'll have no trouble restoring as long as the schema is the
same.
> > Otherwise, you've got to use format files.
> >
> > (BTW, as a side not, BCP is not a backup utility).
> >
> > James Hokes
> >
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > Hi all,
> > >
> > > I'm using bcp from the command line to backup and
> > > restore the data in a table.
> > >
> > > To backup, I run a command like this:
> > >
> > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > and this works fine. However, when I try to restore
> > > the data that I have backed up by doing this
> > >
> > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > -S TestServer -U sa -P pwd
> > >
> > > I get the following error:
> > >
> > > SQLState = 37000, NativeError = 170
> > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect
> > > syntax near 'varchar'.
> > >
> > > Does anyone have any idea what the problem might
> > > be?
> > >
> > > TIA,
> > >
> > >
> > > --
> > > Akin
> > >
> > > aknak at aksoto dot idps dot co dot uk
> > >
> > >
> > >
> >
> >
>|||OK James, I figured it out. The problem was that I had
some fields in the table which had spaces between their
names, like '[First Name]' and I wasn't using the quoted
identifier switch (-q). I thought I only needed to use
this when the name of the *table* or *database* whose
data I wanted to import had a space, but this seems to
apply to fields to.
Cheers,
Akin
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Doh!
> Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> Now I'm well and fully stumped.
> James Hokes
> "Sky Fly" <nobody@.blackhole.com> wrote in message
> news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > Hello James,
> >
> > Thanks for your reply.
> >
> > If you see the command line I typed out, you will see that I
> > *am* using the -n switch, but the error still occurs.
> >
> > Any other ideas?
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > use the -n switch so you get a native dump.
> > > Then, you'll have no trouble restoring as long as the schema is the
> same.
> > > Otherwise, you've got to use format files.
> > >
> > > (BTW, as a side not, BCP is not a backup utility).
> > >
> > > James Hokes
> > >
> > >
> > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > Hi all,
> > > >
> > > > I'm using bcp from the command line to backup and
> > > > restore the data in a table.
> > > >
> > > > To backup, I run a command like this:
> > > >
> > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > and this works fine. However, when I try to restore
> > > > the data that I have backed up by doing this
> > > >
> > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > -S TestServer -U sa -P pwd
> > > >
> > > > I get the following error:
> > > >
> > > > SQLState = 37000, NativeError = 170
> > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect
> > > > syntax near 'varchar'.
> > > >
> > > > Does anyone have any idea what the problem might
> > > > be?
> > > >
> > > > TIA,
> > > >
> > > >
> > > > --
> > > > Akin
> > > >
> > > > aknak at aksoto dot idps dot co dot uk
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sky Fly,
Hey, way to go! That was one that I had not thought of, but now,thanks to
you, I'll keep it in my bag of tricks.
James Hokes
"Sky Fly" <nobody@.blackhole.com> wrote in message
news:bs14nd$8dlel$1@.ID-18325.news.uni-berlin.de...
> OK James, I figured it out. The problem was that I had
> some fields in the table which had spaces between their
> names, like '[First Name]' and I wasn't using the quoted
> identifier switch (-q). I thought I only needed to use
> this when the name of the *table* or *database* whose
> data I wanted to import had a space, but this seems to
> apply to fields to.
> Cheers,
> Akin
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:eCTz#drxDHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Doh!
> >
> > Thanks for not flaming - chalk one up to 'reading too fast, eh?'
> >
> > Now I'm well and fully stumped.
> >
> > James Hokes
> >
> > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > news:bs04of$887l2$1@.ID-18325.news.uni-berlin.de...
> > > Hello James,
> > >
> > > Thanks for your reply.
> > >
> > > If you see the command line I typed out, you will see that I
> > > *am* using the -n switch, but the error still occurs.
> > >
> > > Any other ideas?
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:e1c#MhoxDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > > use the -n switch so you get a native dump.
> > > > Then, you'll have no trouble restoring as long as the schema is the
> > same.
> > > > Otherwise, you've got to use format files.
> > > >
> > > > (BTW, as a side not, BCP is not a backup utility).
> > > >
> > > > James Hokes
> > > >
> > > >
> > > > "Sky Fly" <nobody@.blackhole.com> wrote in message
> > > > news:brvo15$85hhs$1@.ID-18325.news.uni-berlin.de...
> > > > > Hi all,
> > > > >
> > > > > I'm using bcp from the command line to backup and
> > > > > restore the data in a table.
> > > > >
> > > > > To backup, I run a command like this:
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest out C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > and this works fine. However, when I try to restore
> > > > > the data that I have backed up by doing this
> > > > >
> > > > > bcp TestDatabase.dbo.tblTest in C:\test.txt -n
> > > > > -S TestServer -U sa -P pwd
> > > > >
> > > > > I get the following error:
> > > > >
> > > > > SQLState = 37000, NativeError = 170
> > > > > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect
> > > > > syntax near 'varchar'.
> > > > >
> > > > > Does anyone have any idea what the problem might
> > > > > be?
> > > > >
> > > > > TIA,
> > > > >
> > > > >
> > > > > --
> > > > > Akin
> > > > >
> > > > > aknak at aksoto dot idps dot co dot uk
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Problem with backup and restore

When I am backuping and after restoring the DB on another server, all Users
of this DB can't connect to this DB. I am getting teh error "login failed
for user 'somebody' ".
In DB Folder Users , this user exist.
Vaidas
Search on internet for two stored procedures ('sp_help_revlogin') provided
by MS to move logins with their original SID
"Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> When I am backuping and after restoring the DB on another server, all
> Users of this DB can't connect to this DB. I am getting teh error "login
> failed for user 'somebody' ".
> In DB Folder Users , this user exist.
>
|||Hi ,
use dts tranfer login task . It will tranfer all the logins to the
restored database server.
from
Doller
Uri Dimant wrote:[vbcol=seagreen]
> Vaidas
> Search on internet for two stored procedures ('sp_help_revlogin') provided
> by MS to move logins with their original SID
> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
|||yes but not with their oridinal SID
"doller" <sufianarif@.gmail.com> wrote in message
news:1130921760.840115.43300@.g14g2000cwa.googlegro ups.com...
> Hi ,
> use dts tranfer login task . It will tranfer all the logins to the
> restored database server.
> from
> Doller
> Uri Dimant wrote:
>

Problem with backup and restore

When I am backuping and after restoring the DB on another server, all Users
of this DB can't connect to this DB. I am getting teh error "login failed
for user 'somebody' ".
In DB Folder Users , this user exist.Vaidas
Search on internet for two stored procedures ('sp_help_revlogin') provided
by MS to move logins with their original SID
"Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> When I am backuping and after restoring the DB on another server, all
> Users of this DB can't connect to this DB. I am getting teh error "login
> failed for user 'somebody' ".
> In DB Folder Users , this user exist.
>|||Hi ,
use dts tranfer login task . It will tranfer all the logins to the
restored database server.
from
Doller
Uri Dimant wrote:
> Vaidas
> Search on internet for two stored procedures ('sp_help_revlogin') provided
> by MS to move logins with their original SID
> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> > When I am backuping and after restoring the DB on another server, all
> > Users of this DB can't connect to this DB. I am getting teh error "login
> > failed for user 'somebody' ".
> > In DB Folder Users , this user exist.
> >|||yes but not with their oridinal SID
"doller" <sufianarif@.gmail.com> wrote in message
news:1130921760.840115.43300@.g14g2000cwa.googlegroups.com...
> Hi ,
> use dts tranfer login task . It will tranfer all the logins to the
> restored database server.
> from
> Doller
> Uri Dimant wrote:
>> Vaidas
>> Search on internet for two stored procedures ('sp_help_revlogin')
>> provided
>> by MS to move logins with their original SID
>> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
>> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> > When I am backuping and after restoring the DB on another server, all
>> > Users of this DB can't connect to this DB. I am getting teh error
>> > "login
>> > failed for user 'somebody' ".
>> > In DB Folder Users , this user exist.
>> >
>sql

Problem with backup and restore

When I am backuping and after restoring the DB on another server, all Users
of this DB can't connect to this DB. I am getting teh error "login failed
for user 'somebody' ".
In DB Folder Users , this user exist.Vaidas
Search on internet for two stored procedures ('sp_help_revlogin') provided
by MS to move logins with their original SID
"Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...
> When I am backuping and after restoring the DB on another server, all
> Users of this DB can't connect to this DB. I am getting teh error "login
> failed for user 'somebody' ".
> In DB Folder Users , this user exist.
>|||Hi ,
use dts tranfer login task . It will tranfer all the logins to the
restored database server.
from
Doller
Uri Dimant wrote:[vbcol=seagreen]
> Vaidas
> Search on internet for two stored procedures ('sp_help_revlogin') provide
d
> by MS to move logins with their original SID
> "Vaidas Gudas" <vaidas.gudas@.rst.lt> wrote in message
> news:%23Dp$na43FHA.3880@.TK2MSFTNGP12.phx.gbl...|||yes but not with their oridinal SID
"doller" <sufianarif@.gmail.com> wrote in message
news:1130921760.840115.43300@.g14g2000cwa.googlegroups.com...
> Hi ,
> use dts tranfer login task . It will tranfer all the logins to the
> restored database server.
> from
> Doller
> Uri Dimant wrote:
>

Problem with Backup

Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Probl...ict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693
Hi
This may be the issue:
http://support.microsoft.com/default...b;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbf orumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Probl...ict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693

Problem with Backup

Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Prob...pict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/defaul...kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551
220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Prob...pict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693

Problem with Backup

Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME = ?PcyMgntProd backup?, NOSKIP , STATS = 10, DESCRIPTION = ?Daily
backup?, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME => 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693

Problem with Back Up

I have a database I would like to setup auto backup.
More exact it has been set up.
All I want is to change the Destiantion and Schedule.
I remove old destination and establish new one.
Then setup the schedule and press OK.
Next time I check backup properties it has old destiantion.
And doesn't have any schedule.
Could anybody suggest me the means of solving this kind of
problem.
Thank you before.
Alexey,
How is this set up? In a maintenance plan or a SQL Agent job? If a job,
what commands do you have in your job?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Alexey Zhilenko wrote:
> I have a database I would like to setup auto backup.
> More exact it has been set up.
> All I want is to change the Destiantion and Schedule.
> I remove old destination and establish new one.
> Then setup the schedule and press OK.
> Next time I check backup properties it has old destiantion.
> And doesn't have any schedule.
> Could anybody suggest me the means of solving this kind of
> problem.
> Thank you before.
sql

Problem with Back Up

I have a database I would like to setup auto backup.
More exact it has been set up.
All I want is to change the Destiantion and Schedule.
I remove old destination and establish new one.
Then setup the schedule and press OK.
Next time I check backup properties it has old destiantion.
And doesn't have any schedule.
Could anybody suggest me the means of solving this kind of
problem.
Thank you before.Alexey,
How is this set up? In a maintenance plan or a SQL Agent job? If a job,
what commands do you have in your job?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Alexey Zhilenko wrote:
> I have a database I would like to setup auto backup.
> More exact it has been set up.
> All I want is to change the Destiantion and Schedule.
> I remove old destination and establish new one.
> Then setup the schedule and press OK.
> Next time I check backup properties it has old destiantion.
> And doesn't have any schedule.
> Could anybody suggest me the means of solving this kind of
> problem.
> Thank you before.

Problem with Back Up

I have a database I would like to setup auto backup.
More exact it has been set up.
All I want is to change the Destiantion and Schedule.
I remove old destination and establish new one.
Then setup the schedule and press OK.
Next time I check backup properties it has old destiantion.
And doesn't have any schedule.
Could anybody suggest me the means of solving this kind of
problem.
Thank you before.Alexey,
How is this set up? In a maintenance plan or a SQL Agent job? If a job,
what commands do you have in your job?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Alexey Zhilenko wrote:
> I have a database I would like to setup auto backup.
> More exact it has been set up.
> All I want is to change the Destiantion and Schedule.
> I remove old destination and establish new one.
> Then setup the schedule and press OK.
> Next time I check backup properties it has old destiantion.
> And doesn't have any schedule.
> Could anybody suggest me the means of solving this kind of
> problem.
> Thank you before.

Problem with ASP - ODBC - SQL Server after moving to Windows 2003 Server

Hello,
We are setting up a backup server for one of our Web sites, and the current
machine is running Win 2K Server, the new one Win 2003 Server. Otherwise
everything else is the same.
Most everthing works fine, except for one ASP template, which is opening a
recordset with this line:
set RS = Server.CreateObject("ADODB.Recordset") rs.Open "DealerInfo",
Conn,adOpenKeyset,adLockOptimistic,adcmdtable
On the 2003 server it gives an error:
Microsoft OLE DB Provider for ODBC Drivers error '800401f3'
[Microsoft][ODBC SQL Server Driver]Cursor type changed
I have searched for information about thids error, but not found any so far.
Any help would be appreciated.
Ragnar
I have determined that this is not really an error message, but an
informational message. It seems pretty clear that the real error lies
somewhere else, so I will have to come up with a way to add some debug
statements without stopping the code.
Ragnar
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:%23THcMdmpEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We are setting up a backup server for one of our Web sites, and the
> current machine is running Win 2K Server, the new one Win 2003 Server.
> Otherwise everything else is the same.
> Most everthing works fine, except for one ASP template, which is opening a
> recordset with this line:
> set RS = Server.CreateObject("ADODB.Recordset") rs.Open "DealerInfo",
> Conn,adOpenKeyset,adLockOptimistic,adcmdtable
> On the 2003 server it gives an error:
> Microsoft OLE DB Provider for ODBC Drivers error '800401f3'
> [Microsoft][ODBC SQL Server Driver]Cursor type changed
> I have searched for information about thids error, but not found any so
> far.
> Any help would be appreciated.
> Ragnar
>

Problem with "Point in time"- restore of database on new server

In short we are trying to restore a production database backup on a
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
Jan
Your RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>

Problem with "Point in time"- restore of database on new server

In short we are trying to restore a production database backup on a
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
JanYour RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>

Problem while restoring my Backup

This is my problem
I do a full Database BackUp with INIT at 01h30
Then I do a Full Transation Log with INIT at 7h00
Then each hour between 8h00 to 22h00 I do a Full Transaction Log with NO_INIT
When i ask to recover one of my Bakcup between 7h00 to 22h00
I got this message:
The log in this backup set begins at LSN 127000000005700001, which is ...
What's the problem (I use a SQLServer 2000)You need to apply all the log backups, in sequence, sine the latest database
backup:
RESTORE DATABASE... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
...
RESTORE LOG ... WITH RECOVERY
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Guy Tanguay" <guy_tanguay@.hotmail.com> wrote in message
news:3b53412a.0402061042.cdfe4b1@.posting.google.com...
> This is my problem
> I do a full Database BackUp with INIT at 01h30
> Then I do a Full Transation Log with INIT at 7h00
> Then each hour between 8h00 to 22h00 I do a Full Transaction Log with
NO_INIT
> When i ask to recover one of my Bakcup between 7h00 to 22h00
> I got this message:
> The log in this backup set begins at LSN 127000000005700001, which is ...
> What's the problem (I use a SQLServer 2000)

Problem while restoring my Backup

This is my problem
I do a full Database BackUp with INIT at 01h30
Then I do a Full Transation Log with INIT at 7h00
Then each hour between 8h00 to 22h00 I do a Full Transaction Log with NO_INI
T
When i ask to recover one of my Bakcup between 7h00 to 22h00
I got this message:
The log in this backup set begins at LSN 127000000005700001, which is ...
What's the problem (I use a SQLServer 2000)You need to apply all the log backups, in sequence, sine the latest database
backup:
RESTORE DATABASE... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
...
RESTORE LOG ... WITH RECOVERY
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Guy Tanguay" <guy_tanguay@.hotmail.com> wrote in message
news:3b53412a.0402061042.cdfe4b1@.posting.google.com...
> This is my problem
> I do a full Database BackUp with INIT at 01h30
> Then I do a Full Transation Log with INIT at 7h00
> Then each hour between 8h00 to 22h00 I do a Full Transaction Log with
NO_INIT
> When i ask to recover one of my Bakcup between 7h00 to 22h00
> I got this message:
> The log in this backup set begins at LSN 127000000005700001, which is ...
> What's the problem (I use a SQLServer 2000)

problem w/ sp_change_users_login on 64bit SQL Server 2005?

Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen
> How can it work on one and not the other?
Because these are different instances and might not have the same logins.

> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>
|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

problem w/ sp_change_users_login on 64bit SQL Server 2005?

Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.

> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo

problem w/ sp_change_users_login on 64bit SQL Server 2005?

Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo