Read Textfile Contents into a View Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Read Textfile Contents into a View Field

Author

Topic

dominican1979
Starting Member

18 Posts

Posted - 2009-08-07 : 15:42:48

Hello Everyone,

Thanks for reading my post. This is what I need to do:I have an invoice table with order lines. Each order line has a memo field that may or may not be filled in. If it is filled in, then the field contanins a path to the memo file on the file system like this: C:\program files\visma\business\F0002\M600395.txt The actual memo file above may contain something like this: "Defective Return" or whatever. I need to create a view like this:

Select ProductNo, Description, Price, Qty, MemoField
From InvoiceLines

Now the way it is right now if I run this view I will get something like this:

ProductNo Description Price Qty MemoField
211323 Glasses 79.99 1 C:\program files\visma\business\F0002\M600395.txt

But instead I need the memo field to display the content of that text file. I have looked everywhere and haven't found a solution on how to accomplish this. Is there some kind of function I can use on the view so that when I run it it goes out and reads the file for each invoice line and displays it?

I'm using SQL Server 2005

Thank you very much for reading my post, I greately appreciate your help!

johnconstraint
Starting Member

23 Posts

Posted - 2009-08-07 : 16:21:16

Can you check this link ?
http://msdn.microsoft.com/en-us/library/ms190312.aspx

I do not have access to experiment this in the machine that I am currently using. Looks like BULK OPENROWSET requires certain permission to execute them.

Below is a sample query from the link above:
SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;

dominican1979
Starting Member

18 Posts

Posted - 2009-08-07 : 16:59:50

Hi johnconstraint,

Thanks for your reply, I looked at the link you gave me and tried different combinations of the statement sample you showed me, but no luck, I just keep getting syntax errors. I even tried the actual file names that show on the path for the field and still get the same error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources