SQL SERVER
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,...
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...
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...
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:...
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...
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...
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...
Function to convert DateTime to MM/DD/YYYY format CreateFUNCTION [dbo].[tkfunConvertDateToMM... varchar(10)) RETURNS VARCHAR(10) AS BEGIN RETURN substring(@dtDate,4,2)+'/'+... END
Function to Convert DateTime to DD/MM/YYYY format CreateFUNCTION [dbo].[tkfunConvertDateToDD... varchar(10)) RETURNS datetime AS BEGIN RETURN Convert(datetime,substring(... END
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...
Full SQL SERVER Archive