Strange database problem

Hello,

At work we have various servers, therefore we have various DTS Interfaces
which transfer data between them.

However when trying to save a new View to one server which gets its data
from another server nothing happens, I get an hourglass and nothing else.
Then I have to exit out of SQL Enterprise Manager and then no-one can access
the database on that server, so we had to restore from backup!

The query to create the view resides on server A
The query is getting its data from server B
But the query does need to reside on server A for other reasons.

What does this sound like to you?
I am suspecting connection problems between server A and B but the query
runs, its only when I save the view that this happens.

I am puzzled.

Thanks,

Jayne

Little PussyCat (SpamSpam@spam.com)  writes:
> At work we have various servers, therefore we have various DTS Interfaces
> which transfer data between them.
>
> However when trying to save a new View to one server which gets its data
> from another server nothing happens, I get an hourglass and nothing
> else. Then I have to exit out of SQL Enterprise Manager and then no-one
> can access the database on that server, so we had to restore from
> backup!
>
> The query to create the view resides on server A
> The query is getting its data from server B
> But the query does need to reside on server A for other reasons.
>
> What does this sound like to you?
> I am suspecting connection problems between server A and B but the query
> runs, its only when I save the view that this happens.

There is very little information to work from, but I doubt that you really
had to restore the database from a backup. Most likely there was some
blocking problem.

But I can give one tip: stay out of Enterprise Manager. Use Query Analyzer
to submit queries, and that also includes creating views. With QA you know
that the commands you type are those that are sent to the server. And if
there are any messages from SQL Server you will be told about it.

EM, on the other hand, may submit extra commands, and may understand
all error messages that come in.

When you create a view that accesses a remote table on a linked server,
SQL Server accesses the other server to check that columns etc exist.
You will need to have DTC running. If the definition just hangs, you
should check for blocking on the remote server.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp