Is there an oracle provider out there that will let me invoke a parameterless stored procedure that is in a package in my Oracle source?
Better could that stored proc receive a prameter?
Still better, could I use a stored proc in a OLEDB source component and get the resutls from its only out variable (ref cursor) into my SSIS dataflow?
I haven't been able to get any of these basic functionalities working with either the Oracle OLEDB or the Microsoft OLEDB for Oracle provider...
If not, are there any plans to enahnce the MS provider to handle that?
A more tricky question :
Why does the ReportingService data processing extension for Oracle sources allow such things and not the .NET provider in SSIS?
I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DBMicrosoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:
[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.
I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable. i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider. I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.
Dim conn As New OleDbConnection Dim cmd As New OleDbCommand Dim dr As OleDbDataReader Dim QSQL As String
END I'm then using a tableadapter to execute this from my ASP.NET page using the following code, Protected Sub ExecutePackage() Dim ExecuteAdapter As New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0) Dim strResult As String strResult = ExecuteAdapter.Execute() lblResult.Text = strResult End Sub If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code. When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @ReturnValue and SELECT @ReturnValue to no avail. Can someone suggest how I can get the value 0-6 to my code?
In SQL Server 2005 I need a stored procedure that will execute an SSIS Package for me. There is some earlier stuff on the board but I don't understand it. I don't want to create a Job to do it if I don't have to.
Hey guys, I've got a problem here. I need to send the query result to a csv file then transfer the file to a website. I thought this is a good candidate for a SSIS package. The package is ready now but I don't know how can I execute it from within a stored procedure. I thought sp_OA family of extended procedure would be helpfull. After following steps: EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT
EXEC @hr1 = sp_OAMethod @oPKG, 'exec' EXEC @hr1 = sp_OADestroy @oPKG it tells me command execute successfully. But no package actually gets executed and I can see no results Thanks
Hi, I have an ssis package which reads a file and upload the data into a table. Im executing this package through Stored procedure through dtexec /F command If im uploading this file from local machine the package is executing and the data is uploaded to the table. If it is in network and try to upload it will through an error that can't upload the file.. I have also shared the file on the network.
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".
We are executing a SSIS package using a xp_cmdshell command in a SP as shown below. This package does consumes time to execute almost 90 minutes and does get executed successfully too. But the strange thing is we don't get the result in @result variable just because somehow the next sql statement after the below highlighted statement doesn't get executed at all. After checking execution stats for the SP using the query attached below we observed that somehow the SP vanishes out of the execution stats for the server.
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 figured out a way to execute an Oracle Stored Procedure from an Execute SQL Task by using
Declare Begin SomeStoredProc(?,?,?); End;
with an OLE DB connection using the Oracle Provider for OLE DB.
The parameters are getting passed in and the procedure executes but if for some reason it fails SSIS is painting the task green and keeps processing. I'm guessing that's because the outer Declare/End statement completed sucessfully.
I couldn't get it to work as a function with a return value. :(
Is there another way to execute an Oracle stored procedure that I missed?
Can you call an Oracle stored procedure from a Script Task and then fail it on parameter value?
I would like to call a oracle stored procedure which looks like this
Create procedure in oracle sql plus
CREATE OR REPLACE PACKAGE GroupsPackage AS TYPE CURSOR_TYPE IS REF CURSOR; PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE); END; / CREATE OR REPLACE PACKAGE BODY GroupsPackage AS PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE) AS BEGIN OPEN results_cursor FOR SELECT GroupID, GroupName, GroupDescription, LastUpdateDate FROM Groups ORDER BY GroupName; END; END; /
Execute procedure in oracle sql plus
set autoprint on; var x refcursor; groupspackage.usp_selectgroups(:x); end; /
This works in sql plus. But when I try to run the same (or tried everything) in the execute statement in ssis using execute sql task, I am not able to make it work. I am getting an error message. Please advice, what am I doing wrong. Or is this possible or is there any other way to get this working.
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
I am trying to execute a Oracle Procedure through Execute Sql Task but it keeps throwing back an error ORA:00900. Is it possible to execute the procedure from this object? Do I need to use the script task to execute this. If yes then how?
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
I'm using Sql Server 2005, and I am receiving an error when I attempt to run a stored procedure and I have no clue why. Can someone please help?
I receive an error when I attempt to execute the following stored procedure: exec INSERT_OBJ 1234, 'Name', 123, NULL, GetDate(), 'system'
The error I receive is: "Incorrect syntax near ')'" All of the values are valid values and valid types.
Is it possible to be an error within the stored procedure itself? This error makes it sound like the syntax of my attempt is incorrect, thus it never gets ran.
I am trying to execute a "ServerB" Stored Procedure in "ServerA".This SP is in Multiple DB's in ServerB. I am trying to use Openquery and Dynamic SQL to do that.But I am having issues. Intially i am trying to pass just one DBname as parameter..if it returns values then i can use cursor or other options to retrieve for multiple DB's Please Help!!!
Hi, Can anyone tell me how to execute a stored procedure using vb6.0.I am able to connect to sqlserver from my application.Simple select queries are working.
The stored procedure contains a select statement at the end.I want to get that as the resultset in vb6.0.How do I do this.
Hi I have a stored procedure in SQL Server 2005. It make a backup of a database and restores it to a different name.I use ASP.NET and Framework 1.1.It works really fine when I use SQL Server 2000. But!When trying to do the same thing on SQL Server 2005, the database seems to be created "half way" I can see that the database is created, but after the name is the text Restoring....It never finish restoring.... and nothing shows in the server logs. Any ideas?Differences between SQL Server 2000 and SQL Server 2005 that I must be aware of?Priviliges?ConnectionString parameters?Drivers? I'm using .NET Framework 1.1ODBC (SQL Native Client) Here is the Store procedure code:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[CreateProjectDataBase] @AFModelDatabaseProject varchar(200), @TemplateDbBackupFileAndName varchar(200), @DatabaseName varchar(200), @DataFilePathAndName varchar(200), @LogFilePathAndName varchar(200) AS
BACKUP DATABASE @AFModelDatabaseProject TO DISK = @TemplateDbBackupFileAndName WITH INIT RESTORE DATABASE @DatabaseName FROM DISK = @TemplateDbBackupFileAndName WITH MOVE 'AdressTNG_Project_Data' TO @DataFilePathAndName, MOVE 'AdressTNG_Project_Log' TO @LogFilePathAndName
and here is how it is called from within .NET:this.odbcCreateDataBaseCommand.CommandType = System.Data.CommandType.StoredProcedure; this.odbcCreateDataBaseCommand.Parameters["@AFModelDatabaseProject"].Value = afModelDataBaseName;this.odbcCreateDataBaseCommand.Parameters["@TemplateDbBackupFileAndName"].Value = TemplateDbBackupFileAndName; this.odbcCreateDataBaseCommand.Parameters["@DatabaseName"].Value = dbName;this.odbcCreateDataBaseCommand.Parameters["@DataFilePathAndName"].Value = DataFilePathAndName; this.odbcCreateDataBaseCommand.Parameters["@LogFilePathAndName"].Value = LogFilePathAndName;this.odbcCreateDataBaseCommand.CommandText = "{ CALL CreateProjectDataBase(?,?,?,?,?) }"; this.odbcCreateDataBaseCommand.ExecuteNonQuery(); RegardsTomas
We have a test and production environment. After transfering some tables from test to prod and all stored procedures using those tables. We get an error when executing those stored procedures: " DB-library: Possible network error: Bad token from SQL Server: Datastream processing out of sync. Net-library error 0: DB-libray Process Dead - Connection Broken. " When we execute the stored procedure with 1 parameter less we get a parameter missing error. Then we execute the stored procedure again and everything is allright? Has anyone experienced this before? If so, please help. SQlServer 6.50.201
How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..
I got this results from running (sp_dropallindex) like this:
ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO DROP INDEX table1.index1 GO DROP INDEX table1.index2 GO
I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...
I tried something like this:
DECLARE @DROP AS VARCHAR(8000) SET @DROP='exec sp_drop_allindex' EXECUTE (@DROP)
and i see the same out , but my indexes and PK still there ... i'm confused about it ..
how to use opendatasource to execute a stored procedure remotely in MySQL with parameters..I am using sp_configure to enable and disable Ad Hoc Distributed queries as below before and after the open data source statement
sp_configure show,1 reconfigure with override go sp_configure 'Ad Hoc Distributed Queries',1 reconfigure with override go
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Hi everyone,first time here, so I'm sorry if this has been covered already agesago. :(I am trying to learn T-SQL and Stored Procedures and bought the bookon these topics by Djan Sunderic, Publisher McGraw Hill/Osborne. I'malready stuck on my first Stored Procedure and getting error messagesthat I cannot understand. I've already tried Google and Microsoftonline to no avail. I do have the .NET Framework on my system and useit for programming in C# sometimes and by the looks of the error, it'ssomething to do with that? Please note I am only using SQL ServerExpress.Here is the first Stored Procedure:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE ap_HelloASBEGINSET NOCOUNT ON;SELECT 'Hello world'SELECT * from dbo.EqTypeENDGOThe error in its entirety is this:Msg 6522, Level 16, State 1, Procedure ctrd_DDL_PROCEDURE_EVENTS_vb,Line 1A .NET Framework error occurred during execution of user definedroutine or aggregate 'ctrd_DDL_PROCEDURE_EVENTS_vb':System.UnauthorizedAccessException: Access to the path 'c:server_login.log' is denied.System.UnauthorizedAccessException:at System.IO.__Error.WinIOError(Int32 errorCode, StringmaybeFullPath)at System.IO.FileStream.Init(String path, FileMode mode, FileAccessaccess, Int32 rights, Boolean useRights, FileShare share, Int32bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, StringmsgPath, Boolean bFromProxy)at System.IO.FileStream..ctor(String path, FileMode mode,FileAccess access, FileShare share, Int32 bufferSize, FileOptionsoptions)at System.IO.StreamWriter.CreateFile(String path, Boolean append)at System.IO.StreamWriter..ctor(String path, Boolean append,Encoding encoding, Int32 bufferSize)at System.IO.StreamWriter..ctor(String path, Boolean append)at VbTriggers.Triggers.trigger_DDL_PROCEDURE_EVENTS() ..The statement has been terminated.Can anyone assist please? :(Many thanks in advance,Lara
HI,I need to run same kind of transactions (basically deleting records)in a loop but I have only 1 hour in a day to run my procedure. So Ineed to set a timer in a SP so that SP terminates after one hour andthen rest of the transactions will be done next day.Can anybody suggest as how to check execution time in a storedprocedure? The execution of the SP will be scheduled every night.If u need any further info pls ask.Thanks,Subodh
I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.
This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.
When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.
I have a maintenance stored procedure that executes a script using xp_cmdshell and sqlcmd. The stored procedure has one argument that is contains the name of the procedure to update.
The stored procedures retrieves the location of the stored procedures (stored in a table) and then builds a variable that contains the sqlcmd command. Note that the file name of the script is <stored procedure name>.sql
I have a foreach loop in my SSIS script. I am able to successfully enumerate through an input query. I have a script task inside of my container. I would like to use this task to formulate a Stored Procedure and save this procedrue in a variable so I can use in a future Execute SQL task.
Here is a copy of the code (Which Does Not Work) I am using in the script task to set the variables.
