SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, comments - 80, trackbacks - 0

Linked Server to Text Files - SQL2000

Sometimes it's handy to create a Linked Server to text files in order to perform distributed queries or bulk inserts of flat-file data into SQL Server.

In order to demonstrate this capability with SQL Server 2000, create a text file copy of the Categories table from Northwind database and place the text file named CATEGORIES.TXT in a subdirectory named TEXTFILES on a hard drive attached to the SQL Server machine.

Then add a Linked Server to the text file with a script like this:


exec sp_addlinkedserver NW_TEXT, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'Z:\TEXTFILES', NULL, 'Text'


Alternatively, you can add a Linked Server in Enterprise Manager:

Step 1
Linked Server Properties

 Step 2
New Linked Server dialog box 

 Step 3
Linked Server Properties - Security

Step 4
Linked Server - Server Options tab

This now allows you execute queries against the text file like this:


select
* from NW_TEXT...categories#txt



You can use a schema.ini file (Text File Driver) to provide formatting like column headers in a text file and make the file(s) browsable just like tables are when displaying them from the Linked Server node in Enterprise Manger.

For this example, create a schema.ini file that looks like this:


[CATEGORIES.TXT]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeaders=True
Col1=CategoryID Short
Col2=CategoryName Text Width 15
Col3=Description Text Width 100


For further information, check Schema.ini (Text File Driver) on Microsoft MSDN.

One caveate I ran across though, is that the maximum number of columns or fields that you can specify in a schema.ini file is 255. So, if you have a really W-I-D-E data file with lots of columns, and you are considering Linked Server with a schema.ini file, be aware of this limitation.

Hope this helps.
-pj

Print | posted on Friday, April 28, 2006 2:59 PM |

Feedback

Gravatar

# re: Linked Server - To Text Files

Actually, most any data source for which there is an OLE DB provider or ODBC driver can be a linked server. This even includes Exchange Server message stores.
4/30/2006 7:02 PM | Steven Bras
Gravatar

# re: Linked Server - To Text Files

I am having difficulty overcoming an error.
When doing: "INSERT INTO .... SELECT ... FROM (a linked server table) and there are nulls in some of the columns i get error:
"String or binary data would be truncated. The statement has been terminated."
5/16/2006 3:54 AM | Mervyn Kydd
Gravatar

# re: Linked Server - To Text Files

Look at :

http://support.microsoft.com/kb/290415

Regards

Holger
3/12/2007 5:54 PM | Holger
Gravatar

# re: Linked Server to Text Files - SQL2000

I had this problem in sql 2005, but your text help me to do it
as like piece of cake. thanks a lot.

peyman
10/14/2008 7:15 AM | peyman
Gravatar

# re: Linked Server to Text Files - SQL2000

I have 1 table containing user and ordered_product (beside other columns).Now I want to find out - based on a view on product 1 - which user has ordered only product 1 and not product 2.and as a second view which user had ordered both.User with only product 2 will not be looked at.Has someone a good idea?.......
4/10/2010 7:01 AM | promozione del casinò in rete
Gravatar

# re: Linked Server to Text Files - SQL2000

Sometimes it's handy to create a Linked Server to text files in order to perform distributed queries or bulk inserts of flat-file data into SQL Server.

snow boots for women | columbia jackets | mac makeup | the north face outlet
10/23/2010 10:20 AM | north face jackets on sale
Gravatar

# re: Linked Server to Text Files - SQL2000

Hi,
I'm new to SQL Server and I'm trying to establish a link to some text files via Server Management Studio for the first time. I have tried just about every permutation of inputs I can think of but I still get the same error;

Cannot initialise the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "server_name",

OLE DB provider "Microsoft.Jet.OLEDB.4,0" for linked server "server_name" returned message "Unspecified Error". (Microsoft SQL Server, Error; 7303)

Can anybody give me a clue?

Also, I'm not entirely sure whether an Excel.csv file is treated as a text file or an excel file?

Thanks in advance...M
2/1/2011 9:04 AM | Marcus
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET