set a local variable

• nikki123

  Ten Centuries

  Points: 1338

  More actions

  February 24, 2015 at 10:15 am

  #314960

  declare @filename varchar(255),

  @path varchar(255),

  @sql varchar(8000),

  @sql3 varchar(8000),

  @cmd1 varchar(1000),

  @cmd2 varchar(1000),

  @cmd3 varchar(1000),

  @cmd4 varchar(1000)

  SET @path = '\\fileserver1\Repository\Cost Reports\Latest Cost Reports\'+ @year +'\Hospital\HOSPFY' +@year

  SET @cmd1 = 'dir'+' "'+'\\fileserver1\Repository\Cost Reports\Latest Cost Reports\'+ @year +'\Hospital\HOSPFY' +@year +'"' +'\*ALPHA.CSV /b/s'

  SET @SQL3 = 'INSERT INTO ' + @bd_name + '.dbo'+'.ALLFILENAMES(WHICHFILE)' + 'EXEC Master..xp_cmdShell ' + @cmd1

  But i am getting the value as

  INSERT INTO Cost_reports.dbo.ALLFILENAMES(WHICHFILE)EXEC Master..xp_cmdShell dir "\\fileserver1\Repository\Cost Reports\Latest Cost Reports\2009\Hospital\HOSPFY2009"\*ALPHA.CSV /b/s

  But it has to be this -

  EXEC Master..xp_cmdShell ' dir "\\fileserver1\Repository\Cost Reports\Latest Cost Reports\2009\Hospital\HOSPFY2009"\*ALPHA.CSV /b/s'

  i need to set a Apostrophe in front of dir and at the end

  Please let me know how do i add it

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734459

  More actions

  February 24, 2015 at 10:26 am

  #1779671

  Double up on the quotes.

  declare @sql varchar(1000)

  select @sql = 'INSERT dbo.mytest SELECT 'insert sales select ''test'' '

  Also, watch your spaces. You are missing a few.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  February 24, 2015 at 10:28 am

  #1779672

  What Steve said (I just saw his response as I was about to reply)...

  This is a good article SSC on the apostrophe topic... http://www.sqlservercentral.com/articles/T-SQL/95670/

  "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

• nikki123

  Ten Centuries

  Points: 1338

  More actions

  February 24, 2015 at 10:42 am

  #1779678

  tried using this

  declare @sql varchar(1000)

  select @sql = 'INSERT dbo.mytest SELECT 'insert sales select ''test'' '

  getting an error unclosed quoataion marks

• nikki123

  Ten Centuries

  Points: 1338

  More actions

  February 24, 2015 at 10:45 am

  #1779679

  Used this SET @SQL3 = 'INSERT INTO ' + '2009' + '.dbo.ALLFILENAMES(WHICHFILE) EXEC Master..xp_cmdShell ''' + @cmd1 + ''''

  Its working now thank you