Frustrating: Can't get to Provider Options in SQL 2005 Express?

What are you trying to achieve?
Look at the sp_addlinkedserver system procedure.
"Thou shall lear to type. Clicking is the work of the Devil."
-- undisclosed apostle
;)
ML
http://milambda.blogspot.com/Thanks, but I have already used sp_addlinkedserver.
I am trying to set the "allow inprocess" provider options... according to
research in the NG's and BOL (see "Linked Server Properties (Provider
Options Page)"), this is set from a dialog that I am unable to access. I
can't see it when adding a new linked server, editing an existing one, or
right-clicking on the provider in Management Studio.
Any ideas?
Thanks,
Mike
"ML" <ML@.discussions.microsoft.com> wrote in message
news:99625079-C5D6-430C-89AB-8AD8421A2731@.microsoft.com...
> What are you trying to achieve?
> Look at the sp_addlinkedserver system procedure.
> "Thou shall lear to type. Clicking is the work of the Devil."
> -- undisclosed apostle
> ;)
>
> ML
> --
> http://milambda.blogspot.com/|||Hi,
I'm trying to get to the provider options screen in SQL2K5 Management
Studio, however I am unable to find it anywhere. This is really
frustrating... Here is what I am trying to do:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on a provider and select Properties.
However, all I can see when I right-click is "Refresh" ... How can I access
the provider options? Is this a limitation of the Express database? Is there
a stored proc I can use?
Thanks,
Michael|||What are you trying to achieve?
Look at the sp_addlinkedserver system procedure.
"Thou shall lear to type. Clicking is the work of the Devil."
-- undisclosed apostle
;)
ML
http://milambda.blogspot.com/|||Thanks, but I have already used sp_addlinkedserver.
I am trying to set the "allow inprocess" provider options... according to
research in the NG's and BOL (see "Linked Server Properties (Provider
Options Page)"), this is set from a dialog that I am unable to access. I
can't see it when adding a new linked server, editing an existing one, or
right-clicking on the provider in Management Studio.
Any ideas?
Thanks,
Mike
"ML" <ML@.discussions.microsoft.com> wrote in message
news:99625079-C5D6-430C-89AB-8AD8421A2731@.microsoft.com...
> What are you trying to achieve?
> Look at the sp_addlinkedserver system procedure.
> "Thou shall lear to type. Clicking is the work of the Devil."
> -- undisclosed apostle
> ;)
>
> ML
> --
> http://milambda.blogspot.com/

Front end website full-text search

I have a front-end search set up that takes the users input and runs a
stored procedure that uses FREETEXTTABLE to get the results. I want it
so that when a user types in a search that has words grouped together
inside quotes (i.e. "sql server") it does an AND instead of an OR. I
know that CONTAINSTABLE can have this kind of logic but I was wondering
if there is a way to handle this without having my script (ASP)
building the search phrase by parsing the user input.
Any ideas? Or has someone done this and have some advice.
Thanks.
But if it does an AND it will not be the same as "sql server" which searches
for the two words occurring adjacent to each other.
Here is a proc which you can set whether you want the entire phase, an OR,
or an AND.
CREATE PROCEDURE SearchSQL (@.stringin varchar(200), @.BooleanType int= NULL)
AS
-- a @.boolean type of null means a phrase based search
-- a @.boolean type of 0 means an OR type search
-- a @.boolean type of 1 means an AND type search
DECLARE @.holdingString VARCHAR(2000)
DECLARE @.whitespace INT
DECLARE @.boolean VARCHAR(10)
--returning a syntax message if no search phrase is passed
IF LEN(@.stringin)=0
BEGIN
PRINT 'usage is SimpleSQLFTSSearch ''Your Search Phrase goes here'''
RETURN -1
END
SET @.boolean=case WHEN @.booleantype=1 THEN char(34)+' OR ' + char(34)
WHEN @.booleantype=2 THEN char(34)+' AND ' + char(34)
ELSE ' ' END
DECLARE @.counter INT
DECLARE @.posold int
DECLARE @.posnew int
SET @.holdingstring='SELECT * FROM authors AS a JOIN
CONTAINSTABLE(authors,*,'''+char(34)
SELECT @.whitespace=LEN(@.stringin) - LEN(replace(@.stringin,' ',''))
SELECT @.posold=0
SELECT @.posnew=Charindex(' ',@.stringin)
WHILE @.whitespace >=0
BEGIN
IF @.whitespace=0
BEGIN
SELECT
@.holdingString=@.holdingString+SUBSTRING(@.stringin, @.posold+1,LEN(@.stringin)-@.
posold+1)+char(34)+char(39)+',200) AS t ON '
END
ELSE
BEGIN
SELECT @.holdingString = CASE WHEN LEN(SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1))>0 THEN @.holdingString+SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1)+@.boolean ELSE @.holdingstring END
SELECT @.posold=@.posnew, @.posnew=Charindex(' ',@.stringin, @.posold+1)
END
SELECT @.whitespace=@.whitespace-1
END
SELECT @.holdingString = @.holdingString + 't.[KEY]=a.au_id ORDER BY RANK
DESC'
--PRINT @.holdingstring
EXEC(@.holdingstring)
RETURN @.@.rowcount
Usage is:
DECLARE @.returncode int
EXEC @.returncode=SearchSQL 'this is a test'
PRINT @.returncode
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
"JT" <jtreuting@.gmail.com> wrote in message
news:1112316885.228212.171380@.f14g2000cwb.googlegr oups.com...
> I have a front-end search set up that takes the users input and runs a
> stored procedure that uses FREETEXTTABLE to get the results. I want it
> so that when a user types in a search that has words grouped together
> inside quotes (i.e. "sql server") it does an AND instead of an OR. I
> know that CONTAINSTABLE can have this kind of logic but I was wondering
> if there is a way to handle this without having my script (ASP)
> building the search phrase by parsing the user input.
> Any ideas? Or has someone done this and have some advice.
> Thanks.
>

From DataSet To SqlDataReader in a CLR Stored Procedure

Hi all,
I'm writing a CLR stored procedure that just execute a query using 2 parameters.

SqlContext.Pipe.Send can send a SqlDataReader, but if I've got a DataSet?
How can I obtain a SqlDataReader from a DataSet?

Dim command As New SqlCommand(.......)
.....
Dim ds As New DataSet()
Dim adapter As New SqlDataAdapter(command)

adapter.Fill(ds, "MyTable")

... 'manipulating the ds.Tables("MyTable")

At this moment I have to send the table...but
ds.Tables("MyTable").CreateDataReader()
just give me a DataTableReader, and i can't send it with SqlContext.Pipe.Send(...

Help me please!
The DataSet.CreateDataReader method can be used to generate a data reader that reads data from a dataset.|||DataSet.CreateDataReader() is the same of dataSet.Table(x).CreateDataReader()...it returns a DataTableReader!
There is also an overload for dataSet.CreateDataReader that get an array of dataTable as parameter!|||Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||Thank you, but i don't think that can be the "best practice", it's not in .net style!!!
I'll search better!|||? It used to accept IDataReader during the betas, all the way up to one of the final CTPs -- then that functionality was removed for some reason :( -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nicole Calinoiu@.discussions.microsoft.com> wrote in message news:7725b945-d2c1-44d0-a698-fa9a344a48d4@.discussions.microsoft.com...Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||

Well, at least I wasn't imagining things. ;)

Based on the current implementation, I'd have to guess that the change may have been made for 2 main reasons: improved performance and enhanced metadata extraction. However, I can't see any reason why an overload that accepts IDataReader couldn't have been left in since the "improved" SqlDataReader implementation would still be used where possible. A wee bit odd...

From Cursors to a simple Select

Hi all

There is a stored procedure that updates a "sales" table with the current "sales representative" taken from the "customers" table. I'm changing this mess of cursors into a simple update, my first approach was just to do a select (instead of update) just to verify that the rows selected were the ones that really needed to be changed. The select is very simple but is returning a cartesian product instead of just the 200+ (aprox) rows. I would appreciate if someone took a quick look at this select and see what I'm missing :) (I'm including the code for the cursor as well as the code for the select)

CURSOR:

CREATE PROCEDURE [ReCast_Salesman] AS
-- Recast salesman

Declare @.vsite_code varchar(2)
Declare @.vcustno varchar(10)
Declare @.vsalrep char(4)
Declare @.vcustfx smallint
Declare @.Ssite_code varchar(2)
Declare @.Scustno varchar(10)
Declare @.Ssalrep char(4)
Declare @.Scustfx smallint
Declare @.i int
Declare @.intcnt int
Declare @.cnt int
Declare @.vprodate datetime
Declare @.vpro varchar(20)
Declare @.vmesg varchar(30)
Declare @.vmesg2 varchar(30)
Declare @.vmesg3 varchar(30)
Declare @.UpdFlag varchar(1)
Declare @.Ucnt int
Declare @.icnt int

Set @.i = 0
Set @.cnt = 0
Set @.ucnt = 0
Set @.icnt = 0
Set @.UpdFlag = 'N'

Declare Customer_cur Cursor for
Select site_code, cust_no, cust_sffx, sales_rep
From Customer
where Active_Flag = 'A'
and sales_rep <> 'XXXX'
Order by site_code, cust_no, cust_sffx
For Read only

Open Customer_Cur

Fetch Next from Customer_Cur
into @.vsite_code, @.vcustno, @.vcustfx, @.vsalrep

While @.@.FETCH_STATUS = 0
Begin

Declare sales_cur Cursor for
Select site_code, cust_no, cust_sffx, salesrep
From Sales
Where site_code = @.vsite_code
and cust_no = @.vcustno
and cust_sffx = @.vcustfx
and salesrep <> @.vsalrep
Order by Site_code, cust_no, cust_sffx
For Update

Open Sales_cur

Begin

Fetch Next
from sales_cur
into @.ssite_code, @.scustno, @.scustfx, @.Ssalrep

While (@.@.fetch_status = 0)
Begin

-- If @.vsalrep <> @.Ssalrep

-- Begin
Update Sales
set salesrep = @.vsalrep
Where current of Sales_cur

set @.ucnt = @.ucnt + 1
-- End

Fetch Next from sales_cur
into @.ssite_code, @.scustno, @.scustfx, @.Ssalrep

End

FetchNext:
deallocate sales_cur

Fetch Next
from Customer_Cur
into @.vsite_code, @.vcustno, @.vcustfx, @.vsalrep

End

End

set @.vprodate = getdate()
set @.vpro = 'Recast Salesman '
set @.vmesg = Str(@.ucnt) + ' Records Changed'

Insert into Batch
(batchdate, process, message )
Values (@.vprodate, @.vpro, @.vmesg )

Close Customer_Cur
deallocate Customer_Cur

SELECT:

use salesdatamart;
Select A.site_code,
A.cust_no,
A.cust_sffx,
A.salesrep,
From Sales As A
Inner Join Customer As B
On A.site_code = B.site_code AND
A.cust_no = B.cust_no AND
A.cust_sffx = B.cust_sffx AND
A.salesrep != B.sales_rep AND
B.sales_rep != 'XXXX' AND
B.active_flag = 'A'

Thanks in advance for your help :)

Luis TorresDDL and sample data?

Friendly DataSet Table Names

I have a stored procedure that returns a set of tables based on data in a table for a company. These tables are being used to create drop down lists for criteria selection for the client. We have a Javascript based control that will make use of these based on the control name. So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created. The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.

I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction.

I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)

Thanks,

Josh

Have you tried giving alias to the returned tables (result sets) as well as columns? For example:

create proc sp_RtnAlias
as
select EmployeeID EID from Employees as EmployeeID
go

Then you can access the result set using EmployeeID.EID

Hope I haven't misundstood you.

|||

Here's what I do... and I haven't decided if it is cheesey or not...

declare

@.tbltable(Ordinalintidentity(1,1),tableName varchar(50))

/* Insert a row into the temp table for each table returned by the query*/

INSERTINTO @.tblVALUES('Prospects')

INSERTINTO @.tblVALUES('AccountExecs')

INSERTINTO @.tblVALUES('Regions')

INSERTINTO @.tblVALUES('Products')

INSERTINTO @.tblVALUES('ProspectDescriptors')

INSERTINTO @.tblVALUES('Steps')

INSERTINTO @.tblVALUES('ProspectTypes')

INSERTINTO @.tblVALUES('VendorTypes')

INSERTINTO @.tblVALUES('Clients')

-- Index table

SELECT Ordinal, TableName

FROM @.Tbl

ORDERBY Ordinal

The rest of the sproc is a series of selects in the same order as they appear in the index table. I have method in the program that loops through the table objects and assigns each one the appropriate name from the index table. Note that the index table does not contain a reference to itself and can be disposed of when the other table names have been assigned...

FREETEXTTABLE

Hi..

I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:

Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.

The stored procedure :

CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)

SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO

I called the stored procedure using the following(in the Query Analayzer) :

USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT

but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:

SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')

Could any one help and tell me how to solve this problem?

http://support.microsoft.com/?kbid=892924

HTH

|||

Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.

The Search tool is working fine now in my website:www.engineeringprojects.net.

|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||

Sorry for being too late...

The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.

FREETEXTTABLE

Hi..

I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:

Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.

The stored procedure :

CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)

SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO

I called the stored procedure using the following(in the Query Analayzer) :

USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT

but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:

SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')

Could any one help and tell me how to solve this problem?

http://support.microsoft.com/?kbid=892924

HTH

|||

Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.

The Search tool is working fine now in my website:www.engineeringprojects.net.

|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||

Sorry for being too late...

The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.

FREETEXTTABLE

Hi..

I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:

Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.

The stored procedure :

CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)

SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO

I called the stored procedure using the following(in the Query Analayzer) :

USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT

but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:

SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')

Could any one help and tell me how to solve this problem?

http://support.microsoft.com/?kbid=892924

HTH

|||

Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.

The Search tool is working fine now in my website:www.engineeringprojects.net.

|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||

Sorry for being too late...

The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.

Freetext Search with Parameter

Hi All,
I am trying to perform a simple search but not sure what the best way is
to do it.
In my stored procedure I have 3 parameters.
1. Newspapers (if param is 0 search all)
2. Companies (if param is 0 search all)
3. Keywords
Is the only way to do these queries by using dynamic sql?
e.g.
@.SQL = @.SQL + 'Select * from Article a where freetext(a.*, @.Keyword)'
if @.Companies > 0
begin
@.SQL = @.SQL + ' and a.companies = ' + @.Companies
end
etc.
Can anyone help?
Thanks
Angela
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Angela,
If I correctly understand what you're trying to accomplish, then using a
IF... THEN... ELSE construct might work for you, for example:
-- FTS and IF/THEN/ELSE construct
Declare @.searchstring varchar(255)
if(@.searchstring = '1')
SELECT * FROM Article
else
SELECT * FROM Article
WHERE FREETEXT(name, @.searchstring)
-- and so, on...
If the above doesn't work for you, then try using a CASE statement. If
possible, could you post your stored proc code?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"angela" <angela@.anon.com> wrote in message
news:eYM#MGgDFHA.2572@.tk2msftngp13.phx.gbl...
> Hi All,
> I am trying to perform a simple search but not sure what the best way is
> to do it.
> In my stored procedure I have 3 parameters.
> 1. Newspapers (if param is 0 search all)
> 2. Companies (if param is 0 search all)
> 3. Keywords
> Is the only way to do these queries by using dynamic sql?
> e.g.
> @.SQL = @.SQL + 'Select * from Article a where freetext(a.*, @.Keyword)'
> if @.Companies > 0
> begin
> @.SQL = @.SQL + ' and a.companies = ' + @.Companies
> end
> etc.
> Can anyone help?
> Thanks
> Angela
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for the reply John,
Here's my SP so far, which works but is there another way (i.e. not
using dynamic sql). On my asp page, if the user select 'Search ALL
companies' then a 0 is passed on to the SP. And like wise with
'Newspapers'
----
DECLARE @.CompanyID integer, @.NewspaperID integer, @.Keyword varchar(600)
DECLARE @.SQL varchar(8000)
SET @.SQL = ''
SET @.SQL = @.SQL + 'Select * from Article a where freetext(a.*,
@.Keyword)'
IF @.CompanyID > 0
BEGIN
SET @.SQL = @.SQL + ' and a.companies = ' + CAST(@.CompanyID as
varchar)
END
IF @.NewspaperID > 0
BEGIN
SET @.SQL = @.SQL + ' and a.newspapers = ' + CAST(@.NewspaperID as
varchar)
END
EXEC @.SQL
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Angela,
I've not forgotten this issue, as I'm currently working on other issues at
this time. Could you also post the sp_help Article output and a small sample
of the data?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"angela" <angela@.anon.com> wrote in message
news:eY0IypoDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply John,
>
> Here's my SP so far, which works but is there another way (i.e. not
> using dynamic sql). On my asp page, if the user select 'Search ALL
> companies' then a 0 is passed on to the SP. And like wise with
> 'Newspapers'
>
> ----
> DECLARE @.CompanyID integer, @.NewspaperID integer, @.Keyword varchar(600)
> DECLARE @.SQL varchar(8000)
>
> SET @.SQL = ''
> SET @.SQL = @.SQL + 'Select * from Article a where freetext(a.*,
> @.Keyword)'
> IF @.CompanyID > 0
> BEGIN
> SET @.SQL = @.SQL + ' and a.companies = ' + CAST(@.CompanyID as
> varchar)
> END
> IF @.NewspaperID > 0
> BEGIN
> SET @.SQL = @.SQL + ' and a.newspapers = ' + CAST(@.NewspaperID as
> varchar)
> END
> EXEC @.SQL
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!