April 23, 2009 at 4:02 am
Hi All,
I have multiple daily requests where I need to fetch data using a same set of query on around 30 servers. To simplify it a bit I have setup linked servers for all 30 servers on a single server but I still need to replace the server name & database name every time to fetch the output & this eats a lot of my time. I was wondering if I can loop through the servers & fetch data set from each server & put it in a temp table & finally extract the collated data in one shot. Also each server has either db1 or db2 only.
To summarize it here is my problem set:-
linkserver array ={linkserver1 , linkserver2, linkserver3,.....,linserver30}
While not end of linkserver array
if it linkserver contains db1 then
fetch data from db1 of this server & put it in temp table
else
fetch data from db2 of this server & put it in temp table
endif
loop
display data from temp table
I hope I have made it readable :ermm:
Thanks,
RP
April 23, 2009 at 7:43 am
One option is to get SQL Server 2008 developer edition and use the ability in SSMS to run the same query against multiple servers at once. See this article.
Something like this would work:
DECLARE @server_name sysname,
@sql NVARCHAR(4000),
@select NVARCHAR(500),
@from NVARCHAR(100)
SET @select = N'Select * from '
SET @from = N'.schema_name.table_name Where criteria'
DECLARE c_Servers CURSOR FAST_FORWARD FOR
SELECT
NAME
FROM
sys.servers AS S
WHERE
S.is_linked = 1
OPEN c_Servers
FETCH NEXT FROM c_Servers INTO
@server_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @select + @server_name + @from
EXEC(@sql)
FETCH NEXT FROM c_Servers INTO
@server_name
END
CLOSE c_Servers
DEALLOCATE c_Servers
This is one of the few instances where I'd use a cursor. Make sure to watch for R. Barry Young's series on eliminating cursors though because he may provide a better way.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 23, 2009 at 10:01 am
wouldn't it be four-part naming convention?
i.e. SET @from = N'.database_name.schema_name.table_name Where criteria'
-- You can't be late until you show up.
April 23, 2009 at 11:34 am
tosscrosby (4/23/2009)
wouldn't it be four-part naming convention?i.e. SET @from = N'.database_name.schema_name.table_name Where criteria'
Yup. Nice catch. I totally braincramped on that one.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 23, 2009 at 12:16 pm
Jack Corbett (4/23/2009)
tosscrosby (4/23/2009)
wouldn't it be four-part naming convention?i.e. SET @from = N'.database_name.schema_name.table_name Where criteria'
Yup. Nice catch. I totally braincramped on that one.
First time for everything I guess! 😉
-- You can't be late until you show up.
April 23, 2009 at 12:56 pm
Instead of doing this through a SQL query run through SSMS - I would use a combination of powershell and sqlcmd.
Example:
PS> $servers = get-content c:\temp\servers.txt
PS> $servers | % {sqlcmd -S $_ -E -Q "SELECT ... FROM database.schema.object WHERE criteria"}
You should be able to expand on this very easily.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 1:02 pm
Oh no! Not Powershell! I have enough trouble with T-SQL. 😀
Jeffrey,
Seriously, what have you used for resources to learn powershell?
I honestly think the best way to something like this is to shell out the $ (I think it's only like $50 U.S.) for SQL Server 2008 developer edition and use SSMS 2008 to run the multi-server query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 23, 2009 at 1:12 pm
Resources to learn powershell - well, let's see...there's that thing called google...that thing called my time...and I just sort of put them together.
Yep, nothing but my time (well, actually the companies - but we aren't telling them that).
The problem with using SSMS and a multi-server query is how do you get the results into a single location for reporting? You can't use an insert into so you would have to export the results from SSMS somehow and then process the results.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 1:23 pm
Oh man, I was hoping for some kind of, this book was great I highly recommend it answer. Although I'm with Brent Ozar on this one. Not super interested in learning Powershell beyond understanding it when I see it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 23, 2009 at 1:32 pm
Jack Corbett (4/23/2009)
Oh man, I was hoping for some kind of, this book was great I highly recommend it answer. Although I'm with Brent Ozar on this one. Not super interested in learning Powershell beyond understanding it when I see it.
You know - that's about all I do know. Enough to recognize what someone else is doing and modify it so it does what I need it to.
For the most part, I use it to query multiple servers and extract statistics to be loaded into a central repository. For example, I pull back the backup history for our systems - disk space usage and index space usage (for trending), drive space usage, etc...
Schedule the script to run daily and we can now notify when a backup is missed or a database needs to be extended, etc...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 1:51 pm
Cool.
I actually did something several years ago for 7/2000 that used DMO to do that in ASP.NET 1.1.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 24, 2009 at 2:19 am
Thanks everyone for the replies 🙂
Wow, that is nearly what I need. Special thanks to you.
Trying it now & just trying to figure out a way to collate the whole output in a single table.
Also, the whole environment is on SQL Server 2005 (minus the BI tools), so can't really use BI.
and I am sorry that I don't know anything about powershell :crying:
May 7, 2009 at 4:14 am
Hi All,
I have tried and finally included temp tables to collate all the data and also added the functionality to check whether the server contains Database1 or Database2. Here is how the code goes:
DECLARE @server_name sysname,
@sql NVARCHAR(4000),
@select NVARCHAR(500),
@DB_Name NVARCHAR(50),
@from NVARCHAR(100)
-- Create temporary tables with same schema as per my output
Select top 0 * into #data from vcomputer
Select top 0 * into #DB_Check1 from msdb.sys.databases
SET @select = N'Select top 5 * from '
SET @from = N'.dbo.my_Table where Name like ''FL%'''
SET @DB_name = N'.My_DB1'
DECLARE c_Servers CURSOR FAST_FORWARD FOR
SELECT
NAME
FROM
sys.servers AS S
WHERE
S.is_linked = 1
OPEN c_Servers
FETCH NEXT FROM c_Servers INTO
@server_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- To check Whether Server has MY_DB1 or My_DB2
Insert into #DB_Check EXEC('select * from ' +@server_name+ '.msdb.sys.databases where name =''MY_DB1''')
-- Set the porper database to fetch the result
If not exists( select * from #DB_Check)
Set @DB_Name=N'.MY_DB1'
else
Set @DB_Name=N'.MY_DB2'
-- To empty the table for next linked server DB check
Truncate table #DB_Check1
SET @sql = @select + @server_name + @DB_Name + @from
Insert into #data EXEC(@sql)
FETCH NEXT FROM c_Servers INTO
@server_name
END
CLOSE c_Servers
DEALLOCATE c_Servers
The above code works and gives me the required output. However, I was trying to simplify it a bit.
I was trying to replace the below code block to
-- To check Whether Server has MY_DB1 or My_DB2
Insert into #DB_Check EXEC('select * from ' +@server_name+ '.msdb.sys.databases where name =''MY_DB1''')
-- Set the porper database to fetch the result
If not exists( select * from #DB_Check)
Set @DB_Name=N'.MY_DB1'
else
Set @DB_Name=N'.MY_DB2'
to something as below, but it is throwing Syntax error near EXEC
If Exists (EXEC('select * from ' +@server_name+ '.msdb.sys.databases where name =''MY_DB1'''))
Set @DB_Name=N'.MY_DB1'
else
Set @DB_Name=N'.MY_DB2'
Let me know if this can be simplified.
PS: I do not know how to post the code, the way Jack posted in this thread. I will be happy if someone can guide me on that too.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply