Getting the OUT parameter of stored proc in .cmd file.

Hi,
Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
the output parameter of stored proc in .cmd command. Based on the value i
need to terminate the program. Below are the details.
In the .cmd file we use the below syntax for truncating a table.
isql -E -S%1 -d%2 -Q"truncate table tb_mast"
Right now this needs to be replaced by calling a stored procedure by passing
the table name and get the return value to check sucess/failure through the
output parameter. If any error the program (.cmd) should terminate.
Store procedure is ready and it is working fine. Procedure will be like
sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
parameter2 will be set as 0, else 1.
How do I call the procedure in the .cmd file and check the success/failure b
y
getting the output parameter?
Please help me in this regard. Hope my explanation is clear.
Thanks in advance.
Vivek.
rHi,
May not be possible directly. Why do you want to have a command file to do
this?
You could truncate table inside the stored procedure by implementing a
condition right.If there is any technical difficulty please write back.
Thanks
Hari
SQL Server MVP
"rvivek2000" wrote:

> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to acces
s
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passi
ng
> the table name and get the return value to check sucess/failure through th
e
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure
by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
> --
> r
>|||rvivek2000 wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to acces
s
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passi
ng
> the table name and get the return value to check sucess/failure through th
e
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure
by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
>
Have a look at the EXIT command that can be used with isql. You can use
it to set the ERRORLEVEL based the results of a query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Getting the OUT parameter of stored proc in .cmd file.

Hi,
Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
the output parameter of stored proc in .cmd command. Based on the value i
need to terminate the program. Below are the details.
In the .cmd file we use the below syntax for truncating a table.
isql -E -S%1 -d%2 -Q"truncate table tb_mast"
Right now this needs to be replaced by calling a stored procedure by passing
the table name and get the return value to check sucess/failure through the
output parameter. If any error the program (.cmd) should terminate.
Store procedure is ready and it is working fine. Procedure will be like
sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
parameter2 will be set as 0, else 1.
How do I call the procedure in the .cmd file and check the success/failure by
getting the output parameter?
Please help me in this regard. Hope my explanation is clear.
Thanks in advance.
Vivek.
--
rHi,
May not be possible directly. Why do you want to have a command file to do
this?
You could truncate table inside the stored procedure by implementing a
condition right.If there is any technical difficulty please write back.
Thanks
Hari
SQL Server MVP
"rvivek2000" wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passing
> the table name and get the return value to check sucess/failure through the
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
> --
> r
>|||rvivek2000 wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passing
> the table name and get the return value to check sucess/failure through the
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
>
Have a look at the EXIT command that can be used with isql. You can use
it to set the ERRORLEVEL based the results of a query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Getting records and output parameter from ASP

I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset.

I can get either but not both. (ie. if there is a select then there is no output parameter)

The stored procedure is:

ALTER PROCEDURE test
(
@.Msgs nvarchar(150) OUTPUT
)

As
declare @.selCount int
SELECT folderLocation, externalDocsID from externalDocs

set @.selCount = @.@.ROWCOUNT
IF @.@.ROWCOUNT = 0
BEGIN
SET @.Msgs = 'No folders meet this selection criteria'
RETURN
END
else
BEGIN
SET @.Msgs = @.selCount
END
return

-------------------------

the asp code is

Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object
Dim rstStoredProc ' Recordset object
Dim folderText
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open db
' get the correct records for a page
Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Create Command object we'll use to execute the SP
cmdStoredProc.ActiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.CommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Refresh

' ---- SET PARAMETERS --GET A PAGE WORTH OF RECORD---------------------
set prop=ADODB.Parameter
cmdStoredProc.Parameters("@.functionCode").Value = "L" 'functionCode
cmdStoredProc.Parameters("@.customerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.Parameters("@.searchcriteria").Value = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.Parameters("@.excludeDefined").Value = "Y" '
end if
set rstStoredProc = cmdStoredProc.Execute
'

response.write("msgs=" & cmdStoredProc("@.Msgs")) ' <<<<<<< THIS DOESN'T WORK------------------------------

I can enumerate through the recordset but the output parameter is blank.

Any Ideas

Thanks

Quote:

Originally Posted by Mike Lester

I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset.

I can get either but not both. (ie. if there is a select then there is no output parameter)

The stored procedure is:

ALTER PROCEDURE test
(
@.Msgs nvarchar(150) OUTPUT
)

As
declare @.selCount int
SELECT folderLocation, externalDocsID from externalDocs

set @.selCount = @.@.ROWCOUNT
IF @.@.ROWCOUNT = 0
BEGIN
SET @.Msgs = 'No folders meet this selection criteria'
RETURN
END
else
BEGIN
SET @.Msgs = @.selCount
END
return

-------------------------

the asp code is

Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object
Dim rstStoredProc ' Recordset object
Dim folderText
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open db
' get the correct records for a page
Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Create Command object we'll use to execute the SP
cmdStoredProc.ActiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.CommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Refresh

' ---- SET PARAMETERS --GET A PAGE WORTH OF RECORD---------------------
set prop=ADODB.Parameter
cmdStoredProc.Parameters("@.functionCode").Value = "L" 'functionCode
cmdStoredProc.Parameters("@.customerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.Parameters("@.searchcriteria").Value = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.Parameters("@.excludeDefined").Value = "Y" '
end if
set rstStoredProc = cmdStoredProc.Execute
'

response.write("msgs=" & cmdStoredProc("@.Msgs")) ' <<<<<<< THIS DOESN'T WORK------------------------------

I can enumerate through the recordset but the output parameter is blank.

Any Ideas

Thanks

i believe RecordSet have a property called RecordCount...

example:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM Customers"
rs.Open sql,conn

if rs.Supports(adApproxPosition)=true then
i=rs.RecordCount
response.write("The number of records is: " & i)
end if

rs.Close
conn.Close
%>

Getting parameter's user-defined datatype?

Hey,
Is there any way to get routine's parameter's user-defined datatype from
system tables/views?
I see there is a corresponding field in INFORMATION_SCHEMA.PARAMETERS view,
but it is said to be for future use...
Thank You in advance,
MartinThis should server as a starting point:
SELECT *
FROM syscolumns as sc
INNER JOIN systypes AS st on sc.xtype = st.xtype
where sc.id IN (SELECT id FROM sysobjects WHERE type = 'P')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Martin H." <Martin H.@.discussions.microsoft.com> wrote in message
news:BA5DD35C-2FAD-473D-9524-0915FB29AF30@.microsoft.com...
> Hey,
> Is there any way to get routine's parameter's user-defined datatype from
> system tables/views?
> I see there is a corresponding field in INFORMATION_SCHEMA.PARAMETERS view
,
> but it is said to be for future use...
> Thank You in advance,
> Martin|||Martin H. wrote:
> Hey,
> Is there any way to get routine's parameter's user-defined datatype from
> system tables/views?
> I see there is a corresponding field in INFORMATION_SCHEMA.PARAMETERS view
,
> but it is said to be for future use...
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
This works:
select parameter_name, data_type
from information_schema.parameters
where specific_name = 'procedure name'
Substitute your procedure name in the WHERE clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQlJLyIechKqOuFEgEQKibgCcD46kIxMkKQya
73lwxg0DrXcPIOoAn0Zr
W/GAuqaueyUtevN4hQnU1T0D
=/yvi
--END PGP SIGNATURE--

Getting Parameter Values - Need Perspective

Have several server reports (RDLs, not RDLCs) that use report parameters (text boxes, dropdowns, etc). Is it possible to view these reports in web pages using the report viewer control and use the report parameters as they were originally intended?

From what I've experienced so far, reports behave as if parameter data was not entered. I've tried to extract parameter values, but only know how to find the parameter metadata.

Do I need to hide the report parameters and substitute them with visible web controls instead? I know how to programmatically set the report parameters, just don't know the best, if not the only way to extract user input.

See Bill Vaughn's reply in MSDN Forums >> Visual Studio >> Visual Studio Report Controls >> Getting Parameter Values - Need Perspective.

Getting OUTPUT parameter in an OLE DB Command

Is it possible to use stored procedure with output parameter and retrieves the values of that output parameter, in an OLE DB Command within a Data Flow?

What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.

Rather than using the OLE DB Command to get the identity column value (which is going to be significantly slower than inserting through an OLE DB Destination), have you considered generating your own keys in the data flow? There's an example here: http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/ and here: http://www.sqlis.com/37.aspx, plus numerous posts on the forum.|||

Hi jwelch. I will be perusing those sites soon. Regarding key generation, I'm relying on IDENTITY columns so I'm afraid this is not an option for me unless those sites will prove otherwise. Thanks for the reply

|||I discovered that this is not the most optimized way of achieving my goal so I abandoned this one. Unfortunately, I encountered another showstopper which you can find more about here --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1877437&SiteID=1|||

u_r_twisted wrote:

Regarding key generation, I'm relying on IDENTITY columns so I'm afraid this is not an option for me unless those sites will prove otherwise. Thanks for the reply

Gosh, I HATE identity columns. They are a pain in the rear to work with.... Nice for some things, but when doing ETL they stink. When you look at the link at my site above (ssistalk.com) that John linked to, I have a small discussion on why using identity columns is not preferred.