Today was browsing the Blog http://blog.sqlauthority.com/ 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
* FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’,
‘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.