How To Find Dependent/Referenced Objects Of A Stored Procedure?
Jan 31, 2007
Hi Frens,
Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?
I'd need to generate multiple recordsets within a MS SQL stored procedure. The thing is these recordsets depend on each previous one (the first one obviously has parameters). How can I make this work? I can't create views with SPs. Thank you very, very much.
Hi i am trying to get the following result: CALCULATE THE CURRENT NUMBER OF SHARES on base of number of shares bought and number of shares sold
so the logic is int total if transactiontype is "buy", total = total + amount bought if transactiontype is sell, total = total-amount sold plus must be grouped by share
base data are stored in 1 table as share name transaction type share amount tesco buy 200 tesco sell 50
sounds rather easy does it not? well sql syntax must be very foreign to me as i do not manage it I tried a few options: 1 to write a database function as above and then call it from sproc but i need to use group by share name or id so it calculates for each share and it will not let me to use function in select with group by 2 i tried to use if within sproc but i get complain incorrect syntax (I use MSDE database accessed via Visual) - I think it suppose to be possible but it throws me out with the most simple if or = is there some special syntax? 3 I created 2 views: 1 view: all sold shares, 2nd view: all bought shares and their bought/sold quantities as in select share name, sum(sharequantity) where transaction = sell from table group by share name and the same for purchases then I did union of these 2 views with boughtquantity - soldquantity what worked but it only showed me shares which were sold, if share was never sold it was not shown I guess due to union so I am still stuck!
Hi There, Here I have small problem with default and rule. After create rule or default then we will bind that to any table. I bounded that rule to some of tables.If i want see the list of objects dependent on this rule or default how to see.I know sp_depends stored procedure will show the all dependent objetcs but i could not get through that.I found in help it says sp_depends works for all objects in the database like table,view and so on.But default and rule also objects i could not get it.Please let me know on this if you can give this answer as early as possible. I am very thanks to you.Please don't specify SQL-DMO Listboundedcolumns function.....
While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?
Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.
Hi All, I am trying my hand at SQL CLR. I want to know one thing which I'm not able to get from anywhere. Suppose I've a C# class like Student. I create an object of this class and push its values into database. Instead of pushing individual property values to database I want to pass the Student object itself or maybe an array or list etc. Kindly help me if it is possible. Regards
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
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
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
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.
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
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!
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'.
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
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
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?
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?
Im getting this error and i have no idea what it means.
----------------------------------------------------------------------------------------------------------------------------------------- Server Error in '/menu' Application.
Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I created new database in sql server 2005 dev. edition and use aspnet_regsql to configure my database to store information for ASP.NET membership.Than i created simple asp.net login application and that work fine on my local machine but when i deploy it on host server than i got this message : Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. please help.
create trigger test3 on authors after update as exec master.dbo.xp_sendmail "joebob@yahoo.com","Hello"
I then run this script in QA:
update authors set state = 'CA' where state = 'KS'
and receive this error message: Could not find stored procedure 'xp_sendmail'. The statement has been terminated.
What do I need to do ? I realize that xp_sendmail is in the master database, but want a trigger fired to joebob@yahoo.com anytime an update script is run against the authors table in the pubs database. I've set all the executable rights....what else do I need to do ?
After designing the database and completing the Java class to enter data into the database, I decided to take it home to work on the other pages for this application. (The data was transferring to the database with no problem at work). I restored the database and brought it home with me to setup it up on my pc at home. I setup the database at home and was able to load the driver and connect to the database but for some reason it can't find the stored procedure. The stored procedure is in the database but for some reason I keep getting this message -
SQL error.java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'spc_AddInformation'.
I've worked all day to find the problem but no luck. Can somebody let me know what the problem could be? It's not the code in the Java class because I’m connecting to the database and the stored procedure name is correct in the code. What could be the problem? Thank You.
Hi, I'm trying to generate a snapshot of a database (running on SQL 2005 SP1) prior to starting transactional replication to an SQL 7.0 server. After generating the snapshot, the agent fails with the error message 'could not find stored procedure 'dbo.sp_MSdropfkreferencingarticle' and suggests lookin at error number 2812 (which offers no help).
I was convinced this worked prior to installing SP1, but that testing was done some time ago....
Hi everybody, I created several stored procedure in a local sql server 2005 express database, now when I call/execute them in the asp.net 2.0 web page, it returns an error message of "Cannot find the stored procedure '<proc_name>'", this is funny, when I execute the commandin a sql query window "exec sp_GetAllArticles", it also returns cannot find stored procedure, but when I execute the "Use <DBName>" and thenexecute the statement, it works. The sql connection used in codes is ok. So, what I have missed to set or where I made mistake?Code:Inside web.config, <add name="Connectstring" connectionString="Data Source=DENSIASQLExpress;Initial Catalog=DbName;Integrated Security=True" providerName="System.Data.SqlClient" /> inside Load event, DAL.ConnectionString = ConfigurationManager.ConnectionStrings["Connectstring"].ConnectionString; private void DisplayLatestResources() { DAL.CreateCommand("sp_GetLatestFiveTrainingResources"); DAL.ExecuteReader(); SqlDataReader dr = DAL.DataReader; ..... } Try supplying with user id and password, same result...also try using aspnet_regsql.exe, same result also... Thanks in advanced. den2005
Hi, I am developing an .net application and for this application I am trying to locate System Procedures by tracing. In my trace file I find stored procedures with msdb.dbo prefix. I have been searching it in the master db and other db, but cldnt locate it. Can anyone let me know where I can find it?
I have developed an asp.net 2.0 web application that uses sql2000 and with storded procedures.Everything has been working great untill i yesterday found that someone have put in new stored proceduresin the database. The procedures starts with "dbo.aspnet_" like dbo.aspnet_CheckSchemaVersion.Because i din't create them and i surtenly not call them from the application, i deleted them all. Now when i try to use my web application nothing works. The app, somehow calls the procedure and becuase they are deletedan error message is thrown like: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. But the strange thing is that the problem only accure when i publish the site on the web server. if i would publish it to a local folder on my desktop ore just run in debug mode against the database there are no problems. I don't want to use the aspnet procedures that probebly comes from aspnet_regsql.exe and that im not calling from the code but reacts somehow, I just want it to tun as it was designed to do.And if i need them, can someone explain why? please help :)
This is my first forray into writing stored procedures and like the manual said created one in SQL Server 2005 parsed or tested it, executed it, tested it again succesfully,need to modify it but can't find it under stored procedures file where I initially right clicked and selected New Stored Procedure. Any idea where it went?
