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.
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.
(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
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.
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 :/
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
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.
-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.
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 ?
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
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.
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
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.
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 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.
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
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
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')
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
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?
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.
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();
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 ?
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
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
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!!
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
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
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
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?
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:
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.
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".
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?
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?
