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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Store Procedure Debugging Problem.

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
I am trying to debug the store procedure in SQL server 2005 . I have windows 7 64-Bit on my client computer and server is running SQL server 2005 standard edition. I am trying to debug store procedures in visual studio 2005 .  I followed the below steps. 

I have followed all the articales however still i am unable to debug the store proc in my application . I did the following.

1: My local machnice user has administrator rights on my local machine and in SQL server it has sys admin rights. 

2: On my client machine i am running windwos 7 64-bit version , database server is on windows Server 2003 64-bit edition. 

3: I configured the remote debugger on windows server then i start the remote debugger 64 bit .

Now when i try to debug the store procedure i got the following error message.

Unable to start T-SQL Debugging. Could not connect to computer eicsrv01 . The Microsoft visual studio Remote Debugging

Monitor on the remote computer cannot connect to the local computer . A firewall may be preventing communication via 

More Related Resource Links

Help!, Problem with creating my store procedure.

I have two tables. Product_Table with ProductID as the primary key. My other table, ProductSKU_Table which has SKU as the primary key and ProductID as the foreign key. I am trying to create a store procedure to insert into both tables. How do I insert the primary key from the Product_Table into the foreign key of the ProductSKU_Table? This is what I have so far:ALTER PROCEDURE [dbo].[sp_addNewProduct] ( @productName varchar(50) = null, @shortDescription varchar(100) = null, @longDescription varchar(4000) = null, @active bit = null, @category int = null, @sku varchar(11), @skuDescription varchar(200) = null, @orginalPrice money = null, @sellingPrice money = null, @specialPrice money = null, @qtyOnHand int = null, @qtyAtSupplier int = null, @imagePath varchar(200) = null, @releaseDate datetime ) AS BEGIN DECLARE @lastIDInserted int INSERT INTO Product_Table(Name, ShortDescription, LongDescription, Active, CategoryId) VALUES(@productName, @shortDescription, @longDescription, @active, @category) SET @lastIDInserted = @@IDENTITY INSERT INTO ProductSKU_Table(ProductId, SKU, Description, OrginalPrice, SellingPrice, SpecialPrice, QtyOnHand, QtyAtSupplier, ReleaseDate, imagePath, CreatedDate, ModifiedDate) VALUES(@lastIDInserted, @sku, @skuDescription, @orginalPrice, @sellingPrice, @specialP

Insert into and Store procedure problem

Hello I am trying to create store procedure wich will insert data in temp table CREATE PROCEDURE GetDataForUpdate AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDataForUpdate_temp_tb end go WITH cte as (select Sp.[Item No_], Sp.[Starting Date], It.[No_], It.[Manufacturer Code], It.[Description], Sp.[Unit Price], row_number () over (partition by Sp.[Item No_] order by Sp.[Starting Date] desc) as rn from dbo.[Main-db$Sales Price] AS Sp JOIN dbo.[Main-db$Item] AS It ON Sp.[Item No_] = It.[No_] WHERE Sp.[Sales Code]='RETAIL' AND Sp.[Item No_] LIKE 'I%' ), cte2 as ( SELECT [Item No_],SUM(ISNULL(Quantity, 0)) AS Qty FROM [Main-db$Item Ledger Entry] WHERE [Item No_] LIKE 'I%' AND ( [Location Code] = 'WH-AB-#2' ) GROUP BY [Item No_] ) SELECT cte.[Manufacturer Code],cte.[Description], CONVERT(int, cte2.[Qty]) AS 'Qty',CONVERT(int, cte.[Unit Price]) AS 'Unit Price' INTO GetDataForUpdate_temp_tb FROM cte JOIN cte2 ON cte.[Item No_]=cte2.[Item No_] WHERE rn = 1 ORDER BY cte.[Item No_],[Starting Date] But after execute, lefts only this part of query in store procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetDataForUpdate] AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDa

Get an exception while debugging a Stored Procedure

Hi all,      I am using SQL Server 2008 Standard x64 edition on windows 7 desktop. When I try to debug my stored procedure I receive following message. Unable to Start T-SQL Debugging. Could not Attack to SQL SERVER process on 'ADMIN-PC'.Click Help for more Information   And same operation is perfomed sucessfully with same SQL Server user sa with admin windows user. When I receive this message I am using a Standard type windows account. But when I change the account type to Administrator , still this operation dont work. Can any one tell me what's wrong? Nilkanth Desai  

problem for using webservice (sql parameter stored Procedure)

i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

SSIS - Call a Store procedure

Can someone show me an example of how to call a store procedure that checks for empty fields in a SQL database table, by returning a value when fields are empty. then send an email to the adminstratore.

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

Store Procedure

How can i insert and delete a data with use of one store procedure ........... SP File:- ALTER PROCEDURE dbo.One @id int, @Name Varchar(50), @select char(1) AS begin  if @select ='i' insert into j (id,Name) values (@id ,@Name) end begin if @select ='d' delete from j  where id= @id end RETURN How can i called tis two query

Cursor problem with Stored Procedure and PHP

I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(

How I can to check if Store Procedure altered(been called )?

I have ado.net connection to SP               conn = new SqlConnection("Data Source=DY;Initial Catalog=DMSA;Integrated Security=True");                 conn.Open();                   // 1.  create a command object identifying                 //     the stored procedure                 SqlCommand cmd = new SqlCommand("dbo.UpdateCorporationFile", conn);                   // 2. set the command object so it knows                 //    to execute a stored procedure                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add(new SqlParameter("@count_create_C_W", DBNull.Value));        &nb

debugging stored procedure created in sql server 2008

Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

in need of sql 2005 e-book on store procedure

i need e-book on sql 2005 express on STORE PROCEDURE TUTORIAL including how to use IF AND ELSE STATEMENT in SQL

debugging stored procedure created in sql server 2008

Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

Create store Procedure for select Table by Passing Table Name In Parametere

I want to create a Procudure to Select all Information in Table Like (select * from TableName) but Condition is I do not want to Mention The Fix Table Name I will send the Table Name In a parameter from My Vb.net Project like This (select * from @Parameter ) and Then The Select Procudure work with on That Table. It will be change depend on my vb.net reqierment I already Created a Procudure But it Does not working. It is in below. alter proc AllSelect (@Table varchar (30)) as DECLARE @sql nvarchar(MAX) SET @sql = ' Select * from '+@Table+' ' If possible Please let me know also Update and Delete proc by same way. with Regards Suman Bangladesh

Problem in debugging a class library


When I try to debug a class library I am getting an warning message "Unable to automatically step into the server. Connecting to the server machine 'xxxx.xxx' failed. The debugger cannot connect to the remote computer. This may be because the remote computer does not exist or firewall may be preventing communication to the remote computer. Please see help for assistance."

After getting this message my application shows the result set from the web service. I am not trying to debug the web service application, but trying to debug my code which is in another project in the same solution. Also I have added the project reference from my test application not the dll reference. I've checked the windows firewall and added exception for port 80 also.

Any help in this regard will be greatly appreciated.




Problem with Debugging - Cannot communicate with the debug host process


I'm trying to run (with debug) an SSIS  project and getting the following error


"Failed to start project


Cannot communicate with the debug host process. The IDtsHost interface is not registered. (Microsoft.DataTransformationServices.VsIntegration)"


It does run if I run without debug.


Any ideas?

How to run a store procedure as background Job ?



I have scenario where i need to run my store procedure on specific time frame ? For instance i have a field in my table1 called count.

  • If the value of count is 1 for a specific row in table then rightaway the specific row will move to another table2.
  • If the value of count is 2 for a specific row in table1 then the specific row will move to another table2 after 10 min not right away.

i think for this some job should continous run in background, I have store procedure and i want to run that store procedure as background job in MSSQL continous. How can we do that or any other approch? please suggest me.


JP Sharma

How come the same store procedure call can display different results?


Hi all,

This is the sproc I am calling from my Asp.Net application:

ALTER PROCEDURE [Uds].[Dashboard_GetJobs2]
 @date as datetime = null
 Select * From Uds.RealtimeLogging
 Where (@date is null or [timeStamp] > @date)

If I ran from query analyzer I get the correct results:

 exec Uds.Dashboard_GetJobs2 '9/20/2010 11:01:23 PM'

However, if I ran the Asp.Net page, ALL records are showed. Looking at the SQL Profiler I see the following call:
 exec sp_executesql N'exec Uds.Dashboard_GetJobs2',N'@date nvarchar(21)',@date=N'9/20/2010 11:01:23 PM'

And if I ran the statement above in query analyzer it WILL return ALL records. Why????

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