long transaction

Hi,

I am still not very proficient in SQLServer. So apology if the
question sounds basic.

We have a script to clean old unwanted data. It basically deletes
all rows which are more than 2 weeks old. It deletes data from
33 tables and the number of rows in each table runs into few millions.
What I see in the script (not written by me :-) ) is that all data is
deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
I have background in informix, such an action in Informix may result
in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.

Also won't it have performance problem if all rows are marked locked
till they are committed.

TIA."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:bq071h$1tbn6v$1@.ID-75254.news.uni-berlin.de...
> Hi,
> I am still not very proficient in SQLServer. So apology if the
> question sounds basic.
> We have a script to clean old unwanted data. It basically deletes
> all rows which are more than 2 weeks old. It deletes data from
> 33 tables and the number of rows in each table runs into few millions.
> What I see in the script (not written by me :-) ) is that all data is
> deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
> I have background in informix, such an action in Informix may result
> in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.

I'm not sure what the "LONG TRANSACTION PROBLEM" in Informix is, but yes,
you basically don't want transactions to run for long periods of time.

As you point out, this can cause blocking on reads depending on your
isolation level. In addition, it can make recovery that much harder.

Assume your transaction takes 20 minutes to run (that's a really long time
admittedly) and your server gets rebooted 19 minutes into it. You're
looking at probably at least 19 minutes of rollback time when you reboot.
During this time the database will be completely inaccessible.

You have a couple of options that depend on your environment.

One is to simply break this up into multiple transactions. Of course then
it depends on what happens if one fails. Does this matter to other
transactions? Are their any dependencies?

It may also be possible to copy the needed data into a temp table, truncate
the original table and move data back.

But regardless, I would try to redesign this.

> Also won't it have performance problem if all rows are marked locked
> till they are committed.
> TIA.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message news:<bq071h$1tbn6v$1@.ID-75254.news.uni-berlin.de>...
> Hi,
> I am still not very proficient in SQLServer. So apology if the
> question sounds basic.
> We have a script to clean old unwanted data. It basically deletes
> all rows which are more than 2 weeks old. It deletes data from
> 33 tables and the number of rows in each table runs into few millions.
> What I see in the script (not written by me :-) ) is that all data is
> deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
> I have background in informix, such an action in Informix may result
> in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.
> Also won't it have performance problem if all rows are marked locked
> till they are committed.
> TIA.

If the data is unwanted, it seems unusual to delete it like that,
unless there really is a genuine requirement to do it inside a single
transaction. A large transaction like that can easily cause
performance and locking problems.

Assuming there isn't a real need to use a single transaction, then you
could do batch deletes instead, perhaps as an overnight maintenance
job to prevent users seeing unexpected results if they run queries
during the deletion.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote
> If the data is unwanted, it seems unusual to delete it like that,
> unless there really is a genuine requirement to do it inside a single
> transaction.

I don't see any such requirement. It can always continue next time if
the delete fails for some reason.

> A large transaction like that can easily cause
> performance and locking problems.

That's what I thought.

> Assuming there isn't a real need to use a single transaction, then you
> could do batch deletes instead, perhaps as an overnight maintenance
> job to prevent users seeing unexpected results if they run queries
> during the deletion.

I plan to remove BEGIN and COMMIT. This way each delete will be atomic
by itself.

thanks.

long SQL lines for osql

We have a very long and complicated SQL script which we run to upgrade
a version of our software from old to new. It works great in Query
Analyzer, but when run through osql it takes errors on lines that are
very long and (I think) stops reading after a certain amount of
characters. I've searched the net but haven't found anyone mentioning
this before. I have tried the -w 5000 parm to no avail.

Any suggestions?pb648174 (google@.webpaul.net) writes:
> We have a very long and complicated SQL script which we run to upgrade
> a version of our software from old to new. It works great in Query
> Analyzer, but when run through osql it takes errors on lines that are
> very long and (I think) stops reading after a certain amount of
> characters. I've searched the net but haven't found anyone mentioning
> this before. I have tried the -w 5000 parm to no avail.

-w controls the width of the output. As I understand it, you have
problems with the input.

One thing to keep in mind, is by default OSQL runs with QUOTED_IDENTIFIER
off, which is different from Query Analyzer. Run with -I to change this.

It would have helped if you had included any error messages.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The error messages are bogus, i.e. it is stopping processing in the
middle of a line, i.e. "Ad" is not a column, when the line is doing
something with "Address". I'll try the -l option and see if that makes
a difference, but it doesn't seem like it would make a difference since
I am using standard name, i.e. nothing with brackets, quotes or spaces
in the names.|||pb648174 (google@.webpaul.net) writes:
> The error messages are bogus, i.e. it is stopping processing in the
> middle of a line, i.e. "Ad" is not a column, when the line is doing
> something with "Address". I'll try the -l option and see if that makes
> a difference, but it doesn't seem like it would make a difference since
> I am using standard name, i.e. nothing with brackets, quotes or spaces
> in the names.

OK, it sounds like it chokes on something.

It could be the file size, but it could also be the batch size.

If it is the file size, you can split the file into several and then
include the files with ~r. (Well, maybe. It could choke on the total.
But you could try.)

If it is the batch size, maybe you can throw in more "go" of you have
very long batches.

I recall that we had an issue with INSERT-files that we generate
from Excel. I had to fix the tool, so that it added a "go" after
each 40th EXEC or so. But if memory serves, it was the ISQL/W 6.5
that had this problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I haven't been able to reproduce it locally - it always happens during
our production push... Then I have to execute it in QA for every
database we have which is a pain. The total file size is about 2 MB and
there is a go after pretty much each statement.

Erland Sommarskog wrote:
> pb648174 (google@.webpaul.net) writes:
> > The error messages are bogus, i.e. it is stopping processing in the
> > middle of a line, i.e. "Ad" is not a column, when the line is doing
> > something with "Address". I'll try the -l option and see if that makes
> > a difference, but it doesn't seem like it would make a difference since
> > I am using standard name, i.e. nothing with brackets, quotes or spaces
> > in the names.
> OK, it sounds like it chokes on something.
> It could be the file size, but it could also be the batch size.
> If it is the file size, you can split the file into several and then
> include the files with ~r. (Well, maybe. It could choke on the total.
> But you could try.)
> If it is the batch size, maybe you can throw in more "go" of you have
> very long batches.
> I recall that we had an issue with INSERT-files that we generate
> from Excel. I had to fix the tool, so that it added a "go" after
> each 40th EXEC or so. But if memory serves, it was the ISQL/W 6.5
> that had this problems.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

long running queries

Hi Everyone

I wanted to generate a script which will return all the queries which are running more than 5 minutes.

I want the text of the sql query so that i can tune the query if required.

I have used the profiler and have filtered the query which are running setting Duration parameter.

I actually wanted to set a automated method which will send me by mail the list of all queries which are running more than the desired time.
May be some store proc using system tables. which I will call in a job and the job will run periodically.

Thanks in advance.

You can save the results of the profiler trace to a SQL Server table, and then either ;

1) Have a trigger on the table to send an email, or

2) Use the user defined performance monitoring counters to do this - Use a SQL Agent job to periodically query the table, and set the user defined counter if the value exceeds the threshold.

Let me know if you need more details on setting up either of the above.

|||

Hi Rod,

Do you know other than Profiler, is there another less resource intensive way to record the SQL statements that run longer than a certain time? I have a very critical server that is very sensitive and always has blocking issue. I would like to use the least resource intensive way to get the statements that run long thus caused the blocking. I saw an article saying that we can use ODBC trace, to save the long running queries to a log file when configure the system DSN. http://msdn2.microsoft.com/en-us/library/ms403323.aspx Do you know whether the ODBC trace is less intensive than Profiler or not?

Also, where I should set it? on the SQL server that I want to find out the long query? Just create a DSN that points to the blocking issue db and save long query to a log file? Then all the long queries will be logged to the file as long as I turn on the trace? But all user applications on that server won't use that DSN though. The ASP pages set their own connection string. What the file looks like? Do I need to use other tool to interpret the log file? I use SQL server 2000 Enterprise Edition sp4.

Thanks a lot,

-Jessie

long running queries

Hi Everyone

I wanted to generate a script which will return all the queries which are running more than 5 minutes.

I want the text of the sql query so that i can tune the query if required.

I have used the profiler and have filtered the query which are running setting Duration parameter.

I actually wanted to set a automated method which will send me by mail the list of all queries which are running more than the desired time.
May be some store proc using system tables. which I will call in a job and the job will run periodically.

Thanks in advance.

You can save the results of the profiler trace to a SQL Server table, and then either ;

1) Have a trigger on the table to send an email, or

2) Use the user defined performance monitoring counters to do this - Use a SQL Agent job to periodically query the table, and set the user defined counter if the value exceeds the threshold.

Let me know if you need more details on setting up either of the above.

|||

Hi Rod,

Do you know other than Profiler, is there another less resource intensive way to record the SQL statements that run longer than a certain time? I have a very critical server that is very sensitive and always has blocking issue. I would like to use the least resource intensive way to get the statements that run long thus caused the blocking. I saw an article saying that we can use ODBC trace, to save the long running queries to a log file when configure the system DSN. http://msdn2.microsoft.com/en-us/library/ms403323.aspx Do you know whether the ODBC trace is less intensive than Profiler or not?

Also, where I should set it? on the SQL server that I want to find out the long query? Just create a DSN that points to the blocking issue db and save long query to a log file? Then all the long queries will be logged to the file as long as I turn on the trace? But all user applications on that server won't use that DSN though. The ASP pages set their own connection string. What the file looks like? Do I need to use other tool to interpret the log file? I use SQL server 2000 Enterprise Edition sp4.

Thanks a lot,

-Jessie

sql

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2K
Hi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:

>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:

> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.co...ore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K
|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:

>I recently moved my backups from the wizard generated maintenance plans to
a
>simple backup script. My 180GB database is now taking 7 hours to backup wit
h
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the ne
w
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:

> I recently moved my backups from the wizard generated maintenance plans to
a
> simple backup script. My 180GB database is now taking 7 hours to backup wi
th
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researche
d
> (e.g., http://www.sql-server-performance.c...tore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>> I recently moved my backups from the wizard generated maintenance plans
>> to a
>> simple backup script. My 180GB database is now taking 7 hours to backup
>> with
>> instead of the normal 4 hours. The step details show 7.359 MB/sec when
>> the
>> backup completes in 7 hours and 12.453 MB/sec when the backup completes
>> in 4
>> hours.
>> Below is my script. This is actually step 2. Step 1 deletes the previous
>> backup.
>> BACKUP DATABASE MyDB
>> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>> WITH INIT
>> During the backup, pinging the NAS device from the SQL Server takes 0ms.
>> I have used Perfmon and the counters seem usual from what I have
>> researched
>> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>> Average Device Throughput bytes/sec: 5570346
>> Average % Disk Time: 3.854
>> Average Disk Queue Length: .0193
>> Average IO Write Bytes/sec: 5897638
>> Average Split IO/sec: 0
>> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for
>> SQLAgent -
>> Alert Engine.
>> No compression is taking place on either machine.
>> Does anyone have any suggestions as what is causing the backup time to
>> almost double?
>> Thanks,
>> ray
>> SS2K

LogSpace Script

I am writing a logspace checking script. For now I just
want it to send me the size of all my log files. But I
am doing something stupid or don't understand cursors
because it's only putting the last database into @.msg3.
If anyone has any ideas what I'm doing wrong, I'd sure
appreciate it as this is driving me crazy!
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 varchar(100)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
fetch next from email_cursor into @.field1, @.field2
begin
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
print '@.msg2'
print @.msg2
end
close email_cursor
deallocate email_cursor
print '@.msg3'
print @.msg3
Is it possible that @.field1 and/or @.field2 is ever NULL?
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
> I am writing a logspace checking script. For now I just
> want it to send me the size of all my log files. But I
> am doing something stupid or don't understand cursors
> because it's only putting the last database into @.msg3.
> If anyone has any ideas what I'm doing wrong, I'd sure
> appreciate it as this is driving me crazy!
>
> declare @.server varchar(50)
> declare @.subject2 varchar(50)
> declare @.msg2 varchar(8000)
> declare @.msg3 varchar(8000)
> declare @.email_body varchar(8000)
> declare @.field1 varchar(100)
> declare @.field2 varchar(100)
> --declare testint int
> set @.server = 'SQL_3'
> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
> LOG SPACE OVER 2G'
> print @.subject2
> if exists (select name from tempdb..sysobjects where name
> like '#temcmlogspace%')
> begin
> drop table #temcmlogspace
> end
> create table #temcmlogspace (dbname varchar(100) null,
> logsize2 numeric(16, 6) null,
> logusedpcnt numeric(16,6) null, status2 int null)
> --The size is in Megs:
> insert into #temcmlogspace
> exec ('dbcc sqlperf(logspace)')
> set @.msg3 = ''
> declare email_cursor cursor for
> select dbname, logsize2 from #temcmlogspace
> open email_cursor
> while @.@.fetch_status = 0
> fetch next from email_cursor into @.field1, @.field2
> begin
> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
> (20))
> set @.msg3 = @.msg3 + @.msg2
> print '@.msg2'
> print @.msg2
> end
> close email_cursor
> deallocate email_cursor
> print '@.msg3'
> print @.msg3
>
>
|||Shouldn't be: it's taking the results from DBCC
Logspace. Why?
>--Original Message--
>Is it possible that @.field1 and/or @.field2 is ever NULL?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
just[vbcol=seagreen]
WITH[vbcol=seagreen]
name
>
>.
>
|||Well, if you set @.msg3 = @.msg3 + NULL, guess what happens?
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...[vbcol=seagreen]
> Shouldn't be: it's taking the results from DBCC
> Logspace. Why?
> message
> just
> WITH
> name
|||Thx for the help, but I figured out what was wrong. I
had the incorrect order. This script works:
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 numeric(16, 6)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
--select * from #temcmlogspace
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
begin
fetch next from email_cursor into @.field1, @.field2
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
--print '@.msg2'
--print @.msg2
end
close email_cursor
deallocate email_cursor
--print '@.msg3'
--print @.msg3
>--Original Message--
>Well, if you set @.msg3 = @.msg3 + NULL, guess what
happens?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...
NULL?[vbcol=seagreen]
But I[vbcol=seagreen]
cursors[vbcol=seagreen]
@.msg3.[vbcol=seagreen]
sure[vbcol=seagreen]
null,[vbcol=seagreen]
varchar
>
>.
>

LogSpace Script

I am writing a logspace checking script. For now I just
want it to send me the size of all my log files. But I
am doing something stupid or don't understand cursors
because it's only putting the last database into @.msg3.
If anyone has any ideas what I'm doing wrong, I'd sure
appreciate it as this is driving me crazy!
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 varchar(100)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
fetch next from email_cursor into @.field1, @.field2
begin
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
print '@.msg2'
print @.msg2
end
close email_cursor
deallocate email_cursor
print '@.msg3'
print @.msg3Is it possible that @.field1 and/or @.field2 is ever NULL?
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
> I am writing a logspace checking script. For now I just
> want it to send me the size of all my log files. But I
> am doing something stupid or don't understand cursors
> because it's only putting the last database into @.msg3.
> If anyone has any ideas what I'm doing wrong, I'd sure
> appreciate it as this is driving me crazy!
>
> declare @.server varchar(50)
> declare @.subject2 varchar(50)
> declare @.msg2 varchar(8000)
> declare @.msg3 varchar(8000)
> declare @.email_body varchar(8000)
> declare @.field1 varchar(100)
> declare @.field2 varchar(100)
> --declare testint int
> set @.server = 'SQL_3'
> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
> LOG SPACE OVER 2G'
> print @.subject2
> if exists (select name from tempdb..sysobjects where name
> like '#temcmlogspace%')
> begin
> drop table #temcmlogspace
> end
> create table #temcmlogspace (dbname varchar(100) null,
> logsize2 numeric(16, 6) null,
> logusedpcnt numeric(16,6) null, status2 int null)
> --The size is in Megs:
> insert into #temcmlogspace
> exec ('dbcc sqlperf(logspace)')
> set @.msg3 = ''
> declare email_cursor cursor for
> select dbname, logsize2 from #temcmlogspace
> open email_cursor
> while @.@.fetch_status = 0
> fetch next from email_cursor into @.field1, @.field2
> begin
> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
> (20))
> set @.msg3 = @.msg3 + @.msg2
> print '@.msg2'
> print @.msg2
> end
> close email_cursor
> deallocate email_cursor
> print '@.msg3'
> print @.msg3
>
>|||Shouldn't be: it's taking the results from DBCC
Logspace. Why?
>--Original Message--
>Is it possible that @.field1 and/or @.field2 is ever NULL?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
just[vbcol=seagreen]
WITH[vbcol=seagreen]
name[vbcol=seagreen]
>
>.
>|||Well, if you set @.msg3 = @.msg3 + NULL, guess what happens?
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...[vbcol=seagreen]
> Shouldn't be: it's taking the results from DBCC
> Logspace. Why?
> message
> just
> WITH
> name|||Thx for the help, but I figured out what was wrong. I
had the incorrect order. This script works:
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 numeric(16, 6)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
--select * from #temcmlogspace
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
begin
fetch next from email_cursor into @.field1, @.field2
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
--print '@.msg2'
--print @.msg2
end
close email_cursor
deallocate email_cursor
--print '@.msg3'
--print @.msg3
>--Original Message--
>Well, if you set @.msg3 = @.msg3 + NULL, guess what
happens?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...
NULL?[vbcol=seagreen]
But I[vbcol=seagreen]
cursors[vbcol=seagreen]
@.msg3.[vbcol=seagreen]
sure[vbcol=seagreen]
null,[vbcol=seagreen]
varchar[vbcol=seagreen]
>
>.
>sql

LogSpace Script

I am writing a logspace checking script. For now I just
want it to send me the size of all my log files. But I
am doing something stupid or don't understand cursors
because it's only putting the last database into @.msg3.
If anyone has any ideas what I'm doing wrong, I'd sure
appreciate it as this is driving me crazy!
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 varchar(100)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
fetch next from email_cursor into @.field1, @.field2
begin
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
print '@.msg2'
print @.msg2
end
close email_cursor
deallocate email_cursor
print '@.msg3'
print @.msg3Is it possible that @.field1 and/or @.field2 is ever NULL?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
> I am writing a logspace checking script. For now I just
> want it to send me the size of all my log files. But I
> am doing something stupid or don't understand cursors
> because it's only putting the last database into @.msg3.
> If anyone has any ideas what I'm doing wrong, I'd sure
> appreciate it as this is driving me crazy!
>
> declare @.server varchar(50)
> declare @.subject2 varchar(50)
> declare @.msg2 varchar(8000)
> declare @.msg3 varchar(8000)
> declare @.email_body varchar(8000)
> declare @.field1 varchar(100)
> declare @.field2 varchar(100)
> --declare testint int
> set @.server = 'SQL_3'
> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
> LOG SPACE OVER 2G'
> print @.subject2
> if exists (select name from tempdb..sysobjects where name
> like '#temcmlogspace%')
> begin
> drop table #temcmlogspace
> end
> create table #temcmlogspace (dbname varchar(100) null,
> logsize2 numeric(16, 6) null,
> logusedpcnt numeric(16,6) null, status2 int null)
> --The size is in Megs:
> insert into #temcmlogspace
> exec ('dbcc sqlperf(logspace)')
> set @.msg3 = ''
> declare email_cursor cursor for
> select dbname, logsize2 from #temcmlogspace
> open email_cursor
> while @.@.fetch_status = 0
> fetch next from email_cursor into @.field1, @.field2
> begin
> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
> (20))
> set @.msg3 = @.msg3 + @.msg2
> print '@.msg2'
> print @.msg2
> end
> close email_cursor
> deallocate email_cursor
> print '@.msg3'
> print @.msg3
>
>|||Shouldn't be: it's taking the results from DBCC
Logspace. Why?
>--Original Message--
>Is it possible that @.field1 and/or @.field2 is ever NULL?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
>> I am writing a logspace checking script. For now I
just
>> want it to send me the size of all my log files. But I
>> am doing something stupid or don't understand cursors
>> because it's only putting the last database into @.msg3.
>> If anyone has any ideas what I'm doing wrong, I'd sure
>> appreciate it as this is driving me crazy!
>>
>> declare @.server varchar(50)
>> declare @.subject2 varchar(50)
>> declare @.msg2 varchar(8000)
>> declare @.msg3 varchar(8000)
>> declare @.email_body varchar(8000)
>> declare @.field1 varchar(100)
>> declare @.field2 varchar(100)
>> --declare testint int
>> set @.server = 'SQL_3'
>> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs
WITH
>> LOG SPACE OVER 2G'
>> print @.subject2
>> if exists (select name from tempdb..sysobjects where
name
>> like '#temcmlogspace%')
>> begin
>> drop table #temcmlogspace
>> end
>> create table #temcmlogspace (dbname varchar(100) null,
>> logsize2 numeric(16, 6) null,
>> logusedpcnt numeric(16,6) null, status2 int null)
>> --The size is in Megs:
>> insert into #temcmlogspace
>> exec ('dbcc sqlperf(logspace)')
>> set @.msg3 = ''
>> declare email_cursor cursor for
>> select dbname, logsize2 from #temcmlogspace
>> open email_cursor
>> while @.@.fetch_status = 0
>> fetch next from email_cursor into @.field1, @.field2
>> begin
>> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
>> (20))
>> set @.msg3 = @.msg3 + @.msg2
>> print '@.msg2'
>> print @.msg2
>> end
>> close email_cursor
>> deallocate email_cursor
>> print '@.msg3'
>> print @.msg3
>>
>>
>
>.
>|||Well, if you set @.msg3 = @.msg3 + NULL, guess what happens?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...
> Shouldn't be: it's taking the results from DBCC
> Logspace. Why?
> >--Original Message--
> >Is it possible that @.field1 and/or @.field2 is ever NULL?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Alley" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
> >> I am writing a logspace checking script. For now I
> just
> >> want it to send me the size of all my log files. But I
> >> am doing something stupid or don't understand cursors
> >> because it's only putting the last database into @.msg3.
> >> If anyone has any ideas what I'm doing wrong, I'd sure
> >> appreciate it as this is driving me crazy!
> >>
> >>
> >> declare @.server varchar(50)
> >> declare @.subject2 varchar(50)
> >> declare @.msg2 varchar(8000)
> >> declare @.msg3 varchar(8000)
> >> declare @.email_body varchar(8000)
> >> declare @.field1 varchar(100)
> >> declare @.field2 varchar(100)
> >> --declare testint int
> >>
> >> set @.server = 'SQL_3'
> >> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs
> WITH
> >> LOG SPACE OVER 2G'
> >> print @.subject2
> >> if exists (select name from tempdb..sysobjects where
> name
> >> like '#temcmlogspace%')
> >> begin
> >> drop table #temcmlogspace
> >> end
> >> create table #temcmlogspace (dbname varchar(100) null,
> >> logsize2 numeric(16, 6) null,
> >> logusedpcnt numeric(16,6) null, status2 int null)
> >> --The size is in Megs:
> >> insert into #temcmlogspace
> >> exec ('dbcc sqlperf(logspace)')
> >> set @.msg3 = ''
> >> declare email_cursor cursor for
> >> select dbname, logsize2 from #temcmlogspace
> >> open email_cursor
> >>
> >> while @.@.fetch_status = 0
> >> fetch next from email_cursor into @.field1, @.field2
> >> begin
> >> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
> >> (20))
> >> set @.msg3 = @.msg3 + @.msg2
> >> print '@.msg2'
> >> print @.msg2
> >> end
> >> close email_cursor
> >> deallocate email_cursor
> >> print '@.msg3'
> >> print @.msg3
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||Thx for the help, but I figured out what was wrong. I
had the incorrect order. This script works:
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 numeric(16, 6)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
--select * from #temcmlogspace
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
begin
fetch next from email_cursor into @.field1, @.field2
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
--print '@.msg2'
--print @.msg2
end
close email_cursor
deallocate email_cursor
--print '@.msg3'
--print @.msg3
>--Original Message--
>Well, if you set @.msg3 = @.msg3 + NULL, guess what
happens?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...
>> Shouldn't be: it's taking the results from DBCC
>> Logspace. Why?
>> >--Original Message--
>> >Is it possible that @.field1 and/or @.field2 is ever
NULL?
>> >
>> >--
>> >http://www.aspfaq.com/
>> >(Reverse address to reply.)
>> >
>> >
>> >
>> >
>> >"Alley" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
>> >> I am writing a logspace checking script. For now I
>> just
>> >> want it to send me the size of all my log files.
But I
>> >> am doing something stupid or don't understand
cursors
>> >> because it's only putting the last database into
@.msg3.
>> >> If anyone has any ideas what I'm doing wrong, I'd
sure
>> >> appreciate it as this is driving me crazy!
>> >>
>> >>
>> >> declare @.server varchar(50)
>> >> declare @.subject2 varchar(50)
>> >> declare @.msg2 varchar(8000)
>> >> declare @.msg3 varchar(8000)
>> >> declare @.email_body varchar(8000)
>> >> declare @.field1 varchar(100)
>> >> declare @.field2 varchar(100)
>> >> --declare testint int
>> >>
>> >> set @.server = 'SQL_3'
>> >> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs
>> WITH
>> >> LOG SPACE OVER 2G'
>> >> print @.subject2
>> >> if exists (select name from tempdb..sysobjects where
>> name
>> >> like '#temcmlogspace%')
>> >> begin
>> >> drop table #temcmlogspace
>> >> end
>> >> create table #temcmlogspace (dbname varchar(100)
null,
>> >> logsize2 numeric(16, 6) null,
>> >> logusedpcnt numeric(16,6) null, status2 int null)
>> >> --The size is in Megs:
>> >> insert into #temcmlogspace
>> >> exec ('dbcc sqlperf(logspace)')
>> >> set @.msg3 = ''
>> >> declare email_cursor cursor for
>> >> select dbname, logsize2 from #temcmlogspace
>> >> open email_cursor
>> >>
>> >> while @.@.fetch_status = 0
>> >> fetch next from email_cursor into @.field1, @.field2
>> >> begin
>> >> set @.msg2 = @.field1 + ' ' + cast(@.field2 as
varchar
>> >> (20))
>> >> set @.msg3 = @.msg3 + @.msg2
>> >> print '@.msg2'
>> >> print @.msg2
>> >> end
>> >> close email_cursor
>> >> deallocate email_cursor
>> >> print '@.msg3'
>> >> print @.msg3
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

LogReader SubSystem

I'm trying to setup a publisher on an msde database and am getting an error
when running my script. The error in the event viewer (and job history)
is -
Step 2 of job 'machine\instance-database-1' cannot be run because the
LogReader subsystem failed to load. The job has been suspended.
The job category is REPL -LogReader. Any ideas what I can do or what I
missed?
TIA,
Dave
on your msde server open a command prompt and go to c:\program
files\Microsoft sql server\80\com and type logread
do you get an error message?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <dave.golightly@.stgutah.com> wrote in message
news:uZYCJIYpEHA.2304@.TK2MSFTNGP14.phx.gbl...
> I'm trying to setup a publisher on an msde database and am getting an
error
> when running my script. The error in the event viewer (and job history)
> is -
> Step 2 of job 'machine\instance-database-1' cannot be run because the
> LogReader subsystem failed to load. The job has been suspended.
> The job category is REPL -LogReader. Any ideas what I can do or what I
> missed?
> TIA,
> Dave
>
|||I got -
"'logread' is not recognized as an internal or external command, operable
program or batch file"
So the installshield object I'm using to install the msde isn't installing
the logread file. Is there any other files I should be checking for?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uhmT8OZpEHA.744@.TK2MSFTNGP10.phx.gbl...
> on your msde server open a command prompt and go to c:\program
> files\Microsoft sql server\80\com and type logread
> do you get an error message?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dave" <dave.golightly@.stgutah.com> wrote in message
> news:uZYCJIYpEHA.2304@.TK2MSFTNGP14.phx.gbl...
> error
>
|||Oops, I'm making a huge mistake here. MSDE servers are not enable to be
transactional publishers. You will have to make it a merge publisher.
Sorry about that.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <dave.golightly@.stgutah.com> wrote in message
news:Os127ZZpEHA.3688@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I got -
> "'logread' is not recognized as an internal or external command, operable
> program or batch file"
> So the installshield object I'm using to install the msde isn't installing
> the logread file. Is there any other files I should be checking for?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uhmT8OZpEHA.744@.TK2MSFTNGP10.phx.gbl...
history)
>
|||I'm trying to create a snapshot publisher. Is there a setting I've got
wrong?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eG0FwfZpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Oops, I'm making a huge mistake here. MSDE servers are not enable to be
> transactional publishers. You will have to make it a merge publisher.
> Sorry about that.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dave" <dave.golightly@.stgutah.com> wrote in message
> news:Os127ZZpEHA.3688@.TK2MSFTNGP09.phx.gbl...
> history)
>
|||Here's the script -
-- Adding the snapshot publication
exec sp_addpublication @.publication = N'inspPro Snapshot Publication',
@.restricted = N'false', @.sync_method = N'native', @.repl_freq = N'snapshot',
@.description = N'Snapshot publication of inspPro database from Publisher
[workstation]\INSPPRO.', @.status = N'active', @.allow_push = N'true',
@.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet =
N'false', @.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false', @.autogen_sync_procs = N'true', @.retention = 0,
@.allow_queued_tran = N'true', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false', @.conflict_policy
= N'sub reinit', @.centralized_conflicts = N'true', @.conflict_retention = 14,
@.queue_type = N'sql', @.add_to_active_directory = N'false',
@.logreader_job_name = N'[workstation]\INSPPRO-inspPro-1'
GO
exec sp_addpublication_snapshot @.publication = N'inspPro Snapshot
Publication',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.snapshot_job_name = N'inspPro Snapshot
Snapshot Agent'
GO
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eG0FwfZpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Oops, I'm making a huge mistake here. MSDE servers are not enable to be
> transactional publishers. You will have to make it a merge publisher.
> Sorry about that.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dave" <dave.golightly@.stgutah.com> wrote in message
> news:Os127ZZpEHA.3688@.TK2MSFTNGP09.phx.gbl...
> history)
>
|||This looks like a updateable subscription using the queue reader. I don't
believe this is supported on MSDE where MSDE is the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <dave.golightly@.stgutah.com> wrote in message
news:e0W6CpZpEHA.3868@.TK2MSFTNGP15.phx.gbl...
> Here's the script -
> -- Adding the snapshot publication
> exec sp_addpublication @.publication = N'inspPro Snapshot Publication',
> @.restricted = N'false', @.sync_method = N'native', @.repl_freq =
N'snapshot',
> @.description = N'Snapshot publication of inspPro database from Publisher
> [workstation]\INSPPRO.', @.status = N'active', @.allow_push = N'true',
> @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet =
> N'false', @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false', @.autogen_sync_procs = N'true', @.retention =
0,
> @.allow_queued_tran = N'true', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.conflict_policy
> = N'sub reinit', @.centralized_conflicts = N'true', @.conflict_retention =
14,
> @.queue_type = N'sql', @.add_to_active_directory = N'false',
> @.logreader_job_name = N'[workstation]\INSPPRO-inspPro-1'
> GO
> exec sp_addpublication_snapshot @.publication = N'inspPro Snapshot
> Publication',@.frequency_type = 4, @.frequency_interval = 1,
> @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
> @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date
=[vbcol=seagreen]
> 0, @.active_end_date = 0, @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.snapshot_job_name = N'inspPro Snapshot
> Snapshot Agent'
> GO
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eG0FwfZpEHA.592@.TK2MSFTNGP11.phx.gbl...
operable[vbcol=seagreen]
an[vbcol=seagreen]
the[vbcol=seagreen]
what
>

Logins without User Mapping

Hi All
In SQL Server 2005 how do I go about writing a script to determine all
server logins that do not have any User Mapping?
As I have a test server where logins have been created and as databases have
been dropped there are now logins that exist that no longer have access to
any databases and I would like to remove them.
Thanks
Hello,
Take a loook into sp_change_users_logins system proc in books online..
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks
|||Hi David
One way to do it is trying to run the query against the syslogins table in
the master database. An example of the query:
SELECT * FROM master.dbo.syslogins WHERE dbname IS NULL.
or try this one:
SELECT *
FROM master.dbo.syslogins
WHERE [sid] NOT IN (SELECT [sid] FROM database1.dbo.sysusers WHERE [sid] IS
NOT NULL)
AND [sid] NOT IN (SELECT [sid] FROM database2.dbo.sysusers WHERE [sid] IS
NOT NULL)
AND dbname <> 'master'
However, you might want to review the result first before deleting them. I
have not really tested the script in details. But logically it should work.
Hopes that's helpful.
Thank you
Lucas
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks

Logins without User Mapping

Hi All
In SQL Server 2005 how do I go about writing a script to determine all
server logins that do not have any User Mapping?
As I have a test server where logins have been created and as databases have
been dropped there are now logins that exist that no longer have access to
any databases and I would like to remove them.
ThanksHello,
Take a loook into sp_change_users_logins system proc in books online..
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks|||Hi David
One way to do it is trying to run the query against the syslogins table in
the master database. An example of the query:
SELECT * FROM master.dbo.syslogins WHERE dbname IS NULL.
or try this one:
SELECT *
FROM master.dbo.syslogins
WHERE [sid] NOT IN (SELECT [sid] FROM database1.dbo.sysusers WHERE [sid] IS
NOT NULL)
AND [sid] NOT IN (SELECT [sid] FROM database2.dbo.sysusers WHERE [sid] IS
NOT NULL)
AND dbname <> 'master'
However, you might want to review the result first before deleting them. I
have not really tested the script in details. But logically it should work.
Hopes that's helpful.
Thank you
Lucas
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks

Logins without User Mapping

Hi All
In SQL Server 2005 how do I go about writing a script to determine all
server logins that do not have any User Mapping?
As I have a test server where logins have been created and as databases have
been dropped there are now logins that exist that no longer have access to
any databases and I would like to remove them.
ThanksHello,
Take a loook into sp_change_users_logins system proc in books online..
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks|||Hi David
One way to do it is trying to run the query against the syslogins table in
the master database. An example of the query:
SELECT * FROM master.dbo.syslogins WHERE dbname IS NULL.
or try this one:
SELECT *
FROM master.dbo.syslogins
WHERE [sid] NOT IN (SELECT [sid] FROM database1.dbo.sysusers WHERE &
#91;sid] IS
NOT NULL)
AND [sid] NOT IN (SELECT [sid] FROM database2.dbo.sysusers WHERE
1;sid] IS
NOT NULL)
AND dbname <> 'master'
However, you might want to review the result first before deleting them. I
have not really tested the script in details. But logically it should work.
Hopes that's helpful.
Thank you
Lucas
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.
Nevermind. I answered my own q.
"CLM" wrote:

> I have always worked out of Enterprise Manager to view the permissions/users,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a lot
> of databases and I've found Ent Mgr is just too slow.

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.Nevermind. I answered my own q.
"CLM" wrote:
> I have always worked out of Enterprise Manager to view the permissions/users,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a lot
> of databases and I've found Ent Mgr is just too slow.

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users
,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.Nevermind. I answered my own q.
"CLM" wrote:

> I have always worked out of Enterprise Manager to view the permissions/use
rs,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I wa
nt
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a l
ot
> of databases and I've found Ent Mgr is just too slow.

Logins & its associated tables.

Hi All,

Does anyone knows the query/Script, which gives the list of tables the logins( SQL & Windows Authenticated) have access to.

In other words, the output should be logins and their access to respective tables.

Thanks for all your help.

Naj,

Exec sp_helprotect

Run the above statement in your database, you will see all your objects, not only tables and who has access to those objects. More info, refer Books online - search sp_helprotect

Sample output

--

Owner Object Grantee Grantor ProtectType Action Column

- - --

dbo NarasApp Naras\jay dbo Grant Select (All+New)

dbo NarasApp public dbo Grant Select (All+New)

--

Here Object column shows the name of object and Grantee column shows who has access on the object.

Thanks.

Naras.

logins

How can I write a script to generate the list of SQL users that have sysadmin privileges. I would also like to do the same for the windows accounts as well.

Please let me know.

Thanks.

Hi,

SELECT CASE WHEN isntname = 1 OR isntgroup = 1 OR isntuser = 1 THEN 'WindowsLogin' ELSE 'SQLLogin' END

FROM syslogins

WHERE sysadmin = 1

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

|||

The catalog view syslogins has been deprecated in SQL Server 2005 (for more information visit "sys.syslogins (Transact-SQL)" in BOL http://msdn2.microsoft.com/en-us/library/ms178593.aspx).

For SQL 2005 I would recommend using the new catalog views. Here is a sample on how to get the role membership information.

-- Select all role members (fixed role name, member name)

--

SELECT suser_name( role_principal_id ) as server_fixed_role_name,

suser_name( member_principal_id ) as member_login_name

FROM sys.server_role_members

ORDER BY role_principal_id

go

-- Select all role members

-- (fixed role name, member type, member name)

--

SELECT suser_name( sysrolemembers.role_principal_id ) as server_fixed_role_name,

principals.type_desc as member_login_type,

principals.name as member_login_name

FROM sys.server_role_members sysrolemembers,

sys.server_principals principals

WHERE sysrolemembers.member_principal_id = principals.principal_id

ORDER BY sysrolemembers.role_principal_id, principals.type_desc, principals.name

go

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Note that both queries in the above answers will miss Windows users that are not "directly" sysadmins, but inherit their sysadmin property from a Windows group to which they belong.

Thanks
Laurentiu

Login Transfers

I have used this script generator quite successfully to generate transfer
login scripts with encrypted passwords. However, on one server its not
working. I thought it could be collation issue on the machine where the
script is generated. So, I remotely generated on the source server and
applied it on the destination server (both case insensitive sort order on SQL
2K). However, its not working.
Anyone any idea?
SCRIPT
=====
select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32), password),
', @.encryptopt = ''skip_encryption'''
from syslogins
where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
'repl_subscriber')
order by name
SELECT 'EXEC sp_change_users_login ''Report'''
SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''', '''+name+''''
from syslogins
where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
'repl_subscriber')
order by name
Regards,
MZeeshan
Hello MZeeshan,
What is the error message you encountered?
Also, I think the following article shall be helpful
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
| Thread-Topic: Login Transfers
| thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==
| X-WBNR-Posting-Host: 208.250.29.8
| From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| Subject: Login Transfers
| Date: Tue, 10 May 2005 15:14:04 -0700
| Lines: 31
| Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have used this script generator quite successfully to generate transfer
| login scripts with encrypted passwords. However, on one server its not
| working. I thought it could be collation issue on the machine where the
| script is generated. So, I remotely generated on the source server and
| applied it on the destination server (both case insensitive sort order on
SQL
| 2K). However, its not working.
|
| Anyone any idea?
|
| SCRIPT
| =====
|
| select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
password),
| ', @.encryptopt = ''skip_encryption'''
| from syslogins
| where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| 'repl_subscriber')
| order by name
|
| SELECT 'EXEC sp_change_users_login ''Report'''
|
| SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
'''+name+''''
| from syslogins
| where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| 'repl_subscriber')
| order by name
|
|
| --
| Regards,
| MZeeshan
|
|||When I tried logging in the password from source server didn't work on
destination.
Regards,
MZeeshan
"Peter Yang [MSFT]" wrote:

> Hello MZeeshan,
> What is the error message you encountered?
> Also, I think the following article shall be helpful
> 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/?id=246133
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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.
>
>
> --
> | Thread-Topic: Login Transfers
> | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==
> | X-WBNR-Posting-Host: 208.250.29.8
> | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> | Subject: Login Transfers
> | Date: Tue, 10 May 2005 15:14:04 -0700
> | Lines: 31
> | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have used this script generator quite successfully to generate transfer
> | login scripts with encrypted passwords. However, on one server its not
> | working. I thought it could be collation issue on the machine where the
> | script is generated. So, I remotely generated on the source server and
> | applied it on the destination server (both case insensitive sort order on
> SQL
> | 2K). However, its not working.
> |
> | Anyone any idea?
> |
> | SCRIPT
> | =====
> |
> | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
> password),
> | ', @.encryptopt = ''skip_encryption'''
> | from syslogins
> | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | 'repl_subscriber')
> | order by name
> |
> | SELECT 'EXEC sp_change_users_login ''Report'''
> |
> | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
> '''+name+''''
> | from syslogins
> | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | 'repl_subscriber')
> | order by name
> |
> |
> | --
> | Regards,
> | MZeeshan
> |
>
|||Hello MZeeshan,
I think the issue can occur if the logins you want to transfe has already
existed in the destination database.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
| Thread-Topic: Login Transfers
| thread-index: AcVWHVT9ma0LUaspRJuQWeBKhalJyw==
| X-WBNR-Posting-Host: 67.167.85.152
| From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
<DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Login Transfers
| Date: Wed, 11 May 2005 04:34:03 -0700
| Lines: 97
| Message-ID: <CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55812
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| When I tried logging in the password from source server didn't work on
| destination.
|
| --
| Regards,
| MZeeshan
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello MZeeshan,
| >
| > What is the error message you encountered?
| >
| > Also, I think the following article shall be helpful
| >
| > 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL
Server
| > http://support.microsoft.com/?id=246133
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner 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.
| >
| >
| >
| >
| > --
| > | Thread-Topic: Login Transfers
| > | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==
| > | X-WBNR-Posting-Host: 208.250.29.8
| > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| > | Subject: Login Transfers
| > | Date: Tue, 10 May 2005 15:14:04 -0700
| > | Lines: 31
| > | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I have used this script generator quite successfully to generate
transfer
| > | login scripts with encrypted passwords. However, on one server its
not
| > | working. I thought it could be collation issue on the machine where
the
| > | script is generated. So, I remotely generated on the source server
and
| > | applied it on the destination server (both case insensitive sort
order on
| > SQL
| > | 2K). However, its not working.
| > |
| > | Anyone any idea?
| > |
| > | SCRIPT
| > | =====
| > |
| > | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
| > password),
| > | ', @.encryptopt = ''skip_encryption'''
| > | from syslogins
| > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| > | 'repl_subscriber')
| > | order by name
| > |
| > | SELECT 'EXEC sp_change_users_login ''Report'''
| > |
| > | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
| > '''+name+''''
| > | from syslogins
| > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
| > | 'repl_subscriber')
| > | order by name
| > |
| > |
| > | --
| > | Regards,
| > | MZeeshan
| > |
| >
| >
|
|||Peter,
I did delete the logins before transferring.
But, I would like to thank you as the link you provided earlier to create
those stored procs in 'master' database did indeed helped me in transferring
the logins to destination server.
Thank you!
Regards,
MZeeshan
"Peter Yang [MSFT]" wrote:

> Hello MZeeshan,
> I think the issue can occur if the logins you want to transfe has already
> existed in the destination database.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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.
>
>
> --
> | Thread-Topic: Login Transfers
> | thread-index: AcVWHVT9ma0LUaspRJuQWeBKhalJyw==
> | X-WBNR-Posting-Host: 67.167.85.152
> | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> | References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
> <DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
> | Subject: RE: Login Transfers
> | Date: Wed, 11 May 2005 04:34:03 -0700
> | Lines: 97
> | Message-ID: <CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55812
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | When I tried logging in the password from source server didn't work on
> | destination.
> |
> | --
> | Regards,
> | MZeeshan
> |
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello MZeeshan,
> | >
> | > What is the error message you encountered?
> | >
> | > Also, I think the following article shall be helpful
> | >
> | > 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL
> Server
> | > http://support.microsoft.com/?id=246133
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner 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.
> | >
> | >
> | >
> | >
> | > --
> | > | Thread-Topic: Login Transfers
> | > | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==
> | > | X-WBNR-Posting-Host: 208.250.29.8
> | > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
> | > | Subject: Login Transfers
> | > | Date: Tue, 10 May 2005 15:14:04 -0700
> | > | Lines: 31
> | > | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path:
> TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55748
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | I have used this script generator quite successfully to generate
> transfer
> | > | login scripts with encrypted passwords. However, on one server its
> not
> | > | working. I thought it could be collation issue on the machine where
> the
> | > | script is generated. So, I remotely generated on the source server
> and
> | > | applied it on the destination server (both case insensitive sort
> order on
> | > SQL
> | > | 2K). However, its not working.
> | > |
> | > | Anyone any idea?
> | > |
> | > | SCRIPT
> | > | =====
> | > |
> | > | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
> | > password),
> | > | ', @.encryptopt = ''skip_encryption'''
> | > | from syslogins
> | > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | > | 'repl_subscriber')
> | > | order by name
> | > |
> | > | SELECT 'EXEC sp_change_users_login ''Report'''
> | > |
> | > | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
> | > '''+name+''''
> | > | from syslogins
> | > | where name not in ('sa', 'BUILTIN\Administrators', 'repl_publisher',
> | > | 'repl_subscriber')
> | > | order by name
> | > |
> | > |
> | > | --
> | > | Regards,
> | > | MZeeshan
> | > |
> | >
> | >
> |
>
|||Hello MZeeshan,
Welcome! :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
| Thread-Topic: Login Transfers
| thread-index: AcVW+D9xud2lQItISX2USN/hHCRtBQ==
| X-WBNR-Posting-Host: 208.250.29.8
| From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
<DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
<CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
<Lpd1LIrVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Login Transfers
| Date: Thu, 12 May 2005 06:41:06 -0700
| Lines: 174
| Message-ID: <68F79652-11A0-4ABB-9015-1BB3D5924B85@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55945
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Peter,
|
| I did delete the logins before transferring.
|
| But, I would like to thank you as the link you provided earlier to create
| those stored procs in 'master' database did indeed helped me in
transferring
| the logins to destination server.
|
| Thank you!
|
| --
| Regards,
| MZeeshan
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello MZeeshan,
| >
| > I think the issue can occur if the logins you want to transfe has
already
| > existed in the destination database.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner 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.
| >
| >
| >
| >
| > --
| > | Thread-Topic: Login Transfers
| > | thread-index: AcVWHVT9ma0LUaspRJuQWeBKhalJyw==
| > | X-WBNR-Posting-Host: 67.167.85.152
| > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| > | References: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| > <DIIRP2eVFHA.3336@.TK2MSFTNGXA01.phx.gbl>
| > | Subject: RE: Login Transfers
| > | Date: Wed, 11 May 2005 04:34:03 -0700
| > | Lines: 97
| > | Message-ID: <CEDCC44D-ED7A-44D0-A63C-6F4311CCE966@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:55812
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | When I tried logging in the password from source server didn't work
on
| > | destination.
| > |
| > | --
| > | Regards,
| > | MZeeshan
| > |
| > |
| > | "Peter Yang [MSFT]" wrote:
| > |
| > | > Hello MZeeshan,
| > | >
| > | > What is the error message you encountered?
| > | >
| > | > Also, I think the following article shall be helpful
| > | >
| > | > 246133 HOW TO: Transfer Logins and Passwords Between Instances of
SQL
| > Server
| > | > http://support.microsoft.com/?id=246133
| > | >
| > | > Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner 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.
| > | >
| > | >
| > | >
| > | >
| > | > --
| > | > | Thread-Topic: Login Transfers
| > | > | thread-index: AcVVrZN1DsG5iPFdTbmG7jAmcjowmQ==
| > | > | X-WBNR-Posting-Host: 208.250.29.8
| > | > | From: "=?Utf-8?B?TVplZXNoYW4=?=" <mzeeshan@.community.nospam>
| > | > | Subject: Login Transfers
| > | > | Date: Tue, 10 May 2005 15:14:04 -0700
| > | > | Lines: 31
| > | > | Message-ID: <B9EA36FE-4151-41E7-A409-A7755F1F26F3@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | X-Newsreader: Microsoft CDO for Windows 2000
| > | > | Content-Class: urn:content-classes:message
| > | > | Importance: normal
| > | > | Priority: normal
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | > | Newsgroups: microsoft.public.sqlserver.server
| > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | > | Path:
| > TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
| > | > | Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.server:55748
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | > |
| > | > | I have used this script generator quite successfully to generate
| > transfer
| > | > | login scripts with encrypted passwords. However, on one server
its
| > not
| > | > | working. I thought it could be collation issue on the machine
where
| > the
| > | > | script is generated. So, I remotely generated on the source
server
| > and
| > | > | applied it on the destination server (both case insensitive sort
| > order on
| > | > SQL
| > | > | 2K). However, its not working.
| > | > |
| > | > | Anyone any idea?
| > | > |
| > | > | SCRIPT
| > | > | =====
| > | > |
| > | > | select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY(32),
| > | > password),
| > | > | ', @.encryptopt = ''skip_encryption'''
| > | > | from syslogins
| > | > | where name not in ('sa', 'BUILTIN\Administrators',
'repl_publisher',
| > | > | 'repl_subscriber')
| > | > | order by name
| > | > |
| > | > | SELECT 'EXEC sp_change_users_login ''Report'''
| > | > |
| > | > | SELECT 'EXEC sp_change_users_login ''Update_One'', '''+name+''',
| > | > '''+name+''''
| > | > | from syslogins
| > | > | where name not in ('sa', 'BUILTIN\Administrators',
'repl_publisher',
| > | > | 'repl_subscriber')
| > | > | order by name
| > | > |
| > | > |
| > | > | --
| > | > | Regards,
| > | > | MZeeshan
| > | > |
| > | >
| > | >
| > |
| >
| >
|