Copy one db with jobs to another hard drive

Hi all,
I'm replaced our current hard drive with another and I need to know the
best way to transfer all the sql data with jobs.
Going from:
NT4 Server running SQL Server 7
to:
W2K Server running SQL Server 7
I need to transfer all sql data including jobs to the new hard drive.
Can I just copy all the dbs (Master, User defined, etc) to the new hard
drive and be set?
What's the best way to handle this.
Thank you,
BillWilliam,
Go with Backup and Restore procedure.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"William Oliveri" <wuji@.bigvalley.net> wrote in message
news:eV2PpPFkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm replaced our current hard drive with another and I need to know the
> best way to transfer all the sql data with jobs.
> Going from:
> NT4 Server running SQL Server 7
> to:
> W2K Server running SQL Server 7
> I need to transfer all sql data including jobs to the new hard drive.
> Can I just copy all the dbs (Master, User defined, etc) to the new hard
> drive and be set?
> What's the best way to handle this.
> Thank you,
> Bill
>|||> Can I just copy all the dbs (Master, User defined, etc) to the new hard
> drive and be set?
>
No there are two ways:
1)
detach/attach
take care that no user is connected (kill SPID´s or : alter database
set restricted_user with rollback immediate)
all files you have to move you find at sysfiles (master)
exec sp_detach_db DBName
copy or move the files
at the new Server:
exec sp_attach_db DBName, FileName1, FileName2...FileName16
e.g exec sp_attach_db 'MyDB',
'e:\SQLData\MyDB.mdf','e:\SQLLog\MyDB.ldf'
2)
Backup/Restore
Backup Database DBName To Disk='PathName'
then on the new machine:
Restore Database DBName
from Disk = 'PathName'
with move 'MyDB' TO 'g:\SQLData\MyDB.mdf',
move 'MyDb_log' TO 'g:\SQLLog\MyDB.ldf'
hope this was helpful
Peter

Copy DB to a different Machine

Hi,

I have developed an application using SQl Server 2000.

I want to transfer the database to my new server. I want all the tables and datas to be transferred.

I am new to this application. So i am not able to get mysel started. Can someone help me in this problem.

My database is large. It has stored procedures, database diagrams.

Any help in this regard would be highly appreciated.

Thanks,
-sriramFin MS Sql Server on your Programs menu. Choose 'Import/Export' data > next. The following Dialogue is the From dialogue. Choose the Databse you want to copy and press next. In the 'To' dialogue, enter the IP/URL to the remote SQL Server. In the Dropdown list, choose '<New>' and name your Database - press OK. I nteh next dialogue, choose 'Copy objects and data between SQL Server databases'. Click Next 3 times and then End. Now your Database and data will be copied to your new Server.|||I prefer doing a full backup and then restoring the backup on the new machine. I find it quicker that way. This way your DB Diagrams will survive too.|||i agree with the full backup technique.

the only time i "object copy" between db's is to keep my local DB in sync with a shared Development DB and 2 other developers.|||Hi,

Thanks for replying.

How can i do a full backup. I am not able to backup and transfer to the new machine.

So can you help me??
thanks,
-sriram|||if you go to C:\Program Files\Microsoft SQL Server\MSSQL\Data
find your db files ...( 2- the db file and the log file)

copy them over to the new machine in the same data folder...and "attach" the db...

** remember to "Stop" the sql server before you do this...

hth|||I'm working on a project with 3 other people whom are using MSDE for the DB. Is there a function similar to 'attach db' that can be used with MSDE. Our computers are not networked, so we will only be able to pass the database file around.

Thanks in advance!

Copy Database Wizard and DTS Transfer Database Task don't work

Hello,
I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
2000. Both belong to the same domain and can see each other without
problems. A mutual registration of the other sql server in Enterprise
Manager is no problem.
One Server's operating system is win2003Server, the other one's is
Win2000Server.
In both servers the domain account I use for logging in and for
authentification purposes is a member of the server's local
administrators group. And the local administrators have sysadmin rights
in sql server 2000 via builtin\administrators.
After some tests I added my windows domain account explicitly as
sysadmin in both SQL-Servers.
The security mode is "mixed" (SQL-Login and Windows).
Trying to transfer a Test database with 1MB space fails. No matter
whether I use a Transfer Database Task or whether I use the Copy
Database Wizard (which also uses DTS in the background).
The Transfer Database Task needs FIVE minutes for the dialog window
"Transfer Database Properties" to reappear after clicking on the
tab-card [file locations].
Below the grid is a green check mark: Files are ready to be moved or
copied.".
Below that is a red X: Files on the source have the same name as on the
destination or there is not enough free disk space on the destination.
On both servers there is plenty of space (dozens of GB).
In the end I only get the error description "Unspecified error".
Doing the same with the Copy Database wizard" in the Grid beside the
..mdf file and the .ldf file shows the STATUS: OK
Below, again two error messages:
Name conflict: Type a new name into the Destination Files fields
Disk space: Change the destination or free disk space
At the same time is shown the space needed and available:
E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
So I run the job in the wizard immediately and get the following
errors:
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
Apparently DTS uses odbc and can not establish the connection despite
Sysadmin rights on both servers. I tried to specify the account sa of
sql server for authentication instead of using windows authentication
but that yields the same result.
Would someone know the solution?
Thank you.
Andreas.meyer@.dmc-group.de wrote:
> Hello,
> I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> 2000. Both belong to the same domain and can see each other without
> problems. A mutual registration of the other sql server in Enterprise
> Manager is no problem.
> One Server's operating system is win2003Server, the other one's is
> Win2000Server.
> In both servers the domain account I use for logging in and for
> authentification purposes is a member of the server's local
> administrators group. And the local administrators have sysadmin rights
> in sql server 2000 via builtin\administrators.
> After some tests I added my windows domain account explicitly as
> sysadmin in both SQL-Servers.
> The security mode is "mixed" (SQL-Login and Windows).
> Trying to transfer a Test database with 1MB space fails. No matter
> whether I use a Transfer Database Task or whether I use the Copy
> Database Wizard (which also uses DTS in the background).
> The Transfer Database Task needs FIVE minutes for the dialog window
> "Transfer Database Properties" to reappear after clicking on the
> tab-card [file locations].
> Below the grid is a green check mark: Files are ready to be moved or
> copied.".
> Below that is a red X: Files on the source have the same name as on the
> destination or there is not enough free disk space on the destination.
> On both servers there is plenty of space (dozens of GB).
> In the end I only get the error description "Unspecified error".
> Doing the same with the Copy Database wizard" in the Grid beside the
> .mdf file and the .ldf file shows the STATUS: OK
> Below, again two error messages:
> Name conflict: Type a new name into the Destination Files fields
> Disk space: Change the destination or free disk space
> At the same time is shown the space needed and available:
> E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> So I run the job in the wizard immediately and get the following
> errors:
> Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection.
> Step Error code: 80004005
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:700
> Apparently DTS uses odbc and can not establish the connection despite
> Sysadmin rights on both servers. I tried to specify the account sa of
> sql server for authentication instead of using windows authentication
> but that yields the same result.
> Would someone know the solution?
> Thank you.
>
Save yourself the headache and stop using these wizards for such a
simple task. Simply take a backup of the source database, then restore
that onto the destination server. The syntax for the BACKUP and RESTORE
commands is pretty simple, see Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
of course, I did already.
However, there is so much fuss being made of what sql server 2000 can
do.
Also it is so much stressed in the exams: and dare you you wouldn't
know what the copy database wizard or DTS is able to to and that to use
it is so much easier and surely the best solution when asked for "the
least administrative effort", etc.
Then you try with one of the simplest tasks and in practice it just
fails in an environment where it is truly hard that it wouldn't work.
Yet it doesn't.
Nothing left any more of all the glory...
This is annoying.
Then again, I would like to know the reason for the error message in
odbc.
My impression is: a lot is just marketing but not working, really.
Sincerely
Andreas
Tracy McKibben schrieb:

> Andreas.meyer@.dmc-group.de wrote:
> Save yourself the headache and stop using these wizards for such a
> simple task. Simply take a backup of the source database, then restore
> that onto the destination server. The syntax for the BACKUP and RESTORE
> commands is pretty simple, see Books Online for details.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy Database Wizard and DTS Transfer Database Task don't work

Hello,
I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
2000. Both belong to the same domain and can see each other without
problems. A mutual registration of the other sql server in Enterprise
Manager is no problem.
One Server's operating system is win2003Server, the other one's is
Win2000Server.
In both servers the domain account I use for logging in and for
authentification purposes is a member of the server's local
administrators group. And the local administrators have sysadmin rights
in sql server 2000 via builtin\administrators.
After some tests I added my windows domain account explicitly as
sysadmin in both SQL-Servers.
The security mode is "mixed" (SQL-Login and Windows).
Trying to transfer a Test database with 1MB space fails. No matter
whether I use a Transfer Database Task or whether I use the Copy
Database Wizard (which also uses DTS in the background).
The Transfer Database Task needs FIVE minutes for the dialog window
"Transfer Database Properties" to reappear after clicking on the
tab-card [file locations].
Below the grid is a green check mark: Files are ready to be moved or
copied.".
Below that is a red X: Files on the source have the same name as on the
destination or there is not enough free disk space on the destination.
On both servers there is plenty of space (dozens of GB).
In the end I only get the error description "Unspecified error".
Doing the same with the Copy Database wizard" in the Grid beside the
.mdf file and the .ldf file shows the STATUS: OK
Below, again two error messages:
Name conflict: Type a new name into the Destination Files fields
Disk space: Change the destination or free disk space
At the same time is shown the space needed and available:
E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
So I run the job in the wizard immediately and get the following
errors:
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
Apparently DTS uses odbc and can not establish the connection despite
Sysadmin rights on both servers. I tried to specify the account sa of
sql server for authentication instead of using windows authentication
but that yields the same result.
Would someone know the solution?
Thank you.Andreas.meyer@.dmc-group.de wrote:
> Hello,
> I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> 2000. Both belong to the same domain and can see each other without
> problems. A mutual registration of the other sql server in Enterprise
> Manager is no problem.
> One Server's operating system is win2003Server, the other one's is
> Win2000Server.
> In both servers the domain account I use for logging in and for
> authentification purposes is a member of the server's local
> administrators group. And the local administrators have sysadmin rights
> in sql server 2000 via builtin\administrators.
> After some tests I added my windows domain account explicitly as
> sysadmin in both SQL-Servers.
> The security mode is "mixed" (SQL-Login and Windows).
> Trying to transfer a Test database with 1MB space fails. No matter
> whether I use a Transfer Database Task or whether I use the Copy
> Database Wizard (which also uses DTS in the background).
> The Transfer Database Task needs FIVE minutes for the dialog window
> "Transfer Database Properties" to reappear after clicking on the
> tab-card [file locations].
> Below the grid is a green check mark: Files are ready to be moved or
> copied.".
> Below that is a red X: Files on the source have the same name as on the
> destination or there is not enough free disk space on the destination.
> On both servers there is plenty of space (dozens of GB).
> In the end I only get the error description "Unspecified error".
> Doing the same with the Copy Database wizard" in the Grid beside the
> .mdf file and the .ldf file shows the STATUS: OK
> Below, again two error messages:
> Name conflict: Type a new name into the Destination Files fields
> Disk space: Change the destination or free disk space
> At the same time is shown the space needed and available:
> E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> So I run the job in the wizard immediately and get the following
> errors:
> Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection.
> Step Error code: 80004005
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:700
> Apparently DTS uses odbc and can not establish the connection despite
> Sysadmin rights on both servers. I tried to specify the account sa of
> sql server for authentication instead of using windows authentication
> but that yields the same result.
> Would someone know the solution?
> Thank you.
>
Save yourself the headache and stop using these wizards for such a
simple task. Simply take a backup of the source database, then restore
that onto the destination server. The syntax for the BACKUP and RESTORE
commands is pretty simple, see Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
of course, I did already.
However, there is so much fuss being made of what sql server 2000 can
do.
Also it is so much stressed in the exams: and dare you you wouldn't
know what the copy database wizard or DTS is able to to and that to use
it is so much easier and surely the best solution when asked for "the
least administrative effort", etc.
Then you try with one of the simplest tasks and in practice it just
fails in an environment where it is truly hard that it wouldn't work.
Yet it doesn't.
Nothing left any more of all the glory...
This is annoying.
Then again, I would like to know the reason for the error message in
odbc.
My impression is: a lot is just marketing but not working, really.
Sincerely
Andreas
Tracy McKibben schrieb:

> Andreas.meyer@.dmc-group.de wrote:
> Save yourself the headache and stop using these wizards for such a
> simple task. Simply take a backup of the source database, then restore
> that onto the destination server. The syntax for the BACKUP and RESTORE
> commands is pretty simple, see Books Online for details.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy Database Wizard and DTS Transfer Database Task don't work

Hello,
I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
2000. Both belong to the same domain and can see each other without
problems. A mutual registration of the other sql server in Enterprise
Manager is no problem.
One Server's operating system is win2003Server, the other one's is
Win2000Server.
In both servers the domain account I use for logging in and for
authentification purposes is a member of the server's local
administrators group. And the local administrators have sysadmin rights
in sql server 2000 via builtin\administrators.
After some tests I added my windows domain account explicitly as
sysadmin in both SQL-Servers.
The security mode is "mixed" (SQL-Login and Windows).
Trying to transfer a Test database with 1MB space fails. No matter
whether I use a Transfer Database Task or whether I use the Copy
Database Wizard (which also uses DTS in the background).
The Transfer Database Task needs FIVE minutes for the dialog window
"Transfer Database Properties" to reappear after clicking on the
tab-card [file locations].
Below the grid is a green check mark: Files are ready to be moved or
copied.".
Below that is a red X: Files on the source have the same name as on the
destination or there is not enough free disk space on the destination.
On both servers there is plenty of space (dozens of GB).
In the end I only get the error description "Unspecified error".
Doing the same with the Copy Database wizard" in the Grid beside the
.mdf file and the .ldf file shows the STATUS: OK
Below, again two error messages:
Name conflict: Type a new name into the Destination Files fields
Disk space: Change the destination or free disk space
At the same time is shown the space needed and available:
E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
So I run the job in the wizard immediately and get the following
errors:
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
Apparently DTS uses odbc and can not establish the connection despite
Sysadmin rights on both servers. I tried to specify the account sa of
sql server for authentication instead of using windows authentication
but that yields the same result.
Would someone know the solution?
Thank you.Andreas.meyer@.dmc-group.de wrote:
> Hello,
> I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> 2000. Both belong to the same domain and can see each other without
> problems. A mutual registration of the other sql server in Enterprise
> Manager is no problem.
> One Server's operating system is win2003Server, the other one's is
> Win2000Server.
> In both servers the domain account I use for logging in and for
> authentification purposes is a member of the server's local
> administrators group. And the local administrators have sysadmin rights
> in sql server 2000 via builtin\administrators.
> After some tests I added my windows domain account explicitly as
> sysadmin in both SQL-Servers.
> The security mode is "mixed" (SQL-Login and Windows).
> Trying to transfer a Test database with 1MB space fails. No matter
> whether I use a Transfer Database Task or whether I use the Copy
> Database Wizard (which also uses DTS in the background).
> The Transfer Database Task needs FIVE minutes for the dialog window
> "Transfer Database Properties" to reappear after clicking on the
> tab-card [file locations].
> Below the grid is a green check mark: Files are ready to be moved or
> copied.".
> Below that is a red X: Files on the source have the same name as on the
> destination or there is not enough free disk space on the destination.
> On both servers there is plenty of space (dozens of GB).
> In the end I only get the error description "Unspecified error".
> Doing the same with the Copy Database wizard" in the Grid beside the
> .mdf file and the .ldf file shows the STATUS: OK
> Below, again two error messages:
> Name conflict: Type a new name into the Destination Files fields
> Disk space: Change the destination or free disk space
> At the same time is shown the space needed and available:
> E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> So I run the job in the wizard immediately and get the following
> errors:
> Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection.
> Step Error code: 80004005
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:700
> Apparently DTS uses odbc and can not establish the connection despite
> Sysadmin rights on both servers. I tried to specify the account sa of
> sql server for authentication instead of using windows authentication
> but that yields the same result.
> Would someone know the solution?
> Thank you.
>
Save yourself the headache and stop using these wizards for such a
simple task. Simply take a backup of the source database, then restore
that onto the destination server. The syntax for the BACKUP and RESTORE
commands is pretty simple, see Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
of course, I did already.
However, there is so much fuss being made of what sql server 2000 can
do.
Also it is so much stressed in the exams: and dare you you wouldn't
know what the copy database wizard or DTS is able to to and that to use
it is so much easier and surely the best solution when asked for "the
least administrative effort", etc.
Then you try with one of the simplest tasks and in practice it just
fails in an environment where it is truly hard that it wouldn't work.
Yet it doesn't.
Nothing left any more of all the glory...
This is annoying.
Then again, I would like to know the reason for the error message in
odbc.
My impression is: a lot is just marketing but not working, really.
Sincerely
Andreas
Tracy McKibben schrieb:
> Andreas.meyer@.dmc-group.de wrote:
> > Hello,
> > I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> > 2000. Both belong to the same domain and can see each other without
> > problems. A mutual registration of the other sql server in Enterprise
> > Manager is no problem.
> > One Server's operating system is win2003Server, the other one's is
> > Win2000Server.
> > In both servers the domain account I use for logging in and for
> > authentification purposes is a member of the server's local
> > administrators group. And the local administrators have sysadmin rights
> > in sql server 2000 via builtin\administrators.
> > After some tests I added my windows domain account explicitly as
> > sysadmin in both SQL-Servers.
> > The security mode is "mixed" (SQL-Login and Windows).
> >
> > Trying to transfer a Test database with 1MB space fails. No matter
> > whether I use a Transfer Database Task or whether I use the Copy
> > Database Wizard (which also uses DTS in the background).
> > The Transfer Database Task needs FIVE minutes for the dialog window
> > "Transfer Database Properties" to reappear after clicking on the
> > tab-card [file locations].
> > Below the grid is a green check mark: Files are ready to be moved or
> > copied.".
> > Below that is a red X: Files on the source have the same name as on the
> > destination or there is not enough free disk space on the destination.
> >
> > On both servers there is plenty of space (dozens of GB).
> > In the end I only get the error description "Unspecified error".
> >
> > Doing the same with the Copy Database wizard" in the Grid beside the
> > .mdf file and the .ldf file shows the STATUS: OK
> > Below, again two error messages:
> >
> > Name conflict: Type a new name into the Destination Files fields
> > Disk space: Change the destination or free disk space
> >
> > At the same time is shown the space needed and available:
> > E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> >
> > So I run the job in the wizard immediately and get the following
> > errors:
> >
> > Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> > Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> > Server]Login failed for user '(null)'. Reason: Not associated with a
> > trusted SQL Server connection.
> > Step Error code: 80004005
> > Step Error Help File:SQLDMO80.hlp
> > Step Error Help Context ID:700
> >
> > Apparently DTS uses odbc and can not establish the connection despite
> > Sysadmin rights on both servers. I tried to specify the account sa of
> > sql server for authentication instead of using windows authentication
> > but that yields the same result.
> > Would someone know the solution?
> >
> > Thank you.
> >
> Save yourself the headache and stop using these wizards for such a
> simple task. Simply take a backup of the source database, then restore
> that onto the destination server. The syntax for the BACKUP and RESTORE
> commands is pretty simple, see Books Online for details.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy Database Wizard

Hello at all,
I want to transfer sql2000 databases and DTS from machine to another; I have
tried to use "Copy Datebase Wizard", but I'm not sure this is the best
choice. Does anyone want to help me?
thank you
Thanks a lot.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
> hi
> see my blog
> http://oneplace4sql.blogspot.com/
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "acolmano" <acolmano@.tin.it> wrote in message
> news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
>
|||Another question. what does occur if don't copy master database to target
machine?
Can I restore all DB with sql backup/restore utility? It's the same?
Thanks
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
> hi
> see my blog
> http://oneplace4sql.blogspot.com/
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "acolmano" <acolmano@.tin.it> wrote in message
> news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
>
sqlsql

Copy Database Wizard

Hello at all,
I want to transfer sql2000 databases and DTS from machine to another; I have
tried to use "Copy Datebase Wizard", but I'm not sure this is the best
choice. Does anyone want to help me?
thank youhi
see my blog
http://oneplace4sql.blogspot.com/
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"acolmano" <acolmano@.tin.it> wrote in message
news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
> Hello at all,
> I want to transfer sql2000 databases and DTS from machine to another; I
> have tried to use "Copy Datebase Wizard", but I'm not sure this is the
> best choice. Does anyone want to help me?
> thank you|||Thanks a lot.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
> hi
> see my blog
> http://oneplace4sql.blogspot.com/
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "acolmano" <acolmano@.tin.it> wrote in message
> news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
>> Hello at all,
>> I want to transfer sql2000 databases and DTS from machine to another; I
>> have tried to use "Copy Datebase Wizard", but I'm not sure this is the
>> best choice. Does anyone want to help me?
>> thank you
>|||Another question. what does occur if don't copy master database to target
machine?
Can I restore all DB with sql backup/restore utility? It's the same?
Thanks
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
> hi
> see my blog
> http://oneplace4sql.blogspot.com/
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "acolmano" <acolmano@.tin.it> wrote in message
> news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
>> Hello at all,
>> I want to transfer sql2000 databases and DTS from machine to another; I
>> have tried to use "Copy Datebase Wizard", but I'm not sure this is the
>> best choice. Does anyone want to help me?
>> thank you
>|||Hi
You can restore master database restore backup cmd. Microsoft recomended way
is using sqlcmd tool
http://msdn2.microsoft.com/en-us/library/ms190679.aspx
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"acolmano" <acolmano@.tin.it> wrote in message
news:eHwb62FnHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Another question. what does occur if don't copy master database to target
> machine?
> Can I restore all DB with sql backup/restore utility? It's the same?
> Thanks
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
>> hi
>> see my blog
>> http://oneplace4sql.blogspot.com/
>> --
>> VT
>> Knowledge is power, share it...
>> http://oneplace4sql.blogspot.com/
>> "acolmano" <acolmano@.tin.it> wrote in message
>> news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
>> Hello at all,
>> I want to transfer sql2000 databases and DTS from machine to another; I
>> have tried to use "Copy Datebase Wizard", but I'm not sure this is the
>> best choice. Does anyone want to help me?
>> thank you
>>
>

Copy Database Wizard

Hello at all,
I want to transfer sql2000 databases and DTS from machine to another; I have
tried to use "Copy Datebase Wizard", but I'm not sure this is the best
choice. Does anyone want to help me?
thank youhi
see my blog
http://oneplace4sql.blogspot.com/
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"acolmano" <acolmano@.tin.it> wrote in message
news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
> Hello at all,
> I want to transfer sql2000 databases and DTS from machine to another; I
> have tried to use "Copy Datebase Wizard", but I'm not sure this is the
> best choice. Does anyone want to help me?
> thank you|||Thanks a lot.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
> hi
> see my blog
> http://oneplace4sql.blogspot.com/
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "acolmano" <acolmano@.tin.it> wrote in message
> news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
>|||Another question. what does occur if don't copy master database to target
machine?
Can I restore all DB with sql backup/restore utility? It's the same?
Thanks
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
> hi
> see my blog
> http://oneplace4sql.blogspot.com/
> --
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "acolmano" <acolmano@.tin.it> wrote in message
> news:OL37WlEnHHA.3952@.TK2MSFTNGP03.phx.gbl...
>|||Hi
You can restore master database restore backup cmd. Microsoft recomended way
is using sqlcmd tool
http://msdn2.microsoft.com/en-us/library/ms190679.aspx
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"acolmano" <acolmano@.tin.it> wrote in message
news:eHwb62FnHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Another question. what does occur if don't copy master database to target
> machine?
> Can I restore all DB with sql backup/restore utility? It's the same?
> Thanks
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:eVmy20EnHHA.4424@.TK2MSFTNGP03.phx.gbl...
>

Copy Database (SQL Management Object method)

I tried to copy a db instance from one SQL 2005 installation to another. The transfer/copy wizard in management studio showed nothing - a blank - in the "Destination database files" section. That struck me as bad, but the wizard would not let me add any files. Then when I tried to execute the transfer/copy, I got this error message:

Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->The PRIMARY filegroup must have at least one file.

I suppose I would not have had that error message, if the wizard allowed me to add a filegroup. What is causing this?

Are you copying or moving? Do you have to use SMO? If not, just make a backup of the database and restore it to the other server. If you are moving the database, detach the files and reattach them on the other server. Hope this helps.
Tim|||Run SP_HELPFILEGROUP on source server to see what are the contents displayed, as explained you can either take help of backup/restore of detach/attach method.

Copy Data to excel file using dts package in sql server 2000

Friends

Any one of you share your knowledge how to transfer data from a database to a excel using dts packages in sqlserver 2000.

I want clear steps how to create a dts package

Appreciate your help

Thanks

satish

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

You might try this group instead, as this forum is for SQL Server Integration Services

Copy data to another server via XML?

Hello...
I have to transfer/copy data from our SQL 2005 server to another server that
is NOT part of our domain. I was figuing to use XML to do this...but we hav
e
some image data types in some of our tables and I cannot figure out how to
include that data in the XML.
Does anyone know of a way to do this..or an article explaining it?
thanks for any help
- willHello dw,
If you are using SQL Server 2005, take a look at Integration Services.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for the advice. I just found out that the customer requirements do
not include a need for image/binary data...so I am now thinking of using the
HTTP Endpoints stuff...not really sure as I don't know too much about it
yet. But it looks promising.
"Kent Tegels" wrote:

> Hello dw,
> If you are using SQL Server 2005, take a look at Integration Services.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||dw wrote:
> Thanks for the advice. I just found out that the customer requirements do
> not include a need for image/binary data...so I am now thinking of using t
he
> HTTP Endpoints stuff...not really sure as I don't know too much about it
> yet. But it looks promising.
For the record, XML itself is designed for text information, so it
cannot hold binary (image) data direct: it has to be encoded into
text characters first or referenced externally (like HTML).
But as Kent has pointed out, suppliers provide built-in ways to do
this for you.
///Peter
--
XML FAQ: http://xml.silmaril.ie/
> "Kent Tegels" wrote:
>

Copy Data from Access to SQL Express in Code?

I have tried the upsize wizard from access 2000 and access 2003 and get records transfer in 90% of the tables I have data stored an 89 meg MS access database (2003) on an XP machine. I can use, compact and copy etc in MS Access without problem.

With the upsize, I get No errors, just does not reliably move ALL data. I have run it multiple times and occasionally get some data in these tables, but never all data in a few tables and inconsistent.

========================================

So I have written code to copy the data from the access database and move it field by field into an Upsized database that was structure only, no data.

I have ONLY one Identity field (autonumber in MS access) that is the Key Relationship number to all other tables.

1 questions and one "bug" report need solutions to both.

Question: How can I copy the value from the MS access autonumber field to the identity field. (Some of the autonumber rows have been deleted so not always sequentiall) (I can do this moving data in code between to MS access databases that have autonumber fields)

=========================

I tied an elaborate write around to addNEW and then read the data in the newly created row. Reading the Identity field, I look up that number in the MS access database. Then I copy the ACCESS data to the Newly created table row in SQL Exress and update.

It works fine for about 30 record updates, then the Identify field stops giving me the correct sequence for the Identity field value. Makes it impossible to make sure all of my Access rows get copied. Tried transactions but not supported with the ADODB configuration, but doubt that would help.

Example data returned in the SQL Express recordset, when I read the Identify field back I get

1,2,3 etc 31, 33, 32 (out of sequence) then it (my code) can't figure out the out of sequence and end up with the next identity value at 41.

The only info I found was that it could be a cursorlocation problem, what do you recommend?

?Is there a way in code to change the identity field to string, then copy the data and change back to identity field again?

As you are just loading the data I would set the field in the SQL Express system to just an int then load the data back in. Once complete change the filed back to an identity field and start the numbers off on the next availible number.

|||

I tried that, but it will NOT let me set the identity field. It generates it.

It works with Access but NOT SQL EXPRESS, if I hit the ADDNEW and then set the identify field and update it errors.

|||

Hey TurboDoc,

You should be able to insert data into your Identity field by setting the IDENTITY_INSERT option. Check out SQL BOL for more information on that. I think that will be more straight forward than trying to create blank records in SQL and then matching up the Identity values.

Hope this helps,

Mike

Copy All Tables from Server A to Server B

I haven't had any luck using the Transfer SQL Server Objects task and from what I have found in researching the issues I ran into, no else has. I have found the Roll Your Own article but being non-educated in SMO and VB.Net (I know some VB but nothing about C#), I really need an example of a server to server copy/transfer.

Any help would be greatly appreciated.

Did you take a look at this link? It has VB code for copying a database: http://blogs.msdn.com/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx. To copy server to server, you'd just need to modify it to include a source and destination server.|||I know this will sound lame, but that's my problem. I haven't figured out how to add a source and destination server. I've never worked with this before and I haven't found any examples.

Converting User SIDS from SQL 2000 to SQL 2005

WE have a query that we use whenever we transfer a database between servers
that resyns the SIDS in the database to match those in the new installation.
I am trying to convert a databse from 2000 to run on 2005 and the query no
longer works because 2005 does not store the user information in the same wa
y
as 2000. Can someone point me in the right direction to update this query
for 2005. Or, perhaps there is another approach I should be taking?
Here is the query:
/ ****************************************
*****************
* This script is used to synch the SIDs between
* the login and the database user. This should
* be executed after a database is copied to a new
* server and attached to keep the permissions as
* they were on the server where the database originated.
****************************************
******************/
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @.LoginSID VARBINARY(85)
-- Update the SID for the SEMS user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'SEMS'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'SEMS'
-- Update the SID for the RptWriters user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'RptWriters'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'RptWriters'
-- Update the SID for the TSEQALS user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'TSEQALS'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'TSEQALS'
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE
GO
Thanks,
JohnJohn
I did it by using two stored procedures that MS have provided. I have not
played with it on SQL Server 2005 since I re-created logins on the new
server , however it worth to try.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"John Beschler" <JohnBeschler@.discussions.microsoft.com> wrote in message
news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
> WE have a query that we use whenever we transfer a database between
> servers
> that resyns the SIDS in the database to match those in the new
> installation.
> I am trying to convert a databse from 2000 to run on 2005 and the query no
> longer works because 2005 does not store the user information in the same
> way
> as 2000. Can someone point me in the right direction to update this query
> for 2005. Or, perhaps there is another approach I should be taking?
> Here is the query:
> / ****************************************
*****************
> * This script is used to synch the SIDs between
> * the login and the database user. This should
> * be executed after a database is copied to a new
> * server and attached to keep the permissions as
> * they were on the server where the database originated.
> ****************************************
******************/
> sp_configure 'allow updates', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> DECLARE @.LoginSID VARBINARY(85)
> -- Update the SID for the SEMS user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'SEMS'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'SEMS'
> -- Update the SID for the RptWriters user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'RptWriters'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'RptWriters'
> -- Update the SID for the TSEQALS user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'TSEQALS'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'TSEQALS'
> GO
> sp_configure 'allow updates', 0
> GO
> RECONFIGURE
> GO
>
> Thanks,
> John
>|||Uri,
Thanks for your suggestion; however, apparently, the sysxlogins table no
longer exists in 2005. That's the point my script was failing as well.
Thanks,
"Uri Dimant" wrote:

> John
> I did it by using two stored procedures that MS have provided. I have not
> played with it on SQL Server 2005 since I re-created logins on the new
> server , however it worth to try.
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
>
> "John Beschler" <JohnBeschler@.discussions.microsoft.com> wrote in message
> news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
>
>|||Hi John.
Take a look at this link http://support.microsoft.com/kb/246133/en-us. I
change this script to generate new DDL sintax for SQL Server 2005. You can
get it from
http://solidqualitylearning.com/blo...01/28/1515.aspx
(comments are in Spanish, sorry :-) )
"John Beschler" <JohnBeschler@.discussions.microsoft.com> escribi en el
mensaje news:D002F332-27CD-422A-B26C-BA267F5C77C3@.microsoft.com...[vbcol=seagreen]
> Uri,
> Thanks for your suggestion; however, apparently, the sysxlogins table no
> longer exists in 2005. That's the point my script was failing as well.
> Thanks,
>
> "Uri Dimant" wrote:
>