Teamviewer – a great ad-hoc support

As I wrote earlier in my blog post Working with Batch, we had a problems connecting to all computers in our branch offices. Since we live on VPN, provided by ISP on his modems/routers, there was always war with theirs technicians.
First of all, we had an old software Symantec PCAnywhere 11 which we used to connect to one computer in that remote office. from that computer we connected to all others. But, PcAnywhare had a problem, you had to setup a port forwarding (PcAnywhere ports are TCP 5631 and UDP 5632) on ISP’s modem to one of those computers. That computer had to have a fixed IP address, or setup DHCP MAC address assignment. But on power outage, thunder struck or clumsy maid, who needed a power source for her vacuum, the modem/router would reset and loose it’s settings. Then you call ISP’s technician to set it up again, hence the war begins. Then we switched to Win7, but PCAnywhere had issues with it. Connection drops, no picture to display and etc.

image Then came the revolutionary Teamviewer (further TW).
The free version, set limited time for sessions, when it discovered that you are using it for non-commercial use.
I suggested to my bosses that we should buy this little piece of software. And we did. We bought TEAMVIEWER 6. I must say it isn’t the cheapest software but it worth’s every €cent.

Teamviewer works on the principal of fixed addresses. TW server assigns 9 digit address ID code to every computer. BE CAREFUL after reinstalling a computer the ID CANNOT be changed back!!

Several TW products are there to use 
TW Full is used to connect to other TW devices. It can be used as a standalone software, or you can install it and use it as installed software. image
TW Quicksupport is standalone program which user starts on demand. It shows him his ID and generates a random password. TW QS can be also edited (for commercial users) with custom logo or text.
TW Host is an installation of TW as a service on a remote device.
TW QuickJoin is a standalone software, with which an user can connect to your presentation.
TW Portable is a standalone program, which allows you to use it on a USB stick and connect everywhere from anywhere.
TW Manager is a product for central managing your partner ID’s. It can use either a file as a database or a MSSQL database.
TW Web is a web service. You must create your username on TW and you use it as a partner list. Then login to TW Web and you can connect also from anywhere.

With TW you can easy set black and white list (BE CAREFUL after reinstalling a computer the ID CANNOT be changed back!!). Set access control, set password complexity, or set permission to change TW settings. You can transfer files,record sessions, transfer audio or video, and even chat. Organize presentations and meetings.
You can even install VPN adapters on each side and connect through VPN.
The TW is a great time and nerve saver.

Good Luck

Setup Win7 or Win8 from USB drive

I have a little Acer Revo without a CD-ROM. External CD-ROM is expensive, so I reinstalled my little PC from USB stick.

First, you have to have a stick not smaller than installation CD/DVD of Windows 7 or 8. Next, motherboard must support Boot from USB so you can boot to setup.
The procedure goes like this, because we must prepare the USB with DISKPART. In my case CD/DVD-ROM is D: drive and USB flash drive is E: drive


Next, make USB bootable
Enter to your CD-ROM and subfolder boot


And then copy all from CD/DVD to your USB. I used xcopy, but you can copy it from Windows explorer.
XCOPY * E:\ /E /Y

Plug in you USB into a computer, and boot from USB.

Good Luck

Space, the final frontier….

We all need space….

Once upon a time I had a 250GB HDD, and it was back then large. Everything I needed was on that disk. But as the files grew, so did my hard got smaller. On that disk, I have lot of pictures and music, maybe some movie and bunch of documents. Suddenly I ran out of disk space…

Quest No.1 – Understand consumption

My first quest was, first to find what consumes so much space.  For that I used a free disk analyzer software called Space Sniffer. It is a standalone exe file.
You can choose a drive, or a path. works also with UNC paths. Then press Start.image

Very intuitive and practical software. It graphically shows the size of every folder or file. This depends on scaling the details. You go a level deeper by clicking on a particular square. From this screen, you can also open, edit and delete files and folders. Best of all, this software is free, and at the end you can create a log file.

Quest No.2 – Duplicity

As the files grew in numbers, there has also been a problem of some laziness. Some files got duplicated. After googleing and trying couple of software, I found one, also free of charge. Fast Duplicate file Finder from MindGems. Easy to install and easy to work with.

Works with data files, audio and picture files.
For test I have crated a three folders, and put various duplicate files in them. First add folder, and setup scan properties. If you think files are similar, then choose similar (but slower method) or find 100% identical. I will leave it at 100%. Press SCAN.

After it scanned you get report like this, sorted by duplicate groups.

Duplicates are automatically checked. So you can probably delete them and save some space.

So, that’s how I got several GB of space free.

Good luck

TSQL – working with folders and output to file

This article shows how to create a folder for backup. The idea is to create folder with date part in its name, and then backup a specific database to that folder.

First we need to create a parameters to save our SQL command in it.
use master
declare @sql1 nvarchar(max)
declare @sql2 nvarchar(max)
declare @var1 varchar(100)
declare @var2 varchar(100)

–So first we create a folder, manually, called BACKUP. In this folder we will save our subfolders and backups.
set @var1 = N’"C:\BACKUP\SQLDB ‘+(select convert(varchar,getdate(),104))+’"’    –(CONVERT turns the date into dd.MM.yyyy format)

–save to @sql1 statement you wish to execute
set @sql1 = N’exec xp_create_subdir ‘+@var1

–next, we have to crate a variable which will contain path and name of a backup file.
set @var2 = N’C:\BACKUP\SQLDB ‘+(select convert(varchar,getdate(),104))+’\Database_’+convert(varchar,getdate(),112)+’.bkp’

–now, create @sql2 statement you wish to execute
set @sql2 =N’BACKUP DATABASE Database TO DISK = ‘ + QUOTENAME( @var2 , ”” )+’ WITH FORMAT, stats;’  –@var2 replaces path and filename of backup


Now execute both @sql1 and @sql2 statements

EXEC sp_executesql @sql1

EXEC sp_executesql @sql2


You can also export query result to file with small program called BCP.
To call BCP from SQL Query you need to use a function called xp_cmdshell. But to use this function, it must be enabled in SQL Server Advanced options.
The quickest way is to write TSQL for it

— Set advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
— Update the change
— To enable the xp_cmdshell feature.
EXEC sp_configure ‘xp_cmdshell’, 1
— To update the currently configured value for this feature.


Now we can export it with the following statement:

exec master..xp_cmdshell ‘bcp "select top 500 * from sysobjects" queryout "c:\id\tempexportfile.txt" -c -t, -T -S ‘+@@servername

path to file – is referenced to server side path
– C use default code page for char, varchar or text columns
-t     use TAB as field terminator
-T    use trusted connection to server
-S  use server –S servername

Rename SQL Server 2008 – standalone

Sometimes its a necessity to rename a SQL server or SQL server instance. I had to rename it couple of times in my IT career.

These are steps that I made.

step1 :
exec sp_dropserver ‘oldserver’
exec sp_addserver ‘newserver’,’local’
step2 :
restart the sql server and check whether below query output is same or not .
select @@servername,serverproperty(‘servername’)

step3 : for updating jobs – if you have any
use msdb
update sysjobs
set originating_server = serverproperty(‘servername’)


Once I used this procedure to rename newly installed SQL server, the second time it was on “production” server.
On the production server, I had to reinstall Reporting Server, because there was no way I could get it going of all the relations to old server name.
But database server, and jobs and connection to renamed server worked.

So, to everybody, good luck!

Simple Calendar–first thing in BI reporting

When you are building or creating rudimentary BI for someone, there are always some parameters you have to embed in your query. But, almost always there is date to date parameters. So, it is very helpful to have a calendar table, where you know which days are weekends, which days are working days, what week of the year it is, to which quarter does some date belong to, etc…


So first create table:

CREATE TABLE dbo.Calendar
weekend bit,
year bit,
quartal bit,
month bit,
day bit,
day_of_week bit,
month_name varchar(20),
day_name varchar(20),
week_in_year bit


Now we have to fill the table. For this query, you must set your start date, maximal date, language for names and type of query to execute.
My start date is 01.01.2010, end date is 31.12.2050, language Croatian and I’ll work with recursive CTE query.


declare @begdate datetime,
@enddate datetime

set @begdate = ‘2010-01-01’
set @enddate = ‘2050-12-31’

with Ctecalendar(date,isweekend, y, q,m,d,dw,monthname,dayname,w) as –define CTE
select @begdate ,
case when datepart(dw,@begdate ) in (6,7) then 1 else 0 end, –saturday and sunday
year(@begdate ),
datepart(qq,@begdate ),
datepart(mm,@begdate ),
datepart(dd,@begdate ),
datepart(dw,@begdate ),
datename(month, @begdate ),
datename(dw, @begdate ),
datepart(wk, @begdate )
union all
select date + 1,
case when datepart(dw,date + 1) in (6,7) then 1 else 0 end,
year(date + 1),
datepart(qq,date + 1),
datepart(mm,date + 1),
datepart(dd,date + 1),
datepart(dw,date + 1),
datename(month, date + 1),
datename(dw, date + 1),
datepart(wk, date + 1) from Ctecalendar where date + 1<= @enddate
insert into dbo.Calendar
select date,isweekend, y, q,m,d,dw,monthname,dayname,w from Ctecalendar
option(maxrecursion 32767) –bad query can cause infinitive loop, so you set this to loop 32767 times, 0 for infinitive

So there you have it, simple Calendar table for your queries!


Working with batch procedure–going back to roots

Couple of years ago, we had a problem with custom software. The problem was, how to install new version of software (size around 60MB+), only on main computers in our branch offices, which are over 60 locations. Since we don’t have in house programmer, we needed a solution. Solution by now, was connecting to remote computers (our VPN is on ADSL) over ADSL modem, configured to port-forward PcAnywhere. Then copy new version to that computer, unpack, change config file, and deploy. Three men, have been doing this for 5 days.
We (meaning I) needed a solution, to make this less time consuming and less repetitive.

I sat down, and came up with the solution. Semi-automated batch script. The only thing that the person in office would do is, start batch, and press corresponding number (1-proceed,2-Cancel).

So, here is a little demo.
You start by using echo command like this, but must disable “echo” to print with “@echo off”
Then we needed a blank Main Screen. CLS is command to ClearScreen.


Click on picture to open SkyDrive with this file.

So we have small sample of batch file. Now we have to crate a shared folder, somewhere on the VPN, where all users can reach this batch.
And in the end, set shortcuts (you can do that via logon script) to users desktop.

This is simple batch file for copy numerous things to computers around you. But there is one more thing I needed to do.
How to change config file, insert new parameters or change the existing ones.
Prepare to be amazed  🙂

Changing a string in config file:
For this I came across a little exe called change.exe (file is here).
Lets say you need to change PARAM1=OFF to PARAM1=ON.
The syntax is
CHANGE.EXE [filename] “what to change” “change to”

Problem with CHANGE.EXE is that this file does not work in 64bit environment.

Adding or removing text from config file:
Config file:

Lets say you have to put another parameter  PARAM4=OFF (or multiple) into end of config file.
Create a file PARAM4.txt

Make a batch with statement:
find /v “[EOF]” < config.ini> config.ini.temp
//find [EOF] in config.ini and everything above it save to config.ini.temp
copy/b config.ini.temp+param4.txt
//now combine config.ini.temp and pram4.txt into new file
copy/b config.ini
//now overrun config.ini with

Lets say you have to remove PARAM2=OFF, then you need only two lines from above:

find /v “PARAM2=OFF” < config.ini> config.ini.temp
//find PARAM2 in config.ini and everything else save to config.ini.temp
copy/b config.ini.temp config.ini
//now overrun config.ini with config.ini.temp

So, this should cover small portion of batch procedures.
Till next time…

The Love between IBM iSeries Access and SQL 2012

Ok, so let’s start with my first blog.
Couple of days ago, I’ve been given a task to migrate some tables from AS400 to SQL.
So first of all, you would need a connector (provider) to IBM AS400 structure. So that connector comes with IBM iSeries Access software. After quick and “select All option” installation, I thought this is it.
I started SQL Import and Export Wizard to test things out. But the error appeared:

After couple of minutes, I found that the .NET config file had been corrupted. Searching through .NET config, I found the problem. In file “C:WindowsMicrosoft.NETFrameworkv4.0.30319Configmachine.config” there was a unclosed line
<add name=”IBM DB2 for i5/OS .NET Provider” invariant=”IBM.Data.DB2.iSeries” description=”.NET Framework Data Provider for i5/OS” type=”IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26″ />
   <DbProviderFactories/>  —-delete  this line
Of course you’ll probably have problems of saving that protected file. So, my advice, save that file on desktop, edit it, and then copy it back to original location.

After that, the Import Export worked as a charm. Now, next step is to create a DTX package, and schedule it to transfer data. SQL 2012 does not have Business Intelligence, but SQL Server Data Tools (further in text SSDT).

Start SSDT, create a project, create Control Flow, add Connection Managers to SQL server and to AS400.

But, I’ve stumbled on a problem here.
When working and testing DTX package from SSDT, everything works, but for some reason when you import that package into Integration Services, and start it, the password for IBM AS400 connector is missing.
Solution to this problem is:

Start SSDT with “Run as Administrator” option, design package, enter username and password into AS400 connection Manager , and select “Allow saving password”.

Save package. When importing to Integration 
Services you must select option “Rely on server storage and roles for access control”!


And that about covers the problem I’ve encountered working with these two products.

Enjoy migrating!




Let me introduce my idea of this blog post. This should be a little help for all you DBA and IT admins. Little tips and tricks on HOW TO..
So for now, I have to get my thoughts together and think of a couple of thing I did that made my life easier.

Best regards