@pcplayer:

I'm by no means a developer however two thing strike me:

- OLE Automation is only for code that exposes a COM interface. Unless you've put a COM wrapper...

@gallan:

You will need to do your paging SQL-side. If you think about how Reporting Services is designed there's really no other way for it to work: your half-million row...

If you want to avoid DTS for sending your mail you can do it all via OLE automation from TSQL: http://support.microsoft.com/kb/312839

On your specific error - we get exactly that from...

Ah yep, sorry SQL Express 😛 I was reading MSDE...

If you can't change the architecture then minimise the amount of data transfered across the linked server boundary. If...

Ouch - sounds like a horrible setup :w00t:

Are these remote SQL Express clients accessible via static IPs? Do you pull the data from the central SQL server or push...

If you really need to use a LIKE '%xxx%' clause and can't change it to LIKE 'xxx%' so it can use an index perhaps something like this will help:

Assuming that...

Good point Stephen - however your solution makes the WHERE clause non-SARGable as you're putting the datediff() function on the audit_changed column, and that's likely to cause the optimizer to...

Cheers Jeff! There are quite a few articles around that simply say "don't SELECT... INTO... a temp table" without qualifying it with "but only really a problem in 6.5",...

Thanks for the insight Jeff. I've done some re-checking of my old assumptions and it turns out the TempDB locking contention from SELECT... INTO... was only a major issue in...

Interesting Jeff - just compared your test query (1m rows) with slightly modified version that CREATEs #MyHead first and INSERTs into it (both running in TempDB). This is on a...

Something like

select

MyColumn1,

MyColumn2,

MyColumn3,

audit_changed

from dbo.audit_t

where audit_changed >= dateadd(dd,-7,getdate())

should do the job. As for emailing it it depends on how you intended to run this...

To be honest I've never actually measured it myself, just heard it quoted in a number of sources as a bad practice and I've always studiously avoided it. Do...

Whoa! Don't do that - SELECT... INTO... on a temp table needs a schema lock to create the temp...

AndyD - that's not the same thing. Registering the instance in AD does not create the SPN, it just publishes the "presence" of the SQL server so that people can...

You could also so something simple like script out all your procs/views/etc. on a regular basis. We do this daily into a history table and have a running version history...