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