Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for the ‘Screenshot’ 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 »

A-Z of In-Memory OLTP : SSMS Enhancements for In-Memory

Posted by blakhani on December 10, 2013


Microsoft believes that everyone in the world is not a developer there are some DBAs as well. Be it Visual Studio, SQL Server or any other programming paradigm – Microsoft makes it easy for developers and DBA alike. In my previous post, you would have read about new T-SQL constructs added to support In-Memory OLTP objects. This blog post shows the SSMS improvements and dialogs introduced with SQL Server 2014 to work with In-Memory OLTP.

CREATE DATABASE

We have learned earlier that we need a new filegroup which would contain memory_optimized_data. This is the key point to understand the flow of actions below..

  • Right Click on database folder in SSMS and Choose “New database”. This is the same place where we create regular database.

image

  • Open “New Database” interface, go to “Filegroups” tab. Then click on “Add Filegroup” under “Memory Optimized Data” section.

image

  • I have given name as “SQLSeverHelp_IMO_FG” and then went back to “General” tab.

image

  • Here is the little explanation of numbered steps.
    1. We need to come back to “General” Tab after creating filegroup in earlier step.
    2. Provide “Database name” SQLSeverHelp_IMO
    3. Click on “Add” button and provide details in new row (third row)
    4. We have given SQLSeverHelp_IMO_dir as logical name
    5. Since it is going to contain In-Memory optimized data, we need to use “FILESTREAM Data” in “File Type”
    6. Once we choose drop down in step 5, “Filegroup” would be “SQLSeverHelp_IMO_FG” created in previous screen.
    7. Provide complete physical “Path” where the files/data needs to be stored for In-Memory tables. C:\IMO_Database\SQLSeverHelp_IMO_dir 

Follow sequence given in above image and hit OK.

Do you know that I can remember all command by heart? I am joking! If you want to script this action, use the “Script” button (shown in below image). This would give us the same script (with little more setting) which we use in previous part.

image

The “Script” feature is available in almost every user interface of SSMS. Another option is to use predefined template. This is one of under-utilized/less known feature of SSMS. As shown below, go to “View” Menu and click on “Template Explorer” (Ctrl+Alt+T) to access a lot of predefined scripts.

image

  • Once we click on “template explorer”, we would see “template browser” opened within management studio. Over there, we can see many template and one of them of our interest, as of now, is highlighted in below image.

image

  • Double click would open the template and we need to use “Ctrl+Shift+M” to get window called “Specify Values for Template Parameters”

image

  • We can make changes here and hit OK. We can edit path, name and modify the TSQL as per our requirement.

If you have an existing database then, ALTER DATABASE would be needed. This is because the filegroup for memory_optimized_data might not have been defined. We can go to database properties and add new filegroup and follow same screen with 7 steps.

CREATE TABLE

Next step is to create an In-Memory Optimized table. Note that there is no “table designer” UI to create the In-Memory Optimized table. If you have followed earlier steps, template explorer might be open (else press Ctrl+Alt+T)

Option # 1
In situations where we have an existing database which is not having memory_optimize_data filrgroup defined, we can use template called “Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table” (shown below)

image

Option # 2
If we have memory optimized filegroup in the database then we can choose “Create Memory Optimized Table”

image

Option # 3
If we go with conventional way of creating table using SSMS then we can Right Click on “Tables” folder under the database SQLSeverHelp_IMO and choose New > Memory Optimized Table

image

All three options would open a query window with the template. (See, I told you earlier – no table designer)

image

In this query windows, we need to use Ctrl+Shift+M and replace the parameter as needed. We can add more columns, indexes and make modifications. As the commented section in above template says, we need to have filegroup defined for memory_optimized_data

image

In a nut-shell, in the absence of UI, you need to have T-SQL syntax knowledge.

CREATE PROCEDURE

Same as CREATE TABLE, there is no special interface available for natively compiled procedure. We can use either Right Click technique (#1) or template browser (#2) to reach to template.

Option # 1

image

Option # 2

image

Both of the options above are going to open template and then we can write the code there. Remember the shortcut Ctrl+Shift+M for filling template parameter.

image

Till now we have learned how to create objects. In next post, we would see what happens differently (than regular objects) when in-memory tables are in-memory stored procedures are created. Stay tuned!

Missed reading earlier posts? Here is the quick reference:

Stay Tuned!

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

Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, Introduction, screen shot, Screenshot, SQL Server 2014, SQL Server Management Studio, SSMS | 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: , , , , , | 16 Comments »