Thursday, July 25, 2013

[T-SQL] audit my code for current request_id?

[T-SQL] audit my code for current request_id?

audit my code for current request_id?

Posted: 24 Jul 2013 10:54 PM PDT

Hai friends, I made on web appplication dependscreate table onward_joureny(joureny_id int identity,departuredate datetime,from varchar(100),to char(100),is_draft varchar(100))create table users(user_id varchar(100) PK,username varchar(50),password varchar(50))create table travel_request(request_id int identity,user_id varchar(100) fk references users(user_id),total_amount)if new requestId means only my data ll shown......old once not shown be here is my code correct ah?--select * from onward_journey where request_id = 58 order by departuredate--select * from travel_requestcreate procedure bindata( @username varchar(20))as begindeclare @user varchar(20), @request varchar(10)select @user=a.user_id ,@request=max(b.request_id) from travel_request b inner join users a on a.user_id = b.user_id where a.username=@username group by a.user_idif exists (select * from travel_request where request_id = @request)select onward_journey_id,convert(varchar, departuredate, 106)as DepartureDate,from_location,to_location, case metro when 'Yes' then 'Yes' else 'No' end as Metro,case Trans_All when 'Yes' then 'Yes' else 'No' END as transit,case when mode_of_travel like 'Ai%' then mode_of_travel when mode_of_travel like 'Tr%' then mode_of_travel when mode_of_travel like 'Bu%' then mode_of_travel when mode_of_travel like 'Ca%' then mode_of_travel when mode_of_travel like 'Au%' then mode_of_travel when mode_of_travel like 'lo%' then mode_of_travel end as Mode,case when mode_of_travel like ('ai%') then seat_type when mode_of_travel like ('tra%') then seat_type when mode_of_travel like ('Bu%') then seat_type when mode_of_travel like ('ca%') then seat_type when mode_of_travel like ('au%') then seat_type when mode_of_travel like ('loc%') then seat_type end as SEAT,no_of_days as noofdays,other_details from onward_journey inner join(select a.user_id, a.userName, b.request_idfrom users ainner join travel_request bon a.request_id = b.request_idwhere a.username = @username)xxonxx.request_id= onward_journey.request_idwhere xx.username=@usernameand xx.request_id = @requestend

Audit log for INSTEAD OF DELETE trigger

Posted: 24 Jul 2013 03:23 AM PDT

Hi,I'm trying to write an INSTEAD OF DELETE trigger that also logs some data about the attempted delete. It doesn't have to be super-restrictive across the board such as trying to prevent deletes/truncate by sa or anything like that. We just want to have any routine delete operations stopped. Additionally, I would like to log as much as possible about when the delete attempt occurred and who attempted it.My code is below. The trigger successfully stops the delete attempt, but when I check the log table, no records have been inserted. Could someone help me find out what is stopping the log record insert, and how I can fix it?Thanks for any help,webrunner[code]/****** Object: Trigger [dbo].[trNumber_D] Script Date: 07/24/2013 12:16:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trNumber_D] ON [dbo].[Numbers] INSTEAD OF DELETEAS-- Log attempted delete info. IF EXISTS ( SELECT 1 FROM deleted ) BEGIN SET NOCOUNT ON; INSERT INTO Number_Log ( ID, Number, Date_Inserted, Date_Delete_Attempted, UserName ) SELECT d.ID, d.Number, d.Date_Inserted, GETDATE(), SYSTEM_USER FROM deleted d -- Deletion from this table is not allowed. RAISERROR('Deletion of records from Numbers is not allowed.', 16, 1) RETURN; END[/code]

tsql function with dynamic number of parameters?

Posted: 24 Jul 2013 08:57 PM PDT

Hi,in a SELECT, I would like to have a function which returns the minimum of dynamic list of values, like this:select GetMin(4,3) => returns 3select GetMin(4,3,1) => returns 1select GetMin(2,5,0,null,7) => returns 2Do you have an idea, how to code the dynamic parameter list of GetMin?It's like COALSECE, is there a source code available of this build-in function?thank you for your help!Ralf

How to code for already exist date message in my porcedure?

Posted: 24 Jul 2013 06:27 PM PDT

Hai friends , i m creating web application behind using sql server 2000these is my table structure:create table journey( joureny_id int identity, user_id varchar(100) fk references users(user_id), departuredate datetime, from varchar(100), to char(100),is_draft varchar(100))create table users(user_id varchar(100) PK,username varchar(50),password varchar(50))in users table:===========insert into users values('002258','ram','rama@123')in travel_request============insert into travel_request(user_id , departuredate,from,to,is_draft)('002258','01-jul-2013','A','B',' ')insert into travel_request(user_id , departuredate,from,to,is_draft)('002258','02-jul-2013','c','D',' ')now My requirement is:================1)when the same user_id is trying to insert date for already exist date means want to show error mesaage whe n i press the button of draft?2)if is it new date means update colmun depends on request_id " is_draft='Y' " (i.e allow to draft)how to do that?

Is it possible to return a value from a SP like this?

Posted: 24 Jul 2013 06:37 AM PDT

Or should I really be using a function? That's what I was attempting at first, but got pretty stumped on making it dynamic. I'm trying to take the code to return a median value form here ( http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx ) and turn it into a callable SP/FN. Or if anyone has another suggestion, I'm open to whatever. Thanks[code="sql"]USE [Utilities]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET NOCOUNT ONGOalter PROC [dbo].[ReturnMedian] @median sysname,@tablenm sysnameASdeclare @sql nvarchar (max)set @sql = 'WITH MEDCTE (ID, MED) AS (SELECT Id, AVG( ' + QUOTENAME(@median) + ')FROM( SELECT Id, ' + QUOTENAME(@median) + ', ROW_NUMBER() OVER ( PARTITION BY Id ORDER BY ' + QUOTENAME(@median) + ') AS RowNum, COUNT(*) OVER ( PARTITION BY Id) AS RowCnt FROM ' + QUOTENAME(@tablenm) + ') xWHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)GROUP BY IdSELECT MED FROM MEDCTE'--EXEC (@SQL)--PRINT (@SQL)--SELECT (@SQL)--declare @tempmed table (--TEMPMEDFLOAT float--)--insert into @tempmed exec sp_executesql @sql, N'@sql float', @sql--RETURN SELECT TEMPMEDFLOAT FROM @tempmed[/code]

Using DelimitedSplit8k against a Space Delimited Field - Problems

Posted: 24 Jul 2013 07:14 AM PDT

Hi All - I came across Jeff Moden's Splitter Code earlier today and have been trying to run the function against a space delimited field in my database. When running the function against the function does not seem to recognize the space as a delimiting character.[code]Sample DataCoreID ComputerID ResultsText1 1456 192.168.1.1 192.168.1.2 192.168.1.32 1458 192.168.1.8 192.168.1.6 192.168.1.5[/code]Here is what I am running against this table:[code="sql"]Select sd.CoreID, sd.ComputerID, sd.ResultsTextFrom SampleData sdcross apply dbo.DelimitedSplit8k(sd.ResultsText, ' ')[/code]Additionally I have tried the following and it did not work either:[code="sql"]Select sd.CoreID, sd.ComputerID, sd.ResultsTextFrom SampleData sdcross apply dbo.DelimitedSplit8k(sd.ResultsText, SPACE(1))[/code]The output that results from both of these is below (as you can see it is not splitting out the field at all):[code]CoreID ComputerID ResultsText ItemNumber Item1 1456 192.168.1.1 192.168.1.2 192.168.1.3 1 192.168.1.1 192.168.1.2 192.168.1.32 1458 192.168.1.8 192.168.1.6 192.168.1.5 1 192.168.1.8 192.168.1.6 192.168.1.5[/code]Any help would be greatly appreciated. Not sure what is not working here....

handle data which contains space

Posted: 24 Jul 2013 09:10 PM PDT

Hi all,I am having table JobDetail whcih contains jiobname as a one of column.here the data entered from front end is not correct because of space issue. for example if job name is Software engineer then it might be entered single space or sometimes multiple spaces between "Software" & "engineer". but while selecting i want only once.I am using distinct but giving wrong output. Please suggest any idea.thanksAbhas

Temporary Functions?

Posted: 31 Aug 2010 09:06 PM PDT

I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again. In the same way that temporary tables can be created can temporary functions be created? - i.e. you specify the code to create it, query using it then when you close the window it's gone. It doesn't clutter up the DB this way... If so, how would one alter the existing syntax:CREATE FUNCTION [dbo].[f_functionname](paramaters)Cheers!

Case statement

Posted: 24 Jul 2013 08:35 AM PDT

I would like use case statement in select .for example, if rate =100, then 1, if it is null, then 1, and if it is <>100, it is 0,how to write the select case statement in best way?Thanks

Help with query

Posted: 24 Jul 2013 07:13 AM PDT

CREATE TABLE #NewTempLogins ( CompanyCode varchar(12),Login VARCHAR(MAX))CREATE TABLE #TempLogins ( ID INT IDENTITY(1,1), CompanyCode varchar(12),CompanyName VARCHAR(1000),Login VARCHAR(MAX))DECLARE @listStr VARCHAR(MAX)INSERT INTO #TempLogins (CompanyCode,CompanyName,Login)SELECT 'company1','MyCompany','psb'UNION ALL SELECT 'company1','MyCompany','lll'UNION ALL SELECT 'company2','MyCompany2','bbb'UNION ALL SELECT 'company2','MyCompany2','rrr'UNION ALL SELECT 'company2','MyCompany2','test1'DECLARE @StratTrackCount INT = 1DECLARE @TrackCount INT = (SELECT MAX(ID) FROM #TEmpLogins)SELECT @TrackCount select distinct * from #TEmpLogins order By CompanyCodeDECLARE @CompanyCode VARCHAR(12)DECLARE @Login VARCHAR(MAX)WHILE (@StratTrackCount <= @TrackCount)BEGINDECLARE @MyCursor CURSORSET @MyCursor = CURSOR FORSELECT CompanyCode FROM #TEmpLoginsOPEN @MyCursorFETCH NEXTFROM @MyCursor INTO @CompanyCodeWHILE @@FETCH_STATUS = 0BEGINSELECT @listStr = COALESCE(@listStr+',' ,'') + LoginFROM #TEmpLogins where CompanyCode = @CompanyCodeINSERT INTO #NewTempLogins (CompanyCode,Login)SELECT DISTINCT @CompanyCode,@listStr from #TEmpLogins where CompanyCode = @CompanyCode FETCH NEXT FROM @MyCursor INTO @CompanyCode SET @StratTrackCount = @TrackCount + 1 END CLOSE @MyCursor DEALLOCATE @MyCursor END SELECT distinct * FROM #NewTempLogins order By CompanyCode drop table #TEmpLogins drop table #NewTempLogins I want to display the results like below CompanyCode Login company1 psb,lll company2 bbb,rrr,test1

Partitioned table JOIN query performance

Posted: 24 Jul 2013 02:54 AM PDT

I have five large (500m+ records) tables with differing informaton and column structures. However, each of these tables have five fields in common. Coincidentally, the combination of those five fields makes up the logical key for each table. As such, the tables all have a primary key built on those five fields. I have to run a daily report that draws some data from each of those tables, and when I run the SQL for this report against query analyzer, it's clear that it is using these clustered indexes appropriately and doing merge joins and/or nested loops to get it done. The query takes about 90 minutes to complete.Then I set about trying to leverage partitioning to improve performance. I dropped the primary key on each of the tables and recreated all of the primary keys with the same five fields, but all on the same partition scheme - effectively aligning all of the tables and their clustered indexes on the same partition scheme. The scheme uses the same one of those five fields as it's partition key (a date field, if that matters) for each table.The query now runs in about 45 minutes - not bad, right? However, in the original execution plan, all of the time was spent doing the clustered index scans - about 90% of it. In the new execution plan, only 10% of the time is spend scanning indexes, and 80% is spent sorting the data between the index scan and the join (a step which previously didn't exist). Is there a way I can eliminate the sort by structuring my query differently, or is this intermediate sort a natural product of pulling data from partitioned tables?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog