Receiving error when trying to use try..catch block

I am using SQL Server 2005 and am trying to execute the following statements:

BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'Error Caught' END CATCH I am getting the following errors:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'TRY'.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'END'.

This code should work, can anyone tell me why it it not working?

Thanks in advace.

Hmmm, works fine here.

Is there something else you have coded in the same script?

Are you using SSMS?

|||Sorry, my mistake. I was using our SQL Server 2005 Management Studio, but had accidentally connected to the SQL Server 2000 box to test with. Thanks.|||Been there, done that

recbase.cpp:1374

Hello,
I have this error after a select query (wich works for years).
Serveur_: Msg 3624, Niveau 20, tat 1, Ligne 1
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 51
Process ID: 1188
Connexion interrompue
I made a checkdb and everything is Ok.
does anyone has an idea ?Maybe these articles:
http://support.microsoft.com/defaul...kb;en-us;311104
http://support.microsoft.com/defaul...kb;en-us;317089
can help you?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alexandra Bishop" <boccara@.netvision.net.il> wrote in message
news:9e09323a.0402110606.33a4dc1d@.posting.google.com...
> Hello,
> I have this error after a select query (wich works for years).
> Serveur : Msg 3624, Niveau 20, tat 1, Ligne 1
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 51
> Process ID: 1188
> Connexion interrompue
> I made a checkdb and everything is Ok.
> does anyone has an idea ?

recbase.cpp:1374

Hello,
I have this error after a select query (wich works for years).
Serveur : Msg 3624, Niveau 20, État 1, Ligne 1
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 51
Process ID: 1188
Connexion interrompue
I made a checkdb and everything is Ok.
does anyone has an idea ?Maybe these articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;311104
http://support.microsoft.com/default.aspx?scid=kb;en-us;317089
can help you?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alexandra Bishop" <boccara@.netvision.net.il> wrote in message
news:9e09323a.0402110606.33a4dc1d@.posting.google.com...
> Hello,
> I have this error after a select query (wich works for years).
> Serveur : Msg 3624, Niveau 20, État 1, Ligne 1
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 51
> Process ID: 1188
> Connexion interrompue
> I made a checkdb and everything is Ok.
> does anyone has an idea ?sql

Reason to not use SELECT *

Or when to use it...
http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx
Any comment appreciated...I'll add to the list as well
ThanksHere is one of my favorite examples...

create table test1
(col1 int)
go
create view testview1
as
select *
from test1
go
select *
from testview1
go
alter table test1 add col2 varchar(10)
go
select *
from testview1

Some folks depend too much on what they think SQL Server should do, rather than on what it does do.|||see Why "select star" is bad (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576581_tax285649,00.html) (site registration may be required, but it's free)

* can't use GROUP BY unless you itemize every column in the table(s)

* can't write SELECT * MINUS col3 (select all columns except col3)

* can't take advantage of a covering index|||Thanks Rudy...

Nice pix...

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576581_tax285649,00.html

but

That said, if you do actually need all columns, then SELECT * is fine, provided that you never change the table. Or the query.

Almost lends credibility to it's use...better it was left out...MOO

I like the group by argument and the select * minus-col3...mind if I add'em to the list?|||How about the argument that SELECT * makes debugging a bitch for the next poor sod who comes along and has to figure out what your code is doing? Or you, six months later, for that matter.

I always take the time to neatly enumerate the fields I am returning, and I am sure it has saved me time debugging in the long run.|||True...it's like it's self documenting..

That's why when I have a join...I use the labels on every column, even if the column names are unique...

don't have to guess next time...

Thanks|||Absolutely. SELECT * is just sloppy coding, and for that matter so is referencing columns without including the table name.|||Originally posted by blindman
Absolutely. SELECT * is just sloppy coding, and for that matter so is referencing columns without including the table name.

Nope labels are enough for me...

can't stanf the fully qualified names...hard to read...but then I have glasses

Drinking Glasses that is...|||Originally posted by Brett Kaiser
Nice pix...
yah, i keep meaning to get that replaced, i was hardly gray then

what do you think of the mushot on my home page http://rudy.ca/
mind if I add'em to the list? please do :cool:|||Ok I am kind of lost... what should I use instead of the SELECT statement?

breeze76|||No...you need the select

It's just an extremley bad and pervasive coding practice that people use

SELECT *

Except when doing analysis, or trying to figure things out, you should always code like

SELECT Col1, Col2, Col3, ect

Even when we offer advice and samples, a majority of the time we use col lists...I think just to hope that the advice take will be cut and paste and hopefully alter the way the developer works...

MOO|||Ok so, if I want to use:

SELECT user_name, password, email FROM USERS

I should do that instead of:

SELECT * FROM USERS

Correct?

breeze76|||absolutely

but that my own opinion (hence, the MOO)|||OK.. say I want to get info from Three(3) tables in one database, how would I do that?

breeze76|||USE Northwind
GO

SELECT o.OrderId, od.Quantity, od.UnitPrice, o.CustomerId, c.CompanyName
FROM Orders o
JOIN [Order Details] od
ON o.OrderId = od.OrderId
JOIN Customers c
ON o.CustomerID = c.CustomerId
WHERE c.CompanyName = 'Around the Horn'|||I kind of do not understand your example.. so here is a relational of my database...

Thanks again

breeze76|||I think I have a problem here.. I use access for my database... so I guess all this is moot then, eh?

breeze76|||sometimes when i feel naughty, i use select * when i create a dynamic table query.

select A.*
from (select statement) as A|||Originally posted by breeze76
I think I have a problem here.. I use access for my database... so I guess all this is moot then, eh?
no, of course not

whether to use

SELECT *

or the mucho splendido better version

SELECT col1, col2, ...

depends not on your database, because as far as that part of the syntax is concerned, all databases are the same

but you should take your relationship diagram and start a new thread in the access forum if you still have a question about something|||OK thanks.. but I understand what you are saying now... just declare your stuff each and not a global thing..

Thanks

breeze76|||Absolutely. SELECT * is just sloppy coding, and for that matter so is referencing columns without including the table name.

I really do believe this, for my developers have used indexing in the VB application. Upon each form-load and save; they used SELECT *.
It was disclosed when i added ROWGUID for my merge replication, my client/server application banged. :mad: All inserts failed, upon form-loads user were viewing ROWGUID in each last field.
That day i took immediate short-leave, for i would have either killed myself or blow the heads of those developers off. Now i expect any thing from my develpers here. I tried very much that they must get warning-letters ;)
I had to drop the column from each table and the developers changed each page and put column-names in the queries.|||Thanks for the real world example...

As for Access...

The example I gave should actually work with the sample Northwind database that Access is shipped with...just get rid of the USE Northwind and the GO's...

OPEN a new Query, (Well open Northind first), then switch to the SQL Window, and paste it...

it should run...you can then see what it transform it to in QBE...

I hate what Access does to stuff though...

And hey, doesn't anyone think the site looks really nice...just took a little while...(thank god it's not banking ot trades...)|||I kind of do not understand your example.. so here is a relational of my database...

Thanks again

breeze76

So...you can't apply what I gave you to your example?|||Well access is a little picky..

but it generated this

SELECT o.OrderID, od.UnitPrice, od.Quantity, o.CustomerID, c.CompanyName
FROM Customers AS c
INNER JOIN (Orders AS o
INNER JOIN [Order Details] AS od
ON o.OrderID = od.OrderID)
ON c.CustomerID = o.CustomerID;

really strange performance problem

hey
I have a sql2000 server SP3 and I mgrate a databse from SQL7. I run teo basically equal select: he first one 2 seconds the second one 58 minutes.....

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,20)
not in (select substring(col018_dominio,1,20)
from iis_uu_diario_resume where substring(col018_dominio,1,20)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20))
option (maxdop 1)

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,30)
not in (select substring(col018_dominio,1,30)
from iis_uu_diario_resume where substring(col018_dominio,1,30)
= substring(uu_resume_ses_dummy_dummy.dominio,1,30))
option (maxdop 1)

the only differencei s that the substring range: 20 to 30. Notice that the limit is not fixed. SOmetimes the jump in execution time happende when I change from 90 top 91......
really I dont' know. (Fields ara varchar(90) but it was the same with varchar(255). the PLAN are exactly the same. in the second case the CPU was 50% fror 58 minutes fixed.
thanks for all the help (really needed)essentialy, for every record in the "uu_resume_ses_dummy_dummy" table you are looking at every record in the "iis_uu_diario_resume" table Using only one processor.

Since you will be looking at every record you have the potential of being delayed by locks, index leaf splits and other traffic. What happens if you run these selects on a quiet system. I suspect the time diffrence is small.|||I was runnng these queries both in a "busy" server (4 cpu, 4Gb RAM) and on a really quiet server (2 CPU, 4GB RAM) with same timing. Quiet server means that basically % of CPU without that select was netween 0 and 5%|||forgot things.

1) same times without the option of processing in one CPU only
2) both table are index on the specific fields.

what you say is ok. problem is:why almost identical queries have such a big big big difference in execution time?

Really large integer constants - confused

I ran the following in SQL Server 2000 and 2005 with the same results.
select 1234567890123456 / 1234567890123
select 1234567890123456 / convert(bigint,1234567890123)
select convert(bigint,1234567890123456) /
convert(bigint,1234567890123)
1000.00000000036936
---
1000.00000000036936000332
1000
Everything I have found in Books on Line (both 2000 and 2005 versions)
says that talks about constants says those numbers are "integer
constants":
"integer constants are represented by a string of numbers not enclosed
in quotation marks and do not contain decimal points. integer
constants must be whole numbers; they cannot contain decimals."
Obviously these long "strings of numbers not enclosed in quotation
marks" and not containing decimal points are not being treated as
Integers.
Can anyone (1) explain what is going on, and (2) point me to where it
is documented?
Thanks!
Roy> 1000.00000000036936
This post is not going to be helpful at all. Just wanted to post my
observations. My first reaction was that it looks like they are implicity
being converted to FLOAT. But this is not true, since you actually lose
precision in that case:
SELECT CONVERT(FLOAT, 1234567890123456) / CONVERT(FLOAT, 1234567890123)
--
1000.00000000037
But take a look at implicit conversion to decimal:
select 1.0 * 1234567890123456 / 1234567890123
--
1000.000000000369360
Pretty darn close...|||If you do this
select
1234567890123456 as c1,
1234567890123 as c2,
convert(bigint,1234567890123456) as c3,
convert(bigint,1234567890123) as c4
into sometable
and then look at the columns in the new table
it appears to show that integers bigger than 'int'
are converted to numeric with differing precisions.
I'm not aware of this in BOL.|||> and then look at the columns in the new table
> it appears to show that integers bigger than 'int'
> are converted to numeric with differing precisions.
> I'm not aware of this in BOL.
More specifically, it looks like numeric literals exceeding 2147483647 are
converted to numeric(n), where n is the number of digits in the literal.
Consequently, the expression result is also numeric according to standard
data type precedence rules. The result is not an integer because
SELECT 1234567890123456 / 1234567890123
is functionally identical to:
SELECT
CAST(1234567890123456 AS numeric(16)) /
CAST(1234567890123 AS numeric(13))
and the result is numeric(30, 14) to allow for the remainder of the division
expression.
IMHO, this is contrary to the documentation and not intuitive. The final
result should be converted to an integer:
SELECT
CAST(
CAST(1234567890123456 AS numeric(16)) /
CAST(1234567890123 AS numeric(13))
AS numeric(38))
Hope this helps.
Dan Guzman
SQL Server MVP
<markc600@.hotmail.com> wrote in message
news:1140958196.462553.282950@.i40g2000cwc.googlegroups.com...
> If you do this
> select
> 1234567890123456 as c1,
> 1234567890123 as c2,
> convert(bigint,1234567890123456) as c3,
> convert(bigint,1234567890123) as c4
> into sometable
> and then look at the columns in the new table
> it appears to show that integers bigger than 'int'
> are converted to numeric with differing precisions.
> I'm not aware of this in BOL.
>|||>More specifically, it looks like numeric literals exceeding 2147483647 are
>converted to numeric(n), where n is the number of digits in the literal.
I am sure that is it, Dan. It appears that when they added bigint as
a data type they never got around to adjusting the code related to
integer constants.
I should check the docs for 7.0 next w to see if it is mentioned
there.
Do the MVPs still have a more direct line for reporting these sorts of
things than the general public?
Roy|||
Roy Harvey wrote:

>I am sure that is it, Dan. It appears that when they added bigint as
>a data type they never got around to adjusting the code related to
>integer constants.
>I should check the docs for 7.0 next w to see if it is mentioned
>there.
>Do the MVPs still have a more direct line for reporting these sorts of
>things than the general public?
>
Roy,
We have an inside channel, but the best route nowadays is the public
bug-reporting forum at http://lab.msdn.microsoft.com/ProductFeedback/.
I'd encourage you to file a bug report (if you think the behavior is wrong)
or a suggestion (to improve documentation or change behavior). The latter
might be more successful, since a bug report can be closed as "by design",
after which the request to improve documentation might more easily be lost.
There are two issues here: one is how literals are typed. That is a messy
issue, and not uniquely messy to T-SQL. I've reported some cases where
the same literal is typed differently in different contexts, but this is
not quite
as bad, and could be improved with better documentation. (BOL says that
a literal decimal has a decimal point, and that's not helpful here.)
The other issue is the internal/intermediate use of floating-point
arithmetic
in decimal calculations. That behavior (questionable in my estimation) also
causes other oddities, such as power(2.,57) ending up divisible by 10.
Since
DECIMAL is billed as an "exact numeric type" (though it's no more exact
that float - all types represent the values they represent exactly),
maybe someone
will care.
Here's a more glaring example of the first behavior:
select 2000000001/2 -- returns 1000000000
select 20000000001/2 -- returns 10000000000.500000
or select 1000000000/1000000001, 10000000000/10000000001
You can play around with this repro to see more:
declare @.s sql_variant
set @.s = 2147483648
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = 2147483647
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = -2147483647
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = -2147483648
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = cast(-2147483648 as int)
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = cast(25 as smallint)
Any change would be a breaking change, of course.
Steve Kass
Drew University
:

>Roy
>|||With later technologies like SQL Server 2005, you are empowered to submit
bug reports and suggestions directly via the Product Feedback Center at
http://lab.msdn.microsoft.com/productfeedback/. Documentation feedback can
be submitted via the Books Online. The SQL devs do an great job of
monitoring and acting on the feedback.
I submitted this as a bug
<http://lab.msdn.microsoft.com/Produ...BK46
489>.
Feel free to vote on it.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:611402d49jv1siu85i6a5crcmt69g22vtp@.
4ax.com...
> I am sure that is it, Dan. It appears that when they added bigint as
> a data type they never got around to adjusting the code related to
> integer constants.
> I should check the docs for 7.0 next w to see if it is mentioned
> there.
> Do the MVPs still have a more direct line for reporting these sorts of
> things than the general public?
> Roy|||Dan,
Thanks!
I visited the bug you created and validated and voted. I was going to
add a comment, but it said it would "Post In Newsgroup
microsoft.private.msdn.productfeedback.vb". I was thrown off by the
.vb ending, which sure sounds like a different product to me!
The note I was going to add was that the same applies to SQL Server
2000.
I will try to add feedback for the docs too, since that is where
something might actually be done.
Roy
On Sun, 26 Feb 2006 15:40:06 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>With later technologies like SQL Server 2005, you are empowered to submit
>bug reports and suggestions directly via the Product Feedback Center at
>http://lab.msdn.microsoft.com/productfeedback/. Documentation feedback can
>be submitted via the Books Online. The SQL devs do an great job of
>monitoring and acting on the feedback.
>I submitted this as a bug
><http://lab.msdn.microsoft.com/Produ...BK4
6489>.
>Feel free to vote on it.

Real quick Q on Select @@identity

Hi all. Just a quick question for you guys...I did a search on Select @.@.identity and I'm not sure I understand it. The program I'm migrating over (from sql server to oracle) has "Select @.@.identity" at the end of some insert statements. I'm just tryin to figure out what this does and what the Oracle equivlant would be? i have read posts(or in this case a blog) like this http://weblog.anthonyeden.com/archives/000054.html that states it gives you the value of the PK.

I've also read this http://www.kamath.com/tutorials/tut007_identity.asp stating select @.@.identity is handy to use as a "connection specific global variable." This all makes sense, however in the code I am examining select @.@.identity is not assigned to any variable. Basically, my question is if select @.@. identity is not assigned to a variable in your code, what use does putting it at the end of a select statement serve? thank youThe 'Select @.@.Identity' statement after Insert is simply to find out what the the last(max) identity number after the Insert. Identity in Oracle it's the Sequence number. It's the same thing as finding out the last sequence number that was inserted.|||You should probably be using SCOPE_IDENTITY() instead of @.@.IDENTITY. If your insert triggers inserts into other tables, @.@.IDENTITY will contain the ID for a table inserted into by the trigger, not the table you inserted into. SCOPE_IDENTITY() will be the ID of the row you inserted.

READTEXT not returning all data

SELECT @.@.TEXTSIZE
SET TEXTSIZE 4096
SELECT @.@.TEXTSIZE
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(emailTemplates.Data)
FROM emailTemplates WHERE [Name] = 'AccountInfoReceipt1'
READTEXT emailTemplates.Data @.ptrval 0 2000
GO

Thats my code. and it never returns the entire email template. why?

thx in advCurrently you are only reading the first 2000 characters. If you email template is longer than that, you will not get the entire template.|||heh. even when i change 2000 to 5000.....it still returns the same amount......i know all about the size and the offset. its just not working :/ :shrugs: dont owrry about it. i just used regular html files and read them into a string.