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