CREATE TABLE BLOB_TABLE (BLOBName varchar(100),BLOBData varbinary(MAX)) GO INSERT INTO BLOB_TABLE (BLOBName, BLOBData) SELECT 'First test file', BulkColumn FROM OPENROWSET(Bulk 'C:\temp\picture1.jpg', SINGLE_BLOB) AS BLOB GO
OPENROWSET has the functionality of letting you import BLOB data by returning the contents of the Binary Data as a single rowset varbinary(max) output.
SET TEXTSIZE 0 SET NOCOUNT ON --Declare the Local Variables DECLARE @position int, @string varchar(256),@count int,@rownum int,@output varchar(8000) -- Initialize the Local Variables used to keep track of the current position and offsets SET @position = 1 SET @count = 1 -- Get the data that you want to convert to ASCII select [name],ROW_NUMBER() OVER(ORDER BY [name]) as ROWNUM into #tmp_tbl from sys.syslogins select @rownum = max(rownum) from #tmp_tbl -- Begin WHILE loop to convert the String to ASCII while (@rownum >= @count) begin select @string = [name] from #tmp_tbl where rownum = @count print 'Converting:'+@string WHILE @position <= DATALENGTH(@string) BEGIN SET @output = @output+space(1)+CAST(ASCII(SUBSTRING(@string, @position, 1)) as CHAR(4)) + '|' + CHAR(ASCII(SUBSTRING(@string, @position, 1))) SET @position = @position + 1 END set @position = 1 set @count = @count + 1 print @output set @output = '' end -- Drop the Temporary Table created above drop table #tmp_tbl SET NOCOUNT OFF
116 |t 101 |e 115 |s 116 |t 101 |e 114 |r
SQL Server startup parameters -d, -l, -e which store the information about the master database data file, log file and ERRORLOG locations respectively are stored under the registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\Parameters
However, if you need to find out the startup parameters without using the regedit snap-in (default and any extra ones added), you can use the following script:
<<SCRIPT>>
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS'",,48)
For Each objItem in colItems
Wscript.Echo "ServiceName: " & objItem.ServiceName
Wscript.Echo "PropertyName: " & objItem.PropertyName
Wscript.Echo "PropertyStrValue: " & objItem.PropertyStrValue
Next
<</SCRIPT>
This can be done using the following script
******* START OF SCRIPT ********
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM ServerNetworkProtocolProperty where InstanceName='MSSQLSERVER' and ProtocolName = 'Tcp'",,48)
For Each objItem in colItems
Wscript.Echo "-----------------------------------"
Wscript.Echo "ServerNetworkProtocolProperty instance"
Wscript.Echo "-----------------------------------"
Wscript.Echo "InstanceName: " & objItem.InstanceName
Wscript.Echo "IPAddressName: " & objItem.IPAddressName
Wscript.Echo "PropertyName: " & objItem.PropertyName
Wscript.Echo "PropertyNumVal: " & objItem.PropertyNumVal
Wscript.Echo "PropertyStrVal: " & objItem.PropertyStrVal
Wscript.Echo "PropertyType: " & objItem.PropertyType
Wscript.Echo "PropertyValType: " & objItem.PropertyValType
Wscript.Echo "ProtocolName: " & objItem.ProtocolName
Next
******* END OF SCRIPT ********
This uses the class ServerNetworkProtocolProperty. Similarly, you use the above script and modify it to obtain information for the other server network protocols.
Our domain security policy requires us to change our Domain Account passwords every once in a while. This security feature is a good practice but the change of password requires me to change my SQL Server component startup accounts to be changed also as most of them run under my Domain Account.
Tired of accomplishing this task which requires me to type out my user name and password (twice for the reconfirmation 🙂 ) brought forward the need for automating this task. Once I did this, I thought it would be prudent for me to share the same with the SQL community. WMI is quite powerful and a number of tasks can be achieved through it which you shall see in my future blog posts. I have created a category in my blog completely for WMI.
Addition: October 24th, 2011
If you are using SQL Server 2008 R2, then you need to have this fix applied so that SetServiceAccount can work:
2397020 FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 R2 in Windows Vista or in a later version of Windows
http://support.microsoft.com/default.aspx?scid=kb;EN-US;2397020
If you use SetServiceAccount to change the password, then this will result in a SQL Server service restart. The script provided on the Technet Gallery is for changing both the service account and the password. If only the password needs to be changed for the service account, then use SetServiceAccountPassword. This will change the password and will not automatically restart your SQL Server service.
Download the file: pwdchange file and change the name to pwdchange.vbs. This can be executed from command prompt using the following command (provided you have Windows Script Host which almost all Windows Servers have):
cscript pwdchange.vbs
The script has comments written to help you understand the same. This script makes use of the following namespaces:
\root\Microsoft\SqlServer\ComputerManagement (SQL Server 2005)
\root\CIMV2 (SQL Server 2000)
The reason I do not use SQL Server 2000 WMI namespace as it is not installed by default with a SQL Server 2000 default installation. It has to be installed separately. So, I decided to use CIMV2 which has Win32_Service for achieving the same purpose. In the script you would have to provide the following details:
strAccount1 = “Account1” ‘ For alias@domain.com
strAccount2 = “Account2” ‘ For domain\alias naming convention
strAccount3 = “Account1” ‘ To cover any other naming convention
strAlias = “Alias” ‘ Provide the alias
strDomain = “DomainName” ‘ Provide the domain name
strPassword = “Set Password Here” ‘ Put the new password here
TroubleshootingSQL 