Questions tagged [sqlcmd]

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

Filter by
Sorted by
Tagged with
39
votes
2answers
42k views

How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
19
votes
3answers
24k views

SQLCMD command is not able to insert accents

I am trying to run sqlcmd.exe in order to setup a new database from command line. I am using SQL SERVER Express 2012 on Windows 7 64 bits. Here's the command I use: SQLCMD -S .\MSSQLSERVER08 -V 17 -...
17
votes
2answers
23k views

Can I enable SQLCMD mode from inside a script?

Is there something like a directive which I can use in a script to force SSMS to enable/disable SQLCMD mode?
15
votes
1answer
1k views

User-shared queries: Dynamic SQL vs. SQLCMD

I have to refactor and document a number of foo.sql queries which will be shared by a team of DB tech support (for customer configurations and things like that). There are types of tickets that come ...
13
votes
1answer
16k views

Setting variables in SQLCMD mode

Using SQL Server 2008 R2 Enterprise Edition Consider the following statement: :setvar source_server_name "SERVERNAME\INSTANCENAME" Is it possible in SQLCMD mode to get that value using TSQL ...
12
votes
6answers
975 views

Did anybody use sqlcmd mode in practice? [closed]

SQLServer 2005 introduced something called SQLCMD Mode msdn link. At first glance this mode adds variable subsitution from command line batch files, and some escaping to OS commands. Is this ...
10
votes
3answers
4k views

How do I get SSMS to use the relative path of the current script with :r in sqlcmd mode like SSDT does?

If I have foo.sql and bar.sql in the same folder, foo.sql can reference bar.sql when run from SSDT in sqlcmd mode with :r ".\bar.sql". However, SSMS won't find it. Procmon shows SSMS is looking in %...
9
votes
1answer
4k 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: ...
9
votes
1answer
2k views

Executing TSQL script generated from SSMS in SQLCMD. Failing on quotes

I am attempting to deploy a database to a machine by executing a script generated by the "Generate Scripts" tool in SQL Server Management Studio. Here is the command I've issued: sqlcmd -S ...
9
votes
1answer
12k views

Connect to LocalDB via SQLCMD in PowerShell

I'm trying to access the "localdb\MSSQLLocalDB" server on my computer through PowerShell with the SQLCMD utility. I'm using PowerShell v5, .NET v5.0, and the server name is (localdb)\MSSQLLocalDB when ...
8
votes
3answers
16k views

How to get SQLCMD to output errors and warnings only?

How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters? I essentially dont want information based messages to be output.
7
votes
5answers
10k 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 ...
7
votes
2answers
10k views

Suppress SQLCMD messages

Would it be possible to suppress all sqlcmd messages output to the cmd window? I am running a large script and it seems to be slowed down a lot with all the Processed XXXX total records messages. ...
7
votes
1answer
5k views

Transaction with multiple SQLCMD calls in a batch file

Is there a good way to do something like the following? SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION" FOR %%f in (script1 script2 script3) do ( SET CURRENT_SCRIPT=%%f SQLCMD -S Localhost -E -b ...
7
votes
3answers
30k 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 ...
6
votes
2answers
962 views

Running sp_AskBrent with @ExpertMode = 1 in PowerShell

I am trying to run sp_AskBrent from PowerShell using Invoke-SQLCmd and capture its output in a variable: $query = "EXEC SAIDBA.monitoring.sp_AskBrent @Seconds=10;" $check = Invoke-Sqlcmd -...
6
votes
3answers
2k views

Are there any way to stop typing 'go' in sqlcmd?

If I remember correctly, last time Microsoft Sqlcmd require each command to end with ';' and hit Enter key to execute the command. However, currently, we need to type the GO each time to execute the ...
6
votes
1answer
10k views

Why does sqlcmd -Lc not show the local instance?

I have two machines, each with a default instance of SQL Server 2008 installed; each exhibiting a different behavior. On PC1, when I run sqlcmd -Lc, I do not see PC1 in the list of SQL servers on the ...
6
votes
1answer
11k views

How to output more than 4000 characters in sqlcmd

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this: restore database [DB] from disk='F:\FULL1.bak' with norecovery restore ...
6
votes
1answer
2k views

Query performance while using sqlcmd [duplicate]

Possible Duplicate: Running queries with SQLCMD vs. Running queries with SSMS I have a number of scripts that query the data from different tables and consequently update them. I have defined a ...
5
votes
2answers
657 views

Skip sections that have GO

I'm working on a script I can run on new servers to do some initial configuration. I would like to be able to skip some sections but the problem I'm running into is the "GO" that's required. Because ...
5
votes
3answers
2k 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 ...
5
votes
1answer
3k views

sqlcmd -b doesn't exit on error

I'm confused how should I use -b parameter of sqlcmd. I'm trying to write simple automation script for my DB and wanted to stop execution in case of any errors. I'm using SQLServer 2008r2. For ...
5
votes
2answers
628 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, '...
5
votes
2answers
15k views

Where is SQLCMD.EXE in SQL Server 2014 LocalDB or how to install it?

On a machine with SQL Server LocalDB 2014 installed I am looking for SQLCMD.EXE file but cannot find it anywhere. Is there a way how to install it separately? Unfortunately I am not local admin of ...
5
votes
4answers
3k views

How to transfer files at remote location using sql server job?

I have a job configured in which it transfers the file at remote location. My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on ...
5
votes
2answers
1k 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 ...
5
votes
1answer
3k views

Running queries with SQLCMD vs. Running queries with SSMS

I've noticed something which i'm not sure about. can it be that running queries directly with SSMS is much faster than running queries with SQLCMD? I've noticed that the results of queries that ran ...
5
votes
1answer
3k views

Which workarounds exists to missing relative path feature of SQLCMD :r?

I'm not the first to notice the lack of this feature. But I wonder if there are some useful workarounds. I want to deploy a main script and some depended scripts in the same folder. BTW Is there a ...
5
votes
1answer
343 views

How to Natively Return Output when using Invoke-Sqlcmd?

How do I natively log output from the PowerShell Invoke-Sqlcmd? I am using Invoke-Sqlcmd to loop through a large (50,000-90,000 SQL files w/ 1-10 inserts in each) set of small SQL insert statements. ...
5
votes
1answer
5k 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 . ...
5
votes
1answer
2k views

sqlcmd to run large SQL file causes high CPU usage

I am using sqlcmd.exe to run a large SQL script, and the server CPU usage has been at 100% for the past 5 hours. The SQL script file has a size of 156 MB. I am running the script on SQL Server 2005. ...
4
votes
2answers
15k views

Cannot use SQLCMD

I have started SQL Server in single user mode like this: c:\....\sqlservr -m When I try to connect to it with SQLCMD -E, I get the following error: Msg 18461, Level 14, State 1, Server SERVERNAME, ...
4
votes
3answers
25k views

Can I get OSQL if I install SQL Server Client Tools?

We have some legacy .bat scripts full of osql commands to existing SQL Server instances / dbs on our network. We've tried running the .bat scripts from our app server (Win Server 2008) but get the ...
4
votes
3answers
3k views

SQLCMD Exporting 16.5 Million Rows

I am exporting a table to a TSV file using sqlcmd, and I am running into issues. The table has 16+ million rows and about 55 columns. The problem is that it does not export the full table, but seems ...
4
votes
2answers
13k 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 ...
4
votes
2answers
2k views

Are there any reasons not enable SQLCMD-mode as default in SSMS?

I don't consider "just to ensure that sqlcmd-mode is not required by a script" a valid reason. Any things to think of before giving the order to enable this setting to my coworkers?
4
votes
3answers
1k 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 ...
4
votes
2answers
8k views

How do I pass multiple statements into sqlcmd?

Wanting to automate some tasks instead of firing up SSMS for everything, I'm trying to write a batch file to run these statements, which work perfectly when I paste them into the sqlcmd prompt. They ...
4
votes
1answer
1k views

sqlcmd: no rows affected

When I run exec dbo.subprogram from SQL Server Management Studio, it executes and returns result as many rows affected. But when I run from cmd C:\Users\mssadmin>sqlcmd -S servername -d ...
4
votes
1answer
2k views

What are valid connection strings for SSMS login box?

What are valid connection strings for SSMS login box? Every once in a while I will have to connect to an instance of SQL Server with some specific configuration setting or in a DMZ where no host ...
4
votes
1answer
464 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) ...
4
votes
0answers
3k 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 ...
4
votes
1answer
379 views

Permission error when executing a 5.6 GB sqlcmd file

I am trying to run a very large file through the sqlcmd command and it works perfectly with files of 100 MB up to 1.4 GB. This is what I have tested so far, but when I try to run a file which is 5.6 ...
3
votes
1answer
304 views

How do I force windows to use a specific version of SQLCMD?

I have several versions of SSMS installed on my machine. We have legacy servers running several versions of SQL Server, mainly 2008, 2012, and 2014. I use SQLCMD all the time, but it seems to default ...
3
votes
2answers
301 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 ...
3
votes
1answer
2k views

Restoring SQL Server Database Command Line Error

I have a restore database script in a SQL file, and I'm trying to restore a backup generated from one DB to a new DB (that exists). That SQL file is being executed by SQLCMD on the command line. The ...
3
votes
2answers
1k views

Disabling variable substitution inside SQLCMD script when using :r syntax

I am using SQLCMD to deploy a set of scripted Agent jobs. I am running these with a SQLCMD script to calls each script using the :r syntax. It looks like this: :ON ERROR EXIT :r $(scriptpath)\...
3
votes
2answers
133 views

Suppress database change message in SQLCMD.exe

I am writing a script generation script. The script is generating list of DDL statments. The util_generator.sql generation starts with database connection: USE [TARGET_DATABASE] GO ... Many queries ...
3
votes
2answers
3k views

SQL Server sqlcmd setvar depending of selected database

I'm using sqlcmd for a moment now to update database on our project. Before we have use script like this: :setvar relativepath D:\Project1\Sql :r $(relativepath)\Tables\CreateTable1.sql :r $(...