I need to find out what stored procedure was executed when I click on
a button in a third party application, is there a way to find this
information out in some sort of log?
Some kind of profiling to see what procedure was executed and with
what parameters.
I am building a stored procedure that changes based on the data that is available to the query. See below. The query fails on line 24, I have the line highlighted like this. Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
Hello Which is faster : to write a a big stored procedure with if conditions, or to separate them and call them using exec?? i.e: if @id=1 insert into ....else if @id=2 update...-----------------------orif @id=1 exec InsertProcedureelse if @id=2 exec UpdateProcedurePlease help
Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.
Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.
Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).
Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?
I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!
I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!
The ole db command calls a stored procedure with two input variables:
exec stp_updedgrsholds status, temp_cr_num
from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.
but nothing gets updated in the database.
If I call the stored procedure the following way
exec stp_updedgrsholds 'C', 87
It updates fine! I have tried a number of different way with @ symbols and assignment p_status = @status
I have this code in a stored procedure: DECLARE @SQLString VarChar(200) SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')' Exec (@SQLString) it works fine - with just one issue - I must grant select permission on the table. Is there a way to do this WITHOUT granting the select permissions?
My DTS package performs the following: 1. 4 transformations to transform data from Sybase tables A,B,C,D to the temp tables in MSSQL tmpA, tmpB,tmpC,tmpD 2. Next, I have a task to run 4 stored procedures to load the 4 tmp* tables to actual tables A,B,C,D (so the task is "exec spA, exec spB, exec spC, exec spD". 3. There are 9/26/280/10000 records in the tables A,B,C,D 4. each stored procedure basically checks whether the record in the tmp* tables exist in the actual table baesd on the primary key and then perform an insert/update.
The strange thing is: 1. All 9 records in the tmpA is loaded to A. Only 17 records from tmpB is loaded to B 2. The same codes "exec spA, exec spB, exec spC, exec spD", if copied to query analyzer, they all run to completion, ie all 10000+ records are loaded. so there is no pbm with the data. 3. If I "split" the task such that task1 loads A and task2 loads B,C,D (and task2 runs after task1) now again all data for A is loaded, but 25 records from B is loaded 4. I tried to catch the @@error in the stored procedure for the insert/update statements but there's no error. Most importantly, the stored procedures run fine in query analyzer
Is there some sort of timeout or buffer issue here that is causing this strange behaviour.
I Have a problem When I execute a stored procedure from query analyzer (Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters it take 3 sec.
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this: sqlCmd.CommandType = CommandType.Text sqlCmd.Parameters.Add(sqlPar) sqlCmd.ExecuteNonQuery()
With this sql command: "exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question: if it's not possible, how can I pass a Null value to stored procedure? This code: sqlPar = new SqlParameter("@id", SqlDbType.Int) sqlPar.Direction = ParameterDirection.Output cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull) cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error: BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this? Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet] GO /****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[usp_sysconf]
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom )
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
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
IF @@ERROR = 0 BEGIN -- Success. Commit the transaction. Commit Tran END ELSE Rollback Tran
return GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the "SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
======================================================================= Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet": --Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
=========================================================== I ran the above project in VB 2005 Express and I got the following 5 errors: 1. Name 'EXEC' is not declared (in Line 16 of Form1.vb) 2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb) 3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb) 4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb) 5. Expression expected (in Line 16 of Form1.vb) ============================================================ I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error. But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
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?