April 21, 2009 at 3:30 pm
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
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
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
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
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
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
Awesome. Thanks again, Flo. 😀
April 22, 2009 at 12:42 pm
I'm glad I could help 🙂
April 22, 2009 at 4:20 pm
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
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
Nevermind...I misread the post.
April 23, 2009 at 10:42 am
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
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
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
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