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?
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)
* -------------------------------------------------------------------------------
I am attempting to update an oracle table from a insert trigger on my SQL table. When using a trigger to run a sql command against the ole db oracle client (linked server) a distributed transaction is initiated and i get a message stating. "The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."
I can update the oracle table through a query however just not throught a distributed transaction.
However according to all the documentation i can find it states that I should be able to perform this.
Can anyone shed any insight?
I have 2 tables (table1KKK, table2KKK), and want to run the same query on them by using parameter with the value "1" or "2".
Is it possible to use that parameter as a part of the queried table name?
Something like (only for demonstration €“ doesn€™t work):
Select *
From table +myParameter + kkk
Thanks in advance!
Hello!I have an Oracle linked server connected through MSDAORA. Linked serverqueries work perfectly - the "openquery" ones as well as the4-part-named ones.The problem I have is with embedding the queries within SQL Servertriggers.Trigger:CREATE TRIGGER tgTest ON [dbo].[test]FOR INSERT, UPDATE, DELETEASselect * from openquery(LS, 'select * from ORACLE_TEST')executing "delete from test" in SQL Query Analyzer raises this error:Server: Msg 7391, Level 16, State 1, Procedure tgTest, Line 5The operation could not be performed because the OLE DB provider'MSDAORA' was unable to begin a distributed transaction.OLE DB error trace [OLE/DB Provider 'MSDAORA'ITransactionJoin::JoinTransaction returned 0x8004d01b].I've tried almost every solution I found online, but nothing helped:(This looked promissing: http://tinyurl.com/nk2wd , but it didn't get meany futher.Maybe someone can get me through the troubleshoot mentioned in thatlink:- check if DTC running properlyHow do I check that? If I open the "Support services" in EnterpriseManager and right-click the "Distributed Transaction Coordinatior" Ican stop the service, what indicates the service is running, but isthere anything else I should check? I have 0 items in the right windowpane of the DTC item, is it OK?- registry setting as discussed earlierThe following Registry Keys should be entered:[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI ]"OracleXaLib"="oraclient8.dll""OracleSqlLib"="orasql8.dll""OracleOciLib"="oci.dll"My entries are:"OracleOciLib"="ociw32.dll""OracleSqlLib"="SQLLib18.dll""OracleXaLib"="xa73.dll"Are they OK?- check if Mtxoci.dll is loadedThere is a Mtxoci.dll in my system32 dir, but how do I tell if it'sloaded? Should I regsvr32 it?- SET XACT_ABORT ON should be use in your SQL statement, for example:SET XACT_ABORT ONBEGIN DISTRIBUTED TRANSELECT statementCOMMIT TRANI've tried that, both in trigger but also surrounding the query thatfires the trigger.Am getting deseperate - please help. Will send candies!TIA
View 1 Replies View Related
I lost few days to solve a problem regarding the connection to an Oracle database server (10) with goal to execute a distributed transaction.
The environment was:
Windows Server 2003 Standard
MSSQL Server 2000 Standard
Oracle 10g Production Edition
The connection was quite easy to make with the Microsoft driver, but the error was:
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
I tried to use the oracle odbc driver, but all kinds of errors were raised, regarding the TNS name and stuff like this. The real cause was that when you create the linked server you must to check the €œAllow inbound process€? in the provider options.
The Oracle ODBC driver allow distributed transactions.
Hi all,
I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?
This is the code i'm using for the query:
DECLARE @cmd VARCHAR(500)
declare @Year varchar(25)
set @Year = '2006'
DECLARE @Link VARCHAR(100)
DECLARE @Table VARCHAR(100)
select @Link = Server from tbl_Conn where Area='Floor'
select @Table = Target_Table from tbl_Conn where Area='Floor'
SET @cmd =
'
select * from OPENQUERY
(
' + @Link + ',
''
UPDATE '+ @Table +'
SET TARGET_VALUE = '+@Value+'
WHERE Date = '+@Year'
''
)
'
EXEC (@cmd)
How do I put the executed results into a TEMP table ?
Rgds,
Hi,
I have Table A . we already have 80 columns . we have to add 65 more columns.
we are populating this table from oracle .and we need to populate those 65 columns again from the same table.
Is it a better idea to add those new 65 columns to the same table or new table.
If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.
I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
I am new to sqlserver.
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
Any ideas thnx.
Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
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.
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"
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
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
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?
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
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?
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
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
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: ].
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.
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)
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
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.
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
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
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
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")
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...
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
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 RelatedHi.
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