ASP Hosting sale!
Double disk space and transfer for FREE!
Limited time offer! Act Now!

aspdev | articles | tutorials | forums

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Update problemin MS SQL 2000

 
Post new topic   Reply to topic    ASPdev.org Forum Index -> SQL Programming (MS SQL Server, MS Access, MySQL)
View previous topic :: View next topic  
Author Message
rhaazy



Joined: 23 May 2006
Posts: 3

PostPosted: Tue May 23, 2006 11:48 am    Post subject: Update problemin MS SQL 2000 Reply with quote

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
Back to top
View user's profile Send private message
rose



Joined: 21 Feb 2006
Posts: 29

PostPosted: Sun May 28, 2006 9:48 pm    Post subject: Reply with quote

Can you clarify what error you are getting?
I know you say its an update error, but what exactly is happening?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    ASPdev.org Forum Index -> SQL Programming (MS SQL Server, MS Access, MySQL) All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group

SQL Tutorial