Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sqlcmd]

SQLCMD is Microsoft SQL Server's command line utility that allows you execute T-SQL commands from a command prompt.

0
votes
0answers
56 views

Easiest way to update XML inside of a SQL table column?

I have an SQL DB, Directory. It contains a table, dbo.User. Within that table there is a column, Info. The Info column contains ~1000 lines of xml tags. Well I want to modify only one of those tags, ...
2
votes
1answer
39 views

SSMS generated script failing to execute

So I am in a situation where I need to import a small DB (under 1GB) from a newer version of SQL Server (I am restoring to a 2014 SQL Server from a 2016 SQL server), without a direct connection ...
0
votes
2answers
99 views

How to report stats on restore via sqlcmd?

I've inherited a batch file script that calls sqlcmd with parameters. One of the parameters is the path to a SQL file. That file includes a database restore command. The command in the SQL file ...
2
votes
2answers
160 views

How do I install SQL-Server Tools if I can't use curl?

Attempting to install mssql-tools, I get $ sudo apt-get install mssql-tools Reading package lists... Done Building dependency tree... 50% Building dependency tree Reading state information......
2
votes
2answers
80 views

Sqlcmd: 'KReadOnly': Unknown Option

I am using SQL Server 2014 SP2 with AG, running this command: sqlcmd -S ABC-ISELL -E -d ABP_SFA_BMB -K ReadOnly Where ABC-ISELL is the listener name, I am getting this error: Sqlcmd: 'KReadOnly': ...
1
vote
3answers
439 views

help in connecting the sql server 2017 developer edition

I have installed the sql server 2017 for non production environment in my PC. It has installed successfully. when connect using sqlcmd it was throwing an error login failed for login sa. it didn't ...
5
votes
3answers
621 views

sqlcmd :out not working as expected?

Consider the following code: DECLARE @db sysname; DECLARE @filename varchar(260); DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT d.name FROM sys.databases d; OPEN cur; FETCH NEXT ...
-1
votes
1answer
67 views

How to update the first row in the exported query from sqlcmd

I am trying to find an easy way of updating the first row in the export of a sqlcmd query to CT_EVENT,,,,,,,,,,,,,,,,,,,,,,,,. I found this article in stack overflow which builds the csv format. I've ...
5
votes
2answers
390 views

SQL Server 2016 SQL Agent Token Not Working

I have this in scores of SQL Agent Job steps throughout my enterprise and it works as expected: sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..." But on my new SQL ...
0
votes
1answer
419 views

How To Import Binary Dump File To SQL Server

I am trying to import a dump file which is in binary format to SQL Server using sqlcmd command. After running the import command, I am getting this error, Msg 2812, Level 16, State 62 I'm suspecting ...
1
vote
0answers
298 views

Unable to access an SQL Server 2016 named instance locally via sqlcmd when only using @@SERVERNAME value

I have a server with two instances of SQL Server 2016 SP1 CU7 running, one is the default instance (on a custom port, 9433) and the second is a named instance (on another custom port, 9434). I've ...
0
votes
0answers
65 views

SQL CMD Command Script

I have around 10 servers and I need to loop through each server and retrieve the login accounts from each server. To achieve this I am using the following SQL script as input: SET NoCount ON select '...
0
votes
1answer
120 views

sqlcmd gives no sign of failure yet doesn't work

I am running sqlcmd from a linux bash script. My target server is an Azure SQL. I execute an sql file with a lot of sql statements (around 2500). These are Insert/update/delete statements. I used the ...
0
votes
1answer
170 views

Import database from MSSQL export dump on Ubuntu localhost

I have an export file from an online database server that I need to use to generate the database locally on my system. I'm running MSSQL 2017 on Ubuntu 16.04. The export file has Program(application/...
0
votes
1answer
232 views

SQLCMD to connect to Linked server

I'm getting a login error with the below sqlcmd to connect to a linked server: SQLCMD -E -dlinkedserver -ig:\temp\script.sql -o g:\temp\test.txt My linked server which is an oracle connects through ...
0
votes
0answers
195 views

how to make sqlcmd to use odbc driver 11 instead of 13

I'm running sqlcmd on ubuntu 16.04, I want to make sqlcmd to run odbc driver 11 instead of 13, how can I do it?
0
votes
0answers
60 views

SQL update works in SSMS but hangs in command line

I am working with an Arcgis Enterprise SDE Database in SQL Server. I am attempting to use a SQL update query to update a new field for many of the records in the database. This field is populated ...
6
votes
5answers
5k views

How to display MS SQL results vertically?

Here is what I've got currently when executing queries in sqlcmd: 1> SELECT 1,2,3,4 2> GO ----------- ----------- ----------- ----------- 1 2 3 4 (1 ...
1
vote
1answer
442 views

How to setup db credentials for `sqlcmd` using a config file?

When I'm using mysql client, I can configure my db credentials in ~/.my.ini. How can I achieve similar thing with sqlcmd on Linux? Where and what filename I should create? My goal is to avoid keep ...
0
votes
1answer
80 views

How can I increase the length of the string returned by object_definition in sqlcmd?

I have an sql script that uses select object_defintion(object_id(...)) to extract the definition from a view. This script is run with sqlcmd: sqlcmd -i extract.sql -o %oudir%\extracted.dat. ...
3
votes
2answers
176 views

SQL Server Temporary Table Security

I am part of a project to convert an old AS400 system to SQL server. The server is used for our payroll processing. Part of the payroll process is to break out the percentage of the biweekly wages ...
0
votes
1answer
94 views

DAC Connection using the SSMS for executing the code

Can anyone help me on below . I am trying to execute the SQLCMD with DAC (-A switch) but unable to do so . How can I do it both ways below : Execute in SSMS Query Pane Execute using PowerShell ...
2
votes
3answers
342 views

Why does sqlcmd truncate varbinary(max)?

Inside of the sys.assembly_files catalog, I can see a field named content 1> SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files; 2> GO assembly_id ...
1
vote
1answer
706 views

difficulties working with powershell Invoke-SQLCmd

I've setup a simple sqlcmd script. Contents below. use $(dbname); select * from $(tablename) I'm then running the following Invoke SQLCMD $SQLCMDVarArr = "dbname='MyDatabase'", "Tablename='...
-4
votes
1answer
153 views

Does sqlcmd.exe have “spyware”?

I'm just wondering if the sqlcmd.exe has client-side Telemetry, CEIP (Customer Experience Improvement Program), or any other form of "spyware" baked into it and turned on by default? And if so how to ...
3
votes
1answer
365 views

How do I log into the Dedicated Admin Console (DAC) on Linux?

sqlcmd is documented to have, Login-Related Options -A Logs in to SQL Server with a Dedicated Administrator Connection (DAC). This kind of connection is used to troubleshoot a server. This ...
4
votes
1answer
308 views

sqlcmd history?

How can I list/review the sqlcmd history? Mysql, Postgresql and sqlite write out to dot files: .mysql_history, .psql_history and .sqlite_history. I'm looking for an equivalent (or approximation) ...
1
vote
1answer
271 views

Update mysql DB when data has a single quote with Dynamic values

I am writing data to mysql DB from my linux machine using sqlcmd. My script is updating the data Dynamically based on the employee number. I have a situation where the the CN attribute might have ...
2
votes
1answer
207 views

Decrypting a Master Key works well using SQLCMD but not in the script

I would like to use a script to automatize the process of migration of a Db in another instance. The DB is encrypted, so after restore it I need to decrypt it and encrypt again using the new Service ...
3
votes
0answers
2k views

Can not connect to SQL Server from Ubuntu sqlcmd client

I'm trying to connect to a SQL Server Express server from a Linux Ubuntu 16.04 computer. I Installed all SQL commad line utilities for the linux machine and follow instructions from Microsoft SQL ...
0
votes
1answer
159 views

Permission issue on restore of the db from primary to secondary [duplicate]

while restoring the User DB from primary to secondary replica through query in cmd prompt and got following error message Msg 262, Level 14, State 1, CREATE DATABASE permission denied in database '...
1
vote
0answers
1k views

sqlcmd access denied on c:/

I tried to execute a simple .sql script from the command prompt and this is the output I got C:\Users\Administrator>sqlcmd -S {machine name}\SQLEXPRESS -i C:/SqlScripts/UpdateStat.sql Sqlcmd: ...
1
vote
1answer
77 views

Script not working after adding try and catch

I have a working script that I modified to work with SQLCMD since I need to invoke this script remotely. Here is my script: :ON ERROR EXIT USE [master] GO BEGIN TRY /****** Declare Database name **...
3
votes
1answer
195 views

Messages in SQLCMD script output are not in order

I am running a batch file that launches a SQLCMD script that creates an Agent job from TFS Release Manager. The script assigns SQLCMD parameters to variables, lists the variables using selects and ...
5
votes
2answers
8k views

sqlcmd new login/user

I am new to sqlcmd and I want to create a new user. I have done the following: 1> CREATE LOGIN FYI_DBA WITH PASSWORD = 'V_2017' 2> go 1> CREATE USER FYI_DBA FOR LOGIN FYI_DBA 2> go Then ...
1
vote
1answer
388 views

Suppressing non-ANSI warnings in SQLCMD script

I am working with a script that creates a SQL Agent job in SQL Server 2014. The job's last 2 steps are "Complete with Success" and "Complete with Failure". The script always warns of non-existent ...
7
votes
1answer
3k views

Variable inside another variable in SQLCMD mode

Trying to tidy up my SQL script with SQLCMD mode and I ran into an issue: :setvar db_suffix "some_suffix" :setvar some_db "some_db_$(db_suffix)" print 'some_db: $(some_db)' The output of that is: ...
2
votes
1answer
2k views

How can I output more than 256 characters to a file?

I am using the following command to write the output from a stored procedure to a file for further processing: DECLARE @shellCommand VARCHAR(2000) SET @shellCommand = 'SQLCMD -S ' + @@SERVERNAME + ' -...
1
vote
1answer
1k views

Execute Permission Denied Using SQLCMD

I have a Windows Server 2012R2 with SQL Server Express 2014 install that supports a video app. I am trying to create a backup solution for it using the Task Scheduler to do full and incremental ...
0
votes
2answers
321 views

String replacement in SQLCMD

I'm trying to set up a test environment that simulates (very roughly) having three servers. I'm doing this by creating 3 databases on my local instance of SQL which I'll call [A], [B], and [C]. Why I ...
5
votes
2answers
428 views

How can I save the results of DBCC SHRINKFILE into a table?

I have the following script: -- available space in each of the database files PRINT @@SERVERNAME --SQLDWDEV01 USE [BodenStage] GO SELECT name ,(CAST(ROUND((size/128.0 - CAST(FILEPROPERTY(name, '...
6
votes
1answer
4k views

installed SQL Server on RHEL7 and received - Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2AF9

I installed SQL Server on RHEL7 VM msodbcsql-13.1.1.0-1.x86_64 mssql-server-14.0.100.187-1.x86_64 mssql-tools-14.0.2.0-1.x86_64 # ls -al /opt/ total 4 drwxr-xr-x. 5 root root 52 Jan 13 08:31 . ...
0
votes
1answer
356 views

SQLCMD does not prints Rows affected in log file

I am calling stored procedure using SQLCMD which will insert data into multiple tables. And output of that procedure is captured in log file using -o. But, i can't see Row(s) affected any where in the ...
2
votes
2answers
1k views

Login to SQL Server from command line [closed]

I am trying to access a SQL Server 2014 database from cmd. I am using the literal command below where the only thing I am changing is the servername: sqlcmd [ /E ] [ /S servername ] However I get ...
5
votes
3answers
762 views

large insert script (generated from tasks) - errors when running from sqlcmd

I have a large generated .sql file for the data from our UAT database. I'm trying to load that into my (localdb)\v11.0 using sqlcmd. I was able to use the following: sqlcmd -S (localdb)\v11.0 -d ...
5
votes
1answer
17k views

How to connect to SQL Server using sqlcmd on Linux?

I have set up the Microsoft ODBC Driver 13 for SQL Server on an Ubuntu 16.04 machine. I am now trying to test a database connection, that includes both the server name and an instance name. The ...
1
vote
1answer
1k views

SQLCMD: “Login failed for user” on SQL Azure, but it works with SSMS

I have an SQL Azure database, and I'm able to connect to it using SQL Server Management Studio. When connecting to it, it displays myServer.database.windows.net (SQL Server 12.0.2000.8 - myUser) ...
1
vote
1answer
886 views

sqlcmd returning literal 'NULL' for null columns when outputting to CSV

I'm using sqlcmd (SQL Server 2008 R2) to execute a simple query to output data to a .csv file. In the SQL file, I have the following code: USE [OSCAR_UK] SELECT DISTINCT ISNULL(LTRIM(RTRIM(il.[...
2
votes
0answers
740 views

SQLCMD -L Returns Bizarre Response Instead of Server List

I have a server with three installed SQL Server 2014 instances with Service Pack 1 (build number is 12.0.4213.0) installed. When I run SQLCMD -L on the server I am getting the bizarre response below. ...
3
votes
2answers
664 views

Test SQL Server connection with sqlcmd using machine name

How can I test a connection via machine name as the SQL logon name (ie mydomain\server01$ - as an IIS AppPool would connect) using sqlcmd? I'm having trouble figuring out an application's SQL Server ...