Windows Server NIC address error

We virtualized one of our servers with VMM2012 P2V. Conversion went like a charm, but there was some IP problems.
So, the VMM2012 converted NIC adapters, but he transferred addresses to Hyper-V Virtual Network adapter, because his physical adapters are no longer present.
The server was up and running in virtual environment, but I hade to make some modifications to NIC.
then I received an error:

“The IP address X.X.X.X you have entered for this network adapter is already assigned to another adapter……”

So that means that no longer present adapter has the same address as the new virtual one.
Now, first of all we must go to device manager, but we must be able to see also nonpresnet adapters.
Open CMD as admin on that machine:
set devmgr_show_nonpresnet_devices=1
start devmgmt.msc

On View menu select Show hidden devices.
image

Expand Network adapters, and uninstall old/no presented adapters.

I suggest restart the machine

Good Luck

Windows: RPC server unavailable

Today I had some problem of connecting to some of Windows 7 machines. The error I recieved was: The RPC server is unavailable.
After searching some articles on the net, I found out that I need to enable RemoteAdmin firewall rule, and add DCOM TCP port 135 to Inbound rules.

call netsh firewall set service RemoteAdmin enable
call netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135

For port 135 there is a firewall rule Windows Management Instrumentation (DCOM-In) which needs to be enabled.

Good Luck

SQL SMSS connect as another Windows NT user

I tried to find out how to connect through SMSS to SQL server as another domain user.
You must run SMSS as “RUN AS..”

so the code is as follows:
runas /netonly /user:domain\user “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

 

EDIT:!!
Due to version changes:

%windir%\System32\runas.exe /netonly /user:domain\user “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe -S SERVER”

EDIT!!

When you use the “/netonly” switch, you can log in using remote credentials on another domain which you are not member of, even if there’s no trust set up.
Runas uses credentials for accessing remote resources. The application interacts with the local computer as the current user, and interacts with remote computers as the user you’ve given.

After that, jus connect through your SMSS and run “SELECT SUSER_NAME,SYSTEM_USER” and you will see that you are under the login you specified.

Or in a new query you can write
execute as login=’DOMAIN\user’
“SELECT SUSER_NAME,SYSTEM_USER”

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
GO
— Update the change
RECONFIGURE
GO
— To enable the xp_cmdshell feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO

 

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

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.
First
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.

image

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”
CHANGE.EXE config.ini “PARAM1=OFF” “PARAM1=ON”

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

Adding or removing text from config file:
Config file:
[START]
PARAM1=ON
PARAM2=OFF
PARAM3=ON
[EOF]

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

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 config.ini.new
//now combine config.ini.temp and pram4.txt into new file config.ini.new
copy/b config.ini.new config.ini
//now overrun config.ini with config.ini.new

Deleting
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…