Check out my recent blog about running SQLPS scripts without doing copy/paste.
Click here:<
http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!585.entry>
And more tips to improve your user experience with SQLPS.
Part 3 of 3
Now, here's the sample code were you can connect and data from a SQL server. Using the same connection type from the previous samples. Now, we can run a SQL Query using the 'Select ...from..." and load the data into our PowerShell variable. We accomplished this task using the
SqlClient.SqlDataAdapter class. Let's chek it out:
Read/Display Data from SQL table
|
## - Defining your SQL Server Parameters
$SqlServer = "YourSQLServerName";
$SqlDatabase = "YourDatabaseName";
## - Building the SQL connection string
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDatabase; " `
+ "Integrated Security = True; Connect TimeOut=300";
## - Open connection to SQL Server
$SqlConnection.Open()
## - Define your SQL statement
$SqlQuery = "Select LastName, FirstName, City, State from tblPerson";
## - Section to SQLQuery
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection;
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
## - Building the Dataset collection objects that will store the results of the query
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
## - Close connection to SQL Server
$SqlConnection.Close();
## - Pipe the Dataset collection and display the records on screen
$DataSet.Tables[0] | Select LastName, FirstName, City, State | FT -autosize ;
## - End of Script
|
Please, to test this script you need to build a table to similate the sample. But this code will work, So at the end you will be able to list the records from the table. The rest you could use your imagination.
This is the end of the 3 part shortcut series,
Part 1 of 3
First, If you already have install Visual Studio, and/or SQL Server then you probably the ADO.NET already loaded.
So, here's an example on how to connect to SQL Server using Windows trusted authentication. Following best practices, so no hard-coding UserID and Password needed.
Connecting to SQL Server
|
## - Defining your SQL Server Parameters
$SqlServer = "YourSQLServerName";
$SqlDatabase = "YourDatabaseName";
## - Building the SQL connection string
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDatabase; " `
+ "Integrated Security = True; Connect TimeOut=300";
## Open SQL Coonection
$SqlConnection.Open();
## - Other statements comes here
## - Close connection to SQL Server
$SqlConnection.Close();
## - End of Script
|
This code will get you connected and you only need to additional code to work with your SQL Server.
Please check out part 2 of 3 on this series Next...
SQLPart 2.of 3
This sample PowerShell code get you connected to a SQL Server and run a SQL command "Delete..." using the ".
ExecuteNonQuery". This is all .NET Framework working for you.
Executing a SQL Command
|
## - SQL Database information
## - Prepare queries to be executed
$SqlServer = "YourServerName";
$SqlCatalog = "YourDatabase";
## Creating a Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = '$SqlServer'; Database = '$SqlCatalog'; " `
+ "Integrated Security = True; Connect TimeOut=300";
$SqlConnection.Open();
## Use only clear the table
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandTimeout = 200;
$SqlCmd.CommandText = "Delete from tblSysLogs;";
$SqlCmd.ExecuteNonQuery();
$SqlCmd.Connection.Close();
|
I will post more sample reading a table this week.
Check out the last part 3 of 3 of the series next...
During one of my projects at work I was asked if I can use PowerShell to extract data from our dimension and send it to application in another location. To some extend, I can use T-SQL, but I needed to find out if there were more solution built by someone. Well, I found one, but I'm sure that it for SSAS 2005 and looks promising.
Please check out "Darren Gosbell [MVP]" blog on his "PowerSSAS: a PowerShell provider for Analysis Services"... This is great stuff!
