BCP and Date Problems

Hi,

We have a job that, every night, imports mail logs into SQL Server for processing. We receive these mail logs from the mail group, so we have no say in how the log should be formatted, basically we are given what we are given.

Our problem started occurring on Oct-01, and has occured every day since then.

The log file may contain rows with dates such as follows (don't ask me why there isn't a consistant format)

1) 2003-10-01 00:18:5
2) 2003-9-01 00:8:6
3) 2003-9-1 00:9:6
4) 2003-9-1 0:09:6

5) 2003-10-1 00:18:6
6) 2003-10-01 0:19:6
7) 2003-10-01 00:8:6

Now, 1-4 import. 5-7 don't.

From what I can see.

If the month is a single digit month, i.e. September above, then it will import basically all formats (e.g. 1 digit for the hour, 1 digit for the minute or 1 digit for the day).

If the month is a double digit month, i.e. October above, then it will not import unless the date is perfectly formatted (e.g. 2 digits for the hour AND 2 digits for the minute AND 2 digits for day).

An excerpt from the format file we are using is listed below.

Why does SQL Server allow 'bad' dates with single digit months, but not double digit months? It's really confusing me. I know the obvious solution, tell the Mail guys to properly format their files, but are there any other solutions?

Thanks heaps. Hope I've asked this clearly.

Format File
----
8.0
11
1 SQLCHAR 0 0 "" 0 TBL_MAIL_LOG_ID ""
2 SQLCHAR 0 0 "" 0 LOG_FILE_ID ""
3 SQLCHAR 0 1 " " 3 DATE ""

The last line reads (broken into individual lines below)

3
SQLCHAR
0
1
" " <-- there is a space in there
3
DATE
""Howdy

Sadly it appears its a quirk in SQL - unless a '1' is an '01' it seems to get its knickers in a knot, especially at the start of a column of information.

Try importing the raw data into another table that uses CHAR instead of datetime for the dates, then add the '0' to any dates required, then transfer across to final table. Its time consuming, but in the long run more robust.

Cheers

SG

Batch process for sending mail

Hello everyone,

I want to write a batch process that will run everyday and send emails.

The scenario is as follows:

There is a websummit held every year and people can register for it. However the request is subjected to be approved/rejected by the admin belonging to that area. Whenver a person enrolls, the RequestStatus is 0. The admin has to change the RequestStatus to either 1(accepted) or -1(rejected).

I want to write a batch process that will send mails to all admins belonging to their particular areas, everyday at 8.00 A.M. The email will be sent whenever the request has not been changed(ie. RequestStatus=0) and their is a lapse of more than 7 days for their area or the request count for their area has exceeded 5.

Table structure:

Admin

AdminID

Email

AdminArea

AdminID

AreaCode

Area

AreaCode

AreaName

WebSummit

SummitId

RequestorName

DateOfRequest

RequestStatus

AreaCode

Regards,

Vidya.

Hi,

Kindly advise.

Regards,

Vidya

|||

You'll probably want to use the master.dbo.xp_sendmail procedure, using the @.query parameter.

Roughly, create a SQL Server Agent job to run at 8:00AM

and run code that creates a table of recipients, then execute xp_sendmail for each recipient with the query to provide the information in the email you want them to see.

|||

Hi,

Thanks for reading. Some code will be useful.

Regards,

Vidya

|||

Here's the basic template for the script to be executed.

You'll need to adjust the queries to be exactly what you need.

I just did a simple query to get the admin who had 5 or more requests present.

Code Snippet

createtable #Admin (AdminID int, Email varchar(50))

insertinto #Admin values(1,'admin1@.mycompany.com')

insertinto #Admin values(2,'admin2@.mycompany.com')

insertinto #Admin values(3,'admin3@.mycompany.com')

insertinto #Admin values(4,'admin4@.mycompany.com')

createtable #AdminArea (AdminID int, AreaCode int)

insertinto #AdminArea values(1, 100)

insertinto #AdminArea values(2, 200)

insertinto #AdminArea values(3, 300)

insertinto #AdminArea values(4, 400)

createtable #Area (AreaCode int, AreaName varchar(50))

insertinto #Area values(100,'Area 100')

insertinto #Area values(200,'Area 200')

insertinto #Area values(300,'Area 300')

insertinto #Area values(400,'Area 400')

createtable #WebSummit ( SummitId int, RequestorName varchar(50),

DateOfRequest datetime, RequestStatus int, AreaCode int)

insertinto #WebSummit values( 1,'George Jetson',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Jane Jetson',getdate(), 1, 200 )

insertinto #WebSummit values( 1,'Fred Flinstone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Wilma Flintsone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Barney Rubble',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Betty Rubble',getdate(), 1, 100 )

droptable #to

select a.AdminID, a.Email

into #to

from #Admin a

where a.AdminID in

(

select a.AdminID

from #Admin a

innerjoin #AdminArea aa

on a.AdminID = aa.AdminID

innerjoin #Area b

on aa.AreaCode = b.AreaCode

innerjoin #WebSummit w

on b.AreaCode = w.AreaCode

groupby a.AdminID

havingcount(*)>4

)

declare elist cursorfor

select adminid, email from #to

declare @.id int, @.email varchar(50), @.cmd nvarchar(4000)

open elist

if@.@.cursor_rows> 0

begin

fetch next from elist into @.id, @.email

while@.@.fetch_status=0

begin

set @.cmd = N'put your query here WHERE AdminID = '+cast(@.id asvarchar(10))

exec master.dbo.xp_sendmail @.recipient=@.email, @.query=@.cmd,

@.subject='Summit Follow-up'

fetch next from elist into @.id, @.email

end

end

close elist

deallocate elist

|||

Thanks Dale.

That was very helpful.

Regards,

Vidya.