Re: String manipulation - taking one long csv string and putting into separate record fields

From: Keith Langmead (klangmead_at_nospam.kms.co.uk)
Date: 09/08/04 

Date: Wed, 8 Sep 2004 11:49:07 +0100

Thanks for your help Russell.

I've tried implementing the code you gave, however it keep chucking out the
errors below. From what I can work out, it looks as if the script is taking
some of the content from the e-mail text, and is treating this as part of
the script code, rather than data to be processed. I've also included the
script code and a selection of the e-mail content which is being processed.
I just hope someone can spot where it is going wrong as I'm somewhat
flumuxed.

I had thought the errors might be caused by something with a particular line
in the text, but if I remove those lines then I get the same error but on a
different line instead.

Thanks
Keith 

---
SQL Mail session started.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark before the character string 'DCOM got error
"%2147746132" from the computer VPN-01 when attempting to  activate the
server:  {D99E6E73-FC88-11D0-B498-00A0C90312F3}
)'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'DCOM got error "%2147746132" from the
computer VPN-01 when attempting to  activate the server:
{D99E6E73-FC88-11D0-B498-00A0C90'.
Stopped SQL Mail session.
---
DECLARE @hMessage varchar(255), @MessageText varchar(8000)
EXEC xp_startmail
EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
exec xp_readmail @msg_id=@hMessage, @message=@MessageText OUT
SELECT 'Youve got mail! Here it is: ' + @MessageText
DECLARE @MyVariable varchar(8000)
DECLARE @MessageText2 varchar(8000)
DECLARE @MessageText3 varchar(8000)
SET @MessageText2 = Replace(@MessageText, '0x0,0', '0x0 0')
SET @MessageText3 = Replace(@MessageText2,
'_____________________________________________________________________
This message has been checked for all known viruses by KMSinternet, powered
by Messagelabs. For more information contact KMS on 0870 744 7395', ' ')
SELECT @MessageText3
USE "KMS_ServerReporting-local"
SET @MyVariable = 'Insert Into tblSecEvents Values (''' +
Replace(@MessageText3, ',', ''',''') + ')'  -- All single quotes
EXEC (@MyVariable)
USE master
EXEC xp_findnextmsg @msg_id=@hMessage OUT
END
EXEC xp_stopmail
----
2/16/2004,21:49:34,1,0,10006,DCOM,SWRDA-NET\bkupexec,,BACKUP-01,DCOM got
error "%2147746132" from the computer SMTP-01 when attempting to activate
the server: {D99E6E73-FC88-11D0-B498-00A0C90312F3}
2/16/2004,22:42:06,1,0,10006,DCOM,SWRDA-NET\bkupexec,,BACKUP-01,DCOM got
error "%2147746132" from the computer SMTP-02 when attempting to activate
the server: {D99E6E73-FC88-11D0-B498-00A0C90312F3}
2/16/2004,23:06:18,2,0,8021,BROWSER,N/A,BACKUP-01,The browser was unable to
retrieve a list of servers from the browser master \\AUTH-02 on the network
\Device\NetBT_Tcpip_{9F788C22-520D-4AE2-9024-030AE78CD683}. The data is the
error code.
2/16/2004,23:08:18,1,0,8032,BROWSER,N/A,BACKUP-01,The browser service has
failed to retrieve the backup list too many times on transport
\Device\NetBT_Tcpip_{9F788C22-520D-4AE2-9024-030AE78CD683}. The backup
browser is stopping.
2/16/2004,23:27:19,1,0,10006,DCOM,SWRDA-NET\bkupexec,,BACKUP-01,DCOM got
error "%2147746132" from the computer VPN-01 when attempting to activate the
server: {D99E6E73-FC88-11D0-B498-00A0C90312F3}
_____________________________________________________________________
This message has been checked for all known viruses by KMSinternet, powered
by Messagelabs. For more information contact KMS on 0870 744 7395
---
"Russell Fields" <RussellFields@NoMailPlease.Com> wrote in message
news:OOd5$qBlEHA.3648@TK2MSFTNGP09.phx.gbl...
> Keith,
>
> You could try something like this:
>
> DECLARE @MyVariable NVARCHAR (1000)
>
> SET @MyVariable = 'Insert Into MyTable Values (''' + Replace(@SQLMailText,
> ',',  ''',''') + ')'  -- All single quotes
>
> EXEC (@MyVariable)
>
> You would, of course, need to avoid breaking down a message string that
has
> commas embedded in it.
>
> Russell Fields
>
>
> "Keith Langmead" <klangmead@nospam.kms.co.uk> wrote in message
> news:OOPcy1AlEHA.1180@TK2MSFTNGP14.phx.gbl...
> > I'm using SQLMail to pull in e-mails which contain a load of csv
formatted
> > text, in this particular case it is the output from the event log.
> >
> > I can get SQL to see the text content, but for ease I want to split up
the
> > text into it's respective sections, and put each line into its own
record,
> > with each part of the line in its own record. For example, below you can
> see
> > the information from one line :
> >
> > 2/17/2004,10:52:48,8,1,517,Security,NT
AUTHORITY\SYSTEM,,Server-01,SYSTEM
> > NT AUTHORITY (0x0,0x3E7) administrator Domain-net (0x0,0xA4DDB69)
> >
2/17/2004,10:52:48,8,4,578,Security,Domain-net\Administrator,,Server-01,Ev
> > entLog 0 248 Server-01$ Domain-net
> >
> > What I want to end up with is a record for the fields split up :
> >
> > Date : 2/17/2004
> > Time : 10:52:48
> > Category : 8
> > Type : 1
> > EventID : 517
> > Source : Security
> > User : NT AUTHORITY\SYSTEM
> > Computer : Server-01
> > Description : SYSTEM   NT AUTHORITY (0x0,0x3E7) administrator Domain-net
> > (0x0,0xA4DDB69)
> >
> > Can anyone give me an idea of the best way to do something like this, or
> > which commands would be required so I can at least hunt through BOL with
> > some idea of what I'm looking for.
> >
> > Thanks
> > Keith
> >
> >
>
>

Relevant Pages