"Linked Table" from a .txt file in SQL Server?

OKay, I'm really, really good with MS Access, but am just getting up to speed
on SQL Server. Is there a SQL Server equivalent of linking (not importing) a
table from a .txt file?
Thanks!
Something similar would be to create a linked server to
access the text file. You use the Jet provider and set the
data source to the directory which has the text files you
want to access.
EXEC sp_addlinkedserver @.server ='TextLinkServer',
@.srvproduct='',
@.provider ='Microsoft.Jet.OLEDB.4.0',
@.datasrc='D:\',
@.provstr='Text'
You could then get a list of all text files in the directory
if you execute:
EXEC sp_tables_ex 'TextLinkServer'
You can access the files as listed in the directory. Use the
table_name listed when you execute sp_tables_ex which is the
text file name.
select *
from TextLinkServer...[YourFile#txt]
to access D:\YourFile.txt
-Sue
On Wed, 6 Apr 2005 16:05:04 -0700, "Joel"
<Joel@.discussions.microsoft.com> wrote:

>OKay, I'm really, really good with MS Access, but am just getting up to speed
>on SQL Server. Is there a SQL Server equivalent of linking (not importing) a
>table from a .txt file?
>Thanks!
|||Almost there...
The only other thing is that I'm trying to use a text file that exports from
SAP, which wants to export to the 'C:\Documents and
Settings\USERNAME\SapWorkDir' directory. When specifying a datasource, is
there a way to use a variable to put in the currently logged on user in place
of USERNAME?
Thanks!
"Sue Hoegemeier" wrote:

> Something similar would be to create a linked server to
> access the text file. You use the Jet provider and set the
> data source to the directory which has the text files you
> want to access.
> EXEC sp_addlinkedserver @.server ='TextLinkServer',
> @.srvproduct='',
> @.provider ='Microsoft.Jet.OLEDB.4.0',
> @.datasrc='D:\',
> @.provstr='Text'
> You could then get a list of all text files in the directory
> if you execute:
> EXEC sp_tables_ex 'TextLinkServer'
> You can access the files as listed in the directory. Use the
> table_name listed when you execute sp_tables_ex which is the
> text file name.
> select *
> from TextLinkServer...[YourFile#txt]
> to access D:\YourFile.txt
> -Sue
> On Wed, 6 Apr 2005 16:05:04 -0700, "Joel"
> <Joel@.discussions.microsoft.com> wrote:
>
>
|||If the data source is always changing and varies from user
to user, you may want to look at using Openrowset instead.
-Sue
On Thu, 7 Apr 2005 07:33:05 -0700, "Joel"
<Joel@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Almost there...
>The only other thing is that I'm trying to use a text file that exports from
>SAP, which wants to export to the 'C:\Documents and
>Settings\USERNAME\SapWorkDir' directory. When specifying a datasource, is
>there a way to use a variable to put in the currently logged on user in place
>of USERNAME?
>Thanks!
>"Sue Hoegemeier" wrote: