Q.--> An alternative of OpenRowSet in SQL Server


I have created a procedure which uses Dynamic PIVOT to get a result. I have a table which contains column DateRange with values Jan-2014 Feb-2014 Mar-2014 etc...

Using dynamic query in PIVOT, I am able to get the desired output by passing columns from table. The @Columns variable is set by using Stuff function with FOR XML Path. By passing this @Columns variable into dynamic sql query for PIVOT I am able to achieve the following:

Result table:

Col1   | Jan-2014  | Feb-2014 | ...............
ABC    | 123       |11231     | ...............
ASDF   |123123     |123123    | ...............

Now I have to create a physical table which will be having a result from the above procedure as shown in the table. As I mentioned the procedure is containing dynamic query, it is having EXECUTE. Because of which I am not able to create a table inside the procedure.

I tried to create a view as well but that also didn't worked as my procedure contains variable declaration.

To achieve this I used OpenRowSet in a script that runs on a daily basis.

My problem is: I cannot use OpenRowSet, and I am not able to create a physical table from a stored procedure. The OpenRowSet contains connection string that is why I cannot use it.

Please suggest...


My procedure for creating dynamic PIVOT table:

        @Columns AS NVARCHAR(MAX)
        ,@Query AS NVARCHAR(MAX)

                         FROM View
                         ORDER BY ',' + QUOTENAME(DateRange) 
                         FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 1, '')

SET @Query = 'SELECT Col1, Col2, ' + @Columns + 
             ' FROM (SELECT Col1, Col2, [DateRange], Col3 FROM View) A
                 (SUM(Col3) FOR DateRange IN (' + @Columns + ') ) S '

Execute (@Query)

and script for creating a physical table

INTO Physicaltable 
    OPENROWSET('SQLOLEDB', 'ConnectionString',
               'SET FMTONLY OFF;SET NOCOUNT ON;EXECUTE (AboveProcedure)')
  • 3
    Why do you believe you cannot create the table in the stored procedure? If you passed in a table name as another parameter you could include that in your dynamic query changing the SELECT to be a SELECT...INTO... I hope the real procedure doesn't just build a query from Columns like that though, at the very least I would have the procedure error out if Columns contained any single quotes. – Mister Magoo Nov 16 '14 at 23:12

If you cannot use OPENROWSET, you could create a loopback linked server and use OPENQUERY or EXECUTE AT.

DECLARE @srv nvarchar(4000);
SET @srv = @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server     = N'LOOPBACK',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
@datasrc    = @srv;

-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'LOOPBACK',
@useself     = N'True',
@locallogin  = NULL,
@rmtuser     = NULL,
@rmtpassword = NULL;

I blogged about it some years ago.

Another option is using some kind of CLR trickery to interpret the result set definition and create a target table based on the type and length of the columns.

If you're not allowed to use OPENROWSET, I highly doubt you will be allowed to use CLR, though. In this case, a third option could be using sp_describe_first_result_set and code your table creation script in T-SQL. It requires SQL Server 2012 or newer.

| improve this answer | |
  • Thanks spaghetti... Although I cannot use Openquery as well as it also includes sort of server information. And about sp_describe_first_result_set, we are having 2008 R2 so that is not possible as well – Rohit Jain Nov 14 '14 at 5:28
  • @RohitJain What about a CLR procedure? – spaghettidba Nov 14 '14 at 7:09
  • Thanks bro! but I am able to solve the issue. A very silly solution: I added the creation of table in my dynamic sql query. – Rohit Jain Nov 17 '14 at 5:18
  • @RohitJain and @spaghettidba: regarding sp_describe_first_result_set and not being on SQL Server 2012, there is an equivalent proc in my SQL# library ( SQLsharp.com ) called DB_DescribeResultSets. Yes, it is SQLCLR, and yes, it is in the Full version only (i.e. not free), but it does run on SQL 2005, 2008, and 2008 R2 :). – Solomon Rutzky Nov 17 '14 at 7:02

Solution for this :

SET @Query = 'SELECT Col1, Col2, ' + @Columns + ' INTO PhysicalTable

Execute (@Query)

And now I am able to remove the OpenRowSet.

| improve this answer | |

Your Answer

