Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btrimpop
Posting Yak Master
214 Posts |
 Posted - 2007-02-28 : 11:54:45
|
| Hi all,I've been running the following select in a stored procedure for literally years. Insert into ar.#lcodes ( linkid_c, source_c)select distinct linkid_c, source_cfrom ar.limitcodesAnd suddenly I'm getting the following error...Error: 208, Severity: 16, State: 0I've checked all of the normal resources, BOL etc. CHECKDB does not return any db problems. Nothing has changed on the server (other than Microsoft Updates). User is a System Administrator. It's only on this one particular db, I have other db's with the same app running the same process on the same server and no errors. Any thoughts? Thanks"In theory there is no difference between theory and practice. But in practice there is!" |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-28 : 12:02:47
|
| From http://ureader.com/message/1470842.aspx"When a stored procedure is called and there is no plan in cache, SQL Server generates a new plan. At compile time, the temp table (assuming it is created in the proc) does not exist and SQL Server kicks out the 208 error (sometimes multiple ones). That is, an object is accessed, but does not exist, even though it's really fine.Once the plan is in cache, subsequent executions don't cause the error unless you are really dealing with an object that is missing.If this occurrs from a stored procedure, you can examine the SP:StmtStarting event just prior to the 208 Exception. That's the statement that triggered the problem. If you also see a 208 Exception before the SP:CacheInsert and SP:Starting events, that's because when the procedure was compiled, the object was missing (that's where you'd see the errors for temp tables as well on the initial execution).From outside a stored procedure, have a look at the SQL:StmtStarting or RPC:Starting event for the trigger text.There's no easy way to determine the bad from the acceptable 208 errors other than examining the trace in more detail and seeing what statements triggered the problem." |
 |
|
|
btrimpop
Posting Yak Master
214 Posts |
Posted - 2007-02-28 : 12:54:56
|
| Thanks and understood, just not sure that explains why something that literally worked fine yesterday and for the 3 years previous, generates the error today!"In theory there is no difference between theory and practice. But in practice there is!" |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-28 : 14:03:58
|
| Agreed - what I'm thinking is that something could be affecting your procedure caching, so that's where you need to look. |
|
|
|
|
|
|
|
|
Out of the blue Exception 208 error