is it possible to query a different database using the Advanced Setup Wizard?

I'm trying SELECT * from [otherdatabase].[dbo].[tablename] and it returns no results.

If so, any tips for this?   I've been trying for a while...

If it helps any, this is for a new install (just purchased yesterday), which is different than the other install I had all the questions abuot previously :-)

1) First, if you execute that under Host / SQL, does it return anything? Need to determine if the otherdatabase is on the same server and you have permission to access in order for this to work. -- David

Doh, correct - it was a security issue on the SQL side. Blargh. I guess on the other site they had setup a linked server that had a set u/p (which I think is a horrible security flaw)...

On this site, I run the server - so I setup DNN to use a Trusted SQL connection, then I gave read/write to that domain account to the other (external, but on the same server), so the query [otherdb].[dbo].[storedprocedurename] works via Host, but intial SQLBind isn't working, nor is setting up the default using a SQL statement for the question itself.

i'm sure I am doing something wrong.

X10 my vote to all setting up other connection strings :-)

HI, please read the following:

------------------------------------------------------------------------
Select Distinct CentreName as QuestionOption, CentreName as QuestionOptionValue FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=pro3;User ID=removed;Password=removed').abcuniforms.dbo.staff Where CentreState = '$(ABCState)' And Deleted = 0 And Password Like '%childcare.com.au%' Order by CentreName ASC

I am thankful for sites like this that really try to help. I have been struggling to call a stored proc from a linked server when the calling server is SQL 2005 and the linked server is SQL 2000. After a lot of reading and experimenting I found the solution, which I have laid out in simple steps below. Good Luck!

– Add a linked server
EXEC sp_addlinkedserver
@server=’BOB’,
@srvproduct=”,
@provider=’SQLOLEDB’,
@datasrc=’SERVER’,
@catalog = ‘Northwind’

– get a list of linked servers
EXEC sp_linkedservers

– add linked server login (this user and password has already been created on linked server)
EXEC sp_addlinkedsrvlogin ‘BOB’, ‘false’, NULL, ‘userlogin’, ‘password’

EXEC sp_serveroption @server=’BOB’, @optname = ‘rpc’, @optvalue=’true’
EXEC sp_serveroption @server=’BOB’, @optname=’rpc out’, @optvalue=’true’

–see all linked servers and status
EXEC sp_helpserver

– perform query against linked server
SELECT * FROM OPENQUERY (BOB, ‘SELECT * FROM customers’)

– execute stored proc on Linked Server
declare @CustomerID char(50)
EXEC BOB.Northwind.dbo.CustOrderHist ‘ALFKI’

– drop linked server logins (must be done prior to droping linked server)
EXEC sp_droplinkedsrvlogin ‘BOB’, NULL

– drop linked server
EXEC sp_dropserver ‘BOB’
-----------------------------------------------------------------------------------------------------
Hope this points you in the right direction. -- David