Thanks for the info.

However, I don't think the boss can be talked around, I think it will be "my way" or the highway!!

Kev

>I can say that "don't do it!" isn't a strong enough statement.
>
>How about "DON'T DO IT!" :)
>
>There was a system that a consultant did for my employer. She did it over a period of 18 months. She took all of the businesss rule logic for inception-to-date insurance claims and put it into TSQL. Because TSQL is lacking when it comes to case/switch logic, has no (well no real) notion of object PEMs, and syntax that is different from general-purpose languages, the app was, um, s-l-o-w.
>
>The app used to take upwards of 22 hours to process a 225mb file(!) and at end of month there might be 2-20 of these files. The file (coming from an AS/400) had to be imported into a temp table, table-scanned, and then logic had to be applied against the input rows, and ultimately it went into a final MSSQL table. It also did some table lookup for historical data cmparison against MSSQL tables on the same server. The UI was VB, but all you could do with the UI was start a job an know that MSSQL was doing "something".
>
>So it could take several days to close a month period for reporting, and backups would have to be frozen if in advance the on-site developer charged with supporting the app knew about it. The TSQL application also operated serially, so if my submission took 1 hour and yours took 4 but you got yours in first, I would have to wait 5 hours.
>
>By contrast, my development group did this:
>1. Took the approx 4 million rows of lookup data (residing in SQL Server) that the TSQL application used for cross-reference and copied that to an app server, converting it to a VFP7 DBC.
>2. On the same app server, used Windows Script Host cscript 5.6 to read the text file, break it into memory-resident ADO recordsets, and compare it to the data on the VFP7 DBC. The final data inserts were written to the database server.
>
>There was a lot more to the app than that, but by caching the lookup data locally, using cscript instead of TSQL, and keeping everything in RAM*, the 22-hour job now took 2-3 hours, and that is with strictly interpreted late-bound VBScript. Also, cscript.exe can be run multiple times, so we could run up to 10 scripts on the same app server before the CPU was saturated. So if your submission used to take 4 hours, and mine used to take 1, now you would have to wait 4:30 and I would have to wait 1:15 but I get mine when it is done. And that was my 1.0 release with largely unoptimized code and methods.
>
>TSQL was running on a dual-733 with 2gb RAM, the app server was running on a dual-933 with 512mb RAM, so the machines are about equal in horsepower.
>
>Something else - if your middle tier server is SQL Server, and you exhaust the resources, you must buy another expensive database-class hardware, and pay some real money for SQL licenses. For my version, just get another Win2K server, VBScript costs $0 to deploy, VFP costs $0 to deploy.
>
>Hopefully that illustrates that using TSQL for a application logic and security and parsing works, but is slow an inneficient. I would suggest using the database for database tasks, and using a middle-tier language for middle-tier tasks. I imagine that if we used fully compiled and optimized VFP for the middle tier with VFP cursors instead of ADO, the app would have been even faster out of the gate.
>
>
>The next version (or the version after that) of SQL Server is supposed to use C# as its language, so I imagine that things will change then. So if you are willing to wait 2+ years to deploy your app, then you will be able to say SQL Server's language can be used for business logic. :) But you will still be hampered by the fact that SQL Server was created and optimized to CRUD data, not execute (complex) business or security rules. SQL Server was made to take orders, not give them. :)
>
>hth 1%
>
>
>>Hi
>>
>>What is SQL-Server's procedural language like? Is it any good to use as a middle-tier?
>>
>>This will be for a complex system where the rules are pretty heavy.
>>
>>Thanks
>>Kev