Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!
Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings togethe
r,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicon
s"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthlink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.sqlsql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.

For example:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?

FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

Thanks!Would this work for you?

declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string

It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Compilations vs recompilations/sec

I see high number of compilations/sec and not recompilations/sec ? Whats the
difference between the two.. in other words when does it compile vs when
does it recompile ? I could not get a good feel about this even after
skimming through this article
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? Can someone give me the SQL for when it may
recompile in the above condition ? Also will this show as recompilation/sec
or compilation/sec in perfmon ?Are you having performance issues, or is this a general question?
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||First, thanks for the link, it was good reading. However, I would hardly
consider it useful if you're only going to skim it.
Clearly, the queries on your system is either not getting cached, or plans
are timing out and being removed from the cache (I don't know those
specifics about SQL Server). But again I must ask why you are asking the
question in the first place.
The plan/procedure/query cache is part of the Query Optimizer and is quite
possibly the single most complicated portion of a database engine. It is
also an area that DBA's seldom have to muck with, with the exception of some
basic understanding.
So, I must ask why you are asking the question in the first place? Are you
having performance problems, or are you just curious?
Jay
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||I am seeing high compilations/sec on one of our SQL Servers as high as 500
and zero recompilations/sec. No one is complaining yet, but curious to know
why compile vs recompile..
"JayKon" <spam@.nospam.org> wrote in message
news:uUkmBPe4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> First, thanks for the link, it was good reading. However, I would hardly
> consider it useful if you're only going to skim it.
> Clearly, the queries on your system is either not getting cached, or plans
> are timing out and being removed from the cache (I don't know those
> specifics about SQL Server). But again I must ask why you are asking the
> question in the first place.
> The plan/procedure/query cache is part of the Query Optimizer and is quite
> possibly the single most complicated portion of a database engine. It is
> also an area that DBA's seldom have to muck with, with the exception of
> some basic understanding.
> So, I must ask why you are asking the question in the first place? Are you
> having performance problems, or are you just curious?
> Jay
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||Well try not to "skim" next time since this article is very specific about
what recompilation is. But in a nut shell a recompile is when the plan is in
cache and it gets invalidated for one of the many reasons the article
explains so that the next time a user tries to use that plan it must be
recreated or recompiled. A compile is when it never was in cache to begin
with. If you have lots of compiles it means you have lots of adhoc queries
and sql server is either not caching them at all or you have so many that
they don't stay in cache long enough to be reused. I would read the article
several times in depth as it is one of the very best articles on cache
behavior out there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||Thanks Andrew.
What about this statement ? Can you help me here ?
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? can you give an example of a literal ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
> Well try not to "skim" next time since this article is very specific about
> what recompilation is. But in a nut shell a recompile is when the plan is
> in cache and it gets invalidated for one of the many reasons the article
> explains so that the next time a user tries to use that plan it must be
> recreated or recompiled. A compile is when it never was in cache to begin
> with. If you have lots of compiles it means you have lots of adhoc queries
> and sql server is either not caching them at all or you have so many that
> they don't stay in cache long enough to be reused. I would read the
> article several times in depth as it is one of the very best articles on
> cache behavior out there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||A literal is an actual value. I'm not going to give you an actual example
because I would have to type in more than 8000 characters!
But suppose you have a table with a column of varchar(max). A query like the
following would be an example of one with a literal longer than 8K:
UPDATE mytable
SET bigcolumn = 'Some very very long string that is longer than 8000
characters ....'
WHERE key_column = 42
The plan for the above query would not be cached.
Of course I could have made the column nvarchar(max) and then I would only
have to type in 4001 characters, but that is still to many for me to type
right now.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
> Thanks Andrew.
> What about this statement ? Can you help me here ?
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that
> contains a literal larger than 8 KB. Suppose that this batch creates a
> temporary table, and then inserts 20 rows in that table. The insertion of
> the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? can you give an example of a literal ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>|||Kalen,
What about
UPDATE mytable
SET bigcolumn = 42
WHERE key_column = 'Some very very long string that is longer than 8000
characters ....'
Would the plan for this query not be cached too ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>A literal is an actual value. I'm not going to give you an actual example
>because I would have to type in more than 8000 characters!
> But suppose you have a table with a column of varchar(max). A query like
> the following would be an example of one with a literal longer than 8K:
> UPDATE mytable
> SET bigcolumn = 'Some very very long string that is longer than 8000
> characters ....'
> WHERE key_column = 42
> The plan for the above query would not be cached.
> Of course I could have made the column nvarchar(max) and then I would only
> have to type in 4001 characters, but that is still to many for me to type
> right now.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>|||No, this would give you an error, because key columns cannot be longer than
900 bytes.
But if the WHERE included a non-key column that was compared to a literal
longer than 8000 bytes, it is the same as the example I gave. A literal
anywhere in the query that is longer than 8000 bytes will keep the plan from
being cached.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:u4qDLJi4HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> What about
> UPDATE mytable
> SET bigcolumn = 42
> WHERE key_column = 'Some very very long string that is longer than 8000
> characters ....'
>
> Would the plan for this query not be cached too ?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>A literal is an actual value. I'm not going to give you an actual example
>>because I would have to type in more than 8000 characters!
>> But suppose you have a table with a column of varchar(max). A query like
>> the following would be an example of one with a literal longer than 8K:
>> UPDATE mytable
>> SET bigcolumn = 'Some very very long string that is longer than 8000
>> characters ....'
>> WHERE key_column = 42
>> The plan for the above query would not be cached.
>> Of course I could have made the column nvarchar(max) and then I would
>> only have to type in 4001 characters, but that is still to many for me to
>> type right now.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion
>> of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons
>> the article explains so that the next time a user tries to use that
>> plan it must be recreated or recompiled. A compile is when it never was
>> in cache to begin with. If you have lots of compiles it means you have
>> lots of adhoc queries and sql server is either not caching them at all
>> or you have so many that they don't stay in cache long enough to be
>> reused. I would read the article several times in depth as it is one
>> of the very best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ?
>>Whats the difference between the two.. in other words when does it
>>compile vs when does it recompile ? I could not get a good feel about
>>this even after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for
>> the batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this
>> batch creates a temporary table, and then inserts 20 rows in that
>> table. The insertion of the seventh row will cause a recompilation,
>> but because of the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>>
>

Compatibility vs2005/sql2005 and sql2000

Hi,
Are there any known compatibility issues with VS/SQL 2005 and SQL Server 2000?
In other words, can I (continue to) develop a report project for SQL server
2000 when I have VS/SQL2005 installed on my pc?
--
Thanks,
EdgarI have both VS 2003 and VS 2005. I have modified and deployed reports from
VS 2003 to RS 2000.
What you cannot do is create or modify a report in VS 2005 and deploy to RS
2000. RS 2005 reports require RS 2005.
But, you can upgrade RS 2000 to RS 2005 leaving SQL Server at 2000 (still
need a SQL Server 1005 license).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:6DDB23AB-3B8D-4635-B85C-08C7EF2CE08B@.microsoft.com...
> Hi,
> Are there any known compatibility issues with VS/SQL 2005 and SQL Server
> 2000?
> In other words, can I (continue to) develop a report project for SQL
> server
> 2000 when I have VS/SQL2005 installed on my pc?
>
> --
> Thanks,
> Edgar|||Ok...but can you create reports in 2003 and deploy to 2005?|||Yes, you can use the RS 2000 report designer which installs into VS 2003 for
this purpose. RS 2000 RDLs can be directly published from the old report
designer to RS 2005 report servers. Note: the RS 2000 report designer will
not support any of the new RS 2005 features (such as Interactive Sort,
etc.).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <KillnComputers@.Verizon.Net> wrote in message
news:1130962651.742396.163040@.g43g2000cwa.googlegroups.com...
> Ok...but can you create reports in 2003 and deploy to 2005?
>|||Some more questions about this to get it clear:
- Can I use VS 2005 with RS2000 to deploy reports to SQL 2000 Report Server?
- Can I use VS 2005 with RS2005 to deploy reports to SQL 2000 Report Server?
Thanks,
Edgar
"Robert Bruckner [MSFT]" wrote:
> Yes, you can use the RS 2000 report designer which installs into VS 2003 for
> this purpose. RS 2000 RDLs can be directly published from the old report
> designer to RS 2005 report servers. Note: the RS 2000 report designer will
> not support any of the new RS 2005 features (such as Interactive Sort,
> etc.).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Dave" <KillnComputers@.Verizon.Net> wrote in message
> news:1130962651.742396.163040@.g43g2000cwa.googlegroups.com...
> > Ok...but can you create reports in 2003 and deploy to 2005?
> >
>
>|||No to both.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:4068C225-D2EE-4613-B1EE-C03A098F810C@.microsoft.com...
> Some more questions about this to get it clear:
> - Can I use VS 2005 with RS2000 to deploy reports to SQL 2000 Report
> Server?
> - Can I use VS 2005 with RS2005 to deploy reports to SQL 2000 Report
> Server?
>
> --
> Thanks,
> Edgar
>
> "Robert Bruckner [MSFT]" wrote:
>> Yes, you can use the RS 2000 report designer which installs into VS 2003
>> for
>> this purpose. RS 2000 RDLs can be directly published from the old report
>> designer to RS 2005 report servers. Note: the RS 2000 report designer
>> will
>> not support any of the new RS 2005 features (such as Interactive Sort,
>> etc.).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Dave" <KillnComputers@.Verizon.Net> wrote in message
>> news:1130962651.742396.163040@.g43g2000cwa.googlegroups.com...
>> > Ok...but can you create reports in 2003 and deploy to 2005?
>> >
>>

Comparison of words within a column using "Like" keyword

Hello all!
I have got a problem, when I use this query it returns the expected results
select * from tbl_list
where keyword like '%' + 'abc' + '%'
but when I use the same in stored procedure, it returns unexpected results:
my stored procedure is this
Create proc sp_getlist
@.keyword varchar(2000)
as
select * from tbl_list
where keyword like '%' + @.keyword + '%'
I'm Using SQl Server 2000 (Personal Addition) on Windows XP SP2.
Please help me out, thanx in anticipation.Hi Zubair,
What is the kind of result that u are getting. Can you please display a
sample output and the kind of input that u are sending to the Stored Procedu
re
thanks and regards
Chandra
"zubair" wrote:

> Hello all!
> I have got a problem, when I use this query it returns the expected result
s
> select * from tbl_list
> where keyword like '%' + 'abc' + '%'
> but when I use the same in stored procedure, it returns unexpected results
:
> my stored procedure is this
> Create proc sp_getlist
> @.keyword varchar(2000)
> as
> select * from tbl_list
> where keyword like '%' + @.keyword + '%'
> I'm Using SQl Server 2000 (Personal Addition) on Windows XP SP2.
> Please help me out, thanx in anticipation.
>
>