SQL Server Performance

Hoq to query table of different server?

Discussion in 'SQL Server 2005 General Developer Questions' started by rupeshpatel, Aug 31, 2007.

  1. rupeshpatel New Member

    I came across a situation in which i have to create a job on one server which will fetch data from the database of different server and bring that data on to this local server!!!
    can somebody help me out in this?
    local server is SQL_BCDB1, on which i have to create job, and this job has to bring data from server SQL_BCDB2.
    is there anything specific in sql server that i can use with select statement so that i can select data from the different server?
    thanks with regards,
  2. satya Moderator

    Why not create a DTS package to import data from SQL1 to SQL2 in thsi case, same package can be scheduled to execute whenever you want to do.
  3. rupeshpatel New Member

    i dont know, why but our manager doesnt want to do this using dts package... can we extract data using select statement from the other server???
    Thanks for the response...
    Please guide..
  4. ndinakar Member

    If the servers are linked then you can do :INSERT
    INTO TblLocal <columns>SELECT
    Server2.Database.Dbo.RemoteTable WHERE
  5. rupeshpatel New Member

    no, servers are not linked and not going to link. is there any way to query different server data without that? this is going to be one time process...
    Thanks for your response..
    Please advise..
  6. Adriaan New Member

    Use the OPENQUERY(....) syntax. You may need to use dynamic SQL to make it flexible (in SQL 7.0 and 2000 you have to, perhaps 2005 allows you to use variables instead of literal names).
    --- oops, you mention it's a one time process, so you can simply write out the parameters, with no need for flexibility or dynamic SQL.
  7. rupeshpatel New Member

    Thanks Adriaan,
    well, thing is,
    i need to run bulk copy on the remote server from local server!!!.. can i do that? can i execute query on the remote server from the local server? and this will be one time process, and should be without dts package...
    Please guide..
  8. rupeshpatel New Member

    more on that,
    can i use openrowset to fetch data from Excel Source?
    can i write query like,
    SELECT a.*
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    '\data-ftp-01AVC estartinforjp.xls', 'select * from testartinforjp')
    AS a
    I want to extract data from the excel file(testartinforjp.xls) which is on remote server..
  9. rupeshpatel New Member

    I am still hanging on this one... can somebody pls help me out..
    I am trying to extract data from the excel file which is on the remote server. I don't want to create linked server. But I want to do this using opendatasource or openrowset.
    Here is what I am getting.

    SELECT * FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=ny-tmp-02;User ID=sa;Password=aaaaaaa').pubs.dbo.sales WORKS...[:D]
    SELECT a.* from OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=ny-tmp-02;UID=sa;PWD=aaaaaaa', 'SELECT * FROM pubs.dbo.sales')as a WORKS...[:D]
    SELECT * INTO rjp_temp_t3 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database= \ny-temp-01abTempsalesdata.xls', 'SELECT * FROM [salesdata$]') DOESN'T WORK...[:(]
    SELECT * INTO rjp_temp_t3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source= \ny-temp-01vgTempsalesdata.xls;Extended Properties=Excel 8.0')...[salesdata$] DOESN'T WORK...[:(]

    I want to run last two queries..
    Please help.
    Thanks with regards,

  10. Adriaan New Member

    You have to do an OPENQUERY towards the other server, in which you let the other server do an OPENDATASOURCE.
  11. rupeshpatel New Member

    But, Why should I use Openquery, if I am not going to create linked server!!!
    First query works fine without using openquery on remote server...

  12. Adriaan New Member

    From what you describe, you have an Excel file on a remote server, and you cannot access this Excel file directly. Correct?
    In that case you have to let the remote server open the Excel file, and show the results to you. To make a remote server show results for a query that it executes by itself, to you, you issue an OPENQUERY command - or perhaps OPENDATASOURCE in case you need to connect ad-hoc.
    The remote server can open the Excel file the same way that you would open an Excel file locally, with the OPENDATASOURCE command.
    So you would be calling an OPENQUERY or OPENDATASOURCE command directed at the remote server, and the query statement that you ask to be executed includes the OPENDATASOURCE command that will open the Excel sheet.
  13. rupeshpatel New Member

    Thanks Adriaan for your reply,
    BUT, first thing to notice is, I can access this Excel file. This file is on remote server but that directory is shared directory so I can go to that directory using the same path that given in the sql. So I have access to that directory and Excel file.
    What should be done then?
    Let me print error as well, here is what I am getting when I execute this sql.
    Here it is...
    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

  14. Adriaan New Member

    Accessing a file on disk from within SQL Server means that the local Windows account under which the SQL services are running, must have sufficient permissions to open the file within the Windows domain.
    Other than that, can you start Excel and open the file directly? There might be an error message.
  15. satya Moderator

    That error relates to MDAC issues: as MDAC 2.6 onwards doesn't include Access ODBC Drivers and you need to install MDAC 2.5 over the top of MDAC 2.8, and all should be

Share This Page