Jump to content


Photo
- - - - -

PRO-IV and remote databases using SQL Server


6 replies to this topic

#1 ireddy

ireddy

    Newbie

  • Members
  • Pip
  • 3 posts
  • Gender:Male

Posted 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?

#2 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 19 December 2006 - 10:32 PM

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

#3 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 19 December 2006 - 10:32 PM

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 -

#4 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 19 December 2006 - 10:46 PM

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

#5 ireddy

ireddy

    Newbie

  • Members
  • Pip
  • 3 posts
  • Gender:Male

Posted 03 January 2007 - 03:24 PM

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



#6 ireddy

ireddy

    Newbie

  • Members
  • Pip
  • 3 posts
  • Gender:Male

Posted 03 January 2007 - 03:39 PM

:rolleyes: <_< :eek: :D :D

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

:D :D :D :D :D

#7 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 03 January 2007 - 03:45 PM

Ireddy,

Glad it worked out for you.

Regards,

Joseph



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users