asked on

SQL 7 datetime stored procedure parameter

I'm having a problem passing a datetime parameter to an SQL 7 stored procedure. I get an ODBC error - "Error converting datatype varchar to datetime". The string is "01/01/2000 00:23:28.578"

I tried passing it as a string '2000.01.01 00:23:28.578' and using CAST ... AS DATETIME without luck too. Tested the CAST and INSERT in Query Analyzer and they looked fine.

Delphi help, manual and D5 Developer's guide were no help.

-- Delphi 5 Code --

StoredProc1.Params[0].AsDateTime :=
StrToDateTime(spDate+' '+sTime);
StoredProc1.Params[1].AsString :=
sServer;
StoredProc1.Prepare;
StoredProc1.ExecProc;

-- SQL 7 Stored Procedure --

CREATE PROCEDURE [usp_InsertStat]
      @itime datetime,
      @server varchar(15)
AS
INSERT INTO stats (itime, server) VALUES (
      @itime,
      @server)

Can anyone help me out?

Thanks,

Scott

ASKER CERTIFIED SOLUTION

NetoMan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrDelphi

I ran into the same problem a while back and ended up running the entire thing through DTS , making the conversion in VBScript during the transformation.

Good luck!!

sfb

ASKER

NetoMan -
I was planning to try your suggestion on my home computer (Win2000), unfortunately SQL Server is saying it is corrupted and I will need to reinstall.
I brought my work computer (notebook) home tonight, so I'll try soon.

DrDelphi -
I'm using a memorystream to rip through a large number of multi-megabyte, variable length records. A fair bit of logic and manipulation needs to take place. DTS may be able to handle it, but I don't think as quickly. ?

I have the program using dynamic SQL Insert statements, but I want to try stored procedures to compare speed. Besides, I should know how.<g>

Scott

sfb

ASKER

You forced me to take a closer look at how Params was indexed. Looking at the Params in Object inspector I found that [0] in SQL 7 is a 'RETURN_VALUE'.

I switched to .ParamByName('@itime').AsDateTime and everything works well now.

Thanks,

Scott