I am having trouble coming up with a query to extract the min and max time for each Task.. I am using Teradata SQL. The data below shows two tasks which both have the same File and Task ID. Each task starts with a Submitted Status and ends with a Rejected Status. What I need is to get the min and max time for each one of those tasks.. Unfortuneately my data below consists of values that don't have a unique identifier that I can use to split the tasks.
How do I extract the ate and time from a datetime field. The field is called Log_DateTime. I want to extract the date and the time and then use these two expressions to sort/filter on in Query Analyzer.
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
While executing the tasks, I got the error Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
Hi i have a query select 12 * int( "SQLUSER"."BTT120".yyyy) + int("SQLUSER"."BTT120".mm) as actual, 12*year(CURRENT DATE) + month(CURRENT DATE) as ending, 12*year(CURRENT DATE) + month(CURRENT DATE)-3 as starting from "SQLUSER"."BTT120" where "SQLUSER"."BTT120".yyyy ='2007' and "SQLUSER"."BTT120".mm in ('10','11','12')
which gives me ourput
Actual ending Starting 24094 24097 24094
My actual requirment is to get the data for the rolling 3 months for which i used the same logic as above in the WHERE clause.
SELECT "SQLUSER"."BTT120".YYYY CONCAT "SQLUSER"."BTT120".MM AS MO_YR FROM "SQLUSER"."BTT120" where (12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) between (12*year(CURRENT DATE)) + month(CURRENT DATE)-3 and (12*year(CURRENT DATE)) + month(CURRENT DATE)
everything looks good but iam not getting output, there is data for the date range
Hello,Please see the original data below. I would like to extract the accountwith the earliest opening date only, and leave all the accounts openedafter that out. Could anybody help me with the query? Thanks a lot!Original Dataperson_idaccountopen_date1000111111115/15/20031000122222226/20/20041000133333332/16/2005Ideal Outputperson_idaccountopen_date1000111111115/15/2003
Hi, I have a task that requires me to pull a list of tables used in a select query and put them into a string array. For arguments sake, let's say this is my query: select * from table1 tb1, table2 tb2 where ...
I can easily extract the 'from 'clause, in this case: table1 tb1,table2 tb2, but I still have to split out the Alias before I can use it. Can anyone show me a regex that can do this instead?
I have a table, three records of which look like this:
ID PersonID FirstName LastName PostCode 1 999 Barry White BW13 8GS 2 999 <null> <null> BW13 9GS 3 999 <null> Whites <null>
Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
PersonID FirstName LastName PostCode 999 Barry Whites BW13 9GS
in other words, the most recent information we have for that person.
Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!
Hi , is it possible extract file name from script executed in query analyser ?
Example steps
1. open QA 2. open c:mySuperScript.sql code in c:mySuperScript.sql use master select @filename as [file executed] select count(*) as xfrom syslogins 3. run
-- result file executed ------------ c:mySuperScript.sql
x -- 112
Why we need it. Each database update created using sql compare tool from red-gate Script saved in Vss When script executed in production using QA we save output as proof of deployment currently we add to each script
select 'script= put you file name'+ char(13)+ 'server = ' + cast(@@servername as char(20))+ char(13)+ 'dbname= ' + cast(DB_NAME()as char(20)) + char(13) + 'execution date= '+ cast(getdate()as char(24))+ char(13)+ 'executed by = '+ system_user
We want wrap everything in procedure that will extract executed file name and other values
this procedure should be first line of code in each script
We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.
Table_Name = varchar(20) Time_Start = DateTime Status varchar(7) = Success or Error Duration = Number Time_End = DateTime
Select Table_Name into #MyTempTable From ETL.STATS_Table Where Status = 'Error' AND Cast(Time_Start as Date) = GetDate()
How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
Just a quick question, I've got an execute SQL task in the control flow and just want to know if anyone knows how to make it time out like you can do with OLEDB commands in the data flow. if it's sitting there waiting for something I want it to fail after x seconds.
Select 'PIT_ID' = CASE WHEN Best_BID_DATA.PIT_ID IS NOT NULL THEN Best_BID_DATA.PIT_ID ELSE Best_OFFER_DATA.PIT_ID END, Best_Bid_Data.Bid_Customer, Best_Bid_Data.Bid_Size, Best_Bid_Data.Bid_Price, Best_Bid_Data.Bid_Order_Id, Best_Bid_Data.Bid_Order_Version, Best_Bid_Data.Bid_ProductId, Best_Bid_Data.Bid_TraderId, Best_Bid_Data.Bid_BrokerId, Best_Bid_Data.Bid_Reference, Best_Bid_Data.Bid_Indicative, Best_Bid_Data.Bid_Park, Best_Offer_Data.Offer_Customer, Best_Offer_Data.Offer_Size, Best_Offer_Data.Offer_Price, Best_Offer_Data.Offer_Order_Id, Best_Offer_Data.Offer_Order_Version, Best_Offer_Data.Offer_ProductId, Best_Offer_Data.Offer_TraderId, Best_Offer_Data.Offer_BrokerId, Best_Offer_Data.Offer_Reference, Best_Offer_Data.Offer_Indicative, Best_Offer_Data.Offer_Park
from ( Select PITID PIT_ID, CustomerId Bid_Customer, Size Bid_Size, Price Bid_Price, orderid Bid_Order_Id, Version Bid_Order_Version, ProductId Bid_ProductId, TraderId Bid_TraderId, BrokerId Bid_BrokerId, Reference Bid_Reference, Indicative Bid_Indicative, Park Bid_Park From OrderTable C Where version = (select max(version) from OrderTable where orderid = c.orderid) and BuySell = 'B' and Status <> 'D' and Park <> 1 and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4) and Price = ( Select max(Price) From OrderTable cc where version = (select max(version) from OrderTable where orderid = cc.orderid) and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Park <> 1 ) and Orderdate = ( Select min(Orderdate) From OrderTable dd where version = (select max(version) from OrderTable where orderid = dd.orderid) and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Price = c.Price and Park <> 1 ) and OrderId = (select top 1 OrderId from OrderTable ff Where version = (select max(version) from OrderTable where orderid = ff.orderid) and orderid = ff.orderid and PitId = c.PitId and BuySell = 'B' and Status <> 'D' and Price = c.Price and Orderdate = c.Orderdate and Park <> 1 )
) Best_Bid_Data
full outer join ( Select PITID PIT_ID, CustomerId Offer_Customer, Size Offer_Size, Price Offer_Price, orderid Offer_Order_Id, Version Offer_Order_Version, ProductId Offer_ProductId, TraderId Offer_TraderId, BrokerId Offer_BrokerId, Reference Offer_Reference, Indicative Offer_Indicative, Park Offer_Park From OrderTable C Where version = (select max(version) from OrderTable where orderid = c.orderid) and BuySell = 'S' and Status <> 'D' and Park <> 1 and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4) and Price = ( Select min(Price) From OrderTable cc where version = (select max(version) from OrderTable where orderid = cc.orderid) and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Park <> 1 ) and Orderdate = ( Select min(Orderdate) From OrderTable dd where version = (select max(version) from OrderTable where orderid = dd.orderid) and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Price = c.Price and Park <> 1 ) and OrderId = (select top 1 OrderId from OrderTable ff Where version = (select max(version) from OrderTable where orderid = ff.orderid) and orderid = ff.orderid and PitId = c.PitId and BuySell = 'S' and Status <> 'D' and Price = c.Price and Orderdate = c.Orderdate and Park <> 1 )
) Best_Offer_Data ON Best_Bid_Data.Pit_Id = Best_Offer_Data.Pit_Id
I am assigning package string variable to the StandardOuputVariable of a Process Task, and setting the TimeOut to 3, TerminateProcessAfterTimeOut to True, plus the Executable name. The program is executing about 40 seconds, and it seems not to terminate after the 3 seconds has passed.
When I execute the same package but without the StandardOuputVariable the task terminates after the 3 seconds.
Is this a bug or I shouldn't expect the task to terminate when the StandardOuputVariable is set???
I have a batch file which is located in different server. When I try to execute in BIDS it is running fine. But when I execute as SQL agent job it is not executing.
In the execute process task under executable I have supplied the batch file path . For example \112.89.93.9abcsample.bat.
It is executing fine when I execute this in BIDS supplying the above path under executable. But when I run this as SQL agent job it keeps on running and does not end and it is doing anything.
Do I need to mention any cmd.exe file under executable and give the path under arguments tab?
I need help to fix SSIS Send Mail Task Error. I have a
Data Flow Read from Sql Data base and then put the query result into excel file (OLE DB Source --> Excel Destination)
Send Mail Task Email excel file from the data flow
Control Flow : Data Flow --> Send Mail Task
The package throws an error below. I tried to disable my antivirus because i thought it's throwing an error because of the antivirus. But, it's still throwing the same error. If i tried to send email without attachment, it works just fine. Is there anybody know how to fix this?
Progress: The SendMail task is initiated. - 0 percent complete [Send Mail Task] Error: An error occurred with the following error message: "The operation has timed out.". Progress: The SendMail task is completed. - 100 percent complete Task Send Mail Task failed
Hi, I am using SQL Server2005 for SSIS. I want to change the source connection dynamicaly evertime. Let me clear, I have to extract some column from excel to MS-Access. I am using Data Flow Task and able to successfully complete the job. But problem is that, whenever a new file comes , i must have to reconfigure my Excel Source. All the time column in file are same, so no need to worry about mapping but how can my package select a file automatically. I have a directory, suppose "C:dpak". I should able to pick the filename and sheet name from this directory every time when my package will execute.
I'm building a custom task and just wondering what is the correct way of passing errors back to SSIS. Is there a rcommended approach to doing this. Currently I just wrap everything in a TRY...CATCH and use componentEvents to fire it back! Here's my code:
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA 116-NY, 258-NJ, 254-PA, 245-DC, 298-AL How do I get the values in the below format NY,CA,GA --- each state to be followed with comma and the next state NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
I want to log/watch the query executed by the "Execute query Task". It's a query with different variables, the different variables i can see in one of the debug windows and these looks OK, but the query doesn't do what's expected. So i want to see the whole query, is that possible?
Query logging on the database server isn't an option. The server is an oracle server and the dba doesn't want to change this setting.
My asp.net application is attached with SQL database, I record only time in my SQL database , field type is nvarchar. Now I want to do qurey by time and pull the result , but datatype of field in nvarchar, query does not giving me right result. some one tell me how Do I do query so I can get proper result. thanks maxmax
I have asp.net applicatin with SQL database communicating. in database I have date field and time field. Now I wan to do SQL query which can pull informatin on particular date between given start time to given end time Can some one show me sample SQL query so I can pull informatin on particular day between two times
How do I find all the records within a time range when all the datetime's are on different days? Example: Show me all the order placed between 12:00 and 15:00 in May
I need to time a query down to hundredths of a second. I can see from the runtime clock in SQL Studio that my query ran for 00:00:00. However, I need to know exactly how long it took.
I am using IBM DB2 provider in my SSIS. In Script Task when i want to use query retrieving data i.e selecting a row in a table. Should i use SQL SERVER 2005 query statement or IBM DB2 query syntax because queries varies for different provider. Say in SQL SERVER 2005, my query will like 'Select * from test where date_dt = getdate()" but in IBM db2 the query will be like "SElect * from test where date_dt = currentday".
Should i use SQL SErver 2005 query or IBM db2 query since my connection that i am using for IBM DB2 provider?
DELETE T1 WHERE EXISTS (SELECT * FROM T2 A WHERE A.C1= T1.C1 AND A.C3 >= ?)
Results in below error (OLEDB SQL TASK):
" failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement? Thank you very much in advance! Mark
