/**********************************************************/

/******** SETUP *****************************************/

/**********************************************************/

/*****test data******************/

@TableJob table([StartDateTime] [smalldatetime] NOT NULL ,

--to generate the crosstab:

 

--the code is written to minimise use of dynamic SQL and maximise scalability.

--the only absolute limitation...

What data type are the time columns?

 

select maxdepth.FilePath, pmd.* FROM

(

SELECT fi.FilePath, MAX(pmd.Path)...

>If indexes are changed, then the app might have data integrity errors

Not having a usable index will just result in table locks rather than key range locks. It won't...

As you say, there's no great adavatage to using a function for much of this stuff as it won't be used in a SQL statement. In other cases, there...

One last shot in the dark - if you can amend the sp, try putting the select before the inserts...

That's a return value, which is placed directly into a variable in the execute statement if one is specified. It's not output to the client automatically.

Actually, ODBC will take...

Does the sp return any warnings or rowcounts when you run it in QA?

Can you guess what it should be?

I'm not sure of the cause of the problem, but try this:

insert #tmp

select * from openquery ( <linked_server> , 'exec <db>.dbo.sp ''2006-03-13'', ''2006-03-14'', 1' )

You might...

This will get the data for all nonempty permutations of two products.

You may then want to generate a dynamic crosstab from the data.

select o1.ProdID

You can put the three into a single statement. You can probably get further optimisation, too. What table does user_id come from?

 

SELECT

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Clarification: UNION will eliminate duplicates in the output recordset, not just within the individual selects.

e.g.:

select

1

I think locking is the only way to do exactly what you describe for all SQL statements. But the temp table solution might be better. If you do need to go down this...