weird sequal question

[fluppe689]

Hello Experts,

on top of the form
Set date to dmy
set century on
I am have a very weird problem.
I have a sql installed on my pc local
When I do the next statement
replace tmp.date with t_mp.date no problem
the statement is good because t_mp.date is recognized as a date
In a browse the t_mp i see the date as 2011/01/01

I made an exe and on the server with sqlexpress 2008 r2
when i do the same statement i get an error that the date is not a date format - data type mismatch -
In a browe i see the date as 2011-01-01

When I convert the statement to
replace with tmp_date with ctod(t_mp.date) then it works on the server but not on my local pc

It seems like on my pc he recognizes a date and on the server the date is like a string


t_mp is the result of sqlexec(handle,sql,"t_mp") where slq = select * from blablabla

I already searched for it for hours but did not find any solution

wfg,

 

[bborissov]

There is nothing toi dates here. Just in your SQL Server 2008 you use char fields OR you use new field type DATE.
You can't use DATE type with VFP when you use ODBC. You can use this only if you use ADO (CAs).

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Olaf Doschke]

You're mixing vfp dbfs and sql server here. You can't do replace with tmp_date with ctod(t_mp.date) on the sql server, you can only do that on VFP, as REPLACE is no sql command (besides ctod() being no t-sql function), the corresponding sql command is UPDATE.

What's the field definition of the date field within sql server management studio and which odbc or oledb diver do you use to connect?

Bye, Olaf.

 

[fluppe689]

Hello,

The type of the field in sql is date
I do the replace only in my cursor file
What do you mean i can not use date type when using ODBC I have that on serveral program's and that works perfectly

Wfg,

Filip

 

[Olaf Doschke]

actually all ODBC sql drivers I know do not create date fields within vfp, when the sql server field type is date. Until sql server 2008 there was only the dateTIME or equivalent field types in sql server and vfp sql passthrough only resulted in datetimes therefore. That works flawless of course, but not using the new date field type.

The driver you're using is very important, not only the sql server version.

Bye, Olaf.

 

[bborissov]

You can't use DATE type with VFP ODBC connection.
You can use it only with CAs ADO based connection.
You can use DateTime types.
All unknown type VFP handles as strings or memo.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Olaf Doschke]

related to the problem, and what bborissov is saying: VFP actually does retrieve C() fields instead of date in circumstances:

http://social.msdn.microsoft.com/Fo...l/thread/2a51aa87-2444-41d5-b17f-be52d4090b25

My finding there was, SQL Server Native Client 10.0 does return date fields as date. But waqtch out, it can introduce other problems with varchar(max) and varbinary(max) fields.

The cleanest solution actually is to use cursoradapters, as they offer the possibility to define a cursor result schema that then is enforced, as good as it can be. with sqlexec you get what SQLCOLUMNS() guesses and there are not many sqlsetprops that will influence how field types are mapped in the vfp result cursor.

Bye, Olaf.

 

[JRB-Bldr]

As Olaf says above - You're mixing vfp dbfs and sql server here.

Remember that the code which is executed within a SQLEXEC() function is the code-specific to the remote database.

Therefore you cannot use VFP SQL commands (or at least many of them) within a SQL Server backend since SQL Server uses T-SQL commands and VFP uses its own.

There are a number of the commands which are common, but some are not - especially those regarding Dates.

If you have any questions about it, then write your SQL Query commands as a string and attempt to execute them within the SQL Server Maintenace window without using VFP at all.
If the syntax is correct, you can THEN migrate them over to your VFP SQLEXEC() command.

Good Luck,
JRB-Bldr

 

[fluppe689]

Hello all experts,

I changed the field on the server in datetime and i don't have any problems anymore for the moment

But what is for my not understandable is that is worked fine on my local pc


Filip

 

[Olaf Doschke]

Well, nobody can tell without the details. It's obvious there is some difference, as results don't change randomly, and there are always manyfold differences in driver version, sql server configuration, database configuration etc, even if the connection string was the same or you actually used the same vfp program.

What I can tell though, is, that datetime always worked and so that might even be the better choice than changing to sql native client 10.0

Bye, Olaf.