ERR: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The error i.e.

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

comes when you join two tables with different collation. To solve this problem join the two tables by explicitly make the collation same. Like this


select t.invoice_id,t.amountpaid,t.amountrec,t.aging,t.invoicestatus,convert(varchar (25),t.invoicedate,3)invoicedate ,convert(varchar(25),t.duedate,3) duedate, ab.name from #temp t
inner join accountbase ab on t.customerid COLLATE Latin1_General_CI_AS = ab.accountnumber

An Update Query - Integration with Oracle E-Business Suite

An update query I wrote during the creation of a report. I want to share that query with all the readers of this blog, may be it will help you in creating reports.


create table #InvoiceData (Invoice_ID varchar(15),Customer_ID varchar(15),Invoice_Date datetime ,Due_Date datetime,Invoice_Amount float,Invoice varchar(10))
insert into #InvoiceData (Invoice_ID,Customer_ID,Invoice_Date,Due_Date,Invoice_Amount,Invoice)
SELECT DISTINCT Invoice_ID,Customer_ID,Invoice_Date,Due_Date,Invoice_Amount,Invoice
FROM OPENQUERY(OracleEBiz2,'select * from customer_invoice')
create table #temp (invoice_id varchar(25) ,amountpaid float ,amountrec float,aging int,invoicestatus varchar(25),invoicedate datetime, duedate datetime,customerid varchar(25))
insert into #temp(invoice_id)
select distinct invoice_id
from #InvoiceData
update #temp
set amountpaid = temp2.IA --(Select SUM(Inovice_Amount) from @InvoiceData WHERE Invoice='INVOICE' AND Invoice_ID = @temp.invoiceID)
from
(
Select invoice_id,ISNULL(sum(Invoice_Amount),0) IA,invoice
from #InvoiceData
where invoice='INVOICE'
group by invoice_id,invoice
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id

update #temp
set amountrec = temp2.IA --(Select SUM(Inovice_Amount) from @InvoiceData WHERE Invoice='INVOICE' AND Invoice_ID = @temp.invoiceID)
from
(
Select invoice_id,ISNULL(sum(Invoice_Amount),0.0) IA,invoice
from #InvoiceData
where invoice='RECEIPT'
group by invoice_id,invoice
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id

update #temp
set invoicedate = Invoice_Date,
duedate = Due_Date,
customerid = Customer_ID
from #temp,#InvoiceData
where #temp.invoice_id = #InvoiceData.invoice_id
/*update #temp
set
from #temp,#InvoiceData
where #temp.invoice_id = #InvoiceData.invoice_id
*/
update #temp
set aging = temp2.aging
from
(
select invoice_id,(case when (#temp.amountpaid = #temp.amountrec) then 0 else datediff(day,#temp.duedate,getdate()) end) aging
from #temp
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id
update #temp
set invoicestatus = temp2.[status]
from
(
select invoice_id,(case when (#temp.aging = 0) then 'Paid' else 'Pending' end) [status]
from #temp
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id


select t.invoice_id,t.amountpaid,t.amountrec,t.aging,t.invoicestatus,convert(varchar(25),t.invoicedate,3)invoicedate ,convert(varchar(25),t.duedate,3) duedate, ab.name from #temp t
inner join accountbase ab on t.customerid COLLATE Latin1_General_CI_AS = ab.accountnumber
where aging <> 0
order by ab.name,invoice_id

MS CRM Reports Error

During an implementation of MS CRM 3.0, I came accross a problem of MSRS. Before describing the problem I should describe the network structure and details of server. We have two servers

(1) CRM and Sharepoint Server
(2) "SQL Server 2005" Server i.e. database server, reporting service and analytical
service running on this server

i.e. we have crm server separate from database/reporting service server. The problem which I faced was the reports of crm were not opening. Even the link of reports in workplace in crm wasn't visible. The problem became critical day by day as the client was demanding for reports. After excessive search I found the solution in a forum and the solution was to enter a "DWORD" in registry at
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSCRM
and the DWord is "NTLMForSQLRSServer".

This solved my problem.

Access Link Server

In my first implementation of MS CRM 3.0, I have to integrate MS CRM 3.0 with an application which is developed in MS Access. I have to create a stored procedure which picks data from access database and insert or update the corresponding CRM custom entity's table. For this purpose I created a link server in SQL Server 2005 for access. Here is the code of creating that link server


-- ========================================
-- Add Linked Server Access MDB template
-- ========================================
EXEC sp_addlinkedserver
@server = 'InboxAccessDB',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access',
@datasrc = 'D:\INBOX\CRM\Inbox Access DB\Report-Deliver 02-05-2007_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'InboxAccessDB',
@useself = 'TRUE',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = 'inboxdata'
GO
-- List the tables on the linked server
EXEC sp_tables_ex 'InboxAccessDB'
GO