All Questions
Tagged with sqlcmd sql-server
655 questions
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
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
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
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
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
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
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
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
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
-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
-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
1
vote
1
answer
1k
views
How do I make a SQL Server Job connect to another server to run a Query?
I'm attempting to create a SQL Server Job that shuts off high availability, changes a database to the simple recovery model, run a backup to clear the transaction log, do a database shrink, set a ...
- 11
0
votes
1
answer
221
views
How to ignore the new line in table value?
I am trying to export a SQL Server table to CSV file. What I am doing is call the sqlcmd command
C:\>sqlcmd -S EC2AMAZ-5UNBD90 -Q "SELECT * FROM dbo.PMDOCS WHERE hmy=95613900;" -s ",...
- 469
0
votes
0
answers
267
views
T-SQL exec master..xp_cmdshell sqlcmd command unable to include variable output path
I am trying to generate the output result to a .txt file dynamically with whatever the value of @name will be, it will be generated automatically via cursor, just to show an example, I've showed as ...
- 63
0
votes
0
answers
269
views
Invalid object name 'schema.table'
Here is some SQL I am trying to run via sqlcmd
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'schema' AND TABLE_NAME = 'Table')
BEGIN TRANSACTION
...
- 105
0
votes
1
answer
917
views
Is it possible to use a SQLCMD variable in a Visual Studio Database (SSDT) Project without enclosing it in square brackets or single quotes?
I know I can use a SQLCMD variable in a Visual Studio database (SQL Server Data Tools) project if I enclose it within square brackets or single quotes.
For example, something like
:setvar StringFilter ...
- 19.5k
0
votes
2
answers
73
views
Can anyone see why my valid-looking SQL fails when run using SQLCMD?
I have a Bash script that creates a build machine for an Azure DevOps project. This build machine then creates a single-server instance of SAP Hybris Commerce, builds our customisations, runs tests, ...
- 3
1
vote
1
answer
3k
views
sqlcmd error There are no batches in the input script. only if sql file contains GO
sqlcmd has strange behaviour on windows 10 yet works properly on Ubuntu linux box ... when the SQL file does NOT contain GO it seems OK as per
cat some.sql
SELECT s.name AS schema_name,
s....
- 28.2k
0
votes
1
answer
639
views
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Setting QUOTED_IDENTIFIER to 'OFF' is not supported
I am trying to authenticate to Azure Synapse Dedicated SQL Pool from the terminal command line utility sqlcmd ... this syntax works from a ubuntu laptop
sqlcmd -S foo -d bar -G # sudo apt-get ...
- 28.2k
0
votes
0
answers
430
views
When using SQLCMD on windows is there any way to use DSNs?
Rather than configuring connection information directly through each and every sqlcmd command, looking to use DSNs to have that information stored in a centralized location.
However when referring to ...
- 83
1
vote
2
answers
489
views
Question of SQL backup database to destination folder by bat file
I use a bat file to backup databases to destination folder as below.
@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F "tokens=1,2,3,4 delims=/ ...
- 13
1
vote
1
answer
341
views
sqlcmd error with sql file but works running same statement in sql console
I have this file.sql:
INSERT INTO "Parcelas" ("PlanEstudio", "LineaEstudio", "NIF", "Plan", "Cosecha", "Linea", "Modulo",...
- 470
0
votes
0
answers
121
views
Print command truncates message through sqlcmd on Linux
I am using sqlcmd on Linux (version 17.10.0001.1) to generate a SQL script that has a print statement. The sqlcmd is against Microsoft SQL Server 2017.
sqlcmd -S [server] -d [db] -U [user] -i input....
0
votes
1
answer
121
views
Ignoring Scripting variables in sqlcmd (Importing database)
I am having a database import being done form a SQL file and i see my content have some data that is conflicting with sqlcmd import. The following queries are the 2 scenarios i am encountering in my ...
- 17
0
votes
1
answer
324
views
SQL Server don't run in sqlcmd but run in ssms
I have a problem with a script running under sqlcmd vs SSMS.
The first time that I had the problem, I cut the large file (a lot of inserts) into small pieces, cutting it in blocks of 2000 inserts.
In ...
- 11
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
67
views
Can a batch file handle SQLCMD error when :r file is not found?
I have a batch file calling SQLCMD to run a SQL script that tries to call a second script using :r
I need the batch file to be able to tell that SQLCMD could not find the file indicated by :r but even ...
- 3
0
votes
0
answers
185
views
BCP failing to upload CSV file ("Unexpected EOF encountered in BCP data-file" )
I'm trying to automate the copying of some of the columns of a table in one database to a table on another server with my limited skill set.
Approach I've been attempting:
Using SQLCMD to run a ...
- 309
0
votes
1
answer
66
views
Generated script of schema and table data not running in MS SERVER sqlcmd
I have a very large table script that is about ^ GB in size and cannot open in in Query Editor (obviously) due to memory/size.
I am trying to run it on the db server with the command propmt and using ...
- 25.6k
0
votes
1
answer
738
views
Add a line break in sqlcmd output
I am working with SQL Server 2019 on Windows. I have a SQL script (.sql file) which I run from the command prompt using sqlcmd.
This is the script:
use master
go
select '$(SQLCMDWORKSTATION)' '...
- 1,661
0
votes
0
answers
549
views
sqlcmd - problem to connect with sql credentials
I have a SQL database service in Azure with a server name dummyserver.database.windows.net.
I have also a database dummyDB with user dummyuser.
I can connect there providing above parameters and ...
- 1,813
-1
votes
1
answer
93
views
OPENJSON() returns 0 rows from SQLCMD, but returns many rows in SSMS
As the title says, I have a set of SQL queries using OPENJSON() that work beautifully in SQL Server Management Studio, but when ran from the Command Prompt using SQLCMD, the queries return 0 rows.
...
0
votes
1
answer
209
views
How can I execute SQL Statements in a Azure Synapse managed SQL-Pool from Linux command line?
I would like to know if there is a simple way to execute SQL Commands on an Azure Synapse managed SQL-Pool from Linux command line?
In my On-Prem Linux setup I'm using sqlcmd with Kerberos ...
- 41
1
vote
1
answer
389
views
I'm using a script to add a column to a table. In one database it works, in another it doesn't work
I've got two logins, let's call them 1 and 2, and two databases, again 1 and 2, both databases and both logins made using the same sets of instructions. Login1 uses database1 and login2 uses database2....
0
votes
0
answers
342
views
Connect to sql server and do bulk load of data file from Linux server
Problem Statement:
I am trying to Load data file from Linux- source to SQL server table- target (sql server installed Windows server).
After many meetings with Microsoft we got resolve the connection ...
- 25
0
votes
1
answer
966
views
How to clear console(terminal) with sqlcmd in sql server(mssql) command-line client on Windows?
With SQLite command below, I could clear console(terminal) in sqlite3 command-line client on Windows:
sqlite>.shell cls
But, with sqlcmd, I couldn't find how to clear console(terminal) in sql ...
