AX Development

Hey All, I am trying to get a list of error messages on a server using the sp_readlogerror stored procedure. I have a central server A and I am trying to get the information from Server B. exec [server B].model.sys.sp_readerrorlog the above statement works well and gets the data, But it throws up a transaction error when i try to insert into a temp table on server A. Here is the code running on server A: create table #ErrorLog(LogDate datetime, ProcessorInfo varchar(1000),Text varchar(5000)) insert into #errorLog exec [Server B].model.sys.sp_readerrorlog this Throws up a transaction error as below OLE DB provider "SQLNCLI" for linked server "BHSF-BTR-W318" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "BHSF-BTR-W318" was ...

Hey Guys, I have come across a very interesting article by one of the members (Paul White)  of the SQLServerCentral group. Here is the Link to that:   http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx have Fun....

/*This Script is used to list all the Unused tables on a Server. The script does not work for the sql 2000 Server*/ declare @sql varchar(2000), @DatabaseName varchar(100) /* LOOP THROUGH THE SERVERS*/ If Object_ID('tempdb..#unusedTables') is not null drop table #unusedTables create table #unusedTables(DatabaseName varchar(100), Object_Id int, TableName varchar(100))    /*CURSES THROUGH ALL THE DATABASES ON THE SERVER  */     declare Database_list cursor for         select     name from     sys.databases where     name not in ('master','msdb','modeL','tempdb')     open Database_list fetch next from Database_list into @DatabaseName while @@fetch_status = 0 begin      /* THIS PART GETS THE LIST OF TABLES IN A PARTICULAR DATABASE*/         set @sql =    'select   ...

USE [MTSC] GO /****** Object:  StoredProcedure [dbo].[TimesheetHoursDailyReminder]    Script Date: 11/03/2010 13:28:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[TimesheetHoursDailyReminder] AS /******************************************************************************** Stored Procedure Name:    [dbo].[TimesheetHoursDailyReminder] Date Written:             10/06/09            Author:                    Chitanya Chitturi Project/System:            MTSC                Purpose/Description:    Time Sheet Reminder Input Parameters:        Tables:       ...

/*FINAL CODE*/ declare @servername varchar(200), @sql varchar(2000) /* LOOP THROUGH THE SERVERS*/ if object_id('tempdb..#failedjobs') is not NULL drop table #failedjobs create table #failedjobs (servername varchar(200), jobname varchar(500)) declare server_list cursor for select     servername from     dbo.servers where     enabled =1     and retired = '9999-12-31 00:00:00.000'     open server_list fetch next from server_list into @servername while @@fetch_status = 0     begin         set @sql =    'select                     jh.server as server_name,                     sj.name job_name                 from  ...

USE [rmsprod] GO /****** Object:  StoredProcedure [rms].[PROC_SERVER_ACTIVITY]    Script Date: 10/19/2010 15:39:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [rms].[PROC_SERVER_ACTIVITY] AS BEGIN /***************************************************************************************** Stored Procedure Name:   [dbo].[SERVER_ACTIVITY] Date Written:            AUGUST 2010 Author: CHITANYA CHITTURI                                   Project/System:                                                       Purpose/Description:     Input Parameters:     Output Parameters:                 ...

CREATE TABLE YourLogTable(ModificationTime DATETIME, [Application] NVARCHAR(500), [SQL] NVARCHAR(MAX)) GO CREATE TRIGGER [dbo].[trgUpdateLogger] ON [dbo].[YourTable] FOR INSERT,UPDATE,DELETE AS --Get updating process info CREATE TABLE #s(EventType VARCHAR(30), Parameters INT, SQL NVARCHAR(MAX)) INSERT INTO #s EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS') --Write to log table INSERT INTO YourLogTable (ModificationTime, [Application], [SQL]) SELECT GETDATE(), APP_NAME(), #s.SQL FROM #s --Cleanup DROP TABLE #s GO Note: I found it on LinkedIN... thought it might be useful

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

Here are the steps to find the Underlying table for the PRIMS           Note: Click on the Images to Enlarge Image 1 rmsDev>GuiDesigner>ProviderTask > Info Boxes Look for the required option (front screen). See image1 Image 2 Select the whole screen; right click to go to the Property Inspector. Select the query option from the drop down list and click on the Underlying query (Image 2)