Dynamic File Location

• Kirby1367

  Valued Member

  Points: 61

  More actions

  August 3, 2012 at 1:29 pm

  #392889

  I have written a series of script that uses xp_cmdshell (I know the various views on this so don't worry). This is just a very small snippet but will relay what I am trying to accomplish.

  Currently the user has to unzip the folder containing the series of scripts to the root of the C: and everything is handled by Dynamic SQL. I am wondering if I can make the @Pathname dynamic as well so that a user could fire the scripts from any location (such as on the desktop). I realize that a file has no sense of where it resides, but didn't know if there was something I have overlooked. The main goal is to take out as much of the human factor as I can.

  DECLARE @PathName VARCHAR(100) = 'C:\xyzPath\UpgradeFiles\'

  DECLARE @DelBat VARCHAR(100) = 'del ' + @PathName + 'Database_Upgrade.bat'

  EXEC xp_cmdshell @DelBat

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  August 3, 2012 at 4:32 pm

  #1521671

  Kirby1367 (8/3/2012)

  ---

  I have written a series of script that uses xp_cmdshell (I know the various views on this so don't worry). This is just a very small snippet but will relay what I am trying to accomplish.

  Currently the user has to unzip the folder containing the series of scripts to the root of the C: and everything is handled by Dynamic SQL. I am wondering if I can make the @Pathname dynamic as well so that a user could fire the scripts from any location (such as on the desktop). I realize that a file has no sense of where it resides, but didn't know if there was something I have overlooked. The main goal is to take out as much of the human factor as I can.

  DECLARE @PathName VARCHAR(100) = 'C:\xyzPath\UpgradeFiles\'

  DECLARE @DelBat VARCHAR(100) = 'del ' + @PathName + 'Database_Upgrade.bat'

  EXEC xp_cmdshell @DelBat

  One way to do this (I have done something similar) is to create script that does the unzip for them and moves the files to a specific location; say C:\xyzPath\UpgradeFiles\.

  In the script you could include a few lines to write a system variable.

  If you do that you can upgrade your your SQL as follows:

  DECLARE @x TABLE(o varchar(50));

  INSERT INTO @x exec master..xp_cmdshell 'echo %YourSystemVariableHere%'

  DECLARE @PathName VARCHAR(100) = (SELECT MAX(o) FROM @x)

  DECLARE @DelBat VARCHAR(100) = 'del ' + @PathName + 'Database_Upgrade.bat'

  EXEC xp_cmdshell @DelBat

  Look at this example (I am using my program files variable as I am on a machine where I don't have rights to create a new system variable.)

  DECLARE @x TABLE(o varchar(50));

  INSERT INTO @x exec master..xp_cmdshell 'echo %ProgramFiles%'

  DECLARE @PathName VARCHAR(100) = (SELECT MAX(o) FROM @x)

  SELECT @PathName

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001