Cursor Operation Conflict

I keep getting a cursor operation conflict when calling the "Update" method
of a recordsetup object using VB6 with a SQL Server 2000 DB. I have 0
"INSTEAD OF" triggers in the database, my user options are set to 0. I am
running MDAC 2.81.117.0 on a Win XP SP2 machine against a Win 2003 Server
with SQL Server 2000 SP 4. This didn't happen in previous versions of our
software, indicating to me that the software has changed somewhere. I've
looked through the software and nothing different stands out. If I change t
o
a client-side cursor it works ok. The data is still saved with a server sid
e
cursor, but I get the error message "Cursor Operation Conflict". There are
no triggers on the table that I'm inserting into.UPDATE: I'm now using a "NOT IN" in one portion of our software and that
seems to be causing the cursor operation conflict in other portions of the
software. I'm not sure why, but my syntax is "KeyField NOT IN (400, 390)",
which the records return fine both in VB and Query Analyzer, but when I try
to add/update a record in other parts of the software then I get the error.
If I exclude the "NOT IN" then no errors elsewhere.
"John Estrada" wrote:

> I keep getting a cursor operation conflict when calling the "Update" metho
d
> of a recordsetup object using VB6 with a SQL Server 2000 DB. I have 0
> "INSTEAD OF" triggers in the database, my user options are set to 0. I am
> running MDAC 2.81.117.0 on a Win XP SP2 machine against a Win 2003 Server
> with SQL Server 2000 SP 4. This didn't happen in previous versions of our
> software, indicating to me that the software has changed somewhere. I've
> looked through the software and nothing different stands out. If I change
to
> a client-side cursor it works ok. The data is still saved with a server s
ide
> cursor, but I get the error message "Cursor Operation Conflict". There ar
e
> no triggers on the table that I'm inserting into.

Current Activity : Locks/Object : DB Level locks.

The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When
MS looked into how
prohibiting these actions for 7.0, they realized that using the already pres
ent lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>

Current Activity : Locks/Object : DB Level locks.

The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZ
Hi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>
|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ
|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>
|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ
|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>

Current Activity : Locks/Object : DB Level locks.

The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>

Cubes process error

Hi all!

After processing my cubes I have this error: Server error : Process error [object doesn't exist] 'Partner' ; ?

Someone can help me?
Thanks!

Try to do this - go to Business Intelligence development studio, open your data source view and then on the diagram do right mouse click choose option "Refresh...". This will tell you what changes are between what you have in SSAS and what is really in database. It looks like one of your dimensions or measure groups is referencing table that does not more exists in source database.

Vidas Matelis

My Blog: http://www.ssas-info.com/content/blogcategory/14/36/

CTE in OLE DB Command Data Flow Transformation

I am trying to use a CTE in an OLE DB Command data flow transformation object. However, when I enter the cte and corresponding query in the SqlCommand field of the OLE DB command editor dialog, I get a syntax error. Can CTE's be used data flow objects? I have been able to use them in an Execute SQL Control Flow Item, but not in any data flow item.

I was able to paste a query using a CTE inside of the OLE DB Command with no problem(just a quick copy and paste- no parameters invloved); then I think CTEs are not the problem. May be is the parameter mapping or something else. Could you post the query and the error so folks around here can get a better picture of the problem.|||

Thank you for your reply. I was able to successfully use a CTE in an OLE DB Source data flow component. I had a silly syntax error.

I am now running into a problem using a CTE in an OLEDB command object. There are two levels of the problem.

1. I can use a straight forward CTE in an OLEDB command object if I use the "Native OLE DB\SQL Native Client" provider.

For example, this works:

/**BEGIN QUERY**/

with TestCTE2(prod_id) as
(
select ProductID from Production.Product p
where p.ProductID = 321
)

select prod_id from TestCTE2

/**END QUERY**/

but if I try to modify and use a parameter, like:

/**BEGIN QUERY**/

with TestCTE2(prod_id) as
(
select ProductID from Production.Product p
where p.ProductID= ?
)

select prod_id from TestCTE2

/**END QUERY**/

I get the following error:

Error 2 Validation error. Data Flow Task: OLE DB SQL Native Client [863]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Package.dtsx 0 0

2. If I try and use a CTE in an OLE DB Command data flow object using the "Native OLE DB\Microsoft OLE DB Provider for SQL Server" provider

I can't enter the first query from above at all, i get the following error message:

Error 1 Validation error. Data Flow Task: OLE DB provider for SQL Server [875]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Incorrect syntax near the keyword 'with'.". Package.dtsx 0 0

which I find curious, because there is no previous stataments.

The above queries are going against the adventureworks database.

Any further insight? Thanks for your help.

Kyle Key

|||

Kyle,

That was the test I performed in my initial post; and you are right, as soon as you add a parameter it throws an error.

Just out of curiosity; what is your ultimate goal when trying to combine a CTE and an OLEDB command? Are you trying to update rows or what?

For performance reasons, I try to stay away of OLE DB Commands. In some cases, you could replace the OLE DB Command by a OLEDB Destination that point to a staging table and then in control flow you can use a Execute SQL task to execute the same SQL statement using staging table as a reference to constraint which rows should be affected. The advantage of this procedure is to execute the sql statement only once; instead of 1 per row when an OLE DB command is used.

This does not answer your question but could give you other options

|||

I'm using the CTE to flatten out a hierarchy. I can get a list of nodes in a hierarchy, and was hoping to use the OLE DB Command transformation to loop through those nodes to get the leaf nodes and update those leaf nodes. I was going under the assumption that using a staging/temporary table would be costly to performance, but I may have to rethink my approach. I guess getting it done is better than spinning my wheels.

Do you know if the problem I'm seeing is a bug or functioning as designed? I was going to install the latest CTP for SP2 and see if the behavior is any different.

|||

I actually don't know if that would be fixed in SP2. But I found a work around; just place the SQL Statement inside of a stored procedure and then called it from the OLE DB Command.

I ran a test against AdventureWorks, see here for more details:

http://rafael-salas.blogspot.com/2006/12/passing-parameters-to-ole-db-command.html

Pleas, let me know if you found a diffrent approach.

thanks

|||

Kyle Key wrote:

I'm using the CTE to flatten out a hierarchy. I can get a list of nodes in a hierarchy, and was hoping to use the OLE DB Command transformation to loop through those nodes to get the leaf nodes and update those leaf nodes. I was going under the assumption that using a staging/temporary table would be costly to performance, but I may have to rethink my approach. I guess getting it done is better than spinning my wheels.

Do you know if the problem I'm seeing is a bug or functioning as designed? I was going to install the latest CTP for SP2 and see if the behavior is any different.

Kyle,

This probably isn't the optimum way of going about this. As Rafael says the OLE DB Command is not at all performant (we can elaborate as to why if needs be).

In your case it does seem as though using T-SQL will be a better bet. If you really do want to execute this functionality in the data-flow and not have to operate on the data row-by-row a la the OLE DB Command then you could use an asychsronous script component.

It would help to understand the problem better. How are your values in the data-flow being used to update the existing data?

-Jamie