All posts by npulis

How To: Execute and run files from a stored procedure in SQL Server

Sometimes when you need to automate some stuff from the SQL server you need to create a batch file and run it in the Task Scheduler. This way you can run the jobs you need to do and then run the executable or batch file you need from a Stored Procedure.

Firstly you need to make sure that the XP_CMDSHELL is enabled. This will allow you to execute the files from SQL.

This can be easily done from the Surface Area Configuration tool or by executing the following code:

EXEC master.dbo.sp_configure ‘show advanced options’, 1
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1

This should enable the XP_CMDSHELL. Now to execute a file you need to add the following SQL code to your present Stored procedure:

DECLARE @myfile varchar(200)
SET @myfile = ‘C:\sysfiles\copytootherserver.bat’
EXEC master..xp_cmdshell @myfile


Fix: Getting rid of ‘You have been logged on with a temporary profile’ on Windows

For Windows Vista and 7. This might have happen to most of us. When you log in your profile, you get a popup on the bottom right that says ‘You have been logged on with a temporary profile. You cannot access your files and files created in this profile will be deleted when you log off. This sometimes happens when the username has either been changed or the folder of the profile has been deleted. So Windows will continue to look for it and leave you with a temporary profile. To fix this you need to make the following to restore the folder.

:: Log on the machine as an Administrator
:: Click on Start and type regedit
:: Find the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList
:: There are a lot of folders in the key. Find the ones with the .BAK at the end
:: Delete the profiles in question with the .BAK extention (Make sure you take a backup of the registry first)
:: Close Regedit
:: Restart PC

After you log into the computer, the profile folder you are having problems with is re-created.


Fix: How to remove hidden or phantom interfaces from Windows

Sometime you will note that some network interfaces would either not work or just give errors. This usually occurs when using images on HyperV but just in case you need to remove hidden or phantom devices like ‘network adapter #5’, here’s how.

:: Open command prompt
:: Type CMD
:: Type set devmgr_show_nonpresent_devices=1
:: From the same window (Do not close the window in this process), type devmgmt.msc

This will load the Device Manager. Now, click on ‘View’ and select ‘Show hidden devices’.

Now you can simply either delete the hidden/phantom interfaces or uninstall the device.


Fix: Slave server not connecting to master on Yosemite Server Backup

When you install Yosemite Backup solution on a server having multiple network cards, the agents will not work and the server will not be visible by the master server. This is because Yosemite binds itself to a network card. Now, to fix this, simply edit the ytconfig.ini in the Yosemite folder and add the following line under the configuration tab and set it to your the IP address of the network card:



How To: List all software installed on a machine with VBScript

This VBScript will list all the software installed on the computername you specify in the first line of code. Simply save the code below in a .vbs file and execute it by running it as:

CSCRIPT.EXE /NOLOGO myscript.vbs >logfile.txt

This will give you a nice text file with all the applications installed on the specified computer.

strComputer = "mycomputername"
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSoftware = objWMIService.ExecQuery ("Select * from Win32_Product")'
Set colSettings = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")

WScript.Echo "Installed Software List and OS Information"
WScript.Echo "=========================================="
Wscript.Echo ""

'Computer Information'
Wscript.Echo "===================="
Wscript.Echo "Computer Name: " & strComputer
For Each objOperatingSystem in colSettings
'Crop Operating system name for clean name'
Result = InStr(objOperatingSystem.Name, "|")
Result = Result -1
MyString = Left(objOperatingSystem.Name,Result)
'Echo stuff
Wscript.Echo "Operating System: " & MyString
Wscript.Echo "Service Pack: " & objOperatingSystem.ServicePackMajorVersion & "." & objOperatingSystem.ServicePackMinorVersion
Wscript.Echo "Installed In: " & objOperatingSystem.WindowsDirectory
Wscript.Echo ""

'Software Information'
Wscript.Echo "SOFTWARE LIST"
Wscript.Echo "============="
For Each objSoftware in colSoftware
wscript.echo objSoftware.Caption


How To: Send email with embedded text file as body with VBScript

This is an easy VBScript that will allow you to send notifications by mail. Thou applications like SQL have the Database mail, if you have log files which you need to send a text log file from an application as a notification.

Just change the basic email information to your likings and the Mail Server configuration. If it’s an open SMTP server leave the MailSendUsername and MailSendPassword empty. To embed a text file in the body, change the Logfile.txt entry to your filename. Save the below code as anything.vbs and run it as a scheduled task.

Dim objEmail
Set objEmail = CreateObject("CDO.Message")

'** Seting basic email information **
Const EmailFrom = """My server notification"" "
Const EmailTo = ","
Const EmailSubject = "My Notification"

'** Setting Mail Server Configuration **
Const MailSendUsing = "2"
Const MailSendServer = ""
Const MailSendPort = "25"
Const MailSendUsername = ""
Const MailSendPassword = "mypassword"
Const MailSendAuthenticationType = "1"

'** Email Parameters (DO NOT CHANGE) **
objEmail.From = EmailFrom
objEmail.To = EmailTo
objEmail.Subject = EmailSubject
objEmail.Textbody = EmailBody
objEmail.AddAttachment EmailAttachments
objEmail.Configuration.Fields.Item ("") = MailSendUsing
ObjEmail.Configuration.Fields.Item ("") = MailSendServer
objEmail.Configuration.Fields.Item ("") = MailSendPort
objEmail.Configuration.Fields.Item ("") = MailSendAuthenticationType
objEmail.Configuration.Fields.Item ("") = MailSendUsername
objEmail.Configuration.Fields.Item ("") = MailSendPassword

'** Setting a text file to be shown in the email Body **
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
'** File to be inserted in Body
Const FileToBeUsed = "Logfile.txt"
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
'** Open the file for reading
Set f = fso.OpenTextFile(FileToBeUsed, ForReading)
'** The ReadAll method reads the entire file into the variable BodyText
objEmail.Textbody = f.ReadAll
'** Close the file
'** Clear variables
Set f = Nothing
Set fso = Nothing

'* cdoSendUsingPickup (1)
'* cdoSendUsingPort (2)
'* cdoSendUsingExchange (3)

'** Parameters (DO NOT CHANGE) **


How To: Automated shrink transaction logs in SQL

Making a scheduled automated shrink logs for the databases with specific space to shrink.

DECLARE @Name varchar(50)

SELECT NAME FROM sysdatabases WHERE dbid > 4



SET @Step1 = 'USE ' + @Name + '' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY SIMPLE;' + CHAR(10)
SET @Step1 = @Step1 + 'DBCC SHRINKFILE (' +@Name + '_LOG, 500);' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY FULL;' + CHAR(10)

EXEC (@Step1)