DNN with External SQL Datasource

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.
LVL 3
MemorixAsked:
Who is Participating?
 
Jai SConnect With a Mentor Tech ArchCommented:
[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
 
Jai STech ArchCommented:
by DNN you mean DOTNETNUKE ???
can you rephrase your question..if DNN means dotnetnuke...thnx
0
 
MemorixAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jai STech ArchCommented:
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
 
MemorixAuthor Commented:
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
 
MemorixAuthor Commented:
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
 
Jai STech ArchCommented:
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
 
MemorixAuthor Commented:
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
 
Jai STech ArchCommented:
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
 
MemorixAuthor Commented:
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
 
Jai STech ArchCommented:
great...thnx for the grade thou !!! :-))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.