SQL Server Stored Procedure Help

  • I'm currently creating an application where clients can make requests for changes on settings stored in a MS SQL Server database. The current structure of the app is two databases that are mirrors of each other's structure, but the data is stored on the first. The first database serves as the master database and the second is where requests are stored while they are being processed. Ideally, the process I'm trying to achieve goes like this:

    1) A client opens a form that queries the master database. Here the client can find the record that they would like changed. They can select a record and click a button that will open up a second form where the changes can be made.

    2) Once a record is selected and the button is pressed a stored procedure will copy the record from the master database to the requests database where it can be modified.

    3)After being modified and submitted, another stored procedure will compare the record in this database with the original record from the master database column by column. Each column that is found to be different will be inserted into a queue table that will later be used to change the master database.

    Basically, I'm stuck at the third step. The stored procedure sp_fill_queue, created in Enterprise Manager, has been giving low resource errors and not doing what I intended it to do. The code is shown below. I've looked it up and down and I can't figure out what could be causing a possible memory leak or an unending loop. After I execute the query, it says it has finished with errors and a popup shows saying that the system has low resources. Can anybody see anything that I may have missed? Any help would be appreciated.

    SQL stored procedure:


    /*

    **This procedure takes a request number and servername of a new server request

    **and compares the request submitted to the original data in the master database,

    **then inserts a row on the queue for each field changed along with the information

    **about the field location and value.

    **INPUT: @request_no = request number

    ** @servername = name of the new server

    */

    CREATE PROCEDURE sp_fill_queue

    @request_no int,

    @servername varchar(50)

    AS

    SET NOCOUNT ON

    DECLARE@table_cursor CURSOR,--cursor to go through list of tables

    @column_cursor CURSOR,--cursor to go through list of columns

    @Tcursor_status int,--variable to keep track of table cursor status

    @Ccursor_status int,--variable to keep track of column cursor status

    @current_table varchar(50),--current table being compared

    @server_column varchar (50),--label for the server name column of current table

    @current_column varchar(50),--current column being compared

    @current_col_type varchar(50),--data type of the current column

    @master_variant varchar(250),--variable that holds the data to be compared from master db

    @request_variant varchar(250),--variable that holds the data to be compared from request db

    @sql1 nvarchar(4000),--sql statement variables

    @sql2 nvarchar(4000),

    @sql3 nvarchar(4000)

    /*

    **Create a temporary table to hold the list of tables to be copied.

    **The table fields are the name of each table and the Server Name label

    **that goes along with that table.

    */

    CREATE TABLE temp_table_list (table_name varchar(50), column_name varchar(50))

    INSERT INTO temp_table_list

    SELECT table_name, column_name FROM INFORMATION_SCHEMA.columns

    WHERE table_name LIKE 'Server_%'

    AND column_name LIKE '%Server Name'

    ORDER BY table_name

    --Set the table cursor

    SET @table_cursor = CURSOR FAST_FORWARD

    FOR

    SELECT table_name, column_name

    FROM temp_table_list

    --Fetch the first value for the table cursor

    OPEN @table_cursor

    FETCH NEXT FROM @table_cursor

    INTO @current_table, @server_column

    SET @Tcursor_status = @@fetch_status-- store the fetch status of the table cursor

    --While statement that traverses through all the tables

    WHILE (@Tcursor_status = 0) and (@@error = 0)

    BEGIN

    CREATE TABLE temp_column_list (column_name varchar (50), data_type varchar (50))

    INSERT INTO temp_column_list

    SELECT column_name, data_type FROM INFORMATION_SCHEMA.columns

    WHERE table_name = @current_table

    AND column_name NOT LIKE '%Server Name'

    AND column_name <> 'ReqID'

    --Set the column cursor

    SET @column_cursor = CURSOR FAST_FORWARD

    FOR

    SELECT column_name, data_type

    FROM temp_column_list

    --Fetch the first values of the column cursor

    OPEN @column_cursor

    FETCH NEXT FROM @column_cursor

    INTO @current_column, @current_col_type

    SET @Ccursor_status = @@fetch_status--Store the fetch status of the column cursor

    --While loop that traverses through all the columns of the current table

    WHILE (@Ccursor_status = 0) and (@@error = 0)

    BEGIN

    IF (@current_col_type = 'varchar')

    BEGIN

    SELECT CONVERT (varchar(250), @request_variant)

    SELECT CONVERT (varchar(250), @master_variant)

    END

    IF (@current_col_type = 'int')--convert the variables used to hold the data for comparison

    BEGIN-- into int data types if the data type of the current column is int

    SELECT CONVERT (int, @request_variant)

    SELECT CONVERT (int, @master_variant)

    END

    --SQL that extracts the current column value from the request db

    SET @sql1 = 'SELECT ' +@request_variant+ ' = ' +@current_column+

    ' FROM RequestCenter.dbo.Request_' +@current_table+

    ' WHERE ReqID = ' +@request_no

    EXEC sp_executesql @sql1--execute the SQL statement

    --SQL that extracts the current column value from the request db

    SET @sql2 = 'SELECT ' +@master_variant+ ' = ' +@current_column+

    ' FROM RequestCenter.dbo.' +@current_table+

    ' WHERE ' +@server_column+ ' = ' +@servername

    EXEC sp_executesql @sql2--execute the SQL statement

    PRINT @request_variant

    PRINT @master_variant

    IF (@request_variant <> @master_variant) --compare the two values, if not equal then add to the queue

    BEGIN

    SET @sql3 = 'INSERT INTO RequestCenter.dbo.Request_Queue' +

    ' VALUES (' +@servername+ ', ' +@current_table+ ', ' +@current_column+ ', ' +@request_variant+ ', 0, ' +@request_no+ ')'

    END

    --Convert the variables back to variant form

    SELECT CONVERT (sql_variant, @request_variant)

    SELECT CONVERT (sql_variant, @master_variant)

    -- Fetch the next column name and data type

    FETCH NEXT FROM @column_cursor

    INTO @current_column, @current_col_type

    SET @Ccursor_status = @@fetch_status--Store the fetch status of the column cursor

    END

    --Fetch the next table name and server name column label

    FETCH NEXT FROM @table_cursor

    INTO @current_table, @server_column

    SET @Tcursor_status = @@fetch_status-- store the fetch status of the table cursor

    DROP TABLE temp_column_list

    END

    DROP TABLE temp_table_list

    CLOSE @table_cursor

    CLOSE @column_cursor

    DEALLOCATE @table_cursor

    DEALLOCATE @column_cursor

    GO


  • This was removed by the editor as SPAM

  • I won't claim to have spent enough time on this to do it justice.  However, the use of the cursors seems like it would be an expensive process.  Ideally you need to do more set based processes.  In addition, the temp table you created is actually a true DB table, you need to use # before the table to make it a temp table.  That too may use a good bit of resources. 

    SelectCOLUMN_NAME

    Frompubs_mod.INFORMATION_SCHEMA.Columns

    WhereTABLE_NAME = 'authors'

    ANDCOLUMN_NAME NOT IN (

    SELECT COLUMN_NAME

    FROM pubs.INFORMATION_SCHEMA.Columns

    WhereTABLE_NAME = 'authors' )

    I found a bit of code on SQLTeam.com that may help with the column compare.

  • Thanks for the reply. From what I can tell that section of code looks to be comparing the columns in a table, but I need to compare specific records. I think I've narrowed down my problem for now.

    --SQL that extracts the current column value from the request db

    SET @sql1 = 'SELECT @value = '+@current_column+

    ' FROM RequestCenter.dbo.Request_'+@current_table+

    ' WHERE ReqID = @number'

    EXEC sp_executesql @sql1,--execute the SQL statement

    N'@value varchar(250) out, @number int',

    @varchar_value out, @request_no

    In the code above, I am trying to set the @varchar_value to be equal to a value in a record using a dynamic SELECT statement.

    @current_column is the column name

    @current_table is the table name

    @request_no is the unique number of the record

    So the result should be that @varchar_value = value of the column with the unique number specified. Right now, however, I'm only getting a lot of syntax errors. Is there something wrong with this code?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply