Avatar of Peter Chan
Peter Chan
Flag for Hong Kong asked on

Problem to the line

Hi,
How to correct the last exec line below
declare @objcursor as cursor 
declare
	@iteStatus nvarchar(2),
	@ite2tatus nvarchar(2),
	@vsql nvarchar(max),
	@vquery nvarchar(max),
	@unit nvarchar(10),
	@featNo nvarchar(10)
	
	set @vquery = 'select c.iteStatus,c.ite2tatus,c.unit,c.featNo from '+@tab_nm+' i,obj2 a,objs c where isnull(i.EVENT_TYPE,'''') '
		+'in(''A'',''D'',''M'') and isnull(i.UPDATE_STATUS,'''')=''R'' and i.obj_UNIT=a.Unit and i.obj2No=a.obj2No '
		+'and a.UNIT=c.Unit and a.featNo=c.featNo order by i.EVENT_TIMESTAMP'
	set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'
 
	exec sys.sp_executesql
		@vsql
		,N'@cursor cursor output'
		,@objcursor output
		...

Open in new window


due to this error?
Msg 16950, Level 16, State 2, Procedure p_upd_cust_status, Line 22
The variable '@objcursor' does not currently have a cursor allocated to it.

Open in new window

Microsoft SQL ServerDatabasesMicrosoft Legacy OS

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
arnold

Your use of the sp does not seem correct.

Ref MS example.

Your objcursor is declared but is effectively null.

http://technet.microsoft.com/en-us/library/ms175170(v=SQL.105).aspx
Peter Chan

ASKER
Can you please advise what to adjust to the codes? Thanks
arnold

The cursor (objcursor) has to be defined outside the exec sp_

you seem to be defining objcursor as cursor then dynamically declaring @cursor as a cursor thinking the script will treat @objcursor as though it is @cursor

What is it you want to do with your script.
you have a dynamic table name using a variable not included in the snippet you post.

The issue in the snippet and reference, your objcursor cursor for
select something from sometable where some_condition
then you would execute the SP and as the MS example illustrates, you need to tell it what it needs to do with the elements in the cursor tree.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Peter Chan

ASKER
Can you show the way to adjust the codes above? Thanks.
arnold

It is incomplete and I do not understand what it is you are trying to do.

Is this a Stored procedure that will be called or is this is a portion of a much larger stored procedure?

The MS link explains that you can not use a cursor from within the sql statement being executed which is what your variables vquery  and vsql seem to do.
Peter Chan

ASKER
It is a part of one small SP. Like what you said, then what change should be applied to the codes? Can you show with codes instead of talk?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

What is the purpose of what it is you are trying to do.

The cursor that you define is the loop through which the sp_executesql

Refer to the MS technical article that uses a cursor with fetch next near the end.

you have vquery that defines a table name as @tab_nm
where and when is this variable updated?  

If you have the data reliant upon data in a  table that you query
declare the objcursor cursor for
select someparameter from some table where some condition applies.

define your vquery, etc.
fetch next @objcursor .. INTO @tab_nm ' This is where the table name is set
while (@@FETCH) ' meaning as long as the cursor fetch was successfull ..
begin
set @vquery with the new data
exec sp_executesql @vquery......;
fetch next @objcursor .... into @tab_nm
end ' close the while loop of going through the cursor of the sql query

The issue with the portion of your code is that you are trying to define a cursor and execute it within the dynamic portion of the sp_executesql @vsql

Because it is unclear to me what it is you are trying to do as well as the information is partial, there is no way for me to determine whether the error you are getting is because of incorrect syntax in the sp_executesql ........ statement, or one of the variables that you define is malformed.
i.e. when you define vquery if @tab_nm is undefined, your select query is malformed.
i.e. vquery = 'select * from where condition'
this is then compounded in your vsql definition such that @cursor is undefined because the creation of the @cursor cursor fails ........
not sure your declaration of cursor @objcursor as cursor definition/aliasing is valid
Peter Chan

ASKER
I need the exact way to correct the problem of the codes. how?
arnold

For me, Information is needed on what it is you want done, and the complete SP needs to be posted.

Could you layout in simple terms what it is you want done and then logically explain what it is this SP is doing?

i.e. I want to move bricks that are in a pile in the corner (SW of the house) to Where they are being laid in the front of the house, which is NNE.
I take a utiltity cart
wheel it to the SW corner of the house.
take a couple of bricks at a time and load them into the cart
when cart has enough bricks and not too heavy, I wheel the cart around the house to the NNE
Unload the bricks
return to the SW corner and repeat.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Peter Chan

ASKER
I need the details to point out where to correct the problem, as the line in problem has been shown above.
arnold

Declare @objcursor as cursor not set
set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'
,@objcursor output
where is the @objcursor defined?

you are trying to open a cursor within the sp_executesql what are you expecting  

msdn.microsoft.com/en-us/library/ms188001.aspx
from the above,
[ OUT | OUTPUT ]

    Indicates that the parameter is an output parameter. text, ntext, and image parameters can be used as OUTPUT parameters, unless the procedure is a common language runtime (CLR) procedure. An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.

Do you know/resolved, "Msg 16950, Level 16, State 2, Procedure p_upd_cust_status, Line 22"

Listen, you would not bring a few components from an appliance to a repair shop and expect that upon return the appliance will start working unless you already determined and confirmed that these components are at fault and the repair shop sold you those parts. As well as you installed it back correctly.
Peter Chan

ASKER
@objcursor is to hold the output above. why did the original error come out in above?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

Have you resolved the first line Error?

At times an error early on can cascade and impact another, resolve the first and see if the second resolves swell.
Peter Chan

ASKER
The same problem is still there.
arnold

without understanding what it is you are trying to do and seeing the logic behind your approach, I am unable to suggest further attempts to presolve.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Peter Chan

ASKER
I showed the above codes and one line above is having the problem. where to adjust it?
arnold

http://technet.microsoft.com/en-US/library/ms175498(v=SQL.105).aspx

Describes how to

I can not suggest an approach to fix your issue because I do not u derstandinisthat nor gave you provided the information asked for on what it is you are trying to do!
Peter Chan

ASKER
I need the help to point out where to correct the problem, due to the error mentioned.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

Line 1 points to an error in a stored procedure that is not included in your posted question or any subsequent responses.
Line 2 states that your @objcursor which is specified as a cursor place holder for OUTPUT of the executesql is not allocated anywhere in the portion you posted.

Look at the technet link.
Note how it declares the stored procedure which is then referred to by the batch listed.
Note within that example the output declared cursor is pointed/referencing a cursor from the SP.
Peter Chan

ASKER
I've requested that this question be deleted for the following reason:

It is wasting my time, as what I've got on this thread, is not the real solution to the problem.
arnold

Without information that has been requested from you, it makes it nearly impossible to answer a question.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
arnold

Not sure in how many different ways at different times can the same thing be said, the @objcursor is not allocated, Look at the technet example, note that this type of setup has two components a stored procedure and then a batch process.
Peter Chan

ASKER
what I've got to this thread, is really not the solution to the error.
ASKER CERTIFIED SOLUTION
Pavel Celba

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Peter Chan

ASKER
Many thanks pcelba.
Your example is working fine.
In my codes, I also have declared @objcursor, like

declare @objcursor as cursor 
...

Open in new window


but why did the error happen, to my codes above?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

I think the issue is that your vquery and vsql could explain the issue by being malformed.
comment out the sp_executesql
but print as the message the contents of @vquery and @vsql

In the example provided the mytable is explicitly declared/populated.
the vquery is then uses this defined table while your vquery relied on another variable @tab_nm which can not be found in the snippet you posted. replace @tab_nm with the actual table name , and your Sp should not through this error presumably somewhere down the line you have the walk through the cursor to get the output data out of @objcursor
Peter Chan

ASKER
The error message is talking about @objcursor and never claimed anything wrong to @tab_nm.
arnold

Double check what the output of your vquery generates. Does it return results?
In your code, does the error reference actually point to the @objcursor output or to the first instance down the line where you go through the @objcursor as illustrated in the example pcelba provided.

Comment out the executesql
Use select @vquery to see what the query is
And select @vsql to see what it will have

In a car analogy, you insert a key and the car does not start.
First thing to determine the cause is to make sure the key turns in the lock.
Then, if you hear the engine turn over, this means the battery is ok.
Etc.

Try adding a condition in the vquery instead of 'ccccc' as 'abcdefg'
And see if you get an allocated error on the fetch next lines.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
arnold

Are you testing if @objcursor is not null before trying to extract data from it?
Peter Chan

ASKER
Yes, @objcursor is never used.
Peter Chan

ASKER
Only it is being declared and used, within the codes above.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

Try altering the where clause in the example pcelba provided such that it does not matche any dataset and see what you get as a response.

Then try replacing the mytesttable and see Aht error if any you get.
Pavel Celba

I cannot explain error message "The variable '@objcursor' does not currently have a cursor allocated to it." without any previous error...

The problem is in the @vsql code which has something wrong inside.

I agree you should print out @vsql, try to simplify it (remove unnecessary parts) and test simplified versions in SSMS.
arnold

set @vquery = 'select c.iteStatus,c.ite2tatus,c.unit,c.featNo from '+@tab_nm+' i,obj2 a,objs c where isnull(i.EVENT_TYPE,'''') '
		+'in(''A'',''D'',''M'') and isnull(i.UPDATE_STATUS,'''')=''R'' and i.obj_UNIT=a.Unit and i.obj2No=a.obj2No '
		+'and a.UNIT=c.Unit and a.featNo=c.featNo order by i.EVENT_TIMESTAMP'

Open in new window


you have sequential single quotes (') which cancel each other out versus being interpreted as place holders. the effect the vquery variable looks like this:
replace all outer single quotes with double quotes.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Pavel Celba

I would say two quotes are interpreted as one single quote on output:
select c.iteStatus,c.ite2tatus,c.unit,c.featNo 
  from <<tab_nm>> i,obj2 a,objs c 
 where isnull(i.EVENT_TYPE,'') in('A','D','M') 
   and isnull(i.UPDATE_STATUS,'')='R'
   and i.obj_UNIT=a.Unit 
   and i.obj2No=a.obj2No 
   and a.UNIT=c.Unit 
   and a.featNo=c.featNo 
 order by i.EVENT_TIMESTAMP

Open in new window

arnold

Your issue is that @tab_nm is not defined.

Here is the complete error where I replaced mytesttable with #mytesttable1 in the vquery assignment.


Msg 2714, Level 16, State 6, Line 3
There is already an object named '#MyTestTable' in the database.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 208, Level 16, State 0, Line 1
Invalid object name '#MyTestTable1'.
Msg 16950, Level 16, State 2, Line 13
The variable '@objcursor' does not currently have a cursor allocated to it.
Pavel Celba

Do you mean all errors except the last one are rolled out and HuaMinChen did not mention that?

It is just one possibility. SQL command can fail from several other reasons...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

The info posted is incomplete, since @tab_nm is not defined anywhere posted, the query also combines data from two additional tables, the relationship between them is unclear.
as long as the select works even if it returns an empty set, there will be no error.
I find it further peculiar in the last response the only time @objcursor is referenced is as a designated output in the executesql ..........
Peter Chan

ASKER
But @tab_nm is holding value inside.
arnold

If you get no other error besides the @objcursor not allocated, that would suggest that there is something wrong with the query in vquery.

What is the result if you run the query manually?

Your query uses whatever table you have in @tab_nm and obj2 and objs

.....
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Pavel Celba

OK, for whatever reason we did not have all necessary info so the only possibility was to show similar working solution...