Putting System Variables in a Send Mail Task

KB Article 906547 suggest that it's possible to have system variables in the Subject & Body of a Send Email Task. Such as this:

"Package>>> " + @.[System::PackageName] +" was executed at>>> " + (DT_WSTR, 40) @.[System::StartTime] + " by user>>> " + @.[System::UserName] + " on Machine>>> " + @.[System::MachineName]

Or this in an OnError event handler:

"Error in the task: " + @.[System::SourceName] + "with the ID: " + @.[System::SourceID] + " has failed at: " + (DT_WSTR, 20) @.[System::ContainerStartTime] + "."

But when I do this I just get this exact text, no variables.

I'm obviously missing something basic but I can't find it. Can anyone offer a suggestion please?

I believe you are entering ""Package>>> " + @.[System::PackageName] +" was executed at>>> " + (DT_WSTR, 40) @.[System::StartTime] + " by user>>> " + @.[System::UserName] + " on Machine>>> " + @.[System::MachineName]" text directly in the 'Subject' field.

Since this is an expression you cannot enter in the Subject field directly instead you need to specify it as an expression.

Clear the Subject field - Select Expressions - Expand Expressions - Select 'Subject' for the property drop down - Specify "Package>>> " + @.[System::PackageName] +" was executed at>>> " + (DT_WSTR, 40) @.[System::StartTime] + " by user>>> " + @.[System::UserName] + " on Machine>>> " + @.[System::MachineName] in the Property field.

Thanks,
Loonysan

|||

Yes, that was it. Sorry I should have seen that I needed to put the items as an expression.

Thank You for your help.

Puslishing New Article Fail B/C Relationship Exists in Subscriber

Hello,
I add an article to a live publisher. The table to be added has foreign key
relations. At publisher, under "action if there is already an object": Drop
existing object and create a new one. The subscriber database was restored
from the publisher a while ago.
When I sync them (Replicate Schema Changes=True), I got an error at
subscriber saying that the table can not be dropped because the foreign
relationship.
Is there a way make the replication agent drop the relation of the table and
then create it without changing any other articles? Or anyway to avoid this
failure?
“Just don't drop the table” is an easy solution. However, my problem is that
the table schema has been changed since the database restored. So I still
have to apply the table changes to the subscriber.
Thank you in advance for help.
John
Hi John,
I understand that when you added an article to your live publication under
"action if there is already an object": Drop existing object and create a
new one, the following error came out during the synchronization:
the table can not be dropped because the foreign relationship. The
subscriber was restored from the publisher a while ago and the table schema
has been changed since the database restored.
If I have misunderstood, please let me know.
To let me better understand your issue, I would like to confirm with you
the following questions:
1. What is your replication type, snapshot, merge or transactional?
2. Did you mean that the added table existed in the database originally but
just the schema changed?
3. What is the synchronization type of your replication, web
synchronization or not?
4. Did you know which tables were being dropped and the relationships among
those tables and the added table?
5. Why are there some tables being dropped since the added table was not
replicated before?
I really hope that you could describe your environment more detailed so
that I can perform a test to reproduce your issue at my side. Look forward
to your response.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Paul,
Thank you very much. This is a great. Somehow my pre-publish script doesn't
run.
I think this should apply to merger replication although the link article
applies to SQL Server 2005 transactional and snapshot replication. What am I
missing?
System: SQL Server 2005, Merger replication. Please refer my next replying
post to Charles Wang for detailed infomation.
Thank you for help.
John
"Paul Ibison" wrote:

> You could use 2 sp_addscriptexecs to drop and readd the FK. Also to drop the
> FK you could specify @.pre_creation_cmd. Finally, in SQL Server 2005 there is
> a .pre script which should take care of this automatically:
> http://www.replicationanswers.com/PreSnapshotScripts2005.asp.
> HTH,
> Paul Ibison
>
>
|||Charles,
Thank you for response. Sorry for not offering details.
1.SQL 2005 merge
2.Yes. The table to be added is in both databases, just didn't publish before.
3. Try to use both Windows and web. I haven't pass any of them.
4. Yes. PK of this table is FK of the other's and anothe's PK is FK of this.
5. Business requires to he publish the existing table.
Regards,
John
"Charles Wang[MSFT]" wrote:

> Hi John,
> I understand that when you added an article to your live publication under
> "action if there is already an object": Drop existing object and create a
> new one, the following error came out during the synchronization:
> the table can not be dropped because the foreign relationship. The
> subscriber was restored from the publisher a while ago and the table schema
> has been changed since the database restored.
> If I have misunderstood, please let me know.
> To let me better understand your issue, I would like to confirm with you
> the following questions:
> 1. What is your replication type, snapshot, merge or transactional?
> 2. Did you mean that the added table existed in the database originally but
> just the schema changed?
> 3. What is the synchronization type of your replication, web
> synchronization or not?
> 4. Did you know which tables were being dropped and the relationships among
> those tables and the added table?
> 5. Why are there some tables being dropped since the added table was not
> replicated before?
> I really hope that you could describe your environment more detailed so
> that I can perform a test to reproduce your issue at my side. Look forward
> to your response.
> Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
>
>
|||OK - then I'd use the sp_addscriptexec method. You'd need to use this to
readd the FK anyway, so using it to drop the FK initialially is only a bit
more work.
HTH,
Paul Ibison
|||Hi John,
I am sorry for delaying this response.
Due to the existed foreign key constraint, when you drop the table whose PK
is FK of another table, it could not be dropped. I recommend that you first
run the following statement to drop the existed foreign key relations on
the table:
ALTER TABLE tablename DROP CONSTRAINT <foreign key index name>
After the synchronization, then run ALTER TABLE tablename ADD CONSTRAINT
(column name) REFERENCES <foreign table>(column name) to add the foreign
key.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||It works. Thanks.
I guess my previous pre-sub query failed was because of not enoguh access
right to the snapshot directory.
"Paul Ibison" wrote:

> OK - then I'd use the sp_addscriptexec method. You'd need to use this to
> readd the FK anyway, so using it to drop the FK initialially is only a bit
> more work.
> HTH,
> Paul Ibison
>
|||Hi Charles,
I thought SQL Server should save the relations, drop them, replicate the
schema chages and then restored the relations back from the saved. Please
refer to this:
http://www.replicationanswers.com/PreSnapshotScripts2005.asp.
Thank for response.
John
"Charles Wang[MSFT]" wrote:

> Hi John,
> I am sorry for delaying this response.
> Due to the existed foreign key constraint, when you drop the table whose PK
> is FK of another table, it could not be dropped. I recommend that you first
> run the following statement to drop the existed foreign key relations on
> the table:
> ALTER TABLE tablename DROP CONSTRAINT <foreign key index name>
> After the synchronization, then run ALTER TABLE tablename ADD CONSTRAINT
> (column name) REFERENCES <foreign table>(column name) to add the foreign
> key.
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi John,
Thanks for your feedback.
I understand your concerns. You may submit your suggestions to Microsoft
via the following link:
https://connect.microsoft.com/sql
Your suggestiosn will be routed to SQL product team so that this function
may be included in future.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Publishing database hangs on first article

When adding a databases to publications, the process gets stuck / goes into
endless loop on the first article of the database. This only happens on
databases that were previously replicated, others run through without the
slightest problem. We also checked the transactions issued with sp_who2 /
trace, it looks like the transaction goes into a loop of selecting and
updating. We have checked all the replication related system tables in an
attempt to properly remove any traces of previous publication, but to no
avail. Can anyone help? Thanks in advance
What build of SQL Server are you on? run select @.@.version to determine
this. Is it a build greater than 8.00.818?
You can try manually removing and recreating your publication as per KB:
How to manually remove a replication in SQL Server 2000 - ID: 324401
(available at http://support.microsoft.com)
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

publisher malfunction

Hi,
Has anyone seen a problem like this:
when trying to add an article to an existing publication (i.e. running
sp_addarticle) I get the following error if I run it from my workstation:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
the error is a bit more descriptive when running from the server console:
Server: Msg 8526, Level 16, State 2, Procedure sp_helpdistributor, Line 79
Cannot go remote while the session is enlisted in a distributed transaction
that has an active savepoint.
Connection Broken
Any input would be appreciated
run a dbcc opentrans and see if there is an open or orphaned transaction.
use dbcc inputbuffer to evaluate whether you can kill it or not. If so, kill
it and try to run the sp_addarticle command again.
Also is this with a remote distributor?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mor DeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
news:55DBD5B3-15F1-4130-8BB8-F3B2FA7294BC@.microsoft.com...
> Hi,
> Has anyone seen a problem like this:
> when trying to add an article to an existing publication (i.e. running
> sp_addarticle) I get the following error if I run it from my workstation:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> the error is a bit more descriptive when running from the server console:
> Server: Msg 8526, Level 16, State 2, Procedure sp_helpdistributor, Line 79
> Cannot go remote while the session is enlisted in a distributed
> transaction
> that has an active savepoint.
> Connection Broken
> Any input would be appreciated
|||Thanks Hilary, but it does not look like there is an open transaction. here
is the oputput of DBCC OPENTRAN:
Replicated Transaction Information:
Oldest distributed LSN : (2215838:4377:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Hilary Cotter" wrote:

> run a dbcc opentrans and see if there is an open or orphaned transaction.
> use dbcc inputbuffer to evaluate whether you can kill it or not. If so, kill
> it and try to run the sp_addarticle command again.
> Also is this with a remote distributor?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mor DeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
> news:55DBD5B3-15F1-4130-8BB8-F3B2FA7294BC@.microsoft.com...
>
>
|||I found a solution, in case anyone else is interested. It had to do with
"remote proc trans" option being turned on. Here is a link to the forum where
I found it:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18530
"Mor DeRor" wrote:
[vbcol=seagreen]
> Thanks Hilary, but it does not look like there is an open transaction. here
> is the oputput of DBCC OPENTRAN:
> Replicated Transaction Information:
> Oldest distributed LSN : (2215838:4377:1)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> "Hilary Cotter" wrote: