In the question validation there is a field for "Validation SQL Query" but no field for the connection string.

In the form completion events for sql there is the connection string.

Where do I specify the connection string of the question validation?

Thanks,

James.

HI, do you need to specify a connection string here? If you run the query under Host / SQL will it work? If so, you won't need a connection string then. -- David

The query is run against a different database than the actual dnn db. We have a form to catch user data to our own system, and this the case that user must select a user id and we need to check it is unique against our own CRM system, not against any dnn tables.

Hi, is the database on the same server as your DNN database? If so, you don't need a connection string. On the other hand if it's on another server altogether, then can you follow the example below to set up a linked server and then you should be able to call a stored procedure to select from that server. Thanks. -- David

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’