April 3, 2003 at 6:13 am
There is actually a very easy way. Provided your DB can see the net.
Useing sp_OACreate and it's "friends" you can use the XMLHTTP control.
There is a lot in BOL explaining it.
If you need examples, say so. I'll post them.
If you get a http 200 response, the link is live. If you get a 404, the link is dead.
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 3, 2003 at 7:23 am
Crispin examples would be magic..
the database is with a hosting company on their server.
thanks
Michael
April 3, 2003 at 10:29 am
Here you go. Besure to destroy the object. It'll eat your DB's memory!
DECLARE
@vPointerINT,
@vResponseTextVARCHAR(8000),
@vStatusINT,
@vStatusTextVARCHAR(200)
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', 'http://localhost/Fiber/clients/'
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
Select @vStatus, @vStatusText, @vResponseText
|200||OK ||<HTML> Result Trimmed||
You can find definitions of all the codes on the W3C site.
http://www.w3.org/Protocols/HTTP/HTRESP.html
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 3, 2003 at 10:31 am
Something else...
You can use sp_OAGetErrorInfo after each method or property change to catch any errors.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 3, 2003 at 10:53 am
Crispin,
thats looks like double dutch to me..
can you give me more of a description, especially as to how i run that code.
appreciate the help 🙂
michael.
April 3, 2003 at 11:18 am
Crispin, have you looked into the WinHTTP object? I used to use the ServerXMLHTTP like you show, but have since started to look at using WinHttp.
http://msdn.microsoft.com/library/en-us/winhttp/http/capturing_data_vb.asp
Tim C //Will code for food
Tim C //Will code for food
April 3, 2003 at 11:20 am
hmmm. Will have a look at it and compare. Thanks!
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 3, 2003 at 11:34 am
Here's the same code with a bit of error trapping in it. I'll post the example you want in a sec. Will become to much...
DECLARE
-- @vPointer is a pointer to the object and / or any methods or property
@vPointerINT,
@vResponseTextVARCHAR(8000),
@vStatusINT,
@vStatusTextVARCHAR(200),
@vSourceVARCHAR(255),
@vDescriptionVARCHAR(500)
-- Instantiate the object (Gonna use XMLHTTP Version2. This is avaliabe on W2K+)
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
--Check to see if errors where created.
EXEC sp_OAGetErrorInfo @vPointer, @vSource OUT, @vDescription OUT
IF @vSource Is Not Null
Begin
Select 'Error While Creating HTTP Object' AS SOMFINGWONG, @vSource as Source, @vDescription as [Description]
--Return --Add if it's a proc.
End
-- Open a connection to the URL. This does not send anything yet! ('GET' | 'POST') ('Any valid URL')
EXEC sp_OAMethod @vPointer, 'OPEN', NULL, 'GET', 'http://localhost/Fiber/clients/'
EXEC sp_OAGetErrorInfo @vPointer, @vSource OUT, @vDescription OUT
IF @vSource Is Not Null
Begin
Select 'Error While opening connection', @vSource as Source, @vDescription as [Description]
--Return --Add if it's a proc.
End
-- Send the request.
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAGetErrorInfo @vPointer, @vSource OUT, @vDescription OUT
IF @vSource Is Not Null
Begin
Select 'Error While sending data', @vSource as Source, @vDescription as [Description]
--Return --Add if it's a proc.
End
-- Send the request.
-- If it got this far, there _should_ not be any more errors. :) You may add the error code here to if you like.
EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
Select @vStatus, @vStatusText, SubString(@vResponseText, 1, 10) + ' Result Trimmed'
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 3, 2003 at 12:02 pm
Here you go.
The contents of you <link> table are dumped into a temp table so we can loop through it.
If a response code is not in the IF, that link will be deleted. eg: You get a zero back, server not found, 404 : Page not found etc etc.
(3 beers, 2 smokes later and here's your example... 🙂
Let me know if this works.
DECLARE
@vPointerINT,
@vResponseTextVARCHAR(8000),
@vStatusINT,
@vStatusTextVARCHAR(200),
@vCountINT,
@vURLVarchar(500),
@vLinkIDInt,
@vIdentInt
Set NoCount On
Create Table #tmpLinks(Ident INT NOT NULL IDENTITY (1, 1), LinkID INT NOT NULL, URL Varchar(500) NOT NULL)
Insert Into #tmpLinks(LinkID, URL) -- Maybe with some condition
Select LinkID, URL From Links
Set @vCount = @@RowCount
While @vCount > 0
Begin
Select @vIdent = Ident, @vLinkID = LinkID, @vURL = URL From #tmpLinks Where Ident = @vCount
Print 'Testing ' + @vURL
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', @vURL
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
Print 'Status for ' + @vURL + ' was ' + CAST(@vStatus as VARCHAR)
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
If @vStatus NOT IN (200, 305) -- add any other statuses you DO NOT want to delete
Begin
--Delete From Links Where LinkID = @LinkID -- Add this back in!
Print 'Link ' + @vURL + ' was deleted!'
End
Print ''
Set @vCount = @vCount - 1 --Running through the table backwards.
End
Truncate Table #tmpLinks
Drop Table #tmpLinks
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 4, 2003 at 1:56 am
Hi Crispin below is the structure of our database, how would the code change for this table, if at all?
and do i just run it in the SQL query analyser?
CREATE TABLE [dbo].[dbtable] (
[DBkey] [int] IDENTITY (1, 1) NOT NULL ,
[Email] [nvarchar] (50) ,
[Name] [nvarchar] (100) ,
[Keywords] [nvarchar] (255) ,
[About] [nvarchar] (255) ,
[Url] [nvarchar] (100) ,
[Region] [int] NULL ,
[Done] [bit] NULL
) ON [PRIMARY]
GO
thanks
Michael.
April 4, 2003 at 2:09 am
Greetings.
You would use the same code with the exception of:
Insert Into #tmpLinks(LinkID, URL)
Select LinkID, URL From Links
You would use:
Insert Into #tmpLinks(LinkID, URL)
Select DBKey, URL From dbtable
Everything else would stay the same.
BTW: Any reason you are using NVarchars? They consume a lot of space on your drive.
Nx's are used for Unicode data. I would imagine for all your fields you could use "plain" types. eg: Varchar, CHAR etc.
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 4, 2003 at 2:28 am
NVarchars were used to let all languages and symbols to be used..
April 4, 2003 at 2:31 am
quote:
NVarchars were used to let all languages and symbols to be used..
Question: Would email addresses and URL's allow other charaters?
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
April 4, 2003 at 3:25 am
i guess not...
would it be possible to change the datatype at the moment then... with out affecting anything?
April 4, 2003 at 3:37 am
Hi crispin i get this error message
Server: Msg 515, Level 16, State 2, Line 12
Cannot insert the value NULL into column 'URL', table 'tempdb.dbo.#tmpLinks___________________________________________________________________________________________________________00000000009F'; column does not allow nulls. INSERT fails.
The statement has been terminated.
i used this code
DECLARE
@vPointerINT,
@vResponseTextVARCHAR(8000),
@vStatusINT,
@vStatusTextVARCHAR(200),
@vCountINT,
@vURLVarchar(500),
@vLinkIDInt,
@vIdentInt
Set NoCount On
Create Table #tmpLinks(Ident INT NOT NULL IDENTITY (1, 1), LinkID INT NOT NULL, URL Varchar(500) NOT NULL)
Insert Into #tmpLinks(LinkID, URL)
Select DBKey, URL From dbtable
Set @vCount = @@RowCount
While @vCount > 0
Begin
Select @vIdent = Ident, @vLinkID = LinkID, @vURL = URL From #tmpLinks Where Ident = @vCount
Print 'Testing ' + @vURL
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', @vURL
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
Print 'Status for ' + @vURL + ' was ' + CAST(@vStatus as VARCHAR)
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
If @vStatus NOT IN (200, 305) -- add any other statuses you DO NOT want to delete
Begin
--Delete From Links Where LinkID = @LinkID -- Add this back in!
Print 'Link ' + @vURL + ' was deleted!'
End
Print ''
Set @vCount = @vCount - 1 --Running through the table backwards.
End
Truncate Table #tmpLinks
Drop Table #tmpLinks
thanks
michael.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply