SELECT INTO to text file

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
Pieter said:
I'm just curious why you used ODBC for the ADO connection to SQLServer <g>
Click to expand...

The SELECT..INTO syntax and functionality is provided by the Jet
engine, not ADO, therefore my connection must be to a Jet data source.
I cannot embed an OLE DB/ADO connection to SQL Server (MSSQL) in a Jet
SQL statement, therefore the connection must be ODBC.

That said, MSSQL's engine also provides a different SELECT..INTO syntax
and functionality and you can use it's OPENROWSET functionality to
embed a OLE DB connection to a Jet source. However, I think I am
correct in saying that you can't use SELECT..INTO (Jet or MSSQL) with
an OLE DB connection to a text file - I don't know why, just that from
experience it doesn't work, That's why in the above example I am using
an arbitrary (and possibly non-existent) Excel workbook for my ADO
connection rather than connecting to the text file directly i.e.
connecting to Excel makes the syntax work where the Text connection
fails, for similarly unexplained reasons.

Jamie.

--
 
Pieter said:
I'm just curious why you used ODBC for the ADO connection to SQLServer <g>
Click to expand...

The SELECT..INTO syntax and functionality is provided by the Jet
engine, not ADO, therefore my connection must be to a Jet data source.
I cannot embed an OLE DB/ADO connection to SQL Server (MSSQL) in a Jet
SQL statement, therefore the connection must be ODBC.

That said, MSSQL's engine also provides a different SELECT..INTO syntax
and functionality and you can use it's OPENROWSET functionality to
embed a OLE DB connection to a Jet source. However, I think I am
correct in saying that you can't use SELECT..INTO (Jet or MSSQL) with
an OLE DB connection to a text file - I don't know why, just that from
experience it doesn't work, That's why in the above example I am using
an arbitrary (and possibly non-existent) Excel workbook for my ADO
connection rather than connecting to the text file directly i.e.
connecting to Excel makes the syntax work where the Text connection
fails, for similarly unexplained reasons.

Jamie.

--
 
Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.

RBS
 
Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.

RBS
 
Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.

RBS
 
Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.

RBS
 
Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.
Click to expand...

If you can get your ODBC connection to Interbase database to return
data, you are halfway there. What failure messages are you getting? Is
there a problem data type maping? are you using a schema.ini file?

Jamie.

--
 
Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.
Click to expand...

If you can get your ODBC connection to Interbase database to return
data, you are halfway there. What failure messages are you getting? Is
there a problem data type maping? are you using a schema.ini file?

Jamie.

--
 
I have no trouble getting data from Interbase with the ODBC connection.
My whole app relies on that.
Trouble is getting the right syntax to output to text.
I am not using a schema.ini and I found sofar that this gets produced
automatically in the process, so I didn't have to do it.

I will put post some of my efforts + error messages later.

RBS
 
I have no trouble getting data from Interbase with the ODBC connection.
My whole app relies on that.
Trouble is getting the right syntax to output to text.
I am not using a schema.ini and I found sofar that this gets produced
automatically in the process, so I didn't have to do it.

I will put post some of my efforts + error messages later.

RBS
 
one more thing
SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[MyTables#txt]
FROM...

seems to ignore the TextDelimiter parameter (works fine if I edit
schema.ini)
Do you know anything about this?
Not the biggest problem to workaround, but annoying...

Pieter
 
one more thing
SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[MyTables#txt]
FROM...

seems to ignore the TextDelimiter parameter (works fine if I edit
schema.ini)
Do you know anything about this?
Not the biggest problem to workaround, but annoying...

Pieter
 
Pieter said:
one more thing
SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[MyTables#txt]
FROM...

seems to ignore the TextDelimiter parameter (works fine if I edit
schema.ini)
Do you know anything about this?
Click to expand...

Sure, you can put all sorts of rubbish in the connection string and the
driver will ignore it <g>.

Seriously, I think it is merely popular myth, perpetuated in seemingly
respectable MSDN articles, that such properties may be made in the
connection string.

Following this article

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

I occasionally see ONEDAYWHEN appearing in connection strings. Popular
myths are easily (accidentally) made!

Jamie.

--
 
Pieter said:
one more thing
SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[MyTables#txt]
FROM...

seems to ignore the TextDelimiter parameter (works fine if I edit
schema.ini)
Do you know anything about this?
Click to expand...

Sure, you can put all sorts of rubbish in the connection string and the
driver will ignore it <g>.

Seriously, I think it is merely popular myth, perpetuated in seemingly
respectable MSDN articles, that such properties may be made in the
connection string.

Following this article

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

I occasionally see ONEDAYWHEN appearing in connection strings. Popular
myths are easily (accidentally) made!

Jamie.

--
 
so we (outside the US) must as per usual make workarounds for "universal
(read american) standards<g>
Figured as much (as long as schema.ini takes precedence, I'm not to
bothered)

thx for the reply
Pieter

Jamie Collins said:
Pieter said:
one more thing
SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[MyTables#txt]
FROM...

seems to ignore the TextDelimiter parameter (works fine if I edit
schema.ini)
Do you know anything about this?
Click to expand...

Sure, you can put all sorts of rubbish in the connection string and the
driver will ignore it <g>.

Seriously, I think it is merely popular myth, perpetuated in seemingly
respectable MSDN articles, that such properties may be made in the
connection string.

Following this article

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

I occasionally see ONEDAYWHEN appearing in connection strings. Popular
myths are easily (accidentally) made!

Jamie.
Click to expand...
 
Back
Top