OK, I have to ask, why do you choose not to use an identity column? That is the simplest solution to this situation, really.

Chris

If you put a unique index on that column, or make it a Primary Key column, you won't be allowed to have duplicates in there and will no longer have...

OK, thanks.

This does work:

case

When IsNumeric(Diag2) = 1 Then '3'

When Left(Diag2,1) = 'V' Then '4'

When Left(Diag2,1) = 'E' Then '5'

Else ''

End As CodeType2,

Thanks,

Chris

cvandevoorde (10/11/2010)

---

Stamey (10/11/2010)

---

Setting up error handling is is a good practice also, if you want the package to be able to continue running after it encounters an error, such...

RonKyle (10/12/2010)

---

Unless you KNOW you will never use anything but US zip codes, consider making the field length 7 to handle Canadian zip codes. Recently went through a somewhat...

First, if you have exclusive control of these fields in Access, you can just change the Access data type to a more suitable length (what has been allocated in SQL...

I will play with some of the options, but I have already read this page a few times.

Thanks,

Chris

No, no explicit transactions.

Thanks,

Chris

That works nice. I guess I needed to specify Output when I call the proc. That works better.

Thanks,

Chris

Now I'm gonna try that other example, by Eralper, as the pivot has an Achilles heel, it spits back a syntax error if any of the ClaimCode values are null,...

OK, finally got to run it. works like a champ. THANKS!

Chris

Yeah, did the Max. Now I have found data problems. In order to not return a few millions records I chose a random ID that I had verified had the...

I'm getting close. Turns out the data type for the ClaimCode is VarChar, so the Avg operator will not work. Trying to find a better alternative there.

Thanks,

Chris

That looks like a winner, if I can reconfigure it to my needs. In this case I have over 19 million distinct values in the table that would be #T2...

I have found this quite effective for kicking out users.

ALTER DATABASE [DATABASE NAME]

SET OFFLINE WITH ROLLBACK IMMEDIATE

Go

ALTER DATABASE [DATABASE NAME]

SET ONLINE WITH ROLLBACK IMMEDIATE

Chris