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)

0
votes
0answers
8 views

sqlcmd on Azure SQL Data Warehouse - SqlState 24000, Invalid cursor state after INSERT statement

I am working on a script to reload a table using SQLCMD on Linux connecting to an Azure SQL Data Warehouse database. After the INSERT statement completes, the next statement fails (but does not end ...
0
votes
0answers
31 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.[...
3
votes
1answer
2k 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
121 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 ...
3
votes
1answer
199 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: ...
2
votes
0answers
53 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. ...
1
vote
1answer
68 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 ...
3
votes
2answers
3k 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 ...
6
votes
3answers
1k 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 %...
3
votes
3answers
12k 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
1answer
38 views

Monitor if sqlserver is up from distant server using sqlcmd

I want to monitor if my sqlserver is up from a distant machine. I currently send a simple "select @@version" query with sqlcmd and then validate if that query returned the expected part of text : ...
2
votes
1answer
38 views

Using SQLCMD with Variables

Im trying to do some general script that i can use to run the same INSERT/UPDATE or DELETE statements on different site databases. This was my first attempt which works just fine, but i have to copy ...
2
votes
1answer
18 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 ...
0
votes
1answer
45 views

SQLCMD -L returns a number of servers that definitely do not have SQL installed

I have been running this command in our test domain: sqlcmd -L And have found that amongst all the valid results, it also returns several servers that definitely do not have SQL server installed. ...
2
votes
1answer
82 views

How to automatically fix Orphaned Users with PowerShell and SQLCMD

I'm trying to properly capture output from SQLCMD in SQL Server 2012. Here is the PS script: $dbname = "MyDB" $InstanceTarget = "localhost\SQLEXPRESS" $FixOrphansSQL=@" WITH login_CTE (name, type, ...
0
votes
1answer
127 views

Database backup over sqlcmd call from batch file

I'm trying to do a database backup by sqlcmd called from batch file but I can't achieve that. I'm getting error "Sqlcmd: Error: Connection failure. SQL native Client is not installed correctly. To ...
1
vote
1answer
174 views

Ola Hallengren's Backup solution error message after moving files using cmdexec stops job, this disagrees with faq

I am using Ola Hallengren's Maintenance script, and I am having an issue which seems documented in the FAQ. For context, there was an issue where the existing SQL Server backups had to be moved to ...
2
votes
2answers
191 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)\...
14
votes
1answer
415 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 ...
6
votes
2answers
473 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 -...
4
votes
1answer
87 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
59 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 ...
2
votes
1answer
109 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 ...
7
votes
1answer
842 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 ...
3
votes
1answer
92 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 ...
11
votes
2answers
13k 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
61 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 * ...
0
votes
1answer
82 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
0
votes
0answers
40 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 ...
3
votes
2answers
252 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 $(...
2
votes
2answers
3k 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
3k 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.
2
votes
1answer
489 views

Named Pipe Provider Error code 40 [closed]

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 ...
4
votes
2answers
10k 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
3answers
1k 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
104 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 ...
2
votes
1answer
104 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 sqlcmd....
1
vote
1answer
55 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 ...
1
vote
1answer
110 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 ...
3
votes
2answers
1k 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 ...
3
votes
1answer
951 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 ...
1
vote
1answer
133 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
655 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
133 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 ...
4
votes
1answer
1k 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 ...
1
vote
0answers
376 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 ...
1
vote
0answers
246 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
266 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 ...
2
votes
1answer
853 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. ...
1
vote
1answer
5k 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 sys....