Solved

Using Linked Server in SSRS 2000 Reports( Linked Server Query returns NULL)

Posted on 2009-05-08
6
192 Views
Last Modified: 2012-05-06
Hi there:
  I have a query which requires the liked server in sql server . Below is the query.
However,it always returns NULL data...

Anyone has seen this before?

 thanks

 Hui


select ps_tbl.DESCR from PSDB..SYSADM.PS_PRODUCT_TBL ps_tbl 

where ps_tbl.PRODUCT = ' substring(ccontl.gl_type_id,7,4)'

Open in new window

0
Comment
Question by:HuiShi
  • 3
  • 3
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24338005
select ps_tbl.DESCR
from PSDB..SYSADM.PS_PRODUCT_TBL ps_tbl
where ps_tbl.PRODUCT =  substring(ccontl.gl_type_id,7,4)   --- no quotes here
0
 

Author Comment

by:HuiShi
ID: 24338111
Thanks.  I got  the following error after putting the modified Clause

Can not resolve teh collation confliect between
"SQL_LATIN1_GENERAL_CP1_CS_AS"  and
"SQL_LATIN1_GENERAL_CP1_CI_AS" IN the equal to operation

any idea about that?

thanks
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24338201

select ps_tbl.DESCR
from PSDB..SYSADM.PS_PRODUCT_TBL ps_tbl
where ps_tbl.PRODUCT COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS = substring(ccontl.gl_type_id,7,4) COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:HuiShi
ID: 24338428
thanks, it works. Can you tell why it's working? also why do I need to punch the
COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS?

Hui
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24338464
the columns in the where clause have different collations, thats why you were getting the error. and the COLLATE function just convert a column /value into a different collation
0
 

Author Closing Comment

by:HuiShi
ID: 31579537
Thanks!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now