Link to home
Start Free TrialLog in
Avatar of Member_2_6478753
Member_2_6478753Flag for United States of America

asked on

Executing an openrowset sql string will not work from sql server 2008 to a sql server 2000

DECLARE @SQL VARCHAR(2500) = '
SELECT z.* from openrowset(''SQLNCLI'',''Server=10.1.1.5;UID=sa;PWD=;'', 
                                       ''SELECT TOP 10 * FROM [shortocodefiltration].[dbo].[shortcodetable] '') z'

EXECUTE (@SQL)

Open in new window




RESULT

Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Open in new window



the two mssql server on the same network ???
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Your message says the feature is just turned off.  ??  Have someone turn it on, there are instructions in the message.
Avatar of Member_2_6478753

ASKER

my question how to make this feature work ?
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial