November 10, 2011 at 9:34 am
I'm getting a pesky collation error when trying to run my mail script.
The error is: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation.
The script is:
BEGIN TRANSACTION
declare @MyId char( 11 )
declare @MyEmail varchar(255)
declare @MyFirstname varchar(255)
declare @MySubject varchar(MAX)
declare @MyBody varchar(MAX)
declare @stime DATETIME
declare @etime DATETIME
declare @ftime INT
declare @passeddays INT
declare @temp INT
SELECT @stime = SYSDATETIME()
use [databases]
set rowcount 0
select id,UPPER(LEFT(firstname,1))+SUBSTRING(firstname,2,LEN(firstname)) AS firstname,email, DATEDIFF(DAY,lastlogin,GETDATE()) AS passeddays INTO #MyTempTable from users WHERE email='mymail@gmail.com'
set rowcount 1
select @MyId=id, @passeddays=passeddays from #MyTempTable
while @@rowcount <> 0
begin
set rowcount 0
select @MyEmail=email,@MyFirstname=firstname from #MyTempTable where id = @MyId
if @MyEmail='mymail@gmail.com'
It seems like sqlserver doesnt like the line above.
begin
select @temp=count(id) from companies.dbo.companies WHERE [status]=4
set @MySubject = cast(@temp as varchar(50)) + ' subject'
set @MyBody = 'body'
end
EXEC msdb.dbo.sp_send_dbmail @profile_name='name',
@recipients=@MyEmail,
@subject=@MySubject,
@body_format = 'HTML',
@body=@MyBody
delete #MyTempTable where id = @MyId
set rowcount 1
select @MyId = id from #MyTempTable
end
set rowcount 0
DROP TABLE #MyTempTable
SELECT @etime = SYSDATETIME()
SELECT @ftime = DATEDIFF(MILLISECOND,@stime,@etime)
PRINT @ftime
ROLLBACK TRANSACTION
Any idea why?
Thanks for you time!
November 10, 2011 at 9:50 am
What happens if you add COLLATE ?
BEGIN TRANSACTION
DECLARE @MyId CHAR(11)
DECLARE @MyEmail VARCHAR(255)
DECLARE @MyFirstname VARCHAR(255)
DECLARE @MySubject VARCHAR(MAX)
DECLARE @MyBody VARCHAR(MAX)
DECLARE @stime DATETIME
DECLARE @etime DATETIME
DECLARE @ftime INT
DECLARE @passeddays INT
DECLARE @temp INT
SELECT @stime = SYSDATETIME()
--use [databases]
SET ROWCOUNT 0
SELECT id
,UPPER(LEFT(firstname, 1)) + SUBSTRING(firstname, 2, LEN(firstname)) AS firstname
,DATEDIFF(DAY, lastlogin, GETDATE()) AS passeddays
INTO #MyTempTable
FROM users
WHERE email = 'mymail@gmail.com'
SET ROWCOUNT 1
SELECT @MyId = id
,@passeddays = passeddays
FROM #MyTempTable
WHILE @@rowcount <> 0
BEGIN
SET ROWCOUNT 0
SELECT @MyEmail = email
,@MyFirstname = firstname
FROM #MyTempTable
WHERE id = @MyId
IF @MyEmail COLLATE SQL_Latin1_General_CP1_CI_AS = 'mymail@gmail.com'
--It seems like sqlserver doesnt like the line above.
BEGIN
SELECT @temp = count(id)
FROM companies.dbo.companies
WHERE [status] = 4
SET @MySubject = cast(@temp AS VARCHAR(50)) + ' subject'
SET @MyBody = 'body'
END
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'name'
,@recipients = @MyEmail
,@subject = @MySubject
,@body_format = 'HTML'
,@body = @MyBody
DELETE #MyTempTable
WHERE id = @MyId
SET ROWCOUNT 1
SELECT @MyId = id
FROM #MyTempTable
END
SET ROWCOUNT 0
DROP TABLE #MyTempTable
SELECT @etime = SYSDATETIME()
SELECT @ftime = DATEDIFF(MILLISECOND, @stime, @etime)
PRINT @ftime
ROLLBACK TRANSACTION
November 10, 2011 at 10:05 am
November 22, 2011 at 3:14 am
Cadavre (11/10/2011)
What happens if you add COLLATE ?
IF @MyEmail COLLATE SQL_Latin1_General_CP1_CI_AS = 'mymail@gmail.com'
This worked great ty.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply