The SQL Ideas

We are mostly creating the SQL job and schedule it to run automated. But sometime as per requirement, we need to invoke and run on demand basis, not on a schedule basis. I have done the same thing and the following is the smallest demo ready for you. Let's create one stored procedure and schedule in the job. USE [Master] GO CREATE PROCEDURE JobInvokeProc AS BEGIN SET NOCOUNT ON WAITFOR DELAY '00:00:15'; END GO Now we will create the job for the same and execute this stored procedure in the job. The created script for the job is as follows, USE [msdb] GO /****** Object: Job [Start_JobInvokeProc] Script Date: 05/05/2011 07:51:53 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/05/2011 07:51:53 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_cate

Problem: Before a few days ago I have installed SQL Server Denali CTP3 . Then I was querying in query analyzer and found, minimizing result is not working with CTRL+R is not working. Before CTP3 I have already Denali CTP1 installed in my machine and it was working perfectly, So it stops to work with SQL Server CTP3.The default SQL Server settings was changed during this installation. One of the solution: I did not find any option to resolve it. Then I found one solution to reset these settings, which are changed during Denali CTP3 installation. Below are some snaps which I performed to recover it back. Step 1. Step 2. Step 3. Step 4. Step 5. After completion of all above steps, I recovered my SQL Server default setting and CTRL+R working perfectly now with CTP3.

As we have seen earlier posts for Service Broker for single database and Service Broker between databases , we have implemented it with examples. Hope you liked those posts. Remember, in service broker example for a single database, we have created stored procedure to process requested data from inside the target database sent from initiator database. Then we have manually ran the stored procedure to process history data. Please open the link for that example here . We have tested the same there as follows, USE SourceDB GO TRUNCATE TABLE TransactionTable TRUNCATE TABLE TransactionHistory GO -- Inserting a record INSERT INTO TransactionTable SELECT 2,'Insert',Getdate() -- Updating a records UPDATE TransactionTable SET TranType = 'Update', TranDate = GETDATE() WHERE TranId = 2 -- Deleting a record DELETE FROM TransactionTable WHERE TranId = 2 GO -- Executing stored procedure manualy EXEC SB_ReceiveData GO SELECT * FROM TransactionTable SELECT * FROM Tran

As I have started to learn new functions arrived by SQL Server Denali CTP3 version and I have posted some of them. You can learn Analytical functions , Conversion functions and String functions . You can also get the list of all the features coming in SQL Server 2011 CTP3. Now diverting on this post for the new Logical functions which are following, 1. IIF : It returns one of two arguments, depending on the evaluation of expression. It has require three arguments, first is condition, second and third are the values. Depending on the evaluation of first condition second or third values will be returned, means if the first condition is true, then it will return second value and if it is false then it will return third value. It returns the data type with the highest precedence from the types in true_value and false_value. Let us evaluate it to know it better. DECLARE @IsDone bit SET @IsDone = 1 SELECT iif(@IsDone = 1,'Success', 'Failed') GO Above code returns &

During development of stored procedures, I ran into one issue and this was due to not applying best practice of stored procedures, practice and this issue come mostly when we call stored procedures inside another one and so on, which are used in nested in transactions So I would like to write the next post for the stored procedures development, as how we should write it, It will be a just a template of best practice. In this post I will explain the details of the issue which I suffered and how can we resolve it. Let's for the demonstration we will create one table and stored procedures. -- Creating table which will be used in SPs. CREATE TABLE tbl_Tran ( TranId INT NOT NULL PRIMARY KEY ,TranName VARCHAR(10) ) GO -- Creating first stored procedure. CREATE PROCEDURE Firttranproc AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION -- Inserting records in table, -- Due to this insert error will be raised. INSERT INTO t

I hope you may read my earlier posts for the Analytical functions and Conversion functions which are introduced in SQL Server Denali. You can also move on the What's New of Denali CTP3 here. Here I am going to explain new string functions which are also arrived in this SQL Server Denali version also. 1. FORMAT : It returns a formatted value with specified format. It requires three arguments, first is value, second parameter is specified format and the last one is a culture which is optional. If culture is not provided, it will take a default culture. Let us run below queries to get formatted datetime values. It returns resulted output in nvarchar datatype or null. Let us run some below queries to know it better. First, we will run it format for datetime. DECLARE @DATEVAR DATETIME SET @DATEVAR = GETDATE() SELECT FORMAT( @DATEVAR, 'dd-MM-yyyy') -- 18-07-2011 UNION SELECT FORMAT( @DATEVAR, 'yyyy-MM-dd', 'en-US' ) -- 2011-07-18 UNION SELECT FORMAT( @DAT

Recently I have posted for Service Broker for single database in which I have explained it with table history example and it was invoked within a trigger. It was a for a single database, here I will explain service broker for between databases. In an earlier post of service broker with single database messages were traversed from INITIATOR to TARGET only. But with this post we will go for the communication in a bidirectional way, means from INITIATOR to TARGET and from TARGET to INITIATOR. Service broker has two end called TARGET and INITIATOR, so target and initiator will be on different databases for this example. Before going ahead this post, I would like to you, read the earlier post for a single database. Have we read it? What we do now? 1.Create a request & reply MESSAGE TYPE for target and initiator. 2.Create a CONTRACT that specifies the messages between target and initiator. 3.Create an QUEUE for target and initiator. 4.Create a SERVICE for for the created Queu

Recently i have posted some of the posts related SQL Server 2008 new features , SQL Server Denlali CTP1 and CTP3 new features and some other posts as well. You may like these posts. At the start of September Month I would like to review all the articles posted for previous Month. I have also made a one previous post for the Just Learned tips for August Month as well. Hope you liked that as well and those tips helped might be. Here I am pasting all the posts for the Month of August, You can find the links and read also. Just Learned summary - A Month of July, 2011 Stored Procedure Encryption - SQL Server TSQL Debugger for Stored Procedure - SQL Server 2008 Automated All Databases Backups Script - SQL Server Split Database Full Backup to Multiple files - SQL Server Custom Code Snippet - SQL Server Denali "Login failed for user 'username'. The user is not associated with a trusted SQL Server connection" - SQL Server Monitor SQL Server's Activity with