Hi,
Say I have a stored procedure which does two INSERT operation.
How can I check if the first INSERT succeeded in order to know if I
should carry on and do the second one ?
Regards
Is there a parameter allowing to verify successful execution of a stored procedure?
Something that can be run as the last line in a stored procedure to verify its success...?
Thanks!
More of a general SOAP service call question.
Does anybody have any experience/advice on how to ensure that SOAP service call success/failures are returned to the calling app?
Consider a client that calls a SOAP service during which the client goes down and is unable to receive the SOAP response, the work having been done by the service. Similarly, the SOAP service may perform the task but a failure in the return makes the client think the process failed.
What would be the best way to ensure that the client is notified to avoid the call having to be made again?
Are there middleware tools that can be used to provide a form of message queuing for SOAP service calls?
Thanks
I have an SSIS package that executes a stored procedure. In that stored procedure is a try/catch block. If the try isn't successful, it goes to the catch block which does a rollback. So when I execute the SSIS package, it tells me that the stored procedure was ran successfully because there essentianlly were no errors and everything ran fine, but in reality, everytime it goes into the catch block and does a rollback, I want the SSIS package to fail as well. How would I send back a failure to the SSIS package from the stored procedure?
View 13 Replies View RelatedI have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values
-------------------------------------------
PoId Podate Approved
22008-07-07 No
42007-05-05 No
52005-08-06 Yes
62006-07-07 Yes
Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No
3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in the Table which is specified in the table name column in Table B. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
Pls provide me with full stored procedure code.
It is very urgent.
C.R.P RAJAN
I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date
View 2 Replies View RelatedHi i am trying to insert the value of my Request.Querystring into my stored procedure, but i am having trouble with it, how would i insert the id as a parameter which is expected from the stored procedure this is what i have doen so far;string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand comm = new SqlCommand("stream_PersonnelDetails", conn);comm.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
Thank you
I am null in Transact-SQL
Hello , I ve made a SQM request I would provide it result via stored stored procedure how can I do this ?
this is the request
SELECT DISTINCT Account.Name AS exp, Campaign.New_FormationIdName
FROM Email INNER JOIN
CampaignActivity ON Email.RegardingObjectId = CampaignActivity.ActivityId INNER JOIN
Account ON Email.ToRecipients = Account.EMailAddress1 INNER JOIN
Campaign ON CampaignActivity.RegardingObjectId = Campaign.CampaignId CROSS JOIN
New_Formation CROSS JOIN
New_formationparticipation MINUS
SELECT New_exportateurIdName, New_formationIdName
FROM new_formationparticipation
-An other ponit: How can I add a parameter to this stored procedure?
Hi,
I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.
I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.
The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.
Any ideas on how I would do this?
I am tring to use a stored procedure to run a function POST some data through HTTP, but failed. Here are the codes I used. Would appreciate if anyone can help me about that. Thank you very much.
Database Stored Procedure:
CREATE PROCEDURE [dbo].[USP_HTTP_POST]
@URL NCHAR(255),
@CONTENT NCHAR(1024),
@Result bit OUTPUT
AS
BEGIN
/***** Call Function to POST *****/
set @Result = NULL
EXECUTE @Result = [dbo].[udfHttpPost]
@URL,
@Content
END
Database Function:
CREATE FUNCTION [dbo].[udfHttpPost]
(@Url [nchar](255),
@Content [nchar](1024))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServices].[SqlServices.HttpServices].[HttpPost]
VC# Assembly Code
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Security;
using System.Security.Permissions;
using System.IO;
using Microsoft.SqlServer.Server;
namespace SqlServices
{
public class HttpServices
{
[SqlFunction(DataAccess = DataAccessKind.None)]
public static bool HttpPost(string Url, string Content)
{
Url = Url.TrimEnd();
Content = Content.TrimEnd();
string rawOutput = "";
//Get Access Right to Web
WebPermission p = new WebPermission(NetworkAccess.Connect,Url);
p.Assert();
//Prepare the WebRequest
WebRequest Req = (WebRequest)WebRequest.Create(Url);
Req.Timeout = 60000;
Req.Method = "POST";
Req.ContentLength = Content.Length;
Req.ContentType = "application/x-www-form-urlencoded";
StreamWriter PostWriter = new StreamWriter(Req.GetRequestStream());
PostWriter.Write(Content);
PostWriter.Close();
WebResponse Resp = Req.GetResponse();
StreamReader sr = new StreamReader(Resp.GetResponseStream());
rawOutput = sr.ReadToEnd();
sr.Close();
return true;
}
}
}
System Exception:
A .NET Framework error occurred during execution of user defined routine or aggregate 'udfHttpPost':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Assert()
at SqlServices.HttpServices.HttpPost(String Url, String Content)
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------PoId Podate Approved
2 2008-07-07 No 4 2007-05-05 No 5 2005-08-06 Yes 6 2006-07-07 Yes
Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
Pls provide me with full stored procedure code.
It is very urgent.
Hi i have a stored procedure which deletes a row from a table.
Sometimes the proc will break the constraints for the table.
How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?
Thanks in advance.
Gary
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/
if NOT EXISTS (SELECT object_id('procedureName','p'))
CREATE PROCEDURE [dbo].[procedureName]
@location char(2)
AS
...Procedure...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p'))
SELECT 'FALSE'
ELSE
SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@location'.
Any ideas how to do this?
-D
I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure:
Code: ( text )
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies View RelatedI have a number of stored procedures that run one after the other. How do you code to get the success or failure so that some logic can be applied accordingly? I've heard of the TRY CATCH structure, but I new and have yet to use it. How many different ways can success or failure be handled in code?
View 4 Replies View RelatedI get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost
Servername: DED1774 (the machine name)
Servername: localhost/reportserver
Servername: DED1774/reportserver
Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Thanks
hello friends
i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can...
the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country....
i m using SQL server 7.0
i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries...
for this i m generating another table(name of another table is RESULT)...
how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....)
now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only
*** similarly when it checks the 100th record it compares it for 99 times
*** similarly when it checks the 10000th record it compares it for 9999 times
so here lies the problem....
when it checks the 100000th record it gets dammm slow...
what i have get till now is that i have checked.....>>>>>
5000 records in 4 mins....
25000 records in 22 mins....
and
100000 records in 20 hours....(means initially its faster but it gradually slows down)
************************************************** ************************
here i m giving the code for the procedure......
************************************************** *************************
CREATE PROCEDURE pro1 as
SET NOCOUNT ON
Declare @IvgId as numeric(15)
Declare @Address as nvarchar(250)
Declare @AddressClean as nvarchar(250)
Declare @MaxLen as INT
Declare @Add as nvarchar(250)
Declare @Ic as int
Declare @FoundIvgId as numeric(15)
Declare @NewIvgId as numeric(15)
/* here 'N' is for keeping track for some system failures etc */
Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address
WHILE @@FETCH_STATUS = 0
Begin
/*here i m doing string cleaning by removing special characcters */
Select @MaxLen = len(LTRIM(RTRIM(@Address)))
Select @Address = LOWER(@Address)
Select @Ic = 1
Select @AddressClean = ' '
While @Ic <= @MaxLen
/* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */
Begin
Select @Add = Substring(@Address, @Ic, 1)
If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
If ascii(@Add) > 90 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
Select @Ic = @Ic + 1
End
/* now we have removed special characters , for failure checking i m using this 'Y' */
Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y'
Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address
End
PRINT 'Cleaning Done.............................'
Close CurData
Deallocate CurData
/* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */
Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
Select @NewIvgId = 100
WHILE @@FETCH_STATUS = 0
Begin
If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20))
Begin
Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)
End
ELSE
Begin
Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0)
Select @NewIvgId = @NewIvgId + 1
End
Update Data set ProcessDup = 'Y' Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
End
Close CurData
Deallocate CurData
SET NOCOUNT OFF
Print 'Done................................'
************************************************** **************************
now the procedure is over....now i m writing the SQL script of DATA & RESULT table
************************************************** ************************
CREATE TABLE [dbo].[DATA] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Title] [varchar] (10) NULL ,
[FirstName] [varchar] (50) NULL ,
[MiddleName] [varchar] (10) NULL ,
[LastName] [varchar] (30) NULL ,
[Add1] [varchar] (150) NULL ,
[Add2] [varchar] (50) NULL ,
[Add3] [varchar] (50) NULL ,
[City] [varchar] (30) NULL ,
[State] [varchar] (30) NULL ,
[Country] [varchar] (20) NULL ,
[Pincode] [varchar] (10) NULL ,
[OffPhone] [varchar] (20) NULL ,
[OffFax] [varchar] (20) NULL ,
[ResPhone] [varchar] (20) NULL ,
[ResFax] [varchar] (20) NULL ,
[EMail] [varchar] (50) NULL ,
[Source] [varchar] (20) NULL ,
[MODEL] [varchar] (20) NULL ,
[PNCD] [varchar] (6) NULL ,
[DupKey] [decimal](18, 0) NULL ,
[Duplicate] [int] NULL ,
[HouseHoldID] [varchar] (50) NULL ,
[YearSlab] [varchar] (10) NULL ,
[CleanStatus] [int] NULL ,
[AddStatus] [int] NULL ,
[BatchNo] [varchar] (20) NULL ,
[ModelStatus] [int] NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[SapStatus] [int] NULL ,
[ErrCase] [int] NULL ,
[cmpCity] [varchar] (50) NULL ,
[Product] [varchar] (1) NULL ,
[cmpPinCode] [varchar] (6) NULL ,
[Address] [nvarchar] (250) NULL ,
[AddressClean] [nvarchar] (250) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DATA_TEST] Script Date: 15/06/2001 8:36:21 PM ******/
CREATE TABLE [dbo].[DATA_TEST] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Address] [nvarchar] (50) NULL ,
[AddressClean] [nvarchar] (50) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
so now i have given the whole description of my problem....i m eagerly waiting for reply......
if anybody can help....i will be very thankful.....
bye for now
Bhupinder singh
Hey everyone,
I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this code doesn't work right.
Code SnippetIF OBJECT_ID(N'dbo.spCoverageLog') IS NULL
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END
GO
It's generating a syntax error at PROCEDURE.
Anyone know why this might happen?
The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.
Thanks in advance for any suggestions!
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI 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
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View 16 Replies View RelatedI have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
Thanks,
Kevin