using SMO with C#.net to load a .sql file and execute its contents

  • 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...

  • 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.

  • 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]

  • 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