Ive started using try/catch in my t-sql code now and I rather like it, since im a C# developer. I read that some errors with an error code below 10 will not cause the catch block to be entered. What kind of errors does this include ?
View 2 RepliesIs there any way to emulate the try/catch mechanism that SqlServer2005 provides using SqlServer2000?
Or more simply,
Is there any kind of IGNORE_ERROR or CONTINUE_ON_ERROR setting for SqlServer 2000?
Thanks
- John
Hi,
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
Hope someone could help.
Hi everybody:
I wanted to handle with the deadlock issue by using Try/Catch, the the try/Catch block cannot capture this kind of deadlock:
code in connection1:
declare @i int
set @i = 0
WHILE (1=1)
begin
ALTER PARTITION SCHEME PartationedTableA NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION PartationedTableA () SPLIT RANGE (14661)
ALTER PARTITION FUNCTION PartationedTableA () MERGE RANGE (14661)
end
code in connection2:
declare @rowcount int
while 1=1
begin
begin try
select @rowcount =count(0) from PartationedTableA where col1=50021
end try
begin catch
print error_number()
end catch
end
run them at the same time in SSMS, the code in cn2 would get a error in several seconds:
Msg 1205, Level 13, State 55, Line 10
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried using TRY/CATCH to capture anoter deadlock whose Error_State is 45, and it works fine.
Does anybody know why Try/Catch cannot capture my deadlock, does the different state make senses?
Thanks.
In my stored procedure I'm calling a buggy and flaky stored procedurethat comes from a third party. When I run my stored proceure from QA,I'm getting a whole buch of errors raised inside the third party one.Is there any way I could just ignore them, so that if I run my SP fromQA, only errors from my code, if any, show up?TIA
View 2 Replies View Relatedif there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the table
View 4 Replies View RelatedHi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:data.txt -b1 -m100 -c -t, -Sdatabase -Uuser -Ppassword
here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors
please help.
thanks
madhu
I use a CLR external function to call a payment gateway, for some reason, I could not get any status sometime, check the following code, I expect I get either -2 or -3 in status no matter what error happens - include the gateway error or the CLR error since I have try catch there. Do you think the following code could miss any problem?
begin try
....
select @postresult=dbo.linkclr_u_webpost(@posturl,@postdata)
update link_payments set ccagentcode=@ccagentcode,status=-2,postresult=@postresult where id=@paymentid
....
end try
begin catch
select @message=
'<br>ErrorNumber: '+cast(ERROR_NUMBER() as varchar)+char(13)+
'<br>ErrorSeverity: '+cast(ERROR_SEVERITY() as varchar)+char(13)+
'<br>ErrorState: '+cast(ERROR_STATE() as varchar)+char(13)+
'<br>ErrorProcedure: '+isnull(ERROR_PROCEDURE(),'')+char(13)+
'<br>ErrorLine: '+cast(ERROR_LINE() as varchar)+char(13)+
'<br>ErrorMessage: '+isnull(ERROR_MESSAGE(),'')+char(13)
update link_payments set status=-3,postresult=@message where id=@paymentid
return -1
end catch
Hello - the very nature of this question seems to make no sense I know - but we received a huge volume of data (29 tables) in flat file format. I first imported them into MS Access because of its portability and it seemed to be more forgiving on imports. Now I have a complete MS Access DB with all tables, so I figured importing to SQL server should be a snap. However, on the import, I had 14 tables import successfully, and 15 failed!
Here is an example of one of the error messages I received:
Insert Error, Column 3 - status 6; Data Overflow...this was on a date/time field in access, and here is the data contained in the referenced row/column: "8/19/4999"
the year "4999" is obviously the problem (at least i think), and I have no idea why this successfully imported to MS Access, but not to SQL Server....
what i'd like to be able to do (not the best practice, i know) for now is ignore these types of errors - and just force SQL server to take the data straight from MS Access and replicate it. We received this data from a 3rd party, and there's no telling how many data entry errors like this could be in each table - many of the tables have over 500,000 rows, and i don't want to have to go through fixing each of these errors by hand...anyone have any ideas?
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel source will fail.
I have it so that for each iteration if the transform was successful the file is moved to an archive directory, and if it fails the file is moved to a different directory.
But I don't want the package to be marked as failed. For the control flow tasks I have set the individual components to FailPackageonFailure = False, and for the Data Flow tasks I have set ValidateExternalMetadata = False.
It no use to set the MaxErrorCount higher because I can't guarantee how many files will be processed and how many might fail.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.
Thanks
Hi,
I have a big table and want to make a plausibility check of itīs data.
Problem is, that my query stops, if there is an unexpected datatype in one of the rows. But that is it, what i want to filter out of my table with that query and save the result as new correct table.
How can i write a parameter to my query SQL Code, that if a error occurs, the querry resumes and the error line will not displayed in my final querry overview?
In my books and on the net, i donīt found something to this theme ;-(.
Thx in advance.
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as:
check documents table to determine whether a full text index for SQL's full text engine has been created.
If it has not, query the documentText table (which is the table for my in-house full text search)
If it has, use the full text indexing engine
My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error:
Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed.
In my test lab, I tried:
1. creating the full text index
2. creating the stored procedure
3. deleting the ful text index
which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option.
My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure?
Here's a code snippet stripped down to the bare minimum to generate the error:
CREATE PROCEDURE [My_FullTextSearch]
@Term VarChar(1000)
AS
BEGIN
SET NOCOUNT ON;
IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1
BEGIN
Select [DocumentID]
from [DocumentText]
where [Term] like '%' + LTRIM(@Term) + '%'
END
ELSE
BEGIN
Select [key] from FREETEXTTABLE(Documents, Contents, @Term)
END
END
I have a sqldatasource and I allow deletions that could cause a constraint violation. I want to capture this error however, I am using the sqldatasource bound to my datacontrol. I dont know where to catch the error. Unfortunately there is no OnError event for the control... There is no code behind for this at this time. I am hoping there is a way to do this without completely rewiring everything.
My thanks in advance,
When a catchable error occurs in a try block, control is passed to theassociated catch block. While I am then able to examine properties ofthe error using such functions as ERROR_NUMBER() in the catch block,the error-logging scheme used in my company requires that the actualerror be raised so that it can be picked up by components in the nexttier. I appear to be unable to do that -- I can raise some other errorthat has all of the properties of the original except the error number.For example, division by zero raises error 8134. If the code thatproduces that error is enclosed in a try block, I seem to be unable toraise that error from the catch block. Of course, if I never used thetry block to begin with this wouldn't be a problem, but then wewouldn't have the advantages of this structure as it applies to othererrors, some of which we may wish to handle differently. WhileTry-Catch looks great as a way of allowing us to handle errors in acustomized way and to avoid having all errors be passed up to ourmiddle tier, it seems that we are unable to pass ANY such errorsforward, and that is a problem for us, too. Is my understanding ofthis situation correct, or is there some way around this problem, e.g.,is there any way I could have raised error 8134, in the above example?Thanks.
View 2 Replies View Relatedhi all,
All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement
my problem catch does not catch the error. There is no way to find out teh what is causing this error
SqlCeConnection sqlcon = new SqlCeConnection("
Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"
);
SqlCeCommand sqlcmd = new SqlCeCommand();
sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');
sqlcmd.Connection = sqlcon;
SqlCeDataReader sqldr = null;
try
{
sqlcon.Open();
//use nonquery if result is not needed
sqlcmd.ExecuteNonQuery(); // application crashes here
}
catch (Exception e)
{
base.myErrorMsg = e.ToString();
}
finally
{
if (sqlcon != null)
{
if (sqlcon.State != System.Data.ConnectionState.Closed)
sqlcon.Close();
sqlcon.Dispose();
}
}//end of finlally
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
I'm not looking for details or code, just general direction I should go. Here is my goal:
I have a SQL stored procedure that returns data which I will need to add spaces to, then end up with a space delimited text file.
This txt file will then be needed to be saved and emailed.
Also, I would like to be able to schedule this in the Task Scheduler for once a month execution.
Is this all just one big crazy idea?
Thanks for any help.
Andrew
i want to take 70-028 test.
but i still have some questions about administration.
can anybody help me?
Question:
(NY1-----T1-----NY2)-----56kbps-----(Seattle-----T1-----SanFrancisco-----T1-----Los Angeles)
NY1=NewYork1,NY2=NewYork2,SF=SanFrancisco,LA=Los Angeles
A. NY1=pub/dist; NY2,Seattle,SF,LA as subscriber
B.NY1=pubs; NY2=dist;Seattle,SF,LA as subscriber
C.NY1=pubs; SF=subs/dist/pubs; NY2,Seattle,LA as subscriber
D.NY1=pubs; LA=dist; something not workable
which one is correct answer, and why?
How to do the questions like this?
In the company that I work there are two offices, one is outside the city and the other is in the city. They are connected trough a DS0 using frame relay. The thing is that the comunication is very slow (the SQL server is in the city) and we want to add a new server at the office outside the city and we want at the end of the day replicate the data between the two servers so every server has the actual data. We didn't use primary keys, instead of that we used identities (autoincremental value administered by the server). What kind of replication should I use and why? (I'm new with SQL Server). There are 3 kinds: Snapshot, Transactional and Merge.
View 4 Replies View RelatedI have 20 tables (the same schema)
I use view to search it with union operator.
now i have indexes on all of the tables on 1 kolumn (nvarchar)
what kind of index i should use to make it fastest
index on view ?
Im looking to create a Stock Levels table which goes through each record of my Part table updating the corresponding Part in the Stock Level table.
I know how to perform all the Count calculations etc, if someone could just give me an example of a suitable loop that would be great
cheers.
original data type is string ,format like MM/dd/YYYY,also there exists null or "" value
I need to convert them into datetime,format like YYYY--MM-dd
any idea about this?
thanks in advance
Hi everyone,
Is there any way to know how is stored a package before do loadpackage, loadfromsql... bla,bla??
I mean, is there any property for Package class where you can see how it is stored?
Thanks in advance for your comments,
Hello, I created web site using VWD 2005 Express, and I use 'Create New user account' control for signin up. I need to know what kind of database is ASPNETDB.MDF ' in order to set it on server? I created ASPNETDB.MDF (database?) when I first created user in ASP.Net Web Site Administration Tool . What tipe is ASPNETDB.MDF database? . I have my web site on server, and DB is not working, because it is not set on server.I have this databases available on my server:Microsoft Access Unlimited Microsoft SQL Server 2000 1 x MySQL 1 x PostgreSQL 1 xDSN/ODBC Unlimited SQL Database Users Unlimited Which one you recommend should I choose for members database (ASPNETDB.MDF)?Thank you, Beezgetz
View 11 Replies View RelatedHi,
I have a problem in a Stored Procedure SQL query im writing, basically what I want is something that works like this
WHERE intField IN (@strCommaSepList_Of_Integers)
First I tried this:
SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (@strListUserGroupsAllowed))
But if I passed a 25,26 value to the last paramenet is gave an error saying:
Syntax error converting the varchar value '25,36' to a cllumn of data type int.
How can I get this to work??
If for example I manually put the WHERE IN values it works, ike this I mean:
SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (25, 36))
But thats no use as I have to pass the list of values dynamically...
Please help!!
- Carl
Hi,
I am unable to understand how can I create a new filegroup?
I know how to move certain text/ntext/images column to a new file group - thru design table but my problem is how to create a new filegroup?
Do I have to use Alter Table/Alter Database for this or there is any other better way?
TIA
Jai.
Here's a basic overview of my Table
ID identity int
ParentID int
Name varchar
Now, the root level items will have a ParentID of NULL, and the child nodes will have it's parent's ID in it's ParentID column (a simple self-referencing relationship).
However, when I select it, I would like to Order by ID, but then have all it's children right after it. For example, if this is the unsorted view:
Code:
ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
3 <NULL> Test 2
4 1 Test Child 2
5 3 Test 2 Child 1
I would like the sorted to look like:
Code:
ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
4 1 Test Child 2
3 <NULL> Test 2
5 3 Test 2 Child 1
Is this possible with my current structure? If not, how could I change it?
Hey all!
This may sound like a weird one but i couldn't think of a better audience to ask!
If you are a SQL Server DBA (or were, or aspire to be, or play one on T.V., etc.) and the CIO of your 2.something billion $ a year company has offered you an 1 hour forum to sit down and ask him anything you want with the pretense of getting a straight answer, what would you ask?
I know this offers an excellent opportunity to cut up a bit...and do so if you must (keeping us entertained around here is important too!)...but i'm looking for work related, SQL related, direction, strategy, etc, etc type stuff.
Not: 'Whats it like to drive a $250k 'benz to work everyday?' OR 'Can i have .5% of your $2,000,000 bonus this year?'
While i genuinely have those questions in my mind...i'm not looking to waste his $5,000 an hour ass's time - and I only have 1 hour to chew his ear off.
What do ya'll think?
Hi,
What kind of functions i cannot use within a trigger?
Thanks for all your replys for my previous posts.
If we don t have the right to create an SP, can we create a dynamic in memory kind of SP
what s the syntax pls?
Thanks a lot
Hi all,
Just can't figure this out. I'll try not to give a long-winded explanation (I hope), let's say this is the table, for example:
[Table1]
RowNumber | Value
1 | 4
2 | 6
3 | 3
4 |10
5 | 6
6 | 5
7 | 8
8 | 8
9 | 2
10 | 6
I want to write a query that creates a column whose values are similar to (ROW_NUMBER -1), and resets each time a value is present.
Using the above example, 6 will be the test value. So, I want to know how many rows it takes till 6 is repeated.
Output would be:
RowNumber | Value | Spread
1 | 4 | 1
2 | 6 | 0
3 | 3 | 1
4 |10 | 2
5 | 6 | 0
6 | 5 | 1
7 | 8 | 2
8 | 8 | 3
9 | 2 | 4
10 | 6 | 0
I think PARTITION BY can be used somewhere, with the ORDER BY on the [RowNumber] - but I'm just not sure on which ranking function to use, and how to reset the Ranking on the number 6 (above example).
Any help would be appreciated. This has been a mind-teaser for me, and I give
Thanks,
Denvas
I am gonig into interview for a junior developer position. The role involves a lot of SQL based work. Training is on the job, and they know I am new to this, but they want to know what I can do with SQL server by wednesday, and obviosuly I stand a better chance if I can do a reasonable amount by then.I am assuming I can practise with offline databases, so I would like to do that. Also I was wondering if there were any simple example databases I can load up.
I have downloaded and installed the software, and would like to know how to connect and create a test database.
There will be a small test in the interview and the questions are:
1. SQL Management and Data Extraction
For this task you will need to be familiar with database tables, data types and constraints. There will be some administration work using SQL Management Studio along with some T-SQL queries. You will need to show use of the SELECT, INSERT, UPDATE and DELETE statements. If you do not have SQL Server, you can download the express edition for free at the following URL. http://msdn2.microsoft.com/en-us/express/bb410791.aspx
2. XSLT
For this task I will be asking you to produce some HTML output displaying the data from an XML file. The concept is similar to ASP.
3. Database Design
You will be given a scenario for a company that requires some database software for the smooth running of their organisation. You will need to plan and design a data structure to accommodate these requirements. You will be allowed to spend as much time on this part of the test as you wish. Key information here is going to be database normalization.
These questions dont make a whole lot of sense to be at the moment, so would appreciate a breakdown in simpler terms.
This job will be a fantastic opportunity for me to get into development, and would appreaciate any help that you guys have to offer, thanks in advance.
In document for encryption in SQLServer with application XP_Crypt, i see file with extension .pem when using private key or public key. How to create file .pem. Please explain me private key or public key.
View 1 Replies View Related