Jump to content


ireddy

Member Since 19 Dec 2006
Offline Last Active Mar 08 2007 05:52 PM
-----

Topics I've Started

PRO-IV and remote databases using SQL Server

19 December 2006 - 12:33 PM

Right here goes, I am using SQL Server 2000 and Pro-iv 5.5

Basically what I have got is a main database and a remote database on another server. I have a table in the remote database which I need to access via the main database, I have created a link in the main database using the commands in query analyzer.

sp_addlinkedserver 'remote server'



sp_addlinkedsrvlogin 'remote server','false',NULL,'sa','pwd'

Then if I create a View in the main database, I can query this view or add data to it no problem in SQL Query analyzer, but if I then create a proiv function to read this file, I get a record lock however I try to access it.

I then tried to access the table in the remote database directly using a command similar to below.

SELECT * FROM remote server.database.dbo.table

Again this work perfectly in query analyzer. So I then amended the alternate file def in Pro-iv to access the file directly with the alternate against the file similar to below.

remoteserver.database.dbo.table

this time the record lock has gone but I get an 366 SQL error msg

Cannot specify an index or locking hint for a remote data source

Any ideas?