Linking a text file to SQL Server

Hi all.
I need to create a Linked Server in SQL 2000 to a text file. Importing the file is not an option. It has to be linked. I tried creating a ODBC link to it but I must be doing something wrong because it didn't work.
Please help.
Thanks,
ODanielsif you have sucessfully created the linkserver, you can use
EXEC sp_tables_ex <link server name> to query the tables (csv/txt files) of that link.

a sample query to retrive rows from a file can look like
select * from [txtLinkSrv].[d:\folder]..[File1.csv]

here "txtLinkSrv" is the link server name, "d:\folder" is the folder name that contains the files, "File1.csv" is the name of a csv file.|||Thanks upalsen.

Can this be used with a network path? Or does it have to be local?
I tried it and I keep getting:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Linking a Excel file to a Table

I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advance
Check out linked servers in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:F5C27639-4876-4777-A019-211D06D1A29E@.microsoft.com...
I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advance
sql

Linking a Excel file to a Table

I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advanceCheck out linked servers in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:F5C27639-4876-4777-A019-211D06D1A29E@.microsoft.com...
I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advance

Linked to Excel - data changed on export

Hi Group:
Hope this is the right forum...
Occasionally, when I write data to a linked Excel file from a varchar(50)
field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
seem to have the tick in SQL when I view in QA. Any ideas why this would be,
or how I can avoid that?
Thanks!
eric db
Eric,
The apostrophe has to do with the leading zeros in you data, in Excel (and I
believe in 2003 version, it's no longer happening), Excel treats the values
in order not to lose them, thus puts the apostrophe in front. You want to
make sure that the Excel column is formatted for the data type you wanted to
be formatted before pouring the data in.
Hopefully, it helped.
"eric db" wrote:

> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would be,
> or how I can avoid that?
> Thanks!
> eric db
|||Excel does this to indicate the cell value should be treated as text -
but what you've passed in is probably convertible to an int or
something, so Excel explicitly states it's text by adding the (')
Simon Worth
eric db wrote:
> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would be,
> or how I can avoid that?
> Thanks!
> eric db

Linked to Excel - data changed on export

Hi Group:
Hope this is the right forum...
Occasionally, when I write data to a linked Excel file from a varchar(50)
field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
seem to have the tick in SQL when I view in QA. Any ideas why this would be,
or how I can avoid that?
Thanks!
eric dbEric,
The apostrophe has to do with the leading zeros in you data, in Excel (and I
believe in 2003 version, it's no longer happening), Excel treats the values
in order not to lose them, thus puts the apostrophe in front. You want to
make sure that the Excel column is formatted for the data type you wanted to
be formatted before pouring the data in.
Hopefully, it helped.
"eric db" wrote:

> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would b
e,
> or how I can avoid that?
> Thanks!
> eric db|||Excel does this to indicate the cell value should be treated as text -
but what you've passed in is probably convertible to an int or
something, so Excel explicitly states it's text by adding the (')
Simon Worth
eric db wrote:
> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would b
e,
> or how I can avoid that?
> Thanks!
> eric dbsql

Linked text file with columns > 255 chars

I'm trying to create a linked server to a text file using Jet. The text file has columns with data larger than 255 characters. For some reason (I think its a limitation of Jet), when I attempt to query the said columns, SQL Server thinks the data type is ntext. This is causing a problem as I'm trying to insert these columns into corresponding columns in my SQL Server database (whose types are varchar(1000)). Is there a way I can link a text file without Jet? (or get around this restriction) Thanks

TQ1 I'm trying to insert these columns into corresponding columns in my SQL Server database (whose types are varchar(1000)) The text file has columns with data larger than 255 characters.


A1 If the business requirement amounts to simply inserting data rows from a text file source, you may wish to consider one or more of the following (Sql Server versions >= 7.0 for DTS bulk insert):

1 bulk insert,
2 bcp, or
3 DTS.

Q2 Is there a way I can link a text file without Jet?


A2 Yes.

For example, if the business requirement necessitates an external linked text file source (that may be accessed and modified by other applications), you may wish to consider a Microsoft OLE DB Provider for ODBC linked server connection instead. To do so:

Use a Microsoft OLE DB Provider for ODBC linked server connection (with an appropriate System DSN specifying the appropriate directory containing the text and schema files). Implement a LONGCHAR in the schema ini file for any char columns >255 chars wide. {The theoretical limit of the width of a LONGCHAR column in either a fixed-length or delimited table is 65500K. The Text ISAM is more likely to provide reliable support up to about 32K. The LONGCHAR is interpeted as a text type, but that doesn't perclude insertion into a varchar 1000 column.}

See ODBC Drivers text file support:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjettext_data_types.asp|||that should work nicely...

server update error

Does Anyone know what this means?

I run the query in the attached file on multiple servers with success but some of them refuse to cooperate. I've tried recreating the linked server on the problem servers but this doesn't help. All I want to do is check db file size and free space and get it to work. Why does it have to be so damn complicated?

Any ideas welcome - including an entirely different way of doing this.

The script and table definitions in question are attached

Could not open table '"Helpdesk_New"."dbo"."LogStats"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...The script fails at this line:

Update RemoteServer.RemoteDB.dbo.LogStats
Set Servername = @.Servername

Graham

server to Text Files: is possible to detect changes made to those files? (SQL Server 2005

Hi gurus,

I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).

Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?

Thanks a lot in advance!

I think it is not that kind of trigger... so for detecting changes I have some ideeas :

1.Probably there is something in WMI (see ) that let you detect modified data of your file; then compare it with a witness data that reside in SQL and import the file if there is a difference

2.Save the csv files with a name that include data ( 09_04_2007_20_23_12.csv) then in a SSIS package verify all that files ( with "for each loop container control flow item" vis-a-vis a witness data of last import and the import with data flow task.

The checking of new files should be made periodically in a job.