Tuning A Distributed Query

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


ADVERTISEMENT

Query Tuning And Stored Procedure Tuning

Nov 22, 2000

Hi

Is there any good books for Query Tuning and Stored procedure Tuning

Thanks

View 1 Replies View Related

Query Tuning

May 28, 2008

Hi,

Mine Below Query takes considerable time at the time of execution.
Can any one help me, what is the other way to write this query?

Declare @p_Mkt_View_Id int
Set @p_Mkt_View_Id = 17


Select Distinct Customer_id From Active_Product_Cust_Dtl
Where Product_Group_Code in
(Select Distinct Product_Group_Code From Products
Where Product_code in (
select Distinct ProductId from pit
where pitid in (select pitid from marketviewdef
where mktviewid = @p_Mkt_View_Id)))

Thanks
Prashant

View 4 Replies View Related

Tuning A Query

Oct 25, 2007

Hi have this query which is taking too much time to execute..........I have tried the following options but not useful till now
1. NOLOCK
2. SET NO ROW COUNT
3. CHANGED DISK LOCATION OF TEMPDB
4. CHECKED %Processor Time
5. Checked pages.sec

Below is the query, any suggestions will be really helpful


SE IFRepository
--Query
--Returns count of txns whose status is not (10001 or 10002)
declare @fileruntimeuid int
declare @Pendingackcount int
set @Pendingackcount =0
set @fileruntimeuid =0
declare @clientname varchar(256)
set @clientname = NULL
declare @txncnt int
set @txncnt = 0
declare @FileNameClient varchar(256)
set @FileNameClient = NULL


declare @StageStatus int
set @StageStatus = 0
declare @StageDesc varchar(35)

declare PendingAcks cursor for
select distinct fileruntimeuid from tiffileruntime WITH (NOLOCK)
where filecreationdt >= convert(smalldatetime,'9-11-07')
and filecreationdt <= convert(smalldatetime,'9-12-07')
and statusid <> 2
--and filetypeuid in (1,8,16,17,18)
--and clientuid =1205
order by fileruntimeuid
--244873, 244883, 244885, 244892, 244893, 244925, 244926, 244966, 244967, 244873, 244883

Print 'File Life Cycle Viewer via Database'
Print '==========================================================================================='
Print 'Status FileRuntimeUID Client Name File Status File name'
Print '==========================================================================================='
Open PendingAcks
FETCH NEXT FROM PendingAcks
into @fileruntimeuid
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 @StageDesc = b.IFComponentDesc from TIFComponent b, TIFFIleProcessingStatus a
WITH (NOLOCK)
where a.IFComponentUID = b.IFComponentuid
and a.fileruntimeuid = @fileruntimeuid
order by a.FPROCStageStartDt desc-- a.IFComponentUID desc

select @clientname = ClientShortName from tifclientattrib
where clientuid = (select clientuid from tiffileruntime where fileruntimeuid = @fileruntimeuid)

select @StageStatus = statusid, @FileNameClient= FileNameClient from tiffileruntime where fileruntimeuid = @fileruntimeuid
select @txncnt = FProcTxnProcessedInTotal from tiffileprocessingstatus where fileruntimeuid = @fileruntimeuid and IFComponentUID = 5
--if @StageDesc = "" Begin @StageDesc = "------------" End
print RTRIM(convert(varchar(10),@StageStatus)) + ' ' + RTRIM(convert(varchar(10),@fileruntimeuid)) + ' ' + RTRIM(@clientname) + ' ' + @StageDesc + ' ' + RTRIM(@FileNameClient)
set @StageDesc = NULL
FETCH NEXT FROM PendingAcks
into @fileruntimeuid
END

Print '==========================================================================================='
close PendingAcks
deallocate PendingAcks

View 5 Replies View Related

Query Tuning

May 22, 2006

I want to take the execution plan of some transact sql queries, I took the execution plan as text based one, that having

index scan, index seek

Remote scan, remote Update

sort order by cluases

 

in the above clauses what is the high performance, and how will i change to the high performance clauses by changin the query to improve the execution speed of the query

 

Please guide me

 

 

 

the execution plan is

 

 

 



SELECT [Inventory_Profile].[InventoryID]        ,[Inventory_Profile].[Alias]        ,[Inventory_Profile].[InventoryStatusID]        ,[Inventory_Profile].[InventorySubTypeID]        ,[Inventory_Profile].[InventoryTypeID]        ,[Inventory_Profile].[AcquisitionDate]        ,[Inventory_Profile].[UnitNumber]        ,[Inventory_Profile].[YearOfManufacture]        ,[Inventory_Profile].[Manufacturer]        ,[Inventory_Profile].[Make]        ,[Inventory_Profile].[Model]        ,[Inventory_Profile].[SerialNumber]        ,[Inventory_Profile].[UsageConditionID]        ,[Inventory_Profile].[Description1]        ,[Inventory_Profile].[Description2]        ,[Inventory_Profile].[LocationEffectiveFromDate]        ,[Inventory_Profile].[IsFlaggedForSale]        ,[Inventory_Profile].[RentalPurchaseOrderNumber]        ,[Inventory_Profile].[AquisitionPurchaseOrderNumber]        ,[Inventory_Profile].[SortOrder]        ,[Inventory_Profile].[IsSaleLeaseBack]        ,[Inventory_Profile].[InterimRentReceivableUpfrontTaxModeID]        ,[Inventory_Profile].[LeaseRentalReceivableUpfrontTaxModeID]        ,[Inventory_Profile].[OverTermReceivableUpfrontTaxModeID]          ,[TaxDepreciation_Inventory].[IsTaxDepreciationRequired]        ,[TaxDepreciation_Inventory].[IsComputationPending]        ,[TaxDepreciation_Inventory].[TaxDepreciationTemplateID]        ,[TaxDepreciation_Inventory].[InventoryCostBasisAmount]        ,[TaxDepreciation_Inventory].[DepreciationBeginDate]        ,[TaxDepreciation_Inventory].[DepreciationEndDate]     ,[TaxDepreciation_Inventory].[IsTaxDepreciationTerminated]        ,[TaxDepreciation_Inventory].[IsStraightLineMethodUsed]        ,[TaxDepreciation_Inventory].[IsLeaseTermUsedForStraightLineMethod]           ,[Inventory_PTMS].[Division]        ,[Inventory_PTMS].[Branch]        ,[Inventory_PTMS].[SalesTaxPercent]        ,[Inventory_PTMS].[SalesTaxAmount]        ,[Inventory_PTMS].[IsSalesTaxIncluded]        ,[Inventory_PTMS].[GLExpenseAccount]        ,[Inventory_PTMS].[GLAssetAccount]        ,[Inventory_PTMS].[SoftwareExclusionAmount]        ,[Inventory_PTMS].[AssetCategoryCodeID]        ,[Inventory_PTMS].[OwnershipCodeID]        ,[Inventory_PTMS].[ManufacturingCodeID]        ,[Inventory_PTMS].[ReimburseCodeID]        ,[Inventory_PTMS].[BillingStatusID]        ,[Inventory_PTMS].[PropertyTaxExemptionCodeID]        ,[Inventory_PTMS].[UserDefinedField1]        ,[Inventory_PTMS].[UserDefinedField2]        ,[Inventory_PTMS].[Notes]      FROM [Inventory_Profile]    INNER JOIN [TaxDepreciation_Inventory]  ON  [Inventory_Profile].[InventoryID]=[TaxDepreciation_Inventory].[InventoryID]    INNER JOIN [Inventory_PTMS]    ON    [Inventory_Profile].[InventoryID]=[Inventory_PTMS].[InventoryID]    INNER JOIN [Inventory_Status_CnfgLocale]     ON  [Inventory_Profile].[InventoryStatusID] in (SELECT InventoryStatusID FROM  Inventory_Status_CnfgLocale               WHERE InventoryStatusName <> 'Donated'               and   InventoryStatusName <> 'Scrap'                and   InventoryStatusName <>'Write Off'               and   InventoryStatusName <> 'Sold')

 

 

 

 

 

  |--Nested Loops(Inner Join)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
       |    |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]))
       |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
       |    |    |    |    |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_PTMS].[PK_IInvPTMS]))
       |    |    |    |    |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Profile].[PK_IInventory_InvID]), SEEK:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
       |    |    |    |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]), SEEK:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusID]=[LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]),  WHERE:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Donated' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Scrap' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Sold' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Write Off') ORDERED FORWARD)
       |    |    |--Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[UK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[PK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]=[LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]) LOOKUP ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]))

 

View 6 Replies View Related

Fine Tuning This Sql-query. Help!

Jul 20, 2005

Hi, I have problem running this query. It will time out for me...My database are small just about 200 members.I have a site for swaping appartments (rental). my query should lookfor matchin a triangle. Like this member A -> B->CA give his appartment to B. B gives his appartment to C and finallyC gives his appartment to ASoo my query looks for matching parameters like rooms, location, sizeandsoo on..I have one table for existing appartments and one for "whantedappartments"and 1 table called "intresse" where members can store "yes" or "no" ifthey are interessted in a appartment.I also have a table called "omrade" to store locations of interest.Hope you can helpe me with some tip soo i can run this query in a fewseconds instead of 20-30 secThanks MSELECTF.medlemsNr as medlemsNr, F.lfId AS lfId, F.ort AS ort, F.gatuadressAS gatuadress, F.gatuNr AS gatuNr, F.rum AS rum,F.storlek ASstorlek,F.hyra AS hyra, count(F.medlemsNr) As hitsFROMmedlem08 A, medlem08 B, medlem08 C, lagenhetF08 D,lagenhetO08 E, lagenhetF08 F, lagenhetO08 G, lagenhetF08 H,lagenhetO08 IWHERED.rum >= I.rumMin AND D.rum <= I.rumMax ANDD.storlek >= I.storlekMin AND D.storlek <= I.storlekMax ANDI.hyraMax = 0" & " OR D.hyra <= I.hyraMax) ANDI.balkong = '" & "" & "' OR D.balkong = I.balkong) AND(I.badkar = '" & "" & "' OR D.badkar = I.badkar) AND(I.bredband = '" & "" & "' OR D.bredband = I.bredband) AND(I.hiss = '" & "" & "' OR D.hiss = I.hiss) AND(I.spis = '" & "" & "' OR D.spis = I.spis) AND(I.brf = '" & "" & "' OR D.brf = I.brf) ANDD.postNr IN (select postNr from ONSKEMAL08 where loId=I.loId) ANDF.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 wheremedlemsNr1=A.medlemsNr) ANDH.rum >= G.rumMin AND H.rum <= G.rumMax ANDH.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND(G.hyraMax = 0" & " OR H.hyra <= G.hyraMax) AND(G.balkong = '" & "" & "' OR H.balkong = G.balkong) AND(G.badkar = '" & "" & "' OR H.badkar = G.badkar) AND(G.bredband = '" & "" & "' OR H.bredband = G.bredband) AND(G.spis = '" & "" & "' OR H.spis = G.spis) AND(G.brf = '" & "" & "' OR H.brf = G.brf) ANDH.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) ANDF.rum >= E.rumMin AND F.rum <= E.rumMax ANDF.storlek >= E.storlekMin AND F.storlek <= E.storlekMax AND(E.hyraMax = 0" & " OR F.hyra <= E.hyraMax) AND(E.balkong = '" & "" & "' OR F.balkong = E.balkong) AND(E.badkar = '" & "" & "' OR F.badkar = E.badkar) AND(E.bredband = '" & "" & "' OR F.bredband = E.bredband) AND(E.hiss = '" & "" & "' OR F.hiss = E.hiss) AND(E.spis = '" & "" & "' OR F.spis = E.spis) AND(E.brf = '" & "" & "' OR F.brf = E.brf) ANDF.postNr IN (select postNr from ONSKEMAL08 where loId=E.loId) ANDA.medlemsNr=D.medlemsNr AND A.medlemsNr=E.medlemsNr ANDB.medlemsNr<>A.medlemsNr AND C.medlemsNr<>A.medlemsNr ANDB.medlemsNr<>C.medlemsNr ANDB.sparr<>1 AND C.sparr<>1 ANDA.typ=11 AND A.medlemsNr=" & session("medlemsNr") & " ANDB.medlemsNr=F.medlemsNr AND B.medlemsNr=G.medlemsNr ANDB.typ=11 AND A.triangel=1 AND B.triangel=1 AND C.triangel=1 AND " &_C.medlemsNr=H.medlemsNr AND C.medlemsNr=I.medlemsNr ANDC.typ=11 group by F.lfId, F.medlemsNr,F.ort,F.gatuadress,F.gatuNr,F.rum,F.storlek,F.hyra

View 9 Replies View Related

MSSQL2000-Self-Join Query Tuning Help

Apr 5, 2007

SELECT * FROM TA a WHERE a.rx=264886 and
AN= (select max(AN) FROM TA where rx=a.rx)

I have a table TA with 8+ million rows and there is clustered PK on (rx, AN) columns. The count on rx=264886 is 6000+ rows. This query takes about 1 to 2 minutes to fetch data. Can anyone suggest how to improve performance and fetch data faster?

Thanks, Vinnie

View 2 Replies View Related

DB Engine :: Query Tuning - IN Operator

Aug 26, 2015

I have a the following query which takes long time

DECLARE @ACCOUNTS TABLE(ACCOUNT_ID INT)
INSERT INTO @ACCOUNTS
SELECT ACCOUNT_ID FROM ACCOUNT
WHERE A_DESCR in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG')

I used the following key words: sql server query tuning in operator 

View 2 Replies View Related

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 View Related

Distributed Query

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

Distributed Query

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

Distributed Query

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

SQL 2012 :: Table Scan In Query Tuning

Mar 21, 2014

I am doing sp tuning. It has several lines. SO I divided into several small queries and executed individually and check the execution plans. In one small query, I found table scan is happening. That query is basically retrieving all columns from a table but the table doesn't have any pk or Indexes. So is it better to create non-clustered index to remove table sca.

View 2 Replies View Related

Distributed Query Problem

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

Distributed Query Question

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

Distributed Query Question.

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

Distributed Query Problem

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

Subquery Within A Distributed Query.. HELP!!

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

Distributed Query Issue

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

Distributed Query Problem

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

Ad Hoc Distributed Query Against MS Access

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

Distributed Query &&amp; OLE DB Provider

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

Performance Tuning Using Index. How To Force The Query Engine To Use It?

Nov 14, 2007

Hello all.
I have the following table

Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);

Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);

It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!

1. "select I_Company from item group by I_Company " - This one takes 19 seconds

2. "select distinct(I_Company) from item" -This one takes 29 secons

When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By


"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.

Is this a bug?

Thanks in advance,
TipoMan

View 4 Replies View Related

Linked Server / Distributed Query

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

Distributed Query Doing Inserts -----Very Slow

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

The Stored Procedure For Distributed Query

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

Distributed Query Performance Is ID-dependent?

Jan 26, 2007

We have an interesting performance issue with a distributed query. When run by a system-administrator account, the remote computer returns the requested row set. It does the same thing for small row counts (<=7) for user accounts. On larger row counts, however, the user account returns the ENTIRE rowset from the remote table, and performs the restrict operation locally. Performance-wise, this results in a difference between 11 seconds and 12 MINUTES. I'm assuming it's some sort of security issue in DTC, but the remote server is on Windows Server 2000, and there's no security button for DTC under Component Services.

Suggestions?

The query in question is:
EXEC ("INSERT INTO #XML (vin, ws_xml, lang_id)
SELECT slo.vin,
br.xml,
br.lang_id
FROM #SALE_LINEUP_ORDER slo
INNER JOIN RemoteServer.mydatabase.dbo.build_record br ON slo.vin = br.vin
INNER JOIN #LANGUAGE_IDS li ON br.lang_id = li.lang_id")

View 4 Replies View Related

Prob In Distributed Query On SQL 2000

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

Linked Server Distributed Query

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

Four - Part Distributed Query Is Not Working

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

How To Make Distributed Query By SQL Express

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

SQL Server 2012 :: Query Tuning With Each Run Of Fresh / Blank CACHE

Jun 4, 2015

I am using SQL Server 2012 Express.

I am doing performance tuning of SP/Query in Dev-Test environment.

I found that SQL Server caches plan between successive executions.

So if I test/execute SP 10 times, after 1st or 2nd execution, SQL server will pull-up plan-info from CACHE...Not from SQL SERVER Or Database...

Means i am not getting correct answer...

I found this 2 commands:

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

But they say that executing above command might interfere/bother other people executing other query/sp on this server.

They also say that: Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.

Part of query was using Dynamic-SQL executed with EXEC command.

I replaced that with SP_EXECUTESQL.

How can I start testing of each SP-run with Fresh/Blank CACHE ?

View 1 Replies View Related

Distributed Query To Oracle Table With Four-part Name

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







Copyrights 2005-15 www.BigResource.com, All rights reserved