I have two tables one has a three column composite key. The other needs to reference this composite key. What is the syntax for creating a foreign key which references multiple columns. I tried googleing, but I couldn't find it. ...
I have a query to the effect of SELECT t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 WHERE (associate t1,t2, and t3 with each other) GROUP BY t3.id LIMIT 10,20 I want to know to many total rows this query would return without the LIMIT (so I can show pagination information). Normally, I would use this query: SELECT COUNT(t3.id) F...
I have a database (NexusDB (supposedly SQL-92 compliant)) which contains and Item table, a Category table, and a many-to-many ItemCategory table, which is just a pair of keys. As you might expect, Items are assigned to multiple categories. I am wanting to all the end user to select all items which are ItemID | CategoryID -----------...
Hi, i need a report and i should use pivot table for it.Report will be group by categories .It is not good to use case when statement because there are many categories.u can think Northwind Database as sample and All Categories will be shown as Columns and Report will show customers preference among Categories.I dont know another soluti...
I've got some data files that are stored compressed on our company's server with a .Z extension (UNIX compress utility used to zip them down). Can SQL Server's BULK IMPORT operation read these files in that format? Or must I uncompress them before getting at the data? ...
We have a client/server application that consists of multiple EXEs. The data access layer is on the same physical tier as the client in a library shared by our EXE modules. ODBC and OleDB connection pools are managed per-process; are there techniques for sharing DB connections across processes (other than moving the data access layer to ...
I've been developing a winforms app tied to sql server. I haven't rebooted in a while. Today i rebooted and now I can't log into sql. I used every account I know and their passwords including one that was working just before i rebooted and i get a 'Login failed' . I did take the database I use offline just before starting and I do have b...
What datatype would be used for storing video files in sql server2000? ...
Having a table with a colum like: mydate DATETIME ... I have a query such as: SELECT SUM(foo), mydate FROM a_table GROUP BY a_table.mydate; This will group by the full datetime, including hours and minutes. I wish to make the group by, only by the date YYYY/MM/DD not by the YYYY/MM/DD/HH/mm. Anyone know how to do this? I can still d...
Is there a way to avoid row deletion on an specific table using constrains? I'd like to (for example) deny row deletion if the id is 0,1 or 2 This is in order to avoid users deleting master accounts for an application, and I'd like to avoid it even if someone tries it (by mistake) using sql directly. Thanks! EDIT: The whole idea of ...
Hi, I have an articles table and a categories table. I want to fetch 7 articles for each category. Currently I have this but it's terrible slow on large tables so it's not really a solution: SELECT id, title, categories_id, body, DATE_FORMAT(pubdate, "%d/%m/%y %H:%i") as pubdate FROM articles AS t WH...
Below is my stored procedure. I want use stored procedure select all row of date from tbl_member and insert 2 table. But it's not work. Some one can help me? Create PROCEDURE sp_test AS BEGIN SET NOCOUNT ON; Declare @A Varchar(255), @B Varchar(255), @C Varchar(255), @D int Declare Table_Cursor Cursor For select A, B, ...
Hi, I've got the following two SQL tables (in MySQL): Phone_book +----+------+--------------+ | id | name | phone_number | +----+------+--------------+ | 1 | John | 111111111111 | +----+------+--------------+ | 2 | Jane | 222222222222 | +----+------+--------------+ Call +----+------+--------------+ | id | date | phone_number | +----...
I'd like to insert a new field with a Default value using Visual C++ Code. I have wrote this: CADODatabase pDB; String strConnessione = _T("Provider=Microsoft.Jet.OLEDB.4.0;""Data Source="); strConnessione = strConnessione + "MioDatabase.mdb"; pDB.SetConnectionString(strConnessione); pDB.Open(); query.Format("ALTER TABLE TBProva ADD Fa...
hi, can I pass a cursor in a procedure? CURSOR BLT_CURSOR IS SELECT BLT.sol_id, BLT.bill_id, BLT.bank_id FROM BLT; Is my cursor. Procedure abc(i want to pass the cursor here) How do I do it. ...
Given the following table in SQL Server 2005: ID Col1 Col2 Col3 -- ---- ---- ---- 1 3 34 76 2 32 976 24 3 7 235 3 4 245 1 792 What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium...
I'm working on a site similar to digg in the respect that users can submit "stories". I keep track of how many "votes" and "similar adds" each item got. Similar adds are defined as two users adding the same "link". Here is part of the algorithm (essentially the most important): y = day number sy = number of adds on day y ∑ y[1:10]...
For example I have two tables. The first table is student while the second table are the courses that the a student is taking. How can I use a select statement so that I can see two columns student and courses so that the courses are separated by commas. Thanks. ...
Is it possible to select column data using the ordinal_position for a table column? I know using ordinal positions is a bad practice but for a one-off data import process I need to be able to use the ordinal position to get the column data. So for example create table Test( Col1 int, Col2 nvarchar(10) ) instead of using se...
Given the schema PERSON { name, spouse } where PERSON.spouse is a foreign key to PERSON.name, NULLs will be necessary when a person is unmarried or we don't have any info. Going with the argument against nulls, how do you avoid them in this case? I have an alternate schema PERSON { name } SPOUSE { name1, name2 } where SPOUSE.na...