C# - executing bcp command on a database residing on the remote server

shigarr

Hi guys,

I have the following query that executes BCP on a database residing on the remote server

EXEC XP_CMDSHELL 'bcp "SELECT [surname],'', ''+[forename] AS forename, [honours] AS honours, REPLACE(REPLACE(REPLACE([body_text], ''&'', ''&''), ''..'', ''.''), '';.'', ''.'') + CHAR(10) AS body_text FROM [Doctors_Latest].[dbo].[vw_ks_xml_practitioners_output] AS doctor WHERE surname like ''A%'' or surname like ''*A%'' order by strSurnameSortkey, strForenameSortkey FOR XML PATH(''doctor''), ROOT(''root'')" queryout "F:TestMedicalPract_SURNAME_STARTS_WITH_AMedical_Practitioners_Surname_Start_with_A_201115_112631.xml" -c -C65001 -r -q -S ServerName -t -U username -P password '

This query works fine if executed from within SQL Server Management Studio on the client machine but does not work from the code written in C# (residing on the client machine) :

private void ExecuteQuery()

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandTimeout = 5000;

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = query;

cmd.StatementCompleted += new StatementCompletedEventHandler(cmd_StatementCompleted);

OnTableProgressStart(this.message, queryCount);

int rows = cmd.ExecuteNonQuery();

queryCount++;

}

ExecuteNonQuery always returns 3

Any idea please?

I wonder why it works from within SQL Server Management Studio and not from a code written in C#

Thanks!