AnsweredAssumed Answered

SQL Server Stored Procedure problem from Boomi

Question asked by DarrellFlenniken9941 on Jan 6, 2017
Latest reply on Feb 3, 2017 by DarrellFlenniken9941

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'


insert SCHEMA.AAAAAAXML
(
InboundFileName
,XXXID
,YYYYID
,ZZZZDate
,QQQQID
,PPPXML
)
exec (@cmd)

...

 

Notes:

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

Outcomes