Hidden max number of columns in Management Studio?

Hi,
The following is observed in the SQLServer 2005 Management Studio Visual
Editor:
Open table (Right Click option on a table) fails when I try it on a table
with lots of columns in it > 820 columns! Currently the table has no rows
(empty table). The same table opens fine with sql 2000 enterprise manager.
This table has been migrated/imported from sql 2000 without any errors! Other
imported tables with less number of columns can be opened in the visual
editor!
Wondering is there a hidden limit in terms of number of columns with
SQLserver 2005 Management Studio (Grid) Visual Editor?
I get SQL Executaion error
Error Source: Microsoft.VisualStudio.DataTools
Exception has been thrown by the target of an invocation
Is this a known behavior/limitation/bug? Is there a workaround?
Thanks
Kan,
See
http://www.sql-server-performance.co...ID=9016鵬
You may wish to report this as a bug at the suggested URL
http://lab.msdn.microsoft.com/produc...k/default.aspx
(also in the thread).
Steve Kass
Drew University
Kan wrote:

>Hi,
>The following is observed in the SQLServer 2005 Management Studio Visual
>Editor:
>Open table (Right Click option on a table) fails when I try it on a table
>with lots of columns in it > 820 columns! Currently the table has no rows
>(empty table). The same table opens fine with sql 2000 enterprise manager.
>This table has been migrated/imported from sql 2000 without any errors! Other
>imported tables with less number of columns can be opened in the visual
>editor!
>Wondering is there a hidden limit in terms of number of columns with
>SQLserver 2005 Management Studio (Grid) Visual Editor?
>I get SQL Executaion error
>Error Source: Microsoft.VisualStudio.DataTools
>Exception has been thrown by the target of an invocation
>Is this a known behavior/limitation/bug? Is there a workaround?
>Thanks
>
>

Hi i have var which has more than 8000 characters

Hi i heard that in sql server 2005 they introduced MAX to replace text data
type like strtemp varchar(MAX)... i have sql server 2005 and i am trying to
use it but does seem like working ... i guess the only reason is... my
database server still on sql server 2000 but on client i have sql server 200
5
where i am creating store procs tables view etcsss.
kindly any one can tell me how to solve the problem more than 8000 ... thank
samjad,
Can you tell us what are you trying to accomplish?.
AMB
"amjad" wrote:
> Hi i heard that in sql server 2005 they introduced MAX to replace text dat
a
> type like strtemp varchar(MAX)... i have sql server 2005 and i am trying t
o
> use it but does seem like working ... i guess the only reason is... my
> database server still on sql server 2000 but on client i have sql server 2
005
> where i am creating store procs tables view etcsss.
> kindly any one can tell me how to solve the problem more than 8000 ... thanks[/col
or]|||Hi i have two database one table called source and the other is dest...
dest is identifical of source table... both table has 104 fields when their
changed in source table then i wrote a script to track the changes and updat
e
the dest table... their is too many fields in both table like i am writing
dynamic sql query
first i create a cursor which has columnsname in while loop i do some thing
like
SET @.SQL='SELECT ' + @.DestID + ' FROM ' + @.Source_Table + 'INNER JOIN ' +
@.Full_DesTable_Name + ' ON ' + @.SourceID + ' = ' +
@.DestID + 'Where ('
fetch next from cur into @.columnname
while
begin
set @.sql=@.sql + @.Source_Table + '.[' + @.columnname + ']<> ' +
@.Full_DesTable_Name + '.[' + @.columnname + '] Or '
fetch next from cur into @.columnname
end
due to large amount of columns and each columns name is more than 80
character produce a larger string which in my case is 12000
if i dont do that method i have to create view to get changed fields ...in
which i have to set up each field in source to each field in destination mea
n
i have to write a very large where clause which is not possible... this
store proc create a sql query for me where i get the column info..... and
then put in that string in such a way which generate ...
ok i have done this method with two varchar(8000) variable but if i can do
that in one variable that will be great thanks
"Alejandro Mesa" wrote:
> amjad,
> Can you tell us what are you trying to accomplish?.
>
> AMB
> "amjad" wrote:
>|||This frustrates me at times as well. One idea I've had, but have never
put into practice is to store my pieces of my statements as rows in a
temporary table, then have some dynamic sql to build up the dynamic sql
in the table into a format exec (@.sql1 + @.sql2... etc.) then run that,
but at this point it's getting silly, so I've never actually tried it.
It might make for quite a re-usable bit of code though ;)
Cheers
Will|||amjad,
May be functions "checksum" or "binary_checksum" can help you.
Example:
use northwind
go
create table t1 (
c1 int primary key,
c2 char(1)
)
go
create table t2 (
c1 int primary key,
c2 char(1)
)
go
create view v1
as
select c1, c2, binary_checksum(*) as c3
from t1
go
create view v2
as
select c1, c2, binary_checksum(*) as c3
from t2
go
insert into t1 values(1, 'a')
insert into t1 values(2, 'b')
insert into t1 values(3, 'A')
insert into t2 values(1, 'a')
insert into t2 values(2, 'a')
insert into t2 values(3, 'a')
select v1.*, v2.*
from v1 inner join v2
on v1.c1 = v2.c1
where v1.c3 != v2.c3
go
drop view v1, v2
drop table t1, t2
go
I can not assure that this method is reliable.
AMB
"amjad" wrote:
> Hi i have two database one table called source and the other is dest...
> dest is identifical of source table... both table has 104 fields when the
ir
> changed in source table then i wrote a script to track the changes and upd
ate
> the dest table... their is too many fields in both table like i am writing
> dynamic sql query
> first i create a cursor which has columnsname in while loop i do some thin
g
> like
> SET @.SQL='SELECT ' + @.DestID + ' FROM ' + @.Source_Table + 'INNER JOIN ' +
> @.Full_DesTable_Name + ' ON ' + @.SourceID + ' = ' +
> @.DestID + 'Where ('
> fetch next from cur into @.columnname
> while
> begin
> set @.sql=@.sql + @.Source_Table + '.[' + @.columnname + ']<> ' +
> @.Full_DesTable_Name + '.[' + @.columnname + '] Or '
> fetch next from cur into @.columnname
> end
> due to large amount of columns and each columns name is more than 80
> character produce a larger string which in my case is 12000
>
> if i dont do that method i have to create view to get changed fields ...i
n
> which i have to set up each field in source to each field in destination m
ean
> i have to write a very large where clause which is not possible... this
> store proc create a sql query for me where i get the column info..... and
> then put in that string in such a way which generate ...
> ok i have done this method with two varchar(8000) variable but if i can do
> that in one variable that will be great thanks
> "Alejandro Mesa" wrote:
>

Help: Odd Error Mess :The table terms has been created but its max rowsize(8850)

Hi, I've come across a error message and I'm not sure what to do.

Warning: The table 'terms' has been created but its maximum row size (8850) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

I've tried playing with odd and ends, but I'm still getting the message does anyone know hwo you expand the maxium number of bytes per row.

Thanks For any help.You obviously have too much information to fit in one row.

This error message is caused by either a table with too many columns or a few very wide columns.

The best solution is to break up your table in to 2, 3 smaller tables and have a one-to-one relationship. The combination of the matching rows will meet your larger rowsize requirement.|||SQL Server has a 8K block size, which means that a single row cannot exceed this size (8060 bytes). When all column sizes are added up, SQL is telling you that that number potentially exceeds that size (this is because of varchars). You only have 2 options, 1 - reduce the size of your columns until the total is below 8060, or 2 - split the table up - as the previous post suggests.|||You cannot "force" sql to overcome the limit of 8060 bytes per row.

Anyway you'll get an error ONLY IF your actual data is larger that 8060 bytes.
For example if your table is made of two columns of VARCHAR(5000) you'll get the warning you already know.
Than you can insert values in your table with no problem, expect for that rows that are bigger that the near 8Kb limit.

If you really need to have not virtual limits you can use the TEXT or IMAGE data types.

HELP: INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...) - Doesnt work

I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it
INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)
I could swear i have done it before. Please help.
LitoOriginally posted by lito
I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it

INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)

I could swear i have done it before. Please help.

Lito

how about the use of a variable...

declare @.maxid int
select @.maxid = max(myid)+1 from ...

insert (...myID,...) values(...@.maxid...)

might be worth a try|||Is there a reason you are not using an identity field?

If so, then you will need to post your entire insert statement, not just the scrap you have supplied. We need more information about what you are trying to do.

blindman|||rocket39:
thanks for the sugestion, I never tought of that, it works great!.

blindman:
there is a reason why I am not using an Identity filed and that reason is because this database is aprox. 10 old was originaly a DBase. Also I am not the database administrator for this client and not authorised to make any significant changes to it. Plus this is a web based app. and is getting to be huge any change to db means hours of recoding. It is very stupid and frustrating at times.

Thanks for the help.

Lito|||look into setting a transaction lock around the pair of sql statements to fetch the max and then use it to insert a new number

without such a lock you are only asking for trouble|||Originally posted by r937
look into setting a transaction lock around the pair of sql statements to fetch the max and then use it to insert a new number

without such a lock you are only asking for trouble

do you think that TABLOCK would be sufficient?...

SET NOCOUNT ON;
DECLARE @.newID INT;
SELECT @.newID = (MAX(elementID)+1) FROM tblCattle TABLOCK; <<<<<< table LOCKED
INSERT INTO table1 (...values..., myID, ... values)
VALUES (...values..., @.newID, ...values);
INSERT INTO table2 (...values..., myID, ... values)
VALUES (...diff. values..., @.newID, ...diff values);
SET NOCOUNT OFF;|||i don't think that's enough

look into BEGIN TRANSACTION

rudy|||Originally posted by lito
I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it

INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)

I could swear i have done it before. Please help.

Lito

What about this?

INSERT (... myID, ...) select ...,(select MAX(myID)+1 from ... ), ...|||Originally posted by snail
What about this?

INSERT (... myID, ...) select ...,(select MAX(myID)+1 from ... ), ...
It gives me an error, That I can't have a Select statement in that context... who knows why.