ROWNUM and ORDER BY

Am using Oracle 9i.

Just wanted to know in which order the db will execute my query if my query contains a 'WHERE ROWNUM < 1000' and an 'ORDER BY ...'.
The documentation says that the order of evauation depends upon the indexes used in the ORDER BY, but doesn't specify clearly in which order.

Please help.You can't use ROWNUM and ORDER BY in the same select because the pseudo column ROWNUM is affected before the sort.

So, you have to do :

Select ... FROM (SELECT ... FROM ... ORDER bY...) WHERE ROWNUM<1000

Row performance

Hi.
I have a simple question.
What is the maximum number of rows in a table before MS SQL takes more
time to execute a query or the performance will slow down ' Is it
possible that the number is 1 000 000 ? If I remember, I saw that on
the web but I'm not sure. Consider that my table have been indexed.
Am I right or wrong '
Thanks.
Jonathan Chretien
Analyst/ProgrammerI would also expect for a non-indexed table, that performance would scale
linearly corresponding directly to the number of records in the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
news:3F27BEA0.41743AB0@.amisco.moc...
> Hi.
> I have a simple question.
> What is the maximum number of rows in a table before MS SQL takes more
> time to execute a query or the performance will slow down ' Is it
> possible that the number is 1 000 000 ? If I remember, I saw that on
> the web but I'm not sure. Consider that my table have been indexed.
> Am I right or wrong '
>
> Thanks.
>
> Jonathan Chretien
> Analyst/Programmer
>|||Hi.
My table is correctly indexed. I have only one index and my query is base on the
same field of my index. This table come from an AS400 and when I execute this
query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
then my MS SQL Server. I execute the query when the server is not load,
approximatly an average of 2 to 5% and the maximum peak that I receive from my
SQL Server is 50%.
Specification of my MS SQL Server:
Version: MS SQL Server 7.0
CPU: 2 PII Xeon 550
Memory: 512 meg
Disk: 40 gig
Database size 1.6 gig
Avg CPU utilization: Between 10 to 20 %
Jonathan Chretien
Analyst/Programmer
Wayne Snyder a écrit :
> I would also expect for a non-indexed table, that performance would scale
> linearly corresponding directly to the number of records in the table.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it community
> of SQL Server professionals.
> www.sqlpass.org
> "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> news:3F27BEA0.41743AB0@.amisco.moc...
> > Hi.
> >
> > I have a simple question.
> >
> > What is the maximum number of rows in a table before MS SQL takes more
> > time to execute a query or the performance will slow down ' Is it
> > possible that the number is 1 000 000 ? If I remember, I saw that on
> > the web but I'm not sure. Consider that my table have been indexed.
> >
> > Am I right or wrong '
> >
> >
> > Thanks.
> >
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Yes.
Query plan is using my index and have a cost of 81%.
Jonathan Chretien
Analyst/Programmer
Gert-Jan Strik a écrit :
> Check the execution plan to see if SQL-Server is actually using the
> index. There might be some reason why the index is disqualified. For
> example when your statistics are out of date, or when your predicate is
> not SARGuable.
> Hope this helps,
> Gert-Jan
> Jonathan Chretien wrote:
> >
> > Hi.
> >
> > My table is correctly indexed. I have only one index and my query is base on the
> > same field of my index. This table come from an AS400 and when I execute this
> > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > then my MS SQL Server. I execute the query when the server is not load,
> > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > SQL Server is 50%.
> >
> > Specification of my MS SQL Server:
> > Version: MS SQL Server 7.0
> > CPU: 2 PII Xeon 550
> > Memory: 512 meg
> > Disk: 40 gig
> > Database size 1.6 gig
> > Avg CPU utilization: Between 10 to 20 %
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >
> > Wayne Snyder a écrit :
> >
> > > I would also expect for a non-indexed table, that performance would scale
> > > linearly corresponding directly to the number of records in the table.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it community
> > > of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > Hi.
> > > >
> > > > I have a simple question.
> > > >
> > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > time to execute a query or the performance will slow down ' Is it
> > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > the web but I'm not sure. Consider that my table have been indexed.
> > > >
> > > > Am I right or wrong '
> > > >
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > > > Jonathan Chretien
> > > > Analyst/Programmer
> > > >|||Jonathan,
1. Is it performing an index SEEK (good) or index SCAN (bad)?
2. Is it a clustered or non-clustered index?
Gert-Jan
Jonathan Chretien wrote:
> Yes.
> Query plan is using my index and have a cost of 81%.
> Jonathan Chretien
> Analyst/Programmer
> Gert-Jan Strik a écrit :
> > Check the execution plan to see if SQL-Server is actually using the
> > index. There might be some reason why the index is disqualified. For
> > example when your statistics are out of date, or when your predicate is
> > not SARGuable.
> >
> > Hope this helps,
> > Gert-Jan
> >
> > Jonathan Chretien wrote:
> > >
> > > Hi.
> > >
> > > My table is correctly indexed. I have only one index and my query is base on the
> > > same field of my index. This table come from an AS400 and when I execute this
> > > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > > then my MS SQL Server. I execute the query when the server is not load,
> > > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > > SQL Server is 50%.
> > >
> > > Specification of my MS SQL Server:
> > > Version: MS SQL Server 7.0
> > > CPU: 2 PII Xeon 550
> > > Memory: 512 meg
> > > Disk: 40 gig
> > > Database size 1.6 gig
> > > Avg CPU utilization: Between 10 to 20 %
> > >
> > > Jonathan Chretien
> > > Analyst/Programmer
> > >
> > > Wayne Snyder a écrit :
> > >
> > > > I would also expect for a non-indexed table, that performance would scale
> > > > linearly corresponding directly to the number of records in the table.
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > > www.computeredservices.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and it community
> > > > of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > > Hi.
> > > > >
> > > > > I have a simple question.
> > > > >
> > > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > > time to execute a query or the performance will slow down ' Is it
> > > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > > the web but I'm not sure. Consider that my table have been indexed.
> > > > >
> > > > > Am I right or wrong '
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
> > > > > Jonathan Chretien
> > > > > Analyst/Programmer
> > > > >

Row by Row Copy

I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:

1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...

2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.

The latter solution would be the most elegant, but I'll take what I can get...

how about this?

--

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

Syntax

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

Arguments

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

seed

Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the increment to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Return Types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, a name for the column must be specified in the select list in one of these ways:

--(1)

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable

--(2)

SELECT ID_Num = IDENTITY(int, 1, 1)

INTO NewTable

FROM OldTable

Examples

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employees')

DROP TABLE employees

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num,

fname AS first,

minit AS middle,

lname AS last,

IDENTITY(smallint, 100, 1) AS job_num,

job_lvl AS job_level,

pub_id,

hire_date

INTO employees

FROM employee

GO

USE pubs

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

|||

What version of SQL Server are you using? In SQL Server 2005, you can use OUTPUT clause in INSERT statement to get the generated identity values for multiple rows easily. See the link below for a post that describes one example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You can do the same in older versions by dumping the key values into a temporary table created in the SP from the trigger and accessing it from outside. This will still be much more efficient than what you are doing and run many times faster. And you kinda lost me on why you ned the declare list etc. You can do the looping without cursors.

|||

I'm using 2005. I need more than just the newly generated identity values...I also need the original identity values from the records being copied. Like:

old_ID new_ID

4 227

63 228

65 229

I need this "pairing" of old and new, because I am copying both "parents" and "children" of 1-to-many relationships. To fetch and copy the children, I need to know the parent's "copy from" old id (to get the children) and the parent's "copy to" new id (to correlate the new copies of children to the new parent ids).

So I don't really care to explicitly "handle" each and every column of the parent - I don't care what the contents of those columns are - I just want to copy them. So it is really annoying to have an "INTO" clause where I've got a comma delimited list of 40 columns...which I only ever intend on copying without ever inspecting. The only thing I am "inspecting" is the values of the identity (primary key) of the parent.

Perhaps SQL Server 2005 has a "do what I mean" stored proc I can execute...

|||

Ok. This is kind of tricky with OUTPUT clause and INSERT because you can only reference INSERTED table columns / expressions. You can however do it easily using a technique like below:

create table T ( i int not null identity primary key, j int null references T(i));
insert into T (j) values(null) ;
insert into T (j) values(scope_identity());
select * from T;

declare @.t table(i int not null);
set transaction isolation level serializable;
begin tran;
insert into T (j)
output inserted.i into @.t( i)
select j from T order by i;

select t2.i as old_i, t1.i as new_i
from (select i, ROW_NUMBER() OVER(order by i) from @.t) as t1(i, seq)
join (select i, ROW_NUMBER() OVER(order by i) from T) as t2(i, seq)
on t1.seq = t2.seq;

commit;

select * from T;

drop table T;

The trick is to insert the rows into the table in a particular order (you can choose multiple columns if you want). In the code above, the ORDER BY in the SELECT statement of INSERT ensures that the generated identity values are in the same order. You can then sequence the old and newly generated values & join based on the sequence. The serializable transaction is however required since it is not easy to protect the identity values generation in case of concurrent inserts to the table. This technique will work for you if this operation is an expensive one (replicating portions of tree) and you are doing it infrequently. This set-based approach should be much simpler than what you have but there is nothing in the SQL language to simplify column lists. You have to specify those you want to SELECT or use. That is how the language is defined.

|||

Wow, that is quite the solution. I think I need just a wee more help...since I am so rusty/inexperienced with T/SQL.

Firstly, you have a "select * from T" statement near the top of your script that doesn't seem to do anything. Is it just a piece of debug/sanity check output - or is it necessary for the solution?

Secondly, it seems to me that when I turn my attention towards copying records in the child table - I still might need to resort to explicit looping while using your "old-to-new" correlation query. That is, after I copy the child's records, I will need to change the foreign key on each child row, to reflect the new foreign keys in the parent. There are perhaps two ways I can think of to avoid such explicit looping:

1) If the child table also has an identity column (primary key), then I can repeat the magical "old-to-new" correlation query for the child table's insert as well. Then, after the insert (copy) on the child is done, I would do an update on the child's newly inserted rows - predicated on a 3-way inner join between the parent's "old-to-new" recordset, the child's "old-to-new" recordset, and the child itself. Phew!!!

2) After duplicating the relevant child records in the child, I would need to use a facility in SQL Server 2005 that allows me to perform an update only on the newly added child records. Perhaps this just means using the "inserted" virtual table again. As such, I perceive a simpler variation of proposal (1) above. That is, I "save" the contents of the "inserted" rows into a temp table. Then I perform an update predicated on a 3-way inner join between the parent's "old-to-new," the "inserted" temp table, and the child itself. This still supposes that the child has its own identity (primary key) column.

Even if you concur with either of the above strategies, is there an alternative you prefer?

|||The main logic is the part between the declare table and the commit tran. The rest of the code was just to show the rows. I didn't quite understand the part about copying child rows. Please post a simple DDL and data like in my example. And also the expected results so it will be easier to suggest a modified solution or show how my previous example can be used.|||

I made the effort to implement the rest of what I needed, and discovered that producing copies of the child records was nothing as difficult as I anticipated. I did not need a unique primary key in the children, and I did not need looping. Just an insert statement predicated on a simple join. Thats all.

So, in short, you have shown me how to produce all the copies I needed, without using looping structures. Therefore I will mark your primary response as the answer.

Row by Row Copy

I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:

1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...

2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.

The latter solution would be the most elegant, but I'll take what I can get...

how about this?

--

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

Syntax

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

Arguments

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

seed

Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the increment to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Return Types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, a name for the column must be specified in the select list in one of these ways:

--(1)

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable

--(2)

SELECT ID_Num = IDENTITY(int, 1, 1)

INTO NewTable

FROM OldTable

Examples

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employees')

DROP TABLE employees

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num,

fname AS first,

minit AS middle,

lname AS last,

IDENTITY(smallint, 100, 1) AS job_num,

job_lvl AS job_level,

pub_id,

hire_date

INTO employees

FROM employee

GO

USE pubs

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

|||

What version of SQL Server are you using? In SQL Server 2005, you can use OUTPUT clause in INSERT statement to get the generated identity values for multiple rows easily. See the link below for a post that describes one example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You can do the same in older versions by dumping the key values into a temporary table created in the SP from the trigger and accessing it from outside. This will still be much more efficient than what you are doing and run many times faster. And you kinda lost me on why you ned the declare list etc. You can do the looping without cursors.

|||

I'm using 2005. I need more than just the newly generated identity values...I also need the original identity values from the records being copied. Like:

old_ID new_ID

4 227

63 228

65 229

I need this "pairing" of old and new, because I am copying both "parents" and "children" of 1-to-many relationships. To fetch and copy the children, I need to know the parent's "copy from" old id (to get the children) and the parent's "copy to" new id (to correlate the new copies of children to the new parent ids).

So I don't really care to explicitly "handle" each and every column of the parent - I don't care what the contents of those columns are - I just want to copy them. So it is really annoying to have an "INTO" clause where I've got a comma delimited list of 40 columns...which I only ever intend on copying without ever inspecting. The only thing I am "inspecting" is the values of the identity (primary key) of the parent.

Perhaps SQL Server 2005 has a "do what I mean" stored proc I can execute...

|||

Ok. This is kind of tricky with OUTPUT clause and INSERT because you can only reference INSERTED table columns / expressions. You can however do it easily using a technique like below:

create table T ( i int not null identity primary key, j int null references T(i));
insert into T (j) values(null) ;
insert into T (j) values(scope_identity());
select * from T;

declare @.t table(i int not null);
set transaction isolation level serializable;
begin tran;
insert into T (j)
output inserted.i into @.t( i)
select j from T order by i;

select t2.i as old_i, t1.i as new_i
from (select i, ROW_NUMBER() OVER(order by i) from @.t) as t1(i, seq)
join (select i, ROW_NUMBER() OVER(order by i) from T) as t2(i, seq)
on t1.seq = t2.seq;

commit;

select * from T;

drop table T;

The trick is to insert the rows into the table in a particular order (you can choose multiple columns if you want). In the code above, the ORDER BY in the SELECT statement of INSERT ensures that the generated identity values are in the same order. You can then sequence the old and newly generated values & join based on the sequence. The serializable transaction is however required since it is not easy to protect the identity values generation in case of concurrent inserts to the table. This technique will work for you if this operation is an expensive one (replicating portions of tree) and you are doing it infrequently. This set-based approach should be much simpler than what you have but there is nothing in the SQL language to simplify column lists. You have to specify those you want to SELECT or use. That is how the language is defined.

|||

Wow, that is quite the solution. I think I need just a wee more help...since I am so rusty/inexperienced with T/SQL.

Firstly, you have a "select * from T" statement near the top of your script that doesn't seem to do anything. Is it just a piece of debug/sanity check output - or is it necessary for the solution?

Secondly, it seems to me that when I turn my attention towards copying records in the child table - I still might need to resort to explicit looping while using your "old-to-new" correlation query. That is, after I copy the child's records, I will need to change the foreign key on each child row, to reflect the new foreign keys in the parent. There are perhaps two ways I can think of to avoid such explicit looping:

1) If the child table also has an identity column (primary key), then I can repeat the magical "old-to-new" correlation query for the child table's insert as well. Then, after the insert (copy) on the child is done, I would do an update on the child's newly inserted rows - predicated on a 3-way inner join between the parent's "old-to-new" recordset, the child's "old-to-new" recordset, and the child itself. Phew!!!

2) After duplicating the relevant child records in the child, I would need to use a facility in SQL Server 2005 that allows me to perform an update only on the newly added child records. Perhaps this just means using the "inserted" virtual table again. As such, I perceive a simpler variation of proposal (1) above. That is, I "save" the contents of the "inserted" rows into a temp table. Then I perform an update predicated on a 3-way inner join between the parent's "old-to-new," the "inserted" temp table, and the child itself. This still supposes that the child has its own identity (primary key) column.

Even if you concur with either of the above strategies, is there an alternative you prefer?

|||The main logic is the part between the declare table and the commit tran. The rest of the code was just to show the rows. I didn't quite understand the part about copying child rows. Please post a simple DDL and data like in my example. And also the expected results so it will be easier to suggest a modified solution or show how my previous example can be used.|||

I made the effort to implement the rest of what I needed, and discovered that producing copies of the child records was nothing as difficult as I anticipated. I did not need a unique primary key in the children, and I did not need looping. Just an insert statement predicated on a simple join. Thats all.

So, in short, you have shown me how to produce all the copies I needed, without using looping structures. Therefore I will mark your primary response as the answer.

Row and column count in SSMS

When you execute an MDX query the Messages tab displays the number of rows and columns returned.

However, that includes the rows and columns used to hold the member names. Personally I would prefer it only returned the rows and columns in the cellset.

Anyone agree/disagree? Discuss!!!

Also, a minor point. I'd like it if the rows and columns were displayed on the results tab like the number of rows are in a SQL query. Anyone agree?

cheers

Jamie

Hi Jamie,

The row and column counts returned for empty axes are also a bit inconsistent :

>>

select {} on 0
from [Adventure Works]
-
Cell set consists of 1 rows and 0 columns.

-
select {} on 0,
{} on 1
from [Adventure Works]
-
Cell set consists of 0 rows and 0 columns.

>>

The separate Message Tab takes some getting used to, but it works OK for me...

|||

Interesting, thanks Deepak.

Anyone else?

round money data type 2 decimal

How can I execute a query that will round a money data
type field to 2 decimal places. I have:
SELECT
AmountReceipt
FROM
InvoiceReceipt
WHERE
InvoiceID = 'xxxxxx'
I need to return 3099.93 instead of 3099.9300 because I
need to compare it to something else that has already
been rounded out.
TIA,
Vicyou're example may not be the best. The two numbers you use are in fact the
same. How do you want rounding to happen and how did you round the other
number? You could cast the value as decimal with a scale of 2. See Books
Online...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Vic" <vduran@.specpro-inc.com> wrote in message
news:12f0201c411fd$547ddfb0$a401280a@.phx
.gbl...
> How can I execute a query that will round a money data
> type field to 2 decimal places. I have:
> SELECT
> AmountReceipt
> FROM
> InvoiceReceipt
> WHERE
> InvoiceID = 'xxxxxx'
> I need to return 3099.93 instead of 3099.9300 because I
> need to compare it to something else that has already
> been rounded out.
> TIA,
> Vic

Rougue CR/LF in my bcp output flat file!?!?!

The following bcp command is present in an NT job I have scheduled each daybcp "EXECUTE DailyProd.dbo.GetIndexComponentStocks_XML '2006-04-05'" queryout "D:\TABLES\INPUTAP\IndexComponentStocks.wrk"
-S(local) -c -T -o"..\IndexComponenet_XML_LOG.txt" -e"..\IndexComponenet_XML_ERR.txt"
My trouble is that every 2034 characters the output contains a :0D:0A (CR/Linefeed).

Other than that all the output data looks peachy.

Is there some line size setting I am missing (would it be packetsize?)?

Ideally I would just like to not have any CR/LF in my file at all...is there some way to turn it off? I see there are flags to set to change the column and row terminators, but how to turn them OFF?

Thanks much!
PaulYup, just had to add the -r and -c flags without any character following them to turn 'em off.