Easy way to remove international alphabet from all rows?

Is there an easy way to loop through all rows and remove all
international alphabet characters from a column in a table, for example
remove German umlauts "" and convert them to a simple "u".
Thanks,
lqSee the following thread:
http://groups-beta.google.com/group...b45abaa5cf410b8

Razvan|||OK< thanks on that. How about keeping out these characters in the first
place?|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Is there an easy way to loop through all rows and remove all
> international alphabet characters from a column in a table, for example
> remove German umlauts "" and convert them to a simple "u".

Here is a much smarter version than the one in the linkn Razvan foun

create table T (
s nvarchar(20)
)
insert into T values ('cre')
insert into T values ('pt de fois gras')
insert into T values ('HLNE MELANON')

update T
set s = convert(varchar(20), s) COLLATE Cyrillic_General_CS_AS
go
select * from T
go
drop table T

This assumes that your data only has Latin characters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> OK< thanks on that. How about keeping out these characters in the first
> place?

I don't know, nuke the parts of the world where we insist on using other
languages than English?

Seriously, I think it would be a very bad idea. If you want to send
mail to these persons, you do want to spell their names properly, don't
you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's only one of the problems! I want to store the data with the
international characters but then I need to strip out the international
characters for certain output, such as in e-mail addresses...
lq|||Technically, international characters are permitted in email addresses
and domain names so stripping them out is still a bad idea.

You'll have to build your own table to translate characters that you
deem to be unacceptable. Google for some conversion tables but there
isn't really a single answer because it depends on which characters you
need to support (don't forget the two-byte character sets used in the
Far East and elsewhere for example).

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> That's only one of the problems! I want to store the data with the
> international characters but then I need to strip out the international
> characters for certain output, such as in e-mail addresses...

I would recomment that you store the e-mail address. While the e-mail
address often can be formed as firstname.lastname@.domain.xxx, this is
not a requirement.

And the local transformation may not be the one you expect. in German
after often replaced with ae, oe and ue. (Whether this is actually common
in mail addresses, I don't know.)

If you are thinking of the comment part of the e-mail address:

John Smith <johnsmith@.example.com
(John Smith is the comment here) You should either encode the comment
according to MIME as per RFC2047, or send as-is. But don't misspell
people's names.

(By the way, permit me to point out that the term "international characters"
is a misnomer. The internationalest characters I can think of is the
English alphabet A-Z. Non-ASCII characters is a better term, and
technically precise.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks for that. You're right, I do mean non-ASCII characters...
This gets tricky because many city names and people names contain
non-ASCII characters and I want to display then properly, however, some
systems do not support non-ASCII characters and they need to be
cleansed for output to those systems, so as you point out, there is not
an always correct translation into ASCII characters.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Thanks for that. You're right, I do mean non-ASCII characters...
> This gets tricky because many city names and people names contain
> non-ASCII characters and I want to display then properly, however, some
> systems do not support non-ASCII characters and they need to be
> cleansed for output to those systems, so as you point out, there is not
> an always correct translation into ASCII characters.

Systems in 2005 that is not capable to handle non-ASCII characters? So,
not all programs are capable to handle Unicode, but one would at least
imagine that they should be able handle 8-bit charset. If I were you,
I would consider to attempt to kill these systems by starvation and
not feed them any data. :-)

On a more serious point, there are one letter that the snippet I
posted does not handle and that is . This gets converted into a ?.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Windows file names is an example on one such system...|||Which Windows are you referring to? FAT32? NTFS does support non-ASCII
characters in files names, at least it does under Win 2003 so I don't
know why it wouldn't under other versions.

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Windows file names is an example on one such system...

The 8.3 names, yes, but you rarely have to see them. Even less rarely
have to form them. As long as you are on NTFS you can use the whole range
of Unicode, and use letters from any script. The FAT files systems may
not support this, but I'm pretty sure that they support the full range
of the current ANSI code page.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Dynamically changing task name displayed within ForEach Loop Container

Does anyone know how to change the task name displayed within a ForEach Loop Container (or of the ForEach Loop Container task itself) based on a variable. I am pretty familiar with setting variable values during task execution and using expressions to alter task properties based on variables. I have tried using an expression to alter the value of the Name property of the ForEach Loop Container but the name of the ForEach Loop Container does not change during execution. Since the color of the various tasks change during execution, I would think that the task names could be changed as well.

What? Why would you want to do this? The fact that the colors change is just a pretty *debug feature* and means nothing in the execution of the package.|||I know that. I have a loop that executes through a few hundred iterations and runs for 30 minutes or so. I'd like to review how far in the process it is while it runs.|||Can you write a counter to a log file or something?|||

You can create checkpoints or if that does not fit your needs; you could write to a file or table on each iteration an the look at that. if you are interacting with a DB you may use a DB profiler to monitor the activity. Or try to loop only a few times until you get confident with the package result.

Rafael Salas

|||

No, you can't change the task name during execution.

You can log to the log output window though and only log the increment count, that would be the equivalent.

|||Thanks to everyone for the alternative suggestions. Thanks to Kirk for the definitive answer.

Dynamically Change SSIS For Each Loop container

Hello,
I would like to modify "Files" attribute of the Foreach Loop of type File 
Enumerator. This attribute is used to set the mask (for example *.txt) to 
specify which files to include in the selection. I need to be able to change 
this mask dynamically depending on package global variable. Is this possible?
Thank you!
Michael

Use the Expressions property, and create an expression for FileSpec property that references the global variable.|||

More details on How To get to the Extressions Property -

Open the ForEach Loop Editor by double clicking ForEach Loop Container.
Select Collection on left.
Click on the + sign on Expressions
Select FileSpec for Property and On Expression select the Global Variable Name. (which holds the file property such as *.txt)

Thanks,
Loonysan

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic table name in destination

How to create a new table dynamically in OLE DB destination.

This is what i am doing

I am reading multiple flat files in loop and saving file name to a variable. Then i have a source script component which read and transforms data .Now how can I push the data to SQL table. I want to create a new table with name saved in a variable. I tried using OLE DB destination and assigning table name from variable. Does'nt work.

Thanks in advance for any insight on how to make this work.

-Amar

I think you'll have to break this up into multiple work flows. The first executes an Execute SQL task in the control flow to create the table. Then attached to that Execute SQL Task is the data flow that operates on the file and then inserts into the destination, which is derived from the incoming table name.

That may work for you. The problem is, the OLEDB Destination cannot create a table at run time.|||

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

|||

Amar Khaira wrote:

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

In the OLE DB Destination, set it to "Table name or view name variable - fast load". Then just pick the variable that you loaded in the foreach loop. The metadata must be the same, though, for each table.|||

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

|||

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

|||

Amar Khaira wrote:

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

You need to create one table first. Then populate the variable that you are using in the foreach loop with a default value of that table you just created. Then in the OLE DB destination, in selecting that variable, it will read it and find the table. You can then perform your mappings. When you execute the package, the default value of the variable will be over-written.|||

Amar Khaira wrote:

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

See my comment above.|||

Phil,

That what I did...can you please elaborate more...what i am missing

|||

Amar Khaira wrote:

Phil,

That what I did...can you please elaborate more...what i am missing

You need to create a table first. Then, in the variable you are using, TYPE in the name of that table in the DEFAULT VALUE parameter of that variable.

Then in the OLE DB Destination, do as I said above. Select that variable, and it should work for you.|||And this next comment is important, so I'm going to make it its own post:

The metadata (number of columns, data types, etc...) must be the same across all of your tables that you are dynamically feeding into the OLE DB Destination.|||

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

|||

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

|||

Amar Khaira wrote:

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

Try setting the "ValidateExternalMetaData" property on the OLE DB Destination to false. If that doesn't work, then set the "DelayValidation" property of the data flow to true.|||

Amar Khaira wrote:

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

Excellent. Please mark one of these posts as the answer to your question.

Thanks,
Phil

dynamic sql to loop over fiscal years

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECT count(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECT count(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output...

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
... ...Create a table called ExperimentYears, populate it with ExperimentYear char(4), YearStart datetime, YearEnd datetime. Then do just one SELECT similar to yours:

SELECT count(*) as [Data Points], ExperimentYear
from tbl_experiment_data ted
inner join ExperimentYears ey
on ted.start_date between ey.YearStart and ey.YearEnd
and ted.completion_date between ey.YearStart and ey.YearEnd
where ted.status = 'CaseClosed'
group by ey.ExperimentYear|||We use a separate calendar table. something like this:

create table FiscalCalendar
(FiscalYear int,
StartDate datetime,
EndDate datetime)

This should reduce your query to something like

select count(*), fc.FiscalYear
from tbl_experiment_data a, FiscalCalendar fc
where a.startdate between fc.startdate and fc.enddate
and a.enddate between fc.startdate and fc.enddate
group by fc.fiscalyear

Been a while since I messed with this, so experiment with this for a bit. As a curiosity, what happens to experiments that start in one fiscal year and end in the next?|||as i'm re-reading my post now i can see that maybe it wouldn't even work as is because i have the count(*) without a 'group by'

still, i hope these sorts of mistakes can be overlooked as i ask for help.

it also occurred to me just now that maybe i could use a user-defined function that returns the value of the year as redefined by the "year" range above.

that way i could rewrite the query like this...

==============
SELECT count(*) as 'Data Points', getFiscalYear(start_date) as 'Experiment Date'
FROM tbl_experiment_data

WHERE DATEPART(YEAR, start_date) = getFiscalYear(start_date)
and DATEPART(YEAR, completion_date) = getFiscalYear(completion_date) and status = 'CaseClosed'

GROUP BY getFiscalYear(start_date)

ORDER BY getFiscalYear(start_date)

==============

any comments? criticisms? other ideas?

thanks again for reading ... and your input

oh, great! i just noticed the responses now too. thank you. i'll try these ideas out.|||SELECT count(*) as 'Data Points',
year(dateadd(d, 92, StartDate))-1 as 'Experiment Year'
FROM tbl_experiment_data
WHERE status = 'CaseClosed'

The year(dateadd(d, 92, StartDate))-1 function returns the experiment year by addint 92 days (Oct +Nov +Dec) and then subtracting 1 from the year. Note that if you just subtracted days you would have to account for leap years.

You will need to decide what to do if an experiment starts in one year and ends in the next. Your original code would skip those instances entirely.

Dynamic SQL into a cursor

basically im creating a user defined function that i want to take the parameters that are passed to it to create the sql for a cursor to loop through. As far as i can tell a cursor cannot take dynamic sql, anyone got any bright ideas or an easy way round this?

My code so far is as follows (the variable @.sql is the sql statement i want passed in to the cursor):

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

set @.sql = 'select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

declare archivecur cursor
for
@.sql

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
right i thought id found the answer to append the rows from the dynamic sql into a table variable and loop through that but unfortunately im now getting must declare the variable @.curtable

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

declare @.curtable table (Field1 varchar(50), modifdate varchar(8))

set @.sql = 'insert into @.curtable (field1, modifdate) convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + convert(varchar(20),@.inprefno) + ' order by modif_dttm'

execute(@.sql)

declare archivecur cursor
for
select field1, modifdate
from @.curtable
order by modifdate

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
|||

Lamffy,

In order to execute dynamic sql, you need EXEC(...) or sp_executesql and no one of them could be used inside a user defined function.

To play with a cursor and dynamic sql , you can define and open the cursor inside the dynamic sql and return it in an output variable.

Example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.customerid nchar(5)

declare @.c cursor

declare @.orderid int

set @.sql = N'

set @.c = cursor local fast_forward

for

select orderid

from dbo.orders

where customerid = @.customerid;

open @.c'

exec sp_executesql @.sql, N'@.customerid nchar(5), @.c cursor output', 'ALFKI', @.c output

if cursor_status('variable', '@.c') = 1

begin

while 1 = 1

begin

fetch next from @.c into @.orderid

if @.@.error != 0 or @.@.fetch_status != 0 break

print @.orderid

end

close @.c

deallocate @.c

end

what are you trying to accomplish, creating a user defined function that involves dynamic sql and cursors?

AMB

|||

Try

set @.sql = 'declare archivecur cursor for
select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

exec (@.sql)

open archivecur

|||

Maybe it's me, but this seems like extreme overkill.

What function or looping is required to carry out this task?

I'm thinking a SELECT CASE would accomplish what you are looking for.

Adamus

|||Cheers Mark that worked. You're a lifesaver!! :-)
|||I agree Adamus it does look a bit like overkill but when you see the state of NHS data and the data warehouses it's all stored in you'd understand how much of a nightmare doing anything is!