Aliases not found on log in

When loggining into SSMS there does not appear to be a way to connect to the
ALIAS remote servers names like you could with SQL Server 2000. Does any one
know how to use the Alias names at the SQL Server 2005 connection dialog?D. Haber (DHaber@.discussions.microsoft.com) writes:
> When loggining into SSMS there does not appear to be a way to connect to
> the ALIAS remote servers names like you could with SQL Server 2000. Does
> any one know how to use the Alias names at the SQL Server 2005
> connection dialog?
What aliases? Those you defined in the Client Network Utility? I guess you
need to use the SQL Configuration Manager to set up aliaes for the SQL 2005
tools. It's under SQL Native Client configuration, which is a little
misleading, but I did a very quick test, and it seemed to work.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

alias w/IN operator

running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?
broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.

alias w/IN operator

running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.

Aggregate First Not Available?

The following query runs on Access and returns a result table that removes
duplicate account numbers even if they have different names.
SELECT AcctNum, First(t.AccountName) AS AccountName
FROM tblAcctChart t
GROUP BY AcctNum
ORDER BY AcctNum;
101 N1
101 N2
101 N3
results in
101 N1
I am feeling real dumb about this. First is not an aggregate function
available on SQL Server 2000. However, I know a way to accomplish the same
thing exists on SQL Server. I am having a serious senior moment and cannot
work my way through the fog to put one together.
Would some kind soul please give me a query that will run on SQL Server
2000 that will accomplish the same task.
Thanks.
MikeThere is no native ordering of rows in a table, so the meaning of "first"
depends on what criteria you use to pick to one AccountName that survives.
To pick the first name alphabetically:
SELECT AcctNum, MIN(AccountName) AS AccountName
FROM tblAccChart
GROUP BY AcctNum
ORDER BY AccNum
"MikeV06" wrote:

> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
>|||You either need to use MIN or TOP, depending on what you want.
MIN(t.AccountName) will give you the first account name, alphabetically for
each AcctNum.
select top 1 from columnList
order by columnList
will return only the first row based on your order by clause.
"MikeV06" <me@.privacy.net> wrote in message
news:1t6yt3o7yvb6w.dlg@.mycomputer06.invalid.com...
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike|||MikeV06 wrote:
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
Use MIN or MAX or come up with a better definition of what you mean by
"first". The Access query you posted actually returns a random result
for the value of AccountName, which may not be a good idea in many
cases.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000.
Because "FIRST" does not make sense. First what? Physical row? Tables
are, by definition, an unordered set of rows. So the concepts of FIRST,
LAST, or 23rd row make absolutely no sense unless you give more context. As
others have noted, you can order alphabetically by using the aggregate
function MIN(t.AccountName).
I have a brief bit on this here (about 80% of the way down), but in general
the article may be useful to you as well:
http://www.aspfaq.com/2214
A|||My thanks to all who replied. Of course, Min/Max -- blind spot zapping me
broadside. I was overlooking the obvious and trying to make it difficult.
Arggg... Thanks again.
On Wed, 17 May 2006 13:46:01 -0700, Mark Williams wrote:
> There is no native ordering of rows in a table, so the meaning of "first"
> depends on what criteria you use to pick to one AccountName that survives.
> To pick the first name alphabetically:
> SELECT AcctNum, MIN(AccountName) AS AccountName
> FROM tblAccChart
> GROUP BY AcctNum
> ORDER BY AccNum
> "MikeV06" wrote:
>|||On Wed, 17 May 2006 17:05:46 -0400, Aaron Bertrand [SQL Server MVP] wrote:

> Because "FIRST" does not make sense. First what? Physical row? Tables
> are, by definition, an unordered set of rows. So the concepts of FIRST,
> LAST, or 23rd row make absolutely no sense unless you give more context.
As
> others have noted, you can order alphabetically by using the aggregate
> function MIN(t.AccountName).
> I have a brief bit on this here (about 80% of the way down), but in genera
l
> the article may be useful to you as well:
> http://www.aspfaq.com/2214
> A
Very useful indeed. Thank you for the url.

Aggregate Concatinate in join

I have a two tables:
table1
id
name
table2
id
date
I'd like to produce
table3:
id names date
where names = all names for the id concatinated seperated by ","jobs
You have two ID columns ,which one you want?
select tb1 .id,name,date from tb1 join tb2 on tb1.id=tb2.id
where name in ('a','b','c')
"jobs" <jobs@.webdos.com> wrote in message
news:1162310209.501090.68440@.e3g2000cwe.googlegroups.com...
>I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Is table2 supposed to have a name column where a row in table 1 corresponds
a
row in table 2 where the id's are equal?
try this it will create the table and populate it with your data from the
other tables.
select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [d
ate]
into table3 from table1 as a inner join table2 as b on (a.id=b.id)
if the other tables are being updated or used by apps then you can use a
view... create view as remove the 'into table3'
Hope it helps,
Netmon
"jobs" wrote:

> I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Netmon wrote:

> Is table2 supposed to have a name column where a row in table 1 correspond
s a
> row in table 2 where the id's are equal?
> try this it will create the table and populate it with your data from the
> other tables.
> select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [
;date]
> into table3 from table1 as a inner join table2 as b on (a.id=b.id)
> if the other tables are being updated or used by apps then you can use a
> view... create view as remove the 'into table3'
> Hope it helps,
> Netmon
>
> "jobs" wrote:
>
[vbcol=seagreen]
In SQL Server 2005 you can do like this
select distinct a.id , stuff((select ','+name as [text()] from a as b
where a.id = b.id for xml path('')),1,1,'') as names,
b.date
from a inner join b on a.id = b.id
Regards
Amish Shah

Aggregate Concatinate in join

I have a two tables:
table1
id
name
table2
id
date
I'd like to produce
table3:
id names date
where names = all names for the id concatinated seperated by ","jobs
You have two ID columns ,which one you want?
select tb1 .id,name,date from tb1 join tb2 on tb1.id=tb2.id
where name in ('a','b','c')
"jobs" <jobs@.webdos.com> wrote in message
news:1162310209.501090.68440@.e3g2000cwe.googlegroups.com...
>I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Is table2 supposed to have a name column where a row in table 1 corresponds a
row in table 2 where the id's are equal?
try this it will create the table and populate it with your data from the
other tables.
select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
into table3 from table1 as a inner join table2 as b on (a.id=b.id)
if the other tables are being updated or used by apps then you can use a
view... create view as remove the 'into table3'
Hope it helps,
Netmon
"jobs" wrote:
> I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Netmon wrote:
> Is table2 supposed to have a name column where a row in table 1 corresponds a
> row in table 2 where the id's are equal?
> try this it will create the table and populate it with your data from the
> other tables.
> select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
> into table3 from table1 as a inner join table2 as b on (a.id=b.id)
> if the other tables are being updated or used by apps then you can use a
> view... create view as remove the 'into table3'
> Hope it helps,
> Netmon
>
> "jobs" wrote:
>
> > I have a two tables:
> >
> > table1
> > id
> > name
> >
> > table2
> > id
> > date
> >
> > I'd like to produce
> >
> > table3:
> > id names date
> >
> > where names = all names for the id concatinated seperated by ","
> >
> >
In SQL Server 2005 you can do like this
select distinct a.id , stuff((select ','+name as [text()] from a as b
where a.id = b.id for xml path('')),1,1,'') as names,
b.date
from a inner join b on a.id = b.id
Regards
Amish Shah

After seeing AS/400, am i brainwashed by MS SQL Server?

IBM's AS/400 has:
"Libraries" instead of "Databases"
"Files" instead of "Tables"
"Logicals" instead of "Views"
and field names seem to be limited to 6 characters. So field names like
LNM@.CN (i.e. LastName) are not uncommon.
Am i being really closed minded about AS/400; or is what it's doing
perfectly valid and common?
i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
And how the heck is anyone supposed to know that the "file" CFPCLMM is
actually the table "Club Membership Information", and MSTSMM is a field name
for "Member Status"? And that the "logical" CFLDPCN1 is a view?
Is AS/400 stupid, or am i, or a little bit of both?<<
CFLDPCN1
I did an AS/400 to SQL DW a few years ago and drove myself nuts trying to
figure out thier non-verbose column names. Strangley enough the AS400 people
who started at it all day for years seemed to know... but I don't think
you're missing anything. An AS400 is certainly a powerful DB but it's not
quite as easy to use as SQL Server,
--
Brian
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:e%237oWhHVEHA.2840@.TK2MSFTNGP11.phx.gbl...
> IBM's AS/400 has:
> "Libraries" instead of "Databases"
> "Files" instead of "Tables"
> "Logicals" instead of "Views"
> and field names seem to be limited to 6 characters. So field names like
> LNM@.CN (i.e. LastName) are not uncommon.
> Am i being really closed minded about AS/400; or is what it's doing
> perfectly valid and common?
> i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
> And how the heck is anyone supposed to know that the "file" CFPCLMM is
> actually the table "Club Membership Information", and MSTSMM is a field
name
> for "Member Status"? And that the "logical" CFLDPCN1 is a view?
>
> Is AS/400 stupid, or am i, or a little bit of both?
>

After seeing AS/400, am i brainwashed by MS SQL Server?

IBM's AS/400 has:
"Libraries" instead of "Databases"
"Files" instead of "Tables"
"Logicals" instead of "Views"
and field names seem to be limited to 6 characters. So field names like
LNM@.CN (i.e. LastName) are not uncommon.
Am i being really closed minded about AS/400; or is what it's doing
perfectly valid and common?
i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
And how the heck is anyone supposed to know that the "file" CFPCLMM is
actually the table "Club Membership Information", and MSTSMM is a field name
for "Member Status"? And that the "logical" CFLDPCN1 is a view?
Is AS/400 stupid, or am i, or a little bit of both?<<
CFLDPCN1
I did an AS/400 to SQL DW a few years ago and drove myself nuts trying to
figure out thier non-verbose column names. Strangley enough the AS400 people
who started at it all day for years seemed to know... but I don't think
you're missing anything. An AS400 is certainly a powerful DB but it's not
quite as easy to use as SQL Server,
--
Brian
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:e%237oWhHVEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> IBM's AS/400 has:
> "Libraries" instead of "Databases"
> "Files" instead of "Tables"
> "Logicals" instead of "Views"
> and field names seem to be limited to 6 characters. So field names like
> LNM@.CN (i.e. LastName) are not uncommon.
> Am i being really closed minded about AS/400; or is what it's doing
> perfectly valid and common?
> i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
> And how the heck is anyone supposed to know that the "file" CFPCLMM is
> actually the table "Club Membership Information", and MSTSMM is a field
name
> for "Member Status"? And that the "logical" CFLDPCN1 is a view?
>
> Is AS/400 stupid, or am i, or a little bit of both?
>

After seeing AS/400, am i brainwashed by MS SQL Server?

IBM's AS/400 has:
"Libraries" instead of "Databases"
"Files" instead of "Tables"
"Logicals" instead of "Views"
and field names seem to be limited to 6 characters. So field names like
LNM@.CN (i.e. LastName) are not uncommon.
Am i being really closed minded about AS/400; or is what it's doing
perfectly valid and common?
i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
And how the heck is anyone supposed to know that the "file" CFPCLMM is
actually the table "Club Membership Information", and MSTSMM is a field name
for "Member Status"? And that the "logical" CFLDPCN1 is a view?
Is AS/400 stupid, or am i, or a little bit of both?
<<
CFLDPCN1[vbcol=seagreen]
I did an AS/400 to SQL DW a few years ago and drove myself nuts trying to
figure out thier non-verbose column names. Strangley enough the AS400 people
who started at it all day for years seemed to know... but I don't think
you're missing anything. An AS400 is certainly a powerful DB but it's not
quite as easy to use as SQL Server,
Brian
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:e%237oWhHVEHA.2840@.TK2MSFTNGP11.phx.gbl...
> IBM's AS/400 has:
> "Libraries" instead of "Databases"
> "Files" instead of "Tables"
> "Logicals" instead of "Views"
> and field names seem to be limited to 6 characters. So field names like
> LNM@.CN (i.e. LastName) are not uncommon.
> Am i being really closed minded about AS/400; or is what it's doing
> perfectly valid and common?
> i mean, wtf is a "Logical"? Who calls it that? Does Oracle? MySQL?
> And how the heck is anyone supposed to know that the "file" CFPCLMM is
> actually the table "Club Membership Information", and MSTSMM is a field
name
> for "Member Status"? And that the "logical" CFLDPCN1 is a view?
>
> Is AS/400 stupid, or am i, or a little bit of both?
>