Linked Server question - 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)

Linked Server question

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-09 : 09:11:30

We have several queries that connect to db2/400. We are also in the situation where we do a role swap twice a year where we switch from one machine to another. When we do so we are having issues with the server name in the connect string.

Example:

select     * 
from       AS400SRV_MSDASQL.VGSYSB.VGITFRLIB.F551202A

points to server VGSYSB and when we switch machine the query does not work, we manually have to change the server to VGSYSA.

Is there a way to get around this. We have a DNS record called VGSYS400.CORP.COM and wonder how to use this if possible. I have tried this but cannot get it to work:

declare @DNS varchar(30)
set @DNS = 'VGSYS400.CORP.COM'

declare @STRING varchar(100)
set @STRING = ' AS400SRV_MSDASQL.''' + @DNS + '''.VGITFRLIB.F551202A' 

select     * 
from       @From

Any help is very much appreciated. Thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 09:19:53

Try this

declare @DNS varchar(30)
set @DNS = 'VGSYS400.CORP.COM'

declare @STRING varchar(100)
set @STRING = 'SELECT * FROM AS400SRV_MSDASQL.[' + @DNS + '].VGITFRLIB.F551202A'

EXEC(@string)

Madhivanan

Failing to plan is Planning to fail

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-09 : 09:27:52

Tried, got message:

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "AS400SRV_MSDASQL".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 09:34:48

Instead of DNS, cant you use Server name in @STRING ?

Madhivanan

Failing to plan is Planning to fail

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-09 : 09:38:21

We are using server name today, but we are changing server (db2/400) we run on twice a year (we have 2 servers we are swicthin from/to). This means we have to change every query to point to new server.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 02:44:44

will @@servername help?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources