EXEC master.dbo.xp_servicecontrol 'start', 'SQLServerAgent'

It makes no difference.

I have 20 odd TSX servers and I've had my MSX server (an old one for DBA use only) down for hours at a time.

The TSX servers...

Yep, extra quote on line 9 cause an error.

But otherwise Crystal does fall over when there appears to be multiple recordsets returned

See "renaming servers" in BOL for more info, but that doesn't say to restart the SQl service

I saw xpstar being loaded in the event log, but how does this affect the INSERT? Run it twice in the sp? Use WAITFOR?

In fact, search the SQl Server 2000 KB for "8198" just in case

Check Q241643

http://support.microsoft.com/default.aspx?scid=kb;en-us;241643&Product=sql2k

Also see "renaming servers" as you are doing part of this.

The SQL service will need restarted as well, BOL does not say this

Sometimes this occurs when SQL Server Agent can not authenticate the user who owns/starts the job. Search the MS KB for "xp_logininfo", I can't remember the article directly

I've 30+ servers.

I keep 8 days on site on server. Each DB is backed up at least once a day, upto every 15 minutes.

I also have an older server that...

Have you installed any Service Packs for SQL 7?

Our latest verison of SQL 7, SP4 is 7.00.1063

SP3 is 7.00.961

You'll probably have to apply the SP before the HF

Cheers

Shawn

Interesting. I'll use that myself in future.

I tried it with 5 rows and it was inconclusive, too busy to try with any more

Cheers

Shawn

Hi Frank

Good point.

Using float (any of float(1), float(24) or float(53)) gives the correct result

Using real works upto 23:57 (smalldatetime)

Casting to int will round it, rather than truncate won't it?

Cheers

Shawn

Edited by...

For stripping times off, I always prefer this:

CAST(FLOOR(CAST (DateTimeCol AS real)) AS smalldatetime

Drop the fraction from the 2 part number that is a datetime.

I've already tried that, looking at the domain level and at the local policy using my PC.

Thanks anyway.