none
System.Data.SqlClient.SqlCommand and enable quoted identifiers RRS feed

  • Question

  • I am trying to implement the ability to execute several sql scripts within a powershell script using the provider for SQL Server. I am not a developer and was pointed to an existing example that does what I need: www.fwhagen.com/weblog/CategoryView,category,PowerShell.aspx

    The scripts execute fine with sqlcmd using the -I modifier to enable quoted identifiers and I am at a loss as to how one adds this functionality to the above sample.

    Anyone have any pointers they could provide? I would be grateful!

    Thanks.

    Thursday, April 5, 2012 11:48 AM

Answers

  • It turns out that you cannot execute multiple statements in one round, so I have extracted each one out and am looping thruogh them all.

    Thanks a lot for all your assitance Nico!

    Friday, April 6, 2012 11:27 AM

All replies

  • Hi,

    not sure what you mean with -I modifier.

    what is the exact problem?


    Regards, Nico

    Thursday, April 5, 2012 12:02 PM
  • Hi,
    When you manually run the script using sqlcmd.exe, it requires the "-I" modifier to enable quoted identifiers. The sample code will run a script with simple statements but most of mine include schema defintions and stored procedures which cause an issue. I need to enable this in the sample code as well but don't know how.

    Thanks for helping!

    Thursday, April 5, 2012 12:14 PM
  • Hi,

    i was convinced that it was set by default on a connection.

    Anyhow, you could try to get this statement:

    SET QUOTED_IDENTIFIER ON;

    into your file,

    or try and execute it on the same connection, in a separate sqlcommand: (not tested)

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "set quoted_identifier on"
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.EcecuteNonQuery()
    $SqlCmd.Close()


    Regards, Nico

    Thursday, April 5, 2012 12:43 PM
  • Nico,
    I appreciate the help a great deal. I couldn't make the code work so I tried to simply modify the script and use it to test the procedure but it still fails. I presume their is something not applicable to running the more complex sets of commands in the script programmatically with:
    # Setup SQL Command
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    as they do?
    Thursday, April 5, 2012 7:05 PM
  • Ritmo2k,

    is there any error message?

    do you have an idea on which line it fails?


    Regards, Nico

    Friday, April 6, 2012 6:48 AM
  • It turns out that you cannot execute multiple statements in one round, so I have extracted each one out and am looping thruogh them all.

    Thanks a lot for all your assitance Nico!

    Friday, April 6, 2012 11:27 AM