Post reply

Join tables from two SQL servers with ASP?

  • July 21, 2007 at 2:11 am

    #92568

    Hello! Sorry if posting in the wrong group!

    I have the follwing scenario, 2 MS SQL servers, I want to do a INNER JOIN with data from the two servers, in MC Access the qyery looks like this:

    SELECT dbo_Product.ProductNumber, dbo_SC010100.SC01060, dbo_SC010100.SC01042

    FROM dbo_Product INNER JOIN dbo_SC010100 ON dbo_Product.ProductNumber = dbo_SC010100.SC01060;

    and it works great, but how do I solve this on an ASP page? I cant figure out the connection settings so that the ASP code nows what databases to connect to?? Is that possible?

    Product.ProductNumber is on server A and SC010100.SC01060 is in server B.

    TIA / Kent

  • July 21, 2007 at 5:17 am

    #721325

    Check out sp_addlinkedserver in the books online.

  • July 21, 2007 at 6:10 am

    #721333

    Thnx!

    Do I put the sp_addedlinkedserver command in my ASP file?

    Where could I find the syntax for that?

  • July 21, 2007 at 6:36 am

    #721334

    This adds a linked server on the sql server.  This then allows you to do something like this :

     

    Select * From LinkedSvr.DatabaseName.dbo.TableName inner join...

  • July 21, 2007 at 7:34 am

    #721338

    Sorry for all the stupid newbee questions - but where do I run the sp_addlinkedserver command?

    I have only read access from ASP files to the SQL server - must I run the command on the SQL server itself?

  • July 21, 2007 at 8:09 am

    #721339

    Run that in any command or connection object from asp.  This may be your biggest concern of them all :

    Permissions

    Execute permissions default to members of the sysadmin and setupadmin fixed server roles.

     

    Do a trial run and see what happens.  It might be a good idea to ask for permission/help to do this.  It may very well save you some time and efforts.

  • July 21, 2007 at 8:39 am

    #721342

    Thanks for all your help!

    I have all (sa) permissions on one of the SQL servers but only read on one of them!

    Could you give me an example of the code that I should execute?

    I dont unerderstand where I put in username and password for the server in the sp_addlinkedserver command?

  • July 21, 2007 at 9:03 am

    #721343

    That's another command : sp_addlinkedsrvlogin

    I was assuming that you were using windows authentication!

     

    NEVER ASSUME ANYTHING!

     

    There I'll remember this time .

Viewing 8 posts - 1 through 8 (of 8 total)

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