I am trying to query for a list of stored procedure definitions using information_schema.routines that exist in one database but not in another. SELECT t1.Routine_Definition FROM [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 LEFT JOIN [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name WHE...
I have two tables in a one to many relationship. (products and qty break pricing). At the database level I cannot create a relationship between the two tables. I brought those two tables into LINQ and created the association manually. I need to do a big LINQ query and have the tables be joined. My problem is it's not using a join to ge...
Is there a standard way to bind arrays (of scalars) in a SQL query? I want to bind into an IN clause, like so: SELECT * FROM junk WHERE junk.id IN (?); I happen to be using Perl::DBI which coerces parameters to scalars, so I end up with useless queries like: SELECT * FROM junk WHERE junk.id IN ('ARRAY(0xdeadbeef)'); Clarification: ...
In postgresql a query in the querylog gets something like this: 2009-02-05 00:12:27 CET LOG: duration: 3781.634 ms execute <unnamed>: SELECT QUERY .... Is there a possibility to put something more usable into the "< unnamed >" placed like the url the query was requested from? Are there any other possibilities to track the origin o...
In c#, I use a decimal out DbParameter for a Sql Server stored procedure. I create parameter like that DbParameter prm = comm.CreateParameter(); prm.ParameterName = "@Price"; prm.DbType = DbType.Decimal; prm.Direction = ParameterDirection.Output; comm.Parameters.Add(prm); //and set the value of comm.Parameters["@Price"] to a var...
I have a stored procedure and if the stored procedure does this: SELECT 0 As Ret DELETE FROM table where value1 = 1 Returns 1 row result with its value of 0 and column name Ret But if I do this: DELETE FROM table where value1 = 1 SELECT 0 As Ret I get no returned results. My question is, how do I get the second variation to retu...
I am using strongly-typed-dataset as an ORM to wrap around my Microsoft Access database, now I am looking for a way to create an equivalent of UPDATE table1 SET table1.nationality = 'england' WHERE table1.nationality in ( select table2.nationality from table2 where table2.gender ='M' ); in a strongly typed dataset designer, but not...
I need to design a Key/value table in my database and I'm looking for guidance on the best way to do this. Basically, I need to be able to associate values to a dynamic set of named properties and apply them to an external key. The operations I need to be able to support are: Apply a key/value pair to a group of items Enumerate all ...
Hi, I am trying to create a view for the following query. SELECT DISTINCT products.pid AS id, products.pname AS name, products.p_desc AS description, products.p_loc AS location, products.p_uid AS userid, products.isaproduct AS whatisit FROM products UNION SELECT DISTINCT services.s_id AS id...
Hi! There's the (almost religious) discussion, if you should use LIKE or '=' to compare strings in SQL statements. Are there reasons to use LIKE? Are there reasons to use '='? Performance? Readability? Thanks in advance! ...
I am using Spring and JDBCTemplate. The scenario is a CUSTOMER table and ORDERS table - parent-child relationship. I want to do an insert (for example 1 customer and 5 orders) - but I am unsure how you programmatically insert a row in the CUSTOMER table (some how get hold of the Oracle generated unique id), and then insert the correspo...
I am working on a Database that uses the Float data type to store values that should only be 2 decimal positions (dollars and cents). Using Float appears to work OK, as long as the person updating the Float column does a ROUND. Of course, this does not always happen and then when a SUM is done it is always off a few pennies from what is ...
I have a number of stored procedures I call from code with ExecuteNonQuery. It was all good but 2 of my stored procedures started timing out intermittently today with: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. If I e...
I am trying to assign absence dates to an academic year, the academic year being 1st August to the 31st July. So what I would want would be: 31/07/2007 = 2006/2007 02/10/2007 = 2007/2008 08/01/2008 = 2007/2008 Is there an easy way to do this in sql 2000 server. ...
I'm having a brain-dead moment... I have two tables described by: CREATE TABLE table_a ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE (name)) CREATE TABLE table_b ( id INTEGER PRIMARY KEY AUTO_INCREMENT, a_key INTEGER ...
I have implemented a linked list as a self-referencing database table: CREATE TABLE LinkedList( Id bigint NOT NULL, ParentId bigint NULL, SomeData nvarchar(50) NOT NULL) where Id is the primary key, and ParentId is the Id of the previous node on the list. The first node has ParentId = NULL. I now want to SELECT from the t...
How can I fire a trigger BEFORE a delete in T-SQL 2005? The FOR actually fires AFTER an event and they seems no BEFORE argument in the TRIGGER function. The INSTEAD OF is not what I want. I need to fire before I delete a record. Any ideas? ...
I have a year 2009, a month 11 and a day 12. All of these are being passed to me as integers. What I want to do is create a date from these. I want to use these as part of my where statement. What is the best way to do it from either a clean code and most importantly a speed point of view? I personally am using MS SQL, which I'm sure w...
I am trying to find a way to have a SELECT statement return the set of dates between 2 input dates with a given interval. I would like to be able to easily change the time frame and interval that would be returned, so hard coding something with a series of SELECT ... UNIONs would not be ideal. For example: I want all the dates at 5 sec...
We are running a large project with several different languages: Java, Python, PHP, SQL and Perl. Until now people have been working in their own private repositories, but now we want to merge the entire project in a single repository. The question now is: how should the directory structure look? Should we have separate directories for...