SQL TIPS AND TRICKS

Trick 1: -

If your any Database moving on Single User, then apply this Query:

ALTER DATABASE [Database Name] SET MULTI_USER

GO

Trick 2: -

If you facing your SQL database drive is full and there is no space, then apply this

Query:

USE MicrosoftDynamicsAX;

ALTER DATABASE MicrosoftDynamicsAX

SET RECOVERY SIMPLE;

DBCC SHRINKFILE (MicrosoftDynamicsAX_log, 0);

ALTER DATABASE MicrosoftDynamicsAX

SET RECOVERY FULL

Trick 3: -

If you want any database table copy and paste any database, then apply this Query:

select * into [Database Destination Name].dbo.[Table Name] from [Table Name]

Trick 4: -

Install Particular Instance SQL Reporting Extension apply the Query:

Install-AXReportInstanceExtensions –ReportServerInstanceName [SSRSInstanceName] -Credential [DomainName\UserName]

Trick 5: -

Create server session and Create user session

Create server session

USE [YourDatabaseNameGoesHere]

GO

/****** Object: StoredProcedure [dbo].[CREATESERVERSESSIONS]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[CREATESERVERSESSIONS] @aosId varchar(50), @version int, @instanceName nvarchar(50), @recid bigint, @maxservers int, @status int, @loadbalance int, @workload int, @serverid int OUTPUT as declare @first as varchar(50) declare @max_val as int begin select top 1 @first = SERVERID from SYSSERVERSESSIONS WITH (UPDLOCK, READPAST) where STATUS = 0 and AOSID = @aosId and INSTANCE_NAME = @instanceName if (select count(SERVERID) from SYSSERVERSESSIONS where SERVERID IN (@first)) > 0 begin update SYSSERVERSESSIONS set AOSID = @aosId, VERSION = @version, INSTANCE_NAME = @instanceName, LOGINDATETIME = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), LASTUPDATEDATETIME = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), STATUS = @status, WORKLOAD = @workload where SERVERID IN (@first) and ((select count(SERVERID) from SYSSERVERSESSIONS where STATUS = 1 and LOADBALANCE = 0) < @maxservers) if @@ROWCOUNT = 0 select @serverid = 0 else select @serverid = @first end else begin if (select count(SERVERID) from SYSSERVERSESSIONS WITH (UPDLOCK) where STATUS = 1 and LOADBALANCE = 0) >= @maxservers select @serverid = 0 else begin if (select count(SERVERID) from SYSSERVERSESSIONS) = 0 select @max_val = 1 else select @max_val = max(SERVERID)+1 from SYSSERVERSESSIONS insert into SYSSERVERSESSIONS(SERVERID, AOSID, INSTANCE_NAME, VERSION, LOGINDATETIME, LASTUPDATEDATETIME, STATUS, RECID, LOADBALANCE, WORKLOAD) values(@max_val, @aosId, @instanceName, @version, dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), @status, @recid, @loadbalance, @workload) select @serverid = @max_val end end end

Create user session

USE [YourDatabaseNameGoesHere]

GO

/****** Object: StoredProcedure [dbo].[CREATEUSERSESSIONS]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[CREATEUSERSESSIONS] @clientType int, @sessionType int, @serverid int, @versionid int, @userid varchar(5), @lanExt varchar(10), @manExt varchar(10), @sid varchar(124), @recid bigint, @startId int, @maxusers int, @licenseType int, @masterId int, @maxClientId int, @sessionid int OUTPUT, @loginDateTime datetime OUTPUT as declare @return_val as int declare @first as int declare @max_val as int begin select @sessionid = -1 select @max_val = -1 select @loginDateTime = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()) if(not exists(select * from SYSSERVERSESSIONS WITH (NOLOCK) where SERVERID = @serverid AND Status = 1)) begin select @sessionid = -2 return end select @first = min(SESSIONID) from SYSCLIENTSESSIONS WITH (UPDLOCK,READPAST) where STATUS = 0 and SESSIONID > @maxClientId and SESSIONID <> @masterId if (select count(*) from SYSCLIENTSESSIONS where SESSIONID IN (@first)) > 0 begin if (@licenseType = 0) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType where SESSIONID IN (@first) end else if (@licenseType = 1) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType where SESSIONID IN (@first) and ((select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) < @maxusers) end else if (@licenseType = 2) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType where SESSIONID IN (@first) and ( (select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and (USERID = @userid) and ((STATUS = 1) or (STATUS = 2))) > 0 or (select count(distinct USERID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) < @maxusers ) end if @@ROWCOUNT = 0 select @sessionid = 0 else select @sessionid = @first end else begin if (@licenseType = 1) begin if (select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) >= @maxusers select @sessionid = 0 end else if (@licenseType = 2) begin if ( ((select count(distinct USERID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) >= @maxusers) and ((select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and (USERID = @userid) and ((STATUS = 1) or (STATUS = 2))) = 0) ) select @sessionid = 0 end if (@sessionid = -1) or (@licenseType = 0) begin if (select count(SESSIONID) from SYSCLIENTSESSIONS WITH (UPDLOCK) where STATUS = 0 or STATUS = 1 or STATUS = 2 or STATUS = 3) = 0 select @max_val = @startId else select @max_val = max(SESSIONID)+1 from SYSCLIENTSESSIONS WITH (UPDLOCK) if (@max_val > 65535) select @sessionid = -3 else begin insert into SYSCLIENTSESSIONS(SESSIONID, SERVERID, VERSION, LOGINDATETIME, USERID, SID, USERLANGUAGE, HELPLANGUAGE, CLIENTTYPE, SESSIONTYPE, RECID, CLIENTCOMPUTER, STATUS) values(@max_val, @serverid, @versionid, @loginDateTime, @userid, @sid, @lanExt, @manExt, @clientType, @sessionType, @recid, '', 1) if @@ROWCOUNT = 0 select @sessionid = -1 else select @sessionid = @max_val end end end end

Trick 6: -

Install Reporting extension in Particular instance

Install-AXReportInstanceExtensions –ReportServerInstanceName [SSRSInstanceName] -Credential [DomainName\UserName]

Trick 7: -

Copy and Paste SQL Database Table another Database

select * into [Database Destination Name].dbo.[Table Name] from [Table Name]

Trick 8: -

Deploy Reports in Particular Instance in Microsoft Dynamics AX

• Publish-AXReport -ServicesAOSName [AOSSERVER] -ServicesAOSWSDLPORT [8101] -ReportName *

• Publish-AXReport –Id [SSRSConfigID] –ReportName *

• Publish-AXReport –ReportName * -id [AXSSRS] –servicesAOSname [AOSServer] –servicesAOSWSDLPort [8102]

Trick 9: -

If Your database in Restoring Mode. Now apply this Query.

• Restore Database [Database Name] With Recovery