non unicode

Hi,
Is DT_STR Unicode? From SSIS I am using a data conversion component
and defining the data type as DT_STR and saving the output in excel
file. I am getting an error that 'can not convert from Unicode to non-
unicode'. I thought I have already taken care of that in data
conversion.Could you explain exactly which data conversion you prepared? The DT_STR was
specified for the input field or for the output? In this last case, which is
the input field data type?
Gilberto Zampatti
"SB" wrote:
> Hi,
> Is DT_STR Unicode? From SSIS I am using a data conversion component
> and defining the data type as DT_STR and saving the output in excel
> file. I am getting an error that 'can not convert from Unicode to non-
> unicode'. I thought I have already taken care of that in data
> conversion.
>|||On Apr 20, 2:10 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Could you explain exactly which data conversion you prepared? The DT_STR was
> specified for the input field or for the output? In this last case, which is
> the input field data type?
> Gilberto Zampatti
>
> "SB" wrote:
> > Hi,
> > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > and defining the data type as DT_STR and saving the output in excel
> > file. I am getting an error that 'can not convert from Unicode to non-
> > unicode'. I thought I have already taken care of that in data
> > conversion.- Hide quoted text -
> - Show quoted text -
Hi,
DT_STR should be an input field because I am getting data from a table
using ole db source.|||OK; supposing that input field is actually non-unicode (DT_STR could be the
right data type), verify the transformation task to be sure about the output
data type. SQL 2005 uses UNICODE by default, and you should explicitly
declare non unicode data types.
If you correct the input field definition specifying Unicode (DT_WSTR), you
should avoid such error message.
The fact that you are accessing input table via OLE DB does'nt change the
fact that QSL 2005 uses Unicode by default.
Gilberto Zampatti
"SB" wrote:
> On Apr 20, 2:10 pm, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > Could you explain exactly which data conversion you prepared? The DT_STR was
> > specified for the input field or for the output? In this last case, which is
> > the input field data type?
> > Gilberto Zampatti
> >
> >
> >
> > "SB" wrote:
> > > Hi,
> > > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > > and defining the data type as DT_STR and saving the output in excel
> > > file. I am getting an error that 'can not convert from Unicode to non-
> > > unicode'. I thought I have already taken care of that in data
> > > conversion.- Hide quoted text -
> >
> > - Show quoted text -
> Hi,
> DT_STR should be an input field because I am getting data from a table
> using ole db source.
>|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode (DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 uses UNICODE by default, and you should explicitly
> declare non unicode data types.
> If you correct the input field definition specifying Unicode (DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 uses Unicode by default.
> Gilberto Zampatti
>
> "SB" wrote:
> > On Apr 20, 2:10 pm, Gilberto Zampatti
> > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > specified for the input field or for the output? In this last case, which is
> > > the input field data type?
> > > Gilberto Zampatti
> > > "SB" wrote:
> > > > Hi,
> > > > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > > > and defining the data type as DT_STR and saving the output in excel
> > > > file. I am getting an error that 'can not convert from Unicode to non-
> > > > unicode'. I thought I have already taken care of that in data
> > > > conversion.- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > DT_STR should be an input field because I am getting data from a table
> > using ole db source.- Hide quoted text -
> - Show quoted text -
Thanks.
For output I have created a excel table using the statement:
CREATE TABLE `Excel Destination` (
`NAME` VARCHAR(),
)
I have tried string, text etc but no success.
Excel Destination editor:
Data Access Mode:
Table or view
Name of the excel sheet:
Excel Destination|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode(DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 usesUNICODEby default, and you should explicitly
> declare nonunicodedata types.
> If you correct the input field definition specifyingUnicode(DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 usesUnicodeby default.
> Gilberto Zampatti
>
> "SB" wrote:
> > On Apr 20, 2:10 pm, Gilberto Zampatti
> > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > specified for the input field or for the output? In this last case, which is
> > > the input field data type?
> > > Gilberto Zampatti
> > > "SB" wrote:
> > > > Hi,
> > > > Is DT_STRUnicode? From SSIS I am using a data conversion component
> > > > and defining the data type as DT_STR and saving the output in excel
> > > > file. I am getting an error that 'can not convert fromUnicodeto non-
> > > >unicode'. I thought I have already taken care of that in data
> > > > conversion.- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > DT_STR should be an input field because I am getting data from a table
> > using ole db source.- Hide quoted text -
> - Show quoted text -
Hi,
Is it alright if I declare a datatype as nvarchar 'create table (name
nvarchar(20))' and then treat it as if varchar for example functions,
inserts and join with other varchar columns etc? Except giving a
larger string size I don't have much use for unicode. They might as
well keep this type internal so that we didn't have to worry about it.|||On Apr 23, 10:41 am, SB <othell...@.yahoo.com> wrote:
> On Apr 20, 3:28 pm, Gilberto Zampatti
>
>
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > OK; supposing that input field is actually non-unicode(DT_STR could be the
> > right data type), verify the transformation task to be sure about the output
> > data type. SQL 2005 usesUNICODEby default, and you should explicitly
> > declare nonunicodedata types.
> > If you correct the input field definition specifyingUnicode(DT_WSTR), you
> > should avoid such error message.
> > The fact that you are accessing input table via OLE DB does'nt change the
> > fact that QSL 2005 usesUnicodeby default.
> > Gilberto Zampatti
> > "SB" wrote:
> > > On Apr 20, 2:10 pm, Gilberto Zampatti
> > > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > > specified for the input field or for the output? In this last case, which is
> > > > the input field data type?
> > > > Gilberto Zampatti
> > > > "SB" wrote:
> > > > > Hi,
> > > > > Is DT_STRUnicode? From SSIS I am using a data conversion component
> > > > > and defining the data type as DT_STR and saving the output in excel
> > > > > file. I am getting an error that 'can not convert fromUnicodeto non-
> > > > >unicode'. I thought I have already taken care of that in data
> > > > > conversion.- Hide quoted text -
> > > > - Show quoted text -
> > > Hi,
> > > DT_STR should be an input field because I am getting data from a table
> > > using ole db source.- Hide quoted text -
> > - Show quoted text -
> Hi,
> Is it alright if I declare a datatype as nvarchar 'create table (name
> nvarchar(20))' and then treat it as if varchar for example functions,
> inserts and join with other varchar columns etc? Except giving a
> larger string size I don't have much use for unicode. They might as
> well keep this type internal so that we didn't have to worry about it.- Hide quoted text -
> - Show quoted text -
That question was not related to SSIS package (as you have told ssis
is unicode). FWIW I was able to get the data (ssis) imported when I
used unicode for input and output data using a data conversion in
between from varchar to unicode string.

Nominal intrest in TSQL

Hi

I have to make a calculation based on the Excel Nominal function to get the nomanal Interest

Month Quarter Half year per year 2% = 0,16516% 0,49629% 0,99505% 2,00% 3% " 0,24663% 0,74171% 1,48892% 3,00% 4% " 0,32737% 0,98534% 1,98039% 4,00% 5% " 0,40741% 1,22722% 2,46951% 5,00% 6% " 0,48676% 1,46738% 2,95630% 6,00% 7% " 0,56541% 1,70585% 3,44080% 7,00% 8% " 0,64340% 1,94265% 3,92305% 8,00% 9% " 0,72073% 2,17782% 4,40307% 9,00% 10% " 0,79741% 2,41137% 4,88088% 10,00% 11% " 0,87346% 2,64333% 5,35654% 11,00% 12% " 0,94888% 2,87373% 5,83005% 12,00%

In Exell the formula goes like this:
Per Month =NOMINAL(aIntrest;12)/12
Per Quarter =NOMINAL(aIntrest;4)/4
Half Year =NOMINAL(aIntrest;2)/2
Per Year =NOMINAL(aIntrest;1)

I would be very happy to get help on this.

I'm not entirely sure, if Excel's NOMINAL is working that way, however you can use the following to get the same results

DECLARE @.apr FLOAT
DECLARE @.frequency FLOAT

SET @.apr = 0.02
SET @.frequency = 12
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Monthly
SET @.frequency = 4
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Quarterly
SET @.frequency = 2
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Semiannual
SET @.frequency = 1
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Annual
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

Super

Thanks, so simple

Nominal intrest in TSQL

Hi

I have to make a calculation based on the Excel Nominal function to get the nomanal Interest

Month

Quarter

Half year

per year

2%

=

0,16516%

0,49629%

0,99505%

2,00%

3%

"

0,24663%

0,74171%

1,48892%

3,00%

4%

"

0,32737%

0,98534%

1,98039%

4,00%

5%

"

0,40741%

1,22722%

2,46951%

5,00%

6%

"

0,48676%

1,46738%

2,95630%

6,00%

7%

"

0,56541%

1,70585%

3,44080%

7,00%

8%

"

0,64340%

1,94265%

3,92305%

8,00%

9%

"

0,72073%

2,17782%

4,40307%

9,00%

10%

"

0,79741%

2,41137%

4,88088%

10,00%

11%

"

0,87346%

2,64333%

5,35654%

11,00%

12%

"

0,94888%

2,87373%

5,83005%

12,00%

In Exell the formula goes like this:
Per Month =NOMINAL(aIntrest;12)/12
Per Quarter =NOMINAL(aIntrest;4)/4
Half Year =NOMINAL(aIntrest;2)/2
Per Year =NOMINAL(aIntrest;1)

I would be very happy to get help on this.

I'm not entirely sure, if Excel's NOMINAL is working that way, however you can use the following to get the same results

DECLARE @.apr FLOAT
DECLARE @.frequency FLOAT

SET @.apr = 0.02
SET @.frequency = 12
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Monthly
SET @.frequency = 4
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Quarterly
SET @.frequency = 2
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Semiannual
SET @.frequency = 1
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Annual
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

Super

Thanks, so simple

No tables in Linked Excel Server view

This allowed me to create a linked server, but when I try and view the
tables, it does not show any tables.
I assumed the tables would be the sheet names, no?
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'H:\IT\Shared\tblInItem.xls',
NULL,
'Excel 5.0'
GO
--
-Tobias Mazzei
Wraith SystemsNot really - see example F:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
You need to name the cell range. I think I saw a VBA procedure some where
(online) that automates range naming - provided there's only a single table
per worksheet.
ML|||Okay, I found the skinny on this. I hope my pain helps others on this.
Apparently I goofed in setting up the linked servers to begin with.
The standard procedure for setting these up is to
1) Create the Excel file(s)
2) Name the columns using the range name function, these names will show up
in your "tables" list once you establish the link
3) Make sure you have symin AND setupadmin rights first
4) Use Enterprise Mgr to right click on linked servers and create new link
-or-
Use Sql Query Analyzer and build a statement like:
DECLARE @.RC int
DECLARE @.server nvarchar(128)
DECLARE @.srvproduct nvarchar(128)
DECLARE @.provider nvarchar(128)
DECLARE @.datasrc nvarchar(4000)
DECLARE @.location nvarchar(4000)
DECLARE @.provstr nvarchar(4000)
DECLARE @.catalog nvarchar(128)
-- Set parameter values
-- Remote Server Name
SET @.server = 'xcltblInItemUom'
-- Program you are importing from
SET @.srvproduct = 'Excel'
-- Type of Data Link Engine
SET @.provider = 'Microsoft.Jet.OLEDB.4.0'
-- Important, Use full domain namespace
SET @.datasrc = '\\svrsbs3hyc\Company\IT\Shared\tblInIte
mUom.xls'
SET @.provstr = 'Excel 8.0'
-- Execute command to link server to Master.DBO
EXEC @.RC = [master].[dbo].[sp_addlinkedserver]
@.server, @.srvproduct, @.provider,
@.datasrc, @.location, @.provstr, @.catalog
Note that if you are not programming directly ON the SQL server you MUST use
the UNC of the location of the excel files. (most of us dont use the server
to program on, so this is important)
5) Depending on your security policies, choose link server properties after
link, and under security, select Be made without using a security context
6) when referencing the query, the sheet is designated by a '$' after the na
me
7) Rinse, repeat for each file you need to link.
This operation works, although I am sure that there are more glamourous ways
of doing this.
For those beginners out there that didn't get this stuff in MCDBA courses,
good luck!!
-Tobias Mazzei
Wraith Systems
"ML" wrote:

> Not really - see example F:
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> You need to name the cell range. I think I saw a VBA procedure some where
> (online) that automates range naming - provided there's only a single tabl
e
> per worksheet.
>
> ML