Executing SSIS from stored procedure - need to get return value into ASP.NET

I'm executing an SSIS package using the following stored procedure

ALTER PROC [dbo].[SSISRunBuildSCCDW]ASBEGINDECLARE @.ServerNameVARCHAR(30), @.ReturnValueint, @.Cmdvarchar(1000)SET @.ReturnValue = -1SET @.ServerName ='myserver'SET @.Cmd ='DTExec /SER ' + @.ServerName +' ' +' /SQL ' +'\BuildSCCDW '--Location of the package stored in the mdb --' /CONF "\\ConfigFilePath.dtsConfig" ' +--' /SET \Package.Variables[ImportUserID].Value; ' +--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUTRETURN @.ReturnValue--SELECT @.ReturnValue [Result]END

I'm then using a tableadapter to execute this from my ASP.NET page using the following code,

Protected Sub ExecutePackage()Dim ExecuteAdapterAs New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String strResult = ExecuteAdapter.Execute() lblResult.Text = strResultEnd 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?

Sorted!

Commented out RETURN @.ReturnValue and uncommented the line below it and it works. I had already tried that... bizarre.

Executing SQL clause in extended stored procedure

Hello,
I have a question about the executing of an SQL clause in xp(extended stored
procedure).
I saw the examples about the ODS: xp_gettable_dblib and xp_gettable_odbc.
When I call xp_gettable_dblib, this xp execute the SQL clause in session,
which is different from the session from which it is called(EXEC
xp_gettable_dblib. Of course this is because of the dbopen).
I also saw the extended stored procedure xp_execresultset(xp_repl.dll) ,
which also can execute an SQL clause, but in contrast to xp_gettable_dblib,
this SQL clause is executed in the same session, from which it is called.
I am interested how it is made?
I saw that in xp_repl.dll are imported 5 API procedures from ODBC32.dll. In
this case is ODBC API is used?
I want to write my own xp, using the technology used in xp_execresultset.Since extended stored procedures are deprecated in SQL Server 2005, you
should probably find another solution.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:#zfRaJ6sFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a question about the executing of an SQL clause in xp(extended
stored
> procedure).
> I saw the examples about the ODS: xp_gettable_dblib and xp_gettable_odbc.
> When I call xp_gettable_dblib, this xp execute the SQL clause in session,
> which is different from the session from which it is called(EXEC
> xp_gettable_dblib. Of course this is because of the dbopen).
> I also saw the extended stored procedure xp_execresultset(xp_repl.dll) ,
> which also can execute an SQL clause, but in contrast to
xp_gettable_dblib,
> this SQL clause is executed in the same session, from which it is called.
> I am interested how it is made?
> I saw that in xp_repl.dll are imported 5 API procedures from ODBC32.dll.
In
> this case is ODBC API is used?
> I want to write my own xp, using the technology used in xp_execresultset.
>

EXECuting sps within sps (using return params)

Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @.@.IDENTITY) as input for some of the other procedures.

I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...

CREATE PROCEDURE addTemplateDetail
@.TemplateID int,
@.GroupNameID int,
@.SubGroupNameID int=null,
@.MethodID int,
@.AnalyteID int
AS
DECLARE @.TemplateGroupNameID int
DECLARE @.TemplateMethodID int
SET @.TemplateGroupNameID=(EXEC addTemplateGroupName @.TemplateID, @.GroupNameID)
SET @.TemplateMethodID=(EXEC addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID)
EXEC addTemplateAnalyte(@.TemplateMethodID,@.AnalyteID)

I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.Never mind.
I changed the two lines with return params to:

EXEC @.TemplateGroupNameID=addTemplateGroupName @.TemplateID, @.GroupNameID
EXEC@.TemplateMethodID=addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID

Hope that will somebody else too...sql

Executing SP_SpaceUsed for tables in a other database with EXEC

Hi

I'm executing SP_SpaceUsed in a stored procedure like this :

Exec ('SP_SpaceUsed '+ @.table)

This works great but when i want to execute it for a table in a other
database i'm running in to troubles. Things i tried is this :

Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
syntax)

Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
(uncorrect syntax)

Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
syntax)

Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
(uncorrect syntax)

Could someone give me a clue

Thanx,

Hennie de Nooijerhdenooijer@.hotmail.com (Hennie de Nooijer) wrote in message news:<191115aa.0311040343.674519a6@.posting.google.com>...
> Hi
> I'm executing SP_SpaceUsed in a stored procedure like this :
> Exec ('SP_SpaceUsed '+ @.table)
> This works great but when i want to execute it for a table in a other
> database i'm running in to troubles. Things i tried is this :
> Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
> syntax)
> Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
> (uncorrect syntax)
> Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
> syntax)
> Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
> (uncorrect syntax)
> Could someone give me a clue
>
> Thanx,
> Hennie de Nooijer

exec('exec ' + @.database + '..sp_spaceused ' + @.table)

Simon

executing sp_changeownerdb

Hi,
I've a problem executing sp_changedbowner in my stored procedure which
is saved in the master's stored procedures.
The code fails after executing @.proc1. The errors says that i could not
find proc2.
Here's the code:
CREATE PROC usp_RestoreDB(
@.db_name varchar(10),
@.backup_location varchar(255),
@.login varchar(20)
)
AS
DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
SET @.proc1 = @.db_name + '..sp_fixusers'
SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
RESTORE DATABASE @.db_name
FROM DISK = @.backup_location
WITH REPLACE
Begin
EXEC @.proc1
EXEC @.proc2
ENDTry,
...
exec (@.proc1)
exec (@.proc2)
...
AMB
"Jason" wrote:

> Hi,
> I've a problem executing sp_changedbowner in my stored procedure which
> is saved in the master's stored procedures.
> The code fails after executing @.proc1. The errors says that i could not
> find proc2.
> Here's the code:
> CREATE PROC usp_RestoreDB(
> @.db_name varchar(10),
> @.backup_location varchar(255),
> @.login varchar(20)
> )
> AS
> DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
> SET @.proc1 = @.db_name + '..sp_fixusers'
> SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
> RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
> Begin
> EXEC @.proc1
> EXEC @.proc2
> END
>|||On Thu, 29 Sep 2005 16:21:18 +0200, Jason wrote:

>Hi,
>I've a problem executing sp_changedbowner in my stored procedure which
>is saved in the master's stored procedures.
>The code fails after executing @.proc1. The errors says that i could not
>find proc2.
>Here's the code:
>CREATE PROC usp_RestoreDB(
>@.db_name varchar(10),
>@.backup_location varchar(255),
>@.login varchar(20)
> )
>AS
>DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
>SET @.proc1 = @.db_name + '..sp_fixusers'
>SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
>RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
>Begin
>EXEC @.proc1
>EXEC @.proc2
>END
Hi Jason,
Try changing the logic for proc2 to
(...)
SET @.proc2 = @.db_name + '..sp_changedbowner'
(...)
EXEC @.proc2 @.loginame = @.login
(...)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Executing SP in MSSQL takes forever via JDBC?!

Hi all,
I need to execute a stored procedure in our database server, MS SQL Server
2000 and it takes forever...
I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
My code:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
cs.setString(1,"param1");
cs.setString(2,"param2");
long start = System.currentTimeMillis();
ResultSet rs = cs.executeQuery();
System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
ms");
...
Every time I execute this piece of code, it takes between 45-50 seconds...
I have tried to execute my SP from DBVisualizer(an app using the same JDBC
driver) and it is the same result. But, when I execute my SP from Query
Analyzer it takes less than a second?! I have also tried executing the SP
from TOAD for SQL Server, and then the execution time also is less than a
second. Why? What have I missed? Does anybody recognize this problem?
It is not me personally that has wriiten the SP, so I do not really know
what it does and how it looks, but I know that the SP I'm calling is calling
another SP that is creating a couple of temporary tables while it is
executing...
Greatfull for any suggestions...
Cheers
//Anders =)
anders.hedstrom wrote:

> Hi all,
> I need to execute a stored procedure in our database server, MS SQL Server
> 2000 and it takes forever...
> I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
> My code:
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> Connection conn = DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
>
> CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
> cs.setString(1,"param1");
> cs.setString(2,"param2");
Try these two things:
1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
2 -
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
Joe Weinstein at BEA

> long start = System.currentTimeMillis();
> ResultSet rs = cs.executeQuery();
> System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
> ms");
> ...
>
> Every time I execute this piece of code, it takes between 45-50 seconds...
> I have tried to execute my SP from DBVisualizer(an app using the same JDBC
> driver) and it is the same result. But, when I execute my SP from Query
> Analyzer it takes less than a second?! I have also tried executing the SP
> from TOAD for SQL Server, and then the execution time also is less than a
> second. Why? What have I missed? Does anybody recognize this problem?
> It is not me personally that has wriiten the SP, so I do not really know
> what it does and how it looks, but I know that the SP I'm calling is calling
> another SP that is creating a couple of temporary tables while it is
> executing...
> Greatfull for any suggestions...
> Cheers
> //Anders =)
|||
> Try these two things:
> 1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
> 2 -
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
> Joe Weinstein at BEA
Hi Joe,
thanx alot for your tip! This fixed my problem;
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
It would be quite interesting to know why it takes so long to execute the SP
when using CallableStatement and PreparedStatement...
By the way, the lowercase 's' on sendStringParametersAsUnicode made no
difference.
Once again Joe, thanx!!!
Cheers
//Anders =)

Executing SP From a Batch file

I was wondering how I can create a batch file that call a store procedure an
d
put the output on a file.Silver wrote:
> I was wondering how I can create a batch file that call a store
> procedure and put the output on a file.
Try osql.exe - the command-line options are documented in Books Online or by
typing osql /? at the command line.
John.|||Hi,
Call OSQL inside the command prompt
Entries inside the batch file will be
OSQL -Usa -Ppassword -S Servername -d Dbname -Qprocedurename -oc:\output.log
Thanks
Hari
SQL Server MVP
"Silver" <Silver@.discussions.microsoft.com> wrote in message
news:341FE12A-19E6-4A8B-8003-558797A637BF@.microsoft.com...
>I was wondering how I can create a batch file that call a store procedure
>and
> put the output on a file.

Executing SP for all results

I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.comLooping through resultsets with cursors is usually a bad idea and there
is normally a better set-based solution. Can you post your proc, your
select query and some schema so we can advise on a better approach
(maybe some test data & expected results too)?
*mike hodgson*
http://sqlnerd.blogspot.com
Doc Parker wrote:

>I would like to have my stored procedure executed for each item returned by
>a Select query
>
>Ex.
>Declare @.file_id varchar(5)
>Select @.file_id = file_id from GEN where this_value <> ''
>execute sp_mystored_procedure @.file_id
>go
>Currently it only works for one record
>How do I get it to do it for all returned records?
>The top select statement may return 1000 records, but only one pass is made
>through the stored procedure
>Hints Please
>Thanks
>Darrell
>darrellp@.btmcpa.com
>
>
>|||The procedure in question actually runs as part of an exsiting TRIGGER. I am
installing this TRIGGER into a table of a database and want to call it in o
rder to extract data all ready in the table. Otherwise it works great for re
cords currently being added. If I were to use a CURSOR to run this SP it wou
ld be a one time deal. Nothing permanent.
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uWo%23nF6JGHA.340
8@.TK2MSFTNGP12.phx.gbl...
Looping through resultsets with cursors is usually a bad idea and there is n
ormally a better set-based solution. Can you post your proc, your select qu
ery and some schema so we can advise on a better approach (maybe some test d
ata & expected results too)?
mike hodgson
http://sqlnerd.blogspot.com
Doc Parker wrote:
I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.com

executing SP

I have stored procedure with parameters.
Can I exec stored procedure somehow with result set of select statement, for
example:
exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
Or I must declare each parameter:
declare @.par1 int,@.par2 int,@.par3 int
SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
and then exec my procedure:
exec dbo.myProcedure @.par1,@.par2,@.par3
In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.
lp,S>> Can I exec stored procedure somehow with result set of select statement,
No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.
Anith|||To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>|||Hi
If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @. to the names!)
If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.
John
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>

Executing Procedures through LInked Server

I'm trying to execute a procedure through linked server using 4 part names.
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:

>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
|||I have already configured the other server for remote access and reconfigured
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:

> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigured
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
|||Does not work .
"Sue Hoegemeier" wrote:

> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
sql

Executing Procedures through LInked Server

I'm trying to execute a procedure through linked server using 4 part names.
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:

>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.|||I have already configured the other server for remote access and reconfigure
d
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:

> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigur
ed
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
>|||Does not work .
"Sue Hoegemeier" wrote:

> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
>

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) an
d
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...[vbcol
=seagreen]
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set)
and
> instead of returning the result set, just returning an empty result set, o
r
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"
|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>

Executing Procedure (OLEDB Session Object)

I'm executing a very simple Sotred Procedure from VB6 and I'm getting this error:

"Requested operation requires an OLE DB Session object, which is not supported by the current provider."

I executed it from Query Analyzer and Worked perfectly!

I hope you can help me with this now! :(How are you trying to execute the sp in vb6 - please post your code - including connection string ... ?sql

Executing Oracle Stored Procedure with output parameters using ADO.NET connection

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 DB\Microsoft 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.

Any help on this would be most appriciated.

hi Jason,

I got the same error. What driver are you using? I use .NET provider Oracle client data provider.

Can you also tell me how did you successfully invoke the stored proc using OLEDB for Oracle?

I have an sp with 1 input parameter of type Number and 14 output parameters some of type Number and some Varchar2.

Can you tell me the sql statement syntax to use and the parameter mapping to use. I know that the parameter name starts with 0,1,2...

Please help

thanks.

sandeep

sql

Executing Oracle Stored Procedure via SQL Server 2000 Linked Serve

All,
I am trying to execute a simple Oracle stored procedure from SQL Server
2000. The SP has no input or output parameters. I have created the linked
server correctly as I am able to execute Oracle functions just fine. I have
tried the following two methods to execute the SP, SKELETON:
{Call ORADB..MARCO.SKELETON()}
Execute ORADB..MARCO.SKELETON
Both methods result in the same error listed below:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SKELETON' on remote server 'ORADB'.
[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.]
Any ideas on how I can get this working properly?
Thanks,
MarcoHere is an old post by Umachandar. See if it helps:
http://tinyurl.com/7dxrr
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
> All,
> I am trying to execute a simple Oracle stored procedure from SQL Server
> 2000. The SP has no input or output parameters. I have created the
> linked
> server correctly as I am able to execute Oracle functions just fine. I
> have
> tried the following two methods to execute the SP, SKELETON:
> {Call ORADB..MARCO.SKELETON()}
> Execute ORADB..MARCO.SKELETON
> Both methods result in the same error listed below:
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'SKELETON' on remote server 'ORADB'.
> [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.]
>
> Any ideas on how I can get this working properly?
> Thanks,
> Marco
>

Executing Oracle Stored Procedure via SQL Server 2000 Linked S

Thanks for the link. I have a stored procedure in Oracle that was created a
long time ago by someone else. Without manipulating the stored proc at all,
I wanted to feed it the required input parameters via SQL Server and let it
do its magic. From what I gather, this is not possible? I must create the
proc within a package in Oracle?
"oj" wrote:

> Here is an old post by Umachandar. See if it helps:
> http://tinyurl.com/7dxrr
> --
> -oj
>
> "marco" <marco@.discussions.microsoft.com> wrote in message
> news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
>
>Yes. Creating a package wrapper is your ticket to get to oracle proc.
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:4653000F-F545-4044-9876-E113CD14AFE6@.microsoft.com...
> Thanks for the link. I have a stored procedure in Oracle that was created
> a
> long time ago by someone else. Without manipulating the stored proc at
> all,
> I wanted to feed it the required input parameters via SQL Server and let
> it
> do its magic. From what I gather, this is not possible? I must create
> the
> proc within a package in Oracle?
> "oj" wrote:
>

Executing N procedures in 1 Round trip

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @.Campaign, @.Contact 20 times how would I pass in the values for those 20 diffrent versions?

You could pass all the parameters to another stored procedure as a delimited list and parse them over there, create a loop, and call the stored proc in the loop.

|||I'm sure I could. I just thought I'd heard of something similar to ODP.Net's ArrayBinding syntax for SqlServer. I'm fairly sure I wasnt thinking about bulkcopy.|||

To my knowledge I dont think there is any standardized way to make multiple calls in one trip. Perhaps someone else here knows if there's any new feature in 2005. I havent been doing much development in 2005.

|||hrm...

Will the SQL Server provider let me execute blocks of t-sql?

eg something like:

AddContact(@.C1, @.U1);
AddContact(@.C2, @.U2);
AddContact(@.C3, @.U3);
...
AddContact(@.CN, @.UN);
go;|||

Yes, however, you must specify that the call type is text, not stored procedure, and you must properly format your calls like:

EXECUTE AddContact @.C1,@.U1
EXECUTE AddContact @.C2,@.U2
...
GO

|||So what is the "proper" format for sp calls in a 'anonymous block'.

executing DTS Package from stored procedure

There is a DTS package that work when when it is executed directly
from Enterprise Manager or dtsrun utility.
But it does not work when it is executed from a stored procedure.
Do you have any idea?How are you executing the DTS package from the proc? What does "does not
work" mean? Turn on logging (under Package Properties) to see any error
messages.
David Portas
SQL Server MVP
--|||Following is the code of proc that call DTS.
When I run DTS package directly from Enterprise Manager, it works as I want
(185 rows added).
But when I use following procedure, there is no row added.
CREATE PROC [dbo].[Usp_CFDBE13ExecuteDTSPkg]
@.PkgName varchar(255)
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int
--1. Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
--Create Package object failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG, @.PkgName
RETURN 1
END
--2. Load the Pkg
EXEC @.hr = sp_OAMethod @.oPKG,'LoadFromSqlServer', NULL,
@.ServerName='', @.PackageName=@.PkgName, @.Flags=256
IF @.hr <> 0
BEGIN
-- LoadFromSQLServer failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--3.Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
--Execute failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--4.Check Pkg Errors
--EXEC @.ret=spDisplayPkgErrors @.oPKG --Will do it later
--5.Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
--UnInitialize failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--6.Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
RETURN 0
GO|||If you haven't already done so, turn on logging in the package so that
you can trap errors there and determine that the package does run. The
package execute method alone won't show up package errors.
Using a blank server name in the LoadFromSQLServer method defaults to
the default server instance _not_ to the instance in which your code is
executing. If you are targeting a non-default instance you must specify
the name. Better still, specify the name anyway. You can use
SERVERPROPERTY('SERVERNAME') to retrieve the name of the current
instance if you need to.
Be aware that your package will execute in the security context of the
local or domain account configured for the SQL Server service. That
account must therefore have access to any files or other resources
required by the DTS package.
Finally, make sure that your package uses fully qualified names for any
files that it accesses. The drive mappings available when you execute
locally in Enterprise Manager may not be available on the server.
David Portas
SQL Server MVP
--|||Thank you very much.
I got it.