Combining Output PDF files

Is there a way to output reports to the same PDF file. Basicly Appending several reports to the one PDF.

Without combining the reports using subreports, I think you would use code & custom PDF components, unless you want to jump into writing a rendering extension.

Softartisians Officewriter & abcPDF.NET are two possible options. You can also try automating Acrobat.

http://www.planetpdf.com/forumarchive/84747.asp

One alternative may be to export to a different format and then convert to pdf afterwards.

cheers,

Andrew

|||

we're trying to do this on the report server with out having to add another process.

Is there any good tutorials for writing a rendering extension? I've heard its difficult.

|||

Not sure if it has changed since RS 2000, but here is a comment on how difficult...

http://blogs.msdn.com/bryanke/archive/2004/03/16/90797.aspx

If you want to develop one, it might help to travel to Microsoft's main campus and stay for a month or two while you work alongside Reporting Services developers.

cheers,

Andrew

|||

LOL, Yeah I saw that, showed it to my boss. He said no to funding that trip..Oh well...LOL

Doubt they would let me in anyways

But hey if Microsoft wants to use me as a test case, I'm all for it!!! {WINK WINK, NUDGE NUDGE}

sqlsql

Combining Data from Multiple tables

I have a requirment to take data from a large set of tables where the total number of these tables may change on a regular baisis and output into another table. All the tables willl have the same columns. Frequency is being debated but it maybe as much as once per hour.

Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.

2) then I need the following
for each of the tables found above, I need the outfrom from each of those tables to be inputted into another table. basically, I would want the following output from each of the tables found in step 1

select machineid,name from _cpy_offermanager_678

3) In the end I would have something like dbo.ALLCPY with records combined from all other _CPY tables

Ron SorrellWhat about this idea? This draft does not work properly because of filed name does not exists for all tables - but you modify for your case.

declare @.union varchar(8000)
set @.union='insert alltables'+char(13)
select @.union=@.union+' select name from '+name+char(13)+'union all'+char(13) from sysobjects where xtype='U'
select @.union=left(@.union,DATALENGTH(@.union)-10)
exec( @.union)

combining columns into one table

What is the easiest way to combine the output of a several selects on a table and have each output become a column on a new table?
Thanks
Joel
Assuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel
|||well not exactly what I wanted - here's what I'm looking for. for example, suppose you have one table A with 3 columns as shown below:
oid name desc
-- -- --
1 vase container
2 lamp light
1 desk furniture
2 table furniture
1 table furniture
1 lamp light
then execute "select desc from A where oid=1 and desc=container" -- with result
container
and then execute "select desc from A where oid=1 and desc=furniture" -- with result
furniture
furniture
what I want to do is combine both outputs into 2 columns like this:
container furniture
furniture
furniture
Actually the queries and tables are more involved than this simple example but I hope I am getting the concept across.
Thanks
Joel
|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel
|||You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that I could build the table and then the client (ColdFusion) would iterate thru each row and present the row values via an HTML table. And yes, ther
e really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a column is added to the table thereby increasing the number of columns by 1 each time a column is added? Also when one column (with all rows containing values) to be added is longer
that the table to be added to, then will extra rows (which can be empty) be added so that all columns have same number of rows?
Thanks
Joel
|||Joel,
A table consists of a number of rows, where each row has the same column structure and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname ...". If the table is a result
from a SELECT statement, then you define that structure by the column list in the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows where each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The values for each column in a row is
restricted by the datatype that the column has, and a column can also possibly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a database and also the result of
SELECT statements) with presentation of the data (what you do in a client application).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same row.
> But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added? Also when one column (with all rows
containing values) to be added is longer that the table to be added to, then will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel
sqlsql

combining columns into one table

What is the easiest way to combine the output of a several selects on a tabl
e and have each output become a column on a new table?
Thanks
JoelAssuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel|||well not exactly what I wanted - here's what I'm looking for. for example,
suppose you have one table A with 3 columns as shown below:
oid name desc
-- -- --
1 vase container
2 lamp light
1 desk furniture
2 table furniture
1 table furniture
1 lamp light
then execute "select desc from A where oid=1 and desc=container" -- with r
esult
container
and then execute "select desc from A where oid=1 and desc=furniture" -- wi
th result
furniture
furniture
what I want to do is combine both outputs into 2 columns like this:
container furniture
furniture
furniture
Actually the queries and tables are more involved than this simple example b
ut I hope I am getting the concept across.
Thanks
Joel|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel|||You're right, it is a report that will be displayed via ColdFusion on a dyna
mic web page. I was hoping that I could build the table and then the client
(ColdFusion) would iterate thru each row and present the row values via an
HTML table. And yes, ther
e really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a colu
mn is added to the table thereby increasing the number of columns by 1 each
time a column is added? Also when one column (with all rows containing valu
es) to be added is longer
that the table to be added to, then will extra rows (which can be empty) be
added so that all columns have same number of rows?
Thanks
Joel|||Joel,
A table consists of a number of rows, where each row has the same column str
ucture and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a co
lumn is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname
...". If the table is a result
from a SELECT statement, then you define that structure by the column list i
n the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row
which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows wher
e each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The valu
es for each column in a row is
restricted by the datatype that the column has, and a column can also possib
ly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype
for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a d
atabase and also the result of
SELECT statements) with presentation of the data (what you do in a client ap
plication).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic we
b page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru
each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same r
ow.
> But as a general question is it possible to manufacture a table where a column is
added to the table thereby
increasing the number of columns by 1 each time a column is added? Also whe
n one column (with all rows
containing values) to be added is longer that the table to be added to, the
n will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel

combining columns into one table

What is the easiest way to combine the output of a several selects on a table and have each output become a column on a new table
Thank
JoelAssuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel|||well not exactly what I wanted - here's what I'm looking for. for example, suppose you have one table A with 3 columns as shown below
oid name des
-- -- --
1 vase containe
2 lamp ligh
1 desk furnitur
2 table furnitur
1 table furnitur
1 lamp ligh
then execute "select desc from A where oid=1 and desc=container" -- with resul
containe
and then execute "select desc from A where oid=1 and desc=furniture" -- with resul
furnitur
furnitur
what I want to do is combine both outputs into 2 columns like this
container furnitur
furnitur
furnitur
Actually the queries and tables are more involved than this simple example but I hope I am getting the concept across
Thank
Joel|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel|||You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that I could build the table and then the client (ColdFusion) would iterate thru each row and present the row values via an HTML table. And yes, there really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a column is added to the table thereby increasing the number of columns by 1 each time a column is added? Also when one column (with all rows containing values) to be added is longer that the table to be added to, then will extra rows (which can be empty) be added so that all columns have same number of rows
Thank
Joel|||Joel,
A table consists of a number of rows, where each row has the same column structure and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname ...". If the table is a result
from a SELECT statement, then you define that structure by the column list in the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows where each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The values for each column in a row is
restricted by the datatype that the column has, and a column can also possibly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a database and also the result of
SELECT statements) with presentation of the data (what you do in a client application).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same row.
> But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added? Also when one column (with all rows
containing values) to be added is longer that the table to be added to, then will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel

combine pdf with reporting services pdfs

Through another software package, I generate pdfs which I save. I now want to combine those pdf with pdf output from reporting services. how can i do this?

thks

ken

Hi Ken-

You'd need to create your own delivery extension to do that. I imagine you could add the PDF to the RS rendered PDFs in the Deliver method of your custom delivery provider. Check out the PrinterDeliverySample in the Samples folder where you installed RS. This example can give you an idea of what you need to do to set this up.

Scott

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:
> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> > Can someone tell me what defines the order of SQL output columns when i
> > use a
> > default query on a table like "SELECT * from <Table> ".
> >
> > Is there a way to alter the default ORDER of these columns?
> >
> > I am aware of the ORDER BY but will not be able to use it for various
> > reasons.
> >
> > Regards
> > Bk
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
Bk
The order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use
a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/artic...blog_92886.html
http://www.sqlmag.com/article/artic...blog_92887.html
http://www.sqlmag.com/article/artic...blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting ligh
t
> on the subject:
> http://www.sqlmag.com/article/artic...blog_92886.html
> http://www.sqlmag.com/article/artic...blog_92887.html
> http://www.sqlmag.com/article/artic...blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i us
e a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
Thanks
Found the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>
|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>
|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
ThanksFound the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
ThanksFound the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>>I want to see for every stored procedure that completes, how many rows
>>were returned as output ? Is there a column in profiler that shows that
>>value or is there any other way to get it as opposed to running every
>>query manually on my Management Studio session.
>> Using SQL 2005.
>> Thanks
>

Column Heading in Output File

Hi
I am running a SQL query and printing the output to a file, as well as
showing it in the Grid. The Grid has column headings, my file does not. Is
there a way of getting my column headings into the output file?
To create the output file I am using the following;
EXEC master..xp_cmdshell 'bcp "DATABASE.dbo.FILE" out
"C:\SQLSCRIPT\OUTPUT.txt" -c -t, -q -Sserver -U"username" -P"password"'
GO
Any help appreciated.
SteveBCP will not export the headings. You can use OSQL to do that.
Rand
This posting is provided "as is" with no warranties and confers no rights.

column data in the single row

Hi,
I have a table such as

ID Name OS
-----------
10 Paul AIX
10 Paul SOLARIS
10 Paul NT
20 Jack NT
20 Jack SOLARIS

and I have asked to create an output as

ID NAME OS
-----------
10 Paul AIX,SOLARIS,NT
20 JAck NT,SOLARIS

How can I get this output via sql.
Also a good source for such tricky SQLs would be very fruity.Hi Faar,

If this is a one-time deal for a report, then I would go ahead and plug the dreaded cursor within a cursor. If your example table is named testing and is defined as such:

create table testing
(
ID int,
Name varchar(30),
OS varchar(100)
)

-and your values are as you provided. Then the code below should work:

declare @.id int,
@.name varchar(30),
@.OS varchar(100),
@.CurrentOS varchar(100)

create table #formatted
(
ID int,
Name varchar(30),
OS varchar(100)
)

declare person cursor for
select id, name from testing
group by id, name

open person

fetch person into @.id, @.name
while @.@.fetch_status = 0
begin
set @.OS = ''
declare OS cursor for
Select OS from testing
where ID = @.ID
group by OS

open OS
fetch OS into @.CurrentOS

while @.@.fetch_status = 0
begin
set @.OS = @.OS + @.CurrentOS + ', '
fetch OS into @.CurrentOS
end
Set @.OS = Left(@.OS,LEN(@.OS)-1)
close OS
deallocate OS

insert #formatted (id, name, os)
values (@.id, @.name, @.OS)

fetch person into @.id, @.name
end
close person
deallocate person

select * from #formatted

drop table #formatted

--This is pretty much textbook for bad sql - but if you only need to to this once I wouldn't worry about it. If you need to do this regularly, there are better performing methods than the cursors such as cycling through a table variable.

good luck.|||Warning! Untested code. May have syntax errors...
create function OSList(@.ID integer)
returns varchar(500)
as
begin
declare @.ReturnValue varchar(500)
select @.ReturnValue = isnull(@.ReturnValue + ', ', '') + OS
from [YourTable]
where ID = @.ID
order by OS
Return @.ReturnValue
end

To execute:select distinct
ID,
Name,
dbo.OSList(ID)
from [YourTable]|||hi,
"create function " suggestion works very well.
thanks everybody.

Do you know a good source for such tricky SQLs?|||Just search any of the SQL Server forum like :
SQLTeam.com
SQLServerCentral.com
SQL-Server-Performance.com
forums.microsoft.com|||Celko has written good books on SQL.