How to View Logical File Names in MS SQL DB

Log to the MS SQL Server with Sa login and run the below query after selecting the Data base.


SELECT FILE_NAME(1) AS 'File Name 1', FILE_NAME(2) AS 'File Name 2';

Formula for Crystal Report Field Alignment

My requirement is to change the alignment of the filed dynamically.

So that I right click the field and select the the Format Field option. Then I select the Horizontal Alignment option

I put the below formula for fulfill my requirement.

IF {VW_RPT_PAYSLIP.ITEM} = '    Interest' THEN
 crJustified
ELSE IF {VW_RPT_PAYSLIP.ITEM} = '    Balance' THEN
 crJustified

Percentage Query in SQL

SELECT (C.A/C.B)*100,C.EMP_GENDER FROM
(SELECT GEN.*,AALL.* FROM
(select CAST(COUNT(EMP_GENDER) AS DECIMAL) AS A,EMP_GENDER from hs_hr_employee
GROUP BY EMP_GENDER) GEN ,
(SELECT CAST(COUNT(EMP_GENDER) AS DECIMAL) AS B FROM HS_HR_EMPLOYEE) AALL) C

SQL User Mapping After the DB Restore

SQL Syntax :
sp_change_users_login 'Auto_Fix', '<Login Name>' , NULL, '<Password>'

Example :
sp_change_users_login 'Auto_Fix', 'HRMV6' , NULL, 'HRMV6'

Bank Diskette Generate from Crystal Reports

Configure the Formula with below or similar syntax according to the requirement.

{HS_HR_EMPLOYEE.EMP_CALLING_NAME}+' '+
{HS_HR_EMPLOYEE.EMP_SURNAME}+','+
right('0000000000000'+ltrim(rtrim({HS_PR_BANK_TRANSFERS.EBANK_ACC_NO})),13)+','+{HS_HR_BRANCH.BBRANCH_AUTO_CLR_HOUSE_CODE}+','+
totext({HS_PR_BANK_TRANSFERS.EBT_AMOUNT},"0.00")+','+{HS_HR_EMPLOYEE.EMP_PAYROLLNO}

Approal Function (SQL)

CREATE FUNCTION HS_FN_LEV_APPROVING_PERSON_PARAPET (@EMP_NUMBER VARCHAR(6))
RETURNS VARCHAR(6)
AS BEGIN

DECLARE @EMPLOYEE AS VARCHAR(6)
DECLARE @CORPORATE AS VARCHAR(6)
DECLARE @SUP_EMPLOYEE AS VARCHAR(6)
DECLARE @HR AS VARCHAR(6)
DECLARE @HOD AS VARCHAR(6)
DECLARE @CEO AS VARCHAR(6)
DECLARE @APPROVER AS VARCHAR(6)

SET @EMPLOYEE = @EMP_NUMBER
SET @SUP_EMPLOYEE =  ( SELECT EREP_SUP_EMP_NUMBER FROM HS_HR_EMP_REPORTTO WHERE EREP_REPORTING_MODE=1
AND EREP_SUB_EMP_NUMBER= @EMPLOYEE)
SET @CORPORATE = (SELECT CT_CODE FROM HS_HR_EMPLOYEE WHERE EMP_NUMBER = @EMPLOYEE )
SET @HR = ( SELECT WF_MAIN_APP_EMPLOYEE FROM HS_HR_WF_GROUP_APP_PERSON WHERE WFAPPGRP_CODE = '000001' )
SET @CEO = (SELECT WF_MAIN_APP_EMPLOYEE FROM HS_HR_WF_GROUP_APP_PERSON WHERE WFAPPGRP_CODE = '000001')
SET @HOD = (SELECT HIE_HEAD_EMP_NUMBER FROM HS_HR_COMPANY_HIERARCHY WHERE DEF_LEVEL = 4
AND HIE_CODE IN (SELECT HIE_CODE_4 FROM HS_HR_EMPLOYEE WHERE EMP_NUMBER = @EMPLOYEE ))

IF @CORPORATE IN ('000004','000008','000009','000010','000012','000014','000016')
SET @APPROVER = @SUP_EMPLOYEE
ELSE IF @EMPLOYEE = @HOD
SET @APPROVER = @CEO
ELSE
SET @APPROVER = @HOD

RETURN (@APPROVER)
END

GO

DB Restore Error | Microsoft SQL Server, Error 3205

I'm tried to restore the DB backup for MS SQL Server 2008. Unfortunately it appeared below error message.

I checked this again and found the reason of this. Reason was my SQL DB Backup is taken from the latest version of the MS SQL Server and I'm trying to restore it to the previous MS SQL Server version. As a solution of that I run the latest service pack for my MS SQL Server which used to restore my Data Base. You can also found this service packs by using the below link.

http://support.microsoft.com/kb/968382