Ad Hoc Query in C#

[John Scott]

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using
\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
--
Thanks,

John Scott.

[John A. Bailo]

take out the carriage returns and line breaks

The /+/'s will let you break the lines in your c# code

Use an @ in front of the first " (double quote)

string strSQL = @"USE msdb " +
" if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
" EXEC sp_delete_job @job_name = 'TempBackupJob' " +
" GO";
John Scott wrote:

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using
\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.

[Amadelle]

Why do you need the \n and \r ? It is not like you have to print out the sql
string to make it readable. You just need to execute it and considering that
it is SQL all you need is good spacing between your SQL statements and
commands.

Try it with no \r and no \n and see what happens..

Good luck

Amadelle

"John Scott" wrote:

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using
\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
--
Thanks,

John Scott.

[John Scott]

When I take out the \r\n the command exceptions too.( Incorrect syntax near
'GO')
I also tested something in Query Anaylzer..if the SQL statment is all on one
line...it fails...if everything else EXCEPT GO is on the same line...the
query runs successfully. So...I am under the assumption that GO has to be on
its own line when it is evaluated. That's why I was wondering about line
breaks.

Got anything else ??

--
Thanks,

John Scott.
"Amadelle" wrote:

Why do you need the \n and \r ? It is not like you have to print out the sql
string to make it readable. You just need to execute it and considering that
it is SQL all you need is good spacing between your SQL statements and
commands.

Try it with no \r and no \n and see what happens..

Good luck

Amadelle

"John Scott" wrote:

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using
\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
--
Thanks,

John Scott.

[John Scott]

Thanks for the feedback...I took the carriage returns and line breaks out and
added the @ at the beginning of the string before the parenthesis...just like
your example..but that command failed too.

I also replied to Amadelle saying that when the command I'm trying to run is
all on one line in Quyery Anaylzer...it fails there as well. But if the
command is all on one line except for 'GO' it runs successfully. So if GO is
on its own line the command is good.

Any other ideas??
--
Thanks,

John Scott.
"John A. Bailo" wrote:

take out the carriage returns and line breaks

The /+/'s will let you break the lines in your c# code

Use an @ in front of the first " (double quote)

string strSQL = @"USE msdb " +
" if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
" EXEC sp_delete_job @job_name = 'TempBackupJob' " +
" GO";
John Scott wrote:

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using
\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.

[Jakob Christensen]

Take out "GO". "GO" will not work through ADO.

HTH, Jakob.

--
http://www.dotninjas.dk
http://www.powerbytes.dk
"John Scott" wrote:

When I take out the \r\n the command exceptions too.( Incorrect syntax near
'GO')
I also tested something in Query Anaylzer..if the SQL statment is all on one
line...it fails...if everything else EXCEPT GO is on the same line...the
query runs successfully. So...I am under the assumption that GO has to be on
its own line when it is evaluated. That's why I was wondering about line
breaks.

Got anything else ??

--
Thanks,

John Scott.
"Amadelle" wrote:

Why do you need the \n and \r ? It is not like you have to print out the sql
string to make it readable. You just need to execute it and considering that
it is SQL all you need is good spacing between your SQL statements and
commands.

Try it with no \r and no \n and see what happens..

Good luck

Amadelle

"John Scott" wrote:

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using
\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
--
Thanks,

John Scott.

[Jim Underwood]

Rather than using SQL like this just build a stored procedure that contains
the logic. Then call the stored procedure from your code. Always makes for
cleaner and safe code this way.

"John Scott" <jo*******@despammed.com> wrote in message
news:36**********************************@microsof t.com...

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using \r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
--
Thanks,

John Scott.

[John A. Bailo]

He could do that, or just parameterize his string sql query.

You should never put values in quotes in a string.

Use @vars and replace them with sql params, just like in a stored procedure.

Putting logic like this in a stored procedure isn't alway to my liking
(although it seems to be Gospel in the MS camp).

It's perfectly reasonable to use queries in ado, and it gives the
programmer far more control than having to go back and forth to sprocs
and code.
Jim Underwood wrote:

Rather than using SQL like this just build a stored procedure that contains
the logic. Then call the stored procedure from your code. Always makes for
cleaner and safe code this way.

"John Scott" <jo*******@despammed.com> wrote in message
news:36**********************************@microsof t.com...

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried

using

\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
--
Thanks,

John Scott.

[Jim Underwood]

I suppose it is a little different for me, since I have always had access to
the database as well as the application code.

I always like to put as much logic in the database as possible, simply
because I can change the stored procedure without needing to repackage and
deploy the application. It also makes for much cleaner code, although the
tradeoff is the programmer cannot see what is going on in the database.
This last piece can be either a pro or a con, depending on how knowledgeable
and involved the programmer is with the database code.

The other benefit is that it is much simpler to debug a single stored
procedure call than multiple lines of concatenated text.

"John A. Bailo" <ja*****@texeme.com> wrote in message
news:_s********************@speakeasy.net...

He could do that, or just parameterize his string sql query.

You should never put values in quotes in a string.

Use @vars and replace them with sql params, just like in a stored procedure.
Putting logic like this in a stored procedure isn't alway to my liking
(although it seems to be Gospel in the MS camp).

It's perfectly reasonable to use queries in ado, and it gives the
programmer far more control than having to go back and forth to sprocs
and code.
Jim Underwood wrote:

Rather than using SQL like this just build a stored procedure that contains the logic. Then call the stored procedure from your code. Always makes for cleaner and safe code this way.

"John Scott" <jo*******@despammed.com> wrote in message
news:36**********************************@microsof t.com...

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is asnippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried

using

\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
--
Thanks,

John Scott.

[John A. Bailo]

You know -- that really rings true.

I wish -- or maybe I could invent through operator overloading -- a
better way to put in long multiline strings in c# code.

Maybe with an XML comment insert ?

So the code would look like
<DEFINE id="mysqlquery>
SELECT * FROM TABLE
WHERE FIELD1=@VALUE
ORDER BY @ARG1
</DEFINE>

and I could put that right in a method, and then say something like

MySqlCommand.CommandText = SpecialXMLFunction("mysqlquer");


Jim Underwood wrote:

The other benefit is that it is much simpler to debug a single stored
procedure call than multiple lines of concatenated text.

[Jim Underwood]

Being able to write readable SQL within the application code itself would be
nice, as you would be able to see everything in one place at the same time,
rather than going from the code to the database and back.
"John A. Bailo" <ja*****@texeme.com> wrote in message
news:43**************@texeme.com...

You know -- that really rings true.

I wish -- or maybe I could invent through operator overloading -- a
better way to put in long multiline strings in c# code.

Maybe with an XML comment insert ?

So the code would look like
<DEFINE id="mysqlquery>
SELECT * FROM TABLE
WHERE FIELD1=@VALUE
ORDER BY @ARG1
</DEFINE>

and I could put that right in a method, and then say something like

MySqlCommand.CommandText = SpecialXMLFunction("mysqlquer");


Jim Underwood wrote:

The other benefit is that it is much simpler to debug a single stored
procedure call than multiple lines of concatenated text.

[John A. Bailo]

I wonder if it can be treated as an "attribute".

Somehow it doesn't seem exactly a match, but clearly I can add in XML
attributes that are attached to classes and use Reflection to utilize them.

I'm going to have to review Reflectin/Attributes to see if it can be done.
Jim Underwood wrote:

Being able to write readable SQL within the application code itself would be
nice, as you would be able to see everything in one place at the same time,
rather than going from the code to the database and back.
"John A. Bailo" <ja*****@texeme.com> wrote in message
news:43**************@texeme.com...

You know -- that really rings true.

I wish -- or maybe I could invent through operator overloading -- a
better way to put in long multiline strings in c# code.

Maybe with an XML comment insert ?

So the code would look like
<DEFINE id="mysqlquery>
SELECT * FROM TABLE
WHERE FIELD1=@VALUE
ORDER BY @ARG1
</DEFINE>

and I could put that right in a method, and then say something like

MySqlCommand.CommandText = SpecialXMLFunction("mysqlquer");


Jim Underwood wrote:

The other benefit is that it is much simpler to debug a single stored
procedure call than multiple lines of concatenated text.

[Jon Skeet [C# MVP]]

John A. Bailo <ja*****@texeme.com> wrote:

I wonder if it can be treated as an "attribute".

Somehow it doesn't seem exactly a match, but clearly I can add in XML
attributes that are attached to classes and use Reflection to utilize them.

I'm going to have to review Reflectin/Attributes to see if it can be done.

I think what you're really after is NHibernate...
http://www.nhibernate.org

(You might also want to look at DLINQ...)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too