Could not create an acceptable cursor

Question

Post reply

Could not create an acceptable cursor

Viewing 15 posts - 1 through 15 (of 26 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.

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 15

Just to add; also solved my problem by adding unique non-clustered index on the lined table