Could not create an acceptable cursor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Could not create an acceptable cursor

25 posts, Page 1 of 3

1 2 3 »»»

Could not create an acceptable cursor

Rate Topic

Display Mode

Topic Options

Author

Message

timothy.merridew

Posted Monday, March 03, 2008 7:57 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 7:46 AM
Points: 51, Visits: 99

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.

Post #463155

ChrisM@Work

Posted Monday, March 03, 2008 8:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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
Exploring Recursive CTEs by Example Dwain Camps

Post #463162

timothy.merridew

Posted Monday, March 03, 2008 8:30 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 7:46 AM
Points: 51, Visits: 99

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. Smile

Post #463176

ChrisM@Work

Posted Monday, March 03, 2008 8:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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}

Post #463189

ChrisM@Work

Posted Monday, March 03, 2008 9:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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}

Post #463221

timothy.merridew

Posted Monday, March 03, 2008 9:24 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 7:46 AM
Points: 51, Visits: 99

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.

Post #463230

ChrisM@Work

Posted Monday, March 03, 2008 9:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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}

Post #463233

timothy.merridew

Posted Monday, March 03, 2008 9:42 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 7:46 AM
Points: 51, Visits: 99

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

Post #463238

ChrisM@Work

Posted Monday, March 03, 2008 9:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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}

Post #463252

timothy.merridew

Posted Monday, March 03, 2008 10:00 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 7:46 AM
Points: 51, Visits: 99

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.

Post #463256

« Prev Topic | Next Topic »

25 posts, Page 1 of 3

1 2 3 »»»

Permissions