Why should I get this error message "Incorrect parameter count in the call to native function 'DATEDIFF'"

Toad Data Point
1

I am relatively new to Toad Data Point and trying to report by joining two Microsoft SQL tables on two separate servers. One table is on an SQL Server 2005 being joined to another table on an SQL Server 2008 R2. When I Google this error message it appears that Toad thinks I am using MariaDB that requires only 2 parameters. Not sure what I am doing wrong. DATEDIFF as used here works fine when used from within SQL Management Studio with the 3 parameters. I had a similar issue with using GETDATE().

Here is the query.

SELECT

ClerkDocument.DateSentToClerksOffice

, ClerkDocument.ClerksOfficeSentTo

, ClerkDocument.CaseNumber

, ClerkDocument.NPLastName

, ClerkDocument.DateReturned

, ALE_CBU_CASE.ALE_CASE_ID

, ALE_CBU_CASE.ALE_ACTV_TYPE_CD

FROM

11.21.80.22\kcdcss (jrizos), EmergencyContact.EmergencyContact.dbo.ClerkDocument ClerkDocument

INNER JOIN

11.21.80.10 (jrizos), LocalDR.LocalDR.dbo.ALE_CBU_CASE ALE_CBU_CASE

ON (ClerkDocument.CaseNumber = cast(ALE_CBU_CASE.ALE_CASE_ID as char))

WHERE DATEDIFF(Month,ClerkDocument.DateSentToClerksOffice,CURRENT_TIMESTAMP)=1

Any advise will be much appreciated.

2

Try m instead of Month both should work but if ClerkDocument is the 2005 server, I’m not sure of the syntax for that one.

3

Thanx Greg for your suggestion.

I tried it and till get the error "Incorrect parameter count in the call to native function 'DATEDIFF'". The DATEDIFF format I am using works on both SQL Server 2008 R2 and SQL Server 2005 using SQL Management Studio. Toad appears to think I am using some other SQL Data Source.

CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.

"GregDavis11009" bounce-GregDavis11009@toadworld.com 8/27/2015 8:47 AM >>>

RE: Why should I get this error message "Incorrect parameter count in the call to native function 'DATEDIFF'"

Reply by GregDavis11009
Try m instead of Month both should work but if ClerkDocument is the 2005 server, I not sure of the syntax for that one.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

4

Check your Toad connection fro the 2005 Sql Server in the Navigation Manager. Is it under the Microsoft SQL server section? or is it a different kind of connection like ODBC?

5

Hi Greg,

Under the Navigation Manager both connections are under the Microsoft SQL Server section. In addition, under the Advanced tab for each connection, both have an ODBC Driver of SQL Server for creating cross connection queries. Still have the problem.

Thanx again for your advise.

CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.

"GregDavis11009" bounce-GregDavis11009@toadworld.com 8/27/2015 2:24 PM >>>

RE: Why should I get this error message "Incorrect parameter count in the call to native function 'DATEDIFF'"

Reply by GregDavis11009
Check your Toad connection fro the 2005 Sql Server in the Navigation Manager. Is it under the Microsoft SQL server section? or is it a different kind of connection like ODBC?

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

6

If you can run the 2005 server query separately with the dateDiff function then I think you have found a bug in the cross conection query. Run this on one connection:

SELECT

ClerkDocument.DateSentToClerksOffice

, ClerkDocument.ClerksOfficeSentTo

, ClerkDocument.CaseNumber

, ClerkDocument.NPLastName

, ClerkDocument.DateReturned

FROM EmergencyContact.dbo.ClerkDocument ClerkDocument

WHERE DATEDIFF(Month,ClerkDocument.DateSentToClerksOffice,CURRENT_TIMESTAMP)=1

7

I ran the query below on both SQL Server 2008R2 (test) and SQL Server 2005 (production) and the query ran with no problem using DATEDIFF(3 params) on each. I also replaced CURRENT_TIMESTAMP with Getdate() and it also worked as expected. It seems that when these are used in a cross query connection, Toad thinks the SQL being used is not MS SQL.

Not sure where to take it from here.

Thanx again for all your help.

Tony

CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.

"GregDavis11009" bounce-GregDavis11009@toadworld.com 8/27/2015 5:01 PM >>>

RE: Why should I get this error message "Incorrect parameter count in the call to native function 'DATEDIFF'"

Reply by GregDavis11009
If you can run the 2005 server query separately with the dateDiff function then I think you have found a bug in the cross conection query. Run this on one connection:

SELECT

ClerkDocument.DateSentToClerksOffice

, ClerkDocument.ClerksOfficeSentTo

, ClerkDocument.CaseNumber

, ClerkDocument.NPLastName

, ClerkDocument.DateReturned

FROM EmergencyContact.dbo.ClerkDocument ClerkDocument

WHERE DATEDIFF(Month,ClerkDocument.DateSentToClerksOffice,CURRENT_TIMESTAMP)=1

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

8

Hi Tony try using the MySQL DateDiff function in the query if it involves a cross query I see you are getting too many parameters which would point to this as the MySQL syntax only requires two dates and not a descriptor for the part of the date that you have in your SQL syntax - this only gives you days however. If you are wanting to look specifically for months you may want to use TIMESTAMPDIFF(MONTH, ‘2012-05-05’, ‘2012-06-04’) as the function which allows for a descriptor. I have not had chance to test so please let me know how you get on.

Regards

Peter

9

Hi Peter,

I followed your suggestion and used TIMESTAMPDIFF in place of DATEDIFF. First in a simple MS SQL query and, as expected, it was not recognized as a built-in function. When I tried in a Cross Connection Query I get the following error "You have an error in your SQL syntax; check the manual that corresponds to your Cross Query server version for the right syntax to use near 'ClerkDocument.Month, ClerkDocument.DateSentToClerksOffice, ' at line 14". Line 14 is "WHERE TIMESTAMPDIFF(Month,ClerkDocument.DateSentToClerksOffice,CURRENT_TIMESTAMP)=1". It appears that from the error message "Month" is viewed as a column in the ClerkDocument table.

From doing research of the various SQLs I could probably work around this but it's perplexing that Toad seems to not know what functions to accept with MS SQL in a Cross Connection Query.

Thank you for your suggestion.

Tony

CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.

"Peter Evans" bounce-Peter_Evans@toadworld.com 8/28/2015 10:15 AM >>>

RE: Why should I get this error message "Incorrect parameter count in the call to native function 'DATEDIFF'"

Reply by Peter Evans
Hi Tony try using the MySQL DateDiff function in the query if it involves a cross query I see you are getting too many parameters which would point to this as the MySQL syntax only requires two dates and not a descriptor for the part of the date that you have in your SQL syntax - this only gives you days however. If you are wanting to look specifically for months you may want to use TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04') as the function which allows for a descriptor. I have not had chance to test so please let me know how you get on.

Regards

Peter

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

10

You hit a cross connection query bug. You need one the of the amazing Toad experts that troll these forums to weigh in on this one. This is going to need a change request.

11

A work around is to pull the data you need from one server on to the other. You can set this up with Toad automation and the import wizzard and do a direct query to table import, no need for export to a file and then import from the file (one write instead of two). I would import which ever query is the faster/smallest. Then your next step in the automation would be the main query joining to your imported data table.

12

Another work around that works is to create a view of the table on the first server (in this case, the SQL Sever 2005) then use the view to join to the table on the other server (SQL Server 2008R2). DATEDIFF and GETDATE() both work.

This does not seem to use the Cross Connection Query Builder.

Thanx again for your suggestions.

CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.

"GregDavis11009" bounce-GregDavis11009@toadworld.com 8/31/2015 7:55 AM >>>

RE: Why should I get this error message "Incorrect parameter count in the call to native function 'DATEDIFF'"

Reply by GregDavis11009
A work around is to pull the data you need from one server on to the other. You can set this up with Toad automation and the import wizzard and do a direct query to table import, no need for export to a file and then import from the file (one write instead of two). I would import which ever query is the faster/smallest. Then your next step in the automation would be the main query joining to your imported data table.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

13

Sorry for the late response. X-Query is actually run on the local hub which is MySql-based engine. That is why X-Queries should conform with MySql syntax. That is why DATEDIFF with 3 params is producing the error.

Igor.

14

Okay, thanx Igor. That is good to know.

Tony

CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.

"Igor Manokhin" bounce-IgorM@toadworld.com 9/4/2015 12:43 PM >>>

RE: Why should I get this error message "Incorrect parameter count in the call to native function 'DATEDIFF'"

Reply by Igor Manokhin
Sorry for the late response. X-Query is actually run on the local hub which is MySql-based engine. That is why X-Queries should conform with MySql syntax. That is why DATEDIFF with 3 params is producing the error.

Igor.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.