Xp_cmdshell Return Different Value On Different SQL

Apr 8, 2004

Hi,
i tried execute script

declare @r int
exec @r=master..xp_cmdshell 'dir c:labla.txt'
select @r as 'RESULT'

on SQL7 SP4 (WinNT4), SQL2000(W2000) and SQL2000 SP3 (W2003) but i got different result.

There was result 2 for SQL7 and result 1 for SQL2000.

xp_cmdshell would return only 0(success) and 1(failure). But what does it mean result 2 in case SQL7?

Is there any bug in SQL7?

Thank you for your answer.

View 3 Replies

Xp_cmdshell Always Return 'null'

Jun 14, 2006

I have this program that I want to invoke from SQLServer funny thing is I can invoke this from command line but could not get it to work via 'xp_cmdshell' on this particular server. When I invoke run the following from SQLServer Query Analyser I always get 'NULL' returned.
I tried just running EXEC master..xp_cmdshell 'dir ' and it worked, so I am certain it is not a SQL rights problem.

FYI, I executed the same code on another machine and it worked. I just cannot figure out why it is not working on this one server?
Can anyone help? thanks

----

DECLARE @command varchar(500)
SET @command = 'c:ServerClientabc.exe BBB/XX B,AAAA'
EXEC master..xp_cmdshell @command

View 4 Replies View Related

Insert Trigger And Xp_cmdshell With Carriage Return Text

Jul 20, 2005

I have created the following trigger:CREATE TRIGGER [CreateFile] ON OutputTableFOR INSERTASDeclare @filename nvarchar(35)Declare @filecontents nvarchar(2000)Declare @strcmdshell varchar(150)SELECT @filecontents = OutputText FROM INSERTEDSELECT @filename = 'c:' + OutputFileName FROM INSERTEDSELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filenameexec master..xp_cmdshell @strcmdshellIt works fine as long as the column OutputText has no carriagereturns.I have used my Access2K front end to dump about ten lines of text intoOutputText, each line broken by vbcrlf so that OutputText looks like:line1testline2textline3textetc.The trigger won't fire with this text.Is there something I can do to remedy this?lq

View 4 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008

I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.

CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007

I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?

I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).

Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View 5 Replies View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, good day,

i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(

anyone know how to handle the problem when SP return no data ?

thanks in advance

View 1 Replies View Related

DTS & Xp_cmdshell

May 15, 2001

Hello,

I am trying to create a directory containing the date and then copy all the files in the current directory to it.

If I run the T-SQL script found below within Query Analyser it works fine (directory is created and files are copied in to it).

But if I run it as SQL task within DTS, only the directory is created. The files are not copied in to it!?!

I thought it maybe a permissions problem, but the SQL Server/Agent account is a local administrator and has sysadmin role with SQL Server.

I would be grateful of any assistance in this problem.

Thanks in advance,
Chris.

SQL:

declare @directoryname varchar(200)
declare @doscmd varchar(255)

select @directoryname = 'e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char))

select @doscmd = 'mkdir ' + @directoryname

exec master..xp_cmdshell @doscmd

select @doscmd = 'copy e:Audit_table_archive*.* e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char)) + '*.*'

exec master..xp_cmdshell @doscmd

View 1 Replies View Related

Ftp Through Xp_cmdshell Help?

Jul 9, 2001

does anyone know how to execute a FTP command through sql server 7? I am creation a table and need to ftp the result set. I have it currently creation the table, turning it into a CSV txt file and placing it in a directory. I have to then manually ftp the txt file. I want to automate this process but I cant get the FTP command to execute throught the xp_cmdshell. It will work at the DOS prompt though so I know the syntax is correct. Any sugetsions would be appreciated.
-Nathan