CODEWITHSUNDEEP
sql-serverpowershell
Beck123
Beck123

Reputation: 3

Connecting powershell to SQL Server

I'm quite new to both SQL and Power shell, but I want to run a script which pulls data from a server within SQL - Any advice on where to start?

I tried using this as a starting point, but got no luck as it doesn't like my credentials even though they are correct

Test:

SqlConnection -ServerName 'END-HDSQ02\DEV4' -DatabaseName 'tbl_cert_expiry' -Credential (Get-Credential)

Upvotes: 0

Views: 166

Answers (1)

Stuart Frankish
Stuart Frankish

Reputation: 868

EDIT: Since Powershell V2, you are required to manually load the necessary Snap-Ins; Powershell - Invoke-Sqlcmd unable to run

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

This is what we use to query a SQL View;

[string] $Server= "ServerName"
[string] $Database = "DatabaseName"
[string] $SQLQuery= $("SELECT * FROM schema.TableView order by column")


$data = Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $value1 -Query $SQLQuery

Obviously you'll need to pass the connecting user and password as well, but I've omitted them from my example.

Upvotes: 1

Related Questions