Sinan Sert TSQL Blog @snnsrt - Tumblr Blog

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

--Run as login x EXECUTE AS login = 'userx' --Below fails, no execute permission on xp_cmdshell EXEC xp_cmdshell 'DIR C:\*.*' REVERT GO

use master --Note, we are in the master database!!! GRANT EXECUTE ON xp_cmdshell TO userx

--Try again EXECUTE AS login = 'userx' --Execution of xp_cmdshell is allowed. --But I haven't configured the proxy account... EXEC xp_cmdshell 'DIR C:\*.*' REVERT GO

--3, specify the proxy account for non-syadmins --Replace obvious parts! EXEC sp_xp_cmdshell_proxy_account 'COMPANYDC\administrator','**********' EXECUTE AS login = 'userx' --Execution of xp_cmdshell is allowed. --And executes successfully!!! EXEC xp_cmdshell 'DIR C:\*.*' REVERT

•18+ Adults Only

Watch Anya Live on Cam

Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.

✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality

Anya is LIVE right now

FREE

Free to watch • No registration required • HD streaming

SQL Server Sunucu Collation Değiştirme

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=administrator /SAPWD=123456/SQLCOLLATION=TURKISH_CI_AS

UPSERT

begin transaction merge into _cekim222 as Target using ( select BARKOD, ADET from _cekim ) Source on Target.BARKOD = Source.BARKOD when matched then update set Target.ADET = Source.ADET when not matched then insert (BARKOD, ADET ) values (Source.BARKOD,Source.ADET); rollback transaction

İki tarih arası filtre

( (cc.BeginDate <= '2013-01-01' AND cc.EndDate >= '2013-01-22')OR (cc.BeginDate >= '2013-01-01' AND cc.BeginDate <= '2013-01-22') OR (cc.EndDate >= '2013-01-01' AND cc.EndDate <= '2013-01-22') )

SQL SERVER – Get Query Plan Along with Query Text and Execution Count SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st --WHERE OBJECT_NAME(st.objectid,st.dbid) = ''

http://blog.sqlauthority.com/2009/08/21/sql-server-get-query-plan-along-with-query-text-and-execution-count/

•18+ Adults Only

Watch Anya Live on Cam

Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.

✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality

Anya is LIVE right now

FREE

Free to watch • No registration required • HD streaming

Oh my god!!!!

BEGIN TRANSACTION delete from Customers ROLLBACK TRANSACTION (300000 row(s) affected)

Find Array string in Table

DECLARE @p VARCHAR(50), @n Varchar(10)

SET @p = '149475,213,4589'

SELECT ID, FirstName, LastName

FROM Users

WHERE CHARINDEX( ',' + CONVERT(nvarchar,ID) + ',', ',' + @p + ',' ) > 0 ;

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally

ALTER DATABASE DBNAME SET RECOVERY FULL

bu işlemi yaptıktan sonra Log backup almak için önce bir kez full backup almak gerekiyor.

Delete datas in big tables without "truncate table"

DECLARE @maxID INT Select @maxID = ( MAX(ID) - 10000 ) from TableName with(nolock) WHILE (@maxID >= 0) BEGIN delete from TableName Where ID > @maxID Select @maxID = ( MAX(ID) - 10000 ) from TableName with(nolock) CONTINUE; END

Windows Server 2008: Allow multiple Remote Desktop sessions per user

But you can allow multiple Remote Desktop sessions per user by changing a registry key.

Start regedit

Check out the follwoing registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TerminalServer

If the fSingleSessionPerUser value doesn’t exist, create a new DWORD value named fSingleSessionPerUser

Open the fSingleSessionPerUser value. The possible values for this setting are as follows: 0×0 Allow multiple sessions per user 0×1 Force each user to a single session

save this

http://www.thomasmaurer.ch/2010/10/windows-server-2008-allow-multiple-remote-desktop-sessions-per-user/

•18+ Adults Only

Watch Anya Live on Cam

Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.

✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality

Anya is LIVE right now

FREE

Free to watch • No registration required • HD streaming

Windows 8 Kısayol Tuşları

Windows key: Switch between Modern Desktop Start screen and the last accessed application Windows key + C: Access the charms bar Windows key + Tab: Access the Modern Desktop Taskbar Windows key + I: Access the Settings charm Windows key + H: Access the Share charm Windows key + K: Access the Devices charm Windows key + Q: Access the Apps Search screen Windows key + F: Access the Files Search screen Windows key + W: Access the Settings Search screen Windows key + P: Access the Second Screen bar Windows key + Z: Brings up the App Bar when you have a Modern Desktop App running Windows key + X: Access the Windows Tools Menu Windows key + O: Lock screen orientation Windows key + . : Move the screen split to the right Windows key + Shift + . : Move the screen split to the left Windows key + V: View all active Toasts/Notifications Windows key + Shift + V: View all active Toasts/Notifications in reverse order Windows key + PrtScn: Takes a screenshot of the screen and automatically saves it in the Pictures folder as Screenshot Windows key + Enter: Launch Narrator Windows key + E: Open Computer Windows key + R: Open the Run dialog box Windows key + U: Open Ease of Access Center Windows key + Ctrl + F: Open Find Computers dialog box Windows key + Pause/Break: Open the System page Windows key + 1..10: Launch a program pinned on the Taskbar in the position indicated by the number Windows key + Shift + 1..10: Launch a new instance of a program pinned on the Taskbar in the position indicated by the number Windows key + Ctrl + 1..10: Access the last active instance of a program pinned on the Taskbar in the position indicated by the number Windows key + Alt + 1..10: Access the Jump List of a program pinned on the Taskbar in the position indicated by the number Windows key + B: Select the first item in the Notification Area and then use the arrow keys to cycle through the items Press Enter to open the selected item Windows key + Ctrl + B: Access the program that is displaying a message in the Notification Area Windows key + T: Cycle through the items on the Taskbar Windows key + M: Minimize all windows Windows key + Shift + M: Restore all minimized windows Windows key + D: Show/Hide Desktop (minimize/restore all windows) Windows key + L: Lock computer Windows key + Up Arrow: Maximize current window Windows key + Down Arrow: Minimize/restore current window Windows key + Home: Minimize all but the current window Windows key + Left Arrow: Tile window on the left side of the screen Windows key + Right Arrow: Tile window on the right side of the screen Windows key + Shift + Up Arrow: Extend current window from the top to the bottom of the screen Windows key + Shift + Left/Right Arrow: Move the current window from one monitor to the next Windows key + F1: Launch Windows Help and Support

PageUp: Scroll forward on the Modern Desktop Start screen PageDown: Scroll backward on the Modern Desktop Start screen Esc: Close a charm Ctrl + Esc: Switch between Modern Desktop Start screen and the last accessed application Ctrl + Mouse scroll wheel: Activate the Semantic Zoom on the Modern Desktop screen

Alt: Display a hidden Menu Bar Alt + D: Select the Address Bar Alt + P: Display the Preview Pane in Windows Explorer Alt + Tab: Cycle forward through open windows Alt + Shift + Tab: Cycle backward through open windows Alt + F: Close the current window Open the Shut Down Windows dialog box from the Desktop Alt + Spacebar: Access the Shortcut menu for current window Alt + Esc: Cycle between open programs in the order that they were opened Alt + Enter: Open the Properties dialog box of the selected item Alt + PrtScn: Take a screen shot of the active Window and place it in the clipboard Alt + Up Arrow: Move up one folder level in Windows Explorer (Like the Up Arrow in XP) Alt + Left Arrow: Display the previous folder Alt + Right Arrow: Display the next folder Shift + Insert: CD/DVD Load CD/DVD without triggering Autoplay or Autorun Shift + Delete: Permanently delete the item (rather than sending it to the Recycle Bin) Shift + F6: Cycle backward through elements in a window or dialog box Shift + F10: Access the context menu for the selected item Shift + Tab: Cycle backward through elements in a window or dialog box Shift + Click: Select a consecutive group of items Shift + Click on a Taskbar button: Launch a new instance of a program Shift + Right-click on a Taskbar button: Access the context menu for the selected item Ctrl + A: Select all items Ctrl + C: Copy the selected item Ctrl + X: Cut the selected item Ctrl + V: Paste the selected item Ctrl + D: Delete selected item Ctrl + Z: Undo an action Ctrl + Y: Redo an action Ctrl + N: Open a new window in Windows Explorer Ctrl + W: Close current window in Windows Explorer Ctrl + E: Select the Search box in the upper right corner of a window Ctrl + Shift + N: Create new folder Ctrl + Shift + Esc: Open the Windows Task Manager Ctrl + Alt + Tab: Use arrow keys to cycle through open windows Ctrl + Alt + Delete: Access the Windows Security screen Ctrl + Click: Select multiple individual items Ctrl + Click and drag an item: Copies that item in the same folder Ctrl + Shift + Click and drag an item: Creates a shortcut for that item in the same folder Ctrl + Tab: Move forward through tabs Ctrl + Shift + Tab: Move backward through tabs Ctrl + Shift + Click on a Taskbar button: Launch a new instance of a program as an Administrator Ctrl + Click on a grouped Taskbar button: Cycle through the instances of a program in the group F1: Display Help F2: Rename a file F3: Open Search F4: Display the Address Bar list F5: Refresh display F6: Cycle forward through elements in a window or dialog box F7: Display command history in a Command Prompt F10: Display hidden Menu Bar F11: Toggle full screen display Tab: Cycle forward through elements in a window or dialog box PrtScn: Take a screen shot of the entire screen and place it in the clipboard Home: Move to the top of the active window End: Move to the bottom of the active window Delete: Delete the selected item Backspace: Display the previous folder in Windows Explorer Move up one folder level in Open or Save dialog box Esc: Close a dialog box Num Lock Enabled + Plus (+): Display the contents of the selected folder Num Lock Enabled + Minus (-): Collapse the selected folder Num Lock Enabled + Asterisk (*): Expand all subfolders under the selected folder

Press Shift 5 times Turn StickyKeys on or off Hold down right Shift for 8 seconds Turn FilterKeys on or off Hold down Num Lock for 5 seconds Turn ToggleKeys on or off

Changing the name of your SQL Server

http://www.techrepublic.com/blog/datacenter/changing-the-name-of-your-sql-server/192

SQL Giriş

http://www.bilgisayarmuhendisleri.com/sayfa.aspx?s=84

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

http://dba.stackexchange.com/questions/2687/sql-server-maintenance-plan-best-practices-on-tasks-and-scheduling

Search word in All Tables

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END EXEC SearchAllTables 'kedi'

•18+ Adults Only

Watch Anya Live on Cam

Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.

✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality

Anya is LIVE right now

FREE

Free to watch • No registration required • HD streaming

Locked Object List

SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id

Tablonun Identity Alanını sıfırlama.

DBCC CHECKIDENT ("Picture", RESEED, 1)

Trending Blogs

Last Seen Blogs

Sinan Sert TSQL Blog