Question on SQL job step invoking .cmd

I have a simple SQL job that is scheduled to invoke a .cmd to perform a
file copy task.
How can I get SQL job to report to correct status when the copy task
fails, such as due to connectivity issue, or file not found, and etc?
thanks.One option is to capture the return code and do a raiserror
if the return code <> 0. Along the lines of:
DECLARE @.ret int
EXEC @.ret = master..xp_cmdshell 'copy ...etc' , NO_OUTPUT
IF @.ret <> 0 RAISERROR('File copy error',16, 1)
-Sue
On 28 Jul 2006 08:23:49 -0700, "Rose" <rose.say@.gmail.com>
wrote:

>I have a simple SQL job that is scheduled to invoke a .cmd to perform a
>file copy task.
>How can I get SQL job to report to correct status when the copy task
>fails, such as due to connectivity issue, or file not found, and etc?
>thanks.

Question on SQL job step invoking .cmd

I have a simple SQL job that is scheduled to invoke a .cmd to perform a
file copy task.
How can I get SQL job to report to correct status when the copy task
fails, such as due to connectivity issue, or file not found, and etc?
thanks.One option is to capture the return code and do a raiserror
if the return code <> 0. Along the lines of:
DECLARE @.ret int
EXEC @.ret = master..xp_cmdshell 'copy ...etc' , NO_OUTPUT
IF @.ret <> 0 RAISERROR('File copy error',16, 1)
-Sue
On 28 Jul 2006 08:23:49 -0700, "Rose" <rose.say@.gmail.com>
wrote:
>I have a simple SQL job that is scheduled to invoke a .cmd to perform a
>file copy task.
>How can I get SQL job to report to correct status when the copy task
>fails, such as due to connectivity issue, or file not found, and etc?
>thanks.sql

question on ASP calling stored procedure

I'm trying to pass a recordset from an stored procedure to an ASP.
Here's the ASP code:
Dim cnn, cmd, rs
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB;Driver={SQL Server};Server=..."
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cnn
cmd.CommandText = "SP_Name"
cmd.CommandType = adCmdStoredProc
Set rs = cmd.Execute
if rs.bof or rs.eof then
response.write "eof<BR>"
end if
This works fine with the following stored procedure:
CREATE procedure get_recordset
as
select * from mytable
GO
But when I try to do additional stuff in the stored procedure before
selecting, like:
CREATE procedure get_recordset
as
declare @.TableVar TABLE (
field1 varchar(20) null,
field2 decimal(10, 2) null
)
insert into @.TableVar values('hello', 12.2)
select * from @.TableVar
The result on the ASP when asking for BOF/EOF is: Operation is not
allowed when the object is closed. Yet, if I run that stored procedure
from query analyzer, I get the record back. Am I doing something
wrong?
thanks,
JoeUse SET NOCOUNT ON in the stored procedure.
CREATE PROCEDURE dbo.doSomething
AS
BEGIN
SET NOCOUNT ON
-- do stuff
END
GO
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<joseph.fanelli@.vba.va.gov> wrote in message
news:1114097950.793760.84800@.g14g2000cwa.googlegroups.com...
> I'm trying to pass a recordset from an stored procedure to an ASP.
> Here's the ASP code:
> Dim cnn, cmd, rs
> Set cnn = Server.CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB;Driver={SQL Server};Server=..."
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = cnn
> cmd.CommandText = "SP_Name"
> cmd.CommandType = adCmdStoredProc
> Set rs = cmd.Execute
> if rs.bof or rs.eof then
> response.write "eof<BR>"
> end if
> This works fine with the following stored procedure:
> CREATE procedure get_recordset
> as
> select * from mytable
> GO
> But when I try to do additional stuff in the stored procedure before
> selecting, like:
> CREATE procedure get_recordset
> as
> declare @.TableVar TABLE (
> field1 varchar(20) null,
> field2 decimal(10, 2) null
> )
> insert into @.TableVar values('hello', 12.2)
> select * from @.TableVar
> The result on the ASP when asking for BOF/EOF is: Operation is not
> allowed when the object is closed. Yet, if I run that stored procedure
> from query analyzer, I get the record back. Am I doing something
> wrong?
> thanks,
> Joe
>|||If you don't want to change stored procedure ( wich is better solution )
you can add line of code after cmd.Execute :
Set rs = rs.NextRecordset
...
and then check for EOF
Hope it helps
"joseph.fanelli@.vba.va.gov" wrote:

> I'm trying to pass a recordset from an stored procedure to an ASP.
> Here's the ASP code:
> Dim cnn, cmd, rs
> Set cnn = Server.CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB;Driver={SQL Server};Server=..."
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = cnn
> cmd.CommandText = "SP_Name"
> cmd.CommandType = adCmdStoredProc
> Set rs = cmd.Execute
> if rs.bof or rs.eof then
> response.write "eof<BR>"
> end if
> This works fine with the following stored procedure:
> CREATE procedure get_recordset
> as
> select * from mytable
> GO
> But when I try to do additional stuff in the stored procedure before
> selecting, like:
> CREATE procedure get_recordset
> as
> declare @.TableVar TABLE (
> field1 varchar(20) null,
> field2 decimal(10, 2) null
> )
> insert into @.TableVar values('hello', 12.2)
> select * from @.TableVar
> The result on the ASP when asking for BOF/EOF is: Operation is not
> allowed when the object is closed. Yet, if I run that stored procedure
> from query analyzer, I get the record back. Am I doing something
> wrong?
> thanks,
> Joe
>|||Aaron/Sergey,
Thanks, both of your suggestions worked.