Distributed Queries

We are using MS SQL Server 2000 service pack 4.
On our production servers the following scenerio works, our LAN
department recently rebuilt one of our development servers and we are
having problems.
On our central development server I create a temp table CREATE TABLE
#Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
-- Inserting into the temp table on our central server from our DEV2
server works when called this way:
INSERT #temp
SELECT dur_code, tmc_code
FROM dev1.sms.dbo.term_cds
--This dynamic statement works from the central server to dev2 server
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
EXEC (@.vcCmd)
-- Inserting to the same temp table on the central server from the same
DEV2 server does not work when it is called dynamically.
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
INSERT #temp EXEC (@.vcCmd)
I am running this code through SQL Query Analyzer. This code works on
our central server when we call our other development server remotely
(and on all of our production servers). The Distributed Transaction
Coordinator is running on both servers.
Any ideas on what we have done wrong?Karen (karen.jones@.tusd1.org) writes:
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
What "does not work" mean? If there is an error message, please post it.
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|||CLARIFICATION:
We are inserting into a table on our central server and making a
dynamic call to the dev2 server.
Karen wrote:
> We are using MS SQL Server 2000 service pack 4.
> On our production servers the following scenerio works, our LAN
> department recently rebuilt one of our development servers and we are
> having problems.
> On our central development server I create a temp table CREATE TABLE
> #Temp(dur_code char(1) NOT NULL, tmc_code char(1) NOT NULL)
> -- Inserting into the temp table on our central server from our DEV2
> server works when called this way:
> INSERT #temp
> SELECT dur_code, tmc_code
> FROM dev1.sms.dbo.term_cds
>
> --This dynamic statement works from the central server to dev2 server
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> EXEC (@.vcCmd)
>
> -- Inserting to the same temp table on the central server from the same
> DEV2 server does not work when it is called dynamically.
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT dur_code, tmc_code FROM dev2.sms.dbo.term_cds'
> INSERT #temp EXEC (@.vcCmd)
> I am running this code through SQL Query Analyzer. This code works on
> our central server when we call our other development server remotely
> (and on all of our production servers). The Distributed Transaction
> Coordinator is running on both servers.
> Any ideas on what we have done wrong?|||CLARIFICATION:
There is no error... it just hangs and never returns. It will not even
let you cancel the query, you have to end Query Analyzer through task
manager.
Erland Sommarskog wrote:
> Karen (karen.jones@.tusd1.org) writes:
> What "does not work" mean? If there is an error message, please post it.
>
> --
> 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|||Karen,
Please help us out in clarifying a few points.
1. DEV1 = "Central Server"
2. DEV2 = "Development Server"
3. You have a temp table #Temp created on DEV1
4. INSERTS into #Temp succeed when executed on DEV1
5. INSERTS into #Temp FAIL when executed on DEV2
6. DEV2 does NOT have a #Temp table
#Temp tables are accessible only to the connection that created them. It is
logical that #Temp would be accessible and the INSERT would succeed when
executed from DEV1. And also that the INSERT would FAIL when you attempt to
insert into DEV1's #Temp table from code executed on DEV2.
Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
signs) This form of ##Temp table is accessible by all connections and stays
in scope as long as there is any single connection open.
If I have properly understood the issue, let me know. Also you might check
in BOL about #Temp / ##Temp tables and scope.
Regards,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Karen" <karen.jones@.tusd1.org> wrote in message
news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...
> CLARIFICATION:
> We are inserting into a table on our central server and making a
> dynamic call to the dev2 server.
>
> Karen wrote:
>|||I am running the query on the central server through Query Analyzer.
Lets call that DEV1. The data is being added to the temp table on DEV1
from a table on DEV2.
A: The temp table exists on DEV1 and I am executing the statement on
DEV1
INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
The above code works.
B:Using the same scenerio (A:) I execute the following code
DECLARE @.vcCmd varchar(255)
SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
INSERT #temp EXEC(@.vcCmd)
The above code does not work.
The same code as above (example B:) works on our production servers but
does not work on one (1) of our development servers, all other
development servers can run this code just fine.
Recently our LAN staff rebuilt the DEV2 server and this is when the
problem started.
Since example A: works we know it has nothing to do with the linked
server setup
and the DTC is running.
Arnie Rowland wrote:
> Karen,
> Please help us out in clarifying a few points.
> 1. DEV1 = "Central Server"
> 2. DEV2 = "Development Server"
> 3. You have a temp table #Temp created on DEV1
> 4. INSERTS into #Temp succeed when executed on DEV1
> 5. INSERTS into #Temp FAIL when executed on DEV2
> 6. DEV2 does NOT have a #Temp table
> #Temp tables are accessible only to the connection that created them. It i
s
> logical that #Temp would be accessible and the INSERT would succeed when
> executed from DEV1. And also that the INSERT would FAIL when you attempt t
o
> insert into DEV1's #Temp table from code executed on DEV2.
> Perhaps you need to investigate using a 'global' ##Temp table. (2 pound
> signs) This form of ##Temp table is accessible by all connections and stay
s
> in scope as long as there is any single connection open.
> If I have properly understood the issue, let me know. Also you might check
> in BOL about #Temp / ##Temp tables and scope.
> Regards,
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Karen" <karen.jones@.tusd1.org> wrote in message
> news:1151102931.892749.87140@.p79g2000cwp.googlegroups.com...|||Karen wrote:
> I am running the query on the central server through Query Analyzer.
> Lets call that DEV1. The data is being added to the temp table on DEV1
> from a table on DEV2.
> A: The temp table exists on DEV1 and I am executing the statement on
> DEV1
> INSERT #temp EXEC ('SELECT x, y FROM DEV2.sms.dbo.table')
> The above code works.
> B:Using the same scenerio (A:) I execute the following code
> DECLARE @.vcCmd varchar(255)
> SET @.vcCmd = 'SELECT x, y, FROM DEV2.sms.dbo.table'
> INSERT #temp EXEC(@.vcCmd)
> The above code does not work.
> The same code as above (example B:) works on our production servers but
> does not work on one (1) of our development servers, all other
> development servers can run this code just fine.
> Recently our LAN staff rebuilt the DEV2 server and this is when the
> problem started.
> Since example A: works we know it has nothing to do with the linked
> server setup
> and the DTC is running.
>
Have you looked at sysprocesses on each server while this statement is
executing? Look there for any blocking, and also see what waittype is
specified for the spid that is running your statement.
Also look at perfmon on each machine, look at CPU and Average Disk Queue
length to see if either machine is CPU or I/O bound.|||No one is using these servers except myself. No other processes are
running on either server. The table I am accessing has 10 rows. I
believe this is a configuration problem.
Tracy McKibben wrote:
> Karen wrote:
> Have you looked at sysprocesses on each server while this statement is
> executing? Look there for any blocking, and also see what waittype is
> specified for the spid that is running your statement.
> Also look at perfmon on each machine, look at CPU and Average Disk Queue
> length to see if either machine is CPU or I/O bound.|||Karen (karen.jones@.tusd1.org) writes:
> CLARIFICATION:
> There is no error... it just hangs and never returns. It will not even
> let you cancel the query, you have to end Query Analyzer through task
> manager.
Ah, this behaviour. I've have one such situation at work as well. Alas,
I don't have a solution, as I've never cared about getting it fixed.
The only difference to your description is that for me it suffices to
to:
CREATE TABLE #dates (d datetime)
go
INSERT #dates (d)
EXEC ('SELECT * FROM NEMO.sec_93_stb.dbo.dates')
I don't have to put the SQL command in a variable. (And that should not
matter.)
What I have observed is that if I look in sysprocesses on NEMO, the
machice I connect to, there is an entry for a process with host name
JAMIE4K, the machine I connect from. So my conclusion is that the problem
occurs when NEMO tries to connect back to JAMIE4K, but never get a
response. As I've said, I have not dug into this, but our symin people
are very fond of running firewalls about everywhere, and they also lock
down the service accounts for SQL Server.
It is also worth noting that NEMO, the machine I connect to, runs
Windows 2003, whereas JAMIE4K where I connect from runs Windows 2000.
I guess you will have to talk with your LAN people, and ask them to
review what they did with the network.
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|||Karen wrote:
> No one is using these servers except myself. No other processes are
> running on either server. The table I am accessing has 10 rows. I
> believe this is a configuration problem.
>
It very well could be a configuration problem, and looking at
sysprocesses to see what waittype, if any, the process is reporting, you
might well be able to figure out what is misconfigured. Perfmon might
also reveal some important clues. Suggestion is there, use it as you wish.