Objective:

I'm stripping (trying to) data from IIS weblogs and FTP log files for further analysis. I'm loading them into Excel then saving as CSV and importing into SQLServer. So far so good. (Slow process) 

I've added four columns to hold the data that I need to extract from one of the weblog columns [cs-data]. 

             [customerID] 
             [orderID]
             [coachID] 
             [custName] 


SQLServer version:

Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


This is the DDL and the DML:

	
-- if the table exists, get rid of it
if exists 
    (select * from dbo.sysobjects 
     where id = object_id(N'[dbo].[tbl_testlog]') and 
     OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_testlog]
GO

-- Create the table
CREATE TABLE [dbo].[tbl_testlog] (

     [cs_data] [varchar] (140) NOT NULL,
     [customerID] [varchar] (10) NULL ,
     [orderID] [varchar] (10) NULL ,
     [coachID] [varchar] (10) NULL,
     [custName] [varchar] (50) NULL

) 
GO
 
INSERT INTO tbl_testlog VALUES ('http://140.99.40.16/DataAccess/Admin/ScheduleSessionDirector.asp?orderID=7477&customerID=5689&CoachID=227&CustomerName=David%20Owen',NULL, NULL, NULL, NULL)
INSERT INTO tbl_testlog VALUES ('http://140.99.40.16/DataAccess/Admin/EditCustomer.asp?CustomerID=569',NULL, NULL, NULL, NULL)
INSERT INTO tbl_testlog VALUES ('http://140.99.40.16/DataAccess/Director/notes/NoteEditor.asp?customerID=5416&orderID=7204',NULL, NULL, NULL, NULL)
 
GO


------------------------



Note: I use
                     [cs_data] [varchar] (140)
      here, but in real life I use 
                     [cs_data]varchar(1000)
      just because I don't know what I'm doing. I'd like to be able to just put varchar and 
      have it load up to 8000 characters without me having to provide a MAX length. If you 
      know of a better way, I'll listen. 



I've tried the examples from: 

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51476

Results:

I can grab the first number but not a second number.  Can't seem to grab my ass with two hands either. 

     5689
     569
     5416


Here is the select statement and what I would like in the resulting table:

SELECT [cs_data], [customerID], [orderID], [coachID] FROM [testdts].[dbo].[tbl_testlog]


cs_data                                                                                                                                        customerID   orderID     coachID     custName   
--------------------------------------------------------------------------------------------------------------------------------------------   ----------   ----------  ---------   --------------------------
http://140.99.40.16/DataAccess/Admin/ScheduleSessionDirector.asp?orderID=7477&customerID=5689&CoachID=227&CustomerName=David%20Owen            5689         227          NULL       Owen, David
http://140.99.40.16/DataAccess/Admin/EditCustomer.asp?CustomerID=569                                                                           569          NULL         NULL       NULL
http://140.99.40.16/DataAccess/Director/notes/NoteEditor.asp?customerID=5416&orderID=7204                                                      5416         7204         NULL       NULL


(3 row(s) affected)

Help would be considered an act of humanity. Thank you.