Marvin, that's just amazing to me... a data file that has more columns than rows... I don't believe I've ever seen such a thing except maybe for a single row...

Thanks, Johan,

I took a look at the forced parameterization thing... looks real spooky when they say things like it could slow down the whole world and use with great caution,...

Hoo boy... I understand... 2,134 columns of CSV data is enough to make anyone cranky.

How "clean" is the input file? Any problems with missing delimiters, etc?

Ok folks... the statement level OPTION (RECOMPILE) that Matt spoke of really did the trick both on this current problem and the previous problems I had. Estimated execution plan...

Jeff Moden (2/23/2008)

---

matt stockham (2/23/2008)

---

Gail is right on the mark. I wouldn't necessarily recommend "optimize for" though - you can use statement level recompile (new in 2005) to get...

GilaMonster (2/23/2008)

---

It's caused by the same thing. When the optimiser gets a query, it knows the value of any parameters (stored proc, auto parameterisation, or sp_executeSQL) and the value of...

matt stockham (2/23/2008)

---

Gail is right on the mark. I wouldn't necessarily recommend "optimize for" though - you can use statement level recompile (new in 2005) to get the optional...

Not 100% sure, but I think it's just a problem with permissions. What login is the agent using and does it have the privs to see the same directories...

If you use a BCP format file, you can. 😉 See BOL...

Thanks for the feedback guys...

Ryan and Mike C... thanks for the performance comparisons. That's good stuff. 😉

Heh... sorry about that, Matt. Thanks for the help...

Understood... but this doesn't even have anything to do with indexing... changing a constant to a variable causes a massively detrimental change in the execution plan. This is a...

Well, good! At least it's not just me! 😀 Thanks for the help, Matt and Michael.

I also ran into this the other night on something totally different...

Oh yeah... and, of course, it works just fine. But, any idea on how to make it work without being dynamic though? Seems the optimizer is making a...

Pretty good explanation of SET vs SELECT... horrible method of creating a split function. Use a Tally table, instead.