"Invalid character in the given encoding". XML bulk load, SQLE

Hi Martin,
I confirm that the problem is the presence of the character "è", even if it
is in the comment. How can I solve the problem?
I have put the line
<?xml version="1.0" encoding="utf-8"?>
for the encoding. What else should I do?
"Martin Honnen" wrote:

> Tatopitta wrote:
>
> The error usually means that a byte sequence has been encountered that
> does not map to a character in the specified encoding in the XML
> declaration.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>Tatopitta wrote:

> I confirm that the problem is the presence of the character "è", even if
it
> is in the comment. How can I solve the problem?
> I have put the line
> <?xml version="1.0" encoding="utf-8"?>
> for the encoding. What else should I do?
Make sure the document is really UTF-8 encoded, with UTF-8 the character
"è" needs to be encoded with the two byte sequence C3 A8.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

"Error creating MDAC instance" performing Bulk Insert

I get this error when trying to perform a bulk insert. This started
happening after the latest security fixes were loaded on the server.
Anyone know what this error means?
I was able to correct this by installing the latest sp of MDAC and SP3 of
SQLXML.
"DavidSp8" wrote:

> I get this error when trying to perform a bulk insert. This started
> happening after the latest security fixes were loaded on the server.
> Anyone know what this error means?

"Error creating MDAC instance" performing Bulk Insert

I get this error when trying to perform a bulk insert. This started
happening after the latest security fixes were loaded on the server.
Anyone know what this error means?I was able to correct this by installing the latest sp of MDAC and SP3 of
SQLXML.
"DavidSp8" wrote:

> I get this error when trying to perform a bulk insert. This started
> happening after the latest security fixes were loaded on the server.
> Anyone know what this error means?

"complex" SQL and bulk mailing

Well, you guys probably don't think this is very complex SQL :) , but it's taken me ages and it's STILL not working right. I submit the following:

CREATE PROCEDURE forum_readmail AS
Declare @.hMessage varchar(255),@.msg_id varchar(255),@.MessageText varchar(8000),@.message varchar(8000),@.MessageSubject varchar(8000),@.subject varchar(8000),@.Origin varchar (8000),@.originator_address varchar(8000)
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN
exec master.dbo.xp_readmail @.peek='true',@.msg_id=@.hMessage,@.message=@.MessageTe xt OUT,@.subject=@.MessageSubject OUT,@.originator_address=@.Origin OUT
IF ((SELECT COUNT(*) FROM forum_users WHERE email=@.Origin)=1)
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF REPLYING TO EXISTING THREAD
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500), @.usr1 int, @.mySubject varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
SELECT @.mySubject=subject FROM forum_threads WHERE id=@.existingID
-- GET USERS ID FROM EMAIL
declare user_cursor1 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor1
fetch next from user_cursor1
into @.usr1
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.existingID, @.usr1)
fetch next from user_cursor1
into @.usr1
end
close user_cursor1
deallocate user_cursor1

--GET EMAIL LIST
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1
while @.@.FETCH_STATUS=0
begin --CREATE EMAIL
select @.sbj1=@.Origin+': '+@.mySubject+' (ThreadID='+cast(@.existingID as varchar)+')'
select @.bdy1='User '+@.Origin+' has replied to a thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
ELSE -- IF NEW THREAD
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500), @.usr2 int

-- INSERT INTO FORUM_THREADS
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY

-- GET USERS ID FROM EMAIL
declare user_cursor2 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor2
fetch next from user_cursor2
into @.usr2
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.newID, @.usr2)
fetch next from user_cursor2
into @.usr2
end
close user_cursor2
deallocate user_cursor2
--GET EMAIL LIST
declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2
while @.@.FETCH_STATUS=0
begin
select @.sbj2=@.Origin+': '+@.MessageSubject+' (ThreadID='+cast(@.newID as varchar)+')'
select @.bdy2='User '+@.Origin+' has started a new thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em2,
@.message=@.bdy2,
@.subject=@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
END
SET @.hMessage = NULL
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
END
GO

This runs as a scheduled job every 10 minutes. There are about 600 users in the 'forum_users' table - which our experiments indicate it should take about 3 minutes to email.

It seems to be having problems, though - it sits continually in an "Executing job" state, even sometimes when there aren't any emails in the mailbox to read. And sometimes just fails completely - won't read anything.

What could be causing this? Where should I start? Is the code as clean and swift as it can be? Should I be especially vigilant of invalid email addresses? Might we be having network problems? Should I not even be thinking of using SQLMail to deal with large (ie: absolute maximum of about 1500 users) volumes of email both to read and send out?

Help. My fingers are bleeding and my boss is going to beat me.Right, we've done some more testing. It seems that setting it to read all emails (not just the unread ones) and not to "peek" at them (ie: leave them as unread) stops it stitting there in an "executing" state. But there are still problems.

Firstly, it fails every other time. It's like this:
I delete every message in the mailbox
I send it a single email
I start the job - it immediately switches to "Failed".
I start the job again - it spends a few seconds on "Executing" (there are about 100 email addresses to send emails to) and finally switches to "Successful".

It only seems to do this when there's only new messages in the mailbox. I don't understand why.

Secondly - and this is the thing we're hoping to actually test - I have concerns about the volumes that SQLmail can actually cope with. It seems to be able to generate email at about 1000 per minute, but if I've got 1000 users in the database and there's 10 new forum posts for it to process, it's gotta send out 10,000 emails.

Is this a ridiculous thing to expect SQL mail to be able to do?|||Oh, there's one more thing. We've also got a problem with Exchange returning delivery failure notification emails to the mailbox that SQL mail is reading - which can't be helping. Now, we've tried setting Exchange to stop sending the blasted things, but to no avail. So I changed the SP above to include (immediately after the "exec master.dbo.xp_readmail" line), the following:

IF (CHARINDEX('Undeliverable', @.MessageSubject)>0)
BEGIN
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END

but it very blatantly isn't deleting anything with "Undeliverable" in the subject. Can anyone think why?|||OK, update:

I've got round the error message it was generating, but it's still ignoring this 'if' statement, and I've NO idea why. I've tried various checks, including:
IF (@.MessageSubject LIKE '%Undeliverable%')
and
IF (@.Origin LIKE '%System%')
(where @.Origin is the sender of the email; 'System Administrator')

It's just not deleting the email, and it SHOULD. Does anyone know why?

"Bulk copy insert failed"

I'm copying data from tables in one server to tables in another via DTS.

This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.

When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.

Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.

But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?