Ahhh nothing like well indented code :) Dynamic column names are
possible, however your DTS, and SP are going to be more complex,
because you will need to pass the actual column names to the DTS from
CF. In which case, Bulk Insert might be easier, that said code it below.
Here is the code for a dynamic filename import.
Your standard cfstoredproc...
<cfstoredproc procedure="execEntriesImport" datasource="#variables.dsn#">
<cfprocparam type="In"
cfsqltype="CF_SQL_VARCHAR"
dbvarname="filename"
value="#variables.filename#"
null="No">
</cfstoredproc>
The execEntriesImport SP...
CREATE PROCEDURE execEntriesImport
@filename varchar(255)
AS
DECLARE @dtscmd varchar(500)
SELECT @dtscmd = "exec master..xp_cmdshell 'dtsrun /SserverName
/NWebscoreEntriesImport /E /Afilename:8=\\serverName\www\yadayada.net\remoteupload\" +
@filename + "'"
EXEC(@dtscmd)
GO
The actual DTS package is very simple (3 tasks), obviously no code,
but all I do is run the wizard, save the package, then go to the DTS
and go to design mode, and add a Dynamic Properties task, which
assigns the filename variable from the command line above to a global
variable, and uses it as the datasource for the file.
You might want to do some googling on dynamic dts parameters, but it's
pretty easy, just point an click stuff. BOL is great too.
Note that xp_cmdshell is in the master db, so it requires sa rights to
run it.
--
jon
mailto:[EMAIL PROTECTED]
Monday, September 9, 2002, 9:03:03 PM, you wrote:
BD> Thanks Jon! I would love to see the code, I love code. Especially pretty,
BD> well indented code ;)
BD> Will this work for a case where the fieldname/columns are all dynamic
BD> aswell? When you save the DTS package, is it not specific to that transfer,
BD> with those particular columns?
BD> Brook
BD> At 08:39 PM 09/09/02 -0400, you wrote:
>>This temp file, I imagine it is in a delimited format of some sort,
>>and you could import it using SQL Server's import wizard right?
>>
>>If so, you can run through the wizard, save the process as a dts.
>>There is a checkbox at the end of the import wizard.
>>
>>http://www.swynk.com/friends/green/ExecDTS.asp
>>http://www.ihtmlvault.com/tips.ihtml?step=2&nid=72 (ihtml :), still
>>applies though)
>>
>>The steps go like this: cfstoredproc > sp > dtsrun
>>The dts package runs the import, and you are done. I have even done
>>this using dynamic filenames passed from CF to the DTS package, and
>>importing them.
>>This may look complex, but once you understand the steps, it seriously
>>takes 15 minutes to make a CF to SQL import program once you have the
>>data file. Play around with dtsrun.exe, it's a great tool.
>>If you would like to see code let me know, I'll post it.
>>
>>--
>> jon
>> mailto:[EMAIL PROTECTED]
>>
>>Monday, September 9, 2002, 8:10:47 PM, you wrote:
>>BD> I'm using it to allow a member of our site to transfer data between two
>>BD> tables. They can select the fields to transfer so its rather dynamic. My
>>BD> plan was to generate the temp file containing the data to transfer using
>>BD> the COALESCE and CAST functions to generate the file. Then, I wanted
>>to run
>>BD> the Bulk Insert to insert the data.
>>
>>BD> Would I generate the DTS package the same way?
>>
>>BD> Brook
>>
>>BD> At 07:43 PM 09/09/02 -0400, you wrote:
>> >>If you have control over the server, why not execute a dts package via
>> >>a stored procedure? It's so much easier...
>> >>
>> >>--
>> >> jon
>> >> mailto:[EMAIL PROTECTED]
>> >>
>> >>Monday, September 9, 2002, 7:13:02 PM, you wrote:
>> >>BD> I wonder if any one can help with this one. SQL Server management is
>> >>not my
>> >>BD> strongest skill :) I'm using a SQL Server login account via CF
>> (cfuser).
>> >>BD> When I create a table via CF, the tables owner is "cfuser" rather than
>> >>DBO.
>> >>BD> This is fine, until I try to do a BULK INSERT and get an error on the
>> >>BD> opentable statement. I can run the BULK INSERT on any of the tables
>> owned
>> >>BD> by dbo.
>> >>
>> >>BD> I set up a new account, with sys_admin privileges and even this
>> >>account can
>> >>BD> not run the BULK INSERT. Another funny thing is the owner account
>> >>(cfuser)
>> >>BD> can also not run the BULK INSERT. Does any one know why this would be?
>> >>
>> >>BD> Thanks :)
>> >>BD> Brook Davies
>> >>
>> >>
>> >>BD>
>> >>
>>BD>
>>
BD>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
