Bhavani Taninki (11/21/2007)

Select

Income,

Expense,

(

(Cast(Income as Float) - Cast(Expense as Float)) / Cast( ISNULL(NULLIF(Income,0),1) as Float)

) * 100 As percentageexpense

FROM

(

select income =4, expense =3

union all...

Worst advice ever! That's a real rookie...

I think this may be simpler:

while 1=1

begin

fetch ...

if @@fetch_status <> 0 break

if dbname in ('northwind','pubs')

begin

.. send email or whatever ...

continue

end

... rest of stuff in cursor loop ...

end

Maybe there a message box popping up. Since there is no GUI interface, there is no way to respond to it, and it would just wait until you kill...

Jeff, you have to force the conversion to numeric before the division operation.

This code shows that with a check for divide by zero also.

select

income,

expense,

[Percent] = ((income-expense+0.00)/nullif(income,0))*100.0

from

(

-- Test Data

select income =4,...

I have a really simple solution, but I'm just to lazy to post it.

Take a look at the script on the link below. It will return a detailed analysis of all database files on a server.

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

From SQL Server Books Online:

"For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently...

The only time you should have a database without foreign keys is when there are no logical relationships between the entities that your data represents.

In other words, never.

Run the script on the following link to see where the space is being used.

Script to analyze table space usage:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

These all give me the same query plan:

select * from authors where au_id <> '172-32-1176'

select * from authors where au_id != '172-32-1176'

select * from authors where not au_id = '172-32-1176'

This method seems simple enough:

select

[DateTime] = convert(datetime,stuff(a.DT,13,8,''))

from

-- Test Data

(

select DT= '14:52:17.376 CET Wed Oct 8 2007' union all

select DT= '14:54:00.737 CET Wed Oct 8 2007' union all

select DT= '14:08:53.213 CET...

select

current_execution_status

from

openrowset('SQLOLEDB','SERVER=(LOCAL);Trusted_Connection=yes;',

'set fmtonly off;execute msdb.dbo.sp_help_job') rmt

where

name = 'My Job Name'

This code shows how to convert a date to an Academic Year starting on September 1 of each year.

select

a.MyDate,

[Academic Year] = year(dateadd(mm,-8,a.MyDate))

from

MyTable a

where

a.MyDate between '20070831' and '20080902'

Results:

MyDate ...

The function on the link below is designed for loading a date table.

Date Table Function F_TABLE_DATE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519