OUTPUT Clause SQL Server 2005

Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT query
Hi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||?
|||?
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegr oups.com...
> ?
>
|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.
|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegr oups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>
|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query
|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegr oups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>
|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.

OUTPUT Clause SQL Server 2005

Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.|||Adarsh, I think you are confused about the OUTPUT clause. Both the OUTPUT
clause and the WHERE clause are part of the same UPDATE statement. The
following will update only those rows with ProposalID=9, not all rows in the
table. Only the before image of the updated rows will be inserted into
@.OldProposal.
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||The update has a WHERE clause. Just as usual. But the OUTPUT clause doesn't have a WHERE clause.
This is what it should look like:
UPDATE tblname
OUTPUT ...
WHERE...
The WHERE clause above belong to the UPDATE statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||#$%&*(..... Oh ok.... I got it now..;)
Thanks a lot Dan, Tibor and Uri...

OUTPUT Clause SQL Server 2005

Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books
Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-
08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.

out put to text file issue

DECLARE @.cdr_date datetime
SET @.cdr_date = getdate()-1
SET NOCOUNT ON
select *
from call
where year(starttime)=year(@.cdr_date) and month(starttime)=month(@.cdr_date) and day(starttime)=day(@.cdr_date)

I want to run this query with output file like g_YYMMDD ( where YYMMDD will come from @.cdr_date

How can I do thatFor this to work reliably you need to create a couple of procedures:

create proc dbo.sp_test
@.cdr_date datetime = current_timestamp
as
SET NOCOUNT ON
select *
from call
where year(starttime)=year(@.cdr_date) and month(starttime)=month(@.cdr_date) and day(starttime)=day(@.cdr_date)
return (0)
go

Then create another procedure that will create the output file:

create proc dbo.sp_bcp_queryout as
@.cdr_date datetime = current_timestamp
as
declare @.cmd varchar(8000)
--assuming the db name is MYDB, server name - MYSERVER
set @.cmd = 'bcp "exec MYDB.dbo.sp_test ' + char(39) +
convert(char(10), @.cdr_date, 101) + char(39) + '" queryout D:\g_' +
convert(char(6), @.cdr_date, 12) + ' -S MYSERVER -T -c'

exec master.dbo.xp_cmdshell @.cmd
return (0)
go|||Thank you very much|||now I get following error

SQLState = 37000, NativeError = 8146
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure sp_call_get_cdr has no parameters and arguments were supplied.
NULL|||At what point are you getting the error? I also recommend to use "-e Error-ExceptionFile.ERR" on BCP so that you can check what record(-s) caused the error.|||Thanks for the quick reply

I got it. it works now. the only quesitons remains is that if I want to create csv file can I do that with column name as headers.

please advice.

thanks for your help in advance|||Yes you can, but you'll need to switch from BCP to OSQL.|||so can you please be specific. which command I need to put|||You should be able to create a CSV with BCP by using Column terminators:
8.0
4
1 SQLSMALLINT 0 2 "" 1 smallintField ","
2 SQLNCHAR 2 100 "'" 2 charField1 "',"
3 SQLNCHAR 2 100 "'" 3 charField2 "',"
4 SQLDATETIME 0 8 "'" 4 datetimeField "',"

osql -o

I would like to create a dynamic output file with the following code:
Declare @.cmd varchar(1000)
Declare @.filepath varchar(1000)
Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
mm/dd/yy appended to the filename then .csv extension
Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
2000' >>>> here is where I would like the new filename to be used.
Exec master..xp_cmdshell @.cmd
Is there also anyway to log this to a file to track execution ?
Thanks...Hi,
you might be interested in that:
http://www.databasejournal.com/feat...cle.php/3386661
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hoosbruin" <Hoosbruin@.Kconline.com> schrieb im Newsbeitrag
news:BNGdnQ_yUIxBcuvfRVn-1Q@.kconline.com...
>I would like to create a dynamic output file with the following code:
>
> Declare @.cmd varchar(1000)
> Declare @.filepath varchar(1000)
> Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
> mm/dd/yy appended to the filename then .csv extension
> Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
> 2000' >>>> here is where I would like the new filename to be used.
> Exec master..xp_cmdshell @.cmd
>
> Is there also anyway to log this to a file to track execution ?
>
> Thanks...
>
>
>

osql

Hello
I use command OSQL and i have in output file strong character when i use the command xp_cmdshell
test.sql
DECLARE @.cmd sysname, @.var sysname
SET @.var =3D 'd:\mssql7\data'
SET @.cmd =3D 'dir ' + @.var
EXEC master..xp_cmdshell @.cmd
I use this osql osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o backup.log -b
and the oupput file :backup.log
there is stong character like '=FF' , R‚p
WHY ?
R‚pertoire de d:\mssql7\data
NULL
12/09/03 12:05 <DIR> .
12/09/03 12:05 <DIR> ..
26/09/03 19:29 2=FF097=FF152 Coffre.ldf
26/09/03 19:29 2=FF097=FF152 Coffre.mdf
27/08/03 14:19 2=FF886=FF721 coffre.zip
16/09/03 14:05 1=FF048=FF576 Etiquettes_Data.MDF
16/09/03 14:05 12=FF320=FF768 master.mdf
16/09/03 14:05 5=FF308=FF416 mastlog.ldf
16/09/03 14:05 1=FF048=FF576 model.mdf
16/09/03 14:05 786=FF432 modellog.ldf
16/09/03 14:05 8=FF388=FF608 msdbdata.mdf
16/09/03 14:05 786=FF432 msdblog.ldf
16/09/03 14:05 1=FF048=FF576 northwnd.ldf
16/09/03 14:05 3=FF801=FF088 northwnd.mdf
16/09/03 14:09 112=FF197=FF632 oscar_Donn‚es.MDF
26/09/03 19:29 3=FF879=FF731=FF200 oscar_Donn‚es2.MDF
26/09/03 19:29 112=FF132=FF096 oscar_Donn‚es3.MDF
16/09/03 14:09 1=FF310=FF720 oscar_Journal.LDF
26/09/03 19:29 1=FF310=FF720 oscar_Journal2.LDF
26/09/03 19:29 1=FF310=FF720 oscar_Journal3.LDF
16/09/03 14:05 1=FF835=FF008 pubs.mdf
16/09/03 14:05 786=FF432 pubs_log.ldf
25/09/03 15:48 63=FF176=FF704 TEMPDB.MDF
16/09/03 14:47 786=FF432 TEMPLOG.LDF
24 fichier(s) 4=FF216=FF196=FF161 octets
491=FF745=FF280 octets libres
(31 lignes affect‚es)This is possibly caused by your code page settings. Run master..xp_cmdshell
'chcp' to see what your settings are for xp_cmdshell (my guess is code page
437), as SQL Server will probably assume Lation 1 and displays different
characters than expected. You could try the -u (Unicode) osql switch to work
around it.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"brinster" <sbrinster@.seafrance.fr> wrote in message
news:1a4601c3866f$b9824500$a101280a@.phx.gbl...
Hello
I use command OSQL and i have in output file strong
character when i use the command xp_cmdshell
test.sql
DECLARE @.cmd sysname, @.var sysname
SET @.var = 'd:\mssql7\data'
SET @.cmd = 'dir ' + @.var
EXEC master..xp_cmdshell @.cmd
I use this osql
osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o
backup.log -b
and the oupput file :backup.log
there is stong character like 'ÿ' ,
R‚p
WHY ?
R‚pertoire de d:\mssql7\data
NULL
12/09/03 12:05 <DIR> .
12/09/03 12:05 <DIR> ..
26/09/03 19:29 2ÿ097ÿ152 Coffre.ldf
26/09/03 19:29 2ÿ097ÿ152 Coffre.mdf
27/08/03 14:19 2ÿ886ÿ721 coffre.zip
16/09/03 14:05 1ÿ048ÿ576 Etiquettes_Data.MDF
16/09/03 14:05 12ÿ320ÿ768 master.mdf
16/09/03 14:05 5ÿ308ÿ416 mastlog.ldf
16/09/03 14:05 1ÿ048ÿ576 model.mdf
16/09/03 14:05 786ÿ432 modellog.ldf
16/09/03 14:05 8ÿ388ÿ608 msdbdata.mdf
16/09/03 14:05 786ÿ432 msdblog.ldf
16/09/03 14:05 1ÿ048ÿ576 northwnd.ldf
16/09/03 14:05 3ÿ801ÿ088 northwnd.mdf
16/09/03 14:09 112ÿ197ÿ632 oscar_Donn‚es.MDF
26/09/03 19:29 3ÿ879ÿ731ÿ200 oscar_Donn‚es2.MDF
26/09/03 19:29 112ÿ132ÿ096 oscar_Donn‚es3.MDF
16/09/03 14:09 1ÿ310ÿ720 oscar_Journal.LDF
26/09/03 19:29 1ÿ310ÿ720 oscar_Journal2.LDF
26/09/03 19:29 1ÿ310ÿ720 oscar_Journal3.LDF
16/09/03 14:05 1ÿ835ÿ008 pubs.mdf
16/09/03 14:05 786ÿ432 pubs_log.ldf
25/09/03 15:48 63ÿ176ÿ704 TEMPDB.MDF
16/09/03 14:47 786ÿ432 TEMPLOG.LDF
24 fichier(s) 4ÿ216ÿ196ÿ161 octets
491ÿ745ÿ280 octets libres
(31 lignes affect‚es)|||Thanks for your help
chcp return 850 how do run the commande osql with -u
like parameter osql .. -u437 ?
Regards
>--Original Message--
>This is possibly caused by your code page settings. Run master..xp_cmdshell
>'chcp' to see what your settings are for xp_cmdshell (my guess is code page
>437), as SQL Server will probably assume Lation 1 and displays different
>characters than expected. You could try the -u (Unicode) osql switch to work
>around it.
>-- >Michiel Wories, SQL Server PM
>This posting is provided "AS IS" with no warranties, and confers no rights.
>--
>"brinster" <sbrinster@.seafrance.fr> wrote in message
>news:1a4601c3866f$b9824500$a101280a@.phx.gbl...
>Hello
>I use command OSQL and i have in output file strong
>character when i use the command xp_cmdshell
>
>test.sql
>DECLARE @.cmd sysname, @.var sysname
>SET @.var =3D 'd:\mssql7\data'
>SET @.cmd =3D 'dir ' + @.var
>EXEC master..xp_cmdshell @.cmd
>I use this osql
>osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o
>backup.log -b
>and the oupput file :backup.log
>there is stong character like '=FF' ,
>R‚p
>WHY ?
>
>R‚pertoire de d:\mssql7\data
>
>NULL
>
>12/09/03 12:05 <DIR> .
>
>12/09/03 12:05 <DIR> ..
>
>26/09/03 19:29 2=FF097=FF152 Coffre.ldf
>
>26/09/03 19:29 2=FF097=FF152 Coffre.mdf
>
>27/08/03 14:19 2=FF886=FF721 coffre.zip
>
>16/09/03 14:05 1=FF048=FF576 Etiquettes_Data.MDF
>
>16/09/03 14:05 12=FF320=FF768 master.mdf
>
>16/09/03 14:05 5=FF308=FF416 mastlog.ldf
>
>16/09/03 14:05 1=FF048=FF576 model.mdf
>
>16/09/03 14:05 786=FF432 modellog.ldf
>
>16/09/03 14:05 8=FF388=FF608 msdbdata.mdf
>
>16/09/03 14:05 786=FF432 msdblog.ldf
>
>16/09/03 14:05 1=FF048=FF576 northwnd.ldf
>
>16/09/03 14:05 3=FF801=FF088 northwnd.mdf
>
>16/09/03 14:09 112=FF197=FF632 oscar_Donn‚es.MDF
>
>26/09/03 19:29 3=FF879=FF731=FF200 oscar_Donn‚es2.MDF
>
>26/09/03 19:29 112=FF132=FF096 oscar_Donn‚es3.MDF
>
>16/09/03 14:09 1=FF310=FF720 oscar_Journal.LDF
>
>26/09/03 19:29 1=FF310=FF720 oscar_Journal2.LDF
>
>26/09/03 19:29 1=FF310=FF720 oscar_Journal3.LDF
>
>16/09/03 14:05 1=FF835=FF008 pubs.mdf
>
>16/09/03 14:05 786=FF432 pubs_log.ldf
>
>25/09/03 15:48 63=FF176=FF704 TEMPDB.MDF
>
>16/09/03 14:47 786=FF432 TEMPLOG.LDF
>
>24 fichier(s) 4=FF216=FF196=FF161 octets
>
>491=FF745=FF280 octets libres
>
>
>(31 lignes affect‚es)
>
>.
>|||By specifying the -u flag (without parameters), theoratically there cannot
be an issue as osql will return characters as Unicode characters and
therefore the codepages do not need to match. I say "theoratically" as I'm
not 100% sure what's happening.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"brinster" <sbrinster@.seafrance.fr> wrote in message
news:184801c3872f$0f154250$a101280a@.phx.gbl...
Thanks for your help
chcp return 850 how do run the commande osql with -u
like parameter osql .. -u437 ?
Regards
>--Original Message--
>This is possibly caused by your code page settings. Run
master..xp_cmdshell
>'chcp' to see what your settings are for xp_cmdshell (my
guess is code page
>437), as SQL Server will probably assume Lation 1 and
displays different
>characters than expected. You could try the -u (Unicode)
osql switch to work
>around it.
>--
>Michiel Wories, SQL Server PM
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>--
>"brinster" <sbrinster@.seafrance.fr> wrote in message
>news:1a4601c3866f$b9824500$a101280a@.phx.gbl...
>Hello
>I use command OSQL and i have in output file strong
>character when i use the command xp_cmdshell
>
>test.sql
>DECLARE @.cmd sysname, @.var sysname
>SET @.var = 'd:\mssql7\data'
>SET @.cmd = 'dir ' + @.var
>EXEC master..xp_cmdshell @.cmd
>I use this osql
>osql -U sa -P GD6Dsa -S %SERVEUR% -i test.sql -o
>backup.log -b
>and the oupput file :backup.log
>there is stong character like 'ÿ' ,
>R‚p
>WHY ?
>
>R‚pertoire de d:\mssql7\data
>
>NULL
>
>12/09/03 12:05 <DIR> .
>
>12/09/03 12:05 <DIR> ..
>
>26/09/03 19:29 2ÿ097ÿ152 Coffre.ldf
>
>26/09/03 19:29 2ÿ097ÿ152 Coffre.mdf
>
>27/08/03 14:19 2ÿ886ÿ721 coffre.zip
>
>16/09/03 14:05 1ÿ048ÿ576 Etiquettes_Data.MDF
>
>16/09/03 14:05 12ÿ320ÿ768 master.mdf
>
>16/09/03 14:05 5ÿ308ÿ416 mastlog.ldf
>
>16/09/03 14:05 1ÿ048ÿ576 model.mdf
>
>16/09/03 14:05 786ÿ432 modellog.ldf
>
>16/09/03 14:05 8ÿ388ÿ608 msdbdata.mdf
>
>16/09/03 14:05 786ÿ432 msdblog.ldf
>
>16/09/03 14:05 1ÿ048ÿ576 northwnd.ldf
>
>16/09/03 14:05 3ÿ801ÿ088 northwnd.mdf
>
>16/09/03 14:09 112ÿ197ÿ632 oscar_Donn‚es.MDF
>
>26/09/03 19:29 3ÿ879ÿ731ÿ200 oscar_Donn‚es2.MDF
>
>26/09/03 19:29 112ÿ132ÿ096 oscar_Donn‚es3.MDF
>
>16/09/03 14:09 1ÿ310ÿ720 oscar_Journal.LDF
>
>26/09/03 19:29 1ÿ310ÿ720 oscar_Journal2.LDF
>
>26/09/03 19:29 1ÿ310ÿ720 oscar_Journal3.LDF
>
>16/09/03 14:05 1ÿ835ÿ008 pubs.mdf
>
>16/09/03 14:05 786ÿ432 pubs_log.ldf
>
>25/09/03 15:48 63ÿ176ÿ704 TEMPDB.MDF
>
>16/09/03 14:47 786ÿ432 TEMPLOG.LDF
>
>24 fichier(s) 4ÿ216ÿ196ÿ161 octets
>
>491ÿ745ÿ280 octets libres
>
>
>(31 lignes affect‚es)
>
>.
>