Hi,
How can I change the location where the RS log files are
stored/created? The .config file seems to have options to change the
filename, but will this allow me to specify a different folder as
well?
We would like to put these files on a seperate hard drive from our
main application drive, in case the log files ever happen to grow
exponentially and fill the entire DB again.
Matt BrownEach of the following files for MS Reporting Services has a section called
RStrace that contains the settings for the log files.
...\ReportServer\bin\ReportingServicesService.exe.config
â?¦\ReportServer\web.config
â?¦\ReportManager\web.config
A default installation writes the log files to the directory where Reporting
Services is installed under the LogFiles folder. In order to change the
default location of the log files you need to add the following line under
the RStrace section for each of the above files. Once you have added the
directory line you will need to restart the ReportServer service.
<add name=â'Directoryâ' value=â'your dirâ' />
Example:
<RStrace>
<add name="Directory" value="d:\RSLogs" />
<add name="FileName" value="ReportServerService_" />
<add name="FileSizeLimitMb" value="32" />
<add name="KeepFilesForDays" value="14" />
<add name="Prefix" value="tid, time" />
<add name="TraceListeners" value="debugwindow, file"
/>
<add name="TraceFileMode" value="unique" />
<add name="Components" value="all" />
</RStrace>
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Thursday, March 29, 2012
Change location of an Existing DB in MSDE
Hi everyone,
I have an MSDE database that was created using the osql.exe. By default it gets automatically created in the MSSQL7/data directory.
Does anyone know how i can detach and then re-attach the database from a new location?Refer to SP_ATTACH_DB & SP_DETACH_DB topics in books online.
Also you can mention path for data files while using CREATE DATABASE Statement.|||Have U thought of
1)Backup db
2)copy the mdf & ldf files
3)delete the db
4)create a new db with same name but data files in the new place
5)overwite the new files (s'be same name) with the original files
(Don't know if u have to stop the SQL Service to do this bit)
or do 1) 3) & 4) + restore db
Worth a try
Ye Hah !
GW
I have an MSDE database that was created using the osql.exe. By default it gets automatically created in the MSSQL7/data directory.
Does anyone know how i can detach and then re-attach the database from a new location?Refer to SP_ATTACH_DB & SP_DETACH_DB topics in books online.
Also you can mention path for data files while using CREATE DATABASE Statement.|||Have U thought of
1)Backup db
2)copy the mdf & ldf files
3)delete the db
4)create a new db with same name but data files in the new place
5)overwite the new files (s'be same name) with the original files
(Don't know if u have to stop the SQL Service to do this bit)
or do 1) 3) & 4) + restore db
Worth a try
Ye Hah !
GW
Tuesday, March 27, 2012
Change in windows platform from XP to NT
hello... my SQL database is being not able to be accessed by application when i run through VS2005 . I created the database in XP sys now brought to a NT systeem..what shd i do ..? pls help 
Hi,
Could you show us the error message so that we can solve the problem more quickly. In your case, I think there's no relationship with Windows platform.Maybe you haven't attached your database into your current SQLServer properly.You may backup your database from your old SQLServer and restore the backup file into the new one. Thanks.
Change Identify/Primary Key Column Data Type?
Hi ...
I've
taken over a project where the tables were created with
identity/primary key columns of type DECIMAL(12,0). The latest addition
to the project is to replicate data down to Pocket PC applications.
Replication requires that identify/primary key columns be of type
INT/BIGINT.
I've attempted to ALTER TABLE xxx ALTER COLUMN yyy
BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.
I then did an ALTER TABLE xxx NOCHECK CONSTRAINT
ALL; for every table in the system to disable checking of foreign keys
and then attempted to alter the column to a bigint and it still failed.
How
can I change the column from Decimal to BIGINT - or do I have to create
new tables, import all the data, get rid of the original tables? Please
tell me I don't have to do the latter.
Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.
Thursday, March 22, 2012
change db owner
Hi ,
i have created a db and somehow the owner is not the
one that i wanted , how do i actually change the owner of
that db ?
which sp can i use ?
thks & rdgs
sp_changedbowner
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:6bb001c4940f$1868d400$a301280a@.phx.gbl...
> Hi ,
> i have created a db and somehow the owner is not the
> one that i wanted , how do i actually change the owner of
> that db ?
> which sp can i use ?
> thks & rdgs
i have created a db and somehow the owner is not the
one that i wanted , how do i actually change the owner of
that db ?
which sp can i use ?
thks & rdgs
sp_changedbowner
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:6bb001c4940f$1868d400$a301280a@.phx.gbl...
> Hi ,
> i have created a db and somehow the owner is not the
> one that i wanted , how do i actually change the owner of
> that db ?
> which sp can i use ?
> thks & rdgs
Tuesday, March 20, 2012
change db owner
Hi ,
i have created a db and somehow the owner is not the
one that i wanted , how do i actually change the owner of
that db ?
which sp can i use ?
thks & rdgssp_changedbowner
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:6bb001c4940f$1868d400$a301280a@.phx.gbl...
> Hi ,
> i have created a db and somehow the owner is not the
> one that i wanted , how do i actually change the owner of
> that db ?
> which sp can i use ?
> thks & rdgs|||>--Original Message--
>sp_changedbowner
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:6bb001c4940f$1868d400$a301280a@.phx.gbl...
>> Hi ,
>> i have created a db and somehow the owner is not the
>> one that i wanted , how do i actually change the owner
of
>> that db ?
>> which sp can i use ?
>> thks & rdgs
>
>.
>sql
i have created a db and somehow the owner is not the
one that i wanted , how do i actually change the owner of
that db ?
which sp can i use ?
thks & rdgssp_changedbowner
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:6bb001c4940f$1868d400$a301280a@.phx.gbl...
> Hi ,
> i have created a db and somehow the owner is not the
> one that i wanted , how do i actually change the owner of
> that db ?
> which sp can i use ?
> thks & rdgs|||>--Original Message--
>sp_changedbowner
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:6bb001c4940f$1868d400$a301280a@.phx.gbl...
>> Hi ,
>> i have created a db and somehow the owner is not the
>> one that i wanted , how do i actually change the owner
of
>> that db ?
>> which sp can i use ?
>> thks & rdgs
>
>.
>sql
change datasource with sqlReportingService2005 class
hi pro , i created model project with .net 2005 after that , create report with reportbuilder that it use this model. it s ok.
i have project and i want to change datasource (connection string ) in runtime when User click on report ,i use sqlReportingService2005 class :
Dim rs As New ReportingService2005()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "Data Source=" & serverName & ";Initial Catalog=" & DataBaseName & ";Integrated Security=True"
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = False ' True
definition.Prompt = Nothing
definition.WindowsCredentials = True ' False
Try
rs.SetDataSourceContents("/Data Sources/" & DataSourceName, definition)
Catch ex As SoapException
Console.WriteLine(ex.Detail.OuterXml)
End Try
End Sub
error "An unhandled exception of type 'System.Net.WebException' occurred in System.Web.Services.dll
Additional information: The request failed with HTTP status 404: Object Not Found." happend
( i checked Enable Unmanged code debugging =true)
please help me
tanks
This error occurs only when the report server URL is given wrong or does not exists. Do check your report server URL assigned to the rs.Url property.
tanks for reply. i was forgotten to say : i have 2 machin ,the first one uses Windows 2003 Server and i installed sql server on it , another machin uses windows a xp and i run project on it( it s like client). when i take project on sql server 2005 it s work nice but when i run project on xp it dosent work.
Are you able to open the report server url in browser?
Change DataSource between Development and Production
I have some existing .NET app, and so I'd like to integrate the RS w/
them.
Reports created by RS is very impressive, now I need to think about
integration. Two choices for me:
1. URL Access
2. Web Services
Option 1 is the fastest and efficient way, has the followings:
1) DataSource in dev and prod are different, doesn't seem like easy to
change them w/ modifying in the project.
2) SessionID, UserName and Password and other parameters are exposed in
URL unless using SSL, otherwise not secure.
Option 2 needs more coding just like a normal .NET projects but w/o
security concerns, still have to worry about changing DataSource stuff.
For example for .rdl
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
Our existing .NET has its own web.config file works like a .ini, we put
in all data source connection there, when move to production,
Production Release team needs to change SQL box name, UserID and
password inside of the web.config. Now, how do they change them in
.rdl?
Thanks in advance.Couple of things to consider. First, datasource should be a non-problem if
you use shared datasources (which I recommend). The shared datasource has to
have the same name in both production and dev but that is it. The default
when you deploy is to not overwrite the datasource so once you set it up in
production it will be undisturbed. Second, I suggest using a specific
username and password for retrieving the data from wherever you are getting
it. I create a special readonly user that is used by RS only. This is great
for security since it is readonly and you benefit from connection polling
which you would not if the user account of the user requesting the report is
used. If you report needs to include the user in the where clause (or you
need to use a filter with the username) then user the global variable
User!UserID.
As I said, rdl does not change at all for the datasource.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hifchan@.yahoo.com> wrote in message
news:1108586517.346073.261520@.c13g2000cwb.googlegroups.com...
> I have some existing .NET app, and so I'd like to integrate the RS w/
> them.
> Reports created by RS is very impressive, now I need to think about
> integration. Two choices for me:
> 1. URL Access
> 2. Web Services
> Option 1 is the fastest and efficient way, has the followings:
> 1) DataSource in dev and prod are different, doesn't seem like easy to
> change them w/ modifying in the project.
> 2) SessionID, UserName and Password and other parameters are exposed in
> URL unless using SSL, otherwise not secure.
> Option 2 needs more coding just like a normal .NET projects but w/o
> security concerns, still have to worry about changing DataSource stuff.
> For example for .rdl
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
> Our existing .NET has its own web.config file works like a .ini, we put
> in all data source connection there, when move to production,
> Production Release team needs to change SQL box name, UserID and
> password inside of the web.config. Now, how do they change them in
> .rdl?
> Thanks in advance.
>|||Bruce,
You meant
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
not matter as long as the .rdl reference the same <Name> only?|||Yes this is correct. The data source ID is just an internal id used by
report designer only. That is also the reason why the DataSourceID element
is in the report designer namespace: <rd:DataSourceID>. The report server
ignores these ids.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hifchan@.yahoo.com> wrote in message
news:1108657866.193950.180150@.g14g2000cwa.googlegroups.com...
> Bruce,
> You meant
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
> not matter as long as the .rdl reference the same <Name> only?
>
them.
Reports created by RS is very impressive, now I need to think about
integration. Two choices for me:
1. URL Access
2. Web Services
Option 1 is the fastest and efficient way, has the followings:
1) DataSource in dev and prod are different, doesn't seem like easy to
change them w/ modifying in the project.
2) SessionID, UserName and Password and other parameters are exposed in
URL unless using SSL, otherwise not secure.
Option 2 needs more coding just like a normal .NET projects but w/o
security concerns, still have to worry about changing DataSource stuff.
For example for .rdl
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
Our existing .NET has its own web.config file works like a .ini, we put
in all data source connection there, when move to production,
Production Release team needs to change SQL box name, UserID and
password inside of the web.config. Now, how do they change them in
.rdl?
Thanks in advance.Couple of things to consider. First, datasource should be a non-problem if
you use shared datasources (which I recommend). The shared datasource has to
have the same name in both production and dev but that is it. The default
when you deploy is to not overwrite the datasource so once you set it up in
production it will be undisturbed. Second, I suggest using a specific
username and password for retrieving the data from wherever you are getting
it. I create a special readonly user that is used by RS only. This is great
for security since it is readonly and you benefit from connection polling
which you would not if the user account of the user requesting the report is
used. If you report needs to include the user in the where clause (or you
need to use a filter with the username) then user the global variable
User!UserID.
As I said, rdl does not change at all for the datasource.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hifchan@.yahoo.com> wrote in message
news:1108586517.346073.261520@.c13g2000cwb.googlegroups.com...
> I have some existing .NET app, and so I'd like to integrate the RS w/
> them.
> Reports created by RS is very impressive, now I need to think about
> integration. Two choices for me:
> 1. URL Access
> 2. Web Services
> Option 1 is the fastest and efficient way, has the followings:
> 1) DataSource in dev and prod are different, doesn't seem like easy to
> change them w/ modifying in the project.
> 2) SessionID, UserName and Password and other parameters are exposed in
> URL unless using SSL, otherwise not secure.
> Option 2 needs more coding just like a normal .NET projects but w/o
> security concerns, still have to worry about changing DataSource stuff.
> For example for .rdl
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
> Our existing .NET has its own web.config file works like a .ini, we put
> in all data source connection there, when move to production,
> Production Release team needs to change SQL box name, UserID and
> password inside of the web.config. Now, how do they change them in
> .rdl?
> Thanks in advance.
>|||Bruce,
You meant
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
not matter as long as the .rdl reference the same <Name> only?|||Yes this is correct. The data source ID is just an internal id used by
report designer only. That is also the reason why the DataSourceID element
is in the report designer namespace: <rd:DataSourceID>. The report server
ignores these ids.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hifchan@.yahoo.com> wrote in message
news:1108657866.193950.180150@.g14g2000cwa.googlegroups.com...
> Bruce,
> You meant
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
> not matter as long as the .rdl reference the same <Name> only?
>
Change Database Owner
Hello there
I don't member of DBO owners of my database on sql server
After i've created new table it has my name as an owner
How can i change the owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilUse sp_changeobjectowner
Markus|||sp_changedbowner
Changes the owner of the current database.
Syntax
sp_changedbowner [ @.loginame = ] 'login'
[ , [ @.map = ] remap_alias_flag ]
Arguments
[@.loginame =] 'login'
Is the login ID of the new owner of the current database. login is sysname,
with no default. login must be Microsoft SQL ServerT login or a Microsoft
Windows NT user that already exists. login cannot become the owner of the
current database if it already has access to the database through an
existing alias or user security account within the database. To avoid this,
drop the alias or user within the current database first.
[@.map =] remap_alias_flag
Is the value true or false, which indicates whether existing aliases to the
old database owner (dbo) are mapped to the new owner of the current database
or dropped. remap_alias_flag is varchar(5), with a default of NULL,
indicating any existing aliases to the old dbo are mapped to the new owner
of the current database. false indicates that existing aliases to the old
database owner are dropped.
Return Code Values
0 (success) or 1 (failure)
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Hi Roy
Do you want to change the owner of the database (as your subject suggests)
or the owner of the tables?
I will assume, based on your message, that you want to change the table
owner, or have the table owner be 'dbo'.
DBO is a user name. It is a member of a role called db_owners. Members of
the db_owners role can do everything that the owner of the database can do,
within the database.
Other users can also be members of the db_owners role, but their user will
be something other than DBO. That sounds like your situation.
Normally, when a user creates a table, the owner of the table is the user
name of whoever creates it. So if DBO creates the table, it is owned by DBO,
if Roy, who might be a member of db_owners role, creates a table, it is
owned by Roy.
Members of the db_owners role do have a special permission that they can
create tables owned by other users:
CREATE TABLE dbo.mytable
(....)
Or, members of db_owners role can change the owner of existing objects.
EXEC sp_changeobjectowner 'roy.mytable', 'dbo'
Please see more details about this command in the Books Online., You might
also want to read up about login names and user names.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
I don't member of DBO owners of my database on sql server
After i've created new table it has my name as an owner
How can i change the owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilUse sp_changeobjectowner
Markus|||sp_changedbowner
Changes the owner of the current database.
Syntax
sp_changedbowner [ @.loginame = ] 'login'
[ , [ @.map = ] remap_alias_flag ]
Arguments
[@.loginame =] 'login'
Is the login ID of the new owner of the current database. login is sysname,
with no default. login must be Microsoft SQL ServerT login or a Microsoft
Windows NT user that already exists. login cannot become the owner of the
current database if it already has access to the database through an
existing alias or user security account within the database. To avoid this,
drop the alias or user within the current database first.
[@.map =] remap_alias_flag
Is the value true or false, which indicates whether existing aliases to the
old database owner (dbo) are mapped to the new owner of the current database
or dropped. remap_alias_flag is varchar(5), with a default of NULL,
indicating any existing aliases to the old dbo are mapped to the new owner
of the current database. false indicates that existing aliases to the old
database owner are dropped.
Return Code Values
0 (success) or 1 (failure)
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Hi Roy
Do you want to change the owner of the database (as your subject suggests)
or the owner of the tables?
I will assume, based on your message, that you want to change the table
owner, or have the table owner be 'dbo'.
DBO is a user name. It is a member of a role called db_owners. Members of
the db_owners role can do everything that the owner of the database can do,
within the database.
Other users can also be members of the db_owners role, but their user will
be something other than DBO. That sounds like your situation.
Normally, when a user creates a table, the owner of the table is the user
name of whoever creates it. So if DBO creates the table, it is owned by DBO,
if Roy, who might be a member of db_owners role, creates a table, it is
owned by Roy.
Members of the db_owners role do have a special permission that they can
create tables owned by other users:
CREATE TABLE dbo.mytable
(....)
Or, members of db_owners role can change the owner of existing objects.
EXEC sp_changeobjectowner 'roy.mytable', 'dbo'
Please see more details about this command in the Books Online., You might
also want to read up about login names and user names.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
Change Database Name
Excuse my dumb question, I am new to SQL.
Can I change the name of a database once I have created it?
And if so, how?
ThanksYes...take a look at the system stored procedure
sp_renamedb.
EXEC sp_renamedb 'CurrentDBName', 'NewDBName'
You can find more information on this stored procedure in
books online.
-Sue
On Mon, 8 Mar 2004 14:26:46 -0000, "Keith" <@..> wrote:
>Excuse my dumb question, I am new to SQL.
>Can I change the name of a database once I have created it?
>And if so, how?
>Thanks
>|||Use "sp_renamedb"
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Keith" <@..> wrote in message news:eu3GelRBEHA.2796@.TK2MSFTNGP09.phx.gbl...
> Excuse my dumb question, I am new to SQL.
> Can I change the name of a database once I have created it?
> And if so, how?
> Thanks
>
>|||Keith
You can also use 'Alter database' with 'Modify Name' parameter.
As Microsoft are moving more towards doing changes with the alter statement
it is worth being aware of this.
Regards
John|||Hi,
Ensure that no users are accessing the database, You will get the below
error incase any connection exists for the database
"Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation."
Detailed steps to rename the database with commands to rename PUBS database
to PUBS1
How to remove connections
--
Alter database pubs set single_user with rollback IMMEDIATE
Rename the database
--
Alter database pubs modify name = pubs1
Set multi user
--
Alter database pubs1 set multi_user
Thanks
Hari
MCDBA
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:29869442-685D-452C-BE50-4C4A8A39888C@.microsoft.com...
> Keith
> You can also use 'Alter database' with 'Modify Name' parameter.
> As Microsoft are moving more towards doing changes with the alter
statement it is worth being aware of this.
> Regards
> John|||Thank you everyone
"Keith" <@..> wrote in message news:eu3GelRBEHA.2796@.TK2MSFTNGP09.phx.gbl...
> Excuse my dumb question, I am new to SQL.
> Can I change the name of a database once I have created it?
> And if so, how?
> Thanks
>
>
Can I change the name of a database once I have created it?
And if so, how?
ThanksYes...take a look at the system stored procedure
sp_renamedb.
EXEC sp_renamedb 'CurrentDBName', 'NewDBName'
You can find more information on this stored procedure in
books online.
-Sue
On Mon, 8 Mar 2004 14:26:46 -0000, "Keith" <@..> wrote:
>Excuse my dumb question, I am new to SQL.
>Can I change the name of a database once I have created it?
>And if so, how?
>Thanks
>|||Use "sp_renamedb"
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Keith" <@..> wrote in message news:eu3GelRBEHA.2796@.TK2MSFTNGP09.phx.gbl...
> Excuse my dumb question, I am new to SQL.
> Can I change the name of a database once I have created it?
> And if so, how?
> Thanks
>
>|||Keith
You can also use 'Alter database' with 'Modify Name' parameter.
As Microsoft are moving more towards doing changes with the alter statement
it is worth being aware of this.
Regards
John|||Hi,
Ensure that no users are accessing the database, You will get the below
error incase any connection exists for the database
"Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation."
Detailed steps to rename the database with commands to rename PUBS database
to PUBS1
How to remove connections
--
Alter database pubs set single_user with rollback IMMEDIATE
Rename the database
--
Alter database pubs modify name = pubs1
Set multi user
--
Alter database pubs1 set multi_user
Thanks
Hari
MCDBA
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:29869442-685D-452C-BE50-4C4A8A39888C@.microsoft.com...
> Keith
> You can also use 'Alter database' with 'Modify Name' parameter.
> As Microsoft are moving more towards doing changes with the alter
statement it is worth being aware of this.
> Regards
> John|||Thank you everyone
"Keith" <@..> wrote in message news:eu3GelRBEHA.2796@.TK2MSFTNGP09.phx.gbl...
> Excuse my dumb question, I am new to SQL.
> Can I change the name of a database once I have created it?
> And if so, how?
> Thanks
>
>
Monday, March 19, 2012
Change data structure of Replicated data
Hello,
I have a database in SQL Server 2000, on which created publication and it
has one subscriber .
now if I change in data structure , following error occured.
'EMPMAST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
columns to table 'EMPMAST' because it is being published for merge
replication.
Harsha........................
Harsha,
please take a look at sp_repladdcolumn.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||and sp_addmergearticle for merge replication
"Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a database in SQL Server 2000, on which created publication and it
> has one subscriber .
> now if I change in data structure , following error occured.
> 'EMPMAST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
> columns to table 'EMPMAST' because it is being published for merge
> replication.
> Harsha........................
>
>
|||"uykusuz" <yb> wrote in message news:eCssQMmdFHA.720@.TK2MSFTNGP15.phx.gbl...
> and sp_addmergearticle for merge replication
that is for adding articles not columns , sorry
[vbcol=seagreen]
> "Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
> news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
it
>
I have a database in SQL Server 2000, on which created publication and it
has one subscriber .
now if I change in data structure , following error occured.
'EMPMAST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
columns to table 'EMPMAST' because it is being published for merge
replication.
Harsha........................
Harsha,
please take a look at sp_repladdcolumn.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||and sp_addmergearticle for merge replication
"Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a database in SQL Server 2000, on which created publication and it
> has one subscriber .
> now if I change in data structure , following error occured.
> 'EMPMAST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
> columns to table 'EMPMAST' because it is being published for merge
> replication.
> Harsha........................
>
>
|||"uykusuz" <yb> wrote in message news:eCssQMmdFHA.720@.TK2MSFTNGP15.phx.gbl...
> and sp_addmergearticle for merge replication
that is for adding articles not columns , sorry
[vbcol=seagreen]
> "Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
> news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
it
>
Change data source name
Hi all,
I have created my reports using MDB file. Now I have changed my DB to SQL Server.
I can go and change my Datasource loaction on my machine and the reports work fine. But I need to distribute these reports to all my clients.
And all have different server names.
I am using CRX and VB.NET.
TnxYou can change the location by using Datasource property(or its equivalent) in vb.net
I have created my reports using MDB file. Now I have changed my DB to SQL Server.
I can go and change my Datasource loaction on my machine and the reports work fine. But I need to distribute these reports to all my clients.
And all have different server names.
I am using CRX and VB.NET.
TnxYou can change the location by using Datasource property(or its equivalent) in vb.net
Sunday, March 11, 2012
Change CommandText
I have a report created on the reporting services and i
need to change the query that the report uses to retrieve
data dynamically based on a criteria page.
All I need is to change part of the RDL of the report
that includes the command text but i can't seem to find
how to do that. Can anyone please HELP!!!.
Thanks...
Omniams-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Omnia" <anonymous@.discussions.microsoft.com> wrote in message
news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>I have a report created on the reporting services and i
> need to change the query that the report uses to retrieve
> data dynamically based on a criteria page.
> All I need is to change part of the RDL of the report
> that includes the command text but i can't seem to find
> how to do that. Can anyone please HELP!!!.
>
> Thanks...
> Omnia|||Thanks very much for the link...
But I still have the following issues:
- Primarily the walkthrough does not work as it insists
on generating errors being unable to parse the query and
I can't manage to make it work...
- The other issue is that this will provide either
an 'ALL' or single selection but not multiple selection
which is what I primarily wanted to achieve, nevertheless
I do appreciate if you can help me further with the
syntax to make the walkthrough work...
Thanks again !!!
Omnia
>--Original Message--
>ms-
help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
_data_v1_41ir.htm
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and i
>> need to change the query that the report uses to
retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>
>.
>|||Could you post your query and exact error message?
Multiple selection feature is in our wishlist for future releases.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Omnia" <anonymous@.discussions.microsoft.com> wrote in message
news:80f101c495a4$c5efd1a0$a501280a@.phx.gbl...
> Thanks very much for the link...
> But I still have the following issues:
> - Primarily the walkthrough does not work as it insists
> on generating errors being unable to parse the query and
> I can't manage to make it work...
> - The other issue is that this will provide either
> an 'ALL' or single selection but not multiple selection
> which is what I primarily wanted to achieve, nevertheless
> I do appreciate if you can help me further with the
> syntax to make the walkthrough work...
>
> Thanks again !!!
> Omnia
>
>
>
>>--Original Message--
>>ms-
> help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
> _data_v1_41ir.htm
>>--
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and i
>> need to change the query that the report uses to
> retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>>
>>.|||Thank You very much for your assistance, the walkthrough
worked fine, and I will try to apply that around here...
>--Original Message--
>Could you post your query and exact error message?
>Multiple selection feature is in our wishlist for future
releases.
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:80f101c495a4$c5efd1a0$a501280a@.phx.gbl...
>> Thanks very much for the link...
>> But I still have the following issues:
>> - Primarily the walkthrough does not work as it insists
>> on generating errors being unable to parse the query
and
>> I can't manage to make it work...
>> - The other issue is that this will provide either
>> an 'ALL' or single selection but not multiple selection
>> which is what I primarily wanted to achieve,
nevertheless
>> I do appreciate if you can help me further with the
>> syntax to make the walkthrough work...
>>
>> Thanks again !!!
>> Omnia
>>
>>
>>
>>--Original Message--
>>ms-
help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
>> _data_v1_41ir.htm
>>--
>>This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and
i
>> need to change the query that the report uses to
>> retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to
find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>>
>>.
>
>.
>|||What is ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
Is that a URL ? Or is it
http://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
I can access neither.
Please help !|||ms-help: link should work if you have books online installed.
MSDN page is here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_data_v1_41ir.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mangaraju venuturupalli" <mangaraju@.yahoo.com> wrote in message
news:784bfe81.0409141309.18bae5e6@.posting.google.com...
> What is
> ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
> Is that a URL ? Or is it
> http://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
> I can access neither.
> Please help !
need to change the query that the report uses to retrieve
data dynamically based on a criteria page.
All I need is to change part of the RDL of the report
that includes the command text but i can't seem to find
how to do that. Can anyone please HELP!!!.
Thanks...
Omniams-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Omnia" <anonymous@.discussions.microsoft.com> wrote in message
news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>I have a report created on the reporting services and i
> need to change the query that the report uses to retrieve
> data dynamically based on a criteria page.
> All I need is to change part of the RDL of the report
> that includes the command text but i can't seem to find
> how to do that. Can anyone please HELP!!!.
>
> Thanks...
> Omnia|||Thanks very much for the link...
But I still have the following issues:
- Primarily the walkthrough does not work as it insists
on generating errors being unable to parse the query and
I can't manage to make it work...
- The other issue is that this will provide either
an 'ALL' or single selection but not multiple selection
which is what I primarily wanted to achieve, nevertheless
I do appreciate if you can help me further with the
syntax to make the walkthrough work...
Thanks again !!!
Omnia
>--Original Message--
>ms-
help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
_data_v1_41ir.htm
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and i
>> need to change the query that the report uses to
retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>
>.
>|||Could you post your query and exact error message?
Multiple selection feature is in our wishlist for future releases.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Omnia" <anonymous@.discussions.microsoft.com> wrote in message
news:80f101c495a4$c5efd1a0$a501280a@.phx.gbl...
> Thanks very much for the link...
> But I still have the following issues:
> - Primarily the walkthrough does not work as it insists
> on generating errors being unable to parse the query and
> I can't manage to make it work...
> - The other issue is that this will provide either
> an 'ALL' or single selection but not multiple selection
> which is what I primarily wanted to achieve, nevertheless
> I do appreciate if you can help me further with the
> syntax to make the walkthrough work...
>
> Thanks again !!!
> Omnia
>
>
>
>>--Original Message--
>>ms-
> help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
> _data_v1_41ir.htm
>>--
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and i
>> need to change the query that the report uses to
> retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>>
>>.|||Thank You very much for your assistance, the walkthrough
worked fine, and I will try to apply that around here...
>--Original Message--
>Could you post your query and exact error message?
>Multiple selection feature is in our wishlist for future
releases.
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:80f101c495a4$c5efd1a0$a501280a@.phx.gbl...
>> Thanks very much for the link...
>> But I still have the following issues:
>> - Primarily the walkthrough does not work as it insists
>> on generating errors being unable to parse the query
and
>> I can't manage to make it work...
>> - The other issue is that this will provide either
>> an 'ALL' or single selection but not multiple selection
>> which is what I primarily wanted to achieve,
nevertheless
>> I do appreciate if you can help me further with the
>> syntax to make the walkthrough work...
>>
>> Thanks again !!!
>> Omnia
>>
>>
>>
>>--Original Message--
>>ms-
help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure
>> _data_v1_41ir.htm
>>--
>>This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>>"Omnia" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:725a01c494ba$37e1cf70$a601280a@.phx.gbl...
>>I have a report created on the reporting services and
i
>> need to change the query that the report uses to
>> retrieve
>> data dynamically based on a criteria page.
>> All I need is to change part of the RDL of the report
>> that includes the command text but i can't seem to
find
>> how to do that. Can anyone please HELP!!!.
>>
>> Thanks...
>> Omnia
>>
>>.
>
>.
>|||What is ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
Is that a URL ? Or is it
http://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
I can access neither.
Please help !|||ms-help: link should work if you have books online installed.
MSDN page is here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_data_v1_41ir.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mangaraju venuturupalli" <mangaraju@.yahoo.com> wrote in message
news:784bfe81.0409141309.18bae5e6@.posting.google.com...
> What is
> ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
> Is that a URL ? Or is it
> http://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_data_v1_41ir.htm
> I can access neither.
> Please help !
change collation name after creating database ?
can the collation name of a database be changed after it has been created ?
Yes, you can change the collation afterward but this will not change the
collation of columns in existing tables. See ALTER DATABASE in the Books
Online for other information
Hope this helps.
Dan Guzman
SQL Server MVP
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uFBjV$a2EHA.2608@.TK2MSFTNGP10.phx.gbl...
> can the collation name of a database be changed after it has been created
> ?
>
Yes, you can change the collation afterward but this will not change the
collation of columns in existing tables. See ALTER DATABASE in the Books
Online for other information
Hope this helps.
Dan Guzman
SQL Server MVP
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uFBjV$a2EHA.2608@.TK2MSFTNGP10.phx.gbl...
> can the collation name of a database be changed after it has been created
> ?
>
change collation name after creating database ?
can the collation name of a database be changed after it has been created ?Yes, you can change the collation afterward but this will not change the
collation of columns in existing tables. See ALTER DATABASE in the Books
Online for other information
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uFBjV$a2EHA.2608@.TK2MSFTNGP10.phx.gbl...
> can the collation name of a database be changed after it has been created
> ?
>
collation of columns in existing tables. See ALTER DATABASE in the Books
Online for other information
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uFBjV$a2EHA.2608@.TK2MSFTNGP10.phx.gbl...
> can the collation name of a database be changed after it has been created
> ?
>
Thursday, March 8, 2012
Change Clustered Index to Non-Clustered Index
When I create a table in Enterprise Manager, the Primary
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John
Tuesday, February 14, 2012
Catalog Population Fails on Cluster
I have an installation that move their database to a cluster server.
SQL2000 has SP3 applied.
We created a new FTS against a single table, single row.
The table has 500,000 rows of data.
When I select "Start Full Population" the task completes immediately and the
count is one.
The edited text from the Application Log follows:
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3019
Date: 12/14/2004
Time: 4:10:34 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl on project <SQLServer SQL0000500005> has started.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3035
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
One or more warnings or errors for Gatherer project <SQLServer
SQL0000500005> were logged to file <S:\Program Files\Microsoft SQL
Server\MSSQL\FTData\SQLServer\GatherLogs\SQL000050 0005.1.gthr>. If you are
interested in these messages, please, look at the file using the gatherer
log query object (gthrlog.vbs, log viewer web page).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3036
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl seed <MSSQL75://SQLServer/318258d2> in project <SQLServer
SQL0000500005> cannot be accessed. Error: 8007007f - The specified procedure
could not be found. .
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3024
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl for project <SQLServer SQL0000500005> could not be started,
because no crawl seeds could be accessed. Fix the errors and try the crawl
again.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3018
Date: 12/14/2004
Time: 4:10:36 PM
User: N/A
Computer: NT-TWDB2
Description:
The end of crawl for project <SQLServer SQL0000500005> has been detected.
The Gatherer successfully processed 0 documents totaling 0K. It failed to
filter 1 documents. 0 URLs could not be reached or were denied access.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
I followed the recommendations of
http://support.microsoft.com/default...;en-us;817301,
PRB: Microsoft Search Full-Text Catalog Population Fails When You Upgrade
SQL Server 2000 Failover Cluster to SQL Server 2000 Service Pack 3
I registered every dll in the Bin folder.
This did not help.
Where do I go from here?
Binder,
First of all, thank you for providing the "Microsoft Search" source events
from your server's Application event log as this is very helpful info in
troubleshooting this SQL FTS issue in a clustered environment!
The key error here is "Error: 8007007f - The specified procedure could not
be found." and the two following KB articles may be appropriate to this
problem, but the error reference in KB article Q304282 - "80070002 - The
system cannot find the file specified" is close, but not exactly the same
error (8007007f ). Reviewing past postings in this newsgroup, I found one
with your error and a past poster suggested this can be resolved via
re-running the SearchStp.exe program from SP3 file. Another suggestion was
to change the (fulltext) dependency to the SQL Agent - in a clean install -
this stops all errors from occurring. Below are the KB articles:
Q304282 PRB: Full Text Resource Fails to Come Online on a Cluster with
"Event ID 1069 :
SQL Cluster Resource 'Full Text' failed" Error Message
http://support.microsoft.com/default...304282&sd=tech
812666 How to recover a failed full-text search resource on a clustered
instance of SQL Server 2000
http://support.microsoft.com/default.aspx?kbid=812666
Hope this helps!
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:uk6Hp734EHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have an installation that move their database to a cluster server.
> SQL2000 has SP3 applied.
> We created a new FTS against a single table, single row.
> The table has 500,000 rows of data.
> When I select "Start Full Population" the task completes immediately and
the
> count is one.
> The edited text from the Application Log follows:
>
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3019
> Date: 12/14/2004
> Time: 4:10:34 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl on project <SQLServer SQL0000500005> has started.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3035
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> One or more warnings or errors for Gatherer project <SQLServer
> SQL0000500005> were logged to file <S:\Program Files\Microsoft SQL
> Server\MSSQL\FTData\SQLServer\GatherLogs\SQL000050 0005.1.gthr>. If you are
> interested in these messages, please, look at the file using the gatherer
> log query object (gthrlog.vbs, log viewer web page).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3036
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl seed <MSSQL75://SQLServer/318258d2> in project <SQLServer
> SQL0000500005> cannot be accessed. Error: 8007007f - The specified
procedure
> could not be found. .
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3024
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl for project <SQLServer SQL0000500005> could not be started,
> because no crawl seeds could be accessed. Fix the errors and try the crawl
> again.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3018
> Date: 12/14/2004
> Time: 4:10:36 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The end of crawl for project <SQLServer SQL0000500005> has been detected.
> The Gatherer successfully processed 0 documents totaling 0K. It failed to
> filter 1 documents. 0 URLs could not be reached or were denied access.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> I followed the recommendations of
> http://support.microsoft.com/default...;en-us;817301,
> PRB: Microsoft Search Full-Text Catalog Population Fails When You Upgrade
> SQL Server 2000 Failover Cluster to SQL Server 2000 Service Pack 3
> I registered every dll in the Bin folder.
> This did not help.
> Where do I go from here?
>
>
|||John,
If the original database was backed up from another server then restored to
this cluster machine, is the FTData directory
copied from the original machine also? Does a restore affect the FTData
directory?
I believe they installed SQL to the cluster with FTS, then did a restore
from the other machine's backup.
Wouldn't the FTData folder be created when FTS was installed with the
initial SQL setup?
If this is the case, would the issues you reference concerning the FTData
folder and its files be a possible source of the problem?
Rg
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ehECYk44EHA.1976@.TK2MSFTNGP09.phx.gbl...
> Binder,
> First of all, thank you for providing the "Microsoft Search" source events
> from your server's Application event log as this is very helpful info in
> troubleshooting this SQL FTS issue in a clustered environment!
> The key error here is "Error: 8007007f - The specified procedure could not
> be found." and the two following KB articles may be appropriate to this
> problem, but the error reference in KB article Q304282 - "80070002 - The
> system cannot find the file specified" is close, but not exactly the same
> error (8007007f ). Reviewing past postings in this newsgroup, I found one
> with your error and a past poster suggested this can be resolved via
> re-running the SearchStp.exe program from SP3 file. Another suggestion was
> to change the (fulltext) dependency to the SQL Agent - in a clean
install -[vbcol=seagreen]
> this stops all errors from occurring. Below are the KB articles:
> Q304282 PRB: Full Text Resource Fails to Come Online on a Cluster with
> "Event ID 1069 :
> SQL Cluster Resource 'Full Text' failed" Error Message
> http://support.microsoft.com/default...304282&sd=tech
> 812666 How to recover a failed full-text search resource on a clustered
> instance of SQL Server 2000
> http://support.microsoft.com/default.aspx?kbid=812666
> Hope this helps!
> John
>
>
> "Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
> news:uk6Hp734EHA.3908@.TK2MSFTNGP12.phx.gbl...
> the
are[vbcol=seagreen]
gatherer[vbcol=seagreen]
> procedure
crawl[vbcol=seagreen]
detected.[vbcol=seagreen]
to[vbcol=seagreen]
Upgrade
>
|||Binder,
No, the FTData directory (and files under it) are not backed up with a
database backup for SQL Server 2000 and SQL Server 7.0, but this will be a
new feature in SQL Server 2005 (Yukon). However, the FTData directory is
installed by default by SQL 2000 Enterprise Edition, but not necessarily at
the same location as the original database, i.e.., different installation
paths.
If a restore was done to the same database, then an internal system table
(sysfulltextcatalogs) may not have the correct path (a value of null is
normal), as if the source server's location of the FT Catalog (may be
different from the FTData directory) is different from the destination
server's location of the FT Catalog then this may be the cause of the error.
Note, for clustered environments, the FT Catalog can only exist on the
shared drive and if the source server's drive letter for the FT Catalog is
different, then this can be correct in the sysfulltextcatalogs table. If
this is the problem, when you create a new FT Catalog in this database it
will fail with an error. Could you try this?
Thanks,
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:uuMVoF54EHA.2624@.TK2MSFTNGP11.phx.gbl...
> John,
> If the original database was backed up from another server then restored
to[vbcol=seagreen]
> this cluster machine, is the FTData directory
> copied from the original machine also? Does a restore affect the FTData
> directory?
> I believe they installed SQL to the cluster with FTS, then did a restore
> from the other machine's backup.
> Wouldn't the FTData folder be created when FTS was installed with the
> initial SQL setup?
> If this is the case, would the issues you reference concerning the FTData
> folder and its files be a possible source of the problem?
>
> Rg
>
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ehECYk44EHA.1976@.TK2MSFTNGP09.phx.gbl...
events[vbcol=seagreen]
not[vbcol=seagreen]
same[vbcol=seagreen]
one[vbcol=seagreen]
was[vbcol=seagreen]
> install -
and
> are
> gatherer
> crawl
> detected.
> to
> Upgrade
>
SQL2000 has SP3 applied.
We created a new FTS against a single table, single row.
The table has 500,000 rows of data.
When I select "Start Full Population" the task completes immediately and the
count is one.
The edited text from the Application Log follows:
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3019
Date: 12/14/2004
Time: 4:10:34 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl on project <SQLServer SQL0000500005> has started.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3035
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
One or more warnings or errors for Gatherer project <SQLServer
SQL0000500005> were logged to file <S:\Program Files\Microsoft SQL
Server\MSSQL\FTData\SQLServer\GatherLogs\SQL000050 0005.1.gthr>. If you are
interested in these messages, please, look at the file using the gatherer
log query object (gthrlog.vbs, log viewer web page).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3036
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl seed <MSSQL75://SQLServer/318258d2> in project <SQLServer
SQL0000500005> cannot be accessed. Error: 8007007f - The specified procedure
could not be found. .
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3024
Date: 12/14/2004
Time: 4:10:35 PM
User: N/A
Computer: NT-TWDB2
Description:
The crawl for project <SQLServer SQL0000500005> could not be started,
because no crawl seeds could be accessed. Fix the errors and try the crawl
again.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Event Type: Information
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3018
Date: 12/14/2004
Time: 4:10:36 PM
User: N/A
Computer: NT-TWDB2
Description:
The end of crawl for project <SQLServer SQL0000500005> has been detected.
The Gatherer successfully processed 0 documents totaling 0K. It failed to
filter 1 documents. 0 URLs could not be reached or were denied access.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
I followed the recommendations of
http://support.microsoft.com/default...;en-us;817301,
PRB: Microsoft Search Full-Text Catalog Population Fails When You Upgrade
SQL Server 2000 Failover Cluster to SQL Server 2000 Service Pack 3
I registered every dll in the Bin folder.
This did not help.
Where do I go from here?
Binder,
First of all, thank you for providing the "Microsoft Search" source events
from your server's Application event log as this is very helpful info in
troubleshooting this SQL FTS issue in a clustered environment!
The key error here is "Error: 8007007f - The specified procedure could not
be found." and the two following KB articles may be appropriate to this
problem, but the error reference in KB article Q304282 - "80070002 - The
system cannot find the file specified" is close, but not exactly the same
error (8007007f ). Reviewing past postings in this newsgroup, I found one
with your error and a past poster suggested this can be resolved via
re-running the SearchStp.exe program from SP3 file. Another suggestion was
to change the (fulltext) dependency to the SQL Agent - in a clean install -
this stops all errors from occurring. Below are the KB articles:
Q304282 PRB: Full Text Resource Fails to Come Online on a Cluster with
"Event ID 1069 :
SQL Cluster Resource 'Full Text' failed" Error Message
http://support.microsoft.com/default...304282&sd=tech
812666 How to recover a failed full-text search resource on a clustered
instance of SQL Server 2000
http://support.microsoft.com/default.aspx?kbid=812666
Hope this helps!
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:uk6Hp734EHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have an installation that move their database to a cluster server.
> SQL2000 has SP3 applied.
> We created a new FTS against a single table, single row.
> The table has 500,000 rows of data.
> When I select "Start Full Population" the task completes immediately and
the
> count is one.
> The edited text from the Application Log follows:
>
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3019
> Date: 12/14/2004
> Time: 4:10:34 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl on project <SQLServer SQL0000500005> has started.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3035
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> One or more warnings or errors for Gatherer project <SQLServer
> SQL0000500005> were logged to file <S:\Program Files\Microsoft SQL
> Server\MSSQL\FTData\SQLServer\GatherLogs\SQL000050 0005.1.gthr>. If you are
> interested in these messages, please, look at the file using the gatherer
> log query object (gthrlog.vbs, log viewer web page).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3036
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl seed <MSSQL75://SQLServer/318258d2> in project <SQLServer
> SQL0000500005> cannot be accessed. Error: 8007007f - The specified
procedure
> could not be found. .
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3024
> Date: 12/14/2004
> Time: 4:10:35 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The crawl for project <SQLServer SQL0000500005> could not be started,
> because no crawl seeds could be accessed. Fix the errors and try the crawl
> again.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Event Type: Information
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3018
> Date: 12/14/2004
> Time: 4:10:36 PM
> User: N/A
> Computer: NT-TWDB2
> Description:
> The end of crawl for project <SQLServer SQL0000500005> has been detected.
> The Gatherer successfully processed 0 documents totaling 0K. It failed to
> filter 1 documents. 0 URLs could not be reached or were denied access.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> I followed the recommendations of
> http://support.microsoft.com/default...;en-us;817301,
> PRB: Microsoft Search Full-Text Catalog Population Fails When You Upgrade
> SQL Server 2000 Failover Cluster to SQL Server 2000 Service Pack 3
> I registered every dll in the Bin folder.
> This did not help.
> Where do I go from here?
>
>
|||John,
If the original database was backed up from another server then restored to
this cluster machine, is the FTData directory
copied from the original machine also? Does a restore affect the FTData
directory?
I believe they installed SQL to the cluster with FTS, then did a restore
from the other machine's backup.
Wouldn't the FTData folder be created when FTS was installed with the
initial SQL setup?
If this is the case, would the issues you reference concerning the FTData
folder and its files be a possible source of the problem?
Rg
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ehECYk44EHA.1976@.TK2MSFTNGP09.phx.gbl...
> Binder,
> First of all, thank you for providing the "Microsoft Search" source events
> from your server's Application event log as this is very helpful info in
> troubleshooting this SQL FTS issue in a clustered environment!
> The key error here is "Error: 8007007f - The specified procedure could not
> be found." and the two following KB articles may be appropriate to this
> problem, but the error reference in KB article Q304282 - "80070002 - The
> system cannot find the file specified" is close, but not exactly the same
> error (8007007f ). Reviewing past postings in this newsgroup, I found one
> with your error and a past poster suggested this can be resolved via
> re-running the SearchStp.exe program from SP3 file. Another suggestion was
> to change the (fulltext) dependency to the SQL Agent - in a clean
install -[vbcol=seagreen]
> this stops all errors from occurring. Below are the KB articles:
> Q304282 PRB: Full Text Resource Fails to Come Online on a Cluster with
> "Event ID 1069 :
> SQL Cluster Resource 'Full Text' failed" Error Message
> http://support.microsoft.com/default...304282&sd=tech
> 812666 How to recover a failed full-text search resource on a clustered
> instance of SQL Server 2000
> http://support.microsoft.com/default.aspx?kbid=812666
> Hope this helps!
> John
>
>
> "Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
> news:uk6Hp734EHA.3908@.TK2MSFTNGP12.phx.gbl...
> the
are[vbcol=seagreen]
gatherer[vbcol=seagreen]
> procedure
crawl[vbcol=seagreen]
detected.[vbcol=seagreen]
to[vbcol=seagreen]
Upgrade
>
|||Binder,
No, the FTData directory (and files under it) are not backed up with a
database backup for SQL Server 2000 and SQL Server 7.0, but this will be a
new feature in SQL Server 2005 (Yukon). However, the FTData directory is
installed by default by SQL 2000 Enterprise Edition, but not necessarily at
the same location as the original database, i.e.., different installation
paths.
If a restore was done to the same database, then an internal system table
(sysfulltextcatalogs) may not have the correct path (a value of null is
normal), as if the source server's location of the FT Catalog (may be
different from the FTData directory) is different from the destination
server's location of the FT Catalog then this may be the cause of the error.
Note, for clustered environments, the FT Catalog can only exist on the
shared drive and if the source server's drive letter for the FT Catalog is
different, then this can be correct in the sysfulltextcatalogs table. If
this is the problem, when you create a new FT Catalog in this database it
will fail with an error. Could you try this?
Thanks,
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:uuMVoF54EHA.2624@.TK2MSFTNGP11.phx.gbl...
> John,
> If the original database was backed up from another server then restored
to[vbcol=seagreen]
> this cluster machine, is the FTData directory
> copied from the original machine also? Does a restore affect the FTData
> directory?
> I believe they installed SQL to the cluster with FTS, then did a restore
> from the other machine's backup.
> Wouldn't the FTData folder be created when FTS was installed with the
> initial SQL setup?
> If this is the case, would the issues you reference concerning the FTData
> folder and its files be a possible source of the problem?
>
> Rg
>
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ehECYk44EHA.1976@.TK2MSFTNGP09.phx.gbl...
events[vbcol=seagreen]
not[vbcol=seagreen]
same[vbcol=seagreen]
one[vbcol=seagreen]
was[vbcol=seagreen]
> install -
and
> are
> gatherer
> crawl
> detected.
> to
> Upgrade
>
catalog is empty after populating
Hello,
I've got a problem with the Full Text Search of MS SQL=20
Server 2000 (SP3).
I've created a new Full Text catalog (FTC) on a database,=20
including one table and one textfield for searching. Up to=20
this everything goes fine. Population runs without=20
creating an error. But after population the catalog-size=20
is only 1 MB and the catalog doesn't contain any items. A=20
query with CONTAINS is returning 0 results.=20
I've tried the total procedure in Enterprise Manager and=20
in Query Analyzer. I've tried it with the example=20
databases NORTHWIND and PUBS, but everytime the same=20
result.
I've already searched a lot of articles and newsgroup-
entries without finding a solution for my problem.=20
Hopefully one of You may help.=20
Some facts that may help to find a solution:
-SQL Server 2000 SP3 (not upgraded from 7.0)
-Windows 2003 Server as Operating System
-MS Search Service runs correctly
Thank You in advance for every help
Andr=E9
what are the error messages you are getting in the application log in event
viewer from MSSearch and MSSCi?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andr Bretschneider" <andre.bretschneider@.freenet.de> wrote in message
news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
Hello,
I've got a problem with the Full Text Search of MS SQL
Server 2000 (SP3).
I've created a new Full Text catalog (FTC) on a database,
including one table and one textfield for searching. Up to
this everything goes fine. Population runs without
creating an error. But after population the catalog-size
is only 1 MB and the catalog doesn't contain any items. A
query with CONTAINS is returning 0 results.
I've tried the total procedure in Enterprise Manager and
in Query Analyzer. I've tried it with the example
databases NORTHWIND and PUBS, but everytime the same
result.
I've already searched a lot of articles and newsgroup-
entries without finding a solution for my problem.
Hopefully one of You may help.
Some facts that may help to find a solution:
-SQL Server 2000 SP3 (not upgraded from 7.0)
-Windows 2003 Server as Operating System
-MS Search Service runs correctly
Thank You in advance for every help
Andr
|||Hi Hillary,
thanks for your fast answer, But I'm sorry. I'm obviously=20
a greenhorn in SQLServer-Administration. Where can I find=20
this application log?
Andr=E9
>--Original Message--
>what are the error messages you are getting in the=20
application log in event
>viewer from MSSearch and MSSCi?
>--=20
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Andr=E9 Bretschneider" <andre.bretschneider@.freenet.de>=20
wrote in message
>news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
>Hello,
>I've got a problem with the Full Text Search of MS SQL
>Server 2000 (SP3).
>I've created a new Full Text catalog (FTC) on a database,
>including one table and one textfield for searching. Up to
>this everything goes fine. Population runs without
>creating an error. But after population the catalog-size
>is only 1 MB and the catalog doesn't contain any items. A
>query with CONTAINS is returning 0 results.
>I've tried the total procedure in Enterprise Manager and
>in Query Analyzer. I've tried it with the example
>databases NORTHWIND and PUBS, but everytime the same
>result.
>I've already searched a lot of articles and newsgroup-
>entries without finding a solution for my problem.
>Hopefully one of You may help.
>Some facts that may help to find a solution:
>-SQL Server 2000 SP3 (not upgraded from 7.0)
>-Windows 2003 Server as Operating System
>-MS Search Service runs correctly
>Thank You in advance for every help
>Andr=E9
>
>.
>
|||Andre,
Hilary's referring to the Window's Application Event log on the server where
SQL Server is installed as the "Microsoft Search" service cannot write it's
errors to SQL Server's errorlog files as SQL Server has a lock on these
files. Specifically, you should click on Start -> Programs -> Administrator
Tools -> Event Viewer and review the Application event log. You can click on
View and Filter and then select "Microsoft Search" as the Event Source to
see only these events. Below is an example error, you may find or you may
find a different error.
Microsoft Search
Warning Gatherer
3036
JTKNT0
Unable to access crawl seed <MSSQL://LOCALHOST/7.1333579789> in project
<SQLServer SQL0000700006>.
Error: 8007052e - Logon failure: unknown user name or bad password. .
Post any related errors in your server's Application event log.
Regards,
John
"Andr Bretschneider" <andre.bretschneider@.freenet.de> wrote in message
news:2b0901c47e0b$f559ce60$a601280a@.phx.gbl...
Hi Hillary,
thanks for your fast answer, But I'm sorry. I'm obviously
a greenhorn in SQLServer-Administration. Where can I find
this application log?
Andr
>--Original Message--
>what are the error messages you are getting in the
application log in event
>viewer from MSSearch and MSSCi?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Andr Bretschneider" <andre.bretschneider@.freenet.de>
wrote in message
>news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
>Hello,
>I've got a problem with the Full Text Search of MS SQL
>Server 2000 (SP3).
>I've created a new Full Text catalog (FTC) on a database,
>including one table and one textfield for searching. Up to
>this everything goes fine. Population runs without
>creating an error. But after population the catalog-size
>is only 1 MB and the catalog doesn't contain any items. A
>query with CONTAINS is returning 0 results.
>I've tried the total procedure in Enterprise Manager and
>in Query Analyzer. I've tried it with the example
>databases NORTHWIND and PUBS, but everytime the same
>result.
>I've already searched a lot of articles and newsgroup-
>entries without finding a solution for my problem.
>Hopefully one of You may help.
>Some facts that may help to find a solution:
>-SQL Server 2000 SP3 (not upgraded from 7.0)
>-Windows 2003 Server as Operating System
>-MS Search Service runs correctly
>Thank You in advance for every help
>Andr
>
>.
>
|||Hi John,=20
thanks for your advice, now I've found them.
I got five entries (3 informational, 2 errors) in the=20
application log. Here the 2 errors (I try to translate=20
them correctly from German):
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3036
Description:=20
Unable to access crawl seed <MSSQL75://SQLServer/6fe99f9f>=20
in project <SQLServer SQL0002400005>.
Error: 800700e9 - No process at the other end of the pipe.
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3024
Description:=20
Unable to start crawl for project <SQLServer=20
SQL0002400005> because there is no access to the crawl=20
seeds. Patch the error and then start again the crawl.
Obviously the second is an aftereffect of the first. So=20
I'll try to find the reason for the first error. Any idea?
Andr=E9
>--Original Message--
>Andre,
>Hilary's referring to the Window's Application Event log=20
on the server where
>SQL Server is installed as the "Microsoft Search" service=20
cannot write it's
>errors to SQL Server's errorlog files as SQL Server has a=20
lock on these
>files. Specifically, you should click on Start ->=20
Programs -> Administrator
>Tools -> Event Viewer and review the Application event=20
log. You can click on
>View and Filter and then select "Microsoft Search" as the=20
Event Source to
>see only these events. Below is an example error, you may=20
find or you may
>find a different error.
>Microsoft Search
>Warning Gatherer
>3036
>JTKNT0
>Unable to access crawl seed=20
<MSSQL://LOCALHOST/7.1333579789> in project
><SQLServer SQL0000700006>.
>Error: 8007052e - Logon failure: unknown user name or bad=20
password. .
>Post any related errors in your server's Application=20
event log.
>Regards,
>John
|||You're welcome, Andre,
The key error is Error: 800700e9 - No process at the other end of the pipe.
By chance did you or someone else remove the BUILTIN\Administrator login? If
so, you will need to either add it back with the default settings (master db
& sysadmin rights) as the MSSearch service requires that either the
BUILTIN\Administrators login
be present or that [NT Authority\System] localsystem login have the below
rights and this is why you are seeing ("Login failed for NT
AUTHORITY\SYSTEM" ) after removing the BUILTIN\Administrators login:
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
See also KB article Q263712 "INF: How To Prevent Windows NT Administrators
From Administering a Clustered SQL Server"
at http://support.microsoft.com/default...;EN-US;q263712 for more
info.
Additionally, and depending upon if you're FT Indexing documents imported
into SQL Server, you might also want to review KB article 308771 "PRB: A
Full-Text Search May Not Return Any Hits If It Fails to Index a File" at:
http://support.microsoft.com/default...&Product=sql2k
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:2c6901c47e2a$8ed69410$a501280a@.phx.gbl...
Hi John,
thanks for your advice, now I've found them.
I got five entries (3 informational, 2 errors) in the
application log. Here the 2 errors (I try to translate
them correctly from German):
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3036
Description:
Unable to access crawl seed <MSSQL75://SQLServer/6fe99f9f>
in project <SQLServer SQL0002400005>.
Error: 800700e9 - No process at the other end of the pipe.
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3024
Description:
Unable to start crawl for project <SQLServer
SQL0002400005> because there is no access to the crawl
seeds. Patch the error and then start again the crawl.
Obviously the second is an aftereffect of the first. So
I'll try to find the reason for the first error. Any idea?
Andr
>--Original Message--
>Andre,
>Hilary's referring to the Window's Application Event log
on the server where
>SQL Server is installed as the "Microsoft Search" service
cannot write it's
>errors to SQL Server's errorlog files as SQL Server has a
lock on these
>files. Specifically, you should click on Start ->
Programs -> Administrator
>Tools -> Event Viewer and review the Application event
log. You can click on
>View and Filter and then select "Microsoft Search" as the
Event Source to
>see only these events. Below is an example error, you may
find or you may
>find a different error.
>Microsoft Search
>Warning Gatherer
>3036
>JTKNT0
>Unable to access crawl seed
<MSSQL://LOCALHOST/7.1333579789> in project
><SQLServer SQL0000700006>.
>Error: 8007052e - Logon failure: unknown user name or bad
password. .
>Post any related errors in your server's Application
event log.
>Regards,
>John
I've got a problem with the Full Text Search of MS SQL=20
Server 2000 (SP3).
I've created a new Full Text catalog (FTC) on a database,=20
including one table and one textfield for searching. Up to=20
this everything goes fine. Population runs without=20
creating an error. But after population the catalog-size=20
is only 1 MB and the catalog doesn't contain any items. A=20
query with CONTAINS is returning 0 results.=20
I've tried the total procedure in Enterprise Manager and=20
in Query Analyzer. I've tried it with the example=20
databases NORTHWIND and PUBS, but everytime the same=20
result.
I've already searched a lot of articles and newsgroup-
entries without finding a solution for my problem.=20
Hopefully one of You may help.=20
Some facts that may help to find a solution:
-SQL Server 2000 SP3 (not upgraded from 7.0)
-Windows 2003 Server as Operating System
-MS Search Service runs correctly
Thank You in advance for every help
Andr=E9
what are the error messages you are getting in the application log in event
viewer from MSSearch and MSSCi?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andr Bretschneider" <andre.bretschneider@.freenet.de> wrote in message
news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
Hello,
I've got a problem with the Full Text Search of MS SQL
Server 2000 (SP3).
I've created a new Full Text catalog (FTC) on a database,
including one table and one textfield for searching. Up to
this everything goes fine. Population runs without
creating an error. But after population the catalog-size
is only 1 MB and the catalog doesn't contain any items. A
query with CONTAINS is returning 0 results.
I've tried the total procedure in Enterprise Manager and
in Query Analyzer. I've tried it with the example
databases NORTHWIND and PUBS, but everytime the same
result.
I've already searched a lot of articles and newsgroup-
entries without finding a solution for my problem.
Hopefully one of You may help.
Some facts that may help to find a solution:
-SQL Server 2000 SP3 (not upgraded from 7.0)
-Windows 2003 Server as Operating System
-MS Search Service runs correctly
Thank You in advance for every help
Andr
|||Hi Hillary,
thanks for your fast answer, But I'm sorry. I'm obviously=20
a greenhorn in SQLServer-Administration. Where can I find=20
this application log?
Andr=E9
>--Original Message--
>what are the error messages you are getting in the=20
application log in event
>viewer from MSSearch and MSSCi?
>--=20
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Andr=E9 Bretschneider" <andre.bretschneider@.freenet.de>=20
wrote in message
>news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
>Hello,
>I've got a problem with the Full Text Search of MS SQL
>Server 2000 (SP3).
>I've created a new Full Text catalog (FTC) on a database,
>including one table and one textfield for searching. Up to
>this everything goes fine. Population runs without
>creating an error. But after population the catalog-size
>is only 1 MB and the catalog doesn't contain any items. A
>query with CONTAINS is returning 0 results.
>I've tried the total procedure in Enterprise Manager and
>in Query Analyzer. I've tried it with the example
>databases NORTHWIND and PUBS, but everytime the same
>result.
>I've already searched a lot of articles and newsgroup-
>entries without finding a solution for my problem.
>Hopefully one of You may help.
>Some facts that may help to find a solution:
>-SQL Server 2000 SP3 (not upgraded from 7.0)
>-Windows 2003 Server as Operating System
>-MS Search Service runs correctly
>Thank You in advance for every help
>Andr=E9
>
>.
>
|||Andre,
Hilary's referring to the Window's Application Event log on the server where
SQL Server is installed as the "Microsoft Search" service cannot write it's
errors to SQL Server's errorlog files as SQL Server has a lock on these
files. Specifically, you should click on Start -> Programs -> Administrator
Tools -> Event Viewer and review the Application event log. You can click on
View and Filter and then select "Microsoft Search" as the Event Source to
see only these events. Below is an example error, you may find or you may
find a different error.
Microsoft Search
Warning Gatherer
3036
JTKNT0
Unable to access crawl seed <MSSQL://LOCALHOST/7.1333579789> in project
<SQLServer SQL0000700006>.
Error: 8007052e - Logon failure: unknown user name or bad password. .
Post any related errors in your server's Application event log.
Regards,
John
"Andr Bretschneider" <andre.bretschneider@.freenet.de> wrote in message
news:2b0901c47e0b$f559ce60$a601280a@.phx.gbl...
Hi Hillary,
thanks for your fast answer, But I'm sorry. I'm obviously
a greenhorn in SQLServer-Administration. Where can I find
this application log?
Andr
>--Original Message--
>what are the error messages you are getting in the
application log in event
>viewer from MSSearch and MSSCi?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Andr Bretschneider" <andre.bretschneider@.freenet.de>
wrote in message
>news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
>Hello,
>I've got a problem with the Full Text Search of MS SQL
>Server 2000 (SP3).
>I've created a new Full Text catalog (FTC) on a database,
>including one table and one textfield for searching. Up to
>this everything goes fine. Population runs without
>creating an error. But after population the catalog-size
>is only 1 MB and the catalog doesn't contain any items. A
>query with CONTAINS is returning 0 results.
>I've tried the total procedure in Enterprise Manager and
>in Query Analyzer. I've tried it with the example
>databases NORTHWIND and PUBS, but everytime the same
>result.
>I've already searched a lot of articles and newsgroup-
>entries without finding a solution for my problem.
>Hopefully one of You may help.
>Some facts that may help to find a solution:
>-SQL Server 2000 SP3 (not upgraded from 7.0)
>-Windows 2003 Server as Operating System
>-MS Search Service runs correctly
>Thank You in advance for every help
>Andr
>
>.
>
|||Hi John,=20
thanks for your advice, now I've found them.
I got five entries (3 informational, 2 errors) in the=20
application log. Here the 2 errors (I try to translate=20
them correctly from German):
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3036
Description:=20
Unable to access crawl seed <MSSQL75://SQLServer/6fe99f9f>=20
in project <SQLServer SQL0002400005>.
Error: 800700e9 - No process at the other end of the pipe.
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3024
Description:=20
Unable to start crawl for project <SQLServer=20
SQL0002400005> because there is no access to the crawl=20
seeds. Patch the error and then start again the crawl.
Obviously the second is an aftereffect of the first. So=20
I'll try to find the reason for the first error. Any idea?
Andr=E9
>--Original Message--
>Andre,
>Hilary's referring to the Window's Application Event log=20
on the server where
>SQL Server is installed as the "Microsoft Search" service=20
cannot write it's
>errors to SQL Server's errorlog files as SQL Server has a=20
lock on these
>files. Specifically, you should click on Start ->=20
Programs -> Administrator
>Tools -> Event Viewer and review the Application event=20
log. You can click on
>View and Filter and then select "Microsoft Search" as the=20
Event Source to
>see only these events. Below is an example error, you may=20
find or you may
>find a different error.
>Microsoft Search
>Warning Gatherer
>3036
>JTKNT0
>Unable to access crawl seed=20
<MSSQL://LOCALHOST/7.1333579789> in project
><SQLServer SQL0000700006>.
>Error: 8007052e - Logon failure: unknown user name or bad=20
password. .
>Post any related errors in your server's Application=20
event log.
>Regards,
>John
|||You're welcome, Andre,
The key error is Error: 800700e9 - No process at the other end of the pipe.
By chance did you or someone else remove the BUILTIN\Administrator login? If
so, you will need to either add it back with the default settings (master db
& sysadmin rights) as the MSSearch service requires that either the
BUILTIN\Administrators login
be present or that [NT Authority\System] localsystem login have the below
rights and this is why you are seeing ("Login failed for NT
AUTHORITY\SYSTEM" ) after removing the BUILTIN\Administrators login:
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
See also KB article Q263712 "INF: How To Prevent Windows NT Administrators
From Administering a Clustered SQL Server"
at http://support.microsoft.com/default...;EN-US;q263712 for more
info.
Additionally, and depending upon if you're FT Indexing documents imported
into SQL Server, you might also want to review KB article 308771 "PRB: A
Full-Text Search May Not Return Any Hits If It Fails to Index a File" at:
http://support.microsoft.com/default...&Product=sql2k
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:2c6901c47e2a$8ed69410$a501280a@.phx.gbl...
Hi John,
thanks for your advice, now I've found them.
I got five entries (3 informational, 2 errors) in the
application log. Here the 2 errors (I try to translate
them correctly from German):
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3036
Description:
Unable to access crawl seed <MSSQL75://SQLServer/6fe99f9f>
in project <SQLServer SQL0002400005>.
Error: 800700e9 - No process at the other end of the pipe.
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3024
Description:
Unable to start crawl for project <SQLServer
SQL0002400005> because there is no access to the crawl
seeds. Patch the error and then start again the crawl.
Obviously the second is an aftereffect of the first. So
I'll try to find the reason for the first error. Any idea?
Andr
>--Original Message--
>Andre,
>Hilary's referring to the Window's Application Event log
on the server where
>SQL Server is installed as the "Microsoft Search" service
cannot write it's
>errors to SQL Server's errorlog files as SQL Server has a
lock on these
>files. Specifically, you should click on Start ->
Programs -> Administrator
>Tools -> Event Viewer and review the Application event
log. You can click on
>View and Filter and then select "Microsoft Search" as the
Event Source to
>see only these events. Below is an example error, you may
find or you may
>find a different error.
>Microsoft Search
>Warning Gatherer
>3036
>JTKNT0
>Unable to access crawl seed
<MSSQL://LOCALHOST/7.1333579789> in project
><SQLServer SQL0000700006>.
>Error: 8007052e - Logon failure: unknown user name or bad
password. .
>Post any related errors in your server's Application
event log.
>Regards,
>John
Sunday, February 12, 2012
Casting
Hello,
I created a custom assembly with some business logic and attachted it to a
report.
A class in the assemby has an indexer defined which should return a string.
When I call the indexer from the code block I alway get an "Specified cast is
not valid" error back.
I created a test program in VS to test the classes and the indexer there
everything works fine.
What am I missing?
QSolved it.
I called a method on my class like the following:
wga.Attributes.Add("BG_CODE", Report.Parameters("Conc_BG").Value)
The definition for the Add method is:
public void Add(string databaseColumnName, string value)
But I have also another Add method which is:
public void Add(object key, object value)
I have to implement this method because my class inherits from IDictionary
I was under the impression that when I am adding strings the method with the
strings signature would be taken. But this was not the case. Instead the Add
method with the object signature was taken. Therefore my collection was
filled with objects and the indexer expected strings.
To solve this I had to rewrite it like the following:
wga.Attributes.Add("BG_CODE".ToString,
Report.Parameters("Conc_BG").Value.ToString)
Now the Add method with the strings signature is called.
Long live VS debugging!
Q
"Qbee" wrote:
> Hello,
> I created a custom assembly with some business logic and attachted it to a
> report.
> A class in the assemby has an indexer defined which should return a string.
> When I call the indexer from the code block I alway get an "Specified cast is
> not valid" error back.
> I created a test program in VS to test the classes and the indexer there
> everything works fine.
> What am I missing?
> Q
I created a custom assembly with some business logic and attachted it to a
report.
A class in the assemby has an indexer defined which should return a string.
When I call the indexer from the code block I alway get an "Specified cast is
not valid" error back.
I created a test program in VS to test the classes and the indexer there
everything works fine.
What am I missing?
QSolved it.
I called a method on my class like the following:
wga.Attributes.Add("BG_CODE", Report.Parameters("Conc_BG").Value)
The definition for the Add method is:
public void Add(string databaseColumnName, string value)
But I have also another Add method which is:
public void Add(object key, object value)
I have to implement this method because my class inherits from IDictionary
I was under the impression that when I am adding strings the method with the
strings signature would be taken. But this was not the case. Instead the Add
method with the object signature was taken. Therefore my collection was
filled with objects and the indexer expected strings.
To solve this I had to rewrite it like the following:
wga.Attributes.Add("BG_CODE".ToString,
Report.Parameters("Conc_BG").Value.ToString)
Now the Add method with the strings signature is called.
Long live VS debugging!
Q
"Qbee" wrote:
> Hello,
> I created a custom assembly with some business logic and attachted it to a
> report.
> A class in the assemby has an indexer defined which should return a string.
> When I call the indexer from the code block I alway get an "Specified cast is
> not valid" error back.
> I created a test program in VS to test the classes and the indexer there
> everything works fine.
> What am I missing?
> Q
Cast from string to date
I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.
Now in my SELECT statement, I have this:
SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @.startdate) AND (TLMST.INDT <= @.enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER
Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.
Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information
Do a Conversion task in SSIS, with either chopping the values from the string to create a vlid date like yyyymmdd or use the format function with that.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||When you create your source in your data flow, what is the data type SSIS has set for the column. You can find this out by right clicking on the source and selecting advanced editor and then selecting the input and output properties. In there you can expand the input tree to find the data type for the date column.|||The advanced editor is showing the data type as string, however on the AS400 it is of type OdbcType.Date. But that is on the AS400 side which I am not too familiar with.|||In which case follow Jens advice to split the date passed and build a string that is of the correct format
Subscribe to:
Posts (Atom)