conversions part 2: ip addresses stored as decimal

Leave a comment

My previous conversion post covered unix epoch date conversion. Here I discuss my adventures with converting decimal ip addresses.

Internet IP v 4 addresses look like this 41.22.154.123. 4 numbers separated by periods. Each number ranges from 0 to 255. I never knew it, but these addresses are commonly stored as binary or as decimal numbers. One reason is to facilitate sorting the addresses. There’s no good way to sort the addresses in the dotted notation.

Here’s the function I ended up using with Oracle PL/SQL to convert the decimal value to dotted.

create or replacefunction inttoip(ip_address integer) return varchar2
deterministic
is
begin    
return to_char(mod(trunc(ip_address/256/256/256),256)) ||'.'||to_char(mod(trunc(ip_address/256/256),256))           ||'.'||to_char(mod(trunc(ip_address/256),256))           ||'.'||to_char(mod(ip_address,256));
end;

A colleague sent to me some SQL Server and Oracle conversions using a slightly different approach. I’ve copied these below. And of course, google searches will quickly direct you to other material in this area.

-- MS SQL Server with convert to bigint
SELECT dbo.IPADDRESS.IPADDRESS,
CAST(ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST((cast(dbo.IPADDRESS.IPADDRESS as bigint) % 256 ) AS varchar(4)) as IPDottedNotation
FROM  dbo.IPADDRESS

-- MS SQL Server From non numeric datatype
 
SELECT     dbo.DEVICE.HOSTNAME, dbo.IPADDRESS.IPADDRESS,
CAST( ROUND( (dbo.IPADDRESS.IPADDRESS / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST( (dbo.IPADDRESS.IPADDRESS % 256 ) AS varchar(4)) as IPDottedNotation
FROM dbo.IPADDRESS 

-- Oracle PL/SQL
SELECT DEVICE.HOSTNAME, IPADDRESS.IPADDRESS,
cast(
CAST(ROUND((IPADDRESS.IPADDRESS / 16777215 ), 0) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 65535), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 256  ), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(IPADDRESS.IPADDRESS, 256 ) AS varchar2(3))
as char(20)) as IPDottedNotation
FROM IPADDRESS
 

conversions from unix epoch format OR loving the 1970s

Leave a comment

I recently encountered two conversion issues that are specific to working with computers. I address converting unix epoch date format to human readable date format here. My next post will address converting IP addresses stored as decimal integers to the more familiar dotted format.

There’s a popular work request management software product called Teamtrack from Serena software. The version deployed in my shop stores dates in its database using unix epoch format, which is the number of seconds since Jan 1, 1970. To convert this in a sql server t-sql query, I used:

select dateadd(s, table.date_field, '19700101') as friendlydate
from table

Interestingly, there were a few fields where I needed to do a slight modification, and I’m not sure why. Perhaps another timezone?

select dateadd(s, table.date_field,  '19691231 19:00:00') as friendlydate
from table

Forum poster jciappas from Brazil posts the Oracle PL/SQL version.

And at a dedicated web site for epoch converters, you can find code for doing the conversions in a variety of languages and databases. This web site covers going back and forth from unix epoch to human readable.

dtutil, named instance and package import

1 Comment

I was trying to get an SSIS package imported into a named instance using the command line utility dtutil and couldn’t get it to work.

This command had been working just fine with a default instance.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy DTS;"MSDB\Maintenance Plansuser\dbbackup"

But when running the same command on a named instance, I got the following:
dtutil /File userdbbackup.dtsx /DestServer hostnameinstancename /Copy DTS;"MSDB\Maintenance Plans\userdbbackup"

Microsoft (R) SQL Server SSIS Package Utilities
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2004. All rights reserved.

Could not create SSIS Service Storage object because of error 0x800706BA.
Error (0x800706BA) while checking for the existence of package “Maintenance Plansuserdbbackup” in the SSIS package store.
Could not create SSIS Service Storage object because of error 0x800706BA.
Description: The RPC server is unavailable.

The solution was changing the DTS flag to SQL in the /COPY parameter. I also had to change the folder location by removing MSDB.
dtutil /File userdbbackup.dtsx /DestServer hostname /Copy SQL;"\Maintenance Plans\userdbbackup"

This means the package is getting stored within MSDB in the SQL Server rather than the SSIS Package Store.

Note that the commands above do not specify username and password. The commands were run on the local host with a privileged user that had a trusted connection to the DB.

Here are some links on the topic of getting the SSIS Package Store to work with Named Instances:  Robert Davis explains and Tim Cullen covers similar ground.

If I understand the above links correctly, if I manipulate the XML files as they indicate, the dtutil /COPY DTS can be made to work with named instances.

By the way, this concise dtutil tutorial helped steer me in the right direction. It even mentioned dtexec and dtexecui, both new to me.

SQL Server connection protocols and named instances

Leave a comment

I’ve been working on a project that involves automating tasks on SQL Server installs. Things got a bit messy when working with multiple instances on the same host.

To automate sql scripts via a VB.NET program, I ended up using shared memory connections. The code will always run locally on the host where the instances reside, so no problem there. I learned the following about SQL Server connection string behavior:

1. This will always use the default instance with a shared memory connection. Parentheses are required.
Data Source=(local); Integrated Security=True

2. This will allow a connection to a named instance via shared memory. Note there are no parentheses.
Data Source=HOSTNAME\NAMEDINSTANCE; Integrated Security=True

3. This will force a connection over TCP/IP on the port specified, provided the SQL Server Network Configuration has been set to the port indicated.
Data Source=HOSTNAME\NAMEDINSTANCE,port; Integrated Security=True

The above connection strings can be used in data access code in VB.NET and other languages.

Want to force a shared memory connection from sqlcmd? You can append the prefix lpc:
sqlcmd -S lpc:HOSTNAME\NAMEDINSTANCE -E

Although
sqlcmd -S HOSTNAME\NAMEDINSTANCE -E
does the same thing when executed from the local host. When executed remotely, you likely will need the port to appended after NamedInstance.

For other SQL Server protocols such as Named Pipes, there must be additional abbreviations, but I haven’t dug into that…

Lastly, one can tell from within SQL Server what protocols are used by what sessions. The view sys.dm_exec_connections shows that, specifically the net_transport column. See the below query which joins to sysprocesses.

select d.net_transport, s.spid, s.program_name, s.nt_username, s.hostprocess, s.lastwaittype, d.client_tcp_port, d.protocol_type, d.protocol_version
from sysprocesses s
inner join sys.dm_exec_connections d
on s.spid = d.session_id