• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2832
  • Last Modified:

OSQL with stored procedure output parameter

Hi,

I tried to use osql to execute stored procedure with some input parameters and one output parameter, how I can do this.

thanks
0
changeexpert
Asked:
changeexpert
  • 3
  • 2
1 Solution
 
LowfatspreadCommented:
post what your doing...

but

osql -SserverName -ddatabasename -E -iinputfile.sql -ooutputfile.sql

and
inputfile.sql
would look something like
/*
 run the xxx proc
*/
Declare @Outparm Varchar(8000)
Declare @RC Int

Exec @RC = ServerName.DabataseName.Owner.StoreprocedureName  'INPUT1','INPUT2',@Output Output

Print @Outparm

/* return sp execution status to DOS */
EXIT (Select @RC)



...

if not using a trusted connection then replace the -E with -Uuserid -Ppassword

 

0
 
changeexpertAuthor Commented:
Hi

Tried to run this and "stp_s_StoredProc" will accept 2 input parameters and 1 output parameter, the I will use the output parameter value as variable for another query.  This line is included in another stored proc.  Can I do this way instead of using inputfile/outputfile?

exec master..xp_cmdshell 'osql /U "SA" /P "ABC" /S "ServerName" /Q "stp_s_StoredProc"

Thanks
0
 
LowfatspreadCommented:
yes you can specify the query to be executed via the Q (Query parameter instead)
but you not going to get the output variable that way....

what are you actually trying to do...

why do you need to run this in batch...

you maybe better off constructing a wrapper stored procedure to call the ones you want to invoke...


0
 
changeexpertAuthor Commented:
Hi,

I have stored proc that sit on SQL Server A and on SQL Server B will schedule a job to execute stored proc B on SQL B and inside this stored proc will execute stored proc A on SQL A and use the output parameter value in stored proc B.

I know I can use linked server to do execute stored proc A remotely.  However, people do not like the idea of "Linked" two servers for security reason.

Thanks
0
 
LowfatspreadCommented:
but you've got a big(ger) security hole here anyway surely by having procedure with
SA user id and passwords included within them.....

wouldn't you be better scheduling the stored procedure on Server A
and then getting it to pass its output to server B ....

perhaps even doing the data transfer via replication...


or have b regularly read (for the file A produces)  
and process it that way via some temporary tables....


what is the data?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now