September 26, 2016 at 10:47 pm
I have a C# script task which scripts out transPublication objects to a flat file.
This script had been working in one of my test environments without issue, but now produces an error in that environment. The script itself didn't change, but the replications were dropped and recreated. I am not sure if by dropping and creating the replications some issue was introduced into the distribution database.
When the script now runs, I receive the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression."
The script continues to work ok in one of my other test environments which makes me think that there is something specific to the environment which causes this error.
I have included the script task code below. The error occurs on execution of the script method of the TransPublication object (ie TP.script(.....)). Has anyone seen this type issue before?
References required by C# module
------------------------------------------
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum
Microsoft.SqlServer.ManagedDTS
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Replication.NET Programming Interface
Microsoft.SqlServer.Management.Sdk.Sfc
C# script code
-------------------------------------
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Replication;
using System.IO;
using System.Collections.Specialized;
#endregion
namespace ST_9ae2a0771f60410fa177e6393ef1d42c
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string ServerName;
string FolderDate;
string NetworkPath;
ServerName = "xxxxxxxx";
FolderDate = DateTime.Now.ToShortDateString().Replace('\\', '_').Replace('/', '_');
NetworkPath = "D:\\hlq1\\hlq2\\" + ServerName.Replace('\\', ' ').ToString() + "\\Replicaiton\\" + FolderDate + "\\";
ServerConnection conn = new ServerConnection();
conn.LoginSecure = true;
conn.ServerInstance = ServerName;
Server srv = new Server(conn);
System.IO.Directory.CreateDirectory(NetworkPath);
ReplicationServer RS = new ReplicationServer(conn);
try
{
foreach (ReplicationDatabase RD in RS.ReplicationDatabases)
{
if (RD.HasPublications)
{
foreach (TransPublication TP in RD.TransPublications)
{
TextWriter twa = new StreamWriter(NetworkPath + "\\" + TP.Name.ToString() + ".sql");
twa.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludePublisherSideSubscriptions));
twa.Close();
}
}
}
}
catch (Exception eh)
{
MessageBox.Show(eh.ToString());
}
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
September 27, 2016 at 1:24 am
I have been able to get this to work by
- dropping all subscriptions
- dropping all publications
- setting replication database option to false (sp_replicationdboption)
- setting replication database option to true
- creating all publications
- creating all subscriptions
I am going to put this down to being a corrupt distribution database, unless someone has a better reason for it.
thanks to anyone who has spent time thinking about this
Regards,
Phil
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply