I need to know where an alias is stored. I am new to SQL and need to
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, JohnIn the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:
>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, Johnsql
Showing posts with label alias. Show all posts
Showing posts with label alias. Show all posts
Tuesday, March 27, 2012
Aliases
I need to know where an alias is stored. I am new to SQL and need to
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, John
In the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:
>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, John
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, John
In the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:
>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, John
Alias with XP
Why is it necessary to create an alias in the Client Network Utility to be able to connect to SQL Server if you are running Windows XP?To clarify a little, it seems it is only necessary in XP. We don't have to create an alias when the client is running NT or 2000, only XP. Also, if the network connection goes down, we occasionally lose the alias and have to re-create it. Any ideas?sql
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.
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.
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 question
The following is not working:
SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
[Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM [Table]
SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
the above example) when I try to use them in later calculations. Is there a
way to do this without actually having to do all the calculations for each
successive column? I've got a lot more calculations to do than just the ones
I'm showing here, so I'd like to limit the amount of SQL code to sift throug
h
if at all possible.Your alternatives are views/derived tables or reusing the entire expression.
So you can have:
SELECT "Total Calls" / "Conversion Rate" AS "Customers",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" AS "Sales",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" * "Profit Margin" AS "Profit"
FROM Table ;
-- or
SELECT Customers,
Customers * Customer_value AS Sales,
Customers * Customer_value * Profit_margin AS profit
FROM (
SELECT "Total Calls" / "Conversion Rate",
"Customer Value", "Profit Margin"
FROM table
) Derived_tbl ( Customers, Customer_value, Profit_margin ) ;
Anith|||Hi,
You can not use alias for this. The approaches are:-
1. As you mentioned use the calculations for each columns
2. Declare variables and use the variables in select statement
Eg:-
Declare @.customers int,
@.Sales int,
@.profit int
SELECT @.Customers = [Total Calls] / [Conversion Rate] , @.Sales= @.Customers
*
[Customer Value] , @.Profit = @.Sales * [Profit Margin] FROM [Table]
Select @.customers,@.sales,@.Profit
Thanks
Hari
SQL Server MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:F7718D7F-2203-44A3-B664-4FCD4E9CFACE@.microsoft.com...
> The following is not working:
> SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
> [Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM
> [Table]
> SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
> the above example) when I try to use them in later calculations. Is there
> a
> way to do this without actually having to do all the calculations for each
> successive column? I've got a lot more calculations to do than just the
> ones
> I'm showing here, so I'd like to limit the amount of SQL code to sift
> through
> if at all possible.
>
SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
[Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM [Table]
SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
the above example) when I try to use them in later calculations. Is there a
way to do this without actually having to do all the calculations for each
successive column? I've got a lot more calculations to do than just the ones
I'm showing here, so I'd like to limit the amount of SQL code to sift throug
h
if at all possible.Your alternatives are views/derived tables or reusing the entire expression.
So you can have:
SELECT "Total Calls" / "Conversion Rate" AS "Customers",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" AS "Sales",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" * "Profit Margin" AS "Profit"
FROM Table ;
-- or
SELECT Customers,
Customers * Customer_value AS Sales,
Customers * Customer_value * Profit_margin AS profit
FROM (
SELECT "Total Calls" / "Conversion Rate",
"Customer Value", "Profit Margin"
FROM table
) Derived_tbl ( Customers, Customer_value, Profit_margin ) ;
Anith|||Hi,
You can not use alias for this. The approaches are:-
1. As you mentioned use the calculations for each columns
2. Declare variables and use the variables in select statement
Eg:-
Declare @.customers int,
@.Sales int,
@.profit int
SELECT @.Customers = [Total Calls] / [Conversion Rate] , @.Sales= @.Customers
*
[Customer Value] , @.Profit = @.Sales * [Profit Margin] FROM [Table]
Select @.customers,@.sales,@.Profit
Thanks
Hari
SQL Server MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:F7718D7F-2203-44A3-B664-4FCD4E9CFACE@.microsoft.com...
> The following is not working:
> SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
> [Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM
> [Table]
> SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
> the above example) when I try to use them in later calculations. Is there
> a
> way to do this without actually having to do all the calculations for each
> successive column? I've got a lot more calculations to do than just the
> ones
> I'm showing here, so I'd like to limit the amount of SQL code to sift
> through
> if at all possible.
>
Alias or Group SSAS Dimension at Query time.
In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.
Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?
Any help will be appreciated.
WITH MEMBER [Measures].[Long] AS
IIF(
[Measures].[Risk Value]<0,
[Measures].[Risk Value],
null)
SET [GroupedRatings] AS
CASE
WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]
WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]
WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]
WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]
WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]
WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]
WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]
ELSE NULL
END
SELECT { [Measures].[Long]} ON COLUMNS,
{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members
ON ROWS
FROM [DM]
This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.
eg.
WITH MEMBER [Measures].[Long] AS
IIF(
[Measures].[Risk Value]<0,
[Measures].[Risk Value],
null)
MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})
MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}
MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})
MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})
SET [GroupedRatings] AS
{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}
SELECT { [Measures].[Long]} ON COLUMNS,
{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members
ON ROWS
FROM [DM]
|||The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:
WITH MEMBER [Measures].[Long] AS
IIF(
[Measures].[Risk Value]<0,
[Measures].[Risk Value],
null)
Member [Curve Family].[SP Rating].[AA] as
Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),
SOLVE_ORDER = 10
Member [Curve Family].[SP Rating].[AAA] as
Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),
SOLVE_ORDER = 10
Member [Curve Family].[SP Rating].[BB] as
Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),
SOLVE_ORDER = 10
Member [Curve Family].[SP Rating].[BBB] as
Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),
SOLVE_ORDER = 10
SET [GroupedRatings] AS
{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],
[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}
SELECT { [Measures].[Long]} ON COLUMNS,{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members
ON ROWS
FROM [DM]
|||Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.
WITH
MEMBER [Curve Family].[SP Rating].[AA] AS
Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})
MEMBER [Curve Family].[SP Rating].[AAA] AS
Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})
MEMBER [Curve Family].[SP Rating].[BB] AS
Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})
MEMBER [Curve Family].[SP Rating].[BBB]AS
Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})
SET [GroupedRatings] AS
{
[Curve Family].[SP Rating].[AA]
,[Curve Family].[SP Rating].[AAA]
,[Curve Family].[SP Rating].[BB]
,[Curve Family].[SP Rating].[BBB]
}
SELECT
NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,
NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS
FROM
[DM]
Alias on Update query
How can I put an alias on the table in an Update query
Update T64PE as Person
Where Person.ID = 5
(PS This works with Sybase)OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||Originally posted by Karolyn
OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5
Even more :
Update Person
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||noted !
(thks)sql
Update T64PE as Person
Where Person.ID = 5
(PS This works with Sybase)OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||Originally posted by Karolyn
OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5
Even more :
Update Person
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||noted !
(thks)sql
Alias OK, IP not
Hi all,
I was able to get my mirroring setup to work only when I use Alias instead of IP address. Any idea why it is so?
Thanks,
Avi
I am trying to set up using IPS, someone told me you can as long as u do not use a witness server.
I am having trouble setting it up. What did you do to get it working? when you say Alias, what do you mean ( can u walk me using your steps ).
Thanks
|||
Alias is basically associating a name to an IP address for that you use
MS SQL 2005-> Configuration tools ->SQL Server Configuration Manager
Once you set up an alias you can use it instead of the IP. However, my problem was that with an alias my scripts works well but with IP it does not.
Any ideas?
Avi
|||Might be a problem with WINS in this case, have you checked with your Network admin in this case.
Ensure both the servers has similar version of tools in this case.
Alias of a Linked Server
I am using linked server with an IP address then want to user this server in query but using IP address gives error and I want to define an Alias for that linked server to use in query, Please help me.
Thanks in advance
Muhammad Hanif
Have you tried with the hostname?|||You can create an alias using the Client Network Utility (on SQL 2000) or Configuration Manager (on SQL 2005). You can create whatever alias name and enter the IP address for the server.
-Sue
Alias not working on some machines
SQL 2005 SP2 on Windows Server 2003 SP2
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim C
There seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim C
There seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C
Alias not working on some machines
SQL 2005 SP2 on Windows Server 2003 SP2
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim CThere seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C|||I recently had a discussion in .tools about this. Paul O'kasick was nice enough to share his
findings. Aparently there's both a 32 and a 64 bit version of cliconfg.exe and these modify
different registry keys. Here's a quote from Paul most recent reply:
"The alias is working on our test machine. It turns out there is a 64 bit
version of cliconfg.exe (C:\WINDOWS\SysWOW64\cliconfg.exe). As soon as I
created the alias with that version, the application started working. I
removed the alias created with the 32 bit version.
Each version maintains a separate list of aliases. The registry key for the
32 bit vs. 64 bit is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo &
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo,
respectively."
So, my suggestion is that you try to create the alias with both 32 and 64 bit version of
cliconfg.exe to see which it is that is required in your particular case (probably depends on
whether the client app is 32 or 64 bit - meguess).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim C" <TimC@.discussions.microsoft.com> wrote in message
news:098FFFB2-D352-4B16-A32D-CEA42461787C@.microsoft.com...
> There seems to be a subtle incompatibility issue. We have started moving our
> SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
> But if anyone knows of a simpler fix we could implement, I would love to
> hear it.
> Thanks,
> Tim C
> "Tim C" wrote:
>> SQL 2005 SP2 on Windows Server 2003 SP2
>> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
>> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
>> 32-bit. All use 32-bit SQL 2000.
>> Trying to create an Alias on a SQL 2000 machine to point to a named instance
>> on a SQL 2005 machine through TCP/IP (for use in replication).
>> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
>> works.
>> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
>> doesn't.
>> Instead, when I try to test using isql, I get:
>> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
>> Una
>> ble to connect: SQL Server does not exist or network access denied.
>> Net-Library error 53: ConnectionOpen (Connect()).
>> Any ideas?
>> Thanks,
>> Tim C
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim CThere seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C|||I recently had a discussion in .tools about this. Paul O'kasick was nice enough to share his
findings. Aparently there's both a 32 and a 64 bit version of cliconfg.exe and these modify
different registry keys. Here's a quote from Paul most recent reply:
"The alias is working on our test machine. It turns out there is a 64 bit
version of cliconfg.exe (C:\WINDOWS\SysWOW64\cliconfg.exe). As soon as I
created the alias with that version, the application started working. I
removed the alias created with the 32 bit version.
Each version maintains a separate list of aliases. The registry key for the
32 bit vs. 64 bit is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo &
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo,
respectively."
So, my suggestion is that you try to create the alias with both 32 and 64 bit version of
cliconfg.exe to see which it is that is required in your particular case (probably depends on
whether the client app is 32 or 64 bit - meguess).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim C" <TimC@.discussions.microsoft.com> wrote in message
news:098FFFB2-D352-4B16-A32D-CEA42461787C@.microsoft.com...
> There seems to be a subtle incompatibility issue. We have started moving our
> SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
> But if anyone knows of a simpler fix we could implement, I would love to
> hear it.
> Thanks,
> Tim C
> "Tim C" wrote:
>> SQL 2005 SP2 on Windows Server 2003 SP2
>> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
>> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
>> 32-bit. All use 32-bit SQL 2000.
>> Trying to create an Alias on a SQL 2000 machine to point to a named instance
>> on a SQL 2005 machine through TCP/IP (for use in replication).
>> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
>> works.
>> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
>> doesn't.
>> Instead, when I try to test using isql, I get:
>> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
>> Una
>> ble to connect: SQL Server does not exist or network access denied.
>> Net-Library error 53: ConnectionOpen (Connect()).
>> Any ideas?
>> Thanks,
>> Tim C
Alias not recognized
I'm attempting to refer to an alias in my SELECT clause (within a stored
procedure).
Basically I'm building a string which I will eventually execute by calling
the "exec" statement on my string.
Within my string, I have 3 columns for my SELECT clause. Here is an
extremely watered down example of what I'm referring to.
e.g.
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
The issue is that my select clause does NOT recognize "alias_one" and
"alias_two" as aliases when I call exec(myString).
Can I possibly refer to these columns by index within the sql or possibly
declare these aliases at the beginning of the procedure so that they will be
recognized?
Any help is appreciated.
PK9How do you refer the to alias in your stored procedure. If you post the
code, we might be able to help.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
> be
> recognized?
> Any help is appreciated.
> --
> PK9|||This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
posting it may just confuse the issue.
What I end up with at the end of the SQL creation is the following:
'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
alias' in the stored procedure. Now, I want to say as another column' FY
1999 minus FY 1999 CMP' to give me the difference between the two columns.
Does that help?
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||Can Anyone help me with this?
I'm really stuck right now.
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||You are trying to do something like this:
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
This isn't possible in the SQL language. The whole select list happens at th
e same time, logically.
Here's one way with which you don't have to repeat the expressions:
SELECT alias_one, alias_two, alias_one - alias_two
FROM
(
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
FROM W,X,Y,Z
) AS d
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
be
> recognized?
> Any help is appreciated.
> --
> PK9|||dynamic sql for xtab. hmmm...that sounds quite familiar. wait, we have such
a commercial solution (http://rac4sql.net) :-)
anyway, you cannot just add the two aliases because the aliases are
calculated at runtime. what you can do is to derive the first query then use
the aliases. take a look at tibor's comment.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:FD1A0770-2736-49B8-B684-16AE1927EF92@.microsoft.com...
> This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
> posting it may just confuse the issue.
> What I end up with at the end of the SQL creation is the following:
> 'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
> alias' in the stored procedure. Now, I want to say as another column' FY
> 1999 minus FY 1999 CMP' to give me the difference between the two columns.
> Does that help?
> "oj" wrote:
>|||Yoiu missed some basic ideas in SQL. Here is how a SELECT works in SQL
... at least in theory. Real products will optimize things, but the
code has to produce the same results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a
mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.
And dynamic SQL is considered bad programming, not quite as bad as
cursors, but still not the way to do it.|||Thanks, that's exactly what I was looking for!
"Tibor Karaszi" wrote:
> You are trying to do something like this:
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
>
> This isn't possible in the SQL language. The whole select list happens at
the same time, logically.
> Here's one way with which you don't have to repeat the expressions:
> SELECT alias_one, alias_two, alias_one - alias_two
> FROM
> (
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> FROM W,X,Y,Z
> ) AS d
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>sql
procedure).
Basically I'm building a string which I will eventually execute by calling
the "exec" statement on my string.
Within my string, I have 3 columns for my SELECT clause. Here is an
extremely watered down example of what I'm referring to.
e.g.
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
The issue is that my select clause does NOT recognize "alias_one" and
"alias_two" as aliases when I call exec(myString).
Can I possibly refer to these columns by index within the sql or possibly
declare these aliases at the beginning of the procedure so that they will be
recognized?
Any help is appreciated.
PK9How do you refer the to alias in your stored procedure. If you post the
code, we might be able to help.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
> be
> recognized?
> Any help is appreciated.
> --
> PK9|||This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
posting it may just confuse the issue.
What I end up with at the end of the SQL creation is the following:
'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
alias' in the stored procedure. Now, I want to say as another column' FY
1999 minus FY 1999 CMP' to give me the difference between the two columns.
Does that help?
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||Can Anyone help me with this?
I'm really stuck right now.
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||You are trying to do something like this:
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
This isn't possible in the SQL language. The whole select list happens at th
e same time, logically.
Here's one way with which you don't have to repeat the expressions:
SELECT alias_one, alias_two, alias_one - alias_two
FROM
(
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
FROM W,X,Y,Z
) AS d
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
be
> recognized?
> Any help is appreciated.
> --
> PK9|||dynamic sql for xtab. hmmm...that sounds quite familiar. wait, we have such
a commercial solution (http://rac4sql.net) :-)
anyway, you cannot just add the two aliases because the aliases are
calculated at runtime. what you can do is to derive the first query then use
the aliases. take a look at tibor's comment.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:FD1A0770-2736-49B8-B684-16AE1927EF92@.microsoft.com...
> This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
> posting it may just confuse the issue.
> What I end up with at the end of the SQL creation is the following:
> 'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
> alias' in the stored procedure. Now, I want to say as another column' FY
> 1999 minus FY 1999 CMP' to give me the difference between the two columns.
> Does that help?
> "oj" wrote:
>|||Yoiu missed some basic ideas in SQL. Here is how a SELECT works in SQL
... at least in theory. Real products will optimize things, but the
code has to produce the same results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a
mess this statement is in the SQL model.SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.
And dynamic SQL is considered bad programming, not quite as bad as
cursors, but still not the way to do it.|||Thanks, that's exactly what I was looking for!
"Tibor Karaszi" wrote:
> You are trying to do something like this:
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
>
> This isn't possible in the SQL language. The whole select list happens at
the same time, logically.
> Here's one way with which you don't have to repeat the expressions:
> SELECT alias_one, alias_two, alias_one - alias_two
> FROM
> (
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> FROM W,X,Y,Z
> ) AS d
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>sql
alias naming
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.If you are storing alias name for columns,
you have to use dynamic sql, to get the results.
I think, your requirement is so complicated to implement. :D
What advantages you will get, if you implement it..? :rolleyes:
Regards,
Selva Balaji. B
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?. :D :D :D|||There isn't such a functionality within the database. Using the standard tools like Enterprise Manager or Query Analyzer, you have to use the physical names, and to assign the logical names every time again.
You may consider to make a view for each table assigning your logical names.
Client tools, however, can replace the physical names by logical ones in the user interface. Look, for example, the DB Explorer (http://www.DB-Explorer.com).|||Hi,
Actually in my application...
I am creating more stored procedures based on a single table...(database already used by another application).
Since i can't change the field names, i am making use of alias name for the required fields in Stored procedures and binding tha data in the front end where the alias name gets displayed.
I've more than 500 stored procedures in my database.
If I want to change a field caption ,
I cannot change the existing field's name since it is already used by other application.
Also It is very hard to find out and change the alias name in each and every stored procedure wherever it is referenced.
So i am trying to look in other chances...to reflect the change in every stored procedure with a single move.
Is it possible?...
plz help me...
bye
by
durga|||And what about using views? You may consider not to assign your alias within every stored proc, but once in a view definition. If you change aliases in a view, your stored proc will return the changed name, assuming that you are working with SELECT * statements. This is consistent for all stored proc based on a particular view.|||You may want to have a look at extended properties
(sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty).
It will not let you use the alias'es directly, but you do not have to come up with tables/functions etc to utilize them.
To use them you must (by code if you can, use syscomments or sqldmo) regenerate alll ddl's wher ethey are referenced (sysdepends, sysreferences).
not a small task...
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.If you are storing alias name for columns,
you have to use dynamic sql, to get the results.
I think, your requirement is so complicated to implement. :D
What advantages you will get, if you implement it..? :rolleyes:
Regards,
Selva Balaji. B
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?. :D :D :D|||There isn't such a functionality within the database. Using the standard tools like Enterprise Manager or Query Analyzer, you have to use the physical names, and to assign the logical names every time again.
You may consider to make a view for each table assigning your logical names.
Client tools, however, can replace the physical names by logical ones in the user interface. Look, for example, the DB Explorer (http://www.DB-Explorer.com).|||Hi,
Actually in my application...
I am creating more stored procedures based on a single table...(database already used by another application).
Since i can't change the field names, i am making use of alias name for the required fields in Stored procedures and binding tha data in the front end where the alias name gets displayed.
I've more than 500 stored procedures in my database.
If I want to change a field caption ,
I cannot change the existing field's name since it is already used by other application.
Also It is very hard to find out and change the alias name in each and every stored procedure wherever it is referenced.
So i am trying to look in other chances...to reflect the change in every stored procedure with a single move.
Is it possible?...
plz help me...
bye
by
durga|||And what about using views? You may consider not to assign your alias within every stored proc, but once in a view definition. If you change aliases in a view, your stored proc will return the changed name, assuming that you are working with SELECT * statements. This is consistent for all stored proc based on a particular view.|||You may want to have a look at extended properties
(sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty).
It will not let you use the alias'es directly, but you do not have to come up with tables/functions etc to utilize them.
To use them you must (by code if you can, use syscomments or sqldmo) regenerate alll ddl's wher ethey are referenced (sysdepends, sysreferences).
not a small task...
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.
Alias name for addressing SQL databases
I have a number of MS Access apps that link to SQL Sever tables. If
the SQL Sever database is moved to another server, or the server is
replaced, the unc address to the database changes as the server name
has changed. All my apps fall over as the linked tables are addressed
using the server name.
the SQL Sever database is moved to another server, or the server is
replaced, the unc address to the database changes as the server name
has changed. All my apps fall over as the linked tables are addressed
using the server name.
Is there a way to set up an alias name for an SQL database so that you
can link to the tables through an ODBC connection without using the
server name.
Thanks in advanceYou can setup an alias for the server using the SQL Server Client Network
Utility. You can also do this while setting up an ODBC DSN.
I'm not sure I fully understand your situation, though. If you've linked
SQL Server tables using an ODBC DSN, only the DSN is stored in your
connection string. Moving/renaming the server shouldn't require changes to
your app but you'll still need to change the server name on each client
using the Client Network Utility or ODBC DSN Administrator.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant Hammond" <techworks@.clear.net.nz> wrote in message
news:5e784f92.0405111506.2672be22@.posting.google.c om...
> I have a number of MS Access apps that link to SQL Sever tables. If
> the SQL Sever database is moved to another server, or the server is
> replaced, the unc address to the database changes as the server name
> has changed. All my apps fall over as the linked tables are addressed
> using the server name.
> Is there a way to set up an alias name for an SQL database so that you
> can link to the tables through an ODBC connection without using the
> server name.
> Thanks in advance
Alias in the DNS.
Hi,
I have two different server with the same SQL instance
with the same databases on
SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
to switch from SERVER1 to SERVER2 without causing users
problems.
We thought to add in the DNS server the IP adresses of
the server1 and giving the TEST ...but that's not work
because the ip adress alone is not realy associate with
the SQL Instance. And we can't put in DSN an IP adress
with the \NODE1.
So, someone can help me to put on this full system
availability strategy '
Thanks...The client software can specify the IP address and instance name
instead of the computer name and instance name. Ex:
192.x.x.x\<ABCInstance>.
Lou Arnold
Ottawa.
On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have two different server with the same SQL instance
>with the same databases on
>SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
>to switch from SERVER1 to SERVER2 without causing users
>problems.
>We thought to add in the DNS server the IP adresses of
>the server1 and giving the TEST ...but that's not work
>because the ip adress alone is not realy associate with
>the SQL Instance. And we can't put in DSN an IP adress
>with the \NODE1.
>So, someone can help me to put on this full system
>availability strategy '
>Thanks...|||Yes i know that Lou but it's not our goal to past all the
300 user's computers to change the client network alias
ip adresse.
We use actualy an alias name for a server with a default
installation of SQL Server .. and that's work good on
because the ip adress in the DNS is the same name than
the SQL Server name. And we can easyly change the ip
adress into the DNS Server and we don't have to change
300 odbc connections...
>--Original Message--
>The client software can specify the IP address and
instance name
>instead of the computer name and instance name. Ex:
>192.x.x.x\<ABCInstance>.
>Lou Arnold
>Ottawa.
>On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
><anonymous@.discussions.microsoft.com> wrote:
>
with[vbcol=seagreen]
>.
>|||Ok. You're right. Sorry I can't help further.
Lou.
On Thu, 22 Jul 2004 11:03:25 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes i know that Lou but it's not our goal to past all the
>300 user's computers to change the client network alias
>ip adresse.
>We use actualy an alias name for a server with a default
>installation of SQL Server .. and that's work good on
>because the ip adress in the DNS is the same name than
>the SQL Server name. And we can easyly change the ip
>adress into the DNS Server and we don't have to change
>300 odbc connections...
>
>instance name
>with|||Hi Pierre,
Are the instances in your example on a SQL Cluster or StandAlone?
Are you using 1433 as the port or other?
When your clients connect to the Server do the specify Server\InstancName
or just Server?
The reason I ask is that MDAC has some rules in connections to Named
Instances.
You must use either :
1. Server\InstanceName
or
2. Server, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
That's a standalone servers. and actually the clients are
using SERVER1\NODE1 ( port:1435 ) ... and I want to be
able to switch easily to SERVER2\NODE1 ( port:1435 ).
Actually we use an alias in th DNS for a SQL Server (
just server like you wrote ) and that's work very well
because ... the ODBC's client are configured with an
alias named ALIAS1 whose got the SERVER1 IP Adress in the
DNS ... When we want to switch, we just change the IP
Adress of the alias ALIAS1 in DNS and that's it ... the
clients use the SERVER2 without causing connectivity
problem ...
But with Instancename we can't use this strategy but we
would like because we think it's a good way to keep alive
an application 24 hours a days ...
Thanks Again ..
>--Original Message--
>Hi Pierre,
>Are the instances in your example on a SQL Cluster or
StandAlone?
>Are you using 1433 as the port or other?
>When your clients connect to the Server do the specify
Server\InstancName
>or just Server?
>The reason I ask is that MDAC has some rules in
connections to Named
>Instances.
>You must use either :
> 1. Server\InstanceName
> or
> 2. Server, port
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||OK. So, if the client has an alias configure to use Server1,1435 (Using
the SQL Client Network Util) then does this scenario work?
I think it will as long as you've created an alias on each client to
reference the Servername and port. Port being an
important factor.
Since DNS knows nothing about ports or instances, this is the only way i
think it will work. Otherwise, the traffic will
default to 1433 and the clients fail to connect.
Let me know if this makes sense.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
I have two different server with the same SQL instance
with the same databases on
SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
to switch from SERVER1 to SERVER2 without causing users
problems.
We thought to add in the DNS server the IP adresses of
the server1 and giving the TEST ...but that's not work
because the ip adress alone is not realy associate with
the SQL Instance. And we can't put in DSN an IP adress
with the \NODE1.
So, someone can help me to put on this full system
availability strategy '
Thanks...The client software can specify the IP address and instance name
instead of the computer name and instance name. Ex:
192.x.x.x\<ABCInstance>.
Lou Arnold
Ottawa.
On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have two different server with the same SQL instance
>with the same databases on
>SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
>to switch from SERVER1 to SERVER2 without causing users
>problems.
>We thought to add in the DNS server the IP adresses of
>the server1 and giving the TEST ...but that's not work
>because the ip adress alone is not realy associate with
>the SQL Instance. And we can't put in DSN an IP adress
>with the \NODE1.
>So, someone can help me to put on this full system
>availability strategy '
>Thanks...|||Yes i know that Lou but it's not our goal to past all the
300 user's computers to change the client network alias
ip adresse.
We use actualy an alias name for a server with a default
installation of SQL Server .. and that's work good on
because the ip adress in the DNS is the same name than
the SQL Server name. And we can easyly change the ip
adress into the DNS Server and we don't have to change
300 odbc connections...
>--Original Message--
>The client software can specify the IP address and
instance name
>instead of the computer name and instance name. Ex:
>192.x.x.x\<ABCInstance>.
>Lou Arnold
>Ottawa.
>On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
><anonymous@.discussions.microsoft.com> wrote:
>
with[vbcol=seagreen]
>.
>|||Ok. You're right. Sorry I can't help further.
Lou.
On Thu, 22 Jul 2004 11:03:25 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes i know that Lou but it's not our goal to past all the
>300 user's computers to change the client network alias
>ip adresse.
>We use actualy an alias name for a server with a default
>installation of SQL Server .. and that's work good on
>because the ip adress in the DNS is the same name than
>the SQL Server name. And we can easyly change the ip
>adress into the DNS Server and we don't have to change
>300 odbc connections...
>
>instance name
>with|||Hi Pierre,
Are the instances in your example on a SQL Cluster or StandAlone?
Are you using 1433 as the port or other?
When your clients connect to the Server do the specify Server\InstancName
or just Server?
The reason I ask is that MDAC has some rules in connections to Named
Instances.
You must use either :
1. Server\InstanceName
or
2. Server, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
That's a standalone servers. and actually the clients are
using SERVER1\NODE1 ( port:1435 ) ... and I want to be
able to switch easily to SERVER2\NODE1 ( port:1435 ).
Actually we use an alias in th DNS for a SQL Server (
just server like you wrote ) and that's work very well
because ... the ODBC's client are configured with an
alias named ALIAS1 whose got the SERVER1 IP Adress in the
DNS ... When we want to switch, we just change the IP
Adress of the alias ALIAS1 in DNS and that's it ... the
clients use the SERVER2 without causing connectivity
problem ...
But with Instancename we can't use this strategy but we
would like because we think it's a good way to keep alive
an application 24 hours a days ...
Thanks Again ..
>--Original Message--
>Hi Pierre,
>Are the instances in your example on a SQL Cluster or
StandAlone?
>Are you using 1433 as the port or other?
>When your clients connect to the Server do the specify
Server\InstancName
>or just Server?
>The reason I ask is that MDAC has some rules in
connections to Named
>Instances.
>You must use either :
> 1. Server\InstanceName
> or
> 2. Server, port
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||OK. So, if the client has an alias configure to use Server1,1435 (Using
the SQL Client Network Util) then does this scenario work?
I think it will as long as you've created an alias on each client to
reference the Servername and port. Port being an
important factor.
Since DNS knows nothing about ports or instances, this is the only way i
think it will work. Otherwise, the traffic will
default to 1433 and the clients fail to connect.
Let me know if this makes sense.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Alias in the DNS.
Hi,
I have two different server with the same SQL instance
with the same databases on
SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
to switch from SERVER1 to SERVER2 without causing users
problems.
We thought to add in the DNS server the IP adresses of
the server1 and giving the TEST ...but that's not work
because the ip adress alone is not realy associate with
the SQL Instance. And we can't put in DSN an IP adress
with the \NODE1.
So, someone can help me to put on this full system
availability strategy ?
Thanks...
The client software can specify the IP address and instance name
instead of the computer name and instance name. Ex:
192.x.x.x\<ABCInstance>.
Lou Arnold
Ottawa.
On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have two different server with the same SQL instance
>with the same databases on
>SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
>to switch from SERVER1 to SERVER2 without causing users
>problems.
>We thought to add in the DNS server the IP adresses of
>the server1 and giving the TEST ...but that's not work
>because the ip adress alone is not realy associate with
>the SQL Instance. And we can't put in DSN an IP adress
>with the \NODE1.
>So, someone can help me to put on this full system
>availability strategy ?
>Thanks...
|||Yes i know that Lou but it's not our goal to past all the
300 user's computers to change the client network alias
ip adresse.
We use actualy an alias name for a server with a default
installation of SQL Server .. and that's work good on
because the ip adress in the DNS is the same name than
the SQL Server name. And we can easyly change the ip
adress into the DNS Server and we don't have to change
300 odbc connections...
>--Original Message--
>The client software can specify the IP address and
instance name[vbcol=seagreen]
>instead of the computer name and instance name. Ex:
>192.x.x.x\<ABCInstance>.
>Lou Arnold
>Ottawa.
>On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
><anonymous@.discussions.microsoft.com> wrote:
with
>.
>
|||Ok. You're right. Sorry I can't help further.
Lou.
On Thu, 22 Jul 2004 11:03:25 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes i know that Lou but it's not our goal to past all the
>300 user's computers to change the client network alias
>ip adresse.
>We use actualy an alias name for a server with a default
>installation of SQL Server .. and that's work good on
>because the ip adress in the DNS is the same name than
>the SQL Server name. And we can easyly change the ip
>adress into the DNS Server and we don't have to change
>300 odbc connections...
>instance name
>with
|||Hi Pierre,
Are the instances in your example on a SQL Cluster or StandAlone?
Are you using 1433 as the port or other?
When your clients connect to the Server do the specify Server\InstancName
or just Server?
The reason I ask is that MDAC has some rules in connections to Named
Instances.
You must use either :
1. Server\InstanceName
or
2. Server, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin,
That's a standalone servers. and actually the clients are
using SERVER1\NODE1 ( port:1435 ) ... and I want to be
able to switch easily to SERVER2\NODE1 ( port:1435 ).
Actually we use an alias in th DNS for a SQL Server (
just server like you wrote ) and that's work very well
because ... the ODBC's client are configured with an
alias named ALIAS1 whose got the SERVER1 IP Adress in the
DNS ... When we want to switch, we just change the IP
Adress of the alias ALIAS1 in DNS and that's it ... the
clients use the SERVER2 without causing connectivity
problem ...
But with Instancename we can't use this strategy but we
would like because we think it's a good way to keep alive
an application 24 hours a days ...
Thanks Again ..
>--Original Message--
>Hi Pierre,
>Are the instances in your example on a SQL Cluster or
StandAlone?
>Are you using 1433 as the port or other?
>When your clients connect to the Server do the specify
Server\InstancName
>or just Server?
>The reason I ask is that MDAC has some rules in
connections to Named
>Instances.
>You must use either :
> 1. Server\InstanceName
> or
> 2. Server, port
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>
|||OK. So, if the client has an alias configure to use Server1,1435 (Using
the SQL Client Network Util) then does this scenario work?
I think it will as long as you've created an alias on each client to
reference the Servername and port. Port being an
important factor.
Since DNS knows nothing about ports or instances, this is the only way i
think it will work. Otherwise, the traffic will
default to 1433 and the clients fail to connect.
Let me know if this makes sense.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
I have two different server with the same SQL instance
with the same databases on
SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
to switch from SERVER1 to SERVER2 without causing users
problems.
We thought to add in the DNS server the IP adresses of
the server1 and giving the TEST ...but that's not work
because the ip adress alone is not realy associate with
the SQL Instance. And we can't put in DSN an IP adress
with the \NODE1.
So, someone can help me to put on this full system
availability strategy ?
Thanks...
The client software can specify the IP address and instance name
instead of the computer name and instance name. Ex:
192.x.x.x\<ABCInstance>.
Lou Arnold
Ottawa.
On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have two different server with the same SQL instance
>with the same databases on
>SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
>to switch from SERVER1 to SERVER2 without causing users
>problems.
>We thought to add in the DNS server the IP adresses of
>the server1 and giving the TEST ...but that's not work
>because the ip adress alone is not realy associate with
>the SQL Instance. And we can't put in DSN an IP adress
>with the \NODE1.
>So, someone can help me to put on this full system
>availability strategy ?
>Thanks...
|||Yes i know that Lou but it's not our goal to past all the
300 user's computers to change the client network alias
ip adresse.
We use actualy an alias name for a server with a default
installation of SQL Server .. and that's work good on
because the ip adress in the DNS is the same name than
the SQL Server name. And we can easyly change the ip
adress into the DNS Server and we don't have to change
300 odbc connections...
>--Original Message--
>The client software can specify the IP address and
instance name[vbcol=seagreen]
>instead of the computer name and instance name. Ex:
>192.x.x.x\<ABCInstance>.
>Lou Arnold
>Ottawa.
>On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
><anonymous@.discussions.microsoft.com> wrote:
with
>.
>
|||Ok. You're right. Sorry I can't help further.
Lou.
On Thu, 22 Jul 2004 11:03:25 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes i know that Lou but it's not our goal to past all the
>300 user's computers to change the client network alias
>ip adresse.
>We use actualy an alias name for a server with a default
>installation of SQL Server .. and that's work good on
>because the ip adress in the DNS is the same name than
>the SQL Server name. And we can easyly change the ip
>adress into the DNS Server and we don't have to change
>300 odbc connections...
>instance name
>with
|||Hi Pierre,
Are the instances in your example on a SQL Cluster or StandAlone?
Are you using 1433 as the port or other?
When your clients connect to the Server do the specify Server\InstancName
or just Server?
The reason I ask is that MDAC has some rules in connections to Named
Instances.
You must use either :
1. Server\InstanceName
or
2. Server, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin,
That's a standalone servers. and actually the clients are
using SERVER1\NODE1 ( port:1435 ) ... and I want to be
able to switch easily to SERVER2\NODE1 ( port:1435 ).
Actually we use an alias in th DNS for a SQL Server (
just server like you wrote ) and that's work very well
because ... the ODBC's client are configured with an
alias named ALIAS1 whose got the SERVER1 IP Adress in the
DNS ... When we want to switch, we just change the IP
Adress of the alias ALIAS1 in DNS and that's it ... the
clients use the SERVER2 without causing connectivity
problem ...
But with Instancename we can't use this strategy but we
would like because we think it's a good way to keep alive
an application 24 hours a days ...
Thanks Again ..
>--Original Message--
>Hi Pierre,
>Are the instances in your example on a SQL Cluster or
StandAlone?
>Are you using 1433 as the port or other?
>When your clients connect to the Server do the specify
Server\InstancName
>or just Server?
>The reason I ask is that MDAC has some rules in
connections to Named
>Instances.
>You must use either :
> 1. Server\InstanceName
> or
> 2. Server, port
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>
|||OK. So, if the client has an alias configure to use Server1,1435 (Using
the SQL Client Network Util) then does this scenario work?
I think it will as long as you've created an alias on each client to
reference the Servername and port. Port being an
important factor.
Since DNS knows nothing about ports or instances, this is the only way i
think it will work. Otherwise, the traffic will
default to 1433 and the clients fail to connect.
Let me know if this makes sense.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Alias in Query
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchoo
lName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony RobinsonHi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchool
Name
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes matter
s worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:u%23cQP0
f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||...it's comparing it against a USERID with a value of 1:
AND WHERE U.UserID = CONVERT(varchar(15), 1)
think of it as
AND WHERE U.UserID = CONVERT(varchar(15), @.USERID)
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eEBDbNg7
FHA.3804@.TK2MSFTNGP14.phx.gbl...
You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinsonsql
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchoo
lName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony RobinsonHi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchool
Name
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes matter
s worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:u%23cQP0
f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||...it's comparing it against a USERID with a value of 1:
AND WHERE U.UserID = CONVERT(varchar(15), 1)
think of it as
AND WHERE U.UserID = CONVERT(varchar(15), @.USERID)
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eEBDbNg7
FHA.3804@.TK2MSFTNGP14.phx.gbl...
You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinsonsql
alias in insert statement
I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
Sincerely
Why use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
Sincerely
Why use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP
alias in insert statement
I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP
--
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)