June 21, 2004 at 5:27 pm
In our company we need to evaluate zips and fips for addresses... So a fellow developer got me a dll that I can use to access the main system and works great via the gui and vbscript, but I need to run a batch inside sql to evaluate the fips. here is the sample vbscript:
dim ht,a,c,s,z,f,x
a="2900 Featherstone Road"
c="auburn hills"
s="mi"
z="93004"
set ht = createobject("addresstele.address")
x=ht.validateaddress(a,c,s,z,f)
msgbox x & vbcrlf & a & vbcrlf & c & vbcrlf & s & vbcrlf & z & vbcrlf & f
I've never used the sp_OAxx sprocs and was tinkering around with them but I can't get them set up correctly
DECLARE @object int
DECLARE @output varchar(255)
DECLARE @hr int
Declare @a varchar(255)
Declare @C varchar(255)
Declare @s-2 varchar(255)
Declare @z varchar(255)
Declare @f int
Declare @x bit
DECLARE @src varchar(255), @desc varchar(255)
--Create the object
EXEC @hr = sp_OACreate 'addresstele.address', @object OUT, 4
--Set Properties
EXEC @hr = sp_OASetProperty @object, '2900 FeatherStone Rd',@a
EXEC @hr = sp_OASetProperty @object, 'auburn hills',@c
EXEC @hr = sp_OASetProperty @object, 'mi',@s
EXEC @hr = sp_OASetProperty @object, '93004',@z
EXEC @hr = sp_OASetProperty @object, 0,@f
--Call the object's property and return the value
EXEC @hr = sp_OAMethod @object, 'validateaddress', @x OUT
--Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
PRINT 'Error Occurred Calling Object: ' + @src + ' ' + @desc
-- RETURN
END
PRINT @x
PRINT @a
PRINT @f
any ideas are gladly welcomed.
-- Francisco
June 22, 2004 at 12:50 am
No real ideas, sorry, but...
using the sp_OAxxx procs is probably "shaky" at best, and may also add significant overhead, especially if the data volume is great.
Is it really necessary to call this dll for the evaluations inside SQL Server? Perhaps an alternative would be better?
(ie implement a proc instead of the dll, or similar..)
/Kenneth
June 22, 2004 at 8:58 am
there is another way and that is embedding this beast as a vbscript in a DTS and run it from there (or a JOB), but then I have to write in my sql connectivity back so thus it's still gonna batch, but I know that doing it that way will be significantly slower, and this method would allow me to schedule maintenance. :S
-- Francisco
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply