martin.edward (11/3/2009)

---

T1.BaseNum = '15' -- This is the production number of the computer set. As I indicated earlier every time a computer set is made or produced a production...

Hi Martin,

apologies, Im having a hard time exactly where you issue lies.

Please remember this data means nothing to me.

If i execute this...

DECLARE @itemcode VARCHAR (20)

DECLARE @prodnum VARCHAR ...

This article will explain you options

http://www.sommarskog.se/dyn-search-2005.html

Try this link

http://blog.boxedbits.com/archives/34

You need to do a catch all query

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

You will have to do dynamic sql.

try using '&'

Try this link

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Bite the bullet , its a small one, and go for the table.

The reason i like a table is so that not only can you ask the question , which...

I generally find that a calendar table is usually the solution for this (and many other) date related issues

Bcp it out then, for example...

SELECT top 1 planxml = convert(text,query_plan)

into xmlout

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

Cross Apply sys.dm_exec_text_query_plan(plan_handle,0,-1)

order by datalength(TEXT) desc

Then

bcp yourdatabase..xmlout out c:\xmlout.sqlplan -c -Syourserver...

Are any errors reported ?

try using executing the script after using SETUSER to move your session to someone with non dba permissions

Hi ,

im not seeing your attachments.

But is all your after a chain of CTE's ?

use TESTQUERY

DECLARE @itemcode VARCHAR (20)

DECLARE @prodnum VARCHAR (20)

SET @itemcode = 'P10004';

WITH BOM (Father,Code,Quantity)...

First try

SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,

TEXT AS SQL ,

planxml = convert(xml,query_plan)

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

Cross Apply sys.dm_exec_text_query_plan(plan_handle,0,-1)

Which may fail with a 'maximum level error.'

If you push...

Have a google for 'dm_exec_cached_plans'.