Consider that I have a transaction: BEGIN TRANSACTION DECLARE MONEY @amount SELECT Amount AS @amount FROM Deposits WHERE UserId = 123 UPDATE Deposits SET Amount = @amount + 100.0 WHERE UserId = 123 COMMIT And it gets executed on 2 threads, in the order: thread 1 - select thread 2 - select thread 1 - update thread 2 - update ...
I am developing a page view counter to track the amount of views a page is having on our site and displaying it to the user. (I asked an intro question before: http://stackoverflow.com/questions/246919/page-view-counter-like-on-stackoverflow). Using the recommendations, I developed a httpHandler which will handle the request whenever th...
In an interview for a SQL DBA position a while back, I mentioned I prefer the SQL 2005 Management Studio app vs. the old 2000 versions of Enterprise Manager / Query Analyzer. The DBA who was interviewing me said that they had experienced some sort of database corruption in their 2000 databases when making changes from the 2005 MS, so the...
I have a partitioned table in SQL Enterprise 2005. I need to query the PARTITION FUNCTION to find it's partition ranges. What SQL query will give me those values? ...
I know you cannot use a alias column in the where clause for T-SQL; however, has Microsoft provided some kind of workaround for this? Related Questions: Unknown Column In Where Clause Can you use an alias in the WHERE clause in mysql? “Invalid column name” error on SQL statement from OpenQuery results ...
There are two databases in SQL Server 2005: One called "A" and another one called "A_2". "A" is a variable name to be entered by the user, the "_2" prefix for the second database is always known. (So databases could be "MyDB" and "MyDB_2", etc) How to access the other database from within a stored procedure without knowing the actual nam...
What is the upper limit for an autoincrement primary key in SQL Server? What happens when an SQL Server autoincrement primary key reaches its upper limit? ...
When I change the application log level to FINE, SQL Servers log also uses it, and, as consequence, I get a lot of unnecessary log messages. How can I turn off SQL Server log messages? ...
I want to come up with the minimal set of queries that return the databases and tables in a Microsoft SQL Server instance, on as many versions of SQL Server as possible. I'm not sure if I should half-answer my own question, but here's what I think I need for 2000 and 2005. Ideally I'd go back further, but I don't have access to older ve...
I need to programaticaly enable READ COMMITED SNAPSHOT in SQL Server. How can I do that? ...
I'm currently working on someone else's database where the primary keys are generated via a lookup table which contains a list of table names and the last primary key used. A stored procedure increments this value and checks it is unique before returning it to the calling 'insert' SP. What are the benefits for using a method like this (...
When I create a view in SQL Server 2005 and someone later opens it in the GUI modify mode it sometimes completely rearranges my joins. Often to the point it's virtually no longer readable. If I look at it in the GUI it's changed as well from what I originally wrote. Sometimes the join lines no longer even point to a field and the join...
Hi, I have a very simple question. I have 15 stored procedures that return data from a common table, then join that table with a specific table to retrieve inventory. Ex. Common: tblCommonSpecific: tblSpecific Is there i was i can pass the name "tblSpecific" into a single stored procedure as a variable, like this: SELECT .... FROM ...
The ragged right flat file option is great for a single trailing field terminated with a CRLF, but when any number of trailing fields which are blank are suppressed and the line terminated early with CRLF, I have found no alternative in SSIS but to read a few columns in with a single "REMAINDER" column and then used a Derived Column tran...
What is the best way to store a user name and password for a Windows Service? The service has to be able to access an MS SQL Server database on a remote machine. We have to support MS SQL Server authentication (mixed mode) instead of NT Authentication. ...
I have a table and an associated chart contained within a 'list' object in SSRS 2005. I am using an expression to determine the visibility of the table and chart, however when the table and chart are not visible an ugly large blank space (where they would otherwise have been rendered) appears in the report. Is there any functionality t...
I need to backup the original data from a table so I can fill it with test data, then restore it again after deving / testing etc.. Anyone know any quick ways to achieve this? I'm on SQL Server 2005. Thanks. ...
A co-worker recently ran into a situation where a query to look up security permissions was taking ~15 seconds to run using an = comparison on UserID (which is a UNIQUEIDENTIFIER). Needless to say, the users were less than impressed. Out of frustration, my co-worker changed the = comparison to use a LIKE and the query sped up to under 1...
when installing SQLServer2005 developer edition (not express) I created a named instance: MYSERVERNAME/MYINSTANCENAME Is it possible to 1) change the name of my named instance to default: MYSERVERNAME OR 2) create a new default instance (MYSERVERNAME as above). any help would be appreciated! ...
I'm working on an app that takes data from our DB and outputs an xml file using the FOR XML AUTO, ELEMENTS on the end of the generated query, followed by an XSLT to transform it the way we want. However in a particular case where we are generating some data using an sql scalar function, it always puts that element into a sub-table node ...