Exporting tables via ODBC from SQL SERVER

[Mark]

I need to provide a way for a client to pull information
from SQL Server to Word, Excel, etc. via a web browser
using Active Server Pages.
I was going to just write a little utility that output CSV
files to the browser based on the query, but thought I
would see if there was a "better" way.

I see some examples of doing something like:
SELECT * INTO
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;].
[OutFile#txt] FROM TestTable
However when I do this I get an error like

[Microsoft][ODBC SQL Server Driver][SQL Server]Specified
owner name 'Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;'
either does not exist or you do not have permission to use
it.

I've tried all the variations I have found and can think
of. Is this something you just can't do from ASP? Or am I
just missing something obvious?

Appreciate any insights or examples.

Mark

[Aaron Bertrand - MVP]

> I see some examples of doing something like:

> SELECT * INTO
> [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;].
> [OutFile#txt] FROM TestTable

Not from SQL Server, I don't think.

[url]http://www.aspfaq.com/2482[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[onedaywhen]

This syntax is for Jet, not SQL Server.

--

"Mark" <mark@sloanadams.com> wrote in message news:<532a01c3d966$3ea1f980$7d02280a@phx.gbl>...

> I need to provide a way for a client to pull information
> from SQL Server to Word, Excel, etc. via a web browser
> using Active Server Pages.
> I was going to just write a little utility that output CSV
> files to the browser based on the query, but thought I
> would see if there was a "better" way.
>
> I see some examples of doing something like:
> SELECT * INTO
> [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;].
> [OutFile#txt] FROM TestTable
> However when I do this I get an error like
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Specified
> owner name 'Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;'
> either does not exist or you do not have permission to use
> it.
>
> I've tried all the variations I have found and can think
> of. Is this something you just can't do from ASP? Or am I
> just missing something obvious?
>
> Appreciate any insights or examples.
>
> Mark

I realize that what I am writing into is Jet, but my
connection is to SQL server. So, does that mean that I
cannot do this at all or is there some way around it?

Mark

>-----Original Message-----
>This syntax is for Jet, not SQL Server.
>
>--
>
>"Mark" <mark@sloanadams.com> wrote in message

news:<532a01c3d966$3ea1f980$7d02280a@phx.gbl>...

>> I need to provide a way for a client to pull

information

>> from SQL Server to Word, Excel, etc. via a web browser
>> using Active Server Pages.
>> I was going to just write a little utility that output

CSV

>> files to the browser based on the query, but thought I
>> would see if there was a "better" way.
>>
>> I see some examples of doing something like:
>> SELECT * INTO
>> [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;].
>> [OutFile#txt] FROM TestTable
>> However when I do this I get an error like
>>
>> [Microsoft][ODBC SQL Server Driver][SQL Server]

Specified

>> owner

name 'Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;'

>> either does not exist or you do not have permission to

use

>> it.
>>
>> I've tried all the variations I have found and can

think

>> of. Is this something you just can't do from ASP? Or am

I

>> just missing something obvious?
>>
>> Appreciate any insights or examples.
>>
>> Mark

>.
>

[onedaywhen]

*Must* your connection be to SQL Server? Could you instead connect to
a Jet source i.e. using the Jet OLEDB provider? It needn't be an .mdb
file (MS Access), it could be an existing, but totally arbitrary,
Excel workbook (but it can't be the text file folder, it seems). For
example, assuming ADO, your connection string would be something like:

Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Temp\MyWorkbook.xls;Extended Properties='Excel 8.0'

You could then specify the connection to both the text file folder
*and* SQL Server in the query e.g.

SELECT MyCol1, MyCol2
INTO [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP\;].[OutFile#txt]
FROM [ODBC;Driver={SQL
Server};SERVER=MyServer;DATABASE=MyDB;UID=;Pwd=;].MyTable

Note that you can use a SELECT..INTO query to create a new
workbook/worksheet, rather than a text file e.g.

SELECT MyCol1, MyCol2
INTO [Excel 8.0;database=C:\TEMP\NewWorkbook.xls;].Sheet1
FROM [ODBC;Driver={SQL
Server};SERVER=MyServer;DATABASE=MyDB;UID=;Pwd=;].MyTable

If you *must* be connected SQL Server, you will have to use SQL Server
methods e.g. take a look at OPENROWSET.

--

<anonymous@discussions.microsoft.com> wrote in message news:<0c8501c3d9f8$28b986f0$a501280a@phx.gbl>...

> I realize that what I am writing into is Jet, but my
> connection is to SQL server. So, does that mean that I
> cannot do this at all or is there some way around it?
>
> Mark

> >-----Original Message-----
> >This syntax is for Jet, not SQL Server.
> >
> >--
> >
> >"Mark" <mark@sloanadams.com> wrote in message

> news:<532a01c3d966$3ea1f980$7d02280a@phx.gbl>...

> >> I need to provide a way for a client to pull

> information

> >> from SQL Server to Word, Excel, etc. via a web browser
> >> using Active Server Pages.
> >> I was going to just write a little utility that output

> CSV

> >> files to the browser based on the query, but thought I
> >> would see if there was a "better" way.
> >>
> >> I see some examples of doing something like:
> >> SELECT * INTO
> >> [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;].
> >> [OutFile#txt] FROM TestTable
> >> However when I do this I get an error like
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]

> Specified

> >> owner

> name 'Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMP;'

> >> either does not exist or you do not have permission to

> use

> >> it.
> >>
> >> I've tried all the variations I have found and can

> think

> >> of. Is this something you just can't do from ASP? Or am

> I

> >> just missing something obvious?
> >>
> >> Appreciate any insights or examples.
> >>
> >> Mark

> >.
> >