Is there a way to call an Oracle Procedure using the MS OLD DB Provider
for Oracle object in a SQL Server 2000 DTS package? If it can't be done
this way, is there another way to retrieve data from an Oracle database
using an Oracle procedure to a SQL Server table? Also, can parameters
be passed into Oracle from SQL Server via a procedure? Our Oracle DBA
does NOT want to create views to enable SQL Server to access the data.
Examples would be great if possible. Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I am facing problems while calling Oracle stored procedure which has no parameters.
I have used Microsoft OLE DB provider for Oracle. I have taken one Execute SQL task and set the SQLStatement as call procedurename
I also set the IsStoredProcedure property to True for Execute SQL task.
Is there any synatx problem?
I am getting an error saying
Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.
I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error
Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name
Can anyone help me out on this regard! Thanks in advance
I'm using Execute SQL Task to call an Oracle stored procedure. The following is the error that I get.
Error: 0xC002F210 at Validate and Transfer Actuals, Execute SQL Task: Executing the query "{call RS2_RealProject_ETL.TransformLoadAction}
" failed with the following error: "ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TRANSFORMLOADACTION'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Per my review of the Forums, I'm entering the SQL syntax correct that is as follow and also use parameter names in Oracle in ordinal method starting from 0.
I have three "input" and one "output" parameter for the stored procedure that they all are of type "NUMBER" in oracle and I've defined them in "Parameter Mapping" window of Excute SQL Task as "NUMERIC".
Server: Msg 7212, Level 17, State 1, Line 3 Could not execute procedure 'EmployeeOutPrm' on remote server 'GENEVA'. [OLE/DB provider returned message: One or more errors occurred during processing of command.] [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
I am facing problems while calling Oracle stored procedure which has no parameters.
I have used Microsoft OLE DB provider for Oracle. I have taken one Execute SQL task and set the SQLStatement as call procedurename
I also set the IsStoredProcedure property to True for Execute SQL task.
Is there any synatx problem?
I am getting an error saying
Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.
I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error
Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name
Can anyone help me out on this regard! Thanks in advance.
I am trying to call oracle stored procedure from SRSS 2005. I am using the syntax { Call s_test_rcur()} . I am getting following error.An error occurred while retrieving the parameters in the query.ORA-00911: invalid characterORA-06512: at "SYS.DBMS_UTILITY", line 68ORA-06512: at line 1
ORA-00911: invalid characterORA-06512: at "SYS.DBMS_UTILITY", line 68ORA-06512: at line 1Â (System.Data.OracleClient) Here is the Oracel stored proc. TYPE rc_test IS REF CURSOR; PROCEDURE s_test_rcur (po_test_rc OUT rc_test, -- returns a record setpo_error OUT INTEGER)ISBEGIN g_err_level := 1;OPEN po_test_rc FORSELECT a.ssn_id,TO_CHAR (a.acad_yr) || TO_CHAR (a.acad_yr + 1) acad_yr,RPAD (NVL (last_name, ' '), 30, ' ') last_name,RPAD (NVL (first_name, ' '), 30, ' ') first_name,NVL (middle_initial, ' ') middle_initialfrom test_tableorder by last_name;po_error := 0;EXCEPTIONWHEN OTHERSTHENpo_error := -1;g_error_code := SQLCODE;g_error_msg :='Err level :' ||TO_CHAR (g_err_level) ||' ' ||SUBSTR (SQLERRM, 1, 250);END;
Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine
Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally. Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.
I have seen a couple of references to "EXEC AT" in relation to linked servers, that can be used to call Oracle Stored Procedures, in the TechNet postings. Based on the postings, this is new functionality in SQL Server 2005, but I am unable to find any reference to this in the Online Documentation for SQL Server 2005.
Could someone point me in the right direction for documentation of the feature?
1. (calls an oracle package) - call lpaarchive.pibrdg.setlastbridgeruntime(sysdate);
2. (selects rows) - select timestamp ,
pitag ,
rtlmp from LPAARCHIVE.I_PIBRDG_BUS5MINRTLMPS
where timestamp <= (sysdate + 0.002777778)
order by TIMESTAMP ASC; I need to execute these 2 statements together. I tried using Execute SQL task to call the package and then an OLEDB source that calls a variable with the select statement. But it does not retrieve any rows. It seems like the result of calling the package is used by the select statement to give the final rows. Could anyone please help me resolve this issue.
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
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)
'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.
Everytime I execute this script - I am not getting the result I want. I was hoping to take the some parameters call one stored procedufre with in the main procedure. I was hoping to assign a variable to the nested procedure and use that variable in the main procedure, but I am getting a '0' as result - however when I call the a inner procedure by itself if get the correct result. Can someone look at this and tell me where I am wrong? It seems to be a scope problem. Thanks set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: September 8,2006-- Description: Inserts a new project-- =============================================ALTER PROCEDURE [echo88].[insertProject] -- Add the parameters for the stored procedure here @name varchar(50), @desc varchar(MAX), @start datetime, @finish datetime, @memid int, @addr1 varchar(32), @addr2 varchar(16), @city varchar(32), @stateid int, @zipcode char(5)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @siteid int; EXEC @siteid = insertSite @addr1, @addr2, @city, @stateid, @zipcode; -- Insert statements for procedure here INSERT INTO Project(Title, Description, Start, Finish, SiteID, MemberID ) VALUES ( @name, @desc, @start, @finish, @siteid, @memid );END
Hi, i've had this query method: 34 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)35 {36 SqlConnection oConn = new SqlConnection(_connectionString);37 string strSql = "Insert into LogDetail (LogID, CategorieID, Inhoud)";38 strSql += "values(@logID, @categorieID, @inhoud)";39 SqlCommand oCmd = new SqlCommand(strSql, oConn);40 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;41 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;42 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;43 44 try45 {46 oConn.Open();47 int rowsAffected = oCmd.ExecuteNonQuery();48 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");49 oCmd.CommandText = "select @@IDENTITY";50 oCmd.Parameters.Clear();51 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();52 }53 catch (Exception ex)54 {55 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);56 }57 finally58 {59 if (oConn.State == ConnectionState.Open) oConn.Close();60 }61 } which i've converted to a stored procedure: 1 ALTER PROCEDURE [dbo].[insert_DagVerslagDetail] 2 -- Add the parameters for the stored procedure here 3 @dagverslagdetailID int, 4 @logID int, 5 @categorieID int, 6 @inhoud varchar(100) 7 AS 8 BEGIN 9 -- SET NOCOUNT ON added to prevent extra result sets from 10 -- interfering with SELECT statements. 11 SET NOCOUNT ON; 12 SET @dagverslagdetailID = SCOPE_IDENTITY() 13 14 -- Insert statements for procedure here 15 BEGIN TRANSACTION 16 INSERT LogDetail (LogID, CategorieID, Inhoud) 17 VALUES(@logID, @categorieID, @inhoud) 18 COMMIT TRANSACTION 19 END
Now i would like to call that stored procedure in my previous method, so i've changed it to this: 1 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail) 2 { 3 SqlConnection oConn = new SqlConnection(_connectionString); 4 string strSql = "insert_DagVerslagDetail"; 5 strSql += "values(@logID, @categorieID, @inhoud)"; 6 SqlCommand oCmd = new SqlCommand(strSql, oConn); 7 oCmd.CommandType = CommandType.StoredProcedure; 8 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID; 9 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID; 10 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud; 11 12 try 13 { 14 oConn.Open(); 15 int rowsAffected = oCmd.ExecuteNonQuery(); 16 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail"); 17 oCmd.CommandText = "select @@IDENTITY"; 18 oCmd.Parameters.Clear(); 19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar(); 20 } 21 catch (Exception ex) 22 { 23 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message); 24 } 25 finally 26 { 27 if (oConn.State == ConnectionState.Open) oConn.Close(); 28 } 29 }
Do i still need the lines 17 oCmd.CommandText = "select @@IDENTITY"; 19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar(); Because i've declared the identity in my stored procedure
Hi, I have a stored procedure, and it is expecting an output. If I declared the passing varaible as ref, it compiles fine, but it is not returning any value. If I pass the varaible as out, and add the paramater MyComm.Parameters.Add(new SqlParameter("@ReturnValue", returnValue)); it gives the following error. Compiler Error Message: CS0269: Use of unassigned out parameter 'quoteID'. And if I don't supply the previous statement, the following error occurs. System.Data.SqlClient.SqlException: Procedure 'CreateData' expects parameter '@ReturnValue', which was not supplied. How Can I fix this? thanks.
I have a stored procedure that calls a DTS package to grab a text file that has been uploaded to the server and merge it with a table on the database. The DTS package works woderfully in SQL, as does the the file upload. The problem arrises when I create a stored procedure to run the DTS package. I know that you have to shell out and do a command line on the SQL server (and I think that I got the syntax correct) but its calling the Stored Procedure in the ASP.NET app that is causing me hardship. Here is the code that I have so far:
Stored Procedure:
CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUSsqlServer113 /NdtsPackage /UuserID /Ppassword' GO
VB to run DTS:
Dim myCommand As SqlCommand myCommand.CommandType = CommandType.StoredProcedure myCommand.CommandText = "spSampleData" myCommand.ExecuteNonQuery()
I'm not sure what I am doing wrong but any help would be great.
I am trying to set up a call to a Stored Procedure to do an Insert. Here is my code snippet:
<%@ Page Language="VB" %> <%@ import Namespace="System" %> <%@ import Namespace="System.Data.SqlClient" %> . . . Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim cmdInsert as New SQLCommand("AdminUser_Insert", loConn) cmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter() InsertForm.InsertCommand = cmdInsert
Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30451: Name 'CommandType' is not declared.
This error happens on the line: cmdInsert.CommandType = CommandType.StoredProcedure
I'm trying to call a job from a stored procedure. To do so, I've found that sp_start_job is doing just that. My problem is that the sp_start_job is not in my master database stored procedures and I don't know how to add it. I'm working with SQL Server 7 Enterprise without the SP.
Am I looking for the right thing ? (sp_start_job) Where can I find it ?
Hey, I have a parent SP, and within that parent I want to call a a child what is the code to call that child procedure? or teh easiest way to make that happen?
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
When I call a stored procedure from a dll written in Builder C++, it gets blocked. But if I call the same SP from the main program, it works fine. but I need to call SP from the dll. What's the problem? Thanks...
I know this thread is sql, and i'm asking a vb.net question but I cannot find a straight forward answer anywhere else. I am using visual studio 2008 designing a vb.net application where I created a stored procedure using sql management studio 2005. Here is the stored procedure:
CREATE PROCEDURE dbo.StoredProcedure2 @intPID char(10) AS SELECT SUM(Financial.Fee) from dbo.Financial WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, getdate())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Financial.Date))) AND [SPatient Number]=@intPID /* SET NOCOUNT ON */ RETURN
It takes the sum of my Fee column in my financial table where the current date is equal to the column named Date in my Financial table and SPatient Number equals my variable intPID, which the value is defined for in my application (yes, i know that is a horrible name for a column but I cannot change it for it is not my project).
Now, to my knowledge this procedure works fine and should output a single value. However, like i said, i am using visual studio 2008 and therefore am using vs's more automated way of connecting to sql servers(and by that I mean configuring the server through visual studio rather than manually defining datasets, dataadapters, and connection strings through code) All of the tutorials I find use data adapters and are done by manually declaring sql connections and so forth. I like visual studios more automated method of doing sql tasks, and would like to know if there is a simple way to call a stored procedure using visual studio in such a fashion where I would write something like "exec dbo.StoredProcedure2 'intPID' "
Any help is much appreciated, thank you
edit: If i did not provide enough information please let me know, i'm using a strongly typed dataset
I have three stored procedure already created ABC. Now I need to create another one and call other three in each situation. Like If Apple then use Sp_A, if Orange then use Sp_B, and if Mango then use sp_C.
Hi all, I'm new to SQL Server and I'm trying to call BCP from a stored procedure with a parameter passed in as the path to which to export the datafile. This parameter is also the name of a network PC. However, I keep getting this error: SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file NULL
This is the stored procedure: PROCEDURE DownloadLinkEvents @localPath varchar(80) AS declare @bcpCommand varchar(200) begin set @bcpCommand = 'bcp <dbName> out ' + @localPath + '-c -t"|" -S<dbServer> -Usa -P<passowrd>' exec master..xp_cmdshell @bcpCommand end
This is probably the most basic of solutions, but I have spent the last 3 hours trying to work it out, and searching google!
I am trying to call information from a stored procedure (B), from within another stored procedure (A). The select statement from Procedure A contains information to be passed to Procedure B, to get some information.
This is the Procedure I have come up with so far, and I have included dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID) as part of the SELECT clause, in a vain attempt that this would work....but it dosn't!
Anyone got any ideas of how to do this? Or even what it would be called so I can start making inteligent searches on google?
USE [QP] GO /****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/29/2008 18:24:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[USERS_LIST] @STYPE Int, @UserID Int, @Username varchar(100), @Surname varchar(100), @DOB VarChar(40), @Location varchar(100), @Email Varchar(100), @Mobile varchar(100)
As SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender, dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Mobile.Value AS Mobile, dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3, dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age, dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID) FROM dbo.Members INNER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID WHERE @STYPE = '1' AND ((dbo.Members.EntryID = @UserID) or (dbo.Members.Username = @Username) or (dbo.Members.Surname = @surname) or (dbo.Members.DateofBirth = Convert(datetime, @DOB)) or (dbo.ActiveMember_Location.Location2 = @Location) or (dbo.ActiveMember_Location.Location3 = @Location) or (dbo.ActiveMember_Location.Location4 = @Location) or (dbo.ActiveMember_Email.value = @Email) or (dbo.ActiveMember_Mobile.value = @Mobile))
I have created a database and stored procedure there.I want to execute that store procedure having dot net in my front end,i.e i want to execute store procedure from .net.How can I do that.Pls help me.
wanted to use sp_OACreate, sp_OAMethod and sp_OADestroy to execute aDTS package from a stored procedure. I had the dba (using the saaccount) create a wrapper stored procedure as recommended inhttp://msdn.microsoft.com/library/d...rary/en-us/dnsq....However, when I executed the wrapper stored procedure, I stillreceived privilege errors from the underlying sp_oa extended storedprocedures.Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 6EXECUTE permission denied on object 'sp_OACreate', database 'master',owner 'dbo'.Any ideas on what we could be doing wrong, or any suggested resolutionswould be appreciated.Thank you.
Hi,I am learning SQL Server 2005. I need to call .NET assembly procedurefrom T-SQL.Here's part of my assembly:using System;using System.Data.Sql;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace DemoSQLServer{public sealed class Demo{[SqlProcedure(Name="PodajKsi¹¿ki")]public static void PodajKsi¹¿ki(){SqlCommand cmd = new SqlCommand("SELECT * FROM Ksi¹¿ki");SqlDataReader dr = cmd.ExecuteReader();SqlContext.Pipe.Send(dr);}...}}I have created assembly in Object Explorer (Programmability /Assemblies).How to call procedure? I tried:exec DemoSQLServer.PodajKsi¹¿kibut I got a message:Could not find stored procedure 'DemoSQLServer.PodajKsi¹¿ki'.Please help.Thank you./RAM/
I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine. I am able to call the sp from sql advantage passing in several parameters and it runs just fine. However when I created a dataset and tried to call it through the reporting service I get the following error message.
