Issue with .Net's sqlCommand and ALTER PROCEDURE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Issue with .Net's sqlCommand and ALTER PROCEDURE

Author

Topic

JGraterol
Starting Member

5 Posts

Posted - 2011-07-01 : 19:11:45

Hello,

Saw a similar post on the SQL2005 Forum but unfortunately there was no solution because the poster didn't give enough information - will try to avoid that!

Problem: when trying to run an ALTER PROCEDURE using a .net sqlCommand I'm getting an error - "Incorrect syntax near the word 'PROCEDURE'". When I run the exact same script in SQL VS2008 everything runs smoothly.

Code:
The script is as follows:

ALTER PROCEDURE [dbo].[ImportTextFile]
	@TableName varchar(20),
	@SourceFilePath varchar(200), 
	@FormatFilePath varchar(200),
	@RowNumber int
AS
	SET ANSI_WARNINGS OFF
	DECLARE @str_command nvarchar(MAX)
	BEGIN TRANSACTION
		SET @str_command = 'DELETE FROM [' + @TableName + ']'
		EXEC sp_executesql @str_command
		SET @str_command = 'BULK INSERT [' + @TableName + '] FROM ''' + @SourceFilePath + ''' WITH (formatfile = ''' + @FormatFilePath + ''', firstrow = ' + CAST(@RowNumber as nvarchar) + ')'
		EXEC sp_executesql @str_command
	COMMIT TRANSACTION
END

In .net I simply assign the above code to a sqlCommand with the right sqlConnection and do ExecuteNonQuery - that's where it bombs.

Comments: just in case it is of any use, the script above is just to bulk-import a few tables that must be updated daily from text sources; the .net part of the story is to replicate a fix to the script to a rather large number of installs.

Any help would be extremely appreciated, as it would save me countless hours of repetitive work!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-03 : 10:48:37

Don't know if this is the only problem or not, but you have a missing BEGIN in the SQL code, which would cause a syntax error.


ALTER PROCEDURE [dbo].[ImportTextFile]
	@TableName varchar(20),
	@SourceFilePath varchar(200), 
	@FormatFilePath varchar(200),
	@RowNumber int
AS
BEGIN
	SET ANSI_WARNINGS OFF
	DECLARE @str_command nvarchar(MAX)
	BEGIN TRANSACTION
		SET @str_command = 'DELETE FROM [' + @TableName + ']'
		EXEC sp_executesql @str_command
		SET @str_command = 'BULK INSERT [' + @TableName + '] FROM ''' + @SourceFilePath + ''' WITH (formatfile = ''' + @FormatFilePath + ''', firstrow = ' + CAST(@RowNumber as nvarchar) + ')'
		EXEC sp_executesql @str_command
	COMMIT TRANSACTION
END

JGraterol
Starting Member

5 Posts

Posted - 2011-07-10 : 17:14:28

Thanks sunitabeck - you were absolutely right, but when I checked my code I realized I had missed that line when copying the script to my message - it was there indeed.

After searching up and down I haven't been able to find an answer to this one.... suggestions, anyone?

JGraterol
Starting Member

5 Posts

Posted - 2011-07-10 : 17:44:00

The plot thickens...

After posting the previous message I decided to go the brute-force way and do it in two steps - a DROP PROCEDURE followed by a CREATE PROCEDURE, again using .net's sqlCommand. DROP had no problems at all, but CREATE gave me the exact same error as ALTER

I'm starting to suspect a faulty install of .Net framework or something along those lines. Any recommendations?

Thanks...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-10 : 18:18:29

I suspect it may be something that you and I are overlooking rather than any bug in .Net Framework. I created a simple test program in VS 2008 and this seems to work correctly. Can you see if this works for you? You will of course need to replace the connection string appropriately and make sure that the stored procedure exists in your database.

using System;
using System.Data.SqlClient;
using System.Data;

namespace DDLTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection conn = new SqlConnection(
                "Data Source=MyTestServer;Initial Catalog=myDB;Integrated Security=SSPI;"))
            {
                using (SqlCommand cmd = new SqlCommand(@"
                    alter PROCEDURE dbo.TestProcedure AS
                        BEGIN
                           SELECT 1;
                        END
                        ", conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                 }
            }
        }
    }
}

JGraterol
Starting Member

5 Posts

Posted - 2011-07-11 : 21:02:00

Thanks sunitabeck, and sorry for the delay in the answer - been juggling with 5 other projects at the same time

Tried your test and yes, it worked fine. Tried then replacing your sample procedure with mine and it did work, which rules out any problems with .net or any other dll... and leaves me with the prospect of diving into the rest of the updating logic in my (rather old) function.

Thank you very much - you've been a great help!

JGraterol
Starting Member

5 Posts

Posted - 2011-07-11 : 21:15:48

Found the problem (fortunately it didn't take long) and wanted to mention the solution here in case somebody else hits the same wall. There was an unused parameter definition in the sqlCommand that (somehow, don't really care at this point) was confusing .net into throwing that exception. Deleted it, worked like a charm.

Thanks again sunitabeck for pushing me that way!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources