Maximum length of return in Query Analyzer

I am executing a SELECT statement that has about 500 characters of literal characters concatenated with the contents of a field from a table. I am then storing the result to be run as dynamic SQL. I am finding that when run this as select statement in query analyzer, the last part of the literal gets truncated. When I run it as a cursor and store it in a varchar(1000) variable and print the variable everything works fine. In addition when I put the select statement in a stored procedure and return this to a ADO recordset, the resultset is fine as well. But running the stored procedure in query analyzer truncates the results as well. The issue seems to be getting the results of the SELECT in query analyzer. Even running the stored procedure in the SQL area of Enterprise Manager returns a proper result. Has anyone heard of a maximum return from a select in query analyzer?Yes, check out: Tools, Options, the Results-tab. "Maximum characters per column".|||Yes, and I should probably post a FAQ entry for it.

1. Shift-Ctrl-o for options
2. Click the Results tab
3. Near the middle, Maximum characters per column..

-PatP|||Manj Tak
Danke Sha
merci beaucoup
mucho gracias|||Yes, and I should probably post a FAQ entry for it.

-PatP

Can you put one on there for making DBA coffee?

1. 4 heaping tbsp coffee grounds/8oz water
2. Pinch of salt
3. Add water tapped from boiler/radiator
4. Brew in pot that's never been washed using yesterday's filter
5. Serve in cup that's never been washed

Thanks,

hmscott

Maximum length of Datatype text...

Hello,
I see that the max length of datatype text is some where abouts 2
million characters.
My question is, when I conenct the MSDE database to SQL Enterprise
administrator, and look at the table design, the length of the data
reads "16"
It's quite obvious this doesn't mean 16 characters... what does it
mean? 16 bytes of information? if so, that's an insanely large
amount; but that's okay. Right now, just to be safe, I've limited the
input to 200 characters. After you finish snickering to yourself, how
many characters does this value of 16 equate out to? 2 ba-gillion?
Thank!
Matt
On Wed, 11 Jul 2007 13:45:52 -0700, Matt Brown - identify wrote:

>Hello,
>I see that the max length of datatype text is some where abouts 2
>million characters.
Hi Matt,
More, actuallly. 2Gb of characters, meaning well over 2 billion
characters.

>My question is, when I conenct the MSDE database to SQL Enterprise
>administrator, and look at the table design, the length of the data
>reads "16"
That's the length of the pointer.
For a text column, the only thing stored in the regular storage area for
the table is a 16-byte pointer. This pointer points to the start of the
structures that store the real information, up to 2Gb. The sice of this
information is not reported in Enterprise Manager.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 11, 6:46 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 11 Jul 2007 13:45:52 -0700, Matt Brown - identify wrote:
>
> Hi Matt,
> More, actuallly. 2Gb of characters, meaning well over 2 billion
> characters.
>
> That's the length of the pointer.
> For a text column, the only thing stored in the regular storage area for
> the table is a 16-byte pointer. This pointer points to the start of the
> structures that store the real information, up to 2Gb. The sice of this
> information is not reported in Enterprise Manager.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
So... Hugo... you're saying I've got relatively no limit aka a 2GB
limit per-data type entry? like 200 characters is a comical limit? I
can more like make it 2000 without a problem?
|||On Thu, 12 Jul 2007 03:34:21 -0000, Matt Brown - identify wrote:

>So... Hugo... you're saying I've got relatively no limit aka a 2GB
>limit per-data type entry? like 200 characters is a comical limit? I
>can more like make it 2000 without a problem?
Hi Matt,
(Sorry for the delayed reply.)
That is indeed what I am saying. I'd actually go a step further, and say
that as long as you are under the limit of 8000 bytes (that is 4000
characters for unicode text or 8000 character for ASCII text), you
should not even use the text / ntext datatypes at all. These datatypes
have many limitations, making them awkward to work with. Use varchar or
nvarchar for strings with a length of up to 8000 bytes. Onlu ise text or
text if you really need to store more characters.
Also note that in SQL Server 2005, text and ntext have been replaced by
varchar(max) and nvarchar(max), with the same length limit but without
most of the limitations. For compatibility reasons, text and ntext are
still supported - but they will be removed in a future version. For SQL
Server 2005, the advice to use varchar and nvarchar for strings up to
8000 bytes still stands, but you shoud use varchar(max) or nvarchar(max)
rather than text or ntext for longer strings.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Maximum length of an expression

Is there a maximum number of characters in an expression?

thx

Helen

There is no hard limit on the length of a single expression.

But even if there were, you could always convert that expression into a custom code or custom assembly function and then simply call the function from the original expression.

-- Robert

|||

If only that were true! Alas, my longest expressions are to provide a sub-title built of the selected values of a pile of multi-select (and other) parameters, and these require the use of such functions as Count, which cannot be called from code behind.

Debugging these things is a painful experience (as is debugging code behind) -- am I alone in wishing fervently for better debugging?

Thx

Helen

|||

Maybe you can post your expression here and I may have suggestions of how to refactor it.

-- Robert

|||

Here ya go (one of the shorter ones!):

=iif(Parameters!DepartmentIDs.Count = count(Fields!DepartmentID.Value, "Departments"),"All Departments", "Departments: " + join(Parameters!DepartmentIDs.Label,", ")) + "; " + iif(Parameters!EmployeeTypeIDs.Count = count(Fields!EmployeeTypeID.Value, "EmployeeTypes"),"All Employee Types", "Employee Types: " + join(Parameters!EmployeeTypeIDs.Label,", ")) + "; " + iif(Parameters!JobTitleIDs.Count = count(Fields!JobTitleID.Value, "JobTitles"),"All Job Titles", "Job Titles: " + join(Parameters!JobTitleIDs.Label,", ")) + "; " + iif(Parameters!VenueIDs.Count = count(Fields!VenueID.Value, "Venues"),"All Venues", "Venue 1: " + join(Parameters!VenueIDs.Label,", ")) + iif(IsNothing(Parameters!LastName.Value), "", "; Last Name Like " + Parameters!LastName.Value) + iif(IsNothing(Parameters!AgreementStartDate.Value), "", "; Agreement Date Between " + Parameters!AgreementStartDate.Value + " and " + Parameters!AgreementEndDate.Value) + iif(IsNothing(Parameters!JobStartDate.Value), "", "; Job Start Date Between " + Parameters!JobStartDate.Value + " and " + Parameters!JobEndDate.Value)

I realize that I could shorten this beast by creating a function to build the label strings, one of the parameters being the count of the fields (since there appears to be no way of calling Count from Code behind), so I would not have to repeat the logic for each multi-select parameter.

The way I debug these things is to build them section by section, using notepad to add each section and copying it to the expression, then going to preview mode to see if it works. This works (slowly!) unless there is a little bombshell awaiting me -- the report prints fine from Report Designer preview mode, but chokes when I deploy it and open it from Report Manager -- happily this doesn't happen too often.

Maximum length for nvarchar in SQL2000

Hi,

I am using SQLSERVER2000.

When storing data in to database ,it is taking/storing only 255 characters for all the datatypes like nvarchar,nchar,char,ntext,text, etc...

I need to increase the maximum length of a field which takes 1000 characters & more.

I already increased the field length to 2000, but it is taking/storing only 255 characters.

Please help me in this............

It sounds like the parameters in your data access code are set to 255. Try checking them.

|||

I put 2000 characters for the parameter in the access code also.

|||

Why don't you post the client-side code you are using? Somewhere the data is being truncated, we just have to find out where.

Don

maximum column_name length

i searched but could not find
please help
what is the maximum characters allowed for a column name?In SQL Server, all database object names (including columns) are called
identifiers. See the Books Online topic "Identifiers" in which it states the
following:
"Both regular and delimited identifiers must contain from 1 through 128
characters. For local temporary tables, the identifier can have a maximum of
116 characters."
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:u5jqCed%23FHA.2384@.TK2MSFTNGP15.phx.gbl...
>i searched but could not find
> please help
> what is the maximum characters allowed for a column name?
>|||ty for your answer|||Of course, any column name > 30 characters is assumed to be the result of a
stuck keyboard key.
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:uk2yUyd%23FHA.2300@.TK2MSFTNGP10.phx.gbl...
> ty for your answer
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.
Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
http://www.aspfaq.com/
(Reverse address to reply.)
|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>
>.
>
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
>)
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>
|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...

> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...[vbcol=seagreen]
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
> message
> define the maximum
> limiting the maximum
> width... In SQL Server
> could use a trigger or
|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> Not that I can remember...
>
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
--
http://www.aspfaq.com/
(Reverse address to reply.)|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.
>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>> Hello:
>> How do you set the maximum characters per column in SQL
>> Server 6.5? Also, how do you specify this using T-SQL in
>> 7.0 and 2000? Thanks.
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.
>--Original Message--
>> How do you set the maximum characters per column in SQL
>> Server 6.5? Also, how do you specify this using T-SQL in
>> 7.0 and 2000? Thanks.
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
>)
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.
>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>> How do you set the maximum characters per column in SQL
>> Server 6.5? Also, how do you specify this using T-SQL in
>> 7.0 and 2000? Thanks.
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...
> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
> >--Original Message--
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> >> How do you set the maximum characters per column in SQL
> >> Server 6.5? Also, how do you specify this using T-SQL in
> >> 7.0 and 2000? Thanks.
> >
> >You can use the CREATE TABLE or ALTER TABLE statement to
> define the maximum
> >characters stored per column. If you are talking about
> limiting the maximum
> >character length to something less than the column
> width... In SQL Server
> >6.5 you could use a trigger, in SQL Server 7/2000 you
> could use a trigger or
> >a constraint.
> >
> >Steve
> >
> >
> >.
> >|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> > I was wondering if there is an
> > equivalent option under 6.5.
> Not that I can remember...
>
> > And also, its T-SQL
> > equivalent in setting this option using T-SQL.
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...
> > Sorry, I should have been more articulate.
> >
> > I am using QA and would like to limit the # of characters
> > displayed for each field/column.
> >
> > In 7 & 2K, I can set this option under
> > Tools/Options/Results tab. I was wondering if there is an
> > equivalent option under 6.5. And also, its T-SQL
> > equivalent in setting this option using T-SQL.
> >
> > Thanks.
> >
> > >--Original Message--
> > >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> > >> How do you set the maximum characters per column in SQL
> > >> Server 6.5? Also, how do you specify this using T-SQL in
> > >> 7.0 and 2000? Thanks.
> > >
> > >You can use the CREATE TABLE or ALTER TABLE statement to
> > define the maximum
> > >characters stored per column. If you are talking about
> > limiting the maximum
> > >character length to something less than the column
> > width... In SQL Server
> > >6.5 you could use a trigger, in SQL Server 7/2000 you
> > could use a trigger or
> > >a constraint.
> > >
> > >Steve
> > >
> > >
> > >.
> > >
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
http://www.aspfaq.com/
(Reverse address to reply.)|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
> )
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...

> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a60
1280a@.phx.gbl...[vbcol=seagreen]
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
>
> message
> define the maximum
> limiting the maximum
> width... In SQL Server
> could use a trigger or|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> Not that I can remember...
>
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx
.gbl...
>