Tag Archives: sqlcmd

by

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
)

About these ads
by

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
)

by

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
)

by

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
)

by

SQL SERVER – Backup and Restore Database Using Command Prompt – SQLCMD

Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!

Let us see how DBA can automate their task about Backup and Restore using SQLCMD. I am demonstrating a very simple example in this blog post. Please adapt the script based on your environment and needs.

Here is the basic script for Backup and Restore

Note: name of my database server is touch and I am connecting it with windows authentication.

Backup

BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'

Restore

RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'

Here is the basic script for Backup and Restore using SQLCMD

Backup

C:\Users\pinaldave>SQLCMD -E -S touch -Q "BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'"

Restore

C:\Users\pinaldave>SQLCMD -E -S touch -Q "RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"

Please leave a comment if you are facing any issue. As mentioned earlier the scripts are very basic scripts, you may have to adapt them based on your environment. For example, if you are facing error there are chances that database files are already open or exists on the drive. You you should also have necessary permissions to do backup and restore as well file operations in your server.

Watch a 60 second video on this subject

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

by

SQL SERVER – Fix Visual Studio Error : Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL

In one of the virtual environment while I was trying to add SQL Server Database (.mdf) file to asp.net project I encountered following error:

Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: 

For a long time I am using SQL Server 2012 but this error was a bit interesting to me. I realize that there should not be any need of the SQL Server 2005 installation. I quickly figured out that I can remove this error if I do as mentioned below:

  • Open Microsoft Visual Studio
  • Select Tools >> Options >> Database Tools >> Data Connections
  • Enter the name of an installed instance in “SQL Server Instance Name” field.
  • Click OK

If you do not know the instance name, you can follow either of the options.

1) Use the command line sqlcmd utility

2) Using SQL Server Management Studio

Is there any other way to resolve this error?

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

by

SQL SERVER – Connecting to Server Using Windows Authentication by SQLCMD

Recently I got a call from an old friend I used to call “DJ”. Here is the exact conversation we had:

DJ: Pinal, your SQL Server does not like me.
Pinal: Possible, no one likes you!
DJ: I am serious…
Pinal: I am too…
DJ: No really – be serious.
Pinal: Well, you started it, tell me your problem.
DJ: This new SQLCMD thingy does not work. Everytime I am using my username and password it does not log me in.
Pinal: Have you tried with SSMS?
DJ: No, but it works fine. I just logged in my machine using it.
Pinal: Oh, are you trying to login using your Windows Authentication Username and Password?
DJ: Does it matter? I am the admin on my box.
Pinal: It does matter. Tell me your script.
DJ: sqlcmd -S .\sqlent1 -U username -P password. And then I’d been given “error 18456 – login failed for user.”
Pinal: Try sqlcmd -S .\sqlent1 -E
(After a while)
DJ: It works! So you still know SQL Server, what’d you say?
Pinal: I say  you still do not know SQL Server (evil laugh)
DJ: (extends the evil laugh)

Well, it was simple. He was trying to connect to SQL Server which was installed on his local box, where he was the admin. In this case, he can use a simpler script. This is a very specific situation and it was not the production server’s fault. Here is a copy of the scenario on my personal laptop.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Watch the video on the same subject:

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