Jeff Sullivan
going with the flow
Posts:39
 |
| 09-21-2009 05:42 PM |
|
I have a stored procedure to call for my initial bind, but it resides in a different database. How do I specify the data connection to use for the call in the initial bind?
(I can see where to specify the connection for form completion SQL events.) |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-22-2009 06:29 AM |
|
You can try something like the following except replace your datasource:
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
-- David
|
|
|
|
|
Jeff Sullivan
going with the flow
Posts:39
|
| 09-22-2009 06:33 AM |
|
David,
The data source is a stored procedure, not a SQL SELECT. It's got some logic in it that requires more than a single select. (Plus, the username the site runs under doesn't have direct SQL SELECT rights to all the tables.)
Is there no option to specify the data source of the initial bind? |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-22-2009 06:36 AM |
|
If it resides in a different database but on the same DNN server, you should be able to call it by using the fully complete database name. Just try calling the store procedure with the databasename in front of it. Try it under Host / SQL first to see if it works though. -- David |
|
|
|
|
Jeff Sullivan
going with the flow
Posts:39
|
| 09-22-2009 10:49 AM |
|
The other database is not guaranteed to be n the same server by the hosting company. I can't see it from my database now, even if it is ont he same server. We have to use a connection string to get to it. |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-22-2009 02:26 PM |
|
HI, if it's not on the same server, you would need to set up a linked server. Try this out:
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’ |
|
|
|
|
Jeff Sullivan
going with the flow
Posts:39
|
| 09-23-2009 08:16 AM |
|
Thanks, David. I'll have to try this.
I take it from the suggestions that there is no option to define the connection string for the initial bind like there is for the form completion events.
Can this be noted as a request for 4.0? Any time you cna call SQL, provide the option to specify a connection string for the SQL data provider. |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-23-2009 08:52 AM |
|
HI, you could also try adding the connection string in the web.config file and see if it works? -- David |
|
|
|
|
Jeff Sullivan
going with the flow
Posts:39
|
| 09-23-2009 10:55 AM |
|
David,
Where would I put it, and how would I access it in DF for the initial bind? |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-23-2009 11:19 AM |
|
Well, web.config was just a thought. I just talked to our chief developer Chad and he said a connection string under web.config won't work for SQL binding. -- David |
|
|
|
|
Jeff Sullivan
going with the flow
Posts:39
|
| 09-23-2009 11:26 AM |
|
Any chance of a future feature to allow a module-level connection string that any SQL access in the module could potentially use? Or a per-use connection-string override to use in place of the DNN connection if desired? |
|
|
|
|