.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

deadlock issue on my database. how to identify which stored procedure which has to be modified.

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
Hi ,
I am seeing so many deadlock issues on my database captured by a 3rd party tool. Could you help me identify which stored procedure is actually causing the deadlock looking at the below log information. Let me know if you need more information.
Thanks, Jeen
<deadlock-list>  <deadlock victim="process54a7948">  <process-list>   <process id="process54a7948" taskpriority="0" logused="0" waitresource="KEY: 6:72057594044678144 (cc00f361c119)" waittime="2975" ownerId="404930267" transactionname="SELECT" lasttranstarted="2011-05-02T08:19:03.683" XDES="0x8000f940" lockMode="S" schedulerid="2" kpid="9624" status="suspended" spid="65" sbid="1" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-02T08:19:03.653" lastbatchcompleted="2011-05-02T08:19:03.653" clientapp="Microsoft SQL Server" hostname="MyServer" hostpid="2500" loginname="India\testr" isolationlevel="read committed (2)" xactid="404930267" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">   &l

View Complete Post

More Related Resource Links

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

Update Database Record using GridView and Stored Procedure

Hi, I am trying to update record via stored procedure, but i got error at very start point. Problem is when i click on Edit link button within the Gridview it produce error. I can populate values from database fine but its produce error when i click on edit link button. Please see the code below. *********** ASP.net GridView Code I am using *****************<asp:GridView ID="GridView1" runat="server" DataKeyNames="RollID, EmpID" AutoGenerateColumns="False" Width="700px"> <Columns> <asp:TemplateField HeaderText="ApplicationID" Visible="false"> <ItemTemplate> <asp:Label ID="AppsID" runat="server" Visible="false"><%# Eval("RollID") %>'</asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ReleaseID" Visible="false"> <ItemTemplate> <asp:Label ID="rlsID" runat="server" Visible="false"><%# Eval("EmpID") %></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Roll Name"> <ItemTemplate> <asp:Label ID="rlsVersion" runat="server"><%# Eval("Roll_Name")%></asp:Label> </ItemTemplate> <EditI

Stored Procedure Subquery Issue

Hi, In the below stored procedure, I often get this error message... "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Why is this? I check for multiple entries and delete any that exist. Yet still gives that error on occassion.  Thanks. ALTER PROCEDURE [dbo].[MainTbl] -- Add the parameters for the stored procedure here @Name nvarchar(5), AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Begin Transaction DECLARE @DetailsColour nvarchar(6); -- Insert statements for procedure here Begin -- Details DECLARE @CountDetails int; DECLARE @FirstEntryDetails int; SET @CountDetails = (SELECT COUNT (Name) FROM [DetailsTbl] WHERE Name = @Name ) if (@CountDetails > 1) Begin SET @FirstEntryDetails = (SELECT TOP(1) NameID FROM [DetailsTbl] WHERE Name = @Name ORDER BY NameID ASC) DELETE FROM [DetailsTbl] WHERE Name = @Name AND (NameID != @FirstEntryDetails) End Set @DetailsColour = (SELECT DetailsColour FROM [DetailsTbl] WHERE Name = @Name) if @DetailsColour is null begin set @DetailsColour = 'RED' end DECLARE @FoundData int; SET @Found

Managing TypedDataSets when there is a change in backend Table / Stored Procedure / Database schema

Hi All, I am just curious to know if there is any easier way to synchronize the TypedDataSets with the chages made to backend Table / Stored Procedure schema.  I use Typed DataSets in my project extensively and I found them very useful and easier to code, but difficult to maintain  The difficuly I have always faced is whenever there is a change in a backend database table structure or stored procedure that is linked to any of the Typed DataSet in our project requires to recreate whole DataSet again by scrapping the old one. The backend changes will not be reflected unless and untill you recreate the whole stuff again. Is there any easier way to synchronize those typed datasets with the changes made to backend database schema or any other workaround that will not required to recreate the whole DataSet again.Hope above question makes sense. Any ideas.

Collation Issue in Stored Procedure



My stored procedure uses the temp table and other reporting DB table to combine the output. If I specify (hardcode) the collation to temp table column then it works fine.

But I want to retrieve the Collation name of Reporting DB within the stored procedure and assign it to temp table or temp table column.

Can anyone help me in this since I am not able to find any solution in internet?

Thanks in advance.


There is any way to identify the reports name, in which all I have used one Stored Procedure?



In my project, I have one Stored Procedure (SP) & that SP I have used in various reports. I have not maintained any documents for the same. Now for one report, I want to modify my Existing SP but this modification will affect other reports, in which I have used this SP. There is any simple way to find out the reports name, in which I used this SP.

All suggestions are welcome

Thanks Shiven:)

Need to execute ALTER DATABASE inside stored procedure


SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?


C# How to check prammatically if null value exists in database table (using stored procedure)?


How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];

CREATE DATABASE [ExampleDatabase];

USE [ExampleDatabase];

DROP TABLE dbo.ExampleTable;

IsActive BIT,


Stored procedure SQL issue


 Hi there.

Within my stored procedure I have a piece of SQL that is supposed to remove from a temporary table, any values that are not set to '1' for a particular field, but this does not work as required.

The SQL in question looks like this:


  • DELETE FROM table1
  • WHERE value_1 NOT IN
  • (
  •     SELECT tab1.value_1
  •     FROM table1 tab1
  •     JOIN table2 tab2 ON tab1.value_1 = tab2.value_1 AND tab1.line_no = tab2.line_no
  •     AND tab1.client = tab2.client
  •     JOIN table3 tab3 ON tab3.client = tab2.client 
  •     AND tab3.THIS_VALUE = 1 AND tab3.value_2 = tab2.value_2
  •     JOIN table4 tab4 ON tab3.client = tab4.client AND tab3.value_3 = tab4.tab4_value
  •     JOIN table5 tab5 ON tab5.client = tab4.client AND tab5.art_id = tab4.art_id
  • <

    Database Tuning Advisor, Stored Procedure with datetime parameters, syntax error


    I receive these error(s) in the Tuning Log. I have several stored procedures and they all use a datetime parameter.

    E000    exec <some sproc name> ''2009-01-01 00:00:00:000'',''2010-09-30 00:00:00:000'',N'',N'',NULL,NULL,NULL,NULL,NULL,NULL         1    [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2009'.

    I am using SQL 2005. Any suggestions?

    Thanks, Reece

    Stored procedure to do defragment entire database

    Stored procedure to do defragment entire database and to keep the report in seperate table?

    MS SQL Server Stored Procedure - Writing on a table in another another database


    I am writing a stored procedure in a database for an app that will become part of a "shell" program that was written to accept an endless # of VB.Net apps.

    The stored procedure will look at data in a table on that database, but the end result is that I want to update a table that exisits in A DIFFERENT database on the same server. I think I am referencing the table itself correctly, (databasename).dbo.(tablename). The problem is that the SQL is not performing as it does if I manually run the SQL in the database I'm trying to update.

    Here is the SQL:





    /****** Object: StoredProcedure [dbo].[usp_ARReport] Script Date: 10/25/2010 14:43:20 ******/



    Custom DB Installation Issue While Run the Stored Procedure Script


    1. I have created Custom DB Installation by Installar Class
    2. I created Stored Procedure Script From DB
    3. Copyied Script in sql.txt
    4.Created Custom DB Setup and Executed, But I am getting SP script Execution issue, same script is working in QueryAnalyser Execution

    Here is My Execution code



    Sub ExecuteSql(ByVal DatabaseName As String, ByVal S

    Custom DB Installation Issue While Run the Stored Procedure Script


    1. I have created Custom DB Installation by Installar Class
    2. I created Stored Procedure Script From DB
    3. Copyied Script in sql.txt
    4.Created Custom DB Setup and Executed, But I am getting SP script Execution issue, same script is working in QueryAnalyser Execution

    Here is My Execution code



    Sub ExecuteSql(ByVal DatabaseName As String, ByVal S

    connect 2 different database in stored procedure over the internet



    i have two data base in two sql server instance and different computers and different places over the internet.

    I can create a stored procedure that does a select of a table from a database of a computer, and then insert the table in another database of another computer?



    after prepare and insert in the other computer, SERVER2.DATABASE2.dbo.TABLE2


    its possible?


    thanks in advanced

    transfer data from a database to another using a stored procedure


    I need to transfer data  from a database to another using a stored procedure in a C# application. I have some tables in the first database and I need to transfer data from them in others tables from the second database, not all columns but few of them.

    How should I do that?


    Thank you for your time!



    add a stored procedure from a .sql file to a database


    How can I add a stored procedure from a .sql file to a database?

    I have a file that contains the procedure. It has the .sql extension. I need to upload the stored procedure into a database in a C# application.


    Thank you

    ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
    Sql Server  SharePoint  Silverlight  Others  All   

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend