473,326 Members | 2,136 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Repeatable error, with script, with code

Hello

I have spent the last weeks trying to solve this problem but with no
luck at all, I have a piece of code that looks like this
declare @bestnr int,
@artnr varchar(25),
@journalnrrow int,
@bestlevant decimal,
@vb_inpris money

set @bestnr = 33434
set @artnr = '1441'
set @journalnrrow = 11
set @bestlevant = 50
set @vb_inpris = 10
insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
bestlevantextqty)
values (@bestnr, @artnr, @journalnrrow, @bestlevant, @vb_inpris,
@bestlevant)

This code works just fine in query analyzer, but in SQL server agent
it does not work it creates an error message about string or bnary
data would be truncated, Simon suggested this has to do with
ansi_warnings being OFF in query analyser and ON when SQL srver agent
makes its connection.

I have scripted my entire DB at
http://donald.fruitsalad.org/microsoft/entiredb.sql

That script creates all the deps and tables and triggers for our DB,
any help would be appreciated, I just don't know where to turn, we
have hired a consultant but that did not help either as he could not
solve the problem.

If anyone know a good sql consultant in sweden that might have a clue
about this, please let us know we are getting pretty desperate.

Regards

Matt
Jul 20 '05 #1
1 2262
On 18 Apr 2004 10:42:00 -0700, Matt wrote:
Hello

I have spent the last weeks trying to solve this problem but with no
luck at all, I have a piece of code that looks like this
declare @bestnr int,
@artnr varchar(25),
@journalnrrow int,
@bestlevant decimal,
@vb_inpris money

set @bestnr = 33434
set @artnr = '1441'
set @journalnrrow = 11
set @bestlevant = 50
set @vb_inpris = 10
insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
bestlevantextqty)
values (@bestnr, @artnr, @journalnrrow, @bestlevant, @vb_inpris,
@bestlevant)

This code works just fine in query analyzer, but in SQL server agent
it does not work it creates an error message about string or bnary
data would be truncated, Simon suggested this has to do with
ansi_warnings being OFF in query analyser and ON when SQL srver agent
makes its connection.
Hi Matt,

To begin with: Simon (Hayes?) is correct. The reason that you see this
different behaviour is due to the settings Simon mentioned. These
settings are provided for historic reasons.

Previous versions of SQL Server had the feature of trimming strings
that didn't fit the declared length of a variable or column without
notice. For SQL Server 7.0, Microsoft wanted to certify as compliant
with the SQL-92 standard. One of the things this standard proscribed
was that trying to store a string in a columns with a shorter length
is an error condition. In order to provide backwards compatibility,
Microsoft supplied option that allow users to select the amount of
ANSI compliancy. The behaviour on string truncation is governed by the
ANSI_WARNINGS setting.

Try the following in Query Analyzer to see what I mean:

drop table test
go
create table test (try# int, shorttext varchar(6))
go
set ansi_warnings off
insert into test select 1, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
set ansi_warnings on
insert into test select 2, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
select * from test
go
I have scripted my entire DB at
http://donald.fruitsalad.org/microsoft/entiredb.sql
Boy, am I glad I'm no longer on a dialup connection - almost 100MB!!
When I saw how long the download would take, I assumed you had lots of
inserts embedded in the script, but I now see that it's just the DDL.
You sure make some pretty complex databases.... :-|

That script creates all the deps and tables and triggers for our DB,
any help would be appreciated, I just don't know where to turn, we
have hired a consultant but that did not help either as he could not
solve the problem.
I killed the osql process that executed your script after more than 30
minutes, as I've already found the cause (though it's absolutely
possible that there are more culprits).

This line is taken from the create table statement for bpl:
[perssign] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NOT NULL ,

This column has a default, specified in the (old and proprietary)
Transact-SQL syntax:

create default DefDbLoginId as convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
GO
(...)
EXEC sp_bindefault N'[dbo].[DefDbLoginId]', N'[bpl].[perssign]'
GO

The default results in a varchar(30) value. You attempt to store this
in a varchar(6) column. That's why the error gets raised if the ANSI
warnings option is set to on.

Some ways to get around this:
a) Change the default DefDbLoginId - replace "varchar(30)" with
"varchar(6)" in the convert function call. (ANSI standards does permit
truncation of strings in explicit conversions).
b) Make sure you always run your code with ANSI warnings set to off.
c) Change the maximum length of bpl.perssign to varchar(30).

(*) Note on ANSI (and other) settings: a default for these settings
can be set per database. This default can be overriden for each
connection. Many tools (like Query Analyzer) will do so - the settings
QA usses for each connection can be found (and changed) via menu
option Query / Currenc Connection Properties and the defaults for new
connection via Tools / Options / Connection Properties. Within the
connection, the setting can be flipped as often as wanted with the SET
command (like I did in the example above).

If anyone know a good sql consultant in sweden that might have a clue
about this, please let us know we are getting pretty desperate.


In Sweden? No, sorry. I only know SQL consultants in the Netherlands.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Wayno | last post by:
My php logs are coming up empty. I have done all I can think of, and all that made sense to me. Can someone take a look at my php.ini please and tell me what you think may be the problem. I...
1
by: Matt | last post by:
Hello I have spent the last weeks trying to solve this problem but with no luck at all, I have a piece of code that looks like this declare @bestnr int, @artnr varchar(25), @journalnrrow...
1
by: Jurrie | last post by:
Hi all, I use apaches FOP to translate an XML-document into a PDF-document. I use repeatable-page-master-alternitives, but FOP gives some strange errors: Unsupported element encountered:...
0
by: htmlgeek | last post by:
I've beaten my head againts this for two weeks and would appreciate any help. Am running Dreamweaver 2004 MX asp pages with MS Access 2000 and then 2003 and received same problems! I've rebuilt...
5
by: Bjorn Sagbakken | last post by:
Hello I have just migrated from VS 2003 to VS 2005, and .NET framework 1.1 to 2.0 I am at the end of debugging and fixing stuff. Now there is one error I just cannot find a solution to: On...
0
by: ashokingroups | last post by:
One interivewer has asked this question to me: What is repeatable inheritance in dotnet? Can anyone answer this? :)
1
by: brian.j.parker | last post by:
One of the developers where I work found that some of his code would regularly cause index corruption in SQL Server 2000, post-sp4. @@version is 8.00.2050 and I can repeat this, so I can't...
10
by: happyse27 | last post by:
Hi All, I got this apache errors(see section A1 and A2 below) when I used a html(see section b below) to activate acctman.pl(see section c below). Section D below is part of the configuration...
15
by: Lawrence Krubner | last post by:
Does anything about this script look expensive, in terms of resources or execution time? This script dies after processing about 20 or 25 numbers, yet it leaves no errors in the error logs. This is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.