Sql Server

[Giorgio Bravi]

I'm planning a migration from 5.2 to 7 of a large application (ERP) with SQL Server
Until now I used OleDB but in recent news Microsoft says it will be deprecated.
So we must go back to ODBC?
Does anyone have tips on this?
Thank you.

Giorgio

[FRBhote]

If all your connects are done by a single function, then I wouldn't worry about it - when the time comes you can modify just that function.

[Gideon Schulter]

Hi Giorgio,

Microsoft dropped OLEDB support in the SQLServer Native client already.

If you want to keep using OLEDB from TeamDeveloper to a SQLServer 2014 or 2016 instance, you will need to install the Microsoft SQL Server Native Client 2012.
Both SQL Server 2014 and 2016 are compatible to the SQLServer 2012 Native client.

See: https://msdn.microsoft.com/nl-nl/library/cc280510.aspx

If your source implements SqlCreateSession/SqlCreateStatement/SqlCommitSession, it would be wise to start changing your code to just using SqlConnect.

[Giorgio Bravi]

Many tanks to all

After some test i can confirm that using new connettivity data access speed up to 50% better

Giorgio

[gigit]

Hi George,
I connect to all versions of SqlServer with all versions of TD with this in the ODBC session of sql.ini:

REMOTEDBNAME=AGEPROS,Driver=Sql Server;Server=GIGI01\SQL20012;Database=agepros;LastUser=sa;UseProcForPrepare=0;AnsiNPW=No;QuotedId=No;AutoTranslate=No;Language=English;

[Giorgio Bravi]

I was using the same until TD5.2 then changed to OLEDB
Now it's time to go back (forward?) to ODBC in the process to migrate to TD7

Thank for the info.

Giorgio

[FRBhote]

Just trying TD7. The same SQL.ini and the same SqlDatabse, SqlUser and SqlPassword which connects in TD 2.1 gives a 00401 DBA COD Cannot open database.

Is there any change - don't see anything in the documents.

[win32client]
clientname=FRB
setzerolengthstringstonull=on


[win32client.dll]
comdll=sqlora32
comdll=sqlodb32
comdll=sqlws32


[odbcrtr]
remotedbname=V,DSN=V

[visionpro]

Hi All,
ODBC connection for SQL Server (SQLSRV32.ALL) works well on all SQL Server versions ( From SQLServer 7.0 to SQLServer 2016)

After changing the ODBC to SqlServer native Client (SQLNCI10.DLL) , my application running on Team developer 2 & Team developer 5 does not work.

Error Message [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification

It affects fields DATE/TIME. example: UPDATE ACCESS.dbo.SYSUSR SET LSTLOGIN= :dlbpwd.dtDate WHERE USRCOD = :p_sUsrcod
(dlbpwd.dtDate) is defined as DATE/TIME.

Can I have a solution for this.

Thanks

Samad

[FRBhote]

Apparently the comdll=sqlora32 causes the problem. Without it the ODBC connect is fine.

BTW, for TD, the remotedbname is not needed. This is what CCOD.pdf says:

Notes: The remotedbname parameter is not necessary for applications built using Team Developer. It is required if you want to use the SqlTalk utility to connect to the ODBC data source.
When connecting to any ODBC data source where the data source name was configured by the ODBC Administrator tool, your application can bypass the remotedbname parameter altogether, and set the variable SqlDatabase to the actual ODBC data source name. Using the above example, if you issue the following statements (assume SqlUser and SqlPassword are set), your program will connect correctly to the Microsoft SqlServer 7.0 data source without using the remotedbname parameter in SQL.INI:
Set SqlDatabase = "MS SqlServer 7.0"
Call SqlConnect (hSql)

[FRBhote]

Is it actually possible to have a program running on SqlServer? I get all sorts of ridiculous errors:

Attempt to fetch non-numeric data into numeric column

f4.png

As and when it feels like it this happens:

e1.png

Sometimes Input buffer too small.

Just cannot proceed.

[Georg Diczig]

Hi FRBhote,

unfortunately we get the same errors with TD7.0.4 and SQL Server.
Have you found any solutions?

[FRBhote]

Try this.

[Steve Leighton]

For anyone considering the ODBC v OLE DB for SQLServer conundrum ..... wef April 2018, Microsoft have just 'undeprecated' their OLE DB Driver for SQL Server versions 2012 to 2017, and released a brand new OLE DB driver which will 'be developed further in preference to ODBC' .
I am testing right now against TD6 and TD7 - so far Ok.

The variable SqlUDL in place of SqlUser, SqlDatabase and SqlPassword can contain a .udl file name with the SqlServer connection string in it, or the string itself. A .udl file is easiest, just create a .txt file, rename it to .udl , double click the .udl file, and a connection wizard allows easy configuration.

Go here for details / download: https://blogs.msdn.microsoft.com/sqlnat ... ql-server/

Go here for my sample: https://forum.tdcommunity.net/viewtopic.php?f=6&t=45816

[Giorgio Bravi]

Thank you for the info Steve.

Happy to inform that testing with 5.2 and 7.x and is working well without any change on generated EXE.

The rule is always the same: do not believe too quickly what they say .... wait .... wait ... still a little ...

[Steve Leighton]

re: OLE DB

Here's a trap for new ( and old ) players.... quite logical when you realise it....
both the old 'SQLOLEDB' and new 'MSOLEDBSQL' ( 2018 release ) OLE DB drivers for Sql Server do not support the 'RC' range of isolation levels , which of course is only for SqlBase.
SqlSetIsolationLevel() will just return FALSE if you try and set as RC, RC1, RC2 or RC3 , no Sql error - and so subsequent DML crashes with crazy error codes if you don't handle this.

So if you're in the unfortunate position of having to downgrade from SqlBase to SqlServer , then only 'RO', 'RL', 'CS', and 'RR' isolation levels work with OLE DB for Sql Server.