INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
======================
Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
================================================== ======================
INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
================================================== ========================
CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
Nate
I suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.c om...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================
> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ================================================== ======================
> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ================================================== ========================
> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
====================== Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
======================================================================== INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
========================================================================== CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
NateI suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.com...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ========================================================================> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ==========================================================================> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:
> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>