Export to Excel using DTS

I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)

what is the best way to do this? DTS, BCP

thanks
FOCELBrett will be along shortly to offer up a much cleaner solution using BCP and two lines of code (one of which will call a stored procedure to mix up a margarita). In the meantime, may I humbly offer up:

You can create a package with a global variable indicating which SP to run. Have three transformations defined in the package and determine which transformation to execute in a VBScript (using the global variable).

I'm not sure what will happen to the other transformations (the two that are not executed). That may require more testing...

You can look for more hints at http://www.sqldts.com.

Regards,

hmscott

Export table to MAccess from SQL Store Procedure

Hi people.

I need some help to export a table to Microsoft Access using a Store Procedure.

My Store Procedure will pupolate a temporary table with some information. After that I want that the store procedure export this table to Access.

Is there any script or command that can do it? And how can i use it into a Store Procedure?

Please, if possible give a example.

thanks in advance
:confused:DTS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dts_overview.asp) is almost certainly what you want.

-PatP|||How can i do it?? can you give a example??|||Follow the link I provided. There are several hundered examples provided there!

-PatP

Export SQL Table to MS Access file

I want to export a SQL table to new MS Access file or to a file already
existing in a known location. The SQL table is a temporary table (#). If not
for # table, any idea to export a SQL table will be useful. The exporting
logic should be coded in stored procedure.
* I don't want to use the option that uses Access file as Linked Server.
* If it is required to have the SQL table structure in MS Access file,
that's fine.Evening!
Here is an example of how to issue a SELECT statement against an Microsoft
Access database table in the SQL Query Analyzer or a stored proc.
SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders) AS a
Note the path of the access database and the name of the table called
Orders. You can change that to be the path and table that matches what
you've called it.
Now here is a quick example on how to insert data into the access table
orders from a sql query. Again, you can run this via the analyzer or stored
proc.
INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
Orders)
SELECT * FROM #temp_orders
Basically, think of the "OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders)" as a table. You can issue an INSERT,
UPDATE, DELETE just like you would any table.
Hope this helps,
Yosh
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>I want to export a SQL table to new MS Access file or to a file already
> existing in a known location. The SQL table is a temporary table (#). If
> not
> for # table, any idea to export a SQL table will be useful. The exporting
> logic should be coded in stored procedure.
> * I don't want to use the option that uses Access file as Linked Server.
> * If it is required to have the SQL table structure in MS Access file,
> that's fine.|||Hi
If you are wanting to automate this then you may want to look at using DTS
and a scheduled job.
John
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>I want to export a SQL table to new MS Access file or to a file already
> existing in a known location. The SQL table is a temporary table (#). If
> not
> for # table, any idea to export a SQL table will be useful. The exporting
> logic should be coded in stored procedure.
> * I don't want to use the option that uses Access file as Linked Server.
> * If it is required to have the SQL table structure in MS Access file,
> that's fine.|||Great. Thanks. Is there a way in which I need not have the table structure i
n
Access database?
"Yosh" wrote:

> Evening!
> Here is an example of how to issue a SELECT statement against an Microsoft
> Access database table in the SQL Query Analyzer or a stored proc.
> SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) AS a
> Note the path of the access database and the name of the table called
> Orders. You can change that to be the path and table that matches what
> you've called it.
>
> Now here is a quick example on how to insert data into the access table
> orders from a sql query. Again, you can run this via the analyzer or store
d
> proc.
> INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
> Orders)
> SELECT * FROM #temp_orders
>
> Basically, think of the "OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders)" as a table. You can issue an INSERT,
> UPDATE, DELETE just like you would any table.
> Hope this helps,
> Yosh
>
>
>
>
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>
>|||Yosh,
fyi... I don't have permissions to create linked server. When I tried to
execute your code, I'm getting the below error
Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Please advise.
"Yosh" wrote:

> Evening!
> Here is an example of how to issue a SELECT statement against an Microsoft
> Access database table in the SQL Query Analyzer or a stored proc.
> SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) AS a
> Note the path of the access database and the name of the table called
> Orders. You can change that to be the path and table that matches what
> you've called it.
>
> Now here is a quick example on how to insert data into the access table
> orders from a sql query. Again, you can run this via the analyzer or store
d
> proc.
> INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
> Orders)
> SELECT * FROM #temp_orders
>
> Basically, think of the "OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders)" as a table. You can issue an INSERT,
> UPDATE, DELETE just like you would any table.
> Hope this helps,
> Yosh
>
>
>
>
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>
>|||Jafer,
Treat the "OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
Orders) As a" as you would any table in SQL.
For example, this will work:
SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
You notice the 'admin' password? Try playing with that if you have
permission problems. Try making it blank or leaving it out completely.
Hope this helps,
Yosh
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:F408FF70-8F6A-47A5-88A4-9C001A3AE7C5@.microsoft.com...
> Great. Thanks. Is there a way in which I need not have the table structure
> in
> Access database?
> "Yosh" wrote:
>|||Yosh,
SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
gives, incorrect syntax error near the key word OPENROWSET. I gave the exact
location of the MDB file along with the table name in the file.
Any thoughts?
"Yosh" wrote:

> Jafer,
> Treat the "OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';''
,
> Orders) As a" as you would any table in SQL.
> For example, this will work:
> SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
> You notice the 'admin' password? Try playing with that if you have
> permission problems. Try making it blank or leaving it out completely.
> Hope this helps,
> Yosh
>
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:F408FF70-8F6A-47A5-88A4-9C001A3AE7C5@.microsoft.com...
>
>|||Jafer,
Now that I think about it. It does not make sense that this would work. You
will have to create the table manually or via a create statement.
my apologies,
Yosh
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:FE8CAA43-B589-4E73-8D15-68E6A69C5EBD@.microsoft.com...
> Yosh,
> SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
> gives, incorrect syntax error near the key word OPENROWSET. I gave the
> exact
> location of the MDB file along with the table name in the file.
> Any thoughts?
>
> "Yosh" wrote:
>|||Yosh,
FYI... In my previous try, I created the table you mentioned in the SQL in
Access file before running the query and tried to export the records from a
table having the same structure.
"Yosh" wrote:

> Jafer,
> Now that I think about it. It does not make sense that this would work. Yo
u
> will have to create the table manually or via a create statement.
> my apologies,
> Yosh
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:FE8CAA43-B589-4E73-8D15-68E6A69C5EBD@.microsoft.com...
>
>