|
|
You're in the
sql server programming
group:need ideas on importing delimited text files
sql server programming:
I need to import delimited text files:
-webhost locked out bulk insert -building insert queries takes too much cpu -admin privileges not available what else is an option??
the webhost does not allow DTS packages on their server either
[quoted text, click to view] "Mary Bray" <reply@tonewsgroup.com.NOSPAMPLEASE> wrote in message news:O55B69H7DHA.2392@TK2MSFTNGP11.phx.gbl... > DTS? > > "TJS" <nospam@here.com> wrote in message > news:eSowxwH7DHA.3288@TK2MSFTNGP11.phx.gbl... > > I need to import delimited text files: > > -webhost locked out bulk insert > > -building insert queries takes too much cpu > > -admin privileges not available > > > > what else is an option?? > > > > > >
this is on a webhost I can't run exe files
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:OPHsfTI7DHA.1816@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > You can use "BCP" through DMO. Check out the BulkCopy object, for instance. Or use the regular > BCP.EXE... > > -- > Tibor Karaszi, SQL Server MVP > Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > "TJS" <nospam@here.com> wrote in message news:eSowxwH7DHA.3288@TK2MSFTNGP11.phx.gbl... > > I need to import delimited text files: > > -webhost locked out bulk insert > > -building insert queries takes too much cpu > > -admin privileges not available > > > > what else is an option?? > > > > > >
with this sp I get an error message about setting ANSI nulls and warnings.
How do I enable these options?? also , does using sp_addlinkedserver require admin privileges ?? ======================================== CREATE PROCEDURE NCL_Import_ODBC AS EXEC sp_addlinkedserver _private, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' select * from _private...table1#txt GO ======================================= [quoted text, click to view] "Steve Kass" <skass@drew.edu> wrote in message news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > OpenRowSet? You can do this kind of thing: > > select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', > 'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM myTextFile#txt') > > The way it imports is dependent on some registry settings, and there's > some more information here: > groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA > > and in this article, which uses a linked server, but should still be > relevant: > "Using the Microsoft Jet ODBC driver for text files" at > http://users.drew.edu/skass/sql/ <TextDriver.htm> > > SK > > > TJS wrote: > > > I need to import delimited text files: > > -webhost locked out bulk insert > > -building insert queries takes too much cpu > > -admin privileges not available > > > > what else is an option?? > > > > >
OpenRowSet? You can do this kind of thing:
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM myTextFile#txt') The way it imports is dependent on some registry settings, and there's some more information here: groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA and in this article, which uses a linked server, but should still be relevant: "Using the Microsoft Jet ODBC driver for text files" at http://users.drew.edu/skass/sql/ <TextDriver.htm> SK [quoted text, click to view] TJS wrote:
> I need to import delimited text files: > -webhost locked out bulk insert > -building insert queries takes too much cpu > -admin privileges not available > > what else is an option?? > >
You can use "BCP" through DMO. Check out the BulkCopy object, for instance. Or use the regular
BCP.EXE... -- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver [quoted text, click to view] "TJS" <nospam@here.com> wrote in message news:eSowxwH7DHA.3288@TK2MSFTNGP11.phx.gbl... > I need to import delimited text files: > -webhost locked out bulk insert > -building insert queries takes too much cpu > -admin privileges not available > > what else is an option?? > >
I continue to get error mesage about ansi nulls and warnings.
This is on a webhost so settings can't be changed [quoted text, click to view] "Steve Kass" <skass@drew.edu> wrote in message news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... > sp_addlinkedserver permission is for sysadmin and setupadmin roles, by > default. That and the SET options requirements are described in Books > Online under sp_addlinkedserver. > > You should try OpenRowSet: > > select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', > 'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') > > To get it to work, you may also need to change registry settings to > import TABdelimited or change NO to YES. See the links I pointed you to. > > SK > > TJS wrote: > > >with this sp I get an error message about setting ANSI nulls and warnings. > > > >How do I enable these options?? > >also , does using sp_addlinkedserver require admin privileges ?? > > > >======================================== > >CREATE PROCEDURE NCL_Import_ODBC AS > > > >EXEC sp_addlinkedserver _private, 'Jet 4.0', > > 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' > > > >select * from _private...table1#txt > > > >GO > >======================================= > > > >"Steve Kass" <skass@drew.edu> wrote in message > >news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > > > > > >>OpenRowSet? You can do this kind of thing: > >> > >>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', > >>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM myTextFile#txt') > >> > >>The way it imports is dependent on some registry settings, and there's > >>some more information here: > >>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA > >> > >>and in this article, which uses a linked server, but should still be > >>relevant: > >>"Using the Microsoft Jet ODBC driver for text files" at > >>http://users.drew.edu/skass/sql/ <TextDriver.htm> > >> > >>SK > >> > >> > >>TJS wrote: > >> > >> > >> > >>>I need to import delimited text files: > >>>-webhost locked out bulk insert > >>>-building insert queries takes too much cpu > >>>-admin privileges not available > >>> > >>>what else is an option?? > >>> > >>> > >>> > >>> > > > > > > > > >
The idea is that you'd run the file locally, which connects to the SQL Server over the net. But if
it is a web host, they might not allow direct-access to the SQL Server. Where does the text file reside? Locally or on the web-host machine? -- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver [quoted text, click to view] "TJS" <nospam@here.com> wrote in message news:ez%23GOyI7DHA.2460@TK2MSFTNGP09.phx.gbl... > this is on a webhost I can't run exe files > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:OPHsfTI7DHA.1816@TK2MSFTNGP12.phx.gbl... > > You can use "BCP" through DMO. Check out the BulkCopy object, for > instance. Or use the regular > > BCP.EXE... > > > > -- > > Tibor Karaszi, SQL Server MVP > > Archive at: > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > > "TJS" <nospam@here.com> wrote in message > news:eSowxwH7DHA.3288@TK2MSFTNGP11.phx.gbl... > > > I need to import delimited text files: > > > -webhost locked out bulk insert > > > -building insert queries takes too much cpu > > > -admin privileges not available > > > > > > what else is an option?? > > > > > > > > > > > >
sp_addlinkedserver permission is for sysadmin and setupadmin roles, by
default. That and the SET options requirements are described in Books Online under sp_addlinkedserver. You should try OpenRowSet: select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') To get it to work, you may also need to change registry settings to import TABdelimited or change NO to YES. See the links I pointed you to. SK [quoted text, click to view] TJS wrote:
>with this sp I get an error message about setting ANSI nulls and warnings. > >How do I enable these options?? >also , does using sp_addlinkedserver require admin privileges ?? > >======================================== >CREATE PROCEDURE NCL_Import_ODBC AS > >EXEC sp_addlinkedserver _private, 'Jet 4.0', > 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' > >select * from _private...table1#txt > >GO >======================================= > >"Steve Kass" <skass@drew.edu> wrote in message >news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > > >>OpenRowSet? You can do this kind of thing: >> >>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', >>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM myTextFile#txt') >> >>The way it imports is dependent on some registry settings, and there's >>some more information here: >>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA >> >>and in this article, which uses a linked server, but should still be >>relevant: >>"Using the Microsoft Jet ODBC driver for text files" at >>http://users.drew.edu/skass/sql/ <TextDriver.htm> >> >>SK >> >> >>TJS wrote: >> >> >> >>>I need to import delimited text files: >>>-webhost locked out bulk insert >>>-building insert queries takes too much cpu >>>-admin privileges not available >>> >>>what else is an option?? >>> >>> >>> >>> > > > >
DTS?
[quoted text, click to view] "TJS" <nospam@here.com> wrote in message news:eSowxwH7DHA.3288@TK2MSFTNGP11.phx.gbl... > I need to import delimited text files: > -webhost locked out bulk insert > -building insert queries takes too much cpu > -admin privileges not available > > what else is an option?? > >
Check out following commands in Books Online:
SET ANSI_WARNING SET ANSI_NULLS -- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver [quoted text, click to view] "TJS" <nospam@here.com> wrote in message news:%23EX80sN7DHA.1428@TK2MSFTNGP12.phx.gbl... > I continue to get error mesage about ansi nulls and warnings. > > This is on a webhost so settings can't be changed > > > "Steve Kass" <skass@drew.edu> wrote in message > news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... > > sp_addlinkedserver permission is for sysadmin and setupadmin roles, by > > default. That and the SET options requirements are described in Books > > Online under sp_addlinkedserver. > > > > You should try OpenRowSet: > > > > select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', > > 'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') > > > > To get it to work, you may also need to change registry settings to > > import TABdelimited or change NO to YES. See the links I pointed you to. > > > > SK > > > > TJS wrote: > > > > >with this sp I get an error message about setting ANSI nulls and > warnings. > > > > > >How do I enable these options?? > > >also , does using sp_addlinkedserver require admin privileges ?? > > > > > >======================================== > > >CREATE PROCEDURE NCL_Import_ODBC AS > > > > > >EXEC sp_addlinkedserver _private, 'Jet 4.0', > > > 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' > > > > > >select * from _private...table1#txt > > > > > >GO > > >======================================= > > > > > >"Steve Kass" <skass@drew.edu> wrote in message > > >news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > > > > > > > > >>OpenRowSet? You can do this kind of thing: > > >> > > >>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', > > >>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM myTextFile#txt') > > >> > > >>The way it imports is dependent on some registry settings, and there's > > >>some more information here: > > >>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA > > >> > > >>and in this article, which uses a linked server, but should still be > > >>relevant: > > >>"Using the Microsoft Jet ODBC driver for text files" at > > >>http://users.drew.edu/skass/sql/ <TextDriver.htm> > > >> > > >>SK > > >> > > >> > > >>TJS wrote: > > >> > > >> > > >> > > >>>I need to import delimited text files: > > >>>-webhost locked out bulk insert > > >>>-building insert queries takes too much cpu > > >>>-admin privileges not available > > >>> > > >>>what else is an option?? > > >>> > > >>> > > >>> > > >>> > > > > > > > > > > > > > > > >
Where is books online
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:eAghPBO7DHA.2404@TK2MSFTNGP11.phx.gbl... [quoted text, click to view] > Check out following commands in Books Online: > > SET ANSI_WARNING > SET ANSI_NULLS > > -- > Tibor Karaszi, SQL Server MVP > Archive at: > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > "TJS" <nospam@here.com> wrote in message > news:%23EX80sN7DHA.1428@TK2MSFTNGP12.phx.gbl... > > I continue to get error mesage about ansi nulls and warnings. > > > > This is on a webhost so settings can't be changed > > > > > > "Steve Kass" <skass@drew.edu> wrote in message > > news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... > > > sp_addlinkedserver permission is for sysadmin and setupadmin roles, by > > > default. That and the SET options requirements are described in Books > > > Online under sp_addlinkedserver. > > > > > > You should try OpenRowSet: > > > > > > select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', > > > 'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') > > > > > > To get it to work, you may also need to change registry settings to > > > import TABdelimited or change NO to YES. See the links I pointed you > to. > > > > > > SK > > > > > > TJS wrote: > > > > > > >with this sp I get an error message about setting ANSI nulls and > > warnings. > > > > > > > >How do I enable these options?? > > > >also , does using sp_addlinkedserver require admin privileges ?? > > > > > > > >======================================== > > > >CREATE PROCEDURE NCL_Import_ODBC AS > > > > > > > >EXEC sp_addlinkedserver _private, 'Jet 4.0', > > > > 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' > > > > > > > >select * from _private...table1#txt > > > > > > > >GO > > > >======================================= > > > > > > > >"Steve Kass" <skass@drew.edu> wrote in message > > > >news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > > > > > > > > > > > >>OpenRowSet? You can do this kind of thing: > > > >> > > > >>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', > > > >>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM > myTextFile#txt') > > > >> > > > >>The way it imports is dependent on some registry settings, and there's > > > >>some more information here: > > > >>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA > > > >> > > > >>and in this article, which uses a linked server, but should still be > > > >>relevant: > > > >>"Using the Microsoft Jet ODBC driver for text files" at > > > >>http://users.drew.edu/skass/sql/ <TextDriver.htm> > > > >> > > > >>SK > > > >> > > > >> > > > >>TJS wrote: > > > >> > > > >> > > > >> > > > >>>I need to import delimited text files: > > > >>>-webhost locked out bulk insert > > > >>>-building insert queries takes too much cpu > > > >>>-admin privileges not available > > > >>> > > > >>>what else is an option?? > > > >>> > > > >>> > > > >>> > > > >>> > > > > > > > > > > > > > > > > > > > > > > > > >
everything is on the web host and most isp's like mine don't allow remote
connections to sql server because it is claimed to be a yet another security risk. "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:%23%23927hJ7DHA.4060@tk2msftngp13.phx.gbl... [quoted text, click to view] > The idea is that you'd run the file locally, which connects to the SQL Server over the net. But if > it is a web host, they might not allow direct-access to the SQL Server. > > Where does the text file reside? Locally or on the web-host machine? > -- > Tibor Karaszi, SQL Server MVP > Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > "TJS" <nospam@here.com> wrote in message news:ez%23GOyI7DHA.2460@TK2MSFTNGP09.phx.gbl... > > this is on a webhost I can't run exe files > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > > message news:OPHsfTI7DHA.1816@TK2MSFTNGP12.phx.gbl... > > > You can use "BCP" through DMO. Check out the BulkCopy object, for > > instance. Or use the regular > > > BCP.EXE... > > > > > > -- > > > Tibor Karaszi, SQL Server MVP > > > Archive at: > > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > > > > > "TJS" <nospam@here.com> wrote in message > > news:eSowxwH7DHA.3288@TK2MSFTNGP11.phx.gbl... > > > > I need to import delimited text files: > > > > -webhost locked out bulk insert > > > > -building insert queries takes too much cpu > > > > -admin privileges not available > > > > > > > > what else is an option?? > > > > > > > > > > > > > > > > > > > >
In the SQL Server program group.
-- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver [quoted text, click to view] "TJS" <nospam@here.com> wrote in message news:%23ppBfVV7DHA.3024@tk2msftngp13.phx.gbl... > Where is books online > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:eAghPBO7DHA.2404@TK2MSFTNGP11.phx.gbl... > > Check out following commands in Books Online: > > > > SET ANSI_WARNING > > SET ANSI_NULLS > > > > -- > > Tibor Karaszi, SQL Server MVP > > Archive at: > > > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > > "TJS" <nospam@here.com> wrote in message > > news:%23EX80sN7DHA.1428@TK2MSFTNGP12.phx.gbl... > > > I continue to get error mesage about ansi nulls and warnings. > > > > > > This is on a webhost so settings can't be changed > > > > > > > > > "Steve Kass" <skass@drew.edu> wrote in message > > > news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... > > > > sp_addlinkedserver permission is for sysadmin and setupadmin roles, by > > > > default. That and the SET options requirements are described in Books > > > > Online under sp_addlinkedserver. > > > > > > > > You should try OpenRowSet: > > > > > > > > select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', > > > > 'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') > > > > > > > > To get it to work, you may also need to change registry settings to > > > > import TABdelimited or change NO to YES. See the links I pointed you > > to. > > > > > > > > SK > > > > > > > > TJS wrote: > > > > > > > > >with this sp I get an error message about setting ANSI nulls and > > > warnings. > > > > > > > > > >How do I enable these options?? > > > > >also , does using sp_addlinkedserver require admin privileges ?? > > > > > > > > > >======================================== > > > > >CREATE PROCEDURE NCL_Import_ODBC AS > > > > > > > > > >EXEC sp_addlinkedserver _private, 'Jet 4.0', > > > > > 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' > > > > > > > > > >select * from _private...table1#txt > > > > > > > > > >GO > > > > >======================================= > > > > > > > > > >"Steve Kass" <skass@drew.edu> wrote in message > > > > >news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > > > > > > > > > > > > > > >>OpenRowSet? You can do this kind of thing: > > > > >> > > > > >>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', > > > > >>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM > > myTextFile#txt') > > > > >> > > > > >>The way it imports is dependent on some registry settings, and > there's > > > > >>some more information here: > > > > >>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA > > > > >> > > > > >>and in this article, which uses a linked server, but should still be > > > > >>relevant: > > > > >>"Using the Microsoft Jet ODBC driver for text files" at > > > > >>http://users.drew.edu/skass/sql/ <TextDriver.htm> > > > > >> > > > > >>SK > > > > >> > > > > >> > > > > >>TJS wrote: > > > > >> > > > > >> > > > > >> > > > > >>>I need to import delimited text files: > > > > >>>-webhost locked out bulk insert > > > > >>>-building insert queries takes too much cpu > > > > >>>-admin privileges not available > > > > >>> > > > > >>>what else is an option?? > > > > >>> > > > > >>> > > > > >>> > > > > >>> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
OK, so if the text file resides on the server side, I suggest you look into
OPENROWSET, as suggested by Steve. -- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver [quoted text, click to view] "TJS" <nospam@here.com> wrote in message news:%23C8qoWV7DHA.1636@TK2MSFTNGP12.phx.gbl... > everything is on the web host and most isp's like mine don't allow remote > connections to sql server because it is claimed to be a yet another security > risk. > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:%23%23927hJ7DHA.4060@tk2msftngp13.phx.gbl... > > The idea is that you'd run the file locally, which connects to the SQL > Server over the net. But if > > it is a web host, they might not allow direct-access to the SQL Server. > > > > Where does the text file reside? Locally or on the web-host machine? > > -- > > Tibor Karaszi, SQL Server MVP > > Archive at: > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > > "TJS" <nospam@here.com> wrote in message > news:ez%23GOyI7DHA.2460@TK2MSFTNGP09.phx.gbl... > > > this is on a webhost I can't run exe files > > > > > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in > > > message news:OPHsfTI7DHA.1816@TK2MSFTNGP12.phx.gbl... > > > > You can use "BCP" through DMO. Check out the BulkCopy object, for > > > instance. Or use the regular > > > > BCP.EXE... > > > > > > > > -- > > > > Tibor Karaszi, SQL Server MVP > > > > Archive at: > > > > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > > > > > > > > "TJS" <nospam@here.com> wrote in message > > > news:eSowxwH7DHA.3288@TK2MSFTNGP11.phx.gbl... > > > > > I need to import delimited text files: > > > > > -webhost locked out bulk insert > > > > > -building insert queries takes too much cpu > > > > > -admin privileges not available > > > > > > > > > > what else is an option?? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
I'm using msde - I have no books online
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:uh6XPlV7DHA.2480@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > In the SQL Server program group. > > -- > Tibor Karaszi, SQL Server MVP > Archive at: > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > "TJS" <nospam@here.com> wrote in message > news:%23ppBfVV7DHA.3024@tk2msftngp13.phx.gbl... > > Where is books online > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in > > message news:eAghPBO7DHA.2404@TK2MSFTNGP11.phx.gbl... > > > Check out following commands in Books Online: > > > > > > SET ANSI_WARNING > > > SET ANSI_NULLS > > > > > > -- > > > Tibor Karaszi, SQL Server MVP > > > Archive at: > > > > > > http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > > > > > "TJS" <nospam@here.com> wrote in message > > > news:%23EX80sN7DHA.1428@TK2MSFTNGP12.phx.gbl... > > > > I continue to get error mesage about ansi nulls and warnings. > > > > > > > > This is on a webhost so settings can't be changed > > > > > > > > > > > > "Steve Kass" <skass@drew.edu> wrote in message > > > > news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... > > > > > sp_addlinkedserver permission is for sysadmin and setupadmin roles, > by > > > > > default. That and the SET options requirements are described in > Books > > > > > Online under sp_addlinkedserver. > > > > > > > > > > You should try OpenRowSet: > > > > > > > > > > select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', > > > > > 'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') > > > > > > > > > > To get it to work, you may also need to change registry settings to > > > > > import TABdelimited or change NO to YES. See the links I pointed > you > > > to. > > > > > > > > > > SK > > > > > > > > > > TJS wrote: > > > > > > > > > > >with this sp I get an error message about setting ANSI nulls and > > > > warnings. > > > > > > > > > > > >How do I enable these options?? > > > > > >also , does using sp_addlinkedserver require admin privileges ?? > > > > > > > > > > > >======================================== > > > > > >CREATE PROCEDURE NCL_Import_ODBC AS > > > > > > > > > > > >EXEC sp_addlinkedserver _private, 'Jet 4.0', > > > > > > 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' > > > > > > > > > > > >select * from _private...table1#txt > > > > > > > > > > > >GO > > > > > >======================================= > > > > > > > > > > > >"Steve Kass" <skass@drew.edu> wrote in message > > > > > >news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > > > > > > > > > > > > > > > > > >>OpenRowSet? You can do this kind of thing: > > > > > >> > > > > > >>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', > > > > > >>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM > > > myTextFile#txt') > > > > > >> > > > > > >>The way it imports is dependent on some registry settings, and > > there's > > > > > >>some more information here: > > > > > >>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA > > > > > >> > > > > > >>and in this article, which uses a linked server, but should still > be > > > > > >>relevant: > > > > > >>"Using the Microsoft Jet ODBC driver for text files" at > > > > > >>http://users.drew.edu/skass/sql/ <TextDriver.htm> > > > > > >> > > > > > >>SK > > > > > >> > > > > > >> > > > > > >>TJS wrote: > > > > > >> > > > > > >> > > > > > >> > > > > > >>>I need to import delimited text files: > > > > > >>>-webhost locked out bulk insert > > > > > >>>-building insert queries takes too much cpu > > > > > >>>-admin privileges not available > > > > > >>> > > > > > >>>what else is an option?? > > > > > >>> > > > > > >>> > > > > > >>> > > > > > >>> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Books Online is a free download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp SK [quoted text, click to view] TJS wrote:
>I'm using msde - I have no books online > > >"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in >message news:uh6XPlV7DHA.2480@TK2MSFTNGP12.phx.gbl... > > >>In the SQL Server program group. >> >>-- >>Tibor Karaszi, SQL Server MVP >>Archive at: >> >> >> >http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > >>"TJS" <nospam@here.com> wrote in message >>news:%23ppBfVV7DHA.3024@tk2msftngp13.phx.gbl... >> >> >>>Where is books online >>> >>> >>> >>>"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >>> >>> >>in >> >> >>>message news:eAghPBO7DHA.2404@TK2MSFTNGP11.phx.gbl... >>> >>> >>>>Check out following commands in Books Online: >>>> >>>>SET ANSI_WARNING >>>>SET ANSI_NULLS >>>> >>>>-- >>>>Tibor Karaszi, SQL Server MVP >>>>Archive at: >>>> >>>> >>>> >http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > >>>>"TJS" <nospam@here.com> wrote in message >>>>news:%23EX80sN7DHA.1428@TK2MSFTNGP12.phx.gbl... >>>> >>>> >>>>>I continue to get error mesage about ansi nulls and warnings. >>>>> >>>>>This is on a webhost so settings can't be changed >>>>> >>>>> >>>>>"Steve Kass" <skass@drew.edu> wrote in message >>>>>news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... >>>>> >>>>> >>>>>>sp_addlinkedserver permission is for sysadmin and setupadmin >>>>>> >>>>>> >roles, > > >>by >> >> >>>>>>default. That and the SET options requirements are described in >>>>>> >>>>>> >>Books >> >> >>>>>>Online under sp_addlinkedserver. >>>>>> >>>>>>You should try OpenRowSet: >>>>>> >>>>>>select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', >>>>>>'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') >>>>>> >>>>>>To get it to work, you may also need to change registry settings >>>>>> >>>>>> >to > > >>>>>>import TABdelimited or change NO to YES. See the links I pointed >>>>>> >>>>>> >>you >> >> >>>>to. >>>> >>>> >>>>>>SK >>>>>> >>>>>>TJS wrote: >>>>>> >>>>>> >>>>>> >>>>>>>with this sp I get an error message about setting ANSI nulls and >>>>>>> >>>>>>> >>>>>warnings. >>>>> >>>>> >>>>>>>How do I enable these options?? >>>>>>>also , does using sp_addlinkedserver require admin privileges ?? >>>>>>> >>>>>>>======================================== >>>>>>>CREATE PROCEDURE NCL_Import_ODBC AS >>>>>>> >>>>>>>EXEC sp_addlinkedserver _private, 'Jet 4.0', >>>>>>> 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' >>>>>>> >>>>>>>select * from _private...table1#txt >>>>>>> >>>>>>>GO >>>>>>>======================================= >>>>>>> >>>>>>>"Steve Kass" <skass@drew.edu> wrote in message >>>>>>>news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>>>OpenRowSet? You can do this kind of thing: >>>>>>>> >>>>>>>>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', >>>>>>>>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM >>>>>>>> >>>>>>>> >>>>myTextFile#txt') >>>> >>>> >>>>>>>>The way it imports is dependent on some registry settings, and >>>>>>>> >>>>>>>> >>>there's >>> >>> >>>>>>>>some more information here: >>>>>>>>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA >>>>>>>> >>>>>>>>and in this article, which uses a linked server, but should >>>>>>>> >>>>>>>> >still > > >>be >> >> >>>>>>>>relevant: >>>>>>>>"Using the Microsoft Jet ODBC driver for text files" at >>>>>>>>http://users.drew.edu/skass/sql/ <TextDriver.htm> >>>>>>>> >>>>>>>>SK >>>>>>>> >>>>>>>> >>>>>>>>TJS wrote: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>>I need to import delimited text files: >>>>>>>>>-webhost locked out bulk insert >>>>>>>>>-building insert queries takes too much cpu >>>>>>>>>-admin privileges not available >>>>>>>>> >>>>>>>>>what else is an option?? >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > > >
This is another helpful link:
http://www.microsoft.com/sql/msde/techinfo/default.asp SK [quoted text, click to view] TJS wrote:
>I'm using msde - I have no books online > > >"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in >message news:uh6XPlV7DHA.2480@TK2MSFTNGP12.phx.gbl... > > >>In the SQL Server program group. >> >>-- >>Tibor Karaszi, SQL Server MVP >>Archive at: >> >> >> >http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > >>"TJS" <nospam@here.com> wrote in message >>news:%23ppBfVV7DHA.3024@tk2msftngp13.phx.gbl... >> >> >>>Where is books online >>> >>> >>> >>>"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote >>> >>> >>in >> >> >>>message news:eAghPBO7DHA.2404@TK2MSFTNGP11.phx.gbl... >>> >>> >>>>Check out following commands in Books Online: >>>> >>>>SET ANSI_WARNING >>>>SET ANSI_NULLS >>>> >>>>-- >>>>Tibor Karaszi, SQL Server MVP >>>>Archive at: >>>> >>>> >>>> >http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > >>>>"TJS" <nospam@here.com> wrote in message >>>>news:%23EX80sN7DHA.1428@TK2MSFTNGP12.phx.gbl... >>>> >>>> >>>>>I continue to get error mesage about ansi nulls and warnings. >>>>> >>>>>This is on a webhost so settings can't be changed >>>>> >>>>> >>>>>"Steve Kass" <skass@drew.edu> wrote in message >>>>>news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... >>>>> >>>>> >>>>>>sp_addlinkedserver permission is for sysadmin and setupadmin >>>>>> >>>>>> >roles, > > >>by >> >> >>>>>>default. That and the SET options requirements are described in >>>>>> >>>>>> >>Books >> >> >>>>>>Online under sp_addlinkedserver. >>>>>> >>>>>>You should try OpenRowSet: >>>>>> >>>>>>select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', >>>>>>'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') >>>>>> >>>>>>To get it to work, you may also need to change registry settings >>>>>> >>>>>> >to > > >>>>>>import TABdelimited or change NO to YES. See the links I pointed >>>>>> >>>>>> >>you >> >> >>>>to. >>>> >>>> >>>>>>SK >>>>>> >>>>>>TJS wrote: >>>>>> >>>>>> >>>>>> >>>>>>>with this sp I get an error message about setting ANSI nulls and >>>>>>> >>>>>>> >>>>>warnings. >>>>> >>>>> >>>>>>>How do I enable these options?? >>>>>>>also , does using sp_addlinkedserver require admin privileges ?? >>>>>>> >>>>>>>======================================== >>>>>>>CREATE PROCEDURE NCL_Import_ODBC AS >>>>>>> >>>>>>>EXEC sp_addlinkedserver _private, 'Jet 4.0', >>>>>>> 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' >>>>>>> >>>>>>>select * from _private...table1#txt >>>>>>> >>>>>>>GO >>>>>>>======================================= >>>>>>> >>>>>>>"Steve Kass" <skass@drew.edu> wrote in message >>>>>>>news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>>>OpenRowSet? You can do this kind of thing: >>>>>>>> >>>>>>>>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', >>>>>>>>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM >>>>>>>> >>>>>>>> >>>>myTextFile#txt') >>>> >>>> >>>>>>>>The way it imports is dependent on some registry settings, and >>>>>>>> >>>>>>>> >>>there's >>> >>> >>>>>>>>some more information here: >>>>>>>>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA >>>>>>>> >>>>>>>>and in this article, which uses a linked server, but should >>>>>>>> >>>>>>>> >still > > >>be >> >> >>>>>>>>relevant: >>>>>>>>"Using the Microsoft Jet ODBC driver for text files" at >>>>>>>>http://users.drew.edu/skass/sql/ <TextDriver.htm> >>>>>>>> >>>>>>>>SK >>>>>>>> >>>>>>>> >>>>>>>>TJS wrote: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>>I need to import delimited text files: >>>>>>>>>-webhost locked out bulk insert >>>>>>>>>-building insert queries takes too much cpu >>>>>>>>>-admin privileges not available >>>>>>>>> >>>>>>>>>what else is an option?? >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > > >
thank you !
[quoted text, click to view] "Steve Kass" <skass@drew.edu> wrote in message news:exMSJ2b7DHA.804@tk2msftngp13.phx.gbl... > Books Online is a free download: > > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > > SK > > TJS wrote: > > >I'm using msde - I have no books online > > > > > >"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > >message news:uh6XPlV7DHA.2480@TK2MSFTNGP12.phx.gbl... > > > > > >>In the SQL Server program group. > >> > >>-- > >>Tibor Karaszi, SQL Server MVP > >>Archive at: > >> > >> > >> > >http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > >>"TJS" <nospam@here.com> wrote in message > >>news:%23ppBfVV7DHA.3024@tk2msftngp13.phx.gbl... > >> > >> > >>>Where is books online > >>> > >>> > >>> > >>>"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > >>> > >>> > >>in > >> > >> > >>>message news:eAghPBO7DHA.2404@TK2MSFTNGP11.phx.gbl... > >>> > >>> > >>>>Check out following commands in Books Online: > >>>> > >>>>SET ANSI_WARNING > >>>>SET ANSI_NULLS > >>>> > >>>>-- > >>>>Tibor Karaszi, SQL Server MVP > >>>>Archive at: > >>>> > >>>> > >>>> > >http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > >>>>"TJS" <nospam@here.com> wrote in message > >>>>news:%23EX80sN7DHA.1428@TK2MSFTNGP12.phx.gbl... > >>>> > >>>> > >>>>>I continue to get error mesage about ansi nulls and warnings. > >>>>> > >>>>>This is on a webhost so settings can't be changed > >>>>> > >>>>> > >>>>>"Steve Kass" <skass@drew.edu> wrote in message > >>>>>news:OPXq3fN7DHA.1040@TK2MSFTNGP10.phx.gbl... > >>>>> > >>>>> > >>>>>>sp_addlinkedserver permission is for sysadmin and setupadmin > >>>>>> > >>>>>> > >roles, > > > > > >>by > >> > >> > >>>>>>default. That and the SET options requirements are described in > >>>>>> > >>>>>> > >>Books > >> > >> > >>>>>>Online under sp_addlinkedserver. > >>>>>> > >>>>>>You should try OpenRowSet: > >>>>>> > >>>>>>select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', > >>>>>>'Text;Database=C:\_private;HDR=NO','SELECT * FROM table1#txt') > >>>>>> > >>>>>>To get it to work, you may also need to change registry settings > >>>>>> > >>>>>> > >to > > > > > >>>>>>import TABdelimited or change NO to YES. See the links I pointed > >>>>>> > >>>>>> > >>you > >> > >> > >>>>to. > >>>> > >>>> > >>>>>>SK > >>>>>> > >>>>>>TJS wrote: > >>>>>> > >>>>>> > >>>>>> > >>>>>>>with this sp I get an error message about setting ANSI nulls and > >>>>>>> > >>>>>>> > >>>>>warnings. > >>>>> > >>>>> > >>>>>>>How do I enable these options?? > >>>>>>>also , does using sp_addlinkedserver require admin privileges ?? > >>>>>>> > >>>>>>>======================================== > >>>>>>>CREATE PROCEDURE NCL_Import_ODBC AS > >>>>>>> > >>>>>>>EXEC sp_addlinkedserver _private, 'Jet 4.0', > >>>>>>> 'Microsoft.Jet.OLEDB.4.0', 'C:\_private', NULL, 'Text' > >>>>>>> > >>>>>>>select * from _private...table1#txt > >>>>>>> > >>>>>>>GO > >>>>>>>======================================= > >>>>>>> > >>>>>>>"Steve Kass" <skass@drew.edu> wrote in message > >>>>>>>news:eUktYuI7DHA.2760@TK2MSFTNGP09.phx.gbl... > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>>>OpenRowSet? You can do this kind of thing: > >>>>>>>> > >>>>>>>>select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', > >>>>>>>>'Text;Database=C:\datadirectory\;HDR=NO','SELECT * FROM > >>>>>>>> > >>>>>>>> > >>>>myTextFile#txt') > >>>> > >>>> > >>>>>>>>The way it imports is dependent on some registry settings, and > >>>>>>>> > >>>>>>>> > >>>there's > >>> > >>> > >>>>>>>>some more information here: > >>>>>>>>groups.google.com/groups?q=C183FB8D-7346-4ABF-9BC8-F3BABCFB46CA > >>>>>>>> > >>>>>>>>and in this article, which uses a linked server, but should > >>>>>>>> > >>>>>>>> > >still > > > > > >>be > >> > >> > >>>>>>>>relevant: > >>>>>>>>"Using the Microsoft Jet ODBC driver for text files" at > >>>>>>>>http://users.drew.edu/skass/sql/ <TextDriver.htm> > >>>>>>>> > >>>>>>>>SK > >>>>>>>> > >>>>>>>> > >>>>>>>>TJS wrote: > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>>>I need to import delimited text files: > >>>>>>>>>-webhost locked out bulk insert > >>>>>>>>>-building insert queries takes too much cpu > >>>>>>>>>-admin privileges not available > >>>>>>>>> > >>>>>>>>>what else is an option?? > >>>>>>>>> > >>>>>>>>> > >>>>>>>>> > >>>>>>>>> > >>>>>>>>> > >>>>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>> > >>>>> > >>>> > >>>> > >>> > >>> > >> > >> > > > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |