Execute .sql file( sql server script ) and store the output in .sql file

This topic contains 8 replies, has 3 voices, and was last updated by  Robin16 4 years, 7 months ago.

  • Author
  • #10613



    I'm trying to run the sql script .sql file from powershell and save the result into .sql file.
    Overview : SQL database restore requires a user and permission backup pre-restore and once the restore is complete we need to execute the output( users permissions backup which we did pre-restore ) on the database.

    here's my script and when i execute i dont see any output

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    $MyQuery = get-content "C:\Users\Security.sql";
    $SqlConnection.ConnectionString = "Server = DBATest; Database = Testdb; Integrated Security = True"
    $SqlCmd.CommandText = $MyQuery;
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $Userpermissions = "$_" | out-file C:\users\outputuser.sql

    Could someone point me in right direction ? thanks in advance

  • #10614

    Don Jones

    I think the basic code looks okay. That said, I'm not sure what you're doing with $_. Keep in mind, $_ can only be used in specific contexts where PowerShell is looking for it, and where you're using it, I'm not sure $_ would contain anything meaningful. What you've got is $DataSet, which has your data in it. I'm not sure if you can just pipe $Dataset to Out-File... I don't know if the DataSet object has a ToString() method that will do what you're after. I suppose you could try it. But $_ isn't going to be useful right there.

    In order to do "$DataSet | Out-File" the DataSet class has to implement a meaningful object hierarchy, and it doesn't really. I'm looking at the class docs at http://msdn.microsoft.com/en-us/library/system.data.dataset_methods.aspx, and the DataSet is mainly meant to give you access to a DataReader, write the data in various XML representations, etc. DataSets aren't designed to go straight to a text file.

    This also assumes that your query file contains instructions which will output one and only one table of results. Anything else and the DataSet might not populate in a way that will make it useful for what you're doing.

    But anyway, the reason you're not seeing any output is that $_ doesn't contain anything. It wouldn't, because it isn't being used in a place where PowerShell is prepared to do anything with it. You'll have to do something with your $DataSet variable, although I'm not sure what that's physically containing, so I can't offer advice on what to do with it.

  • #10615

    Don Jones

    It might actually be easier to just use Sqlcmd.exe to execute your SQL file, and redirect the results to another text file. The SqlClient class isn't precisely intended to do what you're doing with it.

  • #10622


    Thank you very much Don for your detailed response. I used $DataSet as variable instead of $_ , i can see the below content in outputuser.sql file.
    I have used this sql connection in my whole script to connect to sql database , i'm not sure how to use sqlcmd.exe

    RemotingFormat : Xml
    SchemaSerializationMode : IncludeSchema
    CaseSensitive : False
    DefaultViewManager : {System.Data.DataViewManagerListItemTypeDescriptor}
    EnforceConstraints : True
    DataSetName : NewDataSet
    Namespace :
    Prefix :
    ExtendedProperties : {}
    HasErrors : False
    IsInitialized : True
    Locale : en-US
    Site :
    Relations : {}
    Tables : {}
    Container :
    DesignMode : False

    Not sure how to proceed with this...Please let me know your thoughts...

  • #10623

    Don Jones

    Well, you'll either need to look up the docs on Sqlcmd and learn how to use it, or look up the DataSet class and learn how to use that. I'm not sure what your query contains, what the dataset contains, or what you want as an end result, so I'm not going to be much help in terms of teaching. If it were me, I think I'd find Sqlcmd – which does not involve programming – easier to look up and learn. Using the dataset is going to get programmer-y.

  • #10859

    Kirt Carson

    You are one step away... Assuming your query returns a single column or array of permissions.

    $DataSet.Tables[0] | out-file C:\users\outputuser.sql
    instead of
    $Userpermissions = “$_” | out-file C:\users\outputuser.sql

  • #10887


    Thanks Kirt for the response...query generates a list of all user permissions in a database not a single column...


    create user Test for login Test

    i tried using $DataSet.Tables[0] | out-file C:\users\outputuser.sql
    It was generating a .sql file but the file is empty no permissions in it.
    Please let me know your suggestions.

  • #11002

    Kirt Carson

    Robin: I use notepad to write my powershell for the last few years. I do use MS SQL manager to create tables, test queries. Your code is simple so if you have an empty outputuser.sql, then your query is returning an empty set. Use something to see if your query is good. Google $DataSet.Tables[0].Rows[0][0] or Cols[0][0] or the like. Good luck

  • #11026


    Thanks Kirt, but i dont see any useful information in internet regarding this issue.


You must be logged in to reply to this topic.