Feeds:
Posts
Comments

Posts Tagged ‘OPENROWSET’

SQL SERVER – How to retrieve the metadata of a stored procedure

Posted in Solutions, SQL SERVER, tagged , , , , , on January 18, 2014| Leave a Comment »

In my earlier articles, I wrote about sys.dm_exec_describe_first_result_set
(A new dynamic management view shipped with SQL Server 2012), it is very handy when you need to know the metadata of the first possible result set of any Transact SQL. Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server. So I started developing the solution.

Given below are the solution.

SOLUTION 1 : For SQL Server 2012 and above using sys.dm_exec_describe_first_result_set

USE AdventureWorks2012;
GO
SELECT *  FROM sys.dm_exec_describe_first_result_set
(N'[dbo].[uspGetManagerEmployees] 16', null, 0) ;
--OUTPUT

metadata of stored procedure.1.1

SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET

USE AdventureWorks2012
GO
--DROP TABLE #temp1
GO
SELECT * INTO  #temp1
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=sa;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');
GO

SELECT * FROM tempdb.sys.columns
WHERE object_id=object_id('tempdb..#temp1')
GO
--View the column name / metadata of the
--above created temporary table.
--OUTPUT

metadata of stored procedure.1.2

Read Full Post »

Step by Step – Data Migration from Excel 2007 and above to SQL Server

Posted in SQL SERVER, Tools, tagged , , , , , , on February 22, 2013| Leave a Comment »

It is but a common practice to migrate data from Excel to SQL Server; mostly so, when we implement a new system and the initial data is required from the customer.
I mostly use OPENROWSET to migrate data instead of import/export tool to avoid multiple steps.

Given below is the script to migrate data from Excel 2003:

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 4.0;Database=D:\test.xls','SELECT * FROM [sheet1$]')

But, when the same code is used for Excel 2007 & above, it gives the following error:

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 4.0;Database=D:\test.xlsx','SELECT * FROM [sheet1$]')

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

Lets solve this issue step by step.

Step 1 :
Download Microsoft Access Database Engine 2010

Step 2 :
Install Microsoft Access Database Engine 2010 in your machine.

Step 3 :
Open SQL Server Management Studio. (If already open, close and re-open)

Step 4 :
Enable Ad hoc queries.

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 5 :
Close the excel sheet that you need to import (if open).

Step 6 :
Give the correct path, file name and excel sheet name with $ sign in the script given below and execute the query.
This information must be correct.

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\test.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

If your first row has a column name then “HDR=YES“, else it should be No.

In case you need this query result in temporary tables, given below is the script to insert Excel records into temp table.

SELECT * into Temp FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\test.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

Note : You can use the same technique for Excel 2003 files as well.

Read Full Post »