Generate GUID / Global Unique Identifer simple approach

DECLARE @CNT AS INT =1; 

CREATE TABLE #tmpguid   (      id UNIQUEIDENTIFIER   ) 
WHILE ( @CNT < 100 )   BEGIN       INSERT INTO #tmpguid                   (id)       VALUES      (Newid()) 
      SET @CNT = @CNT + 1   END 
SELECT * FROM   #tmpguid 

SSIS Web Service Task: 417 Expectation Failed

I had some bad time with SSIS web service task... our daily loads used to throw me an exception....

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The request failed with HTTP status 417: Expectation Failed..
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

I tried fighting with Fiddler, then it has thrown me some other Error like could not establish secure connection between the server..
at last Ghees Alias saved my life [http://blog.qburst.com/2013/05/configure-ssis-web-service-task-effectively/]

all we need to do is to add the below configuration in 

Go to C:\Program Files\Microsoft SQL Server\110\DTS\Binn. Here, you will find 2 files, namely DtsDebugHost.exe.config and DTExec.exe.config. You need to modify these two files. Open each file on Visual Studio. Copy-paste the following tags inside the configuration tag of each file.

   

     

   

The configuration files should now look like this:

in normal C# coding in any script we can use the below line before we call our web method..

System.Net.ServicePointManager.Expect100Continue = false; 

Enjoy, it has started working fine.. woooooooh...

SSIS Parent Package Variables configurations

I had a scenario for executing a package like a stored procedure with different parameters.
I did not want to create multiple copies of the package. so here's my approach.

1. Create Parameter Variable(s) in the Child Package [note here, the names should be consistent]
2. Create Parameter Variable(s) in the Parent Package(s) and assign / modify the values in Parent package itself, then simply use a Execute Package Task to execute the child package. nothing else to be configured here. Variable Names and parent package configuration by default takes care of the variable value assignment.
3. The End.

Made life easy.. 

Cheers

Vivek.

MS Support Script for Creating Missed Indexes in all Tables

PRINT 'Missing Indexes: ' 

PRINT 

'The "improvement_measure" column is an indicator of the (estimated) improvement that might '

PRINT 

'be seen if the index was created. This is a unitless number, and has meaning only relative '

PRINT 

'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '

PRINT 

'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

PRINT '' 

PRINT '-- Missing Indexes --' 

SELECT CONVERT (VARCHAR, Getdate(), 126)                              AS runtime 

       , 

       mig.index_group_handle, 

       mid.index_handle, 

       CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact 

                                 * ( 

                                 migs.user_seeks + migs.user_scans )) AS 

       improvement_measure, 

       'CREATE INDEX missing_index_' 

       + CONVERT (VARCHAR, mig.index_group_handle) 

       + '_' + CONVERT (VARCHAR, mid.index_handle) 

       + ' ON ' + mid.statement + ' (' 

       + Isnull (mid.equality_columns, '') + CASE WHEN mid.equality_columns IS 

       NOT NULL 

       AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 

       + Isnull (mid.inequality_columns, '') + ')' 

       + Isnull (' INCLUDE (' + mid.included_columns + ')', '')       AS 

       create_index_statement, 

       migs.*, 

       mid.database_id, 

       mid.[object_id] 

FROM   sys.dm_db_missing_index_groups mig 

       INNER JOIN sys.dm_db_missing_index_group_stats migs 

               ON migs.group_handle = mig.index_group_handle 

       INNER JOIN sys.dm_db_missing_index_details mid 

               ON mig.index_handle = mid.index_handle 

WHERE  CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact 

                                 * ( 

                                        migs.user_seeks + migs.user_scans )) > 

       10 

ORDER  BY migs.avg_total_user_cost * migs.avg_user_impact * ( 

                    migs.user_seeks + migs.user_scans ) DESC 

PRINT '' 

go 

How to Select the Best Women

SELECT
ThePerfectWoman

FROM   AllTheWomenInTheWorld

WHERE  HerInterests
LIKE Mine

      
AND HerMate IS
NULL

      
AND LikesToLive IN (
'Where',
'Ever',
'I',
'Want',
'to',
'live' )

GROUP  BY
CASE

         
WHEN EverMyFeelings>=Sad

                
THEN SheCanMakeMeHappy
ELSE IwillMakeHerHappy
* 2

        
END

HAVING Max(Attitude)
> Good

MS SQL CTE wonders Display 1 to 100 without Looping

I happened to solve a puzzle.
Display 1 to 100 in SSMS without using any loop or table or table variable or even cursor.

I gave up, then got the solution from requester.

WITH cte 

     AS (SELECT 1 Num          UNION ALL          SELECT num + 1          FROM   cte          WHERE  num < 100) SELECT * FROM   cte 


Always wonder with common table expressions. happy querying