Posted by rhaazy on 06/30/06 12:59

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

------------------------------------

ALTER 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

/*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
INSERT INTO tblAsset (AssetName, MAC, DatelastScanned, DateCreated,
LastModified)
SELECT *, DateCreated = getdate(), LastModified = getdate() FROM
openxml(@iTree, 'ComputerScan', 1)
WITH (
ComputerName nvarchar(30) 'computer/ComputerName',
MAC nvarchar(30) 'scanheader/UniqueID',
DatelastScanned smalldatetime 'scanheader/ScanDate'
)

SET @assetid = scope_identity()

INSERT INTO tblScan ( AssetID, AssetName, ScanDate, MAC, DateCreated,
LastModified)
SELECT @assetid, *, LastModified = getdate(), DateCreated =
getdate() FROM openxml(@iTree, 'ComputerScan', 1)
WITH (
ComputerName nvarchar(30) 'computer/ComputerName',
ScanDate smalldatetime 'scanheader/ScanDate',
MAC nvarchar(30) 'scanheader/UniqueID'
)

SET @scanid = scope_identity()

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, @scanid,
#temp.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp
ON tblScanAttribute.Name = #temp.Name
END


DROP TABLE #temp

EXEC sp_xml_removedocument @iTree

RETURN
---------------------------------------
that is the entire stored proc
-----------------------------------------
The sproc gets the Unique ID for each PC (the MAC address) and compares
that with the MAC addresses in tblAsset. If the MAC exists Update, if
not Insert... AssetID is the PK and is an Identity column. tblScan
has ScanID as PK and Identity column.
tblScanAttributes PK is ScanAttributeID, and tblScanDetail has an
identity column ScanDetailID as the PK. Here are sampels of all the
tables...
-------------------------------------
tblAsset
AssetID AssetName MAC
1 RyanPC xx:xx:xx:xx:xx:xx
-------------------------------------
tblScan
ScanID AssetID MAC LastScanned
1 1 xx:xx:xx:xx:xx:xx dd/mm/yy
------------------------------------
tblScanAttribute
ScanAttributeID ScanSection Name
1 Basic OverView Asset Tag
2 Basic OverView BIOS Vers.
3 Basic OverView ComputerName
4 Basic OverView Manufacturer
.....
18 Drives Bytes Per Cluster
18 Drives Drive Type
18 Drives File System Type
......
31 Error Log Log FIle Name
31 Error Log Message
etc etc.
--------------------------------------
tblScanDetail
ID ScanID ScanAttributeID MAC GUID GUIParentID Value
1 1 3 xx:xx 1.0.0 RyanPC
2 1 7 xx:xx 1.0.1 MSHOME
3 1 4 xx:xx 1.0.2 Server
.......
18 1 23 xx:xx 2.0.0 A
19 1 19 xx:xx 2.0.0.0 2.0.0 RemovableDisk
20 1 23 xx:xx 2.0.1 C
21 1 19 xx:xx 2.0.1.0 2.0.1 LocalDisk
22 1 25 xx:xx 2.0.1.1 2.0.1 93%
etc etc.....

My problem as stated earlier is that after each assets FIRST scan,
there information will already be there. When any Scan after the first
is received by the server, it will call the UPDATE portion of my sproc.
After updating tblScanDetail, there may be information left from the
XML that wasn't updated due obviously to the nature of the source. So
the Insert has to take place. However I can't seem to get the logic
of Inserting what wasn't just updated....

[Back to original message]