sql server dts: DTS CREATE TABLE dynamically -- sql server dts

DTS CREATE TABLE dynamically

DJJ
12/19/2003 4:06:24 AM

sql server dts: Howdy all,

I'm trying to use DTS to output the results from a
dynamically generated query into Excel and email the
resultant .xls file directly to the requesting user (the
users will be able to choose from a list of 'Reports' and
supply values to be used in the WHERE clause). I've
gotten everything to work up to the point of dynamically
creating the table in DTS to allow DTS to copy the data
from SQL to the .xls file (If I use the same base query,
just changing the WHERE clause, it works like a charm) -
does anyone know of a way to basically have SQL look at a
query, create a table (dynamically) based on the fields
in the query, and use that as the CREATE TABLE definition
in DTS? It must be possible since the DTS Wizard seems
to be able to do it on-the-fly when you select a table to
output... My other option was to create a DTS package for
each query-type, but I'd rather not do that if I can help
it.

Not sure how clear I've been....

Using SQLServer 2k, SP3.

Thanks

Re: DTS CREATE TABLE dynamically

Allan Mitchell
12/19/2003 12:21:47 PM

You need to read the query and build the statement so

SELECT au_id, au_lname FROM AUTHORS

Would parse to

CREATE TABLE AUTHORS (au_id varchar(255), au_lname varchar(255))

You can easily do this in VBScript. I have a package which does exactly
what you want at home but I am not on the internet so cannot grab it. I
will bring it in monday if you want. Drop me a mail (without no-spam)

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]