Going after repeat offenders in SP

Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIAIs it that the rows generated by this SELECT are to be inserted into
multiple tables? If so, it would make sense to populate a temp table and
then use it. I'm curious as to why these same rows have to be inserted into
multiple tables. I sit possible to use one table and reference it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:uIH3csTmGHA.492@.TK2MSFTNGP05.phx.gbl...
Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIA|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4con5TmGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Is it that the rows generated by this SELECT are to be inserted into
> multiple tables? If so, it would make sense to populate a temp table and
> then use it. I'm curious as to why these same rows have to be inserted
> into
> multiple tables. I sit possible to use one table and reference it?
Thanks for the reply.
It will insert different data table depending, but it's initial where is the
key not in (select key from other table)
So I will refactor this and other sp's that follow the same sense of
nonsense. Actually it's a refactor of an app that only needed to do this
once, but the new and better data layout needs the same call a few more
times.

Globally Change SQL Svc Account passwords

Hello. Is there any batch method to do a mass change of
multiple server's SQL Server Service account and/or
passwords? Just looking for any tips on changing MANY SQL
boxes' Service startup accounts. Is there any batch
commands to set a service account logon? THanks, BruceBelow is a VBScript to change service passwords. It will change the
password for all of the services on the specified servers running under
the account.
'begin script
Option Explicit
Dim oWin32_Services, oWin32_Service
Dim ServerName, StartName, NewPassword, Messages
' *** specify Windows account and new password here ***
StartName = "Domain\Account" 'Windows service account name
NewPassword = "NewAccountPassword"
' **************************
' *** specify server list here ***
Call ChangeServerServicePasswords("ServerName1", _
StartName, _
NewPassword, _
Messages)
Call ChangeServerServicePasswords("ServerName2", _
StartName, _
NewPassword, _
Messages)
' **************************
WScript.Echo Messages
Sub ChangeServerServicePasswords(ServerName, _
StartName, _
NewPassword, _
Messages)
Dim SQL
'select all services running under this account
SQL = "SELECT * FROM Win32_Service WHERE StartName = '" & _
Replace(StartName, "\", "\\") & "'"
Set oWin32_Services =GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
ServerName & _
"/root/cimv2").ExecQuery(SQL, , 48)
For Each oWin32_Service In oWin32_Services
Call ChangeServicePassword(oWin32_Service, _
NewPassword, _
Messages)
Next
End Sub
Sub ChangeServicePassword(oWin32_Service, _
NewPassword, _
Messages)
Dim intResult
intResult = oWin32_Service.Change(,,,,,,,NewPassword)
If intResult = 0 Then
Messages = Messages & _
oWin32_Service.SystemName & " " & _
oWin32_Service.Caption & _
" service account password changed for account " & _
oWin32_Service.StartName & vbcrlf
Else
Messages = Messages & _
oWin32_Service.SystemName & " " & _
oWin32_Service.Caption & _
" service account password change failed for account " & _
oWin32_Service.StartName & _
". Win32_Service.Change result is " & _
CStr(intResult) & vbcrlf
End If
End Sub
'end scrpt
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:056a01c3addf$98198f30$a001280a@.phx.gbl...
> Hello. Is there any batch method to do a mass change of
> multiple server's SQL Server Service account and/or
> passwords? Just looking for any tips on changing MANY SQL
> boxes' Service startup accounts. Is there any batch
> commands to set a service account logon? THanks, Bruce|||Maybe if you code something through WMI or something
similar, but there is no way I know of.
And if you're on a cluster, you have to do it through
Enterprise Manager anyway, so it wouldn't take care of
failover clustering installations of SQL Server.
>--Original Message--
>Hello. Is there any batch method to do a mass change of
>multiple server's SQL Server Service account and/or
>passwords? Just looking for any tips on changing MANY
SQL
>boxes' Service startup accounts. Is there any batch
>commands to set a service account logon? THanks, Bruce
>.
>|||wow... Great stuff Dan, thanks! Bruce
>--Original Message--
>Below is a VBScript to change service passwords. It will
change the
>password for all of the services on the specified servers
running under
>the account.
>
>'begin script
>Option Explicit
>Dim oWin32_Services, oWin32_Service
>Dim ServerName, StartName, NewPassword, Messages
>' *** specify Windows account and new password here ***
>StartName = "Domain\Account" 'Windows service account
name
>NewPassword = "NewAccountPassword"
>' **************************
>' *** specify server list here ***
>Call ChangeServerServicePasswords("ServerName1", _
> StartName, _
> NewPassword, _
> Messages)
>Call ChangeServerServicePasswords("ServerName2", _
> StartName, _
> NewPassword, _
> Messages)
>' **************************
>WScript.Echo Messages
>Sub ChangeServerServicePasswords(ServerName, _
> StartName, _
> NewPassword, _
> Messages)
> Dim SQL
> 'select all services running under this account
> SQL = "SELECT * FROM Win32_Service WHERE StartName
= '" & _
> Replace(StartName, "\", "\\") & "'"
> Set oWin32_Services =>GetObject("winmgmts:{impersonationLevel=impersonate}!//"
& _
> ServerName & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_Service In oWin32_Services
> Call ChangeServicePassword(oWin32_Service, _
> NewPassword, _
> Messages)
> Next
>End Sub
>Sub ChangeServicePassword(oWin32_Service, _
> NewPassword, _
> Messages)
> Dim intResult
> intResult = oWin32_Service.Change(,,,,,,,NewPassword)
> If intResult = 0 Then
> Messages = Messages & _
> oWin32_Service.SystemName & " " & _
> oWin32_Service.Caption & _
> " service account password changed for
account " & _
> oWin32_Service.StartName & vbcrlf
> Else
> Messages = Messages & _
> oWin32_Service.SystemName & " " & _
> oWin32_Service.Caption & _
> " service account password change failed for
account " & _
> oWin32_Service.StartName & _
> ". Win32_Service.Change result is " & _
> CStr(intResult) & vbcrlf
> End If
>End Sub
>'end scrpt
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:056a01c3addf$98198f30$a001280a@.phx.gbl...
>> Hello. Is there any batch method to do a mass change of
>> multiple server's SQL Server Service account and/or
>> passwords? Just looking for any tips on changing MANY
SQL
>> boxes' Service startup accounts. Is there any batch
>> commands to set a service account logon? THanks, Bruce
>
>.
>