What are the challenges you face when working across database platforms? Take the survey
Options

Format SQL fails when using TRIM() function

tentradetentrade Posts: 4 New member
I have the following line in my T-SQL

SELECT @sql=TRIM(',' FROM @sql)

When this line is present the 'Format SQL' fails with Exception "Cannot align to a future token "," at line 73, column 21, offset 2919". Column 26,27 and 28 are ',' in the select statement.

If I comment out this line, then the 'Format SQL' completes successfully.
Tagged:

Answers

  • Options
    Russell DRussell D Posts: 1,324 Diamond 5
    edited August 5, 2019 7:51AM
    What version of Prompt are you using, and can you share the full script please? I can't find any problems with Trim().
    Have you visited our Help Centre?
  • Options
    tentrade1tentrade1 Posts: 1 New member
    edited August 6, 2019 9:21PM
    I am using the most recent version, 9.5.12.10845

    Here is the full script (Format SQL works if the line with TRIM() in it is commented out)

    USE [database1]
    GO

    /****** Object:  StoredProcedure [dataimport].[FileToTable]    Script Date: 8/6/2019 4:16:58 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dataimport].[FileToTable]
    @filepathandname [nvarchar](255),
    @DBName [nvarchar](128),
    @TableSchema [nvarchar](128),
    @TableName [nvarchar](128),
    @addExtraColumns [bit] = 1
    WITH EXECUTE AS CALLER
    AS
    BEGIN
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;

            IF @filepathandname LIKE '"%"'
                SET @filepathandname = SUBSTRING(@filepathandname, 2, LEN(@filepathandname) - 2);

            DECLARE @FILENAME NVARCHAR(255) = REVERSE(LEFT(REVERSE(@filepathandname), CHARINDEX('\', REVERSE(@filepathandname)) - 1));
            DECLARE @PATH NVARCHAR(255) = LEFT(@filepathandname, PATINDEX('%' + @FILENAME, @filepathandname) - 1);
            DECLARE @sql VARCHAR(MAX);
            DECLARE @dbschematable1 NVARCHAR(255) = N'[' + @DBName + N'].[' + @TableSchema + N'].[' + @TableName + N'1]';
            DECLARE @dbschematable2 NVARCHAR(255) = N'[' + @DBName + N'].[' + @TableSchema + N'].[' + @TableName + N']';

            --1. check the file, get column names, create table with these column names
            EXEC ('DROP TABLE IF EXISTS ' + @dbschematable1 + ';');

            SET @sql = 'SELECT TOP 0 * INTO ' + @dbschematable1 + '

    FROM OPENROWSET(''Microsoft.ACE.OLEDB.16.0'',

    ''Text;Database='  + @PATH + ';HDR=YES;MaxScanRows=1;IMEX=1'',

    ''SELECT TOP 1 * FROM ' + @FILENAME + ''')';

            EXEC (@sql);

            DROP TABLE IF EXISTS #colnames;

            CREATE TABLE #colnames (id INT, colname NVARCHAR(255));

            EXEC ('INSERT #colnames SELECT column_id,name FROM ' + @DBName + '.sys.columns WHERE object_id = object_id(''' + @dbschematable1 + ''');');

            SET @sql = 'CREATE TABLE ' + @dbschematable1 + '(';

            DECLARE @colname VARCHAR(MAX);
            DECLARE @colname2 VARCHAR(MAX);

            SELECT @colname = MIN(colname)
            FROM   #colnames
            WHERE  id = (SELECT MIN(id)FROM #colnames);

            WHILE @colname IS NOT NULL
                BEGIN
                    SET @colname2 = @colname;

                    IF @colname IN (SELECT currentname FROM #RenameThese)
                        SELECT @colname2 = newname
                        FROM   #RenameThese
                        WHERE  currentname = @colname;

                    SET @sql = @sql + '[' + @colname2 + '] [NVARCHAR](MAX) NULL,';

                    SELECT @colname = MIN(colname)
                    FROM   #colnames
                    WHERE  id = (SELECT MIN(id)FROM #colnames WHERE id > (SELECT c.id FROM #colnames c WHERE c.colname = @colname));
                END;

            DROP TABLE IF EXISTS #colnames;

            SELECT @sql=TRIM(',' FROM @sql)+') ON [PRIMARY]';
            EXEC ('DROP TABLE IF EXISTS ' + @dbschematable1 + ';');

            EXEC (@sql);

            --now do the data insert
            EXEC ('BULK INSERT ' + @dbschematable1 + '
    FROM ''' + @filepathandname + '''
    WITH
    (
    FORMAT = ''CSV'', 
    FIELDQUOTE = ''"'',
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
    )'  );

            IF @addExtraColumns = 1
                BEGIN
                    DECLARE @columnlisttable TABLE (collist VARCHAR(MAX));

                    INSERT @columnlisttable (collist)
                    EXEC ('SELECT a.NAME FROM '+@DBName+'.sys.columns A WHERE a.object_id = OBJECT_ID(''' + @dbschematable1 + ''')');

    SELECT collist FROM @columnlisttable

                    SET @sql = 'DROP TABLE IF EXISTS ' + @dbschematable2 + ';

                    SELECT *
                          '    + IIF('FullName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(2440), NULL) AS FullName', '') + '
                          '    + IIF('LastName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS LastName', '') + '
                          '    + IIF('FirstName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS FirstName', '') + '
                          '    + IIF('MiddleOtherNames' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS MiddleOtherNames', '') + '
                          '    + IIF('SuffixGen' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(70), NULL) AS SuffixGen', '') + '
                          '    + IIF('DOBDT' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(DATE, NULL) AS DOBDT', '') + '
    '                          + IIF('DispositionDateDT' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(DATE, NULL) AS DispositionDateDT', '') + '
                    INTO   '   + @dbschematable2 + '
                    FROM   '   + @dbschematable1 + '

    DROP TABLE IF EXISTS '     + @dbschematable1;

                    EXEC (@sql);
                END;
            ELSE
                BEGIN
                    EXEC ('DROP TABLE IF EXISTS ' + @dbschematable2 + ';

                    SELECT *
                    INTO   ' + @dbschematable2 + '
                    FROM   ' + @dbschematable1 + '

    DROP TABLE IF EXISTS ' + @dbschematable1);
                END;
        END;
    GO


  • Options
    tentradetentrade Posts: 4 New member
    Nudge. Any news on this?
  • Options
    Using 9.5.15 this formats without problem for me - whilst I can't actually find that we've fixed anything in particular to do with TRIM, I couldn't reproduce it before either. Can you update and try again?
    Have you visited our Help Centre?
  • Options
    I too have this problem. The TSQL TRIM function can take an additional parameter which is the string sequence (usually a single character) to be removed from the start and end of a string. The default value is the single space character, as in: TRIM(@stringVar) which works fine but TRIM(',', @stringVar) to remove leading or trailing commas causes SqlPrompt to display an error panel.  My little string tidy function is:

    <div><i>create function [Maintenance].[strTidy]</i></div><div><i>(</i></div><div><i>&nbsp; &nbsp; @str NVARCHAR(max)</i></div><div><i>)</i></div><div><i>returns NVARCHAR(max)</i></div><div><i>as</i></div><div><i>&nbsp; &nbsp; begin</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; declare @ResultVar NVARCHAR(max);</i></div><div><br></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = TRIM( COALESCE( @str, '' ));</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = TRIM( ',' from @ResultVar);&nbsp; <b><<-- Comment this line and SQL Prompt is OK</b></i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = REPLACE( @ResultVar, '&nbsp; ', ' ' );</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; if @ResultVar = 'undisclosed'</i></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set @ResultVar = N'';</i></div><div><br></div><div><i>&nbsp; &nbsp; &nbsp; &nbsp; return @ResultVar;</i></div><div><br></div><div><i>&nbsp; &nbsp; end</i></div>

    SQL Prompt VS 9.5.16.11294
    SQL Server: 14.0.2027.2
  • Options
    Russell DRussell D Posts: 1,324 Diamond 5
    edited September 12, 2019 12:57PM
    Ok that one helps reproduce it, thank you. I've raised this as SP-7802 and will come back to you when we've had a chance to review it.
    Have you visited our Help Centre?
  • Options
    This is fixed in 9.5.18 - https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_9.5.18.11513.exe. Let me know how you get on.
    Have you visited our Help Centre?
  • Options
    ccparkhillccparkhill Posts: 17 Bronze 1
    edited September 25, 2019 2:55PM
    Still not completely fixed, the below SQL now formats fine:
    <div>SELECT TRIM('AW' FROM [dc].[CustomerAlternateKey]) AS [CustomerAlternateKey]&nbsp; , [dc].[CustomerKey] FROM [dbo].[DimCustomer] AS [dc];</div>

    However, when TRIM is not in the first column "Format SQL" fails with "Exception: Cannot align to a future token "AW" at line 2, column 10, offset 37", e.g.:
    <div>SELECT [dc].[CustomerKey], TRIM('AW' FROM [dc].[CustomerAlternateKey]) AS [CustomerAlternateKey] FROM [dbo].[DimCustomer] AS [dc];</div>

    I'm using 9.5.19.11631.

  • Options
    Still not working correctly in 9.5.23.
  • Options
    Still not working correctly in 10.0.1.12389.
Sign In or Register to comment.