Update: This is a bug but won't get fixed until the next release of SQL Server due to backward compatibility concerns. This is following on from this question which I answered but am still puzzled by. Adding TOP (1) to a query is sufficient to change the result from "Sep 3 2010" to "2010-09-03" (at least on my machine with British se...
Does anyone have information on the file format used to hold SQL Profiler Templates (NOT Profiler trace files)? Or a way to create or parse these via C#? Thanks very much. ...
I need to have a MsSql database table and another 8 (identical) processes accessing the same table in parallel - making a select top n, processing those n rows, and updating a column of those rows. The problem is that I need to select and process each row just once. This means that if one process got to the database and selected the top ...
Good Morning All, I'm trying to refactor an SQL stored procedure. I'm no SQL expert, but something tells me there must be a better way to do this. IF @ipv_dtEndDate IS NOT NULL BEGIN SET @ipv_dtEndDate = DATEADD(hh,23,@ipv_dtEndDate) SET @ipv_dtEndDate = DATEADD(mi,59,@ipv_dtEndDate) SET @ipv_dtEndDate = DAT...
I have SQL 2008 (not R2). I would like to have a matrix report where user can select one of the SQL resultset columns to be the matrix column group.. For example A B Value a1 b1 10 a2 b2 20 a3 b2 30 So the possible matrices could be (user selects from dropdown with A, B). By A a1 a2 a3 Value 10 20 30 By B ...
I'm trying to use the 'For Xml Path' T-SQL to generate a comma separated list of values from a column. This seems to work great, but the problem is I would like to get a count of the items in the comma separated list. Here is an example of the code I am using to generate the comma separated list: Create Table #List ([col] varchar) Inse...
I have an NHibernate entity which contains a many-to-many list of related items. When a certain property of the entity changes, a database trigger is called that removes all associations between the entity and any entities that it is joined to. However, NHibernate throws a StaleStateException with a message along the lines of: Batch u...
So on my test machine, I had PHP installed with Apache, and I had the PHP SQL Driver (not the Microsoft one). So, I used mssql_connect() and such commands to deal with the database. When I shifted to another server, it has Microsoft PHP SQL Driver. Now it is crashing and showing me error of PHP_via_FastCGI error, whenever I execute the ...
Suppose I have this data: Employee Task IsCurrent -------- ---- --------- Jeff 1 Yes Jeff 2 No Jane 1 No Jane 2 Yes Jane 3 No Jane 4 No Jake 1 Yes Employees have some number of tasks, and one of them will be current. The task number indicates an ordering -- t...
SO really there are two parts of the question: 1) how to use CONTAINSTABLE() with bunch of inner joins? Suppose there are two tables: Item (ItemID, CategoryID, Name) Category (CategoryID, ParentCategoryID, Name) using "dumb" like search this looks like this: create view [db].[vItem] as select i.*, c1.Name as SubCategory, c2.Name as...
Thank you for your help in advance. I am looking for a neat solution for the following update scenario: I have a table TableA with 5 columns (KeyCol, colA, ColB, ColC, ColD) I have another table Table2 with 2 columns (KeyCol, AvgCol) I want to do something like this: update AvgCol in table2, joining on KeyCol from this logic: if...
Hi there. I want to know if I can create an Identity (auto increment on a Varchar column. and how can I make it a primary key and create foreign key references on other table. This is the code i have - CREATE TABLE Questions( QuestionID int IDENTITY PRIMARY KEY, QuestionNo as 'Q'+Cast(QuestionID as Varchar(10), Que...
We upgraded our application to .NET 4.0 a while ago, and now need to add the ability to backup SQL Server 2008 databases from the app. However, it doesn't seem that SMO is available for .NET 4. Is there a workaround or better way to perform a database backup (note that our backups need to be user initiated over a website, so we cannot ...
I am trying to restore a database in my sql server 2005 express edition. I know that to restore the database I need to make it to single user. I am giving this command to make it to single user use [master] alter database database_name set single_user with rollback immediate This command executed properly and I can even see a small ima...
I have a bunch of data in MS Access. I want to create INSERT statements from the data with the purpose of running them in SQL Server. The table structure between Access and SQL Server is the same. I don't have the option of doing an export/import because I don't have direct access to the SQL Server. It is a web host's server and they...
Suppose i have this sql statement and I have executed a sql command to get a datareader: "select 1 union select 2" //..... var rdr = cmd.ExecuteReader(); and now i want to read the value in the first column of the first row: var myInt = (int)rdr.GetValue(0); //great this works var myLong = (long)rdr.GetValue(0); //throws cast excepti...
I have records in a table representing bus routes, with SQL Server spatial columns for the actual route geometry. (They're stored in a Geography column type.) I use OpenLayers to display these bus routes on top of an OpenStreetMap layer. In instances where the bus routes overlap, currently you cannot see anything but the top route. I'v...
Hi, I want to store comment replies in database table. I have a table to store comments: comment_id comment_par_id, comment_from comment_text comment date .... New comment has par_id=0 while the replies has par_id set to comment id to which it was replied. The nesting is just one level. Reply to a reply also has the same parent id. ...
For a recent test, I want to 'create' a query which takes atleast 5 seconds to execute and does not change the schema or data of the database - although I can create a table and then delete it. Till that level its ok . How can I do this? ...
Why the error does not appear at the UI layer? I am using ExecuteScaler BEGIN CATCH PRINT N'The transaction is in an uncommittable state. Rolling back transaction.' ROLLBACK TRANSACTION; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), ...