How to find your IP is static or Dynamic

Open command prompt..
type  IPconfig /all

If you find DHCP Enabled ....:  Yes,  -- it says your IP is Dynamic
                                                :   No,  -- It says your IP is Static

Simple Dynamic SQL to get system tables information

Simple Dynamic SQL to get all tables row count from the database.
You have many number of ways to get this information.

SELECT 'SELECT COUNT(*) AS '+ name +' FROM ' + name AS TableName
FROM sys.tables


You can also apply the condition to the sort the data...
WHERE name like 's%'

Restore/Attach the database with out Log(ldf) file.

-- You can attach a database to the server just with mdf even though you dont have ldf.

USE [master] GO
EXEC sp_attach_single_file_db @dbname='AdventureWorksDW2012',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_Data.mdf'
GO

-- You can also use the following method..
USE [master] GO
CREATE DATABASE AdventureWorksDW2012_New ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_New_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO

-- You can also use the following method.
USE [master] GO
CREATE DATABASE AdventureWorksDW2012_New1 ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_New1_Data.mdf')
FOR ATTACH
GO

In all the above cases the ldf file created by itself in default log files folder.

SQL server error during the installation: Unhandled Exception.. System ConfigurationException

Its frustrating sometimes when more issues coming during your SQL server installation.

I have been told to installation SQL server on a machine. As I don't have proper permissions I had to try couple of times install/ uninstall the SQL server on the same machine...
Finally, I got the permissions and thought it will go smooth from now. But suddenly I got the following error during my next try...



Solution:
If your operating system is Windows 8, 7, Vista:
C:\Users\[USERNAME]\AppData\Local\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0

If Windows XP:
C:\Documents and Settings\[USERNAME]\local settings\application data\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0
drive to that folder and Rename the file "user.config" to "user.config.bak" (no quotes).

Reason: Every time you try the install SQL,  it try to create 'user.config' file in the above folder(vary from OS to OS). During your last uninstall SQL it didn't clean up properly from the folder...
So as it tries to create every time during the installation, it tried creating the file and rejected as the file already exists in the folder.. So please rename it.

Delete Selected list of tables from the SQL database

I have selected tables list which I need to delete from my database. If 10 or less I can do it manually in different ways.
But it has more than 1000 tables which required to delete from the database.

Of course people has their own choice to do this. I have implemented the following script.

First you have to send that list of table names into a temp table.
You may give the Serial number for each row..You can done this by adding extra column with identity property (OR) use
Row_Number() function on the existed column.

Once you are ready with a table with the list of table names(I named it AA_DeletedTables) You can follow the script below.

   
DECLARE @TableName VARCHAR(50)
DECLARE @SQL VARCHAR(100)
DECLARE @i INT =1
DECLARE @Count INT
SELECT @Count = COUNT(*) FROM AA_DeleatedTables

WHILE (@i < =  @Count)
    BEGIN
        SELECT @TableName = name from AA_DeleatedTables WHERE Sno = @i
        SET @SQL = 'Drop table '+@TableName
        --PRINT (@SQL)  You can test by enabling this by just printing the test
        EXEC @SQL
       
        SET @i = @i +1
    END

--SELECT * FROM AA_DeleatedTables

Drop table AA_DeleatedTables
NOTE: It will work for when the tables doesn't have any constraints referencing..
If you want you can use this table as audit table by adding time stamp columns....

Deleting Old backup files from the file system by using SQL script

Some times you may frustrate to use maintenance plans. Some times complete work by using T-SQL scripts is better(I usually go for the scripts) than maintenance plans.
If you want to delete the backup files from the filesystem, You can do by using T-SQL script.
Please follow the below script and pass the parameters accordingly your server/system.

DECLARE @DeletedDate DATETIME
            SET @DeletedDate = DateAdd(day, -15, GetDate())  -- Its your setting the number of days
            EXECUTE master.sys.xp_delete_file
            0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
            N'E:\FullBackup\', -- folder path (trailing slash)
            N'bak', -- file extension which needs to be deleted (no dot)
            @DeletedDate, -- date prior which to delete
            1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

Jobs running on the SQL server and get the next run

List of the jobs and when and what time they execute for the next time...
You can use the following script to find that information....


SELECT sj.name AS Name,sjs.next_run_date AS NextRunDate,LEFT(RIGHT('000000' + CAST(sjs.next_run_time AS VARCHAR(6)), 6),2)
                                    + ':' + SUBSTRING(RIGHT('000000'+ CAST(sjs.next_run_time AS VARCHAR(6)),6), 3, 2)
                                    + ':' + RIGHT(RIGHT('000000' + CAST(sjs.next_run_time AS VARCHAR(6)),6), 2) AS NextRunTime,
        sj.date_created AS DateCreated, sj.date_modified AS DateModified,sj.description AS Description
FROM msdb..sysjobschedules sjs JOIN
      msdb..sysjobs sj on sjs.job_id = sj.job_id
  --WHERE description not like '%This job is owned by a report%'
ORDER BY 3 --Name