Thanks for the insite Malcom. In this case, for security purposes, I must use the view, as the developers and the application cannot have access to other fields in the...

Kevin77, that kind of makes sense, but wouldn't the optimizer, since it knows what the field is I am going to be comparing to, even though it doesn't yet know...

Amit, using that statement to clear the proc cache made the TSQL much worse. The same thing, TSQL, with variables passed to it, took 26 seconds to run! The stored procedure execution...

OK, done. Removed ordering and the Top 100 percent from the view. Ran the following, but still 12 seconds:

Declare @NetworkID VARCHAR(255)

Set @NetworkID = 'psridhar@landam.com'

SELECT FirstName as FirstName,LastName as LastName,EmpNumber...

The epEmail field is VarChar(255), in the table behind the view. I have tried it both ways, nvarchar and varchar, and that doesn't make much difference.

12 seconds:

Declare @NetworkID VARCHAR(255)

Set @NetworkID =...

Prasad

Thanks. That made a difference. I now get error info back. I guess this is not like VB, where a string defaults to...

I had a discussion with a developer I know and he helped me work things out. Now, out of 30 possible inserts, 22 successfuly go in and the problem data does...

I'm right there with you, and I let me vendor contacts know. Many have been a recipient of a poster I created that says, "Garbage In Garbage Out", as a...

I agree about the code, and nont covering up problems. I also want the next guy to come along and support this to say, "at least he tried".

I have little...

It works better. I still get the error message from the PK violation though, and it doesn't go on to the next recruit.

Server: Msg 2627, Level 14, State 1, Line...

No luck there. I get the same behavior from that also.

Thanks,

Chris

This what you had in mind?

 Select @ErrorNum = @@Error

 If @ErrorNum <> 0

  Begin

  ROLLBACK TRANSACTION

  Print 'Error: ' + Convert(VarChar(15), @ErrorNum)

  --Return @ErrorNum

  End

It's only getting worse. Now I get the same error about...

Prasad,

Changing that, either using

If @@Error > 1

Or

Set @ErrorNum = @@Error

If @ErrorNum > 1

actually did not help, but did change the behavior slightly.

Out of what should be 13 records, 13 recruits,...

OK, after thinking about it, I was able to eliminate the cursor. The transaction still does not work as expected though.

Here is the new source.

The results are still the same...

The removal of the name did not help.

Here is the entire source of the procedure:

Declare @AppBaseID Int

Declare @AppID Int

Declare @AppEEOID Int

Declare @RBID Int

Declare @AppToReqID Int

Declare @SS VarChar(15) --Social Security Number.

Declare...