Hi,
This is my 1st thread. Hopefully somebody can help me with the full-text serach.
I enabled full text serach on sql server 2k and created a catalog for a table
I tried using the following sql statement but it doesn't seem to return any records although I used a like statement and got some records. Can anybody answer why this is so? Thanks!
Select * from table1 where contains (field1, '"ESTATE PLAN"')
Regards,
Ronald
The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@unitid int
AS
SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled
FROM tbl_units as A
INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid
WHERE A.UnitID = @unitID
GO
-----------------------------------------------------------------------------------------------------
I have a simple report with a group header, details and footer. How do I display a message to inform users when no data is returned when they enter a parameter which returns no records - at the moment my report returns a blank screen, it would be more useful if I could output the following
'Parameter Name' does not exist.
The following statement gives the error that multiple records are returned. I am attempting to pick one weight out of a group of three, the three have the same ResidentID and different dates in the table variable @WeightReportRaw. If I hard code the ResidentID, ResidentID = 6, it works fine. The variable @intMonth3 passes in a month number ans dosen't seem to be a problem.
What am I missing, Thanks in advance for any help.
update tblResidentWeightsTemp set FirstWeight =
(select RWeight from @WeightReportRaw as W where ResidentID = W.ResidentID and month(W.RWeightDate) = @intMonth3)
I'm trying to put the total number of records returned from from a query in the bottom of our report. I don't want to do a count(*) in my sql stmt.
thanks.
How can i get a Count of the number of records returned
Here is my code
Im using VS2005 connected to SQL Compact v3.1
myCmd.CommandType = Data.CommandType.Text
myCmd.CommandText = "Select * From tblParts"
Dim myDataAdapter As SqlCeDataAdapter = New SqlCeDataAdapter(myCmd)
Dim myDataSet As DataSet = New DataSet()
myDataAdapter.Fill(myDataSet)
TblPartsDataGrid.DataSource = myDataSet.Tables(0)
I have a function that opens a connection to an SQL database, issues a
SELECT command, and reads the records with an OleDbDataReader. As the
records are read, any that match certain criteria are deleted with a
DELETE command. Simplified example code is shown below:
Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed to
SQL Server, I get a database timeout error when attempting to do the
DELETE. I suspect the reason is that the connection the reader has open
has the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property
"Mode=Share Deny None". I wonder if there is some similar way to tell
SQL Server to allow editing of records that are open for reading with
an OleDbDataReader.
Any help would be appreciated.
In my database I have an Audit table, that keeps track of teams worked upon the same record in a workflow.I need to find out how many records have been returned to the first team for correction ?The column 'Status' is numbered from 1 to 6 and Column 'EditTime' saves the time when record has been edited.how many records have been returned for correction and identify those records.
View 9 Replies View RelatedHi, all experts here,
Thank you very much for your kind attention.
I got a problem with the records returned by inner join query. It is weird as in table a I with records r1, and in table b with records r2 (r1>r2), but the returned record number r3 is greater than r1? That is so strange. As the reasonable records returned should be less than r1?(inner join on attribute a). What probably is the cause of the problem? I am looking forward to hearing from you and thank you very much.
With best regards,
Yours sincerely,
I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
View 3 Replies View RelatedI would like to use sp_send_dbmail, but I only want to send mail if there are any records returned.
I have found some solutions, but you must always first check if there is any record and later you can call "sp_send_dbmail" and within you must again query database for results.
What I want to do is to query database just once, because I dont want to use server performance two times. Query is bit complicated.
Hi,
I'm using SqlDataSource control.
Is there a way to know how many records a query has returned?
In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.
Query
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
View 1 Replies View RelatedI have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
I am trying to pass several ids to use in a where clause. 1 For Each row as GridViewRow In gv_child.Rows
2 If row.RowType = DataControlRowType.DataRow Then
3 Dim chk as CheckBox = CType(row.FindControl("cb_Approve"),CheckBox)
4 If chk IsNot Nothing AndAlso chk.Checked Then
5 Dim id As Integer = CInt(CType(row.FindControl("lbl_id"),Label).Text)
6 ids &= "," & id
7 End If
8 End If
9 If ids.Length > 1 Then ids = ids.Substring(1)
10 'Submit to sql with ids as param...
11 Next
I am getting an exception. Error converting '38,39' to a column of datatype int. What am I doing wrong?
I need to retrieve records where the date is in between the current date and 4 days previous.
I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...
Can someone help out?
It's blue in SQL Studio but I can't find it in SQL Books on-line. Iwanted to use it as a field name but don't like using [].Is it reserved?
View 6 Replies View RelatedHi guys,
Just a newbie question here.
Do we really need to use the GO keyword?
Thanks,
MeTitus
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name.
At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
View 7 Replies View RelatedHi everyone,
I have a few classes representing my data layer for my ASP.Net 2.0 Web Application. For example, UserDAL, DocumentDAL, etc... All these classes derive from BaseDAL, which stores the SqlConnection in it. The DAL classes access it through a property. The property is defined like that:
private SqlConnection connection;public SqlConnection Connection{get{ if (connection == null) connection = new SqlConnection(); if (connection.State != ConnectionState.Open) { connection.ConnectionString = ConnectionString; connection.Open(); } return connection;}}
The constructor of each DAL class is overloaded and takes a SqlConnection object. So if I need to access two DAL classes, I pass the connection of the first, to the second to prevent opening and closing the connection again and again. Here is a sample: UserDAL userDAL = new UserDAL();
userDAL.DoSomethingAndAccessConnectionProperty();
// Now userDAL.Connection is opened. Pass the connection to the constructor of DocumentDAL
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
So, if I do the following:using (UserDAL userDAL = new UserDAL())
{
userDAL.DoSomethingAndAccessConnectionProperty();
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
}
I assume that the connection is removed from memory, even if the DocumentDAL class has also used it. Am I right? I want to be sure that there will be no open connections.Thanks
I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
Can this be done? What would the query look like?
I am looking for a list like:
bull
barrel
rodeo
western
cowboy
Any suggestions?
Thanks,
Rob
If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct?
ie,
SELECT DISTINCT fieldA, fieldB
FROM tableA
but not
SELECT fieldA, DISTINCT fieldB
FROM tableA
or
SELECT DISTINCT fieldA, DISTINCT fieldB
FROM tableA
thanks again, this is a great forum
ddave
hi guys i have a query that contains several table joins
when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds
is adding distict keyword or order by acts as an index for the query or what ?
.
.
.
.
here is my query :
SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2')
GROUP BY char_field1, char_field2_ar, subcat_id) b
ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2')
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j
Good luck for all the folks
Hi all,
I have two datatables in my database. The first table, named Books, has two columns: BookID and Author (BookID is the primary key). The second table, named Purchases, has three columns: PurchaseID, BookID, BuyerID (Purchase ID is the primary key).
The idea here is that the Books datatable contains information regarding the book and its author while the Purchases datatable contains information on who has purchased what book.
Now, say I want to write an SQL query to extract a list of all the authors who have written a book purchased by buyer X. How would I go about doing this without having any duplicate entries? I figured that the following would work:
SELECT DISTINCT * FROM Books INNER JOIN Purchases ON Books.BookID=Purchases.BookID
But this ends up generating duplicate BookIDs if the Purchases table contains several buyers who have bought that Book. I know I could use BookID rather than * in the above query and that would work, but in reality I'm dealing with more complex tables and I would rather keep the * in there to actually get all the data out in one go.
and how do I write an if phrase?
Shimi
I heard a claim recently at a SQL Server users group meeting that theTOP keyword forces sorting on a database server. I can't find anyreason this might be true and the Books Online say nothing about it.Can someone verify this claim one way or the other?
View 5 Replies View RelatedI am studying for the MSCE/MCDBA exam 70-229. In the book I am using("MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000Database Design and Implementation Exam 70-229, Second Edition") I amlooking at the section on the ANY/ALL keyword.<QUOTE>USE PubsSELECT TitleFROM TitlesWHERE Advance > ANY(SELECT AdvanceFROM Publishers INNER JOIN TitlesON Titles.Pub_id = Publishers.Pub_idAND Pub_name = 'Algodata Infosystems')This statement finds the titles that received an advance larger thanthe minimum advance amount paid by Algodata Infosystems (which, in thiscase, is $5,000). The WHERE clause in the outer SELECT statementcontains a subquery that uses a join to retrieve advance amounts forAlgodata Infosystems. The minimum advanceamount is then used to determine which titles to retrieve from theTitles table.</QUOTE>I don't understand why this references the "minimum advance". If yourun the subquery on its own, it returns the following values:5000.00005000.00005000.00007000.00008000.0000NULL[color=blue]>From my limited understanding, the "ANY" keyword applies to at least[/color]one value, but which one? How is this determined?Any help gratefully received.Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 4 Replies View RelatedI am in the process of importing an Oracle database into SQL Server.Once of the tables has a field called "EXEC".SQL Server seems to reject any queries that include that particularfield because EXEC is a keyword. For eg.SELECT ID, EXEC from USERSresults in a syntax error near keyword EXEC.I can't change the fieldname becuase it will require reworking of awhole bunch of scripts.What can I do to adjust the query?Bijoy
View 3 Replies View RelatedIs possible in SQLSERVER to use a syntax like this?select x,y from table_1 where (x,y) not in (select h,k from table_2)I've tried, but it doen't work.Do you know any workaround?Thank youFede
View 3 Replies View Related