Outsourcing becoming part of the core business of any successful business these days, one of the very nice articles I read couple of weeks ago was about outsourcing. It is explaining the outsourcing in a very good way and explain what to Outsource and when to do that. The subject of this article was "Outsourcing: What and When?" I strongly recommend to have couple of minutes reading this site.
Date different in SQL server Vs Oracle
While setting back in my chair today and thinking of the power that MS tools giving to us , I decided to do a very small compare of things I can do in SQL server and how can I do the same thing in Oracle.
I decided to do a very primitive operation on both DBs… something is very silly. Just to get the differences between 2 dates in Weeks. This is very easy thing
In SQL Server I could do that in single line of SQL Statement
| SELECT DATEDIFF (ww, ’03/20/1983′, ’11/03/2010′) DiffInWeeks_SQLServer |
While I could do the same operation in Oracle by a long equation comparing with the one I have for SQL Serer:
|
SELECT (to_date(’03/20/1983′,’mm/dd/yyyy’) – to_date(’11/03/2010′,’mm/dd/yyyy’) ) / 7.0) DiffInWeeks_Oracle FROM DUAL; |
I still like working on Microsoft platforms.
Reading Data from Excel by using SQL Statements & ODBC
In many cases I had to query data from excel sheet with large number of records … of course I can do this by using filtering feature from MS excel, many developer prefer writing sql statements instead of using filtering feature built into MS Excel. Using Sql statements to query data from Excel sheet will make developer life easier. You can load this sheet into DB server then query it using the ordinary tables, actually this is not the optimum solution for this case.. You can query excel sheet without load it into DB server.
By using ODBC you can query excel sheet’s data without loading it into DB from inside Query analyzer or MS Sql Management studio…. It is very simple query and u can even filter by writing your own WHERE condition.
To query data form MS Excel 2003 you can use the following Select Statement:
|
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;DATABASE=FileNameWithPath.xls’, ‘Select * from [Sheet1$]’) |
And form MS Excel 2007 you can use the following select statement:
|
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;Database=FileNameWithPath.xlsx;HDR=No;IMEX=1’, ‘select * from [Sheet1$]’) |
For example:
Suppose you have an excel sheet with 3 columns; firstName,lastName and Email.And this sheet is stored at C:\Filename.xsl on your hard drive… to query this file:
|
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;DATABASE=c:\filename.xls’, ‘Select * from [Sheet1$]’) |
How to know SQL Server version and Service Pack(SP)
Many time I stuck with a question… what is this DB Server version and what Service pack is installed on it? What is the easiest way to know DB edition? I asked this question to a friend of mine and he gave me the following simple Select statement that answered my questions.
|
SELECT SERVERPROPERTY(‘productversion’) AS version , SERVERPROPERTY (‘productlevel’) AS SP , SERVERPROPERTY (‘edition’) AS DbEdition |
I hope you will find it useful 🙂
Contenet Management System (CMS)
What is CMS?
Computer management system (CMS) is computer software that is used to create, edit and publish contents of websites. Typically, CMS is used to store, control, versioning and publish industry-specific documentations such as manuals, guides, articles…etc. CMS’s users are supposed to edit and publish content of his own site without involving any technical resources. CMS system mainly contains 2 subsystems, which are: Content Management application (CMA) and the content delivery application (CDA).
What is Content Management Application (CMA)?
This subsystem allow content manager or content author, who have no technical background on HTML editing , to create, edit, delete content from website with no need for any technical resources.
What is Content Delivery Application (CDA)?
This subsystem is responsible about rendering the content that already managed by CMA on user’s browser.
CMS Features:
-
Store documents, videos and other type of electronic files.
-
Publishing feature of CMS allows users to choose a template from a pre-defined set of templates adopted by organization, as well as using wizards and other tools to manage web site contents. Format management feature of CMS is responsible about how to render documents into HTML or PDF formats.
-
Versioning feature of CMS allows users to update contents without losing the old contents. This feature enable users to get back to old contents any time and even restore it back to website as well as keep tracking of changes had been done on website contents.
-
Searching feature of CMS allows users to search for website contents by using keywords. This means that CMS system should indexes all data and website contents.
-
Identify the main users and their roles within CMS system.
-
The ability to assign specific security permission for a specific document.
-
Define workflows, tasks and even events messaging so that author of the content got notified of any change.
Why do you need CMS?
When your organization has a public website or an intranet site and this site has grown over the time. And much of the content of this site start being out-dated or inaccurate and you can’t find things. As well as you have to update the content of this site periodically with no need for technical resource. You start lost tracking of pages on your website.
Actually these problems are natural when you are using manual tools to edit your site… manual tools like Dreamweaver, Frontpage..etc.
CMS system is specifically designed to solve all of the above issues.
Benefits from using CMS:
-
Make editing your site easier and straightforward.
-
Your site will have a consistent look and feel.
-
Improved site navigation.
-
Increased security.
-
Reduced duplication of information.
-
Greater capacity for growth.
-
Reduced site maintenance costs.
-
Keep track of site changes.
-
Support your business goals and strategies, by improve Sales and user satisfaction as well as make communication with public more efficient.
Autocomplete (IntelliSense) While Writing SQL Statements; How Nice is SQL Prompt
As a .Net developer who using Visual Studio from early morning till late evening, I like the way MS designed the IntelliSense feature there. and I was so disappointed because there is no such a feature while you are using SQL Server Management Studio.
In version 2008 of SQL server Management Studio, MS solved the IntelliSense issue there but that was specifically if you are connecting to SQL Server 2008 backend engine.But I still need to use this feature while I am using SQL server 2000 and 2005 SQL server engines.
I found a very good software from Red-Gate called SQL Prompt, this software is doing a very nice job for me and solved all of my issues related to IntelliSense.
I have been using this software for 3 years till the moment I am writing this article, and I am still believe on it and if I start writing SQL statement without using it, I feel my life like a hell.
For me the IntelliSense feature of SQL Prompt is much better than the one that MS developed on SQL server 2008. and Red-Gate is keeping enhancing it and making it much better everyday.
Code snap it on SQL prompt is amazing and saving my time a lot. You can define a keyword and its related SQL statements, once you type this keyword and then tab out, SQL Prompt will place the related SQL statements for you. For example, if you type “ssf” and tab out, SQL prompt will replace it by “SELECT * FROM “
As a conclusion, I would strongly recommend to you to use it during your daily development day.