Home TSQL relace a string text with variable
Reply: 1

TSQL relace a string text with variable

Aarion
1#
Aarion Published in 2018-02-12 14:00:15Z

I am trying to replace the folder location in line three of the code below (Set @ProjectBinary ) to a variable.

The original code:

DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

I have created a variable: DECLARE @Location AS varchar(250) = 'C:\SSIS\DWH_IS.ispac'

DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = ('SELECT * FROM OPENROWSET(BULK ' + @Location + ', SINGLE_BLOB') as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

I keep getting an error: Msg 257, Level 16, State 3, Line 61 Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Any suggestions on how to change this to get the code running please?

gbn
2#
gbn Reply to 2018-02-12 14:30:12Z

You need to break it down as follows

DECLARE @SQL nvarchar(1000), @ProjectBinary as varbinary(max);
SET @SQL = N'SELECT @Blob = (SELECT * FROM OPENROWSET(BULK ''' + @Location + ''', SINGLE_BLOB) as BinaryData)'
EXEC sp_executesql @SQL, N'@Blob varbinary(max) OUTPUT', @ProjectBinary OUTPUT;

The line Set @ProjectBinary = (.. can't dynamic or parametrised

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.29868 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO