ASP.NET - QUERY MULTIPLE DATABASES
Hi Experts,
I need to query tables from a range of databases/servers in a single asp:SqlDataSource...i.e. I'm joining tables from
My ConnectionStrings are listed in my WebConfig and I can call each one individually .
How do I now expose my asp:SqlDataSource to more than one ConnectionString in order to query joined tables from my different databases/servers?
or do I need to take a different approach?
I need to query tables from a range of databases/servers in a single asp:SqlDataSource...i.e. I'm joining tables from
My ConnectionStrings are listed in my WebConfig and I can call each one individually .
How do I now expose my asp:SqlDataSource to more than one ConnectionString in order to query joined tables from my different databases/servers?
or do I need to take a different approach?
Or show what you have so far and we can help with your code.
ASKER
Thanks both, I confess having read your comment mroonal I feel a bit lost.
So I have a sql query on two tables - each table lives in a different database on a different server and my query just uses a couple of left joins to get the data I'm after, below the query as it stands on SQL.
SO I'm querying Mobile_Details & Calls3 which live in a database called mobiles on one server and those are joined to StaffDirectory which lives in another database on a different server...
select sd.department, SUM(ca.Cost) as CallCosts
FROM Mobiles.dbo.MobileDetails md
LEFT JOIN Intranet.UserDetails.dbo.S
ON md.Mobile_Number COLLATE DATABASE_DEFAULT = sd.mobile COLLATE DATABASE_DEFAULT
LEFT JOIN Mobiles.dbo.Calls3 Ca
ON md.Mobile_Number COLLATE DATABASE_DEFAULT = Ca.CalledFrom COLLATE DATABASE_DEFAULT
WHERE datapart(m, CallDate) = 1
I just want to replicate it in a web app at this stage, so that I can start to display the data on a web page.
I have all my connection strings 'mobile' & 'intranet' in my webConfig to pick up the databases but to this point I have always used <asp:SqlDataSource to write my query "select ..." etc.
Using this method it would seem you can only name one Connection String at a time but presumably I need to name both because my data originates from two different connectionstrings?
I can't show you anything because there's nothing to show, fallen at the first hurdle :o(
So I have a sql query on two tables - each table lives in a different database on a different server and my query just uses a couple of left joins to get the data I'm after, below the query as it stands on SQL.
SO I'm querying Mobile_Details & Calls3 which live in a database called mobiles on one server and those are joined to StaffDirectory which lives in another database on a different server...
select sd.department, SUM(ca.Cost) as CallCosts
FROM Mobiles.dbo.MobileDetails md
LEFT JOIN Intranet.UserDetails.dbo.S
ON md.Mobile_Number COLLATE DATABASE_DEFAULT = sd.mobile COLLATE DATABASE_DEFAULT
LEFT JOIN Mobiles.dbo.Calls3 Ca
ON md.Mobile_Number COLLATE DATABASE_DEFAULT = Ca.CalledFrom COLLATE DATABASE_DEFAULT
WHERE datapart(m, CallDate) = 1
I just want to replicate it in a web app at this stage, so that I can start to display the data on a web page.
I have all my connection strings 'mobile' & 'intranet' in my webConfig to pick up the databases but to this point I have always used <asp:SqlDataSource to write my query "select ..." etc.
Using this method it would seem you can only name one Connection String at a time but presumably I need to name both because my data originates from two different connectionstrings?
I can't show you anything because there's nothing to show, fallen at the first hurdle :o(
ASKER
Thank you,
I've been trying to understand the options suggested by mroonal, it seems I can do one of two things;
either create a stored procedure from my select in SQl and call that from my aspx page.
or
create a dataset that pulls the data in and holds it in datatables which can then be related
So I tried the stored proceedure approach first - working on the principle that it's better to take just what you need from SQL this seemed the more efficient approach.
My stored procedure works fine in SQL if I run it in a query (Exec StoredProcedureName), but I am having less luck running it in aspx page:
Since I ground to a halt there I've been attempting plan B; creating a Dataset & DataTables - again all new to me.
I have successfully created a dataset & datatable from one database collecting from a single sql table.
I moved on to add my other connectionstring and two more datatables within my single dataset (this seemed to be the approach suggested) I have set up two datarelations to mirror the table joins in my SQL query and am just reading about merging but I feel as though having created this dataset I will need to refine it with a query to get back to something resembling my original cross-server sql query which included things like SUM etc.
I'd be happy to get either method working, assuming it is indeed possible, but would also be interested to know if one approach is preferable.
If anyone can get me a step closer i'd appreciate the help.
I've been trying to understand the options suggested by mroonal, it seems I can do one of two things;
either create a stored procedure from my select in SQl and call that from my aspx page.
or
create a dataset that pulls the data in and holds it in datatables which can then be related
So I tried the stored proceedure approach first - working on the principle that it's better to take just what you need from SQL this seemed the more efficient approach.
My stored procedure works fine in SQL if I run it in a query (Exec StoredProcedureName), but I am having less luck running it in aspx page:
Since I ground to a halt there I've been attempting plan B; creating a Dataset & DataTables - again all new to me.
I have successfully created a dataset & datatable from one database collecting from a single sql table.
I moved on to add my other connectionstring and two more datatables within my single dataset (this seemed to be the approach suggested) I have set up two datarelations to mirror the table joins in my SQL query and am just reading about merging but I feel as though having created this dataset I will need to refine it with a query to get back to something resembling my original cross-server sql query which included things like SUM etc.
I'd be happy to get either method working, assuming it is indeed possible, but would also be interested to know if one approach is preferable.
If anyone can get me a step closer i'd appreciate the help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello (again) thank you for coming to my rescue.
SQL servers:
My query is living in the 'mobiles' database this is on a sql 2008 server
I used addlinkedserver to include the 'intranet' database which lives on a sql 2000 server
Yes we are trying to apportion mobile costs (monthly rental and monthly calls) to individuals and their respective departments for reconciliation purposes. At this stage I believe we will just want to read results on this.
I think I will need to write back on another cross-server query between the master server and an instance on yet another server - but maybe cross that bridge seperately...
So the query given at the start of this thread is now saved as a stored procedure on my master server in the mobiles db and it returns the results fine on SQL, as you know from my earlier question I imagined I could just call that stored procedure in my aspx but from reading more since it seems I need to add a bit more code to get results from procedure to screen.
Hope that makes sense
SQL servers:
My query is living in the 'mobiles' database this is on a sql 2008 server
I used addlinkedserver to include the 'intranet' database which lives on a sql 2000 server
Yes we are trying to apportion mobile costs (monthly rental and monthly calls) to individuals and their respective departments for reconciliation purposes. At this stage I believe we will just want to read results on this.
I think I will need to write back on another cross-server query between the master server and an instance on yet another server - but maybe cross that bridge seperately...
So the query given at the start of this thread is now saved as a stored procedure on my master server in the mobiles db and it returns the results fine on SQL, as you know from my earlier question I imagined I could just call that stored procedure in my aspx but from reading more since it seems I need to add a bit more code to get results from procedure to screen.
Hope that makes sense
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Umm ok I've done a quick read through, going to fill up my coffee cup and re-read next! But this is what I think I've done...
On my aspx page I have a GridView with to pick up department and cost:
<asp:GridView ID="GridView1" runat="server">
<Columns>
<asp:TemplateField HeaderText="Department" ItemStyle-Width="100px" HeaderStyle-HorizontalAlig
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("department") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Costs" ItemStyle-Width="100px" HeaderStyle-HorizontalAlig
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("CallCosts") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
On my code behind I have two things my Stored procedure class which gets my SqlConnections from Web.config calls my SQL stored procedure and then to be honest I get a bit confused and have been trying different approaches because it struck me that maybe I was calling the procedure but not actually executing it:
public static DataTable GetMobileData()
{
DataTable CallCost = new DataTable();
SqlConnection cn;
cn = new SqlConnection(WebConfigura
//cn = new SqlConnection(WebConfigura
try
{
SqlCommand cmd = new SqlCommand("CallCostByDept
cmd.CommandType = CommandType.StoredProcedur
SqlDataReader drCosts;
cn.Open();
drCosts = cmd.ExecuteReader();
//ArrayList alCosts =
//new ArrayList();
//while (drCosts.Read())
//{
// alCosts.Add(drCosts.GetVal
//}
//drCosts.Close();
//cn.Close();
//return alCosts;
cn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(CallCost);
cn.Close();
}
catch
{
cn.Close();
}
return CallCost;
}
and I have the following to bind to my gridview:
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = StoredProcedures.GetMobile
GridView1.DataBind();
when I run the page I get my page title and not much else...no hint of a gridview or any of my efforts, no errors, no flames, just blank white page.
I was thinking the parameters had to come into it but couldn't quite and still can't quite get my head around it as they already exist in my SQL stored procedure and because of the way I'm attaching the datasource to my gridview I'm not sure how/where to have them...umm I haven't listed 'parameters' in my stored procedure either it is literally the query I provide at the start, not sure if that was right or wrong but it was what I was familiar with.
Hope that makes sense.
Umm ok I've done a quick read through, going to fill up my coffee cup and re-read next! But this is what I think I've done...
On my aspx page I have a GridView with to pick up department and cost:
<asp:GridView ID="GridView1" runat="server">
<Columns>
<asp:TemplateField HeaderText="Department" ItemStyle-Width="100px" HeaderStyle-HorizontalAlig
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("department") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Costs" ItemStyle-Width="100px" HeaderStyle-HorizontalAlig
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("CallCosts") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
On my code behind I have two things my Stored procedure class which gets my SqlConnections from Web.config calls my SQL stored procedure and then to be honest I get a bit confused and have been trying different approaches because it struck me that maybe I was calling the procedure but not actually executing it:
public static DataTable GetMobileData()
{
DataTable CallCost = new DataTable();
SqlConnection cn;
cn = new SqlConnection(WebConfigura
//cn = new SqlConnection(WebConfigura
try
{
SqlCommand cmd = new SqlCommand("CallCostByDept
cmd.CommandType = CommandType.StoredProcedur
SqlDataReader drCosts;
cn.Open();
drCosts = cmd.ExecuteReader();
//ArrayList alCosts =
//new ArrayList();
//while (drCosts.Read())
//{
// alCosts.Add(drCosts.GetVal
//}
//drCosts.Close();
//cn.Close();
//return alCosts;
cn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(CallCost);
cn.Close();
}
catch
{
cn.Close();
}
return CallCost;
}
and I have the following to bind to my gridview:
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = StoredProcedures.GetMobile
GridView1.DataBind();
when I run the page I get my page title and not much else...no hint of a gridview or any of my efforts, no errors, no flames, just blank white page.
I was thinking the parameters had to come into it but couldn't quite and still can't quite get my head around it as they already exist in my SQL stored procedure and because of the way I'm attaching the datasource to my gridview I'm not sure how/where to have them...umm I haven't listed 'parameters' in my stored procedure either it is literally the query I provide at the start, not sure if that was right or wrong but it was what I was familiar with.
Hope that makes sense.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
sorry for the delay got tied up doing other stuff had to drop this for a bit.
Ok so I have added in the code for the catch - thank you that was something which had been eluding me I do indeed now get an error but it's annoyingly a login failed error for the mobile database - so that explains the blank page. We were struggling with the logins last week and I thought we'd resolved but apparently not, will need to do some googling on that.
I'm going to try and resolve that then get back to you, i'm on VS2010 but this is the first time i've been able to use the tooling as until now the databases I worked with all lived on a SQL 2005 server which isn't supported, much to my frustration, so thanks for the nudge it hadn't occured to me that I could probably use it for this project (assuming I can figure out the login issue of course). I presume if I start a new 'project' I would 'open new project' and select 'ASP.NET Web Application'.
Many thanks for your help, hugely appreciated.
sorry for the delay got tied up doing other stuff had to drop this for a bit.
Ok so I have added in the code for the catch - thank you that was something which had been eluding me I do indeed now get an error but it's annoyingly a login failed error for the mobile database - so that explains the blank page. We were struggling with the logins last week and I thought we'd resolved but apparently not, will need to do some googling on that.
I'm going to try and resolve that then get back to you, i'm on VS2010 but this is the first time i've been able to use the tooling as until now the databases I worked with all lived on a SQL 2005 server which isn't supported, much to my frustration, so thanks for the nudge it hadn't occured to me that I could probably use it for this project (assuming I can figure out the login issue of course). I presume if I start a new 'project' I would 'open new project' and select 'ASP.NET Web Application'.
Many thanks for your help, hugely appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Thanks,
Mmm so interestingly when I look at my linked servers they are both set to 'Be made using the login's current security context' but when I click 'ok' to close the window for the particular linked server that I am trying to query on this occassion I get 'an exception occurred while executing a Transact_SQL statement or batch'
Ad hoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error259 ) so it looks like that is the problem...will google error for enlightenment as it means little to me.
I was actually wrong this server which I had said was 2000 is actually 2005 - the not supported is the message I get if I try to use the wizards when connecting to a db on the 2005 server, just won't have any of it, so wizards are all new to me. But yes a quick test-run with a db on the SQL2008 server and the wizard was fine...I was a bit lost but the wizard was fine...
ASP.NET Web Application next time I promise!
Mmm so interestingly when I look at my linked servers they are both set to 'Be made using the login's current security context' but when I click 'ok' to close the window for the particular linked server that I am trying to query on this occassion I get 'an exception occurred while executing a Transact_SQL statement or batch'
Ad hoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error259 ) so it looks like that is the problem...will google error for enlightenment as it means little to me.
I was actually wrong this server which I had said was 2000 is actually 2005 - the not supported is the message I get if I try to use the wizards when connecting to a db on the 2005 server, just won't have any of it, so wizards are all new to me. But yes a quick test-run with a db on the SQL2008 server and the wizard was fine...I was a bit lost but the wizard was fine...
ASP.NET Web Application next time I promise!
ASKER
Actually no it is SQL 2000 sorry brain getting addled and the VS message I get if I try to use the wizard tool to coonect my gridview to a SQL 2000 DB is:
Database schema could not be retrieved for this connection. Please make sure the connection settings are correct and that the database is online.
This server version is not supported. You must have Microsoft SQL Server 2005 or later.
Database schema could not be retrieved for this connection. Please make sure the connection settings are correct and that the database is online.
This server version is not supported. You must have Microsoft SQL Server 2005 or later.
ASKER
http://msdn.microsoft.com/en-us/library/aa258720(v=sql.80).aspx
think i need to speak to our server dept.
think i need to speak to our server dept.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks I am determined to get this working, but would never have got this far without your help, only made the switch to IT in '07 so total rookie really.
Ok I have resolved the Error, I think the code I used to addlinkedserver was wrong - having reset that, the error is gone and i'm back to the original login failed message.
And I see now that my add linked server properties reveal that I am using the windows authentication setting so this is now starting to make more sense, you say in your 12:17 post to use the 'SA username and password'.
SA - SQL Administrator??
Would that be the SA username & pasword for the master server or slave servers or do you anticipate it being one and the same..
Yes stored procedure is free from any 'addlinkedserver' calls.
Ok I have resolved the Error, I think the code I used to addlinkedserver was wrong - having reset that, the error is gone and i'm back to the original login failed message.
And I see now that my add linked server properties reveal that I am using the windows authentication setting so this is now starting to make more sense, you say in your 12:17 post to use the 'SA username and password'.
SA - SQL Administrator??
Would that be the SA username & pasword for the master server or slave servers or do you anticipate it being one and the same..
Yes stored procedure is free from any 'addlinkedserver' calls.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmm this could take a while..to find the post-it note !
Umm I was just pondering all this and correct me if i'm wrong but if my login failed for connection to mobiles which is my master db and the error is specifying that login failed for the user I have listed in my web.config for that connection string does that not suggest to you that the problem still lies with that user id & password and specifically that db.
Surely if my linkedserver queries run happily on SQL the addlinkedservers are all speaking to each other already ??
Or am I missing the point...
Umm I was just pondering all this and correct me if i'm wrong but if my login failed for connection to mobiles which is my master db and the error is specifying that login failed for the user I have listed in my web.config for that connection string does that not suggest to you that the problem still lies with that user id & password and specifically that db.
Surely if my linkedserver queries run happily on SQL the addlinkedservers are all speaking to each other already ??
Or am I missing the point...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just been testing a theory this end:
made a new stored procedure that just queried a table in the mobile DB and changed my web code to look at that and ran it.
I get a gridview with results (hurrrahh) .
Switch back and I get the error again :O(.
So this makes me think the problem is to do with the linkedserver access after all(coming back to this in a sec if I may).
We also wondered at one point if the stored procedure itself needs to give access to the defined mobile userid & password held in the web connectionstring - thanks for the link (already stored firmly in my favourites ;0)) had presumed the access was inherited from the db.
my connection string in the web.config is in the following format and works fine for a straightforward call:
<add name="Mobile" providerName="System.Data.
for the stored proc I then call it using:
cn = new SqlConnection(WebConfigura
Going back to the linked server access - reading your para1 & 2 if windowsauth is permitted on both servers am I right in thinking a) it should work and b) we do not need to go looking for post-it notes? i'm told the servers are virtual so post-it notes could be harder to find than I thought.
You know I hadn't even considered looking at IIS settings :0\ what a moron, but presumably if the site uses windowsauth (which it does) that is all we are looking for?
We are starting to wonder this end if SQL 2000 might be the issue here - good time to shift everything over to 2008 maybe...
Might make a 3rd stored procedure between the master and the instance we have on 2008 also a linkedserver (instance accounted for) see if that runs error free - if it does i'm inclined to think thats the answer would you agree with my logic?
made a new stored procedure that just queried a table in the mobile DB and changed my web code to look at that and ran it.
I get a gridview with results (hurrrahh) .
Switch back and I get the error again :O(.
So this makes me think the problem is to do with the linkedserver access after all(coming back to this in a sec if I may).
We also wondered at one point if the stored procedure itself needs to give access to the defined mobile userid & password held in the web connectionstring - thanks for the link (already stored firmly in my favourites ;0)) had presumed the access was inherited from the db.
my connection string in the web.config is in the following format and works fine for a straightforward call:
<add name="Mobile" providerName="System.Data.
for the stored proc I then call it using:
cn = new SqlConnection(WebConfigura
Going back to the linked server access - reading your para1 & 2 if windowsauth is permitted on both servers am I right in thinking a) it should work and b) we do not need to go looking for post-it notes? i'm told the servers are virtual so post-it notes could be harder to find than I thought.
You know I hadn't even considered looking at IIS settings :0\ what a moron, but presumably if the site uses windowsauth (which it does) that is all we are looking for?
We are starting to wonder this end if SQL 2000 might be the issue here - good time to shift everything over to 2008 maybe...
Might make a 3rd stored procedure between the master and the instance we have on 2008 also a linkedserver (instance accounted for) see if that runs error free - if it does i'm inclined to think thats the answer would you agree with my logic?
ASKER
My 3rd stored procedure also produces a login failed so that confirms the problem remains as you suggested with permissions between servers and is not a problem specific to sql 2000.
I'm going to leave the question open if you don't mind while I try and resolve.
I'm going to leave the question open if you don't mind while I try and resolve.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes! problem resolved, the penny finally dropped when I read the bit in this article http://sqlserverplanet.com/dba/how-to-add-a-linked-server on how to define security for a linked server . It follows exactly what you had been saying I just didn't really understand what it was that I needed to do/change.
Now that I have created a user on each of my linked servers with the same login details and permissions I finally have a working page that calls my stored procedure via a connection string to the master server which in turn queries the databases on my various linked (slave)servers.
Thank you so much for your patience and enduring help.
Now that I have created a user on each of my linked servers with the same login details and permissions I finally have a working page that calls my stored procedure via a connection string to the master server which in turn queries the databases on my various linked (slave)servers.
Thank you so much for your patience and enduring help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ha I think we crossed paths.
Yes sorry I was getting confused with the terminology I think, actually I was just plain confused by the end of yesterday...doesn't take much!
Noted suggestion re. instances, fair comment, I just don't do enough pure SQL stuff, so each new experience has a tendancy to induce pain, but it's always satisfying at this stage when the mist clears, yes please add to knowledge base to save others from this torment.
Many thanks once again
Yes sorry I was getting confused with the terminology I think, actually I was just plain confused by the end of yesterday...doesn't take much!
Noted suggestion re. instances, fair comment, I just don't do enough pure SQL stuff, so each new experience has a tendancy to induce pain, but it's always satisfying at this stage when the mist clears, yes please add to knowledge base to save others from this torment.
Many thanks once again
http://forums.asp.net/t/1660960.aspx/1
You have to change your logic.
Here is nice example:
http://forums.asp.net/t/1277853.aspx/1