As far as I am aware, there's no direct ability to have foreign key constraints in SQLite 3. I have a many-to-many table that needs this, so I'm creating a trigger that raises an ABORT when the foreign key constraint is violated. My statement looks like this: CREATE TRIGGER fkFooBar BEFORE INSERT ON Foo_Bar FOR EACH ROW BEGIN ...
I have an Access 2003 app that connects to a SQL Server 2000 box. I have a table in which I need to lock down a record along with all related records in different tables. By "lock down", I mean mark them as read-only so that no clients can edit those records unless an admin unlocks them. Any ideas? ...
I'm busy building a software update application in C#, WinForms, .NET 3.5, where I have a collection of pluggable task classes with a common base class. I would like one task, the database upgrade, to begin a transaction, and another task, the web site upgrade, to commit or roll back the same transaction, so the web site and DB are alwa...
Is there a way around this in SQL Server 2005? (It bugs me, and every time I encounter it I get in to a strop. But this is the first time I've had to deal with AND been on Stack Overflow. Please, save what little sanity I posess!) DimensionTable: id INT IDENTITY(1,1) FactTable: source_id INT NOT NULL, target_id INT NOT NULL I...
I need to write setup scripts for MySQL (usually run using 'source [file]' from mysql console) that depend partly on existing data, and there are differences in environments, meaning that sometimes script does fail. A common case is that a 'SET' statement with a select (to locate an id) fails to find anything; console sets value to NULL....
This may be a trivial question, but I will ask it anyway: can updates in three or four tables in one database trigger updates to one or two tables in another database (in MS SQL 2005?) I am happy to create any amount of T-SQL necessary, and I also have VS 2008 with C# ability but have never written a trigger like that before. Essential...
I'm writing an application that is trying to bulk export data from a SQL Server database (with a given connection string) to a local file (in whatever format). Normally the sql server utility BCP.exe would be ideal, however, the bcp utility may not be available on the machine my app is running on. Also, bcp is not considered a redistri...
hi i need to select only the first row from a query that joins tables A and B, on table B exist multiple records with same name. there are not identifiers in any of the two tables. i cannt change the scheme either because i do not own the DB TABLE A NAME TABLE B NAME DATA1 DATA2 Select Distinct A.NAME,B.DATA1,B.DATA2 From A Inner ...
I have a CRUD-heavy ASP.NET application with all the business logic in Stored Procedures. As an example, there is an UPDATE stored procedure that's ~500 lines long and contains large amounts of conditional logic, referencing multiple tables & UDFs. The proc takes in the field name being updated and the new value, sets a bunch of declar...
I want to create a "view" to eliminate the same three-line sub-query from about 90 queries in an app I'm working on. The problem is the sub-query contains a condition based on a variable. SELECT * FROM items WHERE id NOT IN ( SELECT item_id FROM excluded_items WHERE user_id = 123 ); If it wasn't variable, I could simply make a view...
Can you use sqlcmd to query a sql server 2000 database? and if so where is the smallest download of it? ...
I want to insert text with single quote Eg john's to table in sql server 2005 database ...
Let's say , I have a table, ClientTrade, like thus : ClientName , TradeDate , Quantity And I want to create a query in Oracle PLSQL which should return the result like this : (The days are derived from the TradeDate column and Mon = sum(Quantity) for Mon , Tue = sum(Quantity) for Tue ... etc.) ClientName Mon Tue Wed Thu Fri Sat Sun ...
I'm having an embedded device which keeps a list of inner tables. I would like it to synchronize this table's state with some outside database for debugging purposes. That is whenever I add an element to a certain struct array I wish the device to issue an "INSERT INTO ..." command. However I'm sending the data over an RS232 serial cabl...
I have set up the full-text search functionality in SQL Server 2008 express. This is what I did: -- STEP 1: Create catalog create fulltext catalog HtmlSearch -- STEP 2: Fill catalog create fulltext index on docs (WordHtml) key index IX_docs_1 on HtmlSearch with change_tracking auto -- STEP 3: Search select * from docs where freetext(*...
So I have this weird problem with an SQL Server stored procedure. Basically I have this long and complex procedure. Something like this: SELECT Table1.col1, Table2.col2, col3 FROM Table1 INNER JOIN Table2 Table2 INNER JOIN Table3 ----------------------- ----------------------- (Lots more joins) WHERE Table1.Col1 = db...
Hi all. When Executing the following linq to sql statement: var stuff = from l in _db.SqlLinks select new { Link = l, Rating = (from v in l.SqlLinkVotes where v.Tag == tagId ...
I'm passing View name as parameter in a Table Valued Function, and I want to fetch some data from that view by building a dynamic SQL and executing it by sp_executesql(). when try to execute the function, I get the error: Only functions and extended stored procedures can be executed from within a function. DBMS: SQL Server 2005 any wor...
How can I reuse a computed column in SQL in MySQL? Say: my query is something like: - SELECT CONVERT_TZ( if(timestamp_start > last_update, timestamp_start, last_update), 'GMT', user.timezone ) as time_usr_tz from shecdule inner join user on shecdule.user_id = user.user_id where CONVERT_TZ...
I'm working on an ASP.NET application that uses VB. I'm using a SQLReader/SQLCommand/SQLConnection within the VB file to try to pull data values. I was mystified to find out that the reason why the query wasn't returning any values, and someone here showed me how to troubleshoot the query to verify things were being returned, which they...