Convert Hex String to IP Address SQL

Hi,

I came across this code theu MSDN where someone asked how to convert Hex String to IP Address. Its work sharing so here it is.

DECLARE @v1 AS char(8);
SET @v1 = '0A370D16' ;
DECLARE @b1 AS binary;
DECLARE @b2 AS binary;
DECLARE @b3 AS binary;
DECLARE @b4 AS binary;

SELECT @b1 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,1,2));
SELECT @b2 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,3,2));
SELECT @b3 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,5,2));
SELECT @b4 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,7,2));
SELECT @b1, @b2, @b3, @b4;

DECLARE @d1 AS int;
DECLARE @d2 AS int;
DECLARE @d3 AS int;
DECLARE @d4 AS int;

SELECT @d1 = CAST (@b1 AS int);
SELECT @d2 = CAST (@b2 AS int);
SELECT @d3 = CAST (@b3 AS int);
SELECT @d4 = CAST (@b4 AS int);
SELECT @d1, @d2, @d3, @d4;
SELECT CAST (@d1 AS char(2)) + '.' + CAST (@d2 AS char(2)) +'.'+ CAST (@d3 AS char(2)) + '.'+ CAST (@d4 AS char(2)) AS IPAddress

Output Will Be
******************


---- ---- ---- ----
0x0A 0x37 0x0D 0x16

(1 row(s) affected)


----------- ----------- ----------- -----------
10 55 13 22

(1 row(s) affected)

IPAddress
-----------
10.55.13.22

(1 row(s) affected)

Agent Job To Script All Agent Job SQL 2008

Hi,

I came across a request on MSDN where one of our colleague was running SQL 2008. He want to create a SQL Agent Job which when executed Script Out All existing SQL Agent Jobs. As per him he don't want to use SMO or VB.

I provided POWERSHELL script to do so.

1) Copy below code in a Text Pad and save it as JOBS.PS1

param($sqlserver)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer.Jobs foreach {$_.Script()}
2) Create a New Job in SSMS.
3) In Steps add a step of Type as "Powershell".
4) In Command section provide location of file JOBS.PS1 and Server Name from where we want Jobs
Information.

C:\./JOBS.PS1 GURPSETH\SQL2K*
5) Go to Advances section for this Job Step and provide file in OUTPUT FILE section. idea is once this
command will run it should log the output in it.
6) Once done save this job.
7) Starts this job.

Multiline On A Single Record in SQL

Hi,

I came across a SQL post on MSDN where one of our fellow SQL Colleague asked how to Display Single Record in Multiline. Rest of the story is below:

Problem Description
*******************
is it possible to have multi-line in a single record in sql table?? for example

rowid employee address

1 addin adhika jakarta 123456

indonesia

the address column is multiline, is it possible to achieve that?

Resolution Code
*****************

use tempdb
go


Create table #EMP(id int, name varchar(50), address varchar(100))
go

insert into #EMP values(1,'addin adhika','jakarta 123456 indonesia')
go

DECLARE @CrLf CHAR(2);
SET @CrLf = CHAR(13) ;
select id,name,substring(address,1,15)+char(13)+substring(address,15,datalength(address)) from #EMP
drop table #EMP
go

Note: Make Sure We Run This Code In QA And Output Should Be Set To TEXT.

SUM of Hours and Minutes SQL 2005

Hi,

I came across a post where one of our fellow SQL coleague asked a question i.e. In SQL Server 2005 how to do SUM of Hours and Minutes. So here comes rest of story:

Problem Description
***********************
I want to get SUM of Hours and Minutes in SQL Server 2005 in To SELECT Query Like : SELECT SUM(OTHours) FROM EmpInOutRecords.

Example: I Have OverTime Hours Like: 01:25, 02:30, 05:56, 00:50

Now I Want To SUM of This Total Hours and Minutes Like Answer is : 10:41

Resolution Code
****************

DECLARE @Sample TABLE
( data CHAR(5)
)


INSERT @Sample SELECT '01:25' UNION ALL
SELECT '02:30' UNION ALL
SELECT '05:56' UNION ALL
SELECT '00:50'
SELECT
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM (
SELECT ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 * LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours,
ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 * SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes
FROM @Sample
) AS d

SQL 2005 Licencing

SQL Server is licensed in 2 modes - Server and CAL which is Per Seat or Per Proc, it is an anomaly in the world of Licensing, but, that said, gives greater flexibility, see more info on SQL licensing HERE

For SQL 2000 we had an applet in Control Panel which helped to identify which mode you had deployed in and the user was prompted during setup to enter the license type. The resultant data was stored in the registry and could be viewed using the Control Panel applet. We found that this data was not consistently used nor could it be relied upon to be accurate. As a result it was removed in SQL Server 2005. Now this can prove to be a bit of a problem for larger companies who need to keep a track of this for SAM and compliancy, so, as a work around we have the following solution

Tracking License Information in SQL 2005

SQL 2005 no longer tracks licensing (per seat or per processor) via registry entries. SQL 2005 still reads the registry for this information, but the SQL 2005 setup doesn’t put licensing information in the registry during setup as in SQL 2000.

This is by-design. Hence, when ServerProperty(‘LicenseType’) is run on a SQL 2005 installation, ‘DISABLED’ is always returned.


Supported Resolution

Since SQL 2005 still queries the registry for licensing information, add the following key and values and ServerProperty(‘LicenseType’) will return license information.

Note: Licensing has always been server wide and not SQL instance specific. This setting would apply to all instances of SQL Server on the server.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\MSSQLLicenseInfo\MSSQL9.00

If you want to configure SQL Server for Per Processor then add these Registry Values under that Key adjusting for the number of processors you have a license for:

Name                      Type                            Value
Mode                      REG_DWORD           LICENSE_MODE_PERPROC
ConcurrentLimit    REG_DWORD           Number of Processors

If you want to configure SQL Server for Per Seat licensing then add these Registry values under the Key adjusting for the number of seat license you have purchased.

Name                       Type                           Value
Mode                        REG_DWORD           LICENSE_MODE_PERSEAT
ConcurrentLimit       REG_DWORD           No. of client licenses
registered for SQL Server in
Per Seat mode.

Test in SQL Management Studio

You need to stop and restart SQL Server 2005 before the information will be available to ServerProperty() as the registry is read on start-up of SQL Server.

With the above settings you would see the following when you restart SQL Server 2005.


SELECT ServerProperty('LicenseType') as LicenseType, ServerProperty('NumLicenses') as ProcessorCount

Output:
LicenseType ProcessorCount

PER_PROCESSOR 4



We are aware and committed to the need to provide a much more efficient way for centrally tracking SQL Server licenses and will be looking at different options.

The new control features added to SQL Server 2008 R2 are one possible place where we could centralize license metadata management in a future release and this is being assessed.

In terms of understanding what model/edition of SQL Server you have installed the Microsoft Planning and Assessment Toolkit, which is a free download, does an excellent job of creating an inventory of the SQL Server instances on your network.


Hope this helps

Trigger to Get Information Who Updated a Table

Hi,

I came thru a post in MSDN where some one as how he can save details (like SPID, Name) for user who update a specific table.

Below is the code for that

--Create table for storing values

create table idtrack (id int,uname varchar(100),date datetime)

--Create Trigger on table (table1 in this example)

--Trigger will copy SPID, USER NAME & DATE when a update was fired on table1.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create TRIGGER dbo.testtrigger ON dbo.table1
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into idtrack (id,uname,date)select @@SPID,user_name(),getdate()
END
GO


http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/50bc5c3c-797c-4a18-8b9a-4e52d2465b4f

DTA will fail to execute if loginname in trace didn't have SHOWPLAN permissions.

Hi,

I came across a question which is as below:

Problem Description
*********************
We have a user named TEST and it has DB_DATAREADER ; DB_DATAWRITER permissions on a database say it as TEST.

We have a query which take long time to execute. We capture Profiler Trace for
this query (executed by TEST user) with below mentioned events:

Stored Procedure : RPC Started
RPC Completed
SP:Started
SP:Completed
SP:StmtStarted
SP:StmtCompleted
TSQL : SQL:BatchStarted
SQL:BatchCompleted
SQL:StmtStarting
SQL:StmtCompleted

We use this Profiler trace and execute Database Tunning Advisor (DTA) but DTA
fails to execute.

Root Cause
***********

When we run DTA on a Trace file that has LOGINNAME column selected in this, if
that user didn't have SHOWPLAN permission on said database then it will fail to
run DTA tasks and as a result will fail with below type of errors.

TITLE: Database Engine Tuning Advisor
------------------------------
An unexpected error occurred. Click Continue to ignore this error and attempt to continue. Click Quit to shut down the application immediately.

For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server+Database+Engine+Tuning+Advisor&ProdVer=9.00.4035.00
;EvtSrc=Microsoft.SqlServer.ExpressManager.SRMainError&EvtID=UnexpectedError&LinkId
=20476
------------------------------
ADDITIONAL INFORMATION:

Cannot select non-existing cell (0, 0, 0, 0).
Parameter name: SelectionBlocks (Microsoft.SqlServer.GridControl)
------------------------------
BUTTONS: &Continue &Quit
------------------------------

Resolution
***********

Either we should provide SHOWPLAN permission to the user who ran that query and
which is in the trace file.

Don't select LOGINNAME column while capturing profiler trace in this case DTA
In this case, the dta impersonate the loginname as sysadmin or dbowner, and tune
all the events captured.

Maintenance Plan Will Not Execute Thru Command Line

Hi,

I came across a problem where one of newly created Maintenance Plan was not working thru command line. When I did some I research i found that there a propery called as DISABLE for each plan which is by default set to TRUE and it should be set to FALSE in order to get this Maintenance Plan work from command line.

Steps:
1) Open Maintenance Plan in SSMS.
2) Click on VIEW menu then Property Windows.
3) Property Window will appears on the right hand side of the screen.
4) Under section EXECUTIONs we have 2nd option named as DISABLE which is by
default set to TRUE, Change it to FALSE.

Please check below screebshot

SELECT LAST 2 RECORDS IN A TABLE

Hi Friends,

Recently I got a question on MSDN post to find last 2 records in a table. Below is the query which i figure out in order to do that.

create table hh (Id Int, Name varchar(20), salary int)
go
insert into hh values (5,'ankit',233)
insert into hh values (1,'amit',777)
insert into hh values (6,'anuj',666)
go
select identity(int,1,1) as SlNo,* into #temp from hh
select * from (select top 2 * from #temp order by slno
desc) a order by slno
drop table #temp
go

Below is that post : http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/4d57e34f-85c6-4105-9a17-6b60dc1b251a

Happy Learning......