Post reply

Need help creating a loop

  • April 21, 2009 at 3:30 pm

    #155590

    Hi Guys,

    you all have been incredibly helpful thusfar, so I'm hoping you can help me with this one too. I have a server with 15 databases on it, and I want to make it so I dont have to execute the script manually against every database. I've tried creating a temp table of Db names, and a loop to cycle through the Db names and execute the script against each, but I'm failing miserably.

    If I had the following DB names, how can I incorporate the script below so it will cycle through them all?

    MMed_ACSD

    MMed_Infinity

    MMed_AllData

    MMed_Genesis

    MMed_Aetna

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @start_dt DATETIME, @end_dt DATETIME

    SET @start_dt = '2008-10-01 00:00:00.000'

    SET @end_dt = '2008-12-31 23:59:59.997'

    SELECT DISTINCT

    SUBSTRING(db_Name(),CHARINDEX('_', db_Name())+ 1, LEN(db_name())) [Client],

    month (bh.createdate) [MONTH],

    count (DISTINCT bh.billidno)[BILLS],

    sum(bh.nolines)[LINES],

    CASEWHEN c.cv_code IN ('MP', 'PI')

    THEN 'First Party'

    ELSE 'Third Party'

    END [TYPE]

    FROM claims c

    JOIN prf_ofc o ON o.officeID = c.officeIndex

    JOIN prf_comp co ON co.companyID = c.companyID

    JOIN claim cl ON cl.claimIDNo = c.claimIDNo

    JOIN cmthdr ch ON ch.cmtIDNo = cl.cmtIDNo

    JOIN billhdr bh ON bh.cmt_hdr_IDNo = ch.cmt_hdr_IDNo

    join secusers s on bh.whoCreate = s.LoginName

    join secUser_RightGroups srg on s.userid = srg.userid

    join secrightgroups sr on srg.rightgroupid = sr.rightgroupid

    WHERE bh.createDate BETWEEN @start_dt AND @end_dt

    AND (sr.rightgroupname like ('Service Center') or sr.rightgroupname like ('Service Center Processor'))

    group by month (bh.createdate), CASEWHEN c.cv_code IN ('MP', 'PI')

    THEN 'First Party'

    ELSE 'Third Party'

    END

    Thanks in advance!

  • April 21, 2009 at 3:38 pm

    #981843

    Hi

    You can use a dynamic SQL statement:

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + ' PRINT ''Hello from: '' + DB_NAME()' + CHAR(10)

    FROM sys.databases

    WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')

    EXECUTE (@sql)

    Greets

    Flo

  • April 21, 2009 at 3:54 pm

    #981852

    Thanks Flo! How do I incorporate that into the code I posted above? I've been messing with what you posted but I can seem to get it to work.

  • April 21, 2009 at 4:06 pm

    #981858

    Just mask your apostrophes with double apostrophes and replace the "PRINT ''Hello from: '' + DB_NAME()" of my example. I don't know a better way for this:

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @start_dt DATETIME, @end_dt DATETIME

    SET @start_dt = ''2008-10-01 00:00:00.000''

    SET @end_dt = ''2008-12-31 23:59:59.997''

    SELECT DISTINCT

    SUBSTRING(db_Name(),CHARINDEX(''_'', db_Name())+ 1, LEN(db_name())) [Client],

    month (bh.createdate) [MONTH],

    count (DISTINCT bh.billidno)[BILLS],

    sum(bh.nolines)[LINES],

    CASE WHEN c.cv_code IN (''MP'', ''PI'')

    THEN ''First Party''

    ELSE ''Third Party''

    END [TYPE]

    FROM claims c

    JOIN prf_ofc o ON o.officeID = c.officeIndex

    JOIN prf_comp co ON co.companyID = c.companyID

    JOIN claim cl ON cl.claimIDNo = c.claimIDNo

    JOIN cmthdr ch ON ch.cmtIDNo = cl.cmtIDNo

    JOIN billhdr bh ON bh.cmt_hdr_IDNo = ch.cmt_hdr_IDNo

    join secusers s on bh.whoCreate = s.LoginName

    join secUser_RightGroups srg on s.userid = srg.userid

    join secrightgroups sr on srg.rightgroupid = sr.rightgroupid

    WHERE bh.createDate BETWEEN @start_dt AND @end_dt

    AND (sr.rightgroupname like (''Service Center'') or sr.rightgroupname like (''Service Center Processor''))

    group by month (bh.createdate), CASE WHEN c.cv_code IN (''MP'', ''PI'')

    THEN ''First Party''

    ELSE ''Third Party''

    END

    ' + CHAR(10)

    FROM sys.databases

    WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')

    EXECUTE (@sql)

    Greets

    Flo

  • April 22, 2009 at 11:43 am

    #982374

    Thanks, Flo! I got it working, but I cant use variables otherwise I get the following message

    "Variable names must be unique within a query batch or stored procedure"

    Any idea how I can make the variables work without throwing that error?

    Thanks again,

    Code

  • April 22, 2009 at 11:59 am

    #982387

    Hi Code

    Sorry, didn't notice the variable declarations...

    If the value of the variables is same for all databases, as in your sample, you can use sp_executesql and transfer the values from outside to inside. Remove your declarations within your string and declare them as parameter of the sp_executesql procedure.

    Here a little sample:

    DECLARE @OutsideIn DATETIME

    DECLARE @InsideOut DATETIME

    DECLARE @sql NVARCHAR(MAX)

    SET @OutsideIn = GETDATE()

    SET @sql = 'PRINT ''Outside In: '' + CONVERT(VARCHAR(30), @FromOutside)

    SET @ToOutside = GETDATE() - 1'

    EXECUTE master.sys.sp_executesql

    @sql,

    N'@FromOutside DATETIME, @ToOutside DATETIME OUTPUT',

    @FromOutside = @OutsideIn,

    @ToOutside = @InsideOut OUTPUT

    PRINT 'Outside In: ' + CONVERT(VARCHAR(30), @InsideOut)

    Greets

    Flo

  • April 22, 2009 at 12:32 pm

    #982418

    Awesome. Thanks again, Flo. 😀

  • April 22, 2009 at 12:42 pm

    #982427

    I'm glad I could help 🙂

  • April 22, 2009 at 4:20 pm

    #982596

    Flo,

    I'm having trouble integrating the code for the variables that you posted into my script. Is there any way you could show me how it would be done using the initial script I posted and that you updated for me? Thanks again for the help, and thanks for being patient with me, I'm still learning 🙂

  • April 23, 2009 at 3:35 am

    #982728

    Hi

    Try this:

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SET @start_dt = ''2008-10-01 00:00:00.000''

    SET @end_dt = ''2008-12-31 23:59:59.997''

    SELECT DISTINCT

    SUBSTRING(db_Name(),CHARINDEX(''_'', db_Name())+ 1, LEN(db_name())) [Client],

    month (bh.createdate) [MONTH],

    count (DISTINCT bh.billidno)[BILLS],

    sum(bh.nolines)[LINES],

    CASE WHEN c.cv_code IN (''MP'', ''PI'')

    THEN ''First Party''

    ELSE ''Third Party''

    END [TYPE]

    FROM claims c

    JOIN prf_ofc o ON o.officeID = c.officeIndex

    JOIN prf_comp co ON co.companyID = c.companyID

    JOIN claim cl ON cl.claimIDNo = c.claimIDNo

    JOIN cmthdr ch ON ch.cmtIDNo = cl.cmtIDNo

    JOIN billhdr bh ON bh.cmt_hdr_IDNo = ch.cmt_hdr_IDNo

    join secusers s on bh.whoCreate = s.LoginName

    join secUser_RightGroups srg on s.userid = srg.userid

    join secrightgroups sr on srg.rightgroupid = sr.rightgroupid

    WHERE bh.createDate BETWEEN @start_dt AND @end_dt

    AND (sr.rightgroupname like (''Service Center'') or sr.rightgroupname like (''Service Center Processor''))

    group by month (bh.createdate), CASE WHEN c.cv_code IN (''MP'', ''PI'')

    THEN ''First Party''

    ELSE ''Third Party''

    END

    ' + CHAR(10)

    FROM sys.databases

    WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')

    DECLARE @start_dt DATETIME, @end_dt DATETIME

    EXECUTE sp_executesql

    @sql,

    N'@start_dt DATETIME, @end_dt DATETIME',

    @start_dt = @start_dt,

    @end_dt = @end_dt

    Greets

    Flo

  • April 23, 2009 at 7:15 am

    #982851

    Nevermind...I misread the post.

  • April 23, 2009 at 10:42 am

    #983101

    Flo, I LOVE YOU MAN! If anyone ever says you're not cool, I want names!!

    THANKS! 😀

    Code

  • April 23, 2009 at 11:53 am

    #983160

    I've got a quick question about execution times. It seems that when I run the script that has the code for the loop, even if it's against just one database it runs for a really long time.

    This script runs in less than 1 second:

    USE Mmed_FL

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @start_dt DATETIME, @end_dt DATETIME

    SET @start_dt = '2009-01-01 00:00:00.000'

    SET @end_dt = '2009-03-31 23:59:59.997'

    SELECT SUBSTRING(db_Name(),CHARINDEX('_', db_Name())+ 1, LEN(db_name())) [Client],

    COUNT (DISTINCT RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName))) [Unique Users],

    count (distinct a.userID)[Unique Users Who Modified Bills],

    count (distinct ad_hoc.userID) [ad-hoc Report Users]

    --count (distinct sch.userID) [Scheduled Report users]

    FROM sec_users s

    JOIN auditLog b ON b.userID = s.userID

    LEFT JOIN (

    SELECT DISTINCT userID

    FROM auditLog

    WHERE logDate BETWEEN @start_dt AND @end_dt

    AND eventID IN (14,18,27,30)

    --14 = A bill Line Item was overridden by a user.

    --18 = A Bill was added.

    --27 = A Bill was revised.

    --30 = A Bill was reconsidered

    )a ON a.userID = s.userID

    LEFT JOIN (SELECT DISTINCT userID

    FROM user_reports

    WHERE runDateTime BETWEEN @start_dt AND @end_dt) ad_hoc ON ad_hoc.userID = s.userID

    --LEFT JOIN (SELECT DISTINCT userID

    --FROM rpt_scheduled

    --WHERE LastRunAt BETWEEN @start_dt AND @end_dt

    --AND classID IN (''cProcedureReport'',''cProcedureAnalysis'', ''cClaimDetail'')) sch ON sch.userID = s.userID

    WHERE b.logDate BETWEEN @start_dt AND @end_dt

    and s.loginName NOT LIKE '%TEST%'

    AND s.loginName NOT LIKE '%DPIEP%'

    AND s.loginName NOT LIKE '%Sentry%'

    AND RTRIM(LTRIM(s.firstName)) NOT LIKE 'Mitchell'

    AND RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName)) NOT LIKE '%Decision%Point%'

    AND RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName)) NOT LIKE '%Conversion%'

    AND RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName)) NOT LIKE '%Topaz%'

    AND s.userID NOT IN(SELECT userID FROM sec_user_rightGroups

    WHERE rightGroupID IN(SELECT rightGroupID

    FROM sec_rightGroups

    WHERE rightGroupName LIKE '%Mitchell%'))

    order by 1

    and this one that you helped me with takes 2 minutes and 20 seconds.

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = ISNULL(@sql, '') + 'USE ' + name + '

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SET @start_dt = ''2009-01-01 00:00:00.000''

    SET @end_dt = ''2009-03-31 23:59:59.997''

    SELECT SUBSTRING(db_Name(),CHARINDEX(''_'', db_Name())+ 1, LEN(db_name())) [Client],

    COUNT (DISTINCT RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName))) [Unique Users],

    count (distinct a.userID)[Unique Users Who Modified Bills],

    count (distinct ad_hoc.userID) [ad-hoc Report Users]

    --count (distinct sch.userID) [Scheduled Report users]

    FROM sec_users s

    JOIN auditLog b ON b.userID = s.userID

    LEFT JOIN (

    SELECT DISTINCT userID

    FROM auditLog

    WHERE logDate BETWEEN @start_dt AND @end_dt

    AND eventID IN (14,18,27,30)

    --14 = A bill Line Item was overridden by a user.

    --18 = A Bill was added.

    --27 = A Bill was revised.

    --30 = A Bill was reconsidered

    )a ON a.userID = s.userID

    LEFT JOIN (SELECT DISTINCT userID

    FROM user_reports

    WHERE runDateTime BETWEEN @start_dt AND @end_dt) ad_hoc ON ad_hoc.userID = s.userID

    --LEFT JOIN (SELECT DISTINCT userID

    --FROM rpt_scheduled

    --WHERE LastRunAt BETWEEN @start_dt AND @end_dt

    --AND classID IN (''cProcedureReport'',''cProcedureAnalysis'', ''cClaimDetail'')) sch ON sch.userID = s.userID

    WHERE b.logDate BETWEEN @start_dt AND @end_dt

    and s.loginName NOT LIKE ''%TEST%''

    AND s.loginName NOT LIKE ''%DPIEP%''

    AND s.loginName NOT LIKE ''%Sentry%''

    AND RTRIM(LTRIM(s.firstName)) NOT LIKE ''Mitchell''

    AND RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName)) NOT LIKE ''%Decision%Point%''

    AND RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName)) NOT LIKE ''%Conversion%''

    AND RTRIM(LTRIM(s.firstName)) + RTRIM(LTRIM(s.lastName)) NOT LIKE ''%Topaz%''

    AND s.userID NOT IN(SELECT userID FROM sec_user_rightGroups

    WHERE rightGroupID IN(SELECT rightGroupID

    FROM sec_rightGroups

    WHERE rightGroupName LIKE ''%Mitchell%''))

    order by 1

    ' + CHAR(10)

    FROM sys.databases

    WHERE name IN (

    'Mmed_FL'

    )

    DECLARE @start_dt DATETIME, @end_dt DATETIME

    EXECUTE sp_executesql

    @sql,

    N'@start_dt DATETIME, @end_dt DATETIME',

    @start_dt = @start_dt,

    @end_dt = @end_dt

    Any idea how I can optimize the script with the Db loop so it's execution time is lower?

    Thanks,

    Code

  • April 25, 2009 at 8:52 am

    #983959

    Code (4/23/2009)

    Flo, I LOVE YOU MAN! If anyone ever says you're not cool, I want names!!

    THANKS! 😀

    Code

    :hehe:

    ... the names and maybe the country 😀

    To your problem:

    How many databases do you have?

    Greets

    Flo

  • April 27, 2009 at 9:45 am

    #984448

    Florian Reischl (4/25/2009)

    Code (4/23/2009)

    Flo, I LOVE YOU MAN! If anyone ever says you're not cool, I want names!!

    THANKS! 😀

    Code

    :hehe:

    ... the names and maybe the country 😀

    To your problem:

    How many databases do you have?

    Greets

    Flo

    The server I'll be running this script on has 26 databases. As I said before even if I run this against just one DB, the execution time is very long in comparison to if I dont use the loop portion of the script.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy