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.
April 4, 2003 at 3:40 am
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)
April 4, 2003 at 3:45 am
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!
April 4, 2003 at 3:49 am
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!
April 7, 2003 at 6:27 am
Crispin..
it ran for about 12 hours then i got an error message that it was out of virtual memory.
🙁
April 7, 2003 at 8:44 am
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
John Zacharkan
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply