January 10, 2010 at 6:20 am
Hi,
I have a 64-bit SQL2008 I need to get online, but there's on 32-bit DLL a stored procedure calls and it will take a while before that DLL is migrated to 64-bit. So, I will keep my 32-bit SQL2005 online for a while, only to run that DLL.
The DLL is called on the SQL2005 machine from a scalar function named fx_Traveltime. I can access it from my SQL2008 with:
SELECT * FROM OpenQuery(linked_sql2005_servername , 'select master.dbo.fx_Traveltime(''2023RS'',''1161AE'',-120)')
Question: is this efficient; is there a better way to run a scalar function on a linked server?
And another question: on my Windows2008/SQL2008 64-bit machine I could also install SQLExpress 32-bit, and install the DLL in the 32-bit directory, and link to that server-instance. Is that a good idea?
Thanks for any input,
Raymond
January 10, 2010 at 9:23 am
Calling it through OPENQUERY and passing the parameters inside the query text is about as efficiently as you'll be able to do it, but it's not massively efficient compared to running it locally, as it has to do a full network round trip each time you call it.
It really depends on how often it's called, how complex the procedure is inside etc. to how relevant that is.
E.g. if it's got some very complex logic inside and isn't executed often, the time spent on the network round trip is probably insignificant, however if it's doing a light weight process, but being called 100's of times a second then it could be a massive problem - only you can tell by doing a realistic load test.
Installing a local instance would cut down on the network latencies, but probably not by a huge amount.
I guess my question would be what's the logic behind this function? Could it be converted to T-SQL efficiently?
January 11, 2010 at 1:04 am
Thanks for your reply. Actually I ran into another problem: the SELECT * FROM OpenQuery(linked_sql2005_servername , 'select master.dbo.fx_Traveltime(''2023RS'',''1161AE'',-120)' can't be made dynamically! Obviously I would have to change the parameters 2023RS, 1161AE and -120, but that's just not possible.... beats me why. So, I have to put it all into a Stored Procedure.
The logic behind this is that SQL calls a DLL, that calculates the traveltime between two zip codes. Unfortunately the DLL is 32-bit and the manufacturer is not showing any hurry to convert it to 64-bit, so I have to come up with a workaround.
January 11, 2010 at 2:02 am
You can call openquery dynamically, but as always you need to be careful about sql injection.
e.g.
DECLARE @sql VARCHAR(8000)
SET @sql='select master.dbo.fx_Traveltime(''''2023RS'''',''''1161AE'''',-120)'
SET @sql='SELECT * FROM OPENQUERY(linked_sql2005_servername,'''+@sql+''')'
EXEC(@sql)
Have you checked out things like this to calculate distance between two zip codes?
http://www.sqlservercentral.com/scripts/Miscellaneous/31673/
January 11, 2010 at 2:34 am
Thanks again!
As for your sql injection example: I will try it; but I must be able to get the return value of course.
As for your distance example: I have something better, it's a DLL that calculates the actual travel time (like the navigation devices in cars). It's a DLL (from a company called Andes) we have encapsulated into our software, and the customers that use it have to pay a fee. It also gets yearly updates so the newest roadmaps are used.
Raymond
January 11, 2010 at 2:48 am
OK. Please note that I gave you an example of Dynamic SQL. SQL Injection is what you need to avoid - e.g. if people can enter free text into the postcode box this is a classic example of where you have to very carefully validate the input if you're using dynamic sql or an attacker would be able to enter any arbitrary code.
Please see the below link:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply