none
System.Data.SqlClient.SqlException RRS feed

  • Question

  • I'm trying to execute a stored procedure in sql server to truncate a table.  This is the path to the sql file

    "C:\\Users\\myname\\OneDrive\\Documents\\SQL Server Management Studio\\Truncate.sql";

    This is the exception  thrown at ExecuteReader(); 

    'System.Data.SqlClient.SqlException' in System.Data.dll
    Incorrect syntax near '\'.

    I get unrecognized escape sequence if I use any single back slashes, so why the incorrect syntax?  Any help appreciated

    Monday, February 12, 2018 8:14 PM

Answers

  • Got it working.  It can't find the SP if its never created to begin with.  Query->Parse, then Query->Execute.  Table successfully truncated in Visual Studio.  
    Tuesday, February 13, 2018 6:34 PM

All replies

  • You probably have specified the path instead of the text of the script. Try loading the file using File.ReadAllText, then execute the returned string, and show some details about your program.

    Tuesday, February 13, 2018 5:38 AM
  • Hi Richard,

    I'm not sure if it is because there are some space in the folder name, occasionally, spaces cause some exceptions.

    Try to remove the space in the folder name.

    Also if you want to use single back slashes, you can add "@" at the beginning of the path:

    @"C:\Users\myname\OneDrive\Documents\Truncate.sql";

    Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 13, 2018 6:06 AM
    Moderator
  • The difference between an Exception and a SQLException is that the SQLException is about SQL. 

    Most probably there is something wrong in your Stored Procedure, but that is not visable from the client. 

    Maybe you are even in an organization where security does not allow workstation developers to see server code and then you have to ask it to those who do the SQL SP code. 


    Success
    Cor

    Tuesday, February 13, 2018 4:34 PM
  • I'm developing on my own personal laptop.  I have a file in sql server truncate.sql containing.

    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    CREATE PROCEDURE TruncateTableA
    	
    AS
    BEGIN
    	truncate table tableA;
    END
    GO

    This is my code

    SqlCommand truncateTableA = new SqlCommand("TruncateTableA", Connection);
    truncateTableA.CommandType = CommandType.StoredProcedure;
    Connection.Open();
    TruncateReader = truncateTableA.ExecuteReader();
    

    Getting System.Data.SqlClient.SqlException: 'Could not find stored procedure 

    'TruncateTableA'.'  How do I execute the stored procedure?
    Tuesday, February 13, 2018 4:49 PM
  • Got it working.  It can't find the SP if its never created to begin with.  Query->Parse, then Query->Execute.  Table successfully truncated in Visual Studio.  
    Tuesday, February 13, 2018 6:34 PM