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

usefull sql for finding available database infor:sql

SELECT
DB_NAME(mf.database_id) AS databaseName,
name AS File_LogicalName,
CASE
WHEN type_desc = 'LOG' THEN 'Log File'
WHEN type_desc = 'ROWS' THEN 'Data File'
ELSE type_desc
END AS File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 AS size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.FILE_ID = divfs.FILE_ID
ORDER BY num_of_Reads DESC

how to Drop Tables:Sql

IF OBJECT_ID('normalTable') IS NOT NULL
BEGIN
--DROP Normal table
DROP TABLE normalTable
END

-- just to make sure it doesn't already exist
IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
BEGIN
--DROP Temporary table
DROP TABLE #tmpTable
END

--Or try this sql

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N’tempdb..#tmpTable’)
)
BEGIN
DROP TABLE #tmpTable
END