Thanks for replying, and the monitoring queries as well.

As our "production" system is typically fairly low-use, I don't typically spend much time checking tempdb, and hadn't had any noticeable issues...

Thanks for replying; my supervisor and I were mostly concerned with the "who and why" with the sudden data growth, then later settling back to normal levels...as we didn't get...

Thanks, Lynn, the syntax you suggested works. I seem to have confused how the parameters were defined / used. I've not used this dynamic method before, only EXECUTE('CREATE VIEW ...'),...

Thanks...I've been able to use sp_executesql to execute SELECT, INSERT, etc., but NOT execute a procedure. Maybe it's not possible because of the parameter scope.

I appreciate the response, though.

~ Jeff

I added this:

SET @SQLParms = N'@SQLParm1 INT,@SQLParm2 VARCHAR(10),@SQLParm3 DATETIME';

Still get the same error.

I checked out the MSDN article on that setting; it certainly looks promising for our current environment.

Checking on raw numbers from our plan cache, grouping by distinct usecounts:

Total plans: ...

Sounds good...the "original" script was easy...the objects were all simple, so easy to make dynamic (just enclose in single quotes and EXECUTE...many more now, and with hard-coded XML elements, one...

Thanks for replying...in actuality, we don't really have an "application"...our commercial (proprietary) software stores data within hierarchical files, which our customers cannot easily report from; a while back, a process...

Thanks for replying...it gives me more options to consider.

~ Jeff

Thanks for the reply and the ideas. Unfortunately, we have limitations in many areas that I didn't include, but that your response has revealed.

-- We support SQL Server versions from...

Hope this works for you; generated using Generate Scripts feature:

CREATE TABLE [dbo].[MisIcg_InteractionChecks](

[SourceID] [varchar](3) NOT NULL,

[MisIcgID] [varchar](30) NOT NULL,

[InteractionID] [varchar](25) NOT NULL,

[RowUpdateDateTime] [datetime] NULL,

[InteractionCheckMedication] [varchar](2) NULL,

[InteractionCheckComponent] [varchar](2) NULL,

[InteractionCheckIntravenous] [varchar](2) NULL,

[InteractionCheckSeverity] [varchar](38)...

Thanks for your quick responses.

A quick overview: I need to create a daily report (for our 1000's of customers) that shows the values of various settings within our software. One...

Here's a few rows in their entirety:

exec mt_amstas47 'AMS','78350438','19','4086','0','20121114 08:33','U'

exec mt_amstas47 'AMS','78350438','18','4198','0','20121114 08:33','U'

exec mt_amstas47 'AMS','78350438','22','16','0','20121114 08:33','U'

(There are over 32K rows just calling this one sp; there are hundreds of these...

Thanks for responding..perhaps one more question. I'm curious what the query is returning, then: I've got several thousand of these "EXEC mt_" rows returned that differ only by the value(s)...

Sorry if I was too cryptic/concise...

1) Here's the query I run to check exec plan reuse:

SELECT ECP.usecounts,ECP.refcounts,ECP.cacheobjtype,ECP.objtype,EST.[text]

FROM

sys.dm_exec_cached_plans AS ECP

CROSS APPLY

sys.dm_exec_sql_text(ECP.plan_handle) AS EST

ORDER BY

ECP.usecounts DESC,EST.[text]

;

One row of output:

usecounts ...