Do not display the result of my long text string, approx about 400 characters

I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
......If you are using the MS Query Analyzer from the SQL 2000 Client Tools, you can set the maximum column length. Select Tools | Options | Results and the length control is near the middle of the sheet.

-PatPsql

Do not Display "NULL" in Results tab

Is there a setting in Management Studio to NOT display "NULL" in the
Results tab for either Grid or Text view? I didn't see anything under
Options. I'm currently having to do search and replace to remove it
before passing on to users or using for imports to other apps. Third
party import programs typically treat this as text.
MarkChange the query to say COALESCE(stringValue, '')
"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
> Is there a setting in Management Studio to NOT display "NULL" in the
> Results tab for either Grid or Text view? I didn't see anything under
> Options. I'm currently having to do search and replace to remove it
> before passing on to users or using for imports to other apps. Third
> party import programs typically treat this as text.
>
> Mark|||This is what I was afraid of. It seems for every two steps forward in
Management Studio, you have to take one step back. So now if I have
100 varchar columns, I'll have to create 100 alias columns
(ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
carry over features in previous versions to the next. It seems they
could have added an option to "Query Results" to display NULL or blank
for the output. This wasn't a problem in QA.
On Fri, 30 Jun 2006 08:45:49 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Change the query to say COALESCE(stringValue, '')
>"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
> news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
>|||> (ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
> carry over features in previous versions to the next. It seems they
> could have added an option to "Query Results" to display NULL or blank
> for the output. This wasn't a problem in QA.
It's quite possible that they got a lot of complaints about that
"feature"... to me, at least, there is a big difference between NULL and
blank. If I can't visibly tell the difference, I'm going to have to change
something.
It's kind of like a car manufacturer choosing to put the cup holder on the
dash or in the center console. No matter which option they choose, they're
going to piss someone off.
A

Do CR/LF get stored in a text column

I am capturing user comments in a textarea field on a web form and then storing the comments in a text column in a DB table. I would like to know if the carriage returns and linefeeds get stored in the database (MS SQL 2000)?I would say yes...

USE Northwind
GO

CREATE TABLE myTable99 (col1 varchar(8000))
GO

DECLARE @.x varchar(8000)
SELECT @.x = 'Wasted away again in
Margaritaville'
INSERT INTO myTable99 (Col1) SELECT @.x
SELECT @.x
GO

DROP TABLE myTable99
GO|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
I would say yes...
[/quote

I second that.sql

dm_exec_query_stats/dm_exec_sql_text and plan cache

We are using the following query:
SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
to figure out some of our worst offenders in terms of un-parameterized
queries. The problem is when we run this query in production (we are using
x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in the
plan cache to near zero and an increase in the free buffer pool which in
turns causes our database to become unresponsive and sometimes causes the
CPUs to be pegged for an extended period (on an 8 core box). We might expect
the query to use some memory, but certain not that much and we would expect
the plan cache to come right back up but it does not.
Anyone on this board have any ideas? We have engaged PSS but haven't gotten
very far. We would like to be able to use the management views real-time in
production (one of the reasons we decided to upgrade to 2005).
Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/ 4
gigs of RAM)
It had no problem. Any other actvities on the server at the same time?
"jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
> We are using the following query:
> SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
> REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
> FROM sys.dm_exec_cached_plans AS p
> CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
> WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
> to figure out some of our worst offenders in terms of un-parameterized
> queries. The problem is when we run this query in production (we are using
> x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in
> the
> plan cache to near zero and an increase in the free buffer pool which in
> turns causes our database to become unresponsive and sometimes causes the
> CPUs to be pegged for an extended period (on an 8 core box). We might
> expect
> the query to use some memory, but certain not that much and we would
> expect
> the plan cache to come right back up but it does not.
> Anyone on this board have any ideas? We have engaged PSS but haven't
> gotten
> very far. We would like to be able to use the management views real-time
> in
> production (one of the reasons we decided to upgrade to 2005).
|||There are a lot of activities going on at the same time. We are running this
in the middle of the day in some of our heaviest activity times. Our free
data buffer pool is at 0 and our plan cache is at 2-3 gigs (we do need to do
some parameterization but that's why we want to run this - to know where to
start). We are running at about 1,000 batch requests per second or sometimes
higher. I am trying to replicate this in a test environment by creating load
which will fill up the buffer pool and create a decent amount of plan cache
(run a lot of ad-hoc queries). We wish there was some way which we could set
a minimum plan cache size no matter what the internal memory pressure is -
that would at least probably work around this problem.
Jeff
"Uri Dimant" wrote:

> Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/ 4
> gigs of RAM)
> It had no problem. Any other actvities on the server at the same time?
>
> "jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
> news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
>
>

Distribution task marvelously slow

Greetings!
We are replicating a table (many of them actually, but one in particular)
which is about 10 GB...660K rows including some text columns. We needed to
change the properties of one column and did not want to pay the penalty of
reinitializing the subscription, so I added a temp column "A_temp", populated
that with the data from the original column "A" then dropped column "A" and
readded it with new properties including NOT NULL. We are then loading "A"
from "A_temp" and then dropping the "A_temp" column. In theory this is fine
and it is working...however the updates are remarkably slow...three records a
second with the sp_MSUpd_... stored procedure. At that rate, the whole
shebang will take days instead of hour or so it would have with the reinit
and snapshot. This is generally the way MS would recommend doing the schema
change from what I have studied. The publisher and distributor are same
server and the subscriber is a remote, but on the LAN, machine.
Any ideas? Thanks so much!
You could check the query plan of the update stored proc - perhaps it would
benefit from different indexes at the subscriber or perhaps the indexes are
fragmented - also worth checking. Finally, check to see if there is any
blocking going on that accounts for the slowdown.
HTH,
Paul Ibison
|||Thank you for your reply. I did notice that the replication update was
blocking other things, but we have rectified that - for now. The actual
update doesn't appear to be blocked at all. We do have the column with a
clustered index on it. Actually thought of dropping indexes to see if it
sped up, but the column which is getting updated is not indexes, so I wasn't
expected a lot out of that. I haven't checked out the query plan...I think
you are right that it is worth a look. I'll see if I can get one of my
admins to toss me an actual sp call instead of having me guess at the
parameters...
Thanks again!
"Paul Ibison" wrote:

> You could check the query plan of the update stored proc - perhaps it would
> benefit from different indexes at the subscriber or perhaps the indexes are
> fragmented - also worth checking. Finally, check to see if there is any
> blocking going on that accounts for the slowdown.
> HTH,
> Paul Ibison
>

distribution agent bcp error

I am trying to use immediate updating transactional replication. A few of the tables have text fields. I want to filter out these fields, but when the subscriber reinitializes I get a bcp error - unexpected EOF. When I switch the publisher to use chara
cter format (rather than native) I get an unknown character error. The columns are marked default null. If I do not filter the columns, the initialization works fine. Is there anything I am missing?
Any help is appreciated.
Sql Server 2000 Enterprise sp3
Debra,
I haven't seen this before and don't know of a direct solution. Some posts
have mentioned that the column order is relevant. There is a known bug
(http://support.microsoft.com/?id=271344) which reports the same error - in
this case you'd need to manually apply the snapshot. Please can you post up
the schema of the problematic table and an explanation of the filter you're
using.
TIA,
Paul Ibison
|||I am begining to believe the problem is specific to that server. I copied the databases to to my local machine and the replication with filters worked fine. But, I have to say I have not installed the application locally. I really do not believe the pr
oblem is the application, though.
On the servers, I am replicating the databases behind Great Plains - 2 separate boxes with named instances. I have handled all the identity fields with not for replication and ranges. In the tables that are giving me headaches there are between 10 and 3
0 columns. There are some indexes and PK, but not on the columns I am trying to filter. I tried moving the columns (I had also read that column position might be to blame), with no luck. For a work around, I changed the columns to varchar and left th
em in the replication. When I move to production, I will attempt to leave the fields as text and use the filter. I'll be sure to post if it works or not. I did not set up the servers I am using, but I can not think of why they would not handle the filt
ers!
Thanks for confirming that this was not a known error and possibly is restricted to my enviornment.
"Paul Ibison" wrote:

> Debra,
> I haven't seen this before and don't know of a direct solution. Some posts
> have mentioned that the column order is relevant. There is a known bug
> (http://support.microsoft.com/?id=271344) which reports the same error - in
> this case you'd need to manually apply the snapshot. Please can you post up
> the schema of the problematic table and an explanation of the filter you're
> using.
> TIA,
> Paul Ibison
>
>
|||I moved the databases to a different server and recreated the subsciption and publication. When replicating just that one table, the bcp works with no error. When I add all the other tables (175), the bcp fails if there are any filters on the tables th
at have many columns. I did not test filtering the tables that have fewer columns.
I worked around the issue by doing as you said and manually applying the snapshot. Luckily this is a one time deal and there will only be one subscriber.
Thanks for your help!
"Paul Ibison" wrote:

> Debra,
> I haven't seen this before and don't know of a direct solution. Some posts
> have mentioned that the column order is relevant. There is a known bug
> (http://support.microsoft.com/?id=271344) which reports the same error - in
> this case you'd need to manually apply the snapshot. Please can you post up
> the schema of the problematic table and an explanation of the filter you're
> using.
> TIA,
> Paul Ibison
>
>