AX Development

SELECT name as 'File Name', physical_name as 'File Directory' FROM sys.master_files WHERE database_id = DB_ID('tempdb'); GO /* Moves the Temp Database*/ USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLData\tempdb.mdf'); GO ALTER DATABASE  tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\SQLData\templog.ldf'); GO /* The new path will be used the next time the database is started. The database used is: MASTER. You must restart SQL Server*/ /*Verification of TEMPDB move*/ SELECT name as 'File Name', physical_name as 'File Directory' FROM sys.master_files WHERE database_id = DB_ID('tempdb'); GO

select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)                 , R.cpu_time, R.total_elapsed_time, R.percent_complete from    sys.dm_exec_requests R                 cross apply sys.dm_exec_sql_text(R.sql_handle) T

CREATE procedure [dbo].[DBADS_SP_CHECK_JOB_PROGRESS] @SERVERNAME VARCHAR(200), @BACKUPJOBRUNNING INT OUTPUT AS DECLARE @JOB_ID VARCHAR(100), @RUNNABLE INT, @CMD VARCHAR(2000), @CMD1 VARCHAR(2000) SET @RUNNABLE = 0 SET @BACKUPJOBRUNNING = 0 IF (SELECT IS_LINKED FROM sys.servers WHERE NAME = @SERVERNAME)= 1 BEGIN IF (SELECT RETIRED FROM dbo.servers WHERE SERVERNAME = @SERVERNAME) = '9999-12-31 00:00:00.000' BEGIN /* CREATE A GLOBAL TEMP TABLE AND INSERT THE DATA FROM SYSJOBS*/ IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE ##TEMP CREATE TABLE ##TEMP (JOB_ID VARCHAR(100), RUNNING VARCHAR(100)) SET @CMD1 = 'INSERT INTO ##TEMP(JOB_ID) SELECT JOB_ID FROM ['+ @SERVERNAME + '].msdb.dbo.sysjobs WHERE UPPER(NAME) LIKE ''%COMPLETE%''' EXEC(@CMD1) DECLARE CURSORBACKUP CURSOR FOR SELECT JOB_ID FROM...

Hey, I was trying to restore a database from a backup file on a new server and got the following error. I am using the SQL 2008 version of management studio. Solution:  Check if you have the rights to create a new Database. Else create a new login and give the required permissions.

DECLARE @DATABASENAME VARCHAR ( 2000 ), @DBID INT , @SQL VARCHAR ( 4000 ), @STATUS SQL_VARIANT IF OBJECT_ID ( 'TEMPDB..##TBLDB' ) IS NOT NULL DROP TABLE TEMPDB .. ##TBLDB /* CREATING A TEMP TABLE*/ CREATE TABLE ##TBLDB ( TABLENAME VARCHAR ( 200 ), INDEXNAME VARCHAR ( 200 ), INDEXID INT , DATABASENAME VARCHAR ( 200 )) /* CURSIN THROUGH ALL THE DATABASES ON THE SERVER*/ DECLARE CURSOR_DATABASES CURSOR FOR       SELECT             NAME , DATABASE_ID       FROM             SYS . DATABASES       WHERE             NAME NOT IN ( 'MASTER' , 'MODEL' , 'MSDB' , 'TEMPDB' ) OPEN CURSOR_DATABASES FETCH NEXT FROM CURSOR_DATABASES INTO @DATABASENAME , @DBID WHILE @@FETCH_STATUS = 0 BEGIN       SELECT @Sta...

DECLARE @DBID INT SELECT @DBID = DB_ID () SELECT       OBJECTNAME = OBJECT_NAME ( S . OBJECT_ID ),       S . OBJECT_ID ,       INDEXNAME = I . NAME ,       I . INDEX_ID ,       USER_SEEKS ,       USER_SCANS ,       USER_LOOKUPS ,       SYSTEM_SCANS ,       ( USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES + SYSTEM_SCANS ) AS TOTAL_USAGE FROM       SYS . DM_DB_INDEX_USAGE_STATS S ,       SYS . INDEXES I WHERE       DATABASE_ID = @DBID       AND OBJECTPROPERTY ( S . OBJECT_ID , 'ISUSERTABLE' ) = 1       AND I . OBJECT_ID = S . OBJECT_ID       AND I . INDEX_ID = S . INDEX_ID   ...

SELECT ROUTINE_NAME --, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA . ROUTINES WHERE ROUTINE_NAME LIKE '%DBADS_SP_CHECK_JOB_PROGRESS%'       AND ROUTINE_TYPE = 'PROCEDURE'

DECLARE @SERVERNAME VARCHAR(100), @SQL VARCHAR(2000) DECLARE CURSORSERVER CURSOR FOR SELECT SERVERNAME FROM dbacentral.dbo.servers WHERE RETIRED ='9999-12-31 00:00:00.000' AND ENABLED = 1 AND SERVERNAME NOT IN ('BHSF-BTR-W310','BHSF-BTR-W333') AND SQLVersion NOT LIKE '%Microsoft SQL Server  2000%' OPEN CURSORSERVER FETCH NEXT FROM CURSORSERVER INTO @SERVERNAME WHILE @@FETCH_STATUS = 0 BEGIN PRINT @SERVERNAME SET @SQL ='CREATE LOGIN [dbu_DbaCentral] WITH PASSWORD=N''''dbudc'''', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF CREATE USER [dbu_DbaCentral] FOR LOGIN [dbu_DbaCentral] GRANT SELECT ON msdb.dbo.sysjobs TO [dbu_DbaCentral] GRANT execute ON msdb.dbo.sp_get_composite_job_info TO [dbu_DbaCentral]' EXEC('exec [' + @ServerName + '].msdb.dbo.sp_executesql ' + 'N''' + @SQL + '''') FETCH NEXT FROM CURSORS...