February 21, 2013 at 6:09 am
Hi everybody,
I struggled with a bulk insert in procedure and i eventually found a working piece of code. Except that i did'nt understand the general rules to wrap variables.
Exemple :
N'BULK INSERT ' + QUOTENAME(@nomtable) + N'FROM ''' +
@chemin + @filename +
N''' WITH (FIELDTERMINATOR = '';'', ROWTERMINATOR = '''', FIRE_TRIGGERS)'
This is working, but why are we using ''' ??
Now something that's not working..
SET @effacer = 'master.dbo.xp_cmdshell ''DEL '+ @chemin + @filename
EXECUTE (@effacer)
Something wrong with quotes i guess....
I'd like to understand the rule for this!
February 21, 2013 at 6:12 am
I t works using ''''
SET @effacer = 'master.dbo.xp_cmdshell ''DEL '+ @chemin + @filename +''''
but i don't understand why (tried everything)
April 2, 2013 at 10:22 am
It all has to do about strings ... And what is truely a string?
First example does not have quotes in the string.
-- Simple string
DECLARE @X VARCHAR(128);
SET @X = 'A SIMPLE STRING';
PRINT @X;
Second example uses extra quote to denote usage as a literal, not a start of a string.
-- Escaping the quotes
DECLARE @Y VARCHAR(128);
SET @Y = 'A STRING WITH ''' + CONVERT(CHAR(10), GETDATE(), 101) + ''' QUOTES AROUND THE DATE';
PRINT @Y;
Third example uses the ANSI character for quote.
-- Using CHAR()
DECLARE @Z VARCHAR(128);
SET @Z = 'A STRING WITH ' + CHAR(39) + CONVERT(CHAR(10), GETDATE(), 101) + CHAR(39) + ' QUOTES AROUND THE DATE';
PRINT @Z;
You can use the system function QUOTENAME() to make quoted strings.
http://msdn.microsoft.com/en-us/library/ms176114.aspx
You can even change the character used for escaping (ESCAPE) in a LIKE statement.
http://msdn.microsoft.com/en-us/library/ms179859.aspx
I hope this helps.
Sincerely
John
John Miner
Crafty DBA
www.craftydba.com
April 3, 2013 at 4:58 pm
hi,
yes it helps a lot !
thank you very much. I ll try this tomorrow at work
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply