connection to remote server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

connection to remote server

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-09-16 : 07:26:49

After moving a DB to a new Server (SQL 2005), the DB fails to work properly. This is caused by a function that calls an instance on a different server and I'm wondering about the correct syntax. While the DB was on the old server the function did its job (calling a different instance, but on the same server); now all I try isn't working.

----------------------------
USE [WesContractors]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SubstAbbrev] (
@Input VARCHAR(60)
)
RETURNS VARCHAR(60)
AS
BEGIN
RETURN (SELECT TOP 1 AxProd.dbo.EMPLTABLE.NAME
FROM dbo.Estadisticas_Ofertas INNER JOIN
AxProd.dbo.CUSTTABLE ON LEFT(dbo.Estadisticas_Ofertas.SelectIngenieria, 8) = AxProd.dbo.CUSTTABLE.ACCOUNTNUM INNER JOIN
AxProd.dbo.EMPLTABLE ON AxProd.dbo.CUSTTABLE.VENDEXT = AxProd.dbo.EMPLTABLE.EMPLID
WHERE dbo.Estadisticas_Ofertas.SelectIngenieria = @Input)
END
--------------------------

As the AxProd tables where unknown, I placed the server's name first (OldServer.AxProd...), after having introduced the server using the sp_addlinkedserver proceedure. Executing:

EXEC sp_addlinkedserver
@server = 'OldServer',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=OldServer;UID=print;PWD=print;'

any help is apreciated
Martin

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-16 : 07:44:25

Are you getting any results by executing the following query:

SELECT TOP 200 * from OldServer.AxProd.dbo.CUSTTABLE

If not the there is something wrong in the setup of your linked sever. Permission problems maybe?

- Lumbago

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-09-17 : 02:43:56

SELECT TOP 200 * from OldServer.AxProd.dbo.CUSTTABLE

DOES give the expected results... hmmm?!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-17 : 03:47:28

There are no references to "OldServer." in your function though...

- Lumbago

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-09-17 : 04:19:36

I checked again the syntax of the commands that created the function while the DB was in its old location:

CREATE FUNCTION dbo.SubstAbbrev (
@Input VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
RETURN (SELECT TOP 1 AxProd.dbo.EMPLTABLE.NAME
FROM dbo.Estadisticas_Ofertas INNER JOIN
AxProd.dbo.CUSTTABLE ON LEFT(dbo.Estadisticas_Ofertas.SelectIngenieria, 8) = AxProd.dbo.CUSTTABLE.ACCOUNTNUM INNER JOIN
AxProd.dbo.EMPLTABLE ON AxProd.dbo.CUSTTABLE.VENDEXT = AxProd.dbo.EMPLTABLE.EMPLID
WHERE dbo.Estadisticas_Ofertas.SelectIngenieria = @Input)
END

In order to build the same function in the new location, I have to refer to AxProd as being on a different server and thus replace Ax.Prod with "OldServer.AxProd". Unfortunatly this leads to error messages:

Mens 4104, Nivel 16, Estado 1, Procedimiento SubstAbbrev, Línea 7
The multi-part identifier "OldServer.AxProd.dbo.CUSTTABLE.ACCOUNTNUM" could not be bound.
Mens 4104, Nivel 16, Estado 1, Procedimiento SubstAbbrev, Línea 7
The multi-part identifier "OldServer.AxProd.dbo.CUSTTABLE.VENDEXT" could not be bound.
Mens 4104, Nivel 16, Estado 1, Procedimiento SubstAbbrev, Línea 7
The multi-part identifier "OldServer.AxProd.dbo.EMPLTABLE.EMPLID" could not be bound.
Mens 4104, Nivel 16, Estado 1, Procedimiento SubstAbbrev, Línea 7
The multi-part identifier "OldServer.AxProd.dbo.EMPLTABLE.NAME" could not be bound.

CREATE FUNCTION dbo.SubstAbbrev (
@Input VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
RETURN (SELECT TOP 1 OldServer.AxProd.dbo.EMPLTABLE.NAME
FROM dbo.Estadisticas_Ofertas INNER JOIN
OldServer.AxProd.dbo.CUSTTABLE ON LEFT(dbo.Estadisticas_Ofertas.SelectIngenieria, 8) = OldServer.AxProd.dbo.CUSTTABLE.ACCOUNTNUM INNER JOIN
OldServer.AxProd.dbo.EMPLTABLE ON OldServer.AxProd.dbo.CUSTTABLE.VENDEXT = OldServer.AxProd.dbo.EMPLTABLE.EMPLID
WHERE dbo.Estadisticas_Ofertas.SelectIngenieria = @Input)
END

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-09-18 : 04:23:57

I solved the problem! The key word is distributed queries.

There is a number of guidelines to follow if your query includes remote server connections using four-part table name expressions. In this cases you have to introduce aliases... that's all.

So the Query would look like:

SELECT TOP 1 DAX_EMPLTABLE.NAME
FROM dbo.Estadisticas_Ofertas INNER JOIN OldServer.AxProd.dbo.CUSTTABLE AS DAX_CUSTTABLE ON LEFT(dbo.Estadisticas_Ofertas.SelectIngenieria, 8) = AS DAX_CUSTTABLE.ACCOUNTNUM INNER JOIN
OldServer.AxProd.dbo.EMPLTABLE AS DAX_EMPLTABLE ON DAX_CUSTTABLE.VENDEXT = DAX_EMPLTABLE.EMPLID
WHERE dbo.Estadisticas_Ofertas.SelectIngenieria = @Input

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources