?
Solved

DNN with External SQL Datasource

Posted on 2007-10-16
11
Medium Priority
?
316 Views
Last Modified: 2012-08-13
Hi,

I am creating a custom DNN module and i've got a database structure in a separate SQL database to the DNN database.  How do i write my stored procedures and SQL installation files to reference this external datasource?

Many Thanks.
0
Comment
Question by:Memorix
  • 6
  • 5
11 Comments
 
LVL 14

Expert Comment

by:Jai S
ID: 20084701
by DNN you mean DOTNETNUKE ???
can you rephrase your question..if DNN means dotnetnuke...thnx
0
 
LVL 3

Author Comment

by:Memorix
ID: 20084729
Yeh i mean DotNetNuke.

I am creating a custom DotNetNuke module and i've got a database structure (for a booking system) in a separate SQL database (ATF_Booking) to the DotNetNuke database (dnn_ATF).  How do i write my stored procedures and SQL installation files to reference this external datasource (ATF_Booking)?

i.e.  I have a DriverID field in the booking table in the ATF_Booking database which references the UserID from the Users table in the dnn_ATF database.

Many Thanks.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20084797
is your DNN DB also configured to use SQL server DB ?
then it is easy to write
you have to specify the servername.databasename.spname and call it...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:Memorix
ID: 20084879
Hi,

I've created my sql statement as below:-

select PortalId,
       BookingId,
       CreatedByUser,
       CreatedDate
from ATF_Booking with (nolock)
left outer join [d2h-emachine].[dnn_ATF].Users on ATF_Booking.CreatedByUser = [d2h-emachine].[dnn_ATF].Users.UserId
where  Portal = @PortalId

However i get an error message:-

Invalid object name d2h-emachine.dnn_ATF.Users

Any ideas?
0
 
LVL 14

Accepted Solution

by:
Jai S earned 2000 total points
ID: 20084895
[d2h-emachine].[dnn_ATF].Users
emachine name - servername
dnn_ATF - nukes database
users - tablename

can you try specifying the DBOWNER like
 [d2h-emachine].[dnn_ATF].dbo.Users
0
 
LVL 3

Author Comment

by:Memorix
ID: 20084941
I now have:-

select PortalId,
       BookingId,
       CreatedByUser,
       CreatedDate
from ATF_Booking with (nolock)
left outer join [d2h-emachine].[dnn_ATF].dbo.Users on ATF_Booking.CreatedByUser = [d2h-emachine].[dnn_ATF].dbo.Users.UserId
where  Portal = @PortalId

but it wont let me update the stored produre now as i get:-

Msg 207, Level 16, State 1, Procedure ATF_GetBookings, Line 16
Invalid column name 'CreatedByUser'.
Msg 4104, Level 16, State 1, Procedure ATF_GetBookings, Line 16
The multi-part identifier "d2h-emachine.dnn_ATF.dbo.Users.UserId" could not be bound.
Msg 207, Level 16, State 1, Procedure ATF_GetBookings, Line 17
Invalid column name 'Portal'.
Msg 207, Level 16, State 1, Procedure ATF_GetBookings, Line 13
Invalid column name 'CreatedByUser'.
Msg 207, Level 16, State 1, Procedure ATF_GetBookings, Line 14
Invalid column name 'CreatedDate'.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20084975
so strange...do these fields exists in your table ATF_Booking ?
can you try this instead of yours...

select ab.PortalId,
       ab.BookingId,
       ab.CreatedByUser,
       ab.CreatedDate
from ATF_Booking ab with (nolock)
left outer join [d2h-emachine].[dnn_ATF].dbo.Users dd on ab.CreatedByUser = dd.UserId
where  ab.Portal = @PortalId
0
 
LVL 3

Author Comment

by:Memorix
ID: 20085068
I get the same thing.  If i take out the dbo it will let me save the updates to the stored procedure but when i try to run it i'm back to:-

Invalid object name d2h-emachine.dnn_ATF.Users
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20085085
can you just try to do a normal select  like
select * from [d2h-emachine].[dnn_ATF].Users
and
select * from [d2h-emachine].[dnn_ATF].dbo.Users

can you copy the results to me
0
 
LVL 3

Author Comment

by:Memorix
ID: 20085191
My apologies... this problem appears to be because of my own stupidity.  Just to clear up the problem:-

Adding the .dbo did (and was the solution i needed) however, i didn't have CreatedDate and CreatedByUser in my database... i've got DriverID and no CreatedDate.  Once i actually stopped to read the error messages properly it clicked.

Many Thanks for your help.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20085243
great...thnx for the grade thou !!! :-))
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question