SQL 2012 :: Sync Table With Calendar?
Nov 11, 2014sync values from table to calender. I got SQL Server 2012 and Outlook 2013.
I want to know is there any way all appointments booked in table should sync with exchange server automatically.
ADVERTISEMENT
SQL Server 2012 :: Add Business Days To A Date Using Calendar Table
May 12, 2015I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
SQL Server 2012 :: Create Weeks Calendar From Date
Sep 30, 2015I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:
WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5
SQL 2012 :: How To Sync DB Between 2 Servers
Apr 23, 2014I have 2 server PROD n DR server all the PROD server DB is synched to DR server through log shipping. Now to chk whether the DR server is working or not in case of any disaster we shut down the PROD server now all the app request is handled by DR server,it is working fine. For 1 week we work in DR server now its time to move back to PROD server so that all the 1 week data in DR server should be replicate back to PROD server. Log shipping is deleted once the DR Server is up. What r the options other than Backup? It is SQL 2012
View 4 Replies View RelatedTransact SQL :: Joining A Calendar Table And Employee Table?
Apr 20, 2015I run into a problem when asking to show a query of employee vacation days.
table 1:
column1 Â is dates
e.g.
2015-01-01
2015-01-02
2015-01-03Â
.
.
.
2015-12-31
table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012
My query output need to be:
2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
SQL Server 2012 :: Calendar Style Report With 12 Months (as Columns) And Store Opening Listing In Rows
Oct 16, 2014I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
SQL 2012 :: Database Data Files Out Of Sync
Jul 21, 2014I've stepped into a new environment and have never dealt with multiple data files on user databases only with Temp db.What would be the best way to get all my data files in sync. I have done this on databases that aren't that big in size or off in size by a lot. Here is what I have
Mdf -- 69 GB
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 4
ndf --4
ndf -- 2
SQL 2012 :: AlwaysOn Async Replica Back To Sync
Aug 19, 2014After failing over to the DR replica. All databases are out of sync. DR replicas were setup as async the other 2 are set up as sync. Is this by design. No data has been updated to any of these as they are test dbs. So all dbs should be the same, no data loss.
View 0 Replies View RelatedPopulate Calendar Table
Aug 3, 2007Hello all,
I would like to populate a calendar table similar to the one used in the foodmart2005 sample database. In this table the day of the week, month, and year are recorded as well as additional data which I do not require.
If possible I would like to populate the table with 10 years of data starting with 2007.
Secondly would 10 years of data make the table to large?
Thank you for any thoughts you may have.
SQL 2012 :: Log-shipping Secondary Server Is Out Of Sync And LSRestore Job Failing
Jun 3, 2015I checked the server and found that LS restore job failing and Backup and copy jobs running fine without any issue. and also observed that Copy folder the trn file existing on secondary server. i try to restore trn file im getting the error. and observed that last log backup file that it restored at the secondary database on May2nd,2015.
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
From Restore job histort details below.
Message
2015-06-02 12:25:00.72*** Error: The file 'xxxx\_20150530104503.trn' is too recent to apply to the secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2015-06-02 12:25:00.73Searching for an older log backup file. Secondary Database: 'database'
2015-06-02 12:25:00.73*** Error: Could not find a log backup file that could be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.74Deleting old log backup files. Primary Database: 'database'
SQL Table Data, Smalldatetime And Calendar Control
Jul 13, 2007Inside my SQL 2005 database I have a SmallDateTime with the layout "mm/dd/yyyy hh:mm:ss AM/PM" and then on a selected date of a calendar control it returns the date with 12:00:00 AM which obviously doesn't match with my database's time.
So, I'm looking for a way to drop the time off completely (for the query purpose) the database date when running a select statement. I don't want to touch SQL table data by modifying it, I just want to be able to query sql table using calendar control. Does anyone now how to accomplish this? Thank you in advance. - Nietzky
Create First Day Of Month Column In Calendar Table
Nov 12, 2007
Hello,
I'm trying to get my head around this, any help would be appreciated. I have a calendar table I've created for use with a billing report. It would be great to have a column that had the first day of the month for every row entry (so for every row representing a day in february, that row would have February 1st, in datetime format). I thought it would be easy to create and populate this column at first, but I'm finding it much more difficult. Does anyone know how to populate this column?
Thanks in advance,
Andy
Transact SQL :: Calendar Table - Get The Date Post 3 Business Days
Sep 10, 2015I have a calendar table against entire year 2015 with each day with 2 flag,
1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N"
2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"
DECLARE @CAL TABLE (CAL_DT DATE, WK_DT_IN CHAR(1), HOL_DT_IN CHAR(1))
INSERT INTO @CAL VALUES ('2015-01-01', 'Y', 'Y'), ('2015-01-02', 'Y', 'N'),('2015-01-03', 'N', 'N'),
('2015-01-04', 'N', 'N'), ('2015-01-05', 'Y', 'N'), ('2015-01-06', 'Y', 'N'), ('2015-01-07', 'Y', 'N')
We have a given date, example '2015-01-01', I need to find out a date after 2 business days? I can think of loop, but will it work.
I need to exclude date which having HOL_DT_IN = "Y" and WK_DT_IN = "N".
Power Pivot :: Calendar Look Up Table - Custom 24 Hour Start Time
Jul 9, 2015I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as  the last parameter, I would choose true().
I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table.Â
Transact SQL :: Calc Work Date Using Business Days Field In Calendar Table
May 19, 2014I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.
------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
   @WorkDays VARCHAR(5),
   @DateFromValue AS DateTime )
   RETURNS DATETIME
[Code] ....
Trying To Sync A View Instead Table (ppc)
Jan 5, 2004I am working on a program for the Pocket PC platform, which will piggy-back on a helpdesk program which stores it data in an SQL server. I have no problems with getting information from the server, or any connectivity issues.
I have found that it is easy to synchronize an access .mdb file, or an ODBC Source to a .cdb file on the PPC. I have a data provider for the SQL database in question, and the "Import Database" function works fine; it pulls up a listing of all the tables and fields in the helpdesk database.
My problem is as follows. The table used to store the memo information is at least 20-30 megs. I only need a few key entries from that table. I also need to make the sync process as simple as possible (that's why i was looking into activesync), but there does not appear to be ANY way to import an SQL View, as opposed to a table.
SQL CE appears to be one possible solution, but we are running SQL 7, and the CE server tools require SQL 2000.
So, is there a way to trick activesync into sync'ing a VIEW instead of a table?
Do you have ANY OTHER ideas, which would be simple come time to sync?
Sync'd Table Updates Blocked
Feb 11, 2008Hi
I'm using VS'08 and develop in VB.
I'm using SQL CE 3.5 as a local cache for SQL'05 tables.
The table that's being updated uses an interger autonumber for the PK.
When the program starts up and inserts records into the table, it works, as long as no on else is insering recoreds into the same table.
Once a duplicate PK is created by another WS, the records no longer update the SQL'05 table.
If the blocked program is restarted, it'll insert records with a PK that's past the one found at the initial sync. until blocked again.
PK ---- ProgID ----- MSG
1
0
4
B
hello
2/6/2008 9:33:55 PM
2/6/2008 9:33:55 PM
2
0
4
B
hello
2/11/2008 7:54:38 PM
2/11/2008 7:54:38 PM
3
1
1
B
hello
2/11/2008 8:32:41 PM
2/11/2008 8:32:41 PM
4
0
4
T
just something
2/11/2008 8:34:18 PM
2/11/2008 8:34:18 PM
5
1
1
B
one
2/11/2008 9:13:41 PM
2/11/2008 9:13:41 PM
6
1
1
B
two
2/11/2008 9:14:06 PM
2/11/2008 9:14:06 PM
7
1
1
B
three
2/11/2008 9:14:35 PM
2/11/2008 9:14:35 PM
8
1
1
B
four
2/11/2008 9:15:04 PM
2/11/2008 9:15:04 PM
9
1
1
B
five
2/11/2008 9:15:59 PM
2/11/2008 9:15:59 PM
10
0
4
B
cp 1
2/11/2008 9:17:44 PM
2/11/2008 9:17:44 PM
11
0
4
B
cp 2
2/11/2008 9:18:13 PM
2/11/2008 9:18:13 PM
12
1
1
B
eight
2/11/2008 9:21:31 PM
2/11/2008 9:21:31 PM
13
0
4
B
cp 3
2/11/2008 9:21:52 PM
2/11/2008 9:21:52 PM
NULL
NULL
NULL
NULL
NULL
NULL
NULL
David L.
SQL Server 2008 :: Sync Between A View And Other Concrete Table
Jul 23, 2012Here's the scenario. We tried to create a full text indexing on a view, unfortunately the view was created based on Left outer join. Hence we were pushed to do the following.
We planned to create a table based on the data that is rendered in this view(this view is formed by combining 14 tables.) . Finally we were able to create a full text indexing on this new table. For simplicity let us called this new table as tableA.
Now the problem is how can we update (if any data is changed on one of the concrete tables of the view) or insert (when new data is inserted in one of the underlying tables of the view) the respective data into this new table - tableA which was created based on this view.
In other words, there should be a sync between the view and tableA. Is it possible to achieve this ???
View is formed based on different tables (not based on tableA)?
Store A Calendar
Nov 8, 2004Hi,
I'm developing a site in asp.net and one of the main parts is to manage a timesheet.
Now the problem i'm facing is; how do I save this timesheet in a database in a way that it's easy to save and easy to query from.
I'm thinking about problems as i.e. 1st of a month is middle of the week, so I can't save it like this:
Month Week Mon Tue Wed Thu Fri Sat Sun
1 5 .. .. .. .. .. .. ..
2 1 .. .. .. .. .. .. ..
Anyway, I got stuck to find the best method, maybe someone has made something like this?
Thanks in advance!
Pieje
Outlook Calendar
Nov 27, 2007
When I try to view someone else's Calendar in Outlook I get this message:
The messaging interface has returned an unknown error. If the problem persists, restart Outlook.
Of course restarting Outlook resolves nothing. Please help.
Report To Look Like A Calendar
Sep 17, 2007
I have thought about this, and I cannot in my head come up with a way to create this. Maybe I am thinking about it too much, or maybe it is this difficult and I will need a 3rd party tool.
My users have requested a SQL Reporting Services report that looks like a calendar. Each page will be one month on the calendar. And then the data will fall on the calendar according to a date field in the database. It should not be all that difficult, but I cannot think of a way to acheive this.
Has anyone tried to build a report that is basically just a calendar, and each page is a month of the calendar? If so, did you build it using the standard SQL reporting services tools, or did you purchase a 3rd party vendor?
Let me know what route I should look to follow.
Thank you!
T.J.
ASP.NET C# Calendar DataAccess Issue
Oct 10, 2007Hello there
I'm having a little issue with Updating a SQL Table. *Deep Breath* OK, lets begin from the start...
I Have a Table Called Job. I've an ASP Page setup to insert a new record into the Job table via a DetailsView Control with an SQLDataSource bound to it. This table has many columns in it, 4 of which are DateTime columns. I use a calendar Control from the toolbox for the DateTime selection and bound all the Calender control's SelectedDate to: SelectedDate='<%# Bind('EnteredDate') %>' using the EnteredDate field as my example.
I only insert DateTime Values into 2 of the 4 DateTime columns in the original INSERT, so the remaining 2 columns are NULL. I have an UPDATE setup on a different page to edit the previously inserted rows in job using another SQLDataSource again using the bind method for calendars: '<%# Bind('RevisedDate') %>'
My Problem is (according to this post I found online http://www.thescripts.com/forum/thread529052.html ) the page crashes with "Specified cast is not valid". The reason being I'm trying to bind to the remaining 2 NULL DateTime columns in an attempt to update them with a value my user will not be able to insert in the original INSERT. The solution Code I found on the link above is in VisualBasic and I have had little luck in trying to convert it into C# to even see if it solves my problem:
VB Code:
Function FixDBNull(ByVal inVal As Object) As DateTime If inVal Is DBNull.Value Then Return DateTime.Now Else Return inVal End IfEnd Function
ASP Code:
<asp:calendar id="calEditRevisedDate" runat="server"selecteddaystyle-backcolor="red"selecteddate='<%#FixDBNull(eval("RevisedDate")) %>'visibledate='<%# FixDBNull( eval("RevisedDate")) %>'selectorstyle-backcolor="Green"></asp:calendar>
Any suggestions or work arounds would be greatly appreciated
Thank You
Calendar Filtering In Gridview
Feb 3, 2008
I have 2 dropdownlist and a gridview. The dropdownlists filter the
gridview, but now I have added two calendars and two textboxes. After
user makes selections from the calendar the text boxes are populated
with the dates. How do I add this to the following select parameter so
that the filtering/querying works properly? (what I have here does not
work in terms for the dates, but dropdownlists filter when I remove the
dates section):<asp:SqlDataSource ID="DATA" runat="server" ConnectionString="<%$ ConnectionStrings:DATA_Connection %>"
SelectCommand="SELECT * FROM [atable] WHERE(afield=@param1 or
@Param1='Select something' or((CONVERT(char(10), adate,
101)>=@param3 AND CONVERT(char(10), bdate, 101)<=@param4)) AND(bfield=@Param2 OR @Param2='Select something' or((CONVERT(char(10),
adate, 101)>=@param3 AND CONVERT(char(10), bdate, 101)<=@param4))" UpdateCommand="Update atable set afield=@afield, bfield=@bfield where id=@ID" > <selectParameters> <asp:controlparameter name="param1" controlid="DropDownList1" PropertyName="SelectedValue" type="String" /> <asp:controlparameter name="param2" controlid="DropDownList2" PropertyName="SelectedValue" type="String" /> <asp:ControlParameter Name="param3" ControlID="Textbox1" PropertyName="Text" Type="string" /> <asp:ControlParameter Name="param4" ControlID="Textbox2" PropertyName="Text" Type="string" /> </selectParameters>
Connecting Mdb File To A Calendar
Feb 21, 2008Hi Everyone.
I’m trying to read data from a databasefile called database.mdf which has a connection string called “ConnectionString1�. The aim is to highlit specific days in my calender. For example if there is an event in my database that day on the calander will be highlited. The database has a columb called “Date� in a table called “Dates�. Please can you help? My knowlade in asp.net and VB is very limited. This is as far as I have gone. (I don’t realy understand the VB part as I have copyed it from somone) I just need the vb code to connect to the database.
Imports System.Data.SqlClientImports System.DataImports System.Data.OleDb
Partial Class _Default Inherits System.Web.UI.Page Protected Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As DayRenderEventArgs) If IsExistMeeting(e.Day.Date.ToString("MM/dd/yyyy")) Then e.Cell.BackColor = System.Drawing.Color.Red e.Cell.ForeColor = System.Drawing.Color.Yellow End If
End Sub
Function IsExistMeeting(ByVal datestring As String) As Boolean Dim constr As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString.ToString() '"your connection string" Dim connection As SqlConnection = New SqlConnection(constr) Dim sqlsel As String = "select count(*) from Dates where CONVERT(char, date, 101)='" + datestring + "'" Dim sda As SqlDataAdapter = New SqlDataAdapter(sqlsel, connection) Dim ds As DataSet = New DataSet() sda.Fill(ds, "Dates") If ds.Tables("Dates").Rows.Count = 0 Then Return False End If Return True End FunctionEnd Class
Using A Calendar Control In An SQL Report
May 15, 2008We have developed several reports using VS 2005 and deployed them to our SQL 2005 reporting server. As most of our reports use date parameters we would like to incorporate a calendar control to allow users to select their date range as opposed to typing them in. I have done some reseach online but can only find vague references on how to do this. Has anyone out there been able to successfully deploy a calendar control onto an SQL report? Thanks!
View 2 Replies View Related360-Day Calendar Year Function...
Jun 23, 2008I'm looking for a function that will return an INTEGER that computes the number of days between two dates based on a 360-calendar year instead of using just the datediff function which is actual days.
If anybody knows where I could find something like this, I would be greatly appreciated.
Does this site have a library of user-defined sql functions? Or, does somebody have a site that you know of where there are a bunch?
Thanks,
South Side Rob
Generating New Month In Calendar
Jan 21, 2014i have inherited a sql database to work on and cannot seen to fix this error. its an availability calendar and at the end of each month a new month is generated, it by default makes all dates unavailable but i need them available. is this done in the database as a default??if a member has logged in recently then the avails populate fine but if they have not logged in for a while then they all show unavailable? i
View 3 Replies View RelatedHow To Go From A Database To A Calendar Format?
Jul 23, 2005HelloMy department has a SQL server database that is used to all facets ofproduction within our department. This includes key production jobsteps, project names, numbers and descriptions.The calendar is currently done manually as an Excel document. The userrecieves hard copies of the required information, then keys it into theappropriate place on the Excel document.We would like to pull information out of that database, and put it in amonthly calendar format. For example, take a project name and the dateassociated with that project, and put it on a calendar in theappropriate date "square".If you have information or experience with software that can be used toaccomplish this task, I would appreciate hearing your advice.Thanks in advance.
View 1 Replies View RelatedJob To Be Run Ever First For The Previous Calendar Month
Jan 18, 2007Hi!I have a query that has to return bunch of data based on the calendarmonth. I have to make sure that it will return data to me for 28 daysif it is February and for 31 if it is August(for example). I need tobe able to execute it every first of every month for the past 30, 31 or28 days based on the calendar month. Is there a function or a storedprocedure that I can use to do that?Thank you,T.
View 5 Replies View RelatedCalendar Report Layout Help
Jun 28, 2007Hello
I need to create something like the following table:
MON TUE WED THU FRI SAT SUN
01/01/07 02/01/07 03/01/07 04/01/07 05/01/07 06/01/07 07/01/07
Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field
08/01/07 09/01/07 10/01/07 11/01/07 12/01/07 13/01/07 14/01/07
Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field
The user would enter the start date, in this case the 1st Jan 07 and then this would populate a table. This seems like it should be so simple but I can't work it out, can anyone help please?
Cheers
Calendar Control In A Report?
Oct 19, 2007I have a need to create a report that contains a calander, i.e. last month in rows and columns . Any idea on how to accomplish this need?
View 9 Replies View RelatedCreate A Graghicapl Calendar Using VFP 9.0
Mar 8, 2007I'm working on a project where a user wants to show there scheduled events on a calendar.
The way they want to show it is, displaying a bar across the days that the event takes place.
They want this on a web page and in Word. I'm using VFP 9.0 and I know it has web tools, but I've never used them. Could someone please point me in the right direction of how to do all of this?
Thank!
Filtering Calendar Control?
Feb 15, 2007Hello,
Please, help with following issues.
I have 3 parameters in the report. When user selects 1st one from dataset, one of the fields gives me object creation date. Calendar control is supposed to be filtered to allow users select the dates greater than selected one.
How can I filter (disable dates in) calendar control?
Any suggestion/comment highly appreciated