How to make SQL Server 2005 database empty using cursor & sys.objects (Delete All Tables, stored procedures, views & UDF’s)

Understanding sys.objects: Sys.objects is a system VIEW in SQL Server 2005, for each SQL database there is a separate sys.object view which gets stored within databse itself. Using Sys.objects returns list of all database objects and its types, type can be either of given below: DB OBJECT TYPES F     FOREIGN_KEY_CONSTRAINT IT    INTERNAL_TABLE PK    PRIMARY_KEY_CONSTRAINT S     SYSTEM_TABLE SQ    SERVICE_QUEUE [...]

Read Full Post »

Quick DBMS reference: Single line definitions of important SQL Server terms

Normalization: is a process of organizing data and minimizing redundancy De-normalization: is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. Stored Procedure: is a named group of T-SQL statements which can be created and stored in Database as an object. Primary Key: is [...]

Read Full Post »

How to connect/access remote SQL Server 2005 database via CC Proxy

Most of organizations use proxy server shared internet connection for their internal network. Browsing websites via proxy server requires few settings in browser itself where we set proxy server IP and port to 8080 which is default port, doing so is enough for any 1 to surf internet. But as an SQL server database programmers/developers [...]

Read Full Post »

Import Excel data into SQL Server 2005 table using distributed queries

/*SQL server configuration settings */ exec sp_configure sp_configure ‘show advanced options’, 1 reconfigure exec sp_configure ‘Ad Hoc Distributed Queries’, 1 reconfigure /*Excel import into sql table  using distributed query*/ select * into XLimport from openrowset(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\Documents and Settings\Administrator\Desktop\filename.xls’ ,[Sheet1$]) select * from XLimport

Read Full Post »

Storing and Retrieving image in SQL server 2005 & asp.net using large value data type & HTTP handler

  APSX page : <form id=”form1″ runat=”server”> <div style=”padding: 200px;”> <asp:Image ID=”imagebox” runat=”server” ImageUrl=”~/sql-image-store/image-handler.ashx” /> </div> </form>     Handler code: image-handler.ashx <%@ WebHandler Language=”C#” Class=”image_handler” %> using System; using System.Web; using System.IO; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Configuration; public class image_handler : IHttpHandler { public void ProcessRequest(HttpContext context) { string xStrCon = [...]

Read Full Post »

Writing recursive queries in SQL server 2005 using Common table expression (CTE)

with cte (empID,manID,depth,hierarchy) as ( select e.employeeID,e.managerID ,1 , cast(e.employeeID as nvarchar(max))as hierarchy from humanresources.Employee as e where managerID is null union all select emp.employeeID,emp.managerID ,depth+1  , hierarchy + ‘/’ +cast(emp.employeeID as nvarchar(max)) as hierarchy from humanresources.Employee emp inner join cte on emp.managerID=cte.empID ) select * from cte order by depth

Read Full Post »