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.

  • 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
              FROM (SELECT Col1, Col2, [DateRange], Col3 FROM View) A
                 (SUM(Col3) FOR DateRange IN (' + @Columns + ') ) S '

Execute (@Query)

And now I am able to remove the OpenRowSet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.