referring two databases on two different servers

ravisakee · #1 (**permalink**) 12-29-08, 03:02

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

gvee · #2 (**permalink**) 12-29-08, 04:11

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

ravisakee · #3 (**permalink**) 12-29-08, 04:38

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

gvee · #4 (**permalink**) 12-29-08, 04:45

Have you created the linked server?

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

ravisakee · #5 (**permalink**) 12-29-08, 04:48

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

gvee · #6 (**permalink**) 12-29-08, 05:07

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.

Quote:

Could not find server 'xxx-xxx' in sysservers. Execute sp_addlinkedserver to add the server to sysservers

I wonder...

ravisakee · #7 (**permalink**) 12-29-08, 05:13

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

gvee · #8 (**permalink**) 12-29-08, 05:41

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

ravisakee · #9 (**permalink**) 12-29-08, 06:04

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

gvee · #10 (**permalink**) 12-29-08, 06:10

Only by using dynamic SQL which I won't advocate for this situation.

Internet Services

Web Development

Digital Marketing

Consumer Tech