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.

OpenDataSource Problem

begin
select @.datasource = 'Data Source="c:/'
+@.FOLDERNAME
+'/'
+@.FILENAME
+'";User ID=;Password=;Extended properties=Excel 5.0'
select
*
into
#excel_table
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @.DATASOURCE)...sheet1$
end

need to do this ... but using variables in opendatasource not allowed.

so how do we go about it

What i need to do is allow upload of any excel file ... may contain errors and then perform some checks on the data before putting it into another table with a fixed structure.select @.query ='create procedure sp_excel_upload as select
*
into
excel_table
from OpenDataSource( '+''''+'Microsoft.Jet.OLEDB.4.0'+''''+','+''''+'Da ta Source="'
+@.FOLDERNAME
+@.FILENAME
+'";User ID=;Password=;Extended properties=Excel 5.0'+'''' + ')...sheet1$'
exec (@.query)
exec sp_excel_upload
/* put checks here */
select * into final_table from excel_table
drop procedure sp_excel_upload
drop table excel_table

How much performance overhead would this approach add on say about 40 files daily ?

Any ideas ??|||Guys .. Gurus ...

Nobody has any thoughts about this ??sql