September 8, 2008 at 7:10 am
Hey all,
This post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124137&SiteID=1) pretty much states what my problem is, and more or less reassures me that I can't do what I'm trying to do, so I guess I'm looking for some additional ways to do this ...
Basically, I have a wrapper procedure that you pass a servername into to call a procedure on any server in my environment. This is the important piece of the wrapper procedure:
INSERT INTO #JobInfo
EXEC('
EXEC OPENDATASOURCE(''SQLNCLI'',
''Data Source=' + @ServerName + ';Integrated Security=SSPI'')
.[admin].[dbo].[JobActivityMonitor]
')
It works against all servers in my environment, except my central server which runs all my reporting DBA procs (the one housing this procedure). Any suggestions? The reason I have to dump into a temp table is because I allow for multiple server names to be passed, so I cursor through each value passed in splitting the string based on a comma. The process is very solid and working very well ... just need to figure out how to get around this.
September 8, 2008 at 7:31 am
In your situation, it would probably be best to simply check the server name in your loop and if it is the local server, execute it locally instead of using OPENDATASOURCE. By your description, you only have this process in a single procedure so it should be pretty easy to code.
September 8, 2008 at 7:41 am
Yeah, a local insert into exec without opendatasource would work perfect... how did I not think about that. Jeez ... Mondays. Thanks man.
Now I gotta resolve a An INSERT EXEC statement cannot be nested from making this change ... fun fun.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply