Linked Servers 1

[Savil]

Being a little new to linked servers I need some help on compilation.

I have set the linked server up fine with the server name being tmpServer

If I do for example
'select * from tmp...tbldata'
enterprise manager will not compile saying it can't find tmpserver
If I wrap it into text and run sp_executesql it compiles, runs and produces the results I want.

Do processes run against linked Servers always have to be run in dynamic SQL?

 

[Savil]

sorry should read
'select * from tmpServer...tblData'
Qustion still valid.

 

[JamesLean]

No, you don't have to use dynamic SQL. Is this a linked SQL Server? If so you need to fully qualify the name:

SELECT * FROM server.database.owner.table

--James

 

[Savil]

No the linked server is an Access DB connected usin sp_addlinkedserver

 

[JamesLean]

The only thing it might be is that you say you are using Ent mgr to do the query. EM has several limitations when it comes to queries/object design. Try it from Query Analyzer and see if that works.

--James

 

[Savil]

Tried to compile from QA but returns
'Cannot find server tmpServer in sysservers. Execute sp_addlinkedserver to add the server to sysservers.'

It's quite frustrating that it would most likely be ok at runtime but it will not compile and save unless in dynamic sql format.

I suppose what I could do is create the linked server, enough to compile and save?

 

[JamesLean]

Are you sure the server has been added properly? Does this return anything:

select * from master..sysservers
where srvname = 'tmpServer'

--James

 

[Savil]

I can see where you are coming from,
The linked server is only used once a month so I the sp_addlinkedserver only runs once a month. I have an SP which is like

sp_addlinkedserver
blah
blah

Run queries required that month

sp_droplinkedserver

Doing what I said in my last post works so I will stick with that

Thanks for your help, Have a star for your tolerance.

 

[JamesLean]

Ahhh, I see. You're adding the linked server and then querying it in the same batch, which is why you are getting the compile error.

As you say, workaround is to have the linked server in place just while you're saving the proc.

ALternatively, why do you need to drop the linked server at all? Even if you don't use it that often it won't do any harm keeping it.

--James

 

[cmmrfrds]

If there is only a temporary need to combine data from Access and SQL Server tables then, the OPENROWSET function can work in that capacity.