SELECT from [@ServerName].[msdb].[dbo].[sysjobs] 2

[JohnBates]

hello everyone,

I'm trying to select job info from a linked server. The linked server is configured correctly, but I get this error:
Msg 7202, Level 11, State 2, Line 21
Could not find server '@ServerName' in sysservers.

Can I select based on a Dynamic variable?

Thanks, John



INSERT INTO PNCLDBMONITOR.DatabasesInventory.dbo.DatabaseJobs
(ServerName,
JobName,
JobID,
JobDesc,
JobFrequency,
JobTime
)

SELECT
ServerName,
name,
job_id,
description,
null,
null

FROM [@ServerName].[msdb].[dbo].[sysjobs]

WHERE Enabled = 1

 

[SQLSister]

Not unless you use dynamic sql which is not usually a good choice.

"NOTHING is more important in a database than integrity." ESquared

 

[JohnBates]

Thanks SQLSister.

i do want to use dynamic SQL as in the example above so I can loop thru several server names (which have already been configured as linked servers)

Is there something else I need to do to be able to use this dynamic SQL stmt?

....FROM [@ServerName].[msdb].[dbo].[sysjobs]

John

 

[JohnBates]

..Is there not a way to do this?

I temporarily built 14 sets of code to select from 14 different servers. Not pretty but it worked.

Would much rather use my above code setting the variable server name each time thru.

Thanks, John

 

[SQLDenis]

>>..Is there not a way to do this?

yes, you have been given the answer by SQLSister

Use dynamic SQL in that case read

http://www.sommarskog.se/dynamic_sql.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[JohnBates]

Thanks, I got it.