Post reply

using "use database" and openrowset

  • 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

  • May 18, 2010 at 5:21 am

    #1168714

    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

  • May 18, 2010 at 5:28 am

    #1168718

    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 🙂

  • May 19, 2010 at 5:03 am

    #1169326

    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')

  • May 19, 2010 at 5:10 am

    #1169328

    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')

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

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