Sunday, February 26, 2012
fully qualified names with named instances
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advance
Raffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance
|||[Server\Instance].database.owner_or_schema.object
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance
|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>
|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>
|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.
|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>
fully qualified names with named instances
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advanceRaffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||[Server\Instance].database.owner_or_schema.object
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>
>
fully qualified names with named instances
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advanceRaffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||[Server\Instance].database.owner_or_schema.object
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> > hallo it's unclear to me how to address a named instance using a fully
> > qualified name (server-name.database-name.owner-name.object-name)
> > In this syntax, where does the instance name fit?
> >
> > thanks in advance
>
>|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> > hallo it's unclear to me how to address a named instance using a fully
> > qualified name (server-name.database-name.owner-name.object-name)
> > In this syntax, where does the instance name fit?
> >
> > thanks in advance
>
>|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>> i already tried linking the instance, getting error 15028 - the server
>> already exists.
>
Friday, February 24, 2012
Full-text search services on existing cluster node?
It turns out that one node has full-text search installed while the other
node does not. Is there a way to install full-text search capabilities on an
existing node? Our research has provided an inconclusive answer. Short of
having to do a full re-install to the one node, is there a way to add
full-text search to an existing node?Hello,
You may want to refer to the following steps to install full-text search
service on the node:
1. Use the Searchstp.exe program to install the full-text search service.
If the full-text search service is not installed on the computer, the
Searchstp.exe program will create it.
2. Use the Ftsetup.exe program to configure the full-text search service
and to configure the instance of SQL Server as an application that uses the
full-text search service. On SQL Server clusters, the Ftsetup.exe program
also creates the required configuration files to maintain the failover
properties for the full-text search resource.
Searchstp.exe is under setupCD: x86\FullText\ftsetup.exe. You may want to
run searchstp.exe of SP4 folder to upgrade it to the proper latest version.
The Ftsetup.exe program uses the following parameter options:
? ApplicationName : For a default instance of SQL Server, the parameter
value must be SQLServer. For a named instance of SQL Server, the parameter
value must be SQLServer\ Instance Name .
? User : For a local system account, the parameter value must be 0. For a
domain user account, the value of the parameter must be Domain Name \ User
Account .
? IsMasterNode : This parameter indicates whether the Ftsetup.exe program
runs on the node that owns the disk where the FTDATA folder will be
created. The parameter value must be 0 or 1.
? IsUpgrade : This parameter indicates whether the FtSetup.exe program is
upgrading the full-text search service to a later version. This parameter
value must be 0 or 1.
? IsCluster : This parameter indicates whether the Ftsetup.exe program runs
on a clustered instance of SQL Server. For a stand-alone instance of SQL
Server, the parameter value must be 0. For a clustered instance of SQL
Server, the parameter value must be 1.
? IsUninstall : This parameter indicates whether the Ftsetup.exe program
removes the full-text search service. If the Ftsetup.exe program installs
the full-text search service, the parameter value must be 0. If the
Ftsetup.exe program removes the full-text search service, the parameter
value must be 1. However, if the parameter value is 1, the values for the
IsMasterNode parameter, the IsUpgrade parameter, and the IsCluster
parameter must all be set to 0.
You shall also remove the registry entries for a clean removal of the
full-text search service. To do so, remove the following registry keys on
both nodes of the SQL Server cluster:
? HKEY_LOCAL_MACHINE\Software\Microsoft\Search
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSCNTRS
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSEARCH
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSGATHERER
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSGTHRSVC
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSINDEX
Since the issue might be complex, we probably will not be able to resolve
the issue through the newsgroups. If above steps does not work for you, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Full-text search services on existing cluster node?
00.
It turns out that one node has full-text search installed while the other
node does not. Is there a way to install full-text search capabilities on an
existing node? Our research has provided an inconclusive answer. Short of
having to do a full re-install to the one node, is there a way to add
full-text search to an existing node?Hello,
You may want to refer to the following steps to install full-text search
service on the node:
1. Use the Searchstp.exe program to install the full-text search service.
If the full-text search service is not installed on the computer, the
Searchstp.exe program will create it.
2. Use the Ftsetup.exe program to configure the full-text search service
and to configure the instance of SQL Server as an application that uses the
full-text search service. On SQL Server clusters, the Ftsetup.exe program
also creates the required configuration files to maintain the failover
properties for the full-text search resource.
Searchstp.exe is under setupCD: x86\FullText\ftsetup.exe. You may want to
run searchstp.exe of SP4 folder to upgrade it to the proper latest version.
The Ftsetup.exe program uses the following parameter options:
ApplicationName : For a default instance of SQL Server, the parameter
value must be SQLServer. For a named instance of SQL Server, the parameter
value must be SQLServer\ Instance Name .
User : For a local system account, the parameter value must be 0. For a
domain user account, the value of the parameter must be Domain Name \ User
Account .
IsMasterNode : This parameter indicates whether the Ftsetup.exe program
runs on the node that owns the disk where the FTDATA folder will be
created. The parameter value must be 0 or 1.
IsUpgrade : This parameter indicates whether the FtSetup.exe program is
upgrading the full-text search service to a later version. This parameter
value must be 0 or 1.
IsCluster : This parameter indicates whether the Ftsetup.exe program runs
on a clustered instance of SQL Server. For a stand-alone instance of SQL
Server, the parameter value must be 0. For a clustered instance of SQL
Server, the parameter value must be 1.
IsUninstall : This parameter indicates whether the Ftsetup.exe program
removes the full-text search service. If the Ftsetup.exe program installs
the full-text search service, the parameter value must be 0. If the
Ftsetup.exe program removes the full-text search service, the parameter
value must be 1. However, if the parameter value is 1, the values for the
IsMasterNode parameter, the IsUpgrade parameter, and the IsCluster
parameter must all be set to 0.
You shall also remove the registry entries for a clean removal of the
full-text search service. To do so, remove the following registry keys on
both nodes of the SQL Server cluster:
HKEY_LOCAL_MACHINE\Software\Microsoft\Se
arch
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSCNTRS
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSEARCH
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSGATHERER
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSGTHRSVC
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSINDEX
Since the issue might be complex, we probably will not be able to resolve
the issue through the newsgroups. If above steps does not work for you, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administered un
Hello and thank you for your assistance.
I have two instances of SQL Server 2000 Enterprise Edition (SP4) database running on Windows 2000 Enterprise server. The default instance is dev and a named instance for QA. Full-text search is enabled for dev and is functioning properly.
@.@.Version:
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I am trying to enable full text search in the QA instance with this command: "sp_fulltext_database 'enable' ", but I am receiving the error "Server: Msg 7635 The Microsoft Search service cannot be administered under the present user account.
I can find any reference to what account it needs to run under. Can you provide me with info on why I am getting this message?
This is a common error with SQL Server 2000 Full-text Search (FTS) and the MSSearch service and there can be a few root reasons for it.
1. Did this error occur after a service pack upgrade? If so - review the files and folders under \FTDATA (in the sql server installed instance folder) against what is on your SQL Server 2000 CD. If you find any difference - copy the missing or different file from the CD to the problem server.
2. Have you removed the BUILTIN\Administrators login? If so - either add it back in or execute the sp_grantlogin from the code below as the MSSearch service requires this level of access to the MSSQLServer service.
3. Check these:
317746 PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/kb/317746
277549 PRB: Unable to Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through Control Panel
http://support.microsoft.com/kb/277549
4. Can you run this script and post the results back?
use master
go
SELLECT @.@.version
go
-- many need to set on "show advanced options"
sp_configure 'default full-text language'
go
SELECT FullTextServiceProperty('ResourceUsage') as MSSearch_Resource_Usage
go
xp_logininfo 'BUILTIN\ADMINISTRATORS', 'members'
go
-- if you have removed the BUILTIN\Administrators login, run:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
5. And just to cover all the bases, when you changed the MSSQLServer startup account, did you do it via Win2K's Component Services? If so, could you "re-change" it via the Enterprise Manager's server property security tab?
Filip Skakun
|||Filip,
Thank you so much for your response. To answer your questions; No we haven't added any service packs recently. Yes, we do have the Builtin/Administer removed and the NT Authority has all the required privileges. Keep in mind that the default instance was working just fine.
Since the MSSQL$Inst2/FTData directory didn't exist; I suspect that FullText search was not installed with the second instance. None the less, I re-installed fulltext search (see http://support.microsoft.com/kb/827449). After doing this I no longer received the "Cannot be administered under the present account" error and the FTData directory was created for the second instance. But, at this point Full text search didn't work for both instances. I wasn't receiving any errors, but the catalogs were not getting populated. We later found that the switch in the KB article was incorrect for our servers. The article had us run "ftsetup.exe SQLServer$Instance 1 0 0 0" (incorrect). After we ran "ftsetup.exe SQLServer$Instance 0 1 0 0 0" (correct) FullText search began to function correctly for both instances.
To be honest, I don't think I had to reinstall everything. I believe I could have gotten away with just running ftsetup.exe for the second instance.
|||I am glad you got it working,
Regards,
Filip
Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administered un
Hello and thank you for your assistance.
I have two instances of SQL Server 2000 Enterprise Edition (SP4) database running on Windows 2000 Enterprise server. The default instance is dev and a named instance for QA. Full-text search is enabled for dev and is functioning properly.
@.@.Version:
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I am trying to enable full text search in the QA instance with this command: "sp_fulltext_database 'enable' ", but I am receiving the error "Server: Msg 7635 The Microsoft Search service cannot be administered under the present user account.
I can find any reference to what account it needs to run under. Can you provide me with info on why I am getting this message?
This is a common error with SQL Server 2000 Full-text Search (FTS) and the MSSearch service and there can be a few root reasons for it.
1. Did this error occur after a service pack upgrade? If so - review the files and folders under \FTDATA (in the sql server installed instance folder) against what is on your SQL Server 2000 CD. If you find any difference - copy the missing or different file from the CD to the problem server.
2. Have you removed the BUILTIN\Administrators login? If so - either add it back in or execute the sp_grantlogin from the code below as the MSSearch service requires this level of access to the MSSQLServer service.
3. Check these:
317746 PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/kb/317746
277549 PRB: Unable to Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through Control Panel
http://support.microsoft.com/kb/277549
4. Can you run this script and post the results back?
use master
go
SELLECT @.@.version
go
-- many need to set on "show advanced options"
sp_configure 'default full-text language'
go
SELECT FullTextServiceProperty('ResourceUsage') as MSSearch_Resource_Usage
go
xp_logininfo 'BUILTIN\ADMINISTRATORS', 'members'
go
-- if you have removed the BUILTIN\Administrators login, run:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
5. And just to cover all the bases, when you changed the MSSQLServer startup account, did you do it via Win2K's Component Services? If so, could you "re-change" it via the Enterprise Manager's server property security tab?
Filip Skakun
|||Filip,
Thank you so much for your response. To answer your questions; No we haven't added any service packs recently. Yes, we do have the Builtin/Administer removed and the NT Authority has all the required privileges. Keep in mind that the default instance was working just fine.
Since the MSSQL$Inst2/FTData directory didn't exist; I suspect that FullText search was not installed with the second instance. None the less, I re-installed fulltext search (see http://support.microsoft.com/kb/827449). After doing this I no longer received the "Cannot be administered under the present account" error and the FTData directory was created for the second instance. But, at this point Full text search didn't work for both instances. I wasn't receiving any errors, but the catalogs were not getting populated. We later found that the switch in the KB article was incorrect for our servers. The article had us run "ftsetup.exe SQLServer$Instance 1 0 0 0" (incorrect). After we ran "ftsetup.exe SQLServer$Instance 0 1 0 0 0" (correct) FullText search began to function correctly for both instances.
To be honest, I don't think I had to reinstall everything. I believe I could have gotten away with just running ftsetup.exe for the second instance.
|||I am glad you got it working,
Regards,
Filip
Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administere
Hello and thank you for your assistance.
I have two instances of SQL Server 2000 Enterprise Edition (SP4) database running on Windows 2000 Enterprise server. The default instance is dev and a named instance for QA. Full-text search is enabled for dev and is functioning properly.
@.@.Version:
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I am trying to enable full text search in the QA instance with this command: "sp_fulltext_database 'enable' ", but I am receiving the error "Server: Msg 7635 The Microsoft Search service cannot be administered under the present user account.
I can find any reference to what account it needs to run under. Can you provide me with info on why I am getting this message?
This is a common error with SQL Server 2000 Full-text Search (FTS) and the MSSearch service and there can be a few root reasons for it.
1. Did this error occur after a service pack upgrade? If so - review the files and folders under \FTDATA (in the sql server installed instance folder) against what is on your SQL Server 2000 CD. If you find any difference - copy the missing or different file from the CD to the problem server.
2. Have you removed the BUILTIN\Administrators login? If so - either add it back in or execute the sp_grantlogin from the code below as the MSSearch service requires this level of access to the MSSQLServer service.
3. Check these:
317746 PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/kb/317746
277549 PRB: Unable to Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through Control Panel
http://support.microsoft.com/kb/277549
4. Can you run this script and post the results back?
use master
go
SELLECT @.@.version
go
-- many need to set on "show advanced options"
sp_configure 'default full-text language'
go
SELECT FullTextServiceProperty('ResourceUsage') as MSSearch_Resource_Usage
go
xp_logininfo 'BUILTIN\ADMINISTRATORS', 'members'
go
-- if you have removed the BUILTIN\Administrators login, run:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
5. And just to cover all the bases, when you changed the MSSQLServer startup account, did you do it via Win2K's Component Services? If so, could you "re-change" it via the Enterprise Manager's server property security tab?
Filip Skakun
|||Filip,
Thank you so much for your response. To answer your questions; No we haven't added any service packs recently. Yes, we do have the Builtin/Administer removed and the NT Authority has all the required privileges. Keep in mind that the default instance was working just fine.
Since the MSSQL$Inst2/FTData directory didn't exist; I suspect that FullText search was not installed with the second instance. None the less, I re-installed fulltext search (see http://support.microsoft.com/kb/827449). After doing this I no longer received the "Cannot be administered under the present account" error and the FTData directory was created for the second instance. But, at this point Full text search didn't work for both instances. I wasn't receiving any errors, but the catalogs were not getting populated. We later found that the switch in the KB article was incorrect for our servers. The article had us run "ftsetup.exe SQLServer$Instance 1 0 0 0" (incorrect). After we ran "ftsetup.exe SQLServer$Instance 0 1 0 0 0" (correct) FullText search began to function correctly for both instances.
To be honest, I don't think I had to reinstall everything. I believe I could have gotten away with just running ftsetup.exe for the second instance.
|||I am glad you got it working,
Regards,
Filip
Sunday, February 19, 2012
Full-Text Search / User Instances
FTS is disabled for User Instances.
Mike
|||We just spent quite a bit of time adding support for user instances into our application. It solves a number of problems for us and we're very happy with how it works. But I wouldn't have done it had I known about the FTS limitation.
Please tell me there is a chance of this changing at some point?
|||does this go for the default user instance as well
\sqlExpress
|||.\sqlexpress is not a User Instance, it is the default named instance that SQL Express installs to; we call this the "main" instance. FTS is functional in the main instance.
Mike Wachal - SQL Express team
|||Hi Marc,
This won't change for SQL 2005 but we are investigating it for a future version.
Mike Wachal - SQL Express team