August 29, 2011 at 11:35 am
Hello All! I've got a number of MySQL servers that I extract configuration data from and consolidate into a SQL Server 2005 database for reporting purposes. I'm trying to clean up my working code, as my current code performs the same set of queries on each server individually, and updating those queries requires me to modify each copy of them. I am moving from that to storing the query in a variable and defining my MySQL servers in a table in the database, and creating a cursor on for table and iterating through each server to perform the necessary queries.
My problem is that my old method seems to be returning more up-to-date information than my new code when run at the same time, and I cannot figure out why. Is the linked server caching some queries but not others? Is this the effect of using openquery inside an exec command?
Below is some example code of what I'm trying to do:
DECLARE @Config_Query nvarchar(500)
SET @Config_Query =
'SELECT Value_a, Value_b, Value_c FROM devices;'
DECLARE @EXEC_String nvarchar(2000)
--Server_Id tinyint NOT NULL
--Linked_Server_Name varchar(20) NULL
DECLARE OPENQUERY_CURSOR INSENSITIVE CURSOR FOR
SELECT Server_Id, Linked_Server_Name
FROM dbo.MySQL_Severs
WHERE Linked_Server_Name IS NOT NULL FOR READ ONLY
--Create temp table to consolidate data
IF OBJECT_ID('tempdb..#MySQL_Config_LatestData_dev') IS NOT NULL
DROP TABLE #MySQL_Config_LatestData_dev;
CREATE TABLE #MySQL_Config_LatestData_dev (
Server_Id tinyint,
Value_a int,
Value_b bit,
Value_c varchar(15),
Value_d bit
PRIMARY KEY (Server_Id)
)
DECLARE @Server_Id tinyint, @Linked_Server_Name varchar(20)
OPEN OPENQUERY_CURSOR
FETCH NEXT FROM OPENQUERY_CURSOR INTO @Server_Id, @Linked_Server_Name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @EXEC_String = 'INSERT INTO #MySQL_Config_LatestData_dev SELECT ' + CAST(@Server_Id AS varchar(3)) + ' ,*,0 FROM OPENQUERY(' + @Linked_Server_Name + ', ''' + @Config_Query + ''')';
END;
CLOSE OPENQUERY_CURSOR;
DEALLOCATE OPENQUERY_CURSOR;
--Below is my old Method
IF OBJECT_ID('tempdb..#MySQL_Config_LatestData') IS NOT NULL
DROP TABLE #MySQL_Config_LatestData;
CREATE TABLE #MySQL_Config_LatestData (
Server_Id tinyint,
Value_a int,
Value_b bit,
Value_c varchar(15),
Value_d bit
PRIMARY KEY (Server_Id)
)
--Insert statements are repeated for all servers, for all queries.
INSERT INTO #MySQL_Config_LatestData SELECT 1,*,0 FROM OPENQUERY(Linked_Server_A,'SELECT Value_a, Value_b, Value_c FROM devices;')
INSERT INTO #MySQL_Config_LatestData SELECT 2,*,0 FROM OPENQUERY(Linked_Server_B,'SELECT Value_a, Value_b, Value_c FROM devices;')
INSERT INTO #MySQL_Config_LatestData SELECT 3,*,0 FROM OPENQUERY(Linked_Server_C,'SELECT Value_a, Value_b, Value_c FROM devices;')
--The data in #MySQL_Config_LatestData and #MySQL_Config_LatestData_dev should be identical at this point.
--However, the results of the query below contain rows, and comparing the two results shows that there are differences.
SELECT * FROM #MySQL_Config_LatestData EXCEPT SELECT * FROM #MySQL_Config_LatestData_dev
--#MySQL_Config_LatestData consistantly shows the most recent data, while #MySQL_Config_LatestData_dev's data is a bit older.
Any help would be much appreciated! I'd be happy to provide some additional information.
**Edited Example code to correct error in temp table defs.**
I've done some more digging, and I've found that the WHILE loop and the EXEC statements are not the issue.
To test this, I copied and pasted the while loop from my code, deleted references to the cursor, and set the @Server_Id and @Linked_Server_Name values VIA a hard-coded CASE statement inside the while loop. Example below.
SET @Server_Id = CASE WHEN @Counter = 0 THEN 1
WHEN @Counter = 1 THEN 2
WHEN @Counter = 3
END
**Edit **
I've located my issue. My server definition table had linked server names that were pointing to backup configuration servers, instead of the live ones. My code works fine now. :blink:
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply