6 replies to this topic

[ireddy]

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?

[Joseph Bove]

ireddy,

I don't know if this helps or not.

We have accessed remote databases by doing the following:

Add a section like the one below to your application INI file

[DATABASE

[Joseph Bove]

ireddy,

I don't know if this helps or not.

We have accessed remote databases by doing the following:

Add a section like the one below to your application INI file

[DATABASE -

[Joseph Bove]

ireddy,

sorry - I flubbed my first response...

I don't know if this helps or not.

We have accessed remote databases by doing the following:

Add a section like the one below to your application INI file

[DATABASE - REMOTE]
FILETYPE=SQLSERVE
SQL_DBNAME=REMOTE
CONNECTION=REMOTE/REMOTE/REMOTE

Setup the ODBC connection for the remote database

Within your source code, use the alias command to alias the table name as %REMOTE%Tablename

You can then access the table directly.

We have not had any locking problems with this approach... But, I don't know if it fits your business requirements.

hth,

Joseph

[ireddy]

ireddy,

sorry - I flubbed my first response...

I don't know if this helps or not.

We have accessed remote databases by doing the following:

Add a section like the one below to your application INI file

[DATABASE - REMOTE]
FILETYPE=SQLSERVE
SQL_DBNAME=REMOTE
CONNECTION=REMOTE/REMOTE/REMOTE

Setup the ODBC connection for the remote database

Within your source code, use the alias command to alias the table name as %REMOTE%Tablename

You can then access the table directly.

We have not had any locking problems with this approach... But, I don't know if it fits your business requirements.

hth,

Joseph

[ireddy]

Joseph,

Sorry messed up the reply,

But anyway, it works like a dream now.

I added,

[Database - REMOTE]

PRODB_CHARSET=7

FILETYPE=SQLSERVE

CONNECTION=remote/remote/REMOTE


to the pro4.ini file.

Then setup the ODBC connection and a user on the external database.

Then I changed the alternate against the pro4.ini file to
be %REMOTE%Tablename

and everything works.

But it turned out that one of the guys here, had already done this in another system, he also posted the solution on here a while back, but when I posted my message, he was off until this week.

but thanks anyway

ireddy

[Joseph Bove]

Ireddy,

Glad it worked out for you.

Regards,

Joseph