0

I am using the following code to zip a csv file and pdf. My issue is that I get an error that says:

Msg 102, Level 15, State 1, Line 36

Incorrect syntax near '+'.

the above error refers to the first '+ @Imagename +'

declare  @Imagename NVARCHAR(4000) 

set @Imagename = '343434'
EXEC master.dbo.xp_cmdshell '"C:\Program Files\WinRAR\Rar.exe" a -ep1 "D:\PO_PICKUP\'+ @Imagename +'.ZIP" "D:\PO_PICKUP\'+ @Imagename +'.csv" "D:\PO_PICKUP\'+ @Imagename +'.pdf"'

Think my issue is with the quotes and double quotes but I just cant figure it out. When I give the code a static value as follows it works. :

EXEC master.dbo.xp_cmdshell '"C:\Program Files\WinRAR\Rar.exe" a -ep1 "D:\PO_PICKUP\34.ZIP" "D:\PO_PICKUP\34.csv" "D:\PO_PICKUP\34.pdf"'

What am I missing in the code with a variable?

jarlh
  • 42,561
  • 8
  • 45
  • 63
JCW
  • 17
  • 5
  • 3
    You can't pass an expression for a parameter to a procedure. You'll need to assign the value to a variable, and pass that. Though *why* you would want to use SQL Server to create a zip file is a completely different question: smells like an [XY Problem](//xyproblem.info) – Thom A Nov 11 '21 at 11:26
  • The 3rd party program needs the 2 files to be in one zip file. – JCW Nov 11 '21 at 11:29
  • That doesn't explain why you're using SQL Server to do this task. – Thom A Nov 11 '21 at 11:31
  • There is no other programs loaded on the server other than SQL Server and WINRAR. The only other way is to use PI/PO which I dont have control of. – JCW Nov 11 '21 at 11:36
  • 3
    Powershell exists, and every Windows Instance since Windows XP has had that. – Thom A Nov 11 '21 at 11:37
  • I dont have experience with Powershell. Why would it be an issue to use SQL Server to create a ZIP file? – JCW Nov 11 '21 at 11:42
  • 1
    Because that is not what SQL Server is made for. TSQL is not a generalized scripting language. Yes you can hack it but why would you? You can run these exact commands in `cmd` or Powershell. `xp_cmdshell` just passes commands to `cmd` anyway, why would you want another level of indirection? – Charlieface Nov 11 '21 at 11:45
  • 1
    `xp_cmdshell` is a **very** powerful and *dangerous* tool, @JCW . Someone with access to that procedure can do **literally** anything the service account that SQL Server is running under can do; this can often mean users have more power than they normally would. That doesn't mean you *shouldn't* use it (in general), but there are very few times where it should be; Zipping a file is not one of them. – Thom A Nov 11 '21 at 11:49
  • You've also got some issues here launching WinRAR.exe, in particular, from xp_cmdshell. First, WinRAR.exe needs a desktop, and the account under which SQL Server is configured to run may not be allowed to have a desktop. Second, WinRAR.exe is asynchronous, so xp_cmdshell will return control to SQL Server before WinRAR has even finished its task(s), which could present some timing problems. Avoid xp_cmdshell where possible, but especially don't use it with GUI programs. – AlwaysLearning Nov 11 '21 at 13:36
  • Did research on Powershell. Might not be the correct way but here is my solution: ```set @Imagename2 = 'powershell.exe -Command "Compress-Archive -Path D:\PO_PICKUP\'+@Imagename+'.csv" -DestinationPath "D:\PO_PICKUP\'+@Imagename+'.zip" -Force' print @Imagename2 EXEC master.dbo.xp_cmdshell @Imagename2; set @Imagename2 = 'powershell.exe -Command "Compress-Archive -Path D:\PO_PICKUP\'+@Imagename+'.pdf" -Update -DestinationPath "D:\PO_PICKUP\'+@Imagename+'.zip"' print @Imagename2 EXEC master.dbo.xp_cmdshell @Imagename2;```` – JCW Nov 11 '21 at 13:48
  • 1
    @JCW still the wrong approach. Why do this from TSQL at all? If you need to run a Powershell command, do that from Powershell directly, not TSQL. You can even create a SQL Server Agent Job in Powershell language if you really want. – Charlieface Nov 11 '21 at 16:09

0 Answers0