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

Powershell script to execute sql files in a directory

I've got a directory with a bunch of SQL files ( queries ) that I'd like to execute. What's the best way to go about it? I can do it static but that seems a little inefficient if other files are added. invoke-sqlcmd -inputfile "c:\temp\adduserl.sql"…
Kyuzo
  • 21
  • 1
  • 8
0
votes
0 answers

Powershell Invoke-Sqlcmd SQL queries efficiency

I have a script that retrieves metadata of files and uploads them to a SharePoint site. The problem is that there are around a million files and the process is really slow and I was wondering if there is a more efficient way of doing this. The…
Luis
  • 1
  • 1
0
votes
0 answers

Invoke-SQLCMD does not capture verbose output for multiple queries in SQL Script using Powershell

I am trying to get the output of the queries executed in SQLSErver database using sql script through Powershell. I am using Invoke-SQLCMD to get this done with verbose parameter. Its my understanding that using of "verbose" should capture all…
sandy
  • 1
0
votes
1 answer

Azure DevOps pipeline run sql script against Database AAD

I am trying to wrap up my mind around this process. I have a SQL Server in azure. This server has a Azure Active Directory Admin enable using an azure group to authenticate using MFA. Further more, in the same blade, I have enabled the Support only…
Nayden Van
  • 1,133
  • 1
  • 23
  • 70
0
votes
0 answers

Execute the multiple SQL Select, Update commands from SQL file using invoke-DbaQuery

I am exploring powershell to run the following SQL script stored in my machine with name "TestSQLScript.sql" USE TestDB GO SELECT TOP (1000) [DocId] ,[DocumentInfo] FROM [TestDB].[dbo].[Docs] INSERT INTO WorkDocs ( [DocId] …
Avtansh
  • 81
  • 1
  • 1
  • 9
0
votes
1 answer

Invoke-sqlcmd: -Variable @("DB_EMC_PREF=AML","DB_AML_PREF=GBSET") works but -Variable $StrNameVal doesn't (Parameters from XML)

On the MS website it says that the string variable for parameter in invoke-sqlcmd needs to be in Var-'Val' format but it doesn't work for me. (https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps) When I use…
Shishir Tanwar
  • 21
  • 4
0
votes
0 answers

Blank logs on error with Verbose 4 using PowerShell Invoke-Sqlcmd -InputFile

Can someone please help me understand why the second Invoke-SqlCmd doesn't log anything even though it encounters an error? I am using PowerShell. What can be done to overcome this? Log file: (Invoke-Sqlcmd -Query "PRINT 'Hello World!';"…
Shishir Tanwar
  • 21
  • 4
0
votes
0 answers

using invoke-sqlcmd to call .sql script w/o restart after sql installation

In short, I am running a silent install of SQL Server 2016 using a *.ini file. At the end of the installation, I must restart to use any sort of sqlcmd/invoke-sqlcmd to call a SQL file. Does anyone know of a method (preferably PowerShell) that can…
rockerx208
  • 1
  • 1
0
votes
1 answer

Invoke-Sqlcmd : Incorrect syntax near '¦'. Encoding issue

In a PowerShell script, I read a config file and run some SQL queries. This works fine if I copy the code and paste it into PowerShell ISE. But if I run the script by right clicking the file > Run with Powershell, I get an error Invoke-Sqlcmd :…
Ledda
  • 35
  • 6
0
votes
0 answers

Error "Cannot open database requested by the login. Login failed" while running Invoke-sqlcmd

I am not able to figure out why invoke-sqlcmd is not working. It's giving login failed error. This user has sysadmin permissions. When the azure release pipeline runs, if the database does not exist then it restores the new database from the .bak…
MillionQueries
  • 1
0
votes
1 answer

Invoke-Sqlcmd Join variable as table

Is there a way to link a PS variable as a table within the Invoke-Sqlcmd? I've tried LEFT JOIN $psvar as p on T1.ID=P.ID I updated the script by making a DataTable from the advice of @Cpt.Whale. # Define DataTable Columns $tbl = New-Object…
user10917097
0
votes
1 answer

Powershell invoke-sqlcmd Printing The Wrong Output

I have a SQL query that is running as expected however when I try to use it in PowerShell 'Invoke-SqlCmd' module, the output comes out different than when querying the database. I noticed that there are quite a few questions regarding this module…
Eran Mor
  • 85
  • 1
  • 13
0
votes
1 answer

Invoke-SqlCmd to -OutputAs Datatables - then edit the datatable before writing back to SQL db

The following works fine - now I would like to change a date column in the DataTables output before writing back to the sql database: Invoke-Sqlcmd -ServerInstance $instance -Database master -InputFile $scriptPath$script -OutputAs DataTables | …
Bukester
  • 23
  • 6
0
votes
0 answers

Powershell -Command InvokeSqlcmd + Incorrect Synatx error

I'm trying to restore RDS DB using the InvokeSQL command and there is a use case to explicitly call pwsh! The below works a gem Invoke-Sqlcmd -Query 'exec msdb.dbo.rds_restore_database…
user12517555
  • 1
  • 1
0
votes
2 answers

Powershell: Click button on GUI to move to next item in array

I have created a GUI with the purpose of deleting a row from a SQL table. I am now stuck on having the results show one at a time as a button is pressed. Screenshot of GUI: If I have three results come back, it will show the first result in the…
Chris
  • 219
  • 5
  • 18
1 2 3
…
9 10