SQL Server 2012 :: Permissions For Executing Procedure Which Executes Other Procedures?
May 13, 2015
I have a stored procedure which executes about forty other stored procedures in several different databases. All of these other procedures truncate tables and insert new data into those tables selected from still other tables.
I want to run this top-level procedure using an account which can't do anything else.
Is there a simple way to give it all the permissions it needs without empowering it to do anything else?
View 0 Replies
ADVERTISEMENT
Aug 20, 2014
Is this possible, I am trying to execute a stored procedure depending on what parameter is selected, something like this????
Case
when field = 'value' then execute sp_procedure else execute sp_procedure_2 end
case
View 1 Replies
View Related
Jul 23, 2014
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
View 4 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
View 1 Replies
View Related
Jan 23, 2008
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies
View Related
Nov 26, 2014
I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.
[code=sql]
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.
Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?
If so, how to continue.
In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.
View 2 Replies
View Related
Aug 12, 2013
I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.
Create a new connection to SQL Server, then in the Connection Properties dialog, specify
Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"
but if I want to pass a parameter, I would normally do something like
SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()
but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
View 9 Replies
View Related
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
May 6, 2014
Only to a specific schema? Can this be done?
View 5 Replies
View Related
Apr 28, 2015
I have stored procedures. I have heard that it is a best practice to use stored procedures to encapsulate some SQL statements and then grant permissions to execute the stored procedure. But when I try this and use EXECUTE AS to test it out, the user in question gets errors about not having access to some of the underlying objects.
How does this best practice work? If I need to grant the user permissions to the underlying objects anyway, I am not sure why a stored procedure is considered best practice in this regard.
View 8 Replies
View Related
Feb 16, 2004
Hello,
I deaply need to know how to execute procedures from package in oracle, from sqlserver 2000 using linkserver.
Thank you very much,
Victor
DBA
View 2 Replies
View Related
Jul 18, 2007
I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.
I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.
123.sh sp_my_stored_procedure input_parm1 input_parm2
123.sh sp_different_stored_procedure input_parm1
This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.
How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.
Thanks, Jim
View 4 Replies
View Related
Nov 16, 2006
Hi
My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.
I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!
Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables? :(
many thanks
View 3 Replies
View Related
Jul 20, 2005
Hi,I have stored procedure (MS SQL Server 2000) which operateson around 600 000 rows (SELECT, UPDATE, INSERT)and executes in 5 minutes,when I put it in SQL transaction it slows down to more than 5 hours (!!)I have to admit that it is not problem with data locks (beside thatprocedurenothing else is executed on db),It is not also problem with that exact procedure, other proceduresalso slow down heavily when wrapped by SQL transactionvery very seldom stored procedure within transaction executescomparably long that its copy without transactionI guess it could be MS SQL Server 2000 configuration/tuning problem.Any ideas ?Chris
View 1 Replies
View Related
Feb 1, 2007
I have a SSIS package that contains a DTS 2000 package in it. The DTS 2000
package imports data into several tables from an ODBC data source. When I
execute the package through BIDS, no problems. Everything works great. I am
now trying to execute the SSIS package in my stored procedure & it gives me
the following error:
Error: 2007-01-30 11:54:24.06
Code: 0x00000000
Source: Populate IncrTables
Description: System.Runtime.InteropServices.COMException (0x80040427):
Execution was canceled by user.
at DTS.PackageClass.Execute()
at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
End Error
I did a search for this & found KB 904796. It had the exact error message
but I don't believe my packages uses 2000 metadata services. Just to be
safe, I reinstalled the backward compatibility features & the DTS 2000 tools
on the server. That still did not fix anything. I found another forum that
suggested loading the DTS 2000 package internally, which I did & it did not
fix anything. I am using a password for the protection level so that is not
causing my issue. Does anyone else have any suggestions as to what I might be
able to try?
SQL 2005 Dev Ed SP1 & post SP1 hotfixes installed
Win 2k3 server
Thanks!
John
View 3 Replies
View Related
Jun 25, 2015
What permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.
EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.
EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.
Is there any option other than giving sysadmin privilege on SQL?
View 0 Replies
View Related
May 8, 2008
Hi all,
I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).
Is it possible to change permissions of SQL Server system stored procedures?
Is it possible to change permissions of SQL Server DBCC commands?
Thanks,
Assaf
View 1 Replies
View Related
Feb 29, 2008
All-
Is there a way that I can embedd a call to a stored procedure into an existing INSERT section in a table adapter?
Say my objective is to call a stored procedure called personfill automatically RIGHT AFTER the TableAdapter inserts a row into the person table. One catch is that the stored procedure must be sent the value of unique identifier field person_id, which was created for the new person record automatically by the db. (If this is not possible to do, I might try using a TRIGGER in the person table.)
Below is the INSERT code of the TableAdapter. My guess is that if I could call a procedure, I would want to put the call between lines 12 and 13.
Your comments would be most appreciated!!!
-Kurt1 <InsertCommand>
2 <DbCommand CommandType="Text" ModifiedByUser="false">
3 <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText>
4 <Parameters>
5 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" />
6 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" />
7 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" />
8 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" />
9 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" />
10 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" />
11 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" />
12 </Parameters>
13 </DbCommand>
14 </InsertCommand>
15 <SelectCommand>
16 <DbCommand CommandType="Text" ModifiedByUser="true">
17
View 2 Replies
View Related
Apr 2, 2008
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance
ripern
-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS
DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '
SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END
View 12 Replies
View Related
Aug 8, 2001
Hi ALl
I need a quick solution for my sql server problem as follows.
Actually Earlier we have SQL Server 7, Every 3 hours we execute one
Procedure it takes only 15 seconds. Now we
Install SQL Server 2000 and Same Procedure Is taking 30 Minutes for
execution what could be the reason
I want to minimize the execution time. how can i ?
Please help me
Thanks in advance
Shan
View 1 Replies
View Related
Oct 4, 2014
how to use opendatasource to execute a stored procedure remotely in MySQL with parameters..I am using sp_configure to enable and disable Ad Hoc Distributed queries as below before and after the open data source statement
sp_configure show,1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
[code]...
View 0 Replies
View Related
Sep 24, 2014
I am building a bunch of packages on our new server and all was going well until I edited the project using the client tools on my PC. I now receive the below error if I try to execute any of the packages on the server (all is still fine on the client). I have scoured the net but I don't seem to be able to come up with a solution. I have tried altering the folder & object permissions for my login (that created the project on the server and edited using the client) but I still get the error.
ERROR:
TITLE: Microsoft Visual Studio
------------------------------
Failed to start project
------------------------------
ADDITIONAL INFORMATION:
Exception deserializing the package "Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied.". (Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied. (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------
View 4 Replies
View Related
Jul 3, 2006
What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.
Thank you
View 1 Replies
View Related
Nov 2, 2006
I have the following sp..REATE Procedure SSSP_VehicleReg_Add @DECAL_NO varchar (9) , @NAME varchar (26) , @SSN varchar (9) , @DLN varchar (10) , @Address_1 varchar , @Address_2 varchar , @City varchar (50) , @State_Code varchar , @Zip_Code varchar (9) , @Country_Code varchar (4) , @PHONE varchar (12) , @VM varchar (10) , @MODEL varchar (10) , @VLPNUMBER varchar (10) , @VLPSTATE varchar (2) , @Date_Iss datetime, @Date_Exp datetime, @tran_Date datetime, @Op_Code varchar (20) , @ReturnStatus integer output, @ReturnMessage Varchar (50) output /* Adds a record to the Vehicle Registration table.*/ AS If @Decal_no = ' ' Begin Set @ReturnStatus = 0 Set @ReturnMessage = 'Record not added Key Fields are not valid' End Else If dbo.VehicleReg_Check (@Decal_No) = 0 Begin Insert INTO VehicleReg (Decal_NO, Name, SSN, DLN, Address_1, Address_2, City, State_Code, Zip_Code, Country_Code, Phone, VM, Model, VLPNUMBER, VLPSTATE, Date_Iss, Date_Exp, Tran_Date, Op_Code) Values (@Decal_NO, @Name, @SSN, @DLN, @Address_1, @Address_2, @City, @State_Code, @Zip_Code, @Country_Code, @Phone, @VM, @Model, @VLPNUMBER, @VLPSTATE, @Date_Iss, @Date_Exp, @Tran_Date, @Op_Code) Set @ReturnStatus = 1 Set @ReturnMessage = 'Record added' End Else Begin Set @ReturnStatus = 0 Set @ReturnMessage = 'Record not added' EndGO That is being called via...Dim SqlCmd As New SqlCommand("SSSP_VehicleReg_ADD", MYGF.cnMydb)Dim Current_Time As String = CStr(Now.Date + Now.TimeOfDay)SqlCmd.CommandType = CommandType.StoredProcedureSqlCmd.Parameters.Add("@Decal_No", SqlDbType.VarChar, 9, txtDecal.Text)SqlCmd.Parameters.Add("@NAME", SqlDbType.VarChar, 26, txtName.Text)SqlCmd.Parameters.Add("@SSN", SqlDbType.VarChar, 9, txtStuid.Text)SqlCmd.Parameters.Add("@DLN", SqlDbType.VarChar, 10, txtDln.Text)SqlCmd.Parameters.Add("@Address_1", SqlDbType.VarChar, 50, txtAddress_1.Text)SqlCmd.Parameters.Add("@Address_2", SqlDbType.VarChar, 50, txtAddress_2.Text)SqlCmd.Parameters.Add("@City", SqlDbType.VarChar, 50, txtCity.Text)SqlCmd.Parameters.Add("@State_Code", SqlDbType.VarChar, 2, ddlState.Text)SqlCmd.Parameters.Add("@Zip_Code", SqlDbType.VarChar, 9, txtZip.Text)SqlCmd.Parameters.Add("@Country_Code", SqlDbType.VarChar, 4, " ")SqlCmd.Parameters.Add("@PHONE", SqlDbType.VarChar, 12, txtPhone.Text)SqlCmd.Parameters.Add("@VM", SqlDbType.VarChar, 10, txtMake.Text)SqlCmd.Parameters.Add("@MODEL", SqlDbType.VarChar, 10, txtModel.Text)SqlCmd.Parameters.Add("@VLPNUMBER", SqlDbType.VarChar, 10, txtTagNo.Text)SqlCmd.Parameters.Add("@VLPSTATE", SqlDbType.VarChar, 2, " ")SqlCmd.Parameters.Add("@Date_Iss", SqlDbType.DateTime, 8, txtIssDate.Text)SqlCmd.Parameters.Add("@Date_Exp", SqlDbType.DateTime, 8, txtExpDate.Text)SqlCmd.Parameters.Add("@tran_Date", SqlDbType.DateTime, 8, Current_Time)SqlCmd.Parameters.Add("@Op_Code", SqlDbType.VarChar, 20, "xxxx")Dim ReturnStatus As New SqlParameter(("@ReturnStatus"), SqlDbType.Int, 1, ParameterDirection.Output)Dim ReturnMessage As New SqlParameter(("@ReturnMessage"), SqlDbType.VarChar, 50, ParameterDirection.Output)SqlCmd.Parameters.Add(ReturnStatus)SqlCmd.Parameters.Add(ReturnMessage)SqlCmd.ExecuteNonQuery()Which fails with the following error...System.Data.SqlClient.SqlException was unhandled by user code Class=16 ErrorCode=-2146232060 LineNumber=0 Message="Procedure 'SSSP_VehicleReg_Add' expects parameter '@DECAL_NO', which was not supplied." Number=201 Procedure="SSSP_VehicleReg_Add" Server= Source=".Net SqlClient Data Provider" State=6 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Vechicle_Registration.Move_to_database() in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 304 at Vechicle_Registration.BtnSave_Click(Object sender, EventArgs e) in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 34 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Any help would be appreciated
View 2 Replies
View Related
Nov 2, 2006
I changed "@Decal_No" to "@Decal_NO" same error
View 2 Replies
View Related
Jun 9, 2004
Hello,
I know that syscomments stores the stored procedures in chunks of 4000 characters. Some of my Stored proc's are >4000 characters.I use a temporary table to stock them after some modifications made to them.How can I execute them one by one, do i need to concatenate one whole stored proc's text in one single line(if yes, how can that be done?) Can variables be dynamically created, assigned values and then destroyed? Please help.
View 3 Replies
View Related
Jan 29, 2007
Hi All,I am running SQL2000. Can anyone tell me how I can use the contents ofa table as parameters for a stored procedure that resides on adifferent SQL server?I have a table:Customers:Cust_IDCust_NameCust_ContactCust_PhoneI need to execute a stored procedure and pass each of the above asparameters:@CustID, @CustName, @CustContact,@CustPhoneThe stored procedure also returns an error code.Thanks,Danny
View 3 Replies
View Related
Jul 3, 2007
I have a table with a list of stored procedures, I iterate using a Foreach loop which i iterate through an ADO.NET recordset that I created by executing a select SQL statement.
Now my problem is that in the foreach loop i have an execute sql task container which basically just executes these stored procedures using the 'exec ?' statement (i grab the name of each procedure into a variable). However, error trapping seems to be very difficult. I just figured out how to get a ReturnCode from the execute statement, but apparently I have to use it outside the Execute SQL task ....prolly use an IF condition to see if its not 0....but how do I go about doing this?
Also, I want to error trap if a stored procedure doesnt execute because of some error, how could i possibly handle an error like that? I dont want the foreach iteration to stop on an error because a stored procedure wasnt called either, but currently it does stop the entire package.
Thoughts anyone?
View 4 Replies
View Related
Aug 15, 2007
I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?
View 1 Replies
View Related
Jan 2, 2015
I've managed to get my Instance connected to the internet and I can query it using PHP and SQL, I can also look at views with no problem.
I have it working as an "ADODB.Connection" and like I said it connects and I can query data and display results.
Now I have coded a Stored Proc "GetMonthDays" in Sql Server:
Which returns days 1 through xxx in a given month and also returns the Day name eg... Sat for each date
2014-01-01 Thurs
2014-01-02 Fri
etc...
It works perfectly and very fast so All cool with that side BUT... I want to be able to query the Database through a Stored Proc, I've spent all day trying to find a way to get this to work and I've hit a wall
This T-SQL returns what it needs to
Begin
EXEC dbo.qselGetMonthDays '2015-01-01'
End
View 3 Replies
View Related
Nov 28, 2007
Hey guys,
I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,
However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?
I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)
Hope to get some suggestions and advice from you guys,
Thank you very much in advance.
View 5 Replies
View Related
Apr 30, 2006
w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @Campaign, @Contact 20 times how would I pass in the values for those 20 diffrent versions?
View 6 Replies
View Related
