SQL Command string c#

Question

I'm currently in the process of writing a small script that exports the event log of a program and writes it to a database. However, I am having problems with my SQL insert command. What am I doing wrong? This is just a code cutout

string command = "DECLARE @x xml;";
command += "SELECT @x = P";
command += "FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";
command += "SELECT @x";
command += "DECLARE @hdoc int";
command += "EXEC sp_xml_preparedocument @hdoc, OUTPUT, @x";
command += "INSERT INTO [MFlogs].[dbo].[MLogs]";
command += "SELECT * FROM OPENXML(@hdoc, '/Export/root/event/data/objectversion/objver', 2);";
command += "WITH(";
command += "id int '../../../id',";
command += "type varchar(50) '../../../type',";
command += "category varchar(50) '../../../category',";
command += "timestamp varchar(50) '../../../timestamp',";
command += "causedbyuser varchar(50) '../../../causedbyuser',";
command += "objtype varchar(50),";
command += "objid varchar(50),";
command += "version varchar(50),";
command += "objectguid varchar(50) '../objectguid',";
command += "versionguid varchar(50) '../versionguid',";
command += "title varchar(100) '../title',";
command += "displayid int '../displayid')";

SqlCommand cmd = new SqlCommand(command, connection);
int result = cmd.ExecuteNonQuery();

This is the error from the event log:

The @ hdoc scalar variable must be declared.

Can someone help?

Consider to use StringBuilder instead of += string operator. It is particulary inefficient — Lorenzo Isidori
– Lorenzo Isidori, Commented Oct 26, 2020 at 9:00
SELECT @x = P this statement is wrong you need to use SET @x = 'P' (OK BOTH WORK CORECTLY BUT 'P' MUST ME IN QUOTES) — Muhammad Waqas Aziz
– Muhammad Waqas Aziz, Commented Oct 26, 2020 at 9:08

E.J. Brennan · Accepted Answer · 2020-10-26 08:56:53Z

0

Just looking at the first lines, and you are going to have problems:

    command += "SELECT @x = P";
    command += "FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";

Is going to end up like:

SELECT @x = PFROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORI ....

because you haven't left any spacing, like this, but everywhere else you will need it as well:

command += "SELECT @x = P";
command += " FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";

Try fixing all the spacing issues and see what happens.

answered Oct 26, 2020 at 8:56

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SiarheiK · Accepted Answer · 2020-10-26 09:44:57Z

-2

You don't need a comma before OUTPUT.

Replace

command += "EXEC sp_xml_preparedocument @hdoc, OUTPUT, @x";

With

command += " EXEC sp_xml_preparedocument @hdoc OUTPUT, @x";

See https://learn.microsoft.com/ru-ru/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql?view=sql-server-ver15

Also add a space on the start of each string, or Environment.NewLine or use StringBuilder.AppendLine.

edited Oct 26, 2020 at 9:44

answered Oct 26, 2020 at 9:01

SiarheiK

8375 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL Command string c#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related