Stack Overflow
Stack Overflow

Questions tagged [invoke-sqlcmd]

PowerShell cmdlet that runs a script containing statements from the languages (Transact-SQL and XQuery) and commands supported by the SQL Server sqlcmd utility.

For MicroSoft's official documentation on this cmdlet see: https://msdn.microsoft.com/en-GB/library/cc281720.aspx

Alternative options to this command exist. For a discussion of these see: https://dba.stackexchange.com/questions/17435/which-powershell-technique-should-i-use-to-talk-to-sql-server

148 questions
1
vote
0 answers

convert-csv out of memory exception

Currently My code is Invoke-sqlcmd -query "Select * from master.sys.table" -ServerInstance myserver -database -mydb | convert-csv | select -skip 2 | .\7z.exe a -si tgzip $gzipedfilename The problem here is, Invoke-sqlcmd is producing huge resultset…
HimalayanNinja
  • 421
  • 3
  • 11
1
vote
1 answer

Powershell Invoke-sqlcmd2 - Incorrect syntax near 'GO'

I'm getting the syntax error of Incorrect syntax near 'GO'. I tried a few methods and one of them worked. Anyone can explain it to me? Source for invoke-sqlcmd2:…
Nerdynosaur
  • 1,798
  • 9
  • 32
  • 61
1
vote
3 answers

PowerShell invoke-sqlcmd Get-ChildItem : Cannot call method. The provider does not support the use of filters

I have a strange error occurring when using the invoke-sqlcmd to insert rows into a table. The entire script works perfectly if executed one time, but if I run it a second time, it fails with the error message: Get-ChildItem : Cannot call method.…
Frank DiJohn
  • 47
  • 1
  • 8
1
vote
1 answer

Use PSTypeNames to alter Invoke-SqlCmd's data elements

I would like to develop a pattern to easily switch between 'views' of data when executing a PowerShell function that connects to a database. Based on this article, Quick Hits: Set the Default Property Display in PowerShell on Custom Objects, I…
craig
  • 25,664
  • 27
  • 119
  • 205
1
vote
0 answers

Powershell: convert System.Object to plain string

i'm trying to retrieve some information from SQL server and assign it to a variable: $query = "select top 1 * from [DB1].[dbo].[table1] where servername='ABC-CDE-SVR1'" $svrParams = Invoke-Sqlcmd -Query $query -ServerInstance "ADMNSVR" in this…
Nik
  • 161
  • 1
  • 13
1
vote
4 answers

SQL - Automatic results to CSV or Text File

I was wondering if anyone can help. I have a number of queries in SQL (all in separate *.sql files). I wanted to know if there is a way to run these queries automatically or mass run them to be saved to either a csv or txt file? Also, I have come…
Kajan
  • 45
  • 2
  • 9
1
vote
1 answer

run query against multiple databases on different server

I'm working on a PowerShell script to run a query against multiple servers and databases were the idea is to dynamically add server and databases to an array and execute them. Currently I'm stuck at the last part where everything is combined. I can…
AbNadi
  • 69
  • 1
  • 10
0
votes
0 answers

Could not load file or assembly 'file:///C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18209\Microsoft.SqlServer.Management.PSSnapins.dll

i get bellow error when i call Invoke-Sqlcmd, see below full error log PS>TerminatingError(Import-Module): "Could not load file or assembly 'file:///C:\Program…
Luffy One World
  • 1
  • 1
0
votes
1 answer

Powershell and SQL Server : missing most rows when pulling data via Invoke-Sqlcmd

Any help is always appreciated! I have a Powershell script that pulls lists of usernames and assets from a SQL Server data warehouse using Invoke-Sqlcmd. The script pulls around 20 of these lists, but I'm running into a weird issue with one specific…
gunnys
  • 15
  • 2
0
votes
0 answers

InvokeSQL-cmd not working with -Credential in Powershell

My requirement is to run a SQL script on a remote server via Invoke-SqlCmd and extract some data to provide to end user. For this purpose we got a service account made and hence the invoke-sqlcmd has to work using the same service account This is…
Just Another Developer
  • 479
  • 1
  • 4
  • 12
0
votes
0 answers

Pass parameters to SQL Script through Invoke-SQLCmd

I've been provided a .sql script which will create a database. Within the script there is a variable which is used for the Database name. :setvar DatabaseName database-name-1 Im calling the script from a pipeline which calls a PowerShell script…
Syntegrity
  • 11
  • 2
0
votes
1 answer

Is there any option to accept server certificate in powershell Invoke-Sqlcmd?

Steps I have done: installed new widows server 2022 enables Set-ExecutionPolicy installed sql module for powerhell run command: Invoke-Sqlcmd -ServerInstance "SERVER\SQL" -query "USE databsename SELECT GETDATE();" -Username "username" -Password…
buczo
  • 15
  • 5
0
votes
0 answers

Powershell Invoke-Sqlcmd Use result in another query

I'm attempting to use a result returned from Invoke-Sqlcmd $ORDUNIQ as a variable in a second query. However the second $query2 returns 0 records. Substituting the variable for the actual string value '35973189' works fine. I'm assuming that…
ColinA
  • 99
  • 1
  • 9
0
votes
1 answer

Run SQL query using powershell and AAD authentication with an SSO account

I am trying to run a set of queries from Cloud Shell using powershell that require and Azure AD user. I generally use invoke-sqlcmd using the server admin and password but the specific query I want to run require an AD user. I would like to run the…
new_programmer_22
  • 453
  • 4
  • 23
0
votes
0 answers

Powershell invoke-sqlcmd sees hash as System.Byte[]

Currently i am trying to retrieve a query hash from a database. The plan is to query slow queries from the query store and write them to a csv file. To retrieve the data fro the database I am using the following command: $queryResult = Invoke-Sqlcmd…
Rick
  • 1
1 2 3
…
9 10