Why The Try/Catch Block Cannot Capture This Kind Of Deadlock?

Feb 26, 2008

Hi everybody:

I wanted to handle with the deadlock issue by using Try/Catch, the the try/Catch block cannot capture this kind of deadlock:


code in connection1:
declare @i int
set @i = 0
WHILE (1=1)
begin
ALTER PARTITION SCHEME PartationedTableA NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION PartationedTableA () SPLIT RANGE (14661)
ALTER PARTITION FUNCTION PartationedTableA () MERGE RANGE (14661)
end


code in connection2:
declare @rowcount int
while 1=1
begin

begin try
select @rowcount =count(0) from PartationedTableA where col1=50021
end try
begin catch
print error_number()
end catch
end

run them at the same time in SSMS, the code in cn2 would get a error in several seconds:
Msg 1205, Level 13, State 55, Line 10
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I tried using TRY/CATCH to capture anoter deadlock whose Error_State is 45, and it works fine.

Does anybody know why Try/Catch cannot capture my deadlock, does the different state make senses?

Thanks.


View 7 Replies


ADVERTISEMENT

What Kind Of Errors Will Try / Catch Ignore ?

Apr 10, 2008

Ive started using try/catch in my t-sql code now and I rather like it, since im a C# developer. I read that some errors with an error code below 10 will not cause the catch block to be entered. What kind of errors does this include ?

View 2 Replies View Related

Try.. Catch Block

Dec 11, 2006

HI,
i'm trying to execute some sql using the Try.. Catch blocks.

Following code does not execute in Catch Block

Begin
begin try
insert into dbo.Test values (1,'aaa')
-- here we are inserting int value in identity field...
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO


Whereas the following block works fine and the Catch block executes.

Begin
begin try
Select 1/0
--This causes an error.
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO





Any idea why is it so?

View 5 Replies View Related

Try Catch Block

Apr 12, 2006

Hi:

one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated.
I am unable to understand as to why the CATCH block is not executed when an INSERT is made
On table T3 which is dropped after the first transaction.

The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2
Invalid object name 't3'.

BOL says TRY€¦CATCH constructs do not trap the following conditions:
Warnings or informational messages with a severity of 10 or lower.
Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.


Here is the script.




use tempdb
go

create table t1 (a int)
create table t2 (b int)
create table t3 (c int)


Begin tran

Insert into t1 values (1)
Insert into t2 values (2)
Insert into t3 values (3)

IF @@error <> 0
Rollback tran
else
commit tran
-------------------------------------------------------------

Select * from t1
Select * from t2
Select * from t3
-------------------------------------------------------------

Drop table t3

-------------------------------------------------------------

Set xact_abort on
Begin try
Begin tran insertNow

Insert into t1 values (1)
Insert into t2 values (2)

save tran insertNow

Insert into t3 values (3)
commit tran insertNow
End try

Begin Catch

IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION insertNow
END;

-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION insertNow
END

End Catch



View 9 Replies View Related

Transactions & TRY CATCH Block.

Oct 10, 2006

Can anyone give a template for a stored procedure which involves a transaction and has a TRY CATCH block too...

Thanks in advance.

View 1 Replies View Related

TRY..CATCH Block Not Cetching RAISERROR()

Sep 30, 2007

 I guess this is a common problem because I ran into a lot of threads concerning the matter. Unfortunately, none of them helped my situation.I am throw a RAISERROR() in my sql and my vb.net try catch block is not detecting an error. SELECT '3'
RAISERROR('testerror',10,5)  Dim con As New SqlConnection Dim _sqlcommand As New SqlCommand con = New SqlConnection(Spiritopedia.Web.Globals.Settings.General.ConnectionString) _sqlcommand.CommandType = Data.CommandType.StoredProcedure _sqlcommand.CommandText = "TestFunction"
_sqlcommand.Connection = con


'The value to be returned
Dim value As New Object
'Execute the command making sure the connection gets closed in the end

Try

'Open the connection of the command
_sqlcommand.Connection.Open()
'Execute the command and get the number of affected rows 'value = _sqlcommand.ExecuteScalar().ToString()

value = _sqlcommand.ExecuteScalar()


Catch ex As SqlException Throw ex Finally

'Close the connection
_sqlcommand.Connection.Close()
End Try
  

View 6 Replies View Related

Clearing Exception In Catch Block

Sep 3, 2004

Is there any way to clear out the exception from a previous Try/Catch block if I am nesting another Try/Catch block within it. I am executing a SQL Command and based on the Error number coming back decide whether or not to execute various other Sql commands. Now the Outer exception seems to be taking precedence over the Inner try block, and even though the code is stepped through for the inner try block it is never executed due to the Parent Exception. Is there any way to clear the exception received from the outer Try block? Here is a snippet of code:


Try

Cmd = New SqlCommand(Sql, Con)

Cmd.ExecuteNonQuery()

Catch t as SqlException

if t.Number = "2601" then

sql_upd = "<Text>"

Try

Cmd_upd = New SqlCommand(Sql_upd, Con)

Cmd_upd.ExecuteNonQuery()

Catch b as Exception

response.write("<Text>")

End Try

End If

End Try
Thanks,

View 3 Replies View Related

T-SQL (SS2K8) :: Try Catch Block In While Loop

Feb 25, 2015

I encountered a werid bug that I can't figure it out in my stored procedure.Here's some sample code the can represent the scenario

Create Proc sp_test
@DeptID Int
as
Begin
Declare @i int=0
Declare @Count int=(Select count(*) from Total)
while(@i<@Count)

[code]....

In the above code Total is a table that has employee name and its department ID and row_number info.The above code should list all employee info that belongs to one DEpt.but after I placed a try catch block the select statement returns no records.If I removed the try catch block it behaves correct.For example If three records reside in the Total table for a certain DeptID.

I expect the outPut will be
Name age salary
Mike 35 $60006
Tom 50 $75000
Frank 55 $120000

View 6 Replies View Related

TRY...CATCH Block Not Working Properly

May 25, 2008

Can someone please help me figure out why this trigger causes a "Timeout expired" error after the first try? What I'm trying to do is create an incrementing default value for the projectid field. I have a unique index on the field and for some reason, the INSERT statement won't run inside the TRY block, even though the WHILE loop creates a unique id. It's acting like the loop isn't incrementing, but the PRINT statements prove that the loop does work, but the INSERT statement doesn't work after a succesful first try (i.e. NEWPROJ-1 is inserted correctlly). Anyways, any help would be appreciated.





Code Snippet
GO
/****** Object: Trigger [Projects].[CreateID] Script Date: 05/25/2008 14:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Projects].[CreateID]
ON [Projects].[tblProjects]
INSTEAD OF INSERT
AS
DECLARE @projectid varchar(25)
DECLARE @projectname varchar(100)
DECLARE @projectepskey int
DECLARE @maxcount int
SELECT @maxcount = 1

BEGIN
SET NOCOUNT ON;
SELECT @projectname = projectname
FROM inserted
SELECT @projectepskey = projectepskey
FROM inserted

SELECT projectid
FROM Projects.tblProjects
WHERE (projectid = 'NEWPROJ')

IF (@@RowCount = 0)
BEGIN
SELECT @projectid = 'NEWPROJ'
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
END
ELSE
BEGIN
WHILE @maxcount >= 1
BEGIN

BEGIN TRY
SELECT @projectid = 'NEWPROJ-' + CONVERT(varchar, @maxcount)
PRINT @projectid
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
BREAK
END TRY
BEGIN CATCH
SELECT @maxcount = @maxcount + 1
PRINT CONVERT(varchar, @maxcount)
CONTINUE
END CATCH;
END
END
END









View 4 Replies View Related

T-SQL (SS2K8) :: Catch Block With GOTO Statement?

Mar 6, 2014

I have a Stored Proc which populates a table and then uses BCP to output the table into a flat file and lastly ftp the file out to a remote site.

I'm trying to update the error handling as I first wrote this script on SQL2000 and it has now moved to SQL2008r2. The stored proc looks something like this:

BEGIN TRY
BEGIN TRANSACTION
<A whole bunch of inserts and updates>
COMMIT TRANSACTION
END TRY
BEGIN CATCH
<Error handling>
ROLLBACK TRANSACTION
END CATCH
BEGIN
<xp_cmdshell, BCP, FTP stuff>
END

What I need to do is jump to the end of the script if an error invokes the CATCH block, so the xp_cmdshell stuff is not exicuted. Can I simply put a GOTO statement to take it to the end in the CATCH block, or do I have to set a variable in the CATCH block then test the variable outside the CATCH block or indeed is there a better way to simply terminate the script following the ROLLBACK?

[URL]

View 7 Replies View Related

Raising Errors In A Catch Block In SQL2005

May 3, 2006

When a catchable error occurs in a try block, control is passed to theassociated catch block. While I am then able to examine properties ofthe error using such functions as ERROR_NUMBER() in the catch block,the error-logging scheme used in my company requires that the actualerror be raised so that it can be picked up by components in the nexttier. I appear to be unable to do that -- I can raise some other errorthat has all of the properties of the original except the error number.For example, division by zero raises error 8134. If the code thatproduces that error is enclosed in a try block, I seem to be unable toraise that error from the catch block. Of course, if I never used thetry block to begin with this wouldn't be a problem, but then wewouldn't have the advantages of this structure as it applies to othererrors, some of which we may wish to handle differently. WhileTry-Catch looks great as a way of allowing us to handle errors in acustomized way and to avoid having all errors be passed up to ourmiddle tier, it seems that we are unable to pass ANY such errorsforward, and that is a problem for us, too. Is my understanding ofthis situation correct, or is there some way around this problem, e.g.,is there any way I could have raised error 8134, in the above example?Thanks.

View 2 Replies View Related

Will Try Catch Capture All Errors?

Mar 20, 2008

I use a CLR external function to call a payment gateway, for some reason, I could not get any status sometime, check the following code, I expect I get either -2 or -3 in status no matter what error happens - include the gateway error or the CLR error since I have try catch there. Do you think the following code could miss any problem?


begin try
....

select @postresult=dbo.linkclr_u_webpost(@posturl,@postdata)

update link_payments set ccagentcode=@ccagentcode,status=-2,postresult=@postresult where id=@paymentid


....
end try
begin catch
select @message=
'<br>ErrorNumber: '+cast(ERROR_NUMBER() as varchar)+char(13)+
'<br>ErrorSeverity: '+cast(ERROR_SEVERITY() as varchar)+char(13)+
'<br>ErrorState: '+cast(ERROR_STATE() as varchar)+char(13)+
'<br>ErrorProcedure: '+isnull(ERROR_PROCEDURE(),'')+char(13)+
'<br>ErrorLine: '+cast(ERROR_LINE() as varchar)+char(13)+
'<br>ErrorMessage: '+isnull(ERROR_MESSAGE(),'')+char(13)

update link_payments set status=-3,postresult=@message where id=@paymentid
return -1
end catch

View 3 Replies View Related

Begin Catch Block Executed When Try Completes Without Error

Dec 10, 2007



I was just debugging a stored procedure visual studio and I was surprised with what I was watching.

I'm running SQL 2005 and visual studio 2005. I have a set of nested try/catch blocks.

It fails on the first try and drops into the catch block just as it should. Within the catch block a dynamic sql statement is created. A try block begins and attempts to exec(@sql). It then drops to the catch block. However, I copy the statement into SQL Management Studio and it runs without error everytime.

Why is it dropping into the catch block? Logically it should just complete executing the statement continue without going into the catch block.

Do I have to reset the raiserror values between try blocks or something?



--Thanks--



View 1 Replies View Related

How To Stop Execution Of Stored Procedure If Error Occurs In Try/catch Block

Mar 3, 2008



Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.

Here is a simple example of what I have:




Code Snippet
begin try

if not exists (select * from sys.database_principals where [name] = 'flea')

raiserror('flea not found', 16, 1)
end try
begin catch

declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select

@ErrorMessage = error_message(),
@ErrorSeverity = error_severity();
raiserror(@ErrorMessage, @ErrorSeverity, 1);
end catch
go

begin

print 'hello world'
end






At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!

Thanks,
Flea

View 5 Replies View Related

Try Catch Doesn't Catch Errors Inside A Data Flow Transformation Script Component

Feb 15, 2007

Hi,

I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:

a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?

Hope someone could help.

View 3 Replies View Related

T-SQL (SS2K8) :: Why Block Scope Variables Exist Outside Of Block

Dec 3, 2014

I have the following stored procedure to test scope of variables

alter proc updatePrereq
@pcntr int,
@pmax int
as
begin

[Code] ....

In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script

declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go

As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?

View 1 Replies View Related

Mirroring :: Email Deadlock Information When A Deadlock Occurs

Nov 10, 2015

Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.

View 5 Replies View Related

Try Catch Does Not Catch Exception

May 15, 2007

hi all,



All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement



my problem catch does not catch the error. There is no way to find out teh what is causing this error



SqlCeConnection sqlcon = new SqlCeConnection("

Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"

);

SqlCeCommand sqlcmd = new SqlCeCommand();

sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');

sqlcmd.Connection = sqlcon;

SqlCeDataReader sqldr = null;

try

{

sqlcon.Open();

//use nonquery if result is not needed

sqlcmd.ExecuteNonQuery(); // application crashes here



}

catch (Exception e)

{

base.myErrorMsg = e.ToString();



}

finally

{

if (sqlcon != null)

{

if (sqlcon.State != System.Data.ConnectionState.Closed)

sqlcon.Close();

sqlcon.Dispose();

}

}//end of finlally

View 4 Replies View Related

What Kind Of Project?

Jul 18, 2007

I'm not looking for details or code, just general direction I should go. Here is my goal:
I have a SQL stored procedure that returns data which I will need to add spaces to, then end up with a space delimited text file.
This txt file will then be needed to be saved and emailed.
Also, I would like to be able to schedule this in the Task Scheduler for once a month execution.
Is this all just one big crazy idea?
 Thanks for any help.
Andrew  
 

View 16 Replies View Related

Which Kind Of Replication Should I Use?

Aug 8, 2001

i want to take 70-028 test.
but i still have some questions about administration.
can anybody help me?
Question:
(NY1-----T1-----NY2)-----56kbps-----(Seattle-----T1-----SanFrancisco-----T1-----Los Angeles)
NY1=NewYork1,NY2=NewYork2,SF=SanFrancisco,LA=Los Angeles
A. NY1=pub/dist; NY2,Seattle,SF,LA as subscriber
B.NY1=pubs; NY2=dist;Seattle,SF,LA as subscriber
C.NY1=pubs; SF=subs/dist/pubs; NY2,Seattle,LA as subscriber
D.NY1=pubs; LA=dist; something not workable
which one is correct answer, and why?
How to do the questions like this?

View 1 Replies View Related

I'm Don't Know What Kind Of Replication Should I Use

Sep 18, 2002

In the company that I work there are two offices, one is outside the city and the other is in the city. They are connected trough a DS0 using frame relay. The thing is that the comunication is very slow (the SQL server is in the city) and we want to add a new server at the office outside the city and we want at the end of the day replicate the data between the two servers so every server has the actual data. We didn't use primary keys, instead of that we used identities (autoincremental value administered by the server). What kind of replication should I use and why? (I'm new with SQL Server). There are 3 kinds: Snapshot, Transactional and Merge.

View 4 Replies View Related

What Kind Of Index

May 12, 2004

I have 20 tables (the same schema)

I use view to search it with union operator.
now i have indexes on all of the tables on 1 kolumn (nvarchar)

what kind of index i should use to make it fastest


index on view ?

View 3 Replies View Related

What Kind Of Loop And How?

May 22, 2008



Im looking to create a Stock Levels table which goes through each record of my Part table updating the corresponding Part in the Stock Level table.

I know how to perform all the Count calculations etc, if someone could just give me an example of a suitable loop that would be great

cheers.

View 11 Replies View Related

One Kind Of Transformation

Jun 11, 2007

original data type is string ,format like MM/dd/YYYY,also there exists null or "" value

I need to convert them into datetime,format like YYYY--MM-dd

any idea about this?

thanks in advance

View 10 Replies View Related

What Kind Of Package Is It?

Aug 18, 2006

Hi everyone,

Is there any way to know how is stored a package before do loadpackage, loadfromsql... bla,bla??

I mean, is there any property for Package class where you can see how it is stored?

Thanks in advance for your comments,

View 3 Replies View Related

What Kind Of DB Is Members ASPNETDB.MDF?

Aug 3, 2007

Hello,   I created web site using VWD 2005 Express, and I use 'Create New user account' control for signin up. I need to know what kind of database is ASPNETDB.MDF ' in order to set it on server? I created ASPNETDB.MDF (database?) when I first created user in  ASP.Net Web Site Administration Tool . What tipe is ASPNETDB.MDF database?  . I have my web site on server, and DB is not working, because it is not set on server.I have this databases available on my server:Microsoft Access                   Unlimited     Microsoft SQL Server 2000     1 x    MySQL                                 1 x    PostgreSQL                           1 xDSN/ODBC                           Unlimited SQL Database Users              Unlimited         Which one you recommend should I choose for members database (ASPNETDB.MDF)?Thank you,   Beezgetz       

View 11 Replies View Related

Problem With This Kind Of Sql Statement WHERE ... IN (...,...,...)

Oct 28, 2004

Hi,

I have a problem in a Stored Procedure SQL query im writing, basically what I want is something that works like this

WHERE intField IN (@strCommaSepList_Of_Integers)

First I tried this:

SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (@strListUserGroupsAllowed))

But if I passed a 25,26 value to the last paramenet is gave an error saying:

Syntax error converting the varchar value '25,36' to a cllumn of data type int.

How can I get this to work??

If for example I manually put the WHERE IN values it works, ike this I mean:

SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (25, 36))

But thats no use as I have to pass the list of values dynamically...

Please help!!

- Carl

View 2 Replies View Related

Kind Attn.Ray / Kris

Feb 22, 2001

Hi,
I am unable to understand how can I create a new filegroup?
I know how to move certain text/ntext/images column to a new file group - thru design table but my problem is how to create a new filegroup?
Do I have to use Alter Table/Alter Database for this or there is any other better way?
TIA
Jai.

View 2 Replies View Related

How To Implement This Kind Of Sorting?

Jul 15, 2004

Here's a basic overview of my Table

ID identity int
ParentID int
Name varchar

Now, the root level items will have a ParentID of NULL, and the child nodes will have it's parent's ID in it's ParentID column (a simple self-referencing relationship).

However, when I select it, I would like to Order by ID, but then have all it's children right after it. For example, if this is the unsorted view:

Code:


ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
3 <NULL> Test 2
4 1 Test Child 2
5 3 Test 2 Child 1


I would like the sorted to look like:

Code:


ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
4 1 Test Child 2
3 <NULL> Test 2
5 3 Test 2 Child 1


Is this possible with my current structure? If not, how could I change it?

View 4 Replies View Related

A Different Kind Of MSSQL Question...

Jul 21, 2004

Hey all!
This may sound like a weird one but i couldn't think of a better audience to ask!

If you are a SQL Server DBA (or were, or aspire to be, or play one on T.V., etc.) and the CIO of your 2.something billion $ a year company has offered you an 1 hour forum to sit down and ask him anything you want with the pretense of getting a straight answer, what would you ask?

I know this offers an excellent opportunity to cut up a bit...and do so if you must (keeping us entertained around here is important too!)...but i'm looking for work related, SQL related, direction, strategy, etc, etc type stuff.
Not: 'Whats it like to drive a $250k 'benz to work everyday?' OR 'Can i have .5% of your $2,000,000 bonus this year?'

While i genuinely have those questions in my mind...i'm not looking to waste his $5,000 an hour ass's time - and I only have 1 hour to chew his ear off.

What do ya'll think?

View 4 Replies View Related

What Kind Of Fucntions I Cannot Use In Trigger?

Nov 3, 2006

Hi,

What kind of functions i cannot use within a trigger?
Thanks for all your replys for my previous posts.

View 1 Replies View Related

Can We Create A Kind Of Temporary SP

Jan 4, 2007

If we don t have the right to create an SP, can we create a dynamic in memory kind of SP
what s the syntax pls?
Thanks a lot

View 8 Replies View Related

Help On A 'Spread' Kind Of Ranking...

Nov 9, 2007

Hi all,

Just can't figure this out. I'll try not to give a long-winded explanation (I hope), let's say this is the table, for example:
[Table1]
RowNumber | Value
1 | 4
2 | 6
3 | 3
4 |10
5 | 6
6 | 5
7 | 8
8 | 8
9 | 2
10 | 6

I want to write a query that creates a column whose values are similar to (ROW_NUMBER -1), and resets each time a value is present.
Using the above example, 6 will be the test value. So, I want to know how many rows it takes till 6 is repeated.

Output would be:
RowNumber | Value | Spread
1 | 4 | 1
2 | 6 | 0
3 | 3 | 1
4 |10 | 2
5 | 6 | 0
6 | 5 | 1
7 | 8 | 2
8 | 8 | 3
9 | 2 | 4
10 | 6 | 0

I think PARTITION BY can be used somewhere, with the ORDER BY on the [RowNumber] - but I'm just not sure on which ranking function to use, and how to reset the Ranking on the number 6 (above example).

Any help would be appreciated. This has been a mind-teaser for me, and I give

Thanks,
Denvas

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved