Here is an update of what I have done:

set nocount on

create table dbo.SchoolAttendance(

    AttendanceRuID int,

    StudentID int,

    AttendanceDate datetime,

    AttendanceTyID int,

    RollupCount int,

    termID int null

)

create table dbo.SchoolAttendanceTy (

    AttendanceTyID...

I'm sorry, but I don't see how your code solves the problem.  You still have a condition in the where clause checking for StudentID = 169.  The table SchoolAttendanceTy does...

stax68,

Everything I used is in my big post.  Add where sa.StudentID = 169 to the where clause and you will reduce the result set displayed in my post by 1...

I just figured out the problem.  Your where clause contains sar.studentID = 169.

The line with AttendanceTyID = 2 is null in the column, and fails the where condition.

Change it to...

I took what you posted (I don't see CompanyID anywhere, so I left it off) and did the following:

set nocount on

create table dbo.SchoolAttendance(

    AttendanceRuID int,

    StudentID int,

    AttendanceDate datetime,

    AttendanceTyID...

Please try dropping the termId is null from the where clause using the query stax68 gave you.  What does that return?

Lynn

Colin,

Try the following, you will see that the table variable still has data after the rollback.

This can only mean that the insert to the table var inside the begin transaction...

Looks like this job is part of a maintenacne plan.  Do delete it, you will probably need to modify the plan.

Not sure, but if you deny the SA or Sys Admin from view database objects (databases, tables, etc), doesn't that mean you don't need a Sys Admin or SA?  A...

First problem, SchoolAttendanceType is on the right, not left.  Try a right outer join.  You may also want to drop the termId is null in the where clause.

hth,

Lynn

table vars are also faster as inserts/deletes/updates to table vars are not logged.

Lynn

It looks like you wanted a Windows collation during installation, but selected a SQL collation instead.  I can not find any windows collations that look like this: latin1_general_CP1_CI_AS.  The CP...

It may help to look at the views and tables being used.  As the SQL for these may be extensive, if you send me a private email (PM), I can...

Check the size of your database in production, including the transaction log.  Not sure, but SQL Backup may be making an assumption based on the physical size of the database. ...

Make this change and play with it some.  I changed from a cross join to an inner join:

create table Table1 (

    Part    char(10) constraint pk_table1 primary key,

    Descr   varchar(20),

    Sales  ...