Access text fields can be varchar, nvarchar, or text. If you are not going
to put more than 8k (IIRC) the varchar is the way to go. If you are going to
put more than 8k in the field, then text is the way you want to go. It'll
take multiple gigs of data, but they are more difficult to use than a memo
field in Access. For example, you can't do a select on a text field, and you
will need to use some built in functions to do an insert or update on a text
field. In general, it's a lot easier if you wrap text field access in a
stored procedure.
Here's a cleaned-up copy of a text insert sp I wrote a little while ago
========================================================================
IF EXISTS (select * from sysobjects where id =
object_id('<your_stored_proc_name_here>') and sysstat & 0xf = 4)
DROP PROCEDURE <your_stored_proc_name_here>
GO
CREATE PROCEDURE <your_stored_proc_name_here>
@<your_text_field_here> text,
@<your_primary_key_here> int
AS
/***************************************************************************
*******************
** Desc : This procedure is used to update the text field in
<your_db_table_here>
** It's a real buzz-kill for the users to
have their data truncated, or have CF
** (sometimes) error out and loose all of the
data. Instead of using a
** regular insert statement SQL Server needs
to use WRITETEXT to manage the text
** pointers and junk.
**
** Notes : It's possible for the pointer returned from
TEXTPTR(rec_comments) to be
** invalid by the time the write occurs,
especially if the database is
** getting hit hard, so it needs to be
wrapped in a transaction to lock the db.
**
** : This script based on the one given in
"Inside SQL Server 7.0" by Ron Soukup & Karen Delany
**
** Inputs : @<your_text_field_here> is text to be inserted.
** @<your_primary_key_here> is the
@<your_primary_key_here>_id value to match for the insert/update
**
** Outputs : None
**
** Created By : Jeff Polaski
** Date : 01/18/01
**
** Change Hist **
** Date :
** Developer :
** Description :
**
****************************************************************************
*******************/
----------------------------------------------------------------------------
-------------------
-- Set up environment, declare variables, etc...
SET NOCOUNT ON
DECLARE
@ErrNo int
,@ErrMsg varchar(255)
,@RetVal int
,@ptrval binary(16) --Declare a binary object to use as
a pointer-to-text
----------------------------------------------------------------------------
-------------------
-- Main body of the stored procedure
BEGIN TRANSACTION
SELECT @ptrval = TEXTPTR(rec_comments)
FROM <your_primary_key_here>ender
WHERE <your_primary_key_here>_id = @<your_primary_key_here>
IF ( @@ERROR <> 0 ) BEGIN
GOTO major_error_block
END
-- WRITETEXT will overwrite the value in rec_comments, so there is
no need for an update.
-- The pointer either will be NULL or it will not be NULL, so every
case should be covered in this block
IF ( @ptrval IS NOT NULL ) BEGIN
WRITETEXT <your_primary_key_here>ender.rec_comments @ptrval
@<your_text_field_here>
IF ( @@ERROR = 0 ) BEGIN
COMMIT TRANSACTION
GOTO successfull_completion
END ELSE BEGIN
ROLLBACK TRANSACTION
GOTO major_error_block
END
END ELSE BEGIN
ROLLBACK TRANSACTION
SELECT @ErrNo = 10002
SELECT @ErrMsg = 'Writetext failed because Textpointer
@ptrval is null (<your_primary_key_here>_id not in dbb?)'
GOTO minor_error_block
END
-- Just in case, if control gets to here we roll the transaction back
ROLLBACK TRANSACTION
SELECT @ErrNo = 10003
SELECT @ErrMsg = 'The text insert failed for an unknown reason'
GOTO minor_error_block
----------------------------------------------------------------------------
-------------------
-- Deal with errors
minor_error_block:
IF ( @ErrNo <> 0 ) BEGIN
SELECT @ErrNo = 50501,
@ErrMsg = 'Failed to retrieve (SQL Errno: '
+ LTRIM(STR( @RetVal )) + ')'
RAISERROR @ErrNo @ErrMsg
RETURN (1)
END
major_error_block:
IF (@@ERROR <> 0) BEGIN
SELECT @ErrMsg = 'Failed to retrieve (SQL Errno: ' +
LTRIM(STR( @RetVal )) + ')'
RAISERROR @@ERROR @ErrMsg
RETURN (@@ERROR)
END
----------------------------------------------------------------------------
-------------------
-- If we get here, the stored procedure ran successfully
successfull_completion:
RETURN (0)
----------------------------------------------------------------------------
-------------------
GO
GRANT EXECUTE ON <your_stored_proc_name_here> TO <your_role_here>
GO
========================================================
If anyone wants, I'll send them a copy, too.
Jeff Polaski
Manager, Web Services
Research & Graduate Studies
University California, Irvine
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 1:43 PM
To: CF-Talk
Subject: Datatypes
What are the equivalents of Accesses "text" and "number" datatypes in SQL
Server?
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
