Feeds:
Posts
Comments

Archive for the ‘TSQL’ Category

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:

  • go to Control Panel, Administrative Tools, Services 
  • stop  MSSQLSERVER and SQLSERVERAGENT
  • change Logon as user from local system to This User, specifying an administrative account that is the same on the two machine and with the same password (be aware changing it).
  • start  MSSQLSERVER and SQLSERVERAGENT
  • give privileges to that account to the remote shared folder
  • retry the exec xp_cmdshell
Advertisements

Read Full Post »

XML SelectAvete 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 fragment è:

Code:

SELECT colonne_da_visualizzare
FROM nome_tabella
WHERE condizioni_di_ricerca
FOR XML AUTO | RAW | EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]

Esempio 1

SELECT nome,cognome FROM utenti FOR XML AUTO

restituisce le righe come elementi e le colonne come attributi, esempio:
Code:

<utenti nome="Dino" cognome="Olivieri" />
<utenti nome="Enrico" cognome="Porta" />

Esempio 2

SELECT nome,cognome FROM utenti FOR XML RAW

RAW sostituisce il nome riga della tabella con row sia che usi gli elements (vedi esempio successivo) sia che non li usi:
Code:

<row nome="Enrico" cognome="Porta" />

Esempio 3

SELECT nome,cognome FROM utenti FOR XML AUTO, ELEMENTS

restituisce le righe come elementi e le colonne come elementi figlio, esempio:
Code:

<utenti>
  <nome>Dino</nome>
  <cognome>Olivieri</cognome>
</utenti>
<utenti>
  <nome>Enrico</nome>
  <cognome>Porta</cognome>
</utenti>

Esempio 4

Ecco come ottenere un Fragment TR, TD con tanto di intestazione pronto da impaginare in un documento HTML

Code:

SELECT TOP 1 'ID GRUPPO' as td, 'NOME GRUPPO' AS td FROM table_groups AS tr
UNION ALL
SELECT TOP 10 CONVERT(varchar, idgroup) AS td, group_name AS td FROM table_groups AS tr
FOR XML AUTO, ELEMENTS

Read Full Post »

How to restart IIS…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 of the system is to schedule a nightly IIS RESTART.

Also, many System Administrators wants to keep a unique eye on a unique Job Scheduler and Log, and if you have Sql Server Agent you can think to use it to run any kind of processes (from stored procedure to windows executables).

So, to restart your IIS, schedule this piece of T-SQL code directly in Sql Server Agent:

exec master..xp_cmdshell 'iisreset /RESTART'

Simply not?

Read Full Post »

Running RobotHave 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 lot by directly reading stored-procedure code. The parameter-declaration section of sp_help_job, as shown below, tells you how to interpret the value of current_execution_status:

  • @execution_status INT = NULL,
  • — 1 = Executing,
  • — 2 = Waiting For Thread,
  • — 3 = Between Retries,
  • — 4 = Idle,
  • — 5 = Suspended,
  • — 6 = [obsolete],
  • — 7 = PerformingCompletion
  • — Actions

For example, a job that’s running will have a value of 1; values 2 and 3 show that the job has started but isn’t executing commands.
Now, you can pass the result set to your program by using the syntax:

INSERT INTO MyTable EXEC MyProc

which stores sp_help_job’s output in a table. . . .

Read Full Post »

SqlServer 2000Problem:
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 users do not correspond any security LOGIN.
So you have to rebuild this lost link…

Solution:
First of all get a report of the unlinked users logins:

sp_change_users_login 'report'

If there is an old login that gives you problems, delete it.
Then (re)create the login you need.
Then link the new login with users using the following stored procedure:
Here the syntax:

EXEC sp_change_users_login 'Update_One', 'username',
'loginname'

Read Full Post »

SqlServer 2005I 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 do it like:

Code:

declare @pagesize int;
declare @pageindex int;

-- page size
set @pagesize = 10;
-- page offset where 0 == first page
set @pageindex = 0;
select top(@pagesize) *
from sysobjects
where id not in
(
select top(@pagesize * @pageindex) id
from sysobjects
order by name asc
)
order by name asc;

Read Full Post »