Linking entities in report builder by table columns that are not primary or foriegn keys.

What is the best way to join two tables by something other than there primary or foriegn keys in report builder. I actually want a relationship between two tables based on a combination of two columns.

Thanks

Dear,

Delete the relationship and join the table as per ur criteria.But the Primary and Foregin key Relation is the best to retrieve the record and applying functions. and also on criteria basis.

HTH

From

Sufian

Linking columns with the table name

Hi Everyone,
Is there a way to read syscolumns and link the column to the table in which
the column resides?
Thanks in advance
Larry
select object_name(id),name from syscolumns
order by object_name(id),colorder
will give you everything in the syscolumns, and the table/sp that it is in.
not sure this is what you want or not.
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C8FE40BA-274A-4526-B36E-86483D2D683E@.microsoft.com...
> Hi Everyone,
> Is there a way to read syscolumns and link the column to the table in
which
> the column resides?
> Thanks in advance
> Larry
|||Hi Larry
You can look at the code for sp_help to see how that procedure links the
tables together.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C8FE40BA-274A-4526-B36E-86483D2D683E@.microsoft.com...
> Hi Everyone,
> Is there a way to read syscolumns and link the column to the table in
> which
> the column resides?
> Thanks in advance
> Larry

Linking columns with the table name

Hi Everyone,
Is there a way to read syscolumns and link the column to the table in which
the column resides?
Thanks in advance
Larryselect object_name(id),name from syscolumns
order by object_name(id),colorder
will give you everything in the syscolumns, and the table/sp that it is in.
not sure this is what you want or not.
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C8FE40BA-274A-4526-B36E-86483D2D683E@.microsoft.com...
> Hi Everyone,
> Is there a way to read syscolumns and link the column to the table in
which
> the column resides?
> Thanks in advance
> Larry|||Hi Larry
You can look at the code for sp_help to see how that procedure links the
tables together.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C8FE40BA-274A-4526-B36E-86483D2D683E@.microsoft.com...
> Hi Everyone,
> Is there a way to read syscolumns and link the column to the table in
> which
> the column resides?
> Thanks in advance
> Larry

Linkedserver , Navision C/ODBC, Null columns Error

hello
i am having a few problems with Linkedserver and Navision Financials.
Most of the tables in navision (Customer, Prospect, Sales Line etc) have
DateTime columns that may contain nothing (null).
if i try to select these records i will get an error but i need access to
these columns so i can find out it the record has been updated recently.
the error is:
[Microsoft][ODBC Sql Server Driver][SQL Server]OLE DB error trace
[Non-interface error: Unexpected NULL value returned for the column:
ProviderName='MSDASQL', TableName'[Rowset_1]',ColumnName='Date_Completed'].
so how can i retrieve records with null columns? can i specify somewhere in
sql statements to ignore null columns or something? below is a sample sql
query.
also note that the funtion IFNULL dont work for me, i will still get an
error
example sql statement:
DBCC TRACEON (8765)
SELECT * FROM
OPENQUERY(NAVISION,'SELECT
* FROM Sales_Header WHERE Document_Type = ''Order'' ')
if i replace * with specific column names (No_ Name_ etc) it will work fine,
as long as Date_Completed isnt requested (in this case).
i am develping a navision client for users on the road (using XDA II ppc's)
that will connect over GPRS and webservices so this is why i require the
DateTime fields, so the user will know if the records are upto date in the
SqlCe database.
Regards,
Ricardo Meechan
I am having the exact same problem. Have you gotten any further in resolving this issue?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Linkedserver , Navision C/ODBC, Null columns Error

hello
i am having a few problems with Linkedserver and Navision Financials.
Most of the tables in navision (Customer, Prospect, Sales Line etc) have
DateTime columns that may contain nothing (null).
if i try to select these records i will get an error but i need access to
these columns so i can find out it the record has been updated recently.
the error is:
[Microsoft][ODBC Sql Server Driver][SQL Server]OLE DB error trac
e
[Non-interface error: Unexpected NULL value returned for the column:
ProviderName='MSDASQL', TableName'& #91;Rowset_1]',ColumnName='Date_Complete
d
'].
so how can i retrieve records with null columns? can i specify somewhere in
sql statements to ignore null columns or something' below is a sample sql
query.
also note that the funtion IFNULL dont work for me, i will still get an
error
example sql statement:
DBCC TRACEON (8765)
SELECT * FROM
OPENQUERY(NAVISION,'SELECT
* FROM Sales_Header WHERE Document_Type = ''Order'' ')
if i replace * with specific column names (No_ Name_ etc) it will work fine,
as long as Date_Completed isnt requested (in this case).
i am develping a navision client for users on the road (using XDA II ppc's)
that will connect over GPRS and webservices so this is why i require the
DateTime fields, so the user will know if the records are upto date in the
SqlCe database.
Regards,
Ricardo MeechanI am having the exact same problem. Have you gotten any further in resolving
this issue?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Linked text file with columns > 255 chars

I'm trying to create a linked server to a text file using Jet. The text file has columns with data larger than 255 characters. For some reason (I think its a limitation of Jet), when I attempt to query the said columns, SQL Server thinks the data type is ntext. This is causing a problem as I'm trying to insert these columns into corresponding columns in my SQL Server database (whose types are varchar(1000)). Is there a way I can link a text file without Jet? (or get around this restriction) Thanks

TQ1 I'm trying to insert these columns into corresponding columns in my SQL Server database (whose types are varchar(1000)) The text file has columns with data larger than 255 characters.


A1 If the business requirement amounts to simply inserting data rows from a text file source, you may wish to consider one or more of the following (Sql Server versions >= 7.0 for DTS bulk insert):

1 bulk insert,
2 bcp, or
3 DTS.

Q2 Is there a way I can link a text file without Jet?


A2 Yes.

For example, if the business requirement necessitates an external linked text file source (that may be accessed and modified by other applications), you may wish to consider a Microsoft OLE DB Provider for ODBC linked server connection instead. To do so:

Use a Microsoft OLE DB Provider for ODBC linked server connection (with an appropriate System DSN specifying the appropriate directory containing the text and schema files). Implement a LONGCHAR in the schema ini file for any char columns >255 chars wide. {The theoretical limit of the width of a LONGCHAR column in either a fixed-length or delimited table is 65500K. The Text ISAM is more likely to provide reliable support up to about 32K. The LONGCHAR is interpeted as a text type, but that doesn't perclude insertion into a varchar 1000 column.}

See ODBC Drivers text file support:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjettext_data_types.asp|||that should work nicely...

server to Oracle - error on NVARCHAR2()

I have a linked server in a SQL 2000 DB - links to Oracle9 DB. When I query
tables with columns of type NVARCHAR2 I get an error. Does any one know of a
solution to this?
LMcPheeWhat's the error.
"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:96F46853-6BA2-40FC-BED6-2B391812BFF6@.microsoft.com...
> I have a linked server in a SQL 2000 DB - links to Oracle9 DB. When I
query
> tables with columns of type NVARCHAR2 I get an error. Does any one know of
a
> solution to this?
> LMcPhee