it's very urgent regarding XP_SEND MAIL

Hi,
I have a problem in sending mail from sql server 2000 which is using
windows 2000 professional and outlook express
Iam pasting my code below
/
************************************************************************************************************
Create procedure Pub_SendingMail
as
declare
@.EmailAddTO varchar(30),
@.EmailSubject varchar(130),
@.EmailText varchar(255),
@.return int,
@.Counting int
@.
begin
/* SET value */
set @.return = 0
set @.Counting = 0
set @.EmailSubject = 'TEST EMAIL'
set @.EmailText = 'This is a test email'
set @.EmailAddTO = 'nagesh@.emids.com'
/* LOOP. If e-mail is sent, break loop; ELSE WAIT 10 seconds, and
then RETRY. */
WHILE 1=1
begin
set @.Counting = @.Counting + 1
exec @.return = master.dbo.xp_sendmail
@.recipients = @.EmailAddTO,
@.message = @.EmailText ,
@.subject = @.EmailSubject
@.attachments = 'c:\attachment.txt'
/* CHECK value, break if SUCCESS */
if @.return = 0
begin
print 'EMAIL SENT'
break
end
else
begin
/* Try 1 times */
if @.Counting = 1
break
print 'EMAIL FAILED, WAIT 10 SECONDS, TRY AGAIN'
/*000 hours, 00 minutes, and 10 seconds */
waitfor delay '000:00:03'
end
end
end
*****************************************************************************************************************/
and when iam executing
EXEC Pub_SendingMail
it is giving the following error
Server: Msg 18030, Level 16, State 1, Line 0
xp_sendmail: Either there is no default mail client or the current
mail client cannot fulfill the messaging request. Please run Microsoft
Outlook and set it as the default mail client.
but here i need to send mail not only to out look express but also
general mail servers
I would deeply appriciate if any body can help me about this issue
and send any modifications in the above code.And also how to configure
the Microsoft Outlook Express as the default mail client.
it is very very very very very urgent
Regards
prasadXp_sendmail cannot use Outlook Express. Do yourself a big favor and use xp_smtp_sendmail from
www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shyam.yarlagadda@.gmail.com> wrote in message
news:1170414875.690570.8220@.v45g2000cwv.googlegroups.com...
> Hi,
> I have a problem in sending mail from sql server 2000 which is using
> windows 2000 professional and outlook express
> Iam pasting my code below
> /
> ************************************************************************************************************
> Create procedure Pub_SendingMail
> as
> declare
> @.EmailAddTO varchar(30),
> @.EmailSubject varchar(130),
> @.EmailText varchar(255),
> @.return int,
> @.Counting int
> @.
> begin
> /* SET value */
> set @.return = 0
> set @.Counting = 0
> set @.EmailSubject = 'TEST EMAIL'
> set @.EmailText = 'This is a test email'
> set @.EmailAddTO = 'nagesh@.emids.com'
> /* LOOP. If e-mail is sent, break loop; ELSE WAIT 10 seconds, and
> then RETRY. */
> WHILE 1=1
> begin
> set @.Counting = @.Counting + 1
> exec @.return = master.dbo.xp_sendmail
> @.recipients = @.EmailAddTO,
> @.message = @.EmailText ,
> @.subject = @.EmailSubject
> @.attachments = 'c:\attachment.txt'
> /* CHECK value, break if SUCCESS */
> if @.return = 0
> begin
> print 'EMAIL SENT'
> break
> end
> else
> begin
> /* Try 1 times */
> if @.Counting = 1
> break
> print 'EMAIL FAILED, WAIT 10 SECONDS, TRY AGAIN'
> /*000 hours, 00 minutes, and 10 seconds */
> waitfor delay '000:00:03'
> end
> end
> end
> *****************************************************************************************************************/
> and when iam executing
> EXEC Pub_SendingMail
> it is giving the following error
> Server: Msg 18030, Level 16, State 1, Line 0
> xp_sendmail: Either there is no default mail client or the current
> mail client cannot fulfill the messaging request. Please run Microsoft
> Outlook and set it as the default mail client.
> but here i need to send mail not only to out look express but also
> general mail servers
> I would deeply appriciate if any body can help me about this issue
> and send any modifications in the above code.And also how to configure
> the Microsoft Outlook Express as the default mail client.
> it is very very very very very urgent
> Regards
> prasad
>|||You'll need to set up a MAPI mail profile for this to work. Alternatively
you could use the XPSMTP.DLL which is free
(http://www.sqldev.net/xp/xpsmtp.htm).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Item cannot be found in the collection corresponding to the requested name or ordinal.

I am retrieving the data using the below sql statement in VB 6.0 & it giving me the Error
Item cannot be found in the collection corresponding to the requested name or ordinal.
I have tried to run the same sql statement it ran fine
I am using a recordset to retrive a data & it looks like recorset do have a data init but when I am assigning it to a temp variable it ging me th eabove stated error
strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name & "'"
objRS.CursorLocation = enmCursorLocation
Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType, enmLockType, lngOptions) If (Not objRS Is Nothing) Then
If (Not objRS Is Nothing) Then
dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
end if
Please advice
Thanks
Your column is no longer called amount, since you are performing
aggregations against it. You can change your SQL statement to provide an
alias, e.g.
SELECT Amount = SUM(...) FROM ...
Or you can use a different, more meaningful name, e.g.
SELECT SumAmount = SUM(...) FROM ...
Or in your code you can refer to the ordinal position of the column, instead
of the name.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"SAMAY" <anonymous@.discussions.microsoft.com> wrote in message
news:6C1456DD-FBC0-4ADC-8B99-F7BFA10821F1@.microsoft.com...
> I am retrieving the data using the below sql statement in VB 6.0 & it
giving me the Error
> Item cannot be found in the collection corresponding to the requested name
or ordinal.
> I have tried to run the same sql statement it ran fine
> I am using a recordset to retrive a data & it looks like recorset do have
a data init but when I am assigning it to a temp variable it ging me th
eabove stated error
> strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name
& "'"
> objRS.CursorLocation = enmCursorLocation
> Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType,
enmLockType, lngOptions) If (Not objRS Is Nothing) Then
> If (Not objRS Is Nothing) Then
> dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
> end if
> Please advice
> Thanks
|||Thanks for your help
it worked
Thanks again

Item cannot be found in the collection corresponding to the requested name or ordinal.

I am retrieving the data using the below sql statement in VB 6.0 & it giving
me the Error
Item cannot be found in the collection corresponding to the requested name o
r ordinal.
I have tried to run the same sql statement it ran fine
I am using a recordset to retrive a data & it looks like recorset do have a
data init but when I am assigning it to a temp variable it ging me th eabov
e stated error
strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name &
"'"
objRS.CursorLocation = enmCursorLocation
Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType, enmLockTy
pe, lngOptions) If (Not objRS Is Nothing) Then
If (Not objRS Is Nothing) Then
dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
end if
Please advice
ThanksYour column is no longer called amount, since you are performing
aggregations against it. You can change your SQL statement to provide an
alias, e.g.
SELECT Amount = SUM(...) FROM ...
Or you can use a different, more meaningful name, e.g.
SELECT SumAmount = SUM(...) FROM ...
Or in your code you can refer to the ordinal position of the column, instead
of the name.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"SAMAY" <anonymous@.discussions.microsoft.com> wrote in message
news:6C1456DD-FBC0-4ADC-8B99-F7BFA10821F1@.microsoft.com...
> I am retrieving the data using the below sql statement in VB 6.0 & it
giving me the Error
> Item cannot be found in the collection corresponding to the requested name
or ordinal.
> I have tried to run the same sql statement it ran fine
> I am using a recordset to retrive a data & it looks like recorset do have
a data init but when I am assigning it to a temp variable it ging me th
eabove stated error
> strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name
& "'"
> objRS.CursorLocation = enmCursorLocation
> Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType,
enmLockType, lngOptions) If (Not objRS Is Nothing) Then
> If (Not objRS Is Nothing) Then
> dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
> end if
> Please advice
> Thanks|||Thanks for your help
it worked
Thanks again

Issueing a CREATE TABLE statement within a transaction

Sorry new to the newsgroups so please point me to where I need to be if this is the wrong group. Below is a chunck of code that I have written to play around with transactions in Visual Studion 2005. I want to be able to create a table, the columns in it, and add rows of data and if any of those statements fail I woudl like to rollback the entire transaction. My testing has shown that when the CREATE TABLE sql is issued it automatcially commits. In my example below the VER_INFO table is immediately created when the ExecuteNonQuery() statement is invoked. The next 2 insert statements are held correctly in the transaction, however when the second table, WADES_TEST is created that causes the 2 rows to be inserted and the second table is immediately created. So my commit statement is useless. Is this normal? Is there a way to force the system to keep the CREATE TABLE statement in the transaction so I can roll it back if needed?

OracleCommand cmd = new OracleCommand();

OracleConnection wConnection = new OracleConnection(wConnString);

wConnection.Open();

OracleTransaction trans = wConnection.BeginTransaction();

string wQry = "CREATE TABLE VER_INFO (VER NVARCHAR2(25))";

cmd.CommandText = wQry;

cmd.Connection = wConnection;

cmd.Transaction = trans;

try

{

cmd.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('INITIALIZED')";

OracleCommand cmd4 = new OracleCommand(wQry, wConnection, trans);

cmd4.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('testss')";

OracleCommand cmd2 = new OracleCommand(wQry, wConnection, trans);

cmd2.ExecuteNonQuery();

wQry = "CREATE TABLE WADES_TEST (WADE NVARCHAR2(25))";

OracleCommand cmd3 = new OracleCommand(wQry, wConnection, trans);

cmd3.ExecuteNonQuery();

trans.Commit();

wConnection.Dispose();

}

catch

{

trans.Rollback();

wConnection.Close();

}

Thanks in advance,

Wade Sharp

This might be more appropriate to be posted to an Oracle forum or newsgroup. Or to an ADO.NET forum.

Issue with SqlUserDefinedAggregate

I am using the code below but I am getting a "zero" result for
dbo.AggredIssue('Test') user defined aggregate everytime that the query
executes parallel processing and uses the "Merge" method. It seems that my
private variable "private List<string> myList" gets nullified everytime it
goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was
able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries)
that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
public void Write(BinaryWriter w) {
w.Write(myList.Count);
}
}"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:60C66368-EFD5-4C6B-A9EF-FC363A91C1DB@.microsoft.com...
>I am using the code below but I am getting a "zero" result for
> dbo.AggredIssue('Test') user defined aggregate everytime that the query
> executes parallel processing and uses the "Merge" method. It seems that my
> private variable "private List<string> myList" gets nullified everytime it
> goes through the "Merge".
> I saw other people reporting the same issue in other forums, but nobody
> was
> able to provide a solution or explanation.
> See below a simplified version of my code (posted just after the queries)
> that replicates the issue.
> The query below works because it does't process the query in parallel.
> SELECT GroupID, dbo.AggregIssue('Test')
> FROM MyTable
> where fund = 2
> group by GroupID
>
> The query below doesn't work because it process the query in parallel.
>
Yikes! How on earth do you test the merge method of a CLR Aggregate?
Perhaps you could cook up an appropriate plan guide?
David|||Hello Fernando,
F> I am using the code below but I am getting a "zero" result for
F> dbo.AggredIssue('Test') user defined aggregate everytime that the
F> query executes parallel processing and uses the "Merge" method. It
F> seems that my private variable "private List<string> myList" gets
F> nullified everytime it goes through the "Merge".
I don't believe BinaryRead and BinaryWrite method isn't preserving your List
<T>
as you expect it does. Here's example that serializes such a between calls
to merge.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSVBuilder : IBinarySerialize, INullable
{
List<string> _list = null;
public void Init()
{
_list = new List<string>();
}
public void Accumulate(SqlString Value)
{
_list.Add(Value.Value);
}
public void Merge(CSVBuilder Group)
{
_list.AddRange(Group._list);
}
public SqlString Terminate()
{
StringBuilder sb = new StringBuilder(8000);
foreach (string item in _list) {
sb.Append(", ");
sb.Append(item);
}
return new SqlString(sb.ToString().Substring(2));
}
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
int size = r.ReadInt32();
BinaryFormatter f = new BinaryFormatter();
_list = (List<string> )(f.Deserialize(new MemoryStream(r.ReadBytes(size))));
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
BinaryFormatter f = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
f.Serialize(ms,_list);
Int32 size = (Int32)ms.Length;
w.Write(size);
w.Write(ms.ToArray(), 0, (int)size);
}
bool INullable.IsNull
{
get { return _list == null; }
}
}
It seems to work with this.
USE scratch
go
create table dbo.vs(v varchar(50));
insert into dbo.vs values ('Alpha')
insert into dbo.vs values ('Bravo')
insert into dbo.vs values ('Charlie')
insert into dbo.vs values ('Delta')
insert into dbo.vs values ('Echo')
insert into dbo.vs values ('Foxtrot')
insert into dbo.vs values ('Golf')
insert into dbo.vs values ('Hotel')
insert into dbo.vs values ('India')
insert into dbo.vs values ('Juliet')
insert into dbo.vs values ('Kilo')
insert into dbo.vs values ('Lima')
insert into dbo.vs values ('Mike')
insert into dbo.vs values ('November')
insert into dbo.vs values ('Oscar')
insert into dbo.vs values ('Papa')
insert into dbo.vs values ('Quebec')
insert into dbo.vs values ('Romeo')
insert into dbo.vs values ('Sierra')
insert into dbo.vs values ('Tango')
insert into dbo.vs values ('Uniform')
insert into dbo.vs values ('Victor')
insert into dbo.vs values ('Whiskey')
insert into dbo.vs values ('Yankee')
insert into dbo.vs values ('Zulu')
go
select dbo.csvbuilder(v) from dbo.vs
go
drop table dbo.vs
go
Returns:
Alpha, Bravo, Charlie, Delta, Echo, Foxtrot, Golf, Hotel, India, Juliet,
Kilo, Lima, Mike, November, Oscar, Papa, Quebec, Romeo, Sierra, Tango, Unifo
rm,
Victor, Whiskey, Yankee, Zulu
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thank you very much for your response. The reason I don't serialize the list
itself is because the list exceeds the limit of 8000 for maxbytesize.
I need the whole list to process the resolution of a non-linear equation, so
I cannot pre-aggregate values to save space before serializing. So what I am
trying to do is to solve the equation (using the required data from the list
)
just before serialization.
The weird thing is that it works if the code doesn't go through the merge
method.
If you can think of some other alternative...
Thank you very much,
Fernando|||"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
> Hi Kent,
> Thank you very much for your response. The reason I don't serialize the
> list
> itself is because the list exceeds the limit of 8000 for maxbytesize.
> I need the whole list to process the resolution of a non-linear equation,
> so
> I cannot pre-aggregate values to save space before serializing. So what I
> am
> trying to do is to solve the equation (using the required data from the
> list)
> just before serialization.
> The weird thing is that it works if the code doesn't go through the merge
> method.
> If you can think of some other alternative...
>
As a workaround you can always prevent a parallel plan with a MAXDOP hint.
David|||David,
Thanks for your response. The option "MAXDOP 1" worked. It would be nicer if
parallelism is enabled, but for now it a good workaround.
Thanks again,
Fernando
"David Browne" wrote:

> "Fernando" <Fernando@.discussions.microsoft.com> wrote in message
> news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
>
> As a workaround you can always prevent a parallel plan with a MAXDOP hint.
> David
>
>|||Hello Fernando,
F> The weird thing is that it works if the code doesn't go through the
F> merge method.
Right, I suspect the that the merge method if actually building instances
of the UDA on different CPUs and when has to marshal them together (merging
the threads if you will), that's when it calls the serialization stuff and
that's when you lose your values.
F> If you can think of some other alternative...
Don't use a UDA, use a procedure or function if possible.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent!

> Hello Fernando,
> F> The weird thing is that it works if the code doesn't go through the
> F> merge method.
> Right, I suspect the that the merge method if actually building instances
> of the UDA on different CPUs and when has to marshal them together (mergin
g
> the threads if you will), that's when it calls the serialization stuff and
> that's when you lose your values.
You are probably right, and that would be the reason why my code is failing.

> F> If you can think of some other alternative...
> Don't use a UDA, use a procedure or function if possible.
The solution is much simpler with UDA as the List used in the aggregation is
dynamically populated based on the items in the "Group By" of the query.
The workaround provided by David Browne (force non-parallelism) will work
for now.
Thank you very much for your help!
Fernando

>

Issue with SqlUserDefinedAggregate

I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge".

I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation.

See below a simplified version of my code (posted just after the queries) that replicates the issue.

The query below works because it does't process the query in parallel.

SELECT GroupID, dbo.AggregIssue('Test')

FROM MyTable

where fund = 2

group by GroupID

The query below doesn't work because it process the query in parallel.

SELECT GroupID, dbo.AggregIssue('Test')

FROM MyTable

where fund <= 20

group by GroupID

[Serializable]

[SqlUserDefinedAggregate(

Format.UserDefined,

IsInvariantToNulls = true,

IsInvariantToDuplicates = false,

IsInvariantToOrder = true,

MaxByteSize = 1000)]

public class AggregIssue : IBinarySerialize {

private List<string> myList;

private int myResult;

public void Init() {

myList = new List<string>();

}

public void Accumulate(SqlString Value) {

if (Value.IsNull) { return; }

myList.Add(Value.ToString());

}

public void Merge(AggregIssue Other) {

if (Other.myList != null) {

if (myList == null) {

myList = Other.myList;

}

else {

myList.AddRange(Other.myList);

}

}

}

public SqlInt32 Terminate() {

return new SqlInt32(myResult);

}

public void Read(BinaryReader r) {

myResult = r.ReadInt32();

}

//The code below is simplified for posting in the forum.

//I do additional manipulation of the list and require

//the aggregation to be IBinarySerialize.

//But this code replicates the issue also

public void Write(BinaryWriter w) {

w.Write(myList.Count);

}

}

? If you want to maintain the list properly you should serialize/deserialize the list itself -- and not its count -- in the Read and Write methods. They may be called more than one during the course of aggregation. Your current code is probably failing because it's making assumptions about how and when these will be called. You should instead do something like: public void Write(BinaryWriter w) { w.Write(myList.Count); foreach (string theString in myList) w.Write(theString); } public void Read(BinaryReader r) { this.myList = new List<string>(); int numStrings = r.ReadInt32(); for (int i = 0; i<numStrings; i++) myList.Add(r.ReadString()); } ... Then, in your Terminate method: public SqlInt32 Terminate() { return new SqlInt32(myList.Count); } -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <FernandoT@.discussions..microsoft.com> wrote in message news:e4178255-6dd9-4b48-b5ae-eb072c88e314_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge". I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation. See below a simplified version of my code (posted just after the queries) that replicates the issue. The query below works because it does't process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund = 2 group by GroupID The query below doesn't work because it process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund <= 20 group by GroupID [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = true, MaxByteSize = 1000)] public class AggregIssue : IBinarySerialize { private List<string> myList; private int myResult; public void Init() { myList = new List<string>(); } public void Accumulate(SqlString Value) { if (Value.IsNull) { return; } myList.Add(Value.ToString()); } public void Merge(AggregIssue Other) { if (Other.myList != null) { if (myList == null) { myList = Other.myList; } else { myList.AddRange(Other.myList); } } } public SqlInt32 Terminate() { return new SqlInt32(myResult); } public void Read(BinaryReader r) { myResult = r.ReadInt32(); } //The code below is simplified for posting in the forum. //I do additional manipulation of the list and require //the aggregation to be IBinarySerialize. //But this code replicates the issue also public void Write(BinaryWriter w) { w.Write(myList.Count); } }|||

Hi Adam,

Thank you very much for your response.

The reason that I don't serialize the list itself is because of the MaxByteSize limitation of 8096. My list will easily go beyond that limitation. I test your solution and it works for a small list, but not for long ones.

I noticed that the list is loosing the information in the merge. If the query doesn't go through parallel threads, it works fine.

My assumption is that the serialization will happen before terminate and final output of aggregate value for each row of the result set.

Any other ideas?

Thanks!!

Fernando

|||Hi, Fernando,

I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.

About Merge() stuff, to my limited understanding:

If single thread (no parallel op):

Init() -> Accumulate() -> Terminate()

If multi threads (parallel plan):

T1: Init() -> Accumulate()
T2: Init() -> Accumulate() -> Merge( with T1)
T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()

This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.

In Adam's book Chapter 6, p. 195, I quote:

"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"

I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?

Regards,

Dong Xie
|||? Actually, that quote from the book is not quite accurate anymore -- when I wrote it against an earlier CTP it appeared to be true, but now it does not. I believe that it has been changed in a later printing of the book, but I'll check today with Apress to make sure. Thanks for pointing it out! Anyway, the correct phrasing at this point should be "the intermediate result can be serialized and deserialized up to one time per row of aggregated data" In other words, the SQL Server engine may not do it for every row (or even nearly that often in most cases), but you need to code your aggregate as if it will. There is not necessarily a direct/stated relationship between Merge and Read/Write, but it appears that when Merge is called the engine also does a round of serialization/deserialization. I'm not sure why, though. Hopefully Steven Hemingray or one of the other dataworks guys will show up in this thread and clarify! -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Dong Xie@.discussions.microsoft.com> wrote in message news:c5fafeee-262b-43a4-9295-10a7cb07d92a@.discussions.microsoft.com...Hi, Fernando,I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.About Merge() stuff, to my limited understanding:If single thread (no parallel op):Init() -> Accumulate() -> Terminate()If multi threads (parallel plan):T1: Init() -> Accumulate()T2: Init() -> Accumulate() -> Merge( with T1)T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.In Adam's book Chapter 6, p. 195, I quote:"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?Regards,Dong Xie|||

It seems that you are right and serialization/deserialization happens when merge is called.

For now I have a workaround that was suggested in another forum to use "MAXDOP=1" and it works as merge is never executed.

It is not ideal, as parallelism cannot be leveraged, but it is a workaround.

Thanks to all for the responses!!

Fernando

|||

There seems to be a few issues within the thread:

1) Why is the private field myList nullified?

Whenever serialization takes place (Read/Write), a new instance is instantiated and it is up to your serialization code to fill the instance. Since your Write() only sets myResult, myList will be remain on the default value of null.

2) Why is MaxByteSize not always enforced?

MaxByteSize is enforced during serialization. You could cause instances to become much larger than 8k over Accumulate() calls and then not serialize out 8k.

With this said, there should not be a reason to build UDAggs that become larger than 8k but do not serialize out 8k. Read/write should serialize all necessary information for the UDAggs.

The UDAgg code within this thread is a good example of a contrived case for this. Since Terminate() only returns the count, there is not a need to accumulate the actual strings, but Accumulate() could increment a counter instead.

3) When is Read/Write called?

Within SQL Server 2005, serialization takes place during Merge() and before Terminate() is called. If the UDAgg provided accessed the myList within Terminate(), you would see a NullReferenceException from within Terminate() (since Write() does not reconstruct myList and null is its default reference).

As pointed out within this thread, Read/Write is not called for every row. However, please write your UDAgg with proper serialization semantics as if it were called every row.

Hope that helps!

-Jason

|||

Jason,

Thanks for all your explanation. It makes sense to me now how all this work.

But I still have one issue, which is the limitation of 8K. The list that I am building cannot be contrived until it is complete just before "Terminate". In the example I am passing the count and I could do that in the merge also. But in my real case, I need the complete list to be used in the resolution of a non-linear equation, so I cannot do anything with it until I pass it to a iterative algorithm in order to solve the equation. But this list can grow bigger than 8K.

The only way to avoid so far is to limit the degree of parallelism to 1, so the code doesn't go to merge.

Any thoughts on this?

Thanks,

Fernando

|||

Fernando-

Merge is also called in some more complex queries (such as GROUP BY WITH CUBE).

This is an interesting workaround to the 8k limitation for your scenario. I do worry that your workaround leaves your UDAgg prone to internal changes within SQL Server causes serialization to be more frequently performed or eliminated. For this reason, I'd recommend always using an approach where instances created from Read/Write are no different than the original.

I cannot recommend using this approach for these reasons, but limiting DOP to 1 should eliminate Merge() calls for the most part. If you absolutely must use this workaround, I'd recommend safeguarding against your assumptions (Merge never called, serialization takes place once after all Accumulate calls but before Terminate):

Always throw an exception within your Merge() call so that in the case that Merge() is invoked, you'll know what the issue is and you can try to simplify the query so that Merge is not called.|||

Jason,

Thanks for all your help and explanations.

Fernando

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with checkpoints and Event handlers

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks.The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made.Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed.OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

Rajesh Sridharan wrote:

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks. The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made. Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed. OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

This is a complicated scenario but I think this is as expected. The only tasks for which the checkpoint is written to are those in the control-flow. In scenario 1 DFT1 has not completed therefore execution will start from there on the second execution. In scenario 2 DFT2 HAS completed therfore execution will start from DFT2 on the second execution.

Tasks in the eventhandler do not affect checkpoint files, now should they. If you have "things" happening in your package that are critical to the execution of the package then they should be in the control-flow.

-Jamie

|||

Thank you, Jamie. Appreciate your response.

Rajesh

Issue with cast function

Hi

Can someone help me in the following:
An example is shown below, I had assign a date (format: dd/mm/yyyy) to @.vdate. Follow by a cast to vchar. The issue is that the date format is changed after the cast. I would like the date format to remain as dd/mm/yyyy after doing the cast. Hope someone can give some advices. Thanks alot.

Example:

declare @.vdate as datetime
set @.vdate = convert(datetime,'01/10/2005',103)

select @.vdate --> '10/01/2005' (date format required)

cast(varchar(20),@.vdate) --> 'Oct 1, 2005' (date format changed)Dates are not stored in any format. They are actually stored as numeric values, which then are displayed in a requested format.
Read up in Books Online on the topics of datetime datatypes and the CAST and CONVERT functions.

Issue in using round in Vbscript

I am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>
Please don't ask the same question in more than one thread.
KritiVerma@.hotmail.com wrote:

> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
>
>

Issue in using round in Vbscript

I am using a Round to get the Value of a Float datatype column from a databa
se
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column f
or Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>Please don't ask the same question in more than one thread.
KritiVerma@.hotmail.com wrote:

> I am using a Round to get the Value of a Float datatype column from a data
base
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
>
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a database
and if the use the below code and if the value of the Column is say 0 it returns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samay
I am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>
|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
[vbcol=seagreen]
> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype[vbcol=seagreen]
> please advice why this can be
> Temp = round(0* 0.95,2)
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a databa
se
and if the use the below code and if the value of the Column is say 0 it ret
urns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samayI am using a Round to get the Value of a Float datatype column from a databa
se
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column f
or Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
[vbcol=seagreen]
> I am using a Round to get the Value of a Float datatype column from a data
base
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype[vbcol=seagreen]
> please advice why this can be
> Temp = round(0* 0.95,2)
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a database
and if the use the below code and if the value of the Column is say 0 it returns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samayI am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)
> samay
>|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
>>samay
>>|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
> > samay
> >