Loss of Decimals Upon Link to Access

Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is base
d
on a table which has some data types as float. I created a view on the table
.
The view shows me units of a product divided by units of all products. The
results are expressed in the view as decimals. So, for example, .499857. Thi
s
is what I want. However, when I link the view to Access, all of my decimals
become zero. For example, .499857 becomes 0. I'm completely confounded. Any
suggestions would be fantastic! Thanks!If the SQL view is correct and you can use Query Analyzer to
view the results and they are as expected then this is more
of any MS Access issue. Make sure you have the latest Jet
service pack installed on the client.
But this is more of an Access issue so you would want to try
posting in one of the Access newsgroups. When posting your
question, be sure to include versions (version of SQL
Server, version of Access), what service packs you are
using.
-Sue
On Wed, 19 Apr 2006 08:11:02 -0700, Mike C
<MikeC@.discussions.microsoft.com> wrote:

>Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is bas
ed
>on a table which has some data types as float. I created a view on the tabl
e.
>The view shows me units of a product divided by units of all products. The
>results are expressed in the view as decimals. So, for example, .499857. Th
is
>is what I want. However, when I link the view to Access, all of my decimals
>become zero. For example, .499857 becomes 0. I'm completely confounded. Any
>suggestions would be fantastic! Thanks!

Loss of connection to linked servers -- Please help

All,
SQL 2000, sp3, Server 2000 sp4
I have 3 servers, they are all set up as linked servers. The link to the
other servers works then all of a sudden you can't see the other
servers. Has anyone ever seen this?
I have even deleted the info in the client network utility and tried to
re-register the servers, to no avail.
Please help.
Thanks All,
snyper
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!hi,
mostly linked to physical network factor.
check that part out.
thanks
rahul
>--Original Message--
>All,
>SQL 2000, sp3, Server 2000 sp4
>I have 3 servers, they are all set up as linked servers.
The link to the
>other servers works then all of a sudden you can't see
the other
>servers. Has anyone ever seen this?
>I have even deleted the info in the client network
utility and tried to
>re-register the servers, to no avail.
>Please help.
>Thanks All,
>snyper
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>sql

Lookup with more possibilities?

How can I do a lookup which doens't directly link two columns but uses another statement?

I tried in advanced with:

Code Snippet

select * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)

and adding 2 parameters.

Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0

So I guess that's not the way to go. Any other way to tackle this?

Aren't you missing a ")" at the end? I have sucessfully tried using the advanced tab to input parameters into the lookup transform in the past....

Here is a decent article on this type of action: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

|||Nope no ) missing. I got 2 ( and 2 ) so that's all good. Strange, it should work, I'll play some more with it. Strange thing, it works when I don't do the advanced stuff so something must be wrong there.|||

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

|||

rept wrote:

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

Sure.|||

yes.

Notice that parameter 1 and 2 of the julian kuiters article are both "modifydate".

|||

Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?

|||

" Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?"

I have the same question. It looks like that SQL is more complex that it should be; and I know for sure that SSIS is not very good at finding the parameter in subqueries. Give it a try without using that in-line-view and see if that fix the problem.

|||

Thanks for all the replies!

I just extended what SSIS had by default (same as in Julian Kuiters article as well BTW). I replaced it now but no difference however.

|||

Finally figured it out.

You need to make sure that every parameter that you use in the query is also connected graphically in the columns tab! It doesn't matter if the relation you draw doesn't make sence, you need to for SSIS to be able to find the input column! Hope this will save someone a lot of time someday

Thanks for all who replied!

Lookup with more possibilities?

How can I do a lookup which doens't directly link two columns but uses another statement?

I tried in advanced with:

Code Snippet

select * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)

and adding 2 parameters.

Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0

So I guess that's not the way to go. Any other way to tackle this?

Aren't you missing a ")" at the end? I have sucessfully tried using the advanced tab to input parameters into the lookup transform in the past....

Here is a decent article on this type of action: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

|||Nope no ) missing. I got 2 ( and 2 ) so that's all good. Strange, it should work, I'll play some more with it. Strange thing, it works when I don't do the advanced stuff so something must be wrong there.|||

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

|||

rept wrote:

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

Sure.|||

yes.

Notice that parameter 1 and 2 of the julian kuiters article are both "modifydate".

|||

Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?

|||

" Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?"

I have the same question. It looks like that SQL is more complex that it should be; and I know for sure that SSIS is not very good at finding the parameter in subqueries. Give it a try without using that in-line-view and see if that fix the problem.

|||

Thanks for all the replies!

I just extended what SSIS had by default (same as in Julian Kuiters article as well BTW). I replaced it now but no difference however.

|||

Finally figured it out.

You need to make sure that every parameter that you use in the query is also connected graphically in the columns tab! It doesn't matter if the relation you draw doesn't make sence, you need to for SSIS to be able to find the input column! Hope this will save someone a lot of time someday

Thanks for all who replied!

Lookup Transform error when linking using a DT_R8

I'm trying to lookup a value in another table linking on a column of datatype DT_R8. The lookup transform is complaining that I can't link on that datatype. However, the documentation says that it should work. I'm using the April CTP. Is this fixed in a later version? Any suggestions?

I'm sure DT_R8 does work in a Lookup. You may get an error such as -

[DDD, BackOfficeSoftwareKey]
One or more columns do not have supported data types, or their data types do not match.

This means that the source data type and the reference data type do not match. All lookups, the mappings between source and reference columns, must compare data types that match exactly, so you cannot compare a DT_I4 with a DT_R8 for example. One of them needs to be converted to the same type as the other before the match. You may be able to do this in the source extraction, or in the lookup reference table specification, use a SQL query with a CAST, or finally use a Data Conversion Tramsform or Derived Column.

|||I'm pretty sure that's not the problem becuase I've tried that approach. I've also double-checked the data types in the advanced editor and the datatypes do match. They are both DT_R8. It's not the usual error message that you mentioned. It's a different one that says "input column <column_name> has a datatype which cannot be joined on."|||DT_R8 is not allowed as a join column. So are DT_R4 and BLOBs (DT_TEXT, DT_NTEXT, DT_IMAGE).

Looking for some examples link server, excel file

I have excel file and need to create a link server so I can read excel file
from sql2k.
Server where file is residing is SERVER1
Excel file name is ABC
User name is XYZ
password is PASSWORD
and the column that I want to retreive is Price.
I would appreciate if someone please show me an example for Link server
Thanks
msnews.microsoft.com wrote:
> I have excel file and need to create a link server so I can read excel file
> from sql2k.
> Server where file is residing is SERVER1
> Excel file name is ABC
> User name is XYZ
> password is PASSWORD
>
> and the column that I want to retreive is Price.
> I would appreciate if someone please show me an example for Link server
> Thanks
>
>
Hi
Try to look up "sp_addlinkedserver" in Books On Line. Here you have the
syntax for adding an Excel file as a linked server.
Regards
Steen

Looking for some examples link server, excel file

I have excel file and need to create a link server so I can read excel file
from sql2k.
Server where file is residing is SERVER1
Excel file name is ABC
User name is XYZ
password is PASSWORD
and the column that I want to retreive is Price.
I would appreciate if someone please show me an example for Link server
Thanksmsnews.microsoft.com wrote:
> I have excel file and need to create a link server so I can read excel fil
e
> from sql2k.
> Server where file is residing is SERVER1
> Excel file name is ABC
> User name is XYZ
> password is PASSWORD
>
> and the column that I want to retreive is Price.
> I would appreciate if someone please show me an example for Link server
> Thanks
>
>
Hi
Try to look up "sp_addlinkedserver" in Books On Line. Here you have the
syntax for adding an Excel file as a linked server.
Regards
Steen

LOOKING FOR SITE WITH NOTES ON HOW I CAN CHANGE THE KNOWLEDGE OF MSSQL I HAVE TO ORAC

Hi developers!
I'm looking for the link where I can easily read and understand the way I
can transfer the knowledge I have of MSSQL to ORACLE and simply create some
procedures, views,functions,triggers,tables etc using ORACLE Database.
Anyone who can help on this will be highly appreciated.
Regards,
ComfortI'd try Oracle's site or just google it.
--Mary
On Mon, 14 Feb 2005 15:38:09 +0300, "Comfort" <comfort.peter@.stcl.com>
wrote:

>Hi developers!
>I'm looking for the link where I can easily read and understand the way I
>can transfer the knowledge I have of MSSQL to ORACLE and simply create some
>procedures, views,functions,triggers,tables etc using ORACLE Database.
>Anyone who can help on this will be highly appreciated.
>Regards,
>Comfort
>

Looking for references for querying Active Directory (AD) through SQL Server

Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.

Thanks.

There are a lot of examples out there:

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
http://support.microsoft.com/kb/299410

http://blogs.msdn.com/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Looking for examples for link server, excel file

I have excel file and need to create a link server so I can read excel file
from sql2k.
Server where file is residing is SERVER1
Excel file name is ABC
User name is XYZ
password is PASSWORD
and the column that I want to retreive is Price.
I would appreciate if someone please show me an example for Link server
Thanks
msnews.microsoft.com schrieb:
> I have excel file and need to create a link server so I can read excel file
> from sql2k.
> Server where file is residing is SERVER1
> Excel file name is ABC
> User name is XYZ
> password is PASSWORD
>
> and the column that I want to retreive is Price.
> I would appreciate if someone please show me an example for Link server
> Thanks
>
EXEC sp_addlinkedserver 'XLSSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', 'c:\yourDocument.xls',NULL, 'Excel 8.0'
hth
Stefka