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.
SELECT @WantedDate = CAST(CAST(YEAR(GETDATE()) AS nvarchar)+ '-' +CAST(1 AS nvarchar)+ '-' +CAST(@myNum AS nvarchar) AS SMALLDATETIME), -- User supplied parameter value
I have a stored procedure that normally takes about 5 hours to complete: DELETE tblX WHERE PROC_DT < dateadd(day, -93 , getdate())
tblX has about 55 million records and has an index on PROC_DT.
I have this running as a scheduled task. Over the weekend, the task executed and it is still running 56+ hours later. Does anybody have any ideas as to where I should look for the problem? I am afraid to kill the process because of the rollback time.
We have a customer table and an address table. Each customer can have 1 or more addresses in the address table.
The task is to synchronize customer information from 2 entirely separate systems. I have a stored procedure that reads a text file (exported from the 'master' system) and updates all the customer records in the second system (which is in the sql server db). The queries themselves work. The data is accurate. My issue is with how long it takes the stored procedure to run. With over 11,000 records in the text file, the stored procedure sometimes takes over 3 hours to run.
That seems excessive to me. (and probably to those of you snickering in the back)
As an overview: my stored procedure does the following.
1) creates a temp table 2) dumps the data from the text file into the temp table 3) updates the address table with data from the temp table (records already there) 4) inserts records into the address table from the temp table (records not already there) 5) updates the customer table with an address id 6) drops the temp table
Any help/suggestions is appreciated. If you need more info, let me know.
I have a stored procedure that is taking too long to complete. I'ved narrowed the problem down to the following code. Is there any reason why this should take long to complete for about 40,000 records?
UPDATE Entries
SET EntryStatus = 1, reason = 'code entered more than once'
WHERE TimeSubmitted > @StartOfPeriod AND TimeSubmitted < @EndOfPeriod
AND TimeSubmitted <> @t and ShortCode = @ShortCode
I have a stored procedure being called from Visual Cafe 4.0 that takes over 30 minutes to run. Is there any way to backround this so that control returns to the browser that the JFC Applet is running in? The result set is saved to local disk and an email message sent to the user on completion. Thanks, Dave.
I'm an experienced SQL Server and .NET developer, but I wanted to expand the way I look at things and see how other developers approach the situation I'm going to outline in this post. I'm going to be engineering a large, new project soon and I want to examine how I approach this and see if there is a better way.
I work in a small development group with two developers (myself and another). We pretty much wear all the design, testing ,and development hats during the course of a system's development. I had a discussion today with the other developer about creation of stored procedures.
I like to create small specific stored procedures for whatever I'm doing. I will usually have at least 4 stored procedures for each table; Insert, Delete, Update, and Select. Frequently I'll have more Select procedures for special cases. I do this for several reason. One I can get Visual Studio to generate the basic procedures for me and utilize them in a typed dataset. Secondly I can keep all my SQL code server side, and in small maintainable chunks. It is also fairly obvious what my stored procedures do from the name. The main drawback is that the list of stored procedures gets huge.
The developer I work with likes to create a single stored procedure for Insert, Update, and Deletes. Based on the passed primary key, the procedure determines what it should do. For example:
Code Snippet
CREATE PROCEDURE udp_users_processing @key int output, @name varchar(200), @status int AS IF IsNull(@key,0)=0 BEGIN INSERT INTO ut_users(key, name, status) VALUES (@key, @name, @status) SET @key = SCOPE_IDENTITY() END ELSE IF KEY > 0 UPDATE ut_users SET key = @key, name = @name, status = @status ELSE BEGIN DELETE FROM ut_users WHERE key = @key END This has the advantage of being compact, but it has issues with VS.NET and designer support. Loss of designer support isn't a huge problem, but it can be handy to have. I'm also not certain how this approach would work when using typed dataset and the table adapter to do updates.
What is YOUR opinion? How would YOU approach this in your situations? Are there other alternatives that might work just as well?
I have a stored procedure in SQL 2005 that purges data, and may take a few minutes to run. I'd like to report back to the client with status messages as the sp executes, using PRINT statements or something similar. I imagine something similar to BACKUP DATABASE, where it reports on percentage complete as the backup is executing.
I can't seem to find any information on how to do this. All posts on this subject state that it's not possible; that PRINT data is returned after the procedure executes. However it would seem possible since BACKUP DATABASE, for example, does this.
Is there any way to send status type messages to the client as the sp is executing??
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.
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open() sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table scb = New SqlCeCommandBuilder(sda) sda.Update(dataset) cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open() Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
I am looking at building multiple SSIS packages. There will be some similarities. Flexibility is of highest importance. The main packages will need to connect to SQL Server1 as a source and SQL Server2 as a destination to transfer over dimenion data from multiple databases. (other SSIS packages may need to use SQL Server2 as a source and SQL Server1 as a destination)
For a single dimension table containing column dim_id on the target server (SQLServer2). I need to pass the results of the following SQL and insert into SQLServer2.database.dim_table
select dim.id from SQLServer1.database08.dim_table union select dim.id from SQLServer1.database07.dim_table union select dim.id from SQLServer1.database06.dim_table
Now next year the names of the databases on SQLServer1 will be database09,database08,database07!
Now so far my best thought is creating views in my destination SQL Server. So I need some way of dropping and recreating the views. Previously in DTS I would expect to see SQL Server connection that I could use as source and destination. Now I can see SQL Server destination but not source? Also How do I just use SSIS to run some SQL. i.e execute a stored procedure, drop and creat views?
Many thanks, Ells p.s Flexibility is the key, in the last three months all the ip and server names have changed more than once so need to be as flexible as possible.
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.
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)
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
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.
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?
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.
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.
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!!!
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.
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.
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)
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!
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?
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.
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.
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
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.
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.
