SQL 2012 query via powershell

Question

Post reply

SQL 2012 query via powershell

ging_rojas8990

SSC-Addicted

Points: 463
More actions
September 2, 2014 at 4:35 pm

#310684

Pre-requisites: SQL SMO installed, Powershell ISE 4.0, set-executionpolicy remotesigned and unrestricted in PS.
Goal: I want to run queries from my windows 7 PC via powershell. THe script below is working fine on my SQL server 2012 standard but not working on Windows 7, keep getting network related error. It is always stop at fill($dt). Need some help.
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$serverName = "test-sql\MSSQL"
$databaseName = "mytest"
$query = "Select [Name] from Person"
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)
$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
$dt = New-Object System.Data.DataTable
[void]$dataAdapter.fill($dt)
$dt | Out-GridView

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you connect to the SQL Server using another tool from your Windows 7 PC?

Can you use the same code in a .NET console application (C# or VB) and connect to the SQL Server from your Windows PC?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ging_rojas8990 SSC-Addicted Points: 463 More actions · Answer 2

I don't know other tools , only used SQL POWERSHELL.

Did you see my script OK? It is challenging for SQL connection vis powershell on windows 7.

I cannot find the right answer so far.

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

Including the error you are receiving might help.

that being said, can you telnet to the sql server from your workstation?

telnet test-sql port Replace port with what ever port your MSSQL instance is listening on.

you can also try to connect with the sql provider

Import-module sqlps

cd sql\test-sql\MSSQL

dir

also i am unclear about this statement

THe script below is working fine on my SQL server 2012 standard but not working on Windows 7

do you mean that when you are on the server (test-sql) this script executes fine but when you are on your workstation (windows 7) you get the error when trying to connect to test-sql or are you trying to connect to a sql server on your workstation?

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

ging_rojas8990 SSC-Addicted Points: 463 More actions · Answer 4

Yes, it is working fine on the test-sql server (run this powershell script on the server)but it is not working on the workstation. I did run cd sql\test-sql\MSSQL. I got the same network related error, too

cd : SQL Server PowerShell provider error: Could not connect to 'test-sql\MSSQL'. [Failed to connect to server test-sql\MSSQL. --> A network-related or

instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is

correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]

At line:1 char:1

+ cd sql\test-sql\MSSQL

+ ~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : OpenError: (SQLSERVER:\sql\test-sql\MSSQL:SqlPath) [Set-Location], GenericProviderException

+ FullyQualifiedErrorId : ConnectFailed,Microsoft.PowerShell.Commands.SetLocationCommand

BUT I can see the below listing:

Audits

AvailabilityGroups

BackupDevices

Credentials

CryptographicProviders

Databases

Endpoints

JobServer

Languages

LinkedServers

Logins

Mail

ResourceGovernor

Roles

ServerAuditSpecifications

SystemDataTypes

SystemMessages

Triggers

UserDefinedMessages

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

Check if the SQL Browser service is running on the server.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Lowell SSC Guru Points: 323546 More actions · Answer 6

since it's a named instance, i think it might be a SQL express instance...which has TCP/IP disabled by default.

do you know if it's an express instance?

if it is, first you need to run the SQL Server Configuration Manager and enable TCPIP in two palces, once for 32 bit and another time for 64 bit::

Then you need to make sure SQL allows remote connections: SSMS /right click on the server for properties:

Finally, you need to punch a hole in the windows firewall by going to advanced properties.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ging_rojas8990 SSC-Addicted Points: 463 More actions · Answer 7

I use SQL full version --standard version. I think it might be sql browser disabled by default . For client connection , I has already checked and enabled before.

ging_rojas8990 SSC-Addicted Points: 463 More actions · Answer 8

I can ls and dir all SQL services at PSSQLps prompt but I still failed at this line

void]$dataAdapter.fill($dt)

If I run the same script on the server, it open up a notepad with user name and birthday in it but It cannot open the same way on the desktop (client)

So I am think sqlps didn't recognize " fill" word. BUt I just cannot find anhythinhg to replace 'fill'...

___________________________

$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$serverName = "test-sql\MSSQL"

$databaseName = "mytest"

$query = "Select [Name] from Person?"

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"

$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)

$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter

$dt = New-Object System.Data.DataTable

[void]$dataAdapter.fill($dt)

$dt | Out-GridView

Lowell SSC Guru Points: 323546 More actions · Answer 9

my powershells cript has similarities, butis a little different on that like for .Fill;

i don't have void or[void] anywhere.

i'm also filling a dataset, and not a datatable.

$SQLDataSet = New-Object System.Data.DataSet;

$SqlDataAdapter.Fill($SQLDataSet, "SQLServerInstanceName") | Out-Null;

here's my whole code from some function i use for documentation/exporting to a delimted string:

##################################################################################################

# Query-to-dataset-to-single delimited-string

##################################################################################################

function Get-SQLDocumentation ([string] $SourceServerName,[string] $SourceDatabaseName,[string] $sqlCommand, [int] $IncludeHeaders, [string] $Delimiter)

{

##################################################################################################

# Create SqlConnection object and define connection string

##################################################################################################

$results = New-Object System.Text.StringBuilder

$SQLCon = New-Object System.Data.SqlClient.SqlConnection;

$SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true";

$Delimiter = ""

##################################################################################################

# Create SqlCommand object, define command text, and set the connection

##################################################################################################

write-verbose -Message "$sqlCommand" -verbose

$SQLCmd = New-Object System.Data.SqlClient.SqlCommand;

$SQLCmd.CommandText = $sqlCommand;

$SQLCmd.Connection = $SQLCon;

$SQLCmd.CommandTimeout =0; #wait forever if need be instead of 30 sec

##################################################################################################

# Create SqlDataAdapter object and set the command

##################################################################################################

$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;

$SqlDataAdapter.SelectCommand = $SQLCmd;

##################################################################################################

# Create and fill the DataSet object

##################################################################################################

$SQLDataSet = New-Object System.Data.DataSet;

$SqlDataAdapter.Fill($SQLDataSet, "SQLServerInstanceName") | Out-Null;

##################################################################################################

# Close the connection

##################################################################################################

$SQLCon.close();

##################################################################################################

# Iterate through the dataset to Run the query on Remote Server and send Results as E-Mails

##################################################################################################

if ($IncludeHeaders -ne 0)

{

for ($i=0; $i -lt $SQLDataSet.Tables[0].Columns.Count; $i++)

{

$val = $SQLDataSet.Tables[0].Columns[$i].ColumnName

[void]$results.Append($val + $Delimiter);

}

for ($row = 0; $row -lt $SQLDataSet.tables[0].Rows.Count; $row++)

{

for ($i=0; $i -lt $SQLDataSet.Tables[0].Columns.Count; $i++)

{

#write-host $SQLDataSet.Tables[0].Rows[$row][$i]

#write-host GetString($SQLDataSet.Tables[0].Rows[$row][$i])

$val = GetString($($SQLDataSet.Tables[0].Rows[$row][$i]))

[void]$results.Append( $val + $Delimiter);

}

remove-variable SQLCon

remove-variable SqlDataAdapter

remove-variable SQLDataSet

remove-variable SQLCmd

[GC]::Collect()

return $results.ToString();

}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ging_rojas8990 SSC-Addicted Points: 463 More actions · Answer 10

ging_rojas8990

SSC-Addicted

Points: 463

September 18, 2014 at 11:44 am

#1746502

what is the string for " funtion get-sqldocumentation"

ging_rojas8990 SSC-Addicted Points: 463 More actions · Answer 11

ging_rojas8990

SSC-Addicted

Points: 463

September 18, 2014 at 11:45 am

#1746503

what is the string for " funtion get-sqldocumentation"