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)
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
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$]
Hi, I m using SQL Server 2000's 120 days evaluation copy for testing my application. The prob i am facing is that whenever the rows in one of the table increses beyond 10K it gets cleared up.
I could not trace the reason for this. Plz some one suggest a possible solution for the same.
I am using SQL Server 7.0 SP2. I've got a table x that has fields say:
id1 id2 a b c d e f
(each field name is separated by a space. id1 + id2 is the primary key combination for this table)
If there are say 100 records in this table I can write a SELECT query like:
SELECT (a + b + c + d) AS Item FROM x WHERE id1 = 'my_value1' AND id2 = 'my_value2'
and I could get up to 16 records (say) using this query. The no. of records I get is variable.
My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor? I'll be grateful for any help,
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl' T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
We just upgraded from SQL 7 to 2000 and for some reason I am unable to perform distributed transactions. I keep getting the following error:
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransaction Join:: JoinTransaction returned 0x8004d00a]. Operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction
I tried a few of the fixes recommended on the microsoft website but they did not solve the problem. We are still using the same O/S (Windows 2000 Pro), so the only thing that has changed is the server.
I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...
Linked Server name: SQL2005
From the SQL Server 2000 instance in Query Analyzer....
-- this works fine Delete from [SQL2005].myDB.dbo.myTbl
-- this just hangs Begin Tran Delete from [SQL2005].myDB.dbo.myTbl
It doesn't make any difference what provider I use on the Linked Server.
I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.
We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.
Just curious if anyone out there has had success running a Distributed Transaction on Windows 2000 Professional SP4 lately?
I have come to the conclusion that is is virutally impossible. I am running MSDE on a Win2k Pro machine and trying to run a distributed transaction via linked server and am getting the
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction."
error. Yes I have been scouring the web looking for answers and have implemented pretty much everything I have run accross... and Yes the MSDTC service is started on both machines... BTW: If I run the procedure on XP it works great!!!
I am wondering if anyone even uses Win2k Pro anymore let alone trying to run a distributed tarnsaction on the darn thing.
If anyone out there has it running let me know I would love to chat with you for a minute or two.
I have been struggling with a problem for the last couple days now regarding MSTD and distributed transactions. The main issue is that 3 servers are each sitting in a different domain. Non of the domains trust each other.
As of yesterday, I could get all the SQL 2005 chatting to each other using MSDTC without a problem. However, I have a SQL 2000 box which refuses to work. A normal query across a linked server works fine. A distributed transaction will not work. This is the case from A to B and B to A.
I have tested with DTCPing and it says all is ok.
The error messages are:
SQL 2000 > SQL 2005
OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" was unable to begin a distributed transaction.
And the other way round:
[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].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
If watching the DTC monitor, it shows an active transaction then goes back to 0.
All instances are on a 4 node cluster with 3 nodes turned off for the testing.
I have run out of things to try. Most of the symptoms for the above messages are to do with DTC security, the Turn RPC Security Off reg hack. All instances and OS (W2K3) are the latest updates / patches.
As for the 3rd server, SQL 2005, it is happy to talk
Anybody got a gem which I can try?
Edit: Both SQL 2005 servers are x64 while the 2000 is x86
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?
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?
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
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 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.
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.
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: ].
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.
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?
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.
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.
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.
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..
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")
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.
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.
