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.
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.
I am trying to run xp_cmdshell from the Query Analyzer using SQLServer 2000 running on Windows 98.It seems like it should be simple - I'm typingxp_cmdshell 'dir *.exe'in the Query Analyzer in the Master db. I'm logged in as sa.The timer starts running and never stops. No error message.Can anyone PLEASE help me with this? Any suggestions would beappreciated. Are SQL Server 2000 extended stored procedures notsupported in Windows 98? I've tried searching the Knowledge Base butcan't find anything.Thanks!
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
-- *** 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
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!!
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?
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.
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.
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,
Hi I have a stored procedure in SQL Server 2005. It make a backup of a database and restores it to a different name.I use ASP.NET and Framework 1.1.It works really fine when I use SQL Server 2000. But!When trying to do the same thing on SQL Server 2005, the database seems to be created "half way" I can see that the database is created, but after the name is the text Restoring....It never finish restoring.... and nothing shows in the server logs. Any ideas?Differences between SQL Server 2000 and SQL Server 2005 that I must be aware of?Priviliges?ConnectionString parameters?Drivers? I'm using .NET Framework 1.1ODBC (SQL Native Client) Here is the Store procedure code:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[CreateProjectDataBase] @AFModelDatabaseProject varchar(200), @TemplateDbBackupFileAndName varchar(200), @DatabaseName varchar(200), @DataFilePathAndName varchar(200), @LogFilePathAndName varchar(200) AS
BACKUP DATABASE @AFModelDatabaseProject TO DISK = @TemplateDbBackupFileAndName WITH INIT RESTORE DATABASE @DatabaseName FROM DISK = @TemplateDbBackupFileAndName WITH MOVE 'AdressTNG_Project_Data' TO @DataFilePathAndName, MOVE 'AdressTNG_Project_Log' TO @LogFilePathAndName
and here is how it is called from within .NET:this.odbcCreateDataBaseCommand.CommandType = System.Data.CommandType.StoredProcedure; this.odbcCreateDataBaseCommand.Parameters["@AFModelDatabaseProject"].Value = afModelDataBaseName;this.odbcCreateDataBaseCommand.Parameters["@TemplateDbBackupFileAndName"].Value = TemplateDbBackupFileAndName; this.odbcCreateDataBaseCommand.Parameters["@DatabaseName"].Value = dbName;this.odbcCreateDataBaseCommand.Parameters["@DataFilePathAndName"].Value = DataFilePathAndName; this.odbcCreateDataBaseCommand.Parameters["@LogFilePathAndName"].Value = LogFilePathAndName;this.odbcCreateDataBaseCommand.CommandText = "{ CALL CreateProjectDataBase(?,?,?,?,?) }"; this.odbcCreateDataBaseCommand.ExecuteNonQuery(); RegardsTomas
The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.
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.
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
Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.
Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.
This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.
I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.
As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.
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 .
Hi, the title of this topic about summarises it. I have a dev-machine with SQL Server 2005 Standard edition on it. My host runs SQL Server 2000. What are my options? Is there some sort of backward compatibility? Can i export as 2000? I have no idea and i can't find anything about this particular problem. Thanks
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)
'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.
I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.
I could pin the problem down to this query:
DECLARE @Package_ID bigint
DECLARE @Email varchar(80)
DECLARE @Customer_ID bigint
DECLARE @Payment_Type tinyint
DECLARE @Payment_Status tinyint
DECLARE @Booking_Type tinyint
SELECT @Package_ID = NULL
SELECT @Email = NULL
SELECT @Customer_ID = NULL
SELECT @Payment_Type = NULL
SELECT @Payment_Status = NULL
SELECT @Booking_Type = NULL
CREATE TABLE #TempTable(
PACKAGE_ID bigint,
PRIMARY KEY (PACKAGE_ID))
INSERT INTO
#TempTable
SELECT
PACKAGE.PACKAGE_ID
FROM
PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID
LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID
AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)
AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)
AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)
AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)
-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented
--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'
GROUP BY
PACKAGE.PACKAGE_ID
DROP TABLE #TempTable
The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment. As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second. I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key. Maybe you folks can spot the error / mistake / wrong type of doing things easily. I would appreciate any help you can offer me to solve this problem.
On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:
942659 (http://support.microsoft.com/kb/942659/) FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000
Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.
I had few stored procedures which were working in SQL server 2000. I upgraded SQL server to 2005 and one stored procedure does not work. It gives the Error Msg 102. "Syntax error near ',' " I already tried set quoted identifiers ON & OFFAny help would be appriciated.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
I've done some searching, asking of friends, and searching every log file and event file I can think of. So now I'm coming here.
Recently I moved some of our databases from an NT4 box running SQL 7 to an Advanced 2000 box running SQL 2000. The web server is still on an NT4 box. It seems that about three times a day or so ASP type files will hang on the webserver. This server hosts different sites and all ASP type files will stop even if some of them hit the old SQL7 server.
Right now I moved the web server back to looking at the SQL 7 machine and things are going fine.
Can anyone offer me a direction to start looking? Why is it working fine to the old stuff but not the new stuff? Is there an issue with NT4 with its IIS trying to talk to an Advanced 2000 with its SQL2000?
Hello , I'm a newbie,I programmed a store procedure,and when it runs in query analyzer, I get results very fast,but when I add it in a job,then this job run very very slow, (I add records and delete records in the SP), WHY?
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
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
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
Hi,The SQL server 2000 Server hangs some times. It is not periodic. It isnot specific in any queries, which are taking more time to execute.Because, it is occurring for different types of applications on thesame server on different machines. For the same applications when wehad the SQL Server 7.0 we didn't have any problem.O/S: Windows 2000 advanced serverServer: Dell power edge 2600 4 way server with 2 GB RAMDB: SQL 2000 - Enterprise edition (Normal default installation)- Noservice packs.Are there any server settings to be done...?Thanks in advance..RegardsSeni
I am working in Powerbuilder and SQL Server 2000. Within the application I dynamically Drop then recreate a view named view_selection_list. When another user accesses any screen using view_selection_list the screen will hang on the statement "If Exists (SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list". I also went directly onto the database ran select * from view_selection_list from Query Analyzer. It hangs when the original user creating the view is still active. I know that the issue is locking. I don't know how to fix it.
For example ; String ls_sql ls_sql="If Exists ( SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list " Execute Immediate :ls_sql;
ls_sql="Create View view_selection_list as " Case 'State' ls_sql+=" Select distinct proj_id,'State - '+proj_state title from project where proj_state='"+is_data+"'"
Case 'Project' ls_sql+=" Select distinct proj_id,'Project - '+proj_nam title from project where proj_id='"+is_data+"'"
Case 'All Active Projects' ls_sql+=" Select proj_id,'Project -' +proj_nam title from project where proj_status = 4 and signed_acq_agmt = 'Y' "
End Choose
Execute Immediate :ls_sql;
The SQL Server connection in the application is: SQLCA.DBMS = "OLE DB" SQLCA.ServerName="acq" SQLCA.LogPass ="*******" SQLCA.LogId = "acq" SQLCA.Lock = "RU" SQLCA.AutoCommit = False SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='FSRFIN103'"
I hope someone out there can help, cos i really need it. We have a 2 node cluster + SAN that will be used as our SQL 2000 servers. We have setup the cluster ok, and have tested failover with no problems. However, when we try to install SQL 2000, it just hangs. I have copied the enterprise cd to the local drive, and am installing from there. We run the setup.bat, create the Virtual Server, and assign an ip address. At this point, it just hangs. Upon checking the SQLSTP.LOG file, this is the last entry in the log: Begin Action : DialogShowSdDiskGroups
I have found the Microsoft Technet Article 293788, which gives me a possible soloution, but we dont have any Resource Names or Groups that have the same name.
Can someone PLEASE give me an answer on how to fix this, or at least point me in the right direction.
Hi,Sorry, this is a very easy question. I have to create a storedprocedure in a SQL SERVER Database.I have read lots on MSDN about the syntax of this stored procedure :ie.CREATE PROCEDURE spGetContctDetailsAS-- get everything out of contact tableSELECT * FROM tblContactbut I cannot find anywhere how to actually add this procedure, so thatit is accessable in my VB.NET program.What I am asking is : what screen do I type this into? Is it QueryAnalyzer, if so where?Its got me oh so confused. When I've got time i'll read all about SQLServer, but for this morning I need to know how to simply add thatStored Procedure to my database.Thanks LOADS for any help.
I have a MSSQL2000 box with a large database containing circa 150 SP's.I want to move the DB to another SQL machine, but many of the SP's havereferences to the name of the current machine. Is there any way tobatch edit the procedures to refer to the new server, disassociatingthe old machine completely?Tia,Graham
