What is the best SQL libary for use in Common Lisp?
Ideally something that will work with Oracle, MS SQL Server, MySQL and Posgress. ...
sql
Why am I getting "multiple cascade paths" with this table relationship?
I have the following table relationship in my database: Parent / \ Child1 Child2 \ / GrandChild I am trying to create the FK relationships so that the deletion of the Parent table cascades to both child and the grandchild table. For any one particular granchild, it will either be...
Weird Output on SQL REPLACE
I am using REPLACE in an SQL view to remove the spaces from a property number. The function is setup like this REPLACE(pin, ' ', ''). On the green-screen the query looked fine. In anything else we get the hex values of the characters in the field. I am sure it is an encoding thing, but how do I fix it? Here is the statement I used to cr...
In MS-SQL, how do I INSERT INTO a temp table, and have an IDENTITY field created, without first declaring the temp table?
I need to select a bunch of data into a temp table to then do some secondary calculations; To help make it work more efficiently, I would like to have an IDENTITY column on that table. I know I could declare the table first with an identity, then insert the rest of the data into it, but is there a way to do it in 1 step? ...
How do I deploy a managed stored procedure without using Visual Studio?
Everything I have read says that when making a managed stored procedure, to right click in Visual Studio and choose deploy. That works fine, but what if I want to deploy it outside of Visual Studio to a number of different locations? I tried creating the assembly with the dll the project built in SQL, and while it did add the assembly, i...
Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
I want to write a query like this: SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o But this isn't how the MAX function works, right? It is an aggregate function so it expects a single parameter and then returns the MAX of all rows. Does anyone know how to do it my way? ...
Flags in a database rows, best practices.
I am asking this out of a curiosity. Basically my question is when you have a database which needs a row entry to have things which act like flags, what is the best practice? A good example of this would be the badges on stack overflow, or the operating system field in bugzilla. Any subset of the flags may be set for a given entry. Usua...
Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?
While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For the MySQL folk who have not seen Microsoft's SQL Profiler, here is a screenshot At my previous job we had a tool that trumped SQL Profile...
Generic LINQ query predicate?
Not sure if this is possible or if I'm expressing correctly what I'm looking for, but I have the following piece of code in my library repeatedly and would like to practice some DRY. I have set of SQL Server tables that I'm querying based on a simple user-supplied search field ala Google. I'm using LINQ to compose the final query based ...
SQL Job Status
I am actually working on SP in SQL 2005. Using SP i am creating a job and am scheduling it for a particular time. These jobs take atleast 5 to 10 min to complete as the database is very huge. But I am not aware of how to check the status of the Job. I want to know if it has got completed successfully or was there any error in execution. ...
View Temporary Table Created from Stored Procedure
I have a stored procedure in SQL 2005. The Stored Procedure is actually creating temporary tables in the beginning of SP and deleting it in the end. I am now debugging the SP in VS 2005. In between the SP i would want to know the contents into the temporary table. Can anybody help in in viewing the contents of the temporary table at run ...
Where do the responsibilities of a Db Abstraction in PHP start and end?
In PHP, what is the best practice for laying out the responsibilities of a Db Abstraction Layer? Is OOP a good idea in terms of performance? How much should be generic object code, and how much should be very specific functions? ...
Key value pairs in relational database
Does someone have experience with storing key-value pairs in a database? I've been using this type of table: CREATE TABLE key_value_pairs ( itemid varchar(32) NOT NULL, itemkey varchar(32) NOT NULL, itemvalue varchar(32) NOT NULL, CONSTRAINT ct_primarykey PRIMARY KEY(itemid,itemkey) ) Then for...
Where can I get some good tutorials about views?
I want to design a view in sql server 2005. Where can I get some good reading materials on this? ...
Find two consecutive rows
I'm trying to write a query that will pull back the two most recent rows from the Bill table where the Estimated flag is true. The catch is that these need to be consecutive bills. To put it shortly, I need to enter a row in another table if a Bill has been estimated for the last two bill cycles. I'd like to do this without a cursor, ...
Compute Users average weight
I have two tables, Users and DoctorVisit User - UserID - Name DoctorsVisit - UserID - Weight - Date The doctorVisit table contains all the visits a particular user did to the doctor. The user's weight is recorded per visit. Query: Sum up all the Users weight, using the last doctor's visit's numbers. (then divide by number of users ...
SQL Server Convert integer to binary string
I was wondering if there was an easy way in SQL to convert an integer to its binary representation and then store it as a varchar. For example 5 would be converted to "101" and stored as a varchar. ...
Is every DDL SQL command reversible? [database version control]
I want to setup a mechanism for tracking DB schema changes, such the one described in this answer: For every change you make to the database, you write a new migration. Migrations typically have two methods: an "up" method in which the changes are applied and a "down" method in which the changes are undone. A single comma...
Nested SELECT Statement
SQL is not my forte, but I'm working on it - thank you for the replies. I am working on a report that will return the completion percent of services for indiviudals in our contracts. There is a master table "Contracts," each individual Contract can have multiple services from the "services" table, each service has multiple standards fo...
Sample sql script to zip and transfer database backup file
I was looking for a sample sql script to zip my database backup file (.bak) and transfer to a remote location. Please share if you have it with you. ...