All,

I am using all the necessary criteria for the openrowset procedure in
MSSQL to execute a select statement against a server and database about
which my procedure knows nothing. I would like to use the select
statement passed into my procedure to build a temporary table (select *
into #temp_table from openrowset(...)), in order to obtain the exact
table structure and column names.

I know that if I build a string to be executed using sp_executesql it
will create the table just fine but only for the duration of the
sp_executesql procedure. Leaving me with nothing. I am wondering if
there is any way whatsoever to do this?

I know that when programming website programmers will use stored
procedures to execute select statements, obtaining the result set, but I
do not think you can capture a result set in this way inside a sql
stored procedure. Or can you? (can't use a function because that would
require me to know the structure of the return table).

Thanks in advance!

Cliff

*** Sent via Developersdex http://www.developersdex.com ***

[posted and mailed, please reply in news]

Clifford Schneider (cliff@dimension2000.com) writes:
[quoted text, click to view]

This *may* work:

CREATE PROCEDURE my_openrowset AS
DECLARE @remote_sql nvarchar(3560)
SELECT @remote_sql = sql FROM #sql
EXEC ('SELECT * FROM OPENROWSET(....' + @remote_sql + ''')
go
CREATE TABLE #sql (sql nvarchar(3560 NOT NULL)
INSERT #sql(sql)
VALUES('<Your remote SQL statement goes here>')

SELECT * INTO #tmp
FROM OPENQUERY(LOOPBACK, 'SET FMTONLY OFF EXEC my_openrowset')

LOOPBACK is here a linked server that goes back your own server.

SET FMTONLY OFF is necessary, as OPENQUERY first runs the query it
gets with SET FMTONLY ON to find the structure of the result sets,
but since FMTONLY ON turns off execution, it will find none. By
turning off FMTONLY you trick it. But notice that this causes the
OPENROWSET query to be run twice.

Hm, maybe this could prevent it?

CREATE PROCEDURE my_openrowset AS
DECLARE @remote_sql nvarchar(3560)
SELECT @remote_sql = sql FROM #sql
IF @@rowcount = 1
BEGIN
DELETE #sql
EXEC ('SELECT * FROM OPENROWSET(....' + @remote_sql + ''')
END
go
CREATE TABLE #sql (sql nvarchar(3560 NOT NULL)
INSERT #sql(sql)
VALUES('<Your remote SQL statement goes here>')

SELECT * INTO #tmp
FROM OPENQUERY(LOOPBACK, 'SET FMTONLY OFF EXEC my_openrowset')

By deleting from the temp table, we avoid running the OPENROWSET
twice.

None of this is tested, and I'll be surprised if it works in all situations.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Erland,

I tried several variations of what you suggested and every time I was
faced with the same problem. No matter which way I went about it there
was always something essential part of your process that was out of
scope somehow.

I did come up with a working solution although I'm not sure how much I
like it. I'll post the code below just in case you are interested:

create table #new_record_set (dummy_column int null)


set @sql_script_for_table_structure = ' select top 0 * into
#for_structure_only from openrowset(' + char(39) + @provider_name +
char(39) + ',' + char(39) + @datasource + char(39) + ';' + char(39) +
@user_id + char(39) + ';' + char(39) + @password + char(39) + ',' +
char(39) + @query_string + char(39) + ') ' + char(13) +
' declare @object_id int ' + char(13) +
' declare @sql_string nvarchar(4000) ' + char(13) +
' set @object_id = (select object_id(' +
char(39) + 'tempdb.dbo.#for_structure_only' + char(39) + ')) ' +
char(13) +
' declare @current_column_order int ' + char(13) +
' set @current_column_order = 0 ' + char(13) +
' declare @max_column_order int ' + char(13) +
' set @max_column_order = (select max(colorder) from
tempdb..syscolumns where id = @object_id)
while (@current_column_order <> @max_column_order)
begin
set @current_column_order = @current_column_order + 1

set @sql_string = (select ''alter table #new_record_set
add '' + a.name + '' '' + b.name +
(case b.name
when ''int''
then '' null''
when ''bigint''
then '' null''
when ''datetime''
then '' null''
when ''bit''
then '' null''
else '' ('' + convert(nvarchar,a.length) + '')
null''
end)
from tempdb..syscolumns a inner join
tempdb..systypes b on
a.xtype = b.xtype
where a.id =
object_id(''tempdb.dbo.#for_structure_only'')
and a.colorder = @current_column_order and b.name
<> ''sysnames'')

exec sp_executesql @sql_string

end'

exec sp_executesql @sql_script_for_table_structure

alter table #new_record_set drop column dummy_column

I'm not sure how the formatting will appear once this is posted. I
apologize if it is unreadable. Basically I build an sql script that
executes openrowset into a temp table within the scope of the dynamic
sql. I then alter a table created in the main procedure to include the
exact column names and data types of my openrowset record set. The table
built with the scope of the dynamic sql is lost but I still have the
altered table from my main procedure.

Thanks for your help!

Cliff

*** Sent via Developersdex http://www.developersdex.com ***

Clifford Schneider (cliff@dimension2000.com) writes:
[quoted text, click to view]

Glad to hear you got it work. As for liking your solution...

[quoted text, click to view]

Would you ever expect that code to be readable? :-)

Sometimes in this ugly world, we are forced into really ugly kludges.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Spectacular script - thank you so much.

I just used it to build 220 tables that mimic a foxpro system we are converting to MS SQL. I've got all 220 tables now in SQL using MS SQL datatypes and we can all look at data easily.

From http://www.developmentnow.com/g/95_2004_3_0_0_377869/dynamically-built-temporary-table.htm

Posted via DevelopmentNow.com Groups