My World

Views in SQL Server are updateable, so when you add/update rows the view details could get alter (resultant records from the views). Consider following example

CREATE VIEW Employees_Karnataka

AS

(

SELECT * FROM Authors WHERE state='Karnataka'

)

Now if you execute following DML statement:

UPDATE Employees_Karnataka SET state='Maharashtra'

This statement will execute and the view will not have no records to display. This is very much legal DML statement, which results in anomolous results.

This anamolous situation can avoided by adding WITH CHECK OPTION predicate when you create the view:

CREATE VIEW Employees_Karnataka

AS

(

SELECT * FROM Authors WHERE state='Karnataka'

)

WITH CHECK OPTION

Now any insert or update operation that makes a record disappear from the view raises a trappable runtime error.

You can use the account of a Windows user who is a member of the local Administrators group to add another Windows user to the sysadminfixed server role in SQL Server 2005. To do this, follow these steps:

1. Log on to Windows by using the account of a Windows user who is a member of the local Administrators group.
2. Stop the SQL Server service.
3. At a command prompt, start the instance in single-user mode. To do this, follow these steps:
   1. At a command prompt, change to the following folder:
      SQLInstall\Microsoft SQL Server\MSSQL.X\MSSQL\Binn

      Notes

      • SQLInstall is a placeholder for the folder in which SQL Server 2005 is installed.
      • MSSQL.X is a placeholder for the folder for the instance.
   2. If the instance is a named instance of SQL Server 2005, run the following command:
      sqlservr.exe -sInstanceName -m -c

      If the instance is the default instance of SQL Server 2005, run the following command:

      sqlservr.exe -m -c
4. Use the Sqlcmd utility (Sqlcmd.exe) to connect to the instance. To do this, follow these steps:
   1. Start a command prompt.
   2. If the instance is a named instance of SQL Server 2005, run the following command:
      sqlcmd -SComputerName\InstanceName

      If the instance is the default instance of SQL Server 2005, run the following command:

      sqlcmd -SComputerName
5. At a command prompt, run the following Transact-SQL statement.

   sp_addsrvrolemember '<Login>', 'sysadmin'
   GO

   Note <Login> is a placeholder for the Windows user whom you want to add to the sysadmin fixed server role.

Important The method that is described in this article is a new feature in SQL Server 2005. You cannot disable this feature. However, use other methods to add a Windows user to the sysadmin fixed server role if other methods are available. For example, if you have a different login that is a member of the sysadmin fixed server role, use this different login to log on to the instance. Then, add the Windows user to the sysadmin fixed server role.

Check this lovely site to understand the SkyDrive feature and MS Office 2010 integration.

http://office.microsoft.com/en-us/web-apps-help/using-office-web-apps-in-windows-live-skydrive-HA101231889.aspx

Some of the difference is as mentioned below:

We implemented this design couple of years back, when we had the need to create a dynamic business layer! Dynamic Business Layer, what is that? Here it goes!
Requirement: Not to be surprised! At the time of application design we had no clue about the physical architecture because there were some business constraints and challenges in procuring servers in time.
We had to develop a web based application that could easily augment with one or multiple server and expose the business layer to other applications.
Solution:We decided to go with layered approach for this requirement! We developed the core business layer functionality as class library and a wrapper class library using factory pattern. The wrapper class is used to call the web service which internally used the core business layer functionality.

Process Flow of the above diagram:
1. Request from UI layer
2. Factory Class gives an object to access business layer either via the Web services or directly local class library (Core.businessLayer functionality).
3. Business layer executes the required method by interacting with DAL and returns the response
4. Factory object returns the response to UI Layer

Any other applications (hosted from other servers) could now make direct call for business layer functionality via web services
Depending on the configuration file entries factory object is created with instance of either local class library or of web services for accessing business layer functionality.
With the above approach our physical architecture even if it was dynamic was factored, as we had the option of using either web services (other server) or a local class library (same server). In case we had only one server we had the option of using local class library thus reducing the network call to web services or if in future the application scaled out we had the option of deploying web service separately with core libraries. All this was managed via configuration file entries.
Thus we called it as dynamic business layer, if you had the similar requirement I would certainly like to know what approach you had followed.

Many time while writing web application we come across situation to write details in log file. Simple way to do so is as mentioned below:

function WriteToFile(FileName, Contents, Append)
on error resume next

if Append = true then
fileMode = 8
else
fileMode = 2
end if

set oFs = server.createobject(“Scripting.FileSystemObject”)
if not oFs.FolderExists(Server.MapPath(“./logs”)) then
oFs.CreateFolder(Server.MapPath(“./logs”))
End if

set oTextFile = oFs.OpenTextFile(Server.MapPath(“./logs”) & “\” & FileName, fileMode, True)

oTextFile.Write Contents
oTextFile.Close
set oTextFile = nothing
set oFS = nothing

end function

Let me know if you find any other easy way to do so.

Requirement: I had to find out the last day(mm/dd/yyyy) of the month e.g. Jan – 31, Feb 28, 29 etc and send it as parameter to a query. I was using VBScript inside ASP code.
Solution:
Last Date of the month: DateAdd(“d”, -1,{LastDayOfTheMonth})
In the above case {LastDayOfTheMonth} will be the user input, example user enters 1/23/2009 (mm/dd/yyyy). We can very well find the month and year and have something like this month(“1/23/2009″) & “/” & “1/” & year(“1/23/2009″)

Possible Usage:
I had to use this for a requirement where user provides date range inputs as month and year, so what I did was to have query like the one mentioned below:
sql = ” Select * from members where userCreatedDate between #”&Request(“getmonth1″)&”/1/”&Request(“getYear1″)&”# and #”&Request(“getmonth2″)+1&”/1/”&Request(“getYear2″)&”#”

In the above case since between excludes “to” date so we go with this approach of going with 1 of next month and that is what is required as well.

Note: the above query works with backend as MS Access database

Happy coding !