Eric,

How does the concept of linked servers in sql7 work in 6.5? I need to access data from from a sql 6.5 server db in another 6.5 server's database. Haven't worked with 6.5 much & All I could find on this on 6.5 BOL..was creating remote servers. I did an sp_addserver & ...remotelogins and all that, but why doesn't a statement like " Select * from ServerName.DatabaseName.DBOwner.TableName" work in 6.5 like it does in 7.0

How can I do this? Isn't there a linked server concept at all in 6.5?

Thanks a lot for your time. Any info appreciated.

-- Anonymous, May 15, 2000

Answers

N,

SQL Server 6.5 does not have the linked server concept (that is, the ServerName.DatabaseName.DBOwner.TableName syntax).

You will have to use a hack of some sort. One approach is to use xp_cmdshell to run an isql session against the server like so:

exec master..xp_cmdshell 'isql -Usa -Pmysapassword -Smyremoteserver - Q"select * from mydb..mytable"'

The trouble with this approach is that the output is returned in an nvarchar(255) column

The other possible approach is through the use of remote stored procedures. First, on each server, go into Server, Remote Servers and add the opposing server. Then if, for example, Server1 wanted to capture the output of a paramaterized stored procedure on Server2, you could type: Insert into #TempTable Execute Server1.master.dbo.StoredProc parameter1

You can also write a stored procedure or extended stored procedure that does about the same thing under the covers.

Hope this helps,

Eric

-- Anonymous, May 19, 2000