I have (inherited a) a db with lots of tables looking like the definition at the end of the message.
I need to change all these tables, so that all the textual fields follow the default DB collation.
Any help appreciated!
THANX...
CREATE TABLE [Club] (
[id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE Greek_CI_AS NOT NULL ,
[address] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[zoomAreaId] [int] NULL ,
[phone] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[contact] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[clubCategoryId] [int] NULL ,
[unused] [varchar] (2) COLLATE Greek_CI_AS NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL ,
[sunday] [int] NULL ,
[comments] [text] COLLATE Greek_CI_AS NULL ,
[URL] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[image] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[priceCategoryId] [int] NULL ,
[ratingId] [int] NULL ,
[entryDate] [datetime] NULL ,
[WAPText] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[SMSText] [varchar] (160) COLLATE Greek_CI_AS NULL ,
[SMSAddress] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[active] [bit] NOT NULL ,
[content_id] [bigint] NULL ,
[disp_as_propos] [bit] NULL ,
[date2disp] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOALTER TABLE...ALTER COLUMN would do the trick, but you have to drop all indexes and all constraints. If you have DBArtisan it would be very easy to do, or you can script both DROP <table_name>.<index_name> and ALTER TABLE <table_name> DROP CONSTRAINT and then re-create them yourself.|||Also, BOL:
You cannot alter the collation of a column that is currently referenced by:
A computed column.
An index.
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
A CHECK constraint.
A FOREIGN KEY constraint.
Showing posts with label message. Show all posts
Showing posts with label message. Show all posts
Sunday, March 11, 2012
Sunday, February 19, 2012
CDOSYS email body truncated
I have written a stored procedure, based on the sample code to send email.
Only problem is that the message body is truncated to 255 characters. Any
ideas? Thanks!
The procedure is listed below. [It has an extra sp_OAGetProperty statement
to retrieve and print the message body after it it set. When I run it, the
email is sent but the body is truncated (the 2 print statements show the
before and after).]
Is this an environment problem? Or am I doing something really stupid?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Send_CDOSYSMail]
@.from varchar(100),
@.to varchar(100),
@.cc varchar(100) = null,
@.bcc varchar(100) = null,
@.subject varchar(100),
@.body varchar(4000),
@.html char(1) = 'N'
/ ****************************************
*****************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
****************************************
*******************************/
AS
declare @.iMsg int
declare @.hr int
declare @.source varchar(255)
declare @.description varchar(500)
declare @.output varchar(1000)
-- Tidy up the parameters
set @.from = ltrim(rtrim(@.from))
set @.to = ltrim(rtrim(@.to))
set @.cc = ltrim(rtrim(@.cc))
set @.bcc = ltrim(rtrim(@.bcc))
set @.subject = ltrim(rtrim(@.subject))
set @.body = ltrim(rtrim(@.body))
-- Create the CDO.Message Object
exec @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
-- Configure the Message Object
-- ... configure a remote SMTP server
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- ... configure the Server Name or IP address
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', '<mysmtpservername>'
-- Save the configurations to the message object.
exec @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
exec @.hr = sp_OASetProperty @.iMsg, 'To', @.to
exec @.hr = sp_OASetProperty @.iMsg, 'From', @.from
exec @.hr = sp_OASetProperty @.iMsg, 'Cc', @.cc
exec @.hr = sp_OASetProperty @.iMsg, 'Bcc', @.bcc
exec @.hr = sp_OASetProperty @.iMsg, 'Subject', @.subject
-- Send the email
if @.html = 'Y'
begin
exec @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.body
end
else
begin
exec @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.body
end
print @.body
declare @.xbody varchar(8000)
exec @.hr = sp_OAGetProperty @.iMsg, 'TextBody', @.xbody OUT
print isnull(@.xbody,'?')
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
exec @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- error handling
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
exec @.hr = sp_OADestroy @.iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHello,
Based on my test, the email body is printed twice and the results are
correctly. Can you print the email body correctly?
For your reference, XPSMTP provides a SMTP based SQL Mail solution for
sending MIME based email over
SMTP, implemented as an Extended Stored Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Can you print the email body correctly?
No ... as I said before it is truncated to 255 characters (in the email that
is sent and in the result returned from sp_OAGetProperty).
> For your reference, XPSMTP provides a SMTP based SQL Mail solution for
> sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
Thanks, I will have to use that if there is no obvious reason for what's
going wrong.|||Cracked it, after some googling. I'd forgotten I was using SQL Server 7
(it's an ancient system I'm trying to support!) ... Apparently, there is a
limitation in SQL 7 that truncates all strings passed to COM objects to 255
characters. As a result, the datatypes of the parameters are limited to 255
characters, since only the first 255 characters will be passed to the CDOSYS
COM object. (It's a limitation of the sp_OA... methods.) This is no good fo
r
the body, so the work around is to use a TEXT field for this. Don't know
what the upper limit is, but it seems to work and certainly goes beyond the
255 character limit.|||Hello,
I don't know you are using SQL server 7 and I tested the issue on SQL
server 2000. I found the following articles address the issue:
278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters
http://support.microsoft.com/?id=278448
324146 FIX: SQL Server 7.0 sp_OASetProperty Method Does Not Accept More Than
http://support.microsoft.com/?id=324146
The following information in the BOL is for your reference:
text
Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Only problem is that the message body is truncated to 255 characters. Any
ideas? Thanks!
The procedure is listed below. [It has an extra sp_OAGetProperty statement
to retrieve and print the message body after it it set. When I run it, the
email is sent but the body is truncated (the 2 print statements show the
before and after).]
Is this an environment problem? Or am I doing something really stupid?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Send_CDOSYSMail]
@.from varchar(100),
@.to varchar(100),
@.cc varchar(100) = null,
@.bcc varchar(100) = null,
@.subject varchar(100),
@.body varchar(4000),
@.html char(1) = 'N'
/ ****************************************
*****************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
****************************************
*******************************/
AS
declare @.iMsg int
declare @.hr int
declare @.source varchar(255)
declare @.description varchar(500)
declare @.output varchar(1000)
-- Tidy up the parameters
set @.from = ltrim(rtrim(@.from))
set @.to = ltrim(rtrim(@.to))
set @.cc = ltrim(rtrim(@.cc))
set @.bcc = ltrim(rtrim(@.bcc))
set @.subject = ltrim(rtrim(@.subject))
set @.body = ltrim(rtrim(@.body))
-- Create the CDO.Message Object
exec @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
-- Configure the Message Object
-- ... configure a remote SMTP server
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- ... configure the Server Name or IP address
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', '<mysmtpservername>'
-- Save the configurations to the message object.
exec @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
exec @.hr = sp_OASetProperty @.iMsg, 'To', @.to
exec @.hr = sp_OASetProperty @.iMsg, 'From', @.from
exec @.hr = sp_OASetProperty @.iMsg, 'Cc', @.cc
exec @.hr = sp_OASetProperty @.iMsg, 'Bcc', @.bcc
exec @.hr = sp_OASetProperty @.iMsg, 'Subject', @.subject
-- Send the email
if @.html = 'Y'
begin
exec @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.body
end
else
begin
exec @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.body
end
print @.body
declare @.xbody varchar(8000)
exec @.hr = sp_OAGetProperty @.iMsg, 'TextBody', @.xbody OUT
print isnull(@.xbody,'?')
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
exec @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- error handling
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
exec @.hr = sp_OADestroy @.iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHello,
Based on my test, the email body is printed twice and the results are
correctly. Can you print the email body correctly?
For your reference, XPSMTP provides a SMTP based SQL Mail solution for
sending MIME based email over
SMTP, implemented as an Extended Stored Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Can you print the email body correctly?
No ... as I said before it is truncated to 255 characters (in the email that
is sent and in the result returned from sp_OAGetProperty).
> For your reference, XPSMTP provides a SMTP based SQL Mail solution for
> sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
Thanks, I will have to use that if there is no obvious reason for what's
going wrong.|||Cracked it, after some googling. I'd forgotten I was using SQL Server 7
(it's an ancient system I'm trying to support!) ... Apparently, there is a
limitation in SQL 7 that truncates all strings passed to COM objects to 255
characters. As a result, the datatypes of the parameters are limited to 255
characters, since only the first 255 characters will be passed to the CDOSYS
COM object. (It's a limitation of the sp_OA... methods.) This is no good fo
r
the body, so the work around is to use a TEXT field for this. Don't know
what the upper limit is, but it seems to work and certainly goes beyond the
255 character limit.|||Hello,
I don't know you are using SQL server 7 and I tested the issue on SQL
server 2000. I found the following articles address the issue:
278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters
http://support.microsoft.com/?id=278448
324146 FIX: SQL Server 7.0 sp_OASetProperty Method Does Not Accept More Than
http://support.microsoft.com/?id=324146
The following information in the BOL is for your reference:
text
Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
CDONS.mail not working
I have a procedure that sends mail using CDONTS.mail
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>
Tuesday, February 14, 2012
Catch Error message in Variable?
Greetings all,
When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?
When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...
Thanks
Yes there is a way. In fact, its done for you.
The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.
Please reply if any of that needs clarifying.
-Jamie
|||
Hi Jamie,
Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.
When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:
Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Can't I use it in 'OnPostExecute'?
Thanks.
|||Thiru,It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.
Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.
Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)
Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||
Thiru_ wrote:
Hi Jamie,
Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.
When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:
Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Can't I use it in 'OnPostExecute'?
Thanks.
Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.
-Jamie
|||
Thiru, It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.
Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)
Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))
I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.
-Jamie
|||
Crispin wrote: Thiru, It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.
Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)
Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))
I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.
-Jamie
Nope, you misunderstood me.
Have a data flow which inserts 1000 rows into a table. One of the rows cannot
go because of a constraint on the column. The following is thrown by SQL /
SSIS:
>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code:
0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL
Native Client" Hresult: 0x80040E2F Description: "The
statement has been terminated.". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F
Description: "The INSERT statement conflicted with the CHECK constraint
"CK_cpxx". The conflict occurred in database "POS_ETL",
table "dbo.cpxx", column 'Col1'.".
>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: The "input "OLE
DB Destination Input" (32)" failed because error code 0xC020907B
occurred, and the error row disposition on "input "OLE DB Destination
Input" (32)" specifies failure on error. An error occurred on the
specified object of the specified component.
>>>>>>>>>>>>>>>>>>>>>>>>>>
[DTS.Pipeline] Error: The ProcessInput method on component
"OLE DB Destination" (19) failed with error code 0xC0209029. The
identified component returned an error from the ProcessInput method. The error
is specific to the component, but the error is fatal and will cause the Data
Flow task to stop running.
>>>>>>>>>>>>>>>>>>>>>>>>>>
[DTS.Pipeline] Error: Thread "WorkThread0"
has exited with error code 0xC0209029.
>>>>>>>>>>>>>>>>>>>>>>>>>>
The above is one error with 4 lines explaining what happened. (or not?)
This causes the onerror event to fire 4 times.
This type of behavior is useless for any error handling of this type as
anything you do in the handler will fire again and again and again.
I can think of many dirty ways to get around this but they create more work
than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event
handlers.|||
Hi Jamie/Crispin,
Thanks for your comments. I missed the point that u said.
As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)
Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?
Thanks.
|||Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?
Thanks.
I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).
-Jamie
|||
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.
Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.
Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )
-Jamie
|||
JAmin,
How Can I
put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event
|||Hi Jamie,
Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.
When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:
Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Can't I use it in 'OnPostExecute'?
Thanks.
No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.
-Jamie
|||
JAmin,
How Can I
put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event
I don't know. You have to tell me what your logic is.
Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306
-jamie
|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?
Catch Error message in Variable?
Greetings all,
>>>>>>>>>>>>>>>>>>>>>>>>>>
The above is one error with 4 lines explaining what happened. (or not?)
This causes the onerror event to fire 4 times.
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.
I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.|||
When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?
When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...
Thanks
Yes there is a way. In fact, its done for you.
The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.
Please reply if any of that needs clarifying.
-Jamie
|||Hi Jamie,
Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.
When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:
Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Can't I use it in 'OnPostExecute'?
Thanks.
|||Thiru,It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.
Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.
Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)
Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||
Thiru_ wrote:
Hi Jamie,
Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.
When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:
Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Can't I use it in 'OnPostExecute'?
Thanks.
Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.
-Jamie
|||Thiru, It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.
Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)
Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))
I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.
-Jamie
|||Crispin wrote: Thiru, It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.
Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)
Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))
I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.
-Jamie
Nope, you misunderstood me.
Have a data flow which inserts 1000 rows into a table. One of the rows cannot go because of a constraint on the column. The following is thrown by SQL / SSIS:
>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the CHECK constraint "CK_cpxx". The conflict occurred in database "POS_ETL", table "dbo.cpxx", column 'Col1'.".
>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: The "input "OLE DB Destination Input" (32)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (32)" specifies failure on error. An error occurred on the specified object of the specified component.
>>>>>>>>>>>>>>>>>>>>>>>>>>
[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (19) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>
The above is one error with 4 lines explaining what happened. (or not?)
This causes the onerror event to fire 4 times.
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.
I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.|||
Hi Jamie/Crispin,
Thanks for your comments. I missed the point that u said.
As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)
Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?
Thanks.
|||Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?
Thanks.
I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).
-Jamie
|||
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.
Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.
Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )
-Jamie
|||JAmin,
How Can I
put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event
|||Hi Jamie,
Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.
When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:
Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Can't I use it in 'OnPostExecute'?
Thanks.
No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.
-Jamie
|||JAmin,
How Can I
put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event
I don't know. You have to tell me what your logic is.
Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306
-jamie
|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?catalog information cannot be retrieved
What is the solution to the following udl test
connection error message.
First you receive a 'Test Completed Successfully'
Then you receive 'Login failed. Catalog information
cannot be retrieved.'
Hi,
Could u try installing the latest MDAC and verify.
http://www.microsoft.com/downloads/d...fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Thanks
Hari
MCDBA
"illworth@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
> What is the solution to the following udl test
> connection error message.
> First you receive a 'Test Completed Successfully'
> Then you receive 'Login failed. Catalog information
> cannot be retrieved.'
>
|||We have 2.71 installed. Same udl copied to another
workstation works.
>--Original Message--
>Hi,
>Could u try installing the latest MDAC and verify.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-
>b037-185d0506396c&DisplayLang=en
>Thanks
>Hari
>MCDBA
>"illworth@.hotmail.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
>
>.
>
connection error message.
First you receive a 'Test Completed Successfully'
Then you receive 'Login failed. Catalog information
cannot be retrieved.'
Hi,
Could u try installing the latest MDAC and verify.
http://www.microsoft.com/downloads/d...fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Thanks
Hari
MCDBA
"illworth@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
> What is the solution to the following udl test
> connection error message.
> First you receive a 'Test Completed Successfully'
> Then you receive 'Login failed. Catalog information
> cannot be retrieved.'
>
|||We have 2.71 installed. Same udl copied to another
workstation works.
>--Original Message--
>Hi,
>Could u try installing the latest MDAC and verify.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-
>b037-185d0506396c&DisplayLang=en
>Thanks
>Hari
>MCDBA
>"illworth@.hotmail.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
>
>.
>
catalog information cannot be retrieved
What is the solution to the following udl test
connection error message.
First you receive a 'Test Completed Successfully'
Then you receive 'Login failed. Catalog information
cannot be retrieved.'Hi,
Could u try installing the latest MDAC and verify.
http://www.microsoft.com/downloads/...0fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Thanks
Hari
MCDBA
"illworth@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
> What is the solution to the following udl test
> connection error message.
> First you receive a 'Test Completed Successfully'
> Then you receive 'Login failed. Catalog information
> cannot be retrieved.'
>|||We have 2.71 installed. Same udl copied to another
workstation works.
>--Original Message--
>Hi,
>Could u try installing the latest MDAC and verify.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-
>b037-185d0506396c&DisplayLang=en
>Thanks
>Hari
>MCDBA
>"illworth@.hotmail.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
>
>.
>
connection error message.
First you receive a 'Test Completed Successfully'
Then you receive 'Login failed. Catalog information
cannot be retrieved.'Hi,
Could u try installing the latest MDAC and verify.
http://www.microsoft.com/downloads/...0fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Thanks
Hari
MCDBA
"illworth@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
> What is the solution to the following udl test
> connection error message.
> First you receive a 'Test Completed Successfully'
> Then you receive 'Login failed. Catalog information
> cannot be retrieved.'
>|||We have 2.71 installed. Same udl copied to another
workstation works.
>--Original Message--
>Hi,
>Could u try installing the latest MDAC and verify.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-
>b037-185d0506396c&DisplayLang=en
>Thanks
>Hari
>MCDBA
>"illworth@.hotmail.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
>
>.
>
Sunday, February 12, 2012
CAST statement
It must be something I'm overlooking but I keep getting an error message that this statement can't parse.
UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'
Any help would be greatly appreciated."LEFT(supplier,LEN(supplier-4))" should be "LEFT(supplier,LEN(supplier)-4)"
D'oh!
Also, you don't need to cast a numeric value that is already a string...|||"LEFT(supplier,LEN(supplier-4))" should be "LEFT(supplier,LEN(supplier)-4)"
D'oh!
Also, you don't need to cast a numeric value that is already a string...
thanks for the help i realized after it was too late:D
UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'
Any help would be greatly appreciated."LEFT(supplier,LEN(supplier-4))" should be "LEFT(supplier,LEN(supplier)-4)"
D'oh!
Also, you don't need to cast a numeric value that is already a string...|||"LEFT(supplier,LEN(supplier-4))" should be "LEFT(supplier,LEN(supplier)-4)"
D'oh!
Also, you don't need to cast a numeric value that is already a string...
thanks for the help i realized after it was too late:D
Subscribe to:
Posts (Atom)