How to execute a store procesure using a link server to oracle

Is it possible to execute a store procedure via a link server to oracle.
I tried these two options but none of them worked.
select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
Deepak VermaDverma,
Can you execute the sp directly as with SQL Server?
EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
i.e.,
EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
HTH
Jerry
"dverma" <dverma@.discussions.microsoft.com> wrote in message
news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
> Is it possible to execute a store procedure via a link server to oracle.
> I tried these two options but none of them worked.
> select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
> select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
>
> --
> Deepak Verma|||If this linked server were a sql server then there is no problem, I would
like to know specific answer against a ORACLE database.
Deepak Verma
"Jerry Spivey" wrote:

> Dverma,
> Can you execute the sp directly as with SQL Server?
> EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
> i.e.,
> EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
> HTH
> Jerry
> "dverma" <dverma@.discussions.microsoft.com> wrote in message
> news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
>
>

How to execute a SSIS package from VS 6.0?

Dear all,

I wrote this post in another category but the answer did not satisfied so that I'd like to know if really anyone has ever used or experienced with this possibility. No by .Net language rather than Vb 6.0 or ASP or even instanciacing DMO library.

Thanks a lot for your support,

According this link http://support.microsoft.com/?kbid=817248 it seems possible but I haven't idea if possible keeping in mind that it has been made by yourself no for others... So that, I mean, these assemblies comes along with Sql Server 2005 installation.

sql

How to execute a SSIS dtsx package from an asp.net 2.0 application?

I have a SSIS package that I want users to be able to execute by clicking a button on an a web page. The package does not require any parameters to be passed to it. Previously I've executed DTS packages without any problems but after a fair bit of investigation and trawling the net I've not found a way to do this successfully with SSIS. Some code I've tried -

 Dim app As New Application() ' ' Load package from file system ' Dim package As Package = app.LoadPackage("c:\ssis\Package.dtsx", Nothing) 'package.ImportConfigurationFile("c:\ExamplePackage.dtsConfig") 'Dim vars As Variables = package.Variables 'vars("MyVariable").Value = "value from c#" Dim result As DTSExecResult = package.Execute() lblResult.Text = "Package Execution results: {0} " & result.ToString()

All I get is a message 'Failure'.

Does anyone have an example of how to do this?

Your requirement is different and you are doing in wrong way. The mentioned code run the package on the same machine.

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1801974&SiteID=1

|||

ok I've gone for option number 3 on your first link which uses a webservice to execute the package and then I can run the webservice through my asp.net page.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

This is my webservice - the example taken from that page,

<WebMethod()> _Public Function LaunchPackage( _ByVal sourceTypeAs String, _ByVal sourceLocationAs String, _ByVal packageNameAs String)As Integer'DTSExecResultDim packagePathAs String Dim myPackageAs PackageDim integrationServicesAs New Application' Combine path and file name. packagePath = Path.Combine(sourceLocation, packageName)Select Case sourceTypeCase"file"' Package is stored as a file. ' Add extension if not present.If String.IsNullOrEmpty(Path.GetExtension(packagePath))Then packagePath =String.Concat(packagePath,".dtsx")End If If File.Exists(packagePath)Then myPackage = integrationServices.LoadPackage(packagePath,Nothing)Else Throw New ApplicationException( _"Invalid file location: " & packagePath)End If Case"sql"' Package is stored in MSDB. ' Combine logical path and package name.If integrationServices.ExistsOnSqlServer(packagePath,".",String.Empty,String.Empty)Then myPackage = integrationServices.LoadFromSqlServer( _ packageName,"(local)",String.Empty,String.Empty,Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case"dts"' Package is managed by SSIS Package Store. ' Default logical paths are File System and MSDB.If integrationServices.ExistsOnDtsServer(packagePath,".")Then myPackage = integrationServices.LoadFromDtsServer(packagePath,"localhost",Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case Else Throw New ApplicationException( _ "Invalid sourceType argument: valid values are'file', 'sql', and 'dts'.")End Select Return myPackage.Execute()

and my asp.net page

Protected Sub btnBuildDW_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnBuildDW.Click ExecutePackage()End Sub Protected Sub ExecutePackage()Dim launchPackageServiceAs New SCCBuildDW.SCCBuildDWDim packageResultAs Integer Try packageResult = launchPackageService.LaunchPackage("file","c:\ssis","Package")Catch exAs Exception' The type of exception returned by a Web service is: ' System.Web.Services.Protocols.SoapException lblResult.Text ="The following exception occurred: " & ex.MessageEnd Try lblResult.Text = packageResult.ToString'CType(packageResult, PackageExecutionResult).ToString ' Console.ReadKey()End Sub Private Enum PackageExecutionResult PackageSucceeded PackageFailed PackageCompleted PackageWasCancelledEnd Enum

I'm getting PackageFailed. The webservice runs and returns 1 - which must be PackageFailed.

Any ideas what I'm doing wrong? The webservice is using windows authentication and the folder the package is in has Everyone Full Control.

|||

I've made some progress. It was failing due to permissions, I've altered these and can now execute a package containing a stored procedure. However my original package runs a stored procedure and then an Analysis Services Task. This still fails - I've checked the user is a member of the role that has access to run the task.

Is there something else I need to do?

|||

I'm still stuck on this - can anyone offer any help?

|||

I have this working now on my local machine. As soon as I move it to the live server I get PackageFailed when I attempt to run it. Has anyone experienced similar problems?

Given the amount of information on the internet I'm beginning to think I'm the only one who wants to execute SSIS in ASP.NET!

|||

i have similar issues too. maybe running ssis dtsx package using web services is not a good idea at all. especially if we run into so many authentication issues.

what about using sql server agent to run ssis package!

|||

I'm now using a stored procedure to execute the package and this works without problems.

Details of my code can be found here -http://forums.asp.net/p/1149972/1873190.aspx#1873190

|||

I'm also having the same problems you have described. I don't have the option of executing from a stored procedure.

I noticed that when I use the <identity impersonate="true"> and specified the username and password, that I got much further, but the package still fails.

I'm guessing that I'll probably end up adjusting the permissions on certain directories and so forth until this works.

|||

Hi all,

this might be useful.

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1518271

I tried using this and it works.

How to Execute a SQL (.sql) script from within VB?

Hello,

I am trying to figure out the best way to make VB6 execute an auto-generated .SQL file from SQL Server 2000.

SQL Server 2000 has created a script (about 1000 lines long), creating views and SPs, and I need to execute said script from a VB client application. Similar to the effect of pasting it into the Query Analyser, only automated.

Anyone have a suggestion (besides reformatting evey like and hard-coding it?)

Thanks a ton!read it into a string, and execute it as normal.|||You can read the SQL into a string and give it to a SqlCommand class for ExecuteNonQuery.

One tip: the "GO" batch delimiter is not T-SQL, it is a signal to Query Analyzer. Many script generation tools will place the "GO" delimiter into script files to tell QA where to split up the commands. You'll need to parse the script and execute individual batches as you encounter GO batch delimiters.sql

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks
|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a selectcommand on button click?

Hello friends

Can anyone tell me the means by which I can populate a label or a datagrid by executing a selectcommand statement on the form of my webpage. And what is the best way to retrieve data from a SQL datasource, is it by using separate sqldatasources for each different parts of a page that requires different data from different tables in the same database or by using a tableadapter to retrieve the data for the best performance?

Thanks for replying for my previous questions. It was helpful and great in my program implementation.

Thank you

Hi

When you bind the sqldatasource to datagrid selectcommand will be automatically executed if you call databind().

As for difference of SqlDataSource and tableadapter it largely depends on the implementation you could take a look at:

DataSets vs. Collections