Friday, March 30, 2012

openquery datasource not working

Hi,
I hope someone can help with a query that's puzzling me.
I have this query that I can't understand.
SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
'SELECT createdate,loginname FROM Master.dbo.syslogins where
isntname=1'
) AS p
When I run it, it returns data, but not from the server I specified in
the Datasource. In fact I can change the Datasource to anything even
gibberish and it still returns the same set of records.On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> Hi,
> I hope someone can help with a query that's puzzling me.
> I have this query that I can't understand.
> SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> isntname=1'
> ) AS p
> When I run it, it returns data, but not from the server I specified in
> the Datasource. In fact I can change the Datasource to anything even
> gibberish and it still returns the same set of records.
The connection string that you are using is wrong. Instead of
datasource=server1 it should be server=server1.
Adi|||The argument is called "Data Source" not "datasource".
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/|||On Sep 18, 4:25 pm, Adi <adic...@.hotmail.com> wrote:
> On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> > Hi,
> > I hope someone can help with a query that's puzzling me.
> > I have this query that I can't understand.
> > SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> > Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> > 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> > isntname=1'
> > ) AS p
> > When I run it, it returns data, but not from the server I specified in
> > the Datasource. In fact I can change the Datasource to anything even
> > gibberish and it still returns the same set of records.
> The connection string that you are using is wrong. Instead of
> datasource=server1 it should be server=server1.
> Adi
Thanks, that did the trick.I can't think where I got the Datasource
from.|||On Sep 18, 4:30 pm, ML <M...@.discussions.microsoft.com> wrote:
> The argument is called "Data Source" not "datasource".
> ML
> --
> Matija Lah, SQL Server MVPhttp://milambda.blogspot.com/
Thanks for your response. I did try Data Source but it did the same
thing. Problem resolved with Adi's response but thanks anyway.

No comments:

Post a Comment