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:
>