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)

12
votes
2answers
23k views

How to pass in parameters to a SQL Server script called with sqlcmd? [closed]

Is it possible to pass parameters to a SQL Server script? I have a script that creates a database. It is called from a batch file using sqlcmd. Part of that SQL script is as follows: CREATE DATABASE ...
10
votes
6answers
736 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 ...
9
votes
2answers
6k 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?
9
votes
2answers
3k 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 ...
5
votes
3answers
810 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
1answer
618 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 ...
4
votes
2answers
203 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 ...
4
votes
3answers
862 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
602 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 ...
4
votes
1answer
434 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
4k 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 ...
4
votes
1answer
889 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 ...
4
votes
1answer
2k 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 ...
4
votes
1answer
1k 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. ...
3
votes
2answers
2k 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. ...
3
votes
1answer
928 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
1answer
1k 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 ...
3
votes
1answer
962 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 ...
2
votes
2answers
866 views

Running a number of .sql files using SQLCMD

I'm trying to put together a batch file to run a large number of .sql files without doing so individually. So far I have: @echo off echo "Enter Server" set /p SERVER= echo "Enter Database" set /p ...
2
votes
2answers
2k 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
3k 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, ...
2
votes
2answers
258 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 ...
2
votes
1answer
208 views

FOR XML is generating a empty first line

I'm parsing with flash a XML file generated by this code: :XML ON USE MyDatabaseName GO SET NOCOUNT ON SELECT * FROM ProgramacionDia as programa order by hora FOR XML AUTO, ROOT ('toflash'), ...
2
votes
1answer
144 views

SQLCMDMODE from SSMS. Can I set -b mode when using :r to include files?

As far as I read msdn there is no variable that corresponds to the -b flag of the command (on error batch abort). I found no to enable this mode inside a script when running in sqlcmd mode. Is there ...
2
votes
1answer
93 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 ...
2
votes
3answers
854 views

Is there a way to tell SQLCMD not to output rows to text or a file?

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows ...
2
votes
1answer
2k views

How to modify a stored procedure using SQLCMD for MS SQL?

Suppose a stored procedure as follows have been created using the Microsoft SQL Management Studio. use testbase go create procedure testtable_pricesmaller @pricelimit money as select * from ...
2
votes
2answers
234 views

How can I test sql scripts intended to run with sqlcmd -v in Sql Server Management Studio

From msdn I learn, that the Variable Precedence of :Setvar X Y is higher, than that of sqlcmd -v X=Y. I want to write a script, that uses the variables supplied to sqlcmd with the -v option, but ...
2
votes
1answer
291 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
1answer
348 views

Sqlcmd command strips $ out of query variable

What I am trying to do Restore a database from backup using sqlcmd. Sqlcmd is being called by PowerShell. What is the issue The server folder which contains the database backups has a $ inside of ...
2
votes
1answer
695 views

Sql Server 2005 - sqlcmd with output to shell and file

I do some command line batch (.bat) with sqlcmd as this way: sqlcmd -i Scripts\STEP01.sql -o PROCESS.log -S MYSERVER -E -d MYDATABASE and i need an output file (it's works currently) and also the ...
2
votes
1answer
325 views

Named Pipe Provider Error code 40

I have literally tried everything, from enabling named pipe to adding exception to ports in the firewall, to everything possible in surface configuration. I can connect to the SQL instance(using TCP ...
2
votes
0answers
502 views

Network issues with sql server

There are two servers on co-location. First is Windows Server Standard 2008 R2 SP1. Second is Windows Server 2012 + Sql Server Standard 2012 SP1. First server has a task with a report against tables ...
2
votes
2answers
348 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.
1
vote
2answers
625 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?
1
vote
2answers
733 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
135 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
3answers
3k 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 ...
1
vote
1answer
34 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 ...
1
vote
1answer
261 views

Database created in sqlcmd but missing in SQL Server Management Studio [closed]

I created a database named testbase in sqlcmd in SERVER\MSSQLSERVER. However, I cannot access SERVER\MSSQLSERVER. I also could not find testbase inside the server connection using Microsoft SQL ...
1
vote
2answers
2k views

Database table data transfer between different servers using bcp.exe and mysqldump

I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY. Using MSSQL on two different servers I am using the following command ...
1
vote
0answers
127 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 ...
1
vote
0answers
113 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 ...
0
votes
1answer
184 views

Is it possible to generate table with generated data without manual INSERT statements?

Is it possible to use SQLCMD in SQL Server 2008 to create a table and generate data and insert into the table, without manually typing the INSERT statement for each row? Example: Suppose there are ...
0
votes
1answer
530 views

What does this SQLCMD statement do?

I am new to SqlCmd. Can anyone give a short explanation of the following command? SQLCMD -S VMLIVE-DATA\SQLSERVER -Q "RESTORE DATABASE GIS_ENG FROM DISK = ...
0
votes
4answers
628 views

Writing SQL statement in sqlcmd to hide the milliseconds for column type of time

I have a table called testtable that have a column named expire_time of type [time] The problem is that when I type the following in sqlcmd: select * from testtable It will return the following: ...
0
votes
1answer
1k views

attach database - verify - detach from a script or a batch file

for a hundred good reasons .... and I promise that at least a few are good ... I find myself with almost 200 SQL server databases (mdf,ldf files) generated by different versions of sql server, most ...
0
votes
2answers
38 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 ...
0
votes
1answer
65 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 ...
0
votes
0answers
50 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 ...