Comparing text in WHERE

Hi everyone,

I am compare to text fields like this:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value=@.Value

END

But the environment (I use VS2005) says that the data type is incompatible with the equal operator. Then I tried:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value IN '('+@.Value+')'

END

But nada (nothing). Any ideas? I can't change the data type since this field will hold values of different sizes.

Thank you for your input in advance!

hi,

text/ntext datatype does not support this kind of operation/comparison... you can find the "available" methods against these datatypes in http://msdn2.microsoft.com/en-us/library/ms187993.aspx..

these datatype are not good candidates for "filtering" operations as well.. consider that text can hold up to 2gb of data, and it's not worth the problem to support such heavy features..

so you can end up with "workarounds" if they fit your needs, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Value text NULL ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 'some text' ); INSERT INTO dbo.TestTB VALUES ( 2 , 'some other text' ); GO DECLARE @.key varchar(10); SET @.key = 'some text'; SELECT * FROM dbo.TestTB WHERE SUBSTRING( Value, 1, DATALENGTH(@.key)) = @.key; GO DROP TABLE dbo.TestTB; --<- Id Value -- 1 some text

but consider the other methods as well..

if you are using SQL Server 2005, consider moving the text/ntext datatypes to varchar(MAX)/nvarchar(MAX), as text datatype has been deprecated and, more usefull, varchar(MAX) supports all the traditional "string" operations...

regards

sqlsql

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Re
stlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =
dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
arent.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> to
> this
>|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they
> arent.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
going
things
type
available
>|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TE
XT).
> I just need
> to know when the values are different. Are there any functions available t
o
> do this? Please provide any code if you have it or any links regarding thi
s
> problem.
> Thanks in advance...
>|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdat
a2,1,
Datalength(textdata1))
1

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Re
stlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =
dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
arent.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> to
> this
>|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they
> arent.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
going[vbcol=seagreen]
things[vbcol=seagreen]
type[vbcol=seagreen]
available[vbcol=seagreen]
>|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TE
XT).
> I just need
> to know when the values are different. Are there any functions available t
o
> do this? Please provide any code if you have it or any links regarding thi
s
> problem.
> Thanks in advance...
>|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdat
a2,1,
Datalength(textdata1))
1

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...
anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>
|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Restlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =
dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>
|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>
|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>
|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
arent.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> to
> this
>
|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they[vbcol=seagreen]
> arent.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
going[vbcol=seagreen]
things[vbcol=seagreen]
type[vbcol=seagreen]
available
>
|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TEXT).
> I just need
> to know when the values are different. Are there any functions available to
> do this? Please provide any code if you have it or any links regarding this
> problem.
> Thanks in advance...
>
|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>
|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdata2,1,
Datalength(textdata1))
1

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Restlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
>> to
>> do this? Please provide any code if you have it or any links regarding
>> this
>> problem.
>> Thanks in advance...
>>
>|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
>> to
>> do this? Please provide any code if you have it or any links regarding
>> this
>> problem.
>> Thanks in advance...
>>
>|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
aren´t.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
> to
>> do this? Please provide any code if you have it or any links regarding
> this
>> problem.
>> Thanks in advance...
>>
>|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they
> aren´t.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> > Two text fields in the same record or in related tables? If you are
going
> > to
> > be comparing a large number of records, then you may want to speed
things
> > up
> > by first determing those records where the length of the text values are
> > not
> > the same. Those are obviosly different. Perhaps store their primary key
> > IDs
> > in a temporary table. Once done, you can then perform the full test
> > comparison against the remaining few that are the same size.
> >
> > "helpful sql" <nospam@.stopspam.com> wrote in message
> > news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> >> Hi all,
> >> What is the fastest and best way to compare two text fields(data
type
> >> TEXT). I just need
> >> to know when the values are different. Are there any functions
available
> > to
> >> do this? Please provide any code if you have it or any links regarding
> > this
> >> problem.
> >>
> >> Thanks in advance...
> >>
> >>
> >
> >
>|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TEXT).
> I just need
> to know when the values are different. Are there any functions available to
> do this? Please provide any code if you have it or any links regarding this
> problem.
> Thanks in advance...
>|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available to
>> do this? Please provide any code if you have it or any links regarding this
>> problem.
>> Thanks in advance...
>>
>|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdata2,1,
Datalength(textdata1))
--
1|||Here's a quick and dirty function that will do the comparison:
Create Function dbo.TextDataAreEqual (@.Text1 Text, @.Text2 Text)
Returns Bit
AS
Begin
Declare @.Len1 Int
Declare @.Len2 Int
Set @.Len1 = DataLength(@.Text1)
Set @.Len2 = DataLength(@.Text2)
If @.Len1 <> @.Len2
Return 0
If @.Len1 <= 8000
If Substring(@.Text1, 1, @.Len1) <> Substring(@.Text2, 1, @.Len2)
Return 0
Declare @.Index Int
Set @.Index = 1
While @.Index < @.Len1
Begin
If Substring(@.Text1, @.Index, 8000) <> Substring(@.Text2, @.Index, 8000)
Return 0
Set @.Index = @.Index + 8000
End
Return 1
End
In essence, it compares on length and then the first 8K and only barring that
does it compare using the chunking methodology that people were talking about in
previous posts. You would get a speed improvment by narrowing the list by
comparing datalength in the main query itself. So something like:
Select *
From (
Select T1.Id, T1.TextField1, T1.TextField2
From dbo.TableName As T1
Where T1.DataLength(T1.TextField1) = T1.DataLength(T1.TextField2)
) As T
Where dbo.TextDataAreEqual(T.TextField1, T.TextField2) = 1
Thomas|||OK that could be done in the query which call´s the function:
Select * from Employees where dbo.Comparetext(EmployeeID) =dbo.Comparetext(EmployeeID)
Where Datalength(EmployeeID) <> Datalength(EmployeeID)
Assuming that u don´t have the same column as shown above, just for the
syntax.
HTH, Jens Suessmeyer.
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:el0N7MmVFHA.1508@.tk2msftngp13.phx.gbl...
> Am I correct in assuming that if the text values have different lengths,
> for
> example 2000 vs. 2100, then they are different without performing a text
> comparison? Once we have that list of these candidates in a temporary
> table,
> we can exclude them from the query which performs the text compare.
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
>> Some (bad coded) applications write chunks of data in the database in
> steps
>> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
>> could be a problem because many columns would "seem" to be the same but
> they
>> aren´t.
>> Just a experience and my two cents.
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
>> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
>> > Two text fields in the same record or in related tables? If you are
> going
>> > to
>> > be comparing a large number of records, then you may want to speed
> things
>> > up
>> > by first determing those records where the length of the text values
>> > are
>> > not
>> > the same. Those are obviosly different. Perhaps store their primary key
>> > IDs
>> > in a temporary table. Once done, you can then perform the full test
>> > comparison against the remaining few that are the same size.
>> >
>> > "helpful sql" <nospam@.stopspam.com> wrote in message
>> > news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> >> Hi all,
>> >> What is the fastest and best way to compare two text fields(data
> type
>> >> TEXT). I just need
>> >> to know when the values are different. Are there any functions
> available
>> > to
>> >> do this? Please provide any code if you have it or any links regarding
>> > this
>> >> problem.
>> >>
>> >> Thanks in advance...
>> >>
>> >>
>> >
>> >
>>
>

Comparing DBs with Windiff

I scripted two versions of a database and then compared the text files using
WinDiff. There were major differences in the two files but Windiff said
they were the same "except for blanks." Has anyone else had such a problem
with WinDiff?
Are there any good (free or cheap) file comparison programs around that
work?
Thanks,
G
GaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
Have you tried the command-line "fc.exe" that is bult into Windows. It
should be in the path, but on my XP system is located in the
WINDOWS\SYSTEM32 folder.
David Gugick
Imceda Software
www.imceda.com
|||Not with Windiff, but I've observed the problem with RedGate SQL Compare
where database objects show as the same but manual examination reveals
significant differences.
Michael D. Long
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
>I scripted two versions of a database and then compared the text files
>using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
> problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>
|||Would you be able to post some segments of text that aren't comparing
properly? I'm very surprised -- and disturbed -- to hear that Windiff isn't
reliable; it's quite an old product at this point, and I use it extensively
to compare source code. So I'd really like to know if it has deficiencies.
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
> I scripted two versions of a database and then compared the text files
using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>
|||GaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
I think the problem is that WinDiff is not designed for use with binary
files. It's an ASCII comparison tool. I would use fc.exe instead.
See this article:
http://support.microsoft.com/default...b;en-us;159214
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
Last time I checked, SQL Scripts are just text files. But I guess the
OP could have had them Unicode encoded?
Anyway, if you want a better diff/merge program than WinDiff, I highly
recommend Beyond Compare... fc.exe is rather ancient and difficult to use in
this age of Graphical User Interfaces...
http://www.scootersoftware.com/moreinfo.html
|||Adam Machanic wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Last time I checked, SQL Scripts are just text files. But I
> guess the OP could have had them Unicode encoded?
> Anyway, if you want a better diff/merge program than WinDiff, I
> highly recommend Beyond Compare... fc.exe is rather ancient and
> difficult to use in this age of Graphical User Interfaces...
> http://www.scootersoftware.com/moreinfo.html
I misread the OP. I thought he was comparing data files.
On another note, I use Beyond Compare as well and think it's a great
tool although I do find myself using FC.EXE on occasion for quick
comparisons.
David Gugick
Imceda Software
www.imceda.com
|||..sql files are text script files.
G
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> GaryB wrote:
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
> See this article:
> http://support.microsoft.com/default...b;en-us;159214
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Sorry but it is a script of our entire database that is propritary. one
data base had many tables missing, views missing, some column differences,
and stored procedures missing. WinDiff just pops up a dialog saying
difference in blanks only. I'm using WinDiff 5.1. I'll try tht Beyond
Compare.
G
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uxnwyoEoEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Would you be able to post some segments of text that aren't comparing
> properly? I'm very surprised -- and disturbed -- to hear that Windiff
> isn't
> reliable; it's quite an old product at this point, and I use it
> extensively
> to compare source code. So I'd really like to know if it has
> deficiencies.
>
> "GaryB" <gb@.nospam.com> wrote in message
> news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
> using
> problem
>
sqlsql

Comparing DBs with Windiff

I scripted two versions of a database and then compared the text files using
WinDiff. There were major differences in the two files but Windiff said
they were the same "except for blanks." Has anyone else had such a problem
with WinDiff?
Are there any good (free or cheap) file comparison programs around that
work?
Thanks,
GGaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
Have you tried the command-line "fc.exe" that is bult into Windows. It
should be in the path, but on my XP system is located in the
WINDOWS\SYSTEM32 folder.
--
David Gugick
Imceda Software
www.imceda.com|||Not with Windiff, but I've observed the problem with RedGate SQL Compare
where database objects show as the same but manual examination reveals
significant differences.
--
Michael D. Long
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
>I scripted two versions of a database and then compared the text files
>using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
> problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>|||Would you be able to post some segments of text that aren't comparing
properly? I'm very surprised -- and disturbed -- to hear that Windiff isn't
reliable; it's quite an old product at this point, and I use it extensively
to compare source code. So I'd really like to know if it has deficiencies.
"GaryB" <gb@.nospam.com> wrote in message
news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
> I scripted two versions of a database and then compared the text files
using
> WinDiff. There were major differences in the two files but Windiff said
> they were the same "except for blanks." Has anyone else had such a
problem
> with WinDiff?
> Are there any good (free or cheap) file comparison programs around that
> work?
> Thanks,
> G
>|||GaryB wrote:
> I scripted two versions of a database and then compared the text
> files using WinDiff. There were major differences in the two files
> but Windiff said they were the same "except for blanks." Has anyone
> else had such a problem with WinDiff?
> Are there any good (free or cheap) file comparison programs around
> that work?
> Thanks,
> G
I think the problem is that WinDiff is not designed for use with binary
files. It's an ASCII comparison tool. I would use fc.exe instead.
See this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;159214
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
Last time I checked, SQL Scripts are just text files. But I guess the
OP could have had them Unicode encoded?
Anyway, if you want a better diff/merge program than WinDiff, I highly
recommend Beyond Compare... fc.exe is rather ancient and difficult to use in
this age of Graphical User Interfaces...
http://www.scootersoftware.com/moreinfo.html|||For tools, I would check out Innovartis DB Ghost
http://www.innovartis.co.uk/
Regards,
Nick Evans
>--Original Message--
>I scripted two versions of a database and then compared
the text files using
>WinDiff. There were major differences in the two files
but Windiff said
>they were the same "except for blanks." Has anyone else
had such a problem
>with WinDiff?
>Are there any good (free or cheap) file comparison
programs around that
>work?
>Thanks,
>G
>
>.
>|||Adam Machanic wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
>> I think the problem is that WinDiff is not designed for use with
>> binary files. It's an ASCII comparison tool. I would use fc.exe
>> instead.
> Last time I checked, SQL Scripts are just text files. But I
> guess the OP could have had them Unicode encoded?
> Anyway, if you want a better diff/merge program than WinDiff, I
> highly recommend Beyond Compare... fc.exe is rather ancient and
> difficult to use in this age of Graphical User Interfaces...
> http://www.scootersoftware.com/moreinfo.html
I misread the OP. I thought he was comparing data files.
On another note, I use Beyond Compare as well and think it's a great
tool although I do find myself using FC.EXE on occasion for quick
comparisons.
David Gugick
Imceda Software
www.imceda.com|||.sql files are text script files.
G
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esqkY7FoEHA.1152@.TK2MSFTNGP11.phx.gbl...
> GaryB wrote:
>> I scripted two versions of a database and then compared the text
>> files using WinDiff. There were major differences in the two files
>> but Windiff said they were the same "except for blanks." Has anyone
>> else had such a problem with WinDiff?
>> Are there any good (free or cheap) file comparison programs around
>> that work?
>> Thanks,
>> G
> I think the problem is that WinDiff is not designed for use with binary
> files. It's an ASCII comparison tool. I would use fc.exe instead.
> See this article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;159214
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Sorry but it is a script of our entire database that is propritary. one
data base had many tables missing, views missing, some column differences,
and stored procedures missing. WinDiff just pops up a dialog saying
difference in blanks only. I'm using WinDiff 5.1. I'll try tht Beyond
Compare.
G
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uxnwyoEoEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Would you be able to post some segments of text that aren't comparing
> properly? I'm very surprised -- and disturbed -- to hear that Windiff
> isn't
> reliable; it's quite an old product at this point, and I use it
> extensively
> to compare source code. So I'd really like to know if it has
> deficiencies.
>
> "GaryB" <gb@.nospam.com> wrote in message
> news:ubOAitAoEHA.2804@.TK2MSFTNGP09.phx.gbl...
>> I scripted two versions of a database and then compared the text files
> using
>> WinDiff. There were major differences in the two files but Windiff said
>> they were the same "except for blanks." Has anyone else had such a
> problem
>> with WinDiff?
>> Are there any good (free or cheap) file comparison programs around that
>> work?
>> Thanks,
>> G
>>
>

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

compare text fields

Hi all,
What is the fastest and best way to compare two text fields. I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...What about;
Select Case textfiled1 When textfield2 THEN 'The same" ELSE 'Not Eqal' END
From Sometable
Or you could perform a self outer join.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"sql" <donotspam@.nospaml.com> schrieb im Newsbeitrag
news:eBA3P0jVFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields. I just need
> to know when the values are different. Are there any functions available
> to do this? Please provide any code if you have it or any links regarding
> this problem.
> Thanks in advance...
>|||Thanks. But I thought you can't use text data types in the CASE statements!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ujgKH6jVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> What about;
> Select Case textfiled1 When textfield2 THEN 'The same" ELSE 'Not Eqal'
END
> From Sometable
> Or you could perform a self outer join.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "sql" <donotspam@.nospaml.com> schrieb im Newsbeitrag
> news:eBA3P0jVFHA.3188@.TK2MSFTNGP09.phx.gbl...
need
regarding
>|||You can use the [NOT] LIKE operator.
For example,
SELECT * FROM Table1 t1 JOIN Table2 t2 ON t1.SomeID = t2.SomeID
WHERE t1.SomeTextField NOT LIKE t2.SomeTextField
"Nikhil Patel" wrote:

> Thanks. But I thought you can't use text data types in the CASE statements
!
> "Jens Sü?meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in
> message news:ujgKH6jVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> END
> need
> regarding
>
>|||Hi Edmire,
Thanks. But this only works if the length of the value stored in text
field is <= 8000 characters long. If the length exceeds 8000 characters, it
does not work.
"Edmire" <Edmire@.discussions.microsoft.com> wrote in message
news:FE8C9CF7-E03E-415A-85E1-1FA1252DFA17@.microsoft.com...
> You can use the [NOT] LIKE operator.
> For example,
> SELECT * FROM Table1 t1 JOIN Table2 t2 ON t1.SomeID = t2.SomeID
> WHERE t1.SomeTextField NOT LIKE t2.SomeTextField
>
> "Nikhil Patel" wrote:
>|||Try spinning through them with READTEXT. I think that's the only option.
"sql" wrote:

> Hi Edmire,
> Thanks. But this only works if the length of the value stored in text
> field is <= 8000 characters long. If the length exceeds 8000 characters, i
t
> does not work.
> "Edmire" <Edmire@.discussions.microsoft.com> wrote in message
> news:FE8C9CF7-E03E-415A-85E1-1FA1252DFA17@.microsoft.com...
>
>

Compare tables from one server/database to another

There's no built-in compare tool that comes with SQL Server. You can script
objects and compare with a text file diff tool like Windiff or
BeyondCompare.
There are also third-party tools available like SqlCompare
(http://www.red-gate.com/products/sql_compare/index.htm). If you have
Visual Studio 2005, the CTP3 of the Team Edition for Database Professionals
(http://msdn.microsoft.com/vstudio/t...ro/default.aspx)
has a schema compare feature, among others.
Hope this helps.
Dan Guzman
SQL Server MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:449bbb8e$0$31655$636a55ce@.news.free.fr...
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff
> tool).
> Is there anything for MS SQL?
> thanks
> Wilfrid
>Hello,
I know for mysql there is a tool that compares tables (like a windiff tool).
Is there anything for MS SQL?
thanks
Wilfrid|||There's no built-in compare tool that comes with SQL Server. You can script
objects and compare with a text file diff tool like Windiff or
BeyondCompare.
There are also third-party tools available like SqlCompare
(http://www.red-gate.com/products/sql_compare/index.htm). If you have
Visual Studio 2005, the CTP3 of the Team Edition for Database Professionals
(http://msdn.microsoft.com/vstudio/t...ro/default.aspx)
has a schema compare feature, among others.
Hope this helps.
Dan Guzman
SQL Server MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:449bbb8e$0$31655$636a55ce@.news.free.fr...
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff
> tool).
> Is there anything for MS SQL?
> thanks
> Wilfrid
>|||You could try ApexSQL's SQLCompare tool. Try the 30 day fully functional
evaluation version.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:449bbb8e$0$31655$636a55ce@.news.free.fr...
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff
> tool).
> Is there anything for MS SQL?
> thanks
> Wilfrid
>|||You could try ApexSQL's SQLCompare tool. Try the 30 day fully functional
evaluation version.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:449bbb8e$0$31655$636a55ce@.news.free.fr...
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff
> tool).
> Is there anything for MS SQL?
> thanks
> Wilfrid
>|||Wilfrid -- xSQL Software has a great and free comparison and
synchronization tool that you can get from
http://www.x-sql.com/download.aspx (the free edition supports smaller
databases) -- also an sdk that allows you to integrate the comparison
and synchronization functionality in your application is available.
CJK
Wilfrid wrote:
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff tool
).
> Is there anything for MS SQL?
> thanks
> Wilfrid|||Wilfrid -- xSQL Software has a great and free comparison and
synchronization tool that you can get from
http://www.x-sql.com/download.aspx (the free edition supports smaller
databases) -- also an sdk that allows you to integrate the comparison
and synchronization functionality in your application is available.
CJK
Wilfrid wrote:
> Hello,
> I know for mysql there is a tool that compares tables (like a windiff tool
).
> Is there anything for MS SQL?
> thanks
> Wilfrid|||Wilfrid,
The open-source SchemaCrawler tool will do what you need. SchemaCrawler
outputs details of your schema (tables, views, procedures, and more) in
a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can
also output data (including CLOBs and BLOBs) in the same plain-text
formats. You can use a standard diff program to diff the current output
with a reference version of the output. SchemaCrawler can be run either
from the command line, or as an ant task. A lot of examples are
available with the download to help you get started.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party jars are required.
Once you get familiar with SchemaCrawler's Java API, you can even write
plug-ins that will automatically generate the scripts that you need.
Sualeh Fatehi.|||Wilfrid,
The open-source SchemaCrawler tool will do what you need. SchemaCrawler
outputs details of your schema (tables, views, procedures, and more) in
a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can
also output data (including CLOBs and BLOBs) in the same plain-text
formats. You can use a standard diff program to diff the current output
with a reference version of the output. SchemaCrawler can be run either
from the command line, or as an ant task. A lot of examples are
available with the download to help you get started.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party jars are required.
Once you get familiar with SchemaCrawler's Java API, you can even write
plug-ins that will automatically generate the scripts that you need.
Sualeh Fatehi.

Compare strings in text field

I am trying to build a simple search engine using Sql Server 2000 to scan information about approximatelly 20.000 products.

Heres what I am doing:

I created a table called keywords that contains a reference for each product.

keyword -> varchar(100)
items -> Text

keyword data example:

[keyword] [items]
car 1, 3, 5, 7
blue 3, 5
compact 1,7

I am not using clustered index.

To search basically I run the "AND" or "OR" to select the keywords I want to target.
I need to run another select that would compare the data in the items field depending of the condition selected. If "AND" clause is used I would need to compare all the items that contains the same reference, for example:

looking for car compact using "AND" clause
result = 1

looking for car compact using "OR" clause
result = 1,3,5,7

There is no table that holds references. The items are stored in a text field in the keyword table. I can compare data using script like AsP spliting the items by comma or space, but that can be too slow and use up a lot of RAM. Another solution would be to use a table to hold the references but that would affect performance dramatically because of the large number of records created and storage space used. One example, if I have 60.000 keywords and each keyword has an avereage of 200 references, I would have to generate 12 milion records.

I want to know if there is a function or routine in SQL server to compare matched references on the fly in the server between two or more fields and how should I do it.

In addition, in this scenario, how should a clustered index help?

Thanks

RodrigoNot sure i get it but from what i understand why do you have a , delimited string of the items

why not a seperate row for each item so that, you will get all the items in a query that you can then use easier...i take it the item is the foreign key for some record in another table|||If I am right, that physically generating the rows would fall in the scenario in the 12 milion scenario leading to unecessary records and lots of sql file pages to hold the data references. I 'd ratter have a little IO because I could cache the results. Do you have any suggestion?

Thanks
Rod

Company Dimension

We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.

All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?

1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.

2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.

Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.

Hi jrp210,

Based on my interpretation of your schema, I would design DimCompany as you describe. One row per company, unique IDs, and you may wish to add surrogate keys - they will make your data mart / warehouse easier to scale.

I would also add a DimInformationType for the different types of data you receive.

Another option is to snowflake the InformationType table off of DimCompany. This is common when the referenced lookup table is relatively small and doesn't change often.

There are performance implications but in smaller data warehouses (~1 - 2G) you will probably never notice. And if you do, an index will likely clear up any performance issue.

I don't follow the need for a fuzzy lookup. These are expensive in SSIS and should only be used when necessary.

You are correct: incremental loading does require a lookup (or a merge join) to detect new records.

There's lots of good information online about how to do this. I suggest picking up one of the Kimball books for help in designing data warehouses - I particularly like the Data Warehoue Toolkit which is updated to include information on using SSIS for ETL.

Hope this helps,

Andy

|||

Here are my 2 cents:

Having a conformed 'Company' dimension is the way to 'join' all fact rows. Not sure about using a Fuzzy lookup as it may give you non-expected results; so evaluate yourself the margin of error your company can tolerate. In general you may want to asses the level of cleanness of your data, so you can anticipate the results

About how to organize the fact data; I would say that depends on the way the data is going to be used during analysis and reporting, and on the nature of the data. You may want to spend some time with the end users to get that feedback; draw a data model and review it with them again; this may take a few iterations.

Andy suggestion about Kimball’s book is also a good idea.

Good luck with that

|||

Thanks for the response. I did purchase the book you referenced. It has been very helpful but doesn't delve into the issue of trying to create one dimension table from multiple sources with different primary keys.

I will definitely be creating surrogate keys because more data sources will be introduced over the long haul.

I don't follow the DimInformationType table. Is this more of a helper table to map the surrogate company key to the proprietary key used by the vendor? For example:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

When its time to access vendor B's file, most of the companies will be in the DimCompany table. If not, follow the same procedure as above. If they are in the DimCompany table then I will have to add a row to the vendor b helper table with the surrogate key/vendor b key.

It all stems from the fact that each vendor has its own proprietary (different) key for the same company. I don't know how I would get around not using fuzzy logic or some sort of text mapping. Text mapping could be dangerous as well since the names may be slightly different.

|||

There are 2 other books from Kimball’s group:

The Data Warehouse ETL tool kit

The Microsoft Data Warehouse Toolkit

The first one cover how to conform dimension (coming from different sources).; the second one covers Kimball's warehouse methodology using SQL Server 2005 tools.

|||

jrp210 wrote:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

Hi jrp210,

That's different from what I understood previously. Don't feel bad, this happens to me a lot.

Maybe your schema looks like this:

CompanySK (surrogate key) CompanyName (business key)
1 Microsoft

VendorSK (surrogate key) VendorName (business key)
1 Vendor A
2 Vendor B

VendorCompanySK VendorCompany_CompanySK VendorCompany_VendorSK VendorCompany_ID
1 1 1 123456
2 1 2 789101

This is a snowflake that allows you to utilize Company and Vendor separately in facts, and also use VendorCompany in facts. There are foreign key relationships between DimCompany and DimVendorCompany, and DimVendor and DimVendorCompany.

Hope this helps,

Andy

|||

Andy,

My goal would be to create one company record from multiple sources. I don't necessarily need a vendor or vendorcompany table but will probably need their proprietary company id as an attribute in my company dimension table so that one could link back using their key.

I would assume something like this:

CompanySK
VendorA CompanyId
VendorB CompanyId
VendorC CompanyId
Company Name

For the most part one row should contain an Id for vendor a,b, and c but there are times when that is not the case. Is that why the company name would be the business/natural key?

|||

The first thing you need to do if to define the grain of your dimension; it looks to me like the grain is one row for each company (even when that company exists in several vendor data sources); so if you have more than one 'version' of a company, like in your example of Microsoft company, you would need some kind of auxiliary table to keep that 1:many relationship between the many rows/company in the source and the 1 row per company n your dimension.

BTW, this has nothing to do with SSIS...but I hope it helps

|||

You are right about the SSIS - probably more geared toward dimensional modeling/DW. But, I am using SSIS to do that so this is where I originally posted to.

Yes, grain of the dimension is the company. The really isn't more than one version of the company. It is the same company coming from different source (vendor) systems. The vendor systems have different unique keys in which they tag a company. Because of this there isn't one natural key to use across all. If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

|||

jrp210 wrote:

If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

That is right. You could create a surrogate key in the dimension and then that auxiliary table will keep the relationship between source system keys(many for a company) and the Dimension surrogate key (one per company).

|||

At first it makes sense but to initially load the database how would you keep the company unique in the DimCompany table without a key that would link them together? Or better yet put, which comes first loading the auxillary table or Dimension table?

I was assuming the auxillary table would be loaded first:
VendorId
VendorCompanyCode
CompanyName
etc.

But then I would have to create a unique key that would be used in the DimCompany table. There would have to be another table that then creates this key (identity column) that would be used in the DimCompany table.

Common DTS Source & Multiple Destinations

I want to run multiple DTS packages which export data into text files.
There is only one Data Source ..and multiple destinations.
When i write a code for this in VB ,for each Package i need to define the source connectioninividually. Can't i use the same Source connection which i used for the first package in the subsequent packages?http://www.sqldts.com/default.aspx?200

CommandType changes back to text ALMOST EVERYTIME

When I open a report definition, go to a dataset and open it, the CommandType ALWAYS goes to "Text". I don't want "Text", I want "Stored Procedure". I NEED "Stored Procedure".

I have also found that the CommandText for a different dataset will change even though I have not opened it.

I guess the only solution I have is to open the RDL files, search for the dataset entries and then fix the XML code. This is a real pain in the buttocks when I am working with 300+ reports.

Is there a fix for this bug? Or are you going to FORCE everyone to use "Text"?

I am trying to finish this project so that we can release it to our customers but now I have to wonder why we decided to go with Reporting Services, especially now that we have to do double edits on each and every report.

Steven Broomhead
Optimum Solutions, Inc.

This happened to me with MDX queries.

One thing to try would be to determine why it is happening for some reports and not others. For me it was a carriage return within the RDL file where the query was. Removing this solved the issue.

Try doing a diff on reports that are and aren't experiencing this problem.

Also try using this product instead of visual studio.

http://www.fyireporting.com/

Another thing would be to try using the exec statement before the stored procedure and surrounding the stored proc in brackets, or if you are already doing this try removing the statement.

regards,

Andrew

|||Andrew,

So far it has happened with every report.

If I access any dataset within a report, the CommandType for that dataset will change to Text. It happens every time. If I don't go back and reset it to StoredProcedure, the deployed report expects the call to be a text string not an SP name.

Personally, I like the StoredProcedure CommandType. The Text command with the embedded parameters can get hard to read and debug.

I was hopeful that someone would reply that it was a bug and that there was a hotfix available. I searched but have not had any success to date.

The fyiReporting program looks interesting but our developers need to deploy to a common development server not their local PC. That and the fact I would have to sell my boss on buying software that provides a similar tool to studio (from our MSDN Universal subscription, costing $$$$) because studio has a bug. I don't want that conversation.|||

Steven or anyone,

Has anyone found a fix for this problem! As Steven explains it happens with every report I work on. Even after applying Visual Studio SP1 this bug still exists. It does not leave a warm feeling that this post goes back to Oct 2006 without any MS response.

Scott

CommandType changes back to text ALMOST EVERYTIME

When I open a report definition, go to a dataset and open it, the CommandType ALWAYS goes to "Text". I don't want "Text", I want "Stored Procedure". I NEED "Stored Procedure".

I have also found that the CommandText for a different dataset will change even though I have not opened it.

I guess the only solution I have is to open the RDL files, search for the dataset entries and then fix the XML code. This is a real pain in the buttocks when I am working with 300+ reports.

Is there a fix for this bug? Or are you going to FORCE everyone to use "Text"?

I am trying to finish this project so that we can release it to our customers but now I have to wonder why we decided to go with Reporting Services, especially now that we have to do double edits on each and every report.

Steven Broomhead
Optimum Solutions, Inc.

This happened to me with MDX queries.

One thing to try would be to determine why it is happening for some reports and not others. For me it was a carriage return within the RDL file where the query was. Removing this solved the issue.

Try doing a diff on reports that are and aren't experiencing this problem.

Also try using this product instead of visual studio.

http://www.fyireporting.com/

Another thing would be to try using the exec statement before the stored procedure and surrounding the stored proc in brackets, or if you are already doing this try removing the statement.

regards,

Andrew

|||Andrew,

So far it has happened with every report.

If I access any dataset within a report, the CommandType for that dataset will change to Text. It happens every time. If I don't go back and reset it to StoredProcedure, the deployed report expects the call to be a text string not an SP name.

Personally, I like the StoredProcedure CommandType. The Text command with the embedded parameters can get hard to read and debug.

I was hopeful that someone would reply that it was a bug and that there was a hotfix available. I searched but have not had any success to date.

The fyiReporting program looks interesting but our developers need to deploy to a common development server not their local PC. That and the fact I would have to sell my boss on buying software that provides a similar tool to studio (from our MSDN Universal subscription, costing $$$$) because studio has a bug. I don't want that conversation.|||

Steven or anyone,

Has anyone found a fix for this problem! As Steven explains it happens with every report I work on. Even after applying Visual Studio SP1 this bug still exists. It does not leave a warm feeling that this post goes back to Oct 2006 without any MS response.

Scott

CommandText on Report Server

When a report runs is it possible to retrieve the command text and modify it in order to append some additional "where" conditions, before the sql is processed to return data to the report?

You can write code like this

declare sSQL varchar(500)

declare sWhere varchar(200)

set sSQL = "Select * from mytable"

if lenght(@.Parameter1) > 0

begin

sWhere = " Where mycolume = '" + @.Parameter2 + "'"

end

sSQL = sSQL + sWhere

EXEC (sSQL)

|||

Thanks for the reply, but I need to be clearer with the question that I am asking.

I have written code to construct the piece of SQL that I need to append - it retrieves conditions from the database which define some additional filtering for security.

What I need to know is if it is possible at runtime to trap the SQL statement stored in the <CommandText> element of the RDL for the report that is running - append my additional segment of SQL just before it gets processed. Bear in mind that a Report designed may have several SQL statements from individual parts of the overall report - a data set tp provide values for parameters, a dataset for each section on a report, chart and matrix for example.

Should I be looking at doing it via a Data Processing extension, therefore trapping any report that that is run via SSRS?

Command Type for dataset using Custom Data Processing Extension

Hi,
I'm writing a custom data processing extension, but I cannot get
anything other than a command type of "Text" in the dataset for the
report I have setup to use the Data extension I have written.
Does anyone know what or where the available command types are derived
from?
GregI still haven't had any luck resolving this, hoping someone can help :)

command type = Stored procedure

Hi,
does anyone know an URL with an example of using "StoredProcedure" as
"Command type" in reporting services. Normally it defaults to "Text" instead
of "StoredProcedure"
thanks,
Ludowell its the same as the text only u write the name of the sp(no need exec..).
if the sp has input parameters the RS will add them by default as Report
parameters.
I think it's the best way 2 use RS...
"Ludo Van Dun" wrote:
> Hi,
> does anyone know an URL with an example of using "StoredProcedure" as
> "Command type" in reporting services. Normally it defaults to "Text" instead
> of "StoredProcedure"
> thanks,
> Ludo
>
>

Command to export data from sql server to text file in sql query analyzer

Hi,
Does anyone know how to export a query to a text file by using query
analyzer with command, i dun want to manual click it.
Thanks a lot!
regards,
florenceleeDo you mean save the results to a file? If so, you can set the output of
the query to go to Text, Grid or File from the Query pull-down menu. If you
don't even want to do that then you could use osql.exe rather than Query
Analyzer. The -o parameter allows you to specify an output file to which
you'd like the query results to go.
Cheers,
Mike
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:uQMYVup0EHA.2572@.tk2msftngp13.phx.gbl...
> Hi,
> Does anyone know how to export a query to a text file by using query
> analyzer with command, i dun want to manual click it.
>
> --
> Thanks a lot!
> regards,
> florencelee
>|||Hi,
I understand by using osql, can use sql quey analyzer and i dun want
to output to text file and save manually, i just want a command which can
behave in the same way as in osql but i need to do in query analyzer'
Thanks a lot!
regards,
florencelee
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:uQMYVup0EHA.2572@.tk2msftngp13.phx.gbl...
> Hi,
> Does anyone know how to export a query to a text file by using query
> analyzer with command, i dun want to manual click it.
>
> --
> Thanks a lot!
> regards,
> florencelee
>|||There's not a TSQL command for that. Most of us who need to do this in TSQL
use xp_cmdshell and
OSQL.EXE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:%23CjGX9r0EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I understand by using osql, can use sql quey analyzer and i dun want
> to output to text file and save manually, i just want a command which can
> behave in the same way as in osql but i need to do in query analyzer'
> --
> Thanks a lot!
> regards,
> florencelee
> "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> news:uQMYVup0EHA.2572@.tk2msftngp13.phx.gbl...
>