Hello,
is message exchange between a .NET Webservice and a SQL stored procedure possible?
And if, could you please explain me how? Or give me a tip where i can get more informations
and maybe samples?
thanks for your help
regards
pamelia
The web serivce for blogging to Community Server requires the username and password to be specified in the SOAP Header and not the body, I however can't access the soap header. How can I achieve this?
View 1 Replies View RelatedHi everybody,
How can I access a webservice from inside a stored procedure? any help is greatly appriciated.
Hi,
I need to call a web service (consume a webservice)from a T-SQL stored procedure. Is there a way to do this. If not is there a way to make a simple http request, something like a utl_http in oracle.
At the moment iam using a MSSOAP30.SOAPCLIENT object created using sp_OACreate to make this call. However this means that the soap toolkit be installed on the pc on which SQL server is installed. I was hoping to find a completely independent way.
Also when i call sp_OACreate where does sqlserver 2005 look to find that object. Iam thinking of putting the MSSOAP30.dll on that machine, if all else fails.
Ahmad
Can you have a CRL stored procedure call a webservice that returns a dataset?
View 7 Replies View Relatedhi
i have developed asp.net application which calls web service of localsystem. it also contains logic for interaction with database. we are also creating connection string through coding not from web.config. i want to debug stored procedure which is called by webmethod. then pls suggess me how can i debug this stored procedure from asp.net which is called in local webservice???
if any one have solve pls help...
atul
Hi!
I am developing an integration solution for MS CRM.
The basic idea is to have a CLR stored procedure that draws data from a SQL database, transforms the data, and then adds it to MS CRM via the webservice.
When executing the stored proc, it randomly fails (although at approximately the same time, everytime).
This is the error message:
Msg 6522, Level 16, State 1, Procedure add_CCU_information, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'add_CCU_information':
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: Only one usage of each socket address (protocol/network address/port) is normally permitted
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at CRM_Integration.CRM_Service.CrmService.Execute(Request Request)
at StoredProcedures.add_CCU_information()
If someone could please give some advice, I would really appreciate it.
Regards,
Du Toit
I have a stored procedure which checks to see if a user's email address exists before it inserts a new record. If it does it should return a message that notifies the user they are already subscribed. If they are not a different message should be returned stating that a new subscription has been created. This procedure works and is shown below.
The problem I am having is with the first SELECT statement. How can I get the procedure to show just one message and not the results from the first SELECT statement too?
CREATE PROCEDURE sp_InsertSubscription
@SubscriberFirstName VARCHAR(50),
@SubscriberLastName VARCHAR(50),
@SubscriberEmailAddress VARCHAR(50),
@SubscriberZipCode INT,
@IsActive BIT,
@SubscriberOptIn BIT,
@AdditionalOffersOptIn BIT,
@Msg VARCHAR(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Check to see if email address exists first
SELECT @@ROWCOUNT FROM Subscriber WHERE SubscriberEmailAddress = @SubscriberEmailAddress
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'This email address is already subscribed'
SELECT @Msg AS 'User'
RETURN
END
-- Insert statements for procedure here
INSERT INTO Subscriber (SubscriberFirstName,
SubscriberLastName,
SubscriberEmailAddress,
SubscriberZipCode,
IsActive,
SubscriberOptIn,
AdditionalOffersOptIn,
SubscriberSignUpDate)
VALUES (@SubscriberFirstName,
@SubscriberLastName,
@SubscriberEmailAddress,
@SubscriberZipCode,
@IsActive,
@SubscriberOptIn,
@AdditionalOffersOptIn,
GETDATE())
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'New user subscription created'
END
SELECT @Msg AS 'User'
END
GO
If the user's email address does not exist I get
(No column name)
User
New user subscription created
If the user's email address does exist I get
(No column name)
0
User
This email address is already subscribed
I would like for the (No column name) to go away - I know this is coming from the first SELECT statement. How do I suppress that statement from being output, yet still get the @@ROWCOUNT variable set?
Hi
I have trouble to get this stored procedure running and I tried to figure out where I did a mistake but I'm just lost, so if somebody could help me... Thanks!
The Error Message is 102, Level 15, State 1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE InsertNewDocumentTypeAndAddSystemFields
@varDocumentTypeName nvarchar(254),
@varNEWDocumentTypeID integer = 0,
@varTempID integer
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Document_Type_LookUP
VALUES(@varDocumentTypeName);
@varNEWDocumentTypeID = IDENT_CURRENT('dbo.Document_Type_LookUP')
-- Name or Title
INSERT INTO dbo.Document_Field
SELECT 'Name', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'
@varTempID = IDENT_CURRENT('dbo.Document_Field')
INSERT INTO dbo.Document_Type_Field_Link
VALUES(@varNEWDocumentTypeID,@varTempID)
-- Filename
INSERT INTO dbo.Document_Field
SELECT 'Filename', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'
@varTempID = IDENT_CURRENT('dbo.Document_Field')
INSERT INTO dbo.Document_Type_Field_Link
VALUES(@varNEWDocumentTypeID,@varTempID)
END
GO
Thanks in advance!
I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).
In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.
Code SnippetIF @SQLCode <> 0
BEGIN
SELECT description = @ErrMsg
FROM master.dbo.sysmessages
WHERE error = @SQLCode
END
Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.
Thanks
Hello everybody,
I've encountered a strange thing using a CLR Stored procedure:
The procedure throws an exception with no message inside... value = {" "}
Basically the procedure has a string as argument which contains a SQL statement that changes according to the users selections...
The results of the query are saved into a dataset for further processing.
Those resultsets can sometimes be very big (ex: 15000 records...). (For the record the procedure works fine for smaller datasets ex 6000 records and running the same query on the application server returns the expected resultset )
By Debugging the procedure I could determine that the failing point was when the dataset is filled...
Anyone having any idea??
The only information I have from this exception is the 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.SqlDataReader.CloseInternal(Boolean closeReader)
at System.Data.SqlClient.SqlDataReader.Close()
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at DataAccess.runScalar(String strSQL, Boolean isStoredProcedure) in c:InetpubwwwrootStatistixApp_CodeDataAccess.cs:line 114
Thanks in advance
Alaindlk
Does anyone have any ideas or sample code for firing a stored procedure using an MSMQ message?
I have to assume XML is in there somewhere.
(ie. create XML formatted message with proc call as element?)
Just getting started on project, any help appreciated.
www.beyonder422.com
Greetings,
I am creating a package that has many SQL tasks. Each task executes a stored procedure. I need to capture any error messages returned by the stored procedures. Eventually, the error messages will be logged so that we can audit the package and know if individual tasks succeeded or failed.
I'm not sure where or how I can access a stored procedure message. What is the best way?
Thanks,
BCB
I'm creating a simple stored procedure:
create procedure spzipcode
(
@TableName nvarchar(10)
)
as
select distinct customer_code
from @TableName
GO
When running the code above, I get the following error message:
Server: Msg 137, Level 15, State 2, Procedure spzipcode, Line 8
Must declare the variable '@TableName'.
I can't figure out what I'm doing wrong.
Does anyone have an answer:
Hi All,I am having an error message when running the program below, the message says " Could not find stored procedure 'dbo.U_Login ". I have tried a inline Sql statement to match if user exists and then redirects to another page but when using a Stored Procedure what I get is the above mentioned error. I am logged onto my PC as Administrator but I don't know why this error appaears. Can anyone help me with this ? I am using SQL Server Express with Visual Studio. here the application I am trying to run:1 using System;
2 using System.Data.SqlClient;
3 using System.Data;
4 using System.Configuration;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12
13 public partial class _Default : System.Web.UI.Page
14 {
15 protected void Page_Load(object sender, EventArgs e)
16 {
17
18 }
19 protected void btnSubmit_Click(object sender, EventArgs e)
20 {
21
22 //SqlDataSource LoginDataSource = new SqlDataSource();
23 //LoginDataSource.ConnectionString =
24 // ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
25 ////ConfigurationManager.ConnectionStrings["LoginConnectionString"].ToString();
26 ////// if (LoginDataSource != null)
27 //// // Response.Redirect("DebugPage.aspx");
28
29 //Sql connection = LoginDataSource.ConnectionString;
30
31 string UserName = txtUserName.Text;
32 string Password = txtPassword.Text;
33
34 // string query = "SELECT * FROM Users WHERE userName = @UserName " + "AND Password = @Password";
35 //SqlCommand Command = new SqlCommand(query, new SqlConnection(GetConnectionString()));
36
37 SqlCommand Command = new SqlCommand("dbo.U_Login", new SqlConnection(GetConnectionString()));
38
39
40 //SqlConnection SqlConnection1 = new SqlConnection(GetConnectionString()); //added
41 //SqlCommand Command = new SqlCommand(); //added
42 //Command.Connection = SqlConnection1; //added
43 // Command.Connection = new SqlConnection(GetConnectionString());
44 //SqlConnection_1.Open();//added
45
46 Command.CommandType = CommandType.StoredProcedure; //added
47 //Command.CommandText = "dbo.U_Login"; //added
48
49
50
51
52 Command.Parameters.AddWithValue("@UserName", UserName);
53
54 Command.Parameters.AddWithValue("@Password", Password);
55
56 Command.Connection.Open();
57
58 SqlDataReader reader;
59
60
61 //reader = Command.ExecuteReader(CommandBehavior.CloseConnection);11
62
63 reader = Command.ExecuteReader();
64
65
66 if (reader.Read())
67
68 Response.Redirect("DebugPage.aspx");
69 else
70 Response.Write("user doesn't exist");
71
72
73 //SqlConnection_1.Close();//added
74
75
76 }
77 private static string GetConnectionString()
78 {
79
80 return ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
81
82 }
83
84
85 }
86
87
88
89
Thanks in advance
Client/Server machine: Windows Xp Pro (SP2) (latest patches)
Office Software: Access 2003 (latest patches)
Database S/W: SQL Server 2005 (latest patches)
The following error message is displayed when trying to modify a stored procedure.
This version of Microsoft Access doesn't support design changes to the
version of Microsoft SQL Server your project is connected to. See the
Microsoft Office Update Web site for the latest information and downloads
(on the Help menu, click Office on the Web). Your design changes will not be
saved.
However, if you save, close and re-open the stored procedure having made the required changes, the changes have been saved.
Is there any way to suppress the error message / hotfix available from microsoft since the error message appears to be completely erroneous ?
Have I provided enough detail as this is my first post ?
Philip
I have a public mailbox that gets information mailed to it (in a pre-determined format).
Is there a way for that info to be put into a table in SQL Server without any user interaction (something running on the exchange server)?
I hope I've given enough info.
Thanks for any and all help!
Ron
I had the SP, I want to call in Script Task , had the Result set data value then I need pop up message box. So how can I call stored procedure result in message box in ssis script task using C#.
and I want to use SSIS -OLEDB connection.
Â
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
cm = Dts.Connections["OLE_TEST_"];
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlComm = new System.Data.SqlClient.SqlCommand("Exec dbo.sOp_xx_XXXe_VXX 280", sqlConn);
sqlComm.ExecuteNonQuery();
above code , no message box.
Hi,
I have written a SQL CLR procedure, which will be invoking the webservice..I developed the application locally and it works fine, I am able to invoke the webservice using the SQL CLR procedure present in my database. But when i hosted the webservice in App server and executed the SQL CLR procedure in DB Server.
From DB Server, I am not able to invoke the webservice present in the app server. But i am able to browse the webservice from my db server.
I am getting the foolowing error message
A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_LoadView':
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at StoredProcedures.usp_LoadView(String ConnectionString, String WebserviceUrl, String ColumnMappingsXml, SqlXml AddressXml, SqlXml& ExceptionSqlXml, Int32& ErrorStatus)
I goggled and tried out various options, like increasing €œwebservice timeout€? and increasing €œexecutiontimeout€? for HttpRuntime, but none seems to be working. Please provide me your suggestions to how to fix this..
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
I look forward to any response. Thanks!
-> Communicate with WebService from MS SQL 2005 CLR Procedure, Quick Steps
1- Create SQL project in V.S 2005.
2- Add new Trigger Class, right click on project select Add -> New Item.
3- Add Web Service reference.
4- Use impersonation technique to communicate with Service.
Example:
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext context = id.Impersonate();
/////////////////////////////
proxy = new WebServiceProxy();
proxy.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
proxy.HelloWorld();
/////////////////////////////
context.Undo();
}
5- Set SQL Project Permission Level to External, change from the project properties Database Tab
"Permission Level Select the permission level from the drop-down list to specify a set of code
access permissions granted to the assembly when accessed by Microsoft SQL Server. The value can be
Safe, External, or Unsafe; these values correspond to the SQL Server permission sets SAFE,
EXTERNAL_ACCESS, and UNSAFE, respectively. Safe is the default.
This setting corresponds to the PERMISSION_SET argument for the SQL Server CREATE ASSEMBLY command.
" MSDN
6- "The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts
the database and the contents within it. By default, this setting is OFF, but can be set to ON by
using the ALTER DATABASE statement.
Note:
To set this option, you must be a member of the sysadmin fixed server role." MSDN
ALTER DATABASE [Database Name] SET TRUSTWORTHY ON
.
7- Build and Deploy assembly.
8- The web service reference generates XmlSerializers assembly, there are many ways to resolve
this issue, a quick way would be to add the XmlSerializers assembly to the database where the
main CLR assembly is deployed.
From "SQL Server Management Studio" select the target database, goto "Programmability" then ->
"Assemblies", there right click "New Assembly" and specify reference.
Hope this is helpful to others.
Regards
Rabeeh Abla
Is it possible to call an external web service from a SQL Server trigger or stored procedure?
View 6 Replies View RelatedHello,
I have created a MS Exchange 2000 link server in my MS SQL Server 2k. I have created a stored procedure (and a view...) using info from that linked server. When I am logged on the server as the Administrator, I can call my stored proc without any problems. When I use another computer (and I am not logged as the admin of the server) and I call the stored procedure, the following error is always raised :
Server: Msg 7302, Level 16, State 1, Procedure test_proc, Line 3
" Impossible de créer une instance du fournisseur OLE DB 'exoledb.DataSource.1'. "
<== I know it is a french error but it can be translated as : "Unable to instancied the OLE DB 'exoledb.DataSource.1' provider"
I would like to know if I can make run my stored proc in the admin account or what should I do to make it work
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
helo all..., this is my store procedure. but it can not display message. my friend said it must use output code. can someone add output code to my store procedure, so it can display message?ALTER PROCEDURE [bank].[dbo].[pay]( @no_bill INT, @no_order int, @totalcost money, @message varchar(100) -- make it output parameter in your stored procedure)ASBEGIN TRANSACTION DECLARE @balance AS moneyselect @balance = balancefrom bank.dbo.billwhere no_bill = @no_billselect @totalcost = totalcostfrom games.dbo.totalcostwhere no_order = @no_orderif (@balance > @totalcost)beginset @balance = @balance - @totalcostUPDATE bank.dbo.bill SET [balance] = @balance WHERE [no_bill] = @no_bill-- set @message = 'your have enough balance'endelsebeginset @message = 'sorry, your balance not enough'endCOMMIT TRANSACTIONset nocount off pls.., thx
View 13 Replies View Relatedcreate procedure Pr_addsupportor(
@supportornumber varchar(10),
@company varchar(100),
@title char(10),
@firstname char(30),
@surname char(30),
@addressline1 varchar(1000),
@addressline2 varchar(1000),
@addressline3 varchar(1000),
@postcode varchar(20),
@town char(100),
@county char(100),
@country varchar(100),
@phonenumber varchar(15),
@faxnumber varchar(15),
@email varchar(100),
@paymenttitle varchar(100),
@barcode varchar(2000),
@collector varchar(3),
@status varchar(10),
@registerdate datetime)
INSERT into
supportor(
[supportor number],
company,
title,
[first name],
surname,
[address line1],
[address line2],
[addess line3],
[post code],
town,
county,
country,
[phone number],
[fax number],
[e-mail],
[payment title],
barcode,
collector,
status,
[register date])
values
(
@supportornumber,
@company,
@title,
@firstname,
@surname,
@addressline1,
@addressline2,
@addressline3,
@postcode,
@town,
@county,
@country,
@phonenumber,
@faxnumber,
@email,
@paymenttitle,
@barcode,
@collector,
@status,
@registerdate);
Server: Msg 156, Level 15, State 1, Procedure Pr_addsupportor, Line 22
Incorrect syntax near the keyword 'INSERT'.
plz help me to find the error
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