[dba-VB] Performance of .Net Code in SQL Server 2005

Hi Shamil et al

Here is what I did.

Some of the data (from some "foreign" apps) are stored in mdb files.
These I link to the SQL Server as "linked servers" to be able to import the data to the SQL Server on a regular basis.
This is done via some simple queries (no joins, no aggregating) with some basic manipulation writing to similar tables in the server.

These queries I can control from Visual Studio. However, VS will not connect directly to a linked server, only to normal databases of the SQL Server. So you will have to connect to such a database and then reference the linked server in a query. One advantage is, that the SQL syntax of such a query is that of SQL Server, not Access/JET.
It took some research to locate the syntax for referencing a linked mdb - note the last line in the example here: 

Select 
  varenr as ItemId, 
  dessnr as DessinNo, 
  case when (
    case when 
      isnumeric(right(dessnr, charindex('-', reverse(dessnr)))) <> 0 then 
      convert(bigint, right(dessnr, charindex('-', reverse(dessnr)) - 1)) else 
      0 end) 
    between 1 and 99 
    and 
    charindex('-', dessnr) > 0 then 
    rtrim(left( dessnr, len(dessnr) - charindex('-', reverse(dessnr)))) 
    else 
    dessnr end as DessinId
>From 
  linkeddb...tblDessin

The advantage is, that all that is needed to be set up at the server is the links to the mdb files - the queries are controlled and maintained from VS, and the client apps do not need to know anything about the physical location of the mdb files, neither do they need to establish connections to these.

>From this point I can easily store the data in server tables while recording some log details of success or failure.

/gustav

>>> shamil at users.mns.ru 25-01-2008 17:23:10 >>>
Hello Gustav,

I'd use T-SQL CASE and built-in T-SQL functions: REVERSE and CHARINDEX and
SUBSTRING and/or I'd write UDFs using the listed above and other string
functions, which are many in T-SQL...

...I'd use .NET code for something more like business functionality or when
advanced parsing is needed etc. ...

Just my opinion...

--
Shamil
 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com 
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, January 25, 2008 2:02 PM
To: dba-sqlserver at databaseadvisors.com; dba-vb at databaseadvisors.com 
Subject: [dba-VB] Performance of .Net Code in SQL Server 2005

Hi all

I came across this interesting article:

http://www.novicksoftware.com/coding-in-sql/Vol3/cis-v3-N14-performannce-of- 
dot-net-code-sql-server-2005.htm 

But is anyone here running .Net code in SQL Server?

A client has an mdb file with poorly designed tables and sloppy data. The
task is to clean these and transfer them to SQL Server tables with a better
structure.
I'm not so familiar in T-SQL, so I created queries in Access to test and
view the possible output. A typical query contains code like this:

SELECT .. IIf(InStrRev([dessNr],"-")>0 And
IsNumeric(Right([dessNr],1)),Left([dessNr],InStrRev([dessNr],"-")-1),[dessNr
]) AS dessinNoBase

T-SQL knows nothing about InStrRev so I looked for options on how to
translate such functions.

One option is to use .Net code. Speed is not important (small tables).
How do I approach this? Well, the top links at that page link to
presentations on:

  SQLCLR Programming with SQL Server 2005

Still, has anyone been working with this?

/gustav