Anyone have a better alternative for constants?

SQL Server doesn't appear to have the concept of a global
or public constant. For instance, if LanguageID 1 equals
English or LanguageID 2 equals Spanish, you'd typically have
to hard code 1 or 2 in any procedure that checks this permission
first.
I've recently started using User-Defined Functions named
something like dbo.English or dbo.Spanish that do nothing
but return 1, 2, etc...
So far I haven't noticed any performance problems. Is
there a better alternative to this?
Robbe Morris - 2004/2005 Microsoft MVP C#
Earn money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.aspHave you tried asking this in the SQL-Server 2005 newsgroup?
Axel Dahmen
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> schrieb im Newsbeitrag
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||You can build a table of constants within a schema using Standard SQL
like this:
CREATE TABLE Constant
(lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
CHECK (lock = 'X'),
pi REAL DEFAULT 3.141592653 NOT NULL,
e REAL DEFAULT 2.718281828 NOT NULL,
phi REAL DEFAULT 1.618033988 NOT NULL,
.);
INSERT INTO Constants DEFAULT VALUES;
The insertion creates one row, so the table ought to have a singular
name. The "lock" column assures you that there is always only one row.
In full SQL-92, you can create such as table as a VIEW with a row
constructor:
CREATE VIEW Constant (pi, e, phi, ..)
AS VALUES (3.141592653, 2.718281828, 1.618033988, ..);|||I typically use a constants or properties table. If it's small and accessed
enough, it will always be in memory, and it's a heck of a lot easier to
change than a bunch of user-defined functions.
A
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||Actually this can be devastatng to performance if you use it in a FROM or
WHERE clause. It often gets called once per row, especially if you haven't
made sure that the function is deterministic: objectproperty(function_id,
'isDeterministic'). If you don't use them in a queries, It will be fine.
I would suggest you probably want to build small tables to do this sort of
thing and check the table values. Then you can use a function to get the
value if you just want one value, but you can also join to it for queries if
you need to check a value for each row.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:uhIlU2foFHA.3316@.tk2msftngp13.phx.gbl...
> SQL Server doesn't appear to have the concept of a global
> or public constant. For instance, if LanguageID 1 equals
> English or LanguageID 2 equals Spanish, you'd typically have
> to hard code 1 or 2 in any procedure that checks this permission
> first.
> I've recently started using User-Defined Functions named
> something like dbo.English or dbo.Spanish that do nothing
> but return 1, 2, etc...
> So far I haven't noticed any performance problems. Is
> there a better alternative to this?
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124159052.329343.274620@.g14g2000cwa.googlegroups.com...
> You can build a table of constants within a schema using Standard SQL
> like this:
> CREATE TABLE Constant
> (lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
> CHECK (lock = 'X'),
> pi REAL DEFAULT 3.141592653 NOT NULL,
> e REAL DEFAULT 2.718281828 NOT NULL,
> phi REAL DEFAULT 1.618033988 NOT NULL,
> ..);
> INSERT INTO Constants DEFAULT VALUES;
Constants = Constant|||Shock! Celko used a singular! Could it be the end of the...world? :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:OyLoV7noFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1124159052.329343.274620@.g14g2000cwa.googlegroups.com...
> Constants = Constant
>
>|||>> CREATE TABLE Constant
I don't like this approach because you have to alter the table when you have
a new constant.
The other approaches I like a little bit better:
(a) create a user-defined function (e.g. dbo.pi()) if it is going to be used
in a local variable but NOT in a where clause, e.g. where col <= dbo.pi()...
this is also useful if you have a set of constants that don't match in data
type.
(b) for a set of, e.g., NUMERIC constants:
CREATE TABLE dbo.NumericConstants
(
ConstantName VARCHAR(16) NOT NULL PRIMARY KEY,
Value NUMERIC(15,4)
)
This gives you great flexibility in that you can add and remove constraints
without affecting the table schema. But it ties you to a table per data
type.
(c) for a set of varying datatype constants, you can do this kind of kludge:
CREATE TABLE dbo.Constants
(
ConstantName VARCHAR(16) NOT NULL PRIMARY KEY,
NumericValue NUMERIC(15,4),
DateTimeValue DATETIME,
NVarcharValue NVARCHAR(255)
)
-- of course with a check constraint that only one can be NOT NULL
-- or add a datatype column
Or you can use SQL_VARIANT but this incurs all kinds of conversion costs
going in and out of the table.|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O$jo4QooFHA.3696@.TK2MSFTNGP10.phx.gbl...
> Shock! Celko used a singular! Could it be the end of the...world? :)
Singular was appropriate in this instance, since the table only contains one
(1) row. I simply pointed out the typo for the benefit of those who are
cutting-and-pasting. :)|||Actually this is a really interesting question and I didn't realize (you
were right :)
Strangely though, in this case the row contains multiple independent values,
but in one single row. So constants (in this case) would be the more
appropriate name, since what is represented is in fact is multiple
constants. Hmm.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:u3c9yqooFHA.3084@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:O$jo4QooFHA.3696@.TK2MSFTNGP10.phx.gbl...
> Singular was appropriate in this instance, since the table only contains
> one (1) row. I simply pointed out the typo for the benefit of those who
> are cutting-and-pasting. :)
>sql

Any ideas, please...

I have create a database, login, role, user at a named instance of MSDE by
running the following scropt:
CREATE DATABASE [testdb_vg]
USE Master
EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
USE [testdb_vg]
EXEC SP_ADDROLE [test_role]
GRANT CREATE TABLE TO [test_role]
EXEC SP_ADDUSER [test_login], [test_user], [test_role]
Then I run SQL Analyser and try to connect to the database using SQL
Authentication:
user: test_user
password: psw
and I am getting message:
Unable to connect to server SSSSSS\NNNN
Message: 18452, level 16, state 1,
[Microsoft][ODBC SQL Server Driver][SQL Server] Login for user "test_user".
Reason: not associated with a trusted SQL Server connection
with other SQL server instance it works OK?!Are you sure MSDE is set up to use both SQL Server and Windows
authentication? Sounds like it is set to Windows authentication only...
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||See the section that starts with "Another way to change the security mode
after installation..." in
http://support.microsoft.com/?kbid=285097
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:#QRC#M5eDHA.560@.tk2msftngp13.phx.gbl...
> I have create a database, login, role, user at a named instance of MSDE by
> running the following scropt:
> CREATE DATABASE [testdb_vg]
> USE Master
> EXEC SP_ADDLOGIN @.loginame = 'test_login', @.passwd = 'psw'
> USE [testdb_vg]
> EXEC SP_ADDROLE [test_role]
> GRANT CREATE TABLE TO [test_role]
> EXEC SP_ADDUSER [test_login], [test_user], [test_role]
> Then I run SQL Analyser and try to connect to the database using SQL
> Authentication:
> user: test_user
> password: psw
> and I am getting message:
> Unable to connect to server SSSSSS\NNNN
> Message: 18452, level 16, state 1,
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login for user
"test_user".
> Reason: not associated with a trusted SQL Server connection
> with other SQL server instance it works OK?!
>|||In my case MSDE was set up to use Windows authentication only...|||is there any SQL query way to recognize what the mode is for an instance of
SQL Server?

Any ideas ?

Unable to encrypt or decrypt data managed by the Report Server instance.
Please refer to the Reporting Services online help for guidance in enabling
this functionality.
...In help there is no help...If I understand you corrrectly, one way to do it would be to use a custom
assembly to encrtpy and decrypt data.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> wrote in message
news:O3XEwo9oEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Unable to encrypt or decrypt data managed by the Report Server instance.
> Please refer to the Reporting Services online help for guidance in
enabling
> this functionality.
> ...In help there is no help...
>

Any documentation to set up multi instance cluster

I want to set up 3 active and 1 passive on a 4 node cluster and would like
to see an install guide for something like that. Can anyone redirect me to
one ?
Thanks
Use the guide you found for the first instance, then follow the books online
to install the rest of the instances.
Remember each instance will need an IP and dedicated disk(s).
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Hassan" <hassan@.hotmail.com> wrote in message
news:eVo9tfnRHHA.3948@.TK2MSFTNGP05.phx.gbl...
>I want to set up 3 active and 1 passive on a 4 node cluster and would like
>to see an install guide for something like that. Can anyone redirect me to
>one ?
> Thanks
>
|||Are you having trouble setting up the cluster or the SQL Server instances?
If you are running N + I, then when you install SQL Server, you only include
two nodes as potential resource owners.
If we designate nodes 1, 2, and 3 as the active nodes and 4 as the passive,
then for instance 1 you would include nodes 1 and 4, for instance 2, nodes 2
and 4, and for instance 3, nodes 3 and 4.
Other than this, the setup for SQL Server is exactly the same as for a
single instanced, 2-node cluster.
Perhaps I am not understanding your question fully.
Here are two references, one for multi-node clusters, and one for SQL Server
failover clustering (the document is for SS2K but applicable for SS2K5; it
also provides a link to SS2K5 related information).
See the Number of Nodes and Failover Plan section:
http://technet2.microsoft.com/WindowsServer/en/library/f41adff5-87df-483e-9edd-8484501d79921033.mspx?mfr=true
SQL Server:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en
Sincerely,
Anthony Thomas
"Hassan" <hassan@.hotmail.com> wrote in message
news:eVo9tfnRHHA.3948@.TK2MSFTNGP05.phx.gbl...
> I want to set up 3 active and 1 passive on a 4 node cluster and would like
> to see an install guide for something like that. Can anyone redirect me to
> one ?
> Thanks
>