ERROR in xp_cmdshell syntax

hi!
I'm usng SQL server and i want to rename a file using the xp_cmdshell.

here's my code:

declare @.path varchar (20)
declare @.filename varchar (20)
declare @.new varchar (20)
declare @.cmd varchar (20)

set @.path = 'E:\folder1\folder2\folder3\'
set @.filename = @.path+'filename.txt'
set @.new = 'newfilename.txt'
set @.cmd = 'REN '+@.filename+' '+@.new

exec master..xp_cmdshell @.cmd

--
after running, i get this error:
The syntax of the command is incorrect.

Need technical help. please advise asap.
thnx again.hi
i am not sure but i think u have to delclare another variable such as @.result and then
execute @.result=master..xp_cmdshell @.cmd
i hope it work|||This will help you. first declare the parameters and use the following code.

set @.backup_filePartialname = @.diff_backup_path+@.db_name + '_*.bak'
set @.backup_filename = @.db_name + '.bak'
set @.dos_cmd = 'RENAME ' + @.backup_filePartialname + ' ' + @.backup_filename

EXEC @.dos_result = master.dbo.xp_cmdshell @.dos_cmd, NO_OUTPUT

Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error In Replace With Spaces

Hi !
I'm trying to put a space after . and , in varchar fields.

I've tried

UPDATE Resultados
SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')

but nothing happened

What's wrong ??

Thanks and sorry if my english isnt so good.

Eduardo (from Argentina)This works fine for me:

drop table test2
create table test2(f1 varchar(1000))
insert test2 select '1,2,3,4,5,6,7'
select REPLACE(f1, ',', ', ') from test2

update test2 set f1=REPLACE(f1, ',', ', ')
select * from test2|||It seems to work fine for me, using:SELECT Replace('1,2,3,4,5,6,7,8,9', ',', ', ')Maybe the character you are looking for only looks like it is a comma.

-PatP|||Trabajos para m

DECLARE @.RespuestaAbierta varchar(50)
SELECT @.RespuestaAbierta = 'Brett,Kaiser'
SELECT REPLACE(@.RespuestaAbierta, ',', ', ')|||I can't understand,

this works fine ...

drop table test2
create table test2(f1 varchar(4000))
insert test2 select '1,2,3,4,5,6,7'
insert test2 select 'ninguna en especial,me la recomendo mi hermano.'
select REPLACE(f1, ',', ', ') from test2
update test2 set f1=REPLACE(f1, ',', ', ')

select * from test2
1, 2, 3, 4, 5, 6, 7
ninguna en especial, me la recomendo mi hermano.

but this one doesn't

select respuestaabierta,
REPLACE(respuestaabierta, ',', ', ')
from resultados
where idresultado = 1244591

ninguna en especial,me la recomendo mi hermano. ninguna en especial,me la recomendo mi hermano.

The field respuestaabierta is varchar(4000) null

Thanks for your answers !|||I remeber...the answer is that the comma in your column is not a comma!

That bugged us for a while once...

select char(130), ascii(',')

Do This

DECLARE @.x varchar(80)
SELECT @.x = 'This is not a comma' + CHAR(130) + ' even though it looks like one'
SELECT @.x
-- Then do

select CHARINDEX(CHAR(130),respuestaabierta)
from resultados
where idresultado = 1244591

-- If it's not 0, then that's what it is...

And let us know...|||Thanks, Brett

but that's not the problem :

select CHARINDEX(CHAR(130),respuestaabierta) , CHARINDEX(CHAR(44),respuestaabierta)
from resultados
where idresultado = 1244591
Output:

0 20

I've noticed that the table has

Collation = SQL_Latin1_General_CP1_CI_AS

and, with Collation = databasedefault works fine

what does it mean ?

Thanks|||Damn...I thought that might have been it...

Can you post the DDL of your table?

Do you know how to do that in Enterprise manager?|||Bred,
thanks for everything, I gave up
I did a stored procedure wich updates row by row (instead a single update command).
Thanks again. Eduardo (from Argentina)|||Too bad...I was gonna suggest that you find the position and say

SELECT ASCII(SUBSTRING(col1,n,1))

to see what the damn thing was...

what code did you use?

Error in cross tab statement

Hi,

I have a coding which state as below:

CREATE PROCEDURE [dbo].[crossTab]
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100)
AS

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE ' + @.pivot + ' Is Not Null')

SELECT @.sql='' , @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN ' + @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I just woud like why it come out error stated that

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

when i key in

exec crossTab 'select dbo,fgo,rgt from Table A', 'sum(fgo)', 'rgt', 'Table A'

Please help me

ThanxYet another attempt at a universal cross-tab function...

Does your code error out if you comment out the "EXEC (@.select)" line? If not, then try replacing it with "SELECT @.select" or "PRINT @.select" to see exactly what code is being executed that is causing the error.