Hi Everyone, Could someone give me some guidance on when I should use WITH (NOLOCK) as opposed to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED What are the pros/cons of each? Are there any unintended consequences you've run into using one as opposed to the other? ...
Hi all, I have a strange 'problem' with one of my created queries. Given the next query: SELECT ID, DistanceFromUtrecht, ( SELECT (MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24)) FROM PricePeriod WHERE PricePeriod.FK_Accommodation = Accommodation.ID ) AS LatestBookableTimestamp FROM Accommodation WHERE LatestBooka...
Hi, I have a table with this schema: DeviceID int RoomID int DateInstalled datetime A sample of the table's data looks like: DeviceID RoomID DateInstalled 0001 Room1 1/1/2000 0002 Room1 1/1/2000 0001 Room2 2/1/2000 I need to build a query that will give me the date range each device was locate...
I have a stored procedure that finds all the existing databases and reads from a table in each one. Is there a way I can give a login read access to all databases, and to all future databases i.e., I won't have to do anything when a new database is added? Is there a server role that would work? Is there a way to make a SQL agent job ...
I simply copied my .mdf/.ldf files to another file and suddenly my Microsoft SQL 2008 database dies. I can't open these files on another system either. Can anyone explain what's happened? Any way to recover this application's database now? ...
Hi, I'm working on a web application written in Java using the Tapestry 5.1.0.5 framework. This framework does not have out-of-the-box support for JasperReports, so I wrote a service that modifies ChenilleKit's JasperReport service. I do not depend on the ChenilleKit version, instead I use the JasperReport 3.5.0 dependency. This may not...
I'm working on a database for stat keeping for a sports club where I help administrate some events. I think I've got the parts for keeping track of the competitor names, classifications, categories, etc. figured out using a main table, several lookup tables and a couple bridge tables to cover the many-to-many relationships involved. I ...
what is mssqlsystemresource database? do we need to copy this while moving form one server to another one? ...
Is there a simpler/cleaner way to do this using SQL Server 2000? Every few days I need to do this. I first look at how many records we have total: SELECT COUNT(*) FROM MyTable Then I list all the lengths of a certain field: SELECT LEN(MyText) FROM MyTable ORDER BY LEN(MyText) ASC I then need to scroll down 1/3rd of the way... and...
Hi, I have a table (Counts) with many entries like this: ID | userid | count | entrydate where ID is an autoinc PK, userid refers to my Users table, count is a random integer, and entrydate is a unix timestamp. I also have a table called Listmembers: listid | userid where both fields together are PK, and listid refers to some lis...
Both my local (and remote) SQL SERVER 2005 administrators claim that "allowing linked-servers is a security issue" and forbid their use here. (Huh?) Anyway, is there a way to do something similar WITHOUT linked-servers? SELECT * FROM LOCAL_SERVER.MyDatabase.dbo.MyTable AS t1 INNER JOIN REMOTE_SERVER.MyDatabase.dbo.MyTable AS t2 ON t1....
I have 36 completely independent queries I need to run on a regular bases which would go much faster if they could run 3 at a time (the database cancels our queries if we try and do more than 3 at a time) instead of each one waiting for the previous to finish. I would like to do something like this /* Some prep code here*/ /* Launch b...
I am working on converting a web application over to a WPF desktop application. I want to use the existing data access layer so I don't have to rewrite all the SQL queries. But, as it stands, everything is populated almost purely from DataTables returned from SQL queries. To make things a bit easier to manage, in some instances, it woul...
If I had two tables: PersonID | Count ----------------- 1 | 45 2 | 5 3 | 120 4 | 87 5 | 60 6 | 200 7 | 31 SizeName | LowerLimit ----------------- Small | 0 Medium | 50 Large | 100 I'm trying to figure out how to do a query to get a result similar to: PersonID | SizeName ------...
I did not see any duplicate questions but wondering if somebody can provide some good examples and specially best practices around these. ...
I have a table where it has two datetimes, one of them is a 'completed' datetime, therefore, if it is NULL then the user has not completed. I am trying to do an update, to update their mobile number. How can i do it so it does not update the datetime? currently if the datetime is null it gets set to the earliest possible time. here i...
SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL; The code above works beautifully when you are searching rows from left table without a counterpart in one right table. But how do I find rows from left table without a counterpart in two right tables? ...
I have a table follow: ID first end a 1 3 b 3 8 c 8 10 I want to select follow: ID first end a-c 1 10 But i can't do it. Please! help me. Thanks! ...
I have the following tables: TABLE: companies c FIELDS: id, name, description TABLE: departments d FIELDS: id, name, description TABLE employees e FIELDS: id, company_id, department_id, name I'm trying to get a list of all employees for company 1 and also get the department name (d.name) into the results of the row. Here's my origi...
Only for MSSQL 2000-2005 by SQL query and no stored procedures No cursors and temp tables The table create table my_table ( row_id int identity(1,1), _key varchar(20), _total decimal(18,2) ) Insert the data insert into my_table (_key,_total) Values('qwe',10) insert into my_table(_key,_total) Values ('qwe',10) insert into my_table(...