1,163 questions
0
votes
0
answers
129
views
sqlcmd cannot connect to LocalDB (MSSQLLocalDB) even though instance is running
I have a fresh installation of SQL Server 2022 LocalDB.
When I try to initialize a database with a script using the command:
sqlcmd -S (localdb)\MSSQLLocalDB -E
I get the following error:
No named ...
- 370
0
votes
1
answer
99
views
Headers from SQL not showing up in text file from batch file creation
I can get the data exported and my CSV created but I can't get headers to show up on the file.
This is my batch file code
sqlcmd -S SERVERNAME -U TEST -P TEST123 -d "TESTDB" -Q "set ...
- 11
1
vote
1
answer
37
views
Adding a SQLCMD file to Visual Studio Solution causes Errors
I have an implementation script written in SQLCMD. We store these scripts in our Visual Studio solution file, under a DB project.
This script throws a ton of errors because VS thinks it's bad SQL. ...
- 179
1
vote
0
answers
48
views
SQLCMD executing sp_help_jobhistory via XP_CMDSHELL and trying to write results to table
When I run the command, I get Column name or number of supplied values does not match table definition.
The output from sp_help_jobhistory returns 11 columns which I have defined in my table.
Here is ...
- 11
0
votes
0
answers
67
views
Invoke-SqlCmd "Login failed for user"
I am trying to run a Powershell script off of a VM using Azure to automate a report coming from a SQL DB. I am running it via a job in Logic Apps but when testing the current script I am getting a &...
- 23
0
votes
0
answers
119
views
Getting SQLCMD error - invalid argument for -v, variables flag: 'variable' must be formatted as key=value
I am trying to run an INSERT query through SQLCMD, passing values from a variable. This command runs successfully on many servers except one, where it is failing.
Below is my command, where TESTID is ...
- 1
0
votes
0
answers
22
views
Conversion error in sqlcmd, but no error in SSMS
This query has been running fine for the last year. It is executed from sqlcmd.exe in a bash script because we have a Linux dependency. The UPDATE statement failed due to a "Arithmetic overflow ...
- 166
0
votes
1
answer
439
views
Can't authenticate with sqlcmd -G running in Azure Container App Job
I have an Azure Container App Job running a shell script, one of the commands in said script is sqlcmd. Currently I can run the command successfully if I use user name/password authentication but I ...
- 173
0
votes
1
answer
40
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
2
votes
0
answers
69
views
How to use -y and -W options together in SQLCMD to set variable length type display width and remove trailing spaces?
I’m trying to connect to a SQL Server using SQLCMD. While extracting data, one of my columns has a data length of 1500 characters. If I don’t specify the -y option, the data for this column is limited ...
-1
votes
1
answer
341
views
Use of context_info returns different output in SQL Server Management Studio vs SQLCMD
I have this T-SQL code:
declare @DBNAAM varbinary(128) = cast((select DB_NAME() as [Current Database]) + '_SINGLES-' as varbinary(128));
set context_info @DBNAAM;
go
print cast(context_info() as ...
- 47
0
votes
3
answers
449
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,902
-1
votes
1
answer
433
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
...
- 31
0
votes
0
answers
80
views
The command `sqlcmd -S localhost -U sa -P 123456 -Q "SELECT @@version"` gives error
I’m unable to connect to my local SQL Server instance using sqlcmd. Despite trying several troubleshooting steps, I’m still facing connection issues. Here’s the error I receive:
PS C:\Users\it ...
0
votes
0
answers
68
views
Prevent SQLCMD from truncating error messages
SQLCMD truncates my error long before I see the relevant part of the error. How can I make SQLCMD give me the full error message when the error message is very long?
-y 0 does not fix the problem.
- 1,766
0
votes
1
answer
85
views
Query to SQL Server 2008 R2 with nested function call succeeds in SSMS but fails using ODBC
I have a legacy database that is running SQL Server 2008 R2 Express Edition.
Today, I was trying to use sqlacodegen to generate SQLAlchemy classes via the pyodbc package. I was able to connect to the ...
- 669
0
votes
1
answer
136
views
How can I both export execution plan XML and get column names in tables?
I need to get both the full text of an actual execution plan and my full table of results with column names from sqlcmd. My first requirement forces to me pass in -y 0 as documented here. However, ...
- 1,766
4
votes
1
answer
235
views
Messages from sqlcmd GO vs server's sqlcmd
I have this little SQL script:
CREATE PROCEDURE Something
AS
EXEC SomethingNotExist;
Normally SQL Server Management Studio shows this message:
The module 'Something' depends on the missing object '...
- 155
0
votes
1
answer
911
views
Pass variables to the SqlAzureDacpacDeployment@1 task of a SQL Server instance while running an Azure Pipeline
I want to pass variables to the SqlAzureDacpacDeployment@1 (or DotNetCoreCLI@2) task of an SQL Server instance while running an Azure Pipeline.
This variable is going to be the env (dev, acceptance, ...
- 534
0
votes
1
answer
599
views
How to use SQLCMD in ARM64 architecture?
I am using NVIDIA Jetson Orin nano board (which is based on ARM64 architecture) to as an IoT Edge device to deploy Azure Edge modules from Azure marketplace.
I have deployed the temperature sensor and ...
- 13
0
votes
0
answers
143
views
How can I generate a .xlsx file from a batch file using sqlcmd?
I have a sql script that my batch file is executing, and I'm generating a .csv file with the >> syntax. When I switch the .csv to .xlsx, then the file doesn't place the distinct data elements ...
- 1
0
votes
0
answers
73
views
Unable to run sqlcmd via subprocess.Popen
I am trying to run SQL command using SQL file in Python
process = subprocess.Popen(shlex.split(command), stdout=log)
print("command: {}".format(command))
print("split cmd: {}"....
- 1
0
votes
0
answers
37
views
Using r to load SQL file with french column select
I'm using sqlcmd in SQL Server to, create a action to move data from one structure to a other predefined database table. where both have the same collation
Where I have some French column names. like: ...
0
votes
1
answer
1k
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 ...
- 25
0
votes
0
answers
104
views
Powershell script to extract the data from database using the data in input file and save the results in csv file
SCRIPT TO EXTRACT THE ALL DETAILS
#Variable to hold variable.
$SQLServer = "XXXX"
$SQLDBName = "XXXX"
#Customer data input file path
$InputFile = "F:\\...
1
vote
2
answers
526
views
Use dynamic query for :Connect in SQL Server?
I have a list of database connections in a table and want to iterate through them, connecting to each one within SQL Server through a script, and then execute a select statement.
Initially, I ...
- 41
0
votes
2
answers
352
views
Referring to other files in SQL Pre-deployment script using Devops Self-Hosted agent
We are using DevOps YAML pipelines to build and deploy database changes to our production environment. The database is deployed through a DACPAC file. We have a pre and post deployment file configured ...
- 143
1
vote
0
answers
94
views
Sqlcmd: The -W and the -y/-Y options are mutually exclusive
I am working on SQL server task. I need a sqlcmd result file with complete records with headers.
Here is the command and error message below
sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [...
- 21
2
votes
1
answer
151
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
173
views
sqlcmd truncates column width in result file (.rpt)
I have a sql cmd .bat file that executes a query and returns the query results to a .rpt file (tab delimited). Now in one of the column, not all characters are returned in the .rpt output file.
What I ...
- 37
0
votes
0
answers
89
views
Visual Studio SSDT Database Project - add database reference without inheriting sqlcmd variables
I have several related SSDT SQL Database (.sqlproj) projects in a Visual Studio Solution.
Each project has a set of SQLCMD variables and we use the Database Reference feature to enable cross-project/...
- 2,031
3
votes
2
answers
996
views
SQLCMD in Shell Script Does Not Exit After RESTORE
I wrote a shell script to automate the download and restore of a SQL Server database. The script works great with one exception. It downloads the .bak file(s) that it is supposed to and then, if ...
- 108
1
vote
0
answers
68
views
Column headers missing
I'm exporting records from a SQL server to a csv file.
sqlcmd -y0 -S server -d database -U username -P password -o C:\outfiles\data.csv -s ";" -Q "SET NOCOUNT ON; select field1, field2, ...
- 2,228
1
vote
1
answer
1k
views
Azure SQL authentication - Service Principal not working in SSMS/SqlCmd
We have a requirement to connect to Azure SQL Database using Service Principal Details. We are using SSMS(v19) and sqlcmd to connect.
What we done so far:
Create service principal and secret
Assign ...
- 21
2
votes
1
answer
519
views
Is Go SQLCMD slower than windows-native version?
I have been using the windows-native sqlcmd to run scripts on my local SQL Server database. I have a set of script files that will create the database, add tables and indices, populate domain data, ...
- 1,085
0
votes
0
answers
77
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 ...
- 432
-1
votes
2
answers
93
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.6k
6
votes
0
answers
7k
views
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server:SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate] [duplicate]
I'm creating instance of SQL Server in a Docker container.
$ sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Adminxyz22#" -p 1433:1433 --name sql3 -d mcr.microsoft.com/mssql/...
- 10.6k
3
votes
1
answer
3k
views
Enable SQLCMD mode in Azure Data Studio
Is there any way to set SQLCMD mode in Azure Data Studio? I'm currently running 1.46.1.
The Azure Data Studio - Overview page in MS Learn shows that Azure Data Studio supports SQLCMD in the Query ...
- 20.4k
0
votes
0
answers
104
views
A Replace of special character '$' to '_' works well in SSMS, while it doesn't in sqlcmd
I'm trying to execute a query in SQL Server 2019 (v15) and facing an issue. The replace works well in SSMS, while it doesn't when the same query is executed in sqlcmd
Actual Query
SELECT
RTRIM(...
- 43
0
votes
0
answers
281
views
Back-up of SQL Server Database to network writable location fails
I want to make a backup of SQL Sever database. Using sqlcmd I am able to direct output to a shared folder on my local machine. I can make a back-up to a location on the server but I cannot direct the ...
- 182
1
vote
1
answer
495
views
Sqlcmd multiline script in terraform local provisioner
When I run this provisioner it gives me error:
exit status 2. Output: Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login failed for user 'User'..
│ /bin/sh: 2: Syntax error: "(" ...
- 184
-1
votes
1
answer
325
views
RESTORE DATABASE with dashes-in-its-name with sqlcmd
Given:
Backup from Windows SQL Server 2022, containing a database with dashes in its name
1> RESTORE filelistonly FROM DISK = '/tmp/my-awesome-database_FULL_20230810_000209.bak';
2> go
...
- 1,072
0
votes
1
answer
95
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, ...
- 1,016
0
votes
2
answers
427
views
sqlcmd, single row product multiple lines
I am using sqlcmd command to export a SQL Server table to CSV file.
sqlcmd -S EC2AMAZ-5UNBD90 -d miadmfggp_live -Q "SELECT SDESC FROM dbo.pmdocs WHERE HMY=95613900;" -s "," -o &...
- 469
0
votes
0
answers
200
views
.Net error connecting to SQL Server in docker container
I have 2 docker containers, one with sqlexpress and one with my asp.net app. Both of these containers are in the same network and see each other. I can connect to the database from the host machine ...
- 179
1
vote
2
answers
350
views
Execute SQL query with sqlcmd within specific version
I need to execute this command:
SQLCMD -d dbname -i sc.sql
to run some queries. Since I have both version 2019 and 2016, the query is executed with 2019, but actually I need that on 2016.
I have the ...
- 376
-4
votes
1
answer
205
views
SQLCMD fails on INSERT with "Syntax error at line xxxxx near command '&'"
I use SQLCMD to import DDL generated by mssql-scripter and i got this error:
The offending INSERT is the following:
INSERT [dbo].[Settings_ContentPartFieldDefinitionRecord] ([Id], [Name], [Settings],...
- 23
-2
votes
1
answer
123
views
What are the default locations that OSQL and SQLCMD search to connect to a server when -S option is not specified?
I am trying to identify how osql and sqlcmd resolve server name and user to connect to that server when we do not specify server using -S option and use -E option. Below is the output for various ...
- 473
0
votes
1
answer
2k
views
Run SQL query against Azure SQL managed Instance using PowerShell
My task is to execute Azure MI database SP using Powershell.
I am using connection string. I believe I am able to login to the SQLMI database using the connection string with PowerShell.
However, I am ...
- 21