To catch everyone up that is not in the KNOW... I no longer work for a testing company in Minnesota. I now work for a consulting company. This company has a GREAT reputation in the industry, and is a Microsoft GOLD Level partner. I am currently working at one of their clients in the database area. This is by far not my specialty, however I do know my way around a SQL Server!
Anyway, I have been working on a way to dynamically call across database servers to extract information that is needed for server health reporting. The issue that this client faces; not any different than most; is that there are ALOT of servers on their network, and more and more popup every day. Not to mention that scheduled jobs change!
I thought in the beginning; Linked Servers! That's the ticket.
That didn't work, as most of them are not linked! :(
I started looking at some of the commands that SQL 2k gave us; OpenDataSource and OpenRowSource. I thought; Great, I'll plant variables in this command through a cursor and everything will be fine. This also didn't work, since the two before mentioned commands do not work with variables.
After asking around, and beating my head into many hard objects, I came up with the following code...
set @connect = 'server=' + @server + ';trusted_connection=yes'
select @command = 'insert into {report_table} select * from openrowset(''SQLOLEDB'', ''' + @connect + ''',''{database}.dbo.{procedure}')'
execute sp_executesql @command
This works like a charm.. Oh, incase your SQL boxes will not support trusted connections; you can modify the @connect variable to the following.
set @connect = 'server=' + @server + ';UId=' + @uid + ';pwd=' + @pass
And so the story goes, yet another day; another 50 lumps on my skull!
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5
Tags:
Categories:
SQL Server