1,168 questions
0
votes
0
answers
7
views
sqlcmd is truncating long values or skip headers
I'm trying execute a sql query stored in a .sql file and save the results to csv.
sqlcmd -S the_server -d the_db -U the_user -s ";" -i C:\GIS\Kod\SQL\sqlcmd\test.sql -o C:\GIS\Kod\SQL\sqlcmd\...
- 1,816
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
2
votes
0
answers
27
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
74
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
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
45
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
59
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,590
0
votes
1
answer
66
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
116
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,590
4
votes
1
answer
145
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 '...
- 145
0
votes
1
answer
311
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, ...
- 502
0
votes
1
answer
286
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 ...
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
0
answers
23
views
How do I spell out the month in a filename generated by sqlcmd?
I have a batch file that is using sqlcmd %date:~10,4%%date:~4,2% for a date stamp in a file name. My client wants the month to be spelled out instead, so the date looks like blahblah_May2024.csv.
I ...
- 1
0
votes
0
answers
68
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
48
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
34
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
0
answers
25
views
SQL CMD EXPORT data issue
I'm trying to export data into csv using sqlcmd , there are my parameters -h -1 -y 8000 -w 8000 -s",".
Issue I'm facing is the last column of exported data is too long, i mean last column ...
- 1
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
0
votes
0
answers
77
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
293
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 ...
- 31
0
votes
2
answers
256
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 ...
- 119
1
vote
0
answers
72
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
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
32
views
Not understanding why this .bat file is only pulling some XML info and not all [duplicate]
So I have three SQL queries and when I run them in SQL Management studio with the FOR XML AUTO, ELEMENTS; line, they generate an XML file with everything I am looking for.
Now, I want to automate ...
- 11
0
votes
0
answers
111
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
32
views
$(ProjectDir) in post deployment SQL files
I want to use $(ProjectDir) the one available under macro in pre- and post-deployment scripts instead of hard coding path.
I tried
:setvar tpath '$(ProjectDir)'
Print $(tpath)
This is just printing $(...
- 1
0
votes
0
answers
63
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,021
3
votes
2
answers
732
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 ...
- 88
0
votes
0
answers
75
views
Can I run an sql script for db upgrade using transaction?
I need to run a 56000 lines sql server db upgrade script in SQLCMD mode, because if it fails I need to continue from the first error line, and I'm not allowed to run anything twice.
Can I run the ...
- 1
0
votes
0
answers
67
views
Using SQLCMD Mode within SSMS and a SQL script file path, how can I store the result in a temp table?
Using SQLCMD Mode in SSMS (not the SQLCMD Utility) and a file path to a SQL query script, I would like to store the results into a temp table.
I am aware the following command executes the query from ...
- 1
1
vote
0
answers
53
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, ...
- 1,816
1
vote
1
answer
782
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
361
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,087
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
4
votes
0
answers
4k
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/...
- 10k
2
votes
1
answer
2k
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 ...
- 19.5k
0
votes
0
answers
85
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(...
- 33
0
votes
0
answers
211
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 ...
- 162
0
votes
1
answer
394
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: "(" ...
- 174
-1
votes
1
answer
236
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
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
0
votes
2
answers
363
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
194
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
211
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
187
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
98
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 ...
- 438
0
votes
1
answer
1k
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