June 29, 2006 at 12:49 pm
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
July 3, 2006 at 8:00 am
This was removed by the editor as SPAM
July 5, 2006 at 9:44 pm
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.
July 6, 2006 at 10:22 am
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