OK,. it looks like DTS wins. Thanks ----- Original Message ----- From: "Robertson-Ravo, Neil (REC)" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Wednesday, November 20, 2002 10:03 AM Subject: RE: Importing a text file into SQL Server
> yep, this is just like the one on cfcomet. What I would say is this : never > begin your SP with the name sp_XXXXX > > It wont break anything, but it can have a serious hit on performance as it > will store that SP within the Master DB and not your DB. > > > > -----Original Message----- > From: Joel Firestone [mailto:[EMAIL PROTECTED]] > Sent: 20 November 2002 15:59 > To: SQL > Subject: Re: Importing a text file into SQL Server > > > Bruce: > > I just did the same thing recently. You can create and save your DTS > package > on the server, and then you can call via a stored procedure in CF. Here's > the > SP code: > > CREATE PROCEDURE qryClientImport AS > > DECLARE @object int > DECLARE @hr int > > --create a package object > EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT > if @hr <> 0 > BEGIN > print 'error create DTS.Package' > RETURN > END > > EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile', NULL, 'F:\Temp\FILE > NAME HERE.dts', '' > IF @hr <> 0 > BEGIN > print 'error LoadFromStorageFile' > RETURN > END > > EXEC @hr = sp_OAMethod @object, 'Execute' > IF @hr <> 0 > BEGIN > print 'Execute failed' > RETURN > END > > Hope this helps. > > Joel > > ----- Original Message ----- > From: "Bruce Sorge" <[EMAIL PROTECTED]> > To: "SQL" <[EMAIL PROTECTED]> > Sent: Wednesday, November 20, 2002 10:37 AM > Subject: Importing a text file into SQL Server > > > > Hello list. I have a text file that is comma and double-quote separated, > with a line break that separates the lines. The first row contains the > header information. What I am wanting to do is to import this into a SQL > Server 2K table. I know that I can use DTS to do this, but I need to do this > in ColdFusion code instead as a scheduled task. I have always in the past > just used DTS Wizard to do this, not straight SQL. So, my question is, how > do you import a text file into SQL Server? What would my query look like? > > > > Thanks, > > > > Bruce > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
