FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Hi,

The error message 242 is definitely a server message, and will be produced if 
one of the elements of the date string '1/1/1900' falls out of range for its 
date part - days (1-31), months (1-12) or years, when some operation within 
the stored procedure attempts to convert that string into a date.

Which element of the date string is which is controlled by the setting of 
dateformat e.g. "SET DATEFORMAT dmy". (look that up in Books online)

The setting of dateformat is affected by setting of language, each language 
having a default DATEFORMAT setting.
e.g. SELECT * FROM MASTER..syslanguages

'1' is a valid day, month or year (2001), so it follows that the '1900' part 
is the problem, which SQL server must be regarding as either a day or a 
month. 

Check the setting of dateformat for portuguese in your database, by looking 
at MASTER..syslanguages.
If it's not 'dmy' or 'mdy', there's your problem.

Hope this helps,

Bill

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org 
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: 30 October 2009 14:22
To: FreeTDS Development Group
Subject: Re: [freetds] problem Stored Procedures MSSQL


2009/10/29 demetrius albuquerque <demetrius.albuquerque AT yahoo.com.br>:
> I'm using freeRTS+pymssql+python to work with MSQQL 2005. I use stored
> procedure to insert data in table and return a keydata. But I ever have the
> same error, my stored procedure just give a empty result. My SP is ok, I use
> this with Visual Studio and this work fine.
> When I use a procedure only to check some field on table is OK too.
> freeTDS.log is here http://paste.archlinux-br.org/1347
> Anyone know what is that?
>

Looking at logging you can note

dbutil.c:87:msgno 5703: "Changed language setting to Português."

dbutil.c:87:msgno 5701: "Changed database context to 'dbSFC'."

write.c:136:tds_put_string converting 77 bytes of "exec
SP_SFC_Numero_Serie_Posto_Ins
'1/1/1900','N','E','736382202787',78,157,1"

so your query is sent to server verbating

dbutil.c:87:msgno 242: "The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value."

server does not like the '1/1/1900', perhaps is not Portuges encoded ??

dbutil.c:87:msgno 282: "The 'SP_SFC_Turno_Producao_Get_Data' procedure
attempted to return a status of NULL, which is not allowed. A status
of 0 will be returned instead."

SP_SFC_Turno_Producao_Get_Data is called internally by
SP_SFC_Numero_Serie_Posto_Ins

dbutil.c:87:msgno 547: "The INSERT statement conflicted with the
FOREIGN KEY constraint "Refsfc_turno_producao75". The conflict
occurred in database "dbSFC", table "dbo.sfc_turno_producao", column
'tup_id_turno_producao'."

probably depending on previous error.

So the problem seems date, probably due to language settings. Are you
sure that Visual Studio use Portugues language?? You could try with
odbc escape sequence like "exec SP_SFC_Numero_Serie_Posto_Ins {d
'1900-01-01'},'N','E','736382202787',78,157,1"

freddy77
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

--------------------------------------------------------------------------
This message w/attachments (message) may be privileged, confidential or 
proprietary, and if you are not an intended recipient, please notify the 
sender, do not use or share it and delete it. The information contained in 
this e-mail was obtained from sources believed to be reliable; however, the 
accuracy or completeness of this information is not guaranteed. Unless 
specifically indicated, this message is not an offer to sell or a 
solicitation of any investment products or other financial product or 
service, an official confirmation of any transaction, or an official 
statement of Merrill Lynch. Subject to applicable law, Merrill Lynch may 
monitor, review and retain e-communications (EC) traveling through its 
networks/systems. The laws of the country of each sender/recipient may impact 
the handling of EC, and EC may be archived, supervised and produced in 
countries other than the country in which you are located. This message 
cannot be guaran teed to be secure or error-free. References to "Merrill 
Lynch" are references to any company in the Merrill Lynch & Co., Inc. group 
of companies, which are wholly-owned by Bank of America Corporation. 
Securities and Insurance Products: * Are Not FDIC Insured * Are Not Bank 
Guaranteed * May Lose Value * Are Not a Bank Deposit * Are Not a Condition to 
Any Banking Service or Activity * Are Not Insured by Any Federal Government 
Agency. Past performance is no guarantee of future results. Attachments that 
are part of this E-communication may have additional important disclosures 
and disclaimers, which you should read. This message is subject to terms 
available at the following link: http://www.ml.com/e-communications_terms/. 
By messaging with Merrill Lynch you consent to the foregoing.
--------------------------------------------------------------------------