|
|
Posted by rhaazy on 10/01/32 11:52
Using MS SQL 2000
I have a stored procedure that processes an XML file generated from an
Audit program. The XML looks somewhat like this:
<ComputerScan>
<scanheader>
<ScanDate>somedate&time</ScanDate>
<UniqueID>MAC address</UniqueID>
</scanheader>
<computer>
<ComputerName>RyanPC</ComputerName>
</computer>
<scans>
<scan ID = "1.0" Section= "Basic Overview">
<scanattributes>
<scanattribute ID="1.0.0.0" ParentID=""
Name="NetworkDomian">MSHOMe</scanattribute>
scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....
This is the Update portion of the sproc....
CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)
AS
DECLARE @iTree int
DECLARE @assetid int
DECLARE @scanid int
DECLARE @MAC nvarchar(50)
CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
/* SET NOCOUNT ON */
EXEC sp_xml_preparedocument @iTree OUTPUT, @doc
INSERT INTO #temp
SELECT * FROM openxml(@iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@ID',
ParentID nvarchar(50) './@ParentID',
Name nvarchar(50) './@Name',
scanattribute nvarchar(50) '.'
)
SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',
1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))
IF EXISTS(select MAC from tblAsset where MAC = @MAC)
BEGIN
UPDATE tblAsset set DatelastScanned = (select ScanDate from
openxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime
'scanheader/ScanDate')),
LastModified = getdate() where MAC =
@MAC
UPDATE tblScan set ScanDate = (select ScanDate from
openxml(@iTree,
'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),
LastModified = getdate() where MAC =
@MAC
UPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =
#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =
getdate()
FROM tblScanDetail INNER JOIN #temp
ON (tblScanDetail.GUIID = #temp.ID AND
tblScanDetail.GUIParentID =
#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)
WHERE MAC = @MAC
!!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERT
STATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUS
SCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXIST
YET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,
ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, b.ID, b.ParentID,
tblScanAttribute.ScanAttributeID,
@scanid, b.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScanDetail LEFT OUTER JOIN #temp a ON
(tblScanDetail.GUIID =
a.ID AND tblScanDetail.GUIParentID = a.ParentID AND
tblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN
#temp b ON tblScanAttribute.Name = b.Name
WHERE (tblScanDetail.GUIID IS NULL AND
tblScanDetail.GUIParentID IS
NULL AND tblScanDetail.AttributeValue IS NULL)
END
ELSE
BEGIN
Here are a few table defintions to maybe help out a little too...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
-----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblScan]
GO
CREATE TABLE [dbo].[tblScan] (
[ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetID] [int] NULL ,
[ScanDate] [smalldatetime] NULL ,
[AssetName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO
----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblScanDetail]
GO
CREATE TABLE [dbo].[tblScanDetail] (
[ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL ,
[ScanID] [int] NULL ,
[ScanAttributeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
,
[GUIParentID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[AttributeValue] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO
------------------------------------------------------------
My problem is that Insert statement that follows the update into
tblScanDetail, for some reason it just seems to insert everything twice
if the update is performed. Not sure what I did wrong but any help
would be appreciated. Thanks in advance.
Navigation:
[Reply to this message]
|