How can I ignore an output parameter of a stored procedure. I'm calling the sp from another sp. e.g.: DECLARE @param1 integer EXEC mystoredprocedure @in_param_1, @in_param2_, @param1 OUTPUT, what do I type here to ignore the second output param I'm using T-SQL (MS SQL 2005). Thanks for your help. ...
I have been programming in C# and Java for a little over a year and have a decent grasp of object oriented programming, but my new side project requires a database-driven model. I'm using C# and Linq which seems to be a very powerful tool but I'm having trouble with designing a database around my object oriented approach. My two main qu...
Imagine the following tables: create table boxes( id int, name text, ...); create table thingsinboxes( id int, box_id int, thing enum('apple,'banana','orange'); And the tables look like: Boxes: id | name 1 | orangesOnly 2 | orangesOnly2 3 | orangesBananas 4 | misc thingsinboxes: id | box_id | thing 1 | 1 | orange 2 | 1...
I'm trying to generate a sales reports which lists each product + total sales in a given month. Its a little tricky because the prices of products can change throughout the month. For example: Between Jan-01 and Jan-15, my company sells 50 Widgets at a cost of $10 each Between Jan-15 and Jan-31, my company sells 50 more Widgets at a co...
How do databases (mySQL if a particular example is important) determine the size of the column needed to store an ENUM? Is it something simple like a single byte for less than 256 enum options, etc.? ...
I have a MySQL query like this: SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q WHERE criterion="value" GROUP BY q.P_id ORDER BY Occurrences DESC LIMIT 10; I want to restrict the results to rows where Occurrences>0. This seems very simple to me, but I can't seem to make it work. No matter what I tr...
I plan on installing Multiple Instances of MS SQL EXPRESS on my Development Server. I am hoping this will let me get around the limitations of SQL EXPRESS: 1 GB of RAM, 1 CPU Database size max 4 GB [I understand and wish that I could afford a full licence version of SQL Server.] So, would this work? Would each instance have the...
I've got the following 2 tables: ingredients (id, name) ingredient_prices (id, ingredient_id, price, created_at) such that one ingredient can have many prices. What will be the best way to get the latest entered price per ingredient? I know it can be done easily with sub-selects, but I want to know if there is a more efficient way fo...
I'm in an issue where I don't have enough space to accomodate my MDF and LDF files from a LiteSpeed backup we had done. I've come up with the following sproc: exec master.dbo.xp_restore_database @database = 'OSiteDB', @filename = 'L:\OSiteDB_2009_01_07_Wed_LiteSpeed_Full.BKP', @with = 'move "O1_SITEDB" to "S:\OSiteDB_Data.mdf"', @with...
I need to write a query that will group a large number of records by periods of time from Year to Hour. My initial approach has been to decide the periods procedurally in C#, iterate through each and run the SQL to get the data for that period, building up the dataset as I go. SELECT Sum(someValues) FROM table1 WHERE deliveryDate BETWE...
I currently have the query running on Postgres: SELECT * FROM addenda.users WHERE users.username LIKE '%\_' But rather then returning just entries ending in an underscore, I get all results back, regardless of whether it contains an underscore or not. Running the query below returns a username which is just an underscore, so the esc...
Hi, I have a large varbinary field in one of my tables, and I would like to download in parts for show a download progress indicator in my application. How can I split the data sent in a SELECT query? Thanks ...
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table? (SQL answers preferable over clicking about in the GUI of the management studio.) Thanks in advance. ...
We have a bit of a messy database situation. Our main back-office system is written in Visual Fox Pro with local data (yes, I know!) In order to effectively work with the data in our websites, we have chosen to regularly export data to a SQL database. However the process that does this basically clears out the tables each time and does...
Take a look at these two queries: -- #1 SELECT * FROM my_table WHERE CONTAINS(my_column, 'monkey') -- #2 SELECT * FROM my_table WHERE CONTAINS(my_column, 'a OR monkey') -- "a" is a noise word Query #1 returns 20 rows when I run it in Management Studio. Query #2 returns the same 20 rows, but I also see the following in the Messages t...
Patient ------- PatientID Visit ----- VisitID PatientID HeartRate VisitDate How do I select all of the patients who have a visit, the date of their first visit, and their heart rate at that first visit? ...
I have a relatively simple select statement in a VB6 program that I have to maintain. (Suppress your natural tendency to shudder; I inherited the thing, I didn't write it.) The statement is straightforward (reformatted for clarity): select distinct b.ip_address from code_table a, location b where a.code_item = b.which...
I am working on an application that needs to use a local sql database. The database needs to be encapsulated in a file because they need to be able to easily move the data accross the network, onto a usb stick, burned onto a cd/dvd, etc. Our older apps all used access, which has worked great for us, but I'd like to use the newer .net t...
I'm using VBScript (ASP Classic) and SQL Server; I'm trying to have a section on the website where you can see a count of products at certain price levels. Something like: $50 - $100 (4) $100 - $500 (198) $500 - $1000 (43) For the love of me I cannot figure out a good way to do a query like this. I mean... I know how to get the numb...
I have the following problem : I have rows like ID CODE NAME ......... 1 h1100h1 Cool example1 ......... 2 h654441 Another cool1 ......... I would like to swap them retaining all old primary keys and constraints. Of course, I can easily solve this manually by updating the rows. I am kind of wondering whet...