dead links

Question

dead links

Viewing 6 posts - 16 through 20 (of 20 total)

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

amid SSC Journeyman Points: 87 More actions · Answer 1

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.

amid SSC Journeyman Points: 87 More actions · Answer 2

should i just remove the 'NOT NULL' from Varchar(500) NOT NULL?

Create Table #tmpLinks(Ident INT NOT NULL IDENTITY (1, 1), LinkID INT NOT NULL, URL Varchar(500) NOT NULL)

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

The reason this is happening is because you have nulls in the URL field.

What you need to do is add a where caluse into your select and filter out the null values.

Doing as you wanted would move the error from inserting into the temp table down to the sending. You would be "instructing" the HTTP object to send to null. It'll get very confused...

Use this:



Select DBKey, URL From dbtable Where URL IS NOT NULL

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!

Crispin Proctor SSCoach Points: 18161 More actions · Answer 4

To alter the field to Varchar use:

Alter Table TableName Alter Column ColumnName Varchar(200)

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!

amid SSC Journeyman Points: 87 More actions · Answer 5

Crispin..

it ran for about 12 hours then i got an error message that it was out of virtual memory.

🙁

zach_john SSCertifiable Points: 5922 More actions · Answer 6

Sounds like you have a few records. If you have enough disk add the varchar col under a different name, move the nvarchar to the new col; batch process it. Drop the nvarchar col, then rename the new varchar. Should take no more the 15 minutes or so to code up.

John Zacharkan