Learn, Troubleshoot BizTalk with Ashok Rajpurohit

BizTalk Oracle/SQL execution error on ReceiveLocation When we try to call SQL/Oracle StoredProcedure/package from the ReceiveLocation with Default PollingAction generated then it might give error as   we need to add “Polling” in the name of package or stored procedure. The Messaging Engine failed to add a receive location "Zim.EAI.Framework.RL_ReTestGet" with URL "oracledb://goDEV1/?PolllingID=1111" to the adapter "WCF-Custom". Reason: "System.NotSupportedException: Action "http://Microsoft.LobServices.OracleDB/2007/03/EBT/Package/BL_GO_PCK/GETDATA_XML" is invalid.    at Microsoft.Adapters.OracleDB.OracleDBInboundContract..ctor(OracleDBConnection connection, IOracleCommonUDTHelper oracleUdtHelper, MetadataLookup metadataLookup) Auto Generated Action : http://Microsoft.LobServices.OracleDB/2007/03/EBT/Package/BL_GO_PCK/GETDATA_XML http://Microsoft.LobServices.OracleDB/2007/03/EBT/Procedure/B...

Following SQL query is used to insert multiple record into table INSERT INTO tmpTable (field1, field2,field3) VALUES  ('74251', 'Black', '511black') INSERT INTO tmpTable  (field1, field2,field3) VALUES  ('74251', 'CharcoalF', '5145') INSERT INTO tmpTable  (field1, field2,field3) VALUES  ('74251', 'KhakiQ', '5165') INSERT INTO tmpTable  (field1, field2,field3) VALUES  ('74251', 'NavyA', '515') INSERT INTO tmpTable  (field1, field2,field3) VALUES  ('74251', 'OD', '511')

SQL Query to get random records from table Following query is used to get random number of records from SQL table SELECT TOP 5 *   FROM [JukeBox].[dbo].[tbl_Debate]  ORDER BY NEWID() Following query is update with were clause SELECT TOP 5 *   FROM [JukeBox].[dbo].[tbl_Debate]   where ID not in (21,5,6,20,11,12)  ORDER BY NEWID()

SQL Query to check length of the field and using IF ELSE IF LEN ( @UserLike ) > 0 BEGIN UPDATE tbl_Kukoo SET UserLike = @UserLike where ID = @ID END LEN ( @UserLike ) > 0   - to get length of SQL field.

SQL TABLE ALTER SCRIPT Following script is used to alter sql table with default value. -- Adding Value with Default Value ALTER TABLE TableName ADD ColumnName INT NOT NULL DEFAULT ( 0 ) GO Script alter Column type ALTER TABLE mytable ALTER COLUMN mycolumn newtype

1.                   Download AdventureWorksDB file from Microsoft link http://msftdbprodsamples.codeplex.com/releases/view/4004 2.                   run and install   the .MSI file on your system , Make sure after installing AdventureWorkDB the following files exists in folder location “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data” a.        AdventureWorks_Data.mdf b.       AdventureWorks_Log.ldf 3.                   Once the installation is completed then we need to attach AdventureWorksDB to SQL server. a.        Right Click on Database,Click Attach  b.       On Attach database screen,click Add ...

For normal tables, you can use this query (SQL Server 2000):   IF EXISTS (SELECT 1     FROM INFORMATION_SCHEMA.TABLES     WHERE TABLE_TYPE='BASE TABLE'     AND TABLE_NAME='tablename')         SELECT 'tablename exists.' ELSE         SELECT 'tablename does not exist.' -- or DECLARE @isObject TINYINT, @isTable TINYINT, @objID INT SET @objID = OBJECT_ID('tablename') SELECT @isObject = COALESCE(@objID, 0),     @isTable = OBJECTPROPERTY(@objID, 'IsUserTable') IF @isObject + @isTable = 2     SELECT 'tablename exists.' ELSE     SELECT 'tablename does not exist.'  In SQL Server 7.0, you can query sysobjects:   IF EXISTS (SELECT 1     FROM sysobjects     WHERE xtype='u' AND name='tablename')         SELECT 'tablename exists.' ELSE         SELECT 'tablename does not exist.' ...

http://blog.sqlauthority.com/2007/11/04/sqlauthority-news-best-articles-on-sqlauthoritycom/ There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE . It is tough question. I answered : E xecution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again) Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only. Execution plan retention and reuse Query auto-parameterization Encapsulation of business rules and policies Application modularization Sharing of application logic between applications Access to database objects that is both secure and uniform Consistent, ...