|
Jörgen Andersson wrote: The return statement is never reached, because the raiserror exits the statement block.
If the RAISERROR statement is executed, neither of the EXEC calls will be reached either. There's no need for ELSE blocks. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Don't you need errorlevel 20 or above to terminate batch?
|
|
|
|
|
Looking at the documentation[^], a severity of 20 to 25 will not only terminate the batch; it will terminate the connection, and write an error to the error and application logs.
It's not entirely clear, but as far as I can see, a severity of 11 to 19 will terminate the batch (or transfer to the CATCH block), but leave the connection intact.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It certainly is unclear. So I tested by creating a couple of procedures.
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
PRINT 'Start SubSP1'
RAISERROR('Some Error', 16,1) WITH LOG
PRINT 'End SubSP1'
END
GO
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
PRINT 'Inside SubSP2'
END
GO
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
PRINT 'Start MainSP'
EXEC dbo.SubSP1
EXEC dbo.SubSP2
PRINT 'End MainSP'
END
GO
Running the MainSP results in:
Start MainSP
Start SubSP1
Msg 50000, Level 16, State 1, Procedure SubSP1, Line 6
Some Error
End SubSP1
Inside SubSP2
End MainSP This does not stop the execution at all, Neither on statement, scope, batch or connection level.
Changing the errorlevel to 17 results in:
Start MainSP
Start SubSP1
End SubSP1
Inside SubSP2
End MainSP
Msg 50000, Level 17, State 1, Procedure SubSP1, Line 5
Some Error Still doesn't stop the execution, but note the difference in where the error message is printed.
I suspect there will be some real differences between the errorlevels if I add TRY-CATCH or more probably a transaction.
Errorlevel 19 or higher should stop the execution of the current batch according to Database Engine Error Severities | Microsoft Docs[^] but it doesn't.
Errorlevel 20 and above closes connection as expected from the manual.
Couldn't be bothered to check any further. I've proved myself wrong and thereby only proven that one shouldn't try to answer questions on New Years day.
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: with the same parameters
One possibility of course is that you are not in fact using the same parameters.
You didn't mention but presumably in both test cases you are using exactly same database instance (not a copy) and that you have verified that you are using the same instance.
|
|
|
|
|
Same DB instance, same parameters, same error - only that I see the wrong error from code...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Then trace it while running it through ASP.Net.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
https:
I tried the link above but receiving a syntax error, my is easier then that one.
I have datetime, temperature, and humidity written in a table called tempdata.
I would like to have trigger that if value is above 65 for temperature (float), write to a different table or database called test.
Then email out when test is modified.
I've been trying phpmyadmin because I'm not good with command line.
I have phpmyadmin 4.5.4
mysql ver 14.14 distrib 5.7.20
python 2.7.12
Ubuntu mate 1.16.2
Gnome 2
Raspberry pi 2
Thank You
CREATE TRIGGER TemperatureHigh
AFTER INSERT
ON RawTemperature FOR EACH ROW
BEGIN
IF (new.temperature > 65
THEN
INSERT INTO `test`;
END IF;
END
|
|
|
|
|
brian1210 wrote: receiving a syntax error
In what line is the error detected?
Isn't it the problem with "open" bracket without the "close" one here:
IF (new.temperature > 65
|
|
|
|
|
Your INSERT statement does not look complete.
|
|
|
|
|
You have already posted this in QA:
Trigger value > ? write in different table[^]
Your question was incomplete there too.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi all,
If I want to run the query today(12/2/2017) then my data should be inbetween 2016 January 1st to December 31st --I don't want to hard code dates.
if I want to run the query on jan 1st 2018 or in any day in 2018 then my data should be inbetween 2017 January 1st to 2017 December 31st.
if I want to run the query in 2019 then my data should be inbetween 2018 January 1st to 2018 December 31st.
how to write query, please advice.
|
|
|
|
|
- Obtain current date -> obtain "current year" -> obtain ("current year" - 1)
- Make the dates for "between": [("current year" - 1)-01-01] and [("current year" - 1)-12-31]
- use them in your query.
|
|
|
|
|
WHERE YEAR([date_column]) = YEAR(DATEADD(yy,-1,getdate()))
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Your requirements are bad. (I would presume and hope that they were made up by a teacher.)
The second two requirements can be expressed as
Req: Given today's date run a query that spans the 'prior' year based on that date.
The first requirement is incompatible with that and it is impossible to implement without other input data. One way to do that is in fact to hard code 2017 as a switch value. The second way is to presume that the target table has no data before 2017. However to implement that latter rule would mean that EVERY query would need to do a second query to determine that no data existed.
Not to mention that for users it would be confusing at least over time.
|
|
|
|
|
|
I'm struggling with understanding, what I think are difference between the Types used in .NET framework and the OLE DB Data Types used in Access, Excel, etc.
For example:
Type indicator OLE DB Name .Net Type Description
DBTYPE_UI8 BigInt Int64 64-bit signed integer
What's does Int64 map to in C#? Am I using the correct terminology?
It's difficult to look up an answer to a question when you don't know what something is called.
|
|
|
|
|
|
|
Well, I just spent the better part of the afternoon yesterday creating this chart myself. LOL
Can't seem to get past this. Everything I do is giving me an invalid cast exception.
I'm using GetOleDbSchemaTable ( OleDbSchemaGuid.Columns, ...)
I thought had a breakthrough, when I read that my cast was failing because it needed unboxed, so this one worked:
long ORDINAL_POSITION = (long)row.Field<Int64> ( "ORDINAL_POSITION" );
But the Data_Type is not working. The documentation says it's a
UInt16 which I should be able to cast like this:
ushort DATA_TYPE = (ushort)row.Field<UInt16> ( "DATA_TYPE" ); but that gives me the same invalid cast exception. I can't even be sure that the cast that is causing the exception is ushort and UInt16 because I'm not understanding where to look.
|
|
|
|
|
Presumably you verified that the exception is coming from that exact line.
Extract the column as a generic object and then print that out. That will tell you the exact data type.
If that doesn't help then post the output from the above along with the class cast exception text.
|
|
|
|
|
Thank you. I'm not far enough along to know how to do that just yet, but this is something I've been wanting to learn. It turns out that I was fist having cast issues because of db data type vs my C# data types did not always match appropriately, but there was also an issue with DBNull being returned in numeric types in SQLServer, which is not acceptable for C# value types.
The lack of surety in programming is part of the reason software is fragile.
|
|
|
|
|
You don't need the extra cast - the Field<T> method already returns the correct type. There's no boxing/unboxing involved.
long ORDINAL_POSITION = row.Field<Int64> ( "ORDINAL_POSITION" );
ushort DATA_TYPE = row.Field<UInt16> ( "DATA_TYPE" );
The C# types are just aliases for the .NET equivalents; so long === System.Int64, etc.
I suspect the documentation is probably wrong, and the DATA_TYPE column is most likely an Int32, which can then be cast to an OleDbType value. Check the value returned by table.Columns["DATA_TYPE"].DataType to see what it's defined as.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm not sure I understand that because I I really struggled with trying to get the Int64 to cast to long.
It *may* have been that I was getting DBNull back for value types out of SQL Server, which causes an exception. I left the explicit cases in my code just because I'm afraid of breaking it and I have moved on to something else, finally.
I appreciate your help and will exclude the explicit casts the next time I need to get the values form a database in some code that I feel more comfortable with. This code includes a ton of other C# constructs that are new to me, which lead to a pretty terrible debugging experience.
The lack of surety in programming is part of the reason software is fragile.
|
|
|
|
|
The Problem:
Parent & child relationship currently captured in tblCGs and tbcs tables. The MasterClient are held in tblCGs table while Children are stored in tblCs tables using tblCGs. GroupMasterCustKey as key and CustomerKey as Child key.
• Currently a tblCGs table allows the linking of Parent using (GroupMasterCustKey) to child tblcs - (CustomerKey) table which may or may not exist.
AssociatedClient Field in the tblcs table: These are related clients without any hierarchical relationship.
• Currently the AssociatedClient on the tblCs table is too limiting varchar(20) and
can have implications or cause issues.
Proposed Solution:
• To Implement a new table structure where individual rows such as MIND227 - MIND227(customer ranges) are retrieved from the table rather than IND227 - IND229
• Move the Associatedclients Field in tblcs into a separate table with the following fields (ID, ClientID1, ClientID2..... , ClientRelationshipReasonID, and CreatedDate)
Please see the sample code below
IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs
IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs
--Create ClientGroups table.
CREATE TABLE #tblCGs
(
[GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN]
[GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT [DF_tblCGs_GroupFromCustKey] DEFAULT (' '),
[GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT [DF_tblCGs_GroupToCustKey] DEFAULT (' '),
)
-- Populate the table with values.
INSERT INTO #tblCGs VALUES
(988, N'MIN036', N'MIND227', N'MIND229')
, (668, N'08035635', N'31036422', N'31036422')
,(669, N'08035635', N'31035623', N'31035623')
-- Create Customer table.
CREATE TABLE #tblCs ---Customers
([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN]
,[CompanyName] varchar(300) NULL
,[AssociatedClient] varchar(20)
,[CreatedOn] Datetime
)
--Populate the table with values.
INSERT INTO #tblCs VALUES
(72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16 00:00:00.000')
,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08 00:00:00.000')
,(98199, N'MIND229', N'Brecon and District Mind', N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000')
,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550')
,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837')
--- Current Queries
SELECT *
FROM #tblCGs AS CG
INNER JOIN #tblCs AS C
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey
The desired output should like this and not output of the above query
ID ClientID1 ClientID2 ReasonTypeID
1 201 301 1
2 201 302 1
3 201 303 1
4 303 401 1
5 601 701 3
Thanks for your help
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
