Archive

Posts Tagged ‘Vishal’

Importing data from Excel – using INSERT statements

March 29, 2012 6 comments

You can easily import data from an Excel file to SQL Server using SQL Server Import and Export Wizard. However, when the data is simple and limited I avoid using it (too lazy to click through 6 screens… 🙂 ) instead I generate INSERT statements using CONCATENATE function in Excel and execute those. This is much faster than using Import/Export Wizard.

This approach also requires the destination table to be created manually or it should already exists. Here’s how I use it:

1. The destination table I am using already exists with below columns, and some data:

image

2. The source Excel file contains below data:

image

3. Now to convert this to INSERT statement, we need to add a few columns to Excel sheet which will contain these texts "INSERT INTO dbo.ProductList VALUES (' ", " '' "," ' " etc. as shown below:

image

Note: if single quote (') is the first character in the column, as in Column C; you will need to input two single quotes (''). Also, if table has additional columns which are not being imported then you will need to include column list with INSERT statement i.e. "INSERT INTO dbo.ProductList (Name) VALUES (' " if you are importing only Name column.

4. Then you need to concatenate these columns using Excel's CONCATENATE function:

image

5. Finally, copy the same formula to all rows, these are the INSERT statements you need!:

image

6. That's all folks, Now copy them to SSMS and execute them…

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Exporting data to CSV file using SQLCMD/BCP

March 27, 2012 3 comments

When it comes to exporting data there are different options available in SQL Server, such as SQL Server Integration Services, Generating Insert Scripts, BCP, Import and Export Wizard and SQLCMD.

When it comes to exporting data as CSV file, BCP and SQLCMD are the easiest methods. Both are command-line tools which can be used to export data.

General syntax for SQLCMD is as follows:

SQLCMD S <<ServerName>> E Q "EXEC AdventureWorks2008R2.dbo.uspExport"
s "," o "C:\DataSqlCmd.csv" h1

Where, -S specifies Database Server Name,
-E specifies to use Windows Authentication,
-Q specifies the query to be executed against server,
-s specifies column separator character,
-o specifies the destination file and,
-h-1 specifies that column headers should not be exported.

You can provide a SELECT statement or a Stored Procedure to -Q switch. In general I use stored procedures as it allows to do any formatting required which cannot be done using SQLCMD.

I have created the stored procedure dbo.uspExport as below:

USE AdventureWorks2008R2

 

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 BusinessEntityID,

            JobTitle,

            Gender,

            BirthDate

FROM        HumanResources.Employee

The stored procedure simply returns top5 rows which are to be exported.

Executing the above SQLCMD command will generate the CSV file as follows:

          1,Chief Executive Officer                ,M,      1963-03-02
          2,Vice President of Engineering          ,F,      1965-09-01
          3,Engineering Manager                    ,M,      1968-12-13
          4,Senior Tool Designer                   ,M,      1969-01-23
          5,Design Engineer                        ,F,      1946-10-29

Same data can be exported using BCP as follows:

bcp "EXEC AdventureWorks2008R2.dbo.uspExport" queryout "C:\DataBCP.csv" c t , S (local) T

Here, -c specifies the character type format,
-t specifies the column separator,
-S specifies the database server name and,
-T specifies to use Windows Authentication.

The output generated by BCP is slightly different than of SQLCMD, output generated by BCP is as follows:

1,Chief Executive Officer,M,1963-03-02
2,Vice President of Engineering,F,1965-09-01
3,Engineering Manager,M,1968-12-13
4,Senior Tool Designer,M,1969-01-23
5,Design Engineer,F,1946-10-29

You can see that there is no padding while exporting using BCP by default, same can be achieved by using -W switch with SQLCMD.

 

If you need to implement any "complex" formatting, such as using delimiter for starting and end of row or change data format you can do this inside the stored procedure created for exporting data.

USE AdventureWorks2008R2

 

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 '#' + CAST(BusinessEntityID AS NVARCHAR(MAX)) + '#' +

            CAST(JobTitle AS NVARCHAR(MAX)) + '#' +

            CAST(Gender AS NVARCHAR(MAX)) + '#' +

            CONVERT(NVARCHAR(20), BirthDate, 107) + '#'

FROM        HumanResources.Employee

Here I have returned result as a single column with specifying ‘#’ as a delimiter and to denote start/end of a row. So all rows returned by stored procedure will be pre-formatted and -s or -t switches of SQLCMD/BCP will have no effect.

#1#Chief Executive Officer#M#Mar 02, 1963#
#2#Vice President of Engineering#F#Sep 01, 1965#
#3#Engineering Manager#M#Dec 13, 1968#
#4#Senior Tool Designer#M#Jan 23, 1969#
#5#Design Engineer#F#Oct 29, 1946#

 

To see all switches available for BCP/SQLCMD refer BOL links or execute them with -? at command prompt.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Identifying default data directory for multiple instances through registry

January 5, 2012 10 comments

This is a follow up post from a comment on my blog.

Earlier I posted about using xp_instance_regread to get default data location for current instance. It translates a given registry path to instance specific registry path. This can return the value for a single instance only. However, if you need to locate default data directories for all available instances on a machine, you need to use xp_regread, which reads from an absolute registry path.

Registry path for default data location is same for different SQL Server versions:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer

 

So the first thing we need before we can start reading data location from registry is list of instance name; these also can be read from registry using xp_instance_regenumvalues.

Registry path for instance name is as follows:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

We need to retrieve these to a temporary structure so that we can use it further:

CREATE TABLE #tempInstanceNames

(

      InstanceName      NVARCHAR(100),

      RegPath           NVARCHAR(100),

      DefaultDataPath   NVARCHAR(MAX)

)

 

INSERT INTO #tempInstanceNames (InstanceName, RegPath)

EXEC   master..xp_instance_regenumvalues

       @rootkey = N'HKEY_LOCAL_MACHINE',

       @key     = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'

      

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName  RegPath                    DefaultDataPath

MSSQLSERVER   MSSQL10_50.MSSQLSERVER     NULL

DENALI3       MSSQL11.DENALI3            NULL

SQL08ENT      MSSQL10.SQL08ENT           NULL

SQL05EXP      MSSQL.1                    NULL

We have a list of instance names; now we need to iteratively read registry value for each path; and append it to DefaultDataPath. The paths we need to read from registry will be:

1. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer

2. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.DENALI3\MSSQLServer

3. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL08ENT\MSSQLServer

4. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

These needs to be read using xp_regread. I have used dynamic SQL to iterate through the list:

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = 'DECLARE @returnValue NVARCHAR(100)'

SELECT @SQL = @SQL + CHAR(13) +

'EXEC   master.dbo.xp_regread

@rootkey      = N''HKEY_LOCAL_MACHINE'',

@key          = N''SOFTWARE\Microsoft\Microsoft SQL Server\' + RegPath + '\MSSQLServer'',

@value_name   = N''DefaultData'',

@value        = @returnValue OUTPUT;

 

UPDATE #tempInstanceNames SET DefaultDataPath = @returnValue

WHERE RegPath = ''' + RegPath + '''' + CHAR(13) FROM #tempInstanceNames

 

EXEC (@SQL)

 

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName         RegPath                    DefaultDataPath

MSSQLSERVER          MSSQL10_50.MSSQLSERVER     C:\Database\Data

DENALI3              MSSQL11.DENALI3            C:\Database\Denali\Data

SQL08ENT             MSSQL10.SQL08ENT           C:\Database\2008\Data

SQL05EXP             MSSQL.1                    C:\Database\2005\Data

I have updated the column in #tempInstanceNames so that it can be used further.

You can download the script from here.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Row count for all views / tables

December 28, 2011 2 comments

Getting row count for all tables in a database is straight forward. You can display row count for all tables by joining sys.objects and sys.partitions as below:

[UPDATE: sys.partitions only shows an approximation of the number of rows. (http://msdn.microsoft.com/en-us/library/ms175012.aspx)%5D

USE   [AdventureWorks2008R2]

GO

 

SELECT      SCHEMA_NAME(A.schema_id) + '.' +

            A.Name, SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

GROUP BY    A.schema_id, A.Name

GO

Result Set:

Person.Address                    78456

Person.AddressType                18

dbo.AWBuildVersion                1

dbo.BCPTest                       5

Production.BillOfMaterials        8037

Person.BusinessEntity             41554

Person.BusinessEntityAddress      78456

However, for views row count is not available in sys.partitions. To get the row count for a view; you must query the view itself.

USE   [AdventureWorks2008R2]

GO

 

SELECT COUNT(*) FROM HumanResources.vEmployee

GO

Result Set:

290

 

(1 row(s) affected)

This can be encapsulated in a stored procedure to query all available views and then display the result set. The procedure can be created as:

USE   [AdventureWorks2008R2]

GO

 

CREATE PROCEDURE dbo.ViewsRowCount

AS

BEGIN
SET NOCOUNT ON

CREATE TABLE #tempRowCount

(

      Name        VARCHAR(100),

      Row_Count   INT

)

 

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = ''

SELECT @SQL = @SQL + 'INSERT INTO #tempRowCount SELECT ''' +

            SCHEMA_NAME(schema_id) + '.' + name + ''', COUNT(*) FROM ' +

            SCHEMA_NAME(schema_id) + '.' + name +

            CHAR(13) FROM sys.objects WHERE type = 'V'

EXEC (@SQL)

 

SELECT      Name, Row_Count

FROM        #tempRowCount

END

GO

Once created this stored procedure returns row count for all views in database as bellow:

USE   [AdventureWorks2008R2]

GO

 

EXEC  dbo.ViewsRowCount

GO

Result Set:

Name                           Row_Count

dbo.vApplicationSpecialists    3

Person.vAdditionalContactInfo  10

HumanResources.vEmployee       290

Sales.vIndividualCustomer      18508

Sales.vPersonDemographics      19972

HumanResources.vJobCandidate   13

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Categories: SQLServer

SQL Server – How to identify service pack installed

December 25, 2011 9 comments

Identifying current service pack installed for SQL Server can be difficult if you don’t know which command to use! Most of the time I have seen people using @@VERSION to check for SQL Server service pack level, which is not correct as it returns the service pack level of operation system and not SQL Server.

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2005 – 9.00.5000.00 (Intel X86)

       Dec 10 2010 10:56:29

       Copyright (c) 1988-2005 Microsoft Corporation

       Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

Here the service pack reported is for Operating System. Service Pack for SQL Server can be identified by using SERVERPROPERTY() function as below, This returns SP4 which is the service pack level of SQL Server on my system:

SELECT SERVERPROPERTY('ProductLevel')

Result Set:

SP4

This has been "fixed" with SQL Server 2008+, @@VERSION now also returns service pack level for SQL Server:

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (Intel X86)

       Sep 22 2011 00:28:06

       Copyright (c) 1988-2008 Microsoft Corporation

       Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

As you can see from the output, both SQL Server and Windows service pack are displayed. You can use SERVERPROPERTY('ProductLevel') for a cleaner output.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – When will my backup finish?

November 18, 2011 6 comments

The sys.dm_exec_requests is a great way to find out how long the BACKUP will take to complete. If you are doing a backup WITH STATS or by GUI, you will not need to use the DMV. But, if someone else is running a backup (or a Job), you can find the percent of backup completed and estimated completion time using this DMV. Another easy way is to ask the person who is taking the backup :), but it’s not always possible.

The same thing happened to me recently (yesterday in fact!). I had to deploy a CR for an Application, and a Full database backup was to be taken in case a rollback is required. Now, if things were simple, I will take the backup and then proceed with CR deployment, but the database server is maintained by a different team about 4772 miles away! (managed by customer), and it’s not easy for people like me to keep staring at Outlook while waiting for backup completion notification from customer’s team.

When a backup is running, you can use the below query to check the progress, total_elapsed_time and estimated_completion_time returns milliseconds:

SELECT      command, percent_complete,

            'elapsed' = total_elapsed_time / 60000.0,

            'remaining' = estimated_completion_time / 60000.0

FROM        sys.dm_exec_requests

WHERE       command like 'BACKUP%'

Result Set:

command              percent_complete     elapsed       remaining

BACKUP DATABASE      50.75982             44.594500     41.207166

For a complete list of operation that reports percent_complete, check BOL for sys.dm_exec_requests.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Error Msg 102, Level 15, State 1, Line x when using sys.dm_db_index_physical_stats

November 14, 2011 2 comments

You run into this error when using sys.dm_db_index_physical_stats DMV on a database which is running under SQL Server 2000 (80) compatibility mode.

USE [SqlAndMe]

GO

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (DB_ID('SqlAndMe'),

            OBJECT_ID('dbo.ProductList'),

            1,

            NULL,

            'SAMPLED')

GO

Result Set:

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ‘SqlAndMe’.

There is nothing wrong with the syntax at all, yet you might spend time scratching your head!!! DMVs do not support a function as a parameter when compatibility mode is set to SQL Server 2000 (80). You can check the current compatibility mode using sys.databases:

USE [master]

GO

 

SELECT      name, compatibility_level

FROM        sys.databases

WHERE       name = 'SqlAndMe'

GO

Result Set:

name          compatibility_level

SqlAndMe      80

To use sys.dm_db_index_physical_stats without changing the database compatibility mode you can try one of the below solutions:

Solution 1: Run the statement in the context of a different database which has compatibility mode of SQL Server 2005 (90) or higher:

USE [master]

GO

 

SELECT      name, compatibility_level

FROM        sys.databases

WHERE       name = 'master'

GO

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (DB_ID('SqlAndMe'),

            OBJECT_ID('SqlAndMe.dbo.ProductList'),

            1,

            NULL,

            'SAMPLED')

GO

Since the compatibility mode of [master] is set to SQL Server 2008 (100) this will work fine. We need to use three-part name for object here as we are running the statement in the context of different database.

Result Set:

name          compatibility_level

master        100

 

database_id object_id   index_id    partition_number

6           1906105831  1           1               

Solution 2: Remove functions calls from arguments of sys.dm_db_index_physical_stats. Pass constants or variables instead. This solution does not require context switching and can be done from the context of the database itself:

USE [SqlAndMe]

GO

 

DECLARE @dbid VARCHAR(20)

DECLARE @objid VARCHAR(20)

 

SET @dbid   = DB_ID('SqlAndMe')

SET @objid  = OBJECT_ID('dbo.ProductList')

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (@dbid, @objid, 1, NULL, 'SAMPLED')

GO

Result Set:

database_id object_id   index_id    partition_number

6           1906105831  1           1               

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe