Multiple-step OLE DB operation generated errors

Hello....

I have two linked server... (ServerB and ServerC) which reside on ServerA. I am able to connect to the remote database using "Select" statements without any issues.

When I run this query, It is successful:

delete [SERVERB].MyDatabase.dbo.TableName
from [SERVERB].MyDatabase.dbo.TableName t1
Left join MyDatabase.dbo.TableName t2 on ( t1.ID = t2.ID and
t1.EmployeeNumber = t2.EmployeeNumber and
t1.AccountNumber = t2.AccountNumber)
where t2.ID is null;

However, when I change [SERVERB] to [SERVERC], I receive two errors:

"Could not find server 'ELEARN-FRM-BETA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

And

OLE DB provider "SQLNCLI" for linked server "ELEARN-FRM-BETA" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

When I run profiler on ServerC, I see traffic... mainly a whole bunch of exec "sp_cursorfetch" operations, so I know the connection is valid.

Any ideas?

Forch

Sorry... I forgot to mention that all three servers are SQL Server 2005 x64 edition with SP1.

Forch

sql

Multiple sources and one target

I'm thinking aboug using Service Broker to aggregate transactions from 12 remote SQL Servers to a central SQL Server. Service Broker is new to me, so I don't know what the proper setup would be.

We have five different kinds of transactions that can be sent from any of the remote servers. These remote servers truly are remote, connecting to the central server over a WAN. We don't want problems with one server to interfere with any other server. We also don't want problems with one kind of transaction to interfere with the processing of any other kind of transaction.

If were only talking about one remote server, I'm guessing I'd want to create 5 different services - one for each kind of transaction. Does that mean I'd also need to create 5 different routes on both the remote server and the central server?

And how would I scale this out to a dozen remote servers? Can I stick with 5 services on the central server, or would I need to define 5 different services for each remote server, so 60 total? And would that be 60 different routes defined on the central server? Based on my limited knowledge of routes and how they're tied to services, I think that would mean 60 routes, but maybe I'm missing something.

Please let me know. Thanks.

Service Broker seems like an excellent choice for the scenario that you have described. It provides isolation mechansims to ensure that two unrelated business transactions can run concurrently.

The primitive for communication in Service Broker is a conversation, which is a reliable, durable session for exchange of messages. Two-party conversations are known as dialogs and they contain two endpoints -- an initiator and a target. Services are nothing but logical entities that can initiate or be the target for a conversation. They are addressable (via routes) and securable (via certificates). But one service can have multiple conversation endpoints to deal with concurrent conversations.

Given that, in general you would create a 'Service' to handle all conversations for some business function which needs to have a distinct identity and location. If you decide to move the service to another server or issue a new certificate, you will affect all conversations targetting or initiating from that service. So if you feel that the 5 business transactions are all distinct and may in the future be performed at different locations, you are better off with 5 services. If that seems impossible or highly unlikely, stick with a single service.

Conversations can be strongly typed by defining formal contracts that specify what type of messages may be sent by each endpoint. Since a service can expose multiple contracts, it can accept conversations belonging to different contracts. In your scenario, you might have different contracts for each of the 5 different business transaction types.

A database can certainly support more than just 60 routes, so don't feel that the number of routes is going to be a performance bottleneck. If you think it might be a managability nightmare, you could consider investing into something we call "Broker Configuration Notification". If you enable this in your database and a new conversation cannot find a route, Service Broker will send a special "Missing Route" message to a service of your choice. You could then implement this "route resolution" service to lookup the route from a directory and create the route in your local database and Service Broker will automatically try to deliver the message of former conversation.

One thing to be aware of in a WAN environment is that Service Broker requires both instances of SQL Server to be able to connect to each other over TCP/IP. That means that if either party is behind a firewall/NAT, the appropriate ports need to be opened for two-way communication.

Hope that helps,
Rushi

|||

Thanks for the information!

David

Multiple RS Instances

Can i have multiple Report Server 05 instances on one machine conneting to
one remote Sql Database instance. Each Report Server will have its own
database in the SQL instanceOn Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> Can i have multiple Report Server 05 instances on one machine conneting to
> one remote Sql Database instance. Each Report Server will have its own
> database in the SQL instance
This link should be helpful.
http://technet.microsoft.com/en-us/library/ms403426.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||i read through the documentatation, but its not clear to me if 2 RS 05
standard ed on one machine can share the same database server but separate
databases. Unless i'm misunderstading it... any ideas?
"EMartinez" wrote:
> On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > Can i have multiple Report Server 05 instances on one machine conneting to
> > one remote Sql Database instance. Each Report Server will have its own
> > database in the SQL instance
>
> This link should be helpful.
> http://technet.microsoft.com/en-us/library/ms403426.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> i read through the documentatation, but its not clear to me if 2 RS 05
> standard ed on one machine can share the same database server but separate
> databases. Unless i'm misunderstading it... any ideas?
> "EMartinez" wrote:
> > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > > Can i have multiple Report Server 05 instances on one machine conneting to
> > > one remote Sql Database instance. Each Report Server will have its own
> > > database in the SQL instance
> > This link should be helpful.
> >http://technet.microsoft.com/en-us/library/ms403426.aspx
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
As far as I know, this should be fine as long as your virtual
directories for Reports and ReportServer are physically separate for
both SQL instances. Also, you will want to make sure that you have at
least one default SQL Server Instance and one named SQL Server
Instance local to SSRS. As long as your SQL Servers are not mixed
(i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
Regards,
Enrique Martinez
Sr. Software Consultant|||So am I understanding you correctly that each RS (standard ed) instance will
need to have their own SQL Server Database Instance?
Here is what my environment looks like:
WebServer has RSSite1(default) AND RSSite2(Named)
DatabaseServer has one Instance Of Sql Server called DBServ
DBServ has four databases:
RSSite1_ReportServer
RSSite1_ReportServerTempDb
RSSite2_ReportServer
RSSite2_ReportServerTempDb
I'm doing this right now, but i'm running into some issues. Just want to
know if this is supported so i can stop troubleshooting this.
Thanks
"EMartinez" wrote:
> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > i read through the documentatation, but its not clear to me if 2 RS 05
> > standard ed on one machine can share the same database server but separate
> > databases. Unless i'm misunderstading it... any ideas?
> >
> > "EMartinez" wrote:
> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> > > > Can i have multiple Report Server 05 instances on one machine conneting to
> > > > one remote Sql Database instance. Each Report Server will have its own
> > > > database in the SQL instance
> >
> > > This link should be helpful.
> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> As far as I know, this should be fine as long as your virtual
> directories for Reports and ReportServer are physically separate for
> both SQL instances. Also, you will want to make sure that you have at
> least one default SQL Server Instance and one named SQL Server
> Instance local to SSRS. As long as your SQL Servers are not mixed
> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||They need their own databases, not their own instance. RS 2005 should have
no problem with this at all.
From the article that Enrique provided a link to:"You can install multiple
instances of Reporting Services on the same computer. Installing multiple
instances is useful if you want to host different content for specific
sites. Each instance will have its own report server database, configuration
files, and virtual directories. "
Note it says report server database NOT instance.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
> So am I understanding you correctly that each RS (standard ed) instance
> will
> need to have their own SQL Server Database Instance?
> Here is what my environment looks like:
> WebServer has RSSite1(default) AND RSSite2(Named)
> DatabaseServer has one Instance Of Sql Server called DBServ
> DBServ has four databases:
> RSSite1_ReportServer
> RSSite1_ReportServerTempDb
> RSSite2_ReportServer
> RSSite2_ReportServerTempDb
> I'm doing this right now, but i'm running into some issues. Just want to
> know if this is supported so i can stop troubleshooting this.
> Thanks
> "EMartinez" wrote:
>> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
>> > i read through the documentatation, but its not clear to me if 2 RS 05
>> > standard ed on one machine can share the same database server but
>> > separate
>> > databases. Unless i'm misunderstading it... any ideas?
>> >
>> > "EMartinez" wrote:
>> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
>> > > > Can i have multiple Report Server 05 instances on one machine
>> > > > conneting to
>> > > > one remote Sql Database instance. Each Report Server will have its
>> > > > own
>> > > > database in the SQL instance
>> >
>> > > This link should be helpful.
>> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
>> >
>> > > Regards,
>> >
>> > > Enrique Martinez
>> > > Sr. Software Consultant
>>
>> As far as I know, this should be fine as long as your virtual
>> directories for Reports and ReportServer are physically separate for
>> both SQL instances. Also, you will want to make sure that you have at
>> least one default SQL Server Instance and one named SQL Server
>> Instance local to SSRS. As long as your SQL Servers are not mixed
>> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Thank You for the clarification...I've been trying that configuration with no
luck. I'll look more into it then. Thanks.
"Marvin" wrote:
> Can i have multiple Report Server 05 instances on one machine conneting to
> one remote Sql Database instance. Each Report Server will have its own
> database in the SQL instance|||Thank you for clarifying...so here's my issue.
I installed a default instance of Report Services Std on a Non-Default
Website with Sql Server on a remote machine. I implemented custom security.
No problems so far. I am able to log-in using Forms Auth and add a Folder.
Then I would install a named instance on the same webserver. Without even
touching it after install (choosing to configure later), the DEFAULT instance
returns a "Bad Request" error when trying to log in. I planned to set up the
named instance on another Website.
Any Ideas?
"Bruce L-C [MVP]" wrote:
> They need their own databases, not their own instance. RS 2005 should have
> no problem with this at all.
> From the article that Enrique provided a link to:"You can install multiple
> instances of Reporting Services on the same computer. Installing multiple
> instances is useful if you want to host different content for specific
> sites. Each instance will have its own report server database, configuration
> files, and virtual directories. "
> Note it says report server database NOT instance.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
> news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
> > So am I understanding you correctly that each RS (standard ed) instance
> > will
> > need to have their own SQL Server Database Instance?
> >
> > Here is what my environment looks like:
> > WebServer has RSSite1(default) AND RSSite2(Named)
> > DatabaseServer has one Instance Of Sql Server called DBServ
> > DBServ has four databases:
> > RSSite1_ReportServer
> > RSSite1_ReportServerTempDb
> > RSSite2_ReportServer
> > RSSite2_ReportServerTempDb
> >
> > I'm doing this right now, but i'm running into some issues. Just want to
> > know if this is supported so i can stop troubleshooting this.
> >
> > Thanks
> >
> > "EMartinez" wrote:
> >
> >> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> >> > i read through the documentatation, but its not clear to me if 2 RS 05
> >> > standard ed on one machine can share the same database server but
> >> > separate
> >> > databases. Unless i'm misunderstading it... any ideas?
> >> >
> >> > "EMartinez" wrote:
> >> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> >> > > > Can i have multiple Report Server 05 instances on one machine
> >> > > > conneting to
> >> > > > one remote Sql Database instance. Each Report Server will have its
> >> > > > own
> >> > > > database in the SQL instance
> >> >
> >> > > This link should be helpful.
> >> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
> >> >
> >> > > Regards,
> >> >
> >> > > Enrique Martinez
> >> > > Sr. Software Consultant
> >>
> >>
> >> As far as I know, this should be fine as long as your virtual
> >> directories for Reports and ReportServer are physically separate for
> >> both SQL instances. Also, you will want to make sure that you have at
> >> least one default SQL Server Instance and one named SQL Server
> >> Instance local to SSRS. As long as your SQL Servers are not mixed
> >> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||Did you use different databases for the second install of RS?
Have you checked to see what the default website is? Could it have changed
to the second installation of RS?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:7CA5523E-B6D7-4524-A3C2-DA30939B3F2B@.microsoft.com...
> Thank you for clarifying...so here's my issue.
> I installed a default instance of Report Services Std on a Non-Default
> Website with Sql Server on a remote machine. I implemented custom
> security.
> No problems so far. I am able to log-in using Forms Auth and add a Folder.
> Then I would install a named instance on the same webserver. Without even
> touching it after install (choosing to configure later), the DEFAULT
> instance
> returns a "Bad Request" error when trying to log in. I planned to set up
> the
> named instance on another Website.
> Any Ideas?
> "Bruce L-C [MVP]" wrote:
>> They need their own databases, not their own instance. RS 2005 should
>> have
>> no problem with this at all.
>> From the article that Enrique provided a link to:"You can install
>> multiple
>> instances of Reporting Services on the same computer. Installing multiple
>> instances is useful if you want to host different content for specific
>> sites. Each instance will have its own report server database,
>> configuration
>> files, and virtual directories. "
>> Note it says report server database NOT instance.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
>> news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
>> > So am I understanding you correctly that each RS (standard ed) instance
>> > will
>> > need to have their own SQL Server Database Instance?
>> >
>> > Here is what my environment looks like:
>> > WebServer has RSSite1(default) AND RSSite2(Named)
>> > DatabaseServer has one Instance Of Sql Server called DBServ
>> > DBServ has four databases:
>> > RSSite1_ReportServer
>> > RSSite1_ReportServerTempDb
>> > RSSite2_ReportServer
>> > RSSite2_ReportServerTempDb
>> >
>> > I'm doing this right now, but i'm running into some issues. Just want
>> > to
>> > know if this is supported so i can stop troubleshooting this.
>> >
>> > Thanks
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
>> >> > i read through the documentatation, but its not clear to me if 2 RS
>> >> > 05
>> >> > standard ed on one machine can share the same database server but
>> >> > separate
>> >> > databases. Unless i'm misunderstading it... any ideas?
>> >> >
>> >> > "EMartinez" wrote:
>> >> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com>
>> >> > > wrote:
>> >> > > > Can i have multiple Report Server 05 instances on one machine
>> >> > > > conneting to
>> >> > > > one remote Sql Database instance. Each Report Server will have
>> >> > > > its
>> >> > > > own
>> >> > > > database in the SQL instance
>> >> >
>> >> > > This link should be helpful.
>> >> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
>> >> >
>> >> > > Regards,
>> >> >
>> >> > > Enrique Martinez
>> >> > > Sr. Software Consultant
>> >>
>> >>
>> >> As far as I know, this should be fine as long as your virtual
>> >> directories for Reports and ReportServer are physically separate for
>> >> both SQL instances. Also, you will want to make sure that you have at
>> >> least one default SQL Server Instance and one named SQL Server
>> >> Instance local to SSRS. As long as your SQL Servers are not mixed
>> >> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||i was able to figure out my problem...i used the custom security extention
sample and during login it was returning the wrong url from one of the
methods (using WMI for some reason to get the url). i just overrid that and
got the correct value from web.config. all is well. thank you for taking the
time to help me.
"Bruce L-C [MVP]" wrote:
> Did you use different databases for the second install of RS?
> Have you checked to see what the default website is? Could it have changed
> to the second installation of RS?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
> news:7CA5523E-B6D7-4524-A3C2-DA30939B3F2B@.microsoft.com...
> > Thank you for clarifying...so here's my issue.
> > I installed a default instance of Report Services Std on a Non-Default
> > Website with Sql Server on a remote machine. I implemented custom
> > security.
> > No problems so far. I am able to log-in using Forms Auth and add a Folder.
> >
> > Then I would install a named instance on the same webserver. Without even
> > touching it after install (choosing to configure later), the DEFAULT
> > instance
> > returns a "Bad Request" error when trying to log in. I planned to set up
> > the
> > named instance on another Website.
> >
> > Any Ideas?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> They need their own databases, not their own instance. RS 2005 should
> >> have
> >> no problem with this at all.
> >> From the article that Enrique provided a link to:"You can install
> >> multiple
> >> instances of Reporting Services on the same computer. Installing multiple
> >> instances is useful if you want to host different content for specific
> >> sites. Each instance will have its own report server database,
> >> configuration
> >> files, and virtual directories. "
> >>
> >> Note it says report server database NOT instance.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Marvin" <Marvin@.discussions.microsoft.com> wrote in message
> >> news:06567929-6302-4508-8B96-80D7A02B439E@.microsoft.com...
> >> > So am I understanding you correctly that each RS (standard ed) instance
> >> > will
> >> > need to have their own SQL Server Database Instance?
> >> >
> >> > Here is what my environment looks like:
> >> > WebServer has RSSite1(default) AND RSSite2(Named)
> >> > DatabaseServer has one Instance Of Sql Server called DBServ
> >> > DBServ has four databases:
> >> > RSSite1_ReportServer
> >> > RSSite1_ReportServerTempDb
> >> > RSSite2_ReportServer
> >> > RSSite2_ReportServerTempDb
> >> >
> >> > I'm doing this right now, but i'm running into some issues. Just want
> >> > to
> >> > know if this is supported so i can stop troubleshooting this.
> >> >
> >> > Thanks
> >> >
> >> > "EMartinez" wrote:
> >> >
> >> >> On Aug 16, 7:45 pm, Marvin <Mar...@.discussions.microsoft.com> wrote:
> >> >> > i read through the documentatation, but its not clear to me if 2 RS
> >> >> > 05
> >> >> > standard ed on one machine can share the same database server but
> >> >> > separate
> >> >> > databases. Unless i'm misunderstading it... any ideas?
> >> >> >
> >> >> > "EMartinez" wrote:
> >> >> > > On Aug 14, 12:58 pm, Marvin <Mar...@.discussions.microsoft.com>
> >> >> > > wrote:
> >> >> > > > Can i have multiple Report Server 05 instances on one machine
> >> >> > > > conneting to
> >> >> > > > one remote Sql Database instance. Each Report Server will have
> >> >> > > > its
> >> >> > > > own
> >> >> > > > database in the SQL instance
> >> >> >
> >> >> > > This link should be helpful.
> >> >> > >http://technet.microsoft.com/en-us/library/ms403426.aspx
> >> >> >
> >> >> > > Regards,
> >> >> >
> >> >> > > Enrique Martinez
> >> >> > > Sr. Software Consultant
> >> >>
> >> >>
> >> >> As far as I know, this should be fine as long as your virtual
> >> >> directories for Reports and ReportServer are physically separate for
> >> >> both SQL instances. Also, you will want to make sure that you have at
> >> >> least one default SQL Server Instance and one named SQL Server
> >> >> Instance local to SSRS. As long as your SQL Servers are not mixed
> >> >> (i.e., one SQL Server 2000 and one SQL Server 2005), it should be ok.
> >> >>
> >> >> Regards,
> >> >>
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant
> >> >>
> >> >>
> >>
> >>
> >>
>
>