Transaction Deadlock Error

I created a query which was used by a SSRS Report. While running the report I got the following error:

"Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim"

I resolved the error by writing the following statement in my stored procedure

"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"

This statement set the nolock on each table/view used in the query

Changing Lookup Field Value using Javascript

/*
Author : Syed Ahmed Yasir
Sr. Software Eng.
Enterprise Business Solutions
yasir@bussolpk.com
0333-2219078

Dated : 20th June 2008
Purpose : To Set the Account in Customer Field and Set the Contact in Responsible Contact field
*/
/*Setting the contact ==================================================*/
var lookupItem = crmForm.all.customerid.DataValue;crmForm.all.responsiblecontactid.DataValue = lookupItem;

/*==================================================*/
//Restricting the Account Selection in CustomerID Field
//=================================================
crmForm.all.customerid.setAttribute("lookuptypes", "1");
//=================================================
//Accessing the Database to get the Parent Account of the Contact
//=================================================
var contactid = lookupItem[0].idvar connection = new ActiveXObject("ADODB.Connection");
var connectionString = "Provider=SQLOLEDB;Server=crmdev;Database=inbox_mscrm;Integrated Security=sspi";
connection.Open(connectionString);
var query = "select cb.accountid,ab.name from contactbase cb inner join accountbase ab on cb.accountid = ab.accountid where cb.contactid = '"+ contactid +"'";

var rs = new ActiveXObject("ADODB.Recordset");
rs.Open(query, connection, 1, 2);
rs.moveFirst();
var accountid;
var accountname;
while (!rs.eof)
{
accountid = rs.Fields(0).Value.toString() ;
accountname = rs.Fields(1).Value.toString();
rs.moveNext();
}
connection.Close();
/*=================================================
To Set the Fetched Account in Customer Field*/

var lookupData = new Array();
var lookupItem= new Object();
lookupItem.id = accountid;
lookupItem.typename = 'account';
lookupItem.name = accountname;
lookupData[0] = lookupItem;
crmForm.all.customerid.DataValue = lookupData;

Meet your new friend – The SharePoint Designer 2007

The SharePoint designer 2007 will let you open a site, and edit it to your heart’s content. Here is a useful link regarding Sharepoint Designer 2007

http://blah.winsmarts.com//2006-7-Customizing_Sharepoint_2007__Customizing_using_SharePoint_Designer_2007.aspx

Custom Web Part for MOSS 2007

I am entering in the world of developing/customizing sharepoint 2007. My initial searches on google return many results but this one is very good for a beginner. Here is the link

http://www.codeguru.com/csharp/.net/net_asp/webforms/article.php/c12293/

I hope it will help you.

Writing Microsoft CRM 3.0 Callouts

I found a very useful article on Code project regarding Writing Microsoft CRM 3.0 Callouts. Please go to the following link read the article

http://www.codeproject.com/KB/cs/Writing_MS_CRM_Callouts.aspx

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