Problem With Date Search Plz Help Me Quickly
Oct 22, 2007
I am trying to search for stored files "for example from date: 15/12/2003 to: 24/6/2006" and when i press search no results appeare the following is the database code:
1 public DataTable searchData(string fileNo, string Title, string dFrom, string dTo, string brief)
2 {
3 string str = "";
4
5 str = "select * from Tb_File where Active = 1 ";
6
7 if (fileNo != "")
8 str += " and FileNo='" + fileNo + "'";
9 if (Title != "")
10 str += " and Title like '%" + Title + "%' ";
11 if (brief != "")
12 str += " and Brief like '%" + brief + "%' ";
13 if (dFrom != "")
14 str += " and DFrom >= convert(datetime,'" + Convert.ToDateTime(dFrom).ToShortDateString() + "',103) ";
15 if (dTo != "")
16 str += " and DTo < convert(datetime,'" + Convert.ToDateTime(dTo).ToShortDateString() + "',103) ";
17
18 ole.Open();
19 SqlDataAdapter DA = new SqlDataAdapter(str, ole);
20 DataTable DT = new DataTable();
21 DA.Fill(DT);
22 ole.Close();
23 return DT;
24
25 }
i am using sql 2000, with Visual Studio 2005.
View 14 Replies
ADVERTISEMENT
Apr 23, 2008
I have a database with a couple of tables i need to expand to 4 gigabytes in order to run some tests. (currently 300 megs)
Does anyone have a script or some method that would quickly populate my tables with random data so that i can grow my database to the desired size for testing.
Thanks
I have SQL server 2005 express. I have the management studio installed too.
View 4 Replies
View Related
Mar 20, 2007
We have a server with some pesky RAID5 - which has on 3 separate occasions corrupted the Databases when a drive failed. So we had a maintenance window and decided to change it to RAID10.
We started the configuration at 13:00 today, its now 18:00 and it has done 25% ... is that normal?
Its 8 disks (so 4 mirror-pairs), it will have around 300GB of usable space when its done.
What would happen if we needed to do this in a time critical window? (for this debacle we have moved the database onto the Web Server, so we can survive for a few more hours ...)
Kristen
View 4 Replies
View Related
Jun 15, 2007
Hi, All:
I know oracle SQL, now I need to do a lot of SQL query on Microsoft SQLSERVER, can any one point out any place that I can find out the syntax of SQLserver SQL statement? Since this is just a short term assignment, so I don't want to buy a book, just hoping I can learn something quickly from online. I don't need learn anything deep, just need to know some simple syntax so I can do join, count, concatenate, min(), max(), sum () etc.
thanks in advance.
View 5 Replies
View Related
Jan 6, 2005
when i use enterprise manager, I type the following code to retrieve the date, can you check ?
SELECT *
FROM Table
WHERE (ExpirationDate BETWEEN 11 / 1 / 2004 AND 2 / 1 / 2005)
Thanks.
View 5 Replies
View Related
Mar 26, 2007
Good day,
I have a table of approximately 10 million rows. The table has 3 field making up the key, namely:
ID, Date, Program
I need to extract all the distinct Program's from the table.
I have don so with:
Select distinct Program from table
This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?
Thanks in advance.
View 14 Replies
View Related
May 19, 2004
There have been several threads about changing a database's collation but none have come up with an easy answer before.
The suggestion before was to create an empty database with the correct collation and then copy the data across.
However this is hard work as you have to populate tables in a specific order in order not to violate foreign keys etc. You can't just dts the whole data.
There follows scripts we have written to do the job. If people use them, please could you add to this thread whether they worked successfully or not.
Firstly we change the default collation, then change all the types in the database to match the new collation.
===================
--script to change database collation - James Agnini
--
--Replace <DATABASE> with the database name
--Replace <COLLATION> with the collation, eg SQL_Latin1_General_CP1_CI_AS
--
--After running this script, run the script to rebuild all indexes
ALTER DATABASE <DATABASE> COLLATE <COLLATION>
exec sp_configure 'allow updates',1
go
reconfigure with override
go
update syscolumns
set collationid = (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
where collationid <> (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
go
exec sp_configure 'allow updates',0
go
reconfigure with override
go
===================
As we have directly edited system tables, we need to run a script to rebuild all the indexes. Otherwise you will get strange results like comparing strings in different table not working.
The indexes have to actually be dropped and recreated in separate statements.
You can't use DBCC DBREINDEX or create index with the DROP_EXISTING option as they won't do anything(thanks to SQL Server "optimization").
This script loops through the tables and then loops through the indexes and unique constraints in separate sections. It gets the index information and drops and re-creates it.
(The script could probably be tidied up with the duplicate code put into a stored procedure).
====================
--Script to rebuild all table indexes, Version 0.1, May 2004 - James Agnini
--
--Database backups should be made before running any set of scripts that update databases.
--All users should be out of the database before running this script
print 'Rebuilding indexes for all tables:'
go
DECLARE @Table_Name varchar(128)
declare @Index_Name varchar(128)
declare @IndexId int
declare @IndexKey int
DECLARE Table_Cursor CURSOR FOR
select TABLE_NAME from INFORMATION_SCHEMA.tables where table_type != 'VIEW'
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
INTO @Table_Name
--loop through tables
WHILE @@FETCH_STATUS = 0
BEGIN
print ''
print @Table_Name
DECLARE Index_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
not exists(Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @Table_Name AND (CONSTRAINT_TYPE = 'PRIMARY KEY' or CONSTRAINT_TYPE = 'UNIQUE') and
CONSTRAINT_NAME = name)
order by indid
OPEN Index_Cursor
FETCH NEXT FROM Index_Cursor
INTO @IndexId, @Index_Name
--loop through indexes
WHILE @@FETCH_STATUS = 0
begin
declare @SQL_String varchar(256)
set @SQL_String = 'drop index '
set @SQL_String = @SQL_String + @Table_Name + '.' + @Index_Name
set @SQL_String = @SQL_String + ';create '
if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsUnique')) =1)
set @SQL_String = @SQL_String + 'unique '
if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsClustered')) =1)
set @SQL_String = @SQL_String + 'clustered '
set @SQL_String = @SQL_String + 'index '
set @SQL_String = @SQL_String + @Index_Name
set @SQL_String = @SQL_String + ' on '
set @SQL_String = @SQL_String + @Table_Name
set @SQL_String = @SQL_String + '('
--form column list
SET @IndexKey = 1
-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @IndexKey <= 16 and INDEX_COL(@Table_Name, @IndexId, @IndexKey)
IS NOT NULL
BEGIN
IF @IndexKey != 1
set @SQL_String = @SQL_String + ','
set @SQL_String = @SQL_String + index_col(@Table_Name, @IndexId, @IndexKey)
SET @IndexKey = @IndexKey + 1
END
set @SQL_String = @SQL_String + ')'
print @SQL_String
EXEC (@SQL_String)
FETCH NEXT FROM Index_Cursor
INTO @IndexId, @Index_Name
end
CLOSE Index_Cursor
DEALLOCATE Index_Cursor
--loop through unique constraints
DECLARE Contraint_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
exists(Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @Table_Name AND CONSTRAINT_TYPE = 'UNIQUE' and CONSTRAINT_NAME = name)
order by indid
OPEN Contraint_Cursor
FETCH NEXT FROM Contraint_Cursor
INTO @IndexId, @Index_Name
--loop through indexes
WHILE @@FETCH_STATUS = 0
begin
set @SQL_String = 'alter table '
set @SQL_String = @SQL_String + @Table_Name
set @SQL_String = @SQL_String + ' drop constraint '
set @SQL_String = @SQL_String + @Index_Name
set @SQL_String = @SQL_String + '; alter table '
set @SQL_String = @SQL_String + @Table_Name
set @SQL_String = @SQL_String + ' WITH NOCHECK add constraint '
set @SQL_String = @SQL_String + @Index_Name
set @SQL_String = @SQL_String + ' unique '
if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsClustered')) =1)
set @SQL_String = @SQL_String + 'clustered '
set @SQL_String = @SQL_String + '('
--form column list
SET @IndexKey = 1
-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @IndexKey <= 16 and INDEX_COL(@Table_Name, @IndexId, @IndexKey)
IS NOT NULL
BEGIN
IF @IndexKey != 1
set @SQL_String = @SQL_String + ','
set @SQL_String = @SQL_String + index_col(@Table_Name, @IndexId, @IndexKey)
SET @IndexKey = @IndexKey + 1
END
set @SQL_String = @SQL_String + ')'
print @SQL_String
EXEC (@SQL_String)
FETCH NEXT FROM Contraint_Cursor
INTO @IndexId, @Index_Name
end
CLOSE Contraint_Cursor
DEALLOCATE Contraint_Cursor
FETCH NEXT FROM Table_Cursor
INTO @Table_Name
end
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
print ''
print 'Finished, Please check output for errors.'
====================
Any comments are very welcome.
View 1 Replies
View Related
Mar 6, 2008
I am very new to SSIS. Can someone give me a basic out line to this problem. I kind of understand control tasks, data flow, etc... but not in details(watched couple of webcasts). I need to see something like below in action to understand this better.
Basically, I need to process a flat csv file on daily basis and load it into a table. As I am loading the records, I will need to verify(on a key column) to see if record exists in table already. If so then just update the record otherwise insert a new record. When I find a record, I need to possibly do a checksum on a set of columns before I do update. So, only update if these set of columns are different from file vs. table. I also need to keep performance in mind as I am processing this record one at a time looking up this record. I am thinking this should be fairly easy but I am getting little lost in control tasks and dataflow as to what goes on what. By the way I am using visual studio 2005 and sqlserver 2005.
I would appreciate your help. thanks again. I dont mind an example solution file.
View 3 Replies
View Related
Jul 10, 2007
I have five small tables that I need to insert to a SQL CE database.
I am using the 2.0 Compact Framework with the 2.0 System.Data.SqlServerCe.
My table definition is dynamic so I never know it's design.
1- If I go Row by Row using an this.ExecuteNonQuery(_global, par); it takes about 26 seconds to insert 5 tables of 330 rows.
2- If a use
StringBuilder sbColumns = new StringBuilder();
foreach (DataColumn dc in table.Columns)
{
if (sbColumns.ToString() != "")
sbColumns.Append(",");
sbColumns.Append(dc.ColumnName);
}
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT " + sbColumns.ToString() + " FROM " + _tablename, m_con);
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
da.MissingMappingAction = MissingMappingAction.Passthrough;
da.InsertCommand = cb.GetInsertCommand();
da.Update(table);
da.Dispose();
it takes about 46 seconds.
How Can write it faster or is this fastest it can go?
Thanks
View 1 Replies
View Related
Sep 27, 2006
Hi,I have a application that I’m trying to do a search in a SQL database with a dateI can add, update or delete the date but when I do a search it doesn’t work. I did a store procedure to do the search and its working but it’s only working when I pass the parameter between quote like that ‘09/15/2006’ if I remove the quote it doesn’t work How do I configure my store procedure to automatically put the quote?OrHow do I configure my vb.net code to automatically add the quote? Thanks in advance Soft
View 6 Replies
View Related
Mar 5, 2008
Hi guys,
Am developing an application where I store the date in the database. I would like to search the date from the database table. Can anyone tell me how would the search string would look like?
The current string am using is: select Pid "ID", PName "Name", Address, DateOfBirth "Date Of Birth", Phone, emergencyContact "Emergency Contact", RegistrationDate "Registration Date", DischargeDate "Discharge Date" from patient_177681 Where RegistrationDate Like '%2005-3-4%'
But this string does not return any results and so does any other data in the Like % % string. Can anyone let me know how to compare two dates and return a result? Is there any in-built function to do this?
View 2 Replies
View Related
Mar 28, 2008
hello I have this code Select Waste.WasteName AS [Waste Name], InvoiceDetail.Volume, Branch.BranchLocation AS [Branch Location] From InvoiceHeader INNER JOIN InvoiceDetail ON InvoiceHeader.InvoiceNo = InvoiceDetail.InvoiceNo INNER JOIN Waste ON InvoiceDetail.WasteID = Waste.WasteID INNER JOIN Branch ON InvoiceDetail.BranchID = Branch.BranchID Where WasteName = 'Sludge' AND InvoiceDate >= '" + TextBox1.Text + "' AND InvoiceDate <= '" + TextBox2.Text + "'; The problem is everytime I will search for example InvoiceDate 03/27/2008, I need to add one day for example --> 03/28/2008 for me to be able to get that Invoicedate 03/27/2008.. What's do you think is the problem with my code? Thanks!
View 9 Replies
View Related
Oct 20, 2006
Hello guys I hope everyone is doing well I have a question. I have a database thats an ADP. on one of the forms I have a command button that has a stored procedure in it that will allow them to search by TM# or name or whatever, (I have the code below) I would like them to be able to do the samething but by date rather then TM# or Name, how would I go about doing that with a datetime datatype??
thank you
ALTER PROCEDURE dbo.Search_ActiveConditionals
(@Enter_TM# int)
AS SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, INVESTIGATOR_COND, REASONFORCOND
FROM dbo.ACTIVE_CONDITIONALS
WHERE (TM# = @Enter_TM#)
View 2 Replies
View Related
Jul 3, 2007
hi
i am using a calendar control. the format is mm-dd-yyyy
my table stores date in the following format-
mm/dd/yyyy hh:mm:ss
my search is based only on date(not time). how can i do it?
thanks
Jaimin
View 4 Replies
View Related
Nov 13, 2000
Good morning one and all,
I need to transfer a database (contining one table) containing over 35 million records from one server to another. I have two options at present :
(a) Use DTS to do the transfer
(b) Copy the mdf file across and sp_attach_db it
Does any1 have a better idea, or does any1 know which of the two methods will be the quickest?
TIA
Gurmi
View 1 Replies
View Related
Apr 8, 2015
I just did index defragmentation for some databases include MSDB . I notice there are 3 indexes from MSDB database that fragmented quickly ( I did rebuild last nite at 10 PM - > fragmentation level becomes zero but today at 9 am it become 80 % ).The indexes are backupsetuuid, backup media family uuid, backupmediasetuuid. I am thinking to set the fill factor for those indexes = 80 respectively.
View 6 Replies
View Related
May 1, 2015
This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.
There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.
e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!
Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.
My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?
This is what I have checked so far:
1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked.
2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans
View 9 Replies
View Related
Dec 15, 2006
We have a system that has 35 million conversations piled up. We didn't know to explicitly end the conversation once the processing has completed. Oops. Now, our production box has 35 mm sitting in the table, and we have run into the problem where the amount in sys.conversation_endpoints has exceeded memory and they are being dumped into tempdb, which is killing our disk space, thus bringing the box down. We have fixed the code to end the conversations, but we now have to end the conversations in a hurry. If we select one by one out of the table and end the conversation via END CONVERSATION, it is slow. Very slow. It will finish in a few months. :(
Does anyone know how to get rid of these conversations in a hurry? All of the messages have been applied to our system, so killing the conversations will (should) have no affect on the processed data. Something like a TRUNCATE statement?
Thank you so much in advance,
John Hennesey
View 5 Replies
View Related
May 18, 2007
Dear all
I am pretty new to SQL server 2005 and have the following issue:
I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format.
I am having trouble composing the stored procedure to do that.
Any help would be apreciated.
Sincerely
d
View 2 Replies
View Related
Oct 27, 2000
Hi,
The following is a VB code which I use it to check the existence of the record before I insert a new record
into my SQL 7.0 database. When I run the vb code, I got an error which is
"-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I couldn't figure out what's wrong with my code especially the WHERE clause.
Please help me look into this problem. TQ.
Private Sub AddDate()
Dim rstDate As ADODB.Recordset
Dim myDate As Date
Dim Criteria As String
myFileDate=#27-10-2000 16:00#
myDate = Format(myFileDate, "mm-dd-yyyy")
Criteria = "SELECT Date " _
& "FROM DateGen " _
& "WHERE Date = '" & myDate & "'"
Set rstDate = New ADODB.Recordset
With rstDate
.ActiveConnection = cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Criteria
End With
If rstDate.RecordCount > 0 Then
RecordExist = True
Else
RecordExist = False
rstDate.AddNew
rstDate("Date") = Format(myFileDate, "dd-mm-yyyy")
rstDate.Update
End If
rstDate.Close
End Sub
Regards,
May
View 1 Replies
View Related
Feb 28, 2001
Hi,
Dow do I search for a dat time in table like
select * from table where colDate = '1999-09-09+00:00:00.000'. This doesn't work so how to go abt it?
View 2 Replies
View Related
Nov 23, 2005
I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated
View 1 Replies
View Related
Apr 28, 2008
I am using SQL server to create a rather complicated client database for a nonprofit organization. I have access to an ancient version of the database in Access format, but would rather create a new database from scratch instead of "up-sizing" the old database. Although the old database is mostly useless, it contains a goldmine of names and addresses that I could use to populate the new database that I'm creating. My question is this: Is there any relatively easy way to cut and paste from external data sources into a new SQL database? For example, I would love to just select twenty rows of "first names" from the old database and then paste that into my new table. Can anyone suggest any quick and easy tricks for populating a new database with place-holder content? Thanks!
View 2 Replies
View Related
Mar 9, 2015
I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:
UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null
Currently it is taking around 6 hours.
Is there a quicker way to do this?
View 1 Replies
View Related
Nov 1, 2007
I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data.
Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger.
...
FirstPub as datetime
LastPub as datetime
FirstSale as datetime
LastSale as datetime
Name as varchar(50)
Addr as varchar(50)
DetailText as text....
I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field.
I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields.
I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided.
Questions:
1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures?
2) How to query either a specific date, as well as a range between the first and last date specified? Can it be done efficiently without needing additional screen fields for the range specification? If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem.
3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place?
4) Should we split the large text field to another table in our search database? It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field?
View 3 Replies
View Related
May 5, 2008
hi need help search in long date format
i have table like this
need to search date + time + minute
no second !
only date + time + minute no second
SELECT *
FROM tb_tb
WHERE (dateinb = CONVERT(DATETIME, '2008-05-05 14:58, 102))
dateinb
---------------------------
05/05/2008 14:51:03
05/05/2008 14:51:03
05/05/2008 14:51:03
05/05/2008 14:51:03
05/05/2008 14:53:03
05/05/2008 14:53:03
05/05/2008 14:53:03
05/05/2008 14:53:03
05/05/2008 14:56:03
05/05/2008 14:56:03
05/05/2008 14:56:03
05/05/2008 14:56:03
05/05/2008 14:58:09
05/05/2008 14:58:09
05/05/2008 14:58:09
05/05/2008 14:58:09
TNX
View 4 Replies
View Related
Aug 9, 2006
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
View 3 Replies
View Related
Mar 17, 2008
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression). 1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack?
View 4 Replies
View Related
Apr 5, 2004
i am trying to search an SQL database to retrieve all names from employee table who have a birthday today. this needs to automatically fill in the date parameter with the system date. this is what i have so far:
sub page_load(sender as object, e as eventargs)
dtmDate=DateTime.Now.ToString("M")
con = New SqlConnection("Server=Localhost;UID=******;PWD=*****;Database=Pubs")
cmd = New SqlCommand("Select fname, lname From Employee where dob='& dtmDate'", con)
con.open()
dtrBday = cmd.ExecuteReader()
rptBday.DataSource=dtrBday
rptBday.DataBind()
dtrBday.Close()
I know this isnt quite right. i get errors when it hits my repeater.
the error i am getting is :Syntax error converting string to smalldatetime data type.
if someone could give me a push in the right direction here it would be greatly appreciated.
View 2 Replies
View Related
Mar 28, 2015
Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous system daily which contains approximate 3M updated or new records for table_a. My job is to update table_a with the new data.
The initial solution is:
1 Create a table (table_b) which structur is as the same as table_a
2 Use BCP to import updated records into table_b
3 Remove outdated data in table_a:
delete from table_a inner join table_b on table_a.key_fileds = table_b.key_fields
4 Append updated or new data into table_a:
insert into table_a select * from table_b
As the test result, this solution is very inefficient. Step 3 costs several hours, e.g. How can I improve it?
View 9 Replies
View Related
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Jul 21, 2015
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....
View 4 Replies
View Related
Nov 6, 2014
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
View 0 Replies
View Related
