The DDL creates the schema and data. I am looking for a where statement where it returns only one row for each ID and that row would be the last inserted row based on the inserteddate column. So the result would be John, 5 and Debbie, 5 select Table1.Name, Table2.Rating From table1 join table2 on table1.ID = table2.ID where inserteddat...
I have 2 tables: "orders" and "visits". in the orders table i'm saving some details about the order including "userIP" and "orderDate". in the visits table i'm saving details everytime a user visiting my web page including "userIP" and "visitDate". i'm using ASP.NET and SQL SERVER 2005. i want to create a statistic table whitin i save t...
Suppose you're updating a table that is the result of an expression, like so UPDATE OPENQUERY( server, 'SELECT a from b' ) SET rem.a = loc.a FROM OPENQUERY( server, 'SELECT a from b' ) rem ,local_table loc WHERE rem.id = loc.id Will that OPENQUERY be executed once, or twice? I believe the same would apply to any kind of u...
I've designed my SQL CE tables using the built-in designer in VS2008. I chose the wrong names for a couple. I am now completely stuck trying to find a way to rename them. I am refusing to believe that such a feature could have been "forgotten". How do I rename an existing table using the VS2008 designer, or a free stand-alone app? ...
I am working with sensitive/private files stored in SQL Server as VarBinary(MAX). Is there a way to tell the database or nHibernate to nullify the column after a period of time after its creation. ...
I have got a bit of 'brain fade' going on this afternoon, so if anyone can help with this mssql query it would be fantastic. I have a table called 'seasons' with three columns (there are more but not relevant to the example): seasonId, date, tariffId SeasonId is a unique key. A date can only have one tariffid, but a tariffId can have m...
In a stored procedure I'm trying to conditionally update a field (like the 2nd line in the SQL statement below) UPDATE [some_stuff] SET last_update = CASE WHEN val = @NewVal THEN last_update ELSE GETDATE() END, val = @NewVal ...but for a text/ntext field. What's the most efficient way to go about doing that? Does it have to be a s...
Hi folks, I've got the following trigger on a table for a SQL 2008 database. It's recursing .. so i need to stop it. After I insert or update a record, I'm trying to then update a single field on that table. Simple. Here's the trigger :( ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate] ON [dbo].[tblMedia] BEFORE INSERT, UPDA...
I want to know whether one SP is referenced anywhere. Currently I am checking using SP_DEPENDS. Is there any other way to check this...? ...
Using SQL Server 2000 Table1 Date Holiday 23-02-2009 0 24-02-2009 1 25-02-2009 0 Table2 ID Date 01 23-02-2009 01 24-02-2009 02 25-02-2009 …, Here I want to skip the date where Holiday =1 Expected Output 01 23-02-2009 02 25-02-2009 How to make a query for this condition? ...
i have the a query which is union of two querries, the resulting query is bringing duplicate records, i dont want duplicate records, i tried putting DISTINCT but getting the same result, can anybody help me rectifying this query. i also want to know whether this query is safe from sql injection...i'll be pasting my query below: ALTER PR...
Hey. I have table A. This table does not have any PK, it just stores lots of rows, which can only be identified by combination of its column values. There is procedure that takes data from table A, and from other tables, does proper matching/processing and feeds table B. Now, how do I check if data from table A is correctly inserted into...
Hi, i have this table: Location date temp 1 12-12-2009 19 1 14-12-2009 21 1 13-12-2009 17 2 12-12-2009 18 2 14-12-2009 1...
Hi there, maybe anyone can help me out with my SELECT statement on MS SQL Server. I am not very skilled in (T)SQL. I have a table called RECORDS and with a DATETIME column called [BEGIN]. Now I would like to get some nvarchars which look like this "December 08, January 09, February 09".. I came up with the following myself. SELECT DI...
Is it possible to raise an error in a stored procedure manually to stop execution and jump to BEGIN CATCH block? Some analog of throw new Exception() in C#. Here is my stored procedure's body: BEGIN TRY BEGIN TRAN -- do something IF @foobar IS NULL -- here i want to raise an error to rollback transaction -- do something next C...
I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. Its something like 15 seconds vs milliseconds. To simplify the test instead of joining the CTE/Temp table in the subsequent q...
Given the following sample of XML and the select statement that shreds the xml into a relation, what I need is the second column of the select to be the ordinal of the category (ie 1 for the directions and 2 for the colours in this case). Note: The literal value 'rank()' in the select is left a placeholder. I was poking around with usin...
I need to create a query that will sum the number of True(1) and False(0) into two separate columns from one bit field. I'm joining 3 tables and need it to be something like: Attribute | Class | Pass | Fail I will be grouping on Attribute and Class. ...
I have a SQLClient.DataSet in VB.NET, and I want to insert the entire thing into a SQL Server table without having to do the following: For Each dr as Datarow in MyDataset Dim sc As New SqlCommand("INSERT INTO MyNewTable " & _ "VALUES (@column1, @column2)", MyDBConnection) sc.Parameters.AddWithValue("@col...
So I came across an instance where a stored procedure which handled the Updates on a specific table was doing nothing because the record didn't exist [duh]. So what I decided to do was change the stored procedure to: UPDATE Table SET col1 = @var1 WHERE Type = @type AND UserId = @userId IF @@ROWCOUNT = 0 BEGIN INSER...