Hi all,
Where does SQL 2000 store all check-constraint configurations? i.e. I have a column in a table which I would like to limit the value to be > 10. Would like to retrieve the info via a client program.
Thanks
regards,
tswernall the constrainsts are saved in the table sysconstratints|||looked into sysconstraints table. seems like it does not contain any rules I specified for my check contraint column. Only have a number of ids. Any idea which tables these ids tie to the actual check constraint configuration?|||you might find this useful|||Thanks paul! Indeed it is very usefull.|||Thanks paul! Indeed it is very usefull.
Showing posts with label limit. Show all posts
Showing posts with label limit. Show all posts
Thursday, March 8, 2012
Check constraint
I am new to SQL Server, so I'm not quite sure how to use this feature. I need to limit a field to only accept a set of three values: H, Q, and C. I know how to do this in a native Access table. I'm not quite sure how to do this in SQL Server. I assum
e this is what a check constraint is, but I don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
Yep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
|||Try this -
create table test (
iintidentity
, jchar(1)
check (j in ('H', 'Q', 'C'))
)
|||Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check constraint?
Crystal
|||The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
e this is what a check constraint is, but I don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
Yep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
|||Try this -
create table test (
iintidentity
, jchar(1)
check (j in ('H', 'Q', 'C'))
)
|||Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check constraint?
Crystal
|||The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
Check constraint
I am new to SQL Server, so I'm not quite sure how to use this feature. I need to limit a field to only accept a set of three values: H, Q, and C. I know how to do this in a native Access table. I'm not quite sure how to do this in SQL Server. I assume this is what a check constraint is, but I don't know how to set one up or where to implement it
Any suggestions would be greatly appreciated
CrystalThis is a multi-part message in MIME format.
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Yep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yep, it's a check =constraint:
create table =MyTable
(
PK int primary =key
, =MyCol char (1) not null constraint CK_MyTable (MyCol in =('H', 'Q', 'C'))
)
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Crystal" wrote in message news:BC3=80E12-BD25-4308-8860-08617207DEE4@.microsoft.com...I am new to SQL Server, so I'm not quite sure how to use this =feature. I need to limit a field to only accept a set of three values: H, Q, =and C. I know how to do this in a native Access table. I'm not =quite sure how to do this in SQL Server. I assume this is what a check constraint is, but I don't know how to set one up or where to implement it.Any suggestions would be greatly appreciated,Crystal
--=_NextPart_000_012E_01C42099.51AAF520--|||Ok. This is what is going to make me look like a dork. .
Now that I have the syntax. . . where do I put it to create the check constraint
Crystal|||This is a multi-part message in MIME format.
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The syntax I showed you is to create =the constraint at the time you create the table. However, if the table =already exists, you can do an ALTER TABLE:
alter table =MyTable
add
=constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Crystal" wrote in message news:CFC=4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...Ok. This is what is going to make me look like a dork. . .Now that I =have the syntax. . . where do I put it to create the check constraint?Crystal
--=_NextPart_000_0086_01C42136.B95D8140--
Any suggestions would be greatly appreciated
CrystalThis is a multi-part message in MIME format.
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Yep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yep, it's a check =constraint:
create table =MyTable
(
PK int primary =key
, =MyCol char (1) not null constraint CK_MyTable (MyCol in =('H', 'Q', 'C'))
)
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Crystal" wrote in message news:BC3=80E12-BD25-4308-8860-08617207DEE4@.microsoft.com...I am new to SQL Server, so I'm not quite sure how to use this =feature. I need to limit a field to only accept a set of three values: H, Q, =and C. I know how to do this in a native Access table. I'm not =quite sure how to do this in SQL Server. I assume this is what a check constraint is, but I don't know how to set one up or where to implement it.Any suggestions would be greatly appreciated,Crystal
--=_NextPart_000_012E_01C42099.51AAF520--|||Ok. This is what is going to make me look like a dork. .
Now that I have the syntax. . . where do I put it to create the check constraint
Crystal|||This is a multi-part message in MIME format.
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The syntax I showed you is to create =the constraint at the time you create the table. However, if the table =already exists, you can do an ALTER TABLE:
alter table =MyTable
add
=constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Crystal" wrote in message news:CFC=4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...Ok. This is what is going to make me look like a dork. . .Now that I =have the syntax. . . where do I put it to create the check constraint?Crystal
--=_NextPart_000_0086_01C42136.B95D8140--
Check constraint
I am new to SQL Server, so I'm not quite sure how to use this feature. I ne
ed to limit a field to only accept a set of three values: H, Q, and C. I k
now how to do this in a native Access table. I'm not quite sure how to do t
his in SQL Server. I assum
e this is what a check constraint is, but I don't know how to set one up or
where to implement it.
Any suggestions would be greatly appreciated,
CrystalYep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal|||Try this -
create table test (
i int identity
, j char(1)
check (j in ('H', 'Q', 'C'))
)|||Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check constr
aint?
Crystal|||The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
ed to limit a field to only accept a set of three values: H, Q, and C. I k
now how to do this in a native Access table. I'm not quite sure how to do t
his in SQL Server. I assum
e this is what a check constraint is, but I don't know how to set one up or
where to implement it.
Any suggestions would be greatly appreciated,
CrystalYep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal|||Try this -
create table test (
i int identity
, j char(1)
check (j in ('H', 'Q', 'C'))
)|||Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check constr
aint?
Crystal|||The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
Wednesday, March 7, 2012
check & limit number users
Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software
check & limit number users
Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software
check & limit number users
Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.
js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com
|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com
|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.
js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com
|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com
|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software
check & limit number users
Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
--
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
>> Hi,
>> Can I check and limit number occurrence users connect to sql server?
>> Please advice.
>> Thanks.
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
--
David Gugick
Quest Software
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
--
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
>> Hi,
>> Can I check and limit number occurrence users connect to sql server?
>> Please advice.
>> Thanks.
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
--
David Gugick
Quest Software
Cheapest Solution.
We are a nonprofit organization needing an MSSQL server
able to pass the silly 2 gb limit. What is the cheapest
solution? The only computer which needs to access the
server is that running it.I am the IS Manager for a non-profit also. We were in the same boat, and the
way I got through this was to go to a site called http://www.techsoup.com.
At this site you can register your agency using your 501c3 and Charitable
Status number, then you can purchase software, including MS items, for VERY
cheap rates. I got SBS2k, 45 licenses, and Visio for $98.00. Only catch is
that you can only do it once every 2 years, so have a good plan of what you
need when you do it. Let me know if I can be of any help. Been in the NP
world for awhile and it does offer challenges.
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||The cheapest option is to purchase Enterprise Edition using the Server + CAL
model with very few CALs.
I don't know why you consider the 2GB limit "silly". It cost Microsoft
millions of dollars to develop, test, tune, and support the wierd mechanisms
for getting around the x86 architectural limitations that lead to a 2GB user
virtual address space. It makes sense for them to charge for all the extra
work that benefits only a modest portion of the installed base. Its true
that in a few more years, as Itanium and/or AMD-64 dominate the server
space, that the 2GB limit will become arbitrary. And at that point it will
also make sense for Microsoft to change its packaging/licensing.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||Why, specifically, do you need to surpass to 2GB limitiation, are your
queries running out of memory?
<op ed>
"> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm...."
sounds like someone coming from the public sector.
</op ed>
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||Brian
When I read this, I thought the 2GB limit you were referring to was the
database size restriction imposed by MSDE, but other people's posts seemed
to imply that you were talking about the 2GB memory limitation imposed in
all editions except for SQL Server Enterprise Edition running on Windows
2000.
Can you clarify which 'silly 2GB limit' you are concerned about?
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||Could you tell Kevin? LOL
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:%23gLLmkdXDHA.208@.tk2msftngp13.phx.gbl...
> Why, specifically, do you need to surpass to 2GB limitiation, are your
> queries running out of memory?
> <op ed>
> "> ...cost them millions of dollars to develop something that makes them
> > Billions of dollars. Hmmmm...."
> sounds like someone coming from the public sector.
> </op ed>
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
> news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> > Sounds like someone is paid by Microsoft
> >
> > ...cost them millions of dollars to develop something that makes them
> > Billions of dollars. Hmmmm....
> >
> >
> > "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> > news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > The cheapest option is to purchase Enterprise Edition using the Server
+
> > CAL
> > > model with very few CALs.
> > >
> > > I don't know why you consider the 2GB limit "silly". It cost
Microsoft
> > > millions of dollars to develop, test, tune, and support the wierd
> > mechanisms
> > > for getting around the x86 architectural limitations that lead to a
2GB
> > user
> > > virtual address space. It makes sense for them to charge for all the
> > extra
> > > work that benefits only a modest portion of the installed base. Its
> true
> > > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > > space, that the 2GB limit will become arbitrary. And at that point it
> > will
> > > also make sense for Microsoft to change its packaging/licensing.
> > >
> > > --
> > > Hal Berenson, SQL Server MVP
> > > True Mountain Group LLC
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>|||If you actually saw how many resources they staff to develop and maintain
the sql server product line you would understand that it costs them far more
than a few million dollars to get you this product. And at this point in
time I doubt if sql server is even turning a profit but I could be wrong.
--
Andrew J. Kelly
SQL Server MVP
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||hal started it
;)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> Brian
> When I read this, I thought the 2GB limit you were referring to was the
> database size restriction imposed by MSDE, but other people's posts seemed
> to imply that you were talking about the 2GB memory limitation imposed in
> all editions except for SQL Server Enterprise Edition running on Windows
> 2000.
> Can you clarify which 'silly 2GB limit' you are concerned about?
> Thanks
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Brian Cody" <bjc9019@.rit.edu> wrote in message
> news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > We are a nonprofit organization needing an MSSQL server
> > able to pass the silly 2 gb limit. What is the cheapest
> > solution? The only computer which needs to access the
> > server is that running it.
>|||Started what? We still don't know what the original poster was asking about.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> hal started it
> ;)
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > Brian
> >
> > When I read this, I thought the 2GB limit you were referring to was the
> > database size restriction imposed by MSDE, but other people's posts
seemed
> > to imply that you were talking about the 2GB memory limitation imposed
in
> > all editions except for SQL Server Enterprise Edition running on Windows
> > 2000.
> >
> > Can you clarify which 'silly 2GB limit' you are concerned about?
> >
> > Thanks
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||started us on the tangent regarding memory instead of database size :)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> Started what? We still don't know what the original poster was asking
about.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > hal started it
> >
> > ;)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > Brian
> > >
> > > When I read this, I thought the 2GB limit you were referring to was
the
> > > database size restriction imposed by MSDE, but other people's posts
> seemed
> > > to imply that you were talking about the 2GB memory limitation imposed
> in
> > > all editions except for SQL Server Enterprise Edition running on
Windows
> > > 2000.
> > >
> > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > >
> > > Thanks
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>|||Although I was once employed by Microsoft (and don't try to hide it...Google
searches reveal all) I am neither a current Microsoft employee nor should
anything I say be taken as representing Microsoft in any way.
The SQL Server product may make a billion dollars a year, but that's not the
question. The question is, how much incremental revenue does the millions
of dollars spent on this one feature generate?
I know your thought is a common one for people, but what Microsoft is doing
here is just good common business sense. Increased investment in any
product has to be justified by a return on that investment (ie, greater
revenue of sufficient size to generate greater profit). To generate the
revenue you either increase unit sales (volume), or you increase the value
you are offering and charge accordingly. One solution would be to only
invest your development resources on projects that you expect to generate
increased unit volume. In that case, features such as AWE support, failover
clusters, materialized views, support for more than 4 processors, etc. just
would never have been done. The resources would instead have been assigned
to things like adding VB stored procedures, more Access compatibility,
smaller footprint for downloads, etc. All good things, by the way. You
could invest in the features that don't offer a unit volume increase, put
them in the base product, and then raise the price for the base product.
But that penalizes all the people who don't need the additional features,
which means it penalizes most people, and could actually lower unit volume
resulting in an overall reduction in revenue. The third solution is to
create a premium product that is designed so that those who need the low
volume features are the ones who pay for them. Microsoft went with solution
#3. I guess there is a fourth solution, which is to increase the investment
but not increase revenue accordingly. This is called the "going out of
business" option. Many companies, particularly database companies, have
successfully executed on option #4.
The features in Enterprise Edition are generally those which are extremely
expensive to develop yet lead to a negligable increase in unit sales. This
is the case because those features primarily apply to low-volume
environments like high-end servers purchased, installed and maintained by
Enterprise IT organizations. Occasionally someone wants just one of the
features and doesn't really need the rest of Enterprise Edition, and then
the packaging doesn't seem to make sense. Well, for them I guess it
doesn't. No solution is going to make everyone happy. Any business tries
to maximize the applicability of their offerings to customers without
letting their costs get out of control. I hope that Microsoft has done this
with SQL Server, though certainly its packaging is not perfect for everyone.
One other interesting point. At the time that Microsoft made >2GB an
Enterprise Edition feature 1GB of memory was going for $100K or more and
thus clearly 3GB was a very low volume situation. Today 1GB goes for a few
hundred dollars and 3GB servers are mainstream. Hopefully Microsoft will
take this into account the next time it changes SQL Server packaging.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||But are we sure that is a tangent?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uhfZwpeXDHA.2568@.tk2msftngp13.phx.gbl...
> started us on the tangent regarding memory instead of database size :)
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> > Started what? We still don't know what the original poster was asking
> about.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > > hal started it
> > >
> > > ;)
> > >
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > > Brian
> > > >
> > > > When I read this, I thought the 2GB limit you were referring to was
> the
> > > > database size restriction imposed by MSDE, but other people's posts
> > seemed
> > > > to imply that you were talking about the 2GB memory limitation
imposed
> > in
> > > > all editions except for SQL Server Enterprise Edition running on
> Windows
> > > > 2000.
> > > >
> > > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > > >
> > > > Thanks
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > > We are a nonprofit organization needing an MSSQL server
> > > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > > solution? The only computer which needs to access the
> > > > > server is that running it.
> > > >
> > > >
> > >
> > >
> >
> >
>|||No, not yet. But I think your instinct was correct, let's see if Brian
responds.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uEdMlteXDHA.2392@.TK2MSFTNGP10.phx.gbl...
> But are we sure that is a tangent?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:uhfZwpeXDHA.2568@.tk2msftngp13.phx.gbl...
> > started us on the tangent regarding memory instead of database size :)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> > > Started what? We still don't know what the original poster was asking
> > about.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > > > hal started it
> > > >
> > > > ;)
> > > >
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > > > Brian
> > > > >
> > > > > When I read this, I thought the 2GB limit you were referring to
was
> > the
> > > > > database size restriction imposed by MSDE, but other people's
posts
> > > seemed
> > > > > to imply that you were talking about the 2GB memory limitation
> imposed
> > > in
> > > > > all editions except for SQL Server Enterprise Edition running on
> > Windows
> > > > > 2000.
> > > > >
> > > > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > > > >
> > > > > Thanks
> > > > >
> > > > > --
> > > > > HTH
> > > > > --
> > > > > Kalen Delaney
> > > > > SQL Server MVP
> > > > > www.SolidQualityLearning.com
> > > > >
> > > > >
> > > > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > > > We are a nonprofit organization needing an MSSQL server
> > > > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > > > solution? The only computer which needs to access the
> > > > > > server is that running it.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kalen's right, he could have been talking about the 2GB database limit in
MSDE. But since he didn't mention MSDE, and this isn't the MSDE newsgroup,
I went with the memory limit as what he was asking about.
I happen to agree that the 2GB database limit in MSDE is silly. But getting
around that one is much easier and cheaper. Either split your data over
multiple databases in a single MSDE instance, or (in this case) spend under
a $1000 (even if you aren't a non-profit) to get Standard Edition + 5 CALs.
ATTENTION 501(c)(3)s: The cheapest way for you to get Microsoft software is
to find a Microsoft employee who donates to you and ask them to donate
software instead of cash. The employee can purchase the software at the
employee store at a dramatic discount, and donation to a 501(c)(3) is one of
the things they are allowed to do with that software. Microsoft will even
match the donation with additional software. I know charities whose entire
offices have been outfitted this way. So get your fundraising people moving
:-)
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> Started what? We still don't know what the original poster was asking
about.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > hal started it
> >
> > ;)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > Brian
> > >
> > > When I read this, I thought the 2GB limit you were referring to was
the
> > > database size restriction imposed by MSDE, but other people's posts
> seemed
> > > to imply that you were talking about the 2GB memory limitation imposed
> in
> > > all editions except for SQL Server Enterprise Edition running on
Windows
> > > 2000.
> > >
> > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > >
> > > Thanks
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>
able to pass the silly 2 gb limit. What is the cheapest
solution? The only computer which needs to access the
server is that running it.I am the IS Manager for a non-profit also. We were in the same boat, and the
way I got through this was to go to a site called http://www.techsoup.com.
At this site you can register your agency using your 501c3 and Charitable
Status number, then you can purchase software, including MS items, for VERY
cheap rates. I got SBS2k, 45 licenses, and Visio for $98.00. Only catch is
that you can only do it once every 2 years, so have a good plan of what you
need when you do it. Let me know if I can be of any help. Been in the NP
world for awhile and it does offer challenges.
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||The cheapest option is to purchase Enterprise Edition using the Server + CAL
model with very few CALs.
I don't know why you consider the 2GB limit "silly". It cost Microsoft
millions of dollars to develop, test, tune, and support the wierd mechanisms
for getting around the x86 architectural limitations that lead to a 2GB user
virtual address space. It makes sense for them to charge for all the extra
work that benefits only a modest portion of the installed base. Its true
that in a few more years, as Itanium and/or AMD-64 dominate the server
space, that the 2GB limit will become arbitrary. And at that point it will
also make sense for Microsoft to change its packaging/licensing.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||Why, specifically, do you need to surpass to 2GB limitiation, are your
queries running out of memory?
<op ed>
"> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm...."
sounds like someone coming from the public sector.
</op ed>
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||Brian
When I read this, I thought the 2GB limit you were referring to was the
database size restriction imposed by MSDE, but other people's posts seemed
to imply that you were talking about the 2GB memory limitation imposed in
all editions except for SQL Server Enterprise Edition running on Windows
2000.
Can you clarify which 'silly 2GB limit' you are concerned about?
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||Could you tell Kevin? LOL
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:%23gLLmkdXDHA.208@.tk2msftngp13.phx.gbl...
> Why, specifically, do you need to surpass to 2GB limitiation, are your
> queries running out of memory?
> <op ed>
> "> ...cost them millions of dollars to develop something that makes them
> > Billions of dollars. Hmmmm...."
> sounds like someone coming from the public sector.
> </op ed>
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
> news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> > Sounds like someone is paid by Microsoft
> >
> > ...cost them millions of dollars to develop something that makes them
> > Billions of dollars. Hmmmm....
> >
> >
> > "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> > news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > The cheapest option is to purchase Enterprise Edition using the Server
+
> > CAL
> > > model with very few CALs.
> > >
> > > I don't know why you consider the 2GB limit "silly". It cost
Microsoft
> > > millions of dollars to develop, test, tune, and support the wierd
> > mechanisms
> > > for getting around the x86 architectural limitations that lead to a
2GB
> > user
> > > virtual address space. It makes sense for them to charge for all the
> > extra
> > > work that benefits only a modest portion of the installed base. Its
> true
> > > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > > space, that the 2GB limit will become arbitrary. And at that point it
> > will
> > > also make sense for Microsoft to change its packaging/licensing.
> > >
> > > --
> > > Hal Berenson, SQL Server MVP
> > > True Mountain Group LLC
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>|||If you actually saw how many resources they staff to develop and maintain
the sql server product line you would understand that it costs them far more
than a few million dollars to get you this product. And at this point in
time I doubt if sql server is even turning a profit but I could be wrong.
--
Andrew J. Kelly
SQL Server MVP
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||hal started it
;)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> Brian
> When I read this, I thought the 2GB limit you were referring to was the
> database size restriction imposed by MSDE, but other people's posts seemed
> to imply that you were talking about the 2GB memory limitation imposed in
> all editions except for SQL Server Enterprise Edition running on Windows
> 2000.
> Can you clarify which 'silly 2GB limit' you are concerned about?
> Thanks
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Brian Cody" <bjc9019@.rit.edu> wrote in message
> news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > We are a nonprofit organization needing an MSSQL server
> > able to pass the silly 2 gb limit. What is the cheapest
> > solution? The only computer which needs to access the
> > server is that running it.
>|||Started what? We still don't know what the original poster was asking about.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> hal started it
> ;)
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > Brian
> >
> > When I read this, I thought the 2GB limit you were referring to was the
> > database size restriction imposed by MSDE, but other people's posts
seemed
> > to imply that you were talking about the 2GB memory limitation imposed
in
> > all editions except for SQL Server Enterprise Edition running on Windows
> > 2000.
> >
> > Can you clarify which 'silly 2GB limit' you are concerned about?
> >
> > Thanks
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||started us on the tangent regarding memory instead of database size :)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> Started what? We still don't know what the original poster was asking
about.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > hal started it
> >
> > ;)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > Brian
> > >
> > > When I read this, I thought the 2GB limit you were referring to was
the
> > > database size restriction imposed by MSDE, but other people's posts
> seemed
> > > to imply that you were talking about the 2GB memory limitation imposed
> in
> > > all editions except for SQL Server Enterprise Edition running on
Windows
> > > 2000.
> > >
> > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > >
> > > Thanks
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>|||Although I was once employed by Microsoft (and don't try to hide it...Google
searches reveal all) I am neither a current Microsoft employee nor should
anything I say be taken as representing Microsoft in any way.
The SQL Server product may make a billion dollars a year, but that's not the
question. The question is, how much incremental revenue does the millions
of dollars spent on this one feature generate?
I know your thought is a common one for people, but what Microsoft is doing
here is just good common business sense. Increased investment in any
product has to be justified by a return on that investment (ie, greater
revenue of sufficient size to generate greater profit). To generate the
revenue you either increase unit sales (volume), or you increase the value
you are offering and charge accordingly. One solution would be to only
invest your development resources on projects that you expect to generate
increased unit volume. In that case, features such as AWE support, failover
clusters, materialized views, support for more than 4 processors, etc. just
would never have been done. The resources would instead have been assigned
to things like adding VB stored procedures, more Access compatibility,
smaller footprint for downloads, etc. All good things, by the way. You
could invest in the features that don't offer a unit volume increase, put
them in the base product, and then raise the price for the base product.
But that penalizes all the people who don't need the additional features,
which means it penalizes most people, and could actually lower unit volume
resulting in an overall reduction in revenue. The third solution is to
create a premium product that is designed so that those who need the low
volume features are the ones who pay for them. Microsoft went with solution
#3. I guess there is a fourth solution, which is to increase the investment
but not increase revenue accordingly. This is called the "going out of
business" option. Many companies, particularly database companies, have
successfully executed on option #4.
The features in Enterprise Edition are generally those which are extremely
expensive to develop yet lead to a negligable increase in unit sales. This
is the case because those features primarily apply to low-volume
environments like high-end servers purchased, installed and maintained by
Enterprise IT organizations. Occasionally someone wants just one of the
features and doesn't really need the rest of Enterprise Edition, and then
the packaging doesn't seem to make sense. Well, for them I guess it
doesn't. No solution is going to make everyone happy. Any business tries
to maximize the applicability of their offerings to customers without
letting their costs get out of control. I hope that Microsoft has done this
with SQL Server, though certainly its packaging is not perfect for everyone.
One other interesting point. At the time that Microsoft made >2GB an
Enterprise Edition feature 1GB of memory was going for $100K or more and
thus clearly 3GB was a very low volume situation. Today 1GB goes for a few
hundred dollars and 3GB servers are mainstream. Hopefully Microsoft will
take this into account the next time it changes SQL Server packaging.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||But are we sure that is a tangent?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uhfZwpeXDHA.2568@.tk2msftngp13.phx.gbl...
> started us on the tangent regarding memory instead of database size :)
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> > Started what? We still don't know what the original poster was asking
> about.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > > hal started it
> > >
> > > ;)
> > >
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > > Brian
> > > >
> > > > When I read this, I thought the 2GB limit you were referring to was
> the
> > > > database size restriction imposed by MSDE, but other people's posts
> > seemed
> > > > to imply that you were talking about the 2GB memory limitation
imposed
> > in
> > > > all editions except for SQL Server Enterprise Edition running on
> Windows
> > > > 2000.
> > > >
> > > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > > >
> > > > Thanks
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > > We are a nonprofit organization needing an MSSQL server
> > > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > > solution? The only computer which needs to access the
> > > > > server is that running it.
> > > >
> > > >
> > >
> > >
> >
> >
>|||No, not yet. But I think your instinct was correct, let's see if Brian
responds.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uEdMlteXDHA.2392@.TK2MSFTNGP10.phx.gbl...
> But are we sure that is a tangent?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:uhfZwpeXDHA.2568@.tk2msftngp13.phx.gbl...
> > started us on the tangent regarding memory instead of database size :)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> > > Started what? We still don't know what the original poster was asking
> > about.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > > > hal started it
> > > >
> > > > ;)
> > > >
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > > > Brian
> > > > >
> > > > > When I read this, I thought the 2GB limit you were referring to
was
> > the
> > > > > database size restriction imposed by MSDE, but other people's
posts
> > > seemed
> > > > > to imply that you were talking about the 2GB memory limitation
> imposed
> > > in
> > > > > all editions except for SQL Server Enterprise Edition running on
> > Windows
> > > > > 2000.
> > > > >
> > > > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > > > >
> > > > > Thanks
> > > > >
> > > > > --
> > > > > HTH
> > > > > --
> > > > > Kalen Delaney
> > > > > SQL Server MVP
> > > > > www.SolidQualityLearning.com
> > > > >
> > > > >
> > > > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > > > We are a nonprofit organization needing an MSSQL server
> > > > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > > > solution? The only computer which needs to access the
> > > > > > server is that running it.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kalen's right, he could have been talking about the 2GB database limit in
MSDE. But since he didn't mention MSDE, and this isn't the MSDE newsgroup,
I went with the memory limit as what he was asking about.
I happen to agree that the 2GB database limit in MSDE is silly. But getting
around that one is much easier and cheaper. Either split your data over
multiple databases in a single MSDE instance, or (in this case) spend under
a $1000 (even if you aren't a non-profit) to get Standard Edition + 5 CALs.
ATTENTION 501(c)(3)s: The cheapest way for you to get Microsoft software is
to find a Microsoft employee who donates to you and ask them to donate
software instead of cash. The employee can purchase the software at the
employee store at a dramatic discount, and donation to a 501(c)(3) is one of
the things they are allowed to do with that software. Microsoft will even
match the donation with additional software. I know charities whose entire
offices have been outfitted this way. So get your fundraising people moving
:-)
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> Started what? We still don't know what the original poster was asking
about.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > hal started it
> >
> > ;)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > Brian
> > >
> > > When I read this, I thought the 2GB limit you were referring to was
the
> > > database size restriction imposed by MSDE, but other people's posts
> seemed
> > > to imply that you were talking about the 2GB memory limitation imposed
> in
> > > all editions except for SQL Server Enterprise Edition running on
Windows
> > > 2000.
> > >
> > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > >
> > > Thanks
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>
Saturday, February 25, 2012
Charting problem
Hi all. I have plotted a chart based on the data obtained. However, is there a way to limit the number of bars to be displayed in the chart?
Lets say, I have data from Jan to Dec, i needed all the data because i need the accumulated figures from each month.
But, I do not wish to display in the bar chart for data from Jan to Dec.
There are section in the detail for hiding some data, but I dont know about chart.
Any comments are appreciated!Well, give us an example - what period do you wish to chart on, if it's not Jan-Dec??|||Lets say the data needed is from Jan to Dec, because I need to calculate the running total for the accumulated figures for other processing purposes.
Then for the chart, I would like to plot the data from Sept to Dec only. Is it possible?
The date field is used as a criteria for grouping.
Lets say, I have data from Jan to Dec, i needed all the data because i need the accumulated figures from each month.
But, I do not wish to display in the bar chart for data from Jan to Dec.
There are section in the detail for hiding some data, but I dont know about chart.
Any comments are appreciated!Well, give us an example - what period do you wish to chart on, if it's not Jan-Dec??|||Lets say the data needed is from Jan to Dec, because I need to calculate the running total for the accumulated figures for other processing purposes.
Then for the chart, I would like to plot the data from Sept to Dec only. Is it possible?
The date field is used as a criteria for grouping.
Tuesday, February 14, 2012
Character limit on Data tab?
Does anyone know if there is a character limit on the data tab of SRS 2000? I have a report that requires a lottt of sql and when I copy and paste the sql from Query Analyzer to the data tab, not all of it gets copied there, and it will not let me enter another character.
Thanks,
Jeff
It may make more sense to encapsulate in a stored procedure if possible. It does sound like you have a limitation there.|||Yah...good thought...thanks.Character limit for variables inside a stocked procedure
I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)
I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.
I am looking for a "How to" to put this limit to 8000.
Thank you!
Try Varchar(MAX) . It should help you.|||
Hi , see this link
http://www.sqlmag.com/Articles/ArticleID/26654/pg/2/2.html
|||How you insert data to your field? Are you use stored procedure or any type of parameter? check the size of your parameter if it is not limited to 1000 chars, I never had problems with varchar(8000) like you so check the way how you insert value to your cell.
Thanks
|||The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.
Thus, even if I do :
DECLARE @.SQL varchar(8000)
The @.SQL will not hold more than 1000 characters. And I need to fix that and cannot seem to find were to do so. 1000 character is fine for quite simple task, but we had some stocked procedure that would have required over 10K characters in order to do what we wanted to do.
If you have any idea on how to change the limitation on the number of character a variable within a stored procedure can hold, I am looking for it since it is quite limitating.
|||
Veritek:
The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.
I believe you are mistaken.
Try this from Query Analyzer:
DECLARE @.test varchar(2000)
SELECT @.test = REPLICATE('1',1000) + REPLICATE('2',700)
PRINT LEN(@.test)
PRINT @.test
You will see that the length returned is 1700. And that the string printed contains both 1's and 2's.
Your problem lies elsewhere. Something else is truncating your data at 1000 characters.
|||Affirmative, length is indeed 1700... But then I do not know where I could look ...|||
Affirmative, length is indeed 1700... But then I do not know where I could look ...
Well, either do we since we haven't seen any code...
jpazgier has suggested that you review your parameters to make sure you are not truncating data before it gets to your stored procedure.
|||
Well, still unresolved, and wont be anytime soon now since I would seem to have a new problem with the server.
Since the stocked procedure is receiving data from an aspx/vb set of files. Even when the .vb is of size 0.
Reinstalling the softwares seem in order now...
Thank you tho for the help!
Well, we actually had to disable the SP causing the problem since during the weekend it simply stoped working and kept returning an error which we fail to see where it comes from.
I think we need to upgrade our software :p
|||
Thus, even if I do :
DECLARE @.SQL varchar(8000)
The @.SQL will not hold more than 1000 characters.
How are you determining that @.SQL will not hold more than 1000 characters? Again, you've really not shown us any of your code so it's difficult for us to help. I strongly doubt that reinstalling software is the answer.
Character count limit?
Does anyone know what the character count limit is for a SQL table? We have a table with a column called body_text. It contains characters from a story we publish every day. Example:
News from Canada and Turkey
We get our weekly news update fromour Canadian correspondent, Vaughn Palmer. Plus, the Pope is in Turkey.
The visit is provoking opposition from secular nationalists and
Islamists. We talk to a reporter in Istanbul. Also, our weekly
listener's forum.
I did a word count in MS Word. This paragraph is 275 characters long. I found this in the only SQL book I currently have called Beginning SQL Server 2005. I found a snippet I thought might be related to my question.
Char
The char data type is fixed in length. If you define a
column to be 20 characters long, then 20 characters will be stored. If you
enter less then the number of characters defined, the remaining length will be
space filled to the right. Therefore, if a column were defined as char (10),
"aaa" would be stored as "aaa
". Use the data type when the column data is to be of fixed length,
which tends to be the case for customer IDs and bank account IDs.
So what is the official answer?
Assuming that your actual question is:
"Does anyone know what the character count limit is for a SQL table?"
In Sql Server 2k the limit for in-row-data is 8060. if you want to store more than that you will have to use a data type which is stored off-row like text.
In SQL 2k the limit for in row-data is also 8060, but you can take advantage of tnew data types which extend the VARCHAR /NVARCHAR types to a max of 4GB/2GB.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Sunday, February 12, 2012
char vs varchar - reclaiming free space
One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan Matic
Hi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>
|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan Matic
Hi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>
|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy
char vs varchar - reclaiming free space
One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using
:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using
:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy
char vs varchar - reclaiming free space
One of our customers is using MSDE2000 and has reached 2 gb limit. After
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy
erasing some old data and shrinking database he still had 1938 mb
allocated. After that, I have noticed that some of the larger tables
(around 1,5 - 2 million rows spread across a few tables) had column
defined as char(256) and char(1280), and a lot of fields were just space
filled or filled with around 40-100 chars only. I have changed those
columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
alter table sometable alter column somefield varchar(256) not null
update sometable set somefield = rtrim(somefield)
and after another database shrink it seems that it hasn't reclaimed any
space - what's even worse - the database seems to have grown to 2100 mb.
I expected to gain at least 200 mb, but it didn't happen. Is there a way
to reclaim that space?
Tnx in advance
Dragan MaticHi
Check if ANSI_PADDING is ON or OFF.
From BOL:
When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.
You LOG file may have also grown and it may require shrinking see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"DRagan Matic" wrote:
> One of our customers is using MSDE2000 and has reached 2 gb limit. After
> erasing some old data and shrinking database he still had 1938 mb
> allocated. After that, I have noticed that some of the larger tables
> (around 1,5 - 2 million rows spread across a few tables) had column
> defined as char(256) and char(1280), and a lot of fields were just space
> filled or filled with around 40-100 chars only. I have changed those
> columns to varchar(256) and varchar(1280) and rtrimmed those columns using:
> alter table sometable alter column somefield varchar(256) not null
> update sometable set somefield = rtrim(somefield)
> and after another database shrink it seems that it hasn't reclaimed any
> space - what's even worse - the database seems to have grown to 2100 mb.
> I expected to gain at least 200 mb, but it didn't happen. Is there a way
> to reclaim that space?
> Tnx in advance
> Dragan Matic
>|||Also be sure to run DBCC UPDATEUSAGE(0)
Roy
Friday, February 10, 2012
changing Top lower margin
I been looking for this for quite a while but have not been able to find anything.
Is there a command like MySQL Limit command, or a way to mimic that behavior in SQL?
For those that don’t know the command it is similar to Top but allows you to say what is the first record you want to receive and how many records from there. For instance given the following example recordset:
A
B
C
D
E
F
The following query would return (note Limit low margin is zero based):
Select * From [Table] Limit 2, 3
C
D
E
So far I have figured that I may be able to create a temporary table with an identity field that would allow me to do a where against, but I’m not sure if that would be the most effective way to mimic this behavior, and again, there may be a built in way to do it that I’m not aware off.
AFAIK, there is no built in command in SQL for something like that. You'd have to write a query with a TOP 3 from the table where ID > ( get the TOP 2nd ID) order by the primary key or whatever column you want the results ordered by.|||
There is no built-in way. Limit is a proprietary syntax like TOP or SET ROWCOUNT. You can do something like below using ANSI SQL ranking function (will work in SQL Server 2005, Oracle, DB2):
Code Snippet
select *
from (
select *, ROW_NUMBER() OVER(ORDER BY some_column) as limit
from your_table
) as t
where t.limit between @.start and @.end -- t.limit between 3 and 5 in your case
Subscribe to:
Posts (Atom)