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.

outer join in where clause

Hi all,

I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'

The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)

=*, a.property(+) doesn't work

Thanx
LambikI think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):

select *
from table1 a right outer join table2 b on
a.code = b.code and
a.property = 'TEST'

Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.

Regards,

hmscott

Originally posted by Lambik
Hi all,

I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'

The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)

=*, a.property(+) doesn't work

Thanx
Lambik|||In the original query,
I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
results are still correct, then you can rewrite the query in SQL Server as

Select *
from table1 a, table2 b
where a.code =* b.code
and a.property ='TEST'|||Hi there!

The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.

Greetings,
Carsten|||where a.code(+) = b.code
and a.property(+)='TEST'

I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.

Outer Join - need correct code

Good Morning,

I need to move this part T1."EepEEID"=T3."EecEEID" from Where to From
because this need to be a Left Outer join. Not sure how to do it
properly..

from "ULTIPRO_GPF"."dbo"."EmpPers" T1, (("ULTIPRO_GPF"."dbo"."EmpComp" T3
LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on
T3."EecLocation"=T2."LocCode") LEFT OUTER JOIN
"ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and
1=T4."OrgLvl")

where T1."EepEEID"=T3."EecEEID" and T1."EepDateOfBirth"<'1954-01-01
00:00:00.000' and T3."EecUDField05"!='BOFA' and T3."EecUDField05"!='HEAD'
and T3."EecEmplStatus"!='T' and T3."EecEEType" in ('REG', 'RPT', 'COM')
and T3."EecDateOfOriginalHire"<'1998-01-01 00:00:00.000'
order by 2 asc, 1 ascfrom ULTIPRO_GPF.dbo.EmpPers T1
left outer join ULTIPRO_GPF.dbo.EmpComp T3
on T1.EepEEID=T3.EecEEID
and T3.EecUDField05!='BOFA'
and T3.EecUDField05!='HEAD'
and T3.EecEmplStatus!='T'
and T3.EecEEType in ('REG', 'RPT', 'COM')
and T3.EecDateOfOriginalHire < '1998-01-01 00:00:00.000'
LEFT OUTER JOIN ULTIPRO_GPF.dbo.Location T2 on T3.EecLocation=T2.LocCode
LEFT OUTER JOIN ULTIPRO_GPF.dbo.OrgLevel T4 on T3.EecOrgLvl1=T4.OrgCode and 1=T4.OrgLvl)
where T1.EepDateOfBirth < '1954-01-01 00:00:00.000'|||b, thanks - for some reason - this doesn't work ...
from "ULTIPRO_GPF"."dbo"."EmpPers" T1
left outer join "ULTIPRO_GPF"."dbo"."EmpComp" T3
T1."EepEEID"=T3."EecEEID"
and T3.EecUDField05!='BOFA'
and T3.EecUDField05!='HEAD'
and T3.EecEmplStatus!='T'
and T3.EecEEType in ('REG', 'RPT', 'COM')
and T3.EecDateOfOriginalHire < '1998-01-01 00:00:00.000'
LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on T3."EecLocation"=T2."LocCode"
LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and 1=T4."OrgLvl")
where T1.EepDateOfBirth < '1954-01-01 00:00:00.000'

--

I came up with (using their logic) doesn't give any errors, but doesn't produce the results wanted

from ((("ULTIPRO_GPF"."dbo"."EmpPers" T1 LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."EmpComp" T3 on T1."EepEEID"=T3."EecEEID")
LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on T3."EecLocation"=T2."LocCode")
LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and 1=T4."OrgLvl")

where T1."EepDateOfBirth"<'1954-01-01 00:00:00.000' and T3."EecUDField05"!='BOFA' and T3."EecUDField05"!='HEAD' and T3."EecEmplStatus"!='T' and T3."EecEEType" in ('REG', 'RPT', 'COM') and T3."EecDateOfOriginalHire"<'1998-01-01 00:00:00.000' order by 2 asc, 1 asc|||You need to specify what errors or erroneous results are being returned.

"this doesn't work" doesn't give me much information.

And why do you keep putting double quotes around your fields?

blindman|||Yup ... using [] around your table,db,user names will provide more readability|||sure, it says

Error number -239:

DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
Line 1: Incorrect syntax near 'T1'.
DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
General SQL Server error: Check messages from the SQL Server.
------

the whole code is below. Line 1 they mean the very first line ? if yes, no chaes are being made to it at any point, and no errors are given when its run with other FROM. This program is supposed to use regular T-SQL, so...

--

select T1."EepNameFirst" c1, T1."EepNameLast" c2, T2."LocDesc" c3, T3."EecLocation" c4, T4."OrgDesc" c5, T3."EecOrgLvl1" c6, T1."EepAddressLine1" c7, T1."EepAddressLine2" c8, rtrim(convert(varchar(255),T1."EepAddressCity")) + ', ' + rtrim(convert(varchar(255),T1."EepAddressState")) + ' ' + rtrim(convert(varchar(255),T1."EepAddressZipCode")) c9, T3."EecDateOfOriginalHire" c10, T3."EecDateOfLastHire" c11, case when not T1."EepSSN" is null then substring(T1."EepSSN",1,3) + '-' + substring(T1."EepSSN",4,2) + '-' + substring(T1."EepSSN",6,4) else NULL end c12, T1."EepDateOfBirth" c13, T3."EecEmplStatus" c14

from "ULTIPRO_GPF"."dbo"."EmpPers" T1
left outer join "ULTIPRO_GPF"."dbo"."EmpComp" T3
T1."EepEEID"=T3."EecEEID"
and T3.EecUDField05!='BOFA'
and T3.EecUDField05!='HEAD'
and T3.EecEmplStatus!='T'
and T3.EecEEType in ('REG', 'RPT', 'COM')
and T3.EecDateOfOriginalHire < '1998-01-01 00:00:00.000'
LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."Location" T2 on T3."EecLocation"=T2."LocCode"
LEFT OUTER JOIN "ULTIPRO_GPF"."dbo"."OrgLevel" T4 on T3."EecOrgLvl1"=T4."OrgCode" and 1=T4."OrgLvl")
where T1.EepDateOfBirth < '1954-01-01 00:00:00.000'|||Is this SQL Server ..never seen that error before
"DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'."

out parameter

hello all,
i am trying to get a returned value from a SP using an out parameter.
my code is in C# and i need some help (pls).
be greatfull if you send an example of the SP and the C# code that execute
it and collect the returning parameter value.
thanks a lot, Ran.
I don't think this has a C# example but it does explain OutPut parameters:
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Ran Y" <ranyc@.012.net.il> wrote in message
news:OjMSGU$PEHA.3456@.TK2MSFTNGP11.phx.gbl...
> hello all,
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
> thanks a lot, Ran.
>
|||[posted and mailed, please reply in news]
Ran Y (ranyc@.012.net.il) writes:
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
You did not specify which .Net Data Provider you are using, so I am
assuming SqlClient. Furthermore, the code I have around is VB.Net,
so you will need to transliterate in to C# on your own:
Dim p As SqlParameter = New SqlParameter
p.ParameterName = "@.outparam"
p.DbType = SqlDbType.Int ' For instance.
p.Direction = ParameterDirection.InputOutput
p.Value = DBNull.Value
cmd.Parameters.Add(p)
cmd.ExecuteNonQuery ' Or .Fill or an .ExecuteReader loop
The value of @.outparam is now in p.Value.
There are a couple of variations on how you can create the
parameter, see the online documentation for this.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.
That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegro ups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>
|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

OSQL returns error code of 1 - intermittently

Hello,
I've written an program that runs OSQL to set up a database. I got a weird
situation yesterday where OSQL returns an error level of 1 and didn't
generate any output file at all. The command I use is:
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
Normally, this command runs OK and produces a .log file. Why would it
return 1 and no log file?
I call OSQL from a Borland Builder C++ program.
Any ideas?
Regards,
Robert
PS: I ran the OSQL command straight after I installed MSDE and started up
SQL Server.Hi,
you are assuming some things:
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
-E are you really able to use integrated authentication, if not you
have to provide a user name and a password. Perhaps you are running
your program under a special account which isn=B4t priviledged in the
database ?!
-i Are you in the right folder that SOQL can find the query file while
executing the file from the program ?
(missing -S) If you don=B4t specify a server, the default behaviour is
to go to the local server, the default instance, to the default port
1433. If this is not valid, you have to define the server/indtance,port
e=2Eg. Servername\instancename,Port.
HTH, Jens Suessmeyer.|||Hi,
Thanks for your reply.
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
-E are you really able to use integrated authentication, if not you
have to provide a user name and a password. Perhaps you are running
your program under a special account which isnt priviledged in the
database ?!
I think so. I am running as (machine) administrator when I execute OSQL
-i Are you in the right folder that SOQL can find the query file while
executing the file from the program ?
The SetupRTEDatabase.sql script is in the same directory as the calling
program.
(missing -S) If you dont specify a server, the default behaviour is
to go to the local server, the default instance, to the default port
1433. If this is not valid, you have to define the server/indtance,port
e.g. Servername\instancename,Port.
This is OK. I run OSQL pretty much straight after I do an install of SQL
Server. I don't change any of the default settings.
Regards,
Robert|||I've fixed the problem.
As it turns out, I was running the program in the wrong folder after all.
OSQL was being run (indirectory) from a Setup program which ran on a network
drive.
I did a ChangeDirectory to the path with the SetupRTEDatabase.sql script and
everything worked fine.
"Robert Wheadon" <robert.wheadon@.monitorbm.co.nz> wrote in message
news:uC$01qrTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Thanks for your reply.
> OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
> -E are you really able to use integrated authentication, if not you
> have to provide a user name and a password. Perhaps you are running
> your program under a special account which isnt priviledged in the
> database ?!
> I think so. I am running as (machine) administrator when I execute OSQL
> -i Are you in the right folder that SOQL can find the query file while
> executing the file from the program ?
> The SetupRTEDatabase.sql script is in the same directory as the calling
> program.
> (missing -S) If you dont specify a server, the default behaviour is
> to go to the local server, the default instance, to the default port
> 1433. If this is not valid, you have to define the server/indtance,port
> e.g. Servername\instancename,Port.
> This is OK. I run OSQL pretty much straight after I do an install of SQL
> Server. I don't change any of the default settings.
> Regards,
> Robert
>

oSql Error Handling

Can anyone please tell me why my error handling does not work. I am using the following code in a script file which I pass as an input file to oSql:

IF (@.@.ERROR <> 0)
EXIT(1)
ELSE
EXIT(0)

I have also tried labels as written below to no avail:
IF (@.@.ERROR <> 0) GOTO error_control

success_control:
EXIT(0)

error_control:
EXIT(1)

If anyone knows the solution could they please let me know.I've just written a little script as well and it seems that 'EXIT' must be the last statement within the script.

Example:
SET NOCOUNT ON

DECLARE @.rc int
SET @.rc = 0

IF ((SELECT DB_NAME()) = 'pubs') BEGIN
SET @.rc = 99
GOTO COMMON_EXIT
END

SELECT DB_NAME()

COMMON_EXIT:
EXIT(SELECT @.rc)
GO

The EXIT that will set the MS-DOS ERRORLEVEL variable.

Example (1)
C:\Work>osql -E -Styche -isctest.sql -n -h-1
master

0

C:\Work>echo %errorlevel%
0

C:\Work>


Example (2) against Pubs
C:\Work>osql -E -Styche -isctest.sql -n -dpubs -h-1
99

C:\Work>echo %errorlevel%
99

C:\Work>

Also one thing about @.@.ERROR, it is affect by all T-SQL statements.

Example

UPDATE blah blah blah

IF (@.@.ROWCOUNT = 0) BEGIN
IF (@.@.ERROR = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END

@.@.ERROR will always be 0, since the IF statement is successful it will override the @.@.ERROR value set by the UPDATE statement.

UPDATE blah blah blah

SELECT @.Rows = @.@.ROWCOUNT,
@.Err = @.@.ERROR

IF (@.Rows = 0) BEGIN
IF (@.Err = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END

osql breaks output

Hello,
I would like to return a text column from the table syscomments to put it into
a file, so I can edit and view the code.
SELECT syscomments.text
FROM sysobjects INNER JOIN syscomments
ON sysobjects.id = syscomments.id
WHERE sysobjects.name = 'MySP'
My only problem is: I cannot prevent osql from breaking. The parameter for this is -w:
Standard of -w is 80 as documented.
The content of my text column is greater than the maximum -w65535, so the text gets broken.
If I try -w0, -w-1 or something like this, it says:
[-w] has to be greater than 8 and less than 65536
Question: how can I prevent osql from breaking lines?
(sql2K)
Thank You
Joachim
The object text in syscomments is varchar(4000) so object text that exceeds
that length will be stored as multiple rows in syscomments.
Consider using sp_helptext instead of using syscomments directly. This will
output the original code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:e%23GyPZZ3HHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to return a text column from the table syscomments to put it
> into
> a file, so I can edit and view the code.
> SELECT syscomments.text
> FROM sysobjects INNER JOIN syscomments
> ON sysobjects.id = syscomments.id
> WHERE sysobjects.name = 'MySP'
> My only problem is: I cannot prevent osql from breaking. The parameter for
> this is -w:
> Standard of -w is 80 as documented.
> The content of my text column is greater than the maximum -w65535, so the
> text gets broken.
> If I try -w0, -w-1 or something like this, it says:
> [-w] has to be greater than 8 and less than 65536
>
> Question: how can I prevent osql from breaking lines?
> (sql2K)
> Thank You
> Joachim
|||Dan Guzman wrote:

> Consider using sp_helptext instead of using syscomments directly. This

>
OK, now I try the following:
CREATE TABLE ##T
(
Text nvarchar(255) NULL
) ON [PRIMARY]
INSERT INTO ##T EXECUTE sp_helptext @.objname=spManuellesStornieren_DT_produktiv
SELECT Text FROM ##T
The table (##)T itself seems to be OK and the output with Query Analyzer too.
But when I output T.Text with either osql or bcp (with a nontemporary T),
there is an unnecessary linebreak after each real line of the code.
Moreover through osql this unnecessary line is "padded" with spaces.
How can I avoid this?
Joachim
|||> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
There are a couple of things going on here. First, the source data contains
embedded CR/LF characters (at the end of each proc line). OSQL retains
these and also adds a CR/LF at the end of each line along with padding to
your -w specification. The SQLCMD utility, intoduced in SQL 2005, provides
a -W option that suppresses addition of padding and CRLF. This option is
useful in exporting raw data for another application.
If SQLCMD isn't an option, you can use BCP to export raw lines without a
line or field terminator. For example:
BCP ##t out c:\ProcText.out /T /r /t /S MyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:ezAcfFb3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Dan Guzman wrote:
>
> OK, now I try the following:
> CREATE TABLE ##T
> (
> Text nvarchar(255) NULL
> ) ON [PRIMARY]
> INSERT INTO ##T EXECUTE sp_helptext
> @.objname=spManuellesStornieren_DT_produktiv
> SELECT Text FROM ##T
> The table (##)T itself seems to be OK and the output with Query Analyzer
> too.
> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
> How can I avoid this?
> Joachim
|||Dan Guzman wrote:

> 2005, provides a -W option that suppresses addition of padding and
> CRLF.
OK, now I use sqlcmd with -W to avoid padding. But the output is broken again.
Can I stop sclcmd from *breaking lines*?
Joachim
|||> Can I stop sclcmd from *breaking lines*?
Here's an example of the command I used:
SQLCMD -S"MyServer" -Q"EXEC sp_helptext
'MyDatabase.dbo.MyProc'" -W -h"-1" -o"MyProc.sql"
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eAiBlm03HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Dan Guzman wrote:
>
> OK, now I use sqlcmd with -W to avoid padding. But the output is broken
> again.
> Can I stop sclcmd from *breaking lines*?
> Joachim
|||It works, Thank You.
Here is an example how I use it:
FOR %%F IN (spManuellesStornieren_DT_produktiv spManuellesBuchen_DT_produktiv) DO (
SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
)
PAUSE
Joachim
|||I'l glad you got it working. If you write to write a custom scripting tool
for other object types, take a look at the SMO object model (SQL 2005).
This allows you to develop custom applications in a .NET language like C# to
leverage SMO scripting methods if the SQL Server tools don't fit your needs.
You can download one of the free Visual Studio Express editions if you don't
already have a VS and/or want to learn a .NET language.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:uKJqSd%233HHA.600@.TK2MSFTNGP05.phx.gbl...
> It works, Thank You.
> Here is an example how I use it:
> FOR %%F IN (spManuellesStornieren_DT_produktiv
> spManuellesBuchen_DT_produktiv) DO (
> SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
> SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
> )
> PAUSE
> Joachim

osql breaks output

Hello,
I would like to return a text column from the table syscomments to put it in
to
a file, so I can edit and view the code.
SELECT syscomments.text
FROM sysobjects INNER JOIN syscomments
ON sysobjects.id = syscomments.id
WHERE sysobjects.name = 'MySP'
My only problem is: I cannot prevent osql from breaking. The parameter for t
his is -w:
Standard of -w is 80 as documented.
The content of my text column is greater than the maximum -w65535, so the te
xt gets broken.
If I try -w0, -w-1 or something like this, it says:
[-w] has to be greater than 8 and less than 65536
Question: how can I prevent osql from breaking lines?
(sql2K)
Thank You
JoachimThe object text in syscomments is varchar(4000) so object text that exceeds
that length will be stored as multiple rows in syscomments.
Consider using sp_helptext instead of using syscomments directly. This will
output the original code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:e%23GyPZZ3HHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to return a text column from the table syscomments to put it
> into
> a file, so I can edit and view the code.
> SELECT syscomments.text
> FROM sysobjects INNER JOIN syscomments
> ON sysobjects.id = syscomments.id
> WHERE sysobjects.name = 'MySP'
> My only problem is: I cannot prevent osql from breaking. The parameter for
> this is -w:
> Standard of -w is 80 as documented.
> The content of my text column is greater than the maximum -w65535, so the
> text gets broken.
> If I try -w0, -w-1 or something like this, it says:
> [-w] has to be greater than 8 and less than 65536
>
> Question: how can I prevent osql from breaking lines?
> (sql2K)
> Thank You
> Joachim|||Dan Guzman wrote:

> Consider using sp_helptext instead of using syscomments directly. This

>
OK, now I try the following:
CREATE TABLE ##T
(
Text nvarchar(255) NULL
) ON [PRIMARY]
INSERT INTO ##T EXECUTE sp_helptext @.objname=spManuellesStornieren_DT_produ
ktiv
SELECT Text FROM ##T
The table (##)T itself seems to be OK and the output with Query Analyzer too
.
But when I output T.Text with either osql or bcp (with a nontemporary T),
there is an unnecessary linebreak after each real line of the code.
Moreover through osql this unnecessary line is "padded" with spaces.
How can I avoid this?
Joachim|||> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
There are a couple of things going on here. First, the source data contains
embedded CR/LF characters (at the end of each proc line). OSQL retains
these and also adds a CR/LF at the end of each line along with padding to
your -w specification. The SQLCMD utility, intoduced in SQL 2005, provides
a -W option that suppresses addition of padding and CRLF. This option is
useful in exporting raw data for another application.
If SQLCMD isn't an option, you can use BCP to export raw lines without a
line or field terminator. For example:
BCP ##t out c:\ProcText.out /T /r /t /S MyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:ezAcfFb3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Dan Guzman wrote:
>
>
> OK, now I try the following:
> CREATE TABLE ##T
> (
> Text nvarchar(255) NULL
> ) ON [PRIMARY]
> INSERT INTO ##T EXECUTE sp_helptext
> @.objname=spManuellesStornieren_DT_produk
tiv
> SELECT Text FROM ##T
> The table (##)T itself seems to be OK and the output with Query Analyzer
> too.
> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
> How can I avoid this?
> Joachim|||Dan Guzman wrote:

> 2005, provides a -W option that suppresses addition of padding and
> CRLF.
OK, now I use sqlcmd with -W to avoid padding. But the output is broken agai
n.
Can I stop sclcmd from *breaking lines*?
Joachim|||> Can I stop sclcmd from *breaking lines*?
Here's an example of the command I used:
SQLCMD -S"MyServer" -Q"EXEC sp_helptext
'MyDatabase.dbo.MyProc'" -W -h"-1" -o"MyProc.sql"
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eAiBlm03HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Dan Guzman wrote:
>
> OK, now I use sqlcmd with -W to avoid padding. But the output is broken
> again.
> Can I stop sclcmd from *breaking lines*?
> Joachim|||It works, Thank You.
Here is an example how I use it:
FOR %%F IN (spManuellesStornieren_DT_produktiv spManuellesBuchen_DT_produkti
v) DO (
SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENB
ANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENB
ANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
)
PAUSE
Joachim|||I'l glad you got it working. If you write to write a custom scripting tool
for other object types, take a look at the SMO object model (SQL 2005).
This allows you to develop custom applications in a .NET language like C# to
leverage SMO scripting methods if the SQL Server tools don't fit your needs.
You can download one of the free Visual Studio Express editions if you don't
already have a VS and/or want to learn a .NET language.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:uKJqSd%233HHA.600@.TK2MSFTNGP05.phx.gbl...
> It works, Thank You.
> Here is an example how I use it:
> FOR %%F IN (spManuellesStornieren_DT_produktiv
> spManuellesBuchen_DT_produktiv) DO (
> SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
> SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
> )
> PAUSE
> Joachim

osql breaks output

Hello,
I would like to return a text column from the table syscomments to put it into
a file, so I can edit and view the code.
SELECT syscomments.text
FROM sysobjects INNER JOIN syscomments
ON sysobjects.id = syscomments.id
WHERE sysobjects.name = 'MySP'
My only problem is: I cannot prevent osql from breaking. The parameter for this is -w:
Standard of -w is 80 as documented.
The content of my text column is greater than the maximum -w65535, so the text gets broken.
If I try -w0, -w-1 or something like this, it says:
[-w] has to be greater than 8 and less than 65536
Question: how can I prevent osql from breaking lines?
(sql2K)
Thank You
JoachimThe object text in syscomments is varchar(4000) so object text that exceeds
that length will be stored as multiple rows in syscomments.
Consider using sp_helptext instead of using syscomments directly. This will
output the original code.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:e%23GyPZZ3HHA.1204@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to return a text column from the table syscomments to put it
> into
> a file, so I can edit and view the code.
> SELECT syscomments.text
> FROM sysobjects INNER JOIN syscomments
> ON sysobjects.id = syscomments.id
> WHERE sysobjects.name = 'MySP'
> My only problem is: I cannot prevent osql from breaking. The parameter for
> this is -w:
> Standard of -w is 80 as documented.
> The content of my text column is greater than the maximum -w65535, so the
> text gets broken.
> If I try -w0, -w-1 or something like this, it says:
> [-w] has to be greater than 8 and less than 65536
>
> Question: how can I prevent osql from breaking lines?
> (sql2K)
> Thank You
> Joachim|||Dan Guzman wrote:
> Consider using sp_helptext instead of using syscomments directly. This
>
OK, now I try the following:
CREATE TABLE ##T
(
Text nvarchar(255) NULL
) ON [PRIMARY]
INSERT INTO ##T EXECUTE sp_helptext @.objname=spManuellesStornieren_DT_produktiv
SELECT Text FROM ##T
The table (##)T itself seems to be OK and the output with Query Analyzer too.
But when I output T.Text with either osql or bcp (with a nontemporary T),
there is an unnecessary linebreak after each real line of the code.
Moreover through osql this unnecessary line is "padded" with spaces.
How can I avoid this?
Joachim|||> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
There are a couple of things going on here. First, the source data contains
embedded CR/LF characters (at the end of each proc line). OSQL retains
these and also adds a CR/LF at the end of each line along with padding to
your -w specification. The SQLCMD utility, intoduced in SQL 2005, provides
a -W option that suppresses addition of padding and CRLF. This option is
useful in exporting raw data for another application.
If SQLCMD isn't an option, you can use BCP to export raw lines without a
line or field terminator. For example:
BCP ##t out c:\ProcText.out /T /r /t /S MyServer
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:ezAcfFb3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> Dan Guzman wrote:
>> Consider using sp_helptext instead of using syscomments directly. This
> OK, now I try the following:
> CREATE TABLE ##T
> (
> Text nvarchar(255) NULL
> ) ON [PRIMARY]
> INSERT INTO ##T EXECUTE sp_helptext
> @.objname=spManuellesStornieren_DT_produktiv
> SELECT Text FROM ##T
> The table (##)T itself seems to be OK and the output with Query Analyzer
> too.
> But when I output T.Text with either osql or bcp (with a nontemporary T),
> there is an unnecessary linebreak after each real line of the code.
> Moreover through osql this unnecessary line is "padded" with spaces.
> How can I avoid this?
> Joachim|||Dan Guzman wrote:
> 2005, provides a -W option that suppresses addition of padding and
> CRLF.
OK, now I use sqlcmd with -W to avoid padding. But the output is broken again.
Can I stop sclcmd from *breaking lines*?
Joachim|||> Can I stop sclcmd from *breaking lines*?
Here's an example of the command I used:
SQLCMD -S"MyServer" -Q"EXEC sp_helptext
'MyDatabase.dbo.MyProc'" -W -h"-1" -o"MyProc.sql"
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eAiBlm03HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Dan Guzman wrote:
>
>> 2005, provides a -W option that suppresses addition of padding and CRLF.
> OK, now I use sqlcmd with -W to avoid padding. But the output is broken
> again.
> Can I stop sclcmd from *breaking lines*?
> Joachim|||It works, Thank You.
Here is an example how I use it:
FOR %%F IN (spManuellesStornieren_DT_produktiv spManuellesBuchen_DT_produktiv) DO (
SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
)
PAUSE
Joachim|||I'l glad you got it working. If you write to write a custom scripting tool
for other object types, take a look at the SMO object model (SQL 2005).
This allows you to develop custom applications in a .NET language like C# to
leverage SMO scripting methods if the SQL Server tools don't fit your needs.
You can download one of the free Visual Studio Express editions if you don't
already have a VS and/or want to learn a .NET language.
--
Dan Guzman
SQL Server MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:uKJqSd%233HHA.600@.TK2MSFTNGP05.phx.gbl...
> It works, Thank You.
> Here is an example how I use it:
> FOR %%F IN (spManuellesStornieren_DT_produktiv
> spManuellesBuchen_DT_produktiv) DO (
> SQLCMD -S"Host02\Test" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Test\%%F.sql"
> SQLCMD -S"SWRSQL1.STATT-WERK.REM" -d%DATENBANK% -Q"EXEC sp_helptext
> '%DATENBANK%.dbo.%%F'" -W -h"-1" o".\Echt\%%F.sql"
> )
> PAUSE
> Joachim