1

I'm trying to execute the following stored procedure:

CREATE PROCEDURE  dbo.Compress_taille(@nom_table VARCHAR(64))                                            
AS                       
    PRINT @nom_table                                  
    declare @results table                 
    (                                                        
    TableName varchar(250),                           
    ColumnName varchar(250),                       
    DataType varchar(250),                               
    MaxLength varchar(250),                            
    Longest varchar(250),                   
    SQLText varchar(250),                  
    position float                    
    )                                   
    INSERT INTO @results(TableName,ColumnName,DataType,MaxLength,Longest,SQLText,position)                      
    SELECT                 
        Object_Name(c.object_id) as TableName,        
        c.name as ColumnName,            
        t.Name as DataType,     
        case       
        when t.Name not like  '%char%' Then 'NA'
        when c.max_length = -1 then 'Max' 
        else CAST(c.max_length as varchar)
        end as MaxLength,        
        'NA' as Longest,          
        'SELECT Max(Len([' + c.name + '])) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id) as SQLText,               
        column_id as position   
    FROM                                  
        sys.columns c    
    INNER JOIN                       
        sys.types t ON c.system_type_id = t.system_type_id  
    WHERE                 
        c.object_id = OBJECT_ID(@nom_table)               
        and    t.Name <> 'sysname'   
    order by column_id                       


    DECLARE @position varchar(36)                            
    DECLARE @sql varchar(200)                       
    declare @receiver table(theCount int)                 


    DECLARE cursor_script CURSOR                      
        FOR SELECT position, SQLText FROM @results WHERE MaxLength != 'NA'          
    OPEN cursor_script                        
    FETCH NEXT FROM cursor_script                             
    INTO @position, @sql                  
    WHILE @@FETCH_STATUS = 0                  
    BEGIN                  
        INSERT INTO @receiver (theCount)       
        exec(@sql)        

        UPDATE @results           
        SET Longest = (SELECT theCount FROM @receiver)        
        WHERE position = @position     

        DELETE FROM @receiver             

        FETCH NEXT FROM cursor_script             
        INTO @position, @sql   
    END                       
    CLOSE cursor_script                       
    DEALLOCATE cursor_script                         


    DECLARE @script_sql varchar(max)             


    set @script_sql=' create table [AQR_INF_2017T2].[dbo].'+ left(@nom_table, LEN(@nom_table)-LEN('39CR_201703')) +'39CR_201706('  

    DECLARE                             
            @TableName VARCHAR(80),
            @ColumnName VARCHAR(80),
            @DataType VARCHAR(80),
            @MaxLength VARCHAR(80),
            @Longest VARCHAR(80),
            @code_colonne VARCHAR(1000)


    DECLARE getemp_curs CURSOR                              
    FOR                  
        SELECT TableName, ColumnName, DataType,MaxLength,        
        coalesce(case when Longest='0' then '10' else Longest end ,'1') as Longest,
        position,             
        coalesce(
        case when DataType like '%numer%' then '[' + ColumnName + '] float,'
        when DataType like '%char%' then '[' + ColumnName + '] char(' + coalesce(case when Longest='0' then '10' else Longest end ,'1') + '), ' 
        else '[' +ColumnName + '] ' + DataType+ ',' end,'[' +ColumnName + '] nvarchar(1),')    
        AS code_colonne FROM @results order by position                                           


        OPEN getemp_curs      
        FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne          
        WHILE @@FETCH_STATUS = 0 BEGIN   

        set  @script_sql=@script_sql + @code_colonne                                               
        FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne          
        END      
    CLOSE getemp_curs                      
    DEALLOCATE getemp_curs                        

    set  @script_sql= case when  left(@script_sql,1)=',' then   left(@script_sql, LEN(@script_sql) -1) else @script_sql end  + ') '                             


    PRINT '@script_sql: ' + @script_sql                                      
    exec @script_sql

GO                         

But I when I execute this code:

DECLARE @table varchar(255)        

DECLARE cursor_test CURSOR                               
    FOR SELECT name FROM sysobjects WHERE type='U' and substring(name,1,3) not in ('T_P','T_Z','T_R','TEST_AQR')  order by name    -- SUPPRIME LES TABLES NON UTILES POUR L'INFOCENTRE AQR  
OPEN cursor_test                       
FETCH NEXT FROM cursor_test                            
INTO @table              
WHILE @@FETCH_STATUS = 0               
BEGIN
    EXEC AQR_INF_2017T2.dbo.Compress_taille @table
    FETCH NEXT FROM cursor_test          
    INTO @table    
END                       
CLOSE cursor_test                   
DEALLOCATE cursor_test

I get this error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

This code was working for one year and now it doesn't. Our version control does not seem to help either, and, unfortunately, the logic does not seem straightforward to me.

One thought was about the version of SQL Server causing breaking changes, but I am not convinced.

How would I go about troubleshooting this issue? Are there any good industry practices for tracking down script issues when dynamic sql is involved?

I need to verify where the breaking code starts, not necessarily where the syntax error occurs.

closed as off-topic by Kin Shah, Tom V, Erik Darling, Max Vernon, swasheck Aug 22 '17 at 19:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Kin Shah, Tom V, Erik Darling, Max Vernon, swasheck
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Added a suggestion for making his question meaningful. Troubleshooting procedures with dynamic sql is often unclear, but the best answer below does a great job at prescribing how troubleshooting works. We start with the problem and work our way up validating each premise until we reach the breaking code(s). – clifton_h Aug 27 '17 at 6:29
4

While it's certainly possible that someone changed your stored procedure and broke it in the process, the simplest explanation for something like this breaking is a change in the input data that causes it to break.

In the comments, a number of people tried your stored procedure in various versions of SQL Server, and it ran successfully. This makes it even more likely that the problem is a change in the data being provided to the stored procedure.

If you can think of a table that's using a data type that none of your tables has used before, that could cause your procedure to start running a branch that it had never needed to run before, causing an error.

You can troubleshoot this using the following steps:

  1. You've already got a statement that prints out the dynamic SQL you execute to your "Messages" window in SSMS, before it executes it.

    Look at the output in that window carefully. Are any tables processed, or are you failing on the very first table?

  2. If you are failing on the very first table, before @script_sql is even printed once, then try running the stored procedure manually. Using SSMS, in the Object Explorer window:

    • open a Database Engine connection to your server;
    • expand your server; then Databases; then your database; then Programmability; then Stored Procedures.
    • Right-click on your stored procedure, and select "Script Stored Procedure As", "CREATE To", "New Query Editor Window".
    • In the query editor window, modify the code that creates your stored procedure so you can run the code directly. remove or comment out the CREATE PROCEDURE line; change the parameter list to DECLARE statements; and comment out or remove the AS.

    Figure out the first table name your script is trying to process. Manually set @nom_table to this table name in your DECLARE statement (or in a SET statement right afterwards). Now, run the modified code from your stored procedure. See exactly where it stops (add additional PRINT statements if necessary to determine exactly where it stops).

  3. If you get to the point where @script_sql is printed out, try copying the statement and running that manually, in a new query window. If that fails, look at the SQL statement carefully.

    • Get the length of @script_sql, and compare that with what was output; beyond a certain length, PRINT will truncate your value (so your wouldn't be getting all of @script_sql in the "Messages" window). You can overcome this by printing substrings of @script_sql
    • If you don't see any obvious errors in the statement, try running parts of it only. Can you create the table with just the first column? just the first two? etc. As you include additional columns, you will eventually find one or more that are causing issues. For each test, start from the full code in @script_sql, to minimize the likelihood of introducing errors by repeated cutting and pasting.
  4. One of the current answers points out a couple of potential issues in your code; I'll point out one more. I don't think the line:

    set  @script_sql= case
                        when left(@script_sql,1)=','
                          then left(@script_sql, LEN(@script_sql) -1)
                        else   @script_sql
                      end  + ') '
    

    is working as you'd expect it to. I believe it's intended to strip a trailing comma from the SQL statement before adding the closing parenthesis. However, it's actually checking if the first character in @script_sql is a comma, not the last. Try this instead:

    set  @script_sql= case
                        when right(@script_sql,1)=','
                          then left(@script_sql, LEN(@script_sql) -1)
                        else   @script_sql
                      end  + ') '
    
  5. Finally - you can get an idea of whether anyone has changed your procedure from SSMS. If you don't still have everything expanded, follow the directions from step 2 above to expand your server and database to the point where you see the "Stored Procedures" folder. Left-click on the folder, then go to the "Windows" menu and choose "Object Explorer Details". You'll get a view of your stored procedure that includes "Create Date" and "Date Last Modified" (if you don't see those, right-click on the headers for the columns you do see, and select those columns so you can see them.) Check the create date and last modified date for the procedure; has it been changed recently?

    If so, then hopefully you have an old version of the stored procedure in a text or .sql file somewhere; or, you have an old backup of your database (you can restore this under a different name, and script out the stored procedure from the backup. You can then remove the old restored DB, and try the older version of the stored procedure, to see if it works).

You should also be using NVARCHAR for all of this instead of VARCHAR. And change the declaration of @code_colonne VARCHAR(1000) to be NVARCHAR(MAX). Something might be being silently truncated, or how code_colonne is being built, such as a blank or still NA (instead of a NULL) for Longest causing the resulting type to be either CHAR() or CHAR(NA).

You could also run a Profiler trace, to see what's going on during the procedure execution.

  • >>>a number of people tried your stored procedure in various versions of SQL Server, and it ran successfully<<< The only thing that they are tested is that this procedure can be compiled. It cannot be executed because of the last row exec @script_sql. This syntax is used to exec stored procedures, not the dynamic code (should be exec(@script_sql) – sepupic Aug 23 '17 at 7:10
  • @sepupic you were right ! The problem comes from the number of characters because I have more than 8000. It just recover the code from my co-workers. I split the code in several part in different @'script_sql ,@'script_sql1..... and exec all at the end. And it works. But the code works on others SSMS version older like 2016.... So thank you for your explanations! – martine Aug 23 '17 at 11:46
0

You have 2 potential "issues" in your code and one real issue.

First is this:

create table [AQR_INF_2017T2].[dbo].'+ left(@nom_table, LEN(@nom_table)-LEN('39CR_201703')) +'39CR_201706('

What will happen if table name is less than LEN('39CR_201703') ? You'll get an error about incorrect parameter passed to left function.

And if table name is equal to LEN('39CR_201703'), you'll try to create a table with the name starting with 39 that is not quoted.

So at least you shoul add another filter and len(name) > LEN('39CR_201703') in your cursor query:

SELECT name 
FROM sysobjects 
WHERE type='U' and substring(name,1,3) not in ('T_P','T_Z','T_R','TEST_AQR')
and len(name) > LEN('39CR_201703')  
order by name

The second potential issue is the length of your variable.

Even if you declared your @script_sql as varchar(max), the string you are constucting will be truncated to 8000 characters only, try this code to figure out why:

DECLARE @script_sql varchar(max);
set @script_sql = replicate('a', 10000);
select len(@script_sql);

set @script_sql = replicate(cast('a' as varchar(max)), 10000);
select len(@script_sql);

Saying this imagine wide enough table with long field names that when added to your @script_sql superate 8000 characters. The script you getting will be truncated, an if it's truncated at the point where the size is specified, you'll get smth like this:

create table [AQR_INF_2017T2].[dbo].tblname39CR_201706(col1 int,... colN decimal(10,2)

There is unclosed ) because the table definition is truncated (the last ) close decimal type, not the table definition)

In this case when you'll try to execute the code you'll get the error

Incorrect syntax near ')'.`

To find out if it's your case just ALTER your proc adding try..catch to your exec query, but leave PRINT statement. In this case when the proc will stop finishing in catch block, you can print from it the statement that was truncated

The last issue just cannot allow you to execute your dynamic code because you use wrong syntax:

exec @script_sql

Exec without parentheses will try not to execute the dynamic code but stored prosedure named 'create table...' So your code will stop execution with the error

Could not find stored procedure 'create table ...

You should use this syntax: exec (@script_sql);

Not the answer you're looking for? Browse other questions tagged or ask your own question.