Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL SERVER QUOTATION MARKS IN STORED PROCEDURE

Posted on 2007-10-16
7
Medium Priority
?
772 Views
Last Modified: 2008-01-09
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
Comment
Question by:phil301
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20086355
Use single-quotes twice : -

'WITH (FIELDTERMINATOR ='','')'
0
 

Author Comment

by:phil301
ID: 20086486
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20086742
This should work. (Note ''' before + @MyFile)

Select @Sql = 'BULK INSERT spares
FROM ''\\10.49.32.235\webshared\spares\''' + @MyFile +
'WITH (FIELDTERMINATOR ='','')'
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:phil301
ID: 20086850
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20086876
Oh i see. Then use this : -

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

Accepted Solution

by:
Jinesh Kamdar earned 500 total points
ID: 20086903
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
 

Author Comment

by:phil301
ID: 20087828
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question