Distributed query

First question : what are the necessary step to allow remote access (linked
server) using Windows logging and no Active Directory available
I tried the following without success
EXEC sp_addlinkedsrvlogin 'sql2k5', 'false', 'Soline\myLogin',
'sql2k5\mylogin', 'remotepassword'
but when I map to sa remote server login it works fine, so my questions
A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able t
o
run distributed query. what I mean by that is there any good step by step
procedure to follow to implment this using min priviliges on both sides as o
n
Lcris blog regarding "Cross database access"Hi,
If you would like to use Windows login without AD to access linked servers,
the Windows Authentication is NTLM then.
It is required to create a same user with same password on each machine.
For your two questions:
> A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
If there is no AD, please manually create the Windows user with the same
password on both machine.

> B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able
to run distributed query.
> what I mean by that is there any good step by step procedure to follow to
implment this using min priviliges on both sides as on Lcris blog regarding
"Cross database access"
If you just want to query the database, the min requirements is that the
user corresponed to the login needs db_datareader permission.
First create a login on the remote server, double click the login, switch
to the User Mapping, map the login to a user of the database, and assign
db_datareader permission;
Then on your local server, execute the SPs as following:
sp_addlinkedserver 'mySqlServer-03'
sp_addlinkedsrvlogin 'mySqlServer-03',false,NULL,'s1','s1'
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

distributed queries in win 2003 and win 2000

Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison

distributed queries in win 2003 and win 2000

Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhi
Saradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison

distributed queries in win 2003 and win 2000

Hi there,
well i have 2 servers one on os (win 2000 adv server and
sql 2000)and other on (win 2003 and sql 2000)
when i run a distributed tran with begin tran
i get and error
as--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%
Server: Msg 7391, Level 16, State 1, Procedure
ValueLine_tbl_Insert_tr, Line 7
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction return 0x8004d00a
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%
has any one sol for this well i dont get any errors when
i am acessing the win 2000 server from another win 2000
server i get error only from win 2003 os server
thanks
saradhiSaradhi,
firstly, please can you confirm if MS DTC is started on your Win 2003
server?
Regards,
Paul Ibison

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";J et OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MD W";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
Massimo
After other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work
sql

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.

Distributed queries

Hi,
I am trying to execute the query
select CodRicerca AS codice, PrezzoEuro,ArticoloID,UnitaMisuraID from (
select * from dbo.vArticoliBaseAggiornamentiListini where PrecodiciID=3D
36966 and ArticoliCodificheTipoID=3D 1 and ListinoID=3D
1 ) as vArticoliBaseAggiornamentiListini INNER JOIN
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source=3D"W:\Clipparts\Bin\..\Data\\Listino.mdb";Jet OLEDB:System
database=3D"W:\Clipparts\Bin\..\Data\\CLIPUSERS.MDW";User
ID=3Daaaa;Password=3D123456789')...[listini] as ClipListino on
ClipListino.CodRicerca=3DArticoliCodificheArticolo COLLATE
database_default where ClipListino.IdLineaProdotto=3D 959 and
(ArticoliListiniDataVigore<CAST('2006-04-04 00:00:00' AS smalldatetime)
or PrezzoEuro<>ArticoliListiniPrezzo)
But sometimes I get the error
"OLE/DB provider returned message: Impossibile avviare l'applicazione.
Il file di informazioni sul gruppo di lavoro =E8 mancante o aperto in
modo esclusivo da un altro utente."
[The translation shoul be: OLE/DB provider returned message: Cannot
start your application.The workgroup information file is missing or
opened exclusively by another
user]
It does not happen always. I get the error only in some enviroment (but
I can non find under which ).
Sometime the same query works from query analyzer, but does not work if
embedded in an application running on a host other than that running
sqlserver.
thanks in advance for you help
MassimoAfter other trials, I found that the problem is on the accout connected
to the server. Some accounts are allowed to make distributed queries
but some other not. More specifically if I use the domain account the
query runs well, if I use SQLServer account it does not work