tsql

SQL Server "RESTORE FILELISTONLY" Resultset

I'm trying to write an automated backup and restore T-SQL scripts. I've done BACKUP part but I'm struggling on RESTORE. When I run following statement on SS Management Studio; EXEC('RESTORE FILELISTONLY FROM DISK = ''C:\backup.bak''') I get a result set in a grid and also I can use INSERT INTO <temp_table> EXEC('RESTORE FILELISTO...

What's an efficient way to convert month and year string to a date?

I have column with varchar values like "022008" that I need to convert into a datetime like "02/01/2008". This will run on millions of records for a conversion process. What is an efficient select statement for doing this? ...

Sql Server - INSERT INTO SELECT to avoid duplicates

I have following two tables:- Table1 ------------- ID Name 1 A 2 B 3 C Table2 -------- ID Name 1 Z I need to insert data from Table1 to Table2 and I can use following sytax for the same:- INSERT INTO Table2(Id, Name) SELECT Id, Name FROM Table1 However, In my case duplicate Ids might exist in Table2 (In my case Its Just "1") ...

T-SQL: Using OVER and PARTITION BY

Hello, I have the following data | Item | Value | Date | ------------------------------ | 1 | 10 | 01.01.2010 | 1 | 20 | 02.01.2010 | 1 | 30 | 03.01.2010 | 1 | 40 | 04.01.2010 | 1 | 50 | 05.01.2010 | 1 | 80 | 10.01.2010 | 2 | 30 | 04.01.2010 | 2 | 60 | 06...

using "in" with a case statement

Hi Is it possible to write something like Case when a in (value1,value2) then b else a end ...

What is the best way to apply rowlevel writelock in T-SQL? Best Practices And Samples Please

What is the best way to apply rowlevel writelock in T-SQL? Best Practices And Samples Please ...

Using Stored Procedure into Select (T-SQL)

Hi, I need to access result of a stored procedure withinf a select statement (ie : SELECT * FROM [dbo].[sp_sample]) in SQL_Server 2005. Regards, Stéphane ...

Query to get row from one table, else random row from another

tblUserProfile - I have a table which holds all the Profile Info (too many fields) tblMonthlyProfiles - Another table which has just the ProfileID in it (the idea is that this table holds 2 profileids which sometimes become monthly profiles (on selection)) Now when I need to show monthly profiles, I simply do a select from this tblMont...

LEFT OUTER JOIN in Linq - How to Force

I have a LEFT OUTER OUTER join in LINQ that is combining with the outer join condition and not providing the desired results. It is basically limiting my LEFT side result with this combination. Here is the LINQ and resulting SQL. What I'd like is for "AND ([t2].[EligEnd] = @p0" in the LINQ query to not bew part of the join condition b...

SQL DATEDIFF Not working!?

Hi all, I am running a simple DATEDIFF query but it doesn't seem to calculate the days properly or i'm doing something wrong. If I run PRINT DATEDIFF(Day, 2010-01-20, 2010-01-01) RETURN 19 Which is correct. If i change the month in the first date to Feb (02) I get something strange. PRINT DATEDIFF(Day, 2010-02-20, 2010-01-01) RETUR...

Disable all non-clustered indexes

I select a number of non-clustered indexes from my database with the following: SELECT sys.objects.name tableName, sys.indexes.name indexName FROM sys.indexes JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id WHERE sys.indexes.type_desc = 'NONCLUSTERED' AND sys.objects.type_desc = 'USER_TABL...

Compound IDENTITY column in SQL SERVER 2008

An Orders table has a CustomerId column and an OrderId column. For certain reasons it's important that an order's id is no longer than 2-bytes. There will be several million orders in total, which makes 2-bytes not enough for a global order id. A customer will have no more than several thousand orders making 2-bytes enough. The obviou...

Odd SQL behavior, I'm wondering why this works the way it does.

Consider the following Transact sql. DECLARE @table TABLE(val VARCHAR(255) NULL) INSERT INTO @table (val) VALUES('a') INSERT INTO @table (val) VALUES('b') INSERT INTO @table (val) VALUES('c') INSERT INTO @table (val) VALUES('d') INSERT INTO @table (val) VALUES(NULL) select val from @table where val not in ('a') I would expect this...

I need some ideas on my algortihm for a Hit Counter (Group By Time Interval)

My algorithm is for a 'hit counter', I am trying to not count the same person twice if that person came to the site twice in a time interval (For example if he comes twice in 5 minutes, I want to count it as 1 hit for this person) Here's what my database looks like UserIp UserId Date of user came 127.0.0.1 new.user.akb ...

Retrieving a filtered list of stored procedures using t-sql

I'm trying to get a list of stored procedures in t-sql. I am using the line: exec sys.sp_stored_procedures; I would like to filter the results back though, so I only get user created stored procedures. I would like to filter out sp_*, dt_*, fn_*, xp_* and everything else that is a system stored procedure and no interest to me. How ...

Creating stored procedure having different WHERE clause on different search criteria without putting all in long string.

Is there any alternate way to create stored procedure without putting all query in one long string if criteria of WWHERE clause can be different. Suppose I have Orders table I want to create stored procedure on this table and there are three column on which I wnat to filter records. 1- CustomerId, 2- SupplierId, 3- ProductId. If user ...

Error creating a table : "There is already an object named ... in the database", but not object with that name

Hi, I'm trying to create a table on a Microsoft SQL Server 2005 (Express). When i run this query USE [QSWeb] GO /****** Object: Table [dbo].[QSW_RFQ_Log] Script Date: 03/26/2010 08:30:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[QSW_RFQ_Log]( [RFQ_ID] [int] NOT NULL...

Assign table values to multiple variables using a single SELECT statement and CASE?

I'm trying to assign values contained in a lookup table to multiple variables by using a single SELECT having multiple CASE statements. The table is a lookup table with two columns like so: [GreekAlphabetastic] SystemID Descriptor -------- ---------- 1 Alpha 2 Beta 3 Epsilon This is my s...

Random Function from SQL Server database in ASP .NET

I am creating a webpage which is using asp .net as its backend. I need to grab categories from my database which is SQL Server. I am using this code right now For Each row00 As DataRow In f_oDatatable(7).Rows sCategories += row00.Item("Category").ToString & " / " Next If sCategories.Length > 0 Then sCategories = sCategories.Su...

Selecting a user-defined scalar function that takes as a parameter another field

I have a table a with a list of id's, and a user-defined function foo(id) that takes the id and returns a VARCHAR(20). What I am trying to do is: SELECT id, foo(id) AS 'text field' FROM a However, instead of calling the function for each ID number, like I desired, the text comes back the same for every row. I have tested the foo() f...