|
The transaction has to rollback, so that takes as long (or even longer) than the transaction had been running when you killed it. Everything should go back to normal eventually, if not then you may have to restart the sql server instance.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You didn't mention the lock type on the locked row. If there is X lock present, you cannot select from that row (in normal situation). You'll have to wait that the X lock is gone or kill the transaction that's holding the lock (if it's not expected to hold it).
The problem you're facing may be a result of a transaction that isn't ended as it was supposed to (either rollback or commit).
Also you may want to read through the article I wrote.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Below is my store procedure
when i call GetPurchaseOrders null, 2,null,null,'Vendor','Descending'
it gives error
"Conversion failed when converting datetime from character string"
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <author,,name>
-- Create date: <create>
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetPurchaseOrders]
-- Add the parameters for the stored procedure here
(
@Order_no bigint,
@VendorId int,
@FromDate datetime,
@ToDate datetime,
@Sort_by varchar(15),
@Sort_order varchar(15)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT p.Order_No as OrderNo,v.Vendor_Name as [Vendor], Order_date as OrderDate from
Purchase p
join
Vendor as v
on p.Vendor_ID=v.Vendor_ID
where p.Order_No=COALESCE(@Order_no,p.Order_No)And
p.Vendor_id =COALESCE(@VendorId , p.Vendor_id )And p.delivered='N'
ORDER BY
CASE @Sort_by
WHEN 'Order No' THEN p.ORDER_NO
WHEN 'Date' THEN p.order_date
WHEN 'Vendor' THEN v.Vendor_Name
END desc
end
“You will never be a leader unless you first learn to follow and be led.”
–Tiorio
"Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
|
|
|
|
|
Dear Tiorio,
Since you were sorting on the basis of Parameter provided and it deals
with 3 columns of different data type that is why you were getting the error:
"Conversion failed when converting datetime from character string"
Just Convert the columns to Varchar will give you the desired results of yours.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <author,,name>
-- Create date:
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetPurchaseOrders]
-- Add the parameters for the stored procedure here
(
@Order_no bigint,
@VendorId int,
@FromDate datetime,
@ToDate datetime,
@Sort_by varchar(15),
@Sort_order varchar(15)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT p.Order_No as OrderNo,v.Vendor_Name as [Vendor], Order_date as OrderDate
from
Purchase p
join
Vendor as v
on p.Vendor_ID=v.Vendor_ID
where p.Order_No= COALESCE(@Order_no,p.Order_No) And
p.Vendor_id = COALESCE(@VendorId , p.Vendor_id ) And p.delivered='N'
ORDER BY
CASE @Sort_by
WHEN 'Order No' THEN CONVERT(VARCHAR, p.ORDER_NO)
WHEN 'Date' THEN CONVERT(VARCHAR, p.order_date) WHEN 'Vendor' THEN v.Vendor_Name
END desc
end
Hope this answers your question.
Thanks and Regards,
Umair Feroze
|
|
|
|
|
Looks like it may be coming from the Order_date field. Is that field's datatype datetime or a character type?
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hey Guys,
Here is my issue -
I have a table with 3 date fields. (Entry Date, Pending Date, Accounting Date) -> in my GridView I need to represent this value in 1 column called (Report Date). My rules are if Accounting Date is null use Finalized Date, if Pending date is null use Entry Date. This rule has to work on a row by row basis as I am returning 100's of rows.
Now if this is better done in the code - I can do that also.
Any help would greatly be appreciated.
Thank you.
|
|
|
|
|
Well, depending on whether or not your DBMS supports it, you could use the CASE statement to accomplish this, or you could use a COALESCE method,e.g.
SELECT COALESCE(AccountingDate, PendingDate, EntryDate) AS RuleDate ... This assumes that one of these columns has a date value in it. Basically, coalesce falls through from one entry to the next until it finds a none-null value. BTW - I'm assuming that you got your logic a bit wrong in your statement and you mean Pending Date and not Finalized Date.
|
|
|
|
|
CASE WHEN Accounting Date IS NULL THEN Finalized Date WHEN Pending date IS NULL THEN Entry Date END
etc.
|
|
|
|
|
Hi Everyone,
Ive got a Small Sql Server DB which Holds 3 Variables that i want To Update But Not Necessarily togther,Meaning: One time i want to update the first two Variables, the other one ill need to update the other 2, which leads me up to 9 options for updating queries..
Now my question is.. is there any way to write 1 query (With Stored Procedure/not) that somehow ,someway "sees" that if no parameter was sent to update , it will use a default value which ill define ?
I hope i explained myself well
thanks alot !
|
|
|
|
|
I'm not sure that I completely understand what you're trying to do, so this may not be what you're looking for. You can supply a default for each parameter to a stored procedure where the parameters are declare at the top of the procedure, but simply putting an equal sign ( = ) after the parameter's data type and then putting the value; like so:
create procedure dbo.DoSomething
@param1 int = -1,
@param2 datetime = getdate(),
@param3 char(1) = 'M'
as
....
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Thanks For your reply Ben.
thats a half of what i ment,
I'll try to rephrase myself.. is there any way i can check if a null variable has been sent into the query and to give another value to it in the stored procedure?
For example: TextBox1 Contains 1 Variable.. when i send it empty (and sometimes i need it empty) i want to give it another value.
Thanks!
|
|
|
|
|
Try something like this (not necessarily correct code, just off the top of my head)
create proc1 (@var1 int = null, @var2 int = 1) -- this will default @var1 to null and @var2 to 1
as
update table1
set col1 = coalesce(@var1,99), --set to 99 if no @var1 supplied
col2 = coalesce(@var2,col2) -- set to its current value if @var2 is null
Hopefully this will guide you to what you want.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
What I understood from description is you want to check whether the variables provided are null or not. If any of these is null put default value. If is that so, below is the answer:
CREATE PROC sp_TestSp (@var1 int = 0, @var2 int =0 , @var3 varchar(50) = 'Default') -- this will set default value of @var1 to 0 and @var2 to 0 and @var3 to "Default"
AS
UPDATE table1 set col1 = @var1, col2 = @var2, col3 = @var3
OR
CREATE PROC sp_TestSp (@var1 int = 0, @var2 int =0 , @var3 varchar(50) = 'Default') -- this will set default value of @var1 to 0 and @var2 to 0 and @var3 to "Default"
AS
UPDATE table1 set col1 = ISNULL(@var1, 0), col2 = ISNULL(@var2, 0), col3 = ISNULL(@var3, 'Default')
Hope this answers you question.
Umair Feroze
|
|
|
|
|
Why repeat basically what I put 7 hours ago?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Alot
thats exactly what i wanted!
|
|
|
|
|
hello anyone tried to install SQL 2005 Express with Reporting Service on Vista Home Premium?
It's driving me nuts...[^]
I checked and double checked,
1. IIS installed, ASP/ASP.NET enabled, IIS6/IIS6 Metabase ...etc all there.
2. BEFORE install SQL 2005 Express, I made sure this patch is installed.
Update for Windows Server 2008 (KB950636)[^]
3. I made sure this is intalled AFTER SQL 2005 Express installed and BEFORE I try to reinstall to renable "Reporting Service" - Microsoft SQL Server 2005 SP2[^]
Nothing seems to work.. installer still stuck complaining IIS not installed. Perhaps home edition Vista is inherently incompatible with SQL2005 Reporting Service.
dev
|
|
|
|
|
You were able to run IIS on Vista Home Premium? I have had success with Express on XPpro and Vista Ultimate edition only.
Why are you running a server update on a Vista box?
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
One of our clients has just developed a set of coding standards for their .NET and SQL Server application development. For the SQL Server standards, they say the following regarding error handling:
-----------start of quote---------------
Error Handling
- Should be used in every stored procedure, with an Error Handling section
at the bottom of the procedure
- Stored procedures should return the success or failure of the stored
procedure by returning 0 (zero) upon success, or return the error number upon
error.
- @@ERROR should be checked after Every INSERT or UPDATE to deterimine the
success or failure of that action.
- @@ROWCOUNT should be checked after every INSERT or UPDATE if at least one
or more records were expected to be affected.
DECLARE @Error int, @RowCount int
T-SQL here
RETURN 0
EH:
RAISERROR(@Error, 16, 1)
RETURN @Error
-----------end of quote---------------
Does this sound like a good idea? Having an error handler in every stored procedure seems excessive.
Honestly, I'm of the opinion that error handling for the sake of error handling is a waste of time and only serves to obsficate your code. The only reason why you should handle an error is if you're going to do something meaningful with the exception. For example, you know what the error is and how to fix it. Or maybe you want to log it to the database. Or you need to rollback a transaction. But if all you're going to do is catch it and raise it up the call stack, that's pointless code because the error will bubble up the callstack anyway.
Do you agree or disagree? Why or why not?
|
|
|
|
|
Seems pretty much like the standards everywhere I've contracted in the last few years. If you get an error you can enhance the message to help track down the problem, so no, I don't think its a waste of time. My biggest gripe is that you can't (pre 2005) trap ALL errors so you can handle them. I've spent hours trying to track down such elusive messages as 'string or binary data would be truncated' 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
> If you get an error you can enhance the message to help track down the problem
What sort of information would you include to enhance the error message? For example, what would you change about this stored procedure?
--air code
CREATE PROCEDURE GetCustomerCount
AS
SELECT COUNT(*) FROM CUSTOMERS
RETURN 0
EH:
RAISERROR(@Error, 16, 1)
RETURN @Error
|
|
|
|
|
Obviously, in this example there is nothing, but if you have anything more complex, for example a proc which has several update statements, then it is useful to know where the error originated. Another example would be a series of nested procs - by including the proc name in the error you know where it originated.
As with any set of standards there are cases for and against - for example, stylecop in c# insists that the using statements come after the namespace declaration, but if you create a new class they are generated before.
As a long time contractor I have worked at many companies, they all have slightly different standards, some good, some not so good, but at the end of the day they are called standards for a reason - consistency. The overhead they cause is minimal and they make it easy for someone to pick up the code and see what is happening.
If you think the standard is not good you need to raise it with the powers that be, but in your original post you state "One of our clients..." and at the end of the day they call the tune.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
A lot of stored procedures are simple, one-statement CRUD operations that probably don't require error handling, which is why I think that mandating that every single sproc have an error handler is excessive.
These coding standards are brand new. If we get the contract, this will be the very first project to use them. So, consistency with existing applications is not an issue.
|
|
|
|
|
Its up to you and your client. I still maintain they are called standards for a reason.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Sure, I believe in standards too. In fact, my standards that I used on the last project are actually more strict. But unless someone can come up with a valid justification, this particular standard is a bad one.
|
|
|
|
|
Wait a second! Does this error handling strategy even work? When I execute the following stored procedure, I get an error on the RAISERROR statement.
CREATE PROCEDURE dbo.spErrorHandlingTest1<br />
AS <br />
BEGIN<br />
<br />
DECLARE @Error INT<br />
DECLARE @myInt INT<br />
<br />
SELECT @myInt = 1/0<br />
<br />
SELECT @Error = @@ERROR<br />
<br />
IF @Error > 0 GOTO EH<br />
<br />
RETURN 0 <br />
EH: <br />
RAISERROR(@Error, 16, 1)<br />
RETURN @Error<br />
<br />
END
Msg 8134, Level 16, State 1, Procedure spErrorHandlingTest1, Line 8
Divide by zero error encountered.
Msg 2732, Level 16, State 1, Procedure spErrorHandlingTest1, Line 16
Error number 8134 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
