Saturday, April 6, 2013

[T-SQL] Retrieve Latest and Previous Record

[T-SQL] Retrieve Latest and Previous Record

Retrieve Latest and Previous Record

Posted: 05 Apr 2013 03:53 AM PDT

Any idea how to return the latest and previous record (TOP 2) from a table and have them on the same line, side-by-side. I tried using a CTE and partitioning, but I couldn't figue out how to union two CTE's. Don't know if that is even possible. Any help or ideas will be appreciated.

Change case logic to boolean logic

Posted: 05 Apr 2013 08:26 AM PDT

Hi All,I am trying to convert a case login to boolean logic, but I could not figure out one part of it.Here is the DDL and the query I have done so far. [code="sql"]CREATE TABLE #t ( c1 INT, c2 INT, r1 INT ) INSERT INTO #t SELECT 1, 2, 1 UNION ALL SELECT 1, 1, 1 UNION ALL SELECT 2, 1, 1 UNION ALL SELECT 2, 3, 1 UNION ALL SELECT 2, 2, 5 UNION ALL SELECT 2, 1, 5 SELECT c1, c2, r1, CASE WHEN c1 = 2 THEN ( CASE WHEN c2 = 2 THEN 'X' WHEN r1 = 1 THEN 'O' ELSE 'O' END ) END 't1', CASE WHEN ( ( c1 = 2 OR r1 = 1 ) AND ( c2 <> 2 ) ) THEN 'O' ELSE 'X' END 't2' FROM #t [/code]Column "t1" is the case statement and Column "t2" I am trying to change the case to boolean (Not case with in case)Thanks in advance!!

Need Minimally Logged Operation to Cause Checkpoint!

Posted: 05 Apr 2013 01:59 AM PDT

I am preparing for a presentation next week and am digging for an example....I need a minimally logged operation that will show the behavior that a checkpoint is ran when a minimally logged operation occurs while in SIMPLE or BULK_LOGGED recovery model.I have tried rebuilding, creating and dropping indexes...I have created heaps using SELECT..INTO, but nothing seems to consistently generate a CHECKPOINT.What am I doing wrong?Here is where I read that they should cause checkpoints:[url=http://technet.microsoft.com/en-us/library/ms189573(v=sql.105).aspx]us/library/ms189573(v=sql.105).aspx[/url]Here is the list I am using for minimally logged operations:[url=http://msdn.microsoft.com/en-us/library/ms191244(v=sql.105).aspx]http://msdn.microsoft.com/en-us/library/ms191244(v=sql.105).aspx[/url]I am using the following to look for checkpoints:[code="sql"]select * from fn_dblog(null,null) WHERE Operation IN ('LOP_BEGIN_CKPT','LOP_END_CKPT')[/code]Any help would be greatly appreciated!

Tracking changes to SQL Server

Posted: 05 Apr 2013 02:26 AM PDT

I was wondering if a SQL trace could be set up to track changes to SQL Server, so I can track who is making changes and keep the Recovery Document up to date. So I would not want to track things... like users and logins. In fact I do not need to track anything below the database level, but one issue that came up today... was that someone had deleted a linked server.Going through Profiler, I tried to build the trace... but I am not sure if my selections are correct.-- Trying to create a trace to caputre changes to the SQL Server Instancedeclare @rc intdeclare @TraceID intdeclare @maxfilesize bigintdeclare @OutputFileName nvarchar(256)set @maxfilesize = 10 set @OutputFileName = 'F:\tracefiles\Track_Instance_Changes' + '_' + convert(varchar(20), getdate(),112) + Replace(convert(varchar(20), getdate(),108),':','')exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, NULL if (@rc != 0) goto error-- Set the eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID, 117, 7, @onexec sp_trace_setevent @TraceID, 117, 23, @onexec sp_trace_setevent @TraceID, 117, 8, @onexec sp_trace_setevent @TraceID, 117, 40, @onexec sp_trace_setevent @TraceID, 117, 64, @onexec sp_trace_setevent @TraceID, 117, 1, @onexec sp_trace_setevent @TraceID, 117, 9, @onexec sp_trace_setevent @TraceID, 117, 41, @onexec sp_trace_setevent @TraceID, 117, 49, @onexec sp_trace_setevent @TraceID, 117, 6, @onexec sp_trace_setevent @TraceID, 117, 10, @onexec sp_trace_setevent @TraceID, 117, 14, @onexec sp_trace_setevent @TraceID, 117, 26, @onexec sp_trace_setevent @TraceID, 117, 50, @onexec sp_trace_setevent @TraceID, 117, 3, @onexec sp_trace_setevent @TraceID, 117, 11, @onexec sp_trace_setevent @TraceID, 117, 35, @onexec sp_trace_setevent @TraceID, 117, 51, @onexec sp_trace_setevent @TraceID, 117, 12, @onexec sp_trace_setevent @TraceID, 117, 44, @onexec sp_trace_setevent @TraceID, 117, 60, @onexec sp_trace_setevent @TraceID, 117, 5, @onexec sp_trace_setevent @TraceID, 117, 21, @onexec sp_trace_setevent @TraceID, 117, 29, @onexec sp_trace_setevent @TraceID, 117, 37, @onexec sp_trace_setevent @TraceID, 129, 7, @onexec sp_trace_setevent @TraceID, 129, 23, @onexec sp_trace_setevent @TraceID, 129, 8, @onexec sp_trace_setevent @TraceID, 129, 40, @onexec sp_trace_setevent @TraceID, 129, 64, @onexec sp_trace_setevent @TraceID, 129, 1, @onexec sp_trace_setevent @TraceID, 129, 41, @onexec sp_trace_setevent @TraceID, 129, 49, @onexec sp_trace_setevent @TraceID, 129, 6, @onexec sp_trace_setevent @TraceID, 129, 10, @onexec sp_trace_setevent @TraceID, 129, 14, @onexec sp_trace_setevent @TraceID, 129, 26, @onexec sp_trace_setevent @TraceID, 129, 34, @onexec sp_trace_setevent @TraceID, 129, 50, @onexec sp_trace_setevent @TraceID, 129, 3, @onexec sp_trace_setevent @TraceID, 129, 11, @onexec sp_trace_setevent @TraceID, 129, 35, @onexec sp_trace_setevent @TraceID, 129, 51, @onexec sp_trace_setevent @TraceID, 129, 4, @onexec sp_trace_setevent @TraceID, 129, 12, @onexec sp_trace_setevent @TraceID, 129, 28, @onexec sp_trace_setevent @TraceID, 129, 60, @onexec sp_trace_setevent @TraceID, 129, 5, @onexec sp_trace_setevent @TraceID, 129, 21, @onexec sp_trace_setevent @TraceID, 129, 29, @onexec sp_trace_setevent @TraceID, 129, 37, @onexec sp_trace_setevent @TraceID, 178, 7, @onexec sp_trace_setevent @TraceID, 178, 23, @onexec sp_trace_setevent @TraceID, 178, 8, @onexec sp_trace_setevent @TraceID, 178, 40, @onexec sp_trace_setevent @TraceID, 178, 64, @onexec sp_trace_setevent @TraceID, 178, 1, @onexec sp_trace_setevent @TraceID, 178, 41, @onexec sp_trace_setevent @TraceID, 178, 49, @onexec sp_trace_setevent @TraceID, 178, 6, @onexec sp_trace_setevent @TraceID, 178, 10, @onexec sp_trace_setevent @TraceID, 178, 14, @onexec sp_trace_setevent @TraceID, 178, 26, @onexec sp_trace_setevent @TraceID, 178, 34, @onexec sp_trace_setevent @TraceID, 178, 50, @onexec sp_trace_setevent @TraceID, 178, 3, @onexec sp_trace_setevent @TraceID, 178, 11, @onexec sp_trace_setevent @TraceID, 178, 35, @onexec sp_trace_setevent @TraceID, 178, 51, @onexec sp_trace_setevent @TraceID, 178, 4, @onexec sp_trace_setevent @TraceID, 178, 12, @onexec sp_trace_setevent @TraceID, 178, 28, @onexec sp_trace_setevent @TraceID, 178, 60, @onexec sp_trace_setevent @TraceID, 178, 21, @onexec sp_trace_setevent @TraceID, 178, 29, @onexec sp_trace_setevent @TraceID, 178, 37, @onexec sp_trace_setevent @TraceID, 130, 7, @onexec sp_trace_setevent @TraceID, 130, 23, @onexec sp_trace_setevent @TraceID, 130, 39, @onexec sp_trace_setevent @TraceID, 130, 8, @onexec sp_trace_setevent @TraceID, 130, 40, @onexec sp_trace_setevent @TraceID, 130, 64, @onexec sp_trace_setevent @TraceID, 130, 1, @onexec sp_trace_setevent @TraceID, 130, 41, @onexec sp_trace_setevent @TraceID, 130, 49, @onexec sp_trace_setevent @TraceID, 130, 10, @onexec sp_trace_setevent @TraceID, 130, 26, @onexec sp_trace_setevent @TraceID, 130, 34, @onexec sp_trace_setevent @TraceID, 130, 42, @onexec sp_trace_setevent @TraceID, 130, 50, @onexec sp_trace_setevent @TraceID, 130, 3, @onexec sp_trace_setevent @TraceID, 130, 11, @onexec sp_trace_setevent @TraceID, 130, 35, @onexec sp_trace_setevent @TraceID, 130, 43, @onexec sp_trace_setevent @TraceID, 130, 51, @onexec sp_trace_setevent @TraceID, 130, 4, @onexec sp_trace_setevent @TraceID, 130, 12, @onexec sp_trace_setevent @TraceID, 130, 28, @onexec sp_trace_setevent @TraceID, 130, 60, @onexec sp_trace_setevent @TraceID, 130, 21, @onexec sp_trace_setevent @TraceID, 130, 29, @onexec sp_trace_setevent @TraceID, 130, 37, @onexec sp_trace_setevent @TraceID, 130, 6, @onexec sp_trace_setevent @TraceID, 130, 14, @onexec sp_trace_setevent @TraceID, 118, 7, @onexec sp_trace_setevent @TraceID, 118, 23, @onexec sp_trace_setevent @TraceID, 118, 8, @onexec sp_trace_setevent @TraceID, 118, 40, @onexec sp_trace_setevent @TraceID, 118, 64, @onexec sp_trace_setevent @TraceID, 118, 1, @onexec sp_trace_setevent @TraceID, 118, 9, @onexec sp_trace_setevent @TraceID, 118, 41, @onexec sp_trace_setevent @TraceID, 118, 49, @onexec sp_trace_setevent @TraceID, 118, 6, @onexec sp_trace_setevent @TraceID, 118, 10, @onexec sp_trace_setevent @TraceID, 118, 14, @onexec sp_trace_setevent @TraceID, 118, 26, @onexec sp_trace_setevent @TraceID, 118, 34, @onexec sp_trace_setevent @TraceID, 118, 50, @onexec sp_trace_setevent @TraceID, 118, 3, @onexec sp_trace_setevent @TraceID, 118, 11, @onexec sp_trace_setevent @TraceID, 118, 35, @onexec sp_trace_setevent @TraceID, 118, 51, @onexec sp_trace_setevent @TraceID, 118, 4, @onexec sp_trace_setevent @TraceID, 118, 12, @onexec sp_trace_setevent @TraceID, 118, 28, @onexec sp_trace_setevent @TraceID, 118, 60, @onexec sp_trace_setevent @TraceID, 118, 5, @onexec sp_trace_setevent @TraceID, 118, 21, @onexec sp_trace_setevent @TraceID, 118, 29, @onexec sp_trace_setevent @TraceID, 118, 37, @onexec sp_trace_setevent @TraceID, 176, 7, @onexec sp_trace_setevent @TraceID, 176, 23, @onexec sp_trace_setevent @TraceID, 176, 8, @onexec sp_trace_setevent @TraceID, 176, 40, @onexec sp_trace_setevent @TraceID, 176, 64, @onexec sp_trace_setevent @TraceID, 176, 1, @onexec sp_trace_setevent @TraceID, 176, 9, @onexec sp_trace_setevent @TraceID, 176, 41, @onexec sp_trace_setevent @TraceID, 176, 49, @onexec sp_trace_setevent @TraceID, 176, 10, @onexec sp_trace_setevent @TraceID, 176, 26, @onexec sp_trace_setevent @TraceID, 176, 34, @onexec sp_trace_setevent @TraceID, 176, 50, @onexec sp_trace_setevent @TraceID, 176, 3, @onexec sp_trace_setevent @TraceID, 176, 11, @onexec sp_trace_setevent @TraceID, 176, 35, @onexec sp_trace_setevent @TraceID, 176, 51, @onexec sp_trace_setevent @TraceID, 176, 4, @onexec sp_trace_setevent @TraceID, 176, 12, @onexec sp_trace_setevent @TraceID, 176, 28, @onexec sp_trace_setevent @TraceID, 176, 60, @onexec sp_trace_setevent @TraceID, 176, 21, @onexec sp_trace_setevent @TraceID, 176, 29, @onexec sp_trace_setevent @TraceID, 176, 37, @onexec sp_trace_setevent @TraceID, 176, 45, @onexec sp_trace_setevent @TraceID, 176, 6, @onexec sp_trace_setevent @TraceID, 176, 14, @onexec sp_trace_setevent @TraceID, 176, 46, @on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigint-- Set the trace status to startexec sp_trace_setstatus @TraceID, 1-- display trace id for future referencesselect TraceID=@TraceIDgoto finisherror: select ErrorCode=@rcfinish: go

MSSQL, Oracle, MySQL openquery...

Posted: 05 Apr 2013 03:44 AM PDT

Hello,My problem is that I do not know what to do from here. I am trying to find schedule conflicts. I used to have the data on a MySQL server and I was just using ASP.NET code to generate the conflicts list. Now one category of the data is coming from a different server, Oracle. I already own a MS SQL server that i am usually using for my apps.What I did, is I created a stored procedure that uses a union all from 2 openquery select statements. That works and I can get all the data I need:ALTER PROCEDURE [dbo].[getConflicts0] @repsd varchar(50), @reped varchar(50)ASBEGIN SET NOCOUNT ON; SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;declare @sqltext varchar(max)select @sqltext='SELECT * FROM OPENQUERY(MySQL)UNION ALLselect * from openquery(ORACLE) 'exec (@sqltext)ENDMy next step would be to get this data in a table that has 2 more columns, to identify the conflicts and pair them.I understand I cannot use this inside a function.Would you guys have any suggestion on how to proceed with this?Thank you
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog