Or you should be able to read the file with cffile and then pass it to SQL Server in a cfprocparam...
On Tue, May 3, 2011 at 6:01 AM, Steven Durette <[email protected]> wrote: > > First SQL has to be running under an account that has access to that > directory. Then it is easiest if you make a share, then you reference it as > \\sernername\sharename\filename > > Steve > > Sent from my iPhone > > On May 3, 2011, at 12:48 AM, Kam Heydari <[email protected]> wrote: > > > > > Hi, > > Anyone know how to pass file name to the bulkload when sqlserver and > coldfision servers are on different physical servers; The following xml load > works when both SQl and CF servers are residing on the same physical server. > > - How I can refer to the local file where sqlserver can access it? > > - Any alternative ways? > > Thanks > > K > > > > EXEC(' > > INSERT INTO [ProjectXML](Code, Reference, xmlFileName, [xmlDocument]) > > SELECT '''+'2503'+''','''+'455'+''', ''' + > 'C:\ColdFusion9\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\neotmp6410035959573708012.tmp' > + ''', xmlData FROM ( SELECT * FROM OPENROWSET (BULK ''' + > 'C:\ColdFusion9\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\neotmp6410035959573708012.tmp' > + ''' , SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) ') > > > > Error when on different servers: > > Message: Error Executing Database Query. > > Detail: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot bulk load > because the file > "C:\ColdFusion9\runtime\servers\coldfusion\SERVER-INF\temp\wwwroot-tmp\neotmp4527544419188947137.tmp" > could not be opened. Operating system error code 3(The system cannot find > the path specified.). > > sqlStatement: HY000 > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344156 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
