A QuotedStr function

Hi;
is there a function to add quotes to a string sentence?; like this
exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
if @.NAME is only William, i need to add quotes to it to get a sentence like:
Select * from Customers where Name='William' and AGECUST > 25
is there a function to do that?There is no special function for that. you can do in 2 ways.
1. use double quotes and set quote identifier off so that you can use both
double and single quotes.
2. for using single quote in your string you need to put one more single
quote.
ie taking your e.g
exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST > 25')
please note it looks like double quote it is not, it is 2 single quotes.
Try this.
Amarnath, MCTS
"Willo" wrote:
> Hi;
> is there a function to add quotes to a string sentence?; like this
> exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
> if @.NAME is only William, i need to add quotes to it to get a sentence like:
> Select * from Customers where Name='William' and AGECUST > 25
> is there a function to do that?
>
>
>|||"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> There is no special function for that. you can do in 2 ways.
> 1. use double quotes and set quote identifier off so that you can use both
> double and single quotes.
where can i set that?
> 2. for using single quote in your string you need to put one more single
> quote.
> ie taking your e.g
> exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> 25')
> please note it looks like double quote it is not, it is 2 single quotes.
>
i got a syntax error here|||You need to set in the data tab itself. e.g
SET QUOTED_IDENTIFIER OFF
exec
("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
AGECUST > 25")
Just paste this in your data tab it will work. ps: To make it clear I have
left space in between the double quotes. once you get the idea you can remove
the space. Just to check whether the sql query is correct just replace "exec"
with "select"
you will get the full query itself for you to check.
Amarnath, MCTS
"Willo" wrote:
> "Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
> news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> > There is no special function for that. you can do in 2 ways.
> > 1. use double quotes and set quote identifier off so that you can use both
> > double and single quotes.
> where can i set that?
> > 2. for using single quote in your string you need to put one more single
> > quote.
> > ie taking your e.g
> >
> > exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> > 25')
> > please note it looks like double quote it is not, it is 2 single quotes.
> >
> i got a syntax error here
>
>
>|||Thank! Amarnath, works great.
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:41C16D7C-954B-43D6-A165-1AC2D887AD3F@.microsoft.com...
> You need to set in the data tab itself. e.g
> SET QUOTED_IDENTIFIER OFF
> exec
> ("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
> AGECUST > 25")
> Just paste this in your data tab it will work. ps: To make it clear I have
> left space in between the double quotes. once you get the idea you can
> remove
> the space. Just to check whether the sql query is correct just replace
> "exec"
> with "select"
> you will get the full query itself for you to check.
> Amarnath, MCTS

a question about Exec

Hi,

When I run the following command on sql server:

exec ('print "OK"')
go

This message appears:

Server: Msg 128, Level 15, State 1, Line 1
The name 'OK' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.

Why? Thanks.findu_2005@.yahoo.com (findu_2005@.yahoo.com) writes:

Quote:

Originally Posted by

When I run the following command on sql server:
>
exec ('print "OK"')
go
>
This message appears:
>
Server: Msg 128, Level 15, State 1, Line 1
The name 'OK' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
>
Why? Thanks.

Because when the setting QUOTED_IDENTIFIER is in effect, "" delmits
identifier. This permits you to use table names like Order Details, for
instance:

SELECT ... FROM "Order Details"

In the SQL Server world, we tend to use [] for this function, but "" is
what ANSI mandates.

QUOTED_IDENTIFIER is on by default in most contexts, but not when you
run from SQLCMD, OSQL or Enterprise Manager in SQL 2000. Or for that
matter all DB-Library applications.

When the setting is off, SQL Server reverts to the original behaviour
from 4.x days when you couls use both '' and "" to delimit strings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Something like this will work

exec ('print ''OK''') . *Note: all are single quotes.

or you'll have to use SET QUOTED_IDENTIFIER OFF and then execute the
SQL.

Regards,
Thyagu.

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgnIf you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgn
If you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas

"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn
|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn

A list of Auto exec SPs

Hello All,
A SP can be made to automatically execute when SQLServer restarts using the
store procedure "sp_procoption".
But is there a way to find out the list of SPs that have been congifured to
execute automatically. I took over as a DBA for an existing system and I was
wondering if there are any SP configured this way.
Thanks,
rgnIf you look at the definition for sp_procoption, you will discover the
following line of code:
UPDATE sysobjects SET status = (status & ~2) | (2 * @.intOptionValue) WHERE
id = @.tabid
This tells you, along with the rest of the definition, that if you query the
master.dbo.sysobjects table for status values of 2 on xtypes of X or P you
will find you startup procs and extended procs.
Sincerely,
Anthony Thomas
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
to
> execute automatically. I took over as a DBA for an existing system and I
was
> wondering if there are any SP configured this way.
> Thanks,
> rgn|||Hi rgn
You can use the OBJECTPROPERTY function.
SELECT name
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartup') =1
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:3DE27C29-DEDC-4D0B-AEB0-2C34553BB3E7@.microsoft.com...
> Hello All,
> A SP can be made to automatically execute when SQLServer restarts using
> the
> store procedure "sp_procoption".
> But is there a way to find out the list of SPs that have been congifured
> to
> execute automatically. I took over as a DBA for an existing system and I
> was
> wondering if there are any SP configured this way.
> Thanks,
> rgn