MD5 function ?

Hi,
I am quite new to T-SQL and I'm facing a problem: how can I insert a
MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
call the md5 function, but in SQL Server I failed to find it. Does MD5
function exist in T-SQL ? How can I solve this problem?
Thanks in advance!
AndreiCandreic wrote:
> Hi,
> I am quite new to T-SQL and I'm facing a problem: how can I insert a
> MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
> call the md5 function, but in SQL Server I failed to find it. Does MD5
> function exist in T-SQL ? How can I solve this problem?
> Thanks in advance!
> AndreiC
In SQL Server 2005:
INSERT INTO tbl (col)
SELECT HashBytes('MD5', 'foobar') ;
In SQL Server 2000 you'll have to create your own. .NET has classes for
encryption and hashing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can exttended stored procedure for MD5 in SQL Server 2000.
You can find a example below url.
http://www.codeproject.com/database/xp_md5.asp
"andreic"?? ??? ??:

> Hi,
> I am quite new to T-SQL and I'm facing a problem: how can I insert a
> MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
> call the md5 function, but in SQL Server I failed to find it. Does MD5
> function exist in T-SQL ? How can I solve this problem?
> Thanks in advance!
> AndreiC
>|||If this is a secured application, you might consider using SHA-style hashes,
since MD5 is not considered secure anymore. An SHA-256/384/512 hash
function is available at
http://www.sqlservercentral.com/col...oolkitpart4.asp
"andreic" <andrei.croitoriu@.gmail.com> wrote in message
news:1150048549.960214.268210@.i40g2000cwc.googlegroups.com...
> Hi,
> I am quite new to T-SQL and I'm facing a problem: how can I insert a
> MD5 hash from T-SQL in one field? Using MySQL all I had to do was to
> call the md5 function, but in SQL Server I failed to find it. Does MD5
> function exist in T-SQL ? How can I solve this problem?
> Thanks in advance!
> AndreiC
>

MCTS SQL Server 2005

What scares me about the 70-431 exam is the huge amount of complicated
T-SQL commands. Look at the syntax of any T-SQL command with all its
options; many many options, how can I remember them. Do I need to
remember all these instructions for the exam. Please let me know.If you learn it, you don't have to remember it :) On top of that, I think it
is an easier test than 7.0 or 2000. Just practice and understand why and you
will be fine.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"lrantisi" <lrantisi@.gmail.com> wrote in message
news:b833a574-8d18-4b79-9498-b84df470ec14@.e23g2000prf.googlegroups.com...
> What scares me about the 70-431 exam is the huge amount of complicated
> T-SQL commands. Look at the syntax of any T-SQL command with all its
> options; many many options, how can I remember them. Do I need to
> remember all these instructions for the exam. Please let me know.|||Agree!
70-431 was mutch easier that 70-228/70-229 and 70-028/029
but the follow ups 70-441/70-442 and 70-443/70-444
are more complicated that SQL2000 or SQL7 exams
what is good - You can optain quite easyly MCTS SQL2005
What I can recomment - for repeating, self training and getting the point of
real tests - go thorugh some preparation provider material (measureup or
something like)
But the main point is - You dont know exact syntacs or exact options for
every single thing (I don't know for example and in real life BOL is always
opened on my hand, during work, during traiining etc - unfortunately not
allowed during exams :))
You have to know and understand the principles of things. The structure, the
goal, the reason and the target of sql sentences.
As I remember - there wasn't so much in 431 about performance stuff
With bests
Henn
PS! Let me know when You pass the 431 :)
"jason" <jason-r3move@.statisticsio.com> kirjutas sõnumis
news:93A5B69C-EA5B-4269-9C70-6D3B7EDE0E5F@.microsoft.com...
> If you learn it, you don't have to remember it :) On top of that, I think
> it is an easier test than 7.0 or 2000. Just practice and understand why
> and you will be fine.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "lrantisi" <lrantisi@.gmail.com> wrote in message
> news:b833a574-8d18-4b79-9498-b84df470ec14@.e23g2000prf.googlegroups.com...
>> What scares me about the 70-431 exam is the huge amount of complicated
>> T-SQL commands. Look at the syntax of any T-SQL command with all its
>> options; many many options, how can I remember them. Do I need to
>> remember all these instructions for the exam. Please let me know.
>

MCTS SQL Server 2005

What scares me about the 70-431 exam is the huge amount of complicated
T-SQL commands. Look at the syntax of any T-SQL command with all its
options; many many options, how can I remember them. Do I need to
remember all these instructions for the exam. Please let me know.
If you learn it, you don't have to remember it On top of that, I think it
is an easier test than 7.0 or 2000. Just practice and understand why and you
will be fine.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"lrantisi" <lrantisi@.gmail.com> wrote in message
news:b833a574-8d18-4b79-9498-b84df470ec14@.e23g2000prf.googlegroups.com...
> What scares me about the 70-431 exam is the huge amount of complicated
> T-SQL commands. Look at the syntax of any T-SQL command with all its
> options; many many options, how can I remember them. Do I need to
> remember all these instructions for the exam. Please let me know.
|||Agree!
70-431 was mutch easier that 70-228/70-229 and 70-028/029
but the follow ups 70-441/70-442 and 70-443/70-444
are more complicated that SQL2000 or SQL7 exams
what is good - You can optain quite easyly MCTS SQL2005
What I can recomment - for repeating, self training and getting the point of
real tests - go thorugh some preparation provider material (measureup or
something like)
But the main point is - You dont know exact syntacs or exact options for
every single thing (I don't know for example and in real life BOL is always
opened on my hand, during work, during traiining etc - unfortunately not
allowed during exams )
You have to know and understand the principles of things. The structure, the
goal, the reason and the target of sql sentences.
As I remember - there wasn't so much in 431 about performance stuff
With bests
Henn
PS! Let me know when You pass the 431
"jason" <jason-r3move@.statisticsio.com> kirjutas snumis
news:93A5B69C-EA5B-4269-9C70-6D3B7EDE0E5F@.microsoft.com...
> If you learn it, you don't have to remember it On top of that, I think
> it is an easier test than 7.0 or 2000. Just practice and understand why
> and you will be fine.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "lrantisi" <lrantisi@.gmail.com> wrote in message
> news:b833a574-8d18-4b79-9498-b84df470ec14@.e23g2000prf.googlegroups.com...
>
sql

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

Maximum # of tables in a T-SQL query

KB article 828269, and its companion 818406, say that that they increase
the allowable number of tables and views references in a T-SQL query
from 256 to 260.
Is this for real, or am I missing something here? This hotfix touches a
whole slew of DDLs and other parts of SQL, and only gives you the
ability to use 4 additional tables in a query?
The article says this:
"The maximum number of tables that can be referred in a SELECT Transact-
SQL statement is 256. This limit is documented under the "Maximum
Capacity Specifications" section of the SQL Server Books Online. This
limit includes views and the tables that are referenced in the SELECT
statement. Also, the limit includes the tables and the views that are
referenced by a view that is included in the query. Therefore, if a
table or a view is referenced more than one time in the query, each
reference to the table or the view (or the tables and views that are
referenced in the view) counts against this limit.
With this fix, the maximum limit of the number of tables or views that
are referenced in the query is increased to 260. Therefore, you may
still receive the error message that is mentioned in the "Symptoms"
section of this article."
It seems like I am missing something important; it's a little odd to
make a big fix for a really small improvement.
Thanks.
David WalkerHi David,
You are really a good observer to the KB articles and you are right that
the improvement of the limitation of the numbers of referenced tables and
views from 256 to 260 is not the goal for this hotfix.
In the KB 818406, it is said 'This bug occurs in Microsoft SQL Server 2000
build 613. If you have installed a fix after you installed SQL Server 2000
Service Pack 2 (SP2), you may see this bug. ' However, before install the
fix, you will not encounter the error message described in KB 818406, which
is:
Server: Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The
maximum number of tables in a query (256) was exceeded."
When you have too many views and even when not exceeding the 256 limit.
So, this fix is to resolve this problem. When you installed the fix and you
views and tables are not beyond the 256( this time 260, not to this problem
but it maybe an unintened improvement :-) ), you will not met this problem
any more.
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi David,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if you still have some question about it besides
my answer. For any more question, please post your message here
and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.