I'm writing up a scheduling application for my wife, and I ran into the following question. I have a schema that can be described in ActiveRecord terms as such: Each resource has and belongs to many events Each event has and belongs to many resources, and has many timespans Each timespan belongs to an event, and has three attributes: ...
What would be the most efficient way to do a paging query in SQLServer 2000? Where a "paging query" would be the equivalent of using the LIMIT statement in MySQL. EDIT: Could a stored procedure be more efficient than any set based query in that case? ...
I'm trying to find out the best practice when removing characters from the start of a string. In some languages, you can use MID without a length parameter however, in TSQL the length is required. Considering the following code, what is the best practise? (The hex string is variable length) DECLARE @sHex VARCHAR(66) SET @sHex = '0x7E2...
I have a TSQL script that is used to set up a database as part of my product's installation. It takes a number of steps which all together take five minutes or so. Sometimes this script fails on the last step because the user running the script does not have sufficient rights to the database. In this case I would like the script to fail ...
I'm writing a SQL query in SQL Server in which I need to replace multiple string values with a single string value. For example Product Quantity ------- -------- Apple 2 Orange 3 Banana 1 Vegetable 7 Dairy 6 would become Product Quantity ------- -------- Fruit 2 Fruit 3 Fruit ...
I am using MS SQL Server 2005 (9.0.4035) and trying to find rows that contain the same data in a nvarchar(4000) field. The field contains xml that has both opening and closing square parentheses. Here is sample data: DataID Data 1 1 2 1 3 2] 4 2] 5 3[ 6 3[ Using th...
I'm trying to develop a sql query that will return a list of serial numbers. The table is set up that whenever a serial number reaches a step, the date and time are entered. When it completes the step, another date and time are entered. I want to develop a query that will give me the list of serial numbers that have entered the step, ...
I want to do something like: DELETE FROM student WHERE student.course, student.major IN (SELECT schedule.course, schedule.major FROM schedule) However, it seems that you can only use one column with the IN operator. Is that true? Seems like a query like this should be possible. ...
Hi folks, So, I've got SQL (2005) Transactional Replication generally working well with a single publisher and single (read-only) subscriber. Data changes and updates flow perfectly, with about 5 second latency, which is just fine. My one nagging problem, that I've spent a couple days trying to solve (and Googling everywhere for ans...
MS SQL Server doesn't have row level triggers, correct? If I needed to insert a row from within a trigger and then insert another row, based on the result of the first insert, would a cursor be the best solution? For example, is there a better way to do this: CREATE TABLE t1 (foo int) CREATE TABLE t2 (id int IDENTITY, foo int) CREATE T...
A DBA that my company hired to troubleshoot deadlock issues just told me that our OLTP databases locking problems will improve if we set the transaction level to READ COMMITTED from READ UNCOMMITTED. Isn't that just 100% false? READ COMMITTED will cause more locks, correct? More Details: Our data is very "siloed" and user specific....
Given the tables: role: roleid, name permission: permissionid, name role_permission: roleid, permissionid I have a set of permissions, and I want to see if there is an existing role that has these permissions, or if I need to make a new one. Note that I already know the permissionid, so really the permission table can be ignored ...
I have been wondering whether there is any code out there that enables representing SQL in the form of some object tree that can be assembled, modified and then finally rendered to valid SQL? Off the top of my head it could look something like that... var stmnt = new Statement(); stmnt .AddMaster("Customer") .Show("Firstname, "Last...
So i just received a connection string to a Cube in our one of our SQL databases. I'm supposed to create a report that can be emailed to 30 something people off of this data. I would much rather write a stored procedure and just let SSRS send it out whenever its supposed to. The problem is that the database is extremely complex and I'm ...
I am working on both parts of a data layer. I get to create the procs, and the business object layer that will consume the procs. Is there a best practice for determining where a delete should occur? Here's the scenario. I have a table Book with foreign key to a child table Pages. If I delete the book, I also want to delete the page...
I have two tables named foo and bar, hypothetically speaking. foo has columns foo_id, foo_fluff bar has columns bar_id, foo_id, timestamp I need a query which will retrieve return one row for any foo_id that the table bar contains, with the latest timestamp. So, if bar has three rows, two of which have a foo_id of 1, and 1 of which ha...
i know there is a variable, function, or stored procedure that you can use to find the path that SQL Server is installed to: e.g.: c:\Program Files\Microsoft SQL Server\MSSQL.7\MSSQL or m:\CustomInstance\MSSQL In reality, i'm hoping to SELECT for the default backup path. But since i doubt that exists, i'll just tack \BACKUP ont...
I'm using PSEXEC within batch files to execute DTEXEC (SQL SSIS jobs) as part of a scheduling system. What I'm finding is when a bunch of jobs are triggered together (or even close to one another) I get multiple ERROR 128 messages and the DTEXEC jobs immediately abort. I'm guessing there is some sort of problem running multiple instances...
I'm currently writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/or readable) way? (I am using Microsoft SQL Server, 2005) A simplified example: SELECT col...
I have a set of records in my MS SQL table. With Date as the primary key. But the Dates are only for working days and not the continues days. Eg: 1/3/2000 12:00:00 AM 5209.540000000 5384.660000000 5209.540000000 5375.110000000 1/4/2000 12:00:00 AM 5533.980000000 5533.980000000 5376.430000000 5491.010000000 1/5/2000 12:00:00 AM ...