SqlServer Begin's

Very recently i wanted to migrate the data from 10M record table. This table didn't have clustered key and the business primary key was a GUID column.  For obvious reasons the data needed to be transferred in chunks. The listed code provided the upper and lower boundaries.     DECLARE @NumParts INT = 128; WITH PartsOf256(Part)  AS ( SELECT (256 / @NumParts) UNION ALL SELECT Part + (256 / @NumParts) FROM PartsOf256 WHERE Part < (256 - 256 / @NumParts) ) SELECT CONVERT(UNIQUEIDENTIFIER, 0x00000000000000000000 + CONVERT(VARBINARY(1), Part)) FROM PartsOf256 option (maxrecursion 0)

Including one more script to my script volt. I have always wanted to easily identify the dependent objects for a procedure. The core of code was grabbed from http://www.sqlservercentral.com/scripts/Cross+Reference/108582/ , I have changed the original script to accommodate the common filter conditions and other dependent  objects that I thought was needed. The query identifies the following dependent objects -          -   Tables -          -   Views -          -   Functions -          -   Procedures   /* Parameter -- @ProcedureName ( by default it will be set to  @ProcedureName  = '-1') --  @ProcedureName  = '-1' will  -- generate dependencies for all proceudre in the currnt database --  @ProcedureName  = ' ' + '%' -- will populate the dependencies which are in the LIKE clause */ DE...

Last week I had to investigate a problem with a third party databases in the Health industry and was told by the product owner to look into the potential issue and provide a set of recommendations. One of my objectives were to identify tables with clustered indexes and the composition of the index. i.e if the clustered index had multiple columns and the if the fist column was not incremental As usual,  I hope this script helps  IF OBJECT_ID ( 'TEMPDB..#Temp_Constraint_Columns' ) IS NOT NULL       DROP TABLE TEMPDB .. #Temp_Constraint_Columns CREATE TABLE #Temp_Constraint_Columns       (             IID int identity ( 1 , 1 )             , ObejctName varchar ( 100 )             , Index_Type varchar ( 50 )    ...

I ran into a situation where It required me to transfer several securable for a particular user from PROD  to a UAT environment. I was surprised to find there wasn't any easy way to do this through a GUI.   Google to the rescue and found a simple query at http://www.sqlservercentral.com/Forums/Topic1360174-391-1.aspx , Few changes were done to accommodate my needs.      SELECT DP . State_desc + ' ' + DP . permission_name +               ' ON ' + object_name ( DP . major_id ) +               ' TO ' + '[' + SU . Name   COLLATE DATABASE_DEFAULT   + ']' FROM sys . database_permissions DP JOIN sys . sysusers SU ON   SU . UID = DP . grantee_principal_id WHERE SU . Name = ' ' order by object_name ( DP . major_id )

1.     Click Start, click Run, type  wbemtest  in the Open box, and then click  OK . 2.     In the Windows Management Instrumentation Tester dialog box, click  Connect . 3.     In the box at the top of the Connect dialog box, type  root\cimv2 , and then click  Connect . 4.     Click #Enum Instances". 5.     In the Class Info dialog box, type  Win32_OperatingSystem  in the Enter superclass name box, and then click  OK . 6.     In the Query Result dialog box, double-click the top item. Note this item starts with "Win32_OperatingSystem.Name=Microsoft..." 7.     In the Object editor dialog box, locate the  PAEEnabled  property in the Properties area and double-click on it. 8.     In the Property Editor dialog box, note the value in the Value box.

One of the clients needed its users to remotely execute a SQL job and as usual I picked this up hoping for a quick brownie point. Sure enough there was a catch and there was something to learn. Executing the job through SQLCMD was a no-brainer but getting it to execute on the remote machine was bit of challenge. On the coding Front 1    1.)     The bat file included the following code                 SQLCMD -S "[ServerName] " -E -Q "EXEC MSDB.dbo.sp_start_job @Job_Name = ' '[JobName]" 2    2.)     The Individual users were given minimum permissions  to execute the SQL job Ex. use msdb EXECUTE sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = ' Domain\UserLogin ' At the client machine              This took a fair bit of time till our sysadmin got me an empty VM machine....

Even though there are many ways of working around the TEMDB and user database collation conflicts when using #Temp_* tables. I ran into a situation where I couldn’t find a wayout. The problem was with the SQL Spatial function STGeometryType. Of what I realised, this function just didn’t like the #Temp_* table collation even if the table was created with same collation as the source table. I also couldn’t find a way of converting the returned value to a simple text and neither was I able to explicitly convert it to the required collation at the time of the equality operation. The query that failed look like the following SELECT   * FROM   #Temp_tempt WHERE   Geometry_SPA.STIsValid()=1 AND   Geometry_SPA.MakeValid().STGeometryType()   IN   ( 'LineString' , 'MultiLineString' )       AND   Geometry_SPA.MakeValid().STLength()<0.1 FYI – The Geomerty columns don’t have spatial collation It appeared, w...