System Error Executing CREATE PROCEDURE Script with SqlCommand

  • Thread starter Thread starter Marvin Addison
  • Start date Start date
M

Marvin Addison

I've searched for limitations of the SqlCommand object with regard to
what is valid SQL syntax. Any scripts I have with the GO batch
command don't work unless I remove the GO statement. This is easily
done. However, I used the VS.NET "Generate SQL Script" command to
create a DDL script for a stored procedure I wrote, and it blows up
with a SqlException whose details are simply "System error."

To troubleshoot, I've stripped the stored procedure down to things
I've seen work elsewhere, and I still get the same error. Can someone
please shed some light on what's going on. The text of the stored
procedure is below. Any help would be greatly appreciated.

Thanks,
Marvin
-------------------------------------
-------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[get_next_unique_id]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[get_next_unique_id]
GO

CREATE PROCEDURE get_next_unique_id
@TableName VARCHAR(50)
AS
/******************************************************************************
** File: get_next_unique_id.sql
** Name: get_next_unique_id
** Desc: This procedure returns the next unique id for the primary
key
** field of the given table.
** Auth: Marvin S. Addison
** Date: 4/5/2003
*******************************************************************************/
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

/* Initialize counters if necessary */
DECLARE @record_count INT
SELECT @record_count = COUNT(*) FROM UniqueIds
IF @record_count = 0
BEGIN
DECLARE curUsrTables CURSOR
FOR
SELECT name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id,
N'IsUserTable') = 1
OPEN curUsrTables
DECLARE @name VARCHAR(50)
FETCH NEXT FROM curUsrTables INTO @name
WHILE @@fetch_status = 0
BEGIN
IF @name <> 'UniqueIDs' AND @name <> 'dtproperties'
INSERT INTO UniqueIDs (TableName, UniqueID) VALUES (@name, 0)
FETCH NEXT FROM curUsrTables INTO @name
END
CLOSE curUsrTables
DEALLOCATE curUsrTables
END

UPDATE UniqueIDs SET UniqueID = UniqueID + 1 WHERE
TableName=@TableName
SELECT UniqueID FROM UniqueIDS WHERE TableName=@TableName

COMMIT TRANSACTION

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
Marvin Addison said:
I've searched for limitations of the SqlCommand object with regard to
what is valid SQL syntax. Any scripts I have with the GO batch
command don't work unless I remove the GO statement. This is easily
done. However, I used the VS.NET "Generate SQL Script" command to
create a DDL script for a stored procedure I wrote, and it blows up
with a SqlException whose details are simply "System error."
Click to expand...

You can't just strip out the GO's.
The Go's separate the script into batches. Certain statements must be the
first statement in a batch, and certain pairs of statements can't be in the
same batch.


Each batch must be executed separately.

David
 
David,

Thanks for responding. I tried executing the script as produced from
VS.NET without any modifications as you suggested (with GO statements
intact). This produced the same result - system error. Any other
ideas?

BTW, I have CREATE TABLE scripts that have been auto-generated from
VS.NET that I have used successfully. The only way these work,
however, is to strip out the GO statements. Including the GO
statements causes the same error as I described originally --
SqlException/System error.

Thanks,
Marvin
 
Marvin Addison said:
David,

Thanks for responding. I tried executing the script as produced from
VS.NET without any modifications as you suggested (with GO statements
intact). This produced the same result - system error. Any other
ideas?

BTW, I have CREATE TABLE scripts that have been auto-generated from
VS.NET that I have used successfully. The only way these work,
however, is to strip out the GO statements. Including the GO
statements causes the same error as I described originally --
SqlException/System error.
Click to expand...

Perhaps I wasn't clear. 'GO' is not a valid TSQL statement. It is a marker
to tell the program running the script to stop and run what came before.
The 'GO' is never actually sent to the server.

So

drop table foo
go
create table foo(i int)
go
create procedure insert_foo(@i int)
as
insert into foo(i) values(@i)

Should be executed like


new SqlCommand("drop table foo",con).ExecuteNonQuery()
new SqlCommand("create table foo(i int)",con).ExecuteNonQuery()
new SqlCommand("create procedure insert_foo(@i int) as insert into foo(i)
values @i)",con).ExecuteNonQuery()

David
 
Back
Top