SQL Server Performance Forum – Threads Archive

Stored Procedure Vs C Embedded Code

I have a situation in whic the client is evaluating the option of using embedded C code using DB Library in place of SQL Stored Procedures. How much is the classical way of using the SQL DB Library written in C efficient than writing stored procedures? (Performance Oriented) Any links to this information are appreciated….
Gaurav

DB Library is no longer supported by Microsoft and is only in SQL Server 2000 for backward compatability. I don’t know for sure, but it could very well be dropped in the next version of SQL Server. I don’t have an answer to your performance question, but given that it is no longer supported by Microsoft is a good enough reason not to even consider it. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

Thanks Brad. Can you please support the statement with some links on the net. I have had a hard time finding such info on net. This will help me in justifying the stand. Thanks anyway… Regards, Gaurav

Its worth noting that if your client can hold out a while longer, they will have the option to use a .NET compatible language for stored procedures. Whilst Id still personally prefer to stick to T-SQL for database operations, there are times when a more powerful language could be useful for enforcing business rules within the procs. Like Brad says, DB Library has been deprecated. But not only that, DB Library can only act in 6.5 compatability mode, meaning that a lot of sql 2000’s (and even v7) features cannot be utilised by this client library. Personally I think theyd be mad to even consider such an outdated technology: Less maintainable, less community support, inevitably obsolete. Check sql 2000’s Books Online for more details.

Hey that’s a cool suggestion. I didn’t remember that yukon will have this functionality. Thanks for the info. Can you please support the statement "DB Library can only act in 6.5 compatability mode" with some links. That would be really great… Anyway after hours of serches I have got this info… While the ESQL/C API is still supported in Microsoft SQL Server 2000, no future versions of SQL Server will include the files needed to do programming work on applications that use this API. Connections from existing applications written using ESQL/C will still be supported in the next version of SQL Server, but this support will also be dropped in a future release. When writing new applications, avoid using ESQL/C. When modifying existing applications, you are strongly encouraged to remove dependencies on ESQL/C. Instead of ESQL/C, you can use Microsoft ActiveXÂ® Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server. http://msdn.microsoft.com/library/en-us/esqlforc/ec_6_epr_01_3m03.asp Embedded SQL Server for C is not thread-safe. If you are using ESQL in a threaded application, you should only use ESQL calls from a single thread of execution, and it is best if you use the main thread. http://support.microsoft.com/default.aspx?scid=kb;en-us;151598 Thanks, Gaurav

Search books online for DB-Library and then choose the API subheading. BOL is a little ambiguous on reflection, because in this section it states… ’The DB-Library API has not been enhanced beyond the level of SQL Server version 6.5. All DB-Library applications can work with SQL Server 2000, but only as 6.5 level clients. Features introduced in SQL Server 2000 and SQL Server version 7.0 are not supported for DB-Library applications.' However, if you then look at the FAQ subheading, then choose server FAQ. Now expand the question ‘Are DB-Library applications supported in SQL Server 2000?’, you will be informed that ’Yes. However, DB-Library has not been enhanced for SQL Server 2000. DB-Library includes the same features that the MicrosoftÂ® SQL Serverâ„¢ 7.0 DB-Library contains. This means that a DB-Library application can only connect to a default instance of SQL Server 2000; it cannot connect to a named instance. It will not recognize any of the new features available in SQL Server 2000.' I think this is saying that it will only support upto 6.5 features, which is the same level of compatability as the DB-Lib in v7.0 offered. Either way, in your clients particular case its not very rosy for sql 2000. (A relief for you tho, probably <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Thanks for the info…. This solves my problem. Regards, Gaurav

]]>