Query Improvement

Nov 14, 2007

In our business we receive debt from clients that we work in order to try and reclaim some of the debt on their behalf. When a debt file comes in from a client it is loaded onto the system and every entry in that debt file is stamped with the same batch id. Using the batch id we track each debt file and monitor how well we are working that debt file. So as an example we could receive a debt file with 100 records in it each of these records would be stamped with a batch id of say 100001, the next file that is loaded onto the system, each record would then be stamped with 100002. so we can track how each batch is doing by grouping by batch id.

I have written my query that basically groups all the accounts on the system by batch id. This is fine when I only want to return totals and sums in the select list. The way I have the written the query If I want to calculate the payments we have received for each batch or the commission we will make on the payments (or any other info per batch) I have to use subquerys in the select list using the batchid I have grouped by . Is this the best/only way to achieve what I want to do.


Any input most welcome.



sql below:




Select c.Name,ib.ImportBatchID BatchID,
---------------------------------------------------------------
CONVERT(VARCHAR(10),ib.UpdatedOn,103) LoadedOn,
---------------------------------------------------------------
Count(d.DebtID) Reg_Count, --No. Reg
---------------------------------------------------------------
Sum(d.DebtValue) Reg_Value, --Value Reg
---------------------------------------------------------------


(Select sum(da.Amount) from dbo.DebtAdjustment da WITH (NOLOCK), dbo.ImportBatchItem bi WITH (NOLOCK) where
bi.ItemID=da.DebtID And bi.ImportBatchID=ib.ImportBatchID) Adjustments,
---------------------------------------------------------------
(Select count(dh.DebtID) from dbo.Debthistory dh WITH (NOLOCK), dbo.ImportBatchItem bi WITH (NOLOCK) where --No. OBC's
bi.ItemID=dh.DebtID And dh.Note like 'OBC:%' And bi.ImportBatchID=ib.ImportBatchID) OBC_Num,
---------------------------------------------------------------
(Select count(dh.DebtID) from dbo.Debthistory dh WITH (NOLOCK), dbo.ImportBatchItem bi WITH (NOLOCK) where --No. ICC's
bi.ItemID=dh.DebtID And dh.Note like 'ICC:%' And bi.ImportBatchID=ib.ImportBatchID) ICC_Num,
---------------------------------------------------------------
(Select count(dh.DebtID) from dbo.ImportBatchItem bi WITH (NOLOCK), dbo.DebtHistory dh WITH (NOLOCK)
Where dh.DebtID=bi.ItemID AND bi.ImportBatchID=ib.ImportBatchID AND dh.UserName='Letter Server' AND dh.Note like '%Letter%') ItemsMailed,
---------------------------------------------------------------
Cast((Select sum(CASE
WHEN dp.ReceivedByID = 1
THEN dp.Amount * (tF.Rate /100)
WHEN dp.ReceivedByID = 2
THEN dp.Amount * (tD.Rate /100)
ELSE
dp.Amount * ((tF.Rate + tFe.Rate) / 100)
END)
From
dbo.DebtPayment dp JOIN dbo.Debt d ON d.DebtID=dp.DebtID
JOIN dbo.ImportBatchItem bi ON dp.DebtID=bi.ItemID

LEFT JOIN dbo.mTrackerFeeChange tF ON tF.ClientID=d.ClientID
AND tF.ContractID=d.ContractID AND dp.ReceivedByID=tF.RateType
AND (
(dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate)
OR
(dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL)
)

LEFT JOIN dbo.mTrackerDirectChange tD ON tD.ClientID=d.ClientID
AND tD.ContractID=d.ContractID AND dp.ReceivedByID=tD.RateType
AND (
(dp.PaymentOn >= tD.StartDate AND dp.PaymentOn <= tD.EndDate)
OR
(dp.PaymentOn >= tD.StartDate AND tD.EndDate IS NULL)
)

LEFT JOIN dbo.mTrackerFieldChange tFe ON tFe.ClientID=d.ClientID
AND tFe.ContractID=d.ContractID AND tFe.RateType=dp.ReceivedByID
AND (
(dp.PaymentOn >= tFe.StartDate AND dp.PaymentOn <= tFe.EndDate)
OR
(dp.PaymentOn >= tFe.StartDate AND tFe.EndDate IS NULL)
)
where bi.ImportBatchID=ib.ImportBatchID) AS decimal(10,2)) ComRate,

---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 1 AND Debt.OutstandingValue > 0) Girobank,
---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 2 AND Debt.OutstandingValue > 0) StandingOrder,
---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 3 AND Debt.OutstandingValue > 0) CreditCard,
---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 4 AND Debt.OutstandingValue > 0) DirectDebit,
---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 5 AND Debt.OutstandingValue > 0) Cheque,
---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 6 AND Debt.OutstandingValue > 0) PostalOrder,
---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 7 AND Debt.OutstandingValue > 0) Cash,
---------------------------------------------------------------
(SELECT sum(Debt.OutstandingValue) TotalValue From Debt WITH (NOLOCK), DebtStatus WITH (NOLOCK), ImportBatchItem bi WITH (NOLOCK)
WHERE Debt.Status=DebtStatus.DebtStatusID AND bi.ItemID=Debt.DebtID And bi.ImportBatchID=ib.ImportBatchID AND
DebtStatus.Description LIKE ('%' + 'Arrangement' + '%') AND Debt.AgreementPaymentMethodID = 8 AND Debt.OutstandingValue > 0) BankersDraft
---------------------------------------------------------------



From
dbo.Client c WITH (NOLOCK), dbo.Debt d WITH (NOLOCK), dbo.ImportBatchItem ib WITH (NOLOCK)
Where
c.ClientID=d.ClientID
AND
d.DebtID=ib.ItemID
AND
(@ClientID IS NULL OR c.ClientID = @ClientID)
Group by c.Name, ib.ImportBatchID,CONVERT(VARCHAR(10),ib.UpdatedOn,103),DATENAME(Month, ib.UpdatedOn) + ' ' + DATENAME(Year, ib.UpdatedOn)
Order by ib.ImportBatchID

View 2 Replies


ADVERTISEMENT

SQL - Query Improvement MAX(date), IN

Jul 23, 2005

Hi,I wonder if you could shed some light into this.I have the following table.Id, ContentId, VersionDate, ContentXmlThere are several ContentIds in the table.SELECT *FROM tblVersionsWHERE (VersionDate =(SELECT MAX(tblVersions2.VersionDate)FROM tblContentVersion AStblVersions2WHERE tblVersions2.ContentiD =tblVersions.ContentiD))ORDER BY ContentIdThis query works to select the latest versions (MAX) of every content,but I do not like it, any other way to do this properly?I also want to do this knowing a set of ids (probably using IN )SELECT *FROM tblVersionsWHERE (VersionDate =(SELECT MAX(tblVersions2.VersionDate)FROM tblContentVersion AStblVersions2WHERE tblVersions2.ContentiD =tblVersions.ContentiD AND tblVersions.ContentiD IN (1, 2, 3, 6, 7, 8)))ORDER BY ContentIdAny ideas for improvements on this query?ContentXml is of ntext typeThanks,/ jorge

View 4 Replies View Related

DTA: Expected Improvement 0%

Dec 31, 2007

I did a trace on a production DB for many hours, and got more than 7 million of "RPC:Completed" and "SQL:BatchCompleted" trace records. Then I grouped them and obtained only 545 different events (just EXECs and SELECTs), and save them into a new workload file.

To test the workload file, I run DTA just for 30 minutes over a restored database on a test server, and got the following:
Date 28-12-2007
Time 18:29:31
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time 31 Minutes
Time taken for tuning 31 Minutes
Expected percentage improvement 20.52
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 8116
Number of events in workload 545
Number of events tuned 80
Number of statements tuned 145
Percent SELECT statements in the tuned set 77
Percent INSERT statements in the tuned set 13
Percent UPDATE statements in the tuned set 8
Number of indexes recommended to be created 15
Number of statistics recommended to be created 50
Please note that only 80 of the 545 events were tuned and 20% of improvement is expected if 15 indexes and 50 statistics are created.

Then, I run the same analysis for an unlimited amount of time... After the whole weekend, DTA was still running and I had to stop it. The result was:
Date 31-12-2007
Time 10:03:09
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time Unlimited
Time taken for tuning 2 Days 13 Hours 44 Minutes
Expected percentage improvement 0.00
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 7534
Number of events in workload 545
Number of events tuned 545
Number of statements tuned 1064
Percent SELECT statements in the tuned set 71
Percent INSERT statements in the tuned set 21
Percent DELETE statements in the tuned set 1
Percent UPDATE statements in the tuned set 5
This time DTA processed all the events, but no improvement is expected! Neither indexes/statistics creation recomendation.

It does not seem that Tuning Advisor crashed... Usage reports are fine and make sense to me.

What's happening here? It looks like DTA applied the recomendations and iterated, but no new objects where found in DB.

I guess that recomendations from the first try with only 80 events were invalidated by the remaining from the long run.

I couldn't google an answer for this. Help!!!

Thanks in advance.

++Vitoco

View 1 Replies View Related

Improvement Over Subquery?

Feb 15, 2006

I am in the process of evaluating 2005 to upgrade a DTS and A/S solution for accounts receivable data imported from a legacy application on various AS/400s in our enterprise. Our current process uses staging and mart databases with views providing a layer of abstraction. Instead of using upgrade wizards, I would prefer to implement a "ground up" rearchitecture that better incorporates the 2005 toolset.

One of the challenges is building a fact table for historical a/r balances since the legacy application will only provide transactions, meaning that there is no data entry for a month where there might be a balance, but no transaction (payment, invoice, credit, etc) occurred. Our current implementation uses a date table as its base, perfoming a left join to the transaction table. I have to use subqueries to calculate previous balances, which is also used to calculate ending balance.

Should I replace the current view with data flow logic that would perform this calculation? If so, what would be the best method? A merge join? A calculated column? If it helps to provide sample schema snipets, I would be happy to do so.

View 1 Replies View Related

Improvement Sysdtslog90?

Apr 12, 2007

hello



im trying to create a ssis monitoring tool (using sysdtslog90, reporting services and system tables) including sql server agent ssis schedules/history. but im having some problems evaluating sysdtslog90. im using the built in SSIS logging without any custom logging tasks (i only log OnError and OnWarning) to keep the logging table as small as possible (one sysdtslog90 table for all packages running on the server).

and there is the problem:

there are some executions without any events except OnError. the source of this event is the name of the failed task, so how do i get the name and id of the failed package?



"normal" executions have "PackageStart" and "PackageEnd" where the sourceid and source are equal with the id and name of the package



i could add logging for OnPreValidate and catch the first line, because no package can fail before validation!?

but isnt there any better solution?

i would love it to have two more columns in sysdtslog90.. package id and name. would be much easier to evaluate sysdtslog90 :/



- paul



View 8 Replies View Related

Replication Performance Improvement

Oct 26, 1999

Can anyone suggest hwo can I improve the performance of the replication process and make it faster.
Pran

View 1 Replies View Related

DB2 64-bit Driver: Big Performance Improvement!

Aug 31, 2007

After reading an article today on SQL Server Central about choosing the best connectors for ssis (obviously written to drive sales, but hey it looks like it's working) I downloaded an evaluation copy of DataDirect's 64-bit DB2 driver and started some preliminary testing.

http://www.sqlservercentral.com/columnists/mfrost/3209.asp

Here are some inital findings/opinions:

-Rather easy to install (downloaded, briefly read documentation, and was up and running in about 10 mintues)
-With Microsoft's IBM OLE DB provider for DB2 (32-bit) we can extract 1 million records in approx. 9 minutes
-With the 64-bit driver we can extract 1 million records in approx. 2.25 minutes! 4 times as fast as the 32-bit driver!
-Was unable to get the driver to work through linked server. Tech support opened an issue to look into it.
-Sales rep didn't have exact pricing, but she thought they charged per core.
-DataDirect also has 64-bit Oracle and Sybase drivers available.

For those of you extracting large amounts of data from DB2, Oracle, or Sybase with SSIS and are looking to improve performance I'd recommend at least checking this product out.

Also, I'd be interested to hear if anyone else started testing this or any other 64-bit driver for DB2.

View 2 Replies View Related

How Can I Use Dual Core CPU For Performance Improvement

Jul 17, 2007

Hi,



I am running MSSQL 2005 Standard edition on a two processor Intel Xeon 3GHz (dual-core) with 8GB RAM.



I notice in "Windows task manager CPU performance" while running a long SQL statement (takes 1.5 hours), only 1 logical (out of 4) is utilised at >70%. The remaining 3 logical processors hover around 10%



Using Performance monitor, the average read queue, write queue, and pages/sec also hovers around 25%, indicating no heavy physical disk/memory loading.



How can I set to utilise more physical/logical processor to improve the MSSQL performance ?



Thanks.

View 2 Replies View Related

SQL Tools :: How To Disable Customer Improvement Program From Menu For Server MGMT Tools 2012

Mar 25, 2014

Am customizing SQL server MGMT tools 2012 for Mass deployment.Client had asked to remove Customer Feedback option from help menu.how to disable that.

View 6 Replies View Related

Query Runs Fine In Query Analyser But Not The Query Debugger

Dec 19, 2003

I'm running a query, actually its an insert that works when using the TSQL below.

However when I try to use the debugger to step through and using the exact same values as those below I get the following error:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Its Killing me because everything else works, but this. Can somebody help.


DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,

@BeenRead NVARCHAR(10),-- = NULL

@FK_UserIDList NVARCHAR(4000)-- = NULL


--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'

SET @FK_UserIDList = '1,2,3'


--AS

--SET NOCOUNT ON


SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

SET @Date = GETDATE()

-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)

SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,

@Job_Date,
@Start,
@Finish

-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY


-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList

-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT

COMMIT TRANSACTION

--------------------------------------------------
GO

View 1 Replies View Related

Query Diff Results From Ent Manager Query And Query Analizer

May 28, 2008

ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)

SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007

but in query analizer I get the result of

12/31/2006

Why the different dates

View 4 Replies View Related

[Query] - Query Designer Encountered A Query Error:Unspecified Error

Jan 22, 2001

Hi,

I get this error dialog when I try to open all the rows of any table from Enterprise manager..

Any help would be really appreciated..

Thanks,
-Srini.

View 1 Replies View Related

Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.

May 24, 2007

SQL Server 2005 9.0.3161 on Win 2k3 R2



I receive the following error:



"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."



I have traced this to an insert statement that executes as part of a stored procedure.



INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)



There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.



Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

View 5 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager

Jun 15, 2007

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.



Thanks in advance



Daniel Buchanan.



If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

View 1 Replies View Related

Master Data Services :: Error - Query Processor Could Not Produce A Query Plan

Jul 19, 2015

We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T

• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues

• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"

• We are using the last version of Excel Add-in

• We try to reinstall the MDS feature

• If I backup/restore MDS database to other server it works

• We updated to SQL 2012 SP2 + CU4 but the error persisted ...

Looking at the MDSTraceLog we are routed to the this msg

SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28

At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped

** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
   at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
   at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)

[code]....

View 3 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?

Apr 30, 2007



Hello everybody,



I'm developing a report using the following structure :



declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)



set @sql = 'select ....'



If <conditional1>

begin

set @where = 'some where'

end



If <conditional2>

begin

set @where = 'some where'

end



set @sql = @sql + @where



exec(@sql)



I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.



I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?



Thanks,



Maurício

View 2 Replies View Related

DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan

Oct 6, 2015

SQL Server 2012 Performance Dashboard Main advices me this:

Since the application is from a vendor and I have no control over its code, how can improve this sitation?

View 3 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?

Nov 15, 2007

do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like

SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)

View 1 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Mar 25, 2008

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:


USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.

Thanks in advance,
Scott Chang

View 4 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

Easy SQL Question. How To Display Query Results In Query Analyzer

Feb 12, 2008

When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Sniier */SELECT TOP 1       @SniierName      = Sniiers.SniierName,        @SniierAlias    = Sniiers.SniierAlias,        @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId

View 3 Replies View Related

Query Designer Encountered A Query Error Via Enterprise Manager

Jan 7, 2002

I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error.
We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database
maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go
into production in a few days.

View 2 Replies View Related

Stored Procedure Query Optimization - Query TimeOut Error

Nov 23, 2004

How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :

CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN


SET NOCOUNT ON

SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec

RETURN 1
END



GO

View 8 Replies View Related

A Query Runs Fast In Query Analuser But Slow In APplication

Jul 23, 2005

I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay

View 2 Replies View Related

The Merge Process Failed To Execute A Query Because The Query Timed Out

Dec 22, 2006

Hi there. I have occasional sincronization error.-XSUBSYSTEM Merge
-XSERVER MYSERVER
-XCMDLINE 0
-XCancelEventHandle 000006E0
2006-12-22 14:55:00.833 Connecting to Subscriber 'Subscriber01'
2006-12-22 14:55:00.895 Connecting to Distributor 'Publisher01'
2006-12-22 14:55:02.974 Initializing
2006-12-22 14:55:03.083 Connecting to Publisher 'Publisher01'
2006-12-22 14:55:06.005 Retrieving publication information
2006-12-22 14:55:06.130 Retrieving subscription information.
2006-12-22 15:00:07.222 The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2006-12-22 15:00:07.456 Error converting data type nvarchar to numeric.
2006-12-22 15:00:07.800 Category:SQLSERVER
Source: Subscriber01
Number: 8114
Message: Error converting data type nvarchar to numeric.

After manual syncronization it goes under control.

Could anybody explain the relationship between converting error and timed out query?

If this is poor connection's problem how can I increase the query timeout for the process?

View 5 Replies View Related

SQL 2005 V9.0.2047 (SP1) - The Query Processor Could Not Produce A Query Plan

May 15, 2006

Hi Everyone:

*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error

"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I would have searched the forums myself, but at this moment in time, search is broken :(

If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.

It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.

fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.

Thanks in advance for other suggested examination paths.

View 10 Replies View Related

Prevent Query-Analizer From Caching The Query Execution?

Apr 11, 2008

Hi all I'm using Sql server 2000 and sometimes i need to run my Queries in Query analizer before using them in my application just to test them...BUT most of the time when i run a query in query-analizer for second time ,query analizer populates the result (records) more quicker then the first time. Apparently it caches the query !!! i don't know but for some reasons i dont't want this , so how can i prevent Query-analizer from doing so?
Thanks in advance. Regards.

View 4 Replies View Related







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