DLL''s function results into SQL server.

Question

DLL''s function results into SQL server.

Lowell

SSC Guru

Points: 323518
More actions
February 9, 2007 at 11:19 am

#176765

in another thread, someone basically wanted to read a web page, and put the text results in a table in SQL server. they were trying to use a shell to open a web page, but that does not return the text, just whether IEXPLORER.exe opened or not
ok, very simple, I created an activex dll in vb6. one form with the inet control on it.
one public method called GetWebPageContentsAsString.
created a .vbs file with these two lines of code:
Set x = WScript.CreateObject("WebReader.Reader")
msgbox x.GetWebPageContentsAsString("http://www.yahoo.com")

so the function returns the string as expected, no problem.
so could I use xp_cmdshell to get the results? I'm kinda stumped on once I have the text, how to get it into SQL.
now i know I could write it to a file with the Scripting.FileSystemObject and then Bulk Insert or bcp the results into a temp table....but can anyone think of any other ways to do it?

I'm really looking at how to get ANY results from a dll into a server, so whether the dll had a property i wanted to read, or anything, how to get the value into SQL .
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
February 12, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 1

I'm not sure, Lowell, but have you tried converting the DLL to an "extended stored procedure"? Books Online tells how...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 2

I think that's my next step.... I was looking for an easy sleazy way to get results from any old DLL, but I think I'll have to slap together an xp to get the results back..other than doing what I said before, writing the results to a file, and then importing the file....my forte is vb and .NET , and xp's can only be written in c/c++, so This is a back burner issue;

Thanks Jeff!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

I'm guessing SQL Server 2000 here.... if you were using SQL 2005 you'd build the dll as a .net assembly, and just import the assembly with EXTERNAL access and go through the joy of getting CLR function that accesses an outside web server trusted and running.

Well, the real answer is to continue in VB and write the app that will insert the value into SQL Server.

However, <insert a lot of disclaimers here, cus using the following techniques have a performance cost, and calling stuff in-process can hose your server using poorly-coded activex> SQL Server 2000 can instantiate objects from activex and call properties and methods on them. Similar to your .vbs, you would create an instance of the WebReader.Reader object, and assign the result of GetWebPageContentsAsString to a variable, which can be inserted into a table.

Look up the sp_OA procedures (sp_OACreate, sp_OAGetProperty, sp_OAMethod, sp_OADestroy, etc.)

-Eddie

Eddie Wuerch
MCM: SQL

Lowell SSC Guru Points: 323518 More actions · Answer 4

Eddie thanks; you pointed me on the right track; by using the sp_Oa procs, and a bit of minor modifications to my test dll, i was able to read any web page, and then stick it into a local @table variable in 4K segments at a time till I hav ethe whole thing in sql server; once it's in the @table variable, I could do whatever i want, which was the point of the attempt;

Thanks again

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!