I have a table with equipment ID, transaction ID, date of transaction. How can I write a sql so that the result is a list of
equipment with % up time during 2007.
Transaction ID: 100 is down, 200 is up. Example is below the table
Equipment ID Transaction ID Date
1 200 1/12/2007
2 100 2/12/2007
1 100 2/25/2007
3 100 3/10/2007
2 200 3/14/2007
From the table I know that equiment 2 was down for 32 days and up time % during 2007 is (365-32)/365*100
The TSQL below all works except the bolded part at the end. I'm want to grab only the most recently logged piece of equipment not the most recent and all past ones as well which is what I've got doing minus the bolded part below. But I don't know how to say get this Equipment ID etc and only the most recently logged one to find its present location. The bolded part below is just there to show what I want it to do I know you can use an aggregate in a where clause. So in the first table listed tblRdrLog there is a column Time that I want to do this on so a.Time. I don't want to display a.Time just reference.
String dbsql = " SELECT a.EquipmentID " + " , f.Subcategory " + " , c.Area " + " , d.Room " + " FROM tblRdrLog a " + " JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " + " JOIN tblRdrArea c ON b.AreaID = c.AreaID " + " JOIN tblRdrRm d ON b.RoomID = d.RoomID " + " JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " + " JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " + " WHERE a.EquipmentID IN (SELECT a.EquipmentID " + " FROM tblEquipInfo a " + " JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " + " JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " + " LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " + " LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " + " JOIN tblStatus f ON a.StatusID = f.StatusID " + " WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString())) dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";
#region Advanced Search Criteria
// Check whether advanced search submitted if (adv) { if (!"".Equals(this.tbSerialNo.Text.ToString())) dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' "; if (!"".Equals(this.ddlMake.SelectedValue.ToString())) dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' "; if (!"".Equals(this.ddlModel.SelectedValue.ToString())) dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' "; if (!"".Equals(this.ddlStatus.SelectedValue.ToString())) dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' "; }
I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).
With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?
I am a novice to this type of setup and I need advice.
We have three field offices currently using A VPN over DSL connection to a server in the main office. We use a program that has 11 licenses for all the users and will purcahse 2 more licenses with the upgrade. As you can seee, not all employees are users of the software and for the moent the 11 licenses are adequate. We have 25 computers including the server, 21 employees, 15 in main office and four in one field office and 2 in another. All computer workstations are WIN XP PRO and the server is currently running Server 2003.
The program we currently use is a client/network where the employees fille out the forms and then the data is saved on the main home based server. No data is local to the user. At the workstations, only a small kernel of the client software is installed but it is a significant size and over DSL it is relativel slow to make the connection and save the data to the home server. Speed of this connection is not the issue, however.
We are going to upgrade the current server later this year and install either Server 2008 Standard or Small Business Server.
Our issues are many and suffice it to say I am not sure what to do,but am asking for help only with this one.
Issues We may purchase an Enterprise version of software that we currently use. This requires SQL databses that we do not use in the present version.
Question. Do we need a Separate SQL server. The program developers say we should have a separate server, but the company does not want to purchsae a separate server because of cost. They want to use the single server for all users and as a data storage server for all other work related data not used by the main software. The program vendor says that there may be issues with this setup because we also may use, in the future, another program that may be server based and therte could be a "slowdown" in usage. This future program is not known to me a this time as they have not made this decision as yet so it does not enter into the current scenario.
I feel that a separate server for SQL is necessary because of the needs of each of the users in each of the field offices and the "load" they currently place on our current server.
This isn't a performance type of question, or whose CPU is bigger, but I'm more interested in knowing what people use out there as far as monitor setups go.
The root of the question stems from working in large package where there are many components laid out on the work flow. What do you use to work with large layouts? I constantly find it troubling to view enough of my package and am always zooming out to see more of the package. However, when doing that, the text becomes unreadable, of course.
I currently use a dual-monitor setup using my laptop and a LCD flat screen.
Resolutions on the laptop is 1920 x 1200 and the LCD monitor is 1280 x 1024. This helps to keep windows and output screens on one monitor while keeping the work flow on the other. Even still, I wish I had more screen real estate.
Hi everybody. I am new to VBA,.,, I Have created a folder under inbox in Outlook 2007 which stored the receipients information like name, E-mail, which is not present in the address book of outlook. I have imported this folder in access 2007. My problem is ... it does not display the email address. Rest of the informations is displayed... Is there any way i could do this.?? is there any way i could get the email addresses to appear(import/get) in to the access table??? Is there any module i mite have to create??
Hi everyone, I am new to SQL Server. I have SQL 2007 installed on win 2003 server, my problem is when I try to start the Sql 2007 server I get the error message "The service control action cannot be completed as no service has been selected" Thanks in advance Arunchu from London
Sharepoint Server Portal 2007, MSSQL 2005, Reporting Services
When I try to integrate Reporting Services in Adminitsrator, for url of RS I write this: http://servername/ReportServer/ and click OK.
But when I try to go to 'Set Server defaults' or 'Managed Shared Shedules' I get this:
Server was unable to process request. ---> Client found response content type of '', but expected 'text/xml'. The request failed with an empty responseWhat should I Do ?
I have bcm with outlook 2007 and accounting express. when i create an opportunity in bcm, everything is fine. when i click the convert to quote button, it dumps all of the item information (quantity, name, description, price, tax....) into the description field in accounting. Then I have to copy each piece out of the description field into it's appropriate field in accounting 2007 express. Where is the mapping for these fields, so that I can map each one correctly? Is there something else i should be doing?
I want to user 'acCmdExportFixedFormat' in ms-access to save reports as PDF and XPS. when i use the code runcommand acCmdExportFixedFormat i have to set manually the filename. I want to give the filename in my vba code. How do i user this
I know this is not SQL server related. Do anyone know good Third-party tools to backup sharepoint 2007? I have looked at www.avepoint.com and ideras tools but didn't find price listings. I am looking which has reasonable price and efficient. Only xpurt ownly
hi everybody I've been working in the database field for 9 months I started from very simple tables, to some sql stuff And then I started studying SQL Server 2005 extensively until I took MCTS SQL Server 2005 During that time, I was building a database for a new company from scratch, and while I build it for them I keep on reading books and learning, and using this knowledge to upgrade the database Recently, I could successfully migrate the back-end to SQL Server 2005, and after days of struggling I could connect the front end to it via ODBC in a LAN My next step is connecting the database through the internet, so I just changed the server name in the DSN file to the address of the server, and opened the ports, but it didn't work.... Can you tell me what's the problem? Is there a book that can help me do this thing? I can buy any book and I have the will to learn, provided that I'll apply for MCITP Database Developer next months
I have installed System Center Essentials on our network. Everything seems to be working but we are now getting Perflib errors (event id 1008) on our windows 2000 servers. This error indicates there is a problem with our ASP.net v2.xx.xx.xx. We have been unable to resolve this and are looking for more things to try.
Haven't been able to find much information on using openrowset with excel 2007 xlsx files. I've tried the following with no success so far.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=\serverfolderfile.xlsx;HDR=No;IMEX=1', 'SELECT f1 FROM [Raw Keywords$] WHERE f1 IS NOT NULL')
If anyone knows the correct way to do this please tell me.
I'm having troubles connecting the webparts on SharePoint 2007. I installed the RSWebParts package from SQL Server 2005 Reporting Services on my MOSS 2007 box. The installation went smoothly and I can add both Report Explorer and Report Viewer on a page. But when I try to connect RE to RV I get the following error:
An unexpected error has occurred.
Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.
So I have to go to the maintenance page and remove the web parts.
Have anyone managed to bypass this and make them work together?
Is anyone attempting or has anyone successfully deployed MOSS2007 with a SQL 2005 Database Mirror on the backend? We are doing it in our lab with the automatic failover (principal, mirror & witness) and the SQL portion works great. The only issue is we have is that we have to manually switch MOSS over (via Sharepoint 3.0 Central Admin or stsadm) to the new content database on the other server. Thank you
We have reviewed the White Paper that Microsoft offered with no help to our issue.
now, how do i import excel 2007 files in SSIS? i don't have office 2007 installed on my computer, but i've got some .xslx that i would like to try ro import. i tried to create an OLEDB connection, but i don't know which provider to choose.
anyway, can i do it without the sp2 or office 2007 installed on my server?
Hi Experts, The trigger is not sucessfully calling the Stored Procedure. This was working up until last year (12/21/2007 @ 1600 hrs.)Here I am providing codes for both Trigger and Storedprocedure.Can you help me out where the problem is?
Trigger
USE [personnelreq] GO /****** Object: Trigger [tgrTracking] Script Date: 01/16/2008 11:55:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO
/***************************************************************************************************************************************************************** * Trigger Name: tgrTracking * Description: Calls the CalcTrackingHours stored procedure for replacement and net staff addition * requisitions when the requisitions are emailed, transferred, or completed. * History * TKT/CO # Date Developer Description * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ * 01/03/2005 Zsofia Horompoli Created * CO#1570 03/22/2005 Zsofia Horompoli Added originator email tracking for transfer events *****************************************************************************************************************************************************************/ CREATE TRIGGER [tgrTracking] ON [dbo].[Log2] FOR INSERT AS
DECLARE @reqId int -- store requisition ID DECLARE @eventId int -- event value inserted DECLARE @parentId int -- store parent requisition ID DECLARE @eventTime datetime -- store date/time of event DECLARE @email varchar(50) -- store email address DECLARE @logId int -- store ID assigned to the record in the Log2 table DECLARE @location varchar(30) -- Location DECLARE @title varchar(30) -- Job title DECLARE @multiReq int -- > 0 = Multi NSA DECLARE @origMail varchar(50) -- Originator's email address DECLARE @subject varchar(200) -- Email subject line DECLARE @from varchar(50) -- From email DECLARE @body varchar(8000) -- Email body DECLARE @reqType int -- Requisition type (0-replacement, 1-net staff addition, 6-Bonus) DECLARE @sReqType varchar(30) -- Requisition type description DECLARE @appEmail varchar(500) -- Approvers' email addresses
SELECT @from = 'compensationandbenefits@west.com'
DECLARE @inserted_rows AS CURSOR -- hold the inserted rows SET @inserted_rows = CURSOR FOR SELECT id,reqid, timestamp, event, stuff(notes,1,patindex('%:%',notes),'') AS email FROM inserted -- get the Log2 id,requisition id, event, timestamp, and email address from the updated/inserted rows
OPEN @inserted_rows FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email WHILE (@@FETCH_STATUS = 0) BEGIN --Is this an email/transfer/complete/deny event? IF (@eventId = 4 OR @eventId = 6 OR @eventId = 7 OR @eventId = 0 OR @eventId = 10) BEGIN -- Yes, is this a deny event? IF (@eventId = 0) BEGIN --Yes, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id FROM reqs2 r WHERE requisitiontype in (0,1) and id = @reqId and (SELECT ParentReqId from ParentChild WHERE ChildReqId = @reqId) IS NULL and NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 END ELSE IF (@eventId = 6) BEGIN SELECT @parentId = id FROM reqs2 r WHERE requisitiontype IN (0,1,6) AND id = @reqId AND (SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and NOT (markedfortransfer = 0 AND logging <> 1) and (SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL END ELSE IF (@eventId = 7 OR @eventId = 10) BEGIN SELECT @parentId = id FROM reqs2 r WHERE requisitiontype IN (0,1) AND id = @reqId AND (SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and markedfortransfer <> 2 and (SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL END ELSE BEGIN -- No, is this an active replacement/net staff addition/parent multi NSA requisition? SELECT @parentId = id FROM reqs2 r WHERE requisitiontype IN (0,1) AND id = @reqId AND (SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 and (SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL END
--Is this a requisition to be tracked? IF @parentId is not NULL BEGIN --Yes, is this a transfer event? IF @eventId = 6 BEGIN --Yes, retrieve information for email SELECT @location = RTRIM(L.DESCR), @title = RTRIM(PT.DESCR), @multiReq = (SELECT COUNT(*) FROM ParentChild WHERE ParentReqID = R.id), @reqType = R.requisitiontype FROM reqs2 R LEFT OUTER JOIN TDSdev.dbo.PS_LOCATION_TBL L ON R.sitename = L.LOCATION LEFT OUTER JOIN TDSdev.dbo.PS_JOBCODE_TBL PT ON R.jobtitle = PT.JOBCODE WHERE R.id = @reqId
-- Get email addresses to use EXEC sel_EmailUsers @reqId = @reqId, @bApprover = 1, @currentUser = NULL, @origEmail = @origMail OUTPUT, @appEmail = @appEmail OUTPUT
--Set requisition type description IF @reqType = 0 BEGIN --Replacement requisition SELECT @sReqType = 'Replacement' END ELSE IF @reqType = 1 AND @multiReq > 0 BEGIN --Multi-NSA SELECT @sReqType = 'Multiple Net Staff Addition' END ELSE IF @reqType = 1 BEGIN --NSA SELECT @sReqType = 'Net Staff Addition' END ELSE IF @reqType = 6 BEGIN --Bonus SELECT @sReqType = 'Bonus' END
--Build subject line --Do we have a location? IF @location IS NULL BEGIN --No, default to Nothing SELECT @location = '' END
--Do we have a job title? IF @title IS NULL BEGIN --No, default to Nothing SELECT @title = '' END
--Set email body IF @reqType = 6 -- If Bonus Req BEGIN SELECT @body = '<p>Bonus requisition number ' + CAST(@reqId AS varchar(15)) + ' has been processed by Compensation.</p>' SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>' END ELSE IF @reqType IN (0,1) -- If Net Staff or Replacement Req BEGIN SELECT @body = '<p>Requisition number ' + CAST(@reqId AS varchar(15)) + ' has been finalized and sent to Human Resources on ' + convert(char(10),@eventTime, 101) + ' at ' + substring(convert(char(19),@eventTime, 100), 12, 8) + ' for recruiting.</p>' SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>' END
-- Call the CalcTrackingHours stored procedure IF @reqType IN (0,1) BEGIN EXEC CalcTrackingHours @event = @eventId, @reqId = @reqId, @emailTo = @email, @endDateTime = @eventTime, @logId = @logId END END END FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email END
--clean up CLOSE @inserted_rows DEALLOCATE @inserted_rows
Stored Procedure is
USE [personnelreq] GO /****** Object: StoredProcedure [dbo].[CalcTrackingHours] Script Date: 01/16/2008 12:01:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /****** Object: Stored Procedure dbo.CalcTrackingHours Script Date: 4/12/2005 9:20:19 AM ******/
/***************************************************************************************************************************************************************** * Stored Procedure Name: CalcTrackingHours * Description: Determines the appropriate tracking level, the number of business hours the requisition * spent at the current tracking level and inserts this information into the timeLog table * History * Date Developer Description * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ * 01/03/2005 syszxh Created *****************************************************************************************************************************************************************/ CREATE procedure [dbo].[CalcTrackingHours] @event int, -- 4=Emailed,6=Transferred,7=Completed @reqId int, -- Requisition Id @emailTo varchar(50), -- Email address requisition was forwarded to @endDateTime datetime, -- Date/time of event @logId int -- ID assigned to new record in the Log2 table as set nocount on
declare @currentUserType as int -- Current user type declare @startDateTime as datetime -- Date/time requisition arrived at current step declare @newUserType as int -- New user type declare @currentTrackLevel as varchar(5) -- Tracking level to be inserted into the timeLog table declare @newTrackLevel as varchar(5) -- New tracking level declare @trackEmail as varchar(50) -- Email address at current tracking level declare @count as int -- counter variable declare @errorValue as int -- Error flag (1 = start date/time missing) declare @trackHours as decimal(10,2)
-- Retrieve last email's user type, date/time stamp, and email address select top 1 @currentUserType = userType, @startDateTime = timestamp, @trackEmail = n.userid from personnelreq.dbo.Log2 l left outer join personnelreq.dbo.names2 n on stuff(notes,1,patindex('%:%',notes),'') = n.userid where (l.event = 4 or l.event = 10) and timestamp <= @endDateTime and reqid = @reqId and l.id < @logId order by l.id desc
-- Last email found? if @startDateTime is NULL begin -- No, this is the very first email, retrieve new user type select @newUserType = userType from personnelreq.dbo.names2 n where userid = @emailTo
-- Is new user @C&B? if @newUserType = 2 begin -- Yes, set new tracking level select @newTrackLevel = 'cb1' end else begin -- No, set new tracking level select @newTrackLevel = 'dept' end end else begin -- Is this event = email? if @event = 4 begin -- Retrieve new user type select @newUserType = userType from personnelreq.dbo.names2 n where userid = @emailTo
-- Is the current user the same as the new user? if @currentUserType = @newUserType and @emailTo = @trackEmail begin -- Yes, get latest level select top 1 @newTrackLevel = trackLevel from timeLog where reqID = @reqId and trackEmail = @trackEmail and dateIn = @startDateTime order by dateIn desc
select @currentTrackLevel = @newTrackLevel end -- Is the current user type @C&B? else if @currentUserType <> 2 or @currentUserType is NULL begin -- No, retrieve current tracking level exec GetTrackLevel @userType = @currentUserType, @reqID = @reqID, @email = @trackEmail, @trackLevel = @currentTrackLevel output -- Is the new user type @C&B? if @newUserType = 2 begin -- Yes, retrieve new tracking level exec GetCBLevel @trackLevel = @currentTrackLevel, @reqID = @reqID, @currentTrackLevel = @newTrackLevel output end else -- No, retrieve new tracking level begin exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output end end else begin -- Yes, retrieve new tracking level exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output -- Retrieve current tracking level exec GetCBLevel @trackLevel = @newTrackLevel, @reqID = @reqID, @currentTrackLevel = @currentTrackLevel output end end -- Is this event = transfer/completed? else if @event = 6 or @event = 7 or @event = 10 begin -- No, any existing entries for exec3? if (select count(*) from timeLog where trackLevel = 'exec3' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb5 select @currentTrackLevel = 'cb5' end -- Any existing entries for exec2? else if (select count(*) from timeLog where trackLevel = 'exec2' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb4 select @currentTrackLevel = 'cb4' end -- Any existing entries for exec1? else if (select count(*) from timeLog where trackLevel = 'exec1' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb3 select @currentTrackLevel = 'cb3' end -- Any existing entries for acct? else if (select count(*) from timeLog where trackLevel = 'acct' and reqID = @reqID) > 0 begin -- Yes, set current tracking level to cb2 select @currentTrackLevel = 'cb2' end else begin -- Set current tracking level to cb1 select @currentTrackLevel = 'cb1' end end
-- Calculate hours worked if @event <> 10 begin exec CalcBusinessHours @startDateT = @startDateTime, @endDateT = @endDateTime, @totalHours = @trackHours output
-- Update information in the timeLog table update timeLog set dateOut = @endDateTime, trackHours = @trackHours where reqID = @reqID and trackHours = 0 and dateOut = '1990-12-31' end end
-- Is this event = email? if @event = 4 and @newTrackLevel is not NULL begin -- Yes, insert new row for new track step insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut) values (@reqID, @newTrackLevel, @endDateTime, @emailTo, 0, '1990-12-31') end -- Is this event = reopen? else if @event = 10 and @trackEmail is not NULL begin -- Yes, insert new row for new track step insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut) values (@reqID, @currentTrackLevel, @endDateTime, @trackEmail, 0, '1990-12-31') end
Is there any way in RS2005 to export to Excel 2007 format ?
The reason I'm asking is that we have a report that can potentially have more than 256 columns but for now, there is no way of directly importing to excel 2003 (.xls).
Is microsoft going to come out with an improvement to RS2005 or is there already a way to export to Excel 2007 to bypass the 256 column limit?
I am trying to work through a test on database mirroring I am following the whitepaper that Micrososft published on how to configure mirroring and sahrepoint, but when i failover the config database and admin content database it doesnt appear to work. I execute the following commands after both are failed over
Help! My network support staff just upgraded my MS Office products from Office 2003 to Office 2007, and now I no longer get the "F5 = Run" option in Microsoft Visual Studio to run any of my SSIS packages that ran just fine yesterday, before the upgrade.
The "Debug" option does not occur in the menu choices across the top of the screen. (They are File, Edit, View, Project, Build, Data, Format, SSIS, Tools, Test, Window, Community, and Help -- no Debug.)
I store my packages in the File System. I can run many of them just fine, even today, with the Execute Package Utility. My problem occurs when I try to run them in Visual Studio.
Even if I make a simple, new package that moves a table from Access 2003 to SQL Server 2005, I see no Debug menu selection option when I am finished with the new package.
I figure it must be something simple -- I just don't know where to find it in the Visual Studio menu.
Most of the packages I am trying to run do not access Office files: they access dBase 5 files (database); they run processes on SQL Server 2005 servers. I realize that I may have to make some adjustments to connections to Access databases, should such databases be upgraded to Office 2007.
I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime) I write code select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1) it will like select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1) → select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30 but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code
I am trying to export data from database to excel 2007 file on my machine. I have downloaded the provider for office 2007 and I have XP with sp2 and SQL server 2005 with SP2. I havn't installed office 2007 on my machine,but I have a excel file which is created in office 2007.To get acess of read and write i have downloaded office compatibilty pack too,So I can read and write into the file. Now I am creating one SSIS package to export data into excel file.But I m not able to coause I am getting some errors like
"test connection failed because of an error in intializing provider. Invalid UDl file"
"Test connection is failed because of an error in intializing provider.Not a valid file name".
Please help or suggest how to export data from database to 2007 excel file.
I am wondering if there is a solution for our current issue i.e. we can't export the query report from SQL report services into 2007 Excel. We have no problem with Excel 2003.
Hi, I would really appreciate some help on this one as I know nothing about triggers to be quite honest.
I'm trying to add a feature to my Delphi 2007 application to start a SQL package whenever a load button on my Delphi form is clicked. Assume I'll need SQL code to perform this.
Can anyone please assist in this matter - sample code will also be much appreciated. Thanks a million!
I converted my database to 2007 version and when I tried to execute VBA Module which adds records from one table to another it showed me a mistake (Type mismatch) Here is the code (it worked on 2003 version). Can anybode help me to fix the error?
Function AddBulletin() Dim st As String Dim i As Integer Dim db As Database Dim Table1 As Recordset Dim Table2 As Recordset DoCmd.OpenTable "Today", acNormal, acEdit DoCmd.ApplyFilter "", "[Today]![F3] Is Null" DoCmd.RunCommand acCmdSelectAllRecords DoCmd.RunCommand acCmdDeleteRecord DoCmd.Close acTable, "Today" Set db = DBEngine.Workspaces(0).Databases(0) Set Table1 = db.OpenRecordset("Today", dbOpenDynaset) Set Table2 = db.OpenRecordset("BULLETINS", dbOpenDynaset)
The mistake appears on the last two rows. It's probably easy but I cannot find it.
