Snippet crash

I've got this snippet below. My problem is that most parts of it work like a charm just to utterly fail in the end. All I get is "Executing user-defined query in line 1 ... Failed"

I'm quite sure there is something that I did not notice, but I just cannot find what it is. Could someon cast light on it, please?

Code:

$PROMPT(acon,Alter constraint to?,,,CHECK NOCHECK)$
$PROMPT(con_type,Which type(s) of constraint(s)?,,,'F' 'C' "'F'\,'C'","FOREIGN KEY" "CHECK CONSTRAINT" BOTH)$
$$
DECLARE @tname sysname, @cname SYSNAME, @oname SYSNAME, @duma VARCHAR(4000)
DECLARE @alters TABLE (rizsa VARCHAR(4000))

SET @oname = RIGHT('$OBJECT$', LEN('$OBJECT$') -CHARINDEX('.','$OBJECT$'))

DECLARE cur_alter_constraint CURSOR FAST_FORWARD READ_ONLY FOR
SELECT
ts.[name], cs.[name]
FROM
dbo.sysobjects cs
INNER JOIN
dbo.sysobjects ts
ON ts.id = cs.parent_obj
WHERE cs.xtype in ($con_type$)
AND ts.[name] = @oname

OPEN cur_alter_constraint
print 'cursor open'

FETCH FROM cur_alter_constraint INTO @tname, @cname

WHILE @@FETCH_STATUS = 0
BEGIN
print 'cycle entered'
SET @duma = 'ALTER TABLE ' + @oname + ' $acon$ ' + @cname
INSERT INTO @alters ( rizsa ) VALUES ( @duma )
print 'insert ok'
FETCH FROM cur_alter_constraint INTO @tname, @cname
print 'fetch ok'
END
print 'cycle ends'
CLOSE cur_alter_constraint
DEALLOCATE cur_alter_constraint
print 'cursor closed and deallocated'
print 'and now the surprise!!! where is my resultset??? and why the crash???'
SELECT a.rizsa FROM @alters a
$$

Very nice code snippet!

I tried it on my test system and it returns an error if I set snippet action to "Insert Output Into Code" I get an error. If I set the action to "Display Output Results" it returns valid result But in the latest case I need to manually copy results into the edit window.

Hope this helps.

PS. I will submit a ticket to review this snippet and find out why an error is returned during snippet code execution in "insert" mode

Thanks. I made dozens such snippets to automate the script generation for testing purposes and so far this one is the only one that resists all my efforts to make it work.

Please use "display" workaround for now until we resolve this issue.

By the way, issue tracking number #SA11571

Just in case, it appears that commenting out PRINT commands in the snippet code fixes the issue with SELECT result set output :)

The crash was the reason I put them in :) But yes, I altered it many times after that. I'll try removing them.

UPDATE: Strange. Neither commenting them out nor removing them completely did not resolve the issue. It still crashes. Might it be server version/collation/whatever specific?

Just in case, the server is :
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)

server collation : Hungarian_CI_AS
database collation :Hungarian_CI_AS

I'm unable to reproduce the crashing but I can reproduce that issue with SELECT result set not inserted into the code if any other type of output is coming from the snippet code during code execution. I also added SET NOCOUNT ON to suppress other information messages.

I was told that the private build we sent to you today supports processing of multiple results sets and outputs of PRINT commands and that resolves the issue. Please confirm it is working for you.

Yes, I downloaded it a few minutes ago. Just out of curiosity, I tried inserting SET NOCOUNT ON before installing the private build. Having PRINTs in the snippet crashed it but removing them it worked.

After installing the private build the snippet still works. It does so with or without PRINTs or even having multiple SELECT statements. It returns each one of them. So far this is good, but this build introduced another issue that was not present in the previous one (or at least I don't recall it being so). Let me describe it.

	Code:
	SELECT 1 -- the snippets will be entered on the next line, it is named acon acon

Pressing TAB and making the necessary selections the result is:

	Code:
	SELECT 1 -- the snippets will be entered on the next line ALTER TABLE dbo.belso_muveletek CHECK FK_belso_muveletek__interface

instead of

	Code:
	SELECT 1 -- the snippets will be entered on the next line ALTER TABLE dbo.belso_muveletek CHECK FK_belso_muveletek__interface

There are 4 blank lines inserted before the expected result set.

I'll let the team know about empty lines inserted before the output.

Thank you.

Is there anything else in this build that requires some testing? If not, I'd rather reinstall previous one, I plan to use that snippet extensively in the next few days.

	SysOp wrote:
	I'll let the team know about empty lines inserted before the output.

It seems to be connected to $PROMPT(...)$ macro. Tests indicate that for every $PROMPT$ present in the snippet there are two empty lines inserted.

	Quote:
	Is there anything else in this build

There have been quite a few changes, including a number of bug fixes and a few enhancements. Among bug fixes with high priority – fix for snippet keys mapping in SAS config files after migrated from previous SQL Assistant versions, fix for crash when provided assistance for joins with subqueries. Among enhancements - supporting of multi-server type connections in SSMS, improved error handling in unit tests, column-scope hints, and a number of other relatively minor improvements.

Clarification: the list of changes mentioned in the previous post is based on a report I ran to find changes since publicly released build 5.1.10

The lines breaks that you get inserted into the code are the line breaks that you entered in the snippet code after $PROPMT$ macros. Hope this helps.

That means that if I remove those line breaks, it should not insert them into the final result, right? But will that not cause macro misinterpretation? I mean altering

	Code:
	$PROMPT(a,a)$ $PROMPT(b,b)$

will result

	Code:
	$PROMPT(a,a)$$PROMPT(b,b)$

where $$ will cause trouble, I think. I'll have a try right away.

UPDATE:

I tried with

	Code:
	$PROMPT(a,a)$$PROMPT(b,b)$$PROMPT(g,g)$$PROMPT(f,f)$$PROMPT(k,k)$ SELECT $a$, $b$, $g$, $f$, $k$

which crashed as expected and with

	Code:
	$PROMPT(a,a)$ $PROMPT(b,b)$ $PROMPT(g,g)$ $PROMPT(f,f)$ $PROMPT(k,k)$ SELECT $a$, $b$, $g$, $f$, $k$

which worked (notice the spaces between $-s) and it proved that you were right though it inserted two(?) line breaks instead of one.

Still, I don't feel entering all the PROMPT macros on a single line being an elegant (not to mention easily readable) solution. Are those line breaks by design? If so, what is their purpose?