SqlServer Begin's

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...