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
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
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")
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 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.
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
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.
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.
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
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
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?
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.
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$]