SQL SERVER – Execute Operating System Commands in sqlcmd

Here is the email received just the other day -

“Hi Pinal,

I have been using sqlcmd for a long time and I find it very comfortable. I am going to soon learn Powershell as well.

However, here is one typical problem I face it every day and I want to check if you have any workaround.

When I work with sqlcmd I always connect via command prompt and whenever I want to check something back in OS, I always have to exit from sqlcmd or I have to open different window.

Is there any trick, which I can use to execute Operating System commands while I am still in sqlcmd prompt?

Thanks!”

Indeed a great question, as in my early career I had faced the same issue and I finally figured out the solution.

While we are in the sqlcmd mode at that time we can use !! (Double exclamation marks) to execute Operating System Commands.

Here is the example how you can do the same with the help of command prompt:

Here is the example how you can do the same with the help of SSMS:

First go to SSMS >> Menu >> Query >> SQLCMD mode

Once you enable SQLCMD mode in SSMS you can now execute any SQLCMD command in SSMS New Query Windows. You can also execute OS commands in this window.

If you know any such tricks, please leave a comment and I will publish on the blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Automatically Store Results of Query to File with sqlcmd

I receive lots of email everyday and I do answer almost every email I receive. However, I prefer to answer all the technical questions on my facebook page. With the help of social media it is very easy to reach out to multiple people with the same problem. Here is the recent question which I received on my facebook page.

“How I automatically execute my T-SQL script and save output of the query (resultset) in a different file automatically? I do not want to use SSMS”

Well, very interesting and simple question. It is very easy to direct the output of the query to a new file with the help of sqlcmd.

Let us work on this together.

Step 1: Create SQL file which you want to execute. I have created SQL file testquery.sql and saved it in my drive.

Step 2: Execute following sqlcmd command on prompt:

sqlcmd -i SQLFile.sql -S ServerLocation -E -o OutFileName.txt

If you are using windows authentication execute following script

sqlcmd -i testquery.sql -S localhost -E -o TestResults.txt

If you are using username and password run following script

sqlcmd -i testquery.sql -S localhost -U pinaldave -P password -o TestResults.txt

Step 3: Open the file generated by sqlcmd in the drive

This is indeed a very simple process. If you have any question, you can go to my Facebook Page and ask question.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Set Variable and Use Variable in SQLCMD Mode

Here is the question which I received the other day on SQLAuthority Facebook page. Social media is a wonderful thing and I love the active conversation between blog readers and myself – actually I think social media adds lots of human factor to any conversation. Here is the question -

“I am using sqlcmd in SSMS – I am not sure how to declare variable and pass it, for example I have a database and it has table, how can I make the table variable dynamic and pass different value everytime?”

Fantastic question, and here is its very simple answer. First of all, enable sqlcmd mode in SQL Server Management Studio as described in following image.

Now in query editor type following SQL.

:SETVAR DatabaseName “AdventureWorks2012″
:SETVAR SchemaName “Person”
:SETVAR TableName “EmailAddress
USE $(DatabaseName);
SELECT *
FROM $(SchemaName).$(TableName);

Note that I have set the value of the database, schema and table as a sqlcmd variable and I am executing the query using the same parameters.

Well, that was it, sqlcmd is a very simple language to master and it also aids in doing various tasks easily.

If you have any other sqlcmd tips, please leave a comment and I will publish it with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Interview Questions and Answers Sample Chapter Free Download – SQL in Sixty Seconds #050

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

There are two different milestones to celebrate today.

  1. This is the 50th Episode of SQL in Sixty Seconds
  2. Total play time for SQL in Sixty Seconds is One hour complete

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

You can watch the entire SQL in Sixty Seconds series over here

On SQLAuthority.com one of the most viewed and popular articles are SQL Server Interview Questions and Answers. It has been consistently asked and referred again and again. Earlier I also had a book released on this subject which is also very popular in the industry. Here in today’s SQL in Sixty second I explain how you can download the sample chapters of the popular SQL Server Interview Questions and Answers book in PDF for FREE.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049

One of the most annoying thing which I have personally come across is drop down list of Server Lists on Connect dialog in SQL Server Management Studio. Here are two of the cases when I want to delete something from SSMS Connect Screen: 1) Incorrect server name typed 2) Server does not require in the future. When I see a name of the server which is there for a long time and I know that I am not going to use it, I feel like deleting it right away so I do not have to see it again.

In SQL Server 2008 and earlier version there was a file in the installation folder once deleted it would remove all the cached login from the Connect drop down of SQL Server Management Studio. Here is the direction for SQL Server 2008 and earlier version. However, in SQL Server 2012 product team has made it very easy for all of us. We can just select the connection name which we want to do remove and click on delete and it will immediately remove the entry. This way, instead of removing every single entry, you can pick and select the entry which you want to delete.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Enable SQLCMD Mode in SSMS – SQL in Sixty Seconds #048

The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. Often a developer believes that sqlcmd works with only command prompt, however that is not true. sqlcmd can also work with SQL Server Management Studio. There are lots of cool tricks we can do with sqlcmd while we are using it along with T-SQL.

One of the tricks which I often use it that I use it to connect to different servers from SQL Server Management Studio while I work with multiple SQL Server instances. This way I do not have to keep on connecting/disconnecting the server using SSMS prompt. This makes things very easy as well it is less distracting. The code which you can use to connect to another server is as follows:

SELECT @@SERVERNAME
GO
:Connect localhost
SELECT @@SERVERNAME
GO

In this SQL in Sixty Seconds video I have demonstrated how we can list Windows Directory while we are working with SSMS. The display of the SQLCMD is visible in the message area of the SSMS result pane. The code of the same is as follows:

USE AdventureWorks2012
GO
SELECT DISTINCT Type FROM Sales.SpecialOffer;
GO
!!DIR
GO
SELECT ProductCategoryID, Name FROM Production.ProductCategory;
GO

This trick is described over here.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Exporting Query Results to CSV using SQLCMD

Social media is evolving at a rapid pace and every day I keep on getting question from different methods. Here is the latest question which I received on my Facebook page. The question was how to export the data of query into CSV using SQLCMD.

This is indeed very easy process and very simple command to export any query data. For example we will use AdventureWorks2012 database. Here is the query we will be using for our demonstration.

USE AdventureWorks2012
GO
SELECT TOP 10 sp.BusinessEntityID,
sp.TerritoryID, sp.SalesQuota,
sp.Bonus, sp.CommissionPct
FROM Sales.SalesPerson sp
GO

The above query will return following result set.

Now we can export above data to CSV using SQLCMD using following command.

SQLCMD -S . -d AdventureWorks2012 -Q “SELECT TOP 10 sp.BusinessEntityID, sp.TerritoryID, sp.SalesQuota, sp.Bonus, sp.CommissionPct FROM Sales.SalesPerson sp” -s “,” -o “e:\result.csv”

Generically you can use the following syntax:

SQLCMD -S YourSQLServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\Yourfilename.csv”

Now you can go to your file location and open the file and you will see that new csv file created there. When you open the csv file you will notice the results of the query.

Watch the video on the same subject:

Reference: Pinal Dave (http://blog.sqlauthority.com)