I have stored procedure that I call to insert records that uses OPENROWSET.
As such it references a system file. See below an excerpt of the Sproc
In the Boomi integration:
This Sproc works fine if I use a fully qualified filename E.g. D:\DIR1\DIR2\fiename.xml
If fails if I use: \\Server-Name\Share\fiename.xml or \\Server-IP\Share\filename.xml
However, using SQL Server Management Studio both forms work.
What's the issue?
select @cmd = 'select '''+@FullFileName+''', '+CONVERT(varchar,@XXXID)+','+isnull(CONVERT(varchar,@YYYYID),'NULL')+
', getdate(), '+convert(varchar,@QQQQID)+', '+
' BulkColumn from OPENROWSET(BULK N'''+@FullFileName+''', SINGLE_BLOB) MyXML'
Tests being run on the Atom machine
SQL Server running on the Atom machine
Atom running as local system account
Local system account granted access to the share (don't know if this is required)
Eventually this Sproc will run on a SQL server machine that is different from Atom machine so it will need to be accessed via Server-IP\Share\filename.xml
Why do i need to do this you ask? It's the only way I've found to be able to load text with Unicode characters into SQL server without them being converted. If there's another way to load text and preserve Unicode characters, I'm all ears