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


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Asad Ali
Fauzul Azmi
Post New Web Links

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

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :WPF
 
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


View Complete Post


More Related Resource Links

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

 

 


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 check username and password store in database

  
hi i have login page. how check username and password store in database . plz reply with code.i did it by form authentication b ut it not work.

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 debug store procedure in SQL2008?

  

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


Slow backup via stored procedure that is called from vb.net

  

Ok I have a database that is 3.37 GB in size.  Gets backed up via a stored procedure in a vb.net production run.  It takes close to 3 minutes and seems like it should be faster. Below is the code that calls the procedure and the procedure itself.  Is the problem with one of these 2 or is the time normal given the DB size?

Vb code

Dim backupCmd As SqlCommand = New SqlCommand("BackupPltdataNewProc", sqlconn)
backupCmd.CommandTimeout = 240
backupCmd.CommandType = CommandType.StoredProcedure
sqlconn.Open()
Try
backupCmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
sqlconn.Close()

the procedure

USE [Pltdata]
GO
/****** Object:  StoredProcedure [dbo].[BackupPltDataNewProc]    Script Date: 09/23/2010 13:14:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[BackupPltDataNewProc]
AS
DECLARE @BackupFileName varchar(100)
SET @BackupFileName = 'F:\Backup

Slow backup via stored procedure that is called from vb.net

  

Ok I have a database that is 3.37 GB in size.  Gets backed up via a stored procedure in a vb.net production run.  It takes close to 3 minutes and seems like it should be faster. Below is the code that calls the procedure and the procedure itself.  Is the problem with one of these 2 or is the time normal given the DB size?

Vb code

Dim backupCmd As SqlCommand = New SqlCommand("BackupPltdataNewProc", sqlconn)
backupCmd.CommandTimeout = 240
backupCmd.CommandType = CommandType.StoredProcedure
sqlconn.Open()
Try
backupCmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
sqlconn.Close()

the procedure

USE [Pltdata]
GO
/****** Object:  StoredProcedure [dbo].[BackupPltDataNewProc]    Script Date: 09/23/2010 13:14:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[BackupPltDataNewProc]
AS
DECLARE @BackupFileName varchar(100)
SET @BackupFileName = 'F:\Backup

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


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