checks or constraints?

My table has two columns called offer_date and availability_date

and I want to put a constraint that availability_date should

always be earlier than offer date and if values are inserted into

the table it should return DBMS_server_output:

"offer_date cannot be earlier than availability_date"

Name Null? Type
PROPERTY_ID VARCHAR2(10)
TYPE VARCHAR2(10)
ASKING_PRICE NUMBER(10,2)
SELLING_PRICE NUMBER(10,2)
OFFER_DATE DATE
AVAILABILITY_DATE DATEYou will most likely have to implement using a TRIGGER since you have a specific message you want to display. However, since this is a non-dbms specific area, and not all DBMSs will allow you to implement a trigger, I will not recommend any specific trigger syntax.|||An alternative could be to store the difference of the two instead of the availability_date (possibly with a view on top of this, returning the two dates).
Then you could use a simple check constraint ( >= 0) on that difference.|||I'm 99.9% certain you are using Oracle, right?

You can create a check constraint like this:
alter table x add constraint dates_chk check (offer_date >= availability_date);
However, that will not give you the specific error message you mentioned, it will give you:

ORA-02290: check constraint (MYSCHEMA.DATES_CHK) violated

It is easy to trap that error message in an application, see what constraint was violated, and present a better message. I would not advocate using a trigger instead merely to allow a bespoke message. (Aside: it would be nice if Oracle allowed you to define bespoke error messages for each constraint, wouldn't it!)

I note you said this: "it should return DBMS_server_output [the message]". I presume you mean DBMS_OUTPUT.PUT_LINE? If so, that is wrong: never use DBMS_OUTPUT to handle error messages, it is only suitable for simple debugging etc. If you were writing a trigger you should call raise_application_error like this:
raise_application_error(-20001,'offer_date cannot be earlier than availability_date');

Checking The Users Server Role

I would like to determine if a particular user has sysadmin server
role. Is there a way to do this via the connection string? Currently
our code checks if a login is valid using SQLDriverConnect, however we
need to be certain that the user can login and modify the schema.

Is it possible to fetch a user's server role to determine if it has a
sysadmin server role?Look for the IS_SRVROLEMEMBER function in Books Online.

Razvan|||Thanks.

Checking Jobs Status through Query

I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.

Thanks in Advance

Jacx

you can use opennrowset or openquery to

query other servers

job information are stored in msdb and you can invoke the

following to query job information

use msdb
select * from sysjobs
select * from sysjobhistory

|||

tually i wanted a code to find the urrent job status of a particular job which i am inerested in. Kindly help me with that.

Thanks

Jacx

|||

use msdb
select * from sysjobs sj join --<change the * to get only the columns you need
sysjobhistory sjh
on sj.job_id=sjh.job_id
where name like 'W%' <modify this for the job name

check the run status

check this link

http://msdn2.microsoft.com/en-gb/library/ms174997.aspx

Checking if DB Connection is active or not

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata NarayanaIn the above one, please read it as SELECT 1 instead of SELECT * from 1

Venkata Narayana wrote:

Quote:

Originally Posted by

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.
>
This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)
>
My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.
>
a) Create Table
b) Use SELECT query
c) Drop table
>
You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.
>
One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.
>
So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?
>
Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.
>
Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,
>
Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});
>
The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.
>
What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:
>
try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}
>
This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).
>
Any comments on this would be appreciated.
>
Regards,
Venkata Narayana

|||Venkata Narayana wrote:

Quote:

Originally Posted by

In the above one, please read it as SELECT 1 instead of SELECT * from 1
>
Venkata Narayana wrote:

Quote:

Originally Posted by

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata Narayana

Whatever you do to test a connection, you want it fast and non-taxing
of DBMS resources, so even if you have table-create permissions, you
don't want to do that. The fast thing is a DBMS-specific query:

Sybase, MS: select 1
Oracle: begin null; end; or select 1 from dual
DB2: select 1 from sysdummy
etc.

You can always call DatabaseMetaData.getDatabaseProductVersion()
to figure out what DBMS-specific SQL to send.
If you really must be DBMS-neutral, you can call DatabaseMetaData
getTables() with arguments that define a non-existent table. The DBMS
will still have to look, but the search for a single table
'NONEXISTENT'
won't be too bad.
Lastly, note that whatever you use to test a connection, the
connection
may fail the very instant after your test succeeds, so your subsequent
code will have to be able to deal with a broken connection anyway. In
practice you would only want to test connections that had been sitting
idle for a significant period.

Joe Weinstein at BEA Systems

Checking for transposed numbers

Does anyone have a UDF or Stored Procedure that checks for transposed
numbers in a group?Scott Levine from Atlanta?

Here's an example that might help, depending on your exact definition of
"transposed". This query finds rows with exactly the same digits in any
order.

CREATE TABLE SomeTable (x INTEGER NOT NULL)
/* Sample data */
INSERT INTO SomeTable (x) VALUES (3412)
INSERT INTO SomeTable (x) VALUES (4567)
INSERT INTO SomeTable (x) VALUES (4321)

SELECT T1.x
FROM SomeTable AS T1
JOIN SomeTable AS T2
ON
REPLICATE('0',LEN(T1.x)-LEN(REPLACE(T1.x,'0','')))
+REPLICATE('1',LEN(T1.x)-LEN(REPLACE(T1.x,'1','')))
+REPLICATE('2',LEN(T1.x)-LEN(REPLACE(T1.x,'2','')))
+REPLICATE('3',LEN(T1.x)-LEN(REPLACE(T1.x,'3','')))
+REPLICATE('4',LEN(T1.x)-LEN(REPLACE(T1.x,'4','')))
+REPLICATE('5',LEN(T1.x)-LEN(REPLACE(T1.x,'5','')))
+REPLICATE('6',LEN(T1.x)-LEN(REPLACE(T1.x,'6','')))
+REPLICATE('7',LEN(T1.x)-LEN(REPLACE(T1.x,'7','')))
+REPLICATE('8',LEN(T1.x)-LEN(REPLACE(T1.x,'8','')))
+REPLICATE('9',LEN(T1.x)-LEN(REPLACE(T1.x,'9','')))
=
REPLICATE('0',LEN(T2.x)-LEN(REPLACE(T2.x,'0','')))
+REPLICATE('1',LEN(T2.x)-LEN(REPLACE(T2.x,'1','')))
+REPLICATE('2',LEN(T2.x)-LEN(REPLACE(T2.x,'2','')))
+REPLICATE('3',LEN(T2.x)-LEN(REPLACE(T2.x,'3','')))
+REPLICATE('4',LEN(T2.x)-LEN(REPLACE(T2.x,'4','')))
+REPLICATE('5',LEN(T2.x)-LEN(REPLACE(T2.x,'5','')))
+REPLICATE('6',LEN(T2.x)-LEN(REPLACE(T2.x,'6','')))
+REPLICATE('7',LEN(T2.x)-LEN(REPLACE(T2.x,'7','')))
+REPLICATE('8',LEN(T2.x)-LEN(REPLACE(T2.x,'8','')))
+REPLICATE('9',LEN(T2.x)-LEN(REPLACE(T2.x,'9','')))
AND T1.x<>T2.x

--
David Portas
SQL Server MVP
--|||>> Does anyone have a UDF or Stored Procedure that checks for
transposed numbers in a group? <<

Can we get a better spec and sample data? Numbers are not transposed;
They are abstractions which do not have an ordering. Letters and
Numerals can be transposed. There are pairwise and disjoint
transposes; do you care what kind of transpose? Is there a maximum
length?

Short strings can be done with a table look up, which will be in
parallel as a JOIN and much faster than a proprietary UDF.

Checking for free disk space and getting mail when it falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

Thanks

Hi Vaddi -

Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.

HTH...

checking for free disk space and getting mail , when falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

ThanksHere's what I use:
set nocount on

declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold

set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'

if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message

end

drop table #FreeSpace
go