Yes, SQL 11 has SEQUENCE objects, see SQL Server v.Next (Denali) : Using SEQUENCE. Creating manual sequences is possible, but not recommended. The trick to do a sequence generator is to use UPDATE WITH OUTPUT on a sequences table. Here is pseudo-code: CREATE TABLE Sequences ( Name sysname not null primary key, Sequence bigint not … Read more
It is possible using Apex Recovery Tool,i have successfully recovered my table rows which i accidentally deleted if you download the trial version it will recover only 10th row check here http://www.apexsql.com/sql_tools_log.aspx
2627 is unique constraint (includes primary key), 2601 is unique index SELECT * FROM sys.messages WHERE text like ‘%duplicate%’ and text like ‘%key%’ and language_id = 1033
Since Table2 has a composite primary key (FileID, FileType), then any reference to it must also include both columns. ALTER TABLE dbo.Table1 ADD CONSTRAINT FK_Table1_Table2 FOREIGN KEY(FileID, FileType) REFERENCES Table2(FileID, FileType) Unless you have a unique constraint/index on the Table2.FileID field (but if so: why isn’t this the PK??), you cannot create a FK relationship … Read more
If the two databases are on the same server, you should be able to create a SQL statement something like this: UPDATE Test1.dbo.Employee SET DeptID = emp2.DeptID FROM Test2.dbo.Employee as ’emp2′ WHERE Test1.dbo.Employee.EmployeeID = emp2.EmployeeID From your post, I’m not quite clear whether you want to update Test1.dbo.Employee with the values from Test2.dbo.Employee (that’s what … Read more
The best way to strip the time portion of a datetime field is using datediff and dateadd functions. DateAdd(day, datediff(day,0, MydateValue), 0) This takes advantedge of the fact that SQL Server stores dates as two integers, one representing the number of days since day “0” – (1 jan 1900), and the second one which represents … Read more
Note that TRUNCATE won’t work if you have any referential integrity set. In that case, this will work: EXEC sp_MSForEachTable ‘DISABLE TRIGGER ALL ON ?’ GO EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’ GO EXEC sp_MSForEachTable ‘SET QUOTED_IDENTIFIER ON; DELETE FROM ?’ GO EXEC sp_MSForEachTable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL’ GO … Read more
You need to fix this: SELECT @listCol = STUFF(( SELECT distinct ‘], [‘ + [size] FROM #t FOR XML PATH(”) ), 1, 2, ”) + ‘]’ To return the columns in the right order. You might have to do something like this instead of using DISTINCT: SELECT [size] FROM #t GROUP BY [size] ORDER BY … Read more
There is a link here for other earlier attempts http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510 This is the OLD code for the function CREATE function f_isoweek(@date datetime) RETURNS INT as BEGIN DECLARE @rv int SELECT @rv = datediff(ww, dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4),day4) FROM (SELECT dateadd(ww, datediff(day, 0, @date)/7, 3) day4) a RETURN @rv END After combining @AndriyM … Read more
You can get the difference between the two dates to whatever resolution you want (in your example, minutes): DATEDIFF(minute, @start_date, @end_date) From there it’s a simple matter of dividing minutes into hours and hours into days and modding the remainder.