• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

SQL SERVER QUOTATION MARKS IN STORED PROCEDURE

I desperately need some help in writing the string below in a stored procedure. I cant seem to get the quotations correct.

\\10.49.32.235\webshared\spares\ is a string and needs to be enclosed in single quotations
@MyFile is a variable that I am passing to the procedure
FIELDTERMINATOR =','      The , has to be enclosed in single quotes for this line to.

Any help you could give me would be greatly appreciated.


Select @Sql ='
BULK INSERT spares
FROM '\\10.49.32.235\webshared\spares\' + @MyFile +
'WITH (FIELDTERMINATOR =',')'
END
EXECUTE @SQL
0
phil301
Asked:
phil301
  • 4
  • 3
1 Solution
 
Jinesh KamdarCommented:
Use single-quotes twice : -

'WITH (FIELDTERMINATOR ='','')'
0
 
phil301Author Commented:
The statement below does not work either. It is seeing the @MyFile as a string and the rest of the statement as a literal.  Just the opposite of what is needed.

Select @Sql ='
BULK INSERT spares
FROM ''\\10.49.32.235\webshared\spares\'' + @MyFile +
'WITH (FIELDTERMINATOR ='','')'
0
 
Jinesh KamdarCommented:
This should work. (Note ''' before + @MyFile)

Select @Sql = 'BULK INSERT spares
FROM ''\\10.49.32.235\webshared\spares\''' + @MyFile +
'WITH (FIELDTERMINATOR ='','')'
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
phil301Author Commented:
Almost there, it would appear from the error code below that the only visible problem is that it is inserting and extra ' after the "spares\" and before the "test.csv"

I attempted to take it out, but it screwed everything else up.



'BULK INSERT Order1005 FROM '\\10.49.32.235\webshared\spares\'test.csv WITH (FIELDTERMINATOR =',')' is not a valid identifier."
  Number=203
0
 
Jinesh KamdarCommented:
Oh i see. Then use this : -

Select @Sql = 'BULK INSERT spares
FROM ''\\10.49.32.235\webshared\spares\' + @MyFile +
'''WITH (FIELDTERMINATOR ='','')'
0
 
Jinesh KamdarCommented:
Better stilll, use this. I inserted a whitespace before WITH.

Select @Sql = 'BULK INSERT spares
FROM ''\\10.49.32.235\webshared\spares\' + @MyFile +
''' WITH (FIELDTERMINATOR = '','')'

This should generate the string as below : -

'BULK INSERT spares FROM '\\10.49.32.235\webshared\spares\test.csv' WITH (FIELDTERMINATOR =',')
0
 
phil301Author Commented:
Great, that did the trick for the SQL statement. I will go ahead and accept the solution. However, I need to start another thread now because I am getting the following error  when I execute the stored procecure.

System.Data.SqlClient.SqlException was unhandled
  Class=16
  ErrorCode=-2146232060
  LineNumber=26
  Message="The name 'BULK INSERT Order1006 FROM '\\10.49.32.235\webshared\spares\test.csv' WITH (FIELDTERMINATOR = ',')' is not a valid identifier."
  Number=203
  Procedure="csvimport"
  Server="Ambas0002\BAEWEB"
  Source=".Net SqlClient Data Provider"
  State=2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now