What's the most effective way to re-index tables without using the Index
Tuning Wizard? I have a large collection of tables (200 or so, only 30 of
which have a large amount of data)whose indices I need to update relatively
quickly, and I do not have a trace file to work off of.
I'm considering removing the PK attributes on these tables, then reassigning
them (which should rebuild them if I am not mistaken).
Any tips would be appreciated. Thanks.THE ITW is not meant for index maintenance. Check out DBCC DBREINDEX and
DBCC INDEXDEFRAG in BooksOnLine.
Andrew J. Kelly SQL MVP
"Elliot M. Rodriguez" <noemail> wrote in message
news:eoGIxSgEEHA.2988@.TK2MSFTNGP12.phx.gbl...
> What's the most effective way to re-index tables without using the Index
> Tuning Wizard? I have a large collection of tables (200 or so, only 30 of
> which have a large amount of data)whose indices I need to update
relatively
> quickly, and I do not have a trace file to work off of.
> I'm considering removing the PK attributes on these tables, then
reassigning
> them (which should rebuild them if I am not mistaken).
> Any tips would be appreciated. Thanks.
>|||Good source of info here too:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Ray Mond
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OeGSGZhEEHA.2076@.TK2MSFTNGP09.phx.gbl...
> THE ITW is not meant for index maintenance. Check out DBCC DBREINDEX and
> DBCC INDEXDEFRAG in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Elliot M. Rodriguez" <noemail> wrote in message
> news:eoGIxSgEEHA.2988@.TK2MSFTNGP12.phx.gbl...
of
> relatively
> reassigning
>|||I appreciate your help guys... thank you!
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:eBCoWyhEEHA.3064@.tk2msftngp13.phx.gbl...
> Good source of info here too:
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> --
> Regards
> Ray Mond
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OeGSGZhEEHA.2076@.TK2MSFTNGP09.phx.gbl...
and
Index
> of
>|||"Elliot M. Rodriguez" <noemail> wrote in message
news:eoGIxSgEEHA.2988@.TK2MSFTNGP12.phx.gbl...
> What's the most effective way to re-index tables without using the Index
> Tuning Wizard? I have a large collection of tables (200 or so, only 30 of
> which have a large amount of data)whose indices I need to update
relatively
> quickly, and I do not have a trace file to work off of.
> I'm considering removing the PK attributes on these tables, then
reassigning
> them (which should rebuild them if I am not mistaken).
> Any tips would be appreciated. Thanks.
script the CREATE INDEX commands, but add WITH EXISTING to it. This
effectively rebuilds the index without having to drop it first
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004
Showing posts with label effective. Show all posts
Showing posts with label effective. Show all posts
Sunday, February 19, 2012
Friday, February 17, 2012
Effective restart of SSAS
In SQL Server it is possible to do an effective restart by issuing
DBCC DROPCLEANBUFFERS -- Clears the data cache
DBCC FREEPROCCACHE -- Clears the procedure cache
which is really useful for performance testing.
Is there anything similar for SSAS?
thanks
Jamie
Hi Jamie
Sending the following XML/A command will clear the AS cache. You can run this from SSMS or send the string, by calling the Execute method on the server object in AMO.
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
Cheers
Darren
Top man Darren. Thanks very much!
-Jamie
Effective Permissions for QA and EM
I am looking for information regarding permissions needed to be able to run
1) the Query Analyzer and 2) Enterprise Manager.
Since we are required to revoke all access from the public role, all
inherited permissions needed to run the above applications are gone. I
can't seem to find any documentation on which objects are needed in order to
make those functional for a particular role.
Thanks in advance,
AllenHi,
To open a database in Enterprise manager or Query Analyzer you should be
user in that partcular database. Then to access the object
you should have minimum select rights on table and exec rights on procedures
Thanks
Hari
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:uCpzs$v5GHA.4304@.TK2MSFTNGP03.phx.gbl...
>I am looking for information regarding permissions needed to be able to run
>1) the Query Analyzer and 2) Enterprise Manager.
> Since we are required to revoke all access from the public role, all
> inherited permissions needed to run the above applications are gone. I
> can't seem to find any documentation on which objects are needed in order
> to make those functional for a particular role.
> Thanks in advance,
> Allen
>|||Thanks for your reply.
I understand you have to be a user in that database, but since I revoke all
access from public, simply adding a user to a database has a net result of
nothing. I need to know /specifically/ which objects the user would need
explicit access to.
Examples include:
- system tables in master
- sprocs and extended sprocs in master
- views in master (syslogins? sysconstraints?)
- system tables in user databases they need access to
etc...
I can take care of all the user objects - I need to know about system
objects. I can't just assign people to the db_owner database role either -
I need to grant them just enough privileges to use those two applications,
but no more.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uh8vHs15GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi,
> To open a database in Enterprise manager or Query Analyzer you should be
> user in that partcular database. Then to access the object
> you should have minimum select rights on table and exec rights on
> procedures
> Thanks
> Hari
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:uCpzs$v5GHA.4304@.TK2MSFTNGP03.phx.gbl...
>
1) the Query Analyzer and 2) Enterprise Manager.
Since we are required to revoke all access from the public role, all
inherited permissions needed to run the above applications are gone. I
can't seem to find any documentation on which objects are needed in order to
make those functional for a particular role.
Thanks in advance,
AllenHi,
To open a database in Enterprise manager or Query Analyzer you should be
user in that partcular database. Then to access the object
you should have minimum select rights on table and exec rights on procedures
Thanks
Hari
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:uCpzs$v5GHA.4304@.TK2MSFTNGP03.phx.gbl...
>I am looking for information regarding permissions needed to be able to run
>1) the Query Analyzer and 2) Enterprise Manager.
> Since we are required to revoke all access from the public role, all
> inherited permissions needed to run the above applications are gone. I
> can't seem to find any documentation on which objects are needed in order
> to make those functional for a particular role.
> Thanks in advance,
> Allen
>|||Thanks for your reply.
I understand you have to be a user in that database, but since I revoke all
access from public, simply adding a user to a database has a net result of
nothing. I need to know /specifically/ which objects the user would need
explicit access to.
Examples include:
- system tables in master
- sprocs and extended sprocs in master
- views in master (syslogins? sysconstraints?)
- system tables in user databases they need access to
etc...
I can take care of all the user objects - I need to know about system
objects. I can't just assign people to the db_owner database role either -
I need to grant them just enough privileges to use those two applications,
but no more.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uh8vHs15GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi,
> To open a database in Enterprise manager or Query Analyzer you should be
> user in that partcular database. Then to access the object
> you should have minimum select rights on table and exec rights on
> procedures
> Thanks
> Hari
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:uCpzs$v5GHA.4304@.TK2MSFTNGP03.phx.gbl...
>
Effective Permissions Error with Domain User
If I create a new Login at the server level for a domain group :i.e.,
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:
> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:
> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>
Effective permission for a group
When I click on the properties of a 2005 sql server database and then
permissions.
If I select a group and then effective permission, I got an error,
saying that "cannot execute as the principal server because the 'xxx
\group' does not exist, this type of principal cannot be impersonated,
or you do not have permission (Microsoft SQL Server; Error: 15406)"
I would think the easiest would be to take a user which is a member of that
group, but I was not able to get that to work. Or more precisely, I was
not able to impersonate as such a user.
permissions.
If I select a group and then effective permission, I got an error,
saying that "cannot execute as the principal server because the 'xxx
\group' does not exist, this type of principal cannot be impersonated,
or you do not have permission (Microsoft SQL Server; Error: 15406)"
I guess the error is valid, since the group does not exist in the
database as a user. If so, how do I get the effective permissions of a
group?akkha1234@.gmail.com (akkha1234@.gmail.com) writes:
Quote:
Originally Posted by
When I click on the properties of a 2005 sql server database and then
permissions.
If I select a group and then effective permission, I got an error,
saying that "cannot execute as the principal server because the 'xxx
\group' does not exist, this type of principal cannot be impersonated,
or you do not have permission (Microsoft SQL Server; Error: 15406)"
>
I guess the error is valid, since the group does not exist in the
database as a user. If so, how do I get the effective permissions of a
group?
I would think the easiest would be to take a user which is a member of that
group, but I was not able to get that to work. Or more precisely, I was
not able to impersonate as such a user.
You can always look directly into sys.database_permissions, but I don't
really know this information is exposed. In SQL 2005 you can grant a
principal a permission on a schema, which then applies to all objects
in that schema. But I don't think there is a row for every object in
the schema, but I have not investigated this.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Effective Implementation
Hi,
I am in the process of evaluating the existing Implementation of Fulltext
Index in our product(sql server 2000).We have full text index (text type) in
a table with out timestamp column.The existing settings for creation and
maintenance are ,
sp_fulltext_catalog 'test_catalog', 'create'
sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
sp_fulltext_column 'test', 'value', 'add'
sp_fulltext_table 'test', 'activate'
sp_fulltext_catalog 'test_catalog', 'start_incremental'
I think the above setting should populate the Index.
And then they have a process which is invoked every 2 minutes and does the
following.My contention is this will ne never invoked ..If yes , then how do
i implement incremental indexing ...If no , pls tell me the scenarions under
which this will get invoked...(assume usingChangeTracking=true)
boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
if (usingChangeTracking) {
//check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
turn it on
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextChangeTrackingOn')") == 0) {
KanaPrint.println("Start change tracking for Microsoft FullText
Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_change_tracking'"});
}
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextBackgroundUpdateIndexOn')") == 0) {
KanaPrint.println("Start background updating for Microsoft
FullText Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_background_updateindex'"});
}
return;
}
Rect
Rect,
If I correctly understand that "test_catalog" is the true name of your FT
Catalog for your FT-enabled table "test", then there is a difference between
the code examples you provided. Specifically, your code runs an Incremental
Population against the FT Catalog "Test", while the Change Tracking with
Update Index in Background is operating on the FT-enabled table "Test"
within the FT Catalog "test_catalog":
sp_fulltext_catalog 'test_catalog', 'start_incremental'
-- vs.
sp_fulltext_table 'test', 'Start_change_tracking'
sp_fulltext_table 'test', 'Start_background_updateindex'
Additionally, as your table does not have a timestamp column, when you start
an Incremental Population or enable Change Tracking, what is executed is a
Full Population (see BOL title "sp_fulltext_table" and under
start_change_tracking - "If the table does not have a timestamp, start a
full population of the full-text index". Furthermore, it is not necessary to
invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
is changed.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type)
in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how
do
> i implement incremental indexing ...If no , pls tell me the scenarions
under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
>
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
hangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Thanks John for your reply.
Does that mean the followig scripts run once , would do the job.Pls
confirm... I did some cut & paste job to hide my original table name..I am
attaching here the original scripts..
sp_fulltext_database 'enable'
sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
'kc_rawtext_pk'
sp_fulltext_column 'kc_rawtext', 'value', 'add'
sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
--Not required right ?..
And then one time invokation of the following..
sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
Also tell me if i am wrong , restart of mssearch will cause full population
of the catalogs...?
Rect
sp_fulltext_table 'kc_rawtext', 'activate'
Pls review the same and give your feedback..
"John Kane" wrote:
> Rect,
> If I correctly understand that "test_catalog" is the true name of your FT
> Catalog for your FT-enabled table "test", then there is a difference between
> the code examples you provided. Specifically, your code runs an Incremental
> Population against the FT Catalog "Test", while the Change Tracking with
> Update Index in Background is operating on the FT-enabled table "Test"
> within the FT Catalog "test_catalog":
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
> -- vs.
> sp_fulltext_table 'test', 'Start_change_tracking'
> sp_fulltext_table 'test', 'Start_background_updateindex'
> Additionally, as your table does not have a timestamp column, when you start
> an Incremental Population or enable Change Tracking, what is executed is a
> Full Population (see BOL title "sp_fulltext_table" and under
> start_change_tracking - "If the table does not have a timestamp, start a
> full population of the full-text index". Furthermore, it is not necessary to
> invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
> is changed.
> Regards,
> John
>
> "Rect" <Rect@.discussions.microsoft.com> wrote in message
> news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> in
> do
> under
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
> hangeTracking"));
>
>
|||Awaiting your feedback John.
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Rect,
Yes, that is what I mean. Specifically that running the "Change Tracking"
and "Update Index in Background" should be only run once. However, I'd also
recommend that you alter the table and add a timestamp column and then set
the CT with UIiB first without running a Full Population as setting the CT
with UIiB will run automatically run a Full Population on an un-populated FT
Catalog or if it is populated, it will run an Incremental Population, but in
your case without a timestamp column it will run a Full Population.
There is no need to run both an Incremental Population when you have CT with
UIiB set, unless you have massive (>50%) updates to the table. See BOL title
"Maintaining Full-Text Indexes" for more details on when to use turn off
UIiB and use an Incremental or Full Population with Change Tracking.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:72C752D9-99A7-4B29-B21E-628B31F3E70B@.microsoft.com...
> Thanks John for your reply.
> Does that mean the followig scripts run once , would do the job.Pls
> confirm... I did some cut & paste job to hide my original table name..I am
> attaching here the original scripts..
> sp_fulltext_database 'enable'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
> sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
> 'kc_rawtext_pk'
> sp_fulltext_column 'kc_rawtext', 'value', 'add'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
> --Not required right ?..
> And then one time invokation of the following..
> sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
> sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
> Also tell me if i am wrong , restart of mssearch will cause full
population[vbcol=seagreen]
> of the catalogs...?
> Rect
>
> sp_fulltext_table 'kc_rawtext', 'activate'
>
> Pls review the same and give your feedback..
>
>
>
> "John Kane" wrote:
FT[vbcol=seagreen]
between[vbcol=seagreen]
Incremental[vbcol=seagreen]
start[vbcol=seagreen]
a[vbcol=seagreen]
necessary to[vbcol=seagreen]
it[vbcol=seagreen]
Fulltext[vbcol=seagreen]
type)[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
how[vbcol=seagreen]
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC[vbcol=seagreen]
FullText[vbcol=seagreen]
'test',[vbcol=seagreen]
'test',[vbcol=seagreen]
|||Thanks John.I shall remove the code for start_incermental portion and rest
will be run as one time activity for the fresh set-ups.Altering the table to
have timestamp column will be big exercise as many of the customers are using
this option already , as it will involve code changes also on the application
front.
Regards
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
I am in the process of evaluating the existing Implementation of Fulltext
Index in our product(sql server 2000).We have full text index (text type) in
a table with out timestamp column.The existing settings for creation and
maintenance are ,
sp_fulltext_catalog 'test_catalog', 'create'
sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
sp_fulltext_column 'test', 'value', 'add'
sp_fulltext_table 'test', 'activate'
sp_fulltext_catalog 'test_catalog', 'start_incremental'
I think the above setting should populate the Index.
And then they have a process which is invoked every 2 minutes and does the
following.My contention is this will ne never invoked ..If yes , then how do
i implement incremental indexing ...If no , pls tell me the scenarions under
which this will get invoked...(assume usingChangeTracking=true)
boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
if (usingChangeTracking) {
//check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
turn it on
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextChangeTrackingOn')") == 0) {
KanaPrint.println("Start change tracking for Microsoft FullText
Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_change_tracking'"});
}
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextBackgroundUpdateIndexOn')") == 0) {
KanaPrint.println("Start background updating for Microsoft
FullText Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_background_updateindex'"});
}
return;
}
Rect
Rect,
If I correctly understand that "test_catalog" is the true name of your FT
Catalog for your FT-enabled table "test", then there is a difference between
the code examples you provided. Specifically, your code runs an Incremental
Population against the FT Catalog "Test", while the Change Tracking with
Update Index in Background is operating on the FT-enabled table "Test"
within the FT Catalog "test_catalog":
sp_fulltext_catalog 'test_catalog', 'start_incremental'
-- vs.
sp_fulltext_table 'test', 'Start_change_tracking'
sp_fulltext_table 'test', 'Start_background_updateindex'
Additionally, as your table does not have a timestamp column, when you start
an Incremental Population or enable Change Tracking, what is executed is a
Full Population (see BOL title "sp_fulltext_table" and under
start_change_tracking - "If the table does not have a timestamp, start a
full population of the full-text index". Furthermore, it is not necessary to
invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
is changed.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type)
in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how
do
> i implement incremental indexing ...If no , pls tell me the scenarions
under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
>
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
hangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Thanks John for your reply.
Does that mean the followig scripts run once , would do the job.Pls
confirm... I did some cut & paste job to hide my original table name..I am
attaching here the original scripts..
sp_fulltext_database 'enable'
sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
'kc_rawtext_pk'
sp_fulltext_column 'kc_rawtext', 'value', 'add'
sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
--Not required right ?..
And then one time invokation of the following..
sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
Also tell me if i am wrong , restart of mssearch will cause full population
of the catalogs...?
Rect
sp_fulltext_table 'kc_rawtext', 'activate'
Pls review the same and give your feedback..
"John Kane" wrote:
> Rect,
> If I correctly understand that "test_catalog" is the true name of your FT
> Catalog for your FT-enabled table "test", then there is a difference between
> the code examples you provided. Specifically, your code runs an Incremental
> Population against the FT Catalog "Test", while the Change Tracking with
> Update Index in Background is operating on the FT-enabled table "Test"
> within the FT Catalog "test_catalog":
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
> -- vs.
> sp_fulltext_table 'test', 'Start_change_tracking'
> sp_fulltext_table 'test', 'Start_background_updateindex'
> Additionally, as your table does not have a timestamp column, when you start
> an Incremental Population or enable Change Tracking, what is executed is a
> Full Population (see BOL title "sp_fulltext_table" and under
> start_change_tracking - "If the table does not have a timestamp, start a
> full population of the full-text index". Furthermore, it is not necessary to
> invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
> is changed.
> Regards,
> John
>
> "Rect" <Rect@.discussions.microsoft.com> wrote in message
> news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> in
> do
> under
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
> hangeTracking"));
>
>
|||Awaiting your feedback John.
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Rect,
Yes, that is what I mean. Specifically that running the "Change Tracking"
and "Update Index in Background" should be only run once. However, I'd also
recommend that you alter the table and add a timestamp column and then set
the CT with UIiB first without running a Full Population as setting the CT
with UIiB will run automatically run a Full Population on an un-populated FT
Catalog or if it is populated, it will run an Incremental Population, but in
your case without a timestamp column it will run a Full Population.
There is no need to run both an Incremental Population when you have CT with
UIiB set, unless you have massive (>50%) updates to the table. See BOL title
"Maintaining Full-Text Indexes" for more details on when to use turn off
UIiB and use an Incremental or Full Population with Change Tracking.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:72C752D9-99A7-4B29-B21E-628B31F3E70B@.microsoft.com...
> Thanks John for your reply.
> Does that mean the followig scripts run once , would do the job.Pls
> confirm... I did some cut & paste job to hide my original table name..I am
> attaching here the original scripts..
> sp_fulltext_database 'enable'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
> sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
> 'kc_rawtext_pk'
> sp_fulltext_column 'kc_rawtext', 'value', 'add'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
> --Not required right ?..
> And then one time invokation of the following..
> sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
> sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
> Also tell me if i am wrong , restart of mssearch will cause full
population[vbcol=seagreen]
> of the catalogs...?
> Rect
>
> sp_fulltext_table 'kc_rawtext', 'activate'
>
> Pls review the same and give your feedback..
>
>
>
> "John Kane" wrote:
FT[vbcol=seagreen]
between[vbcol=seagreen]
Incremental[vbcol=seagreen]
start[vbcol=seagreen]
a[vbcol=seagreen]
necessary to[vbcol=seagreen]
it[vbcol=seagreen]
Fulltext[vbcol=seagreen]
type)[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
how[vbcol=seagreen]
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC[vbcol=seagreen]
FullText[vbcol=seagreen]
'test',[vbcol=seagreen]
'test',[vbcol=seagreen]
|||Thanks John.I shall remove the code for start_incermental portion and rest
will be run as one time activity for the fresh set-ups.Altering the table to
have timestamp column will be big exercise as many of the customers are using
this option already , as it will involve code changes also on the application
front.
Regards
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
Effective Date selection in SQL
Problem: Selecting the most appropriate row based upon the row's effective date (a user defined field of the row) verses the current date and time.
Platform: Microsoft SQL Server 2000
Details:
In a table of products, each record is a product, or a version of a product. These products or versions of products are "Effective Dated", meaning, a row is activated by the date it becomes effective by (do not be confused by an active row, and an active product - active row is the currently effective row, an active product is a flag set by the user as to define whether the product is active when it's effective date is reached). Example:
Product 1 (version 1)
Effective Date: 6/1/2003 12:00:00 AM
Price: $30
Status: Active
Product 1 (version 2)
Effective Date: 7/11/2003 12:00:00 AM
Price: $20
Status: Active
Product 1 (version 3)
Effective Date: 12/25/2003 12:00:00 AM
Price: $15
Status: Active
Product 1 (version 4)
Effective Date: 1/1/2004 12:00:00 AM
Status: Inactive
In the above case, this SINGLE product has four records in the product table representing different versions. Product 1 version 1 has expired since Product 1 version 2 is effective today (7/11). Product 1 version 3 and version 4, however, have not come about yet due to their effective dating in the future. Product versions 1 through 3 are all active, and the product is no longer available after 1/1/2004, when version 4 becomes effective and the status changes to "Inactive".
The problem I'm experiencing with my SQL is that I cannot differentiate between the version effective currently and the future versions.
My current SQL looks as such:
SELECT sProdName
FROM tblProducts
WHERE NOT EXISTS
(
SELECT P.cProdCode, P.dtEffDate
FROM tblProducts AS P
WHERE
tblProducts.cProdCode = P.cProdCode AND
tblProducts.dtEffDate < P.dtEffDate
)
AND bActive = 1
AND cProdCode = 'somecode'
ORDER BY sProdName
In the above SQL, bActive is a flag that declares this product as an active product (verses inactive) and cProdCode is a code that all versions of the Product share. Ex:
iID: 1
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 6/1/2003 12:00:00 AM
iID: 2
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 7/11/2003 12:00:00 AM
iID: 3
sProdName: Product 1
cProdCode: PROD1
bActive: 0
dtEffDate: 1/1/2004 12:00:00 AM
The above SQL is effective in ignoring the expired versions of the product, but returns the current and future versions. I've attempted to massage the SQL to ignore the expired and future versions (leaving me with the single, currently active, version).
Ideally, I'd like to do this without having to create flags for the records that deam them as past, present, future, or having to export versions out into an archive table. This should be able to be determined by strictly SQL, but haven't been able to get the data to return as I'd like it..
In the end, the final result would be a single record of the version of the product which is currently effective, and alertnately, the ability to retrieve a set of records listing all of the currently effective active products.
Any ideas?Now, I should mention that I have a version of this SQL that works, but I suppose my ultimate question is "Is this the most efficient and proper way to do this or is there a better way?"
Here is the SQL that does work, but it nags at me with the use of TOP and ORDER BY. It doesn't seem as though this is the proper elegant solution:
SELECT TOP 1 *
FROM tblProducts AS P
WHERE
P.iProdID IN
(
SELECT iProdID
FROM tblProducts P2
WHERE
P2.dtEffDate < GETDATE()
)
AND P.bActive = 1
AND P.cProdCode = 'somecode'
ORDER BY P.dtEffDAte DESC
If I do not use TOP, it returns every version of a product except the future versions. If I do not use ORDER BY it returns them in an order inconsistant with their effective date. So combined together, the records are sorted most current->least current, then chopped at the top for the most current effective dated product.
Now, this comes into play heavily when we start talking about retrieving the most recent version of all of the products. Sure the above works when getting the most current version of a single product, but (due to the "TOP") does not work properly when attempting to retrieve the most current version of all active products. The above SQL's "TOP" directive is in there to drop off the expired version of the product, and in this case, TOP drops off more then just expired versions of products.. it drops everything except the lucky record at the top of the set.|||i really don't understand your various definitions of active, inactive, flags, effective dates, etc.
your design sounds fairly complicated
however, i did pick up on one thing
you said your sql works, but only for one product at a time, and "does not work properly when attempting to retrieve the most current version of all active products"
okay, i think i can translate your sql so it will work for all products:
select *
from tblProducts AS P
where dtEffDate =
( select max(dtEffDate)
from tblProducts
where cProdCode = P.cProdCode
and dtEffDate < GETDATE()
and bActive = 1
)
and bActive = 1 this is a correlated subquery so it picks the highest date in each group, where the group is defined as all product rows with the same cProdCode (the P inside the subquery is the correlation variable)
let me know if it works
rudy
http://r937.com/|||Hi Rudy,
I apologize for the complexity of the project, but I think you nailed the solution. I've done some cursory testing, and just looking over the SQL, it looks right on. Tonight I'll take some time to do some more testing, but it looks good. I hadn't even thought of using the MAX function. Thanks for your assistance, I appreciate you taking the time to go through all my details (even if they were a bit long and confusing) and coming up with a solid solution.|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!
Platform: Microsoft SQL Server 2000
Details:
In a table of products, each record is a product, or a version of a product. These products or versions of products are "Effective Dated", meaning, a row is activated by the date it becomes effective by (do not be confused by an active row, and an active product - active row is the currently effective row, an active product is a flag set by the user as to define whether the product is active when it's effective date is reached). Example:
Product 1 (version 1)
Effective Date: 6/1/2003 12:00:00 AM
Price: $30
Status: Active
Product 1 (version 2)
Effective Date: 7/11/2003 12:00:00 AM
Price: $20
Status: Active
Product 1 (version 3)
Effective Date: 12/25/2003 12:00:00 AM
Price: $15
Status: Active
Product 1 (version 4)
Effective Date: 1/1/2004 12:00:00 AM
Status: Inactive
In the above case, this SINGLE product has four records in the product table representing different versions. Product 1 version 1 has expired since Product 1 version 2 is effective today (7/11). Product 1 version 3 and version 4, however, have not come about yet due to their effective dating in the future. Product versions 1 through 3 are all active, and the product is no longer available after 1/1/2004, when version 4 becomes effective and the status changes to "Inactive".
The problem I'm experiencing with my SQL is that I cannot differentiate between the version effective currently and the future versions.
My current SQL looks as such:
SELECT sProdName
FROM tblProducts
WHERE NOT EXISTS
(
SELECT P.cProdCode, P.dtEffDate
FROM tblProducts AS P
WHERE
tblProducts.cProdCode = P.cProdCode AND
tblProducts.dtEffDate < P.dtEffDate
)
AND bActive = 1
AND cProdCode = 'somecode'
ORDER BY sProdName
In the above SQL, bActive is a flag that declares this product as an active product (verses inactive) and cProdCode is a code that all versions of the Product share. Ex:
iID: 1
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 6/1/2003 12:00:00 AM
iID: 2
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 7/11/2003 12:00:00 AM
iID: 3
sProdName: Product 1
cProdCode: PROD1
bActive: 0
dtEffDate: 1/1/2004 12:00:00 AM
The above SQL is effective in ignoring the expired versions of the product, but returns the current and future versions. I've attempted to massage the SQL to ignore the expired and future versions (leaving me with the single, currently active, version).
Ideally, I'd like to do this without having to create flags for the records that deam them as past, present, future, or having to export versions out into an archive table. This should be able to be determined by strictly SQL, but haven't been able to get the data to return as I'd like it..
In the end, the final result would be a single record of the version of the product which is currently effective, and alertnately, the ability to retrieve a set of records listing all of the currently effective active products.
Any ideas?Now, I should mention that I have a version of this SQL that works, but I suppose my ultimate question is "Is this the most efficient and proper way to do this or is there a better way?"
Here is the SQL that does work, but it nags at me with the use of TOP and ORDER BY. It doesn't seem as though this is the proper elegant solution:
SELECT TOP 1 *
FROM tblProducts AS P
WHERE
P.iProdID IN
(
SELECT iProdID
FROM tblProducts P2
WHERE
P2.dtEffDate < GETDATE()
)
AND P.bActive = 1
AND P.cProdCode = 'somecode'
ORDER BY P.dtEffDAte DESC
If I do not use TOP, it returns every version of a product except the future versions. If I do not use ORDER BY it returns them in an order inconsistant with their effective date. So combined together, the records are sorted most current->least current, then chopped at the top for the most current effective dated product.
Now, this comes into play heavily when we start talking about retrieving the most recent version of all of the products. Sure the above works when getting the most current version of a single product, but (due to the "TOP") does not work properly when attempting to retrieve the most current version of all active products. The above SQL's "TOP" directive is in there to drop off the expired version of the product, and in this case, TOP drops off more then just expired versions of products.. it drops everything except the lucky record at the top of the set.|||i really don't understand your various definitions of active, inactive, flags, effective dates, etc.
your design sounds fairly complicated
however, i did pick up on one thing
you said your sql works, but only for one product at a time, and "does not work properly when attempting to retrieve the most current version of all active products"
okay, i think i can translate your sql so it will work for all products:
select *
from tblProducts AS P
where dtEffDate =
( select max(dtEffDate)
from tblProducts
where cProdCode = P.cProdCode
and dtEffDate < GETDATE()
and bActive = 1
)
and bActive = 1 this is a correlated subquery so it picks the highest date in each group, where the group is defined as all product rows with the same cProdCode (the P inside the subquery is the correlation variable)
let me know if it works
rudy
http://r937.com/|||Hi Rudy,
I apologize for the complexity of the project, but I think you nailed the solution. I've done some cursory testing, and just looking over the SQL, it looks right on. Tonight I'll take some time to do some more testing, but it looks good. I hadn't even thought of using the MAX function. Thanks for your assistance, I appreciate you taking the time to go through all my details (even if they were a bit long and confusing) and coming up with a solid solution.|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!
Subscribe to:
Posts (Atom)