none
SQLMetal problem with stored proc RRS feed

  • Question

  •  

    Simple stored proc..

     

    CREATE PROCEDURE dbp_dropfkindexes
    AS
        DECLARE index_cursor CURSOR
               FOR
            SELECT t1.name, t2.name
              FROM sysobjects t1,
                   sysindexes t2
             WHERE t1.type = 'U'
               AND t2.id = object_id(t1.name)
               AND t2.name like 'xfk_%'
            OPEN index_cursor
            DECLARE @tablename sysname
            DECLARE @indexname sysname
            FETCH NEXT FROM index_cursor INTO @tablename, @indexname
            WHILE (@@FETCH_STATUS <> -1)
            BEGIN
                EXEC ('DROP INDEX ' + @tablename +'.' + @indexname )
                    FETCH NEXT FROM index_cursor INTO @tablename, @indexname
            END
            DEALLOCATE index_cursor
    GO

    When I try to run SQLMetal against the DB that contains this, I get the following error..

     

    Warning : SQM1014: Unable to extract stored procedure 'dbo.dbp_dropfkindexes' from SQLServer. Line 1: Incorrect syntax near '.'

     

    Get similar error with following proc..

     

    CREATE PROCEDURE dbp_droptriggers
        AS
            DECLARE tables_cursor CURSOR
                    FOR
                SELECT name FROM sysobjects
                 WHERE type = 'TR' AND
                       (name like 'tD_%' OR
                        name like 'tI_%' OR
                        name like 'tU_%')
            OPEN tables_cursor
            DECLARE @tablename sysname
            FETCH NEXT FROM tables_cursor INTO @tablename
            WHILE (@@FETCH_STATUS <> -1)
            BEGIN
                    EXEC ('DROP TRIGGER ' + @tablename)
                    FETCH NEXT FROM tables_cursor INTO @tablename
            END
            DEALLOCATE tables_cursor
    GO

    Except that in this case it's complaining about 'TRIGGER'

     

    Any idea why ?

    Monday, March 3, 2008 12:07 PM

Answers

  • Hi,

     

    This looks like a bug in SQLMetal, sorry about that - it doesn't like the EXEC statement where you are concatenating the string value within the call. I tried something like this instead and it worked fine.

     

    set @cmd = 'DROP TABLE ' + @tablename + '.' + @indexname

    EXEC ( @cmd )

     

    I'll log a bug so that we can address this in a future release.

     

    Thanks.

    -Faisal

     

    Wednesday, March 5, 2008 1:39 AM
    Moderator

All replies

  • Hi,

     

    This looks like a bug in SQLMetal, sorry about that - it doesn't like the EXEC statement where you are concatenating the string value within the call. I tried something like this instead and it worked fine.

     

    set @cmd = 'DROP TABLE ' + @tablename + '.' + @indexname

    EXEC ( @cmd )

     

    I'll log a bug so that we can address this in a future release.

     

    Thanks.

    -Faisal

     

    Wednesday, March 5, 2008 1:39 AM
    Moderator
  •  Faisal Mohamood MSFT wrote:

    I'll log a bug so that we can address this in a future release.

     

    Ok thanks..  any idea when that might be ?

    Thursday, March 6, 2008 9:30 AM
  • The root cause of this issue is the following SQL Server behavior: When SQLMetal attempts to obtain metadata for a stored procedure, SQL Server doesn't actually execute the procedure code (which is correct and desirable behavior). However, this also implies that the variables inside the procedure aren't assigned any values.

     

    In the example you supplied, the variables @tablename and @indexname don't have any values, so the statement inside EXEC( ) becomes "DROP INDEX ." which is invalid syntax. Hence the error "Incorrect syntax near '.'"

     

    Moving the string building out of EXEC( ) causes a NULL string to be generated, which "works" because it is syntactically valid -- i.e., EXEC(NULL) is acceptable T-SQL syntax, although it amounts to a non-operation.

     

    Under these circumstances, the SQLMetal warning "SQM1014: Unable to extract stored procedure 'dbo.dbp_dropfkindexes' from SQLServer. Line 1: Incorrect syntax near '.'" is a reasonable indicator that this sproc should be mapped manually, which is the recommended fix in this situation.

     

    Alternatively, you could write a function which simply invokes this stored procedure, and SQLMetal should be able to map that function without errors or warnings.

     

    Hope this helps.

     

    Thanks,

     

    --Samir

     

    Tuesday, April 1, 2008 2:12 AM
  • Samir,

     

    Regarding your suggested workaround, as far as I know a SQL Server User-Defined Function can't call a stored procedure (with the exception of extended stored procedures and Microsoft recommends against using extended stored procedures in new development). Or am I misunderstanding what you meant by "write a function which simply invokes this stored procedure"?

    Thursday, May 1, 2008 2:04 PM