Question about referencing Full-Text noise file through SQL rowset functions.

(SQL Server 2000, SP3a)
Hello all!
I'm trying to cobble together something that will let me reference the "nois
e.eng" (the
English list of the Full Text Search service ignored words) through Transact
-SQL.
I'm trying to reference the "noise.eng" file that's located in:
C:\Program Files\Common Files\Microsoft Shared\MSSearch\Data\Config
There are a couple others in:
C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
C:\windows\SYSTEM32
but the one in the "Common Files" had the most recent date. :-)
I *have* had some measure of success with it -- but I'm trying to come up wi
th a "cleaner"
way, if possible.
This is how I got this to work:
First I tried setting up a linked server:
--Create a linked server
execute [dbo].[sp_addlinkedserver]
'TextTest',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Common Files\Microsoft Shared\MSSearch\Data\Config',
NULL,
'Text'
go
--Set up login mappings
execute [dbo].[sp_addlinkedsrvlogin]
'TextTest',
FALSE,
NULL,
NULL
go
Then I checked out the tables that were available:
--List the tables in the linked server
execute [dbo].[sp_tables_ex] 'TextTest'
go
Interestingly, all the files in that directory with a .TXT extension were vi
sible as
tables! However, the "noise.eng" file didn't seem to be available.
If I copied the "noise.eng" file to "noise.txt", then I could do something l
ike:
select * from TextTest...[noise#txt]
or
select * from openquery(TextTest, 'select * from [noise#txt]') as a
And that seems to return the information! :-)
You can clean up the linked servers with:
execute [dbo].[sp_droplinkedsrvlogin]
'TextTest', NULL
go
execute [dbo].[sp_dropserver]
'TextTest'
go
However, I'd really like to use the OPENROWSET function if at all possible,
and *not* have
to rename/copy the noise.eng file. I was reading that I could use a "schema
.ini" file,
and after browsing the web, discovered this format that I thought might work
:
[noise.eng]
ColNameHeader = False
CharacterSet = ANSI
Format = CSVDelimited
Col1=NoiseWord Char Width 100
Then I tried doing something like:
select *
from openrowset
(
'Microsoft.Jet.OLEDB.4.0',
'Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Program Files\Common Files\
Microsoft
Shared\MSSearch\Data\Config;Extended Properties="text;HDR=No;FMT=CSV";',
'select * from noise.eng'
) as a
Which doesn't quite work -- nor have any of the OPENROWSET variants I've tri
ed. :-(
I'd like to be able to use OPENROWSET so that I don't have to create a linke
d server. I'd
also like to avoid copying the "noise.eng" to "noise.txt". I don't mind hav
ing to drop in
a "schema.ini" (if that's even necessary/helpful) in that directory.
I've found the following URLs to be useful sources of information:
http://groups.google.com/groups?q=s...SFTNGP10&rnum=6
http://groups.google.com/groups?q=s...ogle.com&rnum=2
Sorry for the length of this post, but I sure would be grateful to anyone wh
o might be
able to help! :-)
John PetersonI think I've been able to get close with some of this:
select * from openrowset('MSDASQL.1', 'Driver={Microsoft Text Driver (*
.txt; *.csv)};
DefaultDir=C:\Program Files\Common Files\Microsoft Shared\MSSearch\Data\Conf
ig\;', 'select
* from noise.txt')
However, this seems to treat the first row as a header row.
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\Program
Files\Common
Files\Microsoft Shared\MSSearch\Data\Config\;HDR=NO', 'select * from noise.t
xt')
This is the closest I found, and allows me to specify that there is no heade
r row.
The only remaining nit is that I *must* have an extension of .TXT. If I try
to change
"noise.txt" to "noise.eng", I get errors.
A colleague of mine discovered that the JET driver has a list of "allowable"
extensions:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Je
t\4.0\Engines\Text\Extensions
My guess is that if I put ".ENG" in that list, then the above queries will w
ork with
"noise.eng". I was hoping to avoid modifying the Registry, though.
Does anyone know of a way to circumvent this issue?
Thanks!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Mz740hMEHA.1388@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I'm trying to cobble together something that will let me reference the "no
ise.eng" (the
> English list of the Full Text Search service ignored words) through Transa
ct-SQL.
> I'm trying to reference the "noise.eng" file that's located in:
> C:\Program Files\Common Files\Microsoft Shared\MSSearch\Data\Config
> There are a couple others in:
> C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
> C:\windows\SYSTEM32
> but the one in the "Common Files" had the most recent date. :-)
> I *have* had some measure of success with it -- but I'm trying to come up with a[/
vbcol]
"cleaner"[vbcol=seagreen]
> way, if possible.
> This is how I got this to work:
> First I tried setting up a linked server:
> --Create a linked server
> execute [dbo].[sp_addlinkedserver]
> 'TextTest',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'C:\Program Files\Common Files\Microsoft Shared\MSSearch\Data\Config',
> NULL,
> 'Text'
> go
> --Set up login mappings
> execute [dbo].[sp_addlinkedsrvlogin]
> 'TextTest',
> FALSE,
> NULL,
> NULL
> go
> Then I checked out the tables that were available:
> --List the tables in the linked server
> execute [dbo].[sp_tables_ex] 'TextTest'
> go
> Interestingly, all the files in that directory with a .TXT extension were
visible as
> tables! However, the "noise.eng" file didn't seem to be available.
> If I copied the "noise.eng" file to "noise.txt", then I could do something
like:
> select * from TextTest...[noise#txt]
> or
> select * from openquery(TextTest, 'select * from [noise#txt]') as a
> And that seems to return the information! :-)
> You can clean up the linked servers with:
> execute [dbo].[sp_droplinkedsrvlogin]
> 'TextTest', NULL
> go
> execute [dbo].[sp_dropserver]
> 'TextTest'
> go
> However, I'd really like to use the OPENROWSET function if at all possible, and *n
ot*
have
> to rename/copy the noise.eng file. I was reading that I could use a "sche
ma.ini" file,
> and after browsing the web, discovered this format that I thought might wo
rk:
> [noise.eng]
> ColNameHeader = False
> CharacterSet = ANSI
> Format = CSVDelimited
> Col1=NoiseWord Char Width 100
> Then I tried doing something like:
> select *
> from openrowset
> (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Program Files\Common F
iles\Microsoft
> Shared\MSSearch\Data\Config;Extended Properties="text;HDR=No;FMT=CSV";',
> 'select * from noise.eng'
> ) as a
> Which doesn't quite work -- nor have any of the OPENROWSET variants I've t
ried. :-(
> I'd like to be able to use OPENROWSET so that I don't have to create a linked serv
er.
I'd
> also like to avoid copying the "noise.eng" to "noise.txt". I don't mind having to
drop
in
> a "schema.ini" (if that's even necessary/helpful) in that directory.
> I've found the following URLs to be useful sources of information:
>
http://groups.google.com/groups?q=s...SFTNGP10&rnum=6
>
http://groups.google.com/groups?q=s...ogle.com&rnum=2
> Sorry for the length of this post, but I sure would be grateful to anyone
who might be
> able to help! :-)
> John Peterson
>|||John,
A most interesting solution! You would need to use ".ENU" in that list, then
the above queries will work with "noise.enu" (US_English). Can I assume
you've tried to import the noise word file into a table? For example:
CREATE TABLE sqlfts_stop_words (
term nvarchar(50) NOT NULL )
GO
ALTER TABLE sqlfts_stop_words ADD
CONSTRAINT pk_sqlfts_stop_words PRIMARY KEY CLUSTERED (term)
GO
-- Alter drive letter and path to noise.enu as appropriate
BULK INSERT sqlfts_stop_words FROM
'F:\MSSQL80\MSSQL\FTDATA\SQLServer\Confi
g\noise.enu'
GO
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#p5crXiMEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I think I've been able to get close with some of this:
> select * from openrowset('MSDASQL.1', 'Driver={Microsoft Text Driver
(*.txt; *.csv)};
> DefaultDir=C:\Program Files\Common Files\Microsoft
Shared\MSSearch\Data\Config\;', 'select
> * from noise.txt')
> However, this seems to treat the first row as a header row.
> select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\Program Files\Common
> Files\Microsoft Shared\MSSearch\Data\Config\;HDR=NO', 'select * from
noise.txt')
> This is the closest I found, and allows me to specify that there is no
header row.
> The only remaining nit is that I *must* have an extension of .TXT. If I
try to change
> "noise.txt" to "noise.eng", I get errors.
> A colleague of mine discovered that the JET driver has a list of
"allowable" extensions:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Je
t\4.0\Engines\Text\Extensions
> My guess is that if I put ".ENG" in that list, then the above queries will
work with
> "noise.eng". I was hoping to avoid modifying the Registry, though.
> Does anyone know of a way to circumvent this issue?
> Thanks!
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23Mz740hMEHA.1388@.TK2MSFTNGP09.phx.gbl...
"noise.eng" (the[vbcol=seagreen]
Transact-SQL.[vbcol=seagreen]
up with a[vbcol=seagreen]
> "cleaner"
Shared\MSSearch\Data\Config',[vbcol=seag
reen]
were visible as[vbcol=seagreen]
something like:[vbcol=seagreen]
possible, and *not*[vbcol=seagreen]
> have
"schema.ini" file,[vbcol=seagreen]
work:[vbcol=seagreen]
Files\Microsoft[vbcol=seagreen]
tried. :-([vbcol=seagreen]
linked server.[vbcol=seagreen]
> I'd
having to drop[vbcol=seagreen]
> in
>
http://groups.google.com/groups?q=s...SFTNGP10&rnum=6
>
http://groups.google.com/groups?q=s...ogle.com&rnum=2
anyone who might be[vbcol=seagreen]
>|||Hello John!
I was finally able to come up with a couple of solutions with the OPENQUERY,
and settled
on the Registry "dink" for the various "noise.*" extensions. (My other tech
nique was to
copy the specified noise file to a temporary file with a ".TXT" extension, b
ut that was
using [xp_cmdshell] and our users aren't members of the db_admin role an
d I was leery of
opening up the permissions on that procedure.)
Your option to populate a table with the noise words is intriguing, too. We
don't change
the noise list too often, but we *do* add stuff to it on occasion. I wonder
if I could
have a scheduled Job that would essentially keep the file in sync with the t
able every
week or so? Something like that might work better.
Our application will be caching the results of the OPENQUERY approach -- so
I don't feel
too bad about the potential "hit" for reading the file every time the SP is
invoked. But
it is more "clumsy" than just having a static table...
Thanks for the alternative suggestion -- I'll have to mull it over further!
:-)
"John Kane" <jt-kane@.comcast.net> wrote in message
news:urIuIDxMEHA.2876@.TK2MSFTNGP09.phx.gbl...
> John,
> A most interesting solution! You would need to use ".ENU" in that list, th
en
> the above queries will work with "noise.enu" (US_English). Can I assume
> you've tried to import the noise word file into a table? For example:
> CREATE TABLE sqlfts_stop_words (
> term nvarchar(50) NOT NULL )
> GO
> ALTER TABLE sqlfts_stop_words ADD
> CONSTRAINT pk_sqlfts_stop_words PRIMARY KEY CLUSTERED (term)
> GO
> -- Alter drive letter and path to noise.enu as appropriate
> BULK INSERT sqlfts_stop_words FROM
> 'F:\MSSQL80\MSSQL\FTDATA\SQLServer\Confi
g\noise.enu'
> GO
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#p5crXiMEHA.1312@.TK2MSFTNGP12.phx.gbl...
> (*.txt; *.csv)};
> Shared\MSSearch\Data\Config\;', 'select
> openrowset('Microsoft.Jet.OLEDB.4.0','Text;Database=C:\Program Files\Commo
n
> noise.txt')
> header row.
> try to change
> "allowable" extensions:
> work with
> "noise.eng" (the
> Transact-SQL.
> up with a
> Shared\MSSearch\Data\Config',
> were visible as
> something like:
> possible, and *not*
> "schema.ini" file,
> work:
> Files\Microsoft
> tried. :-(
> linked server.
> having to drop
>
http://groups.google.com/groups?q=s...SFTNGP10&rnum=6
>
http://groups.google.com/groups?q=s...ogle.com&rnum=2
> anyone who might be
>|||Hi John,
When you need to change the noise word file, you can setup a SQLServerAgent
job to BCP out to a noise.tmp text file, then stop the MSSearch service,
swap out the files and restart the MSSearch service and then run a Full
Population. You can make this as simple or as complex as you need. Also, for
the below BULK INSERT to work without errors, you will need to manually edit
the noise.enu file and place CR/LF after each single letter at the end of
the file, for example:
a b c d...
becomes
a
b
c
d...
Regards,
John
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uXsOZ2yMEHA.2976@.TK2MSFTNGP10.phx.gbl...
> Hello John!
> I was finally able to come up with a couple of solutions with the
OPENQUERY, and settled
> on the Registry "dink" for the various "noise.*" extensions. (My other
technique was to
> copy the specified noise file to a temporary file with a ".TXT" extension,
but that was
> using [xp_cmdshell] and our users aren't members of the db_admin role and[/vbc
ol]
I was leery of[vbcol=seagreen]
> opening up the permissions on that procedure.)
> Your option to populate a table with the noise words is intriguing, too.
We don't change
> the noise list too often, but we *do* add stuff to it on occasion. I
wonder if I could
> have a scheduled Job that would essentially keep the file in sync with the
table every
> week or so? Something like that might work better.
> Our application will be caching the results of the OPENQUERY approach --
so I don't feel
> too bad about the potential "hit" for reading the file every time the SP
is invoked. But
> it is more "clumsy" than just having a static table...
> Thanks for the alternative suggestion -- I'll have to mull it over
further! :-)
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:urIuIDxMEHA.2876@.TK2MSFTNGP09.phx.gbl...
then[vbcol=seagreen]
Files\Common[vbcol=seagreen]
I[vbcol=seagreen]
will[vbcol=seagreen]
the[vbcol=seagreen]
come[vbcol=seagreen]
might[vbcol=seagreen]
Files\Common[vbcol=seagreen]
Properties="text;HDR=No;FMT=CSV";',[vbcol=seagreen]
I've[vbcol=seagreen]
a[vbcol=seagreen]
mind[vbcol=seagreen]
>
http://groups.google.com/groups?q=s...SFTNGP10&rnum=6
>
http://groups.google.com/groups?q=s...ogle.com&rnum=2
>|||Thanks John!
I notice that there's a line in the noise.enu file that is:
a b c d e ... x y z
I'm assuming that "word" is really a space-delimited list of single noise wo
rds? If I
leave that "as is", will that be a problem? Or is there something with the
mechanics of
the BULK INSERT that will give me problems with that? I can easily "parse"
those values
out into the "expanded list", so I don't mind leaving it the way it is, unle
ss it hoses up
the BULK INSERT.
Can I also assume, then, that I could potentially put other multiple noise w
ords on the
same line, separated by a space if I wanted to? What if I wanted a *phrase*
to be ignored
that contained spaces?
Thanks again for your help!
John Peterson
"John Kane" <jt-kane@.comcast.net> wrote in message
news:u9t$H88MEHA.3556@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> When you need to change the noise word file, you can setup a SQLServerAgen
t
> job to BCP out to a noise.tmp text file, then stop the MSSearch service,
> swap out the files and restart the MSSearch service and then run a Full
> Population. You can make this as simple or as complex as you need. Also, f
or
> the below BULK INSERT to work without errors, you will need to manually ed
it
> the noise.enu file and place CR/LF after each single letter at the end of
> the file, for example:
> a b c d...
> becomes
> a
> b
> c
> d...
> Regards,
> John
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uXsOZ2yMEHA.2976@.TK2MSFTNGP10.phx.gbl...
> OPENQUERY, and settled
> technique was to
> but that was
> I was leery of
> We don't change
> wonder if I could
> table every
> so I don't feel
> is invoked. But
> further! :-)
> then
> Files\Common
> I
> will
> the
> come
> might
> Files\Common
> Properties="text;HDR=No;FMT=CSV";',
> I've
> a
> mind
>
http://groups.google.com/groups?q=s...SFTNGP10&rnum=6
>
http://groups.google.com/groups?q=s...ogle.com&rnum=2
>|||Yes, that's the line I'm referring to... Yes, if you leave it "as is" it
will cause the BULK INSERT to throw an error and the single letters will not
be imported. However, making the change does not affect MSSearch, it's just
that the bulk insert hick-ups with the spaces between the single letters.
No, your assumption is incorrect (I've already tried that years ago ;-) and
the multiple-word noise phrases are ignored by MSSearch.
Regards,
John
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#f1$$R#MEHA.3972@.TK2MSFTNGP10.phx.gbl...
> Thanks John!
> I notice that there's a line in the noise.enu file that is:
> a b c d e ... x y z
> I'm assuming that "word" is really a space-delimited list of single noise
words? If I
> leave that "as is", will that be a problem? Or is there something with
the mechanics of
> the BULK INSERT that will give me problems with that? I can easily
"parse" those values
> out into the "expanded list", so I don't mind leaving it the way it is,
unless it hoses up
> the BULK INSERT.
> Can I also assume, then, that I could potentially put other multiple noise
words on the
> same line, separated by a space if I wanted to? What if I wanted a
*phrase* to be ignored
> that contained spaces?
> Thanks again for your help!
> John Peterson
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:u9t$H88MEHA.3556@.TK2MSFTNGP09.phx.gbl...
SQLServerAgent[vbcol=seagreen]
for[vbcol=seagreen]
edit[vbcol=seagreen]
of[vbcol=seagreen]
other[vbcol=seagreen]
extension,[vbcol=seagreen]
and[vbcol=seagreen]
too.[vbcol=seagreen]
the[vbcol=seagreen]
approach --[vbcol=seagreen]
SP[vbcol=seagreen]
list,[vbcol=seagreen]
assume[vbcol=seagreen]
example:[vbcol=seagreen]
Driver[vbcol=seagreen]
from[vbcol=seagreen]
is no[vbcol=seagreen]
If[vbcol=seagreen]
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Je
t\4. 0\Engines\Text\Extensions[vbcol=seagreen
]
queries[vbcol=seagreen]
though.[vbcol=seagreen]
reference[vbcol=seagreen]
through[vbcol=seagreen]
Shared\MSSearch\Data\Config[vbcol=seagre
en]
Server\MSSQL\FTDATA\SQLServer\Config[vbc
ol=seagreen]
to[vbcol=seagreen]
extension[vbcol=seagreen]
available.[vbcol=seagreen]
as a[vbcol=seagreen]
all[vbcol=seagreen]
use a[vbcol=seagreen]
thought[vbcol=seagreen]
variants[vbcol=seagreen]
create[vbcol=seagreen]
don't[vbcol=seagreen]
directory.[vbcol=seagreen]
information:[vbcol=seagreen]
>
http://groups.google.com/groups?q=s...SFTNGP10&rnum=6
>
http://groups.google.com/groups?q=s...ogle.com&rnum=2
to[vbcol=seagreen]
>

Question about Indexed VIEWs.

(SQL Server 2000, SP3a)
Hello all!
I've got a question about Indexed VIEWs that some one can assist with.
If I have an Indexed VIEW that does some aggregation:
create view [dbo].[MyTableSummary] with schemabinding
as
select [PK] = mt.[PK],
[Count] = count_big(*),
[CountA] = sum(case when (mt.[DateCreatedA] is NULL) then 0 else 1 e
nd),
[CountB] = sum(case when (mt.[DateCreatedB] is NULL) then 0 else 1 e
nd)
from [dbo].[MyTable] as mt with (nolock)
group by mt.[PK]
go
And I create the index:
create unique clustered index [CI_MyTableSummary] on [dbo].[MyTa
bleSummary]([PK])
go
Will it denormalize/persist the entire results of the VIEW, or is it just go
ing to persist
the contents of the index(es) and still continue to evaluate the VIEW every
time it's
used? Is there a way to essentially have it persist every column in the VIE
W so that it
doesn't need to evaluate the VIEW every time, if so?
Thanks for any help you can provide!
John PetersonThe entire view will be materialized.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eZvSn7REEHA.2600@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a question about Indexed VIEWs that some one can assist with.
> If I have an Indexed VIEW that does some aggregation:
> create view [dbo].[MyTableSummary] with schemabinding
> as
> select [PK] = mt.[PK],
> [Count] = count_big(*),
> [CountA] = sum(case when (mt.[DateCreatedA] is NULL) then 0 else 1[
/color]
end),
> [CountB] = sum(case when (mt.[DateCreatedB] is NULL) then 0 else 1[
/color]
end)
> from [dbo].[MyTable] as mt with (nolock)
> group by mt.[PK]
> go
> And I create the index:
> create unique clustered index [CI_MyTableSummary] on
[dbo].[MyTableSummary]([PK])
> go
> Will it denormalize/persist the entire results of the VIEW, or is it just
going to persist
> the contents of the index(es) and still continue to evaluate the VIEW
every time it's
> used? Is there a way to essentially have it persist every column in the
VIEW so that it
> doesn't need to evaluate the VIEW every time, if so?
> Thanks for any help you can provide!
> John Peterson
>|||Thanks Adam -- I thought (hoped) so, but I wasn't sure. Can you direct me t
o a BOL (or
other) reference that would confirm this?
Thanks again!
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:OY$UjASEEHA.1368@.TK2MSFTNGP11.phx.gbl...
> The entire view will be materialized.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eZvSn7REEHA.2600@.TK2MSFTNGP09.phx.gbl...
> end),
> end)
> [dbo].[MyTableSummary]([PK])
> going to persist
> every time it's
> VIEW so that it
>|||"Creating an Indexed View" in BOL.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e05UjHSEEHA.3424@.tk2msftngp13.phx.gbl...
> Thanks Adam -- I thought (hoped) so, but I wasn't sure. Can you direct me
to a BOL (or
> other) reference that would confirm this?
> Thanks again!
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:OY$UjASEEHA.1368@.TK2MSFTNGP11.phx.gbl...
else 1
else 1
just
the
>|||LOL! I thought I read that -- but I must have missed the germane nugget! ;
-)
Thanks Adam!
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:uaHE2JSEEHA.3696@.TK2MSFTNGP10.phx.gbl...
> "Creating an Indexed View" in BOL.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e05UjHSEEHA.3424@.tk2msftngp13.phx.gbl...
> to a BOL (or
> message
> else 1
> else 1
> just
> the
>|||Ah...I think this blurb at the end has what I was looking for:
<Quote>
Although only the columns that make up the clustered index key are specified
in the CREATE
UNIQUE CLUSTERED INDEX statement, the complete result set of the view is sto
red in the
database. As in a clustered index on a base table, the B-tree structure of t
he clustered
index contains only the key columns, but the data rows contain all of the co
lumns in the
view result set.
</Quote>
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uKpSoQSEEHA.2700@.tk2msftngp13.phx.gbl...
> LOL! I thought I read that -- but I must have missed the germane nugget!
;-)
> Thanks Adam!
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in messa
ge
> news:uaHE2JSEEHA.3696@.TK2MSFTNGP10.phx.gbl...
>|||How about :
"If such views are frequently referenced in queries, you can improve
performance by creating a unique clustered index on the view. When a unique
clustered index is created on a view, the view is executed and the result
set is stored in the database in the same way a table with a clustered index
is stored. "
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OVU8rSSEEHA.1128@.TK2MSFTNGP11.phx.gbl...
> Ah...I think this blurb at the end has what I was looking for:
> <Quote>
> Although only the columns that make up the clustered index key are
specified in the CREATE
> UNIQUE CLUSTERED INDEX statement, the complete result set of the view is
stored in the
> database. As in a clustered index on a base table, the B-tree structure of
the clustered
> index contains only the key columns, but the data rows contain all of the
columns in the
> view result set.
> </Quote>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uKpSoQSEEHA.2700@.tk2msftngp13.phx.gbl...
nugget! ;-)
message
direct me
with.
0
0
is it
VIEW
column in
>|||Heh! That too! ;-)
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:OY4xTXSEEHA.3568@.tk2msftngp13.phx.gbl...
> How about :
> "If such views are frequently referenced in queries, you can improve
> performance by creating a unique clustered index on the view. When a uniqu
e
> clustered index is created on a view, the view is executed and the result
> set is stored in the database in the same way a table with a clustered ind
ex
> is stored. "
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OVU8rSSEEHA.1128@.TK2MSFTNGP11.phx.gbl...
> specified in the CREATE
> stored in the
> the clustered
> columns in the
> nugget! ;-)
> message
> direct me
> with.
> 0
> 0
> is it
> VIEW
> column in
>

Question about deploying DTS objects (COM, DTSRUN.exe, etc.).

Hello all!
I have a series of file-based DTS packages that I want to execute from our
web servers, and I have a couple of questions about the deployment of the
objects necessary to support these packages.
In my DTS packages, I have some ActiveX scripts that do some "package
manipulation" (enabling/disabling Steps, etc.). I'm guessing I'd need the
DTS COM objects to be installed and registered on the web servers?
Additionally, the DTS packages will be invoked by DTSRUN.exe on the Web
servers, so I'd probably need to install that (and any dependencies?) on the
Web servers.
I'm not really sure which files I need, or where to start looking. Are
there any licensing issues involved with having the DTS components on our
production web servers?
I'd be grateful for any assistance anyone can provide! :-)
John PetersonThis article is helpful
Redistributing DTS with your program
(http://www.sqldts.com/default.aspx?225)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23AymH4SbFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hello all!
> I have a series of file-based DTS packages that I want to execute from our
> web servers, and I have a couple of questions about the deployment of the
> objects necessary to support these packages.
> In my DTS packages, I have some ActiveX scripts that do some "package
> manipulation" (enabling/disabling Steps, etc.). I'm guessing I'd need the
> DTS COM objects to be installed and registered on the web servers?
> Additionally, the DTS packages will be invoked by DTSRUN.exe on the Web
> servers, so I'd probably need to install that (and any dependencies?) on
> the Web servers.
> I'm not really sure which files I need, or where to start looking. Are
> there any licensing issues involved with having the DTS components on our
> production web servers?
> I'd be grateful for any assistance anyone can provide! :-)
> John Peterson
>|||Thanks Allan! I had *just* chanced upon that link when you replied. :-)
I notice that there's no mention of any licensing issues. Do you have any
suggestions regarding that?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OLzhPCTbFHA.3040@.TK2MSFTNGP14.phx.gbl...
> This article is helpful
> Redistributing DTS with your program
> (http://www.sqldts.com/default.aspx?225)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23AymH4SbFHA.1404@.TK2MSFTNGP09.phx.gbl...
>|||Licencing - yuck.
Have a look in redist.txt.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ubodiFTbFHA.1148@.tk2msftngp13.phx.gbl...
> Thanks Allan! I had *just* chanced upon that link when you replied. :-)
> I notice that there's no mention of any licensing issues. Do you have any
> suggestions regarding that?
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:OLzhPCTbFHA.3040@.TK2MSFTNGP14.phx.gbl...
>

Question about deploying DTS objects (COM, DTSRUN.exe, etc.).

Hello all!
I have a series of file-based DTS packages that I want to execute from our
web servers, and I have a couple of questions about the deployment of the
objects necessary to support these packages.
In my DTS packages, I have some ActiveX scripts that do some "package
manipulation" (enabling/disabling Steps, etc.). I'm guessing I'd need the
DTS COM objects to be installed and registered on the web servers?
Additionally, the DTS packages will be invoked by DTSRUN.exe on the Web
servers, so I'd probably need to install that (and any dependencies?) on the
Web servers.
I'm not really sure which files I need, or where to start looking. Are
there any licensing issues involved with having the DTS components on our
production web servers?
I'd be grateful for any assistance anyone can provide! :-)
John Peterson
This article is helpful
Redistributing DTS with your program
(http://www.sqldts.com/default.aspx?225)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23AymH4SbFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hello all!
> I have a series of file-based DTS packages that I want to execute from our
> web servers, and I have a couple of questions about the deployment of the
> objects necessary to support these packages.
> In my DTS packages, I have some ActiveX scripts that do some "package
> manipulation" (enabling/disabling Steps, etc.). I'm guessing I'd need the
> DTS COM objects to be installed and registered on the web servers?
> Additionally, the DTS packages will be invoked by DTSRUN.exe on the Web
> servers, so I'd probably need to install that (and any dependencies?) on
> the Web servers.
> I'm not really sure which files I need, or where to start looking. Are
> there any licensing issues involved with having the DTS components on our
> production web servers?
> I'd be grateful for any assistance anyone can provide! :-)
> John Peterson
>
|||Thanks Allan! I had *just* chanced upon that link when you replied. :-)
I notice that there's no mention of any licensing issues. Do you have any
suggestions regarding that?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OLzhPCTbFHA.3040@.TK2MSFTNGP14.phx.gbl...
> This article is helpful
> Redistributing DTS with your program
> (http://www.sqldts.com/default.aspx?225)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23AymH4SbFHA.1404@.TK2MSFTNGP09.phx.gbl...
>
|||Licencing - yuck.
Have a look in redist.txt.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ubodiFTbFHA.1148@.tk2msftngp13.phx.gbl...
> Thanks Allan! I had *just* chanced upon that link when you replied. :-)
> I notice that there's no mention of any licensing issues. Do you have any
> suggestions regarding that?
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:OLzhPCTbFHA.3040@.TK2MSFTNGP14.phx.gbl...
>

Question about "WITH (ROWLOCK)" hint.

(SQL Server 2000, SP3a)
Hello all!
I've got a trigger on a table that I want to cause a column in another table
to be
updated:
update t
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t with (rowlock)
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
Is the WITH (ROWLOCK) hint any different if I express it this way:
update t with (rowlock)
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
My guess is that it isn't (since the compiler will warn you if there are inc
ompatible lock
types between the "alias" and the table) -- so I just thought it'd be the sa
me. But, I'm
not really sure and was hoping for clarification.
Thanks for any help you can provide!
John PetersonJohn,
I don't believe so but your example is a bad one<g>. First off you don't
need to issue a NOLOCK on the Inserted table since the session doing the
updating is the only one that has access to it. I don't know if it even
does anything to tell you the truth. And the ROWLOCK should not be needed
either. Your joining on a PK and unless your updating most of the table it
should use rowlocks anyway. Hints should only be used where absolutely
necessary. You need to prove to yourself they are needed before using them
otherwise you risk getting poor performance overall.
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a trigger on a table that I want to cause a column in another
table to be
> updated:
> update t
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t with (rowlock)
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> Is the WITH (ROWLOCK) hint any different if I express it this way:
> update t with (rowlock)
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> My guess is that it isn't (since the compiler will warn you if there are
incompatible lock
> types between the "alias" and the table) -- so I just thought it'd be the
same. But, I'm
> not really sure and was hoping for clarification.
> Thanks for any help you can provide!
> John Peterson
>|||> from [dbo].[MyOtherTable] as t with (rowlock)
What do you think you're gaining here?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hello Andrew!
Thanks -- yeah, I figured my NOLOCK hint was likely superfluous. The table
that being
updated is a fairly high-use table, and explicitly providing a ROWLOCK hint
has been
helpful in the past. But, your words of caution are appreciated!
Thanks for your help!
John Peterson
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYCc4xb8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> John,
> I don't believe so but your example is a bad one<g>. First off you don't
> need to issue a NOLOCK on the Inserted table since the session doing the
> updating is the only one that has access to it. I don't know if it even
> does anything to tell you the truth. And the ROWLOCK should not be needed
> either. Your joining on a PK and unless your updating most of the table i
t
> should use rowlocks anyway. Hints should only be used where absolutely
> necessary. You need to prove to yourself they are needed before using the
m
> otherwise you risk getting poor performance overall.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> table to be
> incompatible lock
> same. But, I'm
>|||"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23wEOE0b8DHA.452@.TK2MSFTNGP11.phx.gbl...
> What do you think you're gaining here?
I had hoped to mitigate the possibility of conversion deadlocks with this ap
proach
(deadlocks that are caused due to row-->page lock escalation). I was just c
urious to
learn whether specifying the hint at one or the other locations might have a
ny difference.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>