February 1, 2016 at 10:45 am
EXEC msdb.dbo.sp_get_sqlagent_properties
How do I retune the servername along with the columns returned by the abover SP
i.e
Result should have select @@servername+ result of EXEC msdb.dbo.sp_get_sqlagent_properties in a single sql query.
February 1, 2016 at 12:32 pm
sqlnewbie17 (2/1/2016)
EXEC msdb.dbo.sp_get_sqlagent_propertiesHow do I retune the servername along with the columns returned by the abover SP
i.e
Result should have select @@servername+ result of EXEC msdb.dbo.sp_get_sqlagent_properties in a single sql query.
Do you plan on running this against multiple servers at once?
February 1, 2016 at 12:46 pm
Yes,that will be run against multiple servers as part of an SSIS package.
Thanks
February 1, 2016 at 12:49 pm
Well, I can't help you with the SSIS part of it. I was going to say that when you run something against multiple servers at once in SSMS, the server name is returned as a pseudo-column in the result set.
For SSIS, I'm going to have to bow out and let someone who knows SSIS help you.
February 1, 2016 at 2:44 pm
sqlnewbie17 (2/1/2016)
Yes,that will be run against multiple servers as part of an SSIS package.Thanks
Doing this in T-SQL will be a pain. Easier way will be to use a Derived Column in your SSIS Data Flow to add a server-name-column to the Pipeline after you return the results from the proc and before you store it in whatever downstream database you are storing this data into.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 2:50 pm
I don't think you can do this in a single statement. I might be wrong, but I suspect you could drop the results into a temp table, then query that with the servername pre-pended, i.e.
select @@servername, *
from #mytemp
and then use this in some other code.
February 1, 2016 at 3:01 pm
Steve Jones - SSC Editor (2/1/2016)
I don't think you can do this in a single statement. I might be wrong, but I suspect you could drop the results into a temp table, then query that with the servername pre-pended, i.e.
select @@servername, *from #mytemp
and then use this in some other code.
Exactly what I was thinking for T-SQL, but as soon as SSIS came up, I figured anything I come up with is going to be useless.
February 1, 2016 at 4:05 pm
Steve Jones - SSC Editor (2/1/2016)
I don't think you can do this in a single statement. I might be wrong, but I suspect you could drop the results into a temp table, then query that with the servername pre-pended, i.e.
select @@servername, *from #mytemp
and then use this in some other code.
I started to build the temp table required to do an INSERT...EXEC on the proc and capture the results. Anytime I do this work I find:
1) it would be pretty time-consuming to read the code of the proc to determine the resultset
2) a lot of the Agent procs already have an INSERT-EXEC in them and since you cannot nest INSERT...EXEC, a lot of times it's a dead-end
So I bailed and recommended an SSIS solution. Adding a Derived Column to the SSIS Pipeline is trivial and the OP already knows which server they are talking to, and presumably has this in a Variable, since it sounds like they are iterating over a list of servers to capture this info.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply