How to get the Training Error from the Model?

Hi, everyone here.

I am trying to get the training error of the model processed which can reveal how much the model fits the cases. The training error can reveal how many cases (from training set) are classified correctly. The lower traing error is, the more the model fits the training set. (Maybe overfitted) But I found it hard to get. I saw the life chart in AS 2005 which I am not quite understand and don't know how to code it in my program.

Is there some way to getting traing error or predicting error?

I am now using this awful way to get the training error:

select t.*,CollegeTree.CollegePlans as pred

from collegetree
prediction join
openquery(DSource,'select * from CollegePlans') as t
on CollegeTree.StudentID = t.StudentID and
...
where t.CollegePlans = CollegeTree.CollegePlans;

and then use datareader.ItemCount to get the count of cases which classified correctly.

Keyword: train error,predict error, data mining, analysis service

Instead of the lift chart, you could use the classification matrix (it is next to the lift chart in BI dev studio).

The Classification Matrix gives you the exactly the number of correct vs. incorrect classifications

|||Thx, it is what I need. I am still wondering how to program with the classification matrix. Can I just get it from SQL statement or AMO? And that classification matrix need a prediction procedure that may take too long time. Could I get the training error just after the model trained?|||Currently, there is no way to get the training error just after the model trained.
The Classification Matrix is a stored procedure call. The syntax is a bit complex (an XML encoding of the queries used to compute the matrix) and the easiest way to get the statement is to
- launch SQL Server Profiler
- Connect to the Analysis Services server
- Launch the classification matrix in the tools
- look in the profiler for a statement beginning with CALL Microsoft.AnalysisServices.System.DataMining.GetConfusionMatricesUsingDatasource

Then, you can execute that statement as any other DMX statement|||

Thx for your advice, it's really helpful.

I'm now using

Code Snippet

select flattened topcount((select [SUPPORT] from NODE_DISTRIBUTION),[SUPPORT],1) from DModel.content where NODE_TYPE=4

to export the number of positive cases for every rule, i.e. the max [support] for the leaf node( NODE_TYPE=4),

and add them up.

Code Snippet

While DSReader.Read()
pCase = pCase + DSReader(0)
End While
DMTrainingError = pCase/TotalCasesCount

I hava checked the result with the BI Classification Matrix, which prooved my idea.

The rules after processing the model are stored in a table named <Model Name>.content, and the column of NODE_DISTRIBUTION is a nested table containing the cases distribution of each class, and the prediction follows the most probable class with the max support.

So pick up the max value of support in every NODE_DISTRIBUTION and add them up, then you will get the number of positive cases. The ratio of positive cases in all cases can be used as Training Error.

How to get the Training Error from the Model?

Hi, everyone here.

I am trying to get the training error of the model processed which can reveal how much the model fits the cases. The training error can reveal how many cases (from training set) are classified correctly. The lower traing error is, the more the model fits the training set. (Maybe overfitted) But I found it hard to get. I saw the life chart in AS 2005 which I am not quite understand and don't know how to code it in my program.

Is there some way to getting traing error or predicting error?

I am now using this awful way to get the training error:

select t.*,CollegeTree.CollegePlans as pred

from collegetree
prediction join
openquery(DSource,'select * from CollegePlans') as t
on CollegeTree.StudentID = t.StudentID and
...
where t.CollegePlans = CollegeTree.CollegePlans;

and then use datareader.ItemCount to get the count of cases which classified correctly.

Keyword: train error,predict error, data mining, analysis service

Instead of the lift chart, you could use the classification matrix (it is next to the lift chart in BI dev studio).

The Classification Matrix gives you the exactly the number of correct vs. incorrect classifications

|||Thx, it is what I need. I am still wondering how to program with the classification matrix. Can I just get it from SQL statement or AMO? And that classification matrix need a prediction procedure that may take too long time. Could I get the training error just after the model trained?|||Currently, there is no way to get the training error just after the model trained.
The Classification Matrix is a stored procedure call. The syntax is a bit complex (an XML encoding of the queries used to compute the matrix) and the easiest way to get the statement is to
- launch SQL Server Profiler
- Connect to the Analysis Services server
- Launch the classification matrix in the tools
- look in the profiler for a statement beginning with CALL Microsoft.AnalysisServices.System.DataMining.GetConfusionMatricesUsingDatasource

Then, you can execute that statement as any other DMX statement|||

Thx for your advice, it's really helpful.

I'm now using

Code Snippet

select flattened topcount((select [SUPPORT] from NODE_DISTRIBUTION),[SUPPORT],1) from DModel.content where NODE_TYPE=4

to export the number of positive cases for every rule, i.e. the max [support] for the leaf node( NODE_TYPE=4),

and add them up.

Code Snippet

While DSReader.Read()
pCase = pCase + DSReader(0)
End While
DMTrainingError = pCase/TotalCasesCount

I hava checked the result with the BI Classification Matrix, which prooved my idea.

The rules after processing the model are stored in a table named <Model Name>.content, and the column of NODE_DISTRIBUTION is a nested table containing the cases distribution of each class, and the prediction follows the most probable class with the max support.

So pick up the max value of support in every NODE_DISTRIBUTION and add them up, then you will get the number of positive cases. The ratio of positive cases in all cases can be used as Training Error.

sql

How to get the null values in my stored procedure i am getting error dbnullvalue on front

I have the following stored proc. which i am using on the front end to get all the record from table:

if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

Thank you very much.Note use of IsNull() function.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
IsNull(r.ReviewerComment,''),
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

|||You can use the case statement in sql to look at a field before its returned. For example, you could do this (uses the pubs db)

select au_lname, au_fname,
AuthorCity = Case when (city is null) then '' else city end
from authors

if any city values are null, they'll come back as a '' empty string.

Hope this helps,

Scott

How to get the name of the error column from the errorcolumn output

Hi,

Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?

Regard,

pritesh

Possible, but not trivial. MS seem to consider this a design-time lookup, so you could use something that queries the package structure to get the info. Simon has written a transform that does this as well, note it needs both outputs to be able to get all the metadata. http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||Thanks for the info , i will try to implement it. It sounds useful !|||

Anonymous wrote:

Thanks for the info , i will try to implement it. It sounds useful !

And if you want to continue posting here, please change your display name from Anonymous to something else. Microsoft staff will be cleaning up those accounts with display names of Anonymous.

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 stored procedure to ignore an @@error.

I'm trying to pass back to the calling program, the value of @.@.error from a
stored procedure. The stored procedure seems to work properly, but the
calling program diverts program flow to the "catch" portion of the try/catch
section since it knows an error occured. I'd like for it to just continue on
as if an error didn't occur, and if it wants to check for an error, it could
test the @.err output parameter.
I pushed the button twice to generate a duplicate key error on the 2nd push.
Here's the stored procedure I'm using:
----
-- Insert a single record into Categories3
----
CREATE PROC pr_Categories3_Insert
@.CategoryID int,
@.CategoryName nvarchar(30),
@.Description ntext = NULL,
@.Picture image = NULL,
@.err INT OUT
AS
SET NOCOUNT ON
SET @.err = 0
INSERT Categories2(CategoryID, CategoryName, Description, Picture)
VALUES (@.CategoryID, @.CategoryName, @.Description, @.Picture)
SELECT @.err = @.@.error
RETURN
GO
----
--
And here's the calling program that sets up the parameter list and calls it
using the data access blocks.
private void button3_Click(object sender, System.EventArgs e)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
try
{
try
{
connection = GetConnection(txtConnectionString.Text);
}
catch
{
MessageBox.Show("The connection with the database can′t be established",
"Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
using (SqlTransaction trans = connection.BeginTransaction())
{
// Establish command parameters
SqlParameter param_CategoryID =
new SqlParameter("@.CategoryID", SqlDbType.Int);
param_CategoryID.Value = 1111;
SqlParameter param_CategoryName =
new SqlParameter("@.CategoryName", SqlDbType.Char, 10);
param_CategoryName.Value = "Test 1";
SqlParameter param_Description =
new SqlParameter("@.Description", SqlDbType.Char, 50);
param_Description.Value = "ABCD";
SqlParameter param_Picture =
new SqlParameter("@.Picture", SqlDbType.Image);
param_Picture.Value = null;
// @.err
SqlParameter param_err =
new SqlParameter("@.err", SqlDbType.Int);
param_err.Value = null;
param_err.Direction = ParameterDirection.Output;
try
{
// Call ExecuteNonQuery static method of SqlHelper class
// We pass in SqlTransaction object, command type, stored procedure
name, and a comma delimited list of SqlParameters
int @.RC = 0;
// Perform the address Insert operation
@.RC = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure,
"prWS_Categories2_Insert",
param_CategoryID,
param_CategoryName,
param_Description,
param_Picture,
param_err);
int @.err = (int) param_err.Value;
if (@.err != 0)
{
trans.Rollback();
txtResults.Text = "Transfer Error, ID = " + param_CategoryID.Value + ";
@.RC=" + @.RC + "; paramErr = " + param_err.Value;
}
else
{
trans.Commit();
txtResults.Text = "Transfer Completed, ID = " + param_CategoryID.Value + ";
@.RC=" + @.RC + "; paramErr = " + param_err.Value;
}
}
catch (Exception ex)
{
// throw exception
trans.Rollback();
txtResults.Text = "Transfer Error";
throw ex;
}
}
}
catch(Exception ex)
{
string errMessage = "";
for( Exception tempException = ex; tempException != null ; tempException =
tempException.InnerException )
{
errMessage += tempException.Message + Environment.NewLine +
Environment.NewLine;
}
MessageBox.Show( string.Format( "There are some problems while trying to
use the Data Access Application block, please check the following error
messages: {0}"
+ Environment.NewLine + "This test requires some modifications to the
Northwind database. Please make sure the database has been initialized using
the SetUpDataBase.bat database script, or from the Install Quickstart optio
n
on the Start menu.", errMessage ),
"Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
}
finally
{
if(connection != null)
connection.Dispose();
}
}
}"Vern" <Vern@.discussions.microsoft.com> wrote in message
news:462AF1BE-1B3E-40D2-BCD8-1A4FA853772A@.microsoft.com...
> I'm trying to pass back to the calling program, the value of @.@.error from
a
> stored procedure. The stored procedure seems to work properly, but the
> calling program diverts program flow to the "catch" portion of the
try/catch
> section since it knows an error occured. I'd like for it to just continue
on
> as if an error didn't occur, and if it wants to check for an error, it
could
> test the @.err output parameter.
> I pushed the button twice to generate a duplicate key error on the 2nd
push.
> Here's the stored procedure I'm using:
> ----
--
> -- Insert a single record into Categories3
> ----
--
> CREATE PROC pr_Categories3_Insert
> @.CategoryID int,
> @.CategoryName nvarchar(30),
> @.Description ntext = NULL,
> @.Picture image = NULL,
> @.err INT OUT
> AS
> SET NOCOUNT ON
> SET @.err = 0
> INSERT Categories2(CategoryID, CategoryName, Description, Picture)
> VALUES (@.CategoryID, @.CategoryName, @.Description, @.Picture)
> SELECT @.err = @.@.error
> RETURN
> GO
> ----
--
> And here's the calling program that sets up the parameter list and calls
it
> using the data access blocks.
> private void button3_Click(object sender, System.EventArgs e)
> {
> // SqlConnection that will be used to execute the sql commands
> SqlConnection connection = null;
> try
> {
> try
> {
> connection = GetConnection(txtConnectionString.Text);
> }
> catch
> {
> MessageBox.Show("The connection with the database cant be established",
> "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
> return;
> }
> using (SqlTransaction trans = connection.BeginTransaction())
> {
> // Establish command parameters
> SqlParameter param_CategoryID =
> new SqlParameter("@.CategoryID", SqlDbType.Int);
> param_CategoryID.Value = 1111;
> SqlParameter param_CategoryName =
> new SqlParameter("@.CategoryName", SqlDbType.Char, 10);
> param_CategoryName.Value = "Test 1";
> SqlParameter param_Description =
> new SqlParameter("@.Description", SqlDbType.Char, 50);
> param_Description.Value = "ABCD";
> SqlParameter param_Picture =
> new SqlParameter("@.Picture", SqlDbType.Image);
> param_Picture.Value = null;
> // @.err
> SqlParameter param_err =
> new SqlParameter("@.err", SqlDbType.Int);
> param_err.Value = null;
> param_err.Direction = ParameterDirection.Output;
>
> try
> {
> // Call ExecuteNonQuery static method of SqlHelper class
> // We pass in SqlTransaction object, command type, stored procedure
> name, and a comma delimited list of SqlParameters
> int @.RC = 0;
> // Perform the address Insert operation
> @.RC = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure,
> "prWS_Categories2_Insert",
> param_CategoryID,
> param_CategoryName,
> param_Description,
> param_Picture,
> param_err);
> int @.err = (int) param_err.Value;
> if (@.err != 0)
> {
> trans.Rollback();
> txtResults.Text = "Transfer Error, ID = " + param_CategoryID.Value + ";
> @.RC=" + @.RC + "; paramErr = " + param_err.Value;
> }
> else
> {
> trans.Commit();
> txtResults.Text = "Transfer Completed, ID = " + param_CategoryID.Value +
";
> @.RC=" + @.RC + "; paramErr = " + param_err.Value;
> }
> }
> catch (Exception ex)
> {
> // throw exception
> trans.Rollback();
> txtResults.Text = "Transfer Error";
> throw ex;
> }
> }
> }
> catch(Exception ex)
> {
> string errMessage = "";
> for( Exception tempException = ex; tempException != null ; tempException
=
> tempException.InnerException )
> {
> errMessage += tempException.Message + Environment.NewLine +
> Environment.NewLine;
> }
> MessageBox.Show( string.Format( "There are some problems while trying
to
> use the Data Access Application block, please check the following error
> messages: {0}"
> + Environment.NewLine + "This test requires some modifications to the
> Northwind database. Please make sure the database has been initialized
using
> the SetUpDataBase.bat database script, or from the Install Quickstart
option
> on the Start menu.", errMessage ),
> "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
> }
> finally
> {
> if(connection != null)
> connection.Dispose();
> }
> }
> }
>
Hello -
If the error is "Violation of unique key...", I don't think you can stop SQL
Server from throwing it.
To sidestep the problem, you could test beforehand in the stored procedure
whether a conflicting row exists - the test and update should really be
wrapped in a transaction.
Another sidestep would be to create a new method in C# dedicated to calling
the stored procedure, and this method would have a try..catch that would
deal with the exception and prevent it reaching button3_Click().
I think this issue will be easier to deal with in SQL Server 2005 - it has
try..catch built in to T-SQL.
Regards,
Simon|||I did see where 2005 has the try/catch block, and was wishing it was
available now.
I was going to test to see if the key exists first, and write the new record
if it doesn't, but when 100's of people are entering data at the same time,
there is still a chance that two people could be inserting the same record a
t
the same time. The first persons attempt will get through, and the second
person will get an error exception.
I like the idea of creating a module that calls the stored procedure so it
can have it's own try/catch block.
Thanks,
Vern

> Hello -
> If the error is "Violation of unique key...", I don't think you can stop S
QL
> Server from throwing it.
> To sidestep the problem, you could test beforehand in the stored procedure
> whether a conflicting row exists - the test and update should really be
> wrapped in a transaction.
> Another sidestep would be to create a new method in C# dedicated to callin
g
> the stored procedure, and this method would have a try..catch that would
> deal with the exception and prevent it reaching button3_Click().
> I think this issue will be easier to deal with in SQL Server 2005 - it has
> try..catch built in to T-SQL.
> Regards,
> Simon
>
>

How to get started on Microsoft SQL Server Management Studio Express

Hi,

Was trying to open AdventureWorks when i got this error: "There is no editor available "C: Program Flies.....\AdventureWorksDW_Log.LDF'
Make sure the application for the file type(.LDF) is installed.
How can this error be corrected? I also have some error on mdf not installed.May i know how should be the initally steps to be done inorder for me to view this database.
And if i do have a database from microsoft access, what are the steps that i need to do inorder to get it export to Microsoft SQL Server Management Studio Express. Please advise as i really new to it. Thanks!

Hi,

if you want to let the database be controlled by the instance you will have to do a right click on the database node > Attach Database > Select the Database files. The instance will be automatically attached and you will be able to access the database through the server Explorer.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Thanks for the info.Btw, how do we attach access database which is mdb format?
The form design that i have in access database...can this be viewable in microsoft sql server management studio express. Please advise.

|||

Hi,

I have managed to attached the AdventureWorks data. But it seem like there are some restrictions. Must we set any rights to user so that we can view the AdventureWorks data? Please advise. Thanks.

|||

hi,

kumalla wrote:

Thanks for the info.Btw, how do we attach access database which is mdb format?
The form design that i have in access database...can this be viewable in microsoft sql server management studio express. Please advise.

you can not attach an Access database to a SQL Server instance... you have to use the Upsize Wizard or similar tool to import the JET db into a new SQL Server database..

regards

|||

hi,

kumalla wrote:

Hi,

I have managed to attached the AdventureWorks data. But it seem like there are some restrictions. Must we set any rights to user so that we can view the AdventureWorks data? Please advise. Thanks.

start reading BOL at http://msdn2.microsoft.com/en-us/library/ms187648.aspx .. you can there find the whole security architecture to understand permissions and requirements to connect to SQL Server instances, databases and individual objects and their data..

regards

How to get Procedure call text from within SQL Stored Procedure

Hi,
I'm using SQL Server 2005.
I have implemented some SPs with the TRY..CATCH structure and each time
that I have an error, I just log it into a dedicated LogError table.
I would like to add in LogError a column containing the command that
was used by the user (containing the Procedure Name and the calling
parameters)
Is there any way to perform it ?
Thanks
PFI
have a look at
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
The bits with
dbcc inputbuffer
and
fn_get_sql
Get the sql executed if it is available.
dbcc inputbuffer is more likely to have the user command.
Neither is guaranteed.
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot.
dbcc inputbuffer was exactly what I was looking for.
PFI
nigelrivett a crit :

> have a look at
> http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
> The bits with
> dbcc inputbuffer
> and
> fn_get_sql
> Get the sql executed if it is available.
> dbcc inputbuffer is more likely to have the user command.
> Neither is guaranteed.
> www.nigelrivett.net
>
> *** Sent via Developersdex http://www.codecomments.com ***

How to get Procedure call text from within SQL Stored Procedure

Hi,
I'm using SQL Server 2005.
I have implemented some SPs with the TRY..CATCH structure and each time
that I have an error, I just log it into a dedicated LogError table.
I would like to add in LogError a column containing the command that
was used by the user (containing the Procedure Name and the calling
parameters)
Is there any way to perform it ?
Thanks
PFIhave a look at
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
The bits with
dbcc inputbuffer
and
fn_get_sql
Get the sql executed if it is available.
dbcc inputbuffer is more likely to have the user command.
Neither is guaranteed.
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***|||Thanks a lot.
dbcc inputbuffer was exactly what I was looking for.
PFI
nigelrivett a =E9crit :
> have a look at
> http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
> The bits with
> dbcc inputbuffer
> and
> fn_get_sql
> Get the sql executed if it is available.
> dbcc inputbuffer is more likely to have the user command.
> Neither is guaranteed.
> www.nigelrivett.net
> > > *** Sent via Developersdex http://www.developersdex.com ***

How to get Procedure call text from within SQL Stored Procedure

Hi,
I'm using SQL Server 2005.
I have implemented some SPs with the TRY..CATCH structure and each time
that I have an error, I just log it into a dedicated LogError table.
I would like to add in LogError a column containing the command that
was used by the user (containing the Procedure Name and the calling
parameters)
Is there any way to perform it ?
Thanks
PFIhave a look at
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
The bits with
dbcc inputbuffer
and
fn_get_sql
Get the sql executed if it is available.
dbcc inputbuffer is more likely to have the user command.
Neither is guaranteed.
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot.
dbcc inputbuffer was exactly what I was looking for.
PFI
nigelrivett a =E9crit :

> have a look at
> http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
> The bits with
> dbcc inputbuffer
> and
> fn_get_sql
> Get the sql executed if it is available.
> dbcc inputbuffer is more likely to have the user command.
> Neither is guaranteed.
> www.nigelrivett.net
>=20
>=20
> *** Sent via Developersdex http://www.codecomments.com ***

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 job history/error info. from a custom .exe being run by the job?

How to get job history/error info. from a custom .exe being run by the job?

We're using SQL Server 2005 and SQL Server Agent to run some jobs. These jobs execute a custom .exe program (written in C#).

What we're wondering is, is there any way to send status/history information from the custom .exe back to the job that kicked it off?

We're trying to provide execution info. to show if the job succeeded or if some error occurred in the .exe (thus the job didn't complete successfully).

Thanks!

Have the custom app logs to a file then when the job exits, load the logs/histories into a sql table.

How to get Extended error information from stored procedure

Hi

I have a stored procedure in SQL server 2005. It works fine when I execute it from the Management Studio.
But when executing it from ASP.NET code like this:

.... Of course more code is executed before this call ....
int retVal =this.odbcCreateDataBaseCommand.ExecuteNonQuery();

retVal is -1. But -1 doesn't really tell me what the problem is?

Is there anyway to get extended error information so I can figure out whats going wrong?

(The stored procedure was working fine in SQL server 2000 before I upgraded to SQL server 2005. I use .NET Framework 1.1 and ODBC Sql Native Client to access the 2005 server.)

Regards

Tomas

My own thought...
Maybe .NET Framework 1.1 and SQL Server 2005 and ODBC SQL Native Client have poor compatibility...

Anyone out there with experience/insight?

Thanks
Tomas

How to Get Error Output from and OLE DB Command Destination

I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.

However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.

I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).

Any help would be greatly appreciated.

To get rows down the error output you change the ErrorRowDisposition property for the input to be redirect row. Have you done this? If not go the last page of the Advanced Editor, select the Input, and change the ErrorRowDisposition property.|||

I reviewed your suggestion of changing the ErrorRowDisposition value to RD_RedirectRow and that is where the issue is. I view the Advanced Editor for the OLE DB Command destination object and expand the Input Columns under OLE DB Command Input and see several input columns. However, the problem is every one of those columns has an ErrorRowDisposition=RD_NotUsed and the field is greyed out so I am unable to change the setting. Would I need to change any settings in the source or data conversion objects to allow these values to be editable?

|||Select the input and stop there, don't expand the columns. The setting is on the input which is in effect the parent for the columns.

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.