I run the following query select * from my t1 where c1 = 22 and query runs for ever.. How can I find out whats locking that row? I tried runnning sp_who, sp_who2, sp_lock, and googled for information but still no resolution in sight. Please help. I am using sql server 2000. ...
Hi, I want to find if table locking is disabled across all tables in all databases. so I check this property on the index level from sysindexes table or table level? How can I check it? Regards Manjot ...
Searching around the Microsoft.M assembly I found the SourceParser class and whole set of classes in the Microsoft.TSQL10 namespace that seem related to parsing SQL but I cannot find examples of how to use it. I know the you can generate T-SQL easily enough, but can you consume it, manipulate the data structure and re-output a modified ...
I would like to pull a set of records in a SQL sproc by passing in a list of system.guid and also by passing in a list of integer keys. I can put the guids ihto a string, of course, but the question is - how do I quote the test values so they are recognized as UIDs by SQL In essence I was to DECLARE @pklist AS VARCHAR(1000) -- This ...
I have the following database table: Answer MemberID | QuestionNo | AnswerNo | AnswerString 10 | 1 | 2 | q1 anwer2 10 | 2.1 | 3 | q2.1 answer3 10 | 2.2 | 5 | q2.2 answer5 10 | 7 | 1 | q7 answer 7 11 | 1 | 3 | q1 a...
Before I go any further: Yes, I know that cursors perform poorly compared with set-based operations. In this particular case I'm running a cursor on a temporary table of 100 or so records, and that temporary table will always be fairly small, so performance is less crucial than flexibility. My difficulty is that I'm having trouble findi...
I have a view in SQL Server 2008 with several columns that are expressions of one column divided by another. I have to account for the divisor being 0, so I use a CASE statement. Here is an example of one: CASE SUM(dbo.GameStats.BringBacksAttempted) WHEN 0 THEN 0 ELSE SUM(dbo.GameStats.BringBacks) / SUM(dbo.GameStats.B...
Is any better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of distinct items from this table but the distinct is over two columns. I hope that makes sense. ...
I wanted to know what does the # symbol mean? Why is it being used? Example: INSERT INTO #tmpContracts (sym, contractCount) SELECT sym, COUNT(DISTINCT(fulloptionsym)) AS contractCount ...
We have a lot of quieries for which we append a random alias at the end of field and table names (due to a custom ORM implementation that might be hard to change). The queries are like the following (though substantially more complex, most of the time): SELECT fooA.field1 as field1B, fooA.field2 as field1C FROM foo as fooA ...
I have two instances of the same database. The first db represents data from today, the second data from 6 months ago. I need to find differences for a subset of entries in a specific table. For entries with ids that are in both tables, I'd like to find a way to view only the rows that aren't identical. Any ideas? Thanks ...
So I need to pull the e-mail addresses of members who have not logged into my site in the past 30 days. Since the site is over a few years old, we want to pull only a handful of each member from certain Quarters of the year. So for instance, there are about 800,000 people who haven't logged in within the past 30 days. So we want to pu...
I'm looking for some ideas on methods for persisting customer configurable data in a relational database (SQL Server 2000 in my case). For example let's say you have a standard order entry application where your customer enters a product they want to buy. In addition to the fields that are important to both you and the customer (item...
I have the following table: Table A FNAME | LNAME james | Bond John | Brit raje | van I want to insert first letter from first column with full last name to create a new username column for the table: Table A USERNAME jbond jbrit rvan If this is not possible, I at least need to update the lastname from lname to newly created user...
I have a task which require the data manipulation to transpose the rows to columns. The data is stored in SQL Server, typical relational database model. I know it can be done with TSQL, but it is so complex that there are almost ten different row groups to be transposed to about 200 columns. Just wondering whether there is other better...
The SQL below returns any records with min number and it should work fine: SELECT Id, Number FROM TableA WHERE Number = (select Min(Number) from TableA) Is there way I can write a SQL without the sub Select statement, but still returns the same result? ...
I want get the maximum value for this record. Please help me: SELECT rest.field1 FROM mastertable AS m INNER JOIN ( SELECT t1.field1 field1, t2.field2 FROM table1 AS T1 INNER JOIN table2 AS t2 ON t2.field = t1.field WHERE t1.field3=MAX(t1.field3) -- ...
Hi I am looking for A solution to update values in table1 only if the values change. I mean compare to tableb and update only the changed values ...
I have a loginAudit table and I am trying to get a count for all logins for each day. What I'd like to do is have days where there are no logins return their day and a login count of 0. Currently no row is returned for days with no logins. Could be this isn't possible and have to fill in empty groups in the app after query results re...
I'd like to clear a database's schema on my SqlServer instance. What tsql should I use? By schema I mean tables, constraints, etc. I want the result to be similar to if I created a new database, however I don't want to actually drop and create the database. Why: For those curious, I need to empty the schema without dropping because of...