Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for the ‘Step by Step’ Category

Did you know? You can generate Insert Statement with data using Management Studio!!!

Posted by blakhani on December 13, 2013


I must confess that at least I didn’t know about that option till I saw an internal email thread. And my first reaction was – Damn! all these years and never spotted that! Yes, we can get all of the INSERT statements for the data straight out of SSMS. Another hidden feature of SSMS.

Earlier, I used to search on internet and get some stored procedure to get the insert statements generated for me. Here were few search strings which used to give me proper results “SQL Generate Insert Script”, “SQL Server Generate Insert Script”, "SQL Server Script Insert Statements", "Create Insert Script SQL Server", "Generate Insert Script from Table"

Now I can avoid all those searches and do that right from the Management Studio. For demo purpose I would create a small database and show that option. Please note that I am using SQL Server 2012 Management Studio (SSMS 2012)

CREATE DATABASE [myAppDB]
go
Use myAppDB
go
Create table HelloSQL (iID int, vFname varchar(100), vLName varchar(100))
go
insert into HelloSQL values 
(1, 'Balmukund', 'Lakhani'),
(2, 'Vinod', 'Kumar M'),
(3, 'Pinal', 'Dave'),
(4, 'Sourabh', 'Agarwal'),
(5, 'Amit', 'Banerjee')
go
Select * from HelloSQL
go

 

To achieve our insert statements with script use Object Explorer and connect to an instance of the SQL Server. Expand Databases folder, Choose the database for which we want the script, right click, Tasks > Generate Scripts.

Once Wizard is launched, its self-explanatory to navigate through screen.

image

After welcome screen, we need to choose the object (table HelloSQL in our case) for which we want script to be generated.

image

In next screen, we need to choose the destination of script. I have selected “Save to new query window”

image

In the same screen, we have a magical button called “Advanced”. That’s where we have many options about scripting.

image

I have selected “Schema and Data” under “Type of data to script” and hit OK. Hit Next on previous screen

image

hit Next and Finish.

image

… and we have what we needed. Schema creation as well as Insert Statement for the data in table.

image

There are many other options in advanced tab. Play with them and get the desired script. I have learned this and it was new to me. Hope this is useful to you also. SQL Server generate script with data is a powerful SQL Server tool in order to create SQL script to move data from one database to another database. SQL programmers can export data as sql script by using one of the existing SQL Server tools, SQL Server Generate Script Wizard.

 

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOnPaperback, Kindle

Advertisements

Posted in Screenshot, SQL Server, SQL Server Management Studio, SSMS, Step by Step | Tagged: , , , , , , , | 4 Comments »

Help: How to fix error – Reason: Server is in single user mode. Only one administrator can connect at this time

Posted by blakhani on December 6, 2013


First of all this is not normal to start SQL Server in single user mode. So if you are reading this blog by getting this as a search result, you might be in a tough situation. In some situations, like restoring system database or during disaster recovery you may need to start SQL in single user mode.

Okay, so you have started SQL Server in single user mode by specifying start-up parameter “m” either by net start MSSQLServer /m or via command prompt sqlservr.exe –m –sInstanceName but when you are trying to connect via any tool (SQLCMD, OSQL, SQL Server Management Studio or any other) you are welcomed by error message.

TITLE: Microsoft SQL Server Management Studio
——————————
Error connecting to ‘(local)\SQL2k8R2’.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS
OK
——————————

Error message is clear that someone “else” has made connection to SQL Server and due to /m switch, SQL is not letting you get in. Now, logically there are two ways to fix this problem.

  • Find out who is connecting before you and stop that application (difficult in real/disaster time)
  • Ask SQL Server to not to allow anyone except me.

Second one sounds more easy. So let’s discuss that.

If you want to know the root cause of “why its in single user mode” then go back and look for SQL Error Log and you may find something as below.

SingleUser_01

In above Errorlog, we can see additional start-up parameter and warning that SQL is in single user mode.

Essentially we want to start in single use mode and no one else except you should be able to connect. Books online has explained this clearly that you can append m parameter with the client application name. Here are few example

  • Only SQLCMD should be able to connect then it would be m”SQLCMD”
  • If you want to use Management studio only then it would be m"Microsoft SQL Server Management Studio – Query".

Let’s see it in action. I can add start-up parameter in multiple ways. My favorite is staring SQL from command line as a service, My instance name is SQL2K8R2 so below would be the command

Net Start MSSQL$SQL2K8R2 /m”SQLCMD”

SingleUser_02

If you have default instance than it would be

Net Start MSSQLServer /m”SQLCMD”

Once you have started SQL Service in Single use mode then only SQLCMD application can connect and other connection would get error message.

image

Let’s see what we nave in ERRORLOG

2013-12-06 09:13:50.08 Server      Registry startup parameters:
     -d E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2013-12-06 09:13:50.08 Server      Command Line Startup Parameters:
     -s "MSSQLSERVER"
     -m "SQLCMD"

2013-12-06 09:13:50.80 spid4s      Warning ******************
2013-12-06 09:13:50.80 spid4s      SQL Server started in single-user mode. This an informational message only. No user action is required.

….

2013-12-06 09:14:32.93 Logon       Error: 18461, Severity: 14, State: 1.
2013-12-06 09:14:32.93 Logon       Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

It’s important to note that string after –m parameter is case-sensitive. This means that if you give sqlcmd (all lower case) then connection can’t be made. If we want to use management studio then the parameter would be –m”Microsoft SQL Server Management Studio – Query”. This is the exactly same name as in program_name when you look at sys.processes or sys.dm_exec_sessions

Hopefully this blog would help you in making a connection to SQL Server without stopping application, changing password, disabling account as there were the tricks I have seen to get into SQL when only one connection can be made and unfortunately that’s not you.

 

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOnPaperback, Kindle

Posted in Connectivity, Error, Screenshot, Step by Step | Tagged: , , , , , | 20 Comments »

Now Available @ Amazon : My Book on SQL Server 2012 AlwaysOn

Posted by blakhani on August 5, 2013


It might come as surprise to many of you. Finally, book authored by me is available for public on Amazon. (paperback and kindle). Those who follow Vinod’s (b|t) blog have seen this announcement already.

It has been close to an year of writing and multiple reviews, my book on “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” is finally available on Amazon.

Reason of this blog is to answer two common questions which would be asked by my friends – “How?” and “Why?”

“How” –

A little spark was ignited when me, Vinod (b|t). and Pinal (b|t) were sitting together after a User Group meeting and thinking – what can be done to share the knowledge. Pinal and Vinod were already a well-known blogger and author. I said in feeble voice – I may have some additional knowledge on AlwaysOn, how about a book on this topic? … all of us agreed and journey started.
Thanks to Vinod and Pinal for the spark of inspiration to set me off into looking things from different perspective. I wouldn’t have thought to become an “author” without your support. 

After take off of the project, table of content of 5 chapters were prepared and we have taken task to write everything about the feature. I got my home PC upgraded to better RAM so that I can use my home PC for Hyper-V images which were needed for the book. We targeted 100 page and during weekly checkpoint meeting we always thought “something is missing” … after reaching 500+ pages, we felt that we have covered almost everything about the feature which can bring a starter to intermediate level.  Decision was taken to increase the page size and trim down the number of pages without compromising quantity and quality of knowledge. Lots of hours spent on emails, phone calls, chat, internet research, books-online reading, blogs reading to make fine prints. Finally, after an year of hard efforts, book was ready to be published. In paperback as well as kindle version.

“Why” –

It feels good when someone meets and says – “You blog helped me in fixing a problem”. Being a part of SQL Server technical support team, I felt that there is not much “organized” content on AlwaysOn feature. I spoke to many folks and sensed a “fear” of this new feature. There are various blogs on the same topic and books online also has lots of content. By writing this book and sharing my knowledge , I feel that I am doing my part for the community which is driven by passionate people and MVPs.  I love technology, SQL Server and its features. There were many questions asked and being of a very tidy disposition, I liked to have them answered. That’s why we added a new chapter in the book for FAQs along and there were around 70+ questions.

What we have in the book?

Here is the crisp summary of the book.

clip_image001

18 Chapters at a Glance

Chapter 1. High Availability and Disaster Recovery Concepts
Chapter 2. Existing High Availability and Disaster Recovery Options
Chapter 3. What is AlwaysOn?
Chapter 4. Understanding Quorum Models
Chapter 5. AlwaysOn Availability Groups
Chapter 6. Availability Group (AG) Actions on the Secondary
Chapter 7. Deploying AlwaysOn Availability Groups
Chapter 8. Features of AlwaysOn Availability Groups
Chapter 9. AlwaysOn Monitoring and Troubleshooting
Chapter 10. AlwaysOn Diagnostics
Chapter 11. AlwaysOn Advanced Monitoring
Chapter 12. Deployment Variations of AlwaysOn
Chapter 13. AlwaysOn Common Issues
Chapter 14. Availability Group Failover
Chapter 15. Migrating from Previous High Availability Scenarios
Chapter 16. AlwaysOn Availability Group Maintenance Activities
Chapter 17. AlwaysOn – FAQs
Chapter 18. SQL Server 2014 – AlwaysOn Enhancements Teaser

 

How can you get it?

At this moment we don’t have any other channel to publish this book in India. Our publisher is in constant pursuit to look for alternatives, till then Amazon will the only channel.
Print Book: Amazon
e-Book: Kindle

You can read first two chapters for free on amazon via LOOK INSIDE feature on kindle edition.

image

 

I truly hope that it would help many DBAs in the world to get started with AlwaysOn Availability Group feature and deploy as high availability + disaster recovery in the environment.

Before I conclude this blog post, I want to specifically call out name of few people from my management team who have backed me in this adventure of learning and sharing via this book – Sri Krishna Jagannath (Team Manager – SQL Support), Rahul Jacob (Team Manager – SQL Support), Xavier S Raj (Escalation Manager – SQL Support), Ranjan Bhattacharjee (Group Manager – Developer Support). It would have been impossible to deliver this book without their unconditional support.

Last, but not the least, Special Thanks to my wife and daughter for being supportive through out this journey!

 

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, Introduction, SQL Server, SQL Server 2012, Step by Step, TechEd | 11 Comments »