Distributed Query Performance Is ID-dependent?
Jan 26, 2007
We have an interesting performance issue with a distributed query. When run by a system-administrator account, the remote computer returns the requested row set. It does the same thing for small row counts (<=7) for user accounts. On larger row counts, however, the user account returns the ENTIRE rowset from the remote table, and performs the restrict operation locally. Performance-wise, this results in a difference between 11 seconds and 12 MINUTES. I'm assuming it's some sort of security issue in DTC, but the remote server is on Windows Server 2000, and there's no security button for DTC under Component Services.
Suggestions?
The query in question is:
EXEC ("INSERT INTO #XML (vin, ws_xml, lang_id)
SELECT slo.vin,
br.xml,
br.lang_id
FROM #SALE_LINEUP_ORDER slo
INNER JOIN RemoteServer.mydatabase.dbo.build_record br ON slo.vin = br.vin
INNER JOIN #LANGUAGE_IDS li ON br.lang_id = li.lang_id")
View 4 Replies
ADVERTISEMENT
Oct 13, 2014
I am trying to do a select query like below...
SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM,
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR
But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.
how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.
View 3 Replies
View Related
Aug 7, 2007
Hi,
We are doing a POC for transferring a huge number of messages(millions) from oner machine to another. The two approaches we are examining are MSMQ and SQL Broker. The MSMQ is set up as a remote queue on the target machine, and the source machine takes as little as 1 millisecond to send the message (using a .NET program). However, when testing on Service Broker, we find that the time taken to send message to the queue is significantly higher - like 70 millisecond. Could you please help us in understanding why this is happening?
The service broker distributed queues have been set up as per the directions in the posting at http://www.sqlservercentral.com/columnists/sindukuri/2797.asp
The source program (written in .NET) is calling a stored procedure in the source machine to write to the SSB queue. When we run SQL Trace, we find that the SP is responsible for 99% of the time taken. Here is our SP that send the message:
Declare @ConversationHandle uniqueidentifier
Begin Dialog @ConversationHandle
From Service SenderService
To Service 'ReceiverService'
On Contract SampleContract
WITH Encryption=off;
SEND
ON CONVERSATION @ConversationHandle
Message Type SenderMessageType
(<<XML String>>)
Please let us know if there are any additional settings required in the Service Broker to improve its performance. Or , what are the other approaches for building a distributed SSB application?
View 3 Replies
View Related
Mar 28, 2001
Hi Folks,
Is there anyway of running a Store Procedure in Database A that's going against database B? I have a store procedure that does a select on table A in database A and a select on table B in database B.
The problem is that the user have exec rights to the store procedure, which is an object in database A. The store procedure won't run because of permission rights to table B in database B.
Is there anyway of queryiny table B without giving the user select rights to that table? Anyone out there had the same problem?
thank you
Joe R.
View 1 Replies
View Related
Feb 4, 2004
Hi,
I am trying to execute a proc on Linked Server. Now as the Linked server name starts with 2, all of the following fails...
Any help to make it work highly appreciated...
exec [2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail]
exec 2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail
exec "2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail"
View 4 Replies
View Related
Mar 25, 2002
Hi Guys.
Many posted this message and no one anwered. i am facing the same problem now.
Got the error message
Server: Msg 7391, Level 16, State 1, Line 1
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. ]
My MSDTC is on. I had setup linked server using OLEDB.
Microsoft did'nt give enough info or solution for this. ANybody faced this prob and solved it?
Any suggestions , comments, solutions?
-MAK
View 1 Replies
View Related
Apr 17, 2001
I have a procedure where it runs a procedure on another server and returns the results to the calling procedure and dumps it into a temp table..
I get the following message:
-----
Server: Msg 7391, Level 16, State 1, Procedure proc1, Line 60
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.
[OLE/DB provider returned message: Distributed transaction error]
----
But both the servers are running the distributed transaction corordinator
example:
create procedure dbo.proc1
@param1 int
as
create table #temp
(col1 int
col2 varchar(255)
)
insert into #temp
EXEC server.database.dbo.proc2 @param1 = @param1
go
View 4 Replies
View Related
Mar 15, 2001
I am trying to set up linked servers between several SQL 7.0 servers, but everytime I try, I get the message:
Error 18456: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'
Only thing is, I thought I was logged into both servers using my NT name. Any ideas?
View 1 Replies
View Related
May 11, 2000
Can anyone tell me why Query B (see below) works but Query A does not? When Query A is run the following error is received. I would love to know why using a subquery allows Query B to run.
Server: Msg 8623, Level 16, State 2, Line 1
Internal Query Processor Error: The query processor could not produce a query
plan.
Query A.
SELECT pt.Description,
pa.Method_Order,
os.LogAction,
Sum(pa.Amount) Total
FROM BO_PaymentAmountsApplied pa,
BO_OrderStatusLog os,
Members.members.dbo.MBR_PaymentTypes pt
WHEREpa.LogID = os.LogID AND
os.LogAction IN (1,2,3) AND
pt.PmntTypeID = pa.MethodID AND
pa.OrderID = 1526925
GROUP BY pt.Description, pa.Method_Order, os.LogAction
Query B.
SELECT pt.Description,
pa.Method_Order,
os.LogAction,
Sum(pa.Amount) Total
FROM BO_PaymentAmountsApplied pa,
BO_OrderStatusLog os,
Members.members.dbo.MBR_PaymentTypes pt
WHERE (pa.LogID = os.LogID) AND
(os.LogAction IN (1,2,3)) AND
(pt.PmntTypeID = pa.MethodID) AND
(os.orderid = pa.orderid) AND
(pa.OrderID in (select orderid from BO_PaymentAmountsApplied where orderid = 1526925))
GROUP BY pt.Description, pa.Method_Order, os.LogAction
View 1 Replies
View Related
Mar 8, 2001
I am trying to set up linked servers between several SQL 7.0 servers, but everytime I try, I get the message:
Error 18456: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'
Only thing is, I thought I was logged into both servers using my NT name. Any ideas?
View 2 Replies
View Related
Mar 20, 2003
I have the following distributed query. I am running it from
SQL Server "CASTER" while it links databases from SQL1 and SQL2
Servers.
SELECT T1.ENTITY, T2.EMPLNAME, T1.EMPLID, T1.FISCAL_YR,
T1.ACCOUNT_NBR10, T1.POSITION_NBR, JOBCLASS, FINAL_BGT_FTE,
FINAL_BGT_PCT, FINAL_BGT_SAL, FINAL_BGT_FTB, TERM
FROM SQL1.BUDGET.DBO.BDBPDCTB T1,
SQL2.TECHRIS.DBO.TRBASCTB T2
WHERE T1.FISCAL_YR = '2003'
AND T1.EMPLID > 0
AND T1.EMPLID = T2.EMPLID
AND T1.ENTITY = 'H'
AND T1.ORGID = 'TT'
AND EXISTS (SELECT T3.EMPLID FROM SQL1.BUDGET.DBO.BDBPDCTB T3
WHERE T3.EMPLID = T1.EMPLID --------ERROR HERE
AND T3.ENTITY = 'H'
AND T3.FISCAL_YR = '2003'
GROUP BY T3.EMPLID
HAVING SUM(T3.FINAL_BGT_PCT) > 100)
ORDER BY 1,2,5,6
In the error line, it says, T1 does not match with a table name or alias name used in the query.
SELECT T1.ENTITY, T2.EMPLNAME, T1.EMPLID, T1.FISCAL_YR,
T1.ACCOUNT_NBR10, T1.POSITION_NBR, JOBCLASS, FINAL_BGT_FTE,
FINAL_BGT_PCT, FINAL_BGT_SAL, FINAL_BGT_FTB, TERM
FROM SQL1.BUDGET.DBO.BDBPDCTB T1,
SQL2.TECHRIS.DBO.TRBASCTB T2
WHERE T1.FISCAL_YR = '2003'
AND T1.EMPLID > 0
AND T1.EMPLID = T2.EMPLID
AND T1.ENTITY = 'H'
AND T1.ORGID = 'TT'
AND EXISTS (SELECT T3.EMPLID FROM SQL1.BUDGET.DBO.BDBPDCTB T3,SQL1.BUDGET.DBO.BDBPDCTB T5
WHERE T3.EMPLID = T5.EMPLID --------NO ERROR
AND T3.ENTITY = 'H'
AND T3.FISCAL_YR = '2003'
GROUP BY T3.EMPLID
HAVING SUM(T3.FINAL_BGT_PCT) > 100)
ORDER BY 1,2,5,6
When I am going to have the query like the above, i dont get any error.
Any help wd be appreciated.
Thanks
Sathya
View 2 Replies
View Related
Mar 20, 2003
I did not get any reply for my previous post. So i am just trying to make my doubt clear.
I have a subquery within a distributed query.
Eg:
SELECT T1.deptID
FROM SERVER1.ACCOUNT.DBO.DEPT as T1
where deptid IN (SELECT T2.DEPTID
FROM SERVER2.DEPARTMENT.DBO.DEPT as T2
WHERE T1.DIVISIONID = T2.DIVISIONID)
In the above query.. T1.DIVISIONID is not recognoized and it says
T1 is not a table or alias name though I have declared it upfront.
You have to note that both the ACCOUNT and the DEPARTMENT database are on different servers.
If they are on the same servers, this should not be a problem at all. Also, all the security is working perfectly for running a distributed query for me.
What is the means to make this subquery work.
Thanks for any help
Sathya
View 1 Replies
View Related
Oct 9, 2003
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=W:MyExcel.xls', 'select * from [Sheet1$]')
which works on my local server with Microsoft office XP Professional installed
But keep on failing on the QA server which has MDAC installed only, no Microsoft Office installed
The error msg is 7399. I thought it's because of permission issue.
But problem still resides after I move the Excel file into that QA NT server and execute the statement with a service account which has sysadmin permission on both NT Server and SQL Server
What might be te reason?Why it's working perfectly in my Local server.
--------------------------------------
This is the error message:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
View 4 Replies
View Related
Apr 26, 2006
Hi All!
While running a distributed query i am having following problem.
'
Server: Msg 7391, Level 16, State 1, Procedure t25, Line 8
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 returned 0x8004d00a].
'
can any body give any solution for this ?
Regards,
Shabber.
View 4 Replies
View Related
Apr 10, 2007
Hello
I have 2 servers: myLocalServer (SQL2005) and myRemoteServer (SQL2000), both in the same LAN. I wish to syncronize a remote table with a local table (both share the same structure) by means of a stored procedure. The amount of rows to carry from the local to the remote table is about 20,000. The query takes more than a minute, and I would like to take down that time. Can you please help me?
myRemoteServer is declared in myLocalServer by means of a Linked Server object, and I declared a synonym called Syn_RemoteTable which represent the remote table.
First I tried a cursor, but it did not worked:
declare curLocalTable cursor local forward_only static read_only for
select ID, Value from myLocalTable where UpdateTimeStamp>@LastUpdate
open curLocalTable
fetch curLocalTable into @ID, @Value
while @@Fetch_Status=0
begin
if exists(select ID from Syn_RemoteTable where ID=@ID)
begin
update Syn_RemoteTable set Value=@Value where ID=@ID
end
else
begin
insert into Syn_RemoteTable (ID, Value) values (@ID, @Value)
end
fetch curVentasMensuales into @ID, @Value
end
close curLocalTable
deallocate curLocalTable
Other way that I tried -performing equally poorly- was:
update Syn_RemoteTable
set Value=T.Value
from Syn_RemoteTable
inner join
(
select ID, Value from myLocalTable where UpdateTimeStamp>@LastUpdate
) as T
on T.ID=Syn_RemoteTable.ID
insert into Syn_RemoteTable
(
ID,
Value
)
select
ID,
Value
from myLocalTable
where
UpdateTimeStamp>@LastUpdate and
ID not in (select ID from Syn_RemoteTable)
View 2 Replies
View Related
Aug 21, 2007
Hello,
I have been looking for an example of how to do an ad hoc distributed query to an MS Access database. I've tried this;
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'\Server1DataCorporateCPSDailyToolsTest.mdb' ;'admin';'',Names)
I recieve an OLEDB provider error when I try it this way. The version of Access is 2003. The database 'Test.mdb' is on a network share, and the table is called 'Names'. The share is located on the machine that hosts SQL Server 2000.
Is there a setting in sql server that can be set to allow/disallow ad hoc distributed queries?
What am I missing?
Thank you for your help!
cdun2
View 5 Replies
View Related
Aug 13, 2007
Hi,
I hope I'm in correct forum..
I'm working on simple OLE DB provider that should be usable in SQL server as a linked server. The provider should provide some support for SQL (certain SELECTs for now) and therefore I'd like the SQL server to delegate portions of query processing to the provider instead of fetching all rows and doing SELECT on its own.
Alas, I'm no OLE DB expert and my provider apparently doesn't implement enough things to actually make SQL server pass the query to it. My provider's session currently implements mandatory interfaces + couple of others (IDBCreateCommand, IDBSchemaRowset) and via provider properties, I 'claim' to have full support for SQL92. I'm testing the provider with SQL Server 2005 ('Express edition').
I'd like to know what am I missing in the implementation, or whether/how can I find out what makes SQL server decide between fetching full rowset and creating a command to let rthe query run in provider.
Thanks,
L.
View 4 Replies
View Related
Oct 18, 2000
Three weeks ago we began a project that involved importing data from an AIX DB2 6 environment via a linked server configuration. Following the data import a second query was executed against the db2 environment using data that resides in the new table within SQL 7 in the join statement (a very basic example is provided below) This was all accomplished in sequence via a package.
SELECT F_NAME, L_NAME, PASSWD
FROM SQL7.LOCALSRV.dbo.NEWUSERTBL as new, DB2.SYSIBM.MASTERTBL.OLDUESRTBL as old
WHERE new.USER_ID = old.USER_ID
Originally we had no problems and while the execute time was not exactly speedy it was tolerable as we would revisit optimization after we established if what we were trying to accomplish was feasible. At the outset the first data import to build the local table was immeadeate and then the distributed query to retirieve more info to build another local table against the DB2 server took aproxiamtly 1 minute per user row returned. Currently we are looking at still having an immeadeate data import (a matter of seconds to build the first table)but now we are looking at more than 1 hour returned for 1 correesponding row of data off of DB2. We are utilizing the the IBM DB2 ODBC DRIVER.
Any input or suggestions as to what could be causing this or perhaps a more efficeint way to code the statement would be much appreciated. Thanks in advance.
Adrian
View 1 Replies
View Related
Jul 26, 2002
Below given query is being executed on a Sql 2k box with 4CPU and 2GB RAM
testXX.DB_GRP.dbo.group1-----> is a sql 7 box with single CPU and 512MB RAM
The result set is abt 30,000 rows .
This whole Process is taking abt 5 mins to do the Insert Process.
Is there a way to optimise the query and bring down the execution time
insert into testXX.DB_GRP.dbo.group1
select num, group_num,group_desc from group2
where id = 20
---------------
If we just run the
select num, group_num,group_desc from group2
where id = 20
it takes 10 secs to execute this selct statement so i was wondering why it takes 5 mins to do the insert process across the network thru linked server query.
Any help would be appreciated?
Thanks,
MK
View 3 Replies
View Related
Jul 28, 2001
Hello All!
I have created the following Distributed Query to maintain the current data between local server and Remote server:
EXEC SP ADDLINKEDSERVER 'SSNTDB2'
go
-----UPATE asp org table
set identity insert tn.asp org on
go
insert into tn.asp orhh ( ao key, ao name, ao pid, ao login link, ao login instr top, ao login instr bot,ao login link nm, ao lms enabled, ao lms prefix )SELECT ao key, ao name, ao pid, ao login link, ao login instr top, ao login instr bot,ao login link nm, ao lms enabled, ao lms prefix from SSNTDB2.icomm live.tn.asp org jung w
WHERE jung.ao key not in ( select ao key from tn.asp orhh )
go
set identity insert tn.asp org off
go
---update themesw table
set identity insert tn.themesw on
go
insert into tn.themews (seq nbr,name,source dir,th desc,update by,update dt,partner id,Inst Payer Nbr,def lang key,t status,def audience key)
SELECT seq nbr,name,source dir,th desc,update by,update dt,partner id,Inst Payer Nbr,def lang key,t status,def audience key FROM SSNTDB2.icomm live.tn.themes jun
WHERE jun.seq nbr not in (select seq nbr from tn.themes)
go
set identity insert tn.themesw off
GO
EXEC droplinkedserver 'SSNTDB2'
I want to create the stored procedure to make this script run automatically and create a scheduled job so that the local server can be updated it's records periodically. What is the best way to do this? Any tips will be appreciated..
Thanks John
View 2 Replies
View Related
Mar 19, 2004
Hi Chaps!!
I am in serious problem that My production sql 2000 server with winSp 4 and sqlSP 2 with slammer hotfix is not executing four part distributed query well when I combine the query with begin tran statement it enter into hang mode... else without begin tran it is fine.
request to all of u to get rid of this situation as some modules of our applicaiton is not functioning...
can sp3a installation help or going back to winSp2/3 will be helpfull. (recently we have applied winsp4 but i don't think this is concerned with it)
thanks in advance to give your time...
View 9 Replies
View Related
Feb 13, 2008
I am working on a linked server where a few of the queries use almost exclusively remote tables from 1 other server. I have read somewhere that there are options to specify where a query is performed but can't find it anywhere. Could someone tell me the command and how to use it or point me to something to read about it.
Thanks
View 1 Replies
View Related
Aug 28, 2006
I have a huge problem as mentioned in my previous queries some of my applications is using Link Server Query as "select * from sm-matrix.matrix.dbo.stage_orders" this doesn't work it gives following error:-
ODBC: Msg 0, Level 18, State 1
SqlDumpExceptionHandler: Process 62 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Unspecified error]
I understand best method to use is "select * from openquery(sm-matrix,"select * from stage_orders")" but i can't do away with above mentioned query as lot of places in application it has been using.
This was working fine till i moved to Windows 2003 from Windows 2000.
Following is the error we are getting:-
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/28/06 15:52:06 spid 58
*
* Exception Address = 00404743 (RecBase::Resize + 00000005 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* [ M A T R I X ] 1b 00 5b 00 4d 00 41 00 54 00 52 00 49 00 58 00 5d 00
* . . s p _ i n d e 2e 00 2e 00 73 00 70 00 5f 00 69 00 6e 00 64 00 65 00
* x e s _ r o w s e 78 00 65 00 73 00 5f 00 72 00 6f 00 77 00 73 00 65 00
* t ç S 74 00 00 00 00 00 e7 18 00 00 00 00 00 00 18 00 53 00
* T A G E _ O R D E 54 00 41 00 47 00 45 00 5f 00 4f 00 52 00 44 00 45 00
* R S ç 52 00 53 00 00 00 e7 00 00 00 00 00 00 00 ff ff 00 00
* ç d b o e7 06 00 00 00 00 00 00 06 00 64 00 62 00 6f 00
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B32FFF 00733000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* USER32 77380000 77411FFF 00092000
* GDI32 77C00000 77C47FFF 00048000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BA0000 77BF9FFF 0005a000
* UMS 41070000 4107BFFF 0000c000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 60020000 6002FFFF 00010000
* sqlevn70 10000000 10006FFF 00007000
* Secur32 76F50000 76F62FFF 00013000
* NETAPI32 110B0000 11107FFF 00058000
* ole32 113A0000 114D3FFF 00134000
* XOLEHLP 11660000 11665FFF 00006000
* MSDTCPRX 11670000 116E7FFF 00078000
* msvcp60 116F0000 11750FFF 00061000
* MTXCLU 11760000 11778FFF 00019000
* VERSION 11780000 11787FFF 00008000
* WSOCK32 11790000 11798FFF 00009000
* WS2_32 117A0000 117B6FFF 00017000
* WS2HELP 117C0000 117C7FFF 00008000
* OLEAUT32 117D0000 1185BFFF 0008c000
* CLUSAPI 118A0000 118B1FFF 00012000
* RESUTILS 118C0000 118D2FFF 00013000
* USERENV 118E0000 119A3FFF 000c4000
* mswsock 119C0000 11A00FFF 00041000
* DNSAPI 11A10000 11A38FFF 00029000
* winrnr 11A80000 11A86FFF 00007000
* WLDAP32 11A90000 11ABDFFF 0002e000
* rasadhlp 11AE0000 11AE4FFF 00005000
* SSNETLIB 00C70000 00C84FFF 00015000
* NTMARTA 00C90000 00CB1FFF 00022000
* SAMLIB 00CC0000 00CCEFFF 0000f000
* security 125D0000 125D3FFF 00004000
* hnetcfg 125E0000 12638FFF 00059000
* wshtcpip 12800000 12807FFF 00008000
* SSmsLPCn 12810000 12817FFF 00008000
* SSnmPN70 12A20000 12A25FFF 00006000
* ntdsapi 12AE0000 12AF4FFF 00015000
* kerberos 12B10000 12B67FFF 00058000
* cryptdll 12B70000 12B7BFFF 0000c000
* MSASN1 12B80000 12B91FFF 00012000
* SQLFTQRY 12920000 12951FFF 00032000
* xpsp2res 12EB0000 13174FFF 002c5000
* CLBCatQ 13180000 13202FFF 00083000
* COMRes 13210000 132D5FFF 000c6000
* sqloledb 132E0000 13360FFF 00081000
* MSDART 12960000 12979FFF 0001a000
* MSDATL3 12980000 12994FFF 00015000
* oledb32 136F0000 13768FFF 00079000
* OLEDB32R 13770000 13780FFF 00011000
* msv1_0 13810000 13836FFF 00027000
* iphlpapi 13840000 13859FFF 0001a000
* PSAPI 13860000 1386AFFF 0000b000
* xpsqlbot 13910000 13915FFF 00006000
* rsaenh 13A50000 13A7EFFF 0002f000
* xpstar 13BB0000 13BF6FFF 00047000
* SQLRESLD 13C00000 13C06FFF 00007000
* SQLSVC 13C10000 13C26FFF 00017000
* ODBC32 13C60000 13C9CFFF 0003d000
* COMCTL32 13CA0000 13D36FFF 00097000
* comdlg32 13D40000 13D89FFF 0004a000
* SHELL32 14110000 14912FFF 00803000
* SHLWAPI 13D90000 13DE1FFF 00052000
* odbcbcp 13C30000 13C35FFF 00006000
* W95SCM 13C40000 13C4BFFF 0000c000
* SQLUNIRL 13DF0000 13E1CFFF 0002d000
* WINSPOOL 13E20000 13E46FFF 00027000
* SHFOLDER 13E50000 13E58FFF 00009000
* comctl32 14920000 14A22FFF 00103000
* odbcint 13EE0000 13EF6FFF 00017000
* NDDEAPI 13F00000 13F06FFF 00007000
* SQLSVC 14CB0000 14CB5FFF 00006000
* xpstar 14CC0000 14CC8FFF 00009000
* ACTIVEDS 14CD0000 14D02FFF 00033000
* adsldpc 14D10000 14D36FFF 00027000
* credui 14D40000 14D6DFFF 0002e000
* ATL 14D70000 14D87FFF 00018000
* adsldp 14DF0000 14E1DFFF 0002e000
* SXS 14FA0000 1505BFFF 000bc000
* xplog70 15060000 15071FFF 00012000
* xplog70 13C50000 13C53FFF 00004000
* DBNETLIB 11630000 1164BFFF 0001c000
* crypt32 15180000 15212FFF 00093000
* SQLOLEDB 11650000 1165EFFF 0000f000
* dbghelp 15620000 156D4FFF 000b5000
*
* Edi: 00000005:
* Esi: 6BDE4924: 00000000 00000052 00000000 00000000 00000003 0000001B
* Eax: 00000000:
* Ebx: 3B6AFFFD: 013FF000 20000001 CA000000 01000014 CC004200 01000014
* Ecx: 6BDE4924: 00000000 00000052 00000000 00000000 00000003 0000001B
* Edx: 00000E00:
* Eip: 00404743: E183088A 04E9830E 00C9840F 4949001B 4E8B5275 08668304
* Ebp: 1289D77C: 1289D790 005BD328 00A5EA38 1289D78C 1289E8B8 1289EC74
* SegCs: 0000001B:
* EFlags: 00010246: 0057004F 003B0053 003A0043 0057005C 004E0049 004F0044
* Esp: 1289D768: 6BDE4924 00446C52 00000000 00000002 3B6AB940 1289D790
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 00404743 Module(sqlservr+00004743) (RecBase::Resize+00000005)
* 00446C52 Module(sqlservr+00046C52) (CSysScan::GetVaried+0000002A)
* 005BD328 Module(sqlservr+001BD328) (CUserScan::CbGroupBitmap+00000016)
* 006315F7 Module(sqlservr+002315F7) (SecCache::FGetFromDiskScedb+00000317)
* 0040C694 Module(sqlservr+0000C694) (checkdbperm+00000114)
* 0040C3BC Module(sqlservr+0000C3BC) (usedb+000000DA)
* 0040C2DF Module(sqlservr+0000C2DF) (CAutoDb::FUse+00000031)
* 004B2BE3 Module(sqlservr+000B2BE3) (CreateFakeTableRowset+00000038)
* 00424175 Module(sqlservr+00024175) (OpenRowsetSS::OpenRowset+000000EC)
* 0050D873 Module(sqlservr+0010D873) (GetTableCursor+00000056)
* 0050D7FE Module(sqlservr+0010D7FE) (CQScanRowset::StandardGetRowset+0000009D)
* 00539A82 Module(sqlservr+00139A82) (CQScanTableScan::CQScanTableScan+0000008E)
* 005399EA Module(sqlservr+001399EA) (CXteTableScan::QScanGet+00000089)
* 004332E2 Module(sqlservr+000332E2) (CQScanHashMatch::CQScanHashMatch+0000051A)
* 00432E02 Module(sqlservr+00032E02) (CXteHashMatch::QScanGet+0000008C)
* 00427368 Module(sqlservr+00027368) (CXteProject::QScanGet+00000092)
* 0053D884 Module(sqlservr+0013D884) (CQScanSort::CQScanSort+000000BC)
* 0053D7A7 Module(sqlservr+0013D7A7) (CXteSort::QScanGet+0000012C)
* 0042306F Module(sqlservr+0002306F) (CQueryScan::CQueryScan+0000028E)
* 00422E59 Module(sqlservr+00022E59) (CQuery::Execute+0000006A)
* 0041D456 Module(sqlservr+0001D456) (CStmtQuery::ErsqExecuteQuery+0000022C)
* 0042C4AF Module(sqlservr+0002C4AF) (CStmtSelect::XretExecute+00000229)
* 0041C3CB Module(sqlservr+0001C3CB) (CMsqlExecContext::ExecuteStmts+000003B9)
* 0041BA11 Module(sqlservr+0001BA11) (CMsqlExecContext::Execute+000001B6)
* 0041B02D Module(sqlservr+0001B02D) (CSQLSource::Execute+00000357)
* 00437EC6 Module(sqlservr+00037EC6) (execrpc+00000507)
* 00437128 Module(sqlservr+00037128) (execute_rpc+00000019)
* 0042921A Module(sqlservr+0002921A) (process_commands+00000232)
* 41072838 Module(UMS+00002838) (ProcessWorkRequests+00000272)
* 410725B3 Module(UMS+000025B3) (ThreadStartRoutine+00000098)
* 77BCB3CA Module(MSVCRT+0002B3CA) (endthread+000000AB)
* 77E66063 Module(kernel32+00026063) (GetModuleFileNameA+000000EB)
* -------------------------------------------------------------------------------
View 1 Replies
View Related
Aug 26, 2006
I am going to make a distributed query, but in the query design view I cannot add the tables from another server, why?
View 3 Replies
View Related
Jan 17, 2007
Hi all,
I found an article which described about Distributed query to Oracle table with four-part name.
http://support.microsoft.com/kb/294459/en-us
However, we still got the same error with MSSQL2000 sp4 and SQL2005. Any fix to this?
View 1 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Mar 25, 2002
Hi.
I am trying to store the column value to a variable from a distributed query.
The query is formed on the fly.
i need to accomplish something like this
declare @id int
declare @columnval varchar(50)
declare @query varchar(1024)
@Query = "select @columnval = Name from server.database.dbo.table where id ="+convert(varchar,@ID)
exec (@query)
print @Columnname
-MAK
View 2 Replies
View Related
Jun 21, 2006
Hi,
The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.
OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
View 7 Replies
View Related
Apr 12, 2007
Hello
TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. I run an stored procedure in TestMachine1 which inserts about 20,000 rows to a table in myRemoteServer and brings back a similar quantity of rows. This stored procedures take about 1.5min to complete, but no error appears.
When running the same stored procedure in TestMachine2 (also SQL2005SP2), the following error appears after about 1 minute of execution (not the exact text):
SQLNCli. TCP Provider: network name is no longer available - communication link failure.
Please note that this stored procedure worked before on TestMachine2 (but with less than 10,000 rows) and that connectivity is proven among TestMachine1 and myRemoteServer, since I can execute "select * from synonym_MyRemoteTable" with no problems at all in the TestMachine2's Management Studio.
TestMachine1 and TestMachine2 have Windows XP Professional SP2; myRemoteServer has Windows 2003 and SQL Server 2000 SP4.
Can you please help me to avoid this error?
View 1 Replies
View Related
Nov 18, 2005
I'm trying to set up Service Broker Services on SQL 2005 x86. I've got two services set up, and a stored procedure associated with one of them.
View 3 Replies
View Related
Jan 24, 2008
Is it possible to query linked servers without the Distributed Transaction Coordinator service enabled or allowing network access?
Is that ONLY for transactions? What if I just wanted to read the data and nothing else?
View 13 Replies
View Related
Feb 26, 2008
I'm experiencing issues importing XML data using a distributed query with the following statement which is run from an XP client named WorkstationA connecting to SQL2005 SP2 ServerB, the XML data is located on ServerC.
AdHoc Queries using OpenRowSet has been enabled and verified.
The SQL Server service is running using a domain user account with permissions to read the remote files. I have logged in locally to the SQL server and verified this. It still fails even if the SQL services are running using LocalSystem.
User on Workstation A is authenticated with Integrated security (SQL Admin) and has rights to read the XML files on ServerC.
WorkStationA = SQL2005 Mgt Studio running the query
ServerB = SQL2005 SP2
ServerC = XML data files
DECLARE @xml XML
SELECT @xml =CONVERT(XML, bulkcolumn, 2)
FROM OPENROWSET(BULK '\SERVERCSHAREPATHDATAFILE.XML', SINGLE_BLOB) AS x
SELECT @xml
Results: Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "\SERVERCSHAREPATHDATAFILE.XML" could not be opened. Operating system error code 5(Access Denied).
The query fails when it is run from Workstation A connected to SQL ServerB querying data on ServerC via a UNC.
The query is succesful when it is run from the local SQL ServerB. The problem is with distributed queries.
The query is succesful when the XML files are local to the SQL server including referencing them via a local UNC
Thank you for any responses.
Hamish
View 4 Replies
View Related
Aug 24, 2006
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
View 5 Replies
View Related
