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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

how to debug store procedure in SQL2008?

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server
 

I remeber I can use analyzer to debug sql in 2000, and it seems it does not have the analyzer in sql 2008 any more. Here is what I do:

 

1. select the SP, then script SP to->execute to->new window.

2. click debug.

 

then step into the SP line by line.

 

Is this the correct way to do it? one problem is: I will have to script the input into the SP directly. Is it possible to let sql pop the window and ask for input like analyzer does?

 

 

thanks




View Complete Post


More Related Resource Links

Debug a Store procedure

  

Hi,

I have Vs 2005 and sql management studio 2005 standard editions. I want to debug the store procedures and want to see the values inside the parameters . Can you please let me know how to do this.

 

Best Regards,

 


Tabbasi

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

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

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

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

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

How to run a store procedure as background Job ?

  

Hi,

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.

Thanks
--Jai


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
AS
Begin
 Select * From Uds.RealtimeLogging
 Where (@date is null or [timeStamp] > @date)
End

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


JCD

How to check whether a column is been referred in store procedure or not

  

HI Team,

I am about to rename a column in one of the table. How can I check the dependency for the column specially in store procedure? I am using SQL server 2008.

Thanks in advance

 

 


Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Value ....

  

My Store Prodecure.....

------------------------------------------------------------------------------------

ALTER PROC [dbo].[Polls_Polls_SaveAdmin]  
@UserID INT,
@CategoryID INT,
@TypeID INT,
@OptionID INT=NULL,
@Title nvarchar(4000),
@Description nvarchar(MAX),
@StartDate DATETIME,
@EndDate DATETIME,
@PollID INT,
@OptionA NVARCHAR(100),
@OptionB NVARCHAR(100),
@OptionC NVARCHAR(100),
@OptionD NVARCHAR(100)
AS
    DECLARE @SaveStatus INT
    DECLARE @SaveStatus1 INT
    SET @SaveStatus = 0
    SET @SaveStatus1=0

BEGIN
    IF @PollID>0
       BEGIN
        IF NOT EXISTS (SELECT POLLID FROM POLLS WHERE (CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) ) OR (CAST(CONVERT(VARCHAR, EndDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) AND PollID<>@PollID))
            BEGIN
           &

Decrypt the encrypted store procedure through the T-SQL programming in SQL Server 2005

  

HI ,

 

I have a encrypted store procedure in my production server .Right now I do not have the script now I want do some modification.

Please any one can help me to decrypt the store procedure it will be a great help for me.

 

For any suggestion thanks  in advance.


" Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
Click here to read my blog

Open a Batch File in Sql Store Procedure

  

Hi


I have to open a batch file automatically in sql. So i have used the below command.

EXECUTE master.dbo.xp_cmdshell 'D:\Databases\batch\123.bat'

Its worked for few times. After that its not working.

One time i have stopped before the process is complete.May be this is the issue.

Now its returns null value.


Can anyone help me how can i open the file in sql.


Thanks & Regards,

Visa.G


share data in two table using store procedure

  

i want to update one table columns using 2nd table value like this:


ALTER procedure [dbo].[INS_Profile_intotempfrommaster_delete ]
(    
@UserID int
)
As
Begin

select @OrgnisationLink=OrgnisationLink,@F_Name=F_Name,@L_Name=L_Name,@EmailID=EmailID,@EncryptedEmailID=EncryptedEmailID,@Address=Address,@City=City,@State=State,@Pincode=Pincode,@Designation=Designation,@P_Number=P_Number,@M_Number=M_Number,@OrgnisationProfile=OrgnisationProfile
from t_usermaster where UserID=@UserID


update t_usermaster_mirror
set OrgnisationLink=@OrgnisationLink,F_Name=@F_Name,L_Name=@L_Name,EmailID=@EmailID,EncryptedEmailID=@EncryptedEmailID,Address=@Address,City=@City,State=@State,Pincode=@Pincode,Designation=@Designation,P_Number=@P_Number,M_Number=@M_Number,OrgnisationProfile=@OrgnisationProfile

End


but i am getting error



Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'INS_Profile_intotempfrommaster_delete ' expects parameter '@OrgnisationLink', which was not supplied.


plz reply me how can i do this


How to increase performance of store procedure ?

  

Hi

I have written one store procedure for fetching huge data like this. there are nearly 2000 records. It is taking nealy 50 sec to execute. Could you tell me best method to fetch large no of

data ?


    ALTER PROCEDURE bulls_orbit.BuySellUpdateFetchData
    

     AS


    Select Id,Message,EndDate,RefId from tblBuySellUpdate Order By Id Desc


    RETURN


Categories: 
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