Reserver Characters

When saving characters to text or char fields are there any reserved
characters which i should watch out for when using ODBC to store data to SQL
2000 server.
I MySql you have to add and escape character before before the character to
avoid problems.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004THe only character is the quote.. SQL Strings are quoted... Just double the
quote ( 2 single quotes) to include a single quote in a quoted string.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:eX08dq47DHA.1804@.TK2MSFTNGP12.phx.gbl...
> When saving characters to text or char fields are there any reserved
> characters which i should watch out for when using ODBC to store data to
SQL
> 2000 server.
> I MySql you have to add and escape character before before the character
to
> avoid problems.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004
>

Reserver Characters

When saving characters to text or char fields are there any reserved
characters which i should watch out for when using ODBC to store data to SQL
2000 server.
I mysql you have to add and escape character before before the character to
avoid problems.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004THe only character is the quote.. SQL Strings are quoted... Just double the
quote ( 2 single quotes) to include a single quote in a quoted string.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:eX08dq47DHA.1804@.TK2MSFTNGP12.phx.gbl...
> When saving characters to text or char fields are there any reserved
> characters which i should watch out for when using ODBC to store data to
SQL
> 2000 server.
> I mysql you have to add and escape character before before the character
to
> avoid problems.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are ask
ing if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cach
ed for the stored procedure. In complex stored procedures, it is possible th
at the execution plan will be re-compiled during execution, but you can read
more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The
reason being that the table name is not qualified with the owner/schema. It
is possible to have two tables with the same name but different owners, and
data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was
fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where[vbcol=seagreen]
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedur
e Events of the Profiler. These can show individual statements within the St
ored Procedure being executed and if there has been a Cache Hit or Cache Mis
s. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.
Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>
|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.[vbcol=seagreen]
>"Jimbo" wrote:
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where
>.
>
|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is different enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:
> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.
>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>> Hi,
>> Say if you had a store procedure called p_SelectData
>> defined as follows with the parameter @.WhereClause can
by
>> set to either
>> "@.Where = ' Where Surname = ''Jones''...
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''.
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''
>> and HasDetails = 1
>> You get the idea, a dynamic where clause.
>> The SP is as follows
>> CREATE procedure dbo.p_SelectData
>> @.WhereClause varchar(100) as
>> DECLARE @.SQLString varchar(255)
>> SET @.SQLString = 'Select * From MockTable ' +
@.WhereClause
>> EXECUTE ( @.SQLString)
>> My question is can a proper execution plan be formed
>> internally by SQL Server, or does the exec with the
where
>> clause stop it from forming ?
>> N.B. No I didn't implement this it was there when I got
>> there.
>> Thanks.
>>
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be possible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:
> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> >--Original Message--
> >Hi,
> >
> >Firstly, you will always get an execution plan, but I'm
> guessing you are asking if it will cache it and re-use it.
> >
> >I would say that in your simple example, you will get an
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> >
> >As for the EXEC statment, I don't believe that the plan
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> >
> >"Jimbo" wrote:
> >
> >> Hi,
> >>
> >> Say if you had a store procedure called p_SelectData
> >> defined as follows with the parameter @.WhereClause can
> by
> >> set to either
> >>
> >> "@.Where = ' Where Surname = ''Jones''...
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''.
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''
> >> and HasDetails = 1
> >>
> >> You get the idea, a dynamic where clause.
> >>
> >> The SP is as follows
> >>
> >> CREATE procedure dbo.p_SelectData
> >> @.WhereClause varchar(100) as
> >> DECLARE @.SQLString varchar(255)
> >> SET @.SQLString = 'Select * From MockTable ' +
> @.WhereClause
> >> EXECUTE ( @.SQLString)
> >>
> >> My question is can a proper execution plan be formed
> >> internally by SQL Server, or does the exec with the
> where
> >> clause stop it from forming ?
> >>
> >> N.B. No I didn't implement this it was there when I got
> >> there.
> >>
> >> Thanks.
> >>
> >>
> >>
> >.
> >
>