July 16, 2003 at 6:13 pm
--table in question has SSN column defined as char(9)
declare @SSNchar(9)
select @SSN = '996999993'
DELETE [ECD-DBQA01].QSpace.dbo.Prod_XRef WHERE SSN = cast(@SSN AS char(9))
--OR
DELETE [ECD-DBQA01].QSpace.dbo.Prod_XRef WHERE SSN = convert(char(9), @SSN)
throws an error:
Server: Msg 7306, Level 16, State 2, Line 4
Could not open table '"QSpace"."dbo"."Prod_XRef"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
July 17, 2003 at 2:19 am
Just try
Delete From QSpace.dbo.Prod_XRef Where SSN = @SSN
a) i believe you need the From clause and b) if the SSN Column in your table is defined as Char(9) you shouldn't need to perform a Cast or Convert.
Also looking at the message, you may need to change some of the settings in your connection string. Are you performing this from an application of some description ? have you tried running the query from query analyzer?
Growing old is mandatory, growing up is optional
July 17, 2003 at 10:41 am
hi bond007, thanks for reply;
well the issue is that the original was exactly what you did propose to change this to, I suspected that issue is the conversio, hence the explicit cast/convert function, but you're right it is unnecessary since declare takes care of that, I just wanted to be sure; there are not clues as to what causes this error, M$ says to do this
http://support.microsoft.com/default.aspx?scid=kb;en-us;814581 but their worarounds do not fix the issue; ultimately it turned out that droping this table and re-creating it fixes whatever issue there was for that table; the question is: are there any more; hate to be in droping/creating business on this one...anyway big thanks; BTW you are the ONLY reply on this issue in the whole entire internet; beside mine there are 3 more posts from people with the same issue none had any responses...thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply