June 19, 2009 at 10:42 am
The following script creates a cursor containing the names of servers. The script continues by running UPDATE statements against two tables; SSISCustomConfigs, and SSISGlobalConfigs. In the update statements, any string in the ConfiguredValue column that contains a server name within the string is updated to whatever the value of @@ServerName is.
One of the values in the ServerIdentity table is 'CAUAT1'. When the script is run against this server, this server name has an addtional 1 appended to the string. So on the first run, the server name portion of the connection string in the SSISCustomConfigs records becomes CAUAT11. On the second run, it becomes CAUAT111.
Is there something about the fact that there is a number on the end of this server name that creates this problem? Does the fact that @@SERVERNAME returns nvarchar create a problem? The script is below:
**********************************************
DECLARE ServerNamesCursor CURSOR
READ_ONLY
FOR SELECT ServerName FROM dbo.ServerIdentity
DECLARE @ServerNameString varchar(30)
OPEN ServerNamesCursor
FETCH NEXT FROM ServerNamesCursor INTO @ServerNameString
WHILE (@@fetch_status <> -1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE SSISCustomConfigs
SET ConfiguredValue =(REPLACE(SSISCustomConfigs.ConfiguredValue, IQ.ServerName, @@ServerName))
FROM
(
SELECT
CASE
WHEN CHARINDEX(@ServerNameString,ConfiguredValue) > 0 THEN
SUBSTRING(ConfiguredValue, CHARINDEX(@ServerNameString, ConfiguredValue),LEN(@ServerNameString))
END AS ServerName
FROM
dbo.SSISCustomConfigs
) AS IQ
WHERE
IQ.ServerName IS NOT NULL
AND
CHARINDEX(IQ.ServerName, SSISCustomConfigs.ConfiguredValue)> 0
--*******************************
UPDATE SSISGlobalConfigs
SET ConfiguredValue =(REPLACE(SSISGlobalConfigs.ConfiguredValue, IQ.ServerName, @@ServerName))
FROM
(
SELECT
CASE
WHEN CHARINDEX(@ServerNameString,ConfiguredValue) > 0 THEN
SUBSTRING(ConfiguredValue, CHARINDEX(@ServerNameString, ConfiguredValue),LEN(@ServerNameString))
END AS ServerName
FROM
dbo.SSISGlobalConfigs
) AS IQ
WHERE
IQ.ServerName IS NOT NULL
AND
CHARINDEX(IQ.ServerName, SSISGlobalConfigs.ConfiguredValue)> 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrNum,
ERROR_SEVERITY() AS ErrSev,
ERROR_STATE() as ErrState,
ERROR_PROCEDURE() as ErrProc,
ERROR_LINE() + 28 as ErrLine,
ERROR_MESSAGE() as ErrMsg
END CATCH
FETCH NEXT FROM ServerNamesCursor INTO @ServerNameString
END
CLOSE ServerNamesCursor
DEALLOCATE ServerNamesCursor
*********************************************************
Thank you for your help!
cdun2
June 19, 2009 at 12:48 pm
I should add that the ServerIdentity table contains a record for a server named CAUAT, and another for CAUAT1.
I think what is happening is that, in the CASE statement, CAUAT is found in ConfiguredValue, but the real value is CAUAT1. SUBSTRING then gets CAUAT1 without the last character (1).
Then when the REPLACE happens on CAUAT1, CAUAT gets replaced with CAUAT1, and the 'extra' '1' remains: CAUAT11.
How can I change the code so that it picks up on this kind of similarity in the server names?
June 26, 2009 at 9:44 am
Going to need some things from you. First, if you haven't already, please read the first article I reference below in my signature block about asking for assistance.
We need the DDL for the tables involved in you SQL Code. We also need sample data in a readily consumable format that can be cut/paste/run in SSMS to load your tables. Also, the expected results when the query is run.
I'm going to make a guess here and say that your logic is wrong, and that you don't even want to use REPLACE in your code. But without the information requested above, nothing more that I can do to help.
June 26, 2009 at 1:29 pm
I've already fixed this problem. Thanks.
June 26, 2009 at 1:57 pm
cdun2 (6/26/2009)
I've already fixed this problem. Thanks.
Glad to hear that. Wold you mind showing us how you resolved your problem?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply