Has anyone seen this error?

7 Apr 2006 2:30 PM

Henry Stock

I am using SQL Query Analyzer Version SQL 8.00.760.

I have a stored procedure that I built that creates a temporary table inside
of it. When I open the stored procedure for editing, either by
right-clicking the stored procedure in the object browser and choosing edit
or by choosing the option to script the object to a new window as Alter the
following occurs:

1. What should appear as an Alter procedure statement appears as a Create
procedure statement, and
2. The Create Table statement in the body of the stored procedure is changed
to an ALTER Table statement.

I would post the code, but it is large and I am not sure it would be
appropriate anyway.

Does anyone have an idea about what would cause this?

--
Henry Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006

8 Apr 2006 12:17 PM

Erland Sommarskog

Henry Stock (hst***@onproject.com) writes:
Show quote

> I am using SQL Query Analyzer Version SQL 8.00.760.
>
> I have a stored procedure that I built that creates a temporary table
> inside of it. When I open the stored procedure for editing, either by
> right-clicking the stored procedure in the object browser and choosing
> edit or by choosing the option to script the object to a new window as
> Alter the following occurs:
>
> 1. What should appear as an Alter procedure statement appears as a Create
> procedure statement, and
> 2. The Create Table statement in the body of the stored procedure is
> changed to an ALTER Table statement.
>
> I would post the code, but it is large and I am not sure it would be
> appropriate anyway.
>
> Does anyone have an idea about what would cause this?

I think I've heard of this before, although I seem to recall that I
saw this with Enterprise Manager. But it could be that it the bug is
in DMO which performs the actual scripting.

You could try to upgrade the tools to SP4, to see if the bug has been
fixed. Else I guess that you will have to live with it, and change
CREATE and ALTER as needed.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

10 Apr 2006 5:03 AM

Steve Kass

Henry,

This is a bug. Here's a repro:

Run this batch in Query Analyzer:

-- CREATE pALTER
CREATE PROC pALTER (
@i int
) as select 'pALTER'
go

Next, go to Object Browser, right-click on the stored procedure
pALTER and script the procedure to a new window as ALTER.
The following will appear in the new window:

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON
-- ALTER pALTER
CREATE PROC pALTER (
@i int
) as select 'pALTER'

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Note that the comment is changed, not the CREATE PROC. There are other
variations on this scripting bug as well. I assume it's not a high
priority bug
to fix.

Steve Kass
Drew University

Henry Stock wrote:

Show quote

>I am using SQL Query Analyzer Version SQL 8.00.760.
>
>I have a stored procedure that I built that creates a temporary table inside
>of it. When I open the stored procedure for editing, either by
>right-clicking the stored procedure in the object browser and choosing edit
>or by choosing the option to script the object to a new window as Alter the
>following occurs:
>
>1. What should appear as an Alter procedure statement appears as a Create
>procedure statement, and
>2. The Create Table statement in the body of the stored procedure is changed
>to an ALTER Table statement.
>
>I would post the code, but it is large and I am not sure it would be
>appropriate anyway.
>
>Does anyone have an idea about what would cause this?
>
>
>