3 replies to this topic

[Dan Shreeve]

I am running Pro-IV version 5.5 with SQL Server 2000. In my SQL SERVER 2000 I have created a LINKED SERVER to a SQL SERVER 2008 database.

I have created a query in my SQL SERVER 2000 database that addresses a table via the LINKED SERVER in the SQL 2008 database.

View SSUSRQRY:

SELECT UserIdExternal AS SSUSERIDEXTERNALQRY, UserId AS SSUSERIDQRY
FROM [DEV01\SCTSR].SQLSERVER_08_DB.dbo.tblUser tblUser_1

When I run the query in Enterprise Manager, it returns all the rows in the table with no problems.

Pro-IV SQL:
SQL
SELECT SSUSERIDEXTERNALQRY, SSUSERIDQRY FROM SSUSRQRY
WHERE SSUSERIDEXTERNALQRY = :$APTTHER
ENDSQL

When I try to run the query via a Pro4 SQL STATEMENT I get the following error:

You cannot specify scroll locking on a cursor that contains a remote table.[Microsoft][ODBC SQL Server] Driver][SQL Server]Statement(s) could not be prepared.

[Richard Bassett]

CAVEAT: I have no SQL/Server experience... but..

The error message seems pretty exemplary to be fair. PROIV is presumably trying to lock records in a way that SQL/Server simply does not support when accessing remote tables (ie. tables in another SQL/Server database or instance whatever the appropriate terminology is). It's not necessarily any kind of bug it may really, legitimately not be supported.

You're not telling us very much about the context, even the type of PROIV function in which you're doing this. If I was to make a wild-assed guess I'd say I probably would not expect PROIV to provoke this problem with a report or update but it might within a paging screen in C or D mode?

If the use of the remote table is essential to your problem (ie. you can't change that aspect of things), can you play with the function maybe and see if the problem relates to specific modes and if, say, it's change mode try updating the file in another way than, say. as the primary file in a paging screen?

[Dan Shreeve]

CAVEAT: I have no SQL/Server experience... but..

The error message seems pretty exemplary to be fair. PROIV is presumably trying to lock records in a way that SQL/Server simply does not support when accessing remote tables (ie. tables in another SQL/Server database or instance whatever the appropriate terminology is). It's not necessarily any kind of bug it may really, legitimately not be supported.

You're not telling us very much about the context, even the type of PROIV function in which you're doing this. If I was to make a wild-assed guess I'd say I probably would not expect PROIV to provoke this problem with a report or update but it might within a paging screen in C or D mode?

If the use of the remote table is essential to your problem (ie. you can't change that aspect of things), can you play with the function maybe and see if the problem relates to specific modes and if, say, it's change mode try updating the file in another way than, say. as the primary file in a paging screen?

I asked my question to pro-4 directly as well... They indicated that pro-4 doesn't support accessing remote tables.... My point would be that since it is a query it shouldn't have been locking anything. The error I am receiving is due to what ever other code they wrap around my sql. Which from the error it sounds like they were using a cursor which I am not even sure was necessary.

[Richard Bassett]

In C programming/API terms there's essentially always a cursor - it's just the iterator "object" supporting fetch/get-next/etc.. When you're using interactive SQL or whatever there are still plenty of cursors, they're just not as obvious. In fact there may be separate "cursors" on the client side and "cursors" on the database side, but let's not go there..

If when you say it's a query you mean what you're doing is strictly read-only/lookup-mode-only, then I agree with you that it ought to work and one has to conclude that PROIV is acquiring some kind of locks that in principle at least it ought not to need. I think I should stop guessing though given what little I know about PROIV on SQL/Server