June 3, 2009 at 3:44 am
Hi, Im writing a C#.net database service to manage upgrades to my SQL server 2005 database.
most of the articles ive read suggest using SMO.
I have a .sql script file which contains some basic drop stored procedure commands; this code executes fine in SQL Server Management Studio, but when executing it in my application it fails and throws the exception: An exception occurred while executing a Transact-SQL statement or batch.
This is the code im using in C#.Net, several sites suggest doing it this way:
try
{
FileInfo file = new FileInfo(@fileName);
string script = file.OpenText().ReadToEnd();
Server server = new Server();
server.ConnectionContext.ConnectionString = sqlConnectionString;
server.ConnectionContext.ExecuteNonQuery(script);
result = true;
aWriter.WriteToLogFile("ReadScript successful execution for file: " + file.Name);
}
catch (Exception er)
{
result = false;
aWriter.WriteToLogFile("ReadScript exception: "+er.Message);
}
Even if i use the ExecuteNonQuery(script, Microsoft.SqlServer.Management.Common.ExecutionTypes.QuotedIdentifierOn). I still get the exception. My connection the the database is also fine.
----------------------------------------------------------------------------
The script .sql file contains the exact code below which runs ok in SQL Server:
USE DatabaseName
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_TemplateRoster]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_TemplateRoster]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_TemplateRosterDaily]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_TemplateRosterDaily]
GO
exec Sp_AddPatchNumber 1
--------------------------------------------------------------------------
Any ideas of where im going wrong...? I was under the impression that having GO's does not matter in SMO's case, I have even taken the GO's out and get the same error... my connection string tells smo what database to connect to, so im confused...
June 3, 2009 at 8:21 am
I'd check the security. Who are you connecting as?
How about adding another Catch block to handle a SQLClient error? Perhaps you will get a more specific error message.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 3, 2009 at 8:34 am
I agree with Jack, the outer exception is pretty generic in this case, can you get the inner exception also? As Jack suggests, you may need to handle it explicitly as a SQLClient error to get the inner exception (not sure).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 3, 2009 at 1:48 pm
thanks, yes that was it. Security of the user connection, I changed the account to dbo and it worked. Silly me...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply