Solved

Problem to the line

Posted on 2014-11-13
42
159 Views
Last Modified: 2014-11-21
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

0
Comment
Question by:HuaMinChen
  • 20
  • 16
  • 5
42 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 40441939
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
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40441951
Can you please advise what to adjust to the codes? Thanks
0
 
LVL 76

Expert Comment

by:arnold
ID: 40443463
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40443978
Can you show the way to adjust the codes above? Thanks.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40443983
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40444039
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?
0
 
LVL 76

Expert Comment

by:arnold
ID: 40444422
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
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40444456
I need the exact way to correct the problem of the codes. how?
0
 
LVL 76

Expert Comment

by:arnold
ID: 40444618
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40445360
I need the details to point out where to correct the problem, as the line in problem has been shown above.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40445678
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40446484
@objcursor is to hold the output above. why did the original error come out in above?
0
 
LVL 76

Expert Comment

by:arnold
ID: 40446570
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40446594
The same problem is still there.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40447436
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40448880
I showed the above codes and one line above is having the problem. where to adjust it?
0
 
LVL 76

Expert Comment

by:arnold
ID: 40449115
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!
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40449123
I need the help to point out where to correct the problem, due to the error mentioned.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40449185
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40450377
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:arnold
ID: 40450176
Without information that has been requested from you, it makes it nearly impossible to answer a question.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40450378
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.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40451375
what I've got to this thread, is really not the solution to the error.
0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 40452092
Does your code belong to p_upd_cust_status SP?
Does any other error appear prior to the one in your question?
Maybe SQL Server version is your problem...
Try following code in SSMS query window:
set quoted_identifier off

create table MyTestTable (someID int, someCode char(10))
GO
INSERT INTO MyTestTable VALUES (1, 'aaaaa')
INSERT INTO MyTestTable VALUES (2, 'bbbbb')
INSERT INTO MyTestTable VALUES (3, 'ccccc')
INSERT INTO MyTestTable VALUES (4, 'ccccc')
GO

declare @objcursor as cursor 
declare
	@vsql nvarchar(max),
	@vquery nvarchar(max)

declare @a1 int, @a3 char(12)

set @vquery = "select someID, someCode FROM MyTestTable WHERE someCode = 'ccccc'"

set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'
 
exec sys.sp_executesql
	@vsql
	,N'@cursor cursor output'
	,@objcursor output

fetch next FROM @objcursor INTO @a1, @a3
print @a1
print @a3
fetch next FROM @objcursor INTO @a1, @a3
print @a1
print @a3

close @objcursor
deallocate @objcursor

Open in new window

0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40454250
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?
0
 
LVL 76

Expert Comment

by:arnold
ID: 40454308
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
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40454343
The error message is talking about @objcursor and never claimed anything wrong to @tab_nm.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40454366
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.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40454367
Are you testing if @objcursor is not null before trying to extract data from it?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40454381
Yes, @objcursor is never used.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40454385
Only it is being declared and used, within the codes above.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40454436
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40454820
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.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40455383
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40455675
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

0
 
LVL 76

Expert Comment

by:arnold
ID: 40455871
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40456032
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...
0
 
LVL 76

Expert Comment

by:arnold
ID: 40456179
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 ..........
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40456587
But @tab_nm is holding value inside.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40456597
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

.....
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40457121
OK, for whatever reason we did not have all necessary info so the only possibility was to show similar working solution...
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now