February 6, 2007 at 2:51 am
try to get the MDAC version from registry from a remote sql server, but get an incorrect syntax error near '@conn' when running the code below. Any ideas how to fix this or another way to do this, any input would be greatly appreciated.
declare @servername nvarchar(100)
declare @password nvarchar(100)
declare @conn nvarchar(1000)
set @servername = 'servername\instance'
set @password = 'password'
set @conn = 'Data Source='+@servername+';User ID=sa;Password='+@password
--select @conn
declare @returnvalue int
declare @path nvarchar(4000)
exec @returnvalue = OPENDATASOURCE(
'SQLOLEDB',
@conn
).master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\DataAccess',N'FullInstallVer', @path output, 'no_output'
select @path
February 6, 2007 at 7:47 am
first .. why?
Think you'll find access to the registry has been tightened by default on sql 2005 ( in fact I know it has ) to stop this type of query. If I remember correctly you need to enable this in surface config, but I'd probably advise against it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 6, 2007 at 7:08 pm
what if I am trying to remote connect to a SQL server 2000 with sp3a / sp4. Would that work for both also?
February 7, 2007 at 1:29 am
The extended procs still have access on 2000, it's on 2005 the security has been tightened on, as I say you can alter the permissions in surface config.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply