SQL SERVER SCROLL LOCKING
#1
Posted 05 November 2009 - 10:35 PM
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.
#2
Posted 09 November 2009 - 01:08 PM
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?
#3
Posted 09 November 2009 - 02:02 PM
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.
#4
Posted 09 November 2009 - 02:37 PM
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
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users