显示标签为“scenario”的博文。显示所有博文
显示标签为“scenario”的博文。显示所有博文

2012年2月16日星期四

Batch Updation

I have the scenario of selecting values from more than one tables, also while updating the values to DB from Datagrid some changes may or may not be made in grod

Then check the values alreay existing table to the grid values now i had written in OPENXML inside a Stored procedure,the SP have if statements, INSERT,UPDATE for one table or more than one table

which one is best openxml or the adapter.update is best,

Also how can achieve more than one t-sql operation in adapter.update

the SP is below

/*

To UPDATE And INSERT the Asset Values and Asset Status, and to INSERT Asset Change History

For the Assets which are Value and Status Changed

*/

Create procedure AssetValueStatusAdjustment_InsertUpdate

@.XMLString text
,@.LastModifiedUserID varchar(10)
,@.LastModifiedDate varchar(10)
,@.CultureID varchar(5)
As
BEGIN
SET NOCOUNT ON
Declare @.InventoryHistoryID bigint
Declare @.handle int
Declare @.XMLStatusName nvarchar(50)
Declare @.TableStatusName nvarchar(50)
Declare @.Error int

EXEC sp_xml_preparedocument @.handle OUTPUT, @.XMLString

-- This is For INSERT the Details of the Value Adjustment Assets into the Table
-- Inventory_ValueAdjustment

INSERT INTO Inventory_ValueAdjustment
(InventoryID
,PostDate
,PreAdjustmentOriginalCost
,PreAdjustmentNetValue
,AdjustmentAmount
,Comment
,GLTemplateID
,LastModifiedUserID
,LastModifiedDate)

SELECT
XMLAdjustmentColumns.InventoryID
,XMLAdjustmentColumns.PostDate
,XMLAdjustmentColumns.PreAdjustmentOriginalCost
,XMLAdjustmentColumns.PreAdjustmentNetValue
,XMLAdjustmentColumns.AdjustmentAmount
,XMLAdjustmentColumns.Comment
,XMLAdjustmentColumns.GLTemplateID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate


From OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_ValueAdjustment Table

With(InventoryID bigint,
PostDate datetime,
PreAdjustmentOriginalCost decimal(18,2),
PreAdjustmentNetValue decimal(18,2),
AdjustmentAmount decimal(18,2),
Comment nvarchar(200),
GLTemplateID int)XMLAdjustmentColumns

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch


-- UPDATE the ValueAdjustment to Inventory_Profile

UPDATE
Inventory_Profile
SET
NetValue=XMLAdjustmentColumns.NetValue
,LastModifiedUserID= @.LastModifiedUserID
,LastModifiedDate=@.LastModifiedDate


From OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_ValueAdjustment Table

With(InventoryID bigint,
NetValue decimal(19,2))XMLAdjustmentColumns

WHERE
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID


SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch


--INSERT a ValueAdjustment Change History Record

INSERT INTO Inventory_History
(InventoryHistoryID
,InventoryID
,AsOfDate--
,ANum--
,LNum--
,HisoryGenerationReasonID
,Alias
,InventoryStatusID
,InventorySubStatusID
,InventorySubTypeID
,InventoryTypeID
,AcquisitionDate
,OriginalCost
,NetValue
,UnitNumber
,YearOfManufacture
,Manufacturer
,Make
,Model
,SerialNumber
,FundingSourceID--
,OwnerSinceDate--
,UsageConditionID
,Description1
,Description2
,InventoryLocationID
,LocationEffectiveFromDate
,IsFlaggedForSale
,IsDepreciationAllowed--
,PortfolioID
,RentalPurchaseOrderNumber
,AquisitionPurchaseOrderNumber
,CostCenterID
,OwnerTypeID
,IsReplacementUnitOnOrder
,CustomerBookValue
,GLTemplateID
,FlaggedForCofAAlias
,FlaggedForLeaseAlias
,CustomerID
,PreviousLeaseID
,SortOrder
,IsSaleLeaseBack
,ParentInventoryID
,Quantity
,OldSerialNumber
,InventoryGroupID
,InvoiceGroupID
,FactorCategoryID
,IsEligibleForPropertyTaxManagement
,InterimRentReceivableUpfrontTaxModeID
,LeaseRentalReceivableUpfrontTaxModeID
,OverTermReceivableUpfrontTaxModeID
,LastModifiedUserID
,LastModifiedDate)

SELECT
XMLAdjustmentColumns.InventoryHistoryID as InventoryHistoryID
,Inventory_Profile.InventoryID
,XMLAdjustmentColumns.AsOfDate as AsOfDate
,XMLAdjustmentColumns.ANum as ANum
,XMLAdjustmentColumns.LNum as LNum
,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
,Inventory_Profile.Alias
,Inventory_Profile.InventoryStatusID
,Inventory_Profile.InventorySubStatusID
,Inventory_Profile.InventorySubTypeID
,Inventory_Profile.InventoryTypeID
,Inventory_Profile.AcquisitionDate
,Inventory_Profile.OriginalCost
,Inventory_Profile.NetValue
,Inventory_Profile.UnitNumber
,Inventory_Profile.YearOfManufacture
,Inventory_Profile.Manufacturer
,Inventory_Profile.Make
,Inventory_Profile.Model
,Inventory_Profile.SerialNumber
,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
,Inventory_Profile.UsageConditionID
,Inventory_Profile.Description1
,Inventory_Profile.Description2
,Inventory_Profile.InventoryLocationID
,Inventory_Profile.LocationEffectiveFromDate
,Inventory_Profile.IsFlaggedForSale
,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
,Inventory_Profile.PortfolioID
,Inventory_Profile.RentalPurchaseOrderNumber
,Inventory_Profile.AquisitionPurchaseOrderNumber
,Inventory_Profile.CostCenterID
,Inventory_Profile.OwnerTypeID
,Inventory_Profile.IsReplacementUnitOnOrder
,Inventory_Profile.CustomerBookValue
,Inventory_Profile.GLTemplateID
,Inventory_Profile.FlaggedForCofAAlias
,Inventory_Profile.FlaggedForLeaseAlias
,Inventory_Profile.CustomerID
,Inventory_Profile.PreviousLeaseID
,Inventory_Profile.SortOrder
,Inventory_Profile.IsSaleLeaseBack
,Inventory_Profile.ParentInventoryID
,Inventory_Profile.Quantity
,Inventory_Profile.OldSerialNumber
,Inventory_Profile.InventoryGroupID
,Inventory_Profile.InvoiceGroupID
,Inventory_Profile.FactorCategoryID
,Inventory_Profile.IsEligibleForPropertyTaxManagement
,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

FROM
Inventory_Profile INNER JOIN OPENXML(@.handle,'Root/Asset',1)

--This Field From Inventory_ValueAdjustment Table

WITH(InventoryHistoryID bigint,
InventoryID bigint,
--This Fields From Inventory_History Table
AsOfDate datetime,
ANum nvarchar(50),
LNum int,
HisoryGenerationReasonID tinyint,
FundingSourceID int,
OwnerSinceDate datetime,
IsDepreciationAllowed tinyint) XMLAdjustmentColumns

ON
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID


SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch


-- Check Wheather the Status has been Changed or not
SELECT
@.XMLStatusName = XMLAdjustmentColumns.InventoryStatusName
,@.TableStatusName = Inventory_Status_CnfgLocale.InventoryStatusName

FROM
Inventory_Status_Cnfg

INNER JOIN
Inventory_Status_CnfgLocale ON Inventory_Status_Cnfg.InventoryStatusID=Inventory_Status_CnfgLocale.InventoryStatusID

INNER JOIN
OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_Profile Table

WITH(InventoryStatusName nvarchar(100)
,InventoryStatusID tinyint
,CultureID varchar(5))XMLAdjustmentColumns

ON
Inventory_Status_CnfgLocale.InventoryStatusID=XMLAdjustmentColumns.InventoryStatusID

AND
Inventory_Status_CnfgLocale.CultureID=@.CultureID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch

-- If New Status is not matched with the existing Status Name then

IF(@.XMLStatusName <> @.TableStatusName)

BEGIN --To Insert Status Change Record

--UPDATE the New Status Changed Value to Inventory_Profile

UPDATE
Inventory_Profile
SET
InventoryStatusID = XMLAdjustmentColumns.InventoryStatusId
,LastModifiedUserID= @.LastModifiedUserID
,LastModifiedDate=@.LastModifiedDate

FROM
OPENXML(@.handle,'Root/Asset',1)

--This Fields From Inventory_Profile Tavle

WITH(InventoryStatusID tinyint
,InventoryID bigint)XMLAdjustmentColumns

WHERE
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID


SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch


--Select the Maximum available InventoryHistoryID to avoid primary key violation
SELECT
@.InventoryHistoryID=COUNT(InventoryHistoryID)
FROM
Inventory_History

SET @.InventoryHistoryID = @.InventoryHistoryID+1

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch


-- INSERT a status change history record

INSERT INTO Inventory_History
(InventoryHistoryID
,InventoryID
,AsOfDate--
,ANum--
,LNum--
,HisoryGenerationReasonID
,Alias
,InventoryStatusID
,InventorySubStatusID
,InventorySubTypeID
,InventoryTypeID
,AcquisitionDate
,OriginalCost
,NetValue
,UnitNumber
,YearOfManufacture
,Manufacturer
,Make
,Model
,SerialNumber
,FundingSourceID--
,OwnerSinceDate--
,UsageConditionID
,Description1
,Description2
,InventoryLocationID
,LocationEffectiveFromDate
,IsFlaggedForSale
,IsDepreciationAllowed--
,PortfolioID
,RentalPurchaseOrderNumber
,AquisitionPurchaseOrderNumber
,CostCenterID
,OwnerTypeID
,IsReplacementUnitOnOrder
,CustomerBookValue
,GLTemplateID
,FlaggedForCofAAlias
,FlaggedForLeaseAlias
,CustomerID
,PreviousLeaseID
,SortOrder
,IsSaleLeaseBack
,ParentInventoryID
,Quantity
,OldSerialNumber
,InventoryGroupID
,InvoiceGroupID
,FactorCategoryID
,IsEligibleForPropertyTaxManagement
,InterimRentReceivableUpfrontTaxModeID
,LeaseRentalReceivableUpfrontTaxModeID
,OverTermReceivableUpfrontTaxModeID
,LastModifiedUserID
,LastModifiedDate)

SELECT
@.InventoryHistoryID as InventoryHistoryID
,Inventory_Profile.InventoryID
,XMLAdjustmentColumns.AsOfDate as AsOfDate
,XMLAdjustmentColumns.ANum as ANum
,XMLAdjustmentColumns.LNum as LNum
,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
,Inventory_Profile.Alias
,Inventory_Profile.InventoryStatusID
,Inventory_Profile.InventorySubStatusID
,Inventory_Profile.InventorySubTypeID
,Inventory_Profile.InventoryTypeID
,Inventory_Profile.AcquisitionDate
,Inventory_Profile.OriginalCost
,Inventory_Profile.NetValue
,Inventory_Profile.UnitNumber
,Inventory_Profile.YearOfManufacture
,Inventory_Profile.Manufacturer
,Inventory_Profile.Make
,Inventory_Profile.Model
,Inventory_Profile.SerialNumber
,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
,Inventory_Profile.UsageConditionID
,Inventory_Profile.Description1
,Inventory_Profile.Description2
,Inventory_Profile.InventoryLocationID
,Inventory_Profile.LocationEffectiveFromDate
,Inventory_Profile.IsFlaggedForSale
,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
,Inventory_Profile.PortfolioID
,Inventory_Profile.RentalPurchaseOrderNumber
,Inventory_Profile.AquisitionPurchaseOrderNumber
,Inventory_Profile.CostCenterID
,Inventory_Profile.OwnerTypeID
,Inventory_Profile.IsReplacementUnitOnOrder
,Inventory_Profile.CustomerBookValue
,Inventory_Profile.GLTemplateID
,Inventory_Profile.FlaggedForCofAAlias
,Inventory_Profile.FlaggedForLeaseAlias
,Inventory_Profile.CustomerID
,Inventory_Profile.PreviousLeaseID
,Inventory_Profile.SortOrder
,Inventory_Profile.IsSaleLeaseBack
,Inventory_Profile.ParentInventoryID
,Inventory_Profile.Quantity
,Inventory_Profile.OldSerialNumber
,Inventory_Profile.InventoryGroupID
,Inventory_Profile.InvoiceGroupID
,Inventory_Profile.FactorCategoryID
,Inventory_Profile.IsEligibleForPropertyTaxManagement
,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
,@.LastModifiedUserID as LastModifiedUserID
,@.LastModifiedDate as LastModifiedDate

FROM
Inventory_Profile INNER JOIN OPENXML(@.handle,'Root/Asset',1)

--This Field From Inventory_ValueAdjustment Table

WITH(InventoryHistoryID bigint,
InventoryID bigint,
--This Fields From Inventory_History Table

AsOfDate datetime,
ANum nvarchar(50),
LNum int,
HisoryGenerationReasonID tinyint,
FundingSourceID int,
OwnerSinceDate datetime,
IsDepreciationAllowed tinyint) XMLAdjustmentColumns

ON
Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

SET @.Error=@.@.Error
IF(@.Error<>0)
GOTO ErrorCatch


END

GOTO NextStatement --dont want to Update the Status GOTO Next expression


NextStatement:


ErrorCatch:
RETURN @.Error


EXEC sp_xml_removedocument @.handle
END
Go

/*

This Stored Procedure Will Insert two tables as Inventory_Profile, Inventory_History and

Update TaxDepreciation_Profile by OPENXML BULK UPDATE CONCEPT

-
The @.xmlString Parameter is used to get the value as XML Nodes From the Non Transaction

Class

-

*/

guideme please

SqlDataAdapter has an UpdateBatchSize property that will batch updates together before sending them to the server. Value 0 should send as much across as possible. In addition you can use a Transaction to ensure that your updates happen in a consistent manner.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatebatchsize.aspx

In this way you could use the dataset and SqlDataAdapter to acheive what you are doing with your stored procedure. This will probably reduce the amount of code and get your business logic into c#.

Batch process for sending mail

Hello everyone,

I want to write a batch process that will run everyday and send emails.

The scenario is as follows:

There is a websummit held every year and people can register for it. However the request is subjected to be approved/rejected by the admin belonging to that area. Whenver a person enrolls, the RequestStatus is 0. The admin has to change the RequestStatus to either 1(accepted) or -1(rejected).

I want to write a batch process that will send mails to all admins belonging to their particular areas, everyday at 8.00 A.M. The email will be sent whenever the request has not been changed(ie. RequestStatus=0) and their is a lapse of more than 7 days for their area or the request count for their area has exceeded 5.

Table structure:

Admin

AdminID

Email

AdminArea

AdminID

AreaCode

Area

AreaCode

AreaName

WebSummit

SummitId

RequestorName

DateOfRequest

RequestStatus

AreaCode

Regards,

Vidya.

Hi,

Kindly advise.

Regards,

Vidya

|||

You'll probably want to use the master.dbo.xp_sendmail procedure, using the @.query parameter.

Roughly, create a SQL Server Agent job to run at 8:00AM

and run code that creates a table of recipients, then execute xp_sendmail for each recipient with the query to provide the information in the email you want them to see.

|||

Hi,

Thanks for reading. Some code will be useful.

Regards,

Vidya

|||

Here's the basic template for the script to be executed.

You'll need to adjust the queries to be exactly what you need.

I just did a simple query to get the admin who had 5 or more requests present.

Code Snippet

createtable #Admin (AdminID int, Email varchar(50))

insertinto #Admin values(1,'admin1@.mycompany.com')

insertinto #Admin values(2,'admin2@.mycompany.com')

insertinto #Admin values(3,'admin3@.mycompany.com')

insertinto #Admin values(4,'admin4@.mycompany.com')

createtable #AdminArea (AdminID int, AreaCode int)

insertinto #AdminArea values(1, 100)

insertinto #AdminArea values(2, 200)

insertinto #AdminArea values(3, 300)

insertinto #AdminArea values(4, 400)

createtable #Area (AreaCode int, AreaName varchar(50))

insertinto #Area values(100,'Area 100')

insertinto #Area values(200,'Area 200')

insertinto #Area values(300,'Area 300')

insertinto #Area values(400,'Area 400')

createtable #WebSummit ( SummitId int, RequestorName varchar(50),

DateOfRequest datetime, RequestStatus int, AreaCode int)

insertinto #WebSummit values( 1,'George Jetson',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Jane Jetson',getdate(), 1, 200 )

insertinto #WebSummit values( 1,'Fred Flinstone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Wilma Flintsone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Barney Rubble',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Betty Rubble',getdate(), 1, 100 )

droptable #to

select a.AdminID, a.Email

into #to

from #Admin a

where a.AdminID in

(

select a.AdminID

from #Admin a

innerjoin #AdminArea aa

on a.AdminID = aa.AdminID

innerjoin #Area b

on aa.AreaCode = b.AreaCode

innerjoin #WebSummit w

on b.AreaCode = w.AreaCode

groupby a.AdminID

havingcount(*)>4

)

declare elist cursorfor

select adminid, email from #to

declare @.id int, @.email varchar(50), @.cmd nvarchar(4000)

open elist

if@.@.cursor_rows> 0

begin

fetch next from elist into @.id, @.email

while@.@.fetch_status=0

begin

set @.cmd = N'put your query here WHERE AdminID = '+cast(@.id asvarchar(10))

exec master.dbo.xp_sendmail @.recipient=@.email, @.query=@.cmd,

@.subject='Summit Follow-up'

fetch next from elist into @.id, @.email

end

end

close elist

deallocate elist

|||

Thanks Dale.

That was very helpful.

Regards,

Vidya.

2012年2月13日星期一

Batch or command to run reports

I have a question regarding How to run reports through a batch file or a
command line command.
Scenario:
We have a structure of reports based on State. User wants these reports
based on Country (Which includeds all states). It is
not possible to render these reports by Country. We are allowing user
through UI to pick one State at a time and get report
for that state.
Question: Is there any way I can run these reports through Batch file or
Command line, so that reports will get generated in
.PDF files through batch for the Country and get saved in local folder?
Is there any utility or third party tool available to do this?
Thanks,
SamThe same UI you can use to generate reports. Btw have you used asp.net for
your UI. For scripts just check the samples which has a good eg.
Amarnath
"Sam" wrote:
> I have a question regarding How to run reports through a batch file or a
> command line command.
> Scenario:
> We have a structure of reports based on State. User wants these reports
> based on Country (Which includeds all states). It is
> not possible to render these reports by Country. We are allowing user
> through UI to pick one State at a time and get report
> for that state.
> Question: Is there any way I can run these reports through Batch file or
> Command line, so that reports will get generated in
> .PDF files through batch for the Country and get saved in local folder?
> Is there any utility or third party tool available to do this?
> Thanks,
> Sam