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
0
votes
0 answers

Message data output from Invoke-Sqlcmd -Verbose differs in format from data written directly to output file

I have a Powershell script that runs some sql files to install tables/stored procedures. Within the PS script I output some text to the log file before each file is run, then perform an Invoke-SqlCmd using -Verbose for the sql file and redirect all…
Dan
  • 1
0
votes
2 answers

SQLCMD gives conversion failed when converting date and/or time from character string

The important part of this question being the use of Sqlcmd. Simply, I'm trying to figure out how to use date/time in my scripts and documentation is light on this. I've tried several date formats, conversion and casting, etc. Anyone have…
Frankie
  • 11,508
  • 5
  • 53
  • 60
0
votes
1 answer

Invoke-Sqlcmd error in Loop, No error once?

If I run this .ps1 script- $secID = Invoke-Sqlcmd -ServerInstance "MyDBServer" -Database "MyDataBase"-Query "SELECT SysID FROM dbo.SecurityLevels WHERE LEVELNAME LIKE '%User%';" Write-Host "MyDataBase" Write-Host $secID.SysID I get the following…
Chase McKee
  • 5
  • 2
0
votes
0 answers

Powershell Invoke-Sqlcmd string output

I am querying data from SQL server. $usersStatus = Invoke-Sqlcmd ` -query "USE Database SELECT [Status],[ID] FROM [tblUsers]" ` -ServerInstance "Sqlserver\sqlinst" | Select-Object ID,Status Getting output from…
buczo
  • 15
  • 5
0
votes
0 answers

Why am I getting an error with my PowerShell Invoke-SQLCMD InputFile?

I am trying to invoke SQLCMD from a PowerShell script but Powershell does not seem to like how I am passing -InputFile to Invoke-Sqlcmd. MY code looks something like this: $files = Get-ChildItem -Path $PSScriptRoot -Recurse | Where-Object { $_.Name…
Zac Guadagno
  • 76
  • 6
0
votes
1 answer

Why doesn't invoke-sqlcmd return ALL the query data

I run the following query in SQL Server 2014: SELECT dp.class_desc, dp.permission_name, dp.state_desc, ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name FROM sys.database_permissions…
boblewis
  • 83
  • 8
0
votes
0 answers

Automatically escape special characters of parameter value passed to sqlcmd

I have a stored procedure that accepts a script and execute it against the database: CREATE PROCEDURE [dbo].[usp_ExecuteScript] @Script nvarchar(MAX) AS EXECUTE sp_executesql @Script; When I call this procedure from command-line with sqlcmd…
Mostafa Armandi
  • 879
  • 1
  • 11
  • 24
0
votes
0 answers

Character conversion problems when extracting XML using invoke-sqlcmd and out-string

I am running into a character conversion problem when trying to export xml data from a SQL server table using Powershell 4.0. I'm using invoke-sqlcmd to run my sql server query, and found that many characters are not being interpreted correctly. …
Chris Hansen
  • 1
0
votes
0 answers

How to force Invoke-sqlcmd to use domain account for authentication?

Dear Stackoverflow friends: I have an issue with a script I got from Microsoft article "https://learn.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management" It is a PowerShell script for calculating every database's allocated…
condescendent
  • 59
  • 6
0
votes
2 answers

Invoke-SQLCMD in powershell

Sorry for trivial question but ... I have an issue while trying to execute SQL command in PowerShell using invoke command The issue starts when I tried to add a string into an SQL command: $sqlNAVresutl = Invoke-SQLCMD -Query 'SELECT * FROM…
Wiktor
  • 581
  • 1
  • 12
  • 23
0
votes
0 answers

Powershell invoke-sqlcmd "select query_hash"

I'm trying to build a small tool in Powershell to select some data. One of the things I would like to get out is the query_hash, but when I do this with Powershell its not returning the same value as in SQL Management Studio. In SQL Management…
superset
  • 71
  • 1
  • 2
0
votes
2 answers

How to save result from SQL query in Powershell string variable?

I using invoke-sqlcmd to query a database on MSSMS. This works no problem. I have a loop that runs the query X amount of times and the query returns a keycode eg. TGHDWS4. I need this keycode as a string in powershell. I then want to add the string…
Cormac
  • 25
  • 8
0
votes
1 answer

Outputting large XML file from SQL with Powershell

I have a Powershell script that calls a stored procedure in SQL server and outputs the resulting XML directly to a file. It works for a smaller test file but falls down with the full size (~1.5gb file). The stored procedure works fine. I can call it…
user2980115
  • 45
  • 2
  • 9
0
votes
1 answer

invoke-sqlcmd via function throwing positional parameter error

I'm trying to use a function to run a query but it does not seem to accept my server instance and gives an error: "Run-SqlCommand : A positional parameter cannot be found that accepts argument 'mydbservername'." When I execute the same…
Josh Nelson
  • 49
  • 2
  • 7
0
votes
1 answer

Powershell script to create security groups in Azure Active Directory to manage access to Azure SQL

Below is a script that creates groups in Azure AD and then assigns SQL roles to the groups. The script has a dependency on ODBC 17 and sqlcmd utility version 15 and this makes it a bit hard to use in an Azure devops pipeline. I was wondering if…
Dries Venter
  • 31
  • 2
1 2 3
…
9 10