check the sqlserver errorlog file.

Maybe it contains the messages you need to take care of.

(you can also post its content)

Just keep in mind you should get rid of the day , month and year columns to be safe for the future.

That may have heavy application impact, depending on how...

well maybe, just maybe, ... since you're on SQL2005 ....

you may want to add a computed_column of the datetime datatype and put an index on it.

check BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8d17ac9c-f3af-4bbb-9cc1-5cf647e994c4.htm

create table tmpTb...

Quit logic..

If you convert your day / month / year column to int you will get

01012008

21012008

01022008

15022008

and it is integer, so there is no knowlage of date nor time.

So you...

Unless you have strict rules for temptb's I wouldn't rely on "they have the same definition" :ermm:

You can off course always test the existance using

select OBJECT_ID('tempdb..#mytmpTb')

Maybe dbcc opentran can help you a bit.

ado.connection.begintran causes sqlserver to swap to "implicit transactions". That may not be the effect you wanted because you have to add...

just a thought ...

You figured out sqlagent's roles .. that will be OK.

Maybe the logon-triggers can help you out ( need to be on sp2 ) so you can kick...

If your scope is only the local proc, try to use table variables

declare @tmptb table (colx ... )

You've user a tablevariable. That is only available in your own session.

A global temptb (##tb) will fix this.

Keep in mind this affects multiple threads using the same temp object !

you could off course immediatly connect to the correct database

- add -d yourdbname to the commandline

- add set nocount on at the beginning of your script to avoid the...

So you are shrinking the value range !?!

So it can nolonger store existing content !

Max...

as long as you don't send me the bills, I'm happy with that. :hehe:

Indeed SQL2005 has some nice new stuff...

Grant Fritchey (1/14/2008)

---

I knew of the OUTPUT clause, but I'd only really thought of it for stuff like auditing. This is really useful. So, if you had a multi-row insert,...

you could use :

In your proc just set the dateranges (could use an available index (sargeble))

select ...

from yourobject

where mydatetimecol between @dateBegin

and @dateEnd

or

use the less performant ...

A user(id) can connect multiple times.

So indeed a user can block himself (if he has more than one connection)

There have been issues with parallelism where one SPID ( a single...