Cannot reference derived table from a derived table in a subquery?

The follow generates the error:
Server: Msg 207, Level 16, State 3, Line 19
Invalid column name 'UserID'.
The inner derived table ("dt") cannot reference the outer derived table
("ActiveUsers") ?
Nevermind the contrived example - it is only serving to illustrate the
failing. The real situation is entirely more complex.
IF OBJECT_ID('tempdb..#Users') IS NOT NULL
DROP TABLE #Users
IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
DROP TABLE #Logins
CREATE TABLE #Users (
UserID int,
Name varchar(50),
IsActive tinyint)
CREATE TABLE #Logins (
UserID int,
LoginDate datetime,
WasSecured tinyint)
SELECT
ActiveUsers.*,
( SELECT MAX(LoginDate)
FROM (
SELECT * FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) dt
) AS LastSecuredLogin
FROM (
SELECT *
FROM #Users
WHERE IsActive <> 0
) ActiveUsers
DROP TABLE #Users
DROP TABLE #LoginsTry,
SELECT
ActiveUsers.*,
l.dt AS LastSecuredLogin
FROM
#Users as ActiveUsers
left join
(
SELECT
#Logins.UserID,
MAX(LoginDate) as dt
FROM
#Logins
WHERE
#Logins.WasSecured <> 0
group by
#Logins.UserID
) as l
on l.UserID = ActiveUsers.UserID
WHERE
ActiveUsers.IsActive <> 0
go
AMB
"Ian Boyd" wrote:

> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||Right, in your correlated subquery, you have a derived table that references
something outside of the scope of the derived table. Basically you have to
think of derived tables as views. They cannot reference anything from the
current query.
Maybe I am being a bit of a thicky, but I don't see why:
( SELECT MAX(LoginDate)
FROM ( SELECT *
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0 ) dt
) AS LastSecuredLogin
cannot be rewritten as:
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
The only reason to do what you were doing (I think) is if you were going to
do an aggregate on an aggregate. The where clause and the groupings are
exclusive of one another, so it should work. Am I missing something?
Full Query:
SELECT
ActiveUsers.*,
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
FROM ( SELECT *
FROM #Users
WHERE IsActive <> 0 ) as ActiveUsers
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:u462TqHCFHA.3740@.TK2MSFTNGP09.phx.gbl...
> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||> Maybe I am being a bit of a thicky, but I don't see why:
Becuase this wasn't my situation. The two tables is a contrived example -
boiled down to still cause the problem.
The "inner" derived table query's 3 tables unioned to 3 more tables.
The "outer" derived table query 3 more separate tables.|||Ah, I thought you weren't going to give us a contrived example :) I see.
Anyhow, you cannot use derived tables like that. You might look at user
defined functions if Alejandro's suggestion doesn't work. You can do a lot
of stuff in there. They work very well in the SELECT clause. Or post
your entire query and who knows, someone may have done the same thing in the
past.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23tnVDIICFHA.3740@.TK2MSFTNGP09.phx.gbl...
> Becuase this wasn't my situation. The two tables is a contrived example -
> boiled down to still cause the problem.
> The "inner" derived table query's 3 tables unioned to 3 more tables.
> The "outer" derived table query 3 more separate tables.
>
>|||The query has been restructured; and it eliminates the reference from one
derived table to the other.

> Or post your entire query and who knows, someone may have done the same
> thing in the past.
Absoutly not. Nobody has ever done anything like it. The query is 3 screens
long.
And i'm not going to be posting DDL, sample inserts, or explanations.
Or the query :)|||> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
Well, not sure how long a screen is, but I am sure you are unlikely to be
the first person to do "anything like it."

> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
Then good luck, can't help you out if we can't see what you are doing. :)
Either way, your derived table must be self contained.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:Oa3IGfICFHA.3644@.TK2MSFTNGP15.phx.gbl...
> The query has been restructured; and it eliminates the reference from one
> derived table to the other.
>
> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
>sql

Cannot Open Database

We are getting the following error while opening a database.
Msg 946, Level 14, State 1, Line 1
Cannot open database 'ESPune' version 539. Upgrade the database to the
latest version.
Kindly guide us how to upgrade the db to the latest version.
Except this db ESPune , all other dbs have version 611 when I see the
query select * from sysdatabases.
Regards
RajagopalHello Rajagopal!
Try to detach / attach your database.
Ekrem Önsoy
<rajagopal.ns@.gmail.com> wrote in message
news:1190980646.422354.287350@.50g2000hsm.googlegroups.com...
> We are getting the following error while opening a database.
> Msg 946, Level 14, State 1, Line 1
> Cannot open database 'ESPune' version 539. Upgrade the database to the
> latest version.
> Kindly guide us how to upgrade the db to the latest version.
> Except this db ESPune , all other dbs have version 611 when I see the
> query select * from sysdatabases.
> Regards
> Rajagopal
>|||What version of SQL Server are you using? when did you start seeing this
problem?
Was the database recently upgraded from an older version?
Could you please provide more info on this?
Thank you,
Saleem Hakani
WWW.SQLCOMMUNITY.COM
SQL Server Tips, SQL Server Scripts, SQL Server Forums, SQL Server Blogs,
SQL Server Radio, SQL Server Events..
"rajagopal.ns@.gmail.com" wrote:
> We are getting the following error while opening a database.
> Msg 946, Level 14, State 1, Line 1
> Cannot open database 'ESPune' version 539. Upgrade the database to the
> latest version.
> Kindly guide us how to upgrade the db to the latest version.
> Except this db ESPune , all other dbs have version 611 when I see the
> query select * from sysdatabases.
> Regards
> Rajagopal
>

Cannot get CREATE LOGIN from a Windows group to work

I have created a database fronted by an ASP.Net application. It's all nice and simple, and I only need a very simple level of security (and even that is only as a protection against accidents rather than maliciousness). My intention is that users connect using Windows impersonation (<identity="true">), with the database creator having full access and the public group (I'm talking SQL groups here) having specific premissions granted on specific tables.

If I set <identity="false"> on my XP box the application connects to the database as [MACHINE\ASPNET]. This is easy to set up access for - I simply do a

CREATE LOGIN [MACHINE\ASPNET] FROM WINDOWS

and then within the actual database do a

CREATE USER [MACHINE\ASPNET]

But as I said, I want to use Windows impersonation. When I set <identity="true">, the application correctly attempts to connect as the actual Windows user account (e.g. [MACHINE\testuser]). If that user is the user who installed the database, then all is well and it has full access. For anything else, I get a "cannot log on" error - this much I expect.

So I want to permit logins for all other users, and I want this to work regardless of whether the machine is a standalone machine whose "domain" is simply the machine's own name, whether it is in some form of traditional peer-to-peer workgroup, or whether the machine is connected to a real domain. I also want it to work on XP and Windows Server 2003 (and ideally Vista also, but that can wait). When I try the following:

CREATE LOGIN [MACHINE\Users] FROM WINDOWS

I get this error:

Msg 15401, Level 16, State 1, Server MACHINE\SQLEXPRESS, Line 1
Windows NT user or group 'MACHINE\Users' not found. Check the name again.

Nor does it work with [Everyone] (that one has no domain/folder listed against it in any permissions dialogs on my domainless development PC). So I'm stuck and confused. It's taken me ages just to get this far. Any suggestions anyone?

Thanks in advance.

Hi,

Ensure your windows users have grant access for that db.

Refer for adding windows user/group to sql server for work around.

http://msdn2.microsoft.com/en-gb/library/aa905177(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/aa163753(office.10).aspx

http://www.databasedesign-resource.com/adding-users-in-sql-server.html

Hemantgiri S. Goswami

|||

Thanks, but that doesn't really help. One of these articles simply explains how to create Windows accounts, one explains how to use the sp_grantlogin storproc (which essentially issues the same SQL commands as I have listed above, and it gives exactly the same error messages), and the third tells you how to do it all manually, whereas I need to do it programmatically.

I should add that when I try to add a specific user account rather than the group account, the command works fine, so something is wrong with the attempt to add the group account. Does anyone know anything specific to groups?

|||I have found the answer. The issue is local versus global groups. CREATE LOGIN [domain\group] FROM WINDOWS works fine (i.e. a global group), but if you try it with a local group such as MACHINE\Users it will fail every time - this is a security feature of SQL Server. Which means that this approach will only work in a domain environment and not in a workgroup or on a shared single machine. The alternative is to use a nice GUI tool to manually add logins and users to SQL Express, but since SQL Express does not come with nice tools like Enterprise Manager, I may have to write my own and include it in the distribution. The group 'Everyone' fails also because it's what I would term a pseudo-group rather than either a local or a glocal group.|||

hi,

michael412 wrote:

I have found the answer. The issue is local versus global groups. CREATE LOGIN [domain\group] FROM WINDOWS works fine (i.e. a global group), but if you try it with a local group such as MACHINE\Users it will fail every time - this is a security feature of SQL Server. Which means that this approach will only work in a domain environment and not in a workgroup or on a shared single machine. The alternative is to use a nice GUI tool to manually add logins and users to SQL Express, but since SQL Express does not come with nice tools like Enterprise Manager, I may have to write my own and include it in the distribution. The group 'Everyone' fails also because it's what I would term a pseudo-group rather than either a local or a glocal group.

you can try defining your own local Windows group...
add a new Group via the "Computer Management" MMC snap-in, say you name it "MyMachineAllowedAccounts" and add the required windows accounts as members to that group...

then use the "nice ui tool" SQLExpress is provided with (yes, SQLEpress provides an official one ) and execute a standard
CREATE LOGIN [MACHINE_NAME\MyMachineAllowedAccounts] FROM WINDOWS;
and it will succed as expected..

regards

|||

The plot thickens. If I define a group MyGroup myself, then I can do CREATE LOGIN [MACHINE\MyGroup] FROM WINDOWS and it works. If I try to use one of the predefined groups such as MACHINE\Users, it fails. I don't at this point know whether this rule is constrained to local groups or whether it applies to global groups also. The group I added this morning in the office was a global group that we had created (i.e. it wasn't a predefined one), whereas the group that I failed to add was a predefined local group. I concluded that the important difference was whether the group was local or global, but now I realise that it could have been purely down to whether the group was predefined or created after installation. I don't see why SQL Server would want to prevent the use of predefined groups, and I've seen no mention of the importance of this distinction in any of the documentation. Can anyone throw any light on this?

Also, what is this GUI tool that is supposedly provided? I've looked through all the directories that were installed with SQL EXPRESS and haven't found one.

|||

hi Michael,

michael412 wrote:

The plot thickens. If I define a group MyGroup myself, then I can do CREATE LOGIN [MACHINE\MyGroup] FROM WINDOWS and it works. If I try to use one of the predefined groups such as MACHINE\Users, it fails.

it's only a "syntactical" problem as "how" you reference your predefined group's names...

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;

I don't at this point know whether this rule is constrained to local groups or whether it applies to global groups also. The group I added this morning in the office was a global group that we had created (i.e. it wasn't a predefined one), whereas the group that I failed to add was a predefined local group. I concluded that the important difference was whether the group was local or global, but now I realise that it could have been purely down to whether the group was predefined or created after installation. I don't see why SQL Server would want to prevent the use of predefined groups, and I've seen no mention of the importance of this distinction in any of the documentation. Can anyone throw any light on this?

check your syntax

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;

Also, what is this GUI tool that is supposedly provided? I've looked through all the directories that were installed with SQL EXPRESS and haven't found one.

SQL Server Management Studio Express is not installed as part of the "client components" of the "standard" package and must be manually and separately downloaded and installed.. on the contrary, SQL Server Express with Advanced Services includes it and the tool can be selected to be installed as part of the installation process..

regards

|||

Thanks Andrea - I think that's me sorted out now. Sorry to take so long to reply - I got tired and took a couple of days break from the PC.

Best regards,

Michael

Cannot find sp_send_cdosysmail

Hello, everyone:

I want to send email by sp_send_cdosysmail, but I got error,

Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'sp_send_cdosysmail'.

Even though I run the query under master database. Is it not available in SQL Server 2000?

This is my query:

declare @.Body varchar(4000)
select @.Body = 'This is a Test Message'
exec sp_send_cdosysmail 'someone@.example.com','someone2@.example.com','Test of CDOSYS',@.Body

Any help will be appreciated.

Thanks

ZYThttp://support.microsoft.com/kb/312839