none
ERROR - Unclosed quotation mark after the character string?

    Question

  • I keep getting an error that states...Unclosed quotation mark after the character string '@FilePath'

    I am getting this while trying to do a bulk insert in my procedure...here is what my code looks like...any help is much needed as I have been looking at this for a while now. Thanks

     

    USE [myDB]

    GO

    /****** Object:  StoredProcedure [myStoredProc]    Script Date: 11/11/2010 11:15:59 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

     

    ALTER PROCEDURE [myStoredProc] 

    @SourcePath varchar(100),

    @FormatFilePath varchar(100)

     

    AS

    SET ANSI_WARNINGS OFF

     

     

    DECLARE @str_command nvarchar (150)

    SET @str_command = 'BULK INSERT [histTable] FROM ''' + @SourcePath + ''' WITH (formatfile = ''' + @FormatFilePath + ''')'

     

    EXEC SP_EXECUTESQL @str_command


    Thursday, November 11, 2010 9:44 PM

Answers

All replies

  • You don't show what parameters you are using when you call this procedure.  But you are concatenating @SourcePath and @FormatFilePath to some text.  The two parameters can each be 100 characters, so your result might be more than 200 characters.  You are putting this into a nvarchar(150), to you could be getting truncation.

    Another, much less likely, cause would be you have a ' in the parameters you are passing.

    The best way to find out and debug problems with dynamic SQL is to add a PRINT @str_command statement just before the EXEC SP_EXECUTESQL @str_command.  That will let you see what is in your string and that usually makes it obvious what the problem is.  When the procedure is working, then you can remove or comment out the PRINT command.

    Tom

    • Proposed as answer by Kalman TothModerator Thursday, November 11, 2010 10:42 PM
    • Unproposed as answer by Spawn10 Thursday, November 11, 2010 10:49 PM
    Thursday, November 11, 2010 10:12 PM
  • Thanks for your response Tom...i changed the bulk insert statement to try to get a basic operation going first...so the new bulk insert looks like this;

    SET @str_command = 'BULK INSERT histTable FROM '''+@SourcePath+''' WITH (FIELDTERMINATOR = '','') '

    i also included the PRINT @str_command  statement. However, i am no longer getting the same error. I am now getting an error that says -  

    "Cannot bulk load because the file "C:\Documents and Settings\...\Log" could not be opened. Operating system error code 3(The system cannot find the path specified.)."

     

    The bulk construct looks right as this is what the PRINT statement shows

    BULK INSERT histTable FROM 'C:\Documents and Settings\...\Log.txt' WITH (FIELDTERMINATOR = ',') 

     

    Any ideas on how to get past this and get the bulk insert going?

    Thanks again.

    Thursday, November 11, 2010 10:37 PM
  • First, where is the file.  The command you are using will try to read the file from the C drive of the server where SQL Server is running, not the C drive of your workstation you are using (unless, of course, your workstation is the server).  Second, the permissions must be set correctly.  What permissions are used depends on whether you have logged in with Windows Authentication or not.  If you used Windows Authentication, you must have access to the file, if you used a SQL login, then the process SQL is running must have access to the file.

    Both of these issues are discussed at http://msdn.microsoft.com/en-us/library/ms175915.aspx

    Tom

    • Marked as answer by KJian_ Thursday, November 18, 2010 7:21 AM
    Thursday, November 11, 2010 10:58 PM
  • Initially I was trying to point it to the location on my local machine rather than the server. However, when I changed the path to one that is on the server where SQL is running, I still get the same error. And i do have full control on the files and folder on the server. So the permissions are sufficient to do a bulk insert. 

    Am still getting:-

    "Cannot bulk load because the file "\\Sever\...\logger.txt" could not be opened."

    Friday, November 12, 2010 3:11 PM
  • You may have permission issue. The following page has examples for BULK INSERT:

    http://sqlusa.com/bestpractices2005/notepad/

    Consider setting up the BULK INSERT as a SQL Server Agent job with proxy account. Related link:

    How to: Create a SQL Server Agent Job (Transact-SQL)

    How to: Modify a Proxy (SQL Server Management Studio)

    How to: Create a SQL Server Agent Proxy (Transact-SQL)

    How to: Modify a SQL Server Agent Proxy (Transact-SQL)

    How to: Create a Credential (SQL Server Management Studio)

    How to: Schedule a Job (SQL Server Management Studio)

    You can start a job programmatically as explained on the following page:

    http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Thursday, November 18, 2010 7:21 AM
    Monday, November 15, 2010 7:08 PM