Database

Rob Graham7-May-06 6:04

7-May-06 6:04

without seeing the stored procedure you are using, this will be difficult to guess.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

TheEagle7-May-06 6:56

7-May-06 6:56

Thank you for your respond...

The stored procedure code:

CREATE PROCEDURE SearchProject
(
@SearchString varchar(500),
@CultureName varchar(50),
@HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName varchar(1000),
ProjectDescription varchar(1000),
ProjectImage varchar(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

As i said before :this stored procedure was working fine with english words.

Rob Graham7-May-06 7:08

7-May-06 7:08

As a first step, try changing the data types in the SP and the internal table from varchar to nvarchar, so they expect unicode text rather than ansi text...

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

TheEagle7-May-06 7:14

7-May-06 7:14

Should i change the Data Type for all the tables used in the stored procedure or only in the #SearchTable table?

Rob Graham7-May-06 7:19

7-May-06 7:19

At least the #searchtable, and the fields being searched in the other tables.
varchar stores only single byte text, nvarchar stores Unicode. Any UI used to display nvarchar needs to be unicode aware and select the appropriate locale and a font that supports the needed characters. Enterprise manager and query analyzer may default to both a locale and font that results in some characters being displayed as an empty 'box' character...

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

-- modified at 13:19 Sunday 7th May, 2006

TheEagle7-May-06 7:24

7-May-06 7:24

Thank you very much..

I will try it and then i will tell you about the results Smile | :)

TheEagle7-May-06 16:55

7-May-06 16:55

Hi..

I have changed all the data types used by the stored procedure (all the tables and the #SearchTable).

Each time i modify a data type for a table the compiler tell me to repopulate the fulltextIndex for that table and that what i did.

But after all these modifications i got the same error at the SqlAnalyzer.

Is there another thing i should do?

Rob Graham7-May-06 17:24

7-May-06 17:24

Look here[^]. The last section on Noise words might be pertinent...

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

Rob Graham7-May-06 17:45

7-May-06 17:45

This article[^] may also have some useful info

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

TheEagle8-May-06 6:17

8-May-06 6:17

Hi..
When i used the code (part of the procedure):

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)

With arabic words(inputs:@CultureName,@SearchString) it works fine but when i call the whole stored procedure(SearchProject) i got the error i mentioned before..I think the problem exists in the line :

INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)

But i dont know what is the problem and how to solve it?Is there a problem with cultures in the temporary table?

datetime Comparision

DotNetDominator6-May-06 18:52

6-May-06 18:52

Hello,

I have some problem with my Simple query. I just want to compare the DateTime value to a given range.
Here i am giving my query and result. In Result, I do not understand why is selects 2 and 3 numbered rows. Any Other way to Compare datetime value to Date And Time of the field of a table??

SELECT *
FROM dbo.Table
WHERE ((CAST('5/6/2006 5:08:00 AM' AS DATETIME) BETWEEN StartDateTime AND EndDateTime)
OR CAST('5/6/2006 5:08:59 AM' AS DATETIME) BETWEEN StartDateTime AND EndDateTime)

RESULT :-
1 2006-05-06 05:04:00.000 2006-05-08 05:05:00.000
2 2006-05-06 05:07:00.000 2006-05-08 05:08:00.000
3 2006-05-06 05:07:00.000 2006-05-08 05:08:00.000

Thank you

Rob Graham7-May-06 5:23

7-May-06 5:23

Are you sure you posted this correctly? The first datetime in the query is satisfied by the values in 2 & 3, but neither value is saisfied by row 1 in the return.
Note that BETWEEN is inclusive:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
if you want to exclude the start and end values, use

SELECT *
FROM dbo.Table
WHERE ((CAST('5/6/2006 5:08:00 AM' AS DATETIME) > StartDateTime)
      AND (CAST('5/6/2006 5:08:00 AM' AS DATETIME) < EndDateTime))

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

DotNetDominator7-May-06 10:50

7-May-06 10:50

Hi,
Thanks for the Reply.

I have tried everyway. But Still have not been successfull.

SELECT *
FROM dbo.Table
WHERE (((CAST('5/7/2006 1:10:00 AM' AS DATETIME)) > dbo.LoadTestscheduler.StartDateTime AND (CAST('5/7/2006 1:10:00 AM' AS DATETIME)) < dbo.LoadTestscheduler.KillDateTime))

Result:-

0 0 2006-05-07 01:03:00.000 2006-05-09 01:04:00.000

Rob Graham7-May-06 11:09

7-May-06 11:09

I don't see your problem:

'5/7/2006 1:10:00 AM' is later than (>) 2006-05-07 01:03:00.000

AND

'5/7/2006 1:10:00 AM' is earlier than (<) 2006-05-09 01:04:00.000

Are you trying to compare just the time values, irrespective of the date?

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

DotNetDominator7-May-06 11:24

7-May-06 11:24

Thanks for Quick Reply.

My Query is Long and involves join of two table. But, I found this perticular area where problem lies. I do not understand why it is not comparing with time. I want it to compare with date as well as time.

From Some of the Web site I found that I can compare Binary Values datetime field. But, when i do so, I am getting Arithmatic Overflow error. The link below has information for converting into binary. But, I does not work for me.

http://www.sql-server-performance.com/fk_datetime.asp

making even AM to PM or visa versa also does not work.

SELECT *
FROM dbo.LoadTestscheduler
WHERE '2006/05/07 1:10:00 PM' > dbo.LoadTestscheduler.StartDateTime AND '2006/05/07 1:10:00 PM' < dbo.LoadTestscheduler.KillDateTime

Can you tell me How can i convert my date to a perticular format?? Do I have to use CONVERT With/Without or In addition to CAST??

Thank you.

Rob Graham7-May-06 12:40

7-May-06 12:40

DotNetDominator wrote:
My Query is Long and involves join of two table. But, I found this perticular area where problem lies. I do not understand why it is not comparing with time. I want it to compare with date as well as time.

From the examples you provided, it does appear to be working correctly, for the full datetime value (note the bolded date values in my previous post, the date difference makes the answer valid for the given query, since the supplied date-time falls between the two date-times in the returned row.)

Why do you think it is not working? I don't seem to understand what you are trying to accomplish. Perhaps some more examples (both good and bad) would clear this up for me.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

DotNetDominator7-May-06 15:40

7-May-06 15:40

Hi,

My Problem is solved. Credit goes to you. I was not really looking in to Day when i compared my value with start and end date. There was some error in my Logic.

I am really sorry for giving unnecessary trouble. I am really surprised, How can i make such foolish thing..

Thank you very for your help.

Rob Graham7-May-06 15:56

Re: SQL database batch files

7-May-06 15:56

No problem. It's easy to overlook simple things in one's own code (I do it more often than I'd care to admit). That is one of the great values that comes from having others review your code (particularly when having a problem) - they will often see things you are temporarily blind to.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

SQL database batch files

abdelhameed816-May-06 9:43

abdelhameed81

6-May-06 9:43

hi all i would like to know how to create a batch file that carries the schema and the data of database and run it on any pc to add the database with all the data in it in the SQL sever
i have seen such files but the idea of how to create one
thank u all

abdelhameed81

Rob Graham7-May-06 5:36

Deleting from an SQL database

7-May-06 5:36

You can use SQL Enterprise Manager to script the database: select the tree node for the database you want to script, right click. Select 'All Tasks' then 'Generate SQL Scripts'
This will callup a dialog which will allow you to select which schema objects you want in the script, and what format you want for the script file (warning: the default is Unicode Text, change it if you want pain text).

To script the data, you must either write your own tool to "export" the data as text (INSERT Statements) or use the bulk copy tool (bcp) to export and import the data.

A simple and faster method of creating a copy of yur database (complete with data) is simply detach the database for the development server, copy the log and data files to the new server, and attach to the new server, specifing new locactions for the files if necessary. (you can include the data and log files in your installer, and run a script to attach them, if need be.)

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

alee15.10.886-May-06 1:52

alee15.10.88

6-May-06 1:52

Hi all,

I am doing a program with c# in which I am connecting it to an SQL database.
I have a button (called btnDelete) in which in it I have the following code:-

string query = ("DELETE FROM tblUsers WHERE User_name = ("+ txtDelete.Text +")");

SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();

//Calling a method to update the database
UpdateDataIntoDatabase();

MessageBox.Show( "Deleted." );

When I run the program and click the delete button the following error occurs pointing in the myCommand.ExecuteNonQuery() part:-

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.

Can some one please tell me what do I have because I can't see anything wrong!

Thanks a lot guys

Re: Deleting from an SQL database

DotNetDominator7-May-06 2:02

Re: Deleting from an SQL database

7-May-06 2:02

Hi,

replace your String Query to one given here.

string query = "DELETE FROM tblUsers WHERE User_name = '" + txtDelete.Text + "'";

I this should work now. Also, Just check your connection is open before you assign it to myCommand.

Thank you.

alee15.10.887-May-06 20:12

alee15.10.88

7-May-06 20:12

Ohh thanks... I arranged the quotes and it worked out

Thanks a lot!!

Re: Deleting from an SQL database

Rob Graham7-May-06 4:06