how to get the inserted record id

This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C47B19.C651B960
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
hi all
I have auto number field in my table with title ID
I wantted to know when we add a record in table, SQL server takes the = responsibility of increntin / adding auto number in field.
Please advise if there is any way to get this auto number from table. = Like we have table inserted and deleted ... ( getting maximum is not = working in multiple user environemnt ) Is there any stored procedure or = variable i can query ?
thanks.
Khurram Alam
--=_NextPart_000_0048_01C47B19.C651B960
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
hi all

I have auto number = field in my table with title ID
I wantted to know when we add a record = in table, SQL server takes the responsibility of increntin / adding auto number in = field.

Please advise if there is any way to = get this auto number from table. Like we have table inserted and deleted ... ( = getting maximum is not working in multiple user environemnt ) Is there any = stored procedure or variable i can query ?

thanks.
Khurram Alam


--=_NextPart_000_0048_01C47B19.C651B960--Select @.@.identity will give you the record just inserted.
You must be specifying some criteria for the deletes. When
you specify, do a select on that field.
>--Original Message--
>hi all
>I have auto number field in my table with title ID
>I wantted to know when we add a record in table, SQL
server takes the responsibility of increntin / adding auto
number in field.
>Please advise if there is any way to get this auto number
from table. Like we have table inserted and deleted ... (
getting maximum is not working in multiple user
environemnt ) Is there any stored procedure or variable i
can query ?
>thanks.
>Khurram Alam
>|||In SQL Server 2000, @.@.IDENTITY will work, however, in a high volume
multi-user environment, the BOL suggest that you use the SCOPE_IDENTITY()
function instead.
For more information, please see the Books Online
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4a01c47afa$aa5f6e10$a501280a@.phx.gbl...
> Select @.@.identity will give you the record just inserted.
> You must be specifying some criteria for the deletes. When
> you specify, do a select on that field.
>
> >--Original Message--
> >hi all
> >
> >I have auto number field in my table with title ID
> >I wantted to know when we add a record in table, SQL
> server takes the responsibility of increntin / adding auto
> number in field.
> >
> >Please advise if there is any way to get this auto number
> from table. Like we have table inserted and deleted ... (
> getting maximum is not working in multiple user
> environemnt ) Is there any stored procedure or variable i
> can query ?
> >
> >thanks.
> >Khurram Alam
> >
> >sql

How to get the error message rather than the code error

Hi,

After sending a request, I would get the possible error message.
Not the code @.@.error, nor the exact content of sysmessages, but the
message like it could be in the log file.

TIA,
TSalmTSalm (tsalm@.free.fr) writes:

Quote:

Originally Posted by

After sending a request, I would get the possible error message.
Not the code @.@.error, nor the exact content of sysmessages, but the
message like it could be in the log file.

"Sending a request", that sounds like you are issuing a call from a
client program. In that case you should be able to pick up the error
message. If you tell which client API you are using, I may even be
able to tell you how.

If you mean in a stored procedure, it depends on which version of SQL
Server you are on. If you are on SQL 2000, the answer is: you can't.

If you are on SQL 2005, you can use the new function error_message()
and its sisters: error_severity(), error_number(), error_state(),
error_procedure() and error_line(). But they only return data, if you
are in a CATCH handler, or a procedure called from a catch handler:

BEGIN TRY
-- Do something bad here
END TRY
BEGIN CATCH
SELECT error_message()
END CATCH

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to get the correct error message

Hi Everyone!

In my stored procedure I check for any errors during .
If there are any errors I log them. (by checking @.@.ERROR)

What my problem is, the error message that's been logged contain place holders like %s, %l, %d,etc. along with the error message.

How can I get the full error message, with place holders replaced by real error values/text?

Here is a sample what I get as the error:

* Error ID: 547
* Error Desc: %ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.

using this query
SELECT @.errdesc=description FROM master.dbo.sysmessages WHERE error = @.errid

When I run the stored proc in Query Analyser it gives the actual error message as:

Server: Msg 547, Level 16, State 1, Procedure sp_register_change, Line 366
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_TBL_EQUIPMENT'. The conflict occurred in database 'EquipManWT', table 'TBL_EQUIPMENT', column 'unitno'.
The statement has been terminated.

Thanks heaps,

rochanaFORMATMESSAGE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0kv9.asp),
Using RAISERROR (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_05_3f76.asp) ?|||Thanx for your reply buser

Well.. I could use FORMATMESSAGE if i know the parameter values to pass. Because the system issued the RAISERROR I do not have a way of retrieving (or passing) the parameter values to FORMATMESSAGE

I do not know if there is a way to get parameter values for the error raised by the sql server.

But I think there should be a way, because the Query Analyzer displays the full error message when I execute the stored proc in there.

I hope what I am telling is clear :)

Syntax

FORMATMESSAGE ( msg_number , param_value [ ,...n ] )|||I think not... :( Because who is catching error also should do form the message for a mistake... In this case do rase exception sql server...
You can write any errors (or some) to SQL server log file and then using DTS transfer this to some table...
I ask me to correct if it not so...
Sorry for my english :)
see sysmessages col:=dlevel (0x80) and
sp_altermessage [ @.message_id = ] message_number
, [ @.parameter = ] 'write_to_log'
, [ @.parameter_value = ] 'value'|||Hi Buser

One guy pointed me to this on another forum.

http://www.nigelrivett.net/spFormatOutputBuffer.html

And it seems to give the full error message, but not properly formatted.

Thanks for ur help|||Permissions
DBCC OUTPUTBUFFER permissions default only to members of the sysadmin fixed server role, who can see any SPID. Permissions are not transferable.

select @.cmd = 'dbcc outputbuffer(' + convert(varchar(10),@.spid) + ')'
exec (@.cmd)|||yes. can only view if u have admin rights :(

how to get LIST of fields in a report (designer)

Hi All,

When I'm building a report in report designer and get error message that says --error like --field textbox25--whats the easiest way to see all my report fields and quickly access them instead of clicking every field in a report?

Ideally a fields map with links to these fields.

thanks

Sonny

Hello Sonny,

If you press F4, it will open your Properties window; from here, you can select the report object you are looking for and it will select that object in your layout.

Hope this helps.

Jarret

how to get error message

Hello!

I am using T-SQL (SQL Server 2000). I would like to get error message

(for example stored in some string variable) when error appeares. I

found only system variable @.@.error which holds the error number of the

specific error. But I didn't find any way to get a message which is

written in SQL Qery analyzer in the Message window beneath the query

window.

Is it possible to get that message somehow? I would like to store it in my error table...

Thanks a lot,

Ziga

Hi

You can query the sysmessages table in the Master database for the error number that you receive, this will give you the description of the error. Books Online contains a description of the sysmessages table schema should you want to know what each field is, look under sysmessages.

HTH

|||Thanks a lot!

But anyway I would like to get the same message as is reported in sql

server (with object names - attribute name). Systables contains

attribute description with description of the messages with

placeholders. I don't know how to put some real object names on the

place of placeholders... Actually I can't put them, because sql server

should somehow (cause I don't know on which attribute it breaks).|||

Hi

I don't believe there is a way of finding out the exact same message as displayed by SQL Server when an error occurs (although I could be wrong). If your procedure does multiple things then you could use the @.@.Error to determine if an error has occurred at each step of the procedure and then you would have at least more knowledge of where the error occurred. Add to that, the error number returned, you should then be able to log sufficient error information.

HTH

|||Unfortunately, there is no way to obtain the system error message in TSQL. SQL Server 2005 has new exception handling features that provide you this capability. So best is to log server generated error messages on the client-side and capture only the error numbers on the server.