A severe error occurred on the current command. sp_AddMergePublication

[david.brunning]

I have a C# application in which I am allowing users that are members
of sysadmins on SQL Server to create publications. The first thing
that happens is the application sets up a loal distributor on the SQL
server if one doesn't exist and then tries to set up a merge
publication.

The command I am running is:

exec DBName..sp_addmergepublication
@publication = N'COL01',
@description = N'Merge publication (COL01) configured against
DBName',
@retention = 120,
@sync_mode = N'character',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@centralized_conflicts = N'true',
@dynamic_filters = N'true',
@snapshot_in_defaultfolder = N'false',
@alt_snapshot_folder = N'\\laptop022\repldata',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_login = N'anonymous',
@conflict_retention = 120,
@keep_partition_changes = N'true',
@allow_subscription_copy = N'false',
@allow_synctoalternate = N'false',
@validate_subscriber_info = N'SUSER_SNAME()',
@add_to_active_directory = N'false',
@max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0

The result is a SqlException: A severe error occurred on the current
command. The results,
if any, should be discarded.

If I run the same query through Query Analyzer using the same user
credentials the publication is created OK and I can add articles to it
etc.

I am running SQL Server Developer Edition (For now) with SP3a applied
on Windows XP with SP2 applied. I have run this application
successfully on many other machines running developer edition, standard
edition, personal edition and enterprise edition in my own organisation
and in client organisations and this is the first time I have seen this
error.

Any help gratefully received.

 

[Guest]

What type of app is running this code? If a web app, turn off anonymous.

I would profile SQL Server and watch who the code is running under and
ensure it is your user and not some other account. That would be the first
line of troubleshooting, regardless.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[david.brunning]

The application is a fat client C# .NET 1.1 application.
The credentials used in the connection string are the same as in
profiler and the same used in Query Analyzer to run the same query.
I've profiled it to check this out which gave me the command string to
test in Query Analyzer which I also then put into a test console
application (Code below) which also fails with the same error. Query
Analyzer runs the SP OK.

I have removed and reinstalled SQL Server 2000 and SP3a
I have reinstalled .NET Framework 1.1
I have reinstalled my C# application
I have tried different databases

I have also tried running the application in debug mode - when I skip
over the SQLException that results from the sp_addmergepublication call
and continue the processing the application runs through to completion
and creates the snapshot agent - I can then run the snapshot agent and
start synchronising against the publication (Actually, I haven't tried
synchronising yet - I need to do this next).

-----Code from console application------

class Class1
{
private static string DBServer = "laptop022";
private static string DBName = "DBName";

[STAThread]
static void Main(string[] args)
{
try
{
string connectionString = "Data Source=" + DBServer+
";UID=Keystone_System;Pwd=Keystone;Initial Catalog="+DBName;
SqlConnection sqlConn = new SqlConnection(connectionString);
SqlParameter param = null;

string cmdText = "exec DBName..sp_addmergepublication @publication
= N'COL01', @description = N'Merge publication (COL01) configured
against DBName', @retention = 120, @sync_mode = N'character',
@allow_push = N'true', @allow_pull = N'true', @allow_anonymous =
N'true', @enabled_for_internet = N'false', @centralized_conflicts =
N'true', @dynamic_filters = N'true', @snapshot_in_defaultfolder =
N'false', @alt_snapshot_folder = N'\\laptop022\repldata',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login =
N'anonymous', @conflict_retention = 120, @keep_partition_changes =
N'true', @allow_subscription_copy = N'false', @allow_synctoalternate =
N'false', @validate_subscriber_info = N'SUSER_SNAME()',
@add_to_active_directory = N'false', @max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0";
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText= cmdText;

param = cmd.Parameters.Add("@result",SqlDbType.NVarChar);
param.Direction = ParameterDirection.Output;
param.Size = 6000;
sqlConn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine (param.Value.ToString());
sqlConn.Close();
}
catch(SqlException e)
{
Console.WriteLine(e.Message );
}
}
}

 

[William \(Bill\) Vaughn]

Do you call this SP using a single parameter each time? If so, I would set
the default values to the values you're providing in code.
I would also:
1) Never use sp_ as the name of a SP. This tells SS that the SP is a
"system" SP and master is located in the master DB. This slows execution.
2) Use CommandType stored procedure.
3) Build and populate the Parameters collection. For those parameters that
should take the SP default, simply don't add the Parameter to the collection
or set them to Nothing in VB or null in C#.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

The application is a fat client C# .NET 1.1 application.
The credentials used in the connection string are the same as in
profiler and the same used in Query Analyzer to run the same query.
I've profiled it to check this out which gave me the command string to
test in Query Analyzer which I also then put into a test console
application (Code below) which also fails with the same error. Query
Analyzer runs the SP OK.

I have removed and reinstalled SQL Server 2000 and SP3a
I have reinstalled .NET Framework 1.1
I have reinstalled my C# application
I have tried different databases

I have also tried running the application in debug mode - when I skip
over the SQLException that results from the sp_addmergepublication call
and continue the processing the application runs through to completion
and creates the snapshot agent - I can then run the snapshot agent and
start synchronising against the publication (Actually, I haven't tried
synchronising yet - I need to do this next).

-----Code from console application------

class Class1
{
private static string DBServer = "laptop022";
private static string DBName = "DBName";

[STAThread]
static void Main(string[] args)
{
try
{
string connectionString = "Data Source=" + DBServer+
";UID=Keystone_System;Pwd=Keystone;Initial Catalog="+DBName;
SqlConnection sqlConn = new SqlConnection(connectionString);
SqlParameter param = null;

string cmdText = "exec DBName..sp_addmergepublication @publication
= N'COL01', @description = N'Merge publication (COL01) configured
against DBName', @retention = 120, @sync_mode = N'character',
@allow_push = N'true', @allow_pull = N'true', @allow_anonymous =
N'true', @enabled_for_internet = N'false', @centralized_conflicts =
N'true', @dynamic_filters = N'true', @snapshot_in_defaultfolder =
N'false', @alt_snapshot_folder = N'\\laptop022\repldata',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login =
N'anonymous', @conflict_retention = 120, @keep_partition_changes =
N'true', @allow_subscription_copy = N'false', @allow_synctoalternate =
N'false', @validate_subscriber_info = N'SUSER_SNAME()',
@add_to_active_directory = N'false', @max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0";
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText= cmdText;

param = cmd.Parameters.Add("@result",SqlDbType.NVarChar);
param.Direction = ParameterDirection.Output;
param.Size = 6000;
sqlConn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine (param.Value.ToString());
sqlConn.Close();
}
catch(SqlException e)
{
Console.WriteLine(e.Message );
}
}
}

 

[david.brunning]

Hi Bill,
Thanks for that, but you may have missed the point - perhaps I should
have been more explicit in my original post.

1) I realise what you are saying about SP's that begin "sp_" but this
*is* a system stored procedure.
2 & 3) The C# application uses a stored procedure command type and
builds the parameter collection exactly as you describe - the sample in
my original post is the text of the query that I ran in Query Analyzer.
The example in my follow up post was a simple throw together example
in a console application - it was quicker for me to copy and paste the
text from QA into the application to test it.

To restate - the problem appears to running the stored procedure via
ADO.NET raising the "Severe error occurred" exception - if I then take
the command text from a SQL Profiler session and run it in QA it works
every time.

Another interesting point, in code after the call to
sp_addmergepublication raises this exception, in the exception handler
I call sp_helpmergepublication (Another SYSTEM stored procedure) to see
if the publication was actually added successfully - this returns no
results so it is as if the publication hasn't yet been created. If I
break execution in the exception handler and then run
sp_helpmergepublication in QA this returns the new publication - odd.

Is it a timing issue? Is SQL having trouble keeping up? If this is
the case how can I force the issue? I have profiled SQLEM doing the
same thing and there are a couple of "commit transactions" happening
after stored procedure calls... perhaps I'll try this to waste a bit of
time and see if SQL catches up...

Anyone, any other useful ideas?

 

[david.brunning]

Bill,
I've just realised the reason you ask about the single parameter - my
apologies - the sample of code I posted is incorrect - in fact my
console application wasn't using any parameters (Although the full
application does).

The correct console application is posted below. Basically I stole the
console application idea from a MS KB article which talks about a bug
with nvarchar parameters exceeding the maximum size for the datatype.
Sorry to confuse things.

-----Code from console application------

class Class1
{
private static string DBServer = "laptop022";
private static string DBName = "DBName";

[STAThread]
static void Main(string[] args)
{
try
{
string connectionString = "Data
Source=" + DBServer+
";UID=Keystone_System;Pwd=Keystone;Initial Catalog="+DBName;
SqlConnection sqlConn = new
SqlConnection(connectionString);

string cmdText = "exec
DBName..sp_addmergepublication @publication
= N'COL01', @description = N'Merge publication (COL01) configured
against DBName', @retention = 120, @sync_mode = N'character',
@allow_push = N'true', @allow_pull = N'true', @allow_anonymous =
N'true', @enabled_for_internet = N'false', @centralized_conflicts =
N'true', @dynamic_filters = N'true', @snapshot_in_defaultfolder =
N'false', @alt_snapshot_folder = N'\\laptop022\repldata',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login =
N'anonymous', @conflict_retention = 120, @keep_partition_changes =
N'true', @allow_subscription_copy = N'false', @allow_synctoalternate =
N'false', @validate_subscriber_info = N'SUSER_SNAME()',
@add_to_active_directory = N'false', @max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0";
SqlCommand cmd =
sqlConn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText= cmdText;

sqlConn.Open();
cmd.ExecuteNonQuery();
sqlConn.Close();
}
catch(SqlException e)
{
Console.WriteLine(e.Message );
}
}
}

 

[William \(Bill\) Vaughn]

I would try to see if David Sceppa (Microsoft) would pick this up. I would
also file it in Ladybug (online bug reporting).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________