How to use EXEC

Hi,
How can I run EXEC conditionally?
The EXEC syntax woks on its own but not when it's in a query.
TIA
Mike
select
'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0
and 2
then EXEC master..xp_sendmail 'mike', 'The master database is full.' else ''
end as testYou need an IF statement. Also, your SELECT needs a FROM clause.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike_T" <nospam@.yahoo.com> wrote in message
news:uXYK4ZMZGHA.4060@.TK2MSFTNGP02.phx.gbl...
Hi,
How can I run EXEC conditionally?
The EXEC syntax woks on its own but not when it's in a query.
TIA
Mike
select
'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0
and 2
then EXEC master..xp_sendmail 'mike', 'The master database is full.' else ''
end as test|||Try
If exists(select
'name','surname', case when datediff (dd, getdate(), getdate()+2)
between 0
and 2 from table)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
Madhivanan|||Hi Madhivanan,
I tried the following using pubs
--
If exists(select
'au_fname','au_lname', case when datediff (dd, getdate(), getdate()+2)
between 0 and 2 from authors)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
--
but I get this error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'else'.
I understood the idea but not the error
Thank you.
"Madhivanan" <madhivanan2001@.gmail.com> escribi en el mensaje
news:1145615715.694251.14190@.t31g2000cwb.googlegroups.com...
> Try
> If exists(select
> 'name','surname', case when datediff (dd, getdate(), getdate()+2)
> between 0
> and 2 from table)
> EXEC master..xp_sendmail 'mike', 'The master database is full.'
> else
> EXEC master..xp_sendmail 'mike', ''
> Madhivanan
>|||On Fri, 21 Apr 2006 22:35:46 +0200, Mike_T wrote:

>Hi Madhivanan,
>I tried the following using pubs
>--
>If exists(select
> 'au_fname','au_lname', case when datediff (dd, getdate(), getdate()+2)
>between 0 and 2 from authors)
>EXEC master..xp_sendmail 'mike', 'The master database is full.'
>else
>EXEC master..xp_sendmail 'mike', ''
>--
>but I get this error:
>Server: Msg 156, Level 15, State 1, Line 3
>Incorrect syntax near the keyword 'from'.
>Server: Msg 156, Level 15, State 1, Line 5
>Incorrect syntax near the keyword 'else'.
>I understood the idea but not the error
Hi Mike,
The error is caused because the CASE expression is not closed. You need
to add at least one THEN clause and an END.
Also, a SELECT list is irrelevant in an EXISTS subquery; the database
will only look for matching rows and disregard the column list yoou
specified. So you could rewrite your code as
IF EXISTS (SELECT * FROM authors)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
ELSE
EXEC master..xp_sendmail 'mike', ''
However, I doubt that this does what you want. Maybe you should explain
what you try to accomplish instead of posting just a part of your code
and have us guess...
Hugo Kornelis, SQL Server MVP|||Try
If exists(select
'name','surname' from table where datediff (dd, getdate(),
getdate()+2) between 0 and 2)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
Madhivanan

How to use bcp in at SQL Query analyzer

Dear All,
I need to do bcp in. The command I use at SQL Query
analyzer is
exec bcp 'schedule.dbo.emp_new'
out 'e:\sqlserver_bcp_data\new.out' -SSQLSERVER_TRY1 -Usa -
Ptrytry -c
But I am getting the error message
Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a
stored procedure.
So, how to do bcp in. Please help me.BCP is a command line utility, not a T-SQL command. So, try that command
from the command prompt.
You could also invoked such executables from Query Analyzer, using the
xp_cmdshell extended system stored procedure. See SQL Server Books Online
for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Sonia" <anonymous@.discussions.microsoft.com> wrote in message
news:2df0e01c46a5b$46289750$a301280a@.phx.gbl...
Dear All,
I need to do bcp in. The command I use at SQL Query
analyzer is
exec bcp 'schedule.dbo.emp_new'
out 'e:\sqlserver_bcp_data\new.out' -SSQLSERVER_TRY1 -Usa -
Ptrytry -c
But I am getting the error message
Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a
stored procedure.
So, how to do bcp in. Please help me.|||Hi,
You have to xp_cmdshell to execute a bcp from query analyzer.
master..xp_cmdshell 'bcp schedule.dbo.emp_new out
e:\sqlserver_bcp_data\new.out -SSQLSERVER_TRY1 -Usa -Ptrytry -c
--
Thanks
Hari
MCDBA
"Sonia" <anonymous@.discussions.microsoft.com> wrote in message
news:2df0e01c46a5b$46289750$a301280a@.phx.gbl...
> Dear All,
> I need to do bcp in. The command I use at SQL Query
> analyzer is
> exec bcp 'schedule.dbo.emp_new'
> out 'e:\sqlserver_bcp_data\new.out' -SSQLSERVER_TRY1 -Usa -
> Ptrytry -c
> But I am getting the error message
> Server: Msg 179, Level 15, State 1, Line 1
> Cannot use the OUTPUT option when passing a constant to a
> stored procedure.
> So, how to do bcp in. Please help me.
>|||Sonia,
bcp is a commandline tool. To use it in QA you need to shell out and run
it using xp_cmdshell. Alternatively use BULK INSERT or DTS - the command
you posted actually bcps out, not in, so you need to change that.
example using xp_cmdshell:
exec master..xp_cmdshell 'bcp schedule.dbo.emp_new out
e:\sqlserver_bcp_data\new.out -SSQLSERVER_TRY1 -Usa -
Ptrytry -c'
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sonia wrote:
> Dear All,
> I need to do bcp in. The command I use at SQL Query
> analyzer is
> exec bcp 'schedule.dbo.emp_new'
> out 'e:\sqlserver_bcp_data\new.out' -SSQLSERVER_TRY1 -Usa -
> Ptrytry -c
> But I am getting the error message
> Server: Msg 179, Level 15, State 1, Line 1
> Cannot use the OUTPUT option when passing a constant to a
> stored procedure.
> So, how to do bcp in. Please help me.
>