Conversion error when calling stored procedure

Hi all,
I want to execute a stored procedure from a report. The stored procedure
takes nvarchar(50) parameters. When I call the procedure from a report with
string paramerers, I get the error 'Implicit conversion from data type
sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
reporting services parameters when calling Exec to run the stored procedure.
Help! Anyone experience this kind of problem before? Any suggestions are
welcome!hey.
Not sure why it happens as I get those all the time to. I have found that
if i create a make the first dataset something simple, like the query to
populate a parameter drop down list, then add a second data source I can then
call the execute statement for the proc. Boqus I know but it works. Buggy
software is my quess, I think it may have been the service pack as I did not
do this a few months back.
hth
"Bas" wrote:
> Hi all,
> I want to execute a stored procedure from a report. The stored procedure
> takes nvarchar(50) parameters. When I call the procedure from a report with
> string paramerers, I get the error 'Implicit conversion from data type
> sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
> reporting services parameters when calling Exec to run the stored procedure.
> Help! Anyone experience this kind of problem before? Any suggestions are
> welcome!
>

Conversion between data types

Lets say I execute: SELECT hashbytes('MD5','IMTIAZ')

Which returns: 0x60D164C6B64EE81C7E7395C01D838FEE

How do I get a varchar: 60D164C6B64EE81C7E7395C01D838FEE

Not converted.

How do I get the 0x removed from the string ?

Regards

Imtiaz

I have been googling to find a solution to this question.....

I have acome across a few places to use the xp_varbintohexstr undocumented procedure. But in SQL 2005 what is the equivalent and any pointers in this direction will be of great help.

Regards

Imtiaz

|||

Ok...here's the answer...

SELECT substring(upper(master.dbo.fn_varbintohexstr(hashbytes('MD5','SHELLEY'))),3,len(master.dbo.fn_varbintohexstr(hashbytes('MD5','IMTIAZ'))))

|||You have to write your own TSQL/SQLCLR scalar UDF to do the conversion from varbinary to hexadecimal string. Please do not use undocumented stored procedures like xp_varbintohexstr or fn_varbintohexstr. Undocumented objects can be dropped or modified in any release or even service pack of SQL Server. So you should not rely on such interfaces. It is easier to write your own code for these type of problems.

Controls disappear

Is it not possible to execute one package and then design another package? I am executing one package and trying to design another package and just have a grey window in the control tool box that says:

"there are no usable controls in this group. Drag an item onto this text to add it to the toolbar"

Can I only get my controls by dragging when another package is executing? Where do I drag them from?

Thanks,

Kayda

You seem quite new to this and some things are very odd about SSIS.

The controls are dragged from the Toolbox window. Get this by clicking on the "spanner and hammer" icon at the top. Or Crtl+Alt+X or via the menu View->Toolbox.

You have to be on the Control Flow or Data Flow panels. You get different tools for the 2 different contexts.

Also, you need to check you are executing the correct package. In the Solution Explorer, under the SSIS Packages folder right click on your xxxxxx.dtsx file and select "Set as StartUp Object" for the package you want to execute ... or just select "Execute Package" to run the one you want. You have to stop debugging the previous package before you design or run the next one.

Since you have already done one package, I may have misunderstood your questions.

Hope this helps

|||

Hi Kayda,

The Visual Studio Integrated Development Environment (IDE) changes modes when the debugger starts. It disables most editing functionality until it exits debug mode. It also hides controls in the toolboxes. This is by design.

Although the package execution is complete, the IDE remains in debug mode until stopped (Shift-F5 or the VCR-style Stop button will do it).

This is different from DTS and a normal source of confusion for folks making the transition.

Hope this helps,
Andy

|||Perhaps you could open a second session of BIDS; or use DTExec or DTExecUI to execute the packages while still developing other packages

controlling security through stored procedures -- 2005 behaviour

Hi!

I'm trying to control security through sps -- meaning execute permissions are granted on stored procedures, and no users have read/write permissions on tables, etc directly.

Which works fine as long as all objects referenced are in the same db as the procedure.

An issue arises when a stored procedure accesses a table in another database:

Getting a : Msg 229 SELECT permission denied on object 'blah' Even though the procedure is created by sysadmin.

Has this changed since 2000? I'm pretty sure in 2000 it would've worked as the sp would be executed in sp owner's security context.

Moreover, when I try to use EXECUTE AS in the sp as a workaround, I am getting the following, no matter what account I try to impersonate:

Msg 916, Level 14, State 1, Procedure vvv, Line 4
The server principal % is not able to access the database "blah" under the current security context.

any ideas?
Thanks!

Most likely this scenario worked on Windows 2000 with cross-database ownership chaining enabled. Turning on this feature is not recommended, as it may lead to an elevation of privileges (i.e. the DB administrators of the source database may escalate their privileges to become DB administrators on the target DB).

The reason why your stored procedure marked with “execute as” is not working is because the impersonated context is (by default) scoped only to the surrent (source) database, and stripped down from it's server-scoped permissions and privileges. If you wish to use this impersonated context outside the source DB, you need to establish a trust relationship on the target DB.

To solve this problem, you can probably use digital signatures to solve your problem; by signing the stored procedure with a certificate you have a way to ensure that the code has not been tampered with. If at run time the signature matches the code, the certificate can be used in two ways:

* As a secondary identity for the execution context. This means that if there is a user mapped to the signing certificate, the permissions on that user will be used to calculate the permissions on the object.

* When the module (SP) is marked with execute as, the signature will work as an authenticator, that means the signature will be used to vouch for the impersonated context in the stored procedure

Note that for the secondary identity approach, the signature will be added to the current context therefore, if the current context is not a valid one on the server scope (i.e. the caller is an approle), the certificate as secondary identity cannot be used on cross database scenario.

The second approach on the other hand establishes a whole new context on top of the calling context, and it is the signature the one vouching for this new context on the target database.

I am posting a small demo at the end taht I hope will help you.

Thanks a lot for your comments and feedback.

-Raul Garcia
SDE/T
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.

-

/*******************************************************************

*

* This posting is provided "AS IS" with no warranties, and
* confers no rights.

*

* Author: Raulga

* Date: 08/24/2005

* Description:

* This demo shows how to use digital signatures to access
* resources on a different database by using digitaly signed stored
* procedures to control the access rather than using cross database
* ownership chaining.

*

* The first SP will be using the siganture as a secondary identity
* on top of the calling context. This means that only a context with
* a server-presence will succeed on this call (i.e. approles will not
* be able to accsss the resources on the target database as they

* don't have a server presence).

*

* The second approach will be by specifying a context switch
* (EXECUTE AS) on the stored procedure and using the signature as an
* authenticator; this means that the signature can vouch for the
* impersonated context (specifid on the module). This mechanism will
* allow to access the resources regardless of the original calling
* context because a new context (vouched by the signature) is placed
* on top of the orginal one, but requires more managment.

*

* (c) 2005 Microsoft Corporation. All rights reserved.

*

***********************************************************************************************/

CREATE DATABASE db_Source

go

CREATE DATABASE db_Target

go

CREATE LOGIN dbo_db_Source WITH PASSWORD = 'My S0uRc3 D8 p@.55W0rD!'

CREATE LOGIN dbo_db_Target WITH PASSWORD = 'My +@.r637 D8 p@.55W0rD!'

go

-- Change the ownership for the source and the target databases

ALTER AUTHORIZATION ON DATABASE::db_Source to dbo_db_Source

ALTER AUTHORIZATION ON DATABASE::db_Target to dbo_db_Target

go

-- This principal will be the data owner, he can access the data on

-- the target database, and he controls the stored procedures on the

-- source database

CREATE LOGIN data_owner WITH PASSWORD = 'd@.+4 0wn3R'

-- This principal should only have access to the data via the stored

-- procedures

CREATE LOGIN someuser WITH PASSWORD = 's0m3 p@.55w0Rd'

go

use db_Target

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

CREATE TABLE data_owner.MyTable( data nvarchar(100) )

go

INSERT INTO data_owner.MyTable values ( N'My data' )

go

use db_Source

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

-- ALlow someuser to execute any module on the schema called data_owner

GRANT EXECUTE ON SCHEMA::data_owner TO someuser

go

-- Create a stored procedure that uses the default execution context

-- (the caller's context) at runtime

CREATE PROC data_owner.sp_GetMyData01

AS

select * from db_Target.data_owner.MyTable

go

-- Create a stored procedure similar to teh previous one, but this time we will explicitly

-- use the data_owner context via EXECUTE AS

CREATE PROC data_owner.sp_GetMyData02

WITH EXECUTE AS 'data_owner'

AS

select * from db_Target.data_owner.MyTable

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- Will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will fail due to the lack of permissions on the target database

EXEC data_owner.sp_GetMyData01

-- will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Signing the stored procedures

--

-- Create 2 certificates one to sign each SP.

-- Note that I am using passwords to protect the private keys.

-- It is also possible to use a DB master key to protect private the
-- keys, please refer to BOL for more information on the key
-- hierarchy

CREATE CERTIFICATE cert_GetMyData01

ENCRYPTION BY PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData01'

go

CREATE CERTIFICATE cert_GetMyData02

ENCRYPTION BY PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData02'

go

-- Now sign the stored procedures, as the cert's

-- private keys are protected by passwords, we have to use the
-- passwords to sign

ADD SIGNATURE TO data_owner.sp_GetMyData01 BY CERTIFICATE cert_GetMyData01

WITH PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

go

ADD SIGNATURE TO data_owner.sp_GetMyData02 BY CERTIFICATE cert_GetMyData02

WITH PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

go

-- Let's take a quick look to the metadata for the signed modules

SELECT schema_name( c.schema_id ) as schema_name, c.name,

b.name, a.crypt_property as 'module siganture' FROM

sys.crypt_properties a,

sys.certificates b,

sys.objects c

WHERE a.thumbprint = b.thumbprint AND a.class = 1
AND a.major_id = c.object_id

go

-- Depending on your application and environment, sometimes you may
-- not want to leave the private keys on the database, and either
-- destroy the private keys (this way, they can never be used to
-- sign anything else), or back up a copy of the private keys and
-- store them in a safe place. For this demo I will just destoy the
-- private keys as we don't need them anymore

ALTER CERTIFICATE cert_GetMyData01 REMOVE PRIVATE KEY

ALTER CERTIFICATE cert_GetMyData02 REMOVE PRIVATE KEY

go

-- Now, we need to create a backup for the certificate public data.

-- We will need to import it back on teh target database.

BACKUP CERTIFICATE cert_GetMyData01 TO FILE = 'cert_GetMyData01.cer'

BACKUP CERTIFICATE cert_GetMyData02 TO FILE = 'cert_GetMyData02.cer'

go

use db_Target

go

-- Import the certificates on the target database, note that we don't
-- need the private keys

CREATE CERTIFICATE cert_GetMyData01
FROM FILE = 'cert_GetMyData01.cer'

go

CREATE CERTIFICATE cert_GetMyData02
FROM FILE = 'cert_GetMyData02.cer'

go

-- Now let's create users mapped to each one of the certificates.

-- As permissions can only be granted to principals and not directly

-- to a certificate, we need to map the certificate to a user.

-- Note: The cert-mapped user SID is derived from teh certificate
-- thumbprint

-- therefore any 2+ principals (login or user in any database)
-- mapped to the

-- same certificate will have the same SID and will refer to the same

-- principal for practical purposes.

CREATE USER cert_GetMyData01 FOR CERTIFICATE cert_GetMyData01

go

CREATE USER cert_GetMyData02 FOR CERTIFICATE cert_GetMyData02

go

-- For the first SP, grant the permissions to the cert-mapped
-- user directly

GRANT SELECT ON data_owner.MyTable TO cert_GetMyData01

go

-- For the second SP, we want only AUTHENTICATE permissiion, this
-- will allow teh certificate to vouch for the context only on this
-- database.

-- Note: As the trust is only accross database and not accross the
-- instance, the new context is only valid for database operations,
-- and will not honor any server-scoped permissions.

GRANT AUTHENTICATE TO cert_GetMyData02

go

USE db_Source

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will succed as the certificate will be granting the required
-- permission to select the data from the table

-- Note that someuser is a valid context accross the server at
-- this point

EXEC data_owner.sp_GetMyData01

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- cleanup

USE master

go

DROP DATABASE db_Source

go

DROP DATABASE db_Target

go

DROP LOGIN dbo_db_Source

DROP LOGIN dbo_db_Target

DROP LOGIN data_owner

DROP LOGIN someuser

go

|||

Raul -- thanks a lot for taking the time to do this. Excellent explanation and demo!

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is executed?
thanks
Philip
Philip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is executed?
> thanks
> Philip
|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>
|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>
|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is exe
cuted?
thanks
PhilipPhilip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is e
xecuted?
> thanks
> Philip|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 ste
ps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 ste
ps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is executed?
thanks
PhilipPhilip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is executed?
> thanks
> Philip|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:
> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
> > Hi,
> >
> > we have to setup a job that will execute a DTS Package to enrich a file.
> >
> > But we don't want to execute the DTS if the file is not there.
> >
> > So how can I use an ActiveX Script to control whether the DTS Package is executed?
> >
> > thanks
> >
> > Philip
>|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:
> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
> > Hi,
> >
> > we have to setup a job that will execute a DTS Package to enrich a file.
> >
> > But we don't want to execute the DTS if the file is not there.
> >
> > So how can I use an ActiveX Script to control whether the DTS Package is executed?
> >
> > thanks
> >
> > Philip
>|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Control "master merge"

Hi,
We have a fulltext catalog, configure with CHANGE_TRACKING OFF and we have a
Timestamp column. After adding some new data, we execute an “ALTER FULLTEXT
INDEX ON [MyCatalog] START INCREMENTAL POPULATION”.
It’s work well but each START INCREMENTAL POPULATION, fire a master merge;
as we can see in the event viewer:
Component: MicrosoftIndexer
Catalog: SQLFT0000600005. A master merge was started due to an external
request.
A master merge have a very bad performance’s impact (and it take 4min to
complete!). How can we control it?
Thanks,
Thibaut
You can set sp_fulltext_service 'resource_usage' to a lower value. Master
merges occur (IIRC) after every 500,000 rows are processed as described in
http://msdn2.microsoft.com/en-us/library/ms143272.aspx
In SQL Server 2000, a master merge would start at midnight, or when 500,000
documents were full-text indexed.
In SQL Server 2005, a master merge starts at the end of full population and
also when an internal threshold on the number of full-text index files has
been reached.
A master merge also occurs when 500,000 documents are full-text indexed,
which is the same as in SQL Server 2000.
SQL Server 2005 also allows users to start a master merge using data
definition language.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tib" <tib@.discussions.microsoft.com> wrote in message
news:9C2B85EA-3900-478C-B7B6-FD9F2239FF7F@.microsoft.com...
> Hi,
> We have a fulltext catalog, configure with CHANGE_TRACKING OFF and we have
> a
> Timestamp column. After adding some new data, we execute an "ALTER
> FULLTEXT
> INDEX ON [MyCatalog] START INCREMENTAL POPULATION".
> It's work well but each START INCREMENTAL POPULATION, fire a master merge;
> as we can see in the event viewer:
> Component: MicrosoftIndexer
> Catalog: SQLFT0000600005. A master merge was started due to an external
> request.
>
> A master merge have a very bad performance's impact (and it take 4min to
> complete!). How can we control it?
> Thanks,
> Thibaut
>
|||Thanks for your reply,
If the master merge occurred after 500 000 new rows it will be ok for us.
But in our case, it will start after each “START INCREMENTAL POPULATION”
(sometime we just add 10 rows).

> In SQL Server 2005, a master merge starts at the end of full population
> and also when an internal threshold on the number of full-text index
> files has been reached.
We are not in this case. So why a master merge occurs?
Before the execution of an “ALTER FULLTEXT INDEX ON [MyTable] START
INCREMENTAL POPULATION”, we have:
SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') as
PopulateStatus,
FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'IndexSize') as IndexSize,
FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'ItemCount') as ItemCount,
FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'MergeStatus') as MergeStatus,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
TableFulltextPopulateStatus,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextFailCount') as
TableFulltextFailCount,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextDocsProcessed') as
TableFulltextDocsProcessed,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
TableFulltextPopulateStatus
Go
IndexSize ItemCount MergeStatus
0 1455 5493526 0 0000
Just after [ALTER FULLTEXT INDEX ON [MyTable] START INCREMENTAL POPULATION]:
0 0 5493604 11 0000
And in the event viewer:
Component: MicrosoftIndexer
Catalog: SQLFT0000600005. A master merge was started due to an external
request.
“START INCREMENTAL POPULATION” is an external request that’s force a master
merge?
Thanks for help,
Thibaut
Ps: We are using sql server 2005.
|||Why can't you use Change Tracking?
It does sound like a master merge is done when a full or incremental
population is completed.
From http://msdn2.microsoft.com/en-us/library/ms143272.aspx
In SQL Server 2005, a master merge starts at the end of full population and
also when an internal threshold on the number of full-text index files has
been reached.
And in my test I have verified that it also occurs upon completion of an
incremental population.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tib" <tib@.discussions.microsoft.com> wrote in message
news:A195BAAF-2D46-478A-9578-FA3694C2BD1E@.microsoft.com...
> Thanks for your reply,
>
> If the master merge occurred after 500 000 new rows it will be ok for us.
> But in our case, it will start after each "START INCREMENTAL POPULATION"
> (sometime we just add 10 rows).
> We are not in this case. So why a master merge occurs?
> Before the execution of an "ALTER FULLTEXT INDEX ON [MyTable] START
> INCREMENTAL POPULATION", we have:
> SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') as
> PopulateStatus,
> FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'IndexSize') as IndexSize,
> FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'ItemCount') as ItemCount,
> FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'MergeStatus') as MergeStatus,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
> TableFulltextPopulateStatus,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextFailCount') as
> TableFulltextFailCount,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextDocsProcessed') as
> TableFulltextDocsProcessed,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
> TableFulltextPopulateStatus
> Go
> IndexSize ItemCount MergeStatus
> 0 1455 5493526 0 0 0 0 0
> Just after [ALTER FULLTEXT INDEX ON [MyTable] START INCREMENTAL
> POPULATION]:
> 0 0 5493604 11 0 0 0 0
> And in the event viewer:
> Component: MicrosoftIndexer
> Catalog: SQLFT0000600005. A master merge was started due to an external
> request.
> "START INCREMENTAL POPULATION" is an external request that's force a
> master
> merge?
> Thanks for help,
> Thibaut
> Ps: We are using sql server 2005.
>

containstable rank inconsistent

Hi,
I have an FTS table on which I execute a CONTAINSTABLE(ISABOUT..) .
the search works fine but I noticed that after I do an incremental
population or after a track changes has been triggered by a change in the
data
then I get different rank results between identical searches (pre and post
population).
this is then sorted if I do a full population or rebuild the catalog
does anybody know why this happens, is it a bug? can it alter the rank in a
way that changes the order of appearance or is it always relative?
I am building an information retrieval (search) system and this could be a
big upset so any help
would be appreciated
thanks
shay
there was a problem with this prior to sp3. What version of sql server are
you running? do a select @.@.version to determin this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shay cohen" <shay@.infospheraltd.com> wrote in message
news:%23MUx$EHVFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have an FTS table on which I execute a CONTAINSTABLE(ISABOUT..) .
> the search works fine but I noticed that after I do an incremental
> population or after a track changes has been triggered by a change in the
> data
> then I get different rank results between identical searches (pre and post
> population).
> this is then sorted if I do a full population or rebuild the catalog
> does anybody know why this happens, is it a bug? can it alter the rank in
a
> way that changes the order of appearance or is it always relative?
> I am building an information retrieval (search) system and this could be a
> big upset so any help
> would be appreciated
> thanks
> shay
>
>

Contains + Special caractere

When I execute this request
select *
from fiche
where contains(numerofiche,'A30/05')
I get all the row where numerofiche contains 05. Does it come from the
caractere "/"? Is there a way to get exactly the row containing exactly
"A30/05" using the contains predicat.
Thanks
I don't get this result.
What word breaker are you using, French, neutral, or another language?
Do you get this result if you wrap your search phrase in double quotes?
"Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
news:OxSfGRxwEHA.1192@.tk2msftngp13.phx.gbl...
> When I execute this request
> select *
> from fiche
> where contains(numerofiche,'A30/05')
> I get all the row where numerofiche contains 05. Does it come from the
> caractere "/"? Is there a way to get exactly the row containing exactly
> "A30/05" using the contains predicat.
> Thanks
>
|||what do you mean by "word breaker". I'm using a french version of SQL Server
2000
If I execute the request:
select *
from fiche
where contains(numerofiche,'"A30/05"')
I've got the same set of results
numerofiche
A30/05
C99/05
E 05
05
H 05
"Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de
news:ubME$LzwEHA.3844@.TK2MSFTNGP09.phx.gbl...
> I don't get this result.
> What word breaker are you using, French, neutral, or another language?
> Do you get this result if you wrap your search phrase in double quotes?
>
> "Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
> news:OxSfGRxwEHA.1192@.tk2msftngp13.phx.gbl...
>
|||The default for a french version of SQL Server will be using the french word
breaker.
You can verify this by doing the following:
sp_help_fulltext_columns 'TableName' and then noting the number for the
FullText_Language column. It should be 1036. You can get the language which
matches this by issuing a xp_MSFullText which will display a list of
languages and their LocID which corresponds to the value in the
FullText_Language column.
I do get this behavior with the French Word breaker, but not the English
ones or the neutral word breaker.
Are you doing any inflectional or FreeText searches? If not, your best
optioin might be to use the neutral word breaker.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
news:ujzUBdzwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> what do you mean by "word breaker". I'm using a french version of SQL
Server[vbcol=seagreen]
> 2000
> If I execute the request:
> select *
> from fiche
> where contains(numerofiche,'"A30/05"')
> I've got the same set of results
> numerofiche
> A30/05
> C99/05
> E 05
> 05
> H 05
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de
> news:ubME$LzwEHA.3844@.TK2MSFTNGP09.phx.gbl...
exactly
>
|||another option is to replace the / with a _
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
news:ujzUBdzwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> what do you mean by "word breaker". I'm using a french version of SQL
Server[vbcol=seagreen]
> 2000
> If I execute the request:
> select *
> from fiche
> where contains(numerofiche,'"A30/05"')
> I've got the same set of results
> numerofiche
> A30/05
> C99/05
> E 05
> 05
> H 05
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de
> news:ubME$LzwEHA.3844@.TK2MSFTNGP09.phx.gbl...
exactly
>
|||BTW - you'ld have to do this replacement on your content as well in your
search phrases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ual$280wEHA.2676@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> another option is to replace the / with a _
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Cedric DEBARD" <charrue21@.ifrance.com> wrote in message
> news:ujzUBdzwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Server
quotes?[vbcol=seagreen]
the
> exactly
>