Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17

    Question Unanswered: Openquery/openrowset

    Simple question:

    Are OPENQUERY and OPENROWSET an alternative to using a linkedserver?
    I am trying to use both, but am stopped because the db in question is not part of a linkedserver.

    I am currently trying to perform a query between two databases, but dont have permission to create a linked server.

    Any suggestions?

    Many thanks.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dataman2010 View Post
    Simple question:

    Are OPENQUERY and OPENROWSET an alternative to using a linkedserver?
    I am trying to use both, but am stopped because the db in question is not part of a linkedserver.

    I am currently trying to perform a query between two databases, but dont have permission to create a linked server.

    Any suggestions?

    Many thanks.
    IMHO, not a good idea. You will still need to have permissions on the other server (database), but there are issues with performance and bringing back data across the pipeline. I always re-write these queries to use linked servers. If you don't have permission to create a linked server then check with the dba.

    Big negative is sending userid and password in clear text.

    ---
    bad example
    ---
    select
    account_id,date_created,description,0,'',approved, approved_by,date_appr
    oved,ex_vat_amount,null
    from OPENROWSET('SQLOLEDB','server';'userid';'password' ,'select *
    from dbname.dbo.gtcust_charges
    where approved = ''Y'' ') o
    where --date_approved between @start_date and @end_date
    ex_vat_amount <0
    and account_id = 20020944
    Last edited by corncrowe; 03-28-12 at 19:43.

  3. #3
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17
    Thanks for your response.
    I am using a 64 bit version of SQL Server and it doesnt seem to be OLEDB friendly, throwing errors like:
    The OLE DB provider "otherdb" has not been registered.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dataman2010 View Post
    Thanks for your response.
    I am using a 64 bit version of SQL Server and it doesnt seem to be OLEDB friendly, throwing errors like:
    The OLE DB provider "otherdb" has not been registered.
    google is your friend?

    64 bit version of SQL Server can not connect to 32 bit verison
    I do not know what version the other server is, and so just googled an idea.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I connect between 64 bit Microsoft SQL Server 2008 R2 and a pretty wide variety of both 32 and 64 bit clients. There is a distinct shortage of 64 bit drivers for some database protocols, but that is a completely different problem.

    If you are not a SQL Server Administrator, yoiu can't add the linked server to use any external database directly from within SQL Server. In your situation, I'd strongly suggest getting a SQL Server Administrator to help you if that is possible. If you can't get them to assist you, it MAY be possible to work around the problem using SSIS.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •