I am executing script like this. How to check for the errors if "master..xp_cmdshell @bcpCommand" fails. Is there any way to verify that BCP is completed successfully
SET @FileName = 'E:TestBCPOut.txt'
SET @bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -U -P'
it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.public void get_issid(string cse_email, string tech_email, string subject, string issue_details, string response, string solv_date, out int issid) { // Establish ConnectionSqlConnection oConnection = GetConnection(); // build the commandSqlCommand oCommand = new SqlCommand("get_issid", oConnection); oCommand.CommandType = CommandType.StoredProcedure; // ParametersSqlParameter paracse_email = new SqlParameter("@cse_email", SqlDbType.VarChar, 50); paracse_email.Value =cse_email; oCommand.Parameters.Add(paracse_email); SqlParameter paratech_email = new SqlParameter("@tech_email", SqlDbType.VarChar,50); paratech_email.Value = cse_email; oCommand.Parameters.Add(paratech_email);SqlParameter parasubject = new SqlParameter("@subject", SqlDbType.VarChar, 50); parasubject.Value = subject; oCommand.Parameters.Add(parasubject);SqlParameter paraissue_details = new SqlParameter("@issue_details", SqlDbType.VarChar, 500); paraissue_details.Value = issue_details; oCommand.Parameters.Add(paraissue_details);SqlParameter pararesponse = new SqlParameter("@response", SqlDbType.VarChar, 500); pararesponse.Value = response; oCommand.Parameters.Add(pararesponse);SqlParameter parasolv_date = new SqlParameter("@solv_date", SqlDbType.DateTime); parasolv_date.Value = solv_date; oCommand.Parameters.Add(parasolv_date);SqlParameter paraissid = new SqlParameter("@issid", SqlDbType.Int);paraissid.Direction = ParameterDirection.Output; oCommand.Parameters.Add(paraissid); try { oConnection.Open(); oCommand.ExecuteNonQuery();issid = int.Parse(paraissid.Value.ToString()); }catch (Exception oException) {throw oException; } finally { oConnection.Close(); } }
the stored procedure is:
create proc [dbo].[get_issid](@tech_email varchar(50), @cse_email varchar(50),@subject varchar(50),@issue_details varchar(500),@response varchar(500),@solv_date datetime, @issid int output) as select @issid=tech_response.issue_id from tech_response,issue_details where tech_response.tech_email=@tech_email and tech_response.cse_email=@cse_email and tech_response.subject=@subject and tech_response.issue_details=@issue_details and response=@response and solv_date=@solv_date and tech_response.issue_id=issue_details.issue_id requested to help in this
I'm unsure how to handle an SQL Exception correctly when the database is unavailable/offline. I have my aspx file with the C# code-behind, but all of the SQL stuff is done in a separate code file in the App_Code directory.E.g. CODE-BEHINDDatabaseModifier.deleteUser(username); DATABASEMODIFIER.cspublic static void deleteUser(string username){ SqlConnection conn = SqlLogin.SqlConnect; SqlCommand command = new SqlCommand("DELETE FROM <table> WHERE Username = '" + username + "'", conn); conn.Open() command.ExecuteNonQuery(); conn.Close()} Now, that code works perfectly, however if the database I'm connecting to is offline, an SQLException is thrown and because the SQL is handled in my DatabaseModifier class, I'm not sure how to handle it correctly.If I use a Try/Catch block in my code-behind, it doesn't get thrown because the error occurs in my DatabaseModifier class. If I use a Try/Catch block in my DatabaseModifier class, what can I put in the catch block that will inform the user of the database being offline and/or how can I perform a url redirection? Any help is greatly appreciated.
Hi there, Can anyone help me in catching @@error value. I have a stored procedure which return @@error value, I need to read that in my dataaccesslayer and act according to it. how do I catch the return value from stored procedure in my dataaccesslayer. if I am not wrong @@error return a bool value
dear friends i am writng a store procedure to insert into a two table .table 1 data inserted but when i inserting into a table2 it have some error at that time table 1 data also want to delete.give suggestions
Hi All, Please help me out how to implement the locking in below scenarioReq - There are two tables Table1 & Table2 If I will insert in table1 then related data fields will be auto updated in table2 , similarly based on the data in table2 table1 data needs to be updated. Now the sync of table1 & table2 is working fine.My prob is we are handling the updation/insertion from the UI screens . Two separate screen for each table. When we have multiple user accessing the screens say - User1 updates table1 and User2 updates table2 then we need to implement the locking so that at one time one screen will allow updation in the table1 and hence table2.The other screen shouldnt allow updation in table2 and hence in table1.This is very common locking functionality ...but am not getting any way to implement it , Please advise.Srain.
Hello to all, On my webPage I have Used one SQLDataSource to access DataBase. Now whenever some error occures it shows error page by default. I am not able to catch Errors and tackle in my way... Furthermore in this new structure of accessing DataBase even I do not know where to write Try... Catch...
I am writing a stored procedure that loads transaction logs to a database and I am having trouble trapping meaningful error messages. When ever the load fails, it gives me two error messages, the first one is meaningful and the second one just states that the load ended abnormally. Unforunately, when I capture the error using @@error after the load statement, it is the second error message that I am getting.
Is there anyway to trap the first error message as well?
I currently have defined a source server, a transformation, and a destination server using DTS. How and where do I create an error routine that would capture any and all errors that would occur. This would include connection error, transformation errors etc. I know the errors can be written out to a text file but I would like them written to a table on my destination server.
The DTS package will be called from an external program using the xp_cmdshell extended stored procedure. We are using it this way as a flag so if one transformation fails it will return a 1. If all are successful it will return a O. These will be our flags to check the errors table in the destination server.
Could someone tell me where this code is to go and what the code will look like. Samples would be the most help.
is there any way to do sometype of Error handling with in a Stored Proc? Example, If I have a deadlock, can I trap that error and execute other sql code or will always simply kill the stored proc?
I have a composite unique key on the audit_hub table that includes TimeStamp, UID, Type, Mailbox all as part of the unique key.
I’m trying to do inserts, and know that in some cases I will violate unique index.
I’m using stored procedure, and want to handle the error gracefully there – just move on.
Reading from text-based log files via .vbs and get back the nasty popup window.
Looks like I have it right but obviously I don’t considering how it’s (not) working.
I thought error handling would be the simplest way to avoid dupe records. Might be necessary to run the same log in more than once, and only want to add the new records since the last insert session.
Here’s what I have. It obviously simply halts and complains immediately after the insert attempt.
ALTER procedure eml_HubInsert @TimeStamp bigint ,@UID varchar(255) ,@Type varchar(255) ,@MailBox varchar(255) ,@ServerID varchar(50) as insert into audit_hub (TimeStamp,UID,Type,Mailbox,ServerID) values (@TimeStamp,@UID,@Type,@MailBox,@ServerID)
1 question: What Error-Handling msut i mplement in a trigger, who is updating permanently two tables in a database (with the follow commands: insert, delete and update) ???
Hi, Im Nithyananda working on a project with SQL server 6.0 Im having a stored procedure which inserts into a table . If I violate the primary key constraint on the table , I get a 2627 error. I would like to replace this error with my own.Could u tell me how to do it? Im an ORACLE guy and relatively new to SQL server. Is it possible for me to propogate the same error message to any front end? In my case ,Im using VC++ as front end. Could someone also tell me the way of calling a SQL stored procedure from VC++?
I have some stored procedures that insert information from a third party plugin that hooks into our database (so I can't do any client side validiation). The problem I am encountering is that some of the fields I use need to be in DateTime format. In order for SQL SERVER 2000 to be able to insert this field, the Date has to be in the correct syntax or an error is thrown.
Is there any way to do something like this in SQL SERVER 2000
Code:
DELCARE @SomeDate DateTime
Try Set @SomeDate = CONVERT(DateTime, Parameter1, 101) Catch return custom error describing what field is formatted wrong and exit stored procedure END
I am running some bcp copies through a Sql job. I am copying 35 tables in individual steps. However, sometimes the bcp step fails to copy the data, and I want the step to fail if the data is not copied properly. Is that possible? If so, how? Any help is greatly appreciated.
I have this stored procedure that I need to add error handling to. How would I do that??
REATE PROCEDURE November2000 AS SELECT TM#, LASTNAME, FIRSTNAME, FINALSUITDONE FROM dbo.EmployeeGamingLicense WHERE (FINALSUITDONE BETWEEN CONVERT(DATETIME, '2000-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2000-11-30 00:00:00', 102))
Hey Gang, got one for you. error message: Server: Msg 137, Level 15, State 2, Line 19 Must declare the variable '@EFFECTIVE_DATE'. Code: SELECTLOG_PK, LOG_REQUESTOR, LOG_REQ_PHONE, LOG_CHAR_60_1, LOG_CHAR_30_4, LOG_CN_FK, LOG_CREATE_DATE, LOG_REQUEST, (LOG_STATUS), (LOG_TYPE), LOG_FU_FK, LOG_FB_PK, LOG_LT_FK, LOG_CREATE_DATE FROMf_Requests WHERELOG_RQ_FK IS NOT NULL AND LOG_LT_FK <> 10174 AND LOG_RC_CODE = S AND (LOG_MOD_DATE > @EFFECTIVE_DATE OR LOG_CREATE_DATE > @EFFECTIVE_DATE)
i wrote this sql, seems v.straightforward to me. It works first time round, sets the foreigh key, but second time round should error out to the handler - but it just reports the errors to the message out and dosn't seem to fire the errhandler.
Its gotta be a simple mistake - perhaps you could show me.
I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.
When I go through the docs, i find that SQL Server 2000 operates three transaction modes: Autocommit transactions : Each individual statement is a transaction.
Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.
I wonder how to write atomic compound sql statement in the sql server. can anybody please help me on this....
I've managed to grouped by Category field nicely and added extra calculated column for handling to total plus as well as Total for each group plus added a new row for TOTAL in the TABLE and returned as follow:
Now I want to put another extra row underneath TOTAL for handling the calculated but the business rule is only for displaying from the first group for instance:
TOTAL: $3032 Calculated Average per fruits: $1010.67 ($3032 / 3 UNITS).
3 UNITS is coming from Units for Apple and Banana.
I don't know if I can do this cause i did try to use
But it's successfully compiled but the report result error (#Error) on that field only. Any ideas?
I thought a simple like this can be handled quite easily instead of creating a new report view type in the the same dataset to handle this particular extra line.
Can i any one help regd error handling while executing stored procedure.
Sample Sequence of execution :
CREATE procedure RBI_Control_sp as begin set nocount on
begin try BEGIN TRANSACTION --Truncating the Table in ramcovm392(fin_ods) exec fin_ods..trun_sp
--Data Transfer From the Live Server to Dw-Server exec fin_ods..RBI_Data_Transfer_sp
insert into fin_wh..Status_report([object_name],row,[date]) select 'Data Tranfer','SUCCESS',getdate()
COMMIT TRANSACTION end try
begin catch <b> [i need to insert the type of error in status report table]</b> rollback transaction insert into fin_wh..Status_report([object_name],row,[date]) select 'Data Tranfer','FAILURE',getdate() end catch
I am calling some stored procedures from ASP. These strored procedures have to deal with lots of deletes and updates. So i have thought of implementing transaction commits and rollbacks. But if a rollback occurs in these stored procedures, i want to get a value back to asp page, based on this value i will run the next stored procedure.
hello. below is a very simple SP i have coded to insert a user into my database. i have created a unique index for the username column on my database table. if the username already i want to convey this to my C# code which is attempting the insert. i am using a combination of TRY/CATCH, RAISERROR and @@ERROR here to achieve my aim. could somebody please look at my code and tell me if the error handling code is OK, or is it overly complicated?
AS
SET NOCOUNT ON
DECLARE @Error int;
BEGIN
BEGIN TRY INSERT INTO Users( Username, Password, FirstName, Surname, DateBirth, Email, Id_Country) VALUES( @Username, @Password, @FirstName, @Surname, @DateBirth, @Email, @Id_Country) END TRY BEGIN CATCH
Hi, I am using SQL Server 2000 and the clients would like to archive and remove some of the old records from certain tables in the database. I would like to know the methods of setting ID as autogenerated and if the ID will be initialized to 0 again if records are removed, and if autogenerated IDs reuse missing IDs (e.g. I have ID 1-10, remove ID 2, will the next new record be ID 2?)
We need the ID to keep incrementing and not reuse missing IDs, nor reinitialized once we've archive the old records, and I was thinking of storing all the IDs in a table. Just asking for the possibility of SQL Server autogenerated ID does this.
need example on how to add event handling condetion in a package using ssis. my package runs in a loop. the loop creates a connection to diff servers and runs a dynamic query. i want to add a feature which would let the loop run in the event of connection failure for one server.
I have a stored procedure that inserts a new record into a table. The table it inserts into has a unique constraint that utilizes an identity column. Occassionally, this constraint gets violated due to the fact that the app running my stored procedure isn't the only application inserting into the table. Ideally, we would want to change the design to avoid this scenario but these are legacy applications that will eventually be sunsetted. My question is this; is there a way to handle the unique key constraint violation and prevent an exception being thrown in the app calling the stored proc? I know I can check for the error code and handle it within the stored proc but my app still gets an exception caught by the calling method. Can I prevent this from happening? I want for the insert to attempt again and if successful continue processing and not produce the exception. Any help would be greatly appreciated.
in analysis services when i proccess 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 that doesnt match with the dimension table how can i handle this error ? can i insert instead someyhing else so that the proccess can continue and not failed ?
Hi all, working as an intern at MS for a year (SDET) and I've been asked to find a way of transfering items from one sharepoint lists to another (intranet to extranet) without access to the underlying SQL servers'. So what I've done is use an access databaes as a bridge creating active views of the two sharepoint lists. I wrote a C# console application that executed some SQL on the bridge to copy data between the two sharepoint lists, this was great could handle updates, deletions and additions of new items. Now I've been asked to make this update process 2 way on a certain field (status) I've been trying for a couple of days now and I'm having no joy. The method I was using (polling) allowed for the first couple of status changes (used 2 statements) but then wrote back the wrong way see below for example SQL:
Extranet owned statuses: Declined, Approved, Not Started, In-Progress, Completed. Intranet owned statuses: Needs Review, MS Declined
UPDATE intranet JOIN extranet ON (blahblah=blahblah)SEt intranet.status=extranet.status WHERE extranet.status IN (all extranet owned statuses);
UPDATE extranet JOIN intranet ON (blahblah=blahblah)SEt extranet.status=intranet.status WHERE intranet.status IN (all intranet owned statuses);
Can anybody tell me where I'm going wrong and offer any suggestions?
What is the best way to handle schema changes as database needs change.What I need to find out iswhat is the best way to write database interface code in order to beflexible to schema changes. Would having stored procedures as an interfacemechanism be best so that old app software will still be compatible? It isimpractical for us to upgrade all apps simultaneously, so if a new column isadded to a table, old apps will not fill in this column while newer apps whosupport it will.Thanks
I m new to the sqlserver.In Oracle we can handle exceptions like this
declare name varchar(20); begin select ename into name from emp where eno=&eno; dbms_output.put_line(name); exception when no_data_found then dbms_output.put_line('No Entry'); end; /
We will get the message No Entry When corrsponding employee number dosent exists.
In Sqlserver how to handle these things.
Specifically my requirement is with select statement(Like above program)
Hi, I'm trying to decide on the best method for dealing with errors in a DTS package. It is sufficient to retrieve Step failure information after package execution but I have tried both methods. Specifically, these methods are detailed in http://support.microsoft.com/kb/240221. Another article - http://support.microsoft.com/default.aspx?scid=kb;en-us;321525 - details the need to establish a single event sink to avoid "unexpected behaviour". I have used the code as described but noticed very little difference between post execution checking using GetExecutionErrorInfo and the PackageEventSink Interface. Using the Event Sink does retrieve one extra type of error, ie.
"Error at Destination for Row number 2. Errors encountered so far in this task: 1". This error will always give the row number as the last row in the file, in this case an excel file source. This is not really useful, but I don't want to detach the Event Sink because of the possibility of "unexpected behaviour". Could somebody advise? First is it possible to retrieve the line number, and secondly can anyone detail what an example of this unexpected behaviour might be? Finally, is there a way to retrieve a unique key constraint violation from a package if it occurs? It is only caught in the generalised way and produced as "Error at Destination...". Thanks in advance
