Procedure Parameter Retrieval

SELECT name ,
TYPE_NAME(user_type_id )TYPE ,
max_length ,
parameter_id param_order
FROM sys.all_parameters
WHERE object_id = OBJECT_ID('[dbo].[sp_name_p]' )
AND is_output<>1 -- Excluding the OUTPUT parameter

PowerShell Script to find File Size

cd/
powershell
cd\

clear-Host
$FolderPathToVerify="c:\backup\TestTools"
$FilePathToWriteReport="C:\backup\TestTools\Tested.csv"
Get-ChildItem $FolderPathToVerify -force -Recurse -ErrorAction "SilentlyContinue" |Select-Object Name,Length,Directory,CreationTime,LastAccessTime,LastWriteTime |Where-Object {$_.Length -ge 1} |Export-CSV -NoTypeInformation $FilePathToWriteReport

clear-Host
$FolderPathToVerify="c:\backup\TestTools"
$FilePathToWriteReport="C:\backup\TestTools\Tested.csv"
Get-ChildItem $FolderPathToVerify -force -Recurse -ErrorAction "SilentlyContinue" |Select-Object Name,Length,Directory,CreationTime,LastAccessTime,LastWriteTime |Export-CSV -NoTypeInformation $FilePathToWriteReport

cd/
powershell
cd\
Get-ChildItem c:\backup\TestTools -force -Recurse -ErrorAction "SilentlyContinue" |Where-Object {$_.Length -ge 0} |Export-Csv -Delimiter ',' -NoTypeInformation -Path C:\backup\DriveVersion.csv
pause


Excluding Certain Folders like Recycle Bin and Filenames:




clear-Host
$excludefiles = @('desktop.ini', '*AnyFilename*','*.docx')
$FolderPathToVerify="T:\Backup"
$FilePathToWriteReport="C:\temp\Backup.csv"
Get-ChildItem $FolderPathToVerify -force -Recurse -ErrorAction "SilentlyContinue" -Exclude $excludefiles |Select-Object Name,Length,Directory,CreationTime,LastAccessTime,LastWriteTime |Where-Object {($_.Length -ge 1 -and $_.Directory -NotLike "*$RECYCLE.BIN*")} |Export-CSV -NoTypeInformation $FilePathToWriteReport

IIS 6 - Backup and Restore

All below steps involved in the command line:

/sp = source path

/lm = local machine

/dp = destination path

/d = password == /d passwordneedstowrite

/f c:\path = Pathname



Go to command prompt and type:

iiscnfg

Enter

The script does not work with WScript

Would you like to register CScript as your default host for VBScript

Click Yes to register CScript iiscnfg /export /sp /lm /children /inherited /d password /f c:\IISConfig.bak

Go to the directory where you backup the configuration: cd\ dir *.bak

To see the backup file use the below command: more IISConfig.bak iiscnfg /import /dp /lm /sp /lm /children /inherited /d password /f c:\IISConfig.bak

Rename the SQL Server Instance

Get the current name of the SQL Server instance for later comparison.
SELECT @@servername
Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver 'OLDSERVRNAME'
Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver NEWSERVERNAME, 'local'
Get the new name of the SQL Server instance for comparison.
SELECT @@servername

SQL Management Studio

Type SqlWb command in run window to open sql server (for sql server 2005).
Type ssms command in run window to open sql server (for sql server 2008).

MULTIPLE ROWS IN ONE COLUMN TO SINGLE ROWS WITH COMMA SEPARATION

CREATE TABLE TESTCOLROWS (ID INT IDENTITY(1,1), NAME VARCHAR(10))
GO
GO
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST1')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST2')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST3')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST4')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST5')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST6')
INSERT INTO TESTCOLROWS (NAME) VALUES ('')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST7')
INSERT INTO TESTCOLROWS (NAME) VALUES (NULL)
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + ISNULL(NAME,'')
FROM TESTCOLROWS
SELECT @listStr
GO
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + NAME + ','
FROM TESTCOLROWS
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)
GO
CREATE FUNCTION [dbo].[UDF_DOCUMENTIDCHILD] ( @ID INT )
RETURNS VARCHAR(100)
AS BEGIN
--DECLARE @ID INT
DECLARE @listStr VARCHAR(100)
SELECT @listStr = COALESCE(@listStr+',' , '') + ISNULL(NAME,'')
FROM TESTCOLROWS WHERE ID=@ID;
RETURN @listStr
END
GO
SELECT DBO.[UDF_DOCUMENTIDCHILD] (2) AS NAME

Generate Sequential Numbers

CREATE PROCEDURE GENERATEROWNUMBERS(@NUMBEROFROWS INT, @STARTNUMBER INT)AS BEGIN
DECLARE @NUMGEN TABLE (NUM INT)
DECLARE @CNT INT SET @CNT = 1
WHILE @CNT <= 100 BEGIN
INSERT INTO @NUMGEN
SELECT @CNT SET @CNT = @CNT + 1
END
SELECT @STARTNUMBER + ROWNUM FROM ( SELECT ROW_NUMBER() OVER (ORDER BY N1.NUM) AS ROWNUM FROM @NUMGEN N1, @NUMGEN N2, @NUMGEN N3, @NUMGEN N4 ) ROWNUMS WHERE ROWNUM <= @NUMBEROFROWS
END

Hour Based - Group by SQL Query

The below query can be useful if you want to see the result with hour based information:

select convert(varchar(13), datetime, 120), count(datetime), sum(connectionduration/60.0) from T_tablename(nolock) where datetime between '2011-06-07' and '2011-06-08'
group by convert(varchar(13), datetime, 120) order by 1

SQL Server 2008 Expiry Date Find

There are three ways to get this information,

Solution 1

Open the Management Studio, click on the "Help" of Menu Bar and then on "About".

Solution 2

You can check the windows installer for the SQL Product code to pull out the install date.

In registry look under,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\\InstallProperties

Notes :

•The "InstallDate" key will have the date stored in YYYYMMDD
•For SQL 2008 you can search for "Microsoft SQL Server 2008 Database Engine Services" to get the correct GUID.
•For SQL 2005 you can search for "Microsoft SQL Server 2005" only to get the correct GUID

Solution 3

Get this via following TSQL Script

SELECT create_date as 'SQL Server Install Date',
DATEADD(dd,180,create_date) as 'Will Stop Working ON'
FROM sys.server_principals WHERE name='NT AUTHORITY

Top and Set Row Count - SQL Server

http://www.databasejournal.com/features/mssql/article.php/3532226/TOP-Clause-in-SQL-Server-2005.htm

How do I Transfer logins from one SQL Server 2005 instance to another?

http://support.microsoft.com/kb/918992/ - Folllow the procedure from microsoft.

USE master
GO
CREATE PROCEDURE sp_hexadecimal, sp_help_revlogin and get the output script from EXEC sp_help_revlogin in the primary server or old server or source server.

Once done, you have to synchronise the database users

Run the below script under the database where you want the users to synchronise:

DECLARE @UserName nvarchar(255)
DECLARE Cursor_OrphanedUser cursor for
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' Synchronization of Logins in Progress'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser

Refreshing Website using Meta Tag

Objective: Refreshing Website using Meta Tag:

Please keep the meta refresh tag in the sitemanager.master.vb

TEMPDB Usage

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx

select top 10
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
(SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan


from (Select session_id, request_id,
sum(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1,
sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and
(t1.request_id = t2.request_id) and
t1.session_id > 50
order by t1.task_alloc DESC

Finding Filegroup - Objects

select distinct(object_name(id)) from sysindexes
where groupid=filegroup_id('PRIMARY')

Adding Pipe Delimited Option for Report Rendering

One of my customers created a report used to send data to another system via a file transfer. The problem is the transfer is not direct and requires a process to convert the CSV to pipe delimited. The system receiving the data has a format it expects that isn’t configurable and must be pipe delimited. Reporting Services comes with the ability to export a comma delimited out of the box, but that’s the only text file option by default.

After a little chat with Mr. Google I found that adding a new data rendering extension in SSRS is quite simple and any text file output you may want is just a change to the reportserver.config file away.

• 
  
  
  
  
  
  
  

It can be used in file share delivery for your subscriptionsThe Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport has numerous configuration options, pipe delimited is just one

Job Owner, Enabled Status, Job Name - Findings

USE MSDB
GO
SELECT GETDATE() AS 'ExecutionTime'
GO
SELECT @@SERVERNAME AS 'SQLServerInstance'
GO
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY l.[name]