Geeks With Blogs
Gaurav Taneja Great dreams... never even get out of the box. It takes an uncommon amount of guts to put your dreams on the line, to hold them up and say, "How good or how bad am I?" That's where courage comes in. SQL SERVER
Using Derived Tables
Using Derived Tables to Simplify the SQL Server Query Process Problem Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. Unfortunately there are problems with both of these approaches if you are trying to query data on the fly. With the temporary tables approach you need to have multiple steps in your process, first to create the temporary table, then to populate the temporary table, ......

Posted On Monday, January 10, 2011 7:15 PM

Creating a new Job in Sql Step by Step
This is about adding a new SQL Jobs in the Sql Server 2005. Step 1 Make sure that the SQL Server Agent is up and running. You can see it in the taskbar icon. If SQL Server Agent is not running, start it from the SQL Server Configuration Manger You can also start the SQL Server Agent from the command prompt using the command netstart net start "SQL Server Agent (<instance name>)" e.g net start "SQL Server Agent (SQLSERVER01)" Step 2 Connect to the database engine of SQL server using SQL Server ......

Posted On Monday, January 10, 2011 7:02 PM

Truncating All Tables
Create Procedure GT_DeleteRecords_AllTables As /**************************... Stored Procedure: GT_DeleteRecords_AllTables Do not excute this proc Purpose: Delete ALL records within ALL the tables in a DB with ease. Test: Exec GT_DeleteRecords_AllTables*... Set Exec nocount on sp_MSForEachTable 'Alter Table ? NoCheck Constraint All' Exec sp_MSForEachTable ' If ObjectProperty(Object_ID(''... ''TableHasForeignRef'')=1 ......

Posted On Monday, June 29, 2009 5:21 AM

sp_xml_preparedocument
Example displaying when data is sent in form of xml document CREATE PROCEDURE XMLINSERT @intShowVersion INT =0, @strClassName varchar(250), @strLang varchar(10), @strXml text='', @intError int output AS SET NOCOUNT ON /* @intShowVersion INTEGER =0, @strCreatedBy VARCHAR(20) = '' , @intTemplateID INTEGER = 0, @dtClientCreatedOn DATETIME = '' , @strUpdatedBy VARCHAR(20) = '' , @dtClientUpdatedOn DATETIME = '' , @strXml text='', SET DATEFORMAT dmy IF (@intShowVersion = 1 ) BEGIN SELECT VERSION='$Revision: ......

Posted On Tuesday, October 14, 2008 12:17 AM

Count No of Procedure in Database
Count Procedure select count(*) from sysobjects where xtype='P' other option is below select CASE(XType) WHEN 'C' THEN 'CHECK constraint' WHEN 'D' THEN 'Default or DEFAULT constraint' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'L' THEN 'Log' WHEN 'FN' THEN 'Scalar function' WHEN 'IF' THEN 'Inlined table-function' WHEN 'P' THEN 'Stored procedure' WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)' WHEN 'RF' THEN 'Replication filter stored procedure' WHEN 'S' THEN 'System table' WHEN 'TF' THEN 'Table ......

Posted On Monday, May 5, 2008 5:02 PM

Simple procedure using transaction
Begin Begin Tran --Inserting values member details into ServGeneralUser Table Insert into ServGeneralUser(LoginName,P... values (@LoginName,@Password,@Emai... -- reteriving Currently Inserted Members User Id into local variable @userId select @userId=ident_current('Serv... ......

Posted On Sunday, April 6, 2008 11:45 PM

New in Visual Studio 2008
What is new in Visual Studio 2008 A quick list of some of the new features are: Multi-Targeting support Web Designer and CSS support ASP.NET AJAX and JavaScript support Project Designer Data LINQ – Language Integrated Query The features listed and explained in this paper are not complete and this document intends to give you a forehand to start off with VS 2008. 1. Multi-Targeting Support Earlier, each Visual Studio release only supported a specific version of the .NET Framework. For example, VS ......

Posted On Tuesday, April 1, 2008 11:27 PM

Function to convert DateTime to MM/DD/YYYY format + SQL SERVER
Function to convert DateTime to MM/DD/YYYY format

CreateFUNCTION [dbo].[tkfunConvertDateToMMDDYYYY](@dtDate varchar(10))
    RETURNS VARCHAR(10)
AS
BEGIN
    RETURN substring(@dtDate,4,2)+'/'+substring(@dtDate,1,2)+'/'+substring(@dtDate,7,4)
END

Posted On Tuesday, April 1, 2008 9:41 PM

Function to Convert DateTime to DD/MM/YYYY format + SQL SERVER
Function to Convert DateTime to DD/MM/YYYY format

CreateFUNCTION [dbo].[tkfunConvertDateToDDMMYYYY](@dtDate varchar(10))
    RETURNS datetime
AS
BEGIN
    RETURN Convert(datetime,substring(@dtDate,4,2)+'/'+substring(@dtDate,1,2)+'/'+substring(@dtDate,7,4))
END

Posted On Tuesday, April 1, 2008 9:40 PM

Remove Special Character from XML string + sql Server
Remove Special Character from string -- ===========================... -- Author: <Author,,Name> -- Create date: <Create Date, ,> -- Description: <Description, ,> -- ===========================... ALTER FUNCTION [dbo].[RemoveSpChar] ( -- Add the parameters for the function here @sInput varchar(MAX)='' ) RETURNS varchar(MAX) AS BEGIN -- Declare the return variable here DECLARE @sOutput Varchar(MAX), @iIndex int, @iLength int, @sChar varchar(1), @iASCII ......

Posted On Tuesday, April 1, 2008 9:38 PM

How to change the ower of tables in Microsoft SQL Server database
To do this, execute the following command on every table exec sp_changeobjectowner '?????', 'DBO' - where ????? is the name of your database table. Also below is the stored procedure through which you can change the owner for every table inthe database. EXEC sp_changeobjectowner @oldownerplusobject, @new create procedure J_ChangeObjectOwner (@type varchar(1),@old varchar(20),@new varchar(20)) as declare @ObjectName varchar(100) declare @oldownerplusobject varchar(50) begin declare Cursor_Object cursor ......

Posted On Friday, August 4, 2006 4:30 AM

Copyright © Gaurav Taneja | Powered by: GeeksWithBlogs.net