linefeeds in text fields extracted with substring

Im running a query like
select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
MyColumn is th the text type.
The text in MyColumn contains a lot of tabulator characters (TAB),
carrige return (CR) and linefeed (LF) characters.
Sometimes (but not often) when I run the query, even in the Query
Analyser, I get the wrong result. I get too few characters, even though
I know they are in the database. (If I read the field with out the
substring, I can see everything).
The error is consistent - either a record works as it should, or it
fails to.
All answers are very welcome.
Thomas K
select @.@.version
----
----
----
--
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 row(s) affected)I think you did not handle the Tab, CR and Linefeed. The records that
dont have these will work otherwise fail
Madhivanan|||Check your 'maximum characters per column' setting in Query Analyzer under
Tools --> Options --> Results. This may be set too small and truncate the
value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Holme" <seiofecco@.hotmail.com> wrote in message
news:ONdDTThJFHA.1096@.tk2msftngp13.phx.gbl...
> Im running a query like
> select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
> MyColumn is th the text type.
> The text in MyColumn contains a lot of tabulator characters (TAB), carrige
> return (CR) and linefeed (LF) characters.
> Sometimes (but not often) when I run the query, even in the Query
> Analyser, I get the wrong result. I get too few characters, even though I
> know they are in the database. (If I read the field with out the
> substring, I can see everything).
> The error is consistent - either a record works as it should, or it fails
> to.
>
> All answers are very welcome.
> Thomas K
>
> select @.@.version
> ----
----
----
--
--
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> (1 row(s) affected)sql

Line 1: Incorrect syntax near 're'.

Hi Guys,
This is my Sql Query to insert values. All the fields are nvarchar
except the postcode which is Int. I have a condition if the postcode
field is numeric then insert values otherwise error message. Only one
of the customer gets the message on his browser
"Line 1: Incorrect syntax near 're'.
/westernsydney/register.asp, line 198 "
But other registrations are still going through with this code. Can
someone tell me why this is happening
strSql5 = "INSERT INTO tblattendeem
(attendee_firstname,attendee_surname,att
endee_location_street1,attendee_loca
tion_suburb,attendee_location_postcode,a
ttendee_company_name,attendee_phone,
attendee_email,event_id)"_
& "VALUES
('"&Request.QueryString("firstname")&"','"&Request.QueryString("surname")&"'
,"_
&"
'"&Request.QueryString("address")&"','"&Request.QueryString("suburb")&"',"_
&" '"&Request.QueryString("postcode")&"',
'"&Request.QueryString("company")&"',"_
&" '"&Request.QueryString("phone_number")&"',
'"&Request.QueryString("email")&"',"_
&" '"&Request.Cookies("event_id")&"' )"
Many ThanksHey Nachi, without looking at your sample data, it's difficult to tell
what's going on. My guess is that you have a customer with either a
quote (") or an apostrophe (') in the column.
There are other issues that you be aware of, however; building a string
like you are doing increases the chances that someone will be able to
inject another SQL statement into your code, thus doing damange to your
application. See http://aspalliance.com/385 for an example.
Also, do any of your postal codes start with a 0? If so, you may want
reconsider using an integer to store the data.
HTH,
Stu|||Hi Stu,
Thank you so much for your time & Quick reply. This Groups is really
useful. Im waiting on the Data from this specific customer. thank you
for your suggestion as well on Sql Injection. I will impl this in
future.
K Regards,
Nachi
Stu wrote:
> Hey Nachi, without looking at your sample data, it's difficult to tell
> what's going on. My guess is that you have a customer with either a
> quote (") or an apostrophe (') in the column.
> There are other issues that you be aware of, however; building a string
> like you are doing increases the chances that someone will be able to
> inject another SQL statement into your code, thus doing damange to your
> application. See http://aspalliance.com/385 for an example.
> Also, do any of your postal codes start with a 0? If so, you may want
> reconsider using an integer to store the data.
> HTH,
> Stu|||To emphasize Stu's point, there are hackers who do nothing all day but
search for web sites to exploit SQL injection vulnerabilities. If your site
is exposed to the public internet, be sure to heed Stu's advice use only
parameterized SQL queries. I hope that 'in the future' means now.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nachi" <Rengenath@.gmail.com> wrote in message
news:1148612435.121757.148630@.j33g2000cwa.googlegroups.com...
> Hi Stu,
> Thank you so much for your time & Quick reply. This Groups is really
> useful. Im waiting on the Data from this specific customer. thank you
> for your suggestion as well on Sql Injection. I will impl this in
> future.
> K Regards,
> Nachi
> Stu wrote:
>

Limiting Matrix Columns

I am dealing with a report which must display records in columns. The number of columns is dynamic as is the number of fields (rows) which will be displayed.
For example, my data is structured as follows:
ID Name Price State Category etc...
1 Widget $100 AL Tools
2 Gadget $45 NY Accessories
3 Thingy $12.50 TX Tools
etc.
.
.
In order to convert this to a matrix, I have queried the data to return in the following format:
ID Attribute Value
1 Name Widget
1 Price $100
1 State AL
1 Category Tools
2 Name Gadget
2 Price $45
2 State NY
2 Category Accessories
3 Name Thingy
3 Price $12.50
3 State TX
3 Category Tools
etc...
So, I create a matrix in my report with 1 column and 1 row. The column is grouped on ID. The row is grouped on Attribute. The detail displays the Value. Giving me:
1 2 3 etc...
Name Widget Gadget Thingy
Price $100 $45 $12.50
State AL NY TX
Category Tools Accessories Tools
etc...
This all works great and is very simple. The problem stems from the fact that I only want to display a limited number of columns on each page. Once 8 columns are displayed, I would like the matrix to skip to a new page, which redisplays my header. Unfortunately, the matrix just keeps expanding to the right and never wraps. Is there any way to work around this? Has anyone found a way to force a matrix to wrap and repeat the headers?
Thanks

Hello:

I have just sstayed away from Matrix'es in that I have no control. I have not found a way around your above example - maybe someone else knows but I sure don't - so I have just stayed with simple lists and have manipulated the data via a stored procedure with a "crosstab" or "pivot table" to generate the data (with respective columns) that I want and then within RS just layout the report visibility similar to a matrix.

A different subject for a different day but the same occurs within Analysis Services in that the horizontal presentation just extrapolates itself forever based upon the dimensions and columns - maybe I did not say that corectly but the same thing occurs.
So here is a nice little "generic" SQL that will allow you to present your row data as column data. A side Note - that is the ONLY issue I have with Reporting Services in that all data is always represented as a ROW - so this Stored Procedure will definitely help you control what you want to present.
There are some initial definitions you should deploy such as storing these parameters for each report within a table in SQL and if you want I can help you - but nevertheless here is the code and I rename this Stored Procedure for the name of my Reporting Services Report.
CREATE procedure Ex_CrossTab or whatever name you want (@.SQL varchar(2500),

@.PivotCol varchar(100),

@.Summaries varchar(100),

@.GroupBy varchar(100),

@.OtherFields varchar(150) = Null,

@.Debug bit = 0)

AS

set nocount on

set ansi_warnings off

declare @.Vals varchar(8000);

declare @.Vals2 varchar(8000);

declare @.Vals3 varchar(8000);

declare @.tmp varchar(1000);

declare @.TotalLen int;

set @.Vals = '';

set @.Vals2 = '';

set @.Vals3 = '';

set @.TotalLen = len(@.SQL) + len(@.GroupBy) + Len(ISNULL(@.OtherFields,''))

set @.OtherFields = isNull(', ' + @.OtherFields ,'')

create table #temp (Pivot varchar(100))

insert into #temp

exec ('select distinct convert(varchar(100),' + @.PivotCol + ') as Pivot FROM (' + @.SQL + ') A')

select @.tmp =

replace(replace(@.Summaries,'(','(CASE WHEN ' + @.PivotCol + '=''' + replace(Pivot,'''',''') +

''' THEN '),')[', ' END) as [' + Pivot ),

@.TotalLen = @.TotalLen + Len(@.tmp),

@.Vals = case when @.TotalLen < 7800 then @.Vals + ', ' + @.tmp else @.Vals end,

@.Vals2 = case when @.TotalLen between 7800 and 15799 then @.Vals2 + ', ' + @.tmp else @.Vals2 end,

@.Vals3 = case when @.TotalLen between 15800 and 23799 then @.Vals3 + ', ' + @.tmp else @.Vals3 end

from
#Temp
order by
Pivot

drop table #Temp

if (@.Debug=0)

exec ( 'select ' + @.GroupBy + @.OtherFields + @.Vals + @.Vals2 +

@.Vals3 + ' from (' + @.SQL + ') A GROUP BY ' + @.GroupBy)

else

begin

create table #Temp2 (SQLText Text);

insert into #Temp2 (SQLText)

values ('select ' + @.GroupBy + @.OtherFields + @.Vals + @.Vals2 +

@.Vals3 + ' from (' + @.SQL + ') A GROUP BY ' + @.GroupBy);

select * from #Temp2

end

set nocount off
set ansi_warnings on
GO

The above code executes your SQL statement as Dynamic SQL so make sure that you get rid of all blanks within your SQL because it is passed as a parameter to the above stored procedure. So use Query Analyzer and once again eliminate all "white" space and then pass your SQL statement as a parameter to the above stored procedure. Based upon your SQL Select you can eliminate the data that is returned thus "eliminating" your columns from extroploating within Reporting Services.
Definitions on how to use the above stored procedure are below:


Select - This is the SQL statement or table you are querying. It can be any valid SELECT statement, with JOINS, WHERE clauses, TOP, etc -- anything that you can cut and paste into query analyzer and execute. (And, in fact, you should always do this when testing things out).

Limited length of text data type

I cannot put the very long long text into the fields that
have text data type how can I extends the length of this
data type?
Thanks
Hi,
Did you meant that you got problems while retrieval. If it is a problem with
retrieval then use the below commands:-
SET TEXTSIZE { number }
select @.@.textsize
See books online for more details.
Thanks
Hari
MCDBA
"kwan" <anonymous@.discussions.microsoft.com> wrote in message
news:9d2e01c48675$7273bde0$a601280a@.phx.gbl...
> I cannot put the very long long text into the fields that
> have text data type how can I extends the length of this
> data type?
> Thanks
|||How are you trying to "put" the very long long text? Have you tried an
INSERT statement in Query Analyzer or from an application?
Are you sure the problem is in inserting the data, and not in
retrieving/viewing it?
http://www.aspfaq.com/
(Reverse address to reply.)
"kwan" <anonymous@.discussions.microsoft.com> wrote in message
news:9d2e01c48675$7273bde0$a601280a@.phx.gbl...
> I cannot put the very long long text into the fields that
> have text data type how can I extends the length of this
> data type?
> Thanks

Limitation in number of fields a Table published by push merge Replication may have

SQL 2000 SP3: There is a table with more than 254 Fields in it. (please
don't ask why :-) )
we observed, that replication on fields which are beyond the magic limit of
254 simply do not replicate.
( Enterprise manager says Replication takes place, but data do not show up
at destination.
Is this a known documented limitation ? Is it a bug with perhaps an
existing patch?
This is a known limitation. I'd consider splitting up the table into 2
separate related tables related and replicate them both. This can be made
transparent to the users by presenting them with a view which has the
original table's name and an InsteadOf trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I consider this to be a bug:
The Enterprisemanager accepts the publikation,
The replication Monitor says "1 Insert Done"
but the data is lost.
I would have no objections if the enterprise manager said:
"are you mad this is nothing you should publish I can't publish tables that
are so large / task rejected"
But If It accepts the task It should accomplish the task.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uieZiLGFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> This is a known limitation. I'd consider splitting up the table into 2
> separate related tables related and replicate them both. This can be made
> transparent to the users by presenting them with a view which has the
> original table's name and an InsteadOf trigger.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>