SQL SERVER – How to use xp_sscanf in Real World Scenario?

I need your help. I recently came across extended stored procedure xp_sscanf. After reading a lot about it and searching online, I could not figure out how and where in real world, I will use this function.

Microsoft documentations suggest that this extended stored procedure reads data from the string into the argument locations specified by each format argument. I still do not get it. I know it is very similar to C function but again I am not sure where in the real world I will use this function. Here is the demonstration of how this function works.

Following example is an enhanced version of the example provided on MSDN.

DECLARE @valueb VARCHAR(20), @filename VARCHAR(20), @message VARCHAR(20)
EXEC xp_sscanf 'sync -b123 -fauthors10.tmp -rrandom', 'sync -b%s -f%s -r%s',
@valueb OUTPUT, @filename OUTPUT, @message OUTPUT
SELECT @valueb, @filename, @message

The above query will return us following result:

-------------------- -------------------- --------------------
123                  authors10.tmp        random

Here is the result set.

You can see how xp_sscanf retrieves the parameters from the string and returns as an output. However, I am still not sure where I will use this feature in real world scenarios. Any insight and or guidance will be helpful.

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

About these ads

SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

It has been a long time since we have played a puzzle over this blog. This Monday, let us play a quick puzzle.

SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string.


The script above will return following result:


You can notice that it has returned a string ha about 20 times after first Ha.

Now let us run following script.

SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Here is the second part of the Quiz.

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

Please leave a comment with your answer. I will publish the answer to this puzzle on coming Friday’s blog post and will mention the names of the all the valid users.

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

SQL SERVER – Caption the Cartoon Contest – Last 2 Days

Developer’s life is very interesting, we often want to start my day early at a job so we can go home early. However, the day never comes as the life of the developer is always about working late hours. If the developer goes to the office early – there are good chances that his co-workers will come late. Additionally, I am confident that there will be always something urgent for developers or DBA to solve right at the time they are ready to go home. This is the life of the developers! 

Here is the interesting story of a DBA who was about to go to the home. He had to take his girlfriend to a movie and dinner in 30 minutes. However, his manager asks him to fix the performance related issues with their production server. In normal case, he had only two choices a) Job or b) Girlfriend. Well, our super hero DBA decided to use efficient tools and improve the performance of the production server in merely 30 minutes. When he was done, his manager was absolutely surprised by his efficiency and accuracy of the work. He asked him following question -

Here is the contest – you need to guess what was the answer of our Super Hero DBA. If you guess the answer correct you may win Star Wars R2-D2 Inflatable Remote Controlled device.

Additionally, if you Download DB Optimizer before Dec 8, 2012 – you will be eligible for USD 25 Amazon Gift Card (there are total 10 such awards).

Please do not leave comments in this thread – to participate in the contest – please leave a comment here in the original contest page.

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

SQL Contest – Win USD 300 Worth Gift – Cartoon Contest is Back

There are two excellent contests and we have lots of winning to do this year end.

1) Win USD 25 Amazon Gift Cards (10 Units)

This is very simple, you just have to download SQL Server DB Optimizer. That’s it!

There are only two conditions:

  1. You must have a valid email address. As USD 25 Amazon Gift Card will be sent to the same address.
  2. Download DB Optimizer between today and Dec 8, 2012.

Link to Download DB Optimizer.

Every day one winner will be notified about their winning USD 25 Amazon Gift Cards for next 10 days.

2) Win Star Wars R2-D2 Inflatable R/C

This the coolest thing to win. I personally want one but as I am running a contest, I can’t  participate.

You get this cool Remote Controlled Device – you just have to answer following cartoon contest. Read the complete story and think what will be the answer provided by the smart employee.

There are only two conditions:

  1. Leave your answer in the comment area of this blog post (every comment will be hidden till Dec 8, 2012).
  2. Please leave your answer in the comment area between today and Dec 8, 2012.

Remember you can participate as many times as you want. Make sure that your answer is correct and creative. The most creative answer will be selected. The decision of contest owner will be final. We may have runner’s up prices but for the moment let us try to win R2-D2. Here is the cool video of R2D2.

Now here is the cartoon story, please follow the story and complete the very last cartoon template. Your answer should be correct and should be creative. However, the ideal answer will not be longer than one or two sentences.

Hint: (Hint)

Well, Leave your answer in the comment area of this blog post. If you do not win R2D2, trust me there are chances you may win a surprise gift from me. Remember your answer should be correct and should be creative. However, the ideal answer will not be longer than one or two sentences.

Last day to participate in both of the contest is Dec 8, 2012. We will announce the winner in the week of December 10.

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

SQL SERVER – Recover the Accidentally Renamed Table

I have no answer to following question. I saw a desperate email marked as urgent delivered in my mailbox.

I accidentally renamed table in my SSMS. I was scrolling very fast and I made mistakes. It was either because I double clicked or clicked on F2 (shortcut key for renaming). However, I have made the mistake and now I have no idea how to fix this.

I am in big trouble. Help me get my original tablename.”

I have seen many similar scenarios in my life and they give me a very good opportunity to preach wisdom but when the house is burning, we cannot talk about how we should have conserved the water earlier. The goal at that point is to put off the fire as fast as we can. I decided to answer this email with my best knowledge.

If you have renamed the table, I think you pretty much is out of luck. Here are few things which you can do which can give you idea about what your tablename can be if you are lucky.

Method 1: (Not Recommended but try your luck)
Check your naming convention of your system. I have often seen that many organizations name their index as IX_TableName_Colms or name their keys as FK_TableName1_TableName2_Cols. If your organization is following the same you can get the name from your table, you may refer your keys. Again, note that this is quite possible that your tablename was already renamed and your keys were not updated. This can easily lead you to select incorrect name. I think follow this if you are confident or move to the next method.

Method 2: (Not Recommended but try your luck)
This method is also based on your orgs naming convention. If you use the name of the table in any columnname (some organizations use tablename in their incremental identity column name), you can get that name from there.

Method 3: (Not Recommended but try your luck)
If you know where your table was used in your stored procedures, you can script your stored procedure and find the name of the table back.

Method 4: (Try your luck)
All the best organizations first create a data model of the schema and there is good chance that this table is used there, you should take your chances and refer original document. If your organization is good at managing docs or source code, you will get the name of the table back for sure.

Method 5: (It WORKS but try on a development server)
There is no sure way to get you the name of the table which you accidentally renamed however, there is one way which will work for sure. You need to take your latest full backup and restore it on your development server (remember not on production or where you have renamed this column). Now restore latest differential file of the full backup. Now restore all the log files one by one making sure that you are restoring before the point of time of you renamed the tablename. Now go to explore and this will give you the name of the table which you have renamed. If you are confident that the same table existed with the same name when the last full backup was made, you do not have to go to all the steps. You can just get the name of the table directly from last backup’s restore. Read the article about Backup Timeline.

Method 6: Interesting Solution by Muhammad Imran
I suggest you to go ahead and read it yourself.

How can I miss to preach wisdom when I get the opportunity to do so? Here are a few points to remember.

  • Use a different account to explore production environment. Do not use the same account which have all the rights and permissions all the time. Use the account which has read only permissions if there are no modification required.
  • Use policy based management to prevent changes which are accidental. If there was policy of valid names, the accidental change of the table was not possible unless it was intentional delibarate changes.
  • Have a proper auditing of the system in place.
  • You can use DDL triggers but be careful with its usage (get it reviewed properly first).
  • (Add your suggestion here)

I guess Method 5 will work all the time (using point in time restore). Everything else is chance of luck and if you are lucky are bad – you will get further incorrect name.

Now go back and read the first line of this blog. Out of five method four methods are just lucky guesses. The method 5 will work but again it is a lengthy process if the size of the database is huge or if you do not have full backup. Did I miss anything obvious? Please leave a comment and I will publish your answer with due credit.

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

SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1

Earlier on this blog we had asked two puzzles. The response from all of you is nothing but Amazing. I have received 350+ responses. Many are valid and many were indeed something I had not thought about it.

I strongly suggest you read all the puzzles and their answers here - trust me if you start reading the comments you will not stop till you read every single comment. Seriously trust me on it. Personally I have learned a lot from it.

Let us recap the puzzles here quickly.

Puzzle 1:

Why following code when executed in SSMS displays result as a * (Star)?


Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

Bonus Q:

How many different Operating System (OS) NuoDB support?

As I mentioned earlier the participation was nothing but Amazing. I will write about the winners and the best answers in short time. Meanwhile I will give to the point answers to above puzzles.

Solution 1:

When you convert character or binary expressions (char, nchar, nvarchar, varchar,binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

Reference of the text and table from MSDN.

Solution 2:

The shortest code to produce answer 1 :






When SELECT $ it gives us the result as 0.00 and the EXP of the same is 1. I believe it is pretty neat. There were plenty other answers but this was the shortest. Another shorter answer would be PRINT EXP($) but no one has proposed that as in original Question I have explicitly mentioned SELECT in the original question.

Bonus Answer:

5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS Reference

Please do read every single comment here. Do leave a comment which one do you think is the best comment out of all the comments. Meanwhile if there is a better solution and I have missed it do let me know as we still have time to correct it. I will be selecting the winner before the weekend as I am going through each and every of 350 comment.

I will be selecting the best comments along with the winning comment. If our selection matches – one of you may still win something cool. 

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

SQL SERVER – 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes

We all love brain teasers and interesting puzzles. Today I decided to come up with 2 interesting puzzles and winner of the contest will get USD 50 worth Amazon Gift Card. The puzzles are sponsored by NuoDB. Additionally, The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.

Puzzle 1:

Why following code when executed in SSMS displays result as a * (Star)?


Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

Bonus Q:

How many different Operating System (OS) NuoDB support?
Click here HINT

If you can solve above puzzles you will be eligible for winning USD 50 Amazon Gift Card. However, you can always enroll yourself for following Bonus Prizes where if you have good chance of winning USD 10 Amazon Gift Card (if you are first 25 individual in specific time).

Bonus Prizes:

The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.


  • Please leave an answer in the comments section below.
  • You can resubmit your answer multiple times, the latest entry will be considered valid.
  • The winner will be announced on 1st October.
  • Last day to participate in the puzzle is September 28th, 2012.
  • All valid answer will be kept hidden till September 28th, 2012.
  • Only One Winner will get USD 50 worth Amazon Gift Card.
  • The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
  • The winner will be selected using random algorithm.

UPDATE: The winner of the contest is Jaime Mtnz Lafargue (@jaimeml)

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