I'm creating a Rails application, and I've hit a bit of a snag. I want a "create new record for DataType1" form that not only creates a new row for DataType1, but also inserts up to four new rows for DataType2. I know all about fields_for, but my problem is that I need to submit up to four DataType2s, and the only connection they have t...
I have seen some guidance which recommends that you secure a database by layering all data access through stored procedures. I know that for SQL Server, you can secure tables, and even columns against CRUD operations. For example: --// Logged in as 'sa' USE AdventureWorks; GRANT SELECT ON Person.Address(AddressID, AddressLine1) ...
Hi there, I have been running an UPDATE on a table containing 250 million rows with 3 index'; this UPDATE uses another table containing 30 million rows. It has been running for about 36 hours now. I am wondering if their is a way to find out how close it is to being done for if it plans to take a million days to do its thing, I will kil...
I'm looking for a better way to do the following query. I have a table that looks like this: game_id | home_team_id | away_team_id 1 | 100 | 200 2 | 200 | 300 3 | 200 | 400 4 | 300 | 100 5 | 100 | 400 And I want to write a query that counts the number of home ...
I'm trying to optimize query performance and have had to resort to using optimizer hints. But I've never learned if the optimizer will use more than one hint at a time. e.g. SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/ /*+ LEADING(i vol) */ /*+ ALL_ROWS */ i.id_number, ... FROM i_table i JOIN vol_ta...
Suppose I have a database table that has a timedate column of the last time it was updated or inserted. Which would be preferable: Have a trigger update the field. Have the program that's doing the insertion/update set the field. The first option seems to be the easiest since I don't even have to recompile to do it, but that's not r...
I'm using named parameters in a query to match fields in a map-like data structure. The data structure can have fields, or another map-like data structure. This nested structure is repeatable ad nauseum. I would like to name the parameters in the query using an XPath like language, that can be parsed to indicate further nested lookups. ...
I understand that the WITH RECOMPILE option forces the optimizer to rebuild the query plan for stored procs but when would you want that to happen? What are some rules of thumb on when to use the WITH RECOMPILE option and when not to? What's the effective overhead associated with just putting it on every sproc? ...
Hi I'm using ms2005 for a simple calendaring system. We have three 'legacy' tables: Groups, Units and Staff. I need to give each record in the tables a unique identifier (carrying across all 3 tables). What would be best way to approach this? I am using NHibernate and was was wondering whether that could do it for me. Anyway, any nods...
The only thing I don't have an automated tool for when working with SQL Server is a program that can create INSERT INTO scripts. I don't desperately need it so I'm not going to spend money on it. I'm just wondering if there is anything out there that can be used to generate INSERT INTO scripts given an existing database without spending ...
When I retrieve any Scalar value from the database, I usually write code like this for nullable fields. cmd.ExecuteScalar() == DBNull.Value ? 0 : (int)cmd.ExecuteScalar() But I don't like it because it executes the Executescalar statement twice. It's an extra trip to the server for my website and in favor of performance I don't want t...
I'm looking for a way to set up (via SQL) a log table containing everything that had been done to my sqlite database (preferably in terms of the insert, create table etc. statements as issued to the database). I'm sure there are way to do it via setting trigger on each table, but that is just WAY too much work and does not bode well if I...
Can someone provide the download link for SQL Server Reports add-in for Visual Studio 2008(not Visual Web Developer!)? ...
Hey guys, I'm trying to make a graph in Rails, for example the avg sales amount per day for each day in a given date range Say I have a products_sold model which has a "sales_price" float attribute. But if a specific day has no sales (e.g none in the model/db), I want to return simply 0. What's the best way in MySQL/Rails to get this ...
My Server is located in US and I am running a query against it. The Data contained in the table both at Remote Server(U.S) and Local are same. The Problem is when I am retrieving the DataSet using WebService from Remote Server. The Dates Column is showing The Previous Date. For e.g Date Column is having "14 Jan 2007" but when retrieved ...
I have this scenario where I need data integrity in the physical database. For example, I have a variable of @email_address VARCHAR(200) and I want to check if the value of @email_address is of email format. Anyone has any idea how to check format in T-SQL? Many thanks! ...
Is there any difference in the performance of the following three SQL statements? SELECT * FROM tableA WHERE EXISTS (SELECT * FROM tableB WHERE tableA.x = tableB.y) SELECT * FROM tableA WHERE EXISTS (SELECT y FROM tableB WHERE tableA.x = tableB.y) SELECT * FROM tableA WHERE EXISTS (SELECT 1 FROM tableB WHERE tableA.x = tableB.y) The...
if i have data as follows A | 01/01/2008 00:00:00 B | 01/01/2008 01:00:00 A | 01/01/2008 11:59:00 C | 02/01/2008 00:00:00 D | 02/01/2008 01:00:00 D | 02/01/2008 20:00:00 I want to only select the records whose identifiers (A, B, C or D) have occured twice within a 12 hour period. In this example above this would only be 'A' Can a...
Ok. So I am pretty new a this. I have a datatable that I want to pass to a stored procedure for further manipulation. I have read some stuff on the web and it looks like I should be able to convert the datatable to XML and then pass that to the stored procedure. What am I doning wrong? I have SQL server 2005. The data never gets passe...
Let's say you have a table for branches in your organization. Some of them are "main" branches, and others are satellite offices that roll up to a main branch. Other than this distinction, which only impacts a few things in the system, the branches are all peers and have the same attributes (address, etc.). One way to model this is in a ...