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

learn more… | top users | synonyms (1)

4
votes
1answer
52 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 ...
4
votes
1answer
39 views

SQLCMD :connect dynamic parameter

In SSMS I have this :SETVAR DBSrvrInst MyDatabase :connect ADMIN:$(DBSrvrInst) In the Messages tab I get Connecting to ADMIN:$(DBSrvrInst)... The variable isn't being replaced. But if I do ...
7
votes
1answer
107 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 ...
2
votes
1answer
63 views

Run aspnet_regsql.exe from SQLCMD Post-Deployment Script

I am trying to create a Post-Deployment script in SSDT that will include the creation of an ASP.NET Session State database to make sure everyone has this thing in place after deployment of my database ...
3
votes
1answer
44 views

Multi-command SQLCMD mode script

I am trying to execute a sql procedure for a list of tables in sqlcmd mode to redirect all the output into one file per table. Unfortunately only the last file of the redirected outputs of all ...
6
votes
2answers
163 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 ...
1
vote
1answer
49 views

How can I pass the table name as an argument

I have this code ALTER PROCEDURE [dbo].[tablepass12] (@TableName nvarchar(20)) AS BEGIN DECLARE @SQL VARCHAR(8000) SELECT @SQL = 'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * ...
3
votes
1answer
133 views

SQLCMD stuck on “set textsize 4096”

I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012). The script is around 500mb and I added a GO every 100 lines. When I start the script with: ...
0
votes
0answers
27 views

Calling Knime batch file from Sql Server

We have an ExecutionSenario on daily ETL steps. One of them is to start a batch file of Knime to make an analysis of our data. Our batch seems as working on Task Manager > Process in background but ...
0
votes
1answer
42 views

How can I mask a CMD-Variable reference $(myVar)?

It is kind of an accademic question, but if I write a SQL-Script for SQL-Server in CMD-Mode, how could I create an column-alias like [$(myVar)]? SELECT 1 AS [$(myVar)] --error: myVar is not defined
3
votes
2answers
99 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 ...
0
votes
1answer
90 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 ...
14
votes
1answer
213 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 ...
2
votes
2answers
963 views

Pass parameters to a procedure with SQLcmd

I use this SQL command: sqlcmd -v first_date="30/09/2015" last_date="15/09/2016" And I would like to pass that parameters to a stored procedure: ALTER PROCEDURE [dbo].[_testProcedure] WITH ...
2
votes
3answers
269 views

Using variable for database name

I am having difficulty in using a variable name for the database. The below script searches through all databases and finds database that match a certain condition. The matching databases are added to ...
1
vote
0answers
66 views

Using :Connect I keep getting msg 911 - database [dbname] does not exist

I have a two part T-SQL script, the first part runs against a database on a different server, the second part is supposed to run on the local PC. I'm using SQLCMD mode and the :Connect keyword but ...
0
votes
0answers
21 views

Better diagnose information in sqlcmd

I'm tired of running SQLCMD scripts that include a large number of files: :On Error exit :r ".\tables\table01.sql" :r ".\tables\table02.sql" ... :r ".\tables\table99.sql" ... and getting ...
2
votes
1answer
66 views

Problems with sqlcmd -q and quotations marks

I have a SQL Server 2008 R2 Express edition. Because it is only an Express edition I don't have a SQL Server Agent for automation of tasks. So I tried to do something with command line program ...
1
vote
1answer
72 views

With LocalAdmin become sysadmin SQL Server

I am a local Admin on my machine. Same old thing, my account wasnt added when SQL Server was installed, and I don't want to have to reinstall SQL Server. I went through this blog and it worked until ...
1
vote
1answer
85 views

backup to NAS using sqlcmd

I've been searching for possible duplicates before asking but can't find anything. The closest was Backup SQL Server To Nas but not exactly my case. We have a brand new Synology NAS configured, due ...
2
votes
1answer
1k 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 ...
3
votes
2answers
690 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 ...
1
vote
1answer
111 views

In SQLCMD is there any way of shelling out to run arbitrary programs?

In the past I have dropped json into a table in a nvarchar(Max) column. This is structured data but it needed serving back to the client as json and came in from the client as json so it seemed a bit ...
2
votes
1answer
860 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 ...
2
votes
1answer
426 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 ...
2
votes
1answer
97 views

Untraceable error importing Huge dataset with SQLCMD

I am currently doing an internship and I received a 42 gb ms sql-server import script. I tried to import the script using the ms SQLCMD tool all at once. It ran for 30 minutes and then I got the ...
3
votes
1answer
766 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 ...
6
votes
2answers
747 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 ...
1
vote
0answers
205 views

Executing shell commands from SSDT Post Deploy scripts to generate SQL

tl;dr: I'm looking for the equivilant of !! sqlcmd -S $(Server) -o foo.sql -d $database -Q "EXEC sp_generate_merge 'table_name'" in a SSDT post deploy script, but !! doesn't seem to be supported. I ...
0
votes
1answer
210 views

Using dynamic database name for DML in trigger

I am using SQL Server 2008 in my application. Here I need to do database operations between multiple databases. The complexity is database names are user defined except one masster database. I can ...
1
vote
1answer
47 views

Verify failed SQL Server connections to instances on a given server and email them

I am trying to monitor the SQL Server instances on standalone server / cluster server. I would like to setup something from commandline to check for connectivity to each SQL Server instance on the ...
2
votes
1answer
674 views

Export all tables in database using SQLCMD

I'm looking to export all tables from a SQL Server Standard Edition database with data into CSV files onto a remote linux server. I setup FreeTDS and am able to connect, do SQLCMD or bcp successfully. ...
5
votes
2answers
240 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 ...
0
votes
2answers
193 views

SQL Server: Enable Full-Text Search on model Database

I want to use the Microsoft Data-Tier Application framework and its associated .Net API to manage the schema of several SQL Server databases, including the model database. However, when I try deploy ...
1
vote
1answer
3k views

SQLCMD Returning Named Pipe Error [closed]

I am trying to run any command from SQL Server Express from the command line using sqlcmd. Here is an example. sqlcmd -U myname-P mypwd -S mysys/SQLEXPRESS -d MASTER -Q "Select count(*) from ...
0
votes
0answers
236 views

SQLCMD.exe in SQL Server 2012 does not give real-time output logging [closed]

I'm running a long-running stored procedure using sqlcmd.exe for automation purposes. However, I observed that in SQL Server 2012, sqlcmd.exe does not produce real-time logging. It displays only when ...
1
vote
0answers
931 views

SQLCMD cannot find the SQL Server remotely

I am trying to use SQLCMD to remotely detach a database but for some reason it can't find the server. From the workstation, I can connect to the database instance (which is the default instance) using ...
8
votes
2answers
9k 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.
1
vote
1answer
65 views

What methodology is being used to poll SQL instances when using sqlcmd with the -L switch?

I've discovered that issuing sqlcmd -L from the console of a SQL host in my network only reveals other instances on the same subnet. It used to reveal all instances on the network so I was wondering ...
0
votes
1answer
292 views

SQLCMD - add date to CSV. When run inside Command Prompt it works, when in Job then %date

I've been battling for hours to add a date to a CSV name: For brevity, Im just taking the first character of the date here. In SQL Command, this works: sqlcmd -S localhost -d master -E -W -w 999 -s ...
2
votes
1answer
884 views

Profiling and debugging “Incorrect syntax was encountered while parsing :setvar.”

I have the following script using SQLCMD setvar, but I receive an error when I parse the script: :setvar dbname "PROD" :setvar backuppath = "e:\sql\restore\PROD.bak" USE [master]; GO -- backup the ...
1
vote
0answers
540 views

SQL Server, I can execute sqlcmd with trusted connection but not sqlpackage

We have some deployment scripts that use a combination of dacpacs through sqlpackage and straight scripts using sqlcmd. When I execute the sqlpackage dac\sqlpackage.exe ...
1
vote
0answers
305 views

sqlcmd returning NUL instead of NULL for tinyint column

sqlcmd -E -d MyDatabase -Q "sp_helpsubscription" -s "," -o file.csv -W -u Column 23 which is suppose to be a tinyint (subscriber_type) is output as NUL instead of NULL when the value is NULL. I ...
3
votes
1answer
2k views

How to get server cpu, memory, network usage from SQL Server

For reporting purposes, I want to get SQL Server box CPU, memory, network (not SQL Server process alone) and show it in report. Do you have any idea how to pull this? My initial thoughts are run wmi ...
0
votes
1answer
745 views

SQLCMD mode: environment variable syntax?

I assume the best way to set my SSMS (SQL Server 2005) output file path during runtime of T-SQL, is to use SQLCMD mode. How do I use environment variables (e.g. %systemroot%) in the code? The ...
2
votes
1answer
243 views

SQL CMD Mode Issue

SET QUOTED_IDENTIFIER on declare @source_server_name sysname IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp; create table #tmp( id int identity(1,1), server sysname ) insert into #tmp ...
3
votes
2answers
9k 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 ...
2
votes
1answer
511 views

Need to understand output of sqlcmd.exe

I was advised to make another question, from here - http://stackoverflow.com/questions/19674045/need-help-to-make-sqlcmd-run-as-per-requirements My sqlcmd.exe is invoked with a command of the form - ...
2
votes
2answers
652 views

net start sql service with startup option

From this article, I know to that slash (/) is used instead of a hyphen (-) when a net start is used with startup option, net start mssqlserver /m But if I want to have to use with startup option ...
4
votes
2answers
8k 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, ...