All Questions
Tagged with sqlcmd powershell
117 questions
0
votes
1
answer
22
views
I am writing a script to run in a terminal using sqlcmd. There are variables my script expects but what if one or more of them is not in -v
I have this script (my_script.sql):
SET NOCOUNT ON;
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
SET @SchemaName = '$(SchemaName)';
SET @TableName = '$(TableName)';
IF @...
- 99
0
votes
3
answers
167
views
Passing an array as a parameter from PowerShell to an SQL script
I'm creating a quite complex SQL script to delete from a database a bunch of records. Because I have to do a lot of operations, I want to save the IDs of the records I have to delete in a temporary ...
- 6,004
-1
votes
1
answer
94
views
SQL Server using Powershell - Error: the 'Invoke-SqlCmd' command was found in the module 'SQLPS', but the module could not be loaded
I'm encountering an error when trying to create a database and also to execute other queries in SQL Server using PowerShell's Invoke-SqlCmd command.
Environment:
Windows Version: 10.0.19045
...
- 11
0
votes
0
answers
34
views
How do I get the LogicalName from query and store into variables using sqlcmd
I want to write a PowerShell script to automatically restore database backups when I need it. But I'm having some trouble with it because when I restore it, I don't restore it to its original name, ...
- 317
0
votes
1
answer
591
views
Unable to connect to Azure SQL Database using Invoke-Sqlcmd in PowerShell
I'm currently facing an issue with connecting to an Azure SQL Database using PowerShell's Invoke-Sqlcmd cmdlet. I am able to successfully connect to the database using sqlcmd (Version 18.2.0001.1 ...
- 15
2
votes
1
answer
132
views
Export-Excel Powershell Module cannot tranfer all columns - formatting
I am doing MySql queries pipeline and using results to transfer data do storage account.
For DB query and transfer data to .txt file, I use this command:
sqlcmd -S $(Server_prod) -i "G:\...
0
votes
0
answers
66
views
invoke-command with invoke-sql -> Problems with Instance Variable
I am writing a script for reading remote SQL instances.
I have a problem with the transfer of the ServerInstance Variable.
I just can't find the error.
Does anyone have an idea?
This Command is ...
- 422
-1
votes
2
answers
84
views
How do I get just the value of the field name?
I'm trying to use sqlcmd utility and the query is working I'm just wondering how I can get just the value without fieldname and those dashes?
$res = sqlcmd -Q @"
:Connect .
use [...
- 15.4k
0
votes
1
answer
75
views
Unable to connect to database using SqlCmd in a Vagrant provision script
I am using Vagrant to create a reproducible environment for work. In my vagrantfile I have the following:
config.vm.provision :shell, path: "./dependencies.ps1"
config.vm.provision :shell, ...
- 996
1
vote
1
answer
69
views
Looping through till you receive correct validation input Powershell
I would like the code to keep looping through till it recieves the right validation input. If the user enters yes, the the script can continue to execute. if the user enters no, it will ask the user ...
- 43
0
votes
3
answers
468
views
Remove Dashes from Row 2 on file
I have a bat file that uses SQLCMD to call an SQL Statement
My SQL statement is fine
However, under the header row, in Row 2, I get a load of dashes like so,
SKU,Name,Description
---,----,-----------
...
0
votes
1
answer
729
views
SQLCMD Version Not Changing/Updating
A windows 2019 server we use to run batch jobs from has been having issues lately. I think I've narrowed it down to sqlcmd being an old version 11. I installed the newest version 15 Microsoft Command ...
- 101
0
votes
0
answers
437
views
I'm getting this error "Invoke-Expression : Cannot bind argument to parameter 'Command' because it is null. "
I have tried below code and I'm getting this error message. But I'm getting the output generated, is there any way to avoid the error?
Invoke-expression (sqlcmd -S Servername -d DBName -i "D:\...
- 1
0
votes
1
answer
573
views
Prevent variable and special character substitution in variable
I'm loading a regex from a file into a database using a stored procedure, like this:
$stuff = Get-Content -Path '.\data.txt' -Raw
Invoke-Sqlcmd -Query "Exec dbo.loadStuff @stuff='$stuff'" -...
- 1
0
votes
1
answer
139
views
PowerShell 5.1 and sqlcmd.exe - Is there a way to pass arguments to a string query?
Environment:
Windows Server 2016 Standard
Windows 10 Pro
PowerShell 5.1
Is there a way to pass arguments to a string query (instead of an -i input.sql)? I don't have Invoke-Sqlcmd available to me ...
- 15.4k
0
votes
1
answer
977
views
What is SQLCMD in PowerShell?
I have been trying to develop a function that handles all the SQLCMD requirements for a complex process. This includes mixed connection modes (trusted or username/password), different databases, ...
- 119
0
votes
0
answers
215
views
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 ...
0
votes
2
answers
594
views
Sqlcmd : invalid argument path string Powershell
I've tried many solutions but I can't fix my problem.
I want to execute this PowerShell command :
sqlcmd -S localhost\SQLEXPRESS -U sa -P mypassword
-v db="school" bakfile="C:\...
- 132
1
vote
1
answer
2k
views
Iterate through each database on an instance using ForEach-Object in PowerShell does not work
I am new to PowerShell.
Trying to understand how to use ForEach-Object command to iterate through each database on a server.
However it only bring master database multiple times and doesn't seems to ...
- 4,388
0
votes
1
answer
314
views
SQLCMD--Using the :r Command I have a text file with multiple outputs,is there a way to add a delimiter between outputs or an easy way to split output
I'm using the :r command to running multiple sql scripts in the same session. Each script outputs multiple rows and columns. So by time the script is complete, I am left with a text file with multiple ...
- 1,842
0
votes
0
answers
74
views
SQLCMD powershell script with data taken from a file unable to output the expected number
I have a csv file which has one column named PurchaseorderID and some numbers below it. I have a PS script which is supposed to fetch each row from that file and complete a SQL query and run it. ...
1
vote
2
answers
11k
views
Difference between Sqlcmd and Invoke-Sqlcmd
I am trying to learn Sqlcmd utility in PowerShell to run SQL queries, specifically for database administration.
Using Version 15.0.1300.359 NT
But I got confused, what is the difference between Sqlcmd ...
- 4,388
0
votes
1
answer
419
views
SQLCMD: Pass parameter database startig with number to script using powershell
From powershell we are running the following command to create a Database. The database name starts with a number:
SQLCMD -S Z0EUW1WLSQL031\PRUEBA -U rdb00001 -P "1234" -v datos="`"...
0
votes
1
answer
374
views
Retrieve results of running query with invoke-sqlcmd
I have this script which I can use to create logins and assign sysadmin to support group.
It's not perfect, but it works.
However the problem is I am not receiving any feedback from running it. I'd ...
- 45
0
votes
1
answer
415
views
Specifying SQL script with multiple statements for sqlcmd in PowerShell
I need to query across multiple (several) databases to see if there is a specific SQL Server Agent Job name on each. I would have thought that SEMICOLON characters would separate SQL statements, but ...
- 16.1k
0
votes
2
answers
458
views
SqlCmd command execution stops
Iam new to sqlcmd and i'm trying to execute this sql cmd code:
:Connect SERVERNAME
!!if exist $(FullBackup) del $(FullBackup)
GO
!!if exist $(TransactionLog) del $(TransactionLog)
GO
I am passing ...
- 572
0
votes
0
answers
781
views
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 ...
- 1
0
votes
2
answers
383
views
Error while using sqlcmd with Start-Job in PowerShell
When I'm executing this command in PowerShell, everything works fine:
sqlcmd -S dwh -i ".\script.sql" -o ".\log.txt"
However, when I'd like to set up several jobs, the following ...
- 6,348
1
vote
0
answers
56
views
How to stop Invoke-Sqlcmd from truncating query [duplicate]
One of our tables has very very long records in a particular column.
These queries are used infrequently, but are necessary.
I have been using sqlcmd utility in bash like so:
-- getfile.sql
SET ...
- 11.9k
1
vote
1
answer
4k
views
PowerShell script runs manually but not through the Task Scheduler
I have a simple PowerShell script that has 1 line. It logs into a database,and runs a sql script of queries, that deletes old data.
sqlcmd -U myUser -P myPassword -S localHost -i .\deleteOldData.sql
...
- 39
0
votes
0
answers
3k
views
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 -...
- 76
0
votes
1
answer
768
views
Pass byte[] as parameter to sql insert script
I am trying to upload the binary[] of a Zip folder to my database. I used Get-Content -Encoding Byte -ReadCount 0 to read the data into a variable. I want to use this variable in an INSERT statement. ...
- 11
2
votes
2
answers
3k
views
SQLCMD , 'tee' is not recognized as an internal or external command
I've fetchDb.bat file that calls many .sql files to
upgrade the database to last version
this is the command used to execute .Sql File content
sqlcmd %Sqlinstance% -d DataBase -i "fileName.sql" | ...
- 33
2
votes
2
answers
3k
views
Getting errors from Sqlcmd in PowerShell script
I work for a government client and our deployments are controlled by another contractor who has requested we provide powershell scripts to run our database scripts. Unfortunately security is tight and ...
- 884
0
votes
1
answer
1k
views
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 ...
- 31
3
votes
1
answer
844
views
32 internal error "A device attached to the system is not functioning" 0x1F occurred while setting the console window title
I have multiple databases and have multiple sql scripts (250+). I am executing all the sql scripts on each of these databases. I am executing these scripts using SQLCMD.exe through PowerShell.
It is ...
- 63
0
votes
2
answers
1k
views
How to pass array as variable in SQLCMD
I am trying to pass server names in sql script , but its not working.
Please help
SQL Script patch_report.sql, I am running via powershell giving error
SELECT * from table where server in ('$(...
- 158
0
votes
1
answer
152
views
Getting SQL value from sqlcmd.exe remotely
I should execute remotely SQL command on database because sqlcmd is not installed.
$Configuration = Invoke-Command -ComputerName $dataserverName -Credential $cred -ScriptBlock {
sqlcmd.exe -S $...
- 1
0
votes
1
answer
2k
views
Invoke-SQLcmd not working inside Invoke-command
I am currently running a Invoke-SQLcmd inside a Invoke-Command script block and keep receiving a connection error, The Invoke-SQLcmd script works when run on its own but not once wrapped in the Invoke-...
- 31
6
votes
3
answers
8k
views
Execute SQLCMD.exe from PowerShell in an Azure DevOps Release Pipeline
I'm porting our deployment infrastructure to Azure DevOps (formerly Visual Studio Team Services), but I've run into an issue I can't find any good solutions to.
We're using EF Core Migrations, and to ...
- 60.4k
2
votes
1
answer
986
views
Docker run and sqlcmd together errors
Putting together a script to quickly add a SQL Server Linux instance inside a docker container, then immediately connecting to it to test. Seems like the run command and sqlcmd can't be run in the ...
- 325
1
vote
1
answer
609
views
sqlcmd does not create parameters on a backup file name when executed through Powershell or Command line
I want to create an automated backup of some databases on a SQL Server 2017 Express instance and so I used this: How to schedule and automate backups of SQL Server databases in SQL Server Express.
...
- 412
0
votes
1
answer
671
views
Running sqlcmd or invoke-sqlcmd in powershell script in c#
Been using:
private string RunScript(string storeNumber)
{
//string importSQL = "Import-Module sqlserver -DisableNameChecking";
//string executionPolicy = "Set-...
- 13
2
votes
0
answers
127
views
sqlcmd - output to csv | each value in single column
What I do:
$data = Invoke-Sqlcmd -Query "USE DB-Name SELECT DISTINCT name1, name2, name3, name4, name5 FROM Table WHERE Name1 in ('Servername')" -ServerInstance "Name"
| Format-Table
When I Out-...
1
vote
2
answers
8k
views
Powershell - SQL query result to variable with properties
Why the stored output of SQLCMD has only Length property instead of column names?. Is it not possible to store sqlcmd output with its properties?
Invoke-sqlcmd stores it correctly but Invoke-SQLcmd ...
- 3,027
0
votes
2
answers
1k
views
Select (or split) column from SQLCMD output
How do I select the columns from SQLCMD output in PowerShell v1? I'm trying to make JSON output using Write-Output in PowerShell v1.
Query output at the end.
$_ returns both columns. If only we ...
- 3,027
0
votes
3
answers
3k
views
Invoke-Sqlcmd leaves script at 'PS SQLSERVER: \>' prompt
I have a script that queries a MS SQL database as below:
$query = "SELECT Path FROM repoLocations WHERE hostId = '$($hostId)'"
$results = Invoke-Sqlcmd -Query $query -ServerInstance $dbHost -Database ...
- 315
2
votes
2
answers
14k
views
SQL Connection From powershell
I'm trying to connect my script to a Database.
Previously I made it with:
psexec \\servername -accepteula sqlcmd.exe -U username -P password -S database -Q query
Now, I want to convert it into ...
- 115
2
votes
0
answers
702
views
Return Stored Procedure value from invoke-sqlcmd
I have a stored procedure that returns a value, 0 or 1. This is required in powershell to determine the next steps the script or user should take. The problem is, I can't find any documentation on how ...
- 69
1
vote
3
answers
834
views
Invoke-Sqlcmd changes cases of uniqueidentifier
I'm trying to develop a script to routinely export correctly formatted CSV files as per RFC 4180. I'm using a PowerShell script to do this which invokes Sqlcmd. However, the output appears to change ...
- 1,143