DB Design :: How To Find Stored Procedure Which Executed Event
Aug 21, 2015
I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of this stored procedure.
DECLARE @path NVARCHAR(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
[code]...
Is there a way that I can find which stored procedure or event dropped this stored procedure?
View 4 Replies
ADVERTISEMENT
Jul 22, 2015
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
View 3 Replies
View Related
Oct 6, 2015
Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.
View 2 Replies
View Related
May 26, 2008
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
View 4 Replies
View Related
May 29, 2002
Hi,
Is there any system tables or something , which one can query to get when a specific procedure was last run.
Thanks for you help.
View 1 Replies
View Related
Jun 19, 2008
hi,
i want log the query executed in stored procedure ..
any way of doing it in sql server 2000
regards.
View 1 Replies
View Related
Dec 2, 2005
Can a stored procedure be executed from within a select statement?
Given a store procedure named: sp_proc
I wish to do something like this:
For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
View 2 Replies
View Related
Jul 23, 2005
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies
View Related
Aug 16, 2006
In SQL 2005 I have a stored procedure as below:@sub_no smallint OUTPUTBEGINBEGIN TRANSACTIONINSERT...INTOSET @user_no = (SELECT ...... FROM ....WHERE sub_no = @sub_no)INSERT...INTOEXE another_stored_procedure (it includes also BEGIN...COMMIT)EXE another_stored_procedure (it includes also BEGIN...COMMIT)SET @sub_no = .......COMMIT TRANSACTIONWhen Visual Studio (ASP.NET 2005) is open and I run the program,procedure is executed once without any problem. If I publish theproject and put files on another server (or even use the publishedfiles from my machine) I have an error because stored procedure isexecuted twice. @sub_no is used as input/output parameter.I followed/trace the steps in procedure and it seems that procedure isexecuted once with correct value of @sub_no. The second time procedureis executed, the value that it was assigned before COMMIT is used,which gives an error because the INSERT values have NULL values.In ASP.NET I call the store procedure once.What could be the reason ?Thanks a lot for any help.
View 2 Replies
View Related
Nov 5, 2015
I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?
View 2 Replies
View Related
Nov 29, 2006
Hi everyone,
I wonder how one can see or save the real sql statement that was executed by some stored procedure (including the one that used supplied parameters)?
Just need that for debugging purposes...
thanks!
View 1 Replies
View Related
Dec 3, 2006
I need to check the databes on the server side every 3 days and delete old data.
I am using SQL Express.
View 5 Replies
View Related
Feb 14, 2008
Hi all,
I have a Matrix report (SQL 2005 SP2) which uses a stored procedure to retrieve the result set. When I preview or view the report on IE, the SP gets executed thrice (instead of just once)? Anybody know about this? Is this is a bug in Rep Srvcs?
It is slowing down the report considerably.
Any help would be appreciated!
Thanks
SS
- I used SQL Profiler SP-Starting event to track this
View 2 Replies
View Related
Apr 27, 2007
With a Gridview Control, I set the SqlDataSource to be a stored procedure in a Sql Sever database.
Using the wizzard to configure the datasource, the test returns lots of rows. After completing the wizzard, the gridview control does not show the column names in the VS2005 designer. For the gridview column headers the values are Databound Col0, Databound Col1, Databound Col2, ....) This tells me I have a problem.
I tried the same thing with a simpler stored procedure. This test stored procedure does not call anything else, takes several input parameters, returns rows. The column names show in the gridview control as expected.
So I am trying to figure out why the first case of gridview with sqldatasource is not working and the second case works . The stored procedure that is not working with my gridview calls multiple inner stored procedures and has #TEMP tables. My complex stored procedure ends with Select * from #Temp.
Could the calling of other inner stored procedures and use of #temp tables contribute to the problem? If yes then what is the strategy for using a gridview and loading it with the data that the complex stored procedure returns?
View 8 Replies
View Related
Nov 20, 2015
event handlers was not executed when my package get fails it will go to directly to on error...not executing on preexecute. but it was working fine previously...i haven't change anything i have run it again...got this issue...
is there any thing wrong?
View 3 Replies
View Related
Oct 6, 2004
Is it possible to create an event handler in a VB.net application to run whenever a Stored Procedure is run.
My application has a scheduled task which is created and scheduled by users of the application, whenever this scheduled task is run I would like it to contact the application to kick off a sequence of tasks. I would appreciate if anybody could point me in the right direction.
View 4 Replies
View Related
Aug 9, 2006
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
View 10 Replies
View Related
Nov 8, 2007
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
View 7 Replies
View Related
Feb 4, 2008
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies
View Related
Apr 21, 2008
Hey all,
I've got a design problem I'm trying to figure out. I'm designing a search function for a table, and I'm trying to figure out the best way to design my stored procedures. The search form allows for 72 different possibilites of returns: 3 booleans that can be combined (1 of each, 2 of 3, or all 3), and 3 dropdown lists that have 2, 3, and 4 options respectively that also can be an "any" (no preference) value.
Should I write a stored procedure to handle each of this situations (and the corresponding methods in the DAL and BLL providers)? Or would it be easier to use a generic GetItem procedure and use rules in the business layer to sort it all out (which I'm not entirely sure how to go about doing, much less how to display in a gridview from said data)?
Any suggestions, articles, or ideas that'll help get me going on the right path would be appreciated.
Thanks!
View 10 Replies
View Related
Jul 19, 2000
I have the following design as my starting point.
4 sql tables.
I will be doing a variety of searches against these 4 tables (525 different searches to be exact).
Which search is performed is based on table requested and the search criteria fields passed. One of the four tables will always be searched and one of the other three as well.
I have a driver stored procedure that determines which of the 3 tables to read and calls separate subordinate stored procedures for the table selected. Then it calls a stored procedure to select from the table that is always read.
I am building temporary tables to hold the results from the two tables read in each pass. Then the driver will return the results to the client.
My question is this, is there a more elegant way to determine which one of my 525 queries need to be executed than a very ugly large if/then/else, checking all of the possible combinations of parameters passed in each subordinate stored procedure?
Is there some way to create dynamic sql select statements based on the parameters passed to the subordinate stored procedures that have actual values (not null)?
Any insight would be a big help.
Judy
View 3 Replies
View Related
Jun 11, 2007
Hi, all.
I have a question about how you would design this. I have to return a list of projects in a stored procedure with a statement like this:
SELECT ProjectID, ProjectName FROM PROJECTS;
This populates a DropDownList with all possible projects. When the user selects one, I need to find out more information about that project, like this:
SELECT a.ProjectID, a.ProjectName, b.OtherStuff FROM PROJECTS a INNER JOIN OTHERSTUFF b on a.ProjectID = b.ProjectID;
In the old ASP world, we just filled the ProjectID parameter with NULL if we wanted to return all results, and the stored procedure was set up to dynamically return results based on whether that parameter was NULL or not.
I'm looking for a more elegant way to do this. I can use two stored procedures, but I'm not sure what to name them. Obviously, I could call one 'SelectProjects' and the other 'SelectProject', but that just doesn't seem elegant enough. Anyone have any other ideas?
View 4 Replies
View Related
Aug 12, 2015
I want to create a stored procedure for displaying working Saturdays. For example... last working Saturday 08/08/2015 (dd/mm/yyyy) is working day, I want to get next working Saturday as 22/08/2015 (dd/mm/yyyy). Likewise, I want to show for particular year
View 4 Replies
View Related
Mar 11, 2005
I developed a ASP.net web application with a MSDE database backend on my laptop(vs.net 2003 XP Pro), then I transferred the website onto a server(Windows Server 2003) and generated a SQL Server 2000 database from the scripts I exported from MSDE(web administrator). The problem I am having is that it can't find any stored procedures. I keep getting errors when logging on, 'Could not find stored procedure "_myProc" '.
Any one with any clues what might be the problem?
Yes I have changed the connection strings.
Thanks in advance
P
View 2 Replies
View Related
Jan 5, 2006
I use the following code in ASP.NET 2.0 to update the database:
Dim myConnection As New SqlClient.SqlConnection("server=local);uid=sa;pwd=xxx;database=Northwind")Dim myCommand As New SqlClient.SqlCommand("dbo.spTralen_customer_save 'CACTU'", myConnection)myCommand.CommandType = CommandType.StoredProceduremyConnection.Open()myCommand.ExecuteReader(CommandBehavior.CloseConnection)
I get the following error message: "Could not find stored procedure..."
The sp is in the database and dbo is the owner of the sp and I'm logged in as sa as you can see above. It doesn't matter if I remove the "dbo." from the sql command, it still doesn't work. If I remove the parameter value 'CACTU' above I get an error message saying that the sp expects the parameter so the sp is obviously in the database.
Can someone please help me as soon as possible!// Gato Gris
View 2 Replies
View Related
Apr 29, 2008
Not sure if this question belongs here or in a .NET forum.
But Im going to give it a shoot. The problem is that Im getting the following error: "Could not find stored procedure 'xxx'".
Ive never used stored procedures before, so what I am wondering is there anything basic that Im forgetting?
I have this simple stored Procedure:
ALTER PROCEDURE Person_info
@FirstN varchar(128),
@LastN varchar(128)
AS
SELECT FName, LName
FROM Person
WHERE (FName = @FirstN) AND (LName = @LastN)
and each time I call this procedure I get the prior stated error.
returnValue = sqlcmd.ExecuteReader(); //crashes when this line executes.
Ive found some people talking about this and it might be caused due to the "initial catalog=<database name>" in the connection string is missing. I tried that but didnt work.
View 18 Replies
View Related
Sep 13, 2005
Hello,Our SQL machine is getting bogged down by some sort of stored procedureand I am trying to find which one. My SQLdiagnostic software (by Idera)that monitors our SQL server, says that these commands are executingand taking upwards of 30 minutes to run. This is new and unexpected.The commands are:exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =25, @Pm2 = 2, @Pm3 = 1exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =105, @Pm2 = 2, @Pm3 = 1exec sp_executesql @Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @Pm1 =57, @Pm2 = 2, @Pm3 = 1I am getting pages of these and yesterday the are taking upto 30minutes to run (currently they are taking 1-2 minutes to complete w/opeople on the machine).We are not getting much help from our software vendor (of ouradmissions software, not Idera) on this matter. I have sa access to theSQL machine and I can see the pages and pages of stored procedures, butI don't know what the above is running. I want to find the storedprocedure that keeps getting executed. Is the @Pm0 = an encryptedentry?Any advice I would appreciate.ThanksRob Camarda
View 2 Replies
View Related
Jul 20, 2005
I have an Access 2000 database connected to a SQL Server and am tryingto execute my first stored procedure. I created the stored procedureand verified that it works, but when I try to execute it from Access:cnn.Execute("sp_IPT")it says: 'Could not find stored procedure 'sp_IPT'Any ideas?Norman B. ScheininF-22 Applications DevelopmentM/S 4E-09(206) 655-7236Join Bytes!
View 1 Replies
View Related
Aug 22, 2007
I have this error (Merge Replication):
The row was inserted at 'DISTRIBUTION.db_main' but could not
be inserted at 'subscriber.db_test'. Could not find stored
procedure 'bp_ins_8284C429C5514F08046769C0F2D24607'.
How can I solve this problem?
Thanks.
View 11 Replies
View Related
Dec 14, 2006
I am not sure were to start on how to fix this. I am not having any problems connecting to the mssql 2000 server. My problem is Could not find stored procedure 'CMRC_ShoppingCartAddItem'. The user has exec permissions on that procedure. This is a custom VB.net 2005 web application.. Does any one have any ideas on how to check whats wrong?
The sub that calls the procedure:
Public Sub AddItem(ByVal cartID As String, ByVal productID As String, ByVal Company As String, ByVal quantity As Integer) ' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection("Data Source=MANDB02;Initial Catalog=db_name;UId=nobigaccess;Password=$$$$$$$") Dim myCommand As SqlCommand = New SqlCommand("CMRC_ShoppingCartAddItem", myConnection) ' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterProductID As SqlParameter = New SqlParameter("@ProductID", SqlDbType.NVarChar, 15) parameterProductID.Value = productID myCommand.Parameters.Add(parameterProductID) Dim parameterCompany As SqlParameter = New SqlParameter("@Company", SqlDbType.NVarChar, 8) parameterCompany.Value = Company myCommand.Parameters.Add(parameterCompany) Dim parameterCartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.NVarChar, 50) parameterCartID.Value = cartID myCommand.Parameters.Add(parameterCartID) Dim parameterQuantity As SqlParameter = New SqlParameter("@Quantity", SqlDbType.Int, 4) parameterQuantity.Value = quantity myCommand.Parameters.Add(parameterQuantity) ' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub This is what the procedure looks like in sql: CREATE Procedure CMRC_ShoppingCartAddItem( @CartID nvarchar(50), @ProductID nvarchar(23), @Company nvarchar(8), @Quantity int
)
AsDECLARE @CountItems intSELECT
@CountItems = Count(ProductID)FROM
CMRC_ShoppingCart
WHERE
ProductID = @ProductID AND Company = @Company AND
CartID = @CartID
IF @CountItems > 0 /* There are items - update the current quantity */
UPDATE
CMRC_ShoppingCart
SET
Quantity = (@Quantity + CMRC_ShoppingCart.Quantity)
WHERE
ProductID = @ProductID AND Company = @Company AND
CartID = @CartID
ELSE /* New entry for this Cart. Add a new record */
INSERT INTO CMRC_ShoppingCart ( CartID, Quantity, ProductID, Company ) VALUES
(
@CartID,
@Quantity,
@ProductID,
@Company
)
GO
View 3 Replies
View Related
Jan 12, 2007
can anyone please help me with my poll application? whenever i run it, i will comes out with this error "Could not find stored procedure 'GetActivePoll'". i've got stored procedure in my database with the name GetActivePoll', how come it cannot find the stored procedured? below are some images and codes i've attached with. 1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="poll.aspx.vb" Inherits="Polls_poll" title="Fanzine if Liverpool FC" %>
2 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
3 <div style="text-align: left">
4 <span style="font-size: 30px; color: #3333ff; font-family: Verdana"><strong><span
5 style="color: #000000">Please take a vote...</span><br />
6 </strong></span>
7 </div>
8 <div style="text-align: left">
9 <br />
10 <table width="100%" align="center">
11 <tr>
12 <td style="width: 100px; border-top: thin solid; height: 20px;">
13 <asp:Label ID="lblPollQuestion" runat="server" Font-Bold="True" Font-Names="Verdana"
14 Font-Size="10pt" Text="Poll Question" Width="500px"></asp:Label></td>
15 </tr>
16 <tr>
17 <td style="width: 100px">
18 <asp:RadioButtonList ID="rdoPollOptionList" runat="server" Font-Names="Verdana" Font-Size="9pt" Width="500px" DataSourceID="SqlDataSource1" DataTextField="PK_PollId" DataValueField="PK_PollId">
19 </asp:RadioButtonList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Poll.mdf;Integrated Security=True;User Instance=True"
20 ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Polls]"></asp:SqlDataSource>
21 </td>
22 </tr>
23 <tr>
24 <td style="width: 100px; text-align: left">
25 <asp:Button ID="btnVote" runat="server" Text="Vote" Width="71px" BackColor="Silver" BorderColor="Silver" BorderStyle="Solid" Font-Bold="True" ForeColor="White" /><br />
26 <br />
27 <asp:Label ID="lblError" runat="server" Font-Names="Verdana" Font-Size="Smaller"
28 ForeColor="Red" Text="You cannot vote more than once..." Visible="False" Width="500px"></asp:Label></td>
29 </tr>
30 </table>
31 </div>
32 </asp:Content>
33
1 Imports System.Data
2 Imports System.Data.SqlClient
3 Partial Class Polls_poll
4 Inherits System.Web.UI.Page
5 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
6 If Not IsPostBack Then
7 DisplayPoll()
8 End If
9 End Sub
10 Private Sub DisplayPoll()
11 Try
12 Dim ds As DataSet = getActivePoll()
13
14 lblPollQuestion.Text = ds.Tables(0).Rows(0)("Question")
15
16 Dim i As Integer = 0
17 For Each dr As DataRow In ds.Tables(1).Rows
18 rdoPollOptionList.Items.Add(dr("Answer"))
19 rdoPollOptionList.Items(i).Value = dr("PK_OptionId")
20 rdoPollOptionList.SelectedIndex = 0
21
22 i = i + 1
23 Next
24 Catch ex As Exception
25 Throw ex
26 End Try
27 End Sub
28 Private Function getActivePoll() As DataSet
29 Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
30 Dim sqlConn As New SqlConnection(strConnString)
31
32 sqlConn.Open()
33 Dim sqlCmd As New SqlCommand()
34
35 sqlCmd.CommandText = "GetActivePoll"
36 sqlCmd.CommandType = Data.CommandType.StoredProcedure
37 sqlCmd.Connection = sqlConn
38
39 Dim ds As New DataSet
40 Dim da As New SqlDataAdapter(sqlCmd)
41
42 da.Fill(ds)
43
44 sqlConn.Close()
45
46 Return ds
47 End Function
48 Protected Sub btnVote_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVote.Click
49 If Response.Cookies("Voted") Is Nothing Then
50 Response.Cookies("Voted").Value = "Voted"
51 Response.Cookies("Voted").Expires = DateTime.Now.AddDays(1)
52
53 lblError.Visible = False
54
55 RecordVote()
56 Else
57 lblError.Visible = True
58 End If
59 End Sub
60 Private Sub RecordVote()
61 Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
62 Dim sqlConn As New SqlConnection(strConnString)
63
64 sqlConn.Open()
65 Dim sqlCmd As New SqlCommand()
66
67 sqlCmd.CommandText = "IncrementVote"
68 sqlCmd.CommandType = Data.CommandType.StoredProcedure
69 sqlCmd.Connection = sqlConn
70
71 Dim sqlParamQuestion As New SqlParameter("@i_OptionId", Data.SqlDbType.Int)
72
73 sqlParamQuestion.Value = rdoPollOptionList.SelectedValue
74
75 sqlCmd.Parameters.Add(sqlParamQuestion)
76
77 sqlCmd.ExecuteNonQuery()
78
79 sqlConn.Close()
80 End Sub
81 End Class
View 2 Replies
View Related
Jul 21, 2007
I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?
Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?
View 3 Replies
View Related
Feb 27, 2008
I am trying to execute on my current database the following SQL: EXEC sp_grep 'CurrentState'
But I keep getting the error that sp_grep does not exist. I thought the above was a standard was of grepping a SQL Server 2005 database.
Does sp_grep really not exist in SQL Server 2005?
Thanks
View 6 Replies
View Related
