February 20, 2012 at 8:02 am
Hi fellow SQL Server gurus,
*Edit 1: Corrected update statement WHERE clause to match the SELECT. Still same result, however.
We have the following table structure:
DBA_P_Instancenames (contains SQL Server instance names)
DBA_P_PhysicalServers (lists physical and VM's that we host -- table was around before VM's were introduced to the environment)
DBA_P_VirtualServers (contains SQL Server Network Names for clustered instances).
DBA_P_ClusterNodes (contains windows cluster names and computer names from the DBA_P_PhysicalServers table)
For better or worse, the instance names table has a computername column that *should* match up to the computername column on the DBA_P_VirtualServers table, and when the instance is found to be running on a different node, we log a ticket/email citing that the instance of SQL is not running on the proper node.
We don't have a front-end managing this data, and sometimes the computername column gets updated on the dba_p_instancenames table, but not on the DBA_P_VirtualServers tables. This is where I am struggling to come up with a proper-set based update statement to update the values accordingly.
For this, we can assume that the computer names exist in the cluster nodes table and the physical servers table.
Instance Names table:
InstanceVirtualName2\NamedInstance2 | ComputerName1 (properly updated after a failover to ComputerName1)
Virtual Servers table:
InstanceVirtualName2 | ComputerName2 (was not updated after a failover to ComputerName1, only contains the network name and the virtual IP on this table, not the instance name)
The code below is what I have come up with so far, but cannot get it functioning properly.
I know I can change it to a cursor-based RBAR (as Jeff Moden puts it) operation, but the mental block on getting this functioning is frustrating me.
--this code returns one row for testing, although I have upwards of
--select a list of the instances that need to be updated
select network_name = (case
when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename
when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)
end),
dpp.computerName,
dpv.computername,
dpi.instancename
from DBA_P_instancenames dpi
inner join DBA_P_PhysicalServers dpp on dpi.computerName = dpp.computername --these should match
left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computername --left outer so dpi will be pulled even if it doesn't find a match on dpv
WHERE dpp.computername IN (SELECT computer_name from dbo.DBA_P_ClusterNodes)
and dpv.computername is null -- means names do not match between dpi and dpv
--single out a specific instance
and (case
when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename
when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)
end) = 'InstanceVirtualName2'
--this is returning 0 rows when updating, when there is one record that should be updated.
UPDATE [dbo].[DBA_P_VirtualServers]
SET [computerName] = dpp.computerName
from DBA_P_instancenames dpi
inner join DBA_P_PhysicalServers dpp on dpi.computerName = dpp.computername --these should match
left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computername --left outer so dpi will be pulled even if it doesn't find a match on dpv
WHERE dpp.computername IN (SELECT computer_name from dbo.DBA_P_ClusterNodes) -- computername from physical servers is part of a cluster
and dpv.computername is null -- means names do not match between dpi and dpv
and (case
when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename
when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)
end) = 'InstanceVirtualName2'
Thank you very much in advance for helping out.
Cheers,
Steve
February 20, 2012 at 8:34 am
Are you sure this is what you want in your update?
SELECT SUBSTRING('SERVERNAME\NAMEDINSTANCE', 1, CHARINDEX('\', 'SERVERNAME\NAMEDINSTANCE', 1) - 1)
Because this gives the server name in the case of a named instance (assuming your data is SERVERNAME\NAMEDINSTANCE), but the instance name in the other case. Is this what you want?
Jared
CE - Microsoft
February 20, 2012 at 8:38 am
Look at the difference between this:
and (case
when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename
when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)
end) = 'InstanceVirtualName2'
and this:
and dpv.machineName = (case
when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename
when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)
end)
and dpv.machineName = 'InstanceVirtualName2'
One has 1 condition and the other is 2.
Jared
CE - Microsoft
February 20, 2012 at 9:16 am
Whoops- Let me edit that.. Sorry for the confusion.
To your other question, yes, we are looking to join just the Server Name (Network Name) for the clustered instance.
The virtual servers table holds
machineName (aka NetworkName) | computerName | VIP | clusterName
So, to join the Instance Names table to the Virtual Servers table, we would then need to join the Network Name to the Server Name portion of the instance Name.
Thanks for your response.
February 20, 2012 at 9:20 am
S. Kusen (2/20/2012)
Whoops- Let me edit that.. Sorry for the confusion..
Was that a pasting error or was that the cause of your update issue? 🙂
Jared
CE - Microsoft
February 20, 2012 at 9:37 am
SQLKnowItAll (2/20/2012)
S. Kusen (2/20/2012)
Whoops- Let me edit that.. Sorry for the confusion..Was that a pasting error or was that the cause of your update issue? 🙂
LOL- Wasn't the cause.. I wish it was :hehe:
I feel like I'm either missing a JOIN or something since it is not making the "connection" between dbo.DBA_P_VirtualServers and the dpv alias that I'm giving the table lower in the query. without the alias, the computername column becomes ambiguous.
Truly confusing myself with this one....
Steve
February 20, 2012 at 9:41 am
S. Kusen (2/20/2012)
SQLKnowItAll (2/20/2012)
S. Kusen (2/20/2012)
Whoops- Let me edit that.. Sorry for the confusion..Was that a pasting error or was that the cause of your update issue? 🙂
LOL- Wasn't the cause.. I wish it was :hehe:
I feel like I'm either missing a JOIN or something since it is not making the "connection" between dbo.DBA_P_VirtualServers and the dpv alias that I'm giving the table lower in the query. without the alias, the computername column becomes ambiguous.
Truly confusing myself with this one....
Steve
Strange... If the first statement returns 1 row, and the update is EXACTLY the same as the first in the from and where... There is not justification I can think of for the update not to work properly.
Jared
CE - Microsoft
February 20, 2012 at 12:26 pm
I think this is a logic flaw. Your update is against the virtual server table, but you're left joining against that table and specifying you want to exclude those where the name matches. So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).
So in essence you are looking to INSERT a record, not UPDATE a record.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2012 at 12:31 pm
Matt Miller (#4) (2/20/2012)
So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).
EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?
But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?
Jared
CE - Microsoft
February 20, 2012 at 12:56 pm
SQLKnowItAll (2/20/2012)
Matt Miller (#4) (2/20/2012)
So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?
But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?
Except that your join criteria is based on the name as well. So you're not finding records where the name is missing or null - you're excluding them
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2012 at 1:03 pm
Matt Miller (#4) (2/20/2012)
SQLKnowItAll (2/20/2012)
Matt Miller (#4) (2/20/2012)
So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?
But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?
Except that your join criteria is based on the name as well. So you're not finding records where the name is missing or null - you're excluding them
left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computername
WHERE dpv.computername is null
Returns all values where dpi.computername exists and dpv.computername does not. EDIT!!!:What's the issue?
No records exist to update in dpv. Hence, the suggestion on insert. You got this OP?
Jared
CE - Microsoft
February 20, 2012 at 1:17 pm
SQLKnowItAll (2/20/2012)
Matt Miller (#4) (2/20/2012)
SQLKnowItAll (2/20/2012)
Matt Miller (#4) (2/20/2012)
So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?
But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?
Except that your join criteria is based on the name as well. So you're not finding records where the name is missing or null - you're excluding them
left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computernameWHERE dpv.computername is null
Returns all values where dpi.computername exists and dpv.computername does not. EDIT!!!:
What's the issue?No records exist to update in dpv. Hence, the suggestion on insert. You got this OP?
Yes- I think I'm on the right track. Appreciate the extra sets of eyes! I knew I had to be flawed with the joins, I just couldn't see it after looking at it for hours.
I'll post back when I get it working. Thanks again!
Steve
February 20, 2012 at 2:10 pm
Thanks guys! That join was where I had issues,and I really wanted to see where the computer names were different (where NULL that meant I had nothing to join on so the counterpart record was missing, and a separate task for correcting the data). For those interested, here is the code I wound up using successfully:
UPDATE [dpv]
SET [dpv].[computername] = dpp.computerName
from [dbo].[DBA_P_instancenames] [dpi]
inner join [dbo].[DBA_P_PhysicalServers] [dpp] on [dpi].[computerName] = [dpp].[computername] --these should match
left outer join [dbo].[DBA_P_VirtualServers] [dpv] --left outer so dpi will be pulled even if it doesn't find a match on dpv
ON [dpv].[machineName] = (case
when CHARINDEX('\', [dpi].[instancename], 1) = 0 then [dpi].[instancename]
when CHARINDEX('\', [dpi].[instancename], 1) > 0 then SUBSTRING([dpi].[instancename], 1, CHARINDEX('\', [dpi].[instancename], 1) - 1)
end)
WHERE [dpp].[computername] IN (SELECT [computer_name] from [dbo].[DBA_P_ClusterNodes])
and [dpv].[computername] <> [dpp].[computername] --means names do not match between dpi and dpv
Thanks to Matt and Jarod for helping steer me along! 😎
Cheers,
Steve
February 20, 2012 at 2:13 pm
S. Kusen (2/20/2012)
Thanks guys! That join was where I had issues,and I really wanted to see where the computer names were different (where NULL that meant I had nothing to join on so the counterpart record was missing, and a separate task for correcting the data). For those interested, here is the code I wound up using successfully:
UPDATE [dpv]
SET [dpv].[computername] = dpp.computerName
from [dbo].[DBA_P_instancenames] [dpi]
inner join [dbo].[DBA_P_PhysicalServers] [dpp] on [dpi].[computerName] = [dpp].[computername] --these should match
left outer join [dbo].[DBA_P_VirtualServers] [dpv] --left outer so dpi will be pulled even if it doesn't find a match on dpv
ON [dpv].[machineName] = (case
when CHARINDEX('\', [dpi].[instancename], 1) = 0 then [dpi].[instancename]
when CHARINDEX('\', [dpi].[instancename], 1) > 0 then SUBSTRING([dpi].[instancename], 1, CHARINDEX('\', [dpi].[instancename], 1) - 1)
end)
WHERE [dpp].[computername] IN (SELECT [computer_name] from [dbo].[DBA_P_ClusterNodes])
and [dpv].[computername] <> [dpp].[computername] --means names do not match between dpi and dpv
Thanks to Matt and Jarod for helping steer me along! 😎
Cheers,
Steve
Glad to help, but it was really Matt who came to the rescue 🙂
Jared
CE - Microsoft
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply