Skip to Content.
Sympa Menu

freetds - RE: [freetds] MSSQL: Invalid parameter 1 specified for dateadd

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] MSSQL: Invalid parameter 1 specified for dateadd
  • Date: Mon, 10 Jan 2005 10:26:54 -0000

Jeremy,

I think your problem will be solved by removeing the quotes from around
the first parameter to dateadd.
e.g.

AND (I.CREATEDATE > DATEADD(n,-60, GETDATE())

The underlying issue is with the syntax of your dateadd statement
combined with the setting of the "set quoted_identifier" to on or off

try the following in query analyzer:

set quoted_identifier on

select DATEADD("n",-60, GETDATE())

set quoted_identifier off

select DATEADD("n",-60, GETDATE())

...as stated in the BOL entry for SET QUOTED_IDENTIFIER -

"The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server
automatically set QUOTED_IDENTIFIER to ON when connecting. This can be
configured in ODBC data sources, in ODBC connection attributes, or OLE
DB connection properties. SET QUOTED_IDENTIFIER defaults to OFF for
connections from DB-Library applications."

we conform with the spirit of this.

DBD::Sybase uses the ct-library API, and we have made that behave the
same as DB-Library, in other words we will default to set
quoted_identifier off

HTH,

Bill



-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Martin, Jeremy
Sent: 10 January 2005 10:14
To: freetds AT lists.ibiblio.org
Subject: [freetds] MSSQL: Invalid parameter 1 specified for dateadd


Hi everyone,

I'm having an odd problem with FreeTDS and was hoping someone here could
help while I still had a little hair left. ;-)

We have an older RedHat 9 server set up with FreeTDS 0.61.2 and
DBD::Sybase, running some perl scripts to connect to one of our MS SQL
servers. I have been trying to get the same perl scripts to work on a
newer CentOS 3 server (CentOS is a free clone of RedHat Enterprise 3).

FreeTDS compiles and installs ok. However as far as I can tell it's like
"--enable-msdblib" is being ignored. I can run simple queries fine, but
the existing scripts I'm trying to get working use a DATEADD command
which is giving me errors.

The query is good, I can run it manually and the old scripts are still
working fine on the RedHat 9 box. The part of the query using DATEADD is
like this: ... AND (I.CREATEDATE > DATEADD("n",-60, GETDATE())

I am doing "./configure --prefix=/usr/local/freetds --enable-msdblib
--with-tdsver=8.0" to configure FreeTDS. I tried this with 0.61.2,
0.62.4, and even the CSV snapshot from tonight freetds-0.64.dev.20050110
... After each re-install I am re-compiling DBD::Sybase and PHP (using
"--with-mssql=/usr/local/freetds" when configuring PHP)

At first I had a very hard time getting freetds to use the
/usr/local/freetds/etc/freetds.conf, I set up an entry for this server
just like on our existing RH9 box but it kept ignoring it and trying to
use port 4000 instead of port 1433 to connect. I have tried editing the
configure script and hard-coding it to use TDS version 8.0 / port 1433
by default, but once I set
FREETDSCONF=/usr/local/freetds/etc/freetds.conf in my environment I quit
having to do that.

I have even copied over the entire /usr/local/freetds/ folder from the
RH9 server onto the CentOS 3 server but it still gives this exact same
error about the 'dateadd' syntax.

I read that --enable-msdblib changes just a few things in FreeTDS, one
of them being the 'date' part syntax, I guess Sybase doesn't use "n" for
minutes. I also tried using "mi" or also 'n' or just plain n but I got
the same error doing all of those things.

It surprised me that PHP got the same exact error (using PEAR DB) as the
perl scripst were getting, but at least that has helped me narrow it
down to something in FreeTDS itself I believe.

Just tonight I also installed the "unixODBC-devel" RPM but used the same
configure options as before and it didn't help.

Thanks!!
Jeremy
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--------------------------------------------------------

If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy, retain or
redistribute it. Click here for important additional terms relating to this
e-mail. http://www.ml.com/email_terms/
--------------------------------------------------------





Archive powered by MHonArc 2.6.24.

Top of Page