Stored Procedure Excuting By Datetime
Jan 3, 2008
Hello,
I have got an SQL server stored procedure, and I would like to get this stored procedure, dbo.SpDate_Time_Minute_Today below executed against a matching or exact datetime from the database tables, PRODUCT_SALES.
Code Block
CREATE PROCEDURE dbo.SpDate_Time_Minute_Today
AS
SELECT PROD_SAL_DESC FROM PRODUCT_SALES
WHERE (CONVERT(CHAR(11), PROD_SAL_BY_DATE, 103) + ' ' + SUBSTRING(CONVERT(CHAR(17), PROD_SAL_BY_DATE, 100), 13, 19) = CONVERT(CHAR(11), CURRENT_TIMESTAMP, 103)
+ ' ' + SUBSTRING(CONVERT(CHAR(17), CURRENT_TIMESTAMP, 100), 13, 19))
/* SET NOCOUNT ON */
RETURN
PRODUCT_SALES has columns like this:
PROD_SAL_NO
PROD_SAL_BY_DATE
PROD_SAL_DESC
PROD_SAL_MFR
PROD_SAL_DEPT
Please do anyone here know how I can achieve this please. Thanks.
View 6 Replies
ADVERTISEMENT
Oct 10, 2007
I know that you can execute a stored proc with the "execute" command, but is there any way to execute a string that is an sql string? On a related note, is there a better way than building a string like "select * from table where" and then tacking on "value="+@somevalue to do a search if your search is based on multiple (possibly null) variables? If you can answer the first question, dont bother with the second. =P
View 10 Replies
View Related
Dec 8, 2007
Using 'Execute SQL Task' I want to execute a stored procedure in Oracle 10g R2.
What should be the SQL statement?
Thank you,
Smith
View 5 Replies
View Related
Apr 15, 2004
Hi;
I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??
Create Procedure AddLog
(
@User char(10),
@DLogon DateTime(8),
@Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@User,DLogon,Status)
ERROR:
Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9
The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
View 1 Replies
View Related
Mar 14, 2008
Hi,
Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateActiveStates1]
@PROVIDERID INT
AS
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @ACTIVE BIT
SET @ACTIVE = 0
SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)
SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)
IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))
BEGIN
PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'
SET @ACTIVE=0
END
ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())
BEGIN
IF (@ENDDATE = NULL)
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'
SET @ACTIVE=1
END
ELSE IF (@ENDDATE >= GETDATE())
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'
SET @ACTIVE=1
END
ELSE
BEGIN
PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'
SET @ACTIVE=0
END
END
UPDATE Providers SET Active=@ACTIVE
WHERE ProviderID=@PROVIDERID
UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID
View 2 Replies
View Related
May 16, 2008
When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".
Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();
SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs
if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}
I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
View 3 Replies
View Related
Dec 14, 2006
How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?
View 13 Replies
View Related
May 23, 2007
Hello everyone I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter
cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime));
cmdPublish.Parameters["@datPublishDate"].Value = ????
Thanks
View 3 Replies
View Related
Jan 18, 2008
hello,
I have a stored procedure being called from my class.
All values are ok, except for the DateTime value. What am i doing wrong here ? Am wondering if its the date size of 50 I put there or what ?
command.Parameters.Add(new SqlParameter("@dob", SqlDbType.DateTime,50, dob1));
Error Message
Compiler ErrorMessage: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlParameter.SqlParameter(string, System.Data.SqlDbType, int, string)' has some invalid arguments
thanks
Ehi
View 3 Replies
View Related
Aug 19, 2004
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Any idea how this is done?
View 1 Replies
View Related
Nov 3, 2005
In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column:
cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)
The stored procedure then defines the input as smalldatetime:
@unitHdApprove smalldatetime,
However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.
I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.
I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?
Thanks,
Sarah
View 2 Replies
View Related
Sep 9, 2007
Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:
SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition
C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();
Thank you,
View 2 Replies
View Related
Feb 5, 2004
Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.
What I have is:
CREATE PROCEDURE busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
The error I keep getting is:
Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.
Any ideas appreciated.
(Edit:)
I've also tried it this way:
CREATE PROCEDURE UK_busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN
/* Do some error processing */
PRINT 'Error Occured' END
ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
Which gives me the same error!
.logic.
View 5 Replies
View Related
Nov 23, 2005
Hi,When I pass a date time parameter the stored procedure takes about 45seconds, when I hard code the parameter it returns in 1 second. How canI rewrite my stored procedure?@createddatelower datetimeWHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45seconds)vs.WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') ANDdbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)thanks for your help,Paul
View 5 Replies
View Related
Nov 19, 2006
Hi,
I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .
Stored procedure:
ALTER PROCEDURE dbo.SPTest
@testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @testvalue, 'EUR/USD')
RETURN 1
VB.NET code:
Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))
When I run this the ReturnVal is 0.
I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.
View 1 Replies
View Related
Apr 26, 2008
I want to store datetime data in data table.
I am facing problem in using datetime data value in insert statement passed via stored procedure.
Any Help !Thanks in advance
View 3 Replies
View Related
Nov 22, 2007
Hi there,I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).I was duration in the format of hours:minutes:seconds.The stored procedure is pasted below. I am getting the following error. Syntax error converting datetime from character string. Any ideas? ============================== CREATE PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List ( @websiteID AS int = 0, @SelectedDateFrom AS dateTime, @SelectedDateTo AS dateTime) ASIF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0begin set @SelectedDateFrom=null endIF ISNULL(@SelectedDateTo, '') = ''begin SET @SelectedDateTo = @SelectedDateFromendSET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions WHERE UserID != 'Anonymous'GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitleHAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)GO============================== <Columns> <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn> <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn> <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn> <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn> <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn> <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn> </Columns> ============================
View 1 Replies
View Related
Jul 23, 2005
Hi Everyone,I've been battling this for two days with no luck. I'm using SQLServer 2000.Here's the mystery: I've got a stored procedure that takes a singlevarchar parameter to determine how the result set is sorted. Here itis:CREATE PROCEDURE spDemo @SortField varchar(30)ASSELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDescFROM ActivityLogORDER BY CASE @SortFieldWHEN 'dtmTimeStamp' THEN dtmTimeStampWHEN 'strEmpName' THEN strEmpNameWHEN 'strOld' THEN strOldWHEN 'strNew' THEN strNewWHEN 'strActionDesc' THEN strActionDescENDGOWhen I execute the stored procedure in the Query Analyzer, it worksperfectly ONLY IF the @SortField parameter is 'dtmTimeStamp' or'strNew'. When passing in any of the other three possible values for@SortField, I get the following error:Server: Msg 241, Level 16, State 1, Procedure spDemo, Line 4Syntax error converting datetime from character string.Now instead of executing the stored procedure, if I copy and paste theSELECT statement directly into the Query Analyzer (after removing theCASE statement and manually trying each different value of @SortField),it works fine for all five possible values of SortField.Even though the error points to Line 4 of the stored procedure, itseems to me that the CASE statement is causing problems for some, butnot all, values of the @SortField parameter.Any ideas?Thanks,Jimmy
View 4 Replies
View Related
Nov 1, 2007
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
View 2 Replies
View Related
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
May 10, 2004
When excuting the xp_sendmail in the Query analyzer, my mail gets send. When doing this in my code it doesn't work eventhough I explicitly use startmailn then xp_sendmail and then sp_stopmail? I think this is a problem of user rights? I gave my user as parameter but even then it didn't work. Any suggestions? I'm pretty sure, the used code is correct.
greetings,
Geoff
View 1 Replies
View Related
Feb 8, 2008
I have a package that contains several package when executing it like a task, presents the following messages
Error: 2008-02-08 11:24:02.55
Code: 0xC0202009
Source: DWCampo-Dimensiones- Produccion Connection manager "Administrador de conexi¢n con Excel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-02-08 11:24:02.55
Code: 0xC020801C
Source: Agrupacion Canteros Origen - Canteros$ [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Administrador de conexi¢n con Excel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2008-02-08 11:24:02.55
Code: 0xC0047017
Source: Agrupacion Canteros DTS.Pipeline
Description: component "Origen - Canteros$" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2008-02-08 11:24:02.55
Code: 0xC004700C
Source: Agrupacion Canteros DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-02-08 11:24:02.55
Code: 0xC0024107
Source: Agrupacion Canteros
Description: There were errors during task validation.
End Error
Error: 2008-02-08 11:24:02.55
Code: 0xC00220E4
Source: DWCAMPO-Dimensiones (Producci¢n)
Description: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.
.
End Error
some suggestion....!!!
View 4 Replies
View Related
Dec 14, 2006
Hi.
Some time I get delay in running particular querry.I check in my App and also in sql visual stodiu.I'm using sqlexpress edition.If I use Sql developer does speed go up?
View 1 Replies
View Related
Aug 17, 2006
Hi,
Is it possible to execute queries in parallel on multiple linked servers? I retrieve the checksum of a table on a linked servers like this:
SELECT * FROM OPENQUERY(Server1, 'SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*) FROM Table1')
I need to do this on multiple linked servers at the same time - is this possible? I tried the following but my workstation executes the queries sequentially:
SELECT ("Query Server1"), ("Query Server2"), ("Query Server3"),..
Any suggestions??????
Rgds
Bob
View 1 Replies
View Related
Nov 1, 2007
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?
View 1 Replies
View Related
Mar 3, 2008
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.
View 11 Replies
View Related
Nov 14, 2014
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.
View 1 Replies
View Related
Jan 29, 2015
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] ....
View 9 Replies
View Related
Sep 19, 2006
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???
View 1 Replies
View Related
Dec 28, 2005
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 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
Mar 28, 2007
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
View 9 Replies
View Related
Jan 23, 2008
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
View 1 Replies
View Related
