Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

You cannot specify this command in an 'execute immediate' context.

2 posts in General Discussion Last posting was on 2012-07-06 20:23:35.0Z
Mark Pare Posted on 2012-07-06 18:26:07.0Z
Sender: 1038.4ff72d01.1804289383@sybase.com
From: Mark Pare
Newsgroups: sybase.public.ase.general
Subject: You cannot specify this command in an 'execute immediate' context.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ff72dbf.104e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 6 Jul 2012 11:26:07 -0700
X-Trace: forums-1-dub 1341599167 172.20.134.41 (6 Jul 2012 11:26:07 -0700)
X-Original-Trace: 6 Jul 2012 11:26:07 -0700, 172.20.134.41
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31220
Article PK: 74109

Hi, I am getting an error running the following SQL in an
EXECUTE statement in a stored procedure:

DECLARE @Error_Code INTEGER
DECLARE @Row_Count INTEGER

TRUNCATE TABLE #results

WHILE 1=1
BEGIN
BEGIN TRANSACTION

SET ROWCOUNT 25000

UPDATE L_CI_150_61
SET CI_Id = t2.CI_Id
FROM L_CI_150_61 t1
INNER JOIN cidb..CI_Objects t2 NOHOLDLOCK
ON t1.Business_Function_Id = t2.CI_Name
AND t2.CI_Class_Id = 150
WHERE t1.CI_Id IS NULL

SELECT @Error_Code = @@error, @Row_Count = @@rowcount

SET ROWCOUNT 0

IF @Error_Code <> 0
BEGIN
ROLLBACK TRANSACTION

INSERT INTO #results
VALUES (@Error_Code)

BREAK
END

COMMIT TRANSACTION

IF @Row_Count < 25000
BEGIN
INSERT INTO #results
VALUES (0)

BREAK
END
END



I get 3 errors:

Number (11736) Severity (15) State (0) Server (NYTIBV6S020)
line 10: You cannot specify this command in an 'execute
immediate' context.
Number (11736) Severity (15) State (0) Server (NYTIBV6S020)
line 28: You cannot specify this command in an 'execute
immediate' context.
Number (11736) Severity (15) State (0) Server (NYTIBV6S020)
line 36: You cannot specify this command in an 'execute
immediate' context.


I'm assuming I can't do a set rowcount command, so that
takes care of 2 of the 3 errors, any idea what the 3rd is?


Adaptive Server Enterprise/12.5.4/EBF 18267 ESD#10
ONE-OFF/P/x86_64/Enterprise
Linux/ase1254/2160/64-bit/OPT/Mon Sep 13 00:37:43 2010


Let me know. Thanks


Rob V Posted on 2012-07-06 20:23:35.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: You cannot specify this command in an 'execute immediate' context.
References: <4ff72dbf.104e.1681692777@sybase.com>
In-Reply-To: <4ff72dbf.104e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ff74947$1@forums-1-dub>
Date: 6 Jul 2012 13:23:35 -0700
X-Trace: forums-1-dub 1341606215 172.20.134.152 (6 Jul 2012 13:23:35 -0700)
X-Original-Trace: 6 Jul 2012 13:23:35 -0700, vip152.sybase.com
Lines: 101
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31221
Article PK: 74110

It's the begin tran, rollback and commit that are not allowed in an
exec-immediate.
This is in fact documented, see http://tinyurl.com/dytb4t7.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 06-Jul-2012 20:26, Mark Pare wrote:
> Hi, I am getting an error running the following SQL in an
> EXECUTE statement in a stored procedure:
>
> DECLARE @Error_Code INTEGER
> DECLARE @Row_Count INTEGER
>
> TRUNCATE TABLE #results
>
> WHILE 1=1
> BEGIN
> BEGIN TRANSACTION
>
> SET ROWCOUNT 25000
>
> UPDATE L_CI_150_61
> SET CI_Id = t2.CI_Id
> FROM L_CI_150_61 t1
> INNER JOIN cidb..CI_Objects t2 NOHOLDLOCK
> ON t1.Business_Function_Id = t2.CI_Name
> AND t2.CI_Class_Id = 150
> WHERE t1.CI_Id IS NULL
>
> SELECT @Error_Code = @@error, @Row_Count = @@rowcount
>
> SET ROWCOUNT 0
>
> IF @Error_Code <> 0
> BEGIN
> ROLLBACK TRANSACTION
>
> INSERT INTO #results
> VALUES (@Error_Code)
>
> BREAK
> END
>
> COMMIT TRANSACTION
>
> IF @Row_Count < 25000
> BEGIN
> INSERT INTO #results
> VALUES (0)
>
> BREAK
> END
> END
>
>
>
> I get 3 errors:
>
> Number (11736) Severity (15) State (0) Server (NYTIBV6S020)
> line 10: You cannot specify this command in an 'execute
> immediate' context.
> Number (11736) Severity (15) State (0) Server (NYTIBV6S020)
> line 28: You cannot specify this command in an 'execute
> immediate' context.
> Number (11736) Severity (15) State (0) Server (NYTIBV6S020)
> line 36: You cannot specify this command in an 'execute
> immediate' context.
>
>
> I'm assuming I can't do a set rowcount command, so that
> takes care of 2 of the 3 errors, any idea what the 3rd is?
>
>
> Adaptive Server Enterprise/12.5.4/EBF 18267 ESD#10
> ONE-OFF/P/x86_64/Enterprise
> Linux/ase1254/2160/64-bit/OPT/Mon Sep 13 00:37:43 2010
>
>
> Let me know. Thanks
>