IF OBJECTPROPERTY(OBJECT_ID('dbo.RefreshArtistURLs'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.RefreshArtistURLs
    IF OBJECTPROPERTY(OBJECT_ID('dbo.RefreshArtistURLs'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RefreshArtistURLs >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RefreshArtistURLs >>>'
END
go
CREATE PROCEDURE RefreshArtistURLs
--WITH ENCRYPTION
AS
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	3 Feb 2000						*/
/*	Description:	This SP is used to refresh the URLs from my IE favorites*/
/*			The URLs are imported into the Artists_URLS table using */
/*			xp_cmdshell & simple SELECT statements to read the tags.*/
/*			Next version will use the IE document model to directly */
/*			access the favorites.					*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
DECLARE @CmdStr varchar(255)
SELECT @CmdStr = 'TYPE ' + REPLACE(Value, '%s', 'My Music.Htm')
FROM Settings
WHERE Setting = 'My Documents'

CREATE TABLE #HTML (Line varchar(8000) null)
INSERT #HTML
EXEC master..xp_cmdshell @CmdStr

INSERT Artist_URLs
SELECT *
FROM (
        SELECT A.ArtistID,
        	LTRIM(SUBSTRING(Line, CHARINDEX('=', Line) + 1,
        		CHARINDEX('ADD_DATE', Line) - CHARINDEX('=', Line) - 1)),
        	REVERSE(SUBSTRING(REVERSE(Line), 5, cHARINDEX('>', REVERSE(Line), 5) - 5))
        FROM #HTML LEFT JOIN Artists A
        ON CHARINDEX(REPLACE(RTRIM(Artist), 'The', ''), Line) > 0
        WHERE PATINDEX('%<A HREF%</A>', Line) > 0
) AS AU(ArtistID, URL, Name)
WHERE NOT EXISTS(SELECT * FROM Artist_URLs A2
			WHERE A2.ArtistID = AU.ArtistID And A2.URL = AU.URL)

UPDATE A
SET A.Name = AU.Name
FROM Artist_URLs A JOIN
(
        SELECT A.ArtistID,
        	LTRIM(SUBSTRING(Line, CHARINDEX('=', Line) + 1,
        		CHARINDEX('ADD_DATE', Line) - CHARINDEX('=', Line) - 1)),
        	REVERSE(SUBSTRING(REVERSE(Line), 5, cHARINDEX('>', REVERSE(Line), 5) - 5))
        FROM #HTML LEFT JOIN Artists A
        ON CHARINDEX(REPLACE(RTRIM(Artist), 'The', ''), Line) > 0
        WHERE PATINDEX('%<A HREF%</A>', Line) > 0
) AS AU(ArtistID, URL, Name)
ON A.ArtistID = AU.ArtistID And A.URL = AU.URL
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.RefreshArtistURLs'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.RefreshArtistURLs To Public
    PRINT '<<< CREATED PROCEDURE dbo.RefreshArtistURLs >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RefreshArtistURLs >>>'
go