Friday, August 15, 2014

Windows Path Environment Variable Length

There is a known issue with the Windows 7 Path Environment Variable Length limited to 2048 characters.  If it becomes longer than that it will sometimes cause issues when running anything in the %windir%.

First, you need to open your PATH variable and copy its value into a safe spot in case it gets messed up. Then you can extract the last half of your path and create a new environment variable and call it PATH2. After your new environment variable is setup, you can reference it in your main PATH:

PATH2=:\Program Files (x86)\AMD APP\bin\x86_64;c:\Program Files (x86)\AMD APP\bin\x86;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\OpenCL SDK\3.0\bin\x86;C:\Program Files (x86)\Intel\OpenCL SDK\3.0\bin\x64;c:\Program Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\Program Files\TortoiseSVN\bin;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft\Web Platform Installer\;C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\;C:\Program Files (x86)\Windows Kits\8.0\Windows Performance Toolkit\;C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\;C:\Program Files (x86)\WinSCP\;%TFSPowerToolDir%;%BPADir%;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\

PATH=%PATH2%;C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;%systemroot%\idmu\common;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files (x86)\GtkSharp\2.12\bin;C:\sandbox\Medicalis\Eligibility\apache-ant\bin

This works very well, if the Main PATH starts to reach the same limit then ADD PATH3, PATH4 and etc.

-Paul

Thursday, August 14, 2014

Capture the SQL User for Log Shipped Servers

When you restore a database to another SQL Server Instance the SQL Server User login will not match up because they will have different SIDs.
This code can be used to capture the create login script from the source server in a log shipping scenario or where you don't want to have to recreate the users on the individual databases.

SELECT'CREATE LOGIN ['+ loginname +']'
  ,' With PASSWORD ='
  , cast(password AS varbinary(256))
  ,' HASHED , SID = '
  , sid,', DEFAULT_DATABASE=['+ dbname +']'
  ,', DEFAULT_LANGUAGE = ['+ language +']'
  ,', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'/* modify as needed */
FROM syslogins
WHERE isntname =0 AND name IN('[username]', '[username2]')
ORDER BY loginname

Once you run this script you can copy it's results and run on the target server.
- Paul