I have a SQL2005 question:
I have a query that runs fine on SQL2000
declare @rc int,@dir nvarchar(5)
exec @rc = master.dbo.xp_instance_regreadN'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',N'TcpPort', @dir output
@dir returns 1433
Under SQL2005 it returns
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Anyone have an idea?
yes, it is folder heirarchy is changed in SQL 2005.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\TcpPort
It contains one folder with spaces (Microsoft SQL Server) and now master.dbo.xp_instance_regread is giving error there. I tried to put single quote, double quote, all types of braces but none of them worked.
Anybody knows how to resolve this?
Try using xp_regread, such as:
'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll','TcpPort', @dir output