Orphaned Sessions and locks: March 2012

Friday, March 30, 2012

Output Column Width not refected in the Flat File that is created using a Flat File Destination?

I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?

Any inputs will be appreciated.

M.Shah

InputColumnWidth represents the width in the file and OutputColumnWidth is the width in the data flow.

This may sound confusing for the case of Flat File destination connection, but the same connection manager object is used for sources and destination. There is also the description of these properties in the property grid of the Flat File Connection Manager UI.

So, to conclude the InputColumnWidth is the one controlling the size of the columns in the destination file.

Thanks.

|||

Thanks for the information. That was definitely helpful to figure out why the OutputColumnWidth was not reflected in the flat file.

Output Column Width not refected in the Flat File that is created using a Flat File Destination?

Any inputs will be appreciated.

M.Shah

InputColumnWidth represents the width in the file and OutputColumnWidth is the width in the data flow.

So, to conclude the InputColumnWidth is the one controlling the size of the columns in the destination file.

Thanks.

|||

Thanks for the information. That was definitely helpful to figure out why the OutputColumnWidth was not reflected in the flat file.

sql

Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of this
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:
> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> > We are using SQL 2K with Sp4.
> >
> > I am doing a Select statement where it contains a column that composes of
> > a
> > concatenation of various strings and many fields. At time, the output of
> > this
> > column will contain a very very long output string that part the output
> > data
> > is being truncated. I assume it exceeds the limit of an output column in
> > the result panel would allow. Is there a setting in SQL I can change to
> > imcrease the size of a column in the result panel in query anaylzer?
> >
> > Wingman
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of this
>column will contain a very very long output string that part the output data
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:
> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
> >We are using SQL 2K with Sp4.
> >
> >I am doing a Select statement where it contains a column that composes of a
> >concatenation of various strings and many fields. At time, the output of this
> >column will contain a very very long output string that part the output data
> >is being truncated. I assume it exceeds the limit of an output column in
> >the result panel would allow. Is there a setting in SQL I can change to
> >imcrease the size of a column in the result panel in query anaylzer?
> >
> >Wingman
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>> In Query analyzer, go to the menu to Tools, then Options.
>> On the results tab, increase the Maximum characters per
>> column setting.
>> -Sue
>> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
>> <Wingman@.discussions.microsoft.com> wrote:
>> >We are using SQL 2K with Sp4.
>> >
>> >I am doing a Select statement where it contains a column that composes of a
>> >concatenation of various strings and many fields. At time, the output of this
>> >column will contain a very very long output string that part the output data
>> >is being truncated. I assume it exceeds the limit of an output column in
>> >the result panel would allow. Is there a setting in SQL I can change to
>> >imcrease the size of a column in the result panel in query anaylzer?
>> >
>> >Wingman
>>

Output Column names in each Row along with the row Value

Hello,

I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3

Thank you! :-)

YasOn Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?
>
eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?
>
Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3
>
Thank you! :-)
>
Yas

Not sure what you are actually looking at

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')

select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp

drop table #temp|||On 29 Jun, 14:27, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hello,

Quote:

Originally Posted by

Quote:

Originally Posted by

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?

Quote:

Originally Posted by

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3

Quote:

Originally Posted by

Thank you! :-)

Quote:

Originally Posted by

Yas

>
Not sure what you are actually looking at
>

Hi, thanks for the response. Basically I want to have a query in a DTS
package that outputs to a text file with each line containing 1 row of
results.
What I would like to have is on each line before a given value is
written the column header for that value is also written. the file is
a semicolon delimmeted file so it would look something like this...

col1Name;value1;col2Name;value2;col3Name;value3... .etc

so...
FirstName;firstNameValue;SecondName;secondNameValu e;Occupation;occupationValue..etc

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?
especially as I want to do this in a DTS package Export Data which
esentially has a create table type command to ouptut to a text file
and throws errors that destination column name not defined.

Thanks :-)

Quote:

Originally Posted by

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')
>
select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp
>
drop table #temp

|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

That would indeed be the way to do it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 29 Jun, 23:38, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

>
That would indeed be the way to do it.

Thanks! :-)

Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:
I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.
SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.

Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:
I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.
SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

Note:

Thanks all!

sql

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.

OUTPUT Clause

We have a table, which is partitioned on a computed column, based on a UDF function (custom logic). We populate this computed column in an INSTEAD OF Trigger. But when we try to "output" the computed column using OUTPUT caluse, NULL values are returned for computed column. SQL BOL is not clear on this part of using output clause in instead of triggers. This does not even work for a table that is not partitioned. Here is an example of what we are trying to do...

I would appreciate any help on this...

IF OBJECT_ID('dbo.Test') IS NOT NULL

DROP TABLE dbo.Test

CREATE TABLE dbo.Test

( IdCol INT IDENTITY(1,1) NOT NULL,

Date DATETIME NOT NULL,

CalcCol CHAR(10) NOT NULL

)

CREATE TRIGGER trgTest_InsUpd ON dbo.Test

INSTEAD OF INSERT AS

BEGIN

INSERT INTO dbo.Test

( Date, CalcCol ) SELECT Date, CONVERT(CHAR(10), Date, 101) FROM INSERTED

END

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

DROP TABLE #tmp

CREATE TABLE #tmp(IdCol INT, Date DATETIME, CalcCol VARCHAR(10))

TRUNCATE TABLE #tmp

INSERT INTO dbo.Test(Date) OUTPUT INSERTED.IdCol, INSERTED.Date, INSERTED.CalcCol INTO #tmp

VALUES(GETDATE())

--Here the identity and Computed column are returned as NULL

SELECT * FROM #tmp

SELECT * FROM dbo.Test

Instead of trigger would not have data in INSERTED and DELETED tables, because instead of the original insert statement your INSTEAD OF tRIGGER is getting fired. The tables will be populated if you write a AFTER TRIGGER.

Output Buffer Remove Row?

I am using a script component to create the output buffer dynamically. I use the Outputbuffer.AddRow() call. I then set all the fields I want, and its added to the output and later inserted into the database. If a field value fails it causes an error, but the record is partially inserted upto the point where the set field command caused the error. So if I set 10 fields, and it fails on field 5 it inserts data for the 5 fields that worked and nulls into the others.

As a result I have a try catch clause, and if it fails I want to cancell the addition of the new row. Is there a command like RemoveRow(), rollback, etc that can be used to not insert the record in error?

Sample code..

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.RecordID = Row.RecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.removecurrentrow(?)

End Try

Can you provide more of your script? Maybe even all of it?|||

The script is very long. I do the same basic task in three scripts for three different feeds, this is the shortest one. The only difference (basically) between this and the others is the number of field in the outputbuffer. Ok.. Here is the code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Text
Imports Company.SSIS.Functions
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim PaymentRecord As String = System.Text.Encoding.Unicode.GetString(Row.PaymentMethods.GetBlobData(0, CInt(Row.PaymentMethods.Length)))
Dim PaymentArray() As String
Dim pi As Integer
PaymentArray = Parse.StringParse(PaymentRecord, "\|")
For pi = 0 To PaymentArray.Length - 1
If PaymentArray(pi) <> "" Then
Try
PaymentOutputBuffer.AddRow()
PaymentOutputBuffer.Sequence = pi + 1
PaymentOutputBuffer.ServiceRecordID = Row.ServiceRecordID
PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)
Catch e As Exception
PaymentErrorOutputBuffer.AddRow()
PaymentErrorOutputBuffer.ErrorRecordID = Row.ServiceRecordID
PaymentErrorOutputBuffer.ErrorCode = e.Message
PaymentErrorOutputBuffer.ErrorData.AddBlobData(Encoding.UTF8.GetBytes(PaymentArray(pi)))
End Try
End If
Next
End Sub
End Class

The Company.SSIS.Functions import is a dll I wrote. It has the Parse.StringParse(PaymentRecord, "\|") call. It is simply a regex function. I listed it below.. but basically splits the delimited field into multiple rows. So the goal is that it is called per row, splits the data in the row into an array and stores it as records. If the paymentmethod store fails, the record still gets inserted into the datase with the sequence and service record id. The catch directs creates a second output, that i then store to my error table. I essentially want the catch to still send to my error table, but for the first output not to be created if it fails and enters the catch.

Public Class Parse
Public Shared Function StringParse(ByVal StringValue As String, ByVal ParseValue As String) As String()
If Replace(StringValue, "", Nothing) Is Nothing Then
Return Split(StringValue)
Else
Dim pattern As String = ParseValue & "(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
Dim r As RegularExpressions.Regex = New RegularExpressions.Regex(pattern)
Return r.Split(StringValue)
End If
End Function
End Class

|||What are some of the reasons why the assignment to PaymentMethod could fail?|||

Payment method is varchar(20) field. The output is defined as String 20. Primary reason of failure is text truncation. On other code it may be bad date/time stamp, non integer value, etc. Typically it is a data error.

I can do a specific check on payment method to make sure its not in error before calling the addrow() so that the output is not called unless it will succeed. But for scenarios I don't think of, or conditions where I need to check 20+ fields this is not as practical. So I was hoping to use a try/catch process to insert the row if its good, otherwise write to the error table.

|||Yep, I hear ya... I just wanted you to think about performing data validation up front, and you have. Good.

Some of the other code experts may have to chime in now. |||Remove the current row with the call to the PipelineBuffer.RemoveRow() method. The script component does not expose the PipelineBuffer.RemoveRow() method directly, but its does expose the PipelineBuffer(s) in PrimeOutput, on which RemoveRow can be called.

Taking your scenario as example, save the underlying pipeline buffers, and call any PipelineBuffer method on them, including RemoveRow.

Imports System

Imports

Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits

UserComponent

Private

normalBuffer As PipelineBuffer

Private

errorBuffer As PipelineBuffer

Public Overrides Sub

Input0_ProcessInputRow _

(ByVal

Row As Input0Buffer)

Try

With

PaymentOutputBuffer

.AddRow()

.FirstString =

Row.GeneratedStr1

End

With

Catch

ex As Exception

With

PaymentErrorOutputBuffer

.AddRow()

.ErrorMessage =

ex.Message.Substring(0, _

Math.Min(ex.Message.Length,

250))

End

With

normalBuffer.RemoveRow()

' Remove

the row from normal, as normalBuffer

' is the

underlying PipelineBuffer

' for

PaymentOutputBuffer

End Try

End Sub

Public Overrides Sub

PrimeOutput(ByVal Outputs As Integer, _

ByVal

OutputIDs() As Integer,

ByVal

Buffers() As

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' save

underlying PipelineBuffer to allow for calls to

PipelineBuffer methods

normalBuffer = Buffers(0)

errorBuffer = Buffers(1)

MyBase.PrimeOutput(Outputs,

OutputIDs, Buffers)

End Sub

End Class

|||Thanks.. .that works and does what I need. I was curious about the Buffers(x) value and if there is another way to set it to the specific outputbuffer. I tried to look at the help on the Pipeline buffer, but was not able to determine it. I want to be able to reference the Buffers(PaymentOutputBuffer) vs. a 0 so that if people add additional buffers, etc it will not break. Is there a way to make this go off the actual buffer name?|||Yes, the buffers can be referenced by name.

The OutputIDs array parameter to PrimeOutput has the integer buffer ids (not the offset) in coordinated order with the Buffers array.

The buffer's name is available via a call to ComponentMetaData.OutputCollection.FindObjectById(<id from OutputIds here>).Name.

See the following BOL reference for the OutputCollection methods: ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Pipeline_Wrapper_IDTSOutputCollection90_Members.htmsql

Output Buffer Remove Row?

Sample code..

Try

PaymentOutputBuffer.AddRow()

PaymentOutputBuffer.Sequence = pi + 1

PaymentOutputBuffer.RecordID = Row.RecordID

PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)

Catch e As Exception

PaymentErrorOutputBuffer.removecurrentrow(?)

End Try

Can you provide more of your script? Maybe even all of it?|||

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Text
Imports Company.SSIS.Functions
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim PaymentRecord As String = System.Text.Encoding.Unicode.GetString(Row.PaymentMethods.GetBlobData(0, CInt(Row.PaymentMethods.Length)))
Dim PaymentArray() As String
Dim pi As Integer
PaymentArray = Parse.StringParse(PaymentRecord, "\|")
For pi = 0 To PaymentArray.Length - 1
If PaymentArray(pi) <> "" Then
Try
PaymentOutputBuffer.AddRow()
PaymentOutputBuffer.Sequence = pi + 1
PaymentOutputBuffer.ServiceRecordID = Row.ServiceRecordID
PaymentOutputBuffer.PaymentMethod = PaymentArray(pi)
Catch e As Exception
PaymentErrorOutputBuffer.AddRow()
PaymentErrorOutputBuffer.ErrorRecordID = Row.ServiceRecordID
PaymentErrorOutputBuffer.ErrorCode = e.Message
PaymentErrorOutputBuffer.ErrorData.AddBlobData(Encoding.UTF8.GetBytes(PaymentArray(pi)))
End Try
End If
Next
End Sub
End Class

Public Class Parse
Public Shared Function StringParse(ByVal StringValue As String, ByVal ParseValue As String) As String()
If Replace(StringValue, "", Nothing) Is Nothing Then
Return Split(StringValue)
Else
Dim pattern As String = ParseValue & "(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
Dim r As RegularExpressions.Regex = New RegularExpressions.Regex(pattern)
Return r.Split(StringValue)
End If
End Function
End Class

|||What are some of the reasons why the assignment to PaymentMethod could fail?|||

|||Yep, I hear ya... I just wanted you to think about performing data validation up front, and you have. Good.

Taking your scenario as example, save the underlying pipeline buffers, and call any PipelineBuffer method on them, including RemoveRow.

Imports System

Imports

Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits

UserComponent

Private

normalBuffer As PipelineBuffer

Private

errorBuffer As PipelineBuffer

Public Overrides Sub

Input0_ProcessInputRow _

(ByVal

Row As Input0Buffer)

Try

With

PaymentOutputBuffer

.AddRow()

.FirstString =

Row.GeneratedStr1

End

With

Catch

ex As Exception

With

PaymentErrorOutputBuffer

.AddRow()

.ErrorMessage =

ex.Message.Substring(0, _

Math.Min(ex.Message.Length,

250))

End

With

normalBuffer.RemoveRow()

' Remove

the row from normal, as normalBuffer

' is the

underlying PipelineBuffer

' for

PaymentOutputBuffer

End Try

End Sub

Public Overrides Sub

PrimeOutput(ByVal Outputs As Integer, _

ByVal

OutputIDs() As Integer,

ByVal

Buffers() As

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' save

underlying PipelineBuffer to allow for calls to

PipelineBuffer methods

normalBuffer = Buffers(0)

errorBuffer = Buffers(1)

MyBase.PrimeOutput(Outputs,

OutputIDs, Buffers)

End Sub

End Class

The OutputIDs array parameter to PrimeOutput has the integer buffer ids (not the offset) in coordinated order with the Buffers array.

The buffer's name is available via a call to ComponentMetaData.OutputCollection.FindObjectById(<id from OutputIds here>).Name.

OUTPUT and Recordset

I have created a 'Intelligent' search stored procedure that accepts two input parameters SearchFor and SearchIn and an OUTPUT parameter @.Result

Basically it starts by looking for a match for the SearchFor and if more than 1 is found, returns a recordset of the matches so the user can select a SearchFor category, at the same time the OUTPUT parameter is set to let the calling script know what is being returned.

Likewise it does the same for the SearchIn until both are satisfied and the true search results recordset can be returned.

Is it possible to get an OUTPUT parameter AND a recordset at the same time?

Any help will be appreciated,

PhlazarI would put the results of the stored procedure into a global temp table....

Cheers
C

Output an SQL Query to a text file

Hi,

How would I go about writing the results of an SQL query to a text
file?

I cannot find any info in the Online help files.

For example, I would like the results of:

SELECT * FROM TableA

To be written to the file result.txt

AlexOpen query analyzer. Go to Query and select Result to file... and
during execution, it will prompt a file name...|||Thanks but I cannot do that.

This must query must be invisible to the end user.

For example, if a user performs some function on my system, I wish to
execute a query on what they have selected and output the results to a
file. I do not want my user to be selecting Save As etc..|||First your question is not clear. I am still not sure what you are
trying to accomplish.

First if you have a query that you dont want your user to know, wrap it
up in a stored proc and schedule it in a job and let the user execute
the job.

Another easy way is to create a dts and let the user run the dts that
should run the query and output the file.

Hope this helps..!|||Ok, I have no idea what a dts is!!

Is it possible to output the results from a stored procedure to a
specified file.

I do not want to use a tool or anything, just simply write the results
from a query to a file.|||Alex,
You might try adding this to your connection string: -o '<path
name>\results.txt.

such as: isql -SMyserver -Uusera -Puserapassword -i mysqlquery.sql -o
results.tx

hth

"Alex" <alex_seymour@.lineone.net> wrote in message
news:1108412990.821013.237700@.g14g2000cwa.googlegr oups.com...
> Hi,
> How would I go about writing the results of an SQL query to a text
> file?
> I cannot find any info in the Online help files.
> For example, I would like the results of:
> SELECT * FROM TableA
> To be written to the file result.txt
>
> Alex|||On 2/14/05 4:24 PM, in article
1108416279.303119.278950@.z14g2000cwz.googlegroups. com, "Alex"
<alex_seymour@.lineone.net> wrote:

> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

There is no OUTPUT TO FILE argument in a SELECT clause. You can SELECT INTO
a temporary table but this doesn't help you. The simplest thing to do (path
of least resistance) is to use a program in the middle to handle the query
and writing the recordset into a file. For example, if you wanted to create
an .ASP web form you could simply ask for the customer's email address, run
a query and send them the results. You could write a VB app or VB script to
run locally and generate a file from your query.

-Greg|||There isn't a direct method. Two possible workarounds are OSQL and
BCP. I tend to use BCP. BCP only lets you output from a permanent
table, though.

I would create a table called "CACHE" where one of the columns is "IP"
and the other cols match the SELECT query. Then store the output into
CACHE along with the user's IP. Then run BCP to output from CACHE.|||Alex (alex_seymour@.lineone.net) writes:
> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

No, you don't want to do that. Yes, you can invoke xp_cmdshell and from
there do all sorts of things. But then your users would have to have
permission to use xp_cmdshell, and that could be a security issue.

It's not clear why you want to write to a file, but if you want to
log some stuff for debug reasons - write to a table instead. You are
in a database, not in a file system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok this is great, the tables I need data outputting from are permanent.

Basically it is for creating a log file. The database is huge, but the
log only needs to contain the contents of a few tables. It has to go to
a file because I want to be able to save the results of the query to
disk or print it out.

Can I BCP out from several tables into the same file?

i.e. I need SELECT * from TableA, TableB, TableC to result.txt

Security is not an issue.|||Alex wrote:
> Ok this is great, the tables I need data outputting from are
permanent.
> Basically it is for creating a log file. The database is huge, but
the
> log only needs to contain the contents of a few tables. It has to go
to
> a file because I want to be able to save the results of the query to
> disk or print it out.
> Can I BCP out from several tables into the same file?
> i.e. I need SELECT * from TableA, TableB, TableC to result.txt
> Security is not an issue.

It's a little involved but you can write a select statement joining
several tables. Each time BCP executes, I believe it overwrites the
text file.

Check out this article:
http://www.sqlteam.com/item.asp?ItemID=4722

Output a report directly to PDF?

Hey everyone, does anyonw know how to output a report directly to PDF?
That is, without passing any parameters, but defaulted in the report
itslef. So a user would not have a choice to view it in the report
manager?I think the only way to do this is to have your own frontend. No way to
configure the server to do this (I think it would be a bad idea overal, PDFs
are much much slower and more compute intensive). I agree for some reports
it would be nice to mark it as rendering only in a certain format. But, no
capability for that exists.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Daniel.P.Lyonnais@.lmco.com> wrote in message
news:1131047107.559743.200760@.f14g2000cwb.googlegroups.com...
> Hey everyone, does anyonw know how to output a report directly to PDF?
> That is, without passing any parameters, but defaulted in the report
> itslef. So a user would not have a choice to view it in the report
> manager?
>|||Daniel;
You can simply render the report as a PDF file as shown below. Will that do
what you need?
Wayne
=======================================Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim rs1 As New Wedding.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myServer", "tmyPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), "PDF", _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)
End Sub
<Daniel.P.Lyonnais@.lmco.com> wrote in message
news:1131047107.559743.200760@.f14g2000cwb.googlegroups.com...
> Hey everyone, does anyonw know how to output a report directly to PDF?
> That is, without passing any parameters, but defaulted in the report
> itslef. So a user would not have a choice to view it in the report
> manager?
>sql

output 2 table data to a text file

hi, how to output 2 table data to a text file?Not specifing what you want...
You can use bcp, osql with the -o switch or if you just wanna put out the
result of a query in QA you can send the result to a fiel rather than to the
result pane.
Is it that what you need ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
> hi, how to output 2 table data to a text file?
>|||can I do it in a query: select * from tb1 output my.txt?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
> Not specifing what you want...
> You can use bcp, osql with the -o switch or if you just wanna put out the
> result of a query in QA you can send the result to a fiel rather than to
> the result pane.
> Is it that what you need ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
>|||Yes of course, here is an example:
osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
will produuce the following output:
OrderID CustomerID EmployeeID OrderDate
RequiredDate ShippedDate ShipVia
Freight ShipName
ShipAddress
ShipCity ShipRegion ShipPostalCode ShipCountry
-- -- -- --
-- -- --
-- ---
----
-- -- -- --
10248 VINET 5 1996-07-04 00:00:00.000
1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
32.3800 Vins et al

s Chevalier
59 rue de l'Abbaye
Reims NULL 51100 France
(1 row affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> can I do it in a query: select * from tb1 output my.txt?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
>|||Thanks Jens...|||how about this:
osql -E -Q"backup Northwind to disk='c:\output1.txt " -o "C:\Output2.txt" -S
myServer
'c:\output1.txt : Server Path
'C:\Output2.txt : Local Path
can I run it in another worksation? and want to 'c:\output1.txt' located in
the workdation?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
> Yes of course, here is an example:
> osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
> will produuce the following output:
> OrderID CustomerID EmployeeID OrderDate
> RequiredDate ShippedDate ShipVia
> Freight ShipName
> ShipAddress
> ShipCity ShipRegion ShipPostalCode ShipCountry
> -- -- -- --
> -- -- --
> -- ---
> ----
> -- -- -- --
> 10248 VINET 5 1996-07-04 00:00:00.000
> 1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
> 32.3800 Vins et al

s Chevalier
> 59 rue de l'Abbaye
> Reims NULL 51100 France
> (1 row affected)
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Output will be redirected to the workstation due to the output of osql,
backup will be made to the server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
> how about this:
> osql -E -Q"backup Northwind to disk='c:\output1.txt " -o
> "C:\Output2.txt" -S myServer
> 'c:\output1.txt : Server Path
> 'C:\Output2.txt : Local Path
> can I run it in another worksation? and want to 'c:\output1.txt' located
> in the workdation?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
>|||can backup to disk=unc format?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
> Output will be redirected to the workstation due to the output of osql,
> backup will be made to the server.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||YEah that works,
Jens Suessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:OdHsAJQTFHA.2812@.TK2MSFTNGP09.phx.gbl...
> can backup to disk=unc format?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
>

Output 10 rows

Hi! I am a newbie, need to make a report with only 10 rows, and not the whole result. How do I actually specify the output as 10 rows?
Thank you so much in advance!

Depends on your exact circumstances.

If you want show 10 per page then you need to

put a list control in the body|||Thank you som much!

SELECT TOP 10 SUM(cd.price) as Sales, ... gave me a list of the most sold CD's + sales figures. How if I want a list including numbers (1 Elton John, 2 Madonna 3, 4, 5, 6, 7,8,9,10etc)
How do I make a list and a sequence of numbers on a report?

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?
|||

curiousss wrote:

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?

You can use a nested IIF within the group criteria to do this.

=IIF(Age < 16, "<16",IIF(Age <21,"16-20),IIF(Age<31,"21-30",">30")))

Note matching the parens can be a pain, but I am sure you will figure it out.

|||

Further to this, to create a sequence of numbers just use the RowNumber function.

=CStr(RowNumber(Nothing)) + " " + Fields!Album.Value

Output 10 rows

Hi! I am a newbie, need to make a report with only 10 rows, and not the whole result. How do I actually specify the output as 10 rows?
Thank you so much in advance!

Depends on your exact circumstances.

If you want show 10 per page then you need to

put a list control in the body|||Thank you som much!

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?
|||

curiousss wrote:

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?

You can use a nested IIF within the group criteria to do this.

=IIF(Age < 16, "<16",IIF(Age <21,"16-20),IIF(Age<31,"21-30",">30")))

Note matching the parens can be a pain, but I am sure you will figure it out.

|||

Further to this, to create a sequence of numbers just use the RowNumber function.

=CStr(RowNumber(Nothing)) + " " + Fields!Album.Value

Output !

I have a colunms which contain all the numeric data I want to change the
format in the runtime like
145263 that should be like 145,263.
Is there any way to do through sql query?
Thanks
Formatting is typically better off done on the client side.
Otherwise, you need to convert or cast the value to money to
get what you are after. Something like:
DECLARE @.YourValue integer
SET @.YourValue = 145263
SELECT reverse(substring(reverse(convert(varchar, cast
(@.YourValue as money), 1)),
4, 30))
-Sue
On Mon, 23 Jan 2006 14:00:54 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>I have a colunms which contain all the numeric data I want to change the
>format in the runtime like
>145263 that should be like 145,263.
>Is there any way to do through sql query?
>Thanks
>
|||Thanks dear !
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nagat119buf4sdbmbfnq3o035ag2am0g19@.4ax.com...
> Formatting is typically better off done on the client side.
> Otherwise, you need to convert or cast the value to money to
> get what you are after. Something like:
> DECLARE @.YourValue integer
> SET @.YourValue = 145263
> SELECT reverse(substring(reverse(convert(varchar, cast
> (@.YourValue as money), 1)),
> 4, 30))
> -Sue
> On Mon, 23 Jan 2006 14:00:54 -0500, "Rogers"
> <naissani@.hotmail.com> wrote:
>
sql

OUTPUT - help with please

I am using a dynamic t-sql string in proc1 to execute proc2, which returns an int variable named @.Fatal_Error back to proc1.

When I execute proc2 I use the syntax:

EXEC @.SQL @.Params

@.SQL is the Proc Name (varchar) and @.Params is the parameter string (nvarchar).

If I include the @.Fatal_Error variable in the dynamic creation of the @.Params string the returning value from Proc2 is unable to convert int to nvarchar.

I have declared @.Fatal_Error in proc1 as int and tried to add to the end of my dynamic t-sql EXEC but I still get 'Cannot convert int to nvarchar' .

Please help - I'm beginning to pull out hair! :-)

Thanks!

Here' s the syntax I tried when just passing it at the end of the EXEC call:

EXEC @.SQL @.Param_List = @.Fatal_Error

AND I also tried:

EXEC @.SQL @.Param_List + ' '+@.Fatal_Error+' '

You have to use the sp_executesql for parameterized dynamic sql,

Code Snippet

Declare @.idParm as int;

Declare @.nameParm as varchar(100);

Declare @.dynamicSql as nvarchar(1000);

Declare @.dynamicParamDef as nvarchar(1000);

Set @.idParm = 2;

Set @.nameParm = 'sysobjects'

Set @.dynamicSql = N'Select * from sysobjects where id=@.id or name=@.name'

Set @.dynamicParamDef = N'@.id as int, @.name as varchar(100)'

Exec sp_executesql @.dynamicSql, @.dynamicParamDef, @.idParm, @.nameParm

|||

I quote from BOL(look for sp_executesql, building statement at runtime)

Transact-SQL supports the following methods of building SQL statements at run time in Transact-SQL scripts, stored procedures, and triggers:

Use the sp_executesql system stored procedure to execute a Unicode string. sp_executesql supports parameter substitution similar to the RAISERROR statement.

Use the EXECUTE statement to execute a character string. The EXECUTE statement does not support parameter substitution in the executed string."|||...and Manivannan prove it|||

Thank you all for your assistance. I will give it a whirl.

I was able to finally execute with EXEC @.SQL @.Params, @.Fatal_Error = @.Fatal_Error

However, I'm sure this will come back to bite me in the long run..

Thank you ALL for your quick replies!

|||

@.SQL is my stored procedure name

@.Param_List is the list of enumerated parameters

SET @.SQL = @.SQL + IsNull(@.Param_List,'');

EXECUTE sp_executesql @.SQL;

Worked like a charm.

Thanks!

Sandy

output

How to get a field from a specific column as output parameter?
HrckoHi
You don't saying how you are accessing this, so assuming ADO check out
http://msdn.microsoft.com/library/d...
25v.asp
or
http://msdn.microsoft.com/library/d... />
ql01b1.asp
You may also want to check out the ADO sample that come with SQL Server.
John
"Hrvoje Voda" wrote:

> How to get a field from a specific column as output parameter?
> Hrcko
>
>

output

How to get a field from a specific column as output parameter?
HrckoHi
You don't saying how you are accessing this, so assuming ADO check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_525v.asp
or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01b1.asp
You may also want to check out the ADO sample that come with SQL Server.
John
"Hrvoje Voda" wrote:
> How to get a field from a specific column as output parameter?
> Hrcko
>
>

out-of-range smalldatatime value

I am trying to insert a rown into a table with this line in my program :

INSERT INTO MYTABLE (Usuario, Datahora, COD_PROG, Tabela, TipoMov, Registro, Campo, ValAnt, ValAtu, Motivo, Sequencial) VALUES (23, '06/28/2007 19:53:45', '002101', 'Contribuinte', 'A', '1006626', ' ', 'Isentou Tx 2a Via 062007', ' ', ' ', ' ')

and I am receiving this message :

The conversion of char data type to smalldatatime data type resulted in an out-of-range smalldatatime value.

Thanks

Hello

I suppose that the smalldatetime has the value 06/28/2007 19:53:45

what is the language used for the server ?

the format of the date is corresponding to month/day/year ( american format ).Maybe it's the origin of the problem.

For the french format ( dd/mm/yyyy ), you have an error ( 28 does not correspond to a month )

Have a good day

|||

The smalldatetime datatype does NOT include seconds. But it truncates the seconds. -That isn't the problem.

I suspect that your server is expecting a date in the form of 'dd/mm/yyyy', and you are providing 'mm/dd/yyyy'.

If you changed the date format on the INSERT data to the ISO standard of 'yyyy/mm/dd', you wouldn't have any problem.

|||Thank you, that's ok now.

out-of-range datetime value

Hi,

I have a page working with not problem since 3 years, and now that we've moved our database from a SQL Server 2000 to SQL Server 7 and i receive this error everywhere a date is used.

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error append on this line "While myReader.Read()" .. what can i do to solve this problem ?

Thank you

This may be caused by date format that differs from the SQL2000 instance to SQL7.0 instance. Try to change the DATEFORMAT option like this (can be set to mdy,ydm,and ymd) and test again:

SET DATEFORMAT mdy
GO

You can take a look at this link about converting to datetime type:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_27c5.asp?frame=true

sql

Friday, March 30, 2012

Output Column Width not refected in the Flat File that is created using a Flat File Destination?

Output Column Width not refected in the Flat File that is created using a Flat File Destination?

Output column size limit on query analyzer

Output column size limit on query analyzer

Output Column names in each Row along with the row Value

Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

OUTPUT Clause SQL Server 2005

OUTPUT Clause SQL Server 2005

OUTPUT Clause SQL Server 2005

OUTPUT Clause

Output Buffer Remove Row?

Output Buffer Remove Row?

OUTPUT and Recordset

Output an SQL Query to a text file

Output a report directly to PDF?

output 2 table data to a text file

Output 10 rows

Output 10 rows

Output !

OUTPUT - help with please

output

output

out-of-range smalldatatime value

out-of-range datetime value

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Orphaned Sessions and locks

Blog Archive

About Me