locked
Can OPENROWSET(BULK...) be used to connect to a remote sql server database? RRS feed

  • Question

  • 1-) If there is a syntax to use openrowset bulk to connect to a remote sql server (a different instance on remote server to bring data bulk into current server, if yes what is it?

    2-) And if there is would that be more efficient than regular openrowset. How would it compare to BULK INSERT / bcp / SSIS in performance?

    Thank you

    I did not see the syntax in msdn documentation for openrowset

    SELECT * FROM OPENROWSET(BULK...)

    http://msdn.microsoft.com/en-us/library/ms175915.aspx

    Traditional openrowset call.

    SELECT * FROM OPENROWSET('SQLNCLI', 'Server=SERVERNAME;Trusted_Connection=yes', 'SELECT TOP 10 * FROM SOMETABLE') AS a



    Gokhan Varol

    • Moved by Tom Phillips Tuesday, May 14, 2013 7:38 PM TSQL question
    Tuesday, May 14, 2013 7:06 PM

Answers

  • No.  OPENROWSET .. BULK is for reading files, not SQL data.  The file can be located on a remote file share.  But it must be a file.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Tuesday, May 14, 2013 7:47 PM
    • Marked as answer by Allen Li - MSFT Tuesday, May 21, 2013 8:14 AM
    Tuesday, May 14, 2013 7:33 PM
  • You have to use OPENDATASOURCE instead this way:

    SELECT * FROM OPENROWSET('SQLNCLI', 'DRIVER={SQL Server};SERVER=YourServer;UID=UserID;PWD=Password', 'select * from sys.sysobjects')


    m@te

    • Proposed as answer by farkas.mate Thursday, May 16, 2013 3:13 PM
    • Marked as answer by Allen Li - MSFT Tuesday, May 21, 2013 8:14 AM
    Wednesday, May 15, 2013 1:10 PM

All replies

  • No.  OPENROWSET .. BULK is for reading files, not SQL data.  The file can be located on a remote file share.  But it must be a file.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Tuesday, May 14, 2013 7:47 PM
    • Marked as answer by Allen Li - MSFT Tuesday, May 21, 2013 8:14 AM
    Tuesday, May 14, 2013 7:33 PM
  • You have to use OPENDATASOURCE instead this way:

    SELECT * FROM OPENROWSET('SQLNCLI', 'DRIVER={SQL Server};SERVER=YourServer;UID=UserID;PWD=Password', 'select * from sys.sysobjects')


    m@te

    • Proposed as answer by farkas.mate Thursday, May 16, 2013 3:13 PM
    • Marked as answer by Allen Li - MSFT Tuesday, May 21, 2013 8:14 AM
    Wednesday, May 15, 2013 1:10 PM