print len(@a)             {=7999}

print len(@b)             {=8000}

print len(@a+@b)       {=7999}

execute( @a + @b-2 )   {fires just fine!!}

Rockmoose, so I think I am starting to understand.

Though len(@a+@b) {=7999},which has hit the varchar(8000)...

*********

DECLARE @sqlprt1 VARCHAR(8000),@sqlprt2 VARCHAR(8000)

SET @sqlprt1 = 'SELECT * FROM /*....and long and long...*/'

SET @sqlprt2 = ' INFORMATION_SCHEMA.COLUMNS /*...and long and long...*/'

EXECUTE(@sqlprt1+@sqlprt2)

*********

Hi we are trying something almost exact to this suggestion...

Thank you for the insight.

Yes we did get it working again by creating a dynamic query string and capturing in a local var @t.

Firing the script with execute(@t) was just...

Ohh, I hate when its that easy!!

Thanks, tried it and works great.

Is there any drawback to using straight execute() to sp_executesql?

My Thanks

Try taking advantage of SQL jobs.

We did the same you are trying to do like this.

Set up a Job with say 2 steps.

Step 1 searches for that file, if it...

Though i have never encountered that message, I have had problems like that in the past.

Have you examined the source and the destination to make sure they are active and...

Thanks for the reply.

Good to know.

Regards

We are also using SQL 2K AS, and this SP in question used no temp tables.

Also, good idea on testing with constants.

Thank you

Thanks for all the posts,

_____________________________________________

That sounds like "parameter sniffing" with atypical values.  Does your stored procedure have defaults (like null) for its parameters?

______________________________________________

Yes we are assigning default values to...

Thank you for the posts,

We did our own testing and optimizing and found the same thing, using a count was 6Xs faster than the If Exists().  I had always thought...

Thanks,

Worked great!

You might want to experiment using

with recompile

in the stored procedures.

We experienced the same problem and we needed this clause in a few of the Stored Procs hitting large dynamic...

I think we got it.

We threw With recompile in the proc and bamm, back to normal.

Question:

Why was QA acting differently?

Why was is recompile needed?

Thanks

You might also look into sp_executesql.

Basically it allows you to build a dynamic sql query and run it in a Stored Proc.

BULK Insert does not allow you to manipulate the as it goes in,

though there is a way using a Format File.

I ran into this issue; Need the performance of the...