I have a SQL 2005 x64 server, and when I attempt to issue some queries against it (for example, when I try to create an index), my SPID goes to "sleeping" immediately, and seems to wait there indefinitely. It's not being blocked (the "BLKBY" column in SP_WHO2 is empty), and the CPU and DiskIO values are very small (under 300 each), and n...
Using SQL Server 2005, I am working with a database which has two columns to keep track of what is essentially a single DateTime (and I can't for the life of my figure out why anyone would ever think this is a good idea). Basically, one column, let's call it OrderDate, contains a DateTime like this: 2008-02-29 00:00:00.000, and the oth...
Does cardinality play a role in composite indexes? If so, what? I was running a query that was joining on two columns and it used what I thought to be a sup-optimal index, so it's making me rethink how I design indexes... Let's say we had a table that listed all of the Cities in the United States. My first instinct here is to make a cl...
I am looking for a good intro (not a book!) to XPath with SQL Server 2005. Anyone has a link for me? P.S. I don't really want to learn it (yet), I just want to fix my problem now :) So something with a bunch of examples would be useful. Thanks. Edit: Ok. Here is my problem: From the following XML that is within a column, I want to kn...
Hi, I want to return the date and ID for the latest added record in on of our tables. can anyone suggest right query for that plz. We are using sqlServer SELECT [BGArx_ID], [BGArx_PUBLISHED_DATE] FROM TECH_ARTICLES WHERE [BGArx_PUBLISHED_DATE] = ??? ...
I am trying to use pivot on a table to rotate(Transpose) the table but i m not getting how to do that. I want to know how it actually works. i found many egs but i was not able to understant how those work. Check this, e.g. How tis is working, i just want to transpose my rows into cols and cols into rows Say i have 31 cols for day1 da...
I'm seeing behavior which looks like the READPAST hint is set on the database itself. The rub: I don't think this is possible. We have table foo (id int primary key identity, name varchar(50) not null unique); I have several threads which do, basically id = select id from foo where name = ? if id == null insert into foo (name) ...
Hi: I have a scenario and not quite sure how to query it. As a sample, I have following table structure and want to get the history of the action for bus: ID-----TIME---------BUSID----OPID----MOVING----STOPPED----PARKED----COUNT 1------10:10:10-----101------1101-----1---------0----------0---------15 2------10:10:11-----102------1102--...
Hi all, I have the following query, which uses a CASE statement. Is there anyway to add into the where clause WHERE IsBusinessDayFinal = 0? without using a temporary table? thanks so much! SELECT ac.DateTimeValue, CASE WHEN pc.IsBusinessDay IS NOT NULL THEN pc.IsBusinessDay ELSE ac.IsBusinessDay END AS IsB...
We wish to expose some of the application data (in MSSQL Server) to external users so that they can consume it in their OWN reporting environment. Which would be the better options in terms of usability, ease of report creation, security or performance if the external users wish to do reporting in SSRS or Crystal reports: Exposing da...
I have SQL Server 2008 and VS2008 installed on my computer and I am trying to run a web application I've created on a computer which has SQL Server 2008 and VS2008 and VS2010 beta 2 with its SQL Server Express. I am getting an error with the database version that is "...aspnetdb.mdf" cannot be opened because it is version 655. This ser...
I have a .aspx page calling a .asmx page's web service. In that .net web service I attempt to open a database connection to SQLServer 2008. The connection is failing. I am not sure why. I am doing this in a try / catch and the catch does get hit when I debug. I'm not sure what I can output there though as I don't have access to the serve...
Hi I am having the following error {"Unable to cast object of type 'System.Linq.Expressions.MethodCallExpression' to type 'SubSonic.Linq.Structure.ProjectionExpression'."} System.SystemException {System.InvalidCastException} And My Code is var results1 = from la in db.LoanApplications join ld in db.LookUpDeta...
Hi, I am using sql server 2008. Is there a way to generate a unique sequential number? It should exhibit properties of NEWID() and an identity column, meaning that it is always unique but each subsequent value is greater than the previous one. It can't be datetime/datetime2, because it is not unique enough. Thanks. ...
Hi, I've SQL Server 2008 Express installed. I've just download and installed SQL Server 2005 Express, but I changed the name of instance into SQLEXPRESS2005 (I added 2005 at the end to avoid conflicting names). Unfortunately, when I try to access the 2005 instance either using the SQL Server Management Studio or the VWD 2008 Express (...
Why table params aren't allowed in SQL Server? Is there any solution to this? Example: using (SqlCommand myCommand = new SqlCommand("SELECT * FROM @table WHERE USERNAME=@username AND PASSWORD=HASHBYTES('SHA1', @password)", myConnection)) { myCommand.Parameters.AddWithValue("@table", table); myCommand.Parameters.Add...
In an insert trigger I use table 'INSERTED' to get the inserted values. Do I use same INSERTED table in update trigger as well, or here comes in an 'UPDATED' table? ...
Is there a nice way in MySQL to replicate the MS SQL Server function ROW_NUMBER()? For example: SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1 Then I could, for example, add a condition to limit intRow to 1 to get a single row with the highest col3 for each (col1, col...
There are approx 500 sprocs in my SQLSERVER 2000 database; each sproc has a typical Grant Execute statement similar to the following. GRANT EXECUTE ON [dbo].[sproc_name] TO [role1], [role2], [role3], [role4], etc... How to view the names of the sprocs which have grant to a particular role and only that particular role exclusively...
In an environment with a SQL Server failover cluster or mirror, how do you prefer to handle errors? It seems like there are two options: Fail the entire current client request, and let the user retry Catch the error in your DAL, and retry there Each approach has its pros and cons. Most shops I've worked with do #1, but many of them...