Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Posts
    36

    Unanswered: referring two databases on two different servers

    Hi
    My connection string string is like this
    ConnString="Provider=SQLOLEDB; DataSource=xxxx;userID=sa;password=admin;initial catalog=yyyy"


    my sql query goes like this

    SELECT CONVERT(VARCHAR(8), GETDATE(), 112)+'-'+substring(com.container_no,1,11)+'-'+substring(cast(NEWID() as varchar(40)),1,3) as Container_ID,substring(com.container_no,1,11) as Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' as Entered_By,GETDATE() as Entered_Date,cotr.Origin,(select convert(varchar,com.Arrival_Date,101)) as Arrival_Date,(select convert(varchar,com.Arrival_Date,109)) as Arrival_Time from DehartGroup.dbo.Container_Master as com,DehartGroup.dbo.Container_Tracing as cotr where com.Container_No=cotr.Container_No and com.Seal_No=cotr.Seal_No AND COM.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS) AND COTR.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS)


    The underlined database is in another server.How to refer it in my asp page
    how to add it in and refer using connection string
    so that i can put it in one page and use it in all the pages

    Thanks and regards
    Ravi

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a linked server on the SQL instance you are connecting to (the one in your connection string) and then you can refer to it using 4 part naming
    Code:
    [other server name].DehartGroup.dbo.Container_Tracing
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    36
    hi
    I did the change as follows
    SELECT CONVERT(VARCHAR(8), GETDATE(), 112)+'-'+substring(com.container_no,1,11)+'-'+substring(cast(NEWID() as varchar(40)),1,3) as Container_ID,substring(com.container_no,1,11) as Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' as Entered_By,GETDATE() as Entered_Date,cotr.Origin,(select convert(varchar,com.Arrival_Date,101)) as Arrival_Date,(select convert(varchar,com.Arrival_Date,109)) as Arrival_Time from [xxx-xxx].DehartGroup.dbo.Container_Master as com,[xxx-xxx].DehartGroup.dbo.Container_Tracing as cotr where com.Container_No=cotr.Container_No and com.Seal_No=cotr.Seal_No AND COM.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS) AND COTR.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS)

    The underlined is the server name
    But its throwing error as follows
    Could not find server 'xxx-xxx' in sysservers. Execute sp_addlinkedserver to add the server to sysservers
    Thanks and regards
    Ravi

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you created the linked server?

    Read the error message and take a guess at what you need to look up on google.
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2008
    Posts
    36
    hi
    I did the change as follows
    SELECT CONVERT(VARCHAR(8), GETDATE(), 112)+'-'+substring(com.container_no,1,11)+'-'+substring(cast(NEWID() as varchar(40)),1,3) as Container_ID,substring(com.container_no,1,11) as Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' as Entered_By,GETDATE() as Entered_Date,cotr.Origin,(select convert(varchar,com.Arrival_Date,101)) as Arrival_Date,(select convert(varchar,com.Arrival_Date,109)) as Arrival_Time from [xxx-xxx].DehartGroup.dbo.Container_Master as com,[xxx-xxx].DehartGroup.dbo.Container_Tracing as cotr where com.Container_No=cotr.Container_No and com.Seal_No=cotr.Seal_No AND COM.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS) AND COTR.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS)

    The underlined is the server name
    But its throwing error as follows
    Could not find server 'xxx-xxx' in sysservers. Execute sp_addlinkedserver to add the server to sysservers
    Thanks and regards
    Ravi

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thank you for reposting exactly the same thing twice...
    Quote Originally Posted by georgev
    Create a linked server on the SQL instance you are connecting to
    Quote Originally Posted by georgev
    Have you created the linked server?
    Read the error message.
    Could not find server 'xxx-xxx' in sysservers. Execute sp_addlinkedserver to add the server to sysservers
    I wonder...
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2008
    Posts
    36
    hi
    i am able to create the link server now
    but i want store it in a configuration string
    i stored in a variable then tried to execute the query
    as shown below
    temp=xxx-xxx

    and the query is
    SELECT CONVERT(VARCHAR(8), GETDATE(), 112)+'-'+substring(com.container_no,1,11)+'-'+substring(cast(NEWID() as varchar(40)),1,3) as Container_ID,substring(com.container_no,1,11) as Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' as Entered_By,GETDATE() as Entered_Date,cotr.Origin,(select convert(varchar,com.Arrival_Date,101)) as Arrival_Date,(select convert(varchar,com.Arrival_Date,109)) as Arrival_Time from [temp].DehartGroup.dbo.Container_Master as com,[temp].DehartGroup.dbo.Container_Tracing as cotr where com.Container_No=cotr.Container_No and com.Seal_No=cotr.Seal_No AND COM.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS) AND COTR.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS)

    thanks
    ravi

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE PROC dbo.my_proc99 (
       @server sysname
    )
    AS
      BEGIN
    
        IF @server = 'server1'
          BEGIN
            SELECT blah
            FROM   [server1].[db1].[dbo].[table1]
          END
        ELSE IF @server = 'server2'
          BEGIN
            SELECT blah
            FROM   [server2].[db1].[dbo].[table1]
          END
        ELSE IF @server = 'server3'
          BEGIN
            SELECT blah
            FROM   [server3].[db1].[dbo].[table1]
          END
    
      END
    GO
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2008
    Posts
    36
    hi
    is it not possible to read it from a virable
    like
    temp=xxx-xxxx
    and
    SELECT CONVERT(VARCHAR(8), GETDATE(), 112)+'-'+substring(com.container_no,1,11)+'-'+substring(cast(NEWID() as varchar(40)),1,3) as Container_ID,substring(com.container_no,1,11) as Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' as Entered_By,GETDATE() as Entered_Date,cotr.Origin,(select convert(varchar,com.Arrival_Date,101)) as Arrival_Date,(select convert(varchar,com.Arrival_Date,109)) as Arrival_Time from [temp].DehartGroup.dbo.Container_Master as com,[temp].DehartGroup.dbo.Container_Tracing as cotr where com.Container_No=cotr.Container_No and com.Seal_No=cotr.Seal_No AND COM.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS) AND COTR.CONTAINER_NO not in (SELECT CONTAINER_NO FROM TBLCONTAINERS)



    thanks
    Ravi

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Only by using dynamic SQL which I won't advocate for this situation.
    George
    Home | Blog

Posting Permissions

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