We help IT Professionals succeed at work.

sql Invalid precision value

We recently (days) migrated from dev servers to new production servers.  Everything is supposed to be duplicated.  I am getting errors that I was not getting on the dev servers.  on the following query I am getting "invalid precision value";

<cfquery name="QGetTMData" datasource="TelemateTulsa" result="query">
SELECT * FROM CallData
      INNER JOIN History ON CallData.ID_VirtualResource = History.ID_CommResource AND CallData.SessionDateTime BETWEEN History.StartDate AND History.EndDate
      INNER JOIN CommResource DISA ON CallData.ID_VirtualResource = DISA.ID_CommResource
      INNER JOIN CommResource EXTN ON CallData.ID_CommResource = EXTN.ID_CommResource
      INNER JOIN Owner ON History.ID_Owner = Owner.ID_Owner
      INNER JOIN Department ON History.ID_Department = Department.ID_Department
      INNER JOIN Division ON History.ID_Division = Division.ID_Division
      INNER JOIN Company ON History.ID_Company = Company.ID_Company
      WHERE  SessionDateTime >= <cfqueryparam value="#beginDay#" cfsqltype="cf_sql_timestamp">
     AND SessionDateTime < <cfqueryparam value="#DateAdd("d", 1, beginDay)#" cfsqltype="cf_sql_timestamp">
</cfquery>

As usual, SQL error reporting is too general to pinpoint the actual cause, but the point is that this was not an issue prior to migration.  What should I be looking for?
Comment
Watch Question

Adam MenkesC# ASP.NET Developer
Top Expert 2010
Commented:
I would be looking at the timestamp (cf_sql_timestamp).
Is the field of type timestamp?

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

Author

Commented:
I would probably jump on the timestamp too. But, the problem is, there was nothing wrong with the code prior to moving to a new server. There's nothing different in the data.
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Except that timestamp fields start out null, and change when data is changed. These are binary data fields, and not really time / datetime fields.

Eliminate them from your code and see if it works.

Author

Commented:
The question is, why would one MS SQL 2005 server behave differently than another MS SQL 2005 server in this case.
Top Expert 2011

Commented:
WHAT do you mean migrated from Dev to new production servers?

(ie you normally promote from a dev environment to a production environment...
or implement into production.....
or you migrate an old environment to a new environment/release at the same "level")


what are the versions / editions/ fixpacks of sql server in dev and production?
what operating systems are yoou using?
which version of cold fusion in which environment?

Top Expert 2011

Commented:
what is the comopatability level of your database in each environment?

Author

Commented:
How do I check it?
Top Expert 2011
Commented:
cf_sql_timestamp is the correct identification for a sql datetime...

however there appears to be a known problem with sql 2005 and moving from cf 7 to 8
is this your case?

see
http://objectmix.com/cold-fusion/717989-cf_sql_timestamp.html

the other possibility perhaps is a problem with the date format...

what dates are actually giving problems ?  could it be confusing days and months?

Author

Commented:
<cfset tmdate="#dateformat(now(),"yyyy/mm/dd")#">  
<cfset beginDay = createODBCDate(tmdate)>

<cfquery name="QGetTMData" datasource="TelemateTulsa" result="query">
SELECT * FROM CallData
      INNER JOIN History ON CallData.ID_VirtualResource = History.ID_CommResource AND CallData.SessionDateTime BETWEEN History.StartDate AND History.EndDate
      INNER JOIN CommResource DISA ON CallData.ID_VirtualResource = DISA.ID_CommResource
      INNER JOIN CommResource EXTN ON CallData.ID_CommResource = EXTN.ID_CommResource
      INNER JOIN Owner ON History.ID_Owner = Owner.ID_Owner
      INNER JOIN Department ON History.ID_Department = Department.ID_Department
      INNER JOIN Division ON History.ID_Division = Division.ID_Division
      INNER JOIN Company ON History.ID_Company = Company.ID_Company
      WHERE  SessionDateTime >= "#beginDay#"    
     AND SessionDateTime < "#DateAdd("d", 1, beginDay)#"    
</cfquery>


[SQL Server]Invalid column name '{d '2010-06-11'}'.

 WHERE SessionDateTime >= "{d '2010-06-11'}" AND SessionDateTime < "{ts '2010-06-12 00:00:00'}"

Well, I've been on CF8 for some time now, so that's not the issue. But the link you posted was interesting.  I changed my code and now seem to have simple date problems.
table.png

Author

Commented:
sessiondatetime is a datetime type on a MS SQL 2000 server accessed via ODBC from CF8.
Most Valuable Expert 2015

Commented:
(no points ...)

>      WHERE  SessionDateTime >= "#beginDay#"    
>     AND SessionDateTime < "#DateAdd("d", 1, beginDay)#"    

You don't need quotes around date objects.  That's what's causing the syntax error.  just use

       <cfset beginDay = createODBCDate(now())>
       ....

       WHERE  SessionDateTime >= #beginDay#    
       AND        SessionDateTime < #DateAdd("d", 1, beginDay)#

> <cfset tmdate="#dateformat(now(),"yyyy/mm/dd")#">  
> <cfset beginDay = createODBCDate(tmdate)>

BTW:  You don't need dateFormat(). A) CreateODBCDate() already truncates the "time", which I assume is what you're after here.  B) DateFormat() is for _displaying_ dates as strings. So it's the wrong function to use here.

Author

Commented:
I am proud of you people.  I wish we could solve the original problem, but after reading the info at the link posted, it's apparent that it's an unsolved bug on Microsoft' part. And kudos to agx.
Most Valuable Expert 2015

Commented:
> I wish we could solve the original problem ...

The original error is very odd ... I use cf_sql_timestamp w/MS SQL 2005 date/time columns all the time. Can't say I've ever had that problem.  

1) Just to confirm it IS the date/time columns, does the query run successfully if you comment out those two lines?
2) Are you positive the column data type is date/time?
3) Any difference if you temporarily use now() as a test (only)?
    WHERE  SessionDateTime >= <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">
     AND SessionDateTime < <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">

4) When the "invalid precision value" error occurs, what values does the debugging output show for the two parameters?

Author

Commented:
1. Yes the query runs without the where clause.
2. Positive.
3. No difference.
4. valid dates.
Most Valuable Expert 2015

Commented:
> 4. valid dates.

Right, but what are the exact values?  Also - is the error occurring under a JDBC connection or an older ODBC connection?

Author

Commented:
2001-06-11 for both dates.

Older ODBC that comes with the Data Source Manager for Win 2003.
Most Valuable Expert 2015

Commented:
No, I meant the exact string ie {ts '2010-06-12 00:00:00'} or {ts '2010-06-12 00:00:000'} (note 3 zeroes).  I seem to remember something about milliseconds, but it was a while ago. So I'm not positive about it.  I'm wondering if it's ODBC related only.  I only use JDBC connections.  Do you get the same error when running that same query (values and everything) with a JDBC connection?

Author

Commented:
Hmmm.  Haven't tried it.  I'll get back to you.