col1 col2 col3 col4
(No column name)
A123 Test
1
Test 1
Y N 0
Y N
0
A125 Test
1
Test 9
Y N 0
N Y
0
but what I would like is col 1 populated As A123 until it hits A125 then populated A125 etc. I was thinking about using the iff but not getting anywhere fast.
I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.
UPDATE tblEventStaffRequired SET StartTime = <expression here> FROM tblEvents INNER JOIN tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID
I am working on a report that has 3 bars per series. I would like to add some gapping or whitespace between each bar in each series. Also how do you deal with overlapping. I know it can be done in Excel, I can't find the property of how to do it in SSRS. Any assistance would be appreciated.
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE ( Id INT, PublicationId INT, MetricId INT, ProviderId INT, DateCreated DATE, Amount FLOAT
I have 18 odd subscribers at the moment, my publisher and disttribution is on the same machine with push subscriptions.
The questions I have
nr 1. While trying to initialize new subscribers I get loads of deadlocks even after I stop dist cleanup agent. This *I think* cause some other unexpected problems.
nr2. The queue reader would fail saing it cannot find the "insert" proc on the publisher, although it exists. I have changed anything on the publication so I'm not sure how this happens or why.
nr3. I replicate a varbinary(max) column and on the odd occasion get the "Length of LOB data" errors which I then set with sp_configure. The catch here is that the length never exceeds a "len()" of 4000, thus the reported LOB and my calculation doesn't tie up.
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID Date Location 1 4/1/2015 bld1 2 4/2/2015 bld1 3 4/4/2015 bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1) WHERE Date not in (Select Date, Location FROM [table])
Col1 to Col9 represent the account information, Col10 amount, Col 11 represents the month and Col12 represents year. According to the data there wasn't any activity for the month 1, 2 and 3.
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
I have an exact copy of a database on 2 instances of SQL server (dev and test - dev is restored from test).There's a view that I select top 1000 rows from that runs extremely slow on both instances (DEV and TEST) however on DEV the execution plan specifies that I'm missing an index however in TEST the execution plan does not specify that I'm missing an index.
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
I have a table like above where cid is unique but status is not for all the status id i need put 1 as they sent out .but till now i used max listindex because they used to send files sequentially but now list index is random so how to update sent to 1 for all the status ids.
No error is thrown, but the update is not made? Possibly due to the dreaded inline sql being used? The data structure for these 2 servers is horrific (but that is a story in itself). The current structure (which needs immediate change) is each employee all 10 of them, have their own database. If the userid is 6 or higher they are on server2 if the userid is 5 or below they are on server1. Then they each have their own table that stores the data. (A story for another day, but def needs overhaul). However, here is the sql -- what needs to be altered to work with the current data structure and still get the updates needed? Or we can scratch the update statements entirely if we can get the correct counts needed.
I would like to use the following code for querying summary records with paging.
DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 1 SET @RowspPage = 10 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER, * FROM summary ) AS TBL WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage) ORDER BY create_date
Paging is implemented for fast response since the data pool is very large up to 10000000.
The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.
E.g. At 12:00pm, the result of page 1 (5 per page) is R20, R19, R18, R17, R16
After 2 mins, 12:02pm, the user press next page button Since records R21, R22, R23, R24, R25, R26 are inserted page 2 result would be R21, R20, R19, R18, R17
So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC SET LKC.combo = lockCombo2 FROM [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
I am trying to update a large table which consists of 45 million records , it is taking more than 2 days to the update , below is my approach
1. The table has only one clustered index and no other indexes on the table. 2. I am updating in batches say 20000 record-wise. 3. Changed the recovery mode to bulk logged and auto-growth size is set to 300MB and there is enough space in my disk for transaction log .
After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.
We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.
In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime) insert into #InputTable (ID_No,OccurMonth) select 10, '2007-11-01' Union all select 10, '2007-12-01' Union all select 10, '2008-01-01' Union all select 20, '2009-01-01' Union all select 20, '2009-02-01' Union all select 20, '2009-04-01' Union all select 30, '2010-05-01' Union all select 30, '2010-08-01' Union all select 30, '2010-09-01' Union all select 40, '2008-03-01'
I have dataset which has max(column) and between_sale_dates columns So I would like to update those columns. CustomerNo and Sales_date are important . This group by for these 2 colums. I can write a sproc but How can I do with SSIS?
I have a web page that has been in production for over a year. On occasion the web app users report sporadic errors which last a few minutes then go away. The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page. The error is that a column that is expected to be in the dataset is not found. I know this column must exist in the database because its a required field. After we reset IIS, then all returns to normal and the errors go away. Below is the stack trace. Exception: System.IndexOutOfRangeException Message: Cannot find column LastName. Source: System.Data at System.Data.DataTable.ParseSortString(String sortString) at System.Data.DataView.UpdateIndex(Boolean force) at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter) at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState) at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex) at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex) at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain() Our DBA can find no problems on the database side and our web servers don't log any system event problems either. I do not understand why resetting IIS helps the issue. I need to find the real cause of the problems rather than just resetting IIS all the time. Any ideas?
So I setup a flat file csv connection with comma as column delim, CR/LF as row delim, etc. Everything works as planned and the package executes.
Now lets say the file comes in wrong and has two columns instead of three. It looks like this. R1C1, R1C2 R2C1, R2C2 R3C1, R3C2
The SSIS file manager reads the file as two rows vs failing. So it reads the above as this... R1C1, R1C2, {LF/CR}R2C1 R2C2, {LF/CR}R3C1, R3C2
This is obviously not right. You get similar issues if they send an extra column where data is just added to the last column. How do you get SSIS to fail or error out if the column count is wrong?
My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!
