Stored Procedure Runs Very Long
Jan 22, 2008
I think I may need help with query optimization.
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.
View 10 Replies
ADVERTISEMENT
Oct 9, 2001
I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?
I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?
Any advice appreciated.
Derek
************************************************** ***********************
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.sp_ValidateAIGL
GO
CREATE PROCEDURE dbo.sp_ValidateAIGL
@IGLPeriodIDInt,
@IGLProgramIDInt
AS
/* ************************************************** ************************
Name:sp_ValidateIGL
Author:CL
Date:19-Jan-2001
Notes:
************************************************** ************************ */
--SET NOCOUNT ON
DECLARE@TaxYearChar(5),
@FrequencyChar(1),
@PeriodNo Int,
@ProgramLogIDInt
SELECT@TaxYear = TaxYear,
@PeriodNo = PeriodNo,
@Frequency = Frequency
FROMtbl_IGLPeriods
WHEREIGLPeriodID = @IGLPeriodID
SELECT @ProgramLogID = (SELECT ProgramLogID FROM tbl_SYSProgramLogs WHERE IGLProgramID = @IGLProgramID AND IGLPeriodID = @IGLPeriodID)
CREATE TABLE #IGLErrors
(
KeyFieldChar(24),
ErrorIDInt,
DescriptionVarChar(255)
)
-- *** Global Non Program Specific Data Errors ***
-- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed...
IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'
SELECT * FROM #IGLErrors
-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
2,
'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''),
'<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode),
'<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(ExpenseCode, '') IS NULL
ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs
IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'
SELECT * FROM #IGLErrors
-- *** Program 1 and 2 errors ***
IF @IGLProgramID IN (1, 2)
BEGIN
-- CHECK - search for any records where the employee's COST_CENTRE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
1,
'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(CostCenter, '') IS NULL
ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in
-- the DEB_VIEW_APPOINTEE view...)
INSERT INTO #IGLErrors
SELECT DISTINCT
EMP_NO,
11,
'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
ANDPERIOD_NO = @PeriodNo
ANDTAX_YEAR = @TaxYear
SELECT * FROM #IGLErrors
-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table
INSERT INTO #IGLErrors
SELECT DISTINCT
ELEMENT,
12,
'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERE ELEMENT NOT IN
(
SELECT DISTINCT Element
FROM tbl_IGLElements
)
ANDPERIOD_NO = @PeriodNo
SELECT * FROM #IGLErrors
END
-- *** Add a error to indicate the number of errors ***
IF EXISTS (SELECT * FROM #IGLErrors)
INSERT INTO #IGLErrors
SELECT 0,
0,
'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!'
FROM#IGLErrors
-- Transfer the records to the ErrorsLog table ready for the user to view...
DELETE FROM tbl_SYSErrorsLog
INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description)
SELECT@ProgramLogID,
@IGLPeriodID,
KeyField,
ErrorID,
Description
FROM #IGLErrors
ORDER BY ErrorID
DROP TABLE #IGLErrors
SELECT *
FROM tbl_SYSErrorsLog
ORDER BY ErrorID
--SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public
GO
View 2 Replies
View Related
Jan 6, 2006
When i execute a stored procedure it generally takes about half a second to run but sometimes it takes 20 to 30 seconds. I am the only one using the server so I know it is not due to other traffic. I have looked at Profiler and nothing looks out of the ordinary. Another observation is that the slow ones are always near eachother. I will have about 10 fast executions and then 3 slow ones and then back to fast ones. Has anyone seen anything like this before?
View 5 Replies
View Related
Jan 8, 2008
Ok, I'll admit right off the bat that I never suspected that I'd ever raise this complaint, much less worry about how to fix the "problem" associated with it!
We're preparing to take a large set of changes (projects) to PeopleSoft Financials from development to test. The code is still somewhat rough, but it has been "desk checked" to ensure that it does what the developers think that it ought to do, and they've blessed it at that point. The code is now moving into the test phase, and the QA team is finding locking/blocking issues that we've never seen in this code before... Sort of a "lock avalanche" where no one process locks for very long, but many of them block one another to the point where applications actually "freeze" while almost never hitting a deadlock.
My solution was to create a "blitzkrieg" query / stored procedure that would periodically sample master.dbo.sysprocesses, master.dbo.sysdatabases, and apply one of the dm_ functions to gather information on locking, blocking, and deadlocking. My procedure runs nicely (it never hangs) and gets about 99.3% of the data that I want.
The problem is that the blasted query / stored procedure runs either too fast or too slow, depending on how you look at it. Because the dm_ function takes a few ms to run, there can be a situation where either a row appears as a false positive or as a missing row because of timing... Either the culprit shows up as a blocker, but by the time the victim spid is evaluated the block has cleared, or the row is skipped and by the time the victim is evaluated the block has occured.
The whole process runs in well under 100 ms when there is nothing to report, and I've never seen it run 200 ms yet under the worst conditions it has faced, so the code is fast... The problem is that I really don't want to try to enforce any kind of locking to resolve the issue, because that locking would impact performance and that is EXACTLY what I do NOT want to do.
Any suggestions?
-PatP
View 8 Replies
View Related
Oct 8, 2007
I have a stored procedure that syncs 2 databases. It runs fine if I go into the stored procedures and execute it manually, there are a few errors but it doesnt choke on them it completes with the desired end result. However if I create a job to run this dbo, it fails, The error log shows this:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
10/03/2007 16:06:00,sync,Error,0,MACHINENAME,sync,(Job outcome),,The job failed. The Job was invoked by User MACHINENAMEAdministrator. The last step to run was step 1 (sync).,00:00:19,0,0,,,,0
10/03/2007 16:06:00,sync,Error,1,MACHINENAME,sync,sync,,Executed as user: companyname. ... 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles __noelle(5281) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 0(5036) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) New DNN User [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 0000000(3795) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exists [SQLSTATE 01000] (Message 0) DNN User Exists [SQLSTATE 01000] (Message 0) Updaing Roles 007(8) [SQLSTATE 01000] (Message 0) aspnet User Exists [SQLSTATE 01000] (Message 0) aspnet Member Exis... The step failed.,00:00:19,14,3621,,,,0
So the error is 3621, and the severity is 14 which as I understand it means Insufficient Permissions.
I have already tried this:
Making the Job Agent service logon as MACHINeNAME/Administrator instead of System, giving the user here (companyname) more permissions, but I am at a loss as to why this runs ok when manually executed but fails when run as a job. I have created the job by scripting the dbo "Execute to a file" and having the job run that file as a step, I have also tried copying and pasting the code from the stored procedure into the job step. It always fails for the same reason.
I must say I am very new to SQL, I am a network admin and have inherited this database and server from another client and have to get this sync task to run daily at certain intervals.
Please can anyone shed light on this issue?
Thanks,
View 7 Replies
View Related
Apr 23, 2008
Performance issue.
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
View 15 Replies
View Related
Dec 29, 2003
I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.
All the tests where made using the Query Analizer.
What could be wrong?
Thanks a lot in advance.
View 13 Replies
View Related
Apr 19, 2007
Hi there everybody,
I am writing a sales managing software using C# in .NET 2005.
The program was used to work properly with sql server 2000.
I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.
But the problem is when program executes something like this:
EXEC [AddOrder] ...
two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine.
I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.
Any ideas?
Kind Regards: Ali
View 6 Replies
View Related
Nov 8, 2006
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
Thanks,
Jim
View 4 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
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
May 2, 2006
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
AND verbatim = @v
View 4 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
Mar 23, 2015
I have simple query which creates tables by passing database name as parameter from a parameter table .
SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .
Note** I am not passing any database of name '20'
Exception handler error :
ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
Error in SSIS
[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have sysadmin permission .
View 6 Replies
View Related
Feb 21, 2000
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.
View 2 Replies
View Related
Jun 13, 2007
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?
View 1 Replies
View Related
Mar 24, 2008
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??
Thanks.
View 6 Replies
View Related
Nov 8, 2015
I have a vendor database that has a stored procedure that runs a long time.Eventually, the database runs out of log space.
Setting the database to FULL and doing frequent log backups does not work.
The log does not get truncated during this log backups.
The stored procedure in question has SET XACT_ABORT ON statement at the beginning.
View 4 Replies
View Related
Feb 19, 2007
Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
View 3 Replies
View Related
Feb 19, 2008
Hi Guys,
I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.
When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.
I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.
Any idea what might be going on here?
View 7 Replies
View Related
May 13, 2002
My procedure compliles and runs. l 'm running it as 'exec Statement' it should prompt me for the start and end date but it does not. Where
have l gone wrong in my logic l've declared all the variables need. Please advice
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statement]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statement]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure Statement
As
Begin
declare @Startdate As datetime
declare @Enddate As datetime
declare @Customer_No As char(15)
declare @loanno As char(15)
declare @transaction_date As datetime
declare @transaction_type As char(3)
declare @reference As varchar(20)
declare @notes As varchar(255)
declare @transaction_amount As decimal (9,2)
declare @transaction_description As varchar(50)
declare @debit_amount As decimal (9,2)
declare @credit_amount As decimal (9,2)
declare @counter As int
declare @balance As decimal (9,2)
declare @user_changed As char(8)
declare c2 CURSOR FOR
SELECT loan_no
FROM loan
where customer_no = @Customer_No
ORDER BY loan_no
OPEN c2
FETCH NEXT FROM c2 INTO @loanno
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
declare c1 CURSOR FOR
SELECT transaction_record.loan_no,
transaction_record.transaction_date,
transaction_record.transaction_type,
transaction_record.reference_no,
transaction_record.notes,
transaction_record.transaction_amount,
transaction_type.[description]
FROM transaction_record
inner join transaction_type
on transaction_type.transaction_type = transaction_record.transaction_type
where loan_no = @loanno and transaction_Date between @startdate and @enddate and transaction_amount <> 0
ORDER BY transaction_date
OPEN c1
FETCH NEXT FROM c1
INTO @transaction_date,
@transaction_type,
@reference,
@notes,
@transaction_amount,
@transaction_description
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
If (@transaction_amount < 0)
Begin
set @credit_amount = @transaction_amount
set @debit_amount = 0
End
Else
Begin
set @debit_amount = @transaction_amount
set @credit_amount = 0
End
If (@counter = 0)
Begin
set @balance = @transaction_amount
End
Else
Begin
set @balance = @balance + @transaction_amount
End
insert into Statement (customer_no,loan_no,
transaction_date,transaction_type,transaction_desc ription,
reference, notes, debit_amount, credit_amount, balance,
user_changed, transaction_no)
Values (@Customer_No,
@loanno,
@transaction_date,
@transaction_type,
@transaction_description,
@reference,
@notes,
@debit_amount,
@credit_amount,
@balance,
@user_changed,
@counter)
set @counter = @counter+1
FETCH NEXT FROM c1
INTO @transaction_date,
@transaction_type,
@reference,
@notes,
@transaction_amount,
@transaction_description
End
CLOSE c1
DEALLOCATE c1
FETCH NEXT FROM c2 INTO @loanno
End
CLOSE c2
DEALLOCATE c2
END;
Go
View 1 Replies
View Related
May 24, 2004
This is my procedure and the error is incorrect syntax near '01'
DECLARE @returnDay int
--Looking at current date,
SELECT @returnDay = DatePart(day,GetDate())
--If is the 7th of the current moth then
If @returnDay = 24
EXEC master.dbo.xp_sendmail
@query = 'SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID',
@recipients=test@hotmail.com', @message='Submitting Results for the previous month',
@subject=' results for previous month', @attach_results = 'true', @separator = '/s'
SELECT @@ERROR As ErrorNumber
What am I missing here now, I am quite new to stored procedures
REgards
View 12 Replies
View Related
Jan 31, 2007
Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?
Thanks!
View 10 Replies
View Related
Nov 11, 2006
I'm generarintg a mail from a trigger, using xp_sendmail.
I use the query parameter to generate the body of the mail, the query is a procedure call with some parameters, one of this is a long string.
when the length is less that 128 work fine, but, when this parameter is longer than 128 charaters, return me the error 103 telling me that it has exceed 128 characteres, and do not send the mail.
I find this quite frustrating, the parameter is limited only to 128 characteres, what could I do.
Thanks in advance
Cheers.
Ale.
View 1 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Apr 21, 2007
When I execute a long running procedure, I get timeout errors when other users try to execute other procedures with UPDATE or INSERT statements.
I suspect that the other procedures are trying to execute DML statements on tables that are locked by the long running procedure.
I have a sharred trigger on all my tables that creates and updates records in tables AuditLogDetails and AuditLogParent for keeping a log of modifications. I suspect that tables AuditoLogDetails and AuditLogParent are locked by the long running procedure.
How can I change the LOCKING behavior of the long running procedure to fix the time out errors that I get?
The long running procedure is displayed below.
ALTER PROCEDURE [dbo].[spPostPresenceToHistory2]
@PostDate DateTime,
@Department Int,
@Division Int,
@Testing Bit = 0,
@XDoc xml OUTPUT,
@XDoc2 xml OUTPUT,
@ModifierID varchar(20),
@Comment varchar(200)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @PostCount Int,@PreCount Int,@DiffCount Int
IF @Testing=1
BEGIN
PRINT 'DELETE FROM History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
IF EXISTS(SELECT E.ID FROM History2_Personel E WHERE E.PostDate=@PostDate)
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN History2_Personel Ps ON H.Personel_ID=Ps.ID AND Ps.PostDate=@PostDate
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
ELSE
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN Personel As Ps ON H.Personel_ID=Ps.ID
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="History2_Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
PRINT 'INSERT INTO History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
INSERT INTO [dbo].[History2_Presence]
([ID]
,[User_ID]
,[Personel_ID]
,[Date_de_Presence]
,[Category_Motif_ID]
,[DateEntre]
,[Category_TypeDePresence_ID]
,[Travaille_de_Jour]
,[Heur_Supplementaire_Travaille]
,[prime_transport]
,[Tarif]
,[Jour_Travaille]
,[Montant_Supplementaire_Par_Heur]
,[Salair_par_Jour]
,[Salair_Minimum]
,[IsAutomaticRec])
SELECT [P].[ID]
,[P].[User_ID]
,[P].[Personel_ID]
,[P].[Date_de_Presence]
,[P].[Category_Motif_ID]
,[P].[DateEntre]
,[P].[Category_TypeDePresence_ID]
,[P].[Travaille_de_Jour]
,[P].[Heur_Supplementaire_Travaille]
,[P].[prime_transport]
,[P].[Tarif]
,[P].[Jour_Travaille]
,[P].[Montant_Supplementaire_Par_Heur]
,[P].[Salair_par_Jour]
,[P].[Salair_Minimum]
,[P].[IsAutomaticRec]
FROM [dbo].[Presence] AS P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND NOT EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PostCount-@PreCount
SET @xdoc.modify('
insert <Table Name="History2_Presence" RecordAdded="{ sql:variable("@DiffCount") }"/> as last into /Inserted_Records[1]
')
END
IF @Testing=0
BEGIN
PRINT 'DELETE FROM Presence'
EXEC sp_SetPostingProperties 'Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM Presence
DELETE FROM Presence FROM Presence P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
COMMIT TRANSACTION
END
View 1 Replies
View Related
May 20, 2008
Dear All
I have a stored proc that is taking way too long to process, over 50 minutes.
What I need to do is go through 180K rows, and if the projId and langid is the same, increment 1 to a value, if not, reset the incrementer
I am doing the following at the moment
DECLARE @Id int, @ProjectId int, @LangCode char(6)
DECLARE @CurProjectId int, @CurLangCode char(6)
DECLARE @incrementer int
DECLARE Order_cursor CURSOR FOR
SELECT Id, langCode, projectid FROM #tempTable
OPEN Order_cursor
SET @incrementer = 1
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@CurProjectId = @ProjectId)
AND (@CurLangCode = @LangCode)
BEGIN
SET @incrementer = @incrementer + 1
END
ELSE
BEGIN
SET @incrementer = 1
END
UPDATE #tempTable
SET edbOrder = @incrementer
WHERE Id = @id
SET @CurProjectId = @ProjectId
SET @CurLangCode = @LangCode
FETCH NEXT FROM Order_cursor INTO @Id, @LangCode, @ProjectId
END
CLOSE Order_cursor
DEALLOCATE Order_cursor
Is there a better way?
Thanks
Johann
View 2 Replies
View Related
Oct 27, 2006
How does one prevent a long running procedure form crapping out in CLR?
I am trying to do a pull from a distant data source and it works, except I have to break down my stored procedure call into several smaller calls. I would like to do everything in one shot, but I get the thread abort exception when I try to get a lot of data.
Any ideas?
Thanks.
View 3 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Aug 2, 1999
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.
View 4 Replies
View Related
Nov 20, 2006
Hi All,
I've got a problem with increasingly long parameter lists for SProcs... Especially when one calls another SProc, and so on. Is there any way around this? Like can you dynamically construct a string and pass that? I'm just looking to see if more experienced players have found ways around this, or have just dealt with it by using well formed code.
Thanks
Chris
View 7 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
