| enichols76@gmail.com 2006-03-09, 8:23 pm |
| What a headache. I have done extensive research on this and the only
solution I have found is to rename the database. I hope that is not
the case, as it won't be trivial (it's somebody else's server). I have
verified that the period is causing a problem by setting up a dummy
server with the database name (and giving my @rmtuser sysadmin
privliges). When I rename the db without the period, it works fine.
The code that is commented out is what I ideally want to use.
OpenQuery is not flexible enough for me. I am keeping my fingers cross
that there is some way to communicate that period is part of the db
name... I think this is a bonafide SQL/ODBC bug... Here's what I am
trying to accomplish.
use master
if exists(select srvName from sysservers where srvname='server')
exec sp_dropserver @server = 'server', @droplogins = 'droplogins'
exec sp_addlinkedserver @server = 'server', @srvproduct = N'SQL Server'
go
exec sp_addlinkedsrvlogin
@rmtsrvname = 'server', @useself = 'false',
@locallogin = null, @rmtuser = 'rmtuser',
@rmtpassword = 'password'
go
select *
from openquery([server], 'select top 100 * from
[database.com].dbo.syscomments')
--Doesn't work!!!! The period in the linked server !@#$% it all up!
/*
select *
from [server].[database.com].dbo.syscomments
*/
|