|
 |
asp_databases thread: Re: data type "text" in SQL Server 2000
Message #1 by "Ken Schaefer" <ken@a...> on Wed, 10 Apr 2002 19:16:50 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Marijn.Schops@p...>
Subject: [asp_databases] Re: data type "text" in SQL Server 2000
: Hmmmz... That's only possible if I can use output parameters and return a
: recordset at the same time. Is that possible? I'm not sure?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Yes it is possible.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Also, we're having a discussion here at work : wouldn't it be better to
use
: a varchar(4096) instead?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Well, if you *know* you are storing less than 4097 characters then use
varChar (or even Char) rather than Text. Text type fields incur additional
overhead because the data isn't stored in the data page. Instead, the data
is stored in separate pages, and the database needs to look up:
a) both the page that contains the pointer to the other data pages
b) the data pages that actually contain the data.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: A collegue of mine says that a varchar(4096) still saves the complete 4096
: bytes, even if they aren't fully used.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your collegue is confused. He/She is thinking of Char/NChar data types, not
VarChar/NVarChar fields. Tell him/her to look this up in SQL Server Books
Online.
Char fields will be padded with spaces up to the defined size, so a
Char(4096) field will always be 4096 bytes. VarChar only stores the data you
actually put in there.
Cheers
Ken
Message #2 by Marijn.Schops@p... on Wed, 10 Apr 2002 09:42:59 +0200
|
|
Hmmmz... That's only possible if I can use output parameters and return a
recordset at the same time. Is that possible? I'm not sure?
Also, we're having a discussion here at work : wouldn't it be better to use
a varchar(4096) instead?
A collegue of mine says that a varchar(4096) still saves the complete 4096
bytes, even if they aren't fully used. Which means a varchar would be a big
overkill in case nothing or a short string is entered.
I say that only the used space is saved, so it would be better to use one.
Anyone can confirm any of the two (preferable mine ;p) ?
Greetings,
Marijn
----------------------------------------------------------------------------------
Marijn Schops
Software Engineer
Professional Interactive Media Centre
Wetenschapspark 5 - 3590 Diepenbeek - Belgium
Tel. +xx-xx-xx xx xx Fax. +xx-xx-xx xx xx
Email : Marijn.Schops@p...
"Ken
Schaefer" To: "ASP Databases" <asp_databases@p...>
<ken@a... cc:
atic.com> Subject: [asp_databases] Re: data type "text" in SQL Server 2000
10/04/2002
08:54
Please
respond to
"ASP
Databases"
But you are returning the field as an output parameter at the moment - why
can't you return it as part of a SELECT statement instead?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Marijn.Schops@p...>
Subject: [asp_databases] Re: data type "text" in SQL Server 2000
:
: Hmm very hard for me to use a recordset, cause I'm working with something
: like 10 tables where I'm returning data from.
: If I must do all of that in 1 SELECT statement... I doubt that it's
: possible...
:
: I'm really stuck with this problem, can't find a way to solve it :-(
:
:
--------------------------------------------------------------------------
--------
:
: Marijn Schops
: Software Engineer
:
: Professional Interactive Media Centre
: Wetenschapspark 5 - 3590 Diepenbeek - Belgium
: Tel. +xx-xx-xx xx xx Fax. +xx-xx-xx xx xx
: Email : Marijn.Schops@p...
:
:
:
: "Ken
: Schaefer" To: "ASP Databases"
<asp_databases@p...>
: <ken@a... cc:
: atic.com> Subject: [asp_databases] Re:
data type "text" in SQL Server 2000
:
: 09/04/2002
: 09:05
: Please
: respond to
: "ASP
: Databases"
:
:
:
:
:
:
: Can you return the data as a recordset rather than using output
parameters?
: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q200124
:
: The error means that you can't assign a text data type to a declared
: variable.
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <Marijn.Schops@p...>
: Subject: [asp_databases] data type "text" in SQL Server 2000
:
:
: : I'm trying to retreive a value from a field of data type text in SQL
: Server
: : 2000.
: : Here's my situation :
: :
: : I have a text field in my Clients table, called "Description". When I
: write
: : a stored procedure to get all the client information from 1 client
: : (including the Description field), I can't seem to get that Description
: : field. Here's in short what I do :
: :
: : code:
: :
:
--------------------------------------------------------------------------
: ------
: : CREATE PROC dbo.GetClientInfo
: : ...,
: : @Description text OUTPUT,
: : ...
: : AS
: : SELECT ... , @Description = [DESCRIPTION], ...
: : FROM ...
: :
:
--------------------------------------------------------------------------
: ------
: :
: : On that SELECT Line I get this error :
: : Server: Msg 409, Level 16, State 1, Procedure GetClientInfoAdmin, Line
48
: : The assignment operator operation cannot take a text data type as an
: : argument.
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
:
:
:
:
:
:
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 10 Apr 2002 16:54:55 +1000
|
|
But you are returning the field as an output parameter at the moment - why
can't you return it as part of a SELECT statement instead?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Marijn.Schops@p...>
Subject: [asp_databases] Re: data type "text" in SQL Server 2000
:
: Hmm very hard for me to use a recordset, cause I'm working with something
: like 10 tables where I'm returning data from.
: If I must do all of that in 1 SELECT statement... I doubt that it's
: possible...
:
: I'm really stuck with this problem, can't find a way to solve it :-(
:
: --------------------------------------------------------------------------
--------
:
: Marijn Schops
: Software Engineer
:
: Professional Interactive Media Centre
: Wetenschapspark 5 - 3590 Diepenbeek - Belgium
: Tel. +xx-xx-xx xx xx Fax. +xx-xx-xx xx xx
: Email : Marijn.Schops@p...
:
:
:
: "Ken
: Schaefer" To: "ASP Databases"
<asp_databases@p...>
: <ken@a... cc:
: atic.com> Subject: [asp_databases] Re:
data type "text" in SQL Server 2000
:
: 09/04/2002
: 09:05
: Please
: respond to
: "ASP
: Databases"
:
:
:
:
:
:
: Can you return the data as a recordset rather than using output
parameters?
: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q200124
:
: The error means that you can't assign a text data type to a declared
: variable.
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <Marijn.Schops@p...>
: Subject: [asp_databases] data type "text" in SQL Server 2000
:
:
: : I'm trying to retreive a value from a field of data type text in SQL
: Server
: : 2000.
: : Here's my situation :
: :
: : I have a text field in my Clients table, called "Description". When I
: write
: : a stored procedure to get all the client information from 1 client
: : (including the Description field), I can't seem to get that Description
: : field. Here's in short what I do :
: :
: : code:
: :
: --------------------------------------------------------------------------
: ------
: : CREATE PROC dbo.GetClientInfo
: : ...,
: : @Description text OUTPUT,
: : ...
: : AS
: : SELECT ... , @Description = [DESCRIPTION], ...
: : FROM ...
: :
: --------------------------------------------------------------------------
: ------
: :
: : On that SELECT Line I get this error :
: : Server: Msg 409, Level 16, State 1, Procedure GetClientInfoAdmin, Line
48
: : The assignment operator operation cannot take a text data type as an
: : argument.
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
:
:
:
:
:
:
Message #4 by Marijn.Schops@p... on Tue, 9 Apr 2002 09:20:23 +0200
|
|
Hmm very hard for me to use a recordset, cause I'm working with something
like 10 tables where I'm returning data from.
If I must do all of that in 1 SELECT statement... I doubt that it's
possible...
I'm really stuck with this problem, can't find a way to solve it :-(
----------------------------------------------------------------------------------
Marijn Schops
Software Engineer
Professional Interactive Media Centre
Wetenschapspark 5 - 3590 Diepenbeek - Belgium
Tel. +xx-xx-xx xx xx Fax. +xx-xx-xx xx xx
Email : Marijn.Schops@p...
"Ken
Schaefer" To: "ASP Databases" <asp_databases@p...>
<ken@a... cc:
atic.com> Subject: [asp_databases] Re: data type "text" in SQL Server 2000
09/04/2002
09:05
Please
respond to
"ASP
Databases"
Can you return the data as a recordset rather than using output parameters?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q200124
The error means that you can't assign a text data type to a declared
variable.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Marijn.Schops@p...>
Subject: [asp_databases] data type "text" in SQL Server 2000
: I'm trying to retreive a value from a field of data type text in SQL
Server
: 2000.
: Here's my situation :
:
: I have a text field in my Clients table, called "Description". When I
write
: a stored procedure to get all the client information from 1 client
: (including the Description field), I can't seem to get that Description
: field. Here's in short what I do :
:
: code:
:
--------------------------------------------------------------------------
------
: CREATE PROC dbo.GetClientInfo
: ...,
: @Description text OUTPUT,
: ...
: AS
: SELECT ... , @Description = [DESCRIPTION], ...
: FROM ...
:
--------------------------------------------------------------------------
------
:
: On that SELECT Line I get this error :
: Server: Msg 409, Level 16, State 1, Procedure GetClientInfoAdmin, Line 48
: The assignment operator operation cannot take a text data type as an
: argument.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #5 by "Ken Schaefer" <ken@a...> on Tue, 9 Apr 2002 17:05:23 +1000
|
|
Can you return the data as a recordset rather than using output parameters?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q200124
The error means that you can't assign a text data type to a declared
variable.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Marijn.Schops@p...>
Subject: [asp_databases] data type "text" in SQL Server 2000
: I'm trying to retreive a value from a field of data type text in SQL
Server
: 2000.
: Here's my situation :
:
: I have a text field in my Clients table, called "Description". When I
write
: a stored procedure to get all the client information from 1 client
: (including the Description field), I can't seem to get that Description
: field. Here's in short what I do :
:
: code:
: --------------------------------------------------------------------------
------
: CREATE PROC dbo.GetClientInfo
: ...,
: @Description text OUTPUT,
: ...
: AS
: SELECT ... , @Description = [DESCRIPTION], ...
: FROM ...
: --------------------------------------------------------------------------
------
:
: On that SELECT Line I get this error :
: Server: Msg 409, Level 16, State 1, Procedure GetClientInfoAdmin, Line 48
: The assignment operator operation cannot take a text data type as an
: argument.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #6 by Marijn.Schops@p... on Mon, 8 Apr 2002 14:31:43 +0200
|
|
I'm trying to retreive a value from a field of data type text in SQL Server
2000.
Here's my situation :
I have a text field in my Clients table, called "Description". When I write
a stored procedure to get all the client information from 1 client
(including the Description field), I can't seem to get that Description
field. Here's in short what I do :
code:
--------------------------------------------------------------------------------
CREATE PROC dbo.GetClientInfo
...,
@Description text OUTPUT,
...
AS
SELECT ... , @Description = [DESCRIPTION], ...
FROM ...
--------------------------------------------------------------------------------
On that SELECT Line I get this error :
Server: Msg 409, Level 16, State 1, Procedure GetClientInfoAdmin, Line 48
The assignment operator operation cannot take a text data type as an
argument.
I tried this, which works :
code:
--------------------------------------------------------------------------------
DECLARE @Val varbinary(16)
SELECT @Val = TEXTPTR([DESCRIPTION])
FROM [20_CLIENTS]
WHERE [ID] = '121'
READTEXT [20_CLIENTS].[DESCRIPTION] @Val 0 0
--------------------------------------------------------------------------------
But I can't seem to put the result in READTEXT in a variable, it seems to
return some kind of recordset and the second 0 in READTEXT means that it
"only" reads 4kb bytes and my text field could contain more data.
Anyone has an idea on how to solve this?
I hope I explained my problem clearly, English isn't my native language so
it isn't easy to explain something like that...
----------------------------------------------------------------------------------
Marijn Schops
Software Engineer
Professional Interactive Media Centre
Wetenschapspark 5 - 3590 Diepenbeek - Belgium
Tel. +xx-xx-xx xx xx Fax. +xx-xx-xx xx xx
Email : Marijn.Schops@p...
|
|
|
View Cart
