http://source.witssquare.com working with code stuff!... Fri, 22 Jan 2010 11:30:59 +0000 http://backend.userland.com/rss092 en Sometime ago I´m in trouble with a massive and large view. This monster was as slow a performing view as I've seen and I started to put in an effort to speed up the server and lower the response time. First I considered creating some indexes on the view, but there ... http://source.witssquare.com/sql-server/sql-server-indexed-views-with-outer-joins/ One area that always, unfailingly pulls my interest is SQL Server Errors and their solution. I enjoy the challenging task of passing through the maze of error to find a way out with a perfect solution. However, when I received the following error from one of my regular readers, I ... http://source.witssquare.com/sql-server/sql-server-%e2%80%93-how-to-drop-primary-key-contraint/ Primary Key: Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls. Create table with Primary Key: CREATE TABLE Authors ( AuthorID INT NOT NULL PRIMARY KEY, Name VARCHAR(100) NOT NULL ) GO Alter table with Primary Key: ALTER TABLE Authors ADD CONSTRAINT ... http://source.witssquare.com/sql-server/sql-server-primary-key-constraints-and-unique-key-constraints/ Often times in code you want to assign one variable to another, but only if the variable is not null. If it is null, you want to populate the target variable with another ( perhaps default ) value. The code normally may look like this using the C# Conditional Operator: string ... http://source.witssquare.com/net/conditional-operator-in-c/ There are three ways to retrieve the current datetime in SQL SERVER. CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} CURRENT_TIMESTAMP CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced. GETDATE() GETDATE is a ... http://source.witssquare.com/sql-server/retrieve-current-date-time-in-sql-server-current_timestamp-getdate-fn-now/ 1. As per functionality both GET and POST methods were same.Difference is GET method will be showing the information information to the users.But in the case of POST method information will not be shown to the user. 2. The data passed using the GET method would be visible ... http://source.witssquare.com/net/difference-between-get-and-post-method/ This simple program will guide how to do client side validation of Form in JavaScript. In this just make a form as follows: Name : <asp:TextBox ID="txtName" /> Email : <asp:TextBox ID="txtEmail" /> Web URL : <asp:TextBox ID="txtWebUrl" /> Zip : <asp:TextBox ID="txtZip" /> <asp:Button ID="btnSubmit" OnClientClick=" return validate()" runat="server" Text="Submit" /> Now on the source ... http://source.witssquare.com/net/client-side-validation-with-javascript-using-asp-net/ What Is Windows Communication Foundation? The global acceptance of Web services, which includes standard protocols for application-to-application communication, has changed software development. For example, the functions that Web services now provide include security, distributed transaction coordination, and reliable communication. The benefits of the changes in Web services should be reflected in ... http://source.witssquare.com/net/wcf-wpf-wwf-in-net/ The code sample defines a function GetExcelSheetNames that takes an Excel file's path as input, opens it, and find out the sheet names in that file. The function uses a OleDbConnection to connect to the data source (the excel file). The file is is referred by a DataTable and each row ... http://source.witssquare.com/net/reading-excel-sheet-names-in-c-sharp/ How to get the latest Session in SQL Server 2005 below, SELECT Session_Id,Connect_Time,Auth_Scheme,Net_Packet_Size,Client_Net_Address FROM sys.dm_exec_connections WHERE session_id=@@spid Results : Session_Id  Connect_Time            Auth_Scheme                              Net_Packet_Size Client_Net_Address ----------- ----------------------- ---------------------------------------- --------------- ---------------------------------------- 53          2009-11-09 20:19:22.727 SQL                                      4096            <local machine> Get the all details from this query, SELECT * FROM sys.dm_exec_connections The Details from all columns using like, SELECT session_id,most_recent_session_id,connect_time,net_transport,protocol_type, protocol_version,endpoint_id,encrypt_option,auth_scheme,node_affinity, num_reads,num_writes,last_read,last_write,net_packet_size, client_net_address,client_tcp_port,local_net_address,local_tcp_port, connection_id,parent_connection_id,most_recent_sql_handle FROM sys.dm_exec_connections Reference : http://source.witssquare.com See ... http://source.witssquare.com/sql-server/get-the-latest-session-in-sql-server/ How to select top N value from SQL Server,Oracle and MySQL below, SQL Server: SELECT TOP 10 ProductName, Desc, EmailId FROM Products ORACLE: SELECT ProductName, Desc, EmailId FROM Products WHERE ROWNUM <= 10 MySQL: SELECT ProductName, Desc, EmailId FROM Products LIMIT 10 Reference : http://source.witssquare.com http://source.witssquare.com/sql-server/sql-select-top-n-equivalent-in-oracle-and-mysql/ How to find System information in SQl Server using Select Statement below, SELECT * FROM sys.dm_os_sys_info Get from the Particular fields above query, SELECT CPU_Count,HyperThread_Ratio,Physical_Memory_In_Bytes / 1048576 as 'Memory_In_MB',Virtual_Memory_In_Bytes / 1048576 as 'Virtual_Memory_In_MB', Max_Workers_Count,OS_Error_Mode,OS_Priority_Class FROM sys.dm_os_sys_info Results : CPU_Count   HyperThread_Ratio Memory_In_MB         Virtual_Memory_In_MB Max_Workers_Count OS_Error_Mode OS_Priority_Class ----------- ----------------- -------------------- -------------------- ----------------- ------------- ----------------- 2           2                 1013                 2047                 256               5             ... http://source.witssquare.com/sql-server/find-system-information-using-sql-server/ How to get current server username in sql server using select statement below, --Get Current Server Username SELECT SYSTEM_USER AS ServerUserName Results : ServerUserName -------------------------------------------------------------------------------------------------------------------------------- sa How to Change SQL Server Password, --Change the Current Server Password sp_password 'CurrentPassword','NewPassword' http://source.witssquare.com/sql-server/get-server-username-and-change-password-in-sql-server/ How to get the Server Property in SQL Server using select statement below, --Check service pack level. SELECT ServerProperty('ProductLevel') As Property Reference : http://source.witssquare.com http://source.witssquare.com/sql-server/get-server-property-in-product-level-for-sql-server/ How to Get Current Server Name ,using select statement in below, --Get Current Server Name SELECT @@ServerName AS CurrentServerName Reference : http://source.witssquare.com http://source.witssquare.com/sql-server/get-current-server-name-in-sql-server/ How to get current Database name in SQL Server, using select statement in below, --Get Current Database Name SELECT DB_NAME() AS DataBaseName Reference : http://source.witssquare.com http://source.witssquare.com/sql-server/get-current-database-name-in-sql-server/ DataSet ds = new DataSet(); DataTable dt1 = new DataTable(); DataTable dt2 = new DataTable(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { dt1.Columns.Add("ID", Type.GetType("System.Int32")); dt1.Columns.Add("Name", Type.GetType("System.String")); dt1.Rows.Add(new object[] { 1, "Tommy1" }); dt1.Rows.Add(new object[] { 2, "Tommy2" }); dt1.Rows.Add(new object[] { 3, "Tommy3" }); ds.Tables.Add(dt1); dt2.Columns.Add("ID", Type.GetType("System.Int32")); dt2.Columns.Add("Name", Type.GetType("System.String")); dt2.Rows.Add(new object[] { 4, "asdf4" }); dt2.Rows.Add(new object[] { 5, "asdf5" }); dt2.Rows.Add(new object[] { ... http://source.witssquare.com/net/how-to-merge-the-net-datatables/ use testbase create table tblMedian(val int) Insert into tblMedian (val) Values (10) Insert into tblMedian (val) Values (30) Insert into tblMedian (val) Values (60) Insert into tblMedian (val) Values (100) Insert into tblMedian (val) Values (110) Insert into tblMedian (val) Values (150) Insert into tblMedian (val) Values (170) Insert into tblMedian (val) Values (1000) select * from tblMedian ORDER BY ... http://source.witssquare.com/sql-server/calculate-the-median-value-in-sql-server/ While debugging a managed code batch process today I ran into VS throwing a 'ContextSwitchDeadlock MDA' error. The debugger was attached to several long running process's and towards the end of the cycle VS was reporting this issue. As Mike Stall reports it seems to be caused by the thread ... http://source.witssquare.com/net/contextswitchdeadlock-erorr-while-debugging-in-c-net/ SQL Server 2005 adds new security features, not only to make SQL Server more secure, but to make security more understandable and easier to administer. Some of these features will permit programmers to develop database applications while running with the exact privileges that they need. This is known as "the ... http://source.witssquare.com/net/new-security-features-in-sql-server-2005/