getting backup details

--query 1
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

--query 2

-- for single db back up history

DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()

-- Get Backup History for required database

SELECT TOP ( 30 )
s.database_name,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
'BackupType'=CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEn 'L' THEN 'Transaction Log'
END ,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc,
backup_finish_date

How to compare two tables in databases

/*
use DMNDB
SELECT
OBJ.NAME as TableName into #dmndbTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'
go
use DMNqa
SELECT
OBJ.NAME as TableName into #dmnqaTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'

*/
go

select TableName AS dmndbTables FROM #dmndbTables WHERE TableName NOT IN (SELECT TableName FROM #dmnqaTables)

select TableName AS dmnQATables FROM #dmnQATables WHERE TableName NOT IN (SELECT TableName FROM #dmnDBTables)

---------------------
--we can get table columns
CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE

FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP

WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)

how to get rows into single field

declare @AccountCategory varchar(max)

set @AccountCategory=''
SELECT @AccountCategory=@AccountCategory+', '+ Prefix FROM(select Prefix
from a_AccPrefixIncludes) AS Prefix

print @AccountCategory

Crystalreport compatability issue fixing

when you create reports with using crystalreport(old version of 8.5), there may be connection issue will come to connect sql2005. you have to run this script to solve this kind of problem.

EXEC sp_dbcmptlevel yourdbname, 80;

how to pass parameter values for crystal report 8.5 in vb6

Public crxApplication As New CRAXDRT.Application
Public CrxReport As New CRAXDRT.Report
Dim CrtParameters As CRAXDRT.ParameterFieldDefinitions
Dim CrtParameter As CRAXDRT.ParameterFieldDefinition


Private Sub PrintReport()
Dim ReportName As String
Dim i As Integer
Dim cParmValue As String
cParmValue = "Me kiep"
ReportName = App.Path & "\RPT\BillControlZ.rpt"
Set CrxReport = crxApplication.OpenReport(ReportName, crOpenReportByTempCopy)
Dim CRTABLE As CRAXDRT.DatabaseTable
i = 1
For i = 1 To CrxReport.Database.Tables.Count
Set CRTABLE = CrxReport.Database.Tables.Item(1)
CRTABLE.SetLogOnInfo gODBCName, gDBDatabaseName, gDBUserName, gDBPassword
Next i


CrxReport.EnableParameterPrompting = False
Set CrtParameters = CrxReport.ParameterFields
For Each CrtParameter In CrtParameters
If CrtParameter.Name = "?strTemp" Then
CrtParameter.AddCurrentValue cParmValue
End If
Next CrtParameter


CRViewer1.Visible = True
CrxReport.DisplayProgressDialog = True
CrxReport.VerifyOnEveryPrint = True
''CrxReport.UseIndexForSpeed = True
''CrxReport.SQLQueryString = strSQL & " order by SalePoint "
CRViewer1.ReportSource = CrxReport
CrxReport.VerifyOnEveryPrint = True
CrxReport.DiscardSavedData
CRViewer1.DisplayGroupTree = False
CRViewer1.ViewReport
CRViewer1.ShowFirstPage
CRViewer1.Zoom 100
Screen.MousePointer = vbDefault
cmdView.Visible = True
cmdPrinter.Visible = True
End Sub

original link
http://www.developerfusion.com/forum/thread/28348/

get tree view child notes with sql

--create a FranchiseeMaster table
CREATE TABLE FranchiseeMaster (LCOID NVARCHAR(10) PRIMARY KEY ,LCOCode NVARCHAR(15),LCOName NVARCHAR(50),ParentLCOID NVARCHAR(10),LCOLevel INT)

--create a text file under c:\Test.txt
--past the data and save the test.txt file
/*

1000000013 00013 JAIDEV ENTERPRISESS 1000000022 3
1000000014 00014 WIN COM NET 1000000022 3
1000000015 00015 JAI SWAMI SAMARTH ENTERPRISESS 1000000022 3
1000000016 00016 DIGICOM NET (INDIA) PRIVATE LIMITED 1000000022 3
1000000031 00031 SHAILU'S CHANNEL BUSTERS 1000000015 4
1000000032 00032 CRYSTAL COM CLUB 1000000015 4
1000000034 00034 VIDEO VISION 1000000015 4
1000000035 00035 DIGITAL VISION NET 1000000015 4
1000000036 00036 PHUN TIME COM VISION 1000000015 4
1000000037 00037 SAMARTH COM VISION 1000000015 4
1000000038 00038 VERSOVA COM NET 1000000015 4
1000000039 00039 GUPTA COM NET 1000000015 4
1000000040 00040 COMMAN 1000000015 4
1000000041 00041 ROYAL COM NET 1000000015 4
1000000042 00042 SHREE SAI COM 1000000015 4
1000000043 00043 RIYANKA COM NET 1000000015 4
1000000044 00044 MASTER NET 1000000013 4
1000000045 00045 ALFA COM 1000000013 4
1000000046 00046 ALFA COM NET 1000000013 4
1000000047 00047 ANIL VIDEO CLUB 1000000015 4
1000000048 00048 DEEPAK COM VISION 1000000015 4
1000000049 00049 VISTA COM NETWO 1000000022 3
1000000050 00050 SAI HOME VIDEO 1000000049 4
1000000052 00052 SAGAR VISION COM NET 1000000022 3
1000000053 00053 MOON COM NET 1000000022 3
1000000061 00061 MOON VISION 1000000015 4
1000000062 00062 AUTO VISION NET 1000000013 4
1000000063 00063 OM COM SERVICE 1000000013 4
1000000064 00064 SHREE KALIKA ELECTRONIC 1000000013 4
1000000067 00067 SPACE VISION NET 1000000015 4

*/
--inserting the records by using bulk insert method
BULK INSERT FranchiseeMaster FROM 'C:\TEST.txt' WITH (ROWTERMINATOR = '\n')

--crating a function to retrive a selected note all childs

CREATE FUNCTION [dbo].[udf_getMyFranchiseeTree](@Franchisee NVARCHAR(30))
RETURNS @Tree table(RowNumber int IDENTITY (1, 1),LCOID NVARCHAR(10) ,LCOCode NVARCHAR(15),LCOName NVARCHAR(50),LCOLevel INT )
AS
BEGIN
WITH getMyJV(LCOID ,LCOCode ,LCOName,LCOLevel )
AS (
SELECT LCOID ,LCOCode ,LCOName ,LCOLevel
FROM FranchiseeMaster
WHERE ParentLCOID=@Franchisee
UNION ALL
SELECT Node.LCOID ,Node.LCOCode ,Node.LCOName ,Node.LCOLevel
FROM FranchiseeMaster Node
JOIN getMyJV ft
ON Node.ParentLCOID=ft.LCOID
)
INSERT @Tree (LCOID ,LCOCode ,LCOName ,LCOLevel )
SELECT LCOID ,LCOCode ,LCOName ,LCOLevel
FROM getMyJV
RETURN
END

--after creating run this sql.
SELECT * FROM [udf_getMyFranchiseeTree]('1000000022')

reporting service 2008 publish problem

Problem

"The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded."

Solution

SSRS 2008 DEPLOYMENT

Use Given Below Version of SQL SERVER 2008 -- Evaluation (for testing) or Licensed (for production server).

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34 Copyright (c) 1988-2008
Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

STEP 1 > Create Virtual Directory on IIS with name reportserver_SQL2008

Set Local Path of Report Server 2008 --D:\SQL2008\MSRS10.SQL2008\Reporting Services\ReportServer

Under Directory Security>>Edit Authentication and access control>>Enable anonyms access and Provide Administrator Account with the Administrator password


STEP 2 > Configure Report Server 2008

>Put Your Service Account as Network Service with Administrator rights.
>Take Backup of your Encryption keys and try to restore it on report server 2008.

STEP 3 > Configuration Properties of SSRS 2008 Projects.

Go to Project properties
>Make Overwrite data source as True.
>Put Target URL>> http://localhost/reportserver_SQL2008
>Under Configuration Manger check Build and Deploy Box.

Now Deploy your SSRS 2008 Projects its Done.

Thanks.

Kali Charan Tripathi. (INDIA)

Tripathi_soft@yahoo.co.in
kalicharan.tripathi@in.schneider-electric.com

https://connect.microsoft.com/VisualStudio/feedback/details/361103/reporting-services-report-cant-deploy-to-sql-2005