Crystal reports problem

I also posted this to the CR newsgroup, but I think it might be an MSDE
problem also.
We recently moved several Access databases to an instance of MSDE. We use
VB .Net for program development. Upon running one of the programs a dialog
box now pops up alerting us to enter a user name and password. I searched
the Business Solutions help and found that the database server needs to be
set up with a trusted connection. I thought I already did this through the
data connection of the VS IDE.
Is there something more than I have to do on the MSDE side?
Thanks for any useful information.
Brad
hi Brad,
"Brad" <ballison@.ukcdogs.com> ha scritto nel messaggio
news:eRE7fiSkEHA.3612@.TK2MSFTNGP12.phx.gbl...
> I also posted this to the CR newsgroup, but I think it might be an MSDE
> problem also.
> We recently moved several Access databases to an instance of MSDE. We
use
> VB .Net for program development. Upon running one of the programs a
dialog
> box now pops up alerting us to enter a user name and password. I searched
> the Business Solutions help and found that the database server needs to be
> set up with a trusted connection. I thought I already did this through
the
> data connection of the VS IDE.
> Is there something more than I have to do on the MSDE side?
> Thanks for any useful information.
MSDE is usually installed allowing *only* trusted connections, and you have
to force the parameter SECURITYMODE=SQL at install time in order to allow
SQL Server authenticated connection..
chek your registry at
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
or
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
at the
LoginMode key
a value of 2 will allow both, where 1 only allows trusted connections
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thank you for the response, however in researching this problem I did see a
knowleddge base article about changing the key to 2 which I already did and
I still am having the same problem.
Thanks,
Brad
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2prjfcFo4pukU1@.uni-berlin.de...
> hi Brad,
> "Brad" <ballison@.ukcdogs.com> ha scritto nel messaggio
> news:eRE7fiSkEHA.3612@.TK2MSFTNGP12.phx.gbl...
> use
> dialog
> the
> MSDE is usually installed allowing *only* trusted connections, and you
> have
> to force the parameter SECURITYMODE=SQL at install time in order to allow
> SQL Server authenticated connection..
> chek your registry at
> HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
> or
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
> at the
> LoginMode key
> a value of 2 will allow both, where 1 only allows trusted connections
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

cross server update problem

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server “A”.

Another instance of SQL Server 2000 was moved from Windows 2000 Server “B” old to Windows 2003 Server “B” new, by restoring a backed up copy from old to new.

The system has cross server updates, where Server “A” is updating records in very large tables on Server “B”.

After the move from Server “B” old to Server “B” new, suddenly the cross server updates fail, but only when they are conducted within a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no longer have them run in a batch.

Also, I had a cross server update, having “A” update records in “B”, from a delete trigger residing on “A” table.Deletes against the “A” table fail so long as the trigger remains.Once I remove the delete trigger from the “A” table, I can now delete records on the “A” table.

Thanks. Dotnet Fellow

Have you checked to make sure that you have a linked server set up on the machines, When moveing you might not have moved the config for the linked server.

|||

First step is to make sure that the Distributed Transaction Coordinator service is started on both servers.

Next, if they are, can you run a statement in QA and send the error message?

|||

DTC is running on both computers. There is no error message returned. Just hangs for hours until a timeout is encountered. Looking at it through Profiler Trace it appears that no activity is occurring on either server once the transaction is begun. Or, in the case of the delete triggers, it appears that everything comes to a complete standstill once the delete statement is executed. Again, once I remove the begin transaction, or once I remove the trigger, things move along normally.

Hey Davidson, what happened to Chuck Hawkins?

|||

All servers involved, old and new, have a default collation of SQL_Latin1_General_CP1_CI_AS.

My Cross Server update that I execute on Server "A" is below, where

@.p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@.p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'

Again, it doesn't work when I uncomment the BEGIN TRANSACTION

/*
Update the source database, ensure that the archived flag is accurately marked as 'N' if the record does not yet exist in Opus.
*/

SELECT @.l_str_update = ' UPDATE ' + @.p_str_source_workspace + 'pr_history SET archived = ''N'' FROM ' + @.p_str_source_workspace + 'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN (SELECT pr_hist_search FROM ' + @.p_str_destination_workspace + 'pr_history)'

--BEGIN TRANSACTION

EXEC (@.l_str_update)

Thanks for keeping me in mind Erland.

|||There are new network DTC security "features" in Windows 2003 Server. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/2627a956-60b3-4d26-bc04-e0676ec97786.asp

This might you get the specific settings right, if this is indeed the problem: http://www.prophecie.co.uk/Default.aspx?dc=200509

Steve Kass
Drew University
http://www.stevekass.com

cross server update problem

The system has worked for two years.
SQL Server 2000 runs on Windows 2000 Server "A".
Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.
The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".
After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.
The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.
Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.
Thanks. Dotnet Fellowdotnetfellow@.yahoo.com wrote:
> The system has worked for two years.
> SQL Server 2000 runs on Windows 2000 Server "A".
> Another instance of SQL Server 2000 was moved from Windows 2000 Server
> "B" old to Windows 2003 Server "B" new, by restoring a backed
> up copy from old to new.
> The system has cross server updates, where Server "A" is updating
> records in very large tables on Server "B".
> After the move from Server "B" old to Server "B" new, suddenly
> the cross server updates fail, but only when they are conducted within
> a Begin and Commit Transaction batch.
> The system works when I remove the Begin and Commit Transaction, and no
> longer have them run in a batch.
> Also, I had a cross server update, having "A" update records in
> "B", from a delete trigger residing on "A" table. Deletes
> against the "A" table fail so long as the trigger remains. Once I
> remove the delete trigger from the "A" table, I can now delete
> records on the "A" table.
> Thanks. Dotnet Fellow
>
Transactions that involve multiple servers are called "distributed"
transactions, and rely on the MSDTC service to function. On Windows
2003 Server, MSDTC refuses network connections by default. Start here:
http://support.microsoft.com/kb/899191/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||DTC is running on both computers. There is no error message returned.
Just hangs for hours until a timeout is encountered. Looking at it
through Profiler Trace it appears that no activity is occurring on
either server once the transaction is begun. Or, in the case of the
delete triggers, it appears that everything comes to a complete
standstill once the delete statement is executed. Again, once I remove
the begin transaction, or once I remove the trigger, things move along
normally.|||All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.
My Cross Server update that I execute on Server "A" is below, where
@.p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@.p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'
--
/*
Update the source database, ensure that the archived flag is accurately
marked as 'N' if the record does not yet exist in Opus.
*/
SELECT @.l_str_update = ' UPDATE ' + @.p_str_source_workspace +
'pr_history SET archived = ''N'' FROM ' + @.p_str_source_workspace +
'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN
(SELECT pr_hist_search FROM ' + @.p_str_destination_workspace +
'pr_history)'
--BEGIN TRANSACTION
EXEC (@.l_str_update)
--

cross server update problem

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server “A”.

Another instance of SQL Server 2000 was moved from Windows 2000 Server “B” old to Windows 2003 Server “B” new, by restoring a backed up copy from old to new.

The system has cross server updates, where Server “A” is updating records in very large tables on Server “B”.

After the move from Server “B” old to Server “B” new, suddenly the cross server updates fail, but only when they are conducted within a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no longer have them run in a batch.

Also, I had a cross server update, having “A” update records in “B”, from a delete trigger residing on “A” table.Deletes against the “A” table fail so long as the trigger remains.Once I remove the delete trigger from the “A” table, I can now delete records on the “A” table.

Thanks. Dotnet Fellow

Have you checked to make sure that you have a linked server set up on the machines, When moveing you might not have moved the config for the linked server.

|||

First step is to make sure that the Distributed Transaction Coordinator service is started on both servers.

Next, if they are, can you run a statement in QA and send the error message?

|||

DTC is running on both computers. There is no error message returned. Just hangs for hours until a timeout is encountered. Looking at it through Profiler Trace it appears that no activity is occurring on either server once the transaction is begun. Or, in the case of the delete triggers, it appears that everything comes to a complete standstill once the delete statement is executed. Again, once I remove the begin transaction, or once I remove the trigger, things move along normally.

Hey Davidson, what happened to Chuck Hawkins?

|||

All servers involved, old and new, have a default collation of SQL_Latin1_General_CP1_CI_AS.

My Cross Server update that I execute on Server "A" is below, where

@.p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@.p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'

Again, it doesn't work when I uncomment the BEGIN TRANSACTION

/*
Update the source database, ensure that the archived flag is accurately marked as 'N' if the record does not yet exist in Opus.
*/

SELECT @.l_str_update = ' UPDATE ' + @.p_str_source_workspace + 'pr_history SET archived = ''N'' FROM ' + @.p_str_source_workspace + 'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN (SELECT pr_hist_search FROM ' + @.p_str_destination_workspace + 'pr_history)'

--BEGIN TRANSACTION

EXEC (@.l_str_update)

Thanks for keeping me in mind Erland.

|||There are new network DTC security "features" in Windows 2003 Server. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/2627a956-60b3-4d26-bc04-e0676ec97786.asp

This might you get the specific settings right, if this is indeed the problem: http://www.prophecie.co.uk/Default.aspx?dc=200509

Steve Kass
Drew University
http://www.stevekass.com

cross server update problem

The system has worked for two years.
SQL Server 2000 runs on Windows 2000 Server "A".
Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.
The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".
After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.
The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.
Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.
Thanks. Dotnet Fellowdotnetfellow@.yahoo.com wrote:
> The system has worked for two years.
> SQL Server 2000 runs on Windows 2000 Server "A".
> Another instance of SQL Server 2000 was moved from Windows 2000 Server
> "B" old to Windows 2003 Server "B" new, by restoring a backed
> up copy from old to new.
> The system has cross server updates, where Server "A" is updating
> records in very large tables on Server "B".
> After the move from Server "B" old to Server "B" new, suddenly
> the cross server updates fail, but only when they are conducted within
> a Begin and Commit Transaction batch.
> The system works when I remove the Begin and Commit Transaction, and no
> longer have them run in a batch.
> Also, I had a cross server update, having "A" update records in
> "B", from a delete trigger residing on "A" table. Deletes
> against the "A" table fail so long as the trigger remains. Once I
> remove the delete trigger from the "A" table, I can now delete
> records on the "A" table.
> Thanks. Dotnet Fellow
>
Transactions that involve multiple servers are called "distributed"
transactions, and rely on the MSDTC service to function. On Windows
2003 Server, MSDTC refuses network connections by default. Start here:
http://support.microsoft.com/kb/899191/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||DTC is running on both computers. There is no error message returned.
Just hangs for hours until a timeout is encountered. Looking at it
through Profiler Trace it appears that no activity is occurring on
either server once the transaction is begun. Or, in the case of the
delete triggers, it appears that everything comes to a complete
standstill once the delete statement is executed. Again, once I remove
the begin transaction, or once I remove the trigger, things move along
normally.|||All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.
My Cross Server update that I execute on Server "A" is below, where
@.p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@.p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'
/*
Update the source database, ensure that the archived flag is accurately
marked as 'N' if the record does not yet exist in Opus.
*/
SELECT @.l_str_update = ' UPDATE ' + @.p_str_source_workspace +
'pr_history SET archived = ''N'' FROM ' + @.p_str_source_workspace +
'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN
(SELECT pr_hist_search FROM ' + @.p_str_destination_workspace +
'pr_history)'
--BEGIN TRANSACTION
EXEC (@.l_str_update)

cross server update problem

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server "A".

Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.

The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".

After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.

Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.

Thanks. Dotnet Fellow(dotnetfellow@.yahoo.com) writes:

Quote:

Originally Posted by

The system has worked for two years.
>
SQL Server 2000 runs on Windows 2000 Server "A".
>
Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.
>
The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".
>
After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.
>
The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.

Any error message you could share with us?

Since it works without a transaction, it sounds as if DTC is not running
on the new server B.

Quote:

Originally Posted by

Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.

A trigger always operates in a transaction defined by the statement that
fired it, so this is consistent with the above.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||DTC is running on both computers.|||There is no error message returned. Just hangs for hours until a
timeout is encountered. Looking at it through Profiler Trace it
appears that no activity is occurring on either server once the
transaction is begun. Or, in the case of the delete triggers, it
appears that everything comes to a complete standstill once the delete
statement is executed.|||(dotnetfellow@.yahoo.com) writes:

Quote:

Originally Posted by

There is no error message returned. Just hangs for hours until a
timeout is encountered. Looking at it through Profiler Trace it
appears that no activity is occurring on either server once the
transaction is begun. Or, in the case of the delete triggers, it
appears that everything comes to a complete standstill once the delete
statement is executed.

Let me guess: the source server runs Windows 2000, and the target server
runs Windows 2003? Where as the server you replaced ran Windows 2000?

No, I don't know anything. But I think I've seen this scenario at work. As
that has only been with development and test servers I have not dug into it.

But chance had it that one of our customer ran into precisely this
problem today. They have a brand-new server (which I assume runs Win 2003),
and in one place our gateway database that runs on another server (which
I suspect runs Win 2000) tries to use INSERT-EXEC to the linked server.
Worked find Friday. Today it just hung. SQL 2000 on both machines.

We will probably open a case with Microsoft on this. I suspect it's one
of these RTFM things, but I just don't know which manual. My gut feeling
is that this is a DTC problem. Maybe as simple, as opening the correct
port in Windows firewall. Whatever, I believe it's more a Windows problem
than an SQL Server problem per se.

If we open a case, and I hear about the resolution, I'll try to post
back.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.

My Cross Server update that I execute on Server "A" is below, where

@.p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@.p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'

Again, it doesn't work when I uncomment the BEGIN TRANSACTION

---

/*
Update the source database, ensure that the archived flag is accurately
marked as 'N' if the record does not yet exist in Opus.
*/

SELECT @.l_str_update = ' UPDATE ' + @.p_str_source_workspace +
'pr_history SET archived = ''N'' FROM ' + @.p_str_source_workspace +
'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN
(SELECT pr_hist_search FROM ' + @.p_str_destination_workspace +
'pr_history)'

--BEGIN TRANSACTION

EXEC (@.l_str_update)

---

Thanks for keeping me in mind Erland.|||(dotnetfellow@.yahoo.com) writes:

Quote:

Originally Posted by

All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.

I would not expect collations to have anything to do with it. In fact, as I
said, my feeling is that it is not really an SQL Server issue, but a Windows
issue.

You did not confirm my guesses about the operating system. By the way,
when you replaced B, did you give the same IP address to the new B
server? There is another potential source for problem, some reference
to the old machine lingering around.

I spoke to my colleague who has been in touch with Microsoft. Apparently
they did not just say "did you check that...", but instead they asked tim
to send logs and similar information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is an update:

In our case, it turned out that the machine from which we made the
connection is running SQL 2000 SP2 and Windows 2000 SP2. The Microsoft
engineer suggested that the SQL Server should be upgraded to SQL 2000 SP4
and Windows 2000 SP4. Whether this alone will resolve the issue, I don't
know yet. The simple reason the Support Professional wanted us to upgrade
is that they don't support neither SQL 2000 SP2 nor Win 2000 SP2.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It seems that we now have a resolution. As I suspected it's a DTC issue.
Windows 2003 adds improved security which gets in the way.

On the server you are connecting to, open Component Services and select
properties for My Computer. Go to the MS DTC tab. To the lower left, there
is a button Security Configuration. This opens a new dialogue. In this
dialogue, enable 1) Network DTC Access. 2) Allow Inbound. 3) No
Authentication Required.

Really what the implication of these settings are from the security side
point of view, I don't know.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This is some advice that we got from the support professional on our case:

Windows 2003 R2 has new DTC protection measures built-in and enabled
by default, since MSDTC is used for SQL Server purposes and this
machines don't have internet access there are no problem in set no
authentication required. In future if you upgrade your Windows 2000
machine to Windows 2003 SP1 or Windows 2003 R2 and they are in same
domain (this is a requirement) you can activate mutual authentication
in MSDTC and will not have any problem.

These steps are not documented but I let you some recommendations to
troubleshooting MSDTC problems.

1) Put security setting at the minimum level

2) Enable MSDTC logging and verify if anything appears on log file

3) Use DTCPing utility to avoid firewall problems

4) Verify if RPC is started before MSDTC and SQL Server is started
after MSDTC and RPC services.

5) Verify in SQL Server error log that MSDTC started with success

6) Please verify if you have COM/COM+ components enabled and with all
hot fixes installed

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cross instance / server joins

We are implementing two production instances of SQL Server standard
edition so we can separate GIS data from non-GIS data. Is it possible
to do joins between instances or even servers? If yes can someone
point me to a document on how to set it up?
Thank you,
Randy
wawork@.hotmail.com
Yes, you can via linked servers. Be prepared for poor performance, however.
Databases that interact with each other frequently should be in the same
instance.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Randy K" <wawork@.hotmail.com> wrote in message
news:431dbdc5.2953031@.msnews.microsoft.com...
We are implementing two production instances of SQL Server standard
edition so we can separate GIS data from non-GIS data. Is it possible
to do joins between instances or even servers? If yes can someone
point me to a document on how to set it up?
Thank you,
Randy
wawork@.hotmail.com

Cross instance / server joins

We are implementing two production instances of SQL Server standard
edition so we can separate GIS data from non-GIS data. Is it possible
to do joins between instances or even servers? If yes can someone
point me to a document on how to set it up?
Thank you,
Randy
wawork@.hotmail.comYes, you can via linked servers. Be prepared for poor performance, however.
Databases that interact with each other frequently should be in the same
instance.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Randy K" <wawork@.hotmail.com> wrote in message
news:431dbdc5.2953031@.msnews.microsoft.com...
We are implementing two production instances of SQL Server standard
edition so we can separate GIS data from non-GIS data. Is it possible
to do joins between instances or even servers? If yes can someone
point me to a document on how to set it up?
Thank you,
Randy
wawork@.hotmail.com

Cross instance / server joins

We are implementing two production instances of SQL Server standard
edition so we can separate GIS data from non-GIS data. Is it possible
to do joins between instances or even servers? If yes can someone
point me to a document on how to set it up?
Thank you,
Randy
wawork@.hotmail.comYes, you can via linked servers. Be prepared for poor performance, however.
Databases that interact with each other frequently should be in the same
instance.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Randy K" <wawork@.hotmail.com> wrote in message
news:431dbdc5.2953031@.msnews.microsoft.com...
We are implementing two production instances of SQL Server standard
edition so we can separate GIS data from non-GIS data. Is it possible
to do joins between instances or even servers? If yes can someone
point me to a document on how to set it up?
Thank you,
Randy
wawork@.hotmail.com

Cross DB dialog security issues. Was: Can't Route to another LOCAL Broker Instance

Hi Remus,

I am experiencing the same problem, and I can't get the easy fix to work. I drop and create the DB's in between tests, so it is not related to having an old certificate in the DB, as in the case of Tilfried.

The situation is as follows:

DB1 owned by login1, has a user for login2; this DB is for the initiator

DB2 owned by login2, has a user for login1; this DB hosts the target

Both DB's have TRUSTWORTHY flag set to ON

Error in sys.transmission_queue: 'Error 916, State 3: The server principal "Login1" is not able to access the database "DB2" under the current security context.

Going on a limp, I decide to add a remote service binding in DB1, binding the user for Login2 to the target service, even though BOL explicitly states that this is only required for cross-server communications. This does change the situation - I still get an error, but a new message is sys.transmission_queue: "Dialog security is unavailable for this covnersation because there is no certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation." I already know that the first option works, but I wanted to get the simple solution running. As for the second option, I doublechecked and the initiating procedure DOES already specify ENCRYPTION = OFF in the BEGIN DIALOG CONVERSATION command. My theory is that the remote service binding somehow forces SB to use encryption, but (a) that is not stated in the error message, and (b) if so, then how to get the messages sent over to the target service without using the binding?

==> EDIT: Just saw that you confirmed this theory in your last reply to Tlifried. So I am indeed back to having to find out how to get this to work without remote service binding - it should be possible, but how?

BTW, SELECT @.@.VERSION shows that I'm on build 3054, in case it matters.

Between all the errors in BOL and less than helpfull error messages produced by SB, I feel like I'm slowly losing my sanity. Please help!

Best regards,

Hugo Kornelis

Hi Remus (or whoever is listening),

Just to make sure that I'm not looking in the wrong corner, I decided to make sure that the absence of certificates is the ONLY reason that the services won't talk to each other. So I made certificates for the dbo owner in each of the DBs, dumped them to file and used those files to create certificates for the corresponding users in the other DBs.

After setting up certificates like that, I still got the same error (Error 916, State 3) - but once I added a remote service binding, my dialog ran without further problems.

I still don't understand why a remote service binding is required even though BOL says (quote from the CREATE REMOTE SERVICE BINDING page): "A remote service binding is only necessary for initiating services that communicate with target services outside of the SQL Server instance." - but at least I am now certain that my problems with the version without certificates and with the trustworthy option set on are not related to any other errors in my setup.

Best, Hugo

|||

On the interaction between ENCRYPTION = ON/OFF and the presence/absence of a remote service binding see http://blogs.msdn.com/remusrusanu/archive/2006/07/07/659319.aspx. When a RSB is present, the login mapping between databases is discarded in favour of certificates. This is intentional, as a way to circumvent the problem of no wanting to enable TRUSTWORTHY bit (with all the instance level implications).

To map users trough logins, having TRUSTWORTHY ON is a required condition, but not suficcient. The complete story is detailed at http://msdn2.microsoft.com/en-us/library/ms188304.aspx. What you need is for DB2 to grant AUTHENTICATE permision to login1. TRUSTWORTHY bit is required only on DB1. This is nothing specific to Service Broker, using EXECUTE AS user = 'foo' in DB1 and then issue a USE DB2 statement would run into the same error.

I understand the errors are somehow cryptic and misterious, now the cat is out of the bag we hope to get better at next iteration. Tools would shorley help, give a shot to www.codeplex.com/slm

|||

Hugo Kornelis wrote:

I still don't understand why a remote service binding is required even though BOL says (quote from the CREATE REMOTE SERVICE BINDING page): "A remote service binding is only necessary for initiating services that communicate with target services outside of the SQL Server instance."

Remote service bindings are required for remote dialog security. However, altough not required, if present, they are honored for local dialogs.

|||

without reading the whole thread in detail, I found that the SQL Profiler lets you find problems within minutes, for which you would normally take hours or days of checking everything!

Trace shows messages and errors, you will never receive through SSB directly!