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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
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
Aug 17, 2004
Hi,
I'm trying to set the value of the variable @prvYearMonth thru this sp. In the query analyzer I execute the following code to the see the results of my 'CabsSchedule_GetPrevYearMonth' SP, but the only see "The Command(s) completed successfully in the result. What am I missing??
Thanks in advance
CREATE PROCEDURE CabsSchedule_GetPrevYearMonth
(
@prvYearMonth int OUTPUT
)
AS
BEGIN
SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
GO
View 3 Replies
View Related
Jul 1, 2004
I have the following
SELECT @cLastBarcode
SELECT @tmpCount = RIGHT(@cLastBarcode, 4)
SELECT @tmpCount
SELECT @cLastBarcode returns '14001DT0010006'
BUT
SELECT @tmpCount returns nothing. The RIGHT(....) function does not render any results. I am expecting '0006'.
I read that the data type must be compatible with varchar. The @cLastBarcode was declare as char(25). I have even tried casting the @cLastBarcode char string to type varchar.
Any hints?
Mike B
View 2 Replies
View Related
Dec 12, 2007
My first foray into the SQL CLR world is a simple function to return the size of a specified file.
I created the function in VS2005, where it works as expected.
Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.
Here's the class member code:
Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long
' Returns the size of the specified file.
' Parameters: strTargetFolder = path to target file, strTargetFile = target file name.
Dim lngFileSize As Long
Dim objFileInfo As FileInfo
' Confirm file exists.
If Not File.Exists(strTargetFolder & "" & strTargetFile) Then
Return -1
End If
Try
objFileInfo = My.Computer.FileSystem.GetFileInfo(strTargetFolder & "" & strTargetFile)
lngFileSize = objFileInfo.Length
Catch
' TODO: add error handling; system folders cause error during processed.
End Try
Return lngFileSize
End Function
In SSMS (sp2), here's my assembly steps (this is my local dev machine; I have admin rights):
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
-- For file system access, set the database TRUSTWORTHY property.
ALTER DATABASE dba_use
SET TRUSTWORTHY ON
CREATE ASSEMBLY GetFolderInfo
FROM 'C:ProjectsGetFolderInfoGetFolderInfoinDebugGetFolderInfo.dll'
WITH PERMISSION_SET = UNSAFE
Here's the udf declaration:
CREATE FUNCTION dbo.udfGetFileSize( @strTargetFolder nvarchar(200), @strTargetFile nvarchar(50) )
RETURNS bigint
AS EXTERNAL NAME
GetFolderInfo.[GetFolderInfo.clsFolderInfo].GetFileSize
And the function call - note the target file is on a remote server. Actual file name differs slightly:
SELECT dbo.udfGetFileSize('\SomeServerName$MSSQL2000MSSQLData', 'SomeDBName_Data.MDF')
This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log.
The function returns correct values in VS2005.
The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.
Any assistance is greatly appreciated.
View 3 Replies
View Related
Aug 8, 2007
I posted this at the asp.net forums but somone suggested I post it here. So:
Try this in sql server:
select COALESCE(a1, char(254)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:
W
Y
Ya
Z
þ
The second one sorts like this:
W
Y
ÿ
Ya
Z
Is this expected behavior?
View 1 Replies
View Related
Jul 18, 2006
So I€™m at a dead-end looking for the reason behind the following behavior. Just to make sure no one misses it, the 'behavior' is the difference in the number of reads between using sp_executesql and not.
The following statements are executed against a SQL 2000 database that contains >1,000,000 records in the act_item table. They are run using Query Analyzer and the Duration and Reads come from SQL Profiler
SQL 1:
exec sp_executesql N'update act_item set Priority = @Priority where activity_code = @activity_code', N'@activity_code nvarchar(40),@Priority int', @activity_code = N'46DF335F-68F7-493F-B55E-5F9BC6CEBC69', @Priority = 0
Reads: ~22000
Duraction: 250-350 ms
SQL 2:
DECLARE @Priority int
DECLARE @Activity_Code char(36)
SET @Priority = 0
SET @Activity_Code = '46DF335F-68F7-493F-B55E-5F9BC6CEBC69'
update act_item set Priority = @Priority where activity_code = @activity_code
Reads: ~160
Duration: 0 ms
Random information:
Activity_code is an indexed field on the table, although it is not the primary key. There are a total of four indexes on the table, none of which include the priority as one of the fields.
There are two triggers on the table, neither of which is executed for this SQL statement (there is an IF UPDATE(fieldname) surrounding the code in the trigger)
There are no foreign relationships
I checked (using perfmon) to see if a compilation/recompilation was happening. No it's not.
Any suggestions as to avenues that could be examined would be appreciated.
TIA
View 3 Replies
View Related
Feb 17, 2005
Hi All,
I am kindly seeking for help.
I have a table(MyTable) which is defined as (date datetime, ID char (10), and R, P,M,D&Y are all float) and the layout is as following:
Date ID R P M D... Y
1/1/90 A 1 2 3 4... 5
1/2/90 A 2 3 4 5... 1
...
2/11/05 A 3 4 5 6... 2
1/1/90 B 1 2 3 4... 5
1/2/90 B 2 3 4 5... 1
...
2/11/05 B 3 4 5 6... 2
...
The expected query results look like: ( this results from Date, ID and R fields)
Date A B
1/1/90 1 1
1/2/90 2 2
...
2/11/05 3 3
The SQL I wrote:
select date, ID,
A=sum(case when ID=A then R else 0 end),
B=sum(case when id=B then R else 0 end)
from MyTable
Group by date
I would also like to get another set of results with the same format but from date,ID and P fields:
Date A B
1/1/90 2 2
1/2/90 3 3
...
2/11/05 4 4
select date, ID,
A=sum(case when ID=A then P else 0 end),
B=sum(case when id=B then P else 0 end)
from MyTable
Group by date
The problem with that is if I have thousands of ID in MyTable I have to "hard code" thousands times and the same problem with the fields/columns. Is there any easier way to do this?
I also would like to insert the results into a table/view which will be refreshed whenever MyTable gets updated.
Any suggestion/comments are highly appreciated!
shiparsons
View 4 Replies
View Related
Oct 24, 2005
I use the following sproc to populate a table that is used as the base recordset for a report.
For some reason, when the sproc is run from a scheduled job, it doesn't repopulate the table. It does, however, truncate the table. If I run it manually from query analyzer, it works fine.
I've checked all the permissions on all the object touched by the sproc, and everything looks right there. Is there another problem I should be looking for?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
setuser N'mcorron'
GO
CREATE PROCEDURE mcorron.CreateDiscOrders
AS
/*
Creates table for Orders with disc items Actuate report
*/
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
TRUNCATE TABLE dbo.rptDiscOrders
INSERT INTO dbo.rptDiscOrders
SELECT *
FROM (SELECT ORD.product as prod_XREF, ORD.ORDER_NUMB, ORD.CustName, ord.units as ordunits, INV.Product,
INV.Units
FROM (SELECT TOP 100 PERCENT f.PARENT_SITE, f.SITE, dbo.vwCustBillTo.CustName, o.ORDER_NUMB, p.Prod_Xref, o.PRODUCT,
o.ORDER_TONS * 2000 / m.part_wt AS UNITS
FROM dbo.Lawn_Orders o INNER JOIN
dbo.PRODUCT_XREF p ON o.PRODUCT = p.Product INNER JOIN
dbo.FACILITY_MASTER f ON o.WHSE = f.SITE INNER JOIN
dbo.Lawn_PartMstr m ON o.PRODUCT = m.part_code INNER JOIN
dbo.vwCustBillTo ON o.BILLTO = dbo.vwCustBillTo.BillToNum
WHERE (o.SHIP_DATE < DATEADD(d, 30, GETDATE())) and prod_xref not like 'dead%') ORD INNER JOIN
(SELECT f.PARENT_SITE, x.Prod_Xref, i. Product, SUM(i.Qty) AS Units
FROM dbo.Lawn_Inventory i INNER JOIN
dbo.FACILITY_MASTER f ON i.Whse = f.SITE INNER JOIN
dbo.PRODUCT_XREF x ON i. Product = x. Product
WHERE (f.WHSE_TYPE = 'ship')
GROUP BY f.PARENT_SITE, x.Prod_Xref, i. Product) INV ON ORD.PARENT_SITE = INV.PARENT_SITE AND ORD.Prod_Xref = INV.Prod_Xref)
ordinv
WHERE (Prod_Xref <> Product)
GO
setuser
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks
View 4 Replies
View Related
Oct 18, 2007
Do you see anything wrong with this? The first select works and finds rows the second one does not. I have opened the Key since the first query does find rows.
select *
from [dbo].[dmTable]
WHERE cast(decryptByKey(field) as varchar(50)) = 'Value'
select *
from [dbo].[dmTable]
where field = EncryptByKey(Key_GUID('CLTCadminKey'),'Value')
View 1 Replies
View Related
Feb 19, 2008
I have a stored procedure that is Averaging a Difference in dates in seconds. All of the sudden it started throwing an Arithmetic overflow error. After running the query below on the same data, I can see that it is because the DateDiff in my procedure, which is calculating the difference in seconds, is returning a value greater than 68 years. Looking at the dates in the result table, I don't see how it is coming up with the values in the Years Difference column.
Code SnippetSELECT createdate, completeddate, DATEDIFF(y, createdate, completeddate) as 'years difference'
FROM tasks
WHERE (TaskStatusID = 3) and (createdate < completeddate) and (DATEDIFF(y, createdate, completeddate)>=68)
ORDER BY completeddate
Results:
CreateDate
CompletedDate
Years Difference
2007-07-28 16:26:17
2007-10-08 20:45:19
72
2007-07-28 15:56:13
2007-10-12 19:40:28
76
2007-07-28 16:06:20
2007-10-18 21:00:05
82
2007-07-30 01:15:54
2007-10-21 20:18:43
83
2007-07-29 23:12:08
2007-10-22 23:22:47
85
2007-08-16 11:17:29
2007-10-23 15:47:32
68
2007-07-25 21:20:44
2007-10-23 19:11:32
90
2007-07-29 23:04:53
2007-10-23 21:09:47
86
2007-08-22 18:29:50
2007-11-02 14:29:12
72
2007-08-23 18:33:38
2007-11-02 14:38:34
71
2007-07-29 21:46:10
2007-11-02 23:24:39
96
2007-08-14 05:18:17
2007-11-03 21:08:30
81
View 21 Replies
View Related
Jan 15, 2007
I have an Execute SQL Task that selects one column value from one row, so General > ResultSet = Single row. Result Set > Result Name = 0 (the first selected value) and Variable Name = User::objectTypeNbr. The task runs successfully, but after the it runs the value of User::objectTypeNbr is not changed.
User::objectTypeNbr > Data Type = Int32. When I declared the variable Value could not be empty so I set it to 0 arbitraily, assuming it would be overwritten when assigned a new value by the Execute SQL Task, but it remains 0 after the task runs. What am I missing here?
View 11 Replies
View Related
Oct 11, 2007
Hi,
I am trying to convert an active x script in a script task. Below is a snippet of code. The underlined AsOfDate has a blue squiggly line under it and if I hover over it, it says "Declaration Expected."
Public Class ScriptMain
Dim AsOfDate As String
AsOfDate = Dts.Variables("MyDate").Value
...
Can someone please tell me what I'm missing? I thought maybe I'm missing an import statement, but I have:
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
I have used similar syntax in script components and it works fine.
Thanks
View 6 Replies
View Related
Oct 9, 2006
Hi,
In SQL 2005 SP1 - In my transactional replication RMO C# script, I want my snapshot job schedule to run daily at 2:58 AM.
Instead it runs hourly in the 58th minute. Sample code below shows I use the value 025800. That should be interpretted as AM. The frequencytype is daily. The frequency interval is 1. There is no subday frequency. Yet the job runs hourly and disregards the specified hour.
Is there something missing in this code? Is this a SQL Server bug?
// Set the required properties for the trans publication snapshot job.
TransPublication tpublication = new TransPublication();
tpublication.ConnectionContext = conn;
tpublication.Name = publicationName;
tpublication.DatabaseName = publicationDbName;
tpublication.SnapshotSchedule.FrequencyType = ScheduleFrequencyType.Daily;
tpublication.SnapshotSchedule.FrequencyInterval = Convert.ToInt32(0x0001);
tpublication.SnapshotSchedule.ActiveStartDate = 20051101;
string newString = "025800";
tpublication.SnapshotSchedule.ActiveStartTime = Convert.ToInt32(newString);
tpublication.Create();
View 3 Replies
View Related
Oct 25, 2007
Greetings.
What do I now have:
A directory with Access Databases; around 20 databases, all dinamicly created;
Each Database has on average 300 tables inside, all equally structured, all created by software;
Each table has two DateTime fields, 4 double fields and 4 long int fields;
Each table has around 10000 records, average.
The Directory is shared in a Windows 2003 Enterprise server.
Around 20 users access the databases simultaneously, adding, retrieving and deleting data, over 100MBits LAN.
Here's the catch:
As fast as possible, the program needs to retrieve 1 single record matching a single date from a given table in a given database. All databases work together. It needs to gets litterally thousands of individual records in order to work properly. Per user. That means thousands of requests, but not much data in each request. That's its core job.
A small percentage of request write the record back , that is, update it. Maybe 2% of requests.
If I were to reproduce this situation in a SQL server 2005, what would be the expected time for lets say 50000 requests ?
Or should I stick to Access ?
Thanks,,
Any response will be apreciated.
Pedro Ramos
View 1 Replies
View Related
Apr 3, 2007
Hi - I'm using VWD, VB, and created a dataset/tableadapter to insert a record into a SQL Express database. The database has a couple of columns, but specifically a Datetime column.
Using the default insert created, I have the following code:
Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapterProfile.partyid = da.Insert(Profile.UserName, tbName.Text, DateTime.Now)
The compiler throws an error though, saying 'Expression expected' - and it squiggles an underline under the closing bracket after DateTime.Now - I have no problem if I'm trying to update a record using:
Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapterDim pd as partyDetails.partyDetailsDataTablepd = da.GetPartyDetailsByID(Profile.partyid)da.Update(Profile.UserName, tbName.text, DateTime.Now, Profile.partyid, Profile.partyid)
Have I an error in my Insert section?
Thanks for any help,
Mark
View 1 Replies
View Related
Aug 9, 2007
Hi,
I am trying to import an excel spreadsheet to the sql server database, I have 7 spreadsheets. in that 6 of them work fine, but when i try to import the 7th i am getting and error called
External Table is not in the expected format
System.Data.OleDb.OleDbException: External table is not in the expected format
Any help will be appreciated.
Regards,
Karen
View 8 Replies
View Related
Feb 25, 2006
I was hoping that the Trim function inside the update command, cmdUpdate.Parameters.Add("@doc_num", Trim(txtDocNum.Text))
, would deal with any leading and trailing spaces, but it does not seem
to be doing anything at all. The value from the textbox still arrives
in the database table with leading spaces!!
Am I doing something wrong?
View 7 Replies
View Related
Jul 27, 2000
Hi,
I am trying to extract the non matching records and the matching ones
in two tables from two servers, one a linked server in one go.
For example if table A has records,
Rec1, Rec2, Rec3 and Rec6
AND Table B has
Rec1, Rec2, Rec3 and Rec7
I need to get in the result set
Rec1, Rec2, Rec3, Rec6 and Rec7.
The real query I ran is as follows. I want to know the list of all tables
in GlobalDB database in sg_qt1 and sg_q5 servers.
NOTE : sg_q5 is a Linked server to sg_qt1.
Select Substring(a.name,1,30), Substring(user_name(a.uid),1,19)
from sysobjects a
full outer JOIN sg_q5.globaldb.dbo.sysobjects b
ON a.name = b.name
where a.xtype = 'u' and b.xtype = 'u'
and a.name not in ('dtproperties','rowcounts','globalDBrowcounts')
If I run it from sg_qt1, the result I get contain all tables from sg_qt1
but not the non-matching ones from sg_q5.
I am obviously doing something wrong, but what is it?
Thanks.
If possible please reply to, r.wimalaratne@iaea.org
Ranjit
View 1 Replies
View Related
Nov 29, 2012
I am having a query
select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber
tarrifheadno SUM(INVOICEITEMS.ItemQuantity)
84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20
tarrifheadno is unique
Now if i use below query and add invoicetypecode field
select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode
This query return distinct of 11 records
84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2
How to get same 7 records
View 1 Replies
View Related
Apr 26, 2006
Hello,if you create this table:create table hello (int a, int bconstraint pk_hello primary key clustered ( a, b ))and then insert the following recordsa,b1,11,21,32,12,22,33,13,23,3and then doselect a,b from hellothe output seems to be:a,b1,12,13,11,22,23,21,32,33,3which is wrong and (i think) is reflecting the actual index orderand physical order on diskit should be:a,b1,11,21,32,12,22,33,13,23,3i have tested this on a table with 500,000 recordsand sure enough if you declare the clustered primary key fields inreverse order:constraint pk_hello primary key clustered ( b, a )two things happen:- the select with no order by returns the records in the expected order- queries relying on that order run MUCH FASTERhas anyone else seen / noticed this?
View 9 Replies
View Related
May 7, 2007
Hello,
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
I expect that the sorting will be performed within the entire table(data in both of groups will be sorted alphabeticaly by company name).
Instead of it, the sorting is performed independently for every group of table. What is the reason??
Here is the report
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<ConnectString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:wind.mdb</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<InteractiveWidth>8.5in</InteractiveWidth>
<Body>
<ReportItems>
<Table Name="table1">
<Left>0.375in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox3">
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>MediumTurquoise</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.5in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<TableColumns>
<TableColumn>
<Width>3.15in</Width>
</TableColumn>
<TableColumn>
<Width>1.08in</Width>
</TableColumn>
<TableColumn>
<Width>0.27in</Width>
</TableColumn>
</TableColumns>
<Height>0.5in</Height>
</Table>
</ReportItems>
<Height>0.75in</Height>
</Body>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>select * from customers where country='Mexico' or country='Austria'</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
</Field>
<Field Name="City">
<DataField>City</DataField>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
View 4 Replies
View Related
May 2, 2007
I have a package that has a container containing multiple DF Tasks.
The container is set to be Transacted, such that should any of the DF tasks fail the data inserted in any of the previous tasks rolls back.
This works as expected.
However, this container is part of a larger package and so I wanted to have a checkpoint on it, so that should any of the tasks within it fail, the package could be restarted from this container.
However, I would expect the functionality to be that on failure, the checkpoint would cause the whole container to be started again (because the container is transacted all DF task info would be rolled back) so we would expect it to start at task 1 again.
This is not the functionality I see. The package restarts from the failed task within the container every time.
According to the book Prof SSIS, it should start again from the first task and as explained this makes sense on a Transacted container as you would want this to happen.
A previous forum message encountered the same issue it appears:
See SSIS Checkpoints 04 Dec 2006.
This is an extract from it:
"I only experimented a little but my experience was that when I have a transacted container with multiple tasks that are checkpointed, SSIS would try to restart from the task that failed rather than from the first task in the container. The transaction was being rolled back correctly though.
In short, I felt that check points were not aware of transactions.
So, I ended up with this setting and it works for me:
Container is checkpointed and trasacted.
Tasks within the container are not checkpointed.
'FailParentOnFailure' property set to True on the tasks.
That way, if a task failed, it would fail the container and a checkpoint would be created at that level. Transaction would be rolled back as usual."
While this makes sense to me it is not the same properties that the SSIS book has that work.
Additionally, this didn't work for me either !!
I have tried every combination of FailPackageOnProperty and FailParentOnProperty that makes sense but every time the package restarts from the failed container within the task.
The transaction is rolled back correctly every time, but it seems the checkpoint that is created is not used correctly when dealing with transactions within containers.
View 1 Replies
View Related
Mar 30, 2007
Hi,
I have a ForEach Loop that has 3 script tasks in it.
I have them set up so that they execute in order, such as:
script1 ---> script2 ---> script3
script1 creates a file
script2 creates a file
script3 compares the files using a diff command
Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.
The error is "The system cannot find the file specified".
Thanks
View 2 Replies
View Related
Nov 28, 2007
I'm reading a lot of data from a database using SqlDataReader. Now I'd like to report the progress to the user, but for this I need to know in advance how many items SqlDataReader will return.
Is there any way to get this 'number of expected results' from the SqlDataReader?
tia,
Sam
View 1 Replies
View Related
Aug 20, 2007
I have a simple column chart with point labels. The values are displaying at a -90 angle. I want the values to display in the center of the column, but when I choose the center position, it is displaying at the top; half of the value is in the bar and the other half above. Why?
View 2 Replies
View Related
