| Archivio Posts | |
Anno 2015
Anno 2014
Anno 2013
Anno 2012
Anno 2011
Anno 2010
Anno 2009
Anno 2008
Anno 2007
|
|
 Microsoft solution to automate backup on SQL Express
Microsoft solution to automate backup on SQL Express(SQL Server Agent is not available on this SQL release)Microsoft Solutionhttps://support.microsoft.com/en-us/kb/2019698Sample Backup.cmd filesqlcmd -U
sa -P xxxx -S .\SQL2012EXPRESS -Q "EXEC sp_BackupDatabases
@backupLocation = 'C:\SQLBackups\', @backupType = 'F'"You can schedule jobs with standard "Windows Jobs"  Leggi tutto il post...
 sabato, 18 apr 2015 Ore. 17.31
Microsoft Dynamics NAV/SQL Server Configuration RecommendationsMicrosoft Dynamics NAV/SQL Server Configuration Recommendations...old but VERY GOOD & ACTUAL post about NAV/SQL Configuration by NAV TeamMichael De Voe, a Senior Premier Field Engineer at Microsoft, has compiled a set of recommendations for SQL Server configuration to improve performance when running Microsoft Dynamics NAV 5.0 and later versions with one of the following versions of SQL Server:Microsoft SQL Server 2005 SP3 x64Microsoft SQL Server 2008 SP1 x64Microsoft SQL Server 2008 R2 x64& Leggi tutto il post...
venerdì, 12 set 2014 Ore. 21.53
How to repair a SQL Server "Suspect database"How to repair a SQL Server "Suspect" database
Check DB
Sometimes when you connect to your database
server, you may find it in suspect mode. Your
database server won’t allow you to perform any operation on that database until
the database is repaired.
A database can go in suspect mode for many reasons
like improper shutdown of the database server, corruption of the database files
etc.
To get the exact reason of a
database going into suspect mode can be found using the
following Leggi tutto il post...
martedì, 15 lug 2014 Ore. 00.23
Come visualizzare le 50 query più pesanti - Sql ServerSQL Server - How to show most 50 heavy query (come visualizzare le 50 query più pesanti - Sql Server)... very nice SQL Script to discover slow NAV Sessions !!SQL SCRIPTSELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.TEXT)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2)+1),qs.execution_count,qs.total_logical_reads, qs.last_logical_reads,qs.total_logical_writes, qs.last_logical_writes,qs.total_worker_time Leggi tutto il post...
martedì, 15 lug 2014 Ore. 00.17
Sql Server reset "db owner" lost on database Sql Server reset "db owner" lost on database
USE Database
exec sp_changedbowner 'sa', true
>> reassign user "sa" on user dbo lost on database
Leggi tutto il post...
venerdì, 18 gen 2013 Ore. 14.04
Kill sessioni NAV dormienti senza transazioni aperte da piu di 20 minuti-- Kill sessioni NAV dormienti senza transazioni aperte da piu di 20 minuti
USE ER_P
DECLARE @v_spid INTDECLARE c_Users CURSORFAST_FORWARD FOR
SELECT spid FROM master..sysprocesses (NOLOCK) WHERE spid>50 AND (program_name LIKE N'%Microsoft Dynamics NAV%') AND (program_name not like N'%Microsoft Dynamics NAV WEB%') AND status='sleeping' AND DATEDIFF(mi,last_batch,GETDATE())>20 AND spid<>@@spid &nbs Leggi tutto il post...
lunedì, 14 gen 2013 Ore. 12.40
List all processes running on SQL Server
-- List all processes running on SQL Server
SELECT [Database]=DB_NAME(dbid), spid, last_batch, status, hostname, loginame
FROM sys.sysprocesses
WHERE dbid = DB_ID('ER_P'); Leggi tutto il post...
lunedì, 14 gen 2013 Ore. 11.35
Trasferimento di accessi e password tra istanze di SQL Server
Trasferimento di accessi e password tra istanze di SQL Server
Aprire una nuova finestra dell'Editor di Query e quindi eseguire lo script seguente.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @char Leggi tutto il post...
giovedì, 18 ott 2012 Ore. 16.51
High Performance SQL Views Using WITH(NOLOCK)High Performance SQL Views Using WITH(NOLOCK)
Every now and then you find a simple way to make everything much faster. We often find customers creating data warehouses or OLAP cubes even though they have a relatively small amount of data (a few gigs) compared to their server memory. If you have more server memory than the size of your database or working set, nearly any aggregate query should run in a second or less. In some situations there may be high traffic on from the transactional a Leggi tutto il post...
giovedì, 18 ott 2012 Ore. 15.51
Come visualizzare le 50 query più pesanti - Sql Server
SELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_ti Leggi tutto il post...
martedì, 14 feb 2012 Ore. 14.04
SQLCMD utilityL'utilità SQLCMD è scritta in .NET 2.0 e comunica con SQL Server via Client SQL Nativo oppure via ODBC. Fornisce un ambiente di scripting ricco di funzionalità (tra cui una primitiva gestione degli errori) in grado di mettere l'utente nelle condizioni di compiere sul server attività di elevata complessità.
È uno strumento poliedrico, perchè sia l'amministratore di sistema (per la creazione di script di manutenzione per database o tabelle) sia lo sviluppatore (per la creazione di sofisticati wiz Leggi tutto il post...
martedì, 25 ott 2011 Ore. 12.17
Designing Reports in Report Designer and Report Builder 3.0Report Builder and Report Designer Features
The following table summarizes differences in the two report authoring environments for typically used features.
Area
Report Builder 3.0
Report Designer
Description
Report Builder is a report authoring environment for business users who prefer to work in the Microsoft Office environment. You work with one report at a time. You can modify a published report directly from a report server. You can quickly build a repo Leggi tutto il post...
lunedì, 24 ott 2011 Ore. 13.34
How to transfer the logins and the passwords between instances of SQL ServerTo transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:
1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
2. Open a new Query Editor window, and then run the following scriptUSE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULLDROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal@binvalue varbinary(256),@hexva Leggi tutto il post...
sabato, 24 set 2011 Ore. 20.57
SQL CLEAN AUTO STATS & CREATE NEWWell, the "Auto. Create Stats" and also "Auto. Update Stats" could be a real pain; I recommend to disable it.
THe sufficiently maintain the required statistics you could implement a SQL Agent Job (or TSQL task for the MP) doing this:
use [MyNAVdb]goexec sp_updatestatsgo
exec sp_createstats 'indexonly'
To get rid of the already existing auto-stats, you could exec this script (part of the NAV/SQL Performance Toolbox)
declare @id int, @name varchar(128), @statement nvarchar(1000)declare stat Leggi tutto il post...
mercoledì, 21 set 2011 Ore. 09.48
Install & Config "xp_ndo_enumusergroups", "xp_ndo_enumusersids" NAV SQL extended stored proceduresInstall & Config "xp_ndo_enumusergroups", "xp_ndo_enumusersids" NAV SQL extended stored procedures
USE master EXEC sp_addextendedproc xp_ndo_enumusergroups, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo_64.dll' GO
GRANT EXECUTE ON [xp_ndo_enumusergroups] TO PUBLIC GO
USE master EXEC sp_addextendedproc xp_ndo_enumusersids, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo_64.dll' GO GRANT EXECUTE ON [xp_ndo_enumusersids] TO PUBLIC GO Leggi tutto il post...
mercoledì, 31 ago 2011 Ore. 15.13
|
| Statistiche |
- Views Home Page: 471.688
- Views Posts: 888.879
- Views Gallerie: 0
- n° Posts: 343
- n° Commenti: 0
|
|