SQL Server Performance Forum – Threads Archive

ad-hoc query using OPENROWSET

Hi there, How can I perform ad-hoc query with OPENROSET using trusted connection ? This rowset function gets a parameter that includes the data source(server name), user id and password. I thought that the parameter "provider_string" will probably be useful in this case but it is not. I used the follwing provider_string, taken from an OLEDB connection to the local SQL Server (database PerfAnalysis) that I have set using trusted connection (I have taken the text of the UDL file ): Provider=SQLOLEDB.1;Integrated Security=SSPI<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=False;Initial Catalog=PerfAnalysis;Data Source=LocalSQLServer Any suggestion ? Thanks. Aviel Iluz Database Administrator Pacific Micromarketing Melbourne Australia www.pacmicro.com.au

I think that the server is configured for trusted connections only, use the following :
select * from
openrowset
(
‘SQLOLEDB’,
‘Trusted_Connection=yes;Integrated Security=SSPI;Data Source=(local);’,
‘set fmtonly on exec mystoredproc 1’
) http://support.microsoft.com/default.aspx?scid=kb;EN-US;296711 for reference. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks you very much, Satya. It works !
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

]]>