I have a series of records that are timestamped and ordered by time ascending. i.e.
Time
TimeToNext
02/07/2007 11:38:22
0:00:21
<--- Need to calculate these
02/07/2007 11:38:43
0:00:22
<--- Need to calculate these
02/07/2007 11:39:06
0:00:00
<--- Need to calculate these
02/07/2007 11:39:06
0:00:36
<--- Need to calculate these
02/07/2007 11:39:42
0:00:28
<--- Need to calculate these
02/07/2007 11:40:10
0:00:26
<--- Need to calculate these
02/07/2007 11:40:36
Last Record
<--- Need to calculate these
I need to calculate the difference in time between each of these records. The flow should go like this (psuedo):
1. Read FirstRecord
2. Read SecondRecord
3. Set FirstRecord.TimeToNext = SecondRecord.Time - FirstRecord.Time
4. Output FirstRecord
Repeat for all rows in buffer (i.e. SecondRecord.TimeToNext = ThirdRecord.Time - SecondRecord.Time)
I am sure there is an easy way to do this with a script component, but I haven't been able to figure it out, and my head hurts from smashing it against my desk.
I have a COM object that is written using Visual Basic 6. This is referenced in a .NET Stored Procedure. But when I execute the stored procedure I get an error:
Msg 6522, Level 16, State 1, Procedure prCalculator_ExecCalc, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'prCalculator_ExecCalc':
System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {844E8165-ABC1-432B-9490-51B1A6D91E71} failed due to the following error: 80040154.
System.Runtime.InteropServices.COMException:
Here is what I do:
1. Compile the VB DLL.
2. Convert into a .NET assembly by importing to a Visual Studio 2005 project. Sign the interop assembly.
3. Register it as an assembly in SQL 2005 server.
4. Create a .NET stored procedure and reference the above assembly. Compile this assembly again in SQL 2005 and create a stored procedure using the assembly. This assembly is also signed.
Please Note:
1. All the assemblies are registered with UNSAFE permissions.
2. They are compiled with COM Visible flag in Visual Studio 2005.
3. This works perfectly on my local SQL Express where I have Visual Basic/VisualStudio 2005 installed.
4. I get this error, when trying on Test Server. I tried to install the VB Runtime on this machine and still does not work.
I have a requirement to access a lookup table from within an SSIS Transform Script Component
The aim is to eliminate error characters from within the firstname, lastname, address etc. fields by doing a lookup of an ASCII code reference table and making an InStr() type comparison.
I cannot find a way of opening the reference data set from withing the transform.
Hi, I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables. Those values are not needed immediately. The calculation takes between 20 or 30 minutes. Do you have any idea how to do it ? Thanks
My enterprise is looking into migrating to SSRS 2005 and I am trying to gather as much information as possible. One area that I've been unable to find much information is around the area of process (or report request) throttling inherent to SSRS. We are a large organization and the reporting technology that we choose must be able to process thousands of report requests a day. In our current solution, we have the ability to throttle the report requests so that a limit is placed on the number of report request that can be processed concurrently so that the reporting infrastructure is not overwhelmed during peak load times. Subsequent request are queued for processing at a period when there is less load on the servers. Does SSRS have this capability and is it configurable? Is there any detailed information (articles, whitepapers, books) available addressing this subject?
This leads me to a second question concerning an asynchronous reporting solution. Has anyone developed a solution using SSRS that allows long running reports ( that are requested online) to be processed on a background thread and has the background thread alert the user when the report has completed? This is a nice feature of our current solution but I have not been able to find a comparable solution utilizing SSRS. There looks like there might be possibility in the Subscription capabilities but I€™m not sure how this would work online requested reports.
Over the past week and a half we started experiencing a sporadic slowdown in our production x64 SQL 2005 Ent. Edition server. Users started complaining of slowness then they started getting timeouts. In looking at sp_who2 and perfmon we saw the following during the slow/frozen periods: * Dramatic increase in Perfmon Active Transactions * CPU higher than norm, but not dramatically so * sp_who2 shows a number of spids in SUSPENDED state (and not running waits) * no blocking indicated from sp_who2 * active connections slowly increasing * no disk queuing (or at most some spikes to 1) After a couple of minutes of this we would then see the following: * no more spids in SUSPENDED state * Logins per second spikes dramatically * Active transactions spikes down to "normal levels" * CPU goes high then levels out at moderately higher than normal * active connections slowly decreases back towards normal levels * large spike in lock wait time
We turned on the Async Auto Update Statistics option (after testing in our staging environment) on the primary database about a week before we saw this problem. By turning it off we can visually see the problem go away by watching the above metrics. So my question is, What metrics can I use to see the "blocking" or resouce locking that is causing these problems? Anyone? Thx Ron
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type. On exiting from the script the variable of type object should contain something like in the following lines: AAAAAAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDDDDDDDDDDD €¦€¦€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦€¦. On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion. Is there anyone who have experienced something like this? Could anyone provide any example of that? Thanks in advance!
Hi all I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too. Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?? (I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain Public Sub Main() Try Dim xmlDoc As New XmlDocument xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml") MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up" Catch ex As Exception MsgBox(ex.Message) UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message) End Try Dts.TaskResult = Dts.Results.Success End Sub Private Sub UpdateXML(ByVal fileName As String, ByVal message As String) Try Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4) Dim rd As StreamReader = New StreamReader(fileName) Dim xml As String = rd.ReadToEnd() Xml = Xml.Replace(invalidChar, String.Empty) xml = xml.Replace("", String.Empty) xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[") xml = xml.Replace("]]>]]>", "]]>") MsgBox("replaced") rd.Close() Dim wr As StreamWriter = New StreamWriter(fileName) wr.Write(xml) wr.Close() Dim xdoc As XmlDocument = New XmlDocument() xdoc.Load(fileName) Catch ex As Exception UpdateXML(fileName, ex.Message) End Try End Sub End Class
Hi. Is it possible in SQL query to find record previous or next in comparison with record found with clause WHERE (example of query below)? I need to find record with ProblemID less than or greater than 10. Regards Pawelek. SELECT ProblemIDFROM dbo.tblProblemsWHERE (ProblemID = 10)
I'm kindof at a lose for how to ask my database to show me the last payment amount and payment date for clients and also show me the previous payment amount and payment date for clients based on a date range.
Could anyone offer any pointers or examples?
Thank you very much.
We could use a very simple table and field setup like this just so you could show an example:
I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end, (SELECT B.Speedo_end FROM Daily_Trolley AS B WHERE B.Trolley_num = A.Trolley_num AND B.Date = (SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num And C.Date < '1/23/2005')) AS PrevSpeedoEnd FROM Daily_Trolley AS A WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.
I have a table of vehicle usage records with fields including vehicle number, driver, date/time, starting odo, ending odo. I'm looking to compare the starting odo of a given record to the ending odo of the last time that vehicle was used. I also need to return other fields from that previous record, like the date/time and driver.My users basically run a report for any given day, and the vehicles used on that day need to be compared to their most recent usage (most recent relative to the record at hand). This is to ensure that the vehicle hasn't been used in the interim and not recorded (which may indicate theft).I've got a very convoluted process in place, but I'd like to see if it can be streamlined. The current process is done in Access and has a number of queries built on other queries. It is very slow. The data is in SQL Server. Any thoughts on the best ways to accomplish this?TIA
Ok so someone answered my last quesiton about finding what tables are in a Stored procedure. Is there an opposite of that. If I wanted to type in a table and it shows all the stored procedures that use that table.
Please see DDL and INSERT statements below.Let's say that some process throws out the second row, where theClocktime = '02/01/2005 12:34'Without the use of a cursor, how can I retrieve the PREVIOUS value forthat employee? Pseudo SQL might be something like:SELECT*FROMtblTestWHEREfldCLocktime = THE-ONE-IMMEDIATELY-BEFORE '02/01/2005 12:34'ANDfldEmployeeID = 1TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTest]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTest]GOCREATE TABLE [dbo].[tblTest] ([fldTestID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldClocktime] [datetime] NULL ,) ON [PRIMARY]GOINSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'01/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'02/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'03/01/2005 12:34')
Has anyone used the 'previous' function to return the previous row of data? I have tried this =previous(Fields!SomeColumn.value) - when I run the report, no data returns.
Newbie question. I am using a query that pulls month-to-date data that has the following where clause:
WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE()))
this works just fine but what I would like for it to do is give me the previous month of data if the if it's the first day of the month and then any other day give me month to date. Is this possible?
Let me explain. When retrieving a row, an extra column should be added.It's value should be the sum of previous rows whose type is the same with the encountered one. I made it with a function but it's performance was terible with large tables. I have tables larger then fifty housands rows.
How to Get previous record thru sql query For the example my table: 1 usera item1 1.00 01/02/072 usera item1 2.00 02/02/073 userc item2 3.00 03/02/07 --how to use the query to make them join became like this (get/join with the next record) 1 usera item1 1.00 01/02/07 item1 2.00 02/02/073 userc item2 3.00 03/02/07 null null null >.<need help ... thanks alot
currently have my website that is functional...but would like to add some navigation to my photos which are displayed using a repeater tied to a SQL database. (live example can be viewed here) the above link shows a gallery page that displays all the photos within a given shoot (using a querystring to display the unique ID of the photo shoot). When you click on one of the images within the shoot a page called is called displaying the selected photo by passing the shoot ID and the unique ID of the photo itself through a querystring. I would like to add navigation to the page displaying the photo that would allow the user to go to the next photo, previous photo, first and last phot within the specific SHOOT ID. FirstNextPreviousLast I am using a repeater to display the photo with the following sqlDataSource: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connection %>"SelectCommand="SELECT usr_Photos.*, usr_PhotoShoot.* FROM usr_Photos INNER JOIN usr_PhotoShoot ON usr_Photos.Shoot_ID = usr_PhotoShoot.Shoot_ID WHERE usr_Photos.Photo_ID = @ID AND usr_Photos.Shoot_ID = @Shoot"><SelectParameters><asp:QueryStringParameter DefaultValue="" Name="ID" QueryStringField="ID" /><asp:QueryStringParameter DefaultValue="" Name="Shoot" QueryStringField="Shoot" /></SelectParameters> </asp:SqlDataSource> I am having trouble figuring out a way to add the functionality i would need to change photos within the specific photo shoot without having to go back to the gallery page. any help or ideas you could point me in would be great...thanks in advance...
I use the following sql in a view to return the last payment date and amount made by clients. What I need is a way to return the payment date and amount for the payment prior to the last one. Any help is appreciated very much, SELECT dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate AS LastPayDate, SUM(dbo.tblPaymentReceipts.AmountPaid) AS LastPayAmtFROM dbo.tblPaymentReceipts INNER JOIN (SELECT Client_ID, MAX(PaymentDate) AS LastPayDate FROM dbo.tblPaymentReceipts AS tblPaymentReceipts_1 GROUP BY Client_ID) AS A ON dbo.tblPaymentReceipts.Client_ID = A.Client_ID AND dbo.tblPaymentReceipts.PaymentDate = A.LastPayDateGROUP BY dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate
