An Undeclared and Invisible object is Referenced in Error Message

An app is in development that tracts horticultural maintenance activity for NYC Department of Parks. That are many thousands of assets to track.

Data is moved to and from the DB through comma-delimited strings that are "manually" built on the fly.

To build these strings a dynamic cursor is used. Very slick when working, very annoying when not.

This SP uses such a cursor, but when run these errors are returned:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'F'.
Msg 16950, Level 16, State 2, Procedure TaskCompleted_SELECT_CompletedTasks_02, Line 23 [Batch Start Line 0]
The variable '@objcursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Procedure TaskCompleted_SELECT_CompletedTasks_02, Line 40 [Batch Start Line 0]
The variable '@objcursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Procedure TaskCompleted_SELECT_CompletedTasks_02, Line 41 [Batch Start Line 0]
The variable '@objcursor' does not currently have a cursor allocated to it.

Open in new window

"Incorrect syntax near 'F'" is baffling, since there is no text or object named "F" ? Figuring that "F" is unseen, I recreated the SP as a fresh doc and still it persists. Also are the '@objcursor'  a side erect  or a bug?

Any folks have a idea why this is? Thanks for any help.

ALTER PROCEDURE [HTS].[TaskCompleted_SELECT_CompletedTasks_02]
(
	@PropertyMaintenance_ID	as uniqueidentifier
)
AS

declare @objcursor as cursor 
 
declare 
    @vsql       as nvarchar(max)
    ,@vquery    as nvarchar(max)
    ,@id        as int
    ,@value     as varchar(50)
    
DECLARE @list varchar(1000)
DECLARE @listTemp varchar(100)
DECLARE @item varchar(100)

set @vquery = 'select Task_CD from HTS.TaskCompleted WHERE PropertyMaintenance_ID = ' +  
					CONVERT(varchar(36), @PropertyMaintenance_ID) + ' ORDER BY Task_CD ASC'

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 @item

while (@@fetch_status = 0)
begin

SET @listTemp =  @item

SET @list = concat(@list, @listTemp + ',')

 fetch next from @objcursor into @item
end

close @objcursor
deallocate @objcursor
SELECT @list

Open in new window

BobProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Snarf0001Commented:
You need to escape the GUID in @vquery with quotes.
Right now the final @vsql would end up as:

WHERE PropertyMaintenance_ID = A5AAB4C8-A8D6-4184-B719-A4EEF0AFA0B5

And you're probably passing in a GUID with "F" in it, which is where it's failing.  You need that ID wrapped in single quotes so change to this:

set @vquery = 'select Task_CD from HTS.TaskCompleted WHERE PropertyMaintenance_ID = ''' +  
					CONVERT(varchar(36), @PropertyMaintenance_ID) + ''' ORDER BY Task_CD ASC'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just for kicks and giggles, double-click on the 'Incorrect syntax near F' error message, watch the cursor jump to the offending line, then tell us what line that is.
0
BobProgrammerAuthor Commented:
Thanks Guys
0
Mark WillsTopic AdvisorCommented:
Good solution. Well done :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.