Error reporting from SP to .NET code, how?

Hey

ASP.NET 2.0 + MS Sql Server 2005 Express (that version which comes with VS2005)

This code below fails. The Delete method executes a stored procedure, but somehow this SP fails. The SP shown here is a short version of the actual SP. As you see from the code below, there are no way the Delete method can know if SP executed successfully. I assume I just could have returned @.@.error, but that just gives an error number, and not the description of the error??.. And how should I capture exceptions like these?? How should code this SP so it report the error??

Any suggestions??

public override void Delete(System.Guid id, int user)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("SendMessage", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.id", SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@.user", SqlDbType.Int).Value = user;
}
}

ALTER PROCEDURE dbo.DeleteMessage @.id uniqueidentifier, @.user int
AS
BEGIN
UPDATE table_A set column_A = 1 WHERE Id = @.id;
DELETE FROM table_B WHERE Id = @.id;
IF (@.@.error = 0)
COMMIT TRAN;
ELSE
ROLLBACK TRAN;
END

there is a couple of way to report that what happened, you can use execute nonquery to get number of rows affected by last SQL command, you can return from your procedure single cell using SELECT with value 0 for success and 1 for error and finally you can use output parameters and read its values after procedure executes. See T-SQL help in SQL management studio express which is very good and you can find everything you wont.

error on DTS scheduling

I am pretty new to SQL server. I have setup a DTS package that would
delete and then copy data from SQL server to DB2 on mainframe. I
installed the Host Intergration Server 2000 client to get OLEDB for
Db2. The DTS is working fine, when I run it myself from the server.
But when I schedule it it get the following error:
Executed as user: LOMBARD\sqlexec. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)
Error string: Internal NetLib Error Error source: Microsoft
DB2 OLE DB Provider Help file: Help context: 0 Error
Detail Records: Error: -2147217900 (80040E14); Provider Error: 0
(0) Error string: Internal NetLib Error Error source:
Microsoft DB2 OLE DB Provider Help file: Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package
execution complete. Process Exit Code 1. The step failed.
I have been trying to solve this for the last couple of days, but not
getting anywhere...could somebody please help ?
Thanks
RogerThis is because (most probably) because when you schedule a package it runs on the server so everything you need must be on the
scheduling server. You probably have the drivers installed on your workstation which is why it works when you run it.
Have a look here
http://support.microsoft.com/?kbid=269074
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"sql rookie" <anytasks@.gmail.com> wrote in message news:1104952964.389735.87880@.z14g2000cwz.googlegroups.com...
>I am pretty new to SQL server. I have setup a DTS package that would
> delete and then copy data from SQL server to DB2 on mainframe. I
> installed the Host Intergration Server 2000 client to get OLEDB for
> Db2. The DTS is working fine, when I run it myself from the server.
> But when I schedule it it get the following error:
> Executed as user: LOMBARD\sqlexec. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)
> Error string: Internal NetLib Error Error source: Microsoft
> DB2 OLE DB Provider Help file: Help context: 0 Error
> Detail Records: Error: -2147217900 (80040E14); Provider Error: 0
> (0) Error string: Internal NetLib Error Error source:
> Microsoft DB2 OLE DB Provider Help file: Help context: 0
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package
> execution complete. Process Exit Code 1. The step failed.
>
> I have been trying to solve this for the last couple of days, but not
> getting anywhere...could somebody please help ?
> Thanks
> Roger
>|||No , I executed the DTS on the server itself. I am using Terminal
server to login to the server. I can execute the DTS via EM and also
using DTSRUN on command line. But when I schedule it , it throws this
error.|||You,
it sounds to me like a problem with users.
My aasumption is that you installed drivers for DB2 logged-in as
Administrator.
So everything is working when you run the DTS package as Administrator.
But when the package runs as LOMBARD\sqlexec it cannot find the drivers.
Maybe you can try and install the drivers also logged-in as sqlexec.
Danijel
"sql rookie" <anytasks@.gmail.com> wrote in message
news:1104955716.152873.292710@.z14g2000cwz.googlegroups.com...
> No , I executed the DTS on the server itself. I am using Terminal
> server to login to the server. I can execute the DTS via EM and also
> using DTSRUN on command line. But when I schedule it , it throws this
> error.
>|||There is a new developement on this:
I tried to change the sqlserver and agent service account to Local
systems and then the scheduling was working fine. I created a new
domain account , added it to the admin group to the server, made that
domain user the service account for sqlserver and agent. Gave sysadmin
privelege to the domain user. Now when I schedule it , I doesn't work.
I gives the same error
Am I doing something wrong ?
Please help

error occur while delete large volum records

I had write a ActiveX service to delete several tables and those records are more than 100000. When I test it by deleted 1000 records it is ok, but once the volum is increase until 100000, it will give me a error message said timeout operation fail.

how can i overcome this problem. please!!!!Are you deleting the entire table or only a part of it.|||only part of it|||Originally posted by yawhum80
only part of it

Can you post the delete query.. i believe an index would help a lot over here.|||Sub DeleteByReqID(TableName, myConn)
dim rsResult
Set rsResult = CreateObject ("ADODB.Recordset")

strSQL = "DELETE * FROM " & Trim(TableName) & " WHERE ReqID >= " & getFirstReqID & "AND ReqID <= " & getLastReqID
Set rsResult = myConn.Execute(strSQL)

Set rsResult = Nothing

End Sub|||Is reqid the primary key in this table .. if not ... a clustered index on the same would be my recomendation|||not all the table is using reqid as a primary key, may i know what is the problem?|||i mean to say that if you create a clustered index on the reqid column .. it might help speed up your query . The reason i asked whether reqid was the primary key was coz a clustered index is automatically created on the primary key column in case one doesn't exist already...

Can you post the ddl for the table|||i had test it in isql and all the datas are successful to deleted but once i using DTS ActiveX script it will pop up the error message.

no matter i using primary index, cluster index or non primary and non cluster index.

is this because the DTS ActiveX script has a time limit for a operation?

Error Msg 547

Hi,
I'm not an expert in SQL, I just need it to run a blackberry server.
I need to run this follwing command to clear the database :
delete from mdsagconfig
But it does not work and I receive the following message :
Msg 547, Level 16, State 1, Server XXXX, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_ServerConfig_MDSAGConfig' . The conflict occurred in database 'DataBase
Name', table 'ServerConfig', column 'MDSAGConfigId'.
The statement has been terminated.
Any idea ?
--
MerciYou have a foreign key defined on column MDSAGConfigId in table ServerConfig
that references a column in table MDSAGConfig, and you are trying to delete
a
row from MDSAGConfig with a dependent row in ServerConfig. You can delete th
e
row in ServerConfig first, then delete the row from MDSAGConfig.
Linchi
"Chelims" wrote:

> Hi,
> I'm not an expert in SQL, I just need it to run a blackberry server.
> I need to run this follwing command to clear the database :
> delete from mdsagconfig
> But it does not work and I receive the following message :
> Msg 547, Level 16, State 1, Server XXXX, Line 1
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_ServerConfig_MDSAGConfig' . The conflict occurred in database 'DataBas
e
> Name', table 'ServerConfig', column 'MDSAGConfigId'.
> The statement has been terminated.
> Any idea ?
> --
> Merci

Error Msg 547

Hi,
I'm not an expert in SQL, I just need it to run a Blackberry server.
I need to run this follwing command to clear the database :
delete from mdsagconfig
But it does not work and I receive the following message :
Msg 547, Level 16, State 1, Server XXXX, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_ServerConfig_MDSAGConfig' . The conflict occurred in database 'DataBase
Name', table 'ServerConfig', column 'MDSAGConfigId'.
The statement has been terminated.
Any idea ?
--
MerciYou have a foreign key defined on column MDSAGConfigId in table ServerConfig
that references a column in table MDSAGConfig, and you are trying to delete a
row from MDSAGConfig with a dependent row in ServerConfig. You can delete the
row in ServerConfig first, then delete the row from MDSAGConfig.
Linchi
"Chelims" wrote:
> Hi,
> I'm not an expert in SQL, I just need it to run a Blackberry server.
> I need to run this follwing command to clear the database :
> delete from mdsagconfig
> But it does not work and I receive the following message :
> Msg 547, Level 16, State 1, Server XXXX, Line 1
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_ServerConfig_MDSAGConfig' . The conflict occurred in database 'DataBase
> Name', table 'ServerConfig', column 'MDSAGConfigId'.
> The statement has been terminated.
> Any idea ?
> --
> Merci

error message Only members of sysadmin role are allowed to update .....

Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below

by the way I am a member of the sysadmin group

thanks in advance

km

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() End of inner exception stack trace

It looks like you are getting this error because the report has a schedule associated with it and most likely you recently changed the way you connect to the Report Server database. To fix this you will need to set the owner for all of the Report Server SQL Agent jobs to be the user that RS is using to connect to it's database.

Error message as a result of a DELETE

I'm executing a delete in a table and the result is the error 431:
Could not bind foreign key constraint. Too many tables involved in the query
.
There are no triggers associate with the table. What's this could be?
Thanks.
RenataHi
I assume this is a table! Are there any foreign keys referencing it?
John
"renata" wrote:

> I'm executing a delete in a table and the result is the error 431:
> Could not bind foreign key constraint. Too many tables involved in the que
ry.
> There are no triggers associate with the table. What's this could be?
> Thanks.
> Renata