0

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)
jarlh
  • 42,561
  • 8
  • 45
  • 63
Beck123
  • 3
  • 5

1 Answers1

1

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.

Stuart Frankish
  • 818
  • 1
  • 11
  • 27
  • Hello, okay I see what you've done, but when i enter the data I'm looking for, it says that the 'The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program'? – Beck123 Dec 09 '19 at 11:44
  • @Beck123 You need to load in the required snapins. Check here => https://stackoverflow.com/questions/42762989/invoke-sqlcmd-unable-to-run – Stuart Frankish Dec 09 '19 at 11:45
  • I installed the mssql to visual studio code - but it looks like that would only help me if I want to do this through SQL server... however I need to do it through power shell with power shell remotely within it - adding those 'add-PSSnapin' just brings up an error saying that its recognized either – Beck123 Dec 09 '19 at 11:55
  • Solution was solved, apparently my modules were not saving themselves in the place power shell was looking at! thanks everyone :) – Beck123 Dec 10 '19 at 12:46