Query Across Multiple SQL Server Registrations

I have defined 2 SQL Server registrations in Ent Mgr. One is running locally and the other is located on another server. I want to be able to create a stored procedure in the local database that pulls data into a local table from the remote server.

I have wasted much time trying to define Linked Servers and Remote servers, and find the documentation all confusing and subsequently I have gotten nowhere (except frustrated). How do I configure the remote database so I can access it from a stored procedure in the local database?

Here is the code for creating the linked server. Run script against your local database. Hope you have access permissions on remote db. Also replace the IP address 999.999.999.999 with your remote system's IP address. You can query the tables on remote db with four part name.

Code Snippet

USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'Lnk_RemoteDB', @.srvproduct=N'sqlserver', @.provider=N'SQLOLEDB',
@.datasrc = '999.999.999.999', -- IP Address
@.catalog=N'RemoteDB'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'Lnk_RemoteDB', @.locallogin = NULL , @.useself = N'True'

|||

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

|||

Sniegel wrote:

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

select * from Lnk_RemoteDB.AgentDB.dbo.Agents|||I get an Authentication failed error. I tried switching the mode through EM and entering a username/password, but it didn't seem to work.

Q: sum of defined field

Hello,
I created a column and create a field: myField that does some calculation. I
was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
not sum details. How can I sum it?
Thanks,
Jim.Not sure what you mean by "does not sum details" but you will have to
duplicate at the group level any calculations you perform at the detail
level. For example, if at the detail level you have
"=ReportItems!myField.Vlaue*2" then at the group level, you would need
"=Sum(ReportItems!myField.Vlaue)*2"
"JIM.H." wrote:
> Hello,
> I created a column and create a field: myField that does some calculation. I
> was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> not sum details. How can I sum it?
> Thanks,
> Jim.
>|||I have this in one of the columns at the second group level. I named the
field DepTotal
=ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
not have problem at this level, I get my number.
I added this in the first group level in the same column.
=Sum(ReportItems!DepTotal.Value)
It does not compile and it says aggregate function can be used only on
reports items contained in page headers and footers. This column has value in
only second group level, there is no detail data, might it be the problem?
"CGW" wrote:
> Not sure what you mean by "does not sum details" but you will have to
> duplicate at the group level any calculations you perform at the detail
> level. For example, if at the detail level you have
> "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> "=Sum(ReportItems!myField.Vlaue)*2"
> "JIM.H." wrote:
> > Hello,
> > I created a column and create a field: myField that does some calculation. I
> > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > not sum details. How can I sum it?
> > Thanks,
> > Jim.
> >|||When you say you named the field Dep Total, do you mean you named the textbox
that?
I believe you'll get the figure you're wanting for the second group level if
you use
=Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
ReportItems!Dep3.Value), or, if the groups are different from what I
understand them to be...
=Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
Sum(ReportItems!Dep3.Value),
"JIM.H." wrote:
> I have this in one of the columns at the second group level. I named the
> field DepTotal
> =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> not have problem at this level, I get my number.
> I added this in the first group level in the same column.
> =Sum(ReportItems!DepTotal.Value)
> It does not compile and it says aggregate function can be used only on
> reports items contained in page headers and footers. This column has value in
> only second group level, there is no detail data, might it be the problem?
>
> "CGW" wrote:
> > Not sure what you mean by "does not sum details" but you will have to
> > duplicate at the group level any calculations you perform at the detail
> > level. For example, if at the detail level you have
> > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > "=Sum(ReportItems!myField.Vlaue)*2"
> >
> > "JIM.H." wrote:
> >
> > > Hello,
> > > I created a column and create a field: myField that does some calculation. I
> > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > not sum details. How can I sum it?
> > > Thanks,
> > > Jim.
> > >|||yes, when I go to properties of textbox I see DepTotal and thsi is group
level 2. No in group level 1, I need to sum all group level 2 DepTotal
values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
level 1 too. I still get the same message for Dep1,2,3.
"CGW" wrote:
> When you say you named the field Dep Total, do you mean you named the textbox
> that?
> I believe you'll get the figure you're wanting for the second group level if
> you use
> =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> ReportItems!Dep3.Value), or, if the groups are different from what I
> understand them to be...
> =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> Sum(ReportItems!Dep3.Value),
>
> "JIM.H." wrote:
> > I have this in one of the columns at the second group level. I named the
> > field DepTotal
> > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > not have problem at this level, I get my number.
> >
> > I added this in the first group level in the same column.
> > =Sum(ReportItems!DepTotal.Value)
> >
> > It does not compile and it says aggregate function can be used only on
> > reports items contained in page headers and footers. This column has value in
> > only second group level, there is no detail data, might it be the problem?
> >
> >
> >
> > "CGW" wrote:
> >
> > > Not sure what you mean by "does not sum details" but you will have to
> > > duplicate at the group level any calculations you perform at the detail
> > > level. For example, if at the detail level you have
> > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > "=Sum(ReportItems!myField.Vlaue)*2"
> > >
> > > "JIM.H." wrote:
> > >
> > > > Hello,
> > > > I created a column and create a field: myField that does some calculation. I
> > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > not sum details. How can I sum it?
> > > > Thanks,
> > > > Jim.
> > > >|||The same sum(whatever) in each group should yield sums appropriate for that
group. If you get the same total in each group, then the groups are
identical. You might check your group definitions in the properities of the
table to make sure you're grouping distinctly.
"JIM.H." wrote:
> yes, when I go to properties of textbox I see DepTotal and thsi is group
> level 2. No in group level 1, I need to sum all group level 2 DepTotal
> values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
> ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
> level 1 too. I still get the same message for Dep1,2,3.
>
> "CGW" wrote:
> > When you say you named the field Dep Total, do you mean you named the textbox
> > that?
> >
> > I believe you'll get the figure you're wanting for the second group level if
> > you use
> >
> > =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> > ReportItems!Dep3.Value), or, if the groups are different from what I
> > understand them to be...
> > =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> > Sum(ReportItems!Dep3.Value),
> >
> >
> > "JIM.H." wrote:
> >
> > > I have this in one of the columns at the second group level. I named the
> > > field DepTotal
> > > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > > not have problem at this level, I get my number.
> > >
> > > I added this in the first group level in the same column.
> > > =Sum(ReportItems!DepTotal.Value)
> > >
> > > It does not compile and it says aggregate function can be used only on
> > > reports items contained in page headers and footers. This column has value in
> > > only second group level, there is no detail data, might it be the problem?
> > >
> > >
> > >
> > > "CGW" wrote:
> > >
> > > > Not sure what you mean by "does not sum details" but you will have to
> > > > duplicate at the group level any calculations you perform at the detail
> > > > level. For example, if at the detail level you have
> > > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > > "=Sum(ReportItems!myField.Vlaue)*2"
> > > >
> > > > "JIM.H." wrote:
> > > >
> > > > > Hello,
> > > > > I created a column and create a field: myField that does some calculation. I
> > > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > > not sum details. How can I sum it?
> > > > > Thanks,
> > > > > Jim.
> > > > >