Tricks and issues Part 1

Today was browsing the Blog Pinal Dave expert on Sql server and great Blog writer. Got surprise to see the comments on his Blog. Great job and thank you Pinal for having such a wonderful site and sharing valuable information.

Found following information helpful to me today (*as I was having some issue related to this)

Bulk insert CSV and xls:

With this script we can import the CSV data into sql server table.

Following script helped me to import excel file

‘Data Source=D:\excelsourcefile.xls;Extended Properties=Excel 8.0’)…[Sheet1$]

I used sql server 2000 and sheet 1$ data

and if we want to export into xls we can use the below query but we have to give heading Name on excelTargetfile.xls file before running this query:

‘Data Source=e:\excelTargetfile.xls;Extended Properties=Excel 8.0;’)…[Sheet1$]
SELECT name FROM master.dbo.sysdatabases

we can use the same with OPENROWSET

Job server name change (Instance name). Error 14274:


While working on msdb database I restore msdb of other server/instance to different instance. found Pinal’s Blog helped me to solve my issue. by changing the server name in sysjobs table on sql server 2000.

We get the same error when server name changes for that we may have to follow Microsoft KB link as follows

Insert multiple records with single insert

Before sql server 2008 to insert multiple records with single query was very difficult, got the very good Blog by Pinal where he shows a trick to insert multiple records with single insert statement as follows link:

As Microsoft introduce new feature in insert statement were we can insert multiple records with single insert statement simply by a comma separated with multiple records value as follows:

Sending mail from sql server

using SMTP :

Following is a great KB articles by Microsoft, which explains everything and gives a sample script for the same.

We can use sqlmail and database mail for sending mail via sql server.

Writing this blog to keep things as a reference to me.




Twitter @thakurvinay


This entry was posted in What I learned today and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.