I have an ASP page which will fetch records from a SQL server DB table. The table "order_master" has a field called order_date. I want to frame a select query to fetch order date > a date entered by user(ex : 07/01/2008) I tried with convert and cast, but both are not working. The sample data in order_date column is 4/10/2008 8:27:41 PM...
I have SQL data that looks like this events id name capacity 1 Cooking 10 2 Swimming 20 3 Archery 15 registrants id name 1 Jimmy 2 Billy 3 Sally registrant_event registrant_id event_id 1 3 2 3 3 2 I would like to select all of the fields in 'ev...
We are moving our database server to a bigger box. I have several databases with full text indexes. What is the best way to move the full text indexes? ...
I'm attempting to fulfill a rather difficult reporting request from a client, and I need to find away to get the difference between two DateTime columns in minutes. I've attempted to use trunc and round with various formats and can't seem to come up with a combination that makes sense. Is there an elegant way to do this? If not, is th...
I'm using the freeodbc++ library to access data on a MS SQL Server 2000 database (SP3? SP4?). In particular, I'm running a particularly long and nasty stored procedure. I can watch the procedure execute in SQL Profiler, however, it tends to stop processing at a certain point. No error codes or exceptions thrown. If I comment out the ...
Can someone tell me what the statuses mean in SQL Server's sp_who command? Why might a spid be suspended? What does it mean to be "runnable"? Thanks! ...
I tried searching around but couldn't find anything that would help me out. I'm trying to do this in SQL: declare @locationType varchar(50); declare @locationID int; SELECT column1, column2 FROM viewWhatever WHERE CASE @locationType WHEN 'location' THEN account_location = @locationID WHEN 'area' THEN xxx_location_area = @locat...
I need to index a varchar field on my table in MS SQL Server 2005, but it's not clear to me how to do so. If I try to add a non-clustered index on the field, it says "Column 'xxxx' in table 'mytable' is of a type that is invalid for use as a key column in an index" My table has an auto-increment int ID that is set as the primary key on...
I continually get these errors when I try to update tables based on another table. I end up rewriting the query, change the order of joins, change some groupings and then it eventually works, but I just don't quite get it. What is a 'multi-part identifier'? When is a 'multi-part identifier' not able to be bound? What is it being bound t...
I need a SQL query that returns ContactDate, SortName, City, ContactType, and Summary from the tables below. If any value is null, I need it to return the text “No Entry”. ContactTable ContactID ContactDate UserID Summary ContactType SortName UserTable UserID FirstName LastName AddressID AddressTable AddressID City Street ...
Why won't my connection string to SQL server work with Windows authentication? A sql user works fine, acme\administrator or [email protected] won't work. This is a Win Form app written in C#. { OdbcConnection cn = null; String connectionString; connectionString = "Driver={SQL Server};Server=" + cbxDa...
I want to convert a string like this: '10/15/2008 10:06:32 PM' into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. Th...
I have db table with parent child relationship as: NodeId NodeName ParentId ------------------------------ 1 Node1 0 2 Node2 0 3 Node3 1 4 Node4 1 5 Node5 3 6 Node6 5 7 Node7 2 Here parentId = 0 means that it is a root level node. N...
I'm looking for the best SQL book or free tutorial on the web. I know I can Google the tutorial, but picking your brains is easier and it always produces better results. All suggestions are welcome; free is always better. ...
Is it less efficient to use TEXT than varchar in an SQL database? If so why? If not why would you not just always use TEXT? I'm not targetting a specific database here but oracle is probably the most relevant, although I'm testing on MySQL for the time being as part of a proof of concept. ...
I have a large query in a PostgreSQL database. The Query is something like this: SELECT * FROM table1, table2, ... WHERE table1.id = table2.id... When I run this query as a sql query, the it returns the wanted row. But when I tries to use the same query to create a view, it returns an error: "error: column "id" specified more than o...
I was wondering peoples opinions on the naming of ID columns in database tables. If I have a table called Invoices with a primary key of an identity column I would call that column InvoiceID so that I would not conflict with other tables and it's obvious what it is. Where I am workind current they have called all ID columns ID. So the...
I have 3 tables (archive has many sections, section (may) belong to many archives): archive id PK description archive_to_section archive_id PK FK section_id PK FK section id PK description What would the SQL look like to list all the sections that belong a certain archive id? I am just learning SQL. From what I've read it s...
I'm using MS SQL Server 2005. Is there a difference, to the SQL engine, between SELECT * FROM MyTable; and SELECT ColA, ColB, ColC FROM MyTable; When ColA, ColB, and ColC represent every column in the table? If they are the same, is there a reason why you should use the 2nd one anyway? I have a project that's heavy on LINQ, and I...
If I am executing a stored procedure for basic queries, such as: SELECT ColA, ColB FROM MyTable WHERE ID = 123; SELECT * FROM MyTable,OtherTable WHERE MyTable.ID = OtherTable.ID ORDER BY CreatedAt desc Is there any benefit to converting those to a stored procedure if that happen frequently? When is it better to use a stored proc? Wh...