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

PowerShell Help
1

Hi,

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
$SqlAdapter.Fill($DataSet)|out-null
$Userpermissions = "$_" | out-file C:\users\outputuser.sql

Could someone point me in right direction ? thanks in advance

2

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.

3

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.

4

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…
Thanks

5

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.

6

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

7

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

Example

create user Test for login Test
GRANT CONNECT TO 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.

8

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

9

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

Thanks