i don't think the collation is going to affect you at all, you should use the default server collation, you just have to use nvarchar columns everywhere instead of varchar,...

the function below returns all possible colaltions on your server ...much more than i care to digest .

the second query is just the collations currently in use.

hope that helps:

select *...

Frances L (8/23/2011)

---

easy way to write this sp

I want to update one table field value according to other table value.

table a

ID Certfication

2 ACNS-BC

3 CCRN

if table a have...

Jnrstevej (8/23/2011)

---

Thanks for your reply Lowell and kvishu83

@Lowell its just a portion of the SP I've created. You are completely right with your advise it has solved the problem:-) i...

in some cases, that's absolutely true; after answering the same posting question over and over again, some folks get jaded;

I myself got snarky a while back and when someone posted...

just a day or so ago someone asked for the same thing;

see this topic for the discussion and a working solution:

http://www.sqlservercentral.com/Forums/FindPost1162705.aspx

shouldn't your function return a datetime instead of a char(8)?

anyway, you just wrap it with the function you wrote:

here's two ways, depending on if you change the function to return...

the code you posted is not creating the table SystemDateTable

i assume what you psoted is just a portion of what you are doing, right?

in what you did paste, you are...

you should be able to find what the issue is pretty easily;

if you change the object id below to the one you are looking for, what do you get?

on my...

your variable is too small

DECLARE @sql nvarchar(1000)

but the code you pasted is at least 1065 chars, depending on t table name.

change it to nvarchar(max) to be safe and try again.

some of your dates are not dates...probably blank strings for death date(a reasonable assumption would be not all patients die or get transferred)

something like this:

...

CASE

WHEN ISDATE(SUBSTRING(BulkColumn, 41, 8)) =1

THEN...

since you have everything in a staging table, shouldn't you just convert the columns explicitly?

-- Substring input patient

set @sql = N'SELECT

CONVERT(int,SUBSTRING(BulkColumn, 1, 4)) AS patid

,SUBSTRING(BulkColumn, 5, 1) AS...

I'm guessing the new columns( with defaults or not?) caused a lot of page splits of the data.

I'm thinking rebuilding the indexes on the table would improve the perforamnce...

Shurkadze (8/22/2011)

---

What if you have a row modified in OLTP with a column value changed that is not mapped to DW. The data then should not be moved to Data...

Gail I was looking more for a rule of thumb, I'm sure that just like you identified, there are exceptions where the key lookup is better...testing would...