|
Hello,
I have not really coded for error trapping the past since the applications were not that big. Currently, the app being worked on is huge with multiple phases development. I wanted to find out the best practices for error trapping, some simple forms and some more complex ones. I need to start using them
It is also ok, to write them to the database tables if possible.
I know I can google this as well but some times it takes a longer amount of time to find the correct link/article.
Thank you for your time!
modified on Monday, August 1, 2011 3:43 PM
|
|
|
|
|
There is no way to trap everything.
And it is non-sensical as well.
So you need to determine some actual implementation cases where it is reasonable.
vanikanc wrote: It is also ok, to write them to the database tables if possible.
Still depends on what you mean by error.
It is certainly possible for some cases.
Also why is this just a database relevant question? If the system is large then there will be other parts of the system which will fail in relation to the database. For example bouncing the database is very likely to cause ripple failures in a 24x7 system unless those other systems are specifically designed to deal with that.
|
|
|
|
|
Even if there are some scenarios i would like to trap for, which syntax would I use?
Like C# has a Try, Catch, Finally block I use for displaying som error messgaes.
|
|
|
|
|
SQL server has try/catch.
But you need to be careful with it.
One might think that they could put that in every proc as thus record errors but I am rather certain that in many procs the errors that might occur would be the ones that would be compounded by attempting another write to the database. For example a table space error.
|
|
|
|
|
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
|
|
|
|
|
All the data validation is done in the business layer so the data hitting the database is clean and I write perfect stored procedures so I don't need error trapping in the database.
Once a procedure is coded it generally does not break unless there is a change in structure. The few cases where I can identify this may be prevalent is where I use try/catch structures.
I do have a trigger based logging system but that is not error trapping.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hello,
I want to pass the sort as an input parameter to the stored proc.
I am constructing a dynamic sql and executing it. The problem is more like a syntax issue, where in I an checking for certain value in sql.
----
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status='N'
ORDER by'
set @sql1 = @sql1 + ' ' + @sort
---------
How would I check for additional conditions in this sql? I tried to enclose N in double still did not work.
Many thanks!
|
|
|
|
|
you should use '''N'''
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Thank you for your response.
It should be within two single quotes, like
''N''
|
|
|
|
|
That would be ''''N''''
try:
declare @sql1 varchar(2000)
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''''N''''
ORDER by'
print @sql1
Output:
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''N''
ORDER by
|
|
|
|
|
When you wish to embed single quotes within dynamic strings, use two consecutive single quotes, like this:
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''N''
ORDER by'
set @sql1 = @sql1 + ' ' + @sort
EDIT: Corrected a mistake.
modified on Monday, August 1, 2011 2:49 PM
|
|
|
|
|
I don't know but just the double single quotes worked for me.
''N''
|
|
|
|
|
Seems to be some confusion in the back and forth in the answers above.
A SQL text literal is expressed like the following example.
'abc'
If you want to embed a single tick in the above literal you MUST escape it. The standard SQL (ANSI) idiom for that is as follows (but some databases offer other ways.)
'a''bc'
So two single ticks become one.
Three ticks is NEVER correct.
|
|
|
|
|
Priority ways email finding
I have following tables in project
Customer => Customer details
=> Coloums are Customer Name, Customer Type (Gold, Silver, Platinum)
Contact => Can have multiple contacts to a customer
=> Coloums are Customer Id, Name, email, Designation (D1 to D10).
we need to create an SP (MS SQL 2005 ) which returns priority 1 and priority 2 emails of customer (Customer Id will be passed as input) based on following criteria .
When Customer Type is "Gold" Customer, choose email based on following Designation
1. D1, D3,
2 D4, D6
3, D5, D7, D8
Logic :
a). if D1 exist select D1 and move to set 2,
D1 NOT exist select D3 and Move to set 2
D3 NOT exist move to set 2
b) if D4 exist select D4 and move to set 3,
D4 NOT exist select D6 and Move to set 3
D6 NOT exist move to set 3
Note : if we got an email in first step and this step retun two emails. No need to iterate step 3
c) reapeat above logic with step 3 designations sequentially
Continue searching until we get 2 mails.
For "Silver" Customer choose email based on following designation
1. D1, D2,
2 D6, D7
3, D8, D9, D10
For "Platinum" Customer choose email based on following designation
1. D1, D2,
2 D8, D9
3, D3, D4, D10
I am not sure i explained logic very well. Is there any algorith for this type of logic.
|
|
|
|
|
Create a bridge table between CustomerType and EmailDesignation and fill it with data that represents the relationship you specified above. You can then create a query that joins these three tables to get the data that you require.
You also need to create a new table with email designations and their priority.
SELECT TOP 2 C.CustomerName, Co.CustomerType, E.Email, E.Designation
FROM Customer C
INNER JOIN CustomerEmailBridge B ON C.CustomerType = B.CustomerType
INNER JOIN Email E ON B.EmailDesignation = E.EmailDesignation
INNER JOIN EmailPriority P ON E.EmailDesignation = P.EmailDesignation AND B.CustomerType = P.CustomerType
WHERE (C.CustomerID = @CustomerID)
ORDER BY P.Priority
modified on Tuesday, August 2, 2011 2:48 PM
|
|
|
|
|
Shameel,
Simple join will not work here. I need the output based on logic mentioned. It a priority based search.
I am looking for best and optimized way to do it.
|
|
|
|
|
Have a look at my updated answer. Sorry, I missed the priority thing.
|
|
|
|
|
Hi shameel,
this will not work . priority for same designation is different for different customer type
|
|
|
|
|
A little change makes it work. Just add a CustomerType column to the Priority table and include the CustomerType in the <code>WHERE clause.
|
|
|
|
|
Hello,
I connected two computers in LAN 1st having IP 192.168.1.1 and 2nd having IP 192.168.1.2 and i am having sql server management studio express 2005 installed on 1st computer now when i run exe from 192.168.1.2 it throughs an error like.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
where as it runs perfetly on 1st computer.
my conection string is
Public DBConn As ADODB.Connection
DBConn.ConnectionString = "Provider=MSDATASHAPE; Data Provider=SQLOLEDB.1;Data Source=TOSHIBA\SQLEXPRESS; Initial Catalog=" & App.Path & "\AVON_AIR_Data.mdf; User ID=sa; Password=; Integrated Security=SSPI; Persist Security Info=False;"
DBConn.Open DBConn.ConnectionString
Please suggest
Thank you.
|
|
|
|
|
|
For remote connections, the Initial Catalog attribute must specify the database name alone, you cannot attach a local database. And the other problem is, you can either specify Integrated Security or User ID/Password, but not both.
|
|
|
|
|
|
Hi Guys,
My data source is FoxPro for Unix using ODBC connection i would like to bring it to sql 2005 by SSIS 2005.
Can any one explain how i could use Dynamic SQL using SSIS Datareader Like for instance
"SELECT * FROM " @[User::TableName] expression???
Please explain the Steps to follow.
Thank you
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
