Time Out In Triggering A Sqlclr Stored Procedure
Jul 18, 2007
Hello,
I have a sqlclr stored procedure that works well if started via execute in SSMS. (The stored procedures makes a SSRS web service call.) When I install a trigger on a database table to execute the stored procedure
create trigger NewWdsStatsEntry on dbo.WDSSTATS after insert
as execute UpdateReportExecutionSnapshot N'http://localhost/ReportServer/reportservice2005.asmx', N'/Report1';
go
and add something to the table, I get the following error:
Msg 6522, Level 16, State 1, Procedure UpdateReportExecutionSnapshot, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "UpdateReportExecutionSnapshot":
System.Net.WebException: The request was aborted: The operation has timed out.
System.Net.WebException:
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at UserProcedures.ReportService2005.ReportingService2005.UpdateReportExecutionSnapshot(String Report)
at StoredProcedures.UpdateReportExecutionSnapshot(String url, String reportPath)
.
The statement has been terminated.
The insert times out after 100 seconds and no row has been added to the table, but the web service call actually created a new report! Any idea why the trigger can't execute the stored procedure?
Thanks!
werner
View 3 Replies
ADVERTISEMENT
Mar 11, 2006
If I am creating an auction site and I want the auction to absolutely close on a certain date is there a way in SQL I can trigger to change the status of the auction to close?
View 1 Replies
View Related
Jun 11, 2015
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies
View Related
Aug 21, 2006
Hi,
I have create few Mannaged SP which acces some external resources like closing and starting some windows process.. the assembly is assigend unsafe security level
all the processes are started from sqlserver.exe service and are allowed to interact with desktop
these SPs are called frequently
Problem... a very few times sql server is crashed..
does mannaged SP load app domain each time they are called?
what possible reason can be?
I am not exactly clear about the reson .. so I decided to post over here ..
Thanks,
View 3 Replies
View Related
Mar 8, 2002
I would like to have stored procedures run on the first of every month. What would be the best way to do this?
View 1 Replies
View Related
Aug 1, 2007
hi, Derek Comingore
My self Jitendra Nayi....
I am trying to generate a SQL CLR stored procedure and i have done it too. Now the next step is to convert that *.dll file in to assembly. I am getting erro that. My server Database is on the LAN, not on my PC.
Here is the code which i have tried to register an assembly...
/*
CREATE ASSEMBLY MyAssembly FROM 'C:Documents and SettingsAdministrator.ORC80My DocumentsVisual Studio 2005ProjectsMyDB1MyDB1inDebugMyDB1.dll'
WITH PERMISSION_SET=SAFE
GO
*/
and here is the error that i am getting...
/*
Msg 6585, Level 16, State 1, Line 1
Could not impersonate the client during assembly file operation.
*/
Please help me .I am stuck at here.
View 2 Replies
View Related
Apr 13, 2006
I expected Optional ByRef nArg as Integer=123 to setup a sp arg with a default value of 123. Doesn't look like it does. Anybody know what I'm doing wrong?
Also, I can't write a function with a return using AS System.Int32. There is no RETURN sp arg.
Both situations compile/deploy without error.
View 4 Replies
View Related
Jan 30, 2007
I am trying to call a stored procedure from an asp.net application. If I execute the stored procedure in Query Analyzer it takes 1 second to execute, but when I am trying to call it from c# code it times out.
Ideas?
Thanks in advance.
View 4 Replies
View Related
Feb 18, 2000
Hi There,
We have developed a application in VB and connected to SQL Server 6.5, we have some stored procedures where it brings the data from SQL Server 6.5, this application is running since some months, when we run this application it usually take only one minute to generate the report but since couple of days it is taking 25 Minutes to generate the report, even when I run that stored procedure at backend in Query analyzer at Server it is taking 15-20 Minutes to give the result.
please can any one help in identifying the problem, What all the things I need to check to identify it.
Give me the solution.
Thanks in Advance,
Bye,
Madhuker.
View 1 Replies
View Related
Aug 24, 2000
I have a sotred procedure using a cursor which sort data and create subsets based on same oid and same decision_date columns, for each subset I am trying to order them and affect values 1, 2,... for each record in a different subset.
The stored procedure seems to work very well and fast against a small tables (during tests). When used against a table with 200,000 records it takes more than 24 hours...
I am looking for a help to make it work faster, thanks guys.
here is the stored procedure:
CREATE PROCEDURE ORDERING_TEST_PROCEDURE AS
DROP TABLE REVIEWS_TEST_TABLE
SELECT OID,DECISION_DATE,DECISION_ID,VOTES_REQUIRED, ORDERING INTO REVIEWS_TEST_TABLE FROM DECISION_FLAGS
WHERE VOTES_REQUIRED IN ('1','2','3') and FINAL_DECISION_CODE NOT IN ('0800','0810','0840') and DECISION_TYPE_CODE < '0100'
ORDER BY OID, DECISION_DATE,VOTES_REQUIRED DESC
CREATE INDEX OID_DECISIONID_DATE_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID, DECISION_DATE, OID)
CREATE INDEX VOTESREQUIRED_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (VOTES_REQUIRED)
CREATE INDEX DECISIONID_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID)
declare @oldoid varchar(12)
declare @olddecision_date varchar(75)
declare @oid varchar(12)
declare @decision_date varchar(75)
declare @Decision_id varchar(12)
declare @ordering varchar(1)
declare @ordering_count int
declare @votes_required varchar(12)
set @oldoid = 'space'
set @olddecision_date = 'space'
set @oid = 'space'
set @decision_date = 'space'
set @votes_required='space'
set @Decision_id = 'space'
set @ordering = '0'
set @ordering_count = 0
declare review_test_cursor cursor for
select oid,decision_date,votes_required,ordering,decision _id from CRIMS.dbo.reviews_test_table
order by oid,decision_date,votes_required asc
open review_test_cursor
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
while (@@fetch_status = 0 )
begin
if @oldoid <> @oid or @olddecision_date <> @decision_date
begin
set @oldoid = @oid
set @olddecision_date = @decision_date
set @ordering_count=0
end
update reviews_test_table
set ordering = CAST ((@ordering_count + 1) as VARCHAR)
where decision_id = @Decision_id
set @ordering_count = @ordering_count + 1
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
end
close review_test_cursor
deallocate review_test_cursor
/*********************************/
UPDATE DECISION_FLAGS
SET ORDERING = '0'
UPDATE DECISION_FLAGS
SET DECISION_FLAGS.ORDERING = TEM.ORDERING
FROM DECISION_FLAGS DEC, REVIEWS_TEST_TABLE TEM
View 4 Replies
View Related
Oct 5, 1999
I'm having problems testing the effectiveness of changes I'm making to a stored_procedure. I need to test the runtime, but each time I run the sp, it gets progressively faster because the data has already been cached. Is there a way for me to clear the data from cache before each run? I can't stop SQL Server.
View 2 Replies
View Related
Jun 24, 2008
I am trying to test the connection pooling of my app and need to artificially increase the execution of a stored procedure to 10-15 seconds. There aren't any big tables in my sample data to select on. What would be the easiest way to make a stored procedure fun for a few seconds?
Thanks.
View 6 Replies
View Related
Mar 7, 2008
Hi all,I have a problem with a stored procedure.This stored procedure inserts around bout 500,000 records but when it is executed it takes about 15-16 hours to do so.The stored procedure is using a temporary table to do this and is also calling a function.Please let me know if there is a way to reduce the execution time.will a cursor help?
Thanks,
Anne.
View 19 Replies
View Related
May 3, 2008
help-take a long time run this stored procedure 15 second
Code Snippet
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,@NumDays TINYINT,@myNum TINYINT
set @myNum=4
SELECT @WantedDate = CAST(CAST(YEAR(GETDATE()) AS nvarchar)+ '-' +CAST(1 AS nvarchar)+ '-' +CAST(@myNum AS nvarchar) AS SMALLDATETIME), -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
select @WantedDate
IF @NumDays = 28
BEGIN
SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,
CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis
FROM dbo.v_hasot_all INNER JOIN
dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int
WHERE (NOT (dbo.v_hasot_all.nameday IN (29, 30, 31)))
END
ELSE IF @Numdays = 29
BEGIN
SELECT ...............
WHERE (NOT (dbo.v_hasot_all.nameday IN ( 30, 31)))
END
ELSE IF @Numdays = 30
BEGIN
SELECT ......................
WHERE (NOT (dbo.v_hasot_all.nameday IN (31)))
END
ELSE IF @Numdays = 31
BEGIN
SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,
CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis
FROM dbo.v_hasot_all INNER JOIN
dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int
END
and whan i run my code separately it run fast
i think it is the ELSE IF @Numdays
TNX
View 5 Replies
View Related
Aug 15, 2006
For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.
Chapter 1 Introducing SQLCLR
Chapter 2 Building a Procedure
Chapter 3 SQLCLR Strucutre & Common Tasks
Chapter 4 Creating Objects
Chapter 5 Compare & Contrast
Chapter 6 Replacing TSQL Objects
Chapter 7 Using the Base Library
Chapter 8 Using Procedures in Apps
Chapter 9 Error Handling
Chapter 10 Administration
Chapter 11 Case Study
Here is the link:
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html
Enjoy,
Derek
View 2 Replies
View Related
Mar 28, 2006
Hello,
my username is selevalencia, I had before the database on my machine, but as now we have a server I backed up the database and restored on the server, it seems that it has the sames logins and security users,
Its strange because the user dbo is assigned the user selevalencia on the server, and I cant alter the user dbo.
The user dbo should be the login sa?? I am in big trouble with this, beacuase I cant debug my sql clr stored procedure.
T-Sql execution ended without debugging. You may have not have sufficient permissions to debug.
I am using a connection with windows authentication, so it means that its sending tha tokes as selevalencia to the server.
Please feel free to ask for more information about it. Thanks
View 3 Replies
View Related
Sep 14, 2004
G'day everyone...
I have two tables: Category and Product.
CategoryID is the Foreign Key of Product table.
I want to update those tables at the same time using stored procedure.
It will work like this.
If I update the Display column of Category table to have the value of 0, it will also update any records in the Product table -that has the same CategoryID- to have the value of 0 in its Display column.
I just read something about trigger and bit gives me idea that I might use it for this case, but don't know how to write the SQL.
Can anyone please help me...
Thanks...
View 2 Replies
View Related
Jul 14, 2000
I have created a DTS package. I want to know how to call/run this package from a stored procedure.
Please let me know how to call this DTS from a Stored Procedure.
Thanks.
-Rajesh
View 1 Replies
View Related
Jul 21, 2000
Hello all,
We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.
So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?
Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!
Kevin Kraus
View 2 Replies
View Related
Nov 12, 2004
Hi there,
SQL newbie here, and thanks for any help you may able to provide.
My intention is to schedule/execute a stored procedure every morning at 12:00 a.m. that deletes all records with a column value of the day before. I.E., one of my Table columns is named POSTDAY, and could have values such as Sunday, Monday, Tuesday, etc, and on Tuesday morning, I'd like to DELETE all records with a POSTDAY value of Monday.
I think I can do this by creating and scheduling 7 different stored procedures (each with the actual DayName), but was wondering if it's possible to just have 1 accomplish the same thing, and without having to pass any parameters to it.
Thanks again.
View 4 Replies
View Related
Oct 6, 2005
Hi
I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.
What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.
Thanks.
View 1 Replies
View Related
Dec 6, 2007
Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYT
View 14 Replies
View Related
Jul 17, 2015
I have a sp that was taking very little time (about 34 sec). But suddenly is stacked. It is running and running and running but not LOCKED neither SUSPENDED. It is always RUNNABLE. I have made Index and statistics optimization but nothing. I looked into execution plan but everything seems ok. All the time is in 3 indexes that are Index Seek and not Table Scan!!! So why is stacked... I do not know how much time it takes because I have to stop it. (SQL SERVER 2008 R2, the database was migrated from SQL SERVER 2000)
View 6 Replies
View Related
Apr 30, 2008
Hello,
If i want to optimise stored procedure response time.
Which things i need to be considered?
Thanks in advance.
View 3 Replies
View Related
Sep 21, 2007
are sqlclr stored proc sessions running in the same SQLOS execution model (Running, runnable and suspended status, Runnable Queue and Wait List) as other types of sessions and therefore ending up in the same queues, contending for very similar resources (except maybe for memory type because of MTL's role) etc?...or do they get treated in a very different way?
View 1 Replies
View Related
Feb 21, 2002
HI,
I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.
I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?
Any feedback would be appreciated!
Thanks in advance...
View 2 Replies
View Related
Jul 23, 2005
I have a Stored Procedure (SP) that creates the data required for areport that I show on a web page. The SP does all the work and justreturns back a results set that I dump in an ASP.NET DataGrid. The SPtakes a product area and a start and end date as parameters.Here are the basics of the SP.1.Create temp table to store report results, all columns are createdthat will be needed at this point.2.Select products and general product data into the temp table.3.Create a cursor that loops through all the products in the temptable, running a more complex query with each individual product.4.The results of that query are updated on the temp table based on thecurrent product of the cursor.5.A complex "totals" query is run and the results from that areinserted into the temp table as the last 3 rows.In all we are talking about 120 rows in the temp table with 8 columnsthat are mostly numbers.I originally wrote this report SP about a month ago and it worked fine,ran in about 10 - 20 seconds based on server traffic and amount ofdata in the temp table. For the example I'm running there are the120 products.Just yesterday the (SP started timing out and when I ran the SPmanually from Query Analyzer (QA) (exec SP_NAME ... ) with the sameparameters as it was getting in the code it took 6 minutes to complete.I was floored. I immediately copied the SQL out of the SP and pastedinto another QA window, changed the variables to be hard coded valuesand ran it. It completed in 10 seconds.I'm really confused now. I ran a Profiler on the 2 when I ran themagain. The SQL code in QA executed again in ~10 seconds with 65,000reads. When the SP finished some 6 minutes later it had completed witthe right results but it needed 150,000,000 reads to do its job.How can the exact same SQL code produce such different results (time,disk reads) based on whether its in a SP or just run from QA but stillgive me the exact same output. The reports both look correct and havethe same numbers of rows.I asked my Sys Admin if he had done anything to anything and he saidno.I've been reading about recompiles and temp table indexes and allkinds of other stuff that could possibly be affecting it but havegotten nowhere.Any ideas are appreciated.
View 5 Replies
View Related
Aug 23, 2007
I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.
Please help--Iam stumped.
There are no infinite loops in the report. I checked.
View 9 Replies
View Related
Jun 25, 2007
The query below runs in sub second time if I don't call it as a stored procedure. I have looked at the execution plan for both the query and the query as a stored procedure and they are the same.
When I put the query into a stored procedure it takes over 2 minutes to run.
All feedback (even the ugly stuff) is more than welcome. I want to master this issue and forever put it behind me.
This is the sql when I just execute it outright:1 DECLARE
2 @WebUserID nvarchar(20)
3 ,@DocumentTypeID int
4 ,@RouteID nvarchar(10)
5 ,@CustomerID nvarchar(15)
6 ,@DocumentIDPrefix nvarchar(20)
7 ,@StartDate datetime
8 ,@EndDate datetime
9 ,@OversoldOnly bit
10 ,@DexCustomersOnly bit
11 ,@DeviationsOnly bit
12 ,@CashNoPaymentOnly bit
13 ,@SignatureName nvarchar(45)
14 ,@SortExpression varchar(200)
15 ,@StartRowIndex int
16 ,@MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 WITH OrderedDocumentHistory AS
32 (
33 SELECT
34 dh.DocumentHistoryID
35 ,dh.DocumentID
36 ,dh.DocumentTypeID
37 ,dh.DocumentTypeDesc
38 ,dh.RouteID
39 ,dh.RouteDesc
40 ,dh.CustomerID
41 ,dh.CustomerName
42 ,dh.DocDate
43 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
44 ,ddt.Description AS 'SignatureReason'
45 ,a.Amount
46 ,ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC) AS 'RowNumber'
47 FROM
48 DocumentHistory dh
49 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
50 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
51 INNER JOIN
52 (
53 SELECT
54 DocumentHistoryID
55 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
56 FROM
57 DocumentHistoryItems dhia
58 GROUP BY
59 dhia.DocumentHistoryID
60 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
61 LEFT OUTER JOIN
62 (
63 SELECT DISTINCT
64 dca.DocumentID
65 ,1 AS 'HasReceipt'
66 FROM
67 DocumentCollections dca
68 ) AS dc ON dh.DocumentID = dc.DocumentID
69 WHERE
70 dh.DocDate BETWEEN @StartDate AND @EndDate
71 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
72 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
73 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
74 AND dh.DocumentID LIKE @DocumentIDPrefix
75 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
76 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
77 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
78 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
79 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
80 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
81 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
82 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
83 OR @WebUserID IS NULL)
84 )
85
86 SELECT
87 DocumentHistoryID
88 ,DocumentID
89 ,DocumentTypeDesc
90 ,RouteID
91 ,RouteDesc
92 ,CustomerID
93 ,CustomerName
94 ,DocDate
95 ,Amount
96 ,HasReceipt
97 ,SignatureReason
98 FROM
99 OrderedDocumentHistory
100 WHERE
101 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows) Here is the sql for creating the stored procedure. 1 CREATE Procedure w_DocumentHistory_Select
2 (
3 @WebUserID nvarchar(20)
4 ,@DocumentTypeID int
5 ,@RouteID nvarchar(10)
6 ,@CustomerID nvarchar(15)
7 ,@DocumentIDPrefix nvarchar(20)
8 ,@StartDate datetime
9 ,@EndDate datetime
10 ,@OversoldOnly bit
11 ,@DexCustomersOnly bit
12 ,@DeviationsOnly bit
13 ,@CashNoPaymentOnly bit
14 ,@SignatureName nvarchar(45)
15 ,@SortExpression varchar(200)
16 ,@StartRowIndex int
17 ,@MaximumRows int
18 )
19 AS
20 SET NOCOUNT ON
21
22 IF LEN(@SortExpression) = 0 OR @SortExpression IS NULL
23 SET @SortExpression = 'Number DESC'
24
25 IF @StartRowIndex IS NULL
26 SET @StartRowIndex = 0
27
28 IF @MaximumRows IS NULL
29 SELECT
30 @MaximumRows = COUNT(dh.DocumentHistoryID)
31 FROM
32 DocumentHistory dh;
33
34 WITH OrderedDocumentHistory AS
35 (
36 SELECT
37 dh.DocumentHistoryID
38 ,dh.DocumentID
39 ,dh.DocumentTypeID
40 ,dh.DocumentTypeDesc
41 ,dh.RouteID
42 ,dh.RouteDesc
43 ,dh.CustomerID
44 ,dh.CustomerName
45 ,dh.DocDate
46 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
47 ,ddt.Description AS 'SignatureReason'
48 ,a.Amount
49 ,CASE
50 WHEN @SortExpression = 'Number DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID DESC))
51 WHEN @SortExpression = 'Number ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID ASC))
52 WHEN @SortExpression = 'CustomerName DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName DESC))
53 WHEN @SortExpression = 'CustomerName ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName ASC))
54 WHEN @SortExpression = 'CompletedDate DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC))
55 WHEN @SortExpression = 'CompletedDate ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate ASC))
56 WHEN @SortExpression = 'RouteDescription DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc DESC))
57 WHEN @SortExpression = 'RouteDescription ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc ASC))
58 END AS 'RowNumber'
59 FROM
60 DocumentHistory dh
61 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
62 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
63 INNER JOIN
64 (
65 SELECT
66 DocumentHistoryID
67 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
68 FROM
69 DocumentHistoryItems dhia
70 GROUP BY
71 dhia.DocumentHistoryID
72 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
73 LEFT OUTER JOIN
74 (
75 SELECT DISTINCT
76 dca.DocumentID
77 ,1 AS 'HasReceipt'
78 FROM
79 DocumentCollections dca
80 ) AS dc ON dh.DocumentID = dc.DocumentID
81 WHERE
82 dh.DocDate BETWEEN @StartDate AND @EndDate
83 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
84 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
85 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
86 AND dh.DocumentID LIKE @DocumentIDPrefix
87 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
88 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
89 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL((SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
90 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
91 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
92 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
93 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
94 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
95 OR @WebUserID IS NULL)
96 )
97 SELECT
98 DocumentHistoryID
99 ,DocumentID
100 ,DocumentTypeDesc
101 ,RouteID
102 ,RouteDesc
103 ,CustomerID
104 ,CustomerName
105 ,DocDate
106 ,Amount
107 ,HasReceipt
108 ,SignatureReason
109 FROM
110 OrderedDocumentHistory
111 WHERE
112 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows)
Here is the code for calling the stored procedure:1 DECLARE @RC int
2 DECLARE @WebUserID nvarchar(20)
3 DECLARE @DocumentTypeID int
4 DECLARE @RouteID nvarchar(10)
5 DECLARE @CustomerID nvarchar(15)
6 DECLARE @DocumentIDPrefix nvarchar(20)
7 DECLARE @StartDate datetime
8 DECLARE @EndDate datetime
9 DECLARE @OversoldOnly bit
10 DECLARE @DexCustomersOnly bit
11 DECLARE @DeviationsOnly bit
12 DECLARE @CashNoPaymentOnly bit
13 DECLARE @SignatureName nvarchar(45)
14 DECLARE @SortExpression varchar(200)
15 DECLARE @StartRowIndex int
16 DECLARE @MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 EXECUTE @RC = [Odom].[dbo].[w_DocumentHistory_Select]
32 @WebUserID
33 ,@DocumentTypeID
34 ,@RouteID
35 ,@CustomerID
36 ,@DocumentIDPrefix
37 ,@StartDate
38 ,@EndDate
39 ,@OversoldOnly
40 ,@DexCustomersOnly
41 ,@DeviationsOnly
42 ,@CashNoPaymentOnly
43 ,@SignatureName
44 ,@SortExpression
45 ,@StartRowIndex
46 ,@MaximumRows
View 3 Replies
View Related
May 11, 2015
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
BEGIN
I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.
View 7 Replies
View Related
Dec 11, 2007
How to return time & number format that has set in the regional setting using stored procedure.
Following is my sp for getting current date format from Sql Server.
View 1 Replies
View Related
Oct 16, 2007
Hi ,
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.
View 1 Replies
View Related
Nov 2, 2015
Created a report that displays the Maximum Response time (example of value 00:00:00) which is directly pulled from the Stored proc.When I ran the report, the column displays blank values.I am not sure if I should add any conversion to the Response value in the report.
View 2 Replies
View Related
