Could not create an acceptable cursor

Question

Post reply

Could not create an acceptable cursor

timothy.merridew

SSChasing Mays

Points: 651
More actions
March 3, 2008 at 7:57 am

#179539

Hi,
First time poster here, I'm having problems with an update statement at the moment, with an error I've not seen before and despite searching cannot seem to find a resolution to.
We have 2 SQL servers, 1 is a SQL 2000 and the other SQL2005. I'm running this script on the SQL2005 box as this is our data manipulation system for our web data, the SQL2000 is a production box running one of our main business systems.
The aim of the script is to look at a table on the SQL2005 box to see if any of the records in the productswaitingapproval table have been approved, these will be in the products table with an approvalstatus of = '1'. If they have been approved they will have been given a nwcode value and I need to extract this an put it in the table on the SQL 2000 system. The code below gives the following error when run in SQL 2005 Server Management Studio
OLE DB provider "SQLNCLI" for linked server "sage" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.
UPDATE [sage].cleaningnet.dbo.ImportTimStock
SET catalogue_number = 'tim123' COLLATE Latin1_General_CI_AS
WHERE product IN (
SELECT a.sku COLLATE Latin1_General_CI_AS
FROM ProductsWaitingApproval a
JOIN [tmCleaningNet].dbo.products b
ON a.idProduct = b.idProduct
WHERE a.supplierRef IN (
SELECT supplierRef
FROM [tmCleaningNet].dbo.products
WHERE ApprovalStatus = '1')
)
If you can see a better way of doing this SQL statement, I cannot change the database structure, then please let me know. At the moment the SQL above is attempting to populate with a fixed literal for testing purposes. Also what do I need to do to replace the 'tim123' with the value from [tmCleaningNet].dbo.products.nwCode as when I attempt that it complains that it cannot bind.
In addition, I have tried the above as a select statement, replaceing the UPDATE and the SET with a SELECT * FROM [tmCleaningNet].dbo.products and I do get back the 2 rows that I am expecting to be able to update.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try this, Tim. If the SELECT works ok, then comment the SELECT and uncomment the UPDATE.

--UPDATE i SET catalogue_number = a.sku COLLATE Latin1_General_CI_AS

SELECT i.ImportTimStock, a.sku COLLATE Latin1_General_CI_AS

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN ProductsWaitingApproval a

ON a.product = i.product

INNER JOIN [tmCleaningNet].dbo.products b

ON b.idProduct = a.idProduct AND b.ApprovalStatus = '1'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timothy.merridew SSChasing Mays Points: 651 More actions · Answer 2

Chris

I've tried both, the SELECT statement worked, needed a few alterations on the columns and collate placements, however unfortuantly, the UPDATE causes the same error using this code

UPDATE i SET catalogue_number = a.sku COLLATE Latin1_General_CI_AS

--SELECT i.product COLLATE Latin1_General_CI_AS, a.sku COLLATE Latin1_General_CI_AS

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN ProductsWaitingApproval a

ON a.sku = i.product COLLATE Latin1_General_CI_AS

INNER JOIN [tmCleaningNet].dbo.products b

ON b.idProduct = a.idProduct AND b.ApprovalStatus = '1'

I do like how much cleaner it looks over what I had come up with, still learning some of the finer points of SQL joins. 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thanks for the prompt reply Tim. I've seen this error before doing the same i.e. updating a table on one server from another linked server, where the collation is different. I'm sure it came down to the COLLATE qualifier and where they were placed. I'm trying to hunt it down right now: in the meantime, can I suggest you try this...

UPDATE i SET catalogue_number = a.sku

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Tim

This should do it...

UPDATE i SET catalogue_number = a.sku

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN ProductsWaitingApproval a

ON a.sku = i.product COLLATE Latin1_General_CI_AS

INNER JOIN [tmCleaningNet].dbo.products b

ON b.idProduct = a.idProduct

AND b.ApprovalStatus = '1'

but do check the collation of a.sku and i.product.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timothy.merridew SSChasing Mays Points: 651 More actions · Answer 5

Chris

Nope....

UPDATE i SET catalogue_number = a.nwCode

--SELECT i.product COLLATE Latin1_General_CI_AS, a.sku COLLATE Latin1_General_CI_AS

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN ProductsWaitingApproval a

ON a.sku = i.product COLLATE Latin1_General_CI_AS

INNER JOIN [tmCleaningNet].dbo.products b

ON b.idProduct = a.idProduct

AND b.ApprovalStatus = '1'

Still gives the OLE DB provider "SQLNCLI" for linked server "sage" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor. error. I did change the a.sku to a.nwCode as I realised that we were about to update a field with the value that is already in the record. product = sku in this case, just different field names in the different tables.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Grrr!

Have you recorded the collation cast of a.sku and i.product?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timothy.merridew SSChasing Mays Points: 651 More actions · Answer 7

Here is the info.......

Server: SAGE (server collation: Latin1_General_BIN)

SQL: 2000 32bit

Database: cleaningnet

Table: ImportTimStock

Table Collation: SQL_Latin1_General_CP1_CI_AI

Server: chersql01 (server collation: Latin1_General_CI_AS)

SQL: 2005 32bit

Database: [CleaningNet Interim]

Table: ProductsWaitingApproval

Table Collation: Latin1_General_CI_AS

Table: tmCleaningnet

Table Collation: Latin1_General_CI_AS

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Aha! I think you want to do it this way round...

UPDATE i SET catalogue_number = a.nwCode

FROM [sage].cleaningnet.dbo.ImportTimStock i

INNER JOIN ProductsWaitingApproval a

ON a.sku COLLATE SQL_Latin1_General_CP1_CI_AI = i.product --COLLATE Latin1_General_CI_AS

INNER JOIN [tmCleaningNet].dbo.products b

ON b.idProduct = a.idProduct

AND b.ApprovalStatus = '1'

Careful though, a column can have a collation cast different to that of the table...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timothy.merridew SSChasing Mays Points: 651 More actions · Answer 9

Guess what...

Still no luck, same error. I have noticed that although we are SQL 2000 SP4, 2005 is still at original RTM status, I don't know if SP2 may sort it, trouble is arranging the upgrade at the moment, system is under a lot of demand and so is my time!

Thanks for looking at this, I'll try again tomorrow, going home now.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

I'm sorry Tim. Better luck tomorrow.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timothy.merridew SSChasing Mays Points: 651 More actions · Answer 11

For anyone interested I've now resolved this problem. The issue is due to there being no unique index on the target table, as soon as I created an index on the table and set it as unique it allowed me to run the update.

I would like to thank Chris above for all of his assistance yesterday.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Nice bit of investigative work there, Tim. Who would have guessed it from the error message?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

murray50 Old Hand Points: 386 More actions · Answer 13

We had a similar problem (same error message) and found that we were lacking a primary key on the table we were updating

Rene G SSC Enthusiast Points: 183 More actions · Answer 14

Ditto that - similar setup (updating linked server data) same type of error message (Multiple-step OLE DB operation generated errors. ... Could not create an acceptable cursor) .

Fixed with a unique non-clustered index on the table in question.