Distributed Query....help.
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
ADVERTISEMENT
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
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
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
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
Feb 29, 2008
We have a test db, a staging db and a live db. I have a stored procedure that runs fine on test and staging, but throws the following error on live.
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
The stored procedure uses linked servers and a transaction.
We're using the following transaction code in the stored procedure
BEGIN
BEGIN TRANSACTION
BEGIN TRY
---
procedure stuff here
---
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
END CATCH
END
I found the following link which seems to be the problem we're experiencinghttp://support.microsoft.com/kb/937517
The link includes a workaround which is the following:
"To prevent the SQLNCLI provider from sending an attention signal to the server, use the SQLNCLI provider to consume fully any rowsets that the OLE DB consumer creates. "
How do I use the SQLNCLI provider to fully consume any rowsets?
View 11 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Nov 29, 2007
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
View 1 Replies
View Related
