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
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Tuesday, March 27, 2012
Alias has confused me.
I'm trying to learn how to make and use aliases for two tables in in
this update statement:
this update statement:
ALTER PROCEDURE dbo.UpdateStatus
AS UPDATE dbo.npfields
SET Status = N'DROPPED'
FROM dbo.npfields NPF, dbo.importparsed IMP
LEFT JOIN IMP
ON (NPF.pkey = IMP.pkey)
WHERE (IMP.pkey IS NULL) AND
((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =
N'ERR3'))
I thought I could define the aliases in the FROM statement.
I'm using Access as a front end to SQL server if that makes a
difference in the queries.A couple of quick notes...
- In the future please post DDL statements to create your tables as
well as INSERT statements to fill them with sample data. It's also
usually helpful to provide a sample of what you expect to see in your
solution.
- For problems where you are encountering an error, please *provide the
exact error message*. It's very hard to try to help solve someone's
problem when you don't know what the problem is. Imagine taking your
car to a mechanic, saying, "There's something wrong with my car, please
fix it." and then leaving.
Yes, you can (and should usually) define aliases in the FROM statement.
In an UPDATE statement, if you use aliases in the FROM/JOIN clause(s)
then you need to use that alias in the UPDATE clause. So, you should
have:
UPDATE NPF
SET Status = ...
The reason that this is required is because you don't always have to
update the table in the FROM clause - it can be one of the tables in
your JOIN clause. You could also have the same table appear twice in
your query, so SQL would not know which one you wanted to actually
update. For example:
UPDATE MyTable
SET child_flag = 1
FROM MyTable T1
INNER JOIN MyTableT2 ON T2.parent_id = T1.id
Which rows do I really want to update? The rows using T2 or using T1?
HTH,
-Tom.|||Hi shumaker,
On 15 Apr 2005 10:08:27 -0700, shumaker@.cs.fsu.edu wrote:
>I'm trying to learn how to make and use aliases for two tables in in
>this update statement:
(snip)
In addition to Thomas' remarks, some more thoughts:
>FROM dbo.npfields NPF, dbo.importparsed IMP
>LEFT JOIN IMP
This is the part that will cause an error. The parser will interpret
this as an "old-style" join between npfields (aliased as NPF) and
importparsed (aliased as IMP), which is then left joined to a third
table, named IMP (which probably does not exist in your DB). The syntax
you need is
FROM dbo.npfields AS npf
LEFT JOIN dbo.importparsed AS imp
(Note that I included the optional AS keyword - IMO, this makes it
easier to see that you're using aliases).
Also, remember that the UPDATE ... FROM is proprietary syntax that won't
port to any other database. And it has some side effects that can bite
you pretty bad if you're not aware of them (especially if rows in the
table to be updated can be joined to more than one row in the other
tables).
This syntax does have it's uses, but you should consider very carefully
when you use it, and stick to ANSI standard syntax whenever possible. In
the case of this specific query, I'd prefer this version:
UPDATE dbo.npfields
SET Status = N'DROPPED'
WHERE Status IN (N'ERR1', N'ERR2', N'ERR3')
AND NOT EXISTS (SELECT *
FROM dbo.importparsed AS imp
WHERE imp.pkey = npfields.pkey)
>I'm using Access as a front end to SQL server if that makes a
>difference in the queries.
That depends. For pass-through queries, Access just hands the query text
over to SQL Server; SQL Server then executes the query and passes the
results back to Access. These queries have to use SQL that SQL Server
understands (most of the ANSI standard, plus Transact-SQL extensions).
For "normal" (i.e. not pass-through) queries, Access itself will execute
the query, fetching rows from SQL Server, joining and processing on the
client (and, in the case of an UPDATE, passing changes back to SQL
Server). These queries have to use Jet-SQL, that unfortunately is quite
different from both ANSI and T-SQL.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You are a student so you get the full lecture!
Why are you writing in a dangerous dialect? You do know that this
syntax can cause cardinality violations to go undetected. Try using
Standard SQL, instead. My guess is that you want something like this:
UPDATE NPfields
SET foobar_status = 'DROPPED'
WHERE foobar_status IN ('ERR1', 'ERR2', 'ERR3') -- current values
AND EXISTS -- have a match in the other table
(SELECT *
FROM ImportParsed AS IMP
WHERE IMP.pkey = NPfields.pkey);
Since status is too vague to be a data element name, I changed it; you
will want something more meaningful.
>> I thought I could define the aliases in the FROM statement. <<
There is no FROM clause in an UPDATE. It wold make no senses in the
SQL model. An alais has to act as if it is materialized, so in
Standard SQL you would be changing a working table that disappears at
the end of statement.
>> I'm using Access as a front end to SQL server if that makes a
difference in the queries. <<
ACCESS is a total mess; can you get a better front end at your school?|||This is at work. They aren't really familier with anything other than
Access, and I want to have it setup so that if I ever leave they will
be able to make modifications themselves.
Is there some other frontend you would suggest? It needs to be
something that requires little or no programming knowledge for the sake
of being useable by future employees who will likely not have
programming knowledge. I could probably get just about anything I
asked for.
I've got it setup to use SQL Server instead of the Access database
files because they are prone to corruption.
Sorry about vagueness in my post. I really am new to SQL, and the only
examples of UPDATE statements I could find on the net were fairly
simplistic and referenced only a single table.
I get confused about the flow control of the statement. I get the
impression that parts of the SQL statement are executed, and return a
set of records that are operated on by the next part of the statement.
I am trying to update all the records in a table where the primary key
of the record is not found in a second table.
I will look over your posts and if I still am having trouble I will
post again with better detail.
Thanks all.|||Edit:
I am trying to update all the records in a table where the primary key
of the record is not found in a second table, and the Status field of
the record == ERR1, ERR2, or ERR3
but like I said, maybe I can figure it out on my own now.
Thursday, March 8, 2012
Aggregate problem
I am getting the following error in my SQL.
Column 'dbo.ClientWorkerStatus.WorkerTypeID' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
My code is below. What I want to get is up to 1, 2 or 3 counts for one
ClientID as there are multiples in the ClientWorkerStatus table.
SELECT dbo.ClientWorkerStatus.ClientID,
Preferred = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 0 THEN 1
ELSE 0
END,
Family = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 1 THEN 1
ELSE 0
END,
Pool = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 2 THEN 1
ELSE 0
END
FROM dbo.ClientWorkerStatus INNER JOIN
dbo.ClientWorkerLink ON dbo.ClientWorkerStatus.ClientID =
dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID = dbo.ClientWorkerLink.WorkerID
WHERE (dbo.ClientWorkerStatus.StatusEnd IS NULL OR
dbo.ClientWorkerStatus.StatusEnd > GETDATE()) AND
(dbo.ClientWorkerLink.Active = 1)
GROUP BY dbo.ClientWorkerStatus.ClientID
Thanks, DavidWhat aggregate?
Have you tried something like:
SELECT s.ClientID,
Preferred = CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
Family = CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
Pool = CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
FROM
dbo.ClientWorkerStatus s
INNER JOIN dbo.ClientWorkerLink l
ON s.ClientID = l.ClientID
AND s.WorkerID = l.WorkerID
WHERE
COALESCE(s.StatusEnd, '20300101') > GETDATE()
AND l.Active = 1
GROUP BY
s.ClientID,
CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
Without DDL, sample data and desired results, this is only a guess. See
http://www.aspfaq.com/5006 for info on providing details that will yield a
full solution.|||Hello, David
Is the ClientI=ADD column the primary key (or a unique key) in the
ClientWorkerStatus table ? If yes, you can safely add the
WorkerT=ADypeID column to the GROUP BY list, i.e:
SELECT [...]
GROUP BY dbo.ClientWorkerStatus.ClientI=ADD,
dbo.ClientWorkerStatus.WorkerT=ADypeID=20
Razvan|||Erase your group by at the end of your query.
You don't have any sum, avg, .... in your query. The group by clause is not
required.
Jonathan
"Razvan Socol" wrote:
> Hello, David
> Is the ClientI_D column the primary key (or a unique key) in the
> ClientWorkerStatus table ? If yes, you can safely add the
> WorkerT_ypeID column to the GROUP BY list, i.e:
> SELECT [...]
> GROUP BY dbo.ClientWorkerStatus.ClientI_D,
> dbo.ClientWorkerStatus.WorkerT_ypeID
> Razvan
>|||Yes, Aaron...your's worked. However I was also able to get it to work
by surrounding the CASE statements in the SELECT clause by SUM(...).
For example:
Preferred = SUM(CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END), ...etc
I do want to group the results by ClientID because any one ClientID can
have more than 1 WorkerTypeID so I need the count of each.
Thanks.
David
*** Sent via Developersdex http://www.examnotes.net ***|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You have absolutely no idea how to design a schema, do you?
You have columns named "-type_id" which make no sense. Look at the
ISO-11179 Standards.
You have table names that end with "-link", as if you were writing a
1970's navigation database.
You have table of status codes when status is a kind of attribute, not
an entity.
Then after all those FUNDAMENTAL errors, you do not have any aggregates
in the useless crap you posted.
dbo.ClientWorkerStatus.StatusE=ADnd > GETDATE()) <<
You might want to get enough basic SQL programming skills to write this
as:
COALESCE (foo_status_end_date, CURRENT_TIMESTAMP) >=3D CURRENT_TIMESTAMP
There is no such thing as a magical, universal "status": -- it is the
status of **something**. That getdate() is a proprietary syntax which
good SQL programmers avoid like lice. Learn the options in the
language so you can write them in SQL and not in some 3GL-style syntax.
Column 'dbo.ClientWorkerStatus.WorkerTypeID' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
My code is below. What I want to get is up to 1, 2 or 3 counts for one
ClientID as there are multiples in the ClientWorkerStatus table.
SELECT dbo.ClientWorkerStatus.ClientID,
Preferred = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 0 THEN 1
ELSE 0
END,
Family = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 1 THEN 1
ELSE 0
END,
Pool = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 2 THEN 1
ELSE 0
END
FROM dbo.ClientWorkerStatus INNER JOIN
dbo.ClientWorkerLink ON dbo.ClientWorkerStatus.ClientID =
dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID = dbo.ClientWorkerLink.WorkerID
WHERE (dbo.ClientWorkerStatus.StatusEnd IS NULL OR
dbo.ClientWorkerStatus.StatusEnd > GETDATE()) AND
(dbo.ClientWorkerLink.Active = 1)
GROUP BY dbo.ClientWorkerStatus.ClientID
Thanks, DavidWhat aggregate?
Have you tried something like:
SELECT s.ClientID,
Preferred = CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
Family = CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
Pool = CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
FROM
dbo.ClientWorkerStatus s
INNER JOIN dbo.ClientWorkerLink l
ON s.ClientID = l.ClientID
AND s.WorkerID = l.WorkerID
WHERE
COALESCE(s.StatusEnd, '20300101') > GETDATE()
AND l.Active = 1
GROUP BY
s.ClientID,
CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
Without DDL, sample data and desired results, this is only a guess. See
http://www.aspfaq.com/5006 for info on providing details that will yield a
full solution.|||Hello, David
Is the ClientI=ADD column the primary key (or a unique key) in the
ClientWorkerStatus table ? If yes, you can safely add the
WorkerT=ADypeID column to the GROUP BY list, i.e:
SELECT [...]
GROUP BY dbo.ClientWorkerStatus.ClientI=ADD,
dbo.ClientWorkerStatus.WorkerT=ADypeID=20
Razvan|||Erase your group by at the end of your query.
You don't have any sum, avg, .... in your query. The group by clause is not
required.
Jonathan
"Razvan Socol" wrote:
> Hello, David
> Is the ClientI_D column the primary key (or a unique key) in the
> ClientWorkerStatus table ? If yes, you can safely add the
> WorkerT_ypeID column to the GROUP BY list, i.e:
> SELECT [...]
> GROUP BY dbo.ClientWorkerStatus.ClientI_D,
> dbo.ClientWorkerStatus.WorkerT_ypeID
> Razvan
>|||Yes, Aaron...your's worked. However I was also able to get it to work
by surrounding the CASE statements in the SELECT clause by SUM(...).
For example:
Preferred = SUM(CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END), ...etc
I do want to group the results by ClientID because any one ClientID can
have more than 1 WorkerTypeID so I need the count of each.
Thanks.
David
*** Sent via Developersdex http://www.examnotes.net ***|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You have absolutely no idea how to design a schema, do you?
You have columns named "-type_id" which make no sense. Look at the
ISO-11179 Standards.
You have table names that end with "-link", as if you were writing a
1970's navigation database.
You have table of status codes when status is a kind of attribute, not
an entity.
Then after all those FUNDAMENTAL errors, you do not have any aggregates
in the useless crap you posted.
dbo.ClientWorkerStatus.StatusE=ADnd > GETDATE()) <<
You might want to get enough basic SQL programming skills to write this
as:
COALESCE (foo_status_end_date, CURRENT_TIMESTAMP) >=3D CURRENT_TIMESTAMP
There is no such thing as a magical, universal "status": -- it is the
status of **something**. That getdate() is a proprietary syntax which
good SQL programmers avoid like lice. Learn the options in the
language so you can write them in SQL and not in some 3GL-style syntax.
Sunday, February 19, 2012
After RESTORE, must include DBO in queries?
I used to be able to write a query like this:
select * from foo;
But after re-creating my database using RESTORE, I now have to do this:
select * from myName.foo;
...where myName is either the name of the database or the name of the database owner. That is, it's the name of the database, but I believe it's also the name of the database owner.
How do I go back to being able to just use the table name?
Thanks!Looks like what's happening is that for some reason when the ASPNET user tries to run a query on tables that were created by JoeSmith, it needs to qualify them as
<username>.<tablename
But this never used to be the case, so there must be a way around it.|||FromDatabase Object Owner:
Referencing database objects
When users access an object created by another user, the object should be qualified with the name of the object owner; otherwise, Microsoft® SQL Server? may not know which object to use because there could be many objects of the same name owned by different users. If an object is not qualified with the object owner when it is referenced (for example, my_table instead of owner.my_table), SQL Server looks for an object in the database in the following order:
--Owned by the current user.
--Owned by dbo.
If the object is not found, an error is returned.
I'm not sure what changed after the restore, but something is now different. Those tables must have previously not been owned by JoeSmith, or the user accessing the tables was different.
Terri|||Thanks, Terri. The problem was, in fact, table ownership. Thedatabase owner was correct, but not thetable owner. After changing the ownership of the tables to "dbo", everything works.
Thursday, February 16, 2012
after restore db to another server got problem
Hi,
Our production database JDE7334 tables not belong to dbo
owner; those tables belong to 4 different users.
Once I did backuped and restored this database to another
server. I can't use sp_dropuser to drop automatically
existing users because the user owns objects in the
database and cannot be dropped. The thing is I can't
change table's owner from specific user to dbo, this
database work with JDEdwards software.
My question is how to create login and user to access this
database in new server?
The primary server which I did backup installed SQL Server
Standard Edition with SP3 and the secondary server which I
did restore installed SQL Server Enterprise Edition with
SP2.
Regards
JennyTake a look at these:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jenny" <jyu@.iseoptions.com> wrote in message
news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
> Hi,
> Our production database JDE7334 tables not belong to dbo
> owner; those tables belong to 4 different users.
> Once I did backuped and restored this database to another
> server. I can't use sp_dropuser to drop automatically
> existing users because the user owns objects in the
> database and cannot be dropped. The thing is I can't
> change table's owner from specific user to dbo, this
> database work with JDEdwards software.
> My question is how to create login and user to access this
> database in new server?
> The primary server which I did backup installed SQL Server
> Standard Edition with SP3 and the secondary server which I
> did restore installed SQL Server Enterprise Edition with
> SP2.
> Regards
> Jenny|||Thanks a lot Andrew, it worked.
Jenny
>--Original Message--
>Take a look at these:
>
>http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>to a New Location with Detach/Attach
>http://support.microsoft.com/?id=221465 Using
WITH MOVE in a
>Restore
>http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>Passwords Between SQL Servers
>http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>Restore
>http://www.dbmaint.com/SyncSqlLogins.asp Utility
to map logins to
>users
>http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>Errors After Restoring Dump
>http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>Issues When a Database Is Moved Between SQL Servers
>http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
>Restoring a .mdf
>http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>for SQL Server
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jenny" <jyu@.iseoptions.com> wrote in message
>news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
>> Hi,
>> Our production database JDE7334 tables not belong to dbo
>> owner; those tables belong to 4 different users.
>> Once I did backuped and restored this database to
another
>> server. I can't use sp_dropuser to drop automatically
>> existing users because the user owns objects in the
>> database and cannot be dropped. The thing is I can't
>> change table's owner from specific user to dbo, this
>> database work with JDEdwards software.
>> My question is how to create login and user to access
this
>> database in new server?
>> The primary server which I did backup installed SQL
Server
>> Standard Edition with SP3 and the secondary server
which I
>> did restore installed SQL Server Enterprise Edition with
>> SP2.
>> Regards
>> Jenny
>
>.
>
Our production database JDE7334 tables not belong to dbo
owner; those tables belong to 4 different users.
Once I did backuped and restored this database to another
server. I can't use sp_dropuser to drop automatically
existing users because the user owns objects in the
database and cannot be dropped. The thing is I can't
change table's owner from specific user to dbo, this
database work with JDEdwards software.
My question is how to create login and user to access this
database in new server?
The primary server which I did backup installed SQL Server
Standard Edition with SP3 and the secondary server which I
did restore installed SQL Server Enterprise Edition with
SP2.
Regards
JennyTake a look at these:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jenny" <jyu@.iseoptions.com> wrote in message
news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
> Hi,
> Our production database JDE7334 tables not belong to dbo
> owner; those tables belong to 4 different users.
> Once I did backuped and restored this database to another
> server. I can't use sp_dropuser to drop automatically
> existing users because the user owns objects in the
> database and cannot be dropped. The thing is I can't
> change table's owner from specific user to dbo, this
> database work with JDEdwards software.
> My question is how to create login and user to access this
> database in new server?
> The primary server which I did backup installed SQL Server
> Standard Edition with SP3 and the secondary server which I
> did restore installed SQL Server Enterprise Edition with
> SP2.
> Regards
> Jenny|||Thanks a lot Andrew, it worked.
Jenny
>--Original Message--
>Take a look at these:
>
>http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>to a New Location with Detach/Attach
>http://support.microsoft.com/?id=221465 Using
WITH MOVE in a
>Restore
>http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>Passwords Between SQL Servers
>http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>Restore
>http://www.dbmaint.com/SyncSqlLogins.asp Utility
to map logins to
>users
>http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>Errors After Restoring Dump
>http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>Issues When a Database Is Moved Between SQL Servers
>http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
>Restoring a .mdf
>http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>for SQL Server
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jenny" <jyu@.iseoptions.com> wrote in message
>news:12b1301c3f63d$ecccf980$a501280a@.phx.gbl...
>> Hi,
>> Our production database JDE7334 tables not belong to dbo
>> owner; those tables belong to 4 different users.
>> Once I did backuped and restored this database to
another
>> server. I can't use sp_dropuser to drop automatically
>> existing users because the user owns objects in the
>> database and cannot be dropped. The thing is I can't
>> change table's owner from specific user to dbo, this
>> database work with JDEdwards software.
>> My question is how to create login and user to access
this
>> database in new server?
>> The primary server which I did backup installed SQL
Server
>> Standard Edition with SP3 and the secondary server
which I
>> did restore installed SQL Server Enterprise Edition with
>> SP2.
>> Regards
>> Jenny
>
>.
>
Subscribe to:
Posts (Atom)