Update question ms sql 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update question ms sql 2000

Author

Topic

rhaazy
Starting Member

3 Posts

Posted - 2006-05-23 : 13:51:37

I have an app that performs scans and returns information
like what windows updates it has, services running, programs installed,
browsesr history, etc. Scans will be performed once a week and sent to
a server. The server will only save the most recent scan and store the
rest in a history database. I have the methods for inserting and they
work fine. However I am stuck with the task of getting this to work
after a scan from a PC is already stored. The procedure will have to
check the AssetName from tblAsset and compare it to the equivalent in
my XML input. It will get the associated ScanID and use that to make
updates in tblScan and tblScanDetail.

ALTER PROCEDURE csTest.StoredProcedure1 (@doc NTEXT)

AS
declare @iTree int
declare @assetid int
declare @scanid int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))

/* SET NOCOUNT ON */

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

INSERT INTO tblAsset (AssetName, DatelastScanned, LastModified)
SELECT *, LastModified = getdate() FROM openxml(@iTree,
'ComputerScan', 1)
WITH (
ComputerName nvarchar(30) 'computer/ComputerName',
DatelastScanned smalldatetime 'scanheader/ScanDate'
)

set @assetid = scope_identity()

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

SET @scanid = scope_identity()

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) '.'
)

INSERT INTO #dup
SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
WHERE tblScanAttribute.Name = #temp.Name

INSERT INTO tblScanDetail(Instance, ScanAttributeID, ScanID,
AttributeValue, LastModified)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID, tblScan.ScanID, #temp.scanattribute,
getdate()
FROM tblScanAttribute, #temp, tblScan
WHERE tblScanAttribute.Name = #temp.Name
ORDER BY tblScan.ScanID

drop table #temp
drop table #dup

EXEC sp_xml_removedocument @iTree

RETURN

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-24 : 02:57:50

elaborate on why your code is not working, any error messages?

by reading what you described, you may need to verify if the scan is already done

if exists or a where clause probably should do it

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources