Need more than 300 columns

I have a database table with 130 columns, now I need 500 columns for a
special case, but I find that the limit is 300 in sql server 2000. Can i get
around that?
Any suggestions? Why is the limit 300?
The application works fine now, I think i use something around 30-40, I
don't want to change the data base structure and the code for only one case!
Thanx in advance
The max number of columns in SLQ server 2000 is 1024. Perhaps the GUI too you are using to add
columns poses the limitation. Also, having a large number of columns often indicates a
non-normalized database design...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hocke" <Hocke@.discussions.microsoft.com> wrote in message
news:67BB9B5A-1396-48B9-8B7E-66C2A7A84893@.microsoft.com...
> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can i get
> around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40, I
> don't want to change the data base structure and the code for only one case!
> Thanx in advance
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234jeOeq3EHA.524@.TK2MSFTNGP09.phx.gbl...
> The max number of columns in SLQ server 2000 is 1024. Perhaps the GUI too
> you are using to add
> columns poses the limitation. Also, having a large number of columns often
> indicates a
> non-normalized database design...
>
I agree with Tibor. Is it possible to create a bitmap for those other 500
columns and reduce them to a single varbinary(2048) or something similar?
Or possibly moving them to a separate child table with a 1 to 1
relationship?
Rick Sawtell
MCT, MCSD, MCDBA
|||"=?Utf-8?B?SG9ja2U=?=" <Hocke@.discussions.microsoft.com> wrote in
news:67BB9B5A-1396-48B9-8B7E-66C2A7A84893@.microsoft.com:

> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can
> i get around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40,
> I don't want to change the data base structure and the code for only
> one case!
> Thanx in advance
In addition to Tibor's comment, row size is also limited by capacity.
You could get around the problem by vertically partitioning the table
(this solution may also help increase db performance)
What error messages or problems are you getting when trying to add more
columns?
|||The fact that you have 130 columns but "one case" that needs 500 columns
suggests to me that you have the wrong design. Are you sure your schema is in
at least Third Normal Form? I doubt that adding more columns is the best
solution.
David Portas
SQL Server MVP
|||Taking short cuts in database design will surely come back to hunt you so be
very careful.
Normalization is the standard way to proceed in designing a database. There
are cases when you might want to demoralize your database to speed data
retrieval at the expense of data modification. You might want to take a look
at the following article if you are not planning to normalize your DB:
http://www.objectarchitects.de/Objec...s/i001fe02.htm
Sasan Saidi,
MSc. CS, MCSE, IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Hocke" wrote:

> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can i get
> around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40, I
> don't want to change the data base structure and the code for only one case!
> Thanx in advance

Need more than 300 columns

I have a database table with 130 columns, now I need 500 columns for a
special case, but I find that the limit is 300 in sql server 2000. Can i get
around that?
Any suggestions? Why is the limit 300?
The application works fine now, I think i use something around 30-40, I
don't want to change the data base structure and the code for only one case!
Thanx in advanceThe max number of columns in SLQ server 2000 is 1024. Perhaps the GUI too you are using to add
columns poses the limitation. Also, having a large number of columns often indicates a
non-normalized database design...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hocke" <Hocke@.discussions.microsoft.com> wrote in message
news:67BB9B5A-1396-48B9-8B7E-66C2A7A84893@.microsoft.com...
> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can i get
> around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40, I
> don't want to change the data base structure and the code for only one case!
> Thanx in advance|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234jeOeq3EHA.524@.TK2MSFTNGP09.phx.gbl...
> The max number of columns in SLQ server 2000 is 1024. Perhaps the GUI too
> you are using to add
> columns poses the limitation. Also, having a large number of columns often
> indicates a
> non-normalized database design...
>
I agree with Tibor. Is it possible to create a bitmap for those other 500
columns and reduce them to a single varbinary(2048) or something similar?
Or possibly moving them to a separate child table with a 1 to 1
relationship?
Rick Sawtell
MCT, MCSD, MCDBA|||"=?Utf-8?B?SG9ja2U=?=" <Hocke@.discussions.microsoft.com> wrote in
news:67BB9B5A-1396-48B9-8B7E-66C2A7A84893@.microsoft.com:
> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can
> i get around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40,
> I don't want to change the data base structure and the code for only
> one case!
> Thanx in advance
In addition to Tibor's comment, row size is also limited by capacity.
You could get around the problem by vertically partitioning the table
(this solution may also help increase db performance)
What error messages or problems are you getting when trying to add more
columns?|||The fact that you have 130 columns but "one case" that needs 500 columns
suggests to me that you have the wrong design. Are you sure your schema is in
at least Third Normal Form? I doubt that adding more columns is the best
solution.
--
David Portas
SQL Server MVP
--|||Taking short cuts in database design will surely come back to hunt you so be
very careful.
Normalization is the standard way to proceed in designing a database. There
are cases when you might want to demoralize your database to speed data
retrieval at the expense of data modification. You might want to take a look
at the following article if you are not planning to normalize your DB:
http://www.objectarchitects.de/ObjectArchitects/orpatterns/Performance/Denormalization/CraigMullinsGuidelines/i001fe02.htm
Sasan Saidi,
MSc. CS, MCSE, IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Hocke" wrote:
> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can i get
> around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40, I
> don't want to change the data base structure and the code for only one case!
> Thanx in advance

Need Help! Connection Count to Linked DB2 Server Reaches limit (SQL1040N) when execute dyn

Need Help! Connection Count to Linked DB2 Server Reaches limit
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.
Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.
At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.
So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.
The pseud code something like this:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC @.Dynamic_SQL
END
.=2E..
END
But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message: SQL1040N
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7= =94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95= =B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].
I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?
Thanks.
Regards,
Ling, Xiao-liSorry, the pseud code should lik this, just pseud code, in case someone
will question the pseud code validity:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC (@.Dynamic_SQL)
FETCH NEXT FROM condition_cursor INTO
local_variables
END
.=2E..
END
alingsjtu@.gmail.com =E5=86=99=E9=81=93=EF=BC=9A
> CREATE PROCEDURE p_FetchRawData variable_list
> AS
> BEGIN
> ...
> DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
> SELECT * FROM local_condition_table
> OPEN condition_cursor
> FETCH NEXT FROM condition_cursor INTO
> local_variables
> WHILE @.@.FETCH_STATUS =3D 0
> BEGIN
> SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
> SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
> ')'
> EXEC @.Dynamic_SQL
> END
> ...
> END
> But when execute this stored procedure p_FetchRawData, when the loop
> count is too big, then I got the error:
> [OLE/DB provider returned message: SQL1040N
> =E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94==E7=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6==95=B0=E7=9B=AE=E3=80=82
> SQLSTATE=3D57030]
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80040e69].
> I understood this error meaning which said too many OPENQUERY
> connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
> their connections to linked server? How to fail these connections when
> every OPENQUERY execution finished?
> > Thanks.
> > Regards,
> Ling, Xiao-li