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)

About these ads

SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047

One of the most common errors database developer’s receives when they start working with database where there are different collation used. Collation is a very important concept but it is often ignored. First use the method displayed in this video to resolve your error and right away put your efforts to understand what collation stands for.

Language is the most important part of communication. We all communicate with each other through language which both persons to understand. If we do not talk in the language which the other person cannot understand, the end result is not fruitful. In a similar way, collation is very important to any database. I have often seen situations when the collation changes from case sensitive and case insensitive turning the query result hey-wire and creates chaos on the server.

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 – Shortcut to SELECT Single Row from Table – SQL in Sixty Seconds #046 – Video

Earlier I have blogged about the same subject and in very short time I received lots of good comments about this blog post as well lots of email from users who faced issues to make this work. Thought, the instructions are very simple in the blog post, every user read it differently and they have a different interpretation. I finally decided to do convert the same blog post in the video. I hope now it will be much easier to understand it.

If you watch any SQL Server Developer, you will notice one particular task them doing every day frequently. It is they select the row from the table to see what are the various kinds of data it contains. It is very cumbersome for developers to continuously write following code to retrieve a single row to see what the table contains. There is a shortcut how we can make our life easier if we use SQL Server Management Studio Shortcut. In this quick video we will see how we can create a shortcut which will select a single row from a table.

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 – Cycle Clipboard Ring in SSMS – SQL in Sixty Seconds #045 – Video

Copy and Paste! In other words – CTRL + C and CTRL + V – these two are our famous shortcuts for this new age. Remember copy paste is not a bad thing but plagiarism is for sure. I rely on a lot of Copy Paste when I am doing development. There are so many templates, code or name of the objects (tables, stored procedure) etc which we need when we are doing development. If we keep on typing those names, there are chances of making human error which can lead to further problems.

Now the problem with the copy paste is that we can only paste one item which was the last copied. There are often scenarios when we want to paste more than one value without keep on going back again and again to copy them. I was very much delighted when I discovered the shortcut of the Cycle Clipboard Ring and Paste it.  However this shortcut is not very popular among developers – CTRL+SHIFT+V. I have created a short video which describes the same.

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 – Shortcut to SELECT only 1 Row from Table

If you watch any SQL Server Developer, you will notice one particular task them doing every day frequently. It is they select the row from the table to see what are the various kinds of data it contains. Most of the tables are very big so it is always advisable to retrieve only a single row from the table. It is very cumbersome for developers to continuously write following code to retrieve a single row to see what the table contains.

SELECT TOP 1 * FROM TableName

I suggest you try to write above code and there is good chance that one has to look at the keyboard to type above code as it requires numbers. Even expert typist often has to look at the keyboard when they have to deal with Special Symbole (in our case *) and number (in our case 1) when typing subsequently. The counter argument is to not use above command but use SP_HELP or any other SP which describes the schema of the table. Well, this may be a good idea but we developer like to do things our own way. Just to be clear it is never a good idea to do any development on production server.

There is a shortcut how we can make our life easier if we use SQL Server Management Studio Shortcut. You can see following image where I am configuring SSMS shortcut.

Go to SSMS Menu >> Tools >> Options >> Environment >> Keyboard >> Query Shortcuts.

Now on the right side click on the preferred shortcut (in this case I am selecting CTRL + 3) and now in the column of the Stored Procedure type following statement

SELECT TOP 1 * FROM

Pay special attention that I have not mentioned the name of the table. The syntax is incomplete as we will SELECT the name of the table later on of SSMS Query Editor.

Once that is done, now go to SSMS and select any preferred table name. While the table name is selected type CTRL + 3 and you will notice that the query will run and will return a single row as a resultset. Now developer just has to select the table name and click on CTRL + 3 or your preferred shortcut key and you will be able to see a single row from your table.

This trick can be very helpful when a developer is debugging a long stored procedure with lots of a table name or while writing complex query.

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

SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video

“How do I restore my backup?”

I often receive this question from two kinds of users – i) who are panicking as they are not able to restore database ii) developers who have full backup but not sure how to restore it and use it.

In industry everywhere you go, everybody is taking backup but I noticed hardly anybody try to restore it. When users have not restored the backup for a long time, they have either no expertise to restore or have no idea if their backup have an issue while restoring.

Earlier I have created a SQL in Sixty Seconds Video on How to Take Backup of the database, in this video we learn how we can restore the same backup.

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 – Get SQL Server Version and Edition Information – SQL in Sixty Seconds #043 – Video

What do consultants do when they come across any new instance of SQL Server? Well, their very first question is what version of SQL Server is it? The reason is simple – SQL Server is a very vast product and each version of the product have new features released and old features deprecated. Many consultant even remembers service pack and features released in it.

Well, there are multiple ways to know the version numbers of the SQL Server. In this sixty second video we will see a neat trick where we will quickly find the version number of SQL Server.

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)