Easier way to convert Non-Unicode to Unicode

I have built a large package and due to database changes (varchar to nvarchar) I need to do a data conversion of all the flat file columns I am bringing in, to a unicode data type. The way I know how to do this is via the data conversion component/task. My question is, I am looking for an easy way to "Do All Columns" and "Map all Columns" without doing every column by hand in both spots.

I need to change all the columns, can I do this in mass? More importantly once I convert all these and connect it to my data source it fails to map converted fields by name. Is there a way when using the data conversion task to still get it to map by name when connecting it to the OLE destination?

I know I can use the wizard to create the base package, but I have already built all the other components, renamed and set the data type and size on all the columns (over 300) and so I don't want to have to re-do all that work. What is the best solution?

In general I would be happy if I could get the post data conversion to map automatically to the source. But because its DataConversion.CustomerID it will not map to CustomerID field on destination. Any suggestions on the best way to do this would save me hours of work...

Thanks.

If SSIS has to use a namespace to identify a column...this happens when you have Source1.CustID and DataConversion1.CustID it can't do the mapping. If you changed the names in the source it could, but that might be more trouble than it's worth.

Dynamically use variables in SQL in EXECUTE

Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:

> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>

DYNAMIC TSQL

Here is the sample query:

DECLARE @.TABLENAME NVARCHAR(50);

DECLARE @.COL NVARCHAR(50);

DECLARE @.VALUE NVARCHAR(50);

/*** THESE VARIABLES WERE ARE BEING DYNAMICALLY FEEDED THROUGH CURSOR***/

DECLARE @.SQL1 NVARCHAR(1000);

SET SQL1 = 'SELECT * FROM' + @.TABLENAME + ' WHERE' + @. COL + '=" + @.VALUE

EXECUTE sp_executesql @.SQL1

Now when I execute this SP it gives me error invalid colunm name. I figure out that its because of the variable @.VALUE. Eventually I found out that I need to single quote the value of this dynamically feeded variable @. VALUE

is there any way I can do this; give quotes to @.value like @. COL + '=" + ' @.VALUE'

Use the following query,

Code Snippet

DECLARE@.TABLENAME NVARCHAR(50);

DECLARE@.COL NVARCHAR(50);

DECLARE@.VALUE NVARCHAR(50);

DECLARE@.SQL1 NVARCHAR(1000);

DECLARE@.PARAM NVARCHAR(1000);

SET @.SQL1= N'SELECT * FROM ' + @.TABLENAME + N' WHERE ' + @.COL + N'=@.VALUE';

SET @.PARAM = N'@.Value as Nvarchar(50)';

EXECUTEsp_executesql @.SQL1, @.PARAM, @.VALUE

dynamic sql string too long

hello,
i have a stored procedure, which is building a dynamic sql statement with
DECLARE @.myStatement NVARCHAR( 4000 )
SET @.myStatement = 'blablabla'
and than
i do an
EXEC ( @.myStatement )
The problem ist, that the statement now exceeds the maximum length of 4000
nvarchars.
How can I create statements which are longer than 4000?
Switching to varchar is not a good solution.
Currently my statment is about 80.000 characters long!
thank you
michael
Michael Zdarsky"Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> hello,
> i have a stored procedure, which is building a dynamic sql
statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length
of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
> thank you
> michael
Michael Zdarsky,
An eighty thousand character SQL string?
May I ask why it is that long?
If you have SQL Server 2005, you may use NVARCHAR(MAX).
Sincerely,
Chris O.|||Use multiple variables.
DECLARE @.st2 NVARCHAR(4000),
@.st2 NVARCHAR(4000);
EXEC(@.st1+@.st2);
Or, find ways around making such a long string. Why is your query so long?
A massive IN() list? See http://www.aspfaq.com/2248 for some elegant ways
around that.
A
"Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> hello,
> i have a stored procedure, which is building a dynamic sql statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
> thank you
> michael
>
> --
> Michael Zdarsky
>|||Michael Zdarsky (zdarsky_at_zac_dash_it_dot_com) writes:
> i have a stored procedure, which is building a dynamic sql statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
80000? Ouch!
Well, you can do
EXEC(@.sql1 + @.sql2 + @.sql3 + ...)
In SQL 2005 you can you use nvarchar(MAX), which makes things a lot simpler.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Aaron,
thank you for your answer.
This is a high complex query which is generated depending on user input.
the user input is sort of filter criterion for a 2GB database to search
through
special entries.
You are right with massive in list, but the problem is,
that each in list can be longer than 4000 NVARCHAR, so I have to split the
inlists in different variables.
But the in list problem is only one part of the problem.
The filter is realy complex. I haven't seen more complex queries before.
And the greatest problem is the mass data input in that query by user, throu
gh
selection lists on the UI.
I can not work with ids, because the selected data from the UI can be saved
and reused with a different database.
The id's of the selected data wouldn't match, so I have to use the pure UI
data.
Thanks
Michael
Michael Zdarsky
"Chris2" wrote:

> "Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
> news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> statement with
> of 4000
> Michael Zdarsky,
> An eighty thousand character SQL string?
> May I ask why it is that long?
> --
> If you have SQL Server 2005, you may use NVARCHAR(MAX).
>
> Sincerely,
> Chris O.
>
>|||Hello Aaron,
thank you for your answer.
This is a high complex query which is generated depending on user input.
the user input is sort of filter criterion for a 2GB database to search
through
special entries.
You are right with massive in list, but the problem is,
that each in list can be longer than 4000 NVARCHAR, so I have to split the
inlists in different variables.
But the in list problem is only one part of the problem.
The filter is realy complex. I haven't seen more complex queries before.
And the greatest problem is the mass data input in that query by user, throu
gh
selection lists on the UI.
I can not work with ids, because the selected data from the UI can be saved
and reused with a different database.
The id's of the selected data wouldn't match, so I have to use the pure UI
data.
Thanks
Michael
--
Michael Zdarsky
"Aaron Bertrand [SQL Server MVP]" wrote:

> Use multiple variables.
> DECLARE @.st2 NVARCHAR(4000),
> @.st2 NVARCHAR(4000);
> EXEC(@.st1+@.st2);
> Or, find ways around making such a long string. Why is your query so long
?
> A massive IN() list? See http://www.aspfaq.com/2248 for some elegant ways
> around that.
> A
>
> "Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
> news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
>
>|||> You are right with massive in list, but the problem is,
> that each in list can be longer than 4000 NVARCHAR, so I have to split the
> inlists in different variables.
So if you already have them in separate variables, can't you still dump the
list into table form, a la http://www.aspfaq.com/2248 ?
A|||Michael Zdarsky (zdarsky_at_zac_dash_it_dot_com) writes:
> You are right with massive in list, but the problem is,
> that each in list can be longer than 4000 NVARCHAR, so I have to split the
> inlists in different variables.
If your dynamic SQL is only for lists for IN, you are barking up the
wrong tree. Not only is it difficult to manage, you get awful performance.
Have a look at http://www.sommarskog.se/arrays-in-sql.html for several
alternative methods. And you can use ntext for your lists.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dynamic SQL statement

I'm still a newbie and can't get this dynamic SQL statement to work. Any
help appreciated.
Declare @.sqlStmt nvarchar(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 240
Set @.Range = 36000
--EXEC --('insert Into ' + @.TempVS +
SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '
EXEC @.sqlstmt
I get an error :
Server: Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'Select Distinct patNo, DateDrawn,
Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '.
Thanks for the help,
Stephen K. MiyasatoDon't call Exec. Use sp_execsql instead. Look it up in books online for
examples, but you're about 90% of the way to implementing it.
Regards
Colin Dawson
www.cjdawson.com
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>|||Thanks for the help.
Stephen K. Miyasato
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:oH47g.65228$wl.53809@.text.news.blueyonder.co.uk...
> Don't call Exec. Use sp_execsql instead. Look it up in books online for
> examples, but you're about 90% of the way to implementing it.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
>|||Just adding to Colin's comment.
don't use nvarchar unless u are sure about unicode character. use varchar.
and
for executing dynamic sql
use
EXEC(@.sqlstmt)
again. This is just for the syntax.
Use sp_executesql. Its better in code reuse and does the same job from a
developers standpoint.
--
"Stephen K. Miyasato" wrote:

> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
You need parentheses after EXEC to execute dynamic SQL. Now you are
trying to execute a stored procedure of which the name is in @.sqlstmt.
Again, I recommend you to read my article
http://www.sommarskog.se/dynamic_sql.html. That will give you the
answer to this question, it will also sort out some problems that
you have run into yet. And you may even find out that you don't need
dynamic SQL at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dynamic sql problem

Hello: I am tearing my hair out over this.

I am using sql server 2005 and am getting the error "arithmetic overflow error converting nvarchar to numeric." The sp is below. It works fine when I do not include the numeric variable @.z and only insert the other 3 fields. The varbinary(MAX) is not the problem. The problem is syntax error mixing numbers and strings. How do I put the numeric @.z in the @.query string so I can insert a value into the FileID numeric field?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER off

ALTER PROCEDURE [dbo].[uspInsert_Blob] @.x as nvarchar(60), @.y as nchar(5), @.z as numeric(18, 0)

as

DECLARE @.QUERY VARCHAR(2000)

SET @.QUERY = "INSERT INTO myTable(FileID, FileName, FileType, Document)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " +@.z+ " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

EXEC (@.QUERY)

cast the variable to a nvarchar(18)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " + cast(@.z as nvarchar(18)) + " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

|||

Yes that works. I thank you so much.

When the exec query runs, does sql server recast it as numeric since the parameter and the field are numeric?

|||

Yes, it does an implicit conversion based on datatype conversion precedence:

You can see them listed here:
http://msdn2.microsoft.com/en-us/library/ms190309.aspx

All lower precedence types get implicitly converted to the highest precedence type if it is possible.

Louis