Linked Server Queries using SP_MSforeachDB and OPENQUERY

Question

Post reply

Linked Server Queries using SP_MSforeachDB and OPENQUERY

lilywhites

SSCrazy

Points: 2732
More actions
August 16, 2011 at 7:38 am

#255665

Hello,
I know this is undocumented and will happily accept any alternatives....but am trying to do something like the following:
select * from OPENQUERY(servername,
'
exec sp_msforeachdb
''''SELECT @servername, getdate(), ''''?'''' , c.name [objname], b.* from [?].sys.dm_db_index_usage_stats b, [?].sys.objects c
where b.object_id=c.object_id''''
'
)
the select works with ms_foreachdb without openquery....and with openquery but without sp_msforeachdb.
When trying to use both together i get:
OLE DB provider "SQLNCLI10" for linked server "servername" returned message
"Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@P1'.
i think this has something to do with the temp tables or methods used in sp_msforeachdb but cannot seem to solve it. If anyone has an idea how to....or how to get these stats from remote servers and resolve the correct object names from each database i would be a very pleased boy!!
Much appreciated
LilyWhites
August 16, 2011 at 8:21 am

This was removed by the editor as SPAM
August 17, 2011 at 1:17 am

This was removed by the editor as SPAM
August 17, 2011 at 1:22 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

lilywhites SSCrazy Points: 2732 More actions · Answer 1

hello,

i have tried that a number of ways but keep getting the error:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '@P1'

i havent used this exec method before and dont have any variables to pass within the select statement outside of the foreachdb...tried using database name but same error as when using 0, servername, anything really.

using more than 1 variable gives the error:

OLE DB provider "SQLNCLI10" for linked server "servername" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7215, Level 17, State 1, Line 1

Could not execute statement on remote server servername'.

as the errors are consistent regardless of the variable content....i assume the method will not work....was this tested by anyone else???

thanks

lilywhites SSCrazy Points: 2732 More actions · Answer 2

thank you for these....i will test these as soon as possible 🙂

lilywhites SSCrazy Points: 2732 More actions · Answer 3

hi stewartc,

thank you very much for this....i have tested and all works!!

once thing....it was only collecting system database stats until granted extra permissions to all user databases. i will alter the linked server security to take this into account.

thanks again 😀

lilywhites