Problem to the line

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

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

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.

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

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.

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?

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

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

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.

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

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.

@objcursor is to hold the output above. why did the original error come out in above?

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.

The same problem is still there.

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 showed the above codes and one line above is having the problem. where to adjust it?

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!

I need the help to point out where to correct the problem, due to the error mentioned.

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.

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.

Without information that has been requested from you, it makes it nearly impossible to answer a question.

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.

what I've got to this thread, is really not the solution to the error.

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

declare @objcursor as cursor 
...

Select allOpen in new window

but why did the error happen, to my codes above?

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

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

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.

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

Yes, @objcursor is never used.

Only it is being declared and used, within the codes above.

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.

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.

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'

Select allOpen 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.

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

Select allOpen in new window

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.

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

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

But @tab_nm is holding value inside.

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

.....

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