Error List, Sql Server 2005

Hello, I would like to know if there is an list of mistakes where appears all errors that you can find to interact with the SQL.

for example something similar like this:

Error number 2627 Means that exists a record with the same primary key

Error number 505 Means that ther is a row with doesn't accept nulls

Thanks, my email pac_mt@.hotmail.com .

Are you looking for the possible error messages? You can query master.dbo.sysmessages in older versions of SQL Server and sys.messages in SQL Server 2005. Older versions of Books Online also has the list of error numbers/messages.|||

The problem is that sysmessages doesn't contain all error numbers. For instance, I can't find the error 1326: An error has occurred while establishing a connection to the server...

Why are not all errors documented and is there any unofficial documentation where I can find the full list of errors?

Thanks

|||

1326 is not a SQL Server error message. It is raised by the client data access library. All the error messages raised by server should be present in the system table. But not all error messages may be documented in Books Online. If you find such errors and would like to see it documented then please use the feedback mechanism in Books Online to submit a work item. This is the best way to send feedback for documentation issues.

See below topic for SQL Server 2000 error messages:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_syserrors1_6m5z.asp

And btw, the latest update (July 2006) of SQL Server 2005 Books Online contains the error message topics too. See below links:

http://msdn2.microsoft.com/en-us/library/aa337472.aspx

http://msdn2.microsoft.com/en-us/library/ms365262.aspx

http://msdn2.microsoft.com/en-us/library/ms165761.aspx

|||

Thanks.

I actually decompiled System.Data.SqlClient yesterday and noticed these connection errors come from dbnetlib.dll

These error messages, thrown by ado.net as SqlExceptions, are really hard to identify as there are dozens of different error numbers for the VERY same error description (like 5, 51, 121, 231, 1326) and sometimes they overlap the native SQL Server errors.

I'll also post this to a ADO.NET forum. Thanks again.

sql

error in calcualting week nr over DATEPART

hello,
i have the problem that the w nr calcualted from the actual date in not
localized...
in germany for example - from 21.11.2009 till 27.11.2005 there is the w
nr:47
if i execute in the queryanalyser
Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
i get the value 48 - instead of 47
any suggestion how to correct this?
thanksMaybe this will solve your problem:
set datefirst 6
Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
> hello,
> i have the problem that the w nr calcualted from the actual date in not
> localized...
> in germany for example - from 21.11.2009 till 27.11.2005 there is the w
> nr:47
> if i execute in the queryanalyser
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
> i get the value 48 - instead of 47
> any suggestion how to correct this?
> thanks
>
>|||Don't use DATEPART to calculate w numbers, SQL Server doesn't follow the
ISO standard for this.
Use the ISOW function found in Books Online, of use a calendar table (you
can find an article on
this on www.aspfaq.com).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
> hello,
> i have the problem that the w nr calcualted from the actual date in not
> localized...
> in germany for example - from 21.11.2009 till 27.11.2005 there is the w
> nr:47
> if i execute in the queryanalyser
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
> i get the value 48 - instead of 47
> any suggestion how to correct this?
> thanks
>
>|||yes, this solved my problem.
thanks Raymond
"Raymond D'Anjou" wrote:

> Maybe this will solve your problem:
> set datefirst 6
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||hello Raymond,
sorry, but i have still problems
set datefirst 6
go
Print DATEPART(wk, CONVERT(datetime,'21.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'22.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'23.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'24.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'25.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'26.11.2005',104)) ->48 wrong !
Print DATEPART(wk, CONVERT(datetime,'27.11.2005',104)) ->48 wrong !
also the problem exist for
Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be 1
)
any ideas?
thanks
"Raymond D'Anjou" wrote:

> Maybe this will solve your problem:
> set datefirst 6
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||I had a feeling that it may not solve all your problems. :-(
Look at the Tibor's answer.
Here is the direct link to the article he suggested.
http://www.aspfaq.com/show.asp?id=2519
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:49D92FAA-54BE-4791-8C3B-13F3AA8D37C8@.microsoft.com...
> hello Raymond,
> sorry, but i have still problems
> set datefirst 6
> go
> Print DATEPART(wk, CONVERT(datetime,'21.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'22.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'23.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'24.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'25.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'26.11.2005',104)) ->48 wrong !
> Print DATEPART(wk, CONVERT(datetime,'27.11.2005',104)) ->48 wrong !
> also the problem exist for
> Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be
> 1)
> any ideas?
> thanks
>
>
> "Raymond D'Anjou" wrote:
>|||hello Tibor,
yes the ISOW function solved my problem with calculating the w nr.
Any idea how to solve my problem with dw...
Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be 1
)
thanks
"Tibor Karaszi" wrote:

> Don't use DATEPART to calculate w numbers, SQL Server doesn't follow th
e ISO standard for this.
> Use the ISOW function found in Books Online, of use a calendar table (y
ou can find an article on
> this on www.aspfaq.com).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||Always use a language neutral datetime format: http://www.karaszi.com/SQLServer/in...o_datetime.asp.
Anyhow, make sure you have proper SET DATEFIRST:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:9EB31742-EA64-4148-994E-531C3B730DA8@.microsoft.com...
> hello Tibor,
> yes the ISOW function solved my problem with calculating the w nr.
> Any idea how to solve my problem with dw...
> Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be
1)
> thanks
> "Tibor Karaszi" wrote:
>

error handling while processing a cube

hi

in analysis services when i process a cube i got an error that
data from the fact table isnt exists in the dimenstion table
for example : fact table sales has column suplier that one of its row
has data(suplier id = 170) that doesnt match with the dimension table ( in the dimension table there isnt such supier id)
how can i handle this error ?
can i insert instead someyhing else so that the proccess can continue and not failed ?

Thanks

Eyal

Ideally, you've addressed this problem in your data warehouse/data mart. I recommend to my clients they never allow NULLs in their foreign keys. This requires you to place a NULL member in each dimension -- we usually assign that member the surrogate key value of -1 -- and eliminates this issue.

It sounds like in your example, you have a valid key in your partition, so I would suggest you make sure you dimension is processed before you process your cube/partitions.

If that is not an option, you use use the UnknownMember and ErrorConfiguration properties in SSAS to handle this issue. In the dimension that is causing problems (the supplier dimension in your example?) set the UnknownMember property to either visible or hidden. It defaults to None which makes the UnknownMember inaccessible.

Then, on the measure group that points to this dimension table, change the error configuration to replace missing key values with unknown (using the KeyErrorAction and KeyNotFound properties). Be certain to also set the KeyErrorLimitAction property to Stop Logging when the max error is hit.

One important gotcha on this. If your dimension member lands in your dimensions, the facts assigned to the unknown member do not revert to your new dimension value. To line your facts up correctly with the values in your dimension, you will need to do a full reprocess of the partition.

B.

Error handling when one field value is bad

Here's an example of what I'm playing with:

update TestTable

set IntValue = Cast(StringValue, as int)

flag =

BEGIN TRY

0

END TRY

BEGIN CATCH

1

END CATCH

I'm trying to take a string value holding an integer and populate an integer field with that value. But, every so often, the string value does not contain an integer (e.g. '13.9" or "CA"). That error would cause the entire column UPDATE to be rolled back. i'm trying to get around it with some kind of "TRY CATCH" construct but I don't believe it can work.

What would you suggest?

Barkingdog

No, you are right this won′t work. For me, the best Exception handling is to prevent exceptions, in your case check the value for being numeric, there is a function ISNUMERIC which can do that for you. But be careful with that function, because it checks for currency numeric only, so the value "$" which is part of a currency expression will also evaluate to true.

See more details on the site: http://www.aspfaq.com/show.asp?id=2390

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

error handling OPENROWSET

If, for example, i have inside a procedure the next statement:
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + '
'')')

in some ocasions it will generate errors
- if no file or path,
- if no column, ...
how can i handle those errors and continue the procedure without generating an error.Error for "no column" when does that occur ? Only way i cna think of is if the file doe not exist.

To check if the file or path exists

declare @.Path varchar(128)
declare @.FileName varchar(10)

--i used 10 chars because foxpro2.6 allows only length of 10 for a filename and i am not sure what visual fp uses (also fp2.6 doesn't like spaces in path if that matters in your situation)

select @.Path = 'C:\', @.FileName = 'myfile.dbf'
declare @.i int
declare @.File varchar(1000)

select @.File = @.Path + @.FileName
exec master..xp_fileexist @.File, @.i out
if @.i = 1
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + ''')')
else
print 'no file'

Error handling in nested stored procedures

I have a problem with my error handling in nested stored procedures (SQL
Server 2000).
Below is an example of stored procedures that are causing this problem. If
you install the scripts and run sp1, you'll get 2 errors:
(1) Divide by zero error encountered. -- This is expected due to : select 1/
0
(2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
This 2nd error is the problem and I'm not sure what is it.
If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')",
where Col1 is an int, I get : Syntax error converting the varchar value
'hello' to a column of data type int. BUT I don't get that 2nd error about
the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
error, my error handling works perfectly.
Does anyone know what is causing this problem, and how I should change my
error handling to avoid it.
Thanks,
Craig
CREATE proc sp2
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
select 1/0
select @.err=@.@.error if (@.err<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SP
GO
CREATE proc sp1
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
exec @.retvalue = sp2
select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount<>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SPIn SQL Server, you get this error if you, for instance, begin a transaction
in one procedure and end
it in another procedure. In short, if you exist a procedure with a different
@.@.TRANCOUNT compared to
when you entered the procedure, you get this error. The reason why it seems
to work when you assign
'hello' to an int is that SQL Server will stop execution in such case. In sh
ort, have the
transaction handling in the outermost procedure. Or, you can in the inner pr
ocedure check
@.@.TRANCOUNT when you enter and SAVE TRAN if it was > 0 and in such case do t
he rollback to the
savepoint, but you still have to communicate to the caller that you didn't p
erform the work so the
caller can ROLLBACK its work. Check out the error handling articles at www.sommark
og.se for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:8F9E1557-B00E-48BB-AA9F-1C7436C1B547@.microsoft.com...
>I have a problem with my error handling in nested stored procedures (SQL
> Server 2000).
> Below is an example of stored procedures that are causing this problem. If
> you install the scripts and run sp1, you'll get 2 errors:
> (1) Divide by zero error encountered. -- This is expected due to : select
1/0
> (2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> This 2nd error is the problem and I'm not sure what is it.
> If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')"
,
> where Col1 is an int, I get : Syntax error converting the varchar value
> 'hello' to a column of data type int. BUT I don't get that 2nd error about
> the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
> error, my error handling works perfectly.
> Does anyone know what is causing this problem, and how I should change my
> error handling to avoid it.
> Thanks,
> Craig
>
> CREATE proc sp2
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> select 1/0
> select @.err=@.@.error if (@.err<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
> GO
> CREATE proc sp1
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> exec @.retvalue = sp2
> select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount<>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
>