Convert ACCESS code to SQL Server

Hi,
I m working on the project which was using MS Access but now i want to use SQL Server 2005.
But there are some queries which are not understood by me, so please help me in converting those.
Following is the query:

SHAPE {select stlcode , SampleTypeCode,InNo,NoOfSamples,RecdDistrict,RecdT al,DtRecd , RecdFrom, RecdNameDesg, PrimaryInNo, MicroInNo, WaterInNo from SInward order by SampleTypeCode,InNo } AS ParentCMD APPEND
({select SampleType, InNo, SrNo, StlCode, AStlCode, SampleTakenDt, FarmerNm, FarAddVil, FarAddPost, FarAddDist, FarAddTal , FarAddPin, SurveyGrNo, ReprArea, LastSeason, LastSeasonCrop, NextSeason, NextSeasonCrop, NextSeasonCrop22, AgeOfTree, LandProfile, DepthFrom, DepthTo, WaterSource, CollectdBy, SampleAccepted, LabSampleNo, HCNumber, MicroLabSampleNo, WaterLabSampleNo from SDetails order by SrNo } AS ChildCMD RELATE SampleTypeCode TO SampleType, InNo to InNo, stlcode to stlcode) AS ChildCMD

This query was previously written in MS Access and now I want it to be in SQL Server 2005, So please help me in solving this.
Thanx

Quote:

Originally Posted by sachinkale123

Hi,
I m working on the project which was using MS Access but now i want to use SQL Server 2005.
But there are some queries which are not understood by me, so please help me in converting those.
Following is the query:

SHAPE {select stlcode , SampleTypeCode,InNo,NoOfSamples,RecdDistrict,RecdT al,DtRecd , RecdFrom, RecdNameDesg, PrimaryInNo, MicroInNo, WaterInNo from SInward order by SampleTypeCode,InNo } AS ParentCMD APPEND
({select SampleType, InNo, SrNo, StlCode, AStlCode, SampleTakenDt, FarmerNm, FarAddVil, FarAddPost, FarAddDist, FarAddTal , FarAddPin, SurveyGrNo, ReprArea, LastSeason, LastSeasonCrop, NextSeason, NextSeasonCrop, NextSeasonCrop22, AgeOfTree, LandProfile, DepthFrom, DepthTo, WaterSource, CollectdBy, SampleAccepted, LabSampleNo, HCNumber, MicroLabSampleNo, WaterLabSampleNo from SDetails order by SrNo } AS ChildCMD RELATE SampleTypeCode TO SampleType, InNo to InNo, stlcode to stlcode) AS ChildCMD

This query was previously written in MS Access and now I want it to be in SQL Server 2005, So please help me in solving this.
Thanx

That is not simple thing, most of the query you have to change.

1.Access Date in where clause will be # change to
2.Access IIF condition, change to CaseBlab blab.|||

Quote:

Originally Posted by hariharanmca

That is not simple thing, most of the query you have to change.

1.Access Date in where clause will be # change to
2.Access IIF condition, change to CaseBlab blab.

Thanxs for u r reply...
now i can think that way...sqlsql

Convert *= / =* to outer joins (ANSI Join)

Hi,

I'm working on converting *= and =* to 'left outer join' and 'right outer join'.

I noticed the difference in behavior between the =* and the phrase "right outer join" and between *= and 'left outer join'. The result set is different. Here is an example:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

I try to conver the above to:

select a.au_id, b.title, c.qty

from titleauthor a

right outer join titles b

on (a.title_id = b.title_id )

right outer join sales c

on (a.title_id = c.title_id )

The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. It seems not that straight forward to convert.

The question is: If I want to get 391 row, what should I change/add in the second sql statement?

Thank you. Appreciate your help.

Joan

I think this:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

Is actually not a right join between these tables directly, but actually more this query:

--changed the a b c aliases to just be the table for clarity
select titleauthor.au_id, titles.title, sales.qty
from sales
cross join titles
left outer join titleauthor
on titles.title_id = titleauthor.title_id
and sales.title_id = titleauthor.title_id

The commas can be translated directly to CROSS JOIN, with the where being the same. So in your query, since there is no link between sales and titles, it does a cross join. You can see this by looking at the plan:

Code Snippet

SET SHOWPLAN_ALL ON
GO

select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)

SET SHOWPLAN_ALL OFF
GO

|--Hash Match(Right Outer Join, HASH:([a].[title_id])=([b].[title_id]),
RESIDUAL:([pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[titles].[title_id] as [b].[title_id]
AND [pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[sales].[title_id] as [c].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [a]))
|--Nested Loops(Inner Join)
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [b]))

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales] AS [c]))

Note, no join criteria. Hopefully this helps, it is one of the reasons why the syntax was changed
|||

Hi,

Is it because the question is not clear?

Joan

|||You mean the query? It is clear enough, it is just a matter of what you are asking. The ANSI Style is far more clear to express queries as they are going to be/can be expressed.

The old style was ambiguous like this and was based on the concept that you (logically) first cross join each of the tables in the FROM, then for each row check the criteria. Works like a champ for INNER joins, but OUTER joins not so much.|||

Hi Louis,

Thank you so much!

I'll try to fix my query. I might have question later.

Thanks again.

Joan

|||

Hi Louis,

Here I get another example. The old code is as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES ,CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP, CreditSurchargeUnit CU, CreditSurchargePolicyLineItem CI
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.CreditSurchargeId = CU.CreditSurchargeId
and CP.ObjectSubjectId = CU.ObjectSubjectId
and CU.CreditSurchargeId *= CI.CreditSurchargeId
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83
and CP.effectivedateID = 1

I converted as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES, CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP JOIN CreditSurchargeUnit CU ON CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId
LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON (CU.CreditSurchargeId = CI.CreditSurchargeId)
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

The old code returns 23 rows, which it should be. But the new code returns 11 rows, which is not correct. But I can't tell what is wrong with the new code.

Thank you!

Joan

|||

This query may fix your problem..

Code Snippet

Select
CU.Name
, CI.Amount
, CI.PolicyItemId
, CI.timestamp
, CU.CreditSurchargeId
, CU.ValueType_ES
, CU.IsDefault
, CU.IsModifiable
, CU.IsCredit
, CP.ObjectCategoryId
, CU.Type_ES
, CP.DisplayOrder
, CP.Amount
, CP.Effectivedateid
from
CreditSurchargePolicyLine CP

JOIN CreditSurchargeUnit CU ON
CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId

LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON
CU.CreditSurchargeId = CI.CreditSurchargeId
And CI.PolicyItemId = 30153677
And CI.PolicyLineItemId = 1

where
CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

|||

Thank you ManiD! Thank you all!

|||

You are welcome.

Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than

at where clause.

-Mani.D

|||

>>Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than at where clause.<<

In spirit this is usually right, but this isn't quite true. You have to be careful and cognizant about where to put FILTER criteria, but it can go either place. You just have to realize that:

In the JOIN clause, a condition is applied to the joining of the two sets of data. And from the left side of a LEFT join will be returned no matter what (or the right side of a RIGHT join or both sides of a FULL join for that matter

In the WHERE clause, the condition is applied the the set of rows produced from the FROM clause. So if a row was returned in the FROM clause as the result of an LEFT OUTER JOIN, if you then try to filter out the data by comparing data from the right table, all of the values will be NULL. So they will be filtered out unless you realize this.
sqlsql

Convert *= / =* to outer joins (ANSI compliant)

Hi,

I'm working on converting *= and =* to 'left outer join' and 'right outer join'.

I noticed the difference in behavior between the =* and the phrase "right outer join" and between *= and 'left outer join'. The result set is different. Here is an example:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

I try to conver the above to:

select a.au_id, b.title, c.qty

from titleauthor a

right outer join titles b

on (a.title_id = b.title_id )

right outer join sales c

on (a.title_id = c.title_id )

The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. It seems not that straight forward to convert.

The question is: If I want to get 391 row, what should I change/add in the second sql statement?

Thank you. Appreciate your help.

Joan

I think this:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

Is actually not a right join between these tables directly, but actually more this query:

--changed the a b c aliases to just be the table for clarity
select titleauthor.au_id, titles.title, sales.qty
from sales
cross join titles
left outer join titleauthor
on titles.title_id = titleauthor.title_id
and sales.title_id = titleauthor.title_id

The commas can be translated directly to CROSS JOIN, with the where being the same. So in your query, since there is no link between sales and titles, it does a cross join. You can see this by looking at the plan:

Code Snippet

SET SHOWPLAN_ALL ON
GO

select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)

SET SHOWPLAN_ALL OFF
GO

|--Hash Match(Right Outer Join, HASH:([a].[title_id])=([b].[title_id]),
RESIDUAL:([pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[titles].[title_id] as [b].[title_id]
AND [pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[sales].[title_id] as [c].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [a]))
|--Nested Loops(Inner Join)
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [b]))

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales] AS [c]))

Note, no join criteria. Hopefully this helps, it is one of the reasons why the syntax was changed
|||

Hi,

Is it because the question is not clear?

Joan

|||You mean the query? It is clear enough, it is just a matter of what you are asking. The ANSI Style is far more clear to express queries as they are going to be/can be expressed.

The old style was ambiguous like this and was based on the concept that you (logically) first cross join each of the tables in the FROM, then for each row check the criteria. Works like a champ for INNER joins, but OUTER joins not so much.|||

Hi Louis,

Thank you so much!

I'll try to fix my query. I might have question later.

Thanks again.

Joan

|||

Hi Louis,

Here I get another example. The old code is as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES ,CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP, CreditSurchargeUnit CU, CreditSurchargePolicyLineItem CI
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.CreditSurchargeId = CU.CreditSurchargeId
and CP.ObjectSubjectId = CU.ObjectSubjectId
and CU.CreditSurchargeId *= CI.CreditSurchargeId
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83
and CP.effectivedateID = 1

I converted as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES, CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP JOIN CreditSurchargeUnit CU ON CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId
LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON (CU.CreditSurchargeId = CI.CreditSurchargeId)
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

The old code returns 23 rows, which it should be. But the new code returns 11 rows, which is not correct. But I can't tell what is wrong with the new code.

Thank you!

Joan

|||

This query may fix your problem..

Code Snippet

Select
CU.Name
, CI.Amount
, CI.PolicyItemId
, CI.timestamp
, CU.CreditSurchargeId
, CU.ValueType_ES
, CU.IsDefault
, CU.IsModifiable
, CU.IsCredit
, CP.ObjectCategoryId
, CU.Type_ES
, CP.DisplayOrder
, CP.Amount
, CP.Effectivedateid
from
CreditSurchargePolicyLine CP

JOIN CreditSurchargeUnit CU ON
CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId

LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON
CU.CreditSurchargeId = CI.CreditSurchargeId
And CI.PolicyItemId = 30153677
And CI.PolicyLineItemId = 1

where
CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

|||

Thank you ManiD! Thank you all!

|||

You are welcome.

Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than

at where clause.

-Mani.D

|||

>>Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than at where clause.<<

In spirit this is usually right, but this isn't quite true. You have to be careful and cognizant about where to put FILTER criteria, but it can go either place. You just have to realize that:

In the JOIN clause, a condition is applied to the joining of the two sets of data. And from the left side of a LEFT join will be returned no matter what (or the right side of a RIGHT join or both sides of a FULL join for that matter

In the WHERE clause, the condition is applied the the set of rows produced from the FROM clause. So if a row was returned in the FROM clause as the result of an LEFT OUTER JOIN, if you then try to filter out the data by comparing data from the right table, all of the values will be NULL. So they will be filtered out unless you realize this.

Conversion from type SqlInt32 to type Integer is not valid]

Hi all,

I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine.

The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings.

Any suggestions.

Best Regards

Wafi Mohtaseb

You can perform an explicit conversion between those two types

Dim xAs System.Data.SqlTypes.SqlInt32 = 5Dim yAs Integer y =CType(x,Integer)

|||

This is what I am doing in the code, and its working on my local server, the problem appears when I uploaded the site to the hosting company server.

Best regards,

|||

please post the code you are having trouble with.

|||

1Public Overrides Function Insert()As Boolean2 Dim cmdToExecuteAs SqlCommand =New SqlCommand3 cmdToExecute.CommandText ="dbo.[UserFiles_Insert]"4 cmdToExecute.CommandType = CommandType.StoredProcedure56' // Use base class' connection object7 cmdToExecute.Connection = _mainConnection89Try10 cmdToExecute.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int, 4, ParameterDirection.Input,False, 10, 0,"", DataRowVersion.Proposed, _userID))11 cmdToExecute.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileName))12 cmdToExecute.Parameters.Add(New SqlParameter("@.FileType", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileType))13 cmdToExecute.Parameters.Add(New SqlParameter("@.FileSize", SqlDbType.Decimal, 9, ParameterDirection.Input,False, 18, 0,"", DataRowVersion.Proposed, _fileSize))14Dim lengthAs Integer = 015If Not _fileContent.IsNullThen16 length = _fileContent.Length17End If18 cmdToExecute.Parameters.Add(New SqlParameter("@.FileContent", SqlDbType.Image, length, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileContent))19 cmdToExecute.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, 4, ParameterDirection.Output,False, 10, 0,"", DataRowVersion.Proposed, _fileID))2021' // Open connection.22 _mainConnection.Open()2324' // Execute query.25 cmdToExecute.ExecuteNonQuery()26Dim _fileIDAs SqlInt32

//Error ocures here27_fileID =New SqlInt32(CType(cmdToExecute.Parameters.Item("@.FileID").Value,Integer))28Return True29 Catch exAs Exception30' // some error occured. Bubble it to caller and encapsulate Exception object31Throw New Exception("UserFiles::Insert::Error occured.", ex)32Finally33' // Close connection.34 _mainConnection.Close()35 cmdToExecute.Dispose()36End Try37 End Function

Conversion Error...nvarchar to Datetime

Hi Group,
I am new with SQL Server..I am working with SQL Server 2000.
I am storing the date in a nvarchar column of atable... Now I want to
show the data of Weekends..Everything is OK...But the problem is
arising with Conversion of nvarchar to date...to identify the
weekends...Like..Here DATEVALUE is a nvarchar column...But getting the
error..Value of DATEVALUE like dd-mm-yyyy...04-08-2004

------------------
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
------------------
------Actual Query----------
Select DATEVALUE,<Other Column Names> from Result where
Datepart(dw,convert(Datetime,DATEVALUE))<>1 and
Datepart(dw,convert(Datetime,DATEVALUE))<>7
------------------
Thanks in advance..
Regards
Arijit Chatterjee(arijitchatterjee123@.yahoo.co.in) writes:
> I am new with SQL Server..I am working with SQL Server 2000.
> I am storing the date in a nvarchar column of atable... Now I want to
> show the data of Weekends..Everything is OK...But the problem is
> arising with Conversion of nvarchar to date...to identify the
> weekends...Like..Here DATEVALUE is a nvarchar column...But getting the
> error..Value of DATEVALUE like dd-mm-yyyy...04-08-2004

Best is to store date values in datetime columns. If you use character
format, you should use char (the n just doubles the space with no gain
for it, and the var is pointless since size is fixed), and you should use
the format YYYYMMDD. Furthermore, you should attach a constraint to the
columns

datecol char(8) CONSTRAINT ck_tbl_datecol CHECK (isdate(datecol) = 1)

to ascertain that you don't get illegal values.

Storing dates in a format like DD-MM-YYYY is going to give all sorts of
headache. 04-08-2004 could be interpreted as Aug 4th or April 8th, depending
on language and datefromat settings. (And, in case of humans, of the
perceptions of the user.) You can't sort on this format (unless you really
want 3 Aug to come before 4 June).

The format YYYYMMDD sorts well, and is always interpreted in the same way.

See also http://www.karaszi.com/SQLServer/info_datetime.asp.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||See this

declare @.t table (d varchar(20))
insert into @.t values('10-apr-2005')
insert into @.t values('10-MAy-2005')
insert into @.t values('10-Jun-2005')
insert into @.t values('10-Jul-2005')
Select * from @.t where Datepart(dw,convert(Datetime,d))<>1 and
Datepart(dw,convert(Datetime,d))<>7

Madhivanan|||Thanks for your great help..
Regards
Arijit Chatterjee

Conversation ID cannot be associated with an active conversation

Hi:

My service broker was working perfectly fine earlier. As I was testing...I recreated the whole service broker once again.

Now I am able to get the message at the server end from intiator. When trying to send message from my server to the intiator it gives this error in sql profiler.

broker:message undeliverable: This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'.

broker:message undeliverable This message could not be delivered because the 'receive sequenced message' action cannot be performed in the 'ERROR' state.

How do I proceed now ?

Thanks,

Pramod

This is happening randomly....

Now When I am sending the message I am getting this error in intiator sql profiler.

broker:message undeliverable: This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'....

What does this mean ?

Thanks,

Pramod

|||Did you backup, move and restore the initiator database? The error you are seeing could be produced because the initiator endpoint and the target endpoint are not in sync which could be the result of a backup/restore operation. If it is possible, can you drop all services and start all over on the two instances?|||I meant "did you backup, move and restore the TARGET database"|||

In fact I created new databases, new services, new endpoints on both sides i.e on both instances.

Pramod

|||

The problem is from the END CONVERSATION ... WITH CLEANUP. Don't use it, use simple END CONVERSATION. See this http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx

HTH,
~ Remus

|||

Remus:

I removed with cleanup in my sprocs...I notice other interesting things happening.

The error still comes up in the SQL profiler, but the message is delivered randomly.If I try sending 3 times 1 time it reachs target service broker.

One more interesting thing is my xml message which I sent is garbled in the target. Its not the way I sent to target from initiator.

Thanks,

Pramod

|||

Pramod S Kumar wrote:

...the message is delivered randomly.If I try sending 3 times 1 time it reachs target service broker.

Typically this means that there are more instances of the target service and Service Broker does a load balancing across them. Make sure you don't have the same target service in another database you forgot about. Alternatively you can specify the desired broker instance in the BEGIN DIALOG to force the selected target service.

Also, see this post here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=335683&SiteID=1

Pramod S Kumar wrote:

One more interesting thing is my xml message which I sent is garbled in the target. Its not the way I sent to target from initiator.

Can you give an example of how the payload is garbled?
Please note that Unicode XML has a Byte Order Mark (BOM) like 0xFFFE in front of the XML stream. Also, make sure you don't mix VARCHAR and NVARCHAR types when sending/receiving the message. The best practice is to always use the XML datatype for this. If the SEND payload is declared in the T-SQL batch, declare it as XML. If is a parameter sent from Ado.NET, use the System.Data.SqlDbType.Xml parameter type. Same applies to receiving the message, assign the message_body to a XML type.

HTH,
~ Remus

|||

Remus:

Prblm 1:
--
I am forcing to target service name here...Hence that should not be problem.

DECLARE @.dialog_handle uniqueidentifier,

@.msg XML

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE CLIENTSERVICE

TO SERVICE 'SERVERSERVICE'

ON CONTRACT MainContract

WITH ENCRYPTION = OFF ;

Prblm 2:
--
This works fine b/w 2 instances in local server but doesnt work b/w 2 different servers.

Here is my table structure for both target and initiator.

CREATE TABLE [dbo].[messages_log](
[logid] [int] IDENTITY(1,1) NOT NULL,
[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
[msgdata] [xml] NULL,
CONSTRAINT [PK_messages_log] PRIMARY KEY CLUSTERED
(
[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

Thanks,

Pramod

|||

Pramod S Kumar wrote:

Remus:

Prblm 1:
--
I am forcing to target service name here...Hence that should not be problem.

DECLARE @.dialog_handle uniqueidentifier,

@.msg XML

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE CLIENTSERVICE

TO SERVICE 'SERVERSERVICE'

ON CONTRACT MainContract

WITH ENCRYPTION = OFF ;

I think you missed my point. Unless you specify a broker instance, the load balancing is probably the problem. Your script does not specify a broker instance.

Pramod S Kumar wrote:

Prblm 2:
--
This works fine b/w 2 instances in local server but doesnt work b/w 2 different servers.

Here is my table structure for both target and initiator.

CREATE TABLE [dbo].[messages_log](
[logid] [int] IDENTITY(1,1) NOT NULL,
[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
[msgdata] [xml] NULL,
CONSTRAINT [PK_messages_log] PRIMARY KEY CLUSTERED
(
[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

This doesn't help me in any way. I'm asking you to show me an example of how the actual XML message is different between the one you SEND and the one you RECEIVE.

|||

Remus:

Ok you meant to specify broker instance while specifying route...if that is the case here is the script..
Initiator:
CREATE ROUTE SERVERROUTE

WITH

BROKER_INSTANCE = '3F070C35-3C1E-4FA7-B654-33280DA1482B',

SERVICE_NAME = 'SERVERSERVICE' ,

ADDRESS = 'tcp://10.23.2.145:6099';

Target:
CREATE ROUTE CLIENTROUTE

WITH

BROKER_INSTANCE = '4FB2019E-D9D0-4665-9FF6-262D5C33A3D5',

SERVICE_NAME = 'CLIENTSERVICE' ,

ADDRESS = 'tcp://10.23.2.146:6022';

GO

Ok with xml....here is the example...

Original XML

<queue userid="23" Friendlyname="more download" TemplateName="TempDownloadReportName">

<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="YRMTH" datatype="2" grouporder="-1" summed="0" averaged="0" counted="1" />

<fields field="SLINE" datatype="2" grouporder="1" summed="0" averaged="0" counted="0" />

<fields field="VESSEL" datatype="2" grouporder="2" summed="0" averaged="0" counted="0" />

<fields field="COMMODITY" datatype="2" grouporder="3" summed="0" averaged="0" counted="0" />

<fields field="REEFER" datatype="3" grouporder="4" summed="0" averaged="0" counted="0" />

</criteria>

</filters> </queue>

XML received at target:

<queue userid="23" Friendlyname="more download" TemplateName="TempDownloadReportName">

<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="YRMTH" datatype="2" grouporder="-1" summed="0" averaged="0" counted="1" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="SLINE" datatype="2" grouporder="1" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="VESSEL" datatype="2" grouporder="2" summed="0" averaged="0" counted="0" />
</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="COMMODITY" datatype="2" grouporder="3" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="REEFER" datatype="3" grouporder="4" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
</queue>

Now...when I tried today...I am not able to send any messages from target to initiator.I have also enabled message forwarding.

Thanks,

Pramod

|||

Pramod S Kumar wrote:

Remus:

Ok you meant to specify broker instance while specifying route...

Sorry about the confusion. I actually meant specifying the broker instance in the BEGIN DIALOG statement, like this:

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE CLIENTSERVICE

TO SERVICE 'SERVERSERVICE', '3F070C35-3C1E-4FA7-B654-33280DA1482B'

ON CONTRACT MainContract

WITH ENCRYPTION = OFF ;

Pramod S Kumar wrote:

Ok with xml....here is the example...

Original XML

<queue userid="23" Friendlyname="more download" TemplateName="TempDownloadReportName">

<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="YRMTH" datatype="2" grouporder="-1" summed="0" averaged="0" counted="1" />

<fields field="SLINE" datatype="2" grouporder="1" summed="0" averaged="0" counted="0" />

<fields field="VESSEL" datatype="2" grouporder="2" summed="0" averaged="0" counted="0" />

<fields field="COMMODITY" datatype="2" grouporder="3" summed="0" averaged="0" counted="0" />

<fields field="REEFER" datatype="3" grouporder="4" summed="0" averaged="0" counted="0" />

</criteria>

</filters> </queue>

XML received at target:

<queue userid="23" Friendlyname="more download" TemplateName="TempDownloadReportName">

<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="YRMTH" datatype="2" grouporder="-1" summed="0" averaged="0" counted="1" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="SLINE" datatype="2" grouporder="1" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="VESSEL" datatype="2" grouporder="2" summed="0" averaged="0" counted="0" />
</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="COMMODITY" datatype="2" grouporder="3" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="REEFER" datatype="3" grouporder="4" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
</queue>

These are not differences from Service Broker, but either from your processing or from the XML column storage in the table. If you would compare the XML in the queue itself (the one returned by RECEIVE), you'd see it identically with the one sent.

Note that XML data is not a string, you may have different representations of the same XML fragment that are equivalent.

|||

Remus:

I did make all the changes u specified.

Today I am not able to send any message when I send a message...I still have same error...in SQL Profiler.

This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'.

Thanks,

Pramod

|||

This means that you still have a conversation that is sending messages to it's peer conversation endpoint that was ended WITH CLEANUP.

Cleanup all databases involved (use ALTER DATABASE ... SET NEW_BROKER) and make sure that there are no more END CONVERSATION ... WITH CLEANUP in your scripts.

HTH,
~ Remus

control where the backup.dat goes

Hey everyone

I have a question for you, I am creating a database as part of the application that I am currently working one. When I create the database the .mdf and .log files go to the Sql directory however the .backup.dat file goes in my application folder. Is there anyway to disable this or have those files be elsewhere programatically.

Thanks

Kenzie

Using the BACKUP statement, you may specifically direct a backup file location.

See Books Online, Topic: BACKUP

You would issue a command similar to this:

BACKUP DATABASE MyDatabase TO DISK = 'D:\MyDataBackups\MyDatabase.bak'

CONTAINSTABLE and local TABLE variables

Hi I have a stored procedure that uses local variables of type TABLE, is it possible to use CONTAINSTABLE on these as it appears to not be working correctly. The CONTAINSTABLE code is at the very bottom of this long piece of code.

CREATE PROCEDURE dbo.SearchJobs
@.STRING varchar(6000),
@.Longitude float,
@.Latitude float,
@.Distance int,
@.Category int
AS

DECLARE @.SEARCHTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50),
SearchColumn text)

DECLARE @.OUTPUTTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50))

DECLARE @.Cat as varchar(200)
IF (NOT @.Category is NULL)
BEGIN
SET @.Cat = 'AND JobCategoryId = ' + @.Category
END
ELSE
BEGIN
SET @.Cat = ''
END

-- This does not calculate exact coordinate distances. It is designed to find all jobs
-- within a certain distance of a reference point. It uses a sqare box for speed as
-- opposed to calculationg a cirecular reference.
IF (NOT @.Longitude is NULL)
BEGIN
EXEC (' INSERT INTO @.SEARCHTABLE SELECT JobId, Type, PromotionCode, Title, JobCategoryId, [Description], ConditionOfEmployment, DollarAmount, DollarType, DateFrom, DateTo, ResumeEmail,
Phone, AreaCode, AddressId, Street, Suburb, PostCode, State, Longitude, Latitude, Positions, EmployerId, Filled, FilledBy, EmployerVisible,
EmployeeVisible, AdditionalSearchString, Street2, Suburb2, PostCode2, State2, Longitude2, Latitude2, AddressId2, Reference, (str(Title) + '' '' + str(Suburb) + '' '' + str(Street) + '' '' + str(PostCode) + CAST([Description] AS varchar))
FROM Job
WHERE (Longitude < (@.Longitude + @.Distance)) AND (Longitude > (@.Longitude - @.Distance)) AND (Latitude < (@.Latitude + @.Distance)) AND (Latitude > (@.Latitude - @.Distance))' + @.Cat)

END
ELSE
BEGIN
EXEC('INSERT INTO @.SEARCHTABLE SELECT * FROM Job WHERE 1 ' + @.Cat)
END

IF(LEN(@.STRING) > 0)

BEGIN
DECLARE @.SearchString varchar(8000)
SET @.SearchString = ''
DECLARE @.INDEX INT
DECLARE @.SLICE nvarchar(4000)

SELECT @.INDEX = 1
DECLARE @.IDCounter int
SET @.IDCounter = 0
IF @.String IS NULL RETURN
WHILE @.INDEX !=0
BEGIN
SELECT @.INDEX = CHARINDEX(' ' ,@.STRING)
IF @.INDEX !=0
BEGIN
SELECT @.SLICE = LEFT(@.STRING,@.INDEX - 1)
END
ELSE
BEGIN
SELECT @.SLICE = @.STRING
--INSERT INTO @.SEARCHTERMS (searchterm) VALUES(@.SLICE)
IF @.SearchString = ''
BEGIN
SET @.SearchString = @.SLICE
END
ELSE
BEGIN
SET @.SearchString = @.SearchString + ' OR ' + @.SLICE
END


SET @.IDCounter = @.IDCounter + 1
SELECT @.STRING = RIGHT(@.STRING,LEN(@.STRING) - @.INDEX)
IF LEN(@.STRING) = 0 BREAK
END

END
END

EXEC sp_fulltext_table @.SEARCHTABLE

SELECT FT_TBL.JobId, FT_TBL.Type, FT_TBL.PromotionCode, FT_TBL.Title, FT_TBL.JobCategoryId, FT_TBL.[Description], FT_TBL.ConditionOfEmployment, FT_TBL.DollarAmount, FT_TBL.DollarType, FT_TBL.DateFrom, FT_TBL.DateTo, FT_TBL.ResumeEmail,
FT_TBL.Phone, FT_TBL.AreaCode, FT_TBL.AddressId, FT_TBL.Street, FT_TBL.Suburb, FT_TBL.PostCode, FT_TBL.State, FT_TBL.Longitude, FT_TBL.Latitude, FT_TBL.Positions, FT_TBL.EmployerId, FT_TBL.Filled, FT_TBL.FilledBy, FT_TBL.EmployerVisible,
FT_TBL.EmployeeVisible, FT_TBL.AdditionalSearchString, FT_TBL.Street2, FT_TBL.Suburb2, FT_TBL.PostCode2, FT_TBL.State2, FT_TBL.Longitude2, FT_TBL.Latitude2, FT_TBL.AddressId2, FT_TBL.Reference,
KEY_TBL.RANK
FROM @.SEARCHTABLE AS FT_TBL INNER JOIN
CONTAINSTABLE (@.SEARCHTABLE,SearchColumn,
@.SearchString

) AS KEY_TBL
ON FT_TBL.JobId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

The deprecated sp_fulltext_table operates on a table that exists in the database, see sp_fulltext_table (Transact-SQL) in Books Online.

Indexes cannot be created explicitly on table variables. See table (Transact-SQL) in Books Online.

CONTAINSTABLE - weird results - using "and not"

Hello everyone,
I use full text search using containstable for search on my intranet
site. Its been working wonderfully. However, I have recently been
working on an upgrade to my search page to allow users to exclude
words. When excluding words I use the "and not" operator. I have
noticed that with some words it works, and with others it does not.
None of my words are noise or ignored words.
The below query returns 6 results (not using the excludes):
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )') KEY_TBL ON
FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN ICDB.dbo.LU_SearchCategories
LU_SearchCategories ON FT_TBL.Category = LU_SearchCategories.TypeID
WHERE FT_TBL.PermID <= (Select Users.Role from InfoCenter.dbo.Users
Users where Users.UID = 5432) and Category in (2,3) ORDER BY
KEY_TBL.RANK DESC
The top results in the query above returns a record that also contain
the words calculations and also the word integer. When I exclude
either of these words...it doesn't exclude that results from the
results.
Example using "and not"
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from ICDB.dbo.Users Users where Users.UID = 5432) and Category in (2,3)
ORDER BY KEY_TBL.RANK DESC
Further...when I include the word "calculations" in the search query as
a required word...it doesn't pull the record...actually..it doesn't
pull any records.
Example query:
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" ) AND (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC
The words "integer" and "calculations" are not the only words it does
this on...there are others.
Of course as I stated previously...some words to accurately exclude
those results...as in this case with the word "clmfmtdta". Query
example below.
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"clmfmtdta*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC
Does anybody have any ideas as to why this is doing this? Or maybe a
better way to use the "and not" operator?
I did a little more research...and I am thinking that because I use a
wildcard "*" to indicate the column, if say I used
CONTAINSTABLE(ICDB.dbo.SearchT=ADable, *, '(( "rte*" ) AND ( "billing*"
) AND ( "opt*" ) AND ( "editor*" )) and NOT ( "integer*" )')
Both rte, billing, opt, and editor would need to be in the same column
that integer is not in. So if rte, billing, opt, and editor were in
say the title column, and integer was in the description column...it
would not correctly filter out those records with integer in the
description.
Does this make sense? Any ideas?
|||Daniel,
Yes, it does. Unfortunately, the behavior is the "default" behavior for SQL
Server 2000 as SQL Server 7.0 was "fixed" to correspond to this same
behavior, i.e.., FT Search across column with or without the NOT
qualifier... Checkout the following two KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
For a possible workaround to this behavior, see the following blog entry:
"SQL Server FTS across multiple tables or columns" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
use Northwind
-- Multiple columns from one FT-enable table, modified to use the NOT
qualifier:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, '"University" and NOT "Lawrence"') as
A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<daniel.hirsch@.gmail.com> wrote in message
news:1123690134.517437.78570@.g43g2000cwa.googlegro ups.com...
I did a little more research...and I am thinking that because I use a
wildcard "*" to indicate the column, if say I used
CONTAINSTABLE(ICDB.dbo.SearchTXable, *, '(( "rte*" ) AND ( "billing*"
) AND ( "opt*" ) AND ( "editor*" )) and NOT ( "integer*" )')
Both rte, billing, opt, and editor would need to be in the same column
that integer is not in. So if rte, billing, opt, and editor were in
say the title column, and integer was in the description column...it
would not correctly filter out those records with integer in the
description.
Does this make sense? Any ideas?
|||Thanks..that does help...

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
James
It is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||has a full population been done?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> live.
> search
catalog[vbcol=seagreen]
the
>
|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?[vbcol=seagreen]
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
when
> catalog
> the
>
|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> catalog?
and[vbcol=seagreen]
> not
> when
the[vbcol=seagreen]
in
>
|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:

> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> and
> the
> in
>
>

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
James
It is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||has a full population been done?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>
|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> live.
> search
catalog[vbcol=seagreen]
the
>
|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?[vbcol=seagreen]
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
when
> catalog
> the
>
|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> catalog?
and[vbcol=seagreen]
> not
> when
the[vbcol=seagreen]
in
>
|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:

> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> and
> the
> in
>
>

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
JamesIt is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||has a full population been done?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > I'm dealing with a company that have 3 web servers a test, staging and
> live.
> > All these servers are sql server 2000 and are situated offsite. I do not
> > have dirrect access to them, I have to send them email with scripts when
> > things need changing.
> >
> > The website works on the test and the staging but for some reason the
> search
> > doesn't work on the live server. The search uses CONTAINS and the
catalog
> > seem to be set up correctly on all three servers. The search works in
the
> > sence that it doesn't error but all it returns is zero rows.
> >
> > Any clues?
> >
> > Cheers
> > James
> >
> >
>|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > has a full population been done?
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > I'm dealing with a company that have 3 web servers a test, staging and
> > live.
> > > All these servers are sql server 2000 and are situated offsite. I do
not
> > > have dirrect access to them, I have to send them email with scripts
when
> > > things need changing.
> > >
> > > The website works on the test and the staging but for some reason the
> > search
> > > doesn't work on the live server. The search uses CONTAINS and the
> catalog
> > > seem to be set up correctly on all three servers. The search works in
> the
> > > sence that it doesn't error but all it returns is zero rows.
> > >
> > > Any clues?
> > >
> > > Cheers
> > > James
> > >
> > >
> >
> >
>|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> > I've been assured it has.
> >
> > Is there a system query that will tell me the number of rows in the
> catalog?
> >
> > Cheers
> > James
> >
> > "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> > news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > has a full population been done?
> > >
> > > --
> > > Hilary Cotter
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > >
> > > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > Hi
> > > >
> > > > I'm dealing with a company that have 3 web servers a test, staging
and
> > > live.
> > > > All these servers are sql server 2000 and are situated offsite. I do
> not
> > > > have dirrect access to them, I have to send them email with scripts
> when
> > > > things need changing.
> > > >
> > > > The website works on the test and the staging but for some reason
the
> > > search
> > > > doesn't work on the live server. The search uses CONTAINS and the
> > catalog
> > > > seem to be set up correctly on all three servers. The search works
in
> > the
> > > > sence that it doesn't error but all it returns is zero rows.
> > > >
> > > > Any clues?
> > > >
> > > > Cheers
> > > > James
> > > >
> > > >
> > >
> > >
> >
> >
>|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:
> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> > try this
> >
> > select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
> >
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> > > I've been assured it has.
> > >
> > > Is there a system query that will tell me the number of rows in the
> > catalog?
> > >
> > > Cheers
> > > James
> > >
> > > "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> > > news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > > has a full population been done?
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > >
> > > > "James Brett" <james.brett@.unified.co.uk> wrote in message
> > > > news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > > Hi
> > > > >
> > > > > I'm dealing with a company that have 3 web servers a test, staging
> and
> > > > live.
> > > > > All these servers are sql server 2000 and are situated offsite. I do
> > not
> > > > > have dirrect access to them, I have to send them email with scripts
> > when
> > > > > things need changing.
> > > > >
> > > > > The website works on the test and the staging but for some reason
> the
> > > > search
> > > > > doesn't work on the live server. The search uses CONTAINS and the
> > > catalog
> > > > > seem to be set up correctly on all three servers. The search works
> in
> > > the
> > > > > sence that it doesn't error but all it returns is zero rows.
> > > > >
> > > > > Any clues?
> > > > >
> > > > > Cheers
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

CONTAINS search not working on live server

Hi
I'm dealing with a company that have 3 web servers a test, staging and live.
All these servers are sql server 2000 and are situated offsite. I do not
have dirrect access to them, I have to send them email with scripts when
things need changing.
The website works on the test and the staging but for some reason the search
doesn't work on the live server. The search uses CONTAINS and the catalog
seem to be set up correctly on all three servers. The search works in the
sence that it doesn't error but all it returns is zero rows.
Any clues?
Cheers
JamesIt is possible to have a full-text index defined, but the database itself
not be enabled for full-text indexing. I would expect an error with this
circumstance.
Also, make sure that the index is actually populated. The populate process
has been known to fail.
And, just for small measure, make sure that you are not including noise
words in the search. This can raise a 'nothing but noise words' error even
though there are also non-noise words. (Sigh.)
Russell Fields
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||has a full population been done?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm dealing with a company that have 3 web servers a test, staging and
live.
> All these servers are sql server 2000 and are situated offsite. I do not
> have dirrect access to them, I have to send them email with scripts when
> things need changing.
> The website works on the test and the staging but for some reason the
search
> doesn't work on the live server. The search uses CONTAINS and the catalog
> seem to be set up correctly on all three servers. The search works in the
> sence that it doesn't error but all it returns is zero rows.
> Any clues?
> Cheers
> James
>|||I've been assured it has.
Is there a system query that will tell me the number of rows in the catalog?
Cheers
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
> has a full population been done?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:%232GWGJHsEHA.516@.TK2MSFTNGP09.phx.gbl...
> live.
> search
catalog[vbcol=seagreen]
the[vbcol=seagreen]
>|||try this
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I've been assured it has.
> Is there a system query that will tell me the number of rows in the
catalog?
> Cheers
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OEaqk$JsEHA.896@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
when[vbcol=seagreen]
> catalog
> the
>|||That's the one
Thanks
James
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> try this
> select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "James Brett" <james.brett@.unified.co.uk> wrote in message
> news:O6jdEoPsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> catalog?
and[vbcol=seagreen]
> not
> when
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Also,
Try this in order to see if the Index is populated ...
select FulltextCatalogProperty('CatalogName', 'ItemCount').
If you do not have values and you attempt to start Change-Tracking and you
still
have 0 fro an ItemCount after trying to populate the Index, I have actually
found that right-clicking on the catalog and selecting Properties, a defined
file name should be evident...SQL000050001 or something like that.
At this point, what I have done in the past is simply navigate to defined
file location and move it to another directory "Only after stopping the
MS-Search" and then restart MS-Search.
Rebuild and Re-Populate !!!
Anthony E. Castro
MCP, MCDBA
"James Brett" wrote:

> That's the one
> Thanks
> James
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWvuOESsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> and
> the
> in
>
>

CONTAINS not working

When I run the query,
SELECT nn
FROM tt
WHERE CONTAINS(nn, 'test')
I get the error
Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'tt' because it is
not full-text indexed.
How can I set full-text index?
MadhivananHi
Just rey this way
EXEC sp_fulltext_column 'tt', 'nn', 'add'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Madhivanan" wrote:

> When I run the query,
> SELECT nn
> FROM tt
> WHERE CONTAINS(nn, 'test')
> I get the error
> Server: Msg 7601, Level 16, State 2, Line 1
> Cannot use a CONTAINS or FREETEXT predicate on table 'tt' because it is
> not full-text indexed.
> How can I set full-text index?
> Madhivanan
>|||When I execute that I get the error
Server: Msg 15601, Level 16, State 1, Procedure sp_fulltext_column,
Line 13
Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable Full-Text Search.
Madhivanan|||check this URL
http://msdn.microsoft.com/library/d...r />
_0kjd.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Madhivanan" wrote:

> When I execute that I get the error
> Server: Msg 15601, Level 16, State 1, Procedure sp_fulltext_column,
> Line 13
> Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable Full-Text Search.
>
> Madhivanan
>|||can I enable fulltext indexing with os win xp pro?
"Chandra" wrote:
> Hi
> Just rey this way
> EXEC sp_fulltext_column 'tt', 'nn', 'add'
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Madhivanan" wrote:
>

Contains Not Working

Hello,
I have done the following as per BOL on a text field
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'TextSearchBody',
'create'
EXEC sp_fulltext_table 'NewsItems',
'create',
'TextSearchBody',
'PK_newsItems'
EXEC sp_fulltext_column 'NewsItems',
'Body',
'add'
EXEC sp_fulltext_table 'NewsItems',
'activate'
GO
EXEC sp_fulltext_catalog 'TextSearchBody',
'start_full'
After doing this the query
SELECT body
FROM newsitems
WHERE contains (body, 'Triumph' )
does not return any rows.
Any idea what else can I do to get it working. All help appriciated.
1) check the application log in event viewer for any messages from MSSearch
or MSSCI.
2) issue the below queries in your full text enabled database.
select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
go
select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
go
select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Anoop" <Anoop@.discussions.microsoft.com> wrote in message
news:3DE601E2-ACA0-4789-ACE7-E42F5945AC73@.microsoft.com...
> Hello,
> I have done the following as per BOL on a text field
> EXEC sp_fulltext_database 'enable'
> EXEC sp_fulltext_catalog 'TextSearchBody',
> 'create'
> EXEC sp_fulltext_table 'NewsItems',
> 'create',
> 'TextSearchBody',
> 'PK_newsItems'
> EXEC sp_fulltext_column 'NewsItems',
> 'Body',
> 'add'
> EXEC sp_fulltext_table 'NewsItems',
> 'activate'
> GO
>
> EXEC sp_fulltext_catalog 'TextSearchBody',
> 'start_full'
> After doing this the query
> SELECT body
> FROM newsitems
> WHERE contains (body, 'Triumph' )
> does not return any rows.
> Any idea what else can I do to get it working. All help appriciated.
>
>
|||Hillary,
I have looked at the event log and there are warning in the log and the details are as follows
Event id :3024 , category : gatherer
and discription :The crawl for project <SQLServer$TST1 SQL0002600005> could not be started, because no crawl seeds could be accessed. Fix the errors and try the crawl again.
Also the output of the Select statements is as follows
select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
go
NULL
select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
go
0
select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
go
1
Please let me know if you need more info.
Thanks
"Hilary Cotter" wrote:

> 1) check the application log in event viewer for any messages from MSSearch
> or MSSCI.
> 2) issue the below queries in your full text enabled database.
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
> go
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:3DE601E2-ACA0-4789-ACE7-E42F5945AC73@.microsoft.com...
>
>
|||Hi Hillary,
I have now checked the error on MSDN and changed the login account being used to start the search and it seems to be all working.
Thanks for the help.
Anoop
"Hilary Cotter" wrote:

> 1) check the application log in event viewer for any messages from MSSearch
> or MSSCI.
> 2) issue the below queries in your full text enabled database.
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
> go
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:3DE601E2-ACA0-4789-ACE7-E42F5945AC73@.microsoft.com...
>
>

Cont see Cube in AS 2005

Hi

I have built a cube in Analyses Services 2005 and the cube is working.

I can browse the cube in Management Studio.

But when making a new connection to the cube in excel I cant see the cube.

I can establish a connection to the server but when I need to select the cube in the drop down the list is empty.

This is a problem connecting to the cube on the dev server and when connecting to my local cube.

Does anybody have an idea what can be causing this?

Thanks

You will need this add in http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en for SSAS2005.

HTH

Thomas Ivarsson

|||

Sorry, I think my first post was a bit misleading.

This is an error when I try to connect to the dev server or my local AS.

I don’t want to connect to both at once.

|||

The link will give you the correct driver/software update for connectin to SSAS2005 from Excel 2003.

Regards

Thomas Ivarsson

constraint in Trigger

I do not know this is the correct way to do this, but somehow this
isnt working. All I want is not to have a null value in field A if
there is a value in field B

heres the code

CREATE TRIGGER tiu_name ON tblName
FOR INSERT, UPDATE
AS
DECLARE @.FieldA AS REAL, @.FieldB AS REAL;

SELECT @.FieldA=FieldA, @.FieldB=FieldB
FROM Inserted;

IF (@.FieldB IS NOT NULL) AND (@.FieldA IS NULL)
RAISERROR('Error Message',1,2);
GO

Please Help.(jay_wic@.yahoo.com) writes:

Quote:

Originally Posted by

I do not know this is the correct way to do this, but somehow this
isnt working. All I want is not to have a null value in field A if
there is a value in field B
>
heres the code
>
CREATE TRIGGER tiu_name ON tblName
FOR INSERT, UPDATE
AS
DECLARE @.FieldA AS REAL, @.FieldB AS REAL;
>
SELECT @.FieldA=FieldA, @.FieldB=FieldB
FROM Inserted;
>
IF (@.FieldB IS NOT NULL) AND (@.FieldA IS NULL)
RAISERROR('Error Message',1,2);
GO

A common error with triggers: you assume that they fire once per row,
when they in fact fire once per statement. Thus, you cannot select into
variables, but you must work with the inserted table directly:

IF EXISTS (SELECT *
FROM inserted
WHERE fieldB IS NOT NULL and fieldA IS NULL)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Error message', 16, 1)
END

Note two other changes:

o Added ROLLBACK TRANSACTION to rollback back the statement that fired
the trigger.
o Increased the severity level from 1 to 16 in the RAISERROR statement.
Level 1-10 are informational only. Level 11 or higher raises an error.

Finally, there is a simpler solution, without a trigger, in this case.
Just add a table constraint:

CONSTRAINT ckt_nullcheck CHECK
(NOT (fieldB IS NOT NULL AND fieldA IS NULL))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx