Combine two queries

Hello, i am an SMS guy trying to write some SQL queries and having little
luck. I have two queries. The first returns the dept code which in my case
is the first 2 letters a computer and a count of computers with that dept
code(see below).
Total counts by dept code
SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM v_R_System
GROUP BY LEFT(Name0, 2)
Second is a query that returns the dept code and a count of machines that
have the client installed.
Totals counts installed by dept code
SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM v_R_System where client0=1
GROUP BY LEFT(Name0, 2)
My question is this... How the heck can I combine the two into one query so
I return Dept code, total machines, and total machines with client?
Thanks for helping out this rookie...
scottYou can use a CASE like:
SELECT LEFT( Name0, 2 ) AS "dept_code",
COUNT( * ) AS "total_machines",
SUM( CASE WHEN client0 = 1 THEN 1 ELSE 0 END ) AS "client0_count"
FROM v_R_System
GROUP BY LEFT( Name0, 2 ) ;
Anith|||Try,
SELECT
LEFT(Name0, 2) AS [dept Code],
client0,
COUNT(*) AS [Total Machines],
(select count(*) from v_R_System as t1 where LEFT(t1.Name0, 2) =
LEFT(v_R_System.Name0, 2)) as total_dept_comp
FROM
v_R_System
GROUP BY
LEFT(Name0, 2),
client0
order by
LEFT(Name0, 2),
client0
AMB
"scott" wrote:

> Hello, i am an SMS guy trying to write some SQL queries and having little
> luck. I have two queries. The first returns the dept code which in my ca
se
> is the first 2 letters a computer and a count of computers with that dept
> code(see below).
> Total counts by dept code
> SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM v_R_System
> GROUP BY LEFT(Name0, 2)
> Second is a query that returns the dept code and a count of machines that
> have the client installed.
> Totals counts installed by dept code
> SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM v_R_System where client0=1
> GROUP BY LEFT(Name0, 2)
> My question is this... How the heck can I combine the two into one query
so
> I return Dept code, total machines, and total machines with client?
> Thanks for helping out this rookie...
> scott
>sqlsql

Combine results into one field

If I do a basic query
select * from grouping where code='12345'
I get the results
(fields are code,result)
12345 aaaaa
12345 assas
12345 f5fgh
I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
just want one result. Possible?
*** Sent via Developersdex http://www.examnotes.net ***http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:eUn4NjrTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> If I do a basic query
> select * from grouping where code='12345'
> I get the results
> (fields are code,result)
> 12345 aaaaa
> 12345 assas
> 12345 f5fgh
>
> I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
> just want one result. Possible?
> *** Sent via Developersdex http://www.examnotes.net ***

columns_updated compatibility between sql2000 and sql 2005

Hi,
I am working on a trigger that could be installed on both sql2000 and
sql2005, so the code has to work on both systems.
The trigger uses COLUMNS_UPDATED() function to determine which fields were
updated, as BOL for sql 2005 indicate there is a slight difference
in this function parameters: if you work with sql2000 you can use
ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
calculations and then use the value with columns_updated, in case of sql2005
you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
does not work properly in sql 2000.
My question: Is it possible to write a single trigger that uses
columns_updated and works on both versions, if not how to distinguish
between 2 versions in a trigger,
e.g. if ver2000 set @.var = ....
else if ver2005 set @.var=...
Please let me know if the question is not clear I'll try to add more info.
Thank you
VadimHi
The ColumnId property is new in SQL 2005, therefore earlier versions would
return NULL so you could try something like:
ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
You could use the columnid and other information directly from syscolumns if
you aren't concerned about using system catalogues.
If you want to check SQL Server version look at
SELECT SERVERPROPERTY('ProductVersion')
other ways are listed at
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John
"Vadim" wrote:
> Hi,
> I am working on a trigger that could be installed on both sql2000 and
> sql2005, so the code has to work on both systems.
> The trigger uses COLUMNS_UPDATED() function to determine which fields were
> updated, as BOL for sql 2005 indicate there is a slight difference
> in this function parameters: if you work with sql2000 you can use
> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
> calculations and then use the value with columns_updated, in case of sql2005
> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
> does not work properly in sql 2000.
> My question: Is it possible to write a single trigger that uses
> columns_updated and works on both versions, if not how to distinguish
> between 2 versions in a trigger,
> e.g. if ver2000 set @.var = ....
> else if ver2005 set @.var=...
> Please let me know if the question is not clear I'll try to add more info.
> Thank you
> Vadim
>
>|||John,
Thank you very much, that's exactly what I needed, it worked.
Vadim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F8A7747-8E97-432B-A722-A50801BDB805@.microsoft.com...
> Hi
> The ColumnId property is new in SQL 2005, therefore earlier versions would
> return NULL so you could try something like:
> ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
> You could use the columnid and other information directly from syscolumns
> if
> you aren't concerned about using system catalogues.
> If you want to check SQL Server version look at
> SELECT SERVERPROPERTY('ProductVersion')
> other ways are listed at
> http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
>
> John
> "Vadim" wrote:
>> Hi,
>> I am working on a trigger that could be installed on both sql2000 and
>> sql2005, so the code has to work on both systems.
>> The trigger uses COLUMNS_UPDATED() function to determine which fields
>> were
>> updated, as BOL for sql 2005 indicate there is a slight difference
>> in this function parameters: if you work with sql2000 you can use
>> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
>> calculations and then use the value with columns_updated, in case of
>> sql2005
>> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' +
>> TABLE_NAME),
>> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter
>> version
>> does not work properly in sql 2000.
>> My question: Is it possible to write a single trigger that uses
>> columns_updated and works on both versions, if not how to distinguish
>> between 2 versions in a trigger,
>> e.g. if ver2000 set @.var = ....
>> else if ver2005 set @.var=...
>> Please let me know if the question is not clear I'll try to add more
>> info.
>> Thank you
>> Vadim
>>

Column Width

How can you widen or shorten a column's width when the report is run?
The width doesn't allow custom code or expressions.
Basically, based on a parameter I pass in, I want to widen or shrink a
column's width.Anyone?
"JSF" wrote:
> How can you widen or shorten a column's width when the report is run?
> The width doesn't allow custom code or expressions.
> Basically, based on a parameter I pass in, I want to widen or shrink a
> column's width.|||JSF,
It doesn't look like it's possible to dynamically change column width.
I just did another search through the group and found nothing new.

Column name as variable

Does anyone know if I can use a variable for a column name in a query?
I'm wanting to reuse some code that updates a table, but, depending on
conditions, I want it to update a different column.
Something like:
declare @.col_name as ?
Select @.col_name = "last_week"
update tblTest set @.col_name = blah blah blah
Perspiring minds want to know.
DS
Hello,
You may need to use dynamic sql for this. Take a look into EXEC and
SP_EXECUTESQL in books online
Thanks
Hari
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178038066.444666.41510@.y5g2000hsa.googlegrou ps.com...
> Does anyone know if I can use a variable for a column name in a query?
> I'm wanting to reuse some code that updates a table, but, depending on
> conditions, I want it to update a different column.
> Something like:
> declare @.col_name as ?
> Select @.col_name = "last_week"
> update tblTest set @.col_name = blah blah blah
> Perspiring minds want to know.
> DS
>
|||On May 1, 9:54 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> You may need to use dynamic sql for this. Take a look into EXEC and
> SP_EXECUTESQL in books online
> Thanks
> Hari
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178038066.444666.41510@.y5g2000hsa.googlegrou ps.com...
>
>
>
>
>
> - Show quoted text -
Gracias. That looks promising.

Column name as variable

Does anyone know if I can use a variable for a column name in a query?
I'm wanting to reuse some code that updates a table, but, depending on
conditions, I want it to update a different column.
Something like:
declare @.col_name as '
Select @.col_name = "last_week"
update tblTest set @.col_name = blah blah blah
Perspiring minds want to know.
DSHello,
You may need to use dynamic sql for this. Take a look into EXEC and
SP_EXECUTESQL in books online
Thanks
Hari
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
> Does anyone know if I can use a variable for a column name in a query?
> I'm wanting to reuse some code that updates a table, but, depending on
> conditions, I want it to update a different column.
> Something like:
> declare @.col_name as '
> Select @.col_name = "last_week"
> update tblTest set @.col_name = blah blah blah
> Perspiring minds want to know.
> DS
>|||On May 1, 9:54 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> You may need to use dynamic sql for this. Take a look into EXEC and
> SP_EXECUTESQL in books online
> Thanks
> Hari
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Gracias. That looks promising.

Column name as variable

Does anyone know if I can use a variable for a column name in a query?
I'm wanting to reuse some code that updates a table, but, depending on
conditions, I want it to update a different column.
Something like:
declare @.col_name as '
Select @.col_name = "last_week"
update tblTest set @.col_name = blah blah blah
Perspiring minds want to know.
DSHello,
You may need to use dynamic sql for this. Take a look into EXEC and
SP_EXECUTESQL in books online
Thanks
Hari
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
> Does anyone know if I can use a variable for a column name in a query?
> I'm wanting to reuse some code that updates a table, but, depending on
> conditions, I want it to update a different column.
> Something like:
> declare @.col_name as '
> Select @.col_name = "last_week"
> update tblTest set @.col_name = blah blah blah
> Perspiring minds want to know.
> DS
>|||On May 1, 9:54 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> You may need to use dynamic sql for this. Take a look into EXEC and
> SP_EXECUTESQL in books online
> Thanks
> Hari
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
>
> > Does anyone know if I can use a variable for a column name in a query?
> > I'm wanting to reuse some code that updates a table, but, depending on
> > conditions, I want it to update a different column.
> > Something like:
> > declare @.col_name as '
> > Select @.col_name = "last_week"
> > update tblTest set @.col_name = blah blah blah
> > Perspiring minds want to know.
> > DS- Hide quoted text -
> - Show quoted text -
Gracias. That looks promising.

Column insert help

Hi,

I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..

I'llshow my code, and the bold is explaining what I want inserted and wherefrom. I'd apprechiate if someone could help me with syntax etc. Thereare 2 about getting value from another table and one about just puttingin straight forward text..:

command.CommandText ="INSERT INTO Messages (sendername,recievername,message,Date,subject)VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

Thanks alot if anyone can help me with those three things..

Jon

Pls help!

|||

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

Could you further explain that.

Thanks

|||

Hi, sorry not very well explained!

The bold writing means:

I have a talbe called 'Transactions' - and for every row in which Itemid = Itemid, the username is retrieved. So this could be 1 username, or many, depending on how many people have the same Itemid in their row. I guess the usernames would have to be separated by commas.

Thanks!

Jon

|||

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

You are trying to do sub query's with your parameters. You cant do that. Also, I would think that you would want one record for each value. I would probably create a table with one record for each user, another Table (ex. Transactions with a UserId) with one record for each transactions and another table (ex. Items) with a TransactionId. If not you are going to have a hard time with your data. Then you could just simply do one select. Something like this:
SELECT ui.UserName, t.TransactionId, i.Item FROM UserId ui INNER JOIN Transactions t ON t.UserId = ui.UserID INNER JOIN Items i ON i.TransactionId = t.TransactionId WHERE ui.UserId = @.UserId

That would get you every record based on the userid with out having to do all that funky stuff. You dont really ever want to insert values(espcially with orders) by CSV's. Maintenance nightmare.

Does that make sense?

|||

Hi, I'll have to go through what you said and ask in more detail sorry.

What do you mean by sub query's?

What do you mean by one record for each value? Create a table for each value?

I will explain my scenario maybe it will help:

I have a message system within the site, and the method above is to send a 'bulk message' - that is sending the same message to many people.

My messages table has the columns @.sendername, @. recievername etc.. From this people check the messages through a formview which shoes the messages which reciever name is their username.

This mass message is sent by a user. He presses a button on a formview which contains an Itemid value. - then the button adds all the users in the transactions table which contain the same itemid value.

The second bold bit - this is to insert the value of the paymentinstructions (from 'items' table) of the user (who is sending the message).

Last bit - just universal text that gets inserted every time the same.

Hope this deeper explanation helps things, thanks for helping

Jon

|||

SO i would create a UserMessage(Or something like that) table that has a foreign key UserId in one table, and then create a second table called Messages which has a messageId (foreign key to UserMessage) and the other columns would be SenderName, ReceiverName, Message, PaymentInstructios etc...

Then you can get all messages by sending in one USerId and when you insert you can simply provide one UserId.

Make sense?

|||

Hi, before I try your method, can your hear this out -

I have a page with gridviews that retrieve the data that I want to insert - from 'SqlDataSource1' and 2 etc etc.

Can I just set the parameter to SqlDataSource1? Its on the same page..

I.e.:

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.AddWithValue("@.recievername", SqlDataSource2);
command.Parameters.AddWithValue("@.message", SqlDataSource3);
command.Parameters.AddWithValue("@.subject", TextBox1.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

Thanks

Jon

|||

Yes, you can do it that way, but you are hitting the DB 3 times to retrieve the data you need. That is a sign of your tables not being normalized. I would think that you would want a one to many with Users and MessageId table and then a one to many table with MessageId table and Messages. This would allow you to only hit the db one time and retrieve the data you want.

|||

Hi thanks for your help. I think im nearly there so im going to post each individual error message that I have up, and see what people make of them!

Thanks again,

Jon

column formula

Hi everybody,

I have an company table and it has 2 columns, Company Code and User Code, I am incrementing "User Code" with "column identity" property of MS SQL Server 2K. I have different companies and those companies have different users. When I increment User Code one by one, of course it doesnt consider whether it is the same company or not.

Question 1: How can I satisfy this condition below?

EX:

company user
1---1
1---2
2---3
2---4
3---5
4---6

What I want is

company user
1---1
1---2
1---3
2---1
2---2
3---1
3---2
3---3

etc.


Question 2: I want to know that whether it is possible to do that by writing column formula or not?Question 1: How can I satisfy this condition below?
EX:
company user
1---1
1---2
2---3
2---4
3---5
What I want is
company user
1---1
1---2
1---3
2---1
2---2
3---1
Question 2: I want to know that whether it is possible to do that by writing column formula or not?

A1 One approach in supporting such a business requirement: one may implement a "key assignment" table that privately tracks and assigns user ID numbers for each company.

A2 It is not exactly clear what is meant by a "column formula"? However, the built in MS Sql Server 2k identity column support / functionality likely won't help much in implementing a typical "key assignment" table. (A "key assignment" table approach, as described in A1, would likely be implemented primarily using stored procedures / triggers, and / or user functions).|||/*
create table companyuser (
"user" int identity(1,1) primary key
,company int not null
)
*/

--ad 1
select
company
,newusernum=(select count(*) from companyuser cu2 where cu1.company=cu2.company and cu1."user"<=cu2."user")
,origusernum="user"
from companyuser cu1

--OR on large table
create table companyuserTMP (
"id" int identity(1,1) primary key
,origusernum int null
,company int not null
)
insert companyuserTMP(company,origusernum)
select company,"user"
from companyuser
order by company,"user"
select
tmp.company
,newusernum=tmp."id"-XXX."id"+1
,origusernum
from companyuserTMP tmp
join (
select "id"=min("id"),company
from companyuserTMP
group by company
) XXX on tmp.company=XXX.company
drop table companyuserTMP

--ad 2-- computed columns can use one row information only, use TR

column formula

Hi everybody,

I have an company table and it has 2 columns, Company Code and User Code, I

am incrementing "User Code" with "column identity" property of MS SQL Server

2K. I have different companies and those companies have different users. When

I increment User Code one by one, of course it doesnt consider whether it is

the same company or not.

Question 1: How can I satisfy this condition below?

EX:

company user
1---1
1---2
2---3
2---4
3---5
4---6

What I want is

company user
1---1
1---2
1---3
2---1
2---2
3---1
3---2
3---3

etc.


Question 2: I want to know that whether it is possible to do that by writing

column formula or not?You may want to add a third column for that information, so that you can have a column with PK for joining other tables to. Are you asking whether a script can be written to modify the user codes after they're entered, or as they're being entered?|||it's ok now, thanx for help

Column Alias Behavior

I don't know why I never noticed this before, but in the code below, why can
I ORDER BY an aliased column, but I have to use the *actual* column name or
expression in the GROUP BY?
I would really rather say "GROUP BY Column1, Column2"
CREATE TABLE msl_T1 (
col1 INT,
col2 INT,
col3 INT
)
GO
SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
FROM msl_T1
GROUP BY col1, col2 * 2 -- RIGHT HERE!!
ORDER BY Column3
DROP TABLE msl_T1
GO
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneYou can use column aliases in the ORDER BY, but nowhere else. You can use a
derived table to circumvent this:
SELECT Column1, Column2, min (col3) Column3
FROM
(
SELECT col1 AS Column1, col2 * 2 AS Column2, col3
FROM msl_T1
) X
GROUP BY Column1, Column2
ORDER BY Column3
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eMlNTrE9FHA.4084@.TK2MSFTNGP10.phx.gbl...
>I don't know why I never noticed this before, but in the code below, why
>can I ORDER BY an aliased column, but I have to use the *actual* column
>name or expression in the GROUP BY?
> I would really rather say "GROUP BY Column1, Column2"
> CREATE TABLE msl_T1 (
> col1 INT,
> col2 INT,
> col3 INT
> )
> GO
> SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
> FROM msl_T1
> GROUP BY col1, col2 * 2 -- RIGHT HERE!!
> ORDER BY Column3
> DROP TABLE msl_T1
> GO
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> You can use column aliases in the ORDER BY, but nowhere else.
HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
"PTHPTHPTPTH" :-)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||It's not a question of consistency.
5aa6fc669a8" target="_blank">http://groups.google.ca/group/comp.../>
5aa6fc669a8
and read Joe Celko's explanation on Select.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eWwqf5E9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
> "PTHPTHPTPTH" :-)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

Colour My World

In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
the QA color coding? When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white.
Thanks!>> In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code
Not sure if direct printing is possible or not. This might be silly but if
the code is small enough to fit the screen, you could use a screen print to
a word document and then print it.
Anith|||>> When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white. <<
Just for fun, you might want to research the effect of "colored code"
on maintaining it. There is a standard test for brain damage where you
show the subject a seires of flashcards with the names of colors in
colored ink (I.e. "RED" pritned in green ink) and ask them to call out
the either word or the color. This has nothing to do with being
color-blind.
Since it involves switching brain hemispheres and thus the physical
structure of the brain as a organ, the rate is fairly constant over a
person's lifetime. Unless they get some physical damage to the brain.
Strongly analytical ("left brain, right hand") people have an awful
time with "neon vomit programming tools" because they have to filter
out the colors to abstract the code from the text.
So, I have to ask, why would you ever want to do this? Get a copy of
SQL PROGRAMMING STYLE for some more info on how humans read code. I
did a bit of the work on this back in the 1980's for AIRMICS while I
was a Georgia Tech.|||Well, cut-n-paste it into MS Word - that should do the trick ;)|||Screen capture could work, but the code is many, many pages long.
Is this possible in SQL Server? If not , are there any 3rd party programs
that might work?
Thanks again...
"Anith Sen" wrote:

> Not sure if direct printing is possible or not. This might be silly but if
> the code is small enough to fit the screen, you could use a screen print t
o
> a word document and then print it.
> --
> Anith
>
>|||Nope, tried it. Still black-and-white in MS Word.
"Alexander Kuznetsov" wrote:

> Well, cut-n-paste it into MS Word - that should do the trick ;)
>|||Why would I want color-coded printouts? for the same reason they're
color-coded on the screen. Easier to separate what's code, what's keywords,
and what's comments.
As far as the brain damage research, although it sounds rather intriguing,
considering how many more years of my life I'm going to spend looking at M&M
colored typing, I may be better off not knowing...
"--CELKO--" wrote:

> including the keywords and comments come out in black and white. <<
> Just for fun, you might want to research the effect of "colored code"
> on maintaining it. There is a standard test for brain damage where you
> show the subject a seires of flashcards with the names of colors in
> colored ink (I.e. "RED" pritned in green ink) and ask them to call out
> the either word or the color. This has nothing to do with being
> color-blind.
> Since it involves switching brain hemispheres and thus the physical
> structure of the brain as a organ, the rate is fairly constant over a
> person's lifetime. Unless they get some physical damage to the brain.
>
> Strongly analytical ("left brain, right hand") people have an awful
> time with "neon vomit programming tools" because they have to filter
> out the colors to abstract the code from the text.
> So, I have to ask, why would you ever want to do this? Get a copy of
> SQL PROGRAMMING STYLE for some more info on how humans read code. I
> did a bit of the work on this back in the 1980's for AIRMICS while I
> was a Georgia Tech.
>|||On Fri, 10 Feb 2006 08:51:29 -0800, "Joel"
<Joel@.discussions.microsoft.com> wrote:

>In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
>the QA color coding? When I print, even to a color laser printer, everythin
g
>including the keywords and comments come out in black and white.
>Thanks!
I use Macromedia's Homesite. You can set the colors for each entity.
What you see on the screen is what prints. I also use Ultra Edit and
Slick Edit. Both of these allow you to print a selection where
Homesite will only print the entire file. I like the printout from
Homesite best.
--
BettyB -- www.flamingo-code.com
"I have noticed even people who claim everything is
predestined, and that we can do nothing to change it,
look before they cross the road." - Stephen Hawking|||I don't know if it is an option or not, but the 2005 replacement of QA (Mana
gement Studio) does keep
formatting (including colour) when you copy text. Personally, I hate that, b
ut it might be an upside
in these situations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:F6077A67-C933-47CA-A4C5-654D2E7881A8@.microsoft.com...
> Nope, tried it. Still black-and-white in MS Word.
> "Alexander Kuznetsov" wrote:
>|||> Personally, I hate that,
me too. Sometimes I need to paste into notepad - that removes colors
and fonts

Colour coding the RDL XML in vs2005

Hi.
Does any one know how to get vs2005 to colour code rdl apart from
renaming the file xml?
Some times one just needs to check the rdl file, and looking at it in
B&W isn't easy to visually scan.
Thanks.On Aug 4, 1:49 am, Ray Proffitt <ray...@.gmail.com> wrote:
> Hi.
> Does any one know how to get vs2005 to colour code rdl apart from
> renaming the file xml?
> Some times one just needs to check the rdl file, and looking at it in
> B&W isn't easy to visually scan.
> Thanks.
A good software to do this with is Notepad++ (open source): its a text
editor that will color code based on your language selection (i.e.,
XML, C#, HTML, etc). You can download it at: http://notepad-plus.sourceforge.net/uk/site.htm
It works well for this sort of thing.
Regards
Enrique Martinez
Sr. Software Consultant|||I've been using notepad++ for years. Problem is notepad++ doesn't have
the Report design view, and I frequently change to see the code, cos
sometimes I just need to...
VS2005 will colour code the XML too, just not when it has an RDL
extension, I think it's MS just trying to mother us too much.
I figured there might be a reg setting I could change.
I've tried a couple in here:
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\Text Editor\RDL
Expression
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\Languages
\Language Services\RDL Expression
On Aug 5, 9:26 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 4, 1:49 am, Ray Proffitt <ray...@.gmail.com> wrote:
> > Hi.
> > Does any one know how to get vs2005 to colour code rdl apart from
> > renaming the file xml?
> > Some times one just needs to check the rdl file, and looking at it in
> > B&W isn't easy to visually scan.
> > Thanks.
> A good software to do this with is Notepad++ (open source): its a text
> editor that will color code based on your language selection (i.e.,
> XML, C#, HTML, etc). You can download it at:http://notepad-plus.sourceforge.net/uk/site.htm
> It works well for this sort of thing.
> Regards
> Enrique Martinez
> Sr. Software Consultant