Changing a SQL login from DBO to SA ( and back )

Hi,
Looking for a proper way to change a SQL login ( not a domain user ) from
DBO to SA ( and back if a curtain action has finished ) using TSQL.
This user is NOT connected to any role therefor sp_droprolemember/
sp_addrolemember is not appropriate.
Can you help me out guys?
G Kramerwould SETUSER or EXECUTE AS do?
Peter

Changing a null to another value in select?

Hi all,
I have a result set coming back with one item that is null, because I am
trying to sort by this column this result is always at the top of the result
set, however I need them to be at the bottom of the result set, I cant
change the ordering because then the other results will no longer be
alphabetically sorted. Is there a way to change a 'null' to something else
if it is returned in a result set? My hope was perhaps to set it to be 10
Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
happens) be at the bottom?
Possible?
Regards
RobSelect isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hope this helps
Chris
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:4BbJd.17892$GG1.5654@.text.news.blueyonder.co.uk...
> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the
> result
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something
> else
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>|||"Chris, Master of All Things Insignificant" wrote ...

> Select isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hi Chris,
Thanks for the reply. I managed something similar myself, here's my view:
SELECT '1' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '0'
UNION ALL
SELECT '1' AS ViewOrder1, '2' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '1'
UNION ALL
SELECT '3' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID <> '0'
UNION ALL
SELECT '4' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID = '0'
As you can see the last 2 sections of this (where viewOrder1 is either 3 or
4 etc) I have inserted the 'ZZZZZZZZZZ' as the section name...
This does work, but feels a bit flakey...if someone was to actually create a
'section' in my application called 'ZZZZZZZZZZZ' it would obviously cause
problems...
I originally had in my ASP code 4 statements which went off and got the
respective results for each case, but I thought making just one hit would be
better, now I face a different problem...
Any ideas anyone?
Regards
Rob|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
customerid,
companyname,
region
from
customers
order by
case when region is null then 1 else 0 end asc,
customerid
go
AMB
"Rob Meade" wrote:

> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the resu
lt
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something els
e
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>
>|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.

> use northwind
(wishing now I'd not deleted that recently)

> order by
> case when region is null then 1 else 0 end asc,
> customerid
That looks like what I want, I think, I cant quite work out the 1 / 0 thing
though - whats that actually going to do - in words etc...
ie, does that make region = 1 if it = null, and = 0 if its not null?
Thanks for the reply.
Regards
Rob|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.
>
[snip]
Hi Alejandro,
I was unable to get it to work in the ORDER BY clause, but your reply did
make me go and investigate the use of CASE expressions, I've now popped it
in the SELECT statement and its working perfectly.
Thank you.
Regards
Rob|||Rob,
ORDER BY
CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
itemColumn
Steve Kass
Drew University
Rob Meade wrote:

>Hi all,
>I have a result set coming back with one item that is null, because I am
>trying to sort by this column this result is always at the top of the resul
t
>set, however I need them to be at the bottom of the result set, I cant
>change the ordering because then the other results will no longer be
>alphabetically sorted. Is there a way to change a 'null' to something else
>if it is returned in a result set? My hope was perhaps to set it to be 10
>Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
>happens) be at the bottom?
>Possible?
>Regards
>Rob
>
>|||"Steve Kass" wrote ...

> ORDER BY
> CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
> itemColumn
Hi Steve,
Could you explain to me what the 1 and 0 are/do please?
Regards
Rob|||Imagine that you put this in the SELECT list instead:
SELECT ... CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END AS sortcol
For a row with NULL in itemColumn, the expression will return the value 1. I
f there is a non-null
value in itemColumn, the expression will return the value 0. So, say you hav
e some rows (sortcol is
the one produced by above expression)
ItemColumn SortCol
23 0
NULL 1
1 0
NULL 1
Now, imagine that you do:
ORDER BY SortCol, ItemColumn
Get it?
The "short cut" Steve did is to put the expression in the ORDER BY clause in
stead of in the SELECT
clause. Logically, the columns didn't "materialize" before the sort operatio
n...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:8roJd.18310$GG1.8683@.text.news.blueyonder.co.uk...
> "Steve Kass" wrote ...
>
> Hi Steve,
> Could you explain to me what the 1 and 0 are/do please?
> Regards
> Rob
>|||"Tibor Karaszi" wrote ...

> ItemColumn SortCol
> 23 0
> NULL 1
> 1 0
> NULL 1
> Now, imagine that you do:
> ORDER BY SortCol, ItemColumn
> Get it?
> The "short cut" Steve did is to put the expression in the ORDER BY clause
instead of in the SELECT
> clause. Logically, the columns didn't "materialize" before the sort
operation...
Hi Tibor,
Thanks for your reply...
I get it now, the 1 / 0 were for the ordering etc, as where I was using
1,2,3,4 previously...When I first looked at it I was wondering whether they
could only 'ever' be those values or something - its not something I've come
across/used before..
Thanks for the explanation.
Regards
Rob

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>
|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this
> connected
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > I have a db that I changed from multiple user to single user, and want
to
> > change it back now. However, it keeps giving me the message below. I
> have
> > checked with EM and sysprocesses that there is no connection to it. Any
> > suggestion what I can do?
> >
> > Thanks.
> >
> > Quentin
> >
> >
> > message:
> >
> > Server: Msg 5064, Level 16, State 1, Line 1
> > Changes to the state or options of database 'mydb' cannot be made at
this
> > time. The database is in single-user mode, and a user is currently
> connected
> > to it.
> > Server: Msg 5069, Level 16, State 1, Line 1
> > ALTER DATABASE statement failed.
> >
> >
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this[vbcol=seagreen]
> connected
>

change user login at runtime

Hi, some one help me with this please
Our application is developed in Dot Net with Sql Server 2000 as a back
end.We have created users in our application (Dot Net) that will be
physically created in the back-end in the DB to which we will be connected
other than SA. These users will be authenticated by an administrator user to
login first time. After logging the application a tree menu appears to which
the forms and reports are attached . We are trying to implement a scenario
that the user will be given privileges of select , update, insert, delete on
the forms when they will double click it and open them. After closing the
form the privileges will be revoked from the user on the certain tables
associated with that form.
Now we cannot understand which user will be giving those privileges to the
connected user as we dont want to use SA. As this could become a problem at
the deployment time as we do not want to hard code the "SA" user. I have
seen "CONNECT To" statement that could change the login at runtime but it is
not running with these commands
EXEC SQL CONNECT TO :svr USER :usr;
Or
EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";
Or
EXEC SQL CONNECT TO gizmo.pubs USER sa;
RegardsHi Shahzad:
I think you can use the connectstring to applied this.
You can change the connect string by the value you get from the asp and
change the connectstring and then connect to the sql server, I think it is
not very complexity.
Best Wishes
Wei Ci Zhou

Change table owner to dbo

How do I change the table owner back to dbo?
Never mind. I got it. I did not query MSDN correctly to find the answer.
sp_changeobjectowner
"sxa222" wrote:

> How do I change the table owner back to dbo?
|||sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
MG
"sxa222" wrote:

> How do I change the table owner back to dbo?
|||Note that in 2005, this proc change both owner and schema, and also that the proc is deprecated. As
of 2005, use ALTER AUTHORIZATION to change owner of an object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sxa222" <sxa222@.discussions.microsoft.com> wrote in message
news:06384594-B178-42B8-B705-33E60E1F8B40@.microsoft.com...[vbcol=seagreen]
> Never mind. I got it. I did not query MSDN correctly to find the answer.
> sp_changeobjectowner
> "sxa222" wrote:

Change table owner to dbo

How do I change the table owner back to dbo?Never mind. I got it. I did not query MSDN correctly to find the answer.
sp_changeobjectowner
"sxa222" wrote:
> How do I change the table owner back to dbo?|||sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
--
MG
"sxa222" wrote:
> How do I change the table owner back to dbo?|||Note that in 2005, this proc change both owner and schema, and also that the proc is deprecated. As
of 2005, use ALTER AUTHORIZATION to change owner of an object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sxa222" <sxa222@.discussions.microsoft.com> wrote in message
news:06384594-B178-42B8-B705-33E60E1F8B40@.microsoft.com...
> Never mind. I got it. I did not query MSDN correctly to find the answer.
> sp_changeobjectowner
> "sxa222" wrote:
>> How do I change the table owner back to dbo?

Change table owner to dbo

How do I change the table owner back to dbo?Never mind. I got it. I did not query MSDN correctly to find the answer.
sp_changeobjectowner
"sxa222" wrote:

> How do I change the table owner back to dbo?|||sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owne
r'
MG
"sxa222" wrote:

> How do I change the table owner back to dbo?|||Note that in 2005, this proc change both owner and schema, and also that the
proc is deprecated. As
of 2005, use ALTER AUTHORIZATION to change owner of an object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sxa222" <sxa222@.discussions.microsoft.com> wrote in message
news:06384594-B178-42B8-B705-33E60E1F8B40@.microsoft.com...[vbcol=seagreen]
> Never mind. I got it. I did not query MSDN correctly to find the answer.
> sp_changeobjectowner
> "sxa222" wrote:
>

Change schemas owned

I am using SQL Server mgmt studio with 2005 db. I accidently clicked the
owned schemas while in a database user in security. When I went back to
remove the checkboxes they were greyed out. How do I remove them? Thanks.
DavidDavid,
You could, for instance, go to the dbo user and make it the owner of the
schema. That would take it away from the other user. (You may have a
specific user other than dbo that should be the owner.) Or, if they are not
used, get rid of the extra schemas altogether.
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23kgXZKxGIHA.2328@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server mgmt studio with 2005 db. I accidently clicked the
>owned schemas while in a database user in security. When I went back to
>remove the checkboxes they were greyed out. How do I remove them? Thanks.
> David
>

Change results

I have created query As shown:

SELECT distinct [Table] FROM [Database]

The results i get back are A, B & C. What i am trying to do is show a result as a different word.

i.e instead of displaying A is shows in drop down list as Apple, B shows Banana. etc.

Can anyone help please.

Thanks

Duncan:

It sounds to me like what you want to do is to join to a translation table to do a simple lookup; something like?

declare @.source table ( sourceKey varchar(8))
insert into @.source values ('A')
insert into @.source values ('B')
insert into @.source values ('C')

declare @.translate table ( sourceKey varchar(8), xlate varchar (10))
insert into @.translate values ('A', 'Apple')
insert into @.translate values ('B', 'Banana')
insert into @.translate values ('C', 'Cherry')

select xlate as Target
from @.source p
inner join @.translate q
on p.sourceKey = q.sourceKey

-- Target
-- -
-- Apple
-- Banana
-- Cherry

|||

The syntax of your query is not correct. Normally in SQL Server you would use the following:

SELECT [Columns] FROM [Table]

It is good practice to create a lookup table that contains a set of unique shorthand codes or IDs, such as 'A' and 'B' in your example, and an alphanumerical field to contain a description of the code/ID - 'Apple' or 'Banana' in your example. You would then join the fact table to the lookup table and return the appropriate columns from each table.

Could you post your table definitions and the query that you are using to return the data?

Chris

|||Thanks for that. I can see what you've done but the fields i need are carries in a field in a table. there can be only 3 results a b or c. there are alot of records. i don;t see how i can use this to select distinct from field in table but display as something else.|||I must agree with Chris; I don't know where I am going and I'm afraid that I need a roadmap.|||

Query

SELECT distinct [COLUMN] FROM [TABLE]

Returns

A
B
C

What i would like is for it to show

Apple
Banana
Cranberry

It is to populate a drop down list with SSRS. So that the users see what each entry means and not just ABC.

|||

In that case then the answers previously given to your original post will work, however if in the short term you want to make this really simple then you could use this:

SELECT 'Apple' AS [LookupValue], 'A' AS LookupCode UNION
SELECT 'Banana', 'B' UNION
SELECT 'Cranberry', 'C'
ORDER BY 1

The LookupValue is the value that you would display in your drop-down list, the LookupCode is the value that you feed back into the report's main query.

I wouldn't recommend the above approach as you'd need to modify the SELECT statement if you make changes to the reference data - you'd be far better off by using a lookup table as originally suggested, I've provided the example below to show how this can be done.

The choice is yours.

Chris

CREATE TABLE dbo.FruitType
(
FruitTypeCode CHAR(1) NOT NULL PRIMARY KEY,
FruitTypeValue VARCHAR(100) NOT NULL,
)
GO

--Ideally you would declare a Foreign Key constraint between the FruitTypeCode columns in your fact table and the following lookup table.
INSERT INTO dbo.FruitType(FruitTypeCode, FruitTypeValue)
SELECT 'A', 'Apple' UNION
SELECT 'B', 'Banana' UNION
SELECT 'C', 'Cranberry'
GO

--Use this to populate your combo - display FruitTypeValue and feed FruitTypeCode back into the report's main query.
SELECT FruitTypeCode, FruitTypeValue
FROM dbo.FruitType
ORDER BY FruitTypeValue
GO

|||

Ok got it working.

select distinct

case [COLUMN NAME]

when 'A' then 'Apple'

when 'B' then 'Banana'

else 'Unknown'end

from [TABLE NAME]

where [COLUMN NAME] in ('A', 'B', 'C')

change nc-index to c-index?

Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?

thanx,Is update executed against facts_id itself or against the table "where facts_id=<whatever>"? If it's the first, then you have a lot of page splits in your index pages, but rebuilding non-clustered index with so many rows is much less destructive than if you had a clustered index there.|||the updates are like:

update facts
set name = value
where facts_id = @.facts_id

so I can expect only few page splits (is there a way to count 'em?).
There's no update on the facts_id itself and it's unlikely it'll happen in the future which, I guess, makes the facts_id even more attractive for a clustered index.
I'll have to rebuild the other indexes as well though but I guess that's okay.

I saw the facts has a bunch of statistics, my dev does not have this nor do the other warehouses that are derived from prod. Could this be a problem? If I'd throw 'em out (delete 'em), would sql rebuild them by itself?|||It will if Auto Create Statistics is on. It's a database setting.