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 server. Wherever it fails to connect to an instance I would like to have an email (we have smtp server setup) sent to our DBA team. Each server is having more than 5 instances and we are unable to track them without customer complaining about connectivity, hence trying to be proactive. Can we try something like osql or sqlcmd?

I have a SQL Server that is running under Windows. And I have a Linux client. AFAIK SQLCMD requires "local" (server-related) path to backup files. So the question is - how to specify windows paths (e:\backups\db_name\etc) under Linux shell or script? Do I need to dublicate backslashes? Or can I use forward slashes?

I am trying to run an MSSQL script using sqlcmd on Windows 10. The command is something like this:

sqlcmd -S "host" -U "user" -P "password" -d "database" -C -i "script.sql" -I

where the values here are dummy values. I’m getting the message:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server :  Data source name not found and no default driver specified.

I didn’t set up the system, so I don’t know how everything has been installed, but I assume that the sqlcmd was installed with SQLServer Express. When I check the version, I get:

Microsoft (R) SQL Server Command Line Tool
Version 14.0.2052.1 NT
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

I checked the installed drivers and got: I don’t know where to go from here. Is it possible to get sqlcmd to use the relevant driver? Why is it looking for version 13 anyway?

I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job. This is what i am doing. Created a SQL server agent job 1) XCOPY source *.bak /Y destination 2) Ren *bak mydbbackup.bak Questions: *What mistake am i doing here ?* *Are there any xcopy **/**commands which can copy the latest files only and ignore old files ? Is there a way i can do that using another SQLCMD command besides creating a stored procedure* Thanks, learner

I am working on SQL server task. I need a sqlcmd result file with complete records with headers. Here is the command and error message below sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [DB_Name] -W -y0 -Q "select test_desc from Test0 " 1>>Result.txt **Error:** Sqlcmd: The -W and the -y/-Y options are mutually exclusive. Has anyone had this problem before? Thoughts on how to solve it? **Actual Data:** test_desc dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i9128489823hrfhbdcx  dcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217hbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscde2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns s fqwhvchedcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i91= **-W command:** **-W** command is not useful for large data. the columns are getting truncated at 256 bytes. sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [DB_Name] -W -Q "select test_desc from Test0 " >> Result.txt Result.txt: test_desc --------- dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i9128489823hrfhbdcx ÿdcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv234821`13142414v42vhv4jh1b2j1bjhb412489ywq8e8772 **-y0 command:** -y0 command returns complete records of a column, but column header is missing. sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [DB_Name] -y0 -Q "select test_desc from Test0 " >> Result.txt Result.txt: dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i9128489823hrfhbdcx ÿdcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217hbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscde2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns s fqwhvchedcuyxghbscvafvagwefdhewfdsh47346221bhv3g4h321bj14bh5vj4jh3b2jb23283234e2hv23482113142414v42vhv4jh1b2j1bjhb412489ywq8e8772845478129804217dkbrgewejdnxd dsvdwjih32i9885y3ehdc svajns seebdjsxnbyewnj92348y3384uhbc xmsbhfgwqhdnscdhvrewdusjijeurgadsbcnnwj0i91= (1 rows affected)

I have an issue where when I log into SSMS I am not seeing any logins. I am running as an administrator and logging in with SA. It does not matter which account I log in with. I only ever see the two logins listed. If I try and make a change or create a login I get access denied. I used PStools.exe and still get the same Access Denied Error. I can not make even the slightest changes with PStools.exe, Sqlcmd or SSMS.

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. > C:\Users\Tester>sqlcmd -L > > Servers: > ;UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?; > *APP:AppName=?;*WSID:WorkStation ID=?; The SQL Server is running normally and the SQL Browser service is started. Querying the SQL Browser service with another tool on the same server yields the expected results. I can run regular SQL queries with SQLCMD and get the expected results. I was able to verify that the output above is the same on a workstation where I caused SQLCMD -L to receive no responses at all by enabling the firewall; however, the firewall is not an issue on this server. I've checked the settings and, as stated previously, my other tool gets SQL Browser Service responses to a broadcast just fine. Also, running WHERE reports that the path to SQLCMD is C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE, which is correct per File Locations for Default and Named Instances of SQL Server . Does anyone have any idea why SQLCMD is doing this and how to fix it?

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 %systemroot%\syswow64: How do I tell SSMS to look in the folder that the current script is saved to without explicitly declaring the path?

*Similar issue seen [here](https://dba.stackexchange.com/questions/223664/sql-command-mode-unable-to-run-command).* I am trying to run the following command from SSMS running in SQLCMD mode.

:r C:\dev\test scripts\sample.sql

But I get the following error:

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :r .

I have checked the file, the line endings are appropriate for the Windows machine, and the batch separator matches the settings. What else can I try to run this?

I have a sql script that truncates the table and insert into database from a linked server.Since the server is SQL express I will need to use batch jobs to schedule the jobs. Below is a snippet of the batch job that calls the sql script TRUNCATE TABLE [TABLE1].[dbo].[TableData] WAITFOR DELAY '000:00:01' INSERT INTO [TABLE1].[dbo].[TableData] SELECT * FROM [SourceServer].[SourceDatabase].[dbo].[VW_View] The batch jobs works fine if I use the below code but since osql is deprecated I will need to use sqlcmd which is runs in a loop and never stops. osql -S myserver -E -i "c:\test.sql" -o "c:\output.txt" --- Runs fine sqlcmd -S myserver -E -i "c:\test.sql" ----- Does not work and goes to a loop that does not end. I will appreciate if someone can provide me the correct syntax for the sqlcmd.

I'm working with a vendor about this, but a supplied script that they have to run to upgrade their software, after running, ruins access to SQLCMD. Before running the script I can open a Command Prompt window > SQLCMD > Drops me in. After running their scripts, when attempting to do the same thing, get the error: Access is Denied I've tried dropping a copy of SQLCMD.exe from a known good working server into same location where the affected server was, but even trying to manually invoke it from the location, receive a: "Windows cannot access the specified device, path, or file. You may not have the appropriate permissions to access the item". Going to permissions > Security, it has all the same permissions as the known good working file, as is the entire directory path. So obviously osmething deeper, but I cannot / do not know where to start to diagnose.

I've just been granted access to a SQL Server data warehouse and I'm trying to get to grips with sqlcmd. Coming from a Postgres/psql background, I'm used to being able to quickly look up a table definition using short cuts like \d+ [tablename]. This produces a nice, single screen vertical summary of the table columns, datatypes and constraints. Something like this: The equivalent I've found so far is EXEC sp_help 'table_name' but this prints an unreadable, multi-page mess. Is there a way to clean up this output? Is there a better TSQL command that will give a simple table summary?

I'm trying to install sql server 2022 on a build machine, and then test that I can connect to that server with sqlcmd. I previously had ODBC driver 17 and was getting errors from that command, so I uninstalled that driver and installed ODBC driver 18 instead but I'm still receiving a Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Data source name not found and no default driver specified. error from that command. How can I be getting an ODBC driver 17 error when that driver is not installed on the machine? This seems quite related to [this question](https://dba.stackexchange.com/questions/314745/sqlcmd-error-microsoft-odbc-driver-17-for-sql-server-data-source-name-not-fo) which remains unanswered.

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 from cmd prompt I ran the following: sqlcmd -s LOCALHOST -U 'FYI_DBA' -P 'V_2017' Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ''FYI_DBA''.. What am I doing wrong?

This is my code:

SET @msg = 'Customer Voice replies.';  
SET @query= 
	'SELECT [column1],[column2] FROM [StagingMX].[dbo].[_CVA] 
	WHERE DATEDIFF(day, [Completion time] ,GETDATE()) = 1 
	AND [column1] = IQS 
	AND [column1] = MED;'
	;
	
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'cvApp'
    , @recipients = 'someone@gmail.com'
    , @subject = 'queryresultset'
    , @body= @msg
    , @body_format = 'TEXT'
    , @query = @query
    , @execute_query_database = 'StagingMX'  
    , @attach_query_result_as_file = 1
    , @query_attachment_filename='CustomerVoice.csv'
    , @query_result_header = 1
    , @query_result_width = 100
    , @query_result_separator = ' '
    , @exclude_query_output = 0
    , @append_query_error = 1
    , @query_no_truncate = 0
    , @query_result_no_padding = 0;

And the result I'm being given is: *Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259.* My query works outside of sp_send_dbmail, but would not work inside it. And if I change my query for the following code, it will send the email and work as expected:

@query = 'SELECT TOP(1) * FROM [StagingMX].[dbo].[_CVA] ORDER  BY [ID] DESC;',

Also, I have already executed:

GRANT EXECUTE ON sp_send_dbmail TO PUBLIC

So far, I've concluded that: - I have the permissions to send emails. - I am able to send emails with queries (both as attachment and variable). I don't know for sure, but I suspect that the problem has to do something with the limitations of the clause *WHERE* inside sp_send_dbmail ? Anyway, I read the following statement in a forum, and I believe he went through the same error I am getting: >After hunting around a lot more, I found a few articles discussing security and granting permissions that sent me on a wild goose chase and ultimately made no difference to the outcome. The script continued to work outside of sp_send_dbmail, but would not work with it. I don't recall how, but I have subsequently discovered that stored procedures the indexing script allegedly uses had gone AWOL (ones created by the file CommandExecute.sql), even though the indexing script worked fine outside of sp_send_dbmail. Once I created those stored procedures, everything seems to work as it should! I am recently new to SQL, and I've tried different ways to do this but to no avail. Could someone please guide me with code through what the former statement is referring to ? Or perhaps, point me into the right direction ? At this point anything helps.

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 + ' -d ' + @dbName + ' -U ' + @UserName + ' -P ' + @Password + ' -Q "' + @sqlCommand + '" -s "," -h -1 -k 1 -W -o "' + @outputfileName + '"' @sqlCommand is set correctly, to "EXEC StoredProcedureName parameterValue" and when run independently apparently produces the correct data. The problem I'm having is that one of the fields contains text greater than 256 characters long and is getting truncated when fed through the SQLCMD command. On reading the [documentation](https://msdn.microsoft.com/en-us/library/ms162773.aspx) I see that the default column width is 256 characters. So I'm looking at using the -y or -Y option to allow more than 256 characters to be output. However, these options aren't compatible with the -W option which I was using to remove trailing spaces from the output. I started by using -y 0 (despite the performance warning - once I get the output working I can look at the performance). However, that produces some strange results. The file consists of a header row followed by the data rows and should look something like this: First,ABC,Number,String,ReallyLongString,... A,0123,14.99,"Short string","Longish string",... B,0456,23.99,"Normal string","Really, really long string that's causing problems",... With the -W option the file format is correct but the really long string is truncated. The reason we found it was because the trailing " was missing and the file wasn't being read properly. With -y 0 the really long string is getting output but large numbers of spaces are being added to apparently random columns. We're getting something like this: First,ABC ,Number [...] ,String,ReallyLongString,... A ,0123,14.99 [...] ,"Short string","Longish string",... B ,0456,23.99 [...] ,"Normal string","Really, really long string that's causing problems",... (There are many, many more spaces between "Number" and the next column as represented by the "[...]", I'm just showing a few). There are more numeric values that have to be formatted in a similar way and it does appear that it's these that are causing the extra spaces after the value and before the next comma. We can live with a few extra spaces, but not as many as this as the resultant file is too large to be read by the target program. The data is generated by a stored procedure that looks like this: SELECT 'First' AS First, 'ABC' AS ABC, 'Number' AS Number, 'String' AS String, 'ReallyLongString' AS ReallyLongString, ... UNION ALL SELECT 'A' as First, Column1 as ABC, REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', '') as Number, -- to get the format correct '"' + Column3 + '"' as String, '"' + Column4 + '"' as ReallyLongString, .... FROM Table WHERE I'm thinking that the problem is the output from the stored procedure. I only added the -W option to the SQLCMD because of the extra trailing spaces being output, but looking at the stored procedure I can't work out where they are coming from. I changed the number formatting to include RTRIM: RTRIM(REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', '')) but that appeared to make no difference. Is there something I can do to the stored procedure or is there a combination of options to SQLCMD that will produce the desired output? Or am I going to have to find some other way of producing this file?

I am using this script to backup database on computer A:

sqlcmd -U MyDatabase -P MyPassword -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation = 'c:\shared', @databaseName='MyDatabase', @BackupType='F'"

everything works fine, but I decided to store backup files to computer B. I shared folder on computer B, which is accessible from:

\\computerB\shared

I changed backup script to include new backup location:

sqlcmd -U MyDatabase -P MyPassword -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation = '\\computerB\shared', @databaseName='MyDatabase', @BackupType='F'"

But when I run this, I am getting an error: > Cannot open backup device '\\computerB\shared\MyDatabase_FULL_101 > 22022_232734.BAK'. Operating system error 5(Access denied .). Msg > 3013, Level 16, State 1, Server WIN-C28934ASNF1\SQLEXPRESS, Line 1 > BACKUP DATABASE is terminating abnormally. I don't understand why am I getting Access denied. I can easly eccess \\computerB\shared from file explorer on computer A. What is wrong there?

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 Something like: :setvar source_server_name = SELECT @@servername Thank you **Update 7/15/2013** The two answers offered below did not quite give the desired result, so I'm adding a more relevant exampe. :setvar source_server_name [myserver] The variable source_server_name is set to the text string [myserver_1] I'd like to be able to do this: create table #tmp( id int identity(1,1), server sysname ) insert into #tmp values('myserver_1'),('myserver_2'); :setvar source_server_name = SELECT server FROM #tmp WHERE id = 1 select '$(source_server_name)' (No column name) myserver_1 The variable source_server_name would be set to the value in server for id 1.

The following sqlcmd fails with no error and no output, just returns silently.

-U dbuser -d dbname -w 255 -h-1 -P mypassword -S dbserver -Q "ALTER TABLE xyz ADD abcd NUMERIC(1)" -c;

While it works fine without the -c; argument

-U dbuser -d dbname -w 255 -h-1 -P mypassword -S dbserver -Q "ALTER TABLE xyz ADD abcd NUMERIC(1)"

This parameter has been there since before 2005, with OSQL, then recently (less than a year ago) we replaced OSQL with SQLCMD. And now we have discovered that one of our install scripts is not executing and it has come down to this -c; argument. Also, I am testing on SQL Server 2019. I asked someone to execute the same scripts on a SQL Server 2008 and there the SQLCMD is working with the -c; parameter. I have even tried specifying : and a few other characters instead of ; to no avail. How should this parameter be used? And if it is not recommended or not supported to use it any more, then please point to any documentation/bug report/release notes.

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) However, when I'm using the SQLCMD utility on the same machine, against the same SQL Azure server, with the same credentials, it always answers Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user 'myUser' The syntax I'm using is sqlcmd -S tcp:myServer.database.windows.net -U myUser@myServer -P "myPassword" I tried everything I could think of: - Added the port: myServer.database.windows.net,1433 - Removed the tcp: protocol - Enclosed the user in double quotes, like the password - Tried the -I and -C command line options - Combinations of the above. The error is always the same. Is there some specific flag that has to be activated for SQLCMD to work with SQL Azure? Any other ideas?