Thinking about it some more, we came up with a solution that might be a bit faster... instead of doing an update for ever record (processing row by row may...

Alan,

Yes... I was just getting to resort to an older, cursor method for going through all of the databases when I decided to give it one more try.

1)...

I tried both SA and "windows authentication", both of which have full permissions in in the SQL Server Express instance and access to all of the databases.

Nope... putting the [] in did not help retrieve any more databases with without the [].

Thanks for the suggestion about using EXEC sp_dropserver 'yourServerName,'droplogins'.

We were just trying to use EXEC sp_dropserver 'yourServerName without the droplogins directive and received errors about a linked server (another...

Please ignore the CODE GOES HERE comments... I use templates when I create some of these scripts. 😉

Hmmm.... no matter what I did, I could not get the WHERE clause to use the DATEDIFF function without getting a conversion error message.

I re-did the process... here it...

Thanks for the suggestions all! 🙂

Doing a replace would not work, as any occurrences such as 0901 would become 9 1 (char data). But doing WHERE CAST(ActualTime AS INT) <>...

IF NOT EXISTS

(

SELECT *

FROM SYS.OBJECTS

WHERE TYPE = 'FN'

AND

NAME = 'GETMONTHEND'

)

EXEC sp_executesql @Statement = N'CREATE FUNCTION GETMONTHEND(@DATE DATETIME)

RETURNS DATETIME

...

My bad... they are UNION ALL in the Views.

Here is the rest of the post, part of the execution plan:

[/RelOp]

[RelOp AvgRowSize="75" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="3512.44" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="85" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="5.80676"]

[OutputList]

[ColumnReference...

Ok... here is the query:

SELECT

LO.Territory,

IV.AccountNumber,

CH.InvoiceItem, CH.InvoiceNumber,

CH.NetAmount,

CH.IncentiveAmount,

AD.[Name], AD.Company, AD.AddrLine1, AD.AddrLine2,

AD.City + N', ' + AD.State + N' ' + AD.PostalCode AS ConsigneeCityLine

FROM VIEW_CustTable CH --WITH(NOLOCK)

INNER JOIN...

Thanks for the info.... started looking into DMV(s) and I noted the comment in the BOL regarding the SHOWCONTIG command. Still learning (thrown into DBA position here last year).

I...

Ellis,

Thanks for the response! Unfortunately, on this server the databases tend to be pretty static during the week. There will be only one of two things happening:

1) Weekly...

To answer GSquared question, my experience is that derived tables (like the ones I used) or CTEs are not as fast or efficient being more "creative" with the query (as...