using "use database" and openrowset

Question

using "use database" and openrowset

usenet

SSC Eights!

Points: 809
More actions
May 17, 2010 at 1:05 pm

#235180

Hi all,
I am beating my head around this and can't figure out how to do it.
What I am trying is to get information via Openrowset from a remote SQL Server (either 2005 or 2008).
This is my code:
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=TheServer;Trusted_Connection=yes;',
'SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.index_type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
ORDER BY ps.OBJECT_ID') AS a;
This runs fine, as long as I am interested in the index information from the master database.
But I cannot bring it to return me information for a user database. Obviously, I cannot do a
Use <MyDB>
Go
Select..
since the first statement returns no rows.
Is there anyone with a solution?
I tried doing it via linked servers as well and a select from server.database.schema etc, but got an error returned stating that the server is not configured for rpc 🙁
Any help about this would be really appreciated!
Thanks in advance for your time

Viewing 5 posts - 1 through 4 (of 4 total)

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

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 1

instead of using use dbname command use database name as prefix in select query like dbname.sys.dm_db_index_physical_stats, hope this will help you.

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

usenet SSC Eights! Points: 809 More actions · Answer 2

yes, thanks. that solved the immediate problem - I was slapping my head tonight when i realized that i just didn't think about this solution 🙂

But the more general problem was not so easy to fix. it just seems impossible to do a multi command statement through openquery. The only solution I found in the end was to set it up as a linked server and then I could issue multiple statements easily. Thought I would get around the linkedservers, but well 🙂

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 3

You can put your multiple statements in a stored procedure and then call the fully qualified stored procedure from your OPENROWSET statement.

SELECT * FROM OPENROWSET('SQLNCLI','Server=TheServer;Trusted_Connection=yes;',

'EXEC Databasename.dbo.Storedprocname')

usenet SSC Eights! Points: 809 More actions · Answer 4

Hi Steve,

yes. But that requires to create them on the remote server. I ended up using linkedServers and local stored procedures to encapsulate the complex logic. The sample I posted was only that, a sample.

I am required to query a lot of data from the remote server, and for that a linked server was the only alternative I could find..

Thanks for all your help!

steve-893342 (5/19/2010)
You can put your multiple statements in a stored procedure and then call the fully qualified stored procedure from your OPENROWSET statement.
SELECT * FROM OPENROWSET('SQLNCLI','Server=TheServer;Trusted_Connection=yes;',
'EXEC Databasename.dbo.Storedprocname')