Suppose you use a Sql Server user to execute a shell command on a remote shared folder exec xp_cmdshell ‘command UNC path’ and you get an Access Denied error. This is because MSSQLSERVER, SQLSERVERAGENT services run as Local System Account that is not recognized on the remote machine you are pointing by the UNC path. Solution: [...]
Archive for the ‘TSQL’ Category
Access Denied issue when exec xp_cmdshell on a UNC pathname
Posted in Database, How To, SqlServer 2000, SqlServer 2005, TSQL, Windows on March 28, 2007 | Leave a Comment »
XML Select with Microsoft SqlServer 2000
Posted in SqlServer 2000, TSQL, XML on March 4, 2007 | Leave a Comment »
Avete mai provato ad aggiungere la clausola FOR XML AUTO in fondo a una select fatta su SQLServer 2000? Provate, otterete come risposta un recordset di una riga una colonna con dentro una bella stringona che รจ un XML Fragment contenente la risposta alla Vs query. La sintassi generale per avere resultset in formato XML [...]
How to Restart IIS from a Sql Server Agent Job
Posted in Database, SqlServer 2000, SqlServer 2005, TSQL on March 1, 2007 | Leave a Comment »
Suppose to have an ASP3 or ASP.Net application running on the same machine where you Microsoft Sql Server runs. For sure you have experienced that IIS grows up in memory (or better: the underlying worker processes) day by day decreasing constantly the overall system performances. The most used solution to restore an initial idle state [...]
Sql Server Agent – How to Test Job Execution Status
Posted in SqlServer 2000, SqlServer 2005, TSQL on February 28, 2007 | Leave a Comment »
Have you some Sql Server Agent job you want to test the execution status? EXEC msdb..sp_help_job @job_name = ‘InfiniteLoop’, @job_aspect = N’job’ The result set includes a column called current_execution_status, but you might not know how to interpret the column values because SQL Server Books Online (BOL) doesn’t document them. However, you can learn a [...]
Change users login after a SqlServer 2000 Restore or a Detach Attach
Posted in Database, SqlServer 2000, TSQL on February 22, 2007 | Leave a Comment »
Problem: You make a SqlServer 2000 DB backup from server A. You need to restore it on server B. In alternative suppose you detach a DB from server A to attach it on server B (copying the MDF, NDF and LDF files). After the restoration, what you get at 99% is that to the DB [...]
Paged Select with SqlServer 2005, finally!
Posted in Database, SqlServer 2005, TSQL on February 5, 2007 | Leave a Comment »
I remember the annoying tricks with SqlServer 2000 to have paged results from a query. For example, in MySql exists the useful keyword limit … using limit 10,10 extracts 10 records after the 10th. But the question is, what exists in SqlServer 2005? Well it is not so easy as in MySql but you can [...]
