UPDATE Trigger Not Working Properly
Oct 4, 2004
We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Code = 800a0c93
Source = ADODB.Recordset
Description = Operation is not allowed in this context.
What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.
trigger code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings
ON viewViaNetCardholder
INSTEAD OF UPDATE AS
UPDATE tblIDCross
SET
tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END,
tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END,
tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END,
tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END,
tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END,
tblIDCross.intCustomerType = ins.intCustomerType
FROM INSERTED ins
WHERE tblIDCross.intUSCardId = ins.intUSCardId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Please help...
View 1 Replies
ADVERTISEMENT
Sep 24, 2007
Hi,
I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger:
CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp] FOR INSERT, UPDATEASUPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05
I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.Please advise. Many Thanks.
View 2 Replies
View Related
Jan 17, 2007
Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics.
So i tried:
SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC
Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC?
Patrick
View 1 Replies
View Related
Apr 21, 2008
Hello friends, I have developed an ASP.NET 2.0 application. The problem is that the IIS server returns the next error:
SQL Server does not allow remote connections
Sometimes returns:
SQL Server does not exists.
My question is if there is any known bug with ASP.NET and SQL Server. My sql server have activated the Remote Connections (its working properly on other web page).
Its a problem of the number of connections??
Its a problem ofbad configuration??
Its a IIS problem??
Please bring me some help.
View 8 Replies
View Related
Dec 25, 2007
Hi,
I am exporting a table to a fixed width flat file using "Ragged Right" format. Though i specify carriage return for the last column, the text file is not taking it up.
I am doing the following for connection
cmFlatFile.properties("Format").SetValue(cmFlatFile,"RaggedRight")
cmFlatFile.properties("RowDelimiter").SetValue(cmFlatFile,vbCrLf)
and while adding the column i am setting up the width as follows
vColumns = flatFileDestination.InputCollection(0).GetVirutalInput().VirtualInputColumnCollection
For cols = 0 to vColumns.Count -1
Dim col As IDTSConnectionManagerFlatFileColumn90 = ff.Columns.Add()
if cols = vColumns.Count - 1 Then
col.ColumnDelimiter = vbCrLf
end if
cols.ColumnType = "FixedWidth"
cols.Columnwidth = 20
......
Next cols
Still the rows are exported next to each other just like when we use FixedWidth.
View 4 Replies
View Related
Oct 2, 2007
Hi,
I have several packages where I set up a SQL Server log provider. The packages are all called from jobs.
However, for some reason, only ONE of the packages is logging to the sysdtslog90 table in my specified database.
All of packages have the identical set up for the logging. Here are my settings under logging:
PROVIDERS AND LOGS:
Name: SSIS Log Provider for SQL Server (box is checked)
Configuration: ServerName.DBName (I use this same connection for all the packages)
DETAILS:
OnError is checked
CONTAINERS:
All items are checked
So what's going on here? I have successfully logged to this table using test cases in BIDS, and as I mentioned, ONE of the packages is actually logging to the table when it's called from the job.
Thanks
View 5 Replies
View Related
Oct 11, 2007
Hi all,
I use SqlHelper to connect to database. I need to set a timeout to execute some sp because I don´t want to wait for this sp if it make me wait a lot. So I set the Connection TimeOut every time I execute a sp. But this seems not working properly. I set this value to 5 and I execute a sp than runs in 15 but It waits for it.
Any idea
Best regards
www.ITCubo.net
View 1 Replies
View Related
Mar 28, 2008
Hi guys, I am importing a file using the Bulk Insert command, but the fieldterminator is not working for me.
My data:
“lname�, “fname�, “addr�, “phone�, “lang�
My command:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '","')
The quote are removed from every field except the first quote in front of lname and the trailing quote behind lang. I tried changing the command to:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '"')
But ran into field conversion problems, I also tried defining two fieldterminators got a syntax error. How can I get the desired results of removing all dbl quotes? Or do I have to write something to follow the Bulk Insert to remove the leading and trailing quotes?
View 4 Replies
View Related
Dec 3, 2005
Hi all...I have a stored proc that works fine except I can't get thepaging property to work in ASP. It seems that because I'm building theSQL withing the SP is causing the problem. If I don't build the SQL asa string paging works. In my ASP page, the pagecount property returns-1 for some reason.Here's the proc:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER Procedure LS_DisplayAllCategoriesBoolean@aSections varchar(255),@field varchar(255)Asdeclare @sql varchar(1000)SET NOCOUNT ONset @sql = 'SELECT DISTINCT ls_product_catalog.id,ls_product_catalog.ItemNumber, ls_product_catalog.Name,ls_product_catalog.[Descriptor], ls_product_catalog.Price,ls_product_catalog.Shade_Name, ls_product_catalog.Sale_Price,ls_product_catalog.Sale_DescFROM dbo.ls_brand INNER JOINdbo.ls_product_catalog ON dbo.ls_brand.nID =dbo.ls_product_catalog.CategoryID INNER JOINdbo.ls_product_category ON dbo.ls_product_catalog.id =dbo.ls_product_category.nProductIDWHERE dbo.ls_brand.bVisible = 1 anddbo.ls_product_category.nCategoryID in (Select IntValuefrom dbo.CsvToInt(''' + @aSections + ''') ) 'if @field = 'bNew'beginset @sql = @sql + ' AND bNew = 1 AND(dbo.ls_product_catalog.bGiftOnly is null ordbo.ls_product_catalog.bGiftOnly = 0)'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGift'beginset @sql = @sql + ' AND bGift = 1 or bGiftOnly =1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGiftOnly'beginset @sql = @sql + ' AND bGiftOnly= 1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'sale_price'beginset @sql = @sql + ' AND (sale_price is not null ORsale_desc is notnull)'set @sql = @sql + ' order by ls_product_catalog.Name'endexec(@sql)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ON
View 1 Replies
View Related
May 25, 2008
Can someone please help me figure out why this trigger causes a "Timeout expired" error after the first try? What I'm trying to do is create an incrementing default value for the projectid field. I have a unique index on the field and for some reason, the INSERT statement won't run inside the TRY block, even though the WHILE loop creates a unique id. It's acting like the loop isn't incrementing, but the PRINT statements prove that the loop does work, but the INSERT statement doesn't work after a succesful first try (i.e. NEWPROJ-1 is inserted correctlly). Anyways, any help would be appreciated.
Code Snippet
GO
/****** Object: Trigger [Projects].[CreateID] Script Date: 05/25/2008 14:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Projects].[CreateID]
ON [Projects].[tblProjects]
INSTEAD OF INSERT
AS
DECLARE @projectid varchar(25)
DECLARE @projectname varchar(100)
DECLARE @projectepskey int
DECLARE @maxcount int
SELECT @maxcount = 1
BEGIN
SET NOCOUNT ON;
SELECT @projectname = projectname
FROM inserted
SELECT @projectepskey = projectepskey
FROM inserted
SELECT projectid
FROM Projects.tblProjects
WHERE (projectid = 'NEWPROJ')
IF (@@RowCount = 0)
BEGIN
SELECT @projectid = 'NEWPROJ'
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
END
ELSE
BEGIN
WHILE @maxcount >= 1
BEGIN
BEGIN TRY
SELECT @projectid = 'NEWPROJ-' + CONVERT(varchar, @maxcount)
PRINT @projectid
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
BREAK
END TRY
BEGIN CATCH
SELECT @maxcount = @maxcount + 1
PRINT CONVERT(varchar, @maxcount)
CONTINUE
END CATCH;
END
END
END
View 4 Replies
View Related
Oct 21, 2015
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. Â The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. Â That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv
FROM (`DailyReport` `DailyReport`
INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
[Code] ...
The return data looks like this:
3212,2015-07-31,126.35,1781.20,0.00,Fountain,Hot Dsp Bev,0
3212,2015-07-31,149.17,1311.94,0.00,Fountain,Cold Dsp Bev,0
3212,2015-07-31,666.63,4930.02,0.00,Food Service,My Deli,0
3212,2015-08-01,88.67,0.00,0.00,Fountain,Hot Dsp Bev,0
3212,2015-08-01,109.62,0.00,0.00,Fountain,Cold Dsp Bev,0
[Code] ....
I want it to look like this:
3212,2015-08-04,595.53,1311.94,400.76,Fountain,Cold Dsp Bev,0
3212,2015-08-04,485.85,1781.20,165.66,Fountain,Hot Dsp Bev,0
3212,2015-08-04,2762.05,4930.02,1388.00,Food Service,My Deli,0
View 8 Replies
View Related
May 30, 2006
Hi,
I have an SSIS Package to get the data from an Excel Source. For that I am using WMI Event Watcher to find when the file drops into a specific folder. It is working fine when I check any folders in the local machine.
We have a separate WebServer and Database Server for our application. The application is deployed in the Web server and the SSIS package is deployed in the Database server.
Since the files are dropped through application, it is dropped in the Webserver. I have mapped that drive in my Database Server and I am trying to link that folder in the SSIS package, but the event is not firing.
Please provide me some solution for this and revert for any clarifications.
Thanks & Regards,
Prakash Srinivasan
View 13 Replies
View Related
Sep 14, 2006
Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance!
View 2 Replies
View Related
Jan 7, 2008
Hello,
I have a SqlDataSource that is not doing my inserts properly. even if the user is logged in (UserName!=""), it always inserts a null in the UserName field.
SqlDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLLConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO [tblDiaryEntries] ([DiaryEntry], [Subject], [EntryDate], [UserName]) VALUES (@DiaryEntry, @Subject, @EntryDate, @UserName)" SelectCommand="SELECT [DiaryEntry], [Subject], [EntryDate], [DiaryEntryID], [UserName] FROM [tblDiaryEntries] WHERE [UserName]=@UserName" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [Subject] = @Subject, [EntryDate] = @EntryDate WHERE [DiaryEntryID] = @DiaryEntryID"> <DeleteParameters> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserName" Type="String"/> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserName" Type="String"/> </InsertParameters> </asp:SqlDataSource>
and from code behind, i do:
protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { SqlDataSource1.SelectParameters.Add("UserName", this.User.Identity.Name); SqlDataSource1.InsertParameters.Add("UserName", this.User.Identity.Name); } }
Any ideas/suggestions? Thanks!
View 9 Replies
View Related
Oct 20, 2007
Hi,
I have an Execute Process Task that calls a batch file.
I need to pass in a date value to the batch file.
If I enter the value 20071018 in the "Arguments" section, it works fine.
However, I use the "Standard Input Variable", User::MyDate (which gets assigned in a previous Execute SQL Task), it bombs. The query I am using returns the date back in the format yyyymmdd. It gets assigned to a string variable, called MyDate.
e.g.)
SELECT TOP 1 CONVERT(VARCHAR, MyDate, 112) AS MyDate
FROM myTable
which result is mapped to User::MyDate
Is there any obvious reason why this doesn't work?
Thanks
View 3 Replies
View Related
Feb 21, 2008
Hi All
I have a parameter @User givning a dropdown list from a dataset:
SELECT FullName FROM tvf_GetListOfUsers ('LDAP://OU=Business
Users,OU=Users,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree')
ORDER BY FullName
The parameter is passed to a second dataset when the User is chosen:
SELECT GroupName FROM tvf_GetUserMembership('LDAP://cn=' + @User +
',ou=business users,ou=users,ou=production,DC=MRP,DC=NET,DC=NZ')
ORDER BY GroupName
This works fine in VS but when I deploy it only works the first time.
That is, I enter the username and run the report - it brings back the
desired results. But when I enter a different username it just redisplays the
old data from the first username I entered.
Any ideas?
Thanks
Pete
PS The funcions above called in the datasets are for querying Active
Directory but I think this is not the cause of the problem.
View 1 Replies
View Related
Oct 5, 2007
Hi,
I have a FOR EACH LOOP that loops through a bunch of folders in search of particular filename (including path). That part works fine.
Then, INSIDE the FOR EACH LOOP, I have a script task that evaluates the filename and grabs the date portion of the filename. That part works fine too.
Now, here's my dilemma. If the date portion of the file name IS GREATER THAN the the value of another date in a variable, I want it to load the file into a table using a data flow task.
So, this is what my package looks like:
[ FOR EACH LOOP (Script task --- evaluate the date using an expression (see below) ---> Data flow task) ]
My expression looks like this:
@[User::fileDate] > @[User::maxLoadDate]
You think that this would work, right??
However, after the VERY FIRST iteration of the loop, the script task turns green, then it goes right to the data flow task, which then says that the file name is "", and can't be loaded. (I'm not sure why this is occuring)
My question is, is my logic correct? Can I use an expression inside the for each loop and have it work?
View 1 Replies
View Related
Sep 28, 2007
hi,
i have a table having two column say columnA and columnB.columnA(text datatype) contains the data that is to written in a file and columnB(nvarchar datatype) contains the filepath.
but while configuring the export column tranformation, in the export column field i am getting the list of both the colums. here i am choosing columnA but in the file path name column i am not getiing the name of the column which contains the file name.
can anyone help me regarding how to configure the file path name column in export column transformation. from where we ll get the file path name.
in control flow, i have used only dataflow component and data flow tab i have used oledb source-->export column transformation-->oledb destination.
please help in configuring the export column transformation.
thanks a lot !!!!!!!!!!!!!!!!!!!!!!!
View 2 Replies
View Related
May 24, 2008
I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.
Thanks for the help.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState]
(@ShippingStateProvince VARCHAR(50),
@ShippingCountry VARCHAR(50),
@StartDate smalldatetime,
@EndDate smalldatetime)
AS
SELECT OrderID,
DateCreated,
DateShipped,
Comments,
Status,
CustomerID,
AuthCode,
Reference,
ShippingCounty,
ShippingStateProvince,
ShippingCountry,
ShippingID,
TaxID,
ShippingAmount,
TaxAmount
FROM Orders
WHERE (DateCreated BETWEEN @StartDate AND @EndDate)
AND (ShippingStateProvince = @ShippingStateProvince)
AND (ShippingCountry = @ShippingCountry)
ORDER BY DateCreated DESC'
END
View 4 Replies
View Related
Aug 8, 2007
hi,
i have installed SQLEXPRESS (SQL Server 9.0.3042) on my windows ultimate laptop. of late am facing this issue of database. this database is from SQl 7.0 Desktop edition(the one that i use in my office machine with Xp SP2). As such it works fine in SQL 2000 server after attachment (for test).
i have run SQLEXPRESS as administrator to make/attach new database. i can attach the database easily and can even view tables and other stuffs inside database in administrator mode. the issue pops up when i run SQLEXPRESS in normal mode. when i try to open and view the same DB's content, error pops up stating
"The database XXX is not accessible. (Micorosoft.sqlserver.express.objectExplorer)"
i cannot access this DB via VB 6 form as it dosnt show up at all in database list when tried to connect via ADODC.
i want to make this DB accessibility as normal operation, not previliged only to administrator as application run on normal mode only.
the issue dosnt arise when i locally make a new database as it can be accessed when run in normal mode also. why does this happen to database that is attached and not to ones locally made.
pls guide me as i usually do my works at home and making whole database again will surely take lots of pain. why not it just attach then start working, not asking for preveliged mode.
thanks in adv
Nyaken
View 3 Replies
View Related
Dec 11, 2007
I'm a SQL newbie, and I'm trying to write a report that returns records based on a beginning and end date that the user supplies. But when I run the query and supply the dates (begin 11/29/2007 / end 11/30/2007, for example), I'm only getting back one record, when there should be at least 3. It appears that my query is ignoring the =, and only returning those records that have a date > or <, not <= or >=.
I was told that it's possible that the time needs to be converted to UTC as well, but I'm not sure how to do that ( I tried, but I really don't know what I'm doing so I commented it out)... Can someone offer some guidance on how to get all the records to show up? I've pasted my query below, thanks in advance!
SELECT FilteredContact.fullname, FilteredContact.new_memberleadsourcename, FilteredContact.new_prospectforclubname, FilteredContact.owneridname,
FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime
FROM FilteredContact JOIN filteredOpportunity on
FilteredOpportunity.contactid = Filteredcontact.contactid
where filteredopportunity.new_opportunitytype = 1 and (FilteredContact.createdon >= '11/29/2007') and (FilteredContact.createdon <= '11/30/2007')
View 3 Replies
View Related
May 3, 2004
I have the following trigger that will insert the correctly if none of the items in the #tempKCS exist in the destination table. However if one of the items in the #tempKCS table do exist in the Sales table then the insert does not execute for the other itmes in the #temoKCS list that are not already listed in the Sales Table. Below is the trigger. Tell me where I am making my mistake. Thanks
CREATE TRIGGER [Insert_KCSales] on [dbo].[Reclines]
FOR INSERT, UPDATE
AS
SELECT KitItemSum.Date, Kits.KitItemNo AS ItemNo, (Kits.Quantity * KitItemSum.QtySold) As QtySold, ((Kits.[Percentage]/100) * KitItemSum.AmtSold) AS AmtSold, KitItemSum.Division, 0 AS QtyReturned, 0 AS AmtReturned Into #TempKCS
FROM (SELECT Summary.Recdate AS [Date], Summary.ItemNo, Sum(Summary.Qty) AS QtySold, SUM((Summary.Amount-Summary.DiscAmt)) AS AmtSold, Summary.Division
FROM (SELECT DISTINCT R.Recno, R.Recdate,I.RecLineNo, I.ItemNo, I.Qty, I.Amount, I.DiscAmt, I.Division FROM Inserted I
INNER JOIN Kits K ON I.ItemNo = K.MasterItemNo INNER JOIN Receipt R ON I.RecNo = R.RecNo INNER JOIN Items ON I.ItemNo = Items.ItemNo) AS Summary
GROUP BY Summary.RecDate, Summary.ItemNo, Summary.Division) AS KitItemSum INNER JOIN Kits ON KitItemSum.ItemNo = Kits.MasterItemNo;
INSERT INTO Sales ([Date], ItemNo, Division, QtySold, AmtSold, Qtyreturned, AmtReturned)
SELECT [Date], ItemNo, Division, QtySold, Amtsold, Qtyreturned, Amtreturned
FROM #TempKCS
WHERE NOT EXISTS (SELECT S.[Date], S.[ItemNo] FROM Sales S INNER JOIN #TempKCS KCS on S.ItemNo = KCS.ItemNo AND S.[Date] = KCS.[Date]);
View 1 Replies
View Related
Feb 9, 2006
Hi all,
Drill through bar charts work great in the report designer preview, but as soon as I deploy the report, and view the report in a webpage, drill through does not work smoothly.
I have to move the mouse around to be able to find a place on the bar to click through to the next report. Sometimes, I am able to click on the bar to go to next report, but then sometimes I have to click on the label, and other times somewhere in the vicinity of the label.
Any help will be much appreciated.
Thanks,
Kanwal
View 1 Replies
View Related
Jul 29, 2015
Send Mail task not working properly when it configured in Precedence Constraint (Failure)...
Scenario:
I'm using multiple containers in my ssis package. Also I have configured mail task for capturing error.
I'm getting mail only when error occurs in foreach loop container. If error occurs in other containers it doesn't works.
Actually I would like to receive mail notification on error when it occurs in any of the containers or DFT.
View 9 Replies
View Related
Oct 20, 2006
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
View 1 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Aug 6, 2000
Hi,
Am having problems creating trigger. I want to have a trigger update a
table (tblClaimItemID) with the value of ClaimItemID + 1 and insert this
value as the primary key of tblClaimItemsDaily(to field ClaimItemID) on
insert of a record into tblClaimItemsDaily (so that when data is appended to
tblClaimsItems we have a unique primary key). tblClaimItemsDaily is the day
sheet, so need to have another table generate the primary key since there
will be multiple tblClaimItemsDaily tables open at any given time, but the
key must be unique. I've been looking at deja and in my books, but they
don't seem to indicate how to do this.
Here's the code. Any ideas?
------------begin code-------
CREATE TRIGGER trgClaimItemID
ON [tblClaimItemsDaily]
FOR INSERT,UPDATE
AS
DECLARE @Item int
IF UPDATE(ClaimID)
BEGIN
IF @@ROWCOUNT = 1
Update dbo.tblClaimItemID
SET dbo.tblClaimItemID.ClaimItemID = dbo.tblClaimItemID.ClaimItemID +
1
FROM dbo.tblClaimItemID
Select @Item = dbo.tblClaimItemID.ClaimItemID FROM dbo.tblClaimItemID
END
UPdate tblClaimItemID set ClaimItemID = @Item
------------end code-------
View 1 Replies
View Related
Nov 3, 2004
i am having an issue with my trigger code
i believe that i should be able to use the multiple if else statements
and i just threw in an update in each begin end statement
but when i check the syntax it fails
any reason why that is
CREATE TRIGGER [tr_update_conveyor_interface] ON [dbo].[t_conveyor_interface]
FOR UPDATE
AS
declare
@id integer,
@barcode varchar(25),
@epc_tag varchar(24),
@reject_flag char(1),
@hold_flag char(1),
@pe_3_flag char(1),
@old_id integer,
@old_barcode varchar(25),
@old_epc_tag varchar(24),
@old_reject_flag char(1),
@old_hold_flag char(1),
@old_pe_3_flag char(1)
select @barcode = c.barcode,
@id = c.id,
@epc_tag = c.epc_tag,
@reject_flag = c.reject_flag,
@hold_flag = c.hold_flag,
@pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join deleted d
on c.barcode = d.barcode
select @old_barcode = c.barcode,
@old_id = c.id,
@old_epc_tag = c.epc_tag,
@old_reject_flag = c.reject_flag,
@old_hold_flag = c.hold_flag,
@old_pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join inserted i
on c.barcode = i.barcode
if(@old_epc_tag <> @epc_tag)
begin
update t_load_audit
set id = 1
end
else
if(@old_reject_flag <> @reject_flag)
begin
update t_load_audit
set id = 1
end
else
if(@old_hold_flag <> @hold_flag)
begin
update t_load_audit
set id = 1
end
else
if(@old_pe_3_flag <> @pe_3_flag
begin
update t_load_audit
set id = 1
end
View 1 Replies
View Related
Dec 16, 2004
Hi,
here is my code for a trigger,
--------------------------------
CREATE TRIGGER trIns_Test
ON tblTest
FOR INSERT
AS
DECLARE @Message VARCHAR(100),
@Num INT
SELECT @Num = user_id from INSERTED
SELECT @Message = 'NET SEND NICK '+' Here is the new # - ' + CONVERT(VARCHAR(100), @Num)
EXEC master.dbo.xp_cmdshell @Message
--------------------------------
But when i insert a row into tblTest table i'am supposed to receive a NET SEND message. But its not happening if i hardcode the value @Num i.e
SELECT @Num = 321
Then i receive a message, but why this INSERTED clause does't work i'm unable to trace out.
Version of SQL is 2K SP3
Thanks,
View 2 Replies
View Related
May 4, 2004
Hi,
I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table?
Any idea?
Any help will be highly appreciated.
CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@var_DB_contract INTEGER,
@var_CQE INTEGER,
@var_PC INTEGER,
@var_item VARCHAR(7),
@var_AMT_PAID INTEGER,
@var_AMT_RET INTEGER,
@var_ITEM_NEW VARCHAR(1),
@var_quant DECIMAL,
@var_fiyr INTEGER,
@var_amt_result INTEGER,
@var_amt_ret_result INTEGER,
@var_amt_old INTEGER,
@var_amt_ret_old INTEGER,
@var_quant_result INTEGER,
@var_quant_new INTEGER,
@var_quant_old INTEGER,
@Item_new VARCHAR(7),
@var_chk varchar(1)
--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
IF @var_db_contract IS NOT NULL
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk ="Y"
END
ELSE
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk="N"
END
SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0)
SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0)
SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0)
SELECT @item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @var_db_contract
AND PC_CODE = @var_PC
AND ITEM_NO = @var_ITEM
UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result
where db_contract = @var_db_contract
IF @item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and fy = @var_fiyr
UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result,
qtd = isnull(qtd,0) + @var_quant_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and item_no = @var_item
View 1 Replies
View Related
Jan 16, 2008
Hi Experts,
The trigger is not sucessfully calling the Stored Procedure. This was working up until last year (12/21/2007 @ 1600 hrs.)Here I am providing codes for both Trigger and Storedprocedure.Can you help me out where the problem is?
Trigger
USE [personnelreq]
GO
/****** Object: Trigger [tgrTracking] Script Date: 01/16/2008 11:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*****************************************************************************************************************************************************************
* Trigger Name: tgrTracking
* Description: Calls the CalcTrackingHours stored procedure for replacement and net staff addition
* requisitions when the requisitions are emailed, transferred, or completed.
* History
* TKT/CO # Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 Zsofia Horompoli Created
* CO#1570 03/22/2005 Zsofia Horompoli Added originator email tracking for transfer events
*****************************************************************************************************************************************************************/
CREATE TRIGGER [tgrTracking] ON [dbo].[Log2]
FOR INSERT
AS
DECLARE @reqId int -- store requisition ID
DECLARE @eventId int -- event value inserted
DECLARE @parentId int -- store parent requisition ID
DECLARE @eventTime datetime -- store date/time of event
DECLARE @email varchar(50) -- store email address
DECLARE @logId int -- store ID assigned to the record in the Log2 table
DECLARE @location varchar(30) -- Location
DECLARE @title varchar(30) -- Job title
DECLARE @multiReq int -- > 0 = Multi NSA
DECLARE @origMail varchar(50) -- Originator's email address
DECLARE @subject varchar(200) -- Email subject line
DECLARE @from varchar(50) -- From email
DECLARE @body varchar(8000) -- Email body
DECLARE @reqType int -- Requisition type (0-replacement, 1-net staff addition, 6-Bonus)
DECLARE @sReqType varchar(30) -- Requisition type description
DECLARE @appEmail varchar(500) -- Approvers' email addresses
SELECT @from = 'compensationandbenefits@west.com'
DECLARE @inserted_rows AS CURSOR -- hold the inserted rows
SET @inserted_rows = CURSOR FOR
SELECT id,reqid, timestamp, event, stuff(notes,1,patindex('%:%',notes),'') AS email FROM inserted -- get the Log2 id,requisition id, event, timestamp, and email address from the updated/inserted rows
OPEN @inserted_rows
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Is this an email/transfer/complete/deny event?
IF (@eventId = 4 OR @eventId = 6 OR @eventId = 7 OR @eventId = 0 OR @eventId = 10)
BEGIN -- Yes, is this a deny event?
IF (@eventId = 0)
BEGIN --Yes, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype in (0,1) and id = @reqId and
(SELECT ParentReqId from ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2
END
ELSE IF (@eventId = 6)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1,6) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE IF (@eventId = 7 OR @eventId = 10)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE
BEGIN -- No, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
--Is this a requisition to be tracked?
IF @parentId is not NULL
BEGIN --Yes, is this a transfer event?
IF @eventId = 6
BEGIN --Yes, retrieve information for email
SELECT @location = RTRIM(L.DESCR), @title = RTRIM(PT.DESCR), @multiReq = (SELECT COUNT(*) FROM ParentChild WHERE ParentReqID = R.id), @reqType = R.requisitiontype
FROM reqs2 R
LEFT OUTER JOIN TDSdev.dbo.PS_LOCATION_TBL L ON R.sitename = L.LOCATION
LEFT OUTER JOIN TDSdev.dbo.PS_JOBCODE_TBL PT ON R.jobtitle = PT.JOBCODE
WHERE R.id = @reqId
-- Get email addresses to use
EXEC sel_EmailUsers @reqId = @reqId, @bApprover = 1, @currentUser = NULL, @origEmail = @origMail OUTPUT, @appEmail = @appEmail OUTPUT
--Set requisition type description
IF @reqType = 0
BEGIN --Replacement requisition
SELECT @sReqType = 'Replacement'
END
ELSE IF @reqType = 1 AND @multiReq > 0
BEGIN --Multi-NSA
SELECT @sReqType = 'Multiple Net Staff Addition'
END
ELSE IF @reqType = 1
BEGIN --NSA
SELECT @sReqType = 'Net Staff Addition'
END
ELSE IF @reqType = 6
BEGIN --Bonus
SELECT @sReqType = 'Bonus'
END
--Build subject line
--Do we have a location?
IF @location IS NULL
BEGIN --No, default to Nothing
SELECT @location = ''
END
--Do we have a job title?
IF @title IS NULL
BEGIN --No, default to Nothing
SELECT @title = ''
END
SELECT @subject = @location + ': ' + @sReqType + ': ' + CAST(@reqId AS varchar(15)) + ': ' + @title
--Set email body
IF @reqType = 6 -- If Bonus Req
BEGIN
SELECT @body = '<p>Bonus requisition number ' + CAST(@reqId AS varchar(15)) + ' has been processed by Compensation.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END
ELSE IF @reqType IN (0,1) -- If Net Staff or Replacement Req
BEGIN
SELECT @body = '<p>Requisition number ' + CAST(@reqId AS varchar(15)) + ' has been finalized and sent to Human Resources on ' + convert(char(10),@eventTime, 101) + ' at ' + substring(convert(char(19),@eventTime, 100), 12, 8) + ' for recruiting.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END
--Send email
EXEC usp_SMTPMail @SenderAddress = @from, @RecipientAddress = @origMail, @Subject = @subject, @Body = @body, @Cc = @appEmail
END
-- Call the CalcTrackingHours stored procedure
IF @reqType IN (0,1)
BEGIN
EXEC CalcTrackingHours @event = @eventId, @reqId = @reqId, @emailTo = @email, @endDateTime = @eventTime, @logId = @logId
END
END
END
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
END
--clean up
CLOSE @inserted_rows
DEALLOCATE @inserted_rows
Stored Procedure is
USE [personnelreq]
GO
/****** Object: StoredProcedure [dbo].[CalcTrackingHours] Script Date: 01/16/2008 12:01:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.CalcTrackingHours Script Date: 4/12/2005 9:20:19 AM ******/
/*****************************************************************************************************************************************************************
* Stored Procedure Name: CalcTrackingHours
* Description: Determines the appropriate tracking level, the number of business hours the requisition
* spent at the current tracking level and inserts this information into the timeLog table
* History
* Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 syszxh Created
*****************************************************************************************************************************************************************/
CREATE procedure [dbo].[CalcTrackingHours]
@event int, -- 4=Emailed,6=Transferred,7=Completed
@reqId int, -- Requisition Id
@emailTo varchar(50), -- Email address requisition was forwarded to
@endDateTime datetime, -- Date/time of event
@logId int -- ID assigned to new record in the Log2 table
as
set nocount on
declare @currentUserType as int -- Current user type
declare @startDateTime as datetime -- Date/time requisition arrived at current step
declare @newUserType as int -- New user type
declare @currentTrackLevel as varchar(5) -- Tracking level to be inserted into the timeLog table
declare @newTrackLevel as varchar(5) -- New tracking level
declare @trackEmail as varchar(50) -- Email address at current tracking level
declare @count as int -- counter variable
declare @errorValue as int -- Error flag (1 = start date/time missing)
declare @trackHours as decimal(10,2)
select @errorValue = 0
select @startDateTime = NULL
-- Retrieve last email's user type, date/time stamp, and email address
select top 1 @currentUserType = userType, @startDateTime = timestamp, @trackEmail = n.userid
from personnelreq.dbo.Log2 l left outer join personnelreq.dbo.names2 n on stuff(notes,1,patindex('%:%',notes),'') = n.userid
where (l.event = 4 or l.event = 10) and timestamp <= @endDateTime and reqid = @reqId and l.id < @logId order by l.id desc
-- Last email found?
if @startDateTime is NULL
begin
-- No, this is the very first email, retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo
-- Is new user @C&B?
if @newUserType = 2
begin -- Yes, set new tracking level
select @newTrackLevel = 'cb1'
end
else
begin -- No, set new tracking level
select @newTrackLevel = 'dept'
end
end
else
begin
-- Is this event = email?
if @event = 4
begin
-- Retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo
-- Is the current user the same as the new user?
if @currentUserType = @newUserType and @emailTo = @trackEmail
begin
-- Yes, get latest level
select top 1 @newTrackLevel = trackLevel
from timeLog
where reqID = @reqId and trackEmail = @trackEmail and dateIn = @startDateTime
order by dateIn desc
select @currentTrackLevel = @newTrackLevel
end
-- Is the current user type @C&B?
else if @currentUserType <> 2 or @currentUserType is NULL
begin
-- No, retrieve current tracking level
exec GetTrackLevel @userType = @currentUserType, @reqID = @reqID, @email = @trackEmail, @trackLevel = @currentTrackLevel output
-- Is the new user type @C&B?
if @newUserType = 2
begin -- Yes, retrieve new tracking level
exec GetCBLevel @trackLevel = @currentTrackLevel, @reqID = @reqID, @currentTrackLevel = @newTrackLevel output
end
else -- No, retrieve new tracking level
begin
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
end
end
else
begin
-- Yes, retrieve new tracking level
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
-- Retrieve current tracking level
exec GetCBLevel @trackLevel = @newTrackLevel, @reqID = @reqID, @currentTrackLevel = @currentTrackLevel output
end
end
-- Is this event = transfer/completed?
else if @event = 6 or @event = 7 or @event = 10
begin
-- No, any existing entries for exec3?
if (select count(*) from timeLog where trackLevel = 'exec3' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb5
select @currentTrackLevel = 'cb5'
end
-- Any existing entries for exec2?
else if (select count(*) from timeLog where trackLevel = 'exec2' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb4
select @currentTrackLevel = 'cb4'
end
-- Any existing entries for exec1?
else if (select count(*) from timeLog where trackLevel = 'exec1' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb3
select @currentTrackLevel = 'cb3'
end
-- Any existing entries for acct?
else if (select count(*) from timeLog where trackLevel = 'acct' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb2
select @currentTrackLevel = 'cb2'
end
else
begin
-- Set current tracking level to cb1
select @currentTrackLevel = 'cb1'
end
end
-- Calculate hours worked
if @event <> 10
begin
exec CalcBusinessHours @startDateT = @startDateTime, @endDateT = @endDateTime, @totalHours = @trackHours output
-- Update information in the timeLog table
update timeLog
set dateOut = @endDateTime, trackHours = @trackHours
where reqID = @reqID and trackHours = 0 and dateOut = '1990-12-31'
end
end
-- Is this event = email?
if @event = 4 and @newTrackLevel is not NULL
begin
-- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @newTrackLevel, @endDateTime, @emailTo, 0, '1990-12-31')
end
-- Is this event = reopen?
else if @event = 10 and @trackEmail is not NULL
begin -- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @currentTrackLevel, @endDateTime, @trackEmail, 0, '1990-12-31')
end
endProcedure:
Return @errorValue
View 2 Replies
View Related