How to get the SQL from a Report Builder ?

Hi,

I am trying to extract the SQL out of a Report Builder Report. As recommended in a msdn article, I have enabled the trace flag as below

"add name="Components" value="all,RunningJobs:3,SemanticQueryEngine:4,SemanticModelGenerator:2" .

However i still do not see the SQL in the log files.

Can you please help me on this ?

Hi there,

You could try to use SQL Profiler and run a trace against the server.

cheers,

Andrew

|||

Hi Andrew,

Thanks for your reply. Just wanted to know, Is there any way other than profiler. Any idea ?

Thanks again,

Sam

How to get the log file with /logger option

Hi,

I want to get the log for the below command.

DTExec /FILE "C:\Test.dtsx" /CONNECTION DestinationConnectionFlatFile;"c:\test.csv" /CONNECTION SourceConnectionOLEDB;"Data Source=test;User ID=user;Initial Catalog=DATA_CONV;Provider=SQLNCLI;Auto Translate=false;PASSWORD=password" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /logger "DTS.LogProviderTextFile;log.txt" /Set "\package.Connections[log.txt].Properties[ConnectionString];c:\log.txt"

But I got the error as below. Please advise.

Error: 2006-10-10 09:57:21.07
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by C
onnections collection when the specific connection element is not found.
End Error
Error: 2006-10-10 09:57:21.10
Code: 0xC001000E
Source: Test
Description: The connection "log.txt" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2006-10-10 09:57:21.14
Code: 0x8001F02F
Source: Test
Description: Cannot resolve a package path to an object in the package ".Connections[log.txt].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2006-10-10 09:57:21.17
Code: 0x80012017
Source: Test
Description: The package path referenced an object that cannot be found: "\package.Connections[log.txt].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set \package.Connections[log.txt].Properties[ConnectionString]
value to c:\log.txt.

Are you using dtexecui.exe to build the command line? If not, please do that, as it will build properly formed dtexec parameter set.

If you want to add a logger from the command line, it must reference a pre-existent connection manager in the package. So add a file connection manager to the package.

Now if you want a logger without a pre-existent connection manager, and you don't mind coding, create your own LogProvider by creating a subclass of LogProviderBase and overriding of meaning of the ConfigString, when generally points to a connection manager in stock loggers.

Two custom log providers with source code are available in the SQL 2005 samples available at http://www.microsoft.com/downloads/details.aspx?FamilyID=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

How to get the count of a value( for ex "PASS") in each row and showits count as a col

I have my sP output as given below:

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2

1 abc 1/1/2007 Pass PassYes No

2 abc 1/1/2007 Pass Fail Yes No

3 abc 1/1/2007 Pass PassYes No

4 abc 1/1/2007 Pass Fail Yes No

5 abc 1/1/2007 Pass Fail Yes No

What i need is this way

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2 Passcount

1 abc 1/1/2007 Pass Pass Yes No 2

2 abc 1/1/2007 Pass Fail Yes No 1

3 abc 1/1/2007 Pass Pass Yes No 2

4 abc 1/1/2007 Pass Fail Yes No 1

5 abc 1/1/2007 Pass Fail Pass No 2

Similarly i need FailCount, Yes Count, Nocount as few more columns.

The query for the first table output is this way..

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[VerificationSummaryReport_TEST] '1/1/2007','3/28/2007','ONBOARD'

-- Add the parameters for the stored procedure here

@.FromDate datetime,@.ToDate datetime,@.VerificationType varchar(15)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

select AD.audit_id, AD.Audit_Name, SM.Shortcode 'shortcode_name', CM.Campaign_Name , CM.Shortcode_Owner 'brand_name', SM.Operator_Name,

E.first_name+' '+E.last_name 'employee_name',AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate,

--case when DE.Status_criteria = 'PASS' then count(*) else '0' end as 'PassCount',

max(case when DE.criteria_id = 1 then DE.Status_criteria else '-' end) as '6.2',

max(case when DE.criteria_id = 2 then DE.Status_criteria else '-' end) as '6.2.1',

max(case when DE.criteria_id = 3 then DE.Status_criteria else '-' end) as '6.2.2',

max(case when DE.criteria_id = 4 then DE.Status_criteria else '-' end) as '6.3',

max(case when DE.criteria_id = 5 then DE.Status_criteria else '-' end) as '6.3.1',

max(case when DE.criteria_id = 6 then DE.Status_criteria else '-' end) as '6.4',

max(case when DE.criteria_id = 7 then DE.Status_criteria else '-' end) as '6.4.1',

max(case when DE.criteria_id = 8 then DE.Status_criteria else '-' end) as '6.4.3',

max(case when DE.criteria_id = 9 then DE.Status_criteria else '-' end) as '6.4.2',

max(case when DE.criteria_id = 10 then DE.Status_criteria else '-' end) as '3.1',

max(case when DE.criteria_id = 11 then DE.Status_criteria else '-' end) as '3.1.1',

max(case when DE.criteria_id = 12 then DE.Status_criteria else '-' end) as '3.1.2',

max(case when DE.criteria_id = 14 then DE.Status_criteria else '-' end) as '3.2',

max(case when DE.criteria_id = 15 then DE.Status_criteria else '-' end) as '3.2.1',

max(case when DE.criteria_id = 16 then DE.Status_criteria else '-' end) as '3.2.2',

max(case when DE.criteria_id = 19 then DE.Status_criteria else '-' end) as '4.6',

max(case when DE.criteria_id = 20 then DE.Status_criteria else '-' end) as '4.6.2',

max(case when DE.criteria_id = 21 then DE.Status_criteria else '-' end) as '4.6.3',

max(case when DE.criteria_id = 22 then DE.Status_criteria else '-' end) as '4.6.4',

max(case when DE.criteria_id = 23 then DE.Status_criteria else '-' end) as '4.7',

max(case when DE.criteria_id = 24 then DE.Status_criteria else '-' end) as '4.7.2',

max(case when DE.criteria_id = 25 then DE.Status_criteria else '-' end) as '4.8',

max(case when DE.criteria_id = 26 then DE.Status_criteria else '-' end) as '4.8.1',

max(case when DE.criteria_id = 27 then DE.Status_criteria else '-' end) as '4.9',

max(case when DE.criteria_id = 28 then DE.Status_criteria else '-' end) as '4.9.2',

max(case when DE.criteria_id = 29 then DE.Status_criteria else '-' end) as '7.1',

max(case when DE.criteria_id = 30 then DE.Status_criteria else '-' end) as '7.1.1',

max(case when DE.criteria_id = 31 then DE.Status_criteria else '-' end) as '7.2',

max(case when DE.criteria_id = 32 then DE.Status_criteria else '-' end) as '7.2.1',

max(case when DE.criteria_id = 36 then DE.Status_criteria else '-' end) as '5.10',

max(case when DE.criteria_id = 37 then DE.Status_criteria else '-' end) as '5.10.1',

max(case when DE.criteria_id = 38 then DE.Status_criteria else '-' end) as '5.5',

max(case when DE.criteria_id = 39 then DE.Status_criteria else '-' end) as '5.5.1',

max(case when DE.criteria_id = 41 then DE.Status_criteria else '-' end) as '5.5.2',

max(case when DE.criteria_id = 42 then DE.Status_criteria else '-' end) as '5.6',

max(case when DE.criteria_id = 43 then DE.Status_criteria else '-' end) as '5.6.1',

max(case when DE.criteria_id = 44 then DE.Status_criteria else '-' end) as '5.6.2',

max(case when DE.criteria_id = 45 then DE.Status_criteria else '-' end) as '5.7',

max(case when DE.criteria_id = 46 then DE.Status_criteria else '-' end) as '5.7.1',

max(case when DE.criteria_id = 47 then DE.Status_criteria else '-' end) as '5.9',

max(case when DE.criteria_id = 48 then DE.Status_criteria else '-' end) as '5.9.1',

max(case when DE.criteria_id = 49 then DE.Status_criteria else '-' end) as '5.9.2',

max(case when DE.criteria_id = 51 then DE.Status_criteria else '-' end) as '1.3',

max(case when DE.criteria_id = 60 then DE.Status_criteria else '-' end) as '8.2',

max(case when DE.criteria_id = 66 then DE.Status_criteria else '-' end) as '9.3',

max(case when DE.criteria_id = 67 then DE.Status_criteria else '-' end) as '9.3.1',

max(case when DE.criteria_id = 68 then DE.Status_criteria else '-' end) as '9.3.2',

max(case when DE.criteria_id = 69 then DE.Status_criteria else '-' end) as '10.1',

max(case when DE.criteria_id = 70 then DE.Status_criteria else '-' end) as '10.1.1',

max(case when DE.criteria_id = 71 then DE.Status_criteria else '-' end) as '10.1.2',

max(case when DE.criteria_id = 72 then DE.Status_criteria else '-' end) as '10.5',

max(case when DE.criteria_id = 73 then DE.Status_criteria else '-' end) as '10.5.1',

max(case when DE.criteria_id = 74 then DE.Status_criteria else '-' end) as '10.5.2',

max(case when DE.criteria_id = 75 then DE.Status_criteria else '-' end) as '10.6',

max(case when DE.criteria_id = 76 then DE.Status_criteria else '-' end) as '10.6.1',

max(case when DE.criteria_id = 77 then DE.Status_criteria else '-' end) as '10.7',

max(case when DE.criteria_id = 78 then DE.Status_criteria else '-' end) as '10.7.1',

max(case when DE.criteria_id = 79 then DE.Status_criteria else '-' end) as '6.4.4'

--, case when DE.Status_criteria = 'PASS' then Count(*) else 0 end as 'PASSCOUNT'

from dbo.Audit_Details AD INNER JOIN

dbo.Data_Evaluation DE ON DE.Audit_Id = AD.Audit_ID INNER JOIN

dbo.ShortCode_Master SM ON SM.ShortCode_Id = AD.Shortcode_Id INNER JOIN

dbo.Campaign_Master CM ON CM.Campaign_Id = AD.Campaign_Id INNER JOIN

dbo.Employee E ON E.Emp_Id = AD.DE_empid

where AD.present_auditstate = 'AS' and AD.verificationtype = @.VerificationType AND AD.STATUS = 'ACTIVE'

AND AD.audit_createdDate between @.FromDate and --'3/29/2007'

REPLACE(CONVERT(CHAR(10),DATEADD(day, 1,@.ToDate),110),'-','/')

group by AD.audit_id, AD.shortcode_id, AD.campaign_id, AD.Audit_Name, SM.Shortcode, CM.Campaign_Name , CM.Shortcode_Owner, SM.Operator_Name,

E.first_name,E.last_name ,AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate--, DE.Status_criteria

--order by AD.audit_id

END

Please Help me out in solving this problem. Thanks in advance

Moving to T-SQL forum.

Mike

|||sum(case when DE.Status_criteria = 'PASS' then 1 else 0 end) as 'PASSCOUNT'

How to get the count of a value( for ex "PASS") in each row and showits count as a col

I have my sP output as given below:

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2

1 abc 1/1/2007 Pass PassYes No

2 abc 1/1/2007 Pass Fail Yes No

3 abc 1/1/2007 Pass PassYes No

4 abc 1/1/2007 Pass Fail Yes No

5 abc 1/1/2007 Pass Fail Yes No

What i need is this way

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2 Passcount

1 abc 1/1/2007 Pass Pass Yes No 2

2 abc 1/1/2007 Pass Fail Yes No 1

3 abc 1/1/2007 Pass Pass Yes No 2

4 abc 1/1/2007 Pass Fail Yes No 1

5 abc 1/1/2007 Pass Fail Pass No 2

Similarly i need FailCount, Yes Count, Nocount as few more columns.

The query for the first table output is this way..

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[VerificationSummaryReport_TEST] '1/1/2007','3/28/2007','ONBOARD'

-- Add the parameters for the stored procedure here

@.FromDate datetime,@.ToDate datetime,@.VerificationType varchar(15)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

select AD.audit_id, AD.Audit_Name, SM.Shortcode 'shortcode_name', CM.Campaign_Name , CM.Shortcode_Owner 'brand_name', SM.Operator_Name,

E.first_name+' '+E.last_name 'employee_name',AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate,

--case when DE.Status_criteria = 'PASS' then count(*) else '0' end as 'PassCount',

max(case when DE.criteria_id = 1 then DE.Status_criteria else '-' end) as '6.2',

max(case when DE.criteria_id = 2 then DE.Status_criteria else '-' end) as '6.2.1',

max(case when DE.criteria_id = 3 then DE.Status_criteria else '-' end) as '6.2.2',

max(case when DE.criteria_id = 4 then DE.Status_criteria else '-' end) as '6.3',

max(case when DE.criteria_id = 5 then DE.Status_criteria else '-' end) as '6.3.1',

max(case when DE.criteria_id = 6 then DE.Status_criteria else '-' end) as '6.4',

max(case when DE.criteria_id = 7 then DE.Status_criteria else '-' end) as '6.4.1',

max(case when DE.criteria_id = 8 then DE.Status_criteria else '-' end) as '6.4.3',

max(case when DE.criteria_id = 9 then DE.Status_criteria else '-' end) as '6.4.2',

max(case when DE.criteria_id = 10 then DE.Status_criteria else '-' end) as '3.1',

max(case when DE.criteria_id = 11 then DE.Status_criteria else '-' end) as '3.1.1',

max(case when DE.criteria_id = 12 then DE.Status_criteria else '-' end) as '3.1.2',

max(case when DE.criteria_id = 14 then DE.Status_criteria else '-' end) as '3.2',

max(case when DE.criteria_id = 15 then DE.Status_criteria else '-' end) as '3.2.1',

max(case when DE.criteria_id = 16 then DE.Status_criteria else '-' end) as '3.2.2',

max(case when DE.criteria_id = 19 then DE.Status_criteria else '-' end) as '4.6',

max(case when DE.criteria_id = 20 then DE.Status_criteria else '-' end) as '4.6.2',

max(case when DE.criteria_id = 21 then DE.Status_criteria else '-' end) as '4.6.3',

max(case when DE.criteria_id = 22 then DE.Status_criteria else '-' end) as '4.6.4',

max(case when DE.criteria_id = 23 then DE.Status_criteria else '-' end) as '4.7',

max(case when DE.criteria_id = 24 then DE.Status_criteria else '-' end) as '4.7.2',

max(case when DE.criteria_id = 25 then DE.Status_criteria else '-' end) as '4.8',

max(case when DE.criteria_id = 26 then DE.Status_criteria else '-' end) as '4.8.1',

max(case when DE.criteria_id = 27 then DE.Status_criteria else '-' end) as '4.9',

max(case when DE.criteria_id = 28 then DE.Status_criteria else '-' end) as '4.9.2',

max(case when DE.criteria_id = 29 then DE.Status_criteria else '-' end) as '7.1',

max(case when DE.criteria_id = 30 then DE.Status_criteria else '-' end) as '7.1.1',

max(case when DE.criteria_id = 31 then DE.Status_criteria else '-' end) as '7.2',

max(case when DE.criteria_id = 32 then DE.Status_criteria else '-' end) as '7.2.1',

max(case when DE.criteria_id = 36 then DE.Status_criteria else '-' end) as '5.10',

max(case when DE.criteria_id = 37 then DE.Status_criteria else '-' end) as '5.10.1',

max(case when DE.criteria_id = 38 then DE.Status_criteria else '-' end) as '5.5',

max(case when DE.criteria_id = 39 then DE.Status_criteria else '-' end) as '5.5.1',

max(case when DE.criteria_id = 41 then DE.Status_criteria else '-' end) as '5.5.2',

max(case when DE.criteria_id = 42 then DE.Status_criteria else '-' end) as '5.6',

max(case when DE.criteria_id = 43 then DE.Status_criteria else '-' end) as '5.6.1',

max(case when DE.criteria_id = 44 then DE.Status_criteria else '-' end) as '5.6.2',

max(case when DE.criteria_id = 45 then DE.Status_criteria else '-' end) as '5.7',

max(case when DE.criteria_id = 46 then DE.Status_criteria else '-' end) as '5.7.1',

max(case when DE.criteria_id = 47 then DE.Status_criteria else '-' end) as '5.9',

max(case when DE.criteria_id = 48 then DE.Status_criteria else '-' end) as '5.9.1',

max(case when DE.criteria_id = 49 then DE.Status_criteria else '-' end) as '5.9.2',

max(case when DE.criteria_id = 51 then DE.Status_criteria else '-' end) as '1.3',

max(case when DE.criteria_id = 60 then DE.Status_criteria else '-' end) as '8.2',

max(case when DE.criteria_id = 66 then DE.Status_criteria else '-' end) as '9.3',

max(case when DE.criteria_id = 67 then DE.Status_criteria else '-' end) as '9.3.1',

max(case when DE.criteria_id = 68 then DE.Status_criteria else '-' end) as '9.3.2',

max(case when DE.criteria_id = 69 then DE.Status_criteria else '-' end) as '10.1',

max(case when DE.criteria_id = 70 then DE.Status_criteria else '-' end) as '10.1.1',

max(case when DE.criteria_id = 71 then DE.Status_criteria else '-' end) as '10.1.2',

max(case when DE.criteria_id = 72 then DE.Status_criteria else '-' end) as '10.5',

max(case when DE.criteria_id = 73 then DE.Status_criteria else '-' end) as '10.5.1',

max(case when DE.criteria_id = 74 then DE.Status_criteria else '-' end) as '10.5.2',

max(case when DE.criteria_id = 75 then DE.Status_criteria else '-' end) as '10.6',

max(case when DE.criteria_id = 76 then DE.Status_criteria else '-' end) as '10.6.1',

max(case when DE.criteria_id = 77 then DE.Status_criteria else '-' end) as '10.7',

max(case when DE.criteria_id = 78 then DE.Status_criteria else '-' end) as '10.7.1',

max(case when DE.criteria_id = 79 then DE.Status_criteria else '-' end) as '6.4.4'

--, case when DE.Status_criteria = 'PASS' then Count(*) else 0 end as 'PASSCOUNT'

from dbo.Audit_Details AD INNER JOIN

dbo.Data_Evaluation DE ON DE.Audit_Id = AD.Audit_ID INNER JOIN

dbo.ShortCode_Master SM ON SM.ShortCode_Id = AD.Shortcode_Id INNER JOIN

dbo.Campaign_Master CM ON CM.Campaign_Id = AD.Campaign_Id INNER JOIN

dbo.Employee E ON E.Emp_Id = AD.DE_empid

where AD.present_auditstate = 'AS' and AD.verificationtype = @.VerificationType AND AD.STATUS = 'ACTIVE'

AND AD.audit_createdDate between @.FromDate and --'3/29/2007'

REPLACE(CONVERT(CHAR(10),DATEADD(day, 1,@.ToDate),110),'-','/')

group by AD.audit_id, AD.shortcode_id, AD.campaign_id, AD.Audit_Name, SM.Shortcode, CM.Campaign_Name , CM.Shortcode_Owner, SM.Operator_Name,

E.first_name,E.last_name ,AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate--, DE.Status_criteria

--order by AD.audit_id

END

Please Help me out in solving this problem. Thanks in advance

Moving to T-SQL forum.

Mike

|||sum(case when DE.Status_criteria = 'PASS' then 1 else 0 end) as 'PASSCOUNT'

How to Get the 2nd the 2nd Record AND DISPLAY IN SINGLE ROW ?

Can you please assist me on how to get the 2nd record in case there are

3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get

the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.

How to GET the 2nd Row of Record in Case that There are 3 or more
records IN A SINGLE ROW ?

-----------------------*--

This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.

Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...

SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,

(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate DESC) AS NEWBASIC,

******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ?

(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate ASC) AS OLDBASIC

FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @.FromDate AND
@.ToDate
ORDER BY E.LastNameheri (heri.carandang@.acspacific.com) writes:
> Can you please assist me on how to get the 2nd record in case there are
> 3 or more records of an employee, the query below gets the MAX and MIN
> BasicSalary. However, my MIN Basic Salary is wrong because I should get
> the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
> more records and not the last Basic Salary of the Last Record.
>
> How to GET the 2nd Row of Record in Case that There are 3 or more
> records IN A SINGLE ROW ?

SELECT TOP 1 val
FROM (SELECT TOP 2 val
FROM tbl
ORDER BY val DESC) AS x
ORDER BY val ASC

Gives you the second highest value of val.

If you want to do this for a set values, this may be more practical:

SELECT s.empid, secondest = MAX(s.salary)
FROM salaries s
JOIN (SELECT empid, maxsalary = MAX(salary)
FROM salaries
GROUP BY empid) AS m ON s.empid = m.empid
WHERE s.salary < m.maxsalary

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||or

Select min(col) from
(
select top N col from table Order by col DESC
) T

Madhivanan

How to get SQL Server Service Pack information

Hi,
The below result i get from running xp_msver, but is this information
showing any SQL service pack information?
How can i get it?
1,ProductName,NULL,Microsoft SQL Server
2,ProductVersion,524288,8.00.760
3,Language,1033,English (United States)
4,Platform,NULL,NT INTEL X86
5,Comments,NULL,NT INTEL X86
6,CompanyName,NULL,Microsoft Corporation
7,FileDescription,NULL,SQL Server Windows NT
8,FileVersion,NULL,2000.080.0760.00
9,InternalName,NULL,SQLSERVR
10,LegalCopyright,NULL,© 1988-2003 Microsoft Corp. All rights reserved.
11,LegalTrademarks,NULL,Microsoft® is a registered trademark of Microsoft
Corporation. Windows(TM) is a trademark of Microsoft Corporation
12,OriginalFilename,NULL,SQLSERVR.EXE
13,PrivateBuild,NULL,NULL
14,SpecialBuild,49807360,NULL
15,WindowsVersion,143851525,5.0 (2195)
16,ProcessorCount,1,1
17,ProcessorActiveMask,1,00000001
18,ProcessorType,586,PROCESSOR_INTEL_PENTIUM
19,PhysicalMemory,511,511 (536264704)
20,Product ID,NULL,NULL
--
Kzeto NgHi,
From the output the service pack level for your SQL Server is SP3 (
2,ProductVersion,524288,8.00.760 )
Thanks
Hari
MCDBA
"Kzeto ng" <kzetong@.yahoo.com> wrote in message
news:ev9prZIuDHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> The below result i get from running xp_msver, but is this information
> showing any SQL service pack information?
> How can i get it?
>
> 1,ProductName,NULL,Microsoft SQL Server
> 2,ProductVersion,524288,8.00.760
> 3,Language,1033,English (United States)
> 4,Platform,NULL,NT INTEL X86
> 5,Comments,NULL,NT INTEL X86
> 6,CompanyName,NULL,Microsoft Corporation
> 7,FileDescription,NULL,SQL Server Windows NT
> 8,FileVersion,NULL,2000.080.0760.00
> 9,InternalName,NULL,SQLSERVR
> 10,LegalCopyright,NULL,© 1988-2003 Microsoft Corp. All rights reserved.
> 11,LegalTrademarks,NULL,Microsoft® is a registered trademark of Microsoft
> Corporation. Windows(TM) is a trademark of Microsoft Corporation
> 12,OriginalFilename,NULL,SQLSERVR.EXE
> 13,PrivateBuild,NULL,NULL
> 14,SpecialBuild,49807360,NULL
> 15,WindowsVersion,143851525,5.0 (2195)
> 16,ProcessorCount,1,1
> 17,ProcessorActiveMask,1,00000001
> 18,ProcessorType,586,PROCESSOR_INTEL_PENTIUM
> 19,PhysicalMemory,511,511 (536264704)
> 20,Product ID,NULL,NULL
>
>
> --
> Kzeto Ng
>|||http://www.aspfaq.com/2160
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Kzeto ng" <kzetong@.yahoo.com> wrote in message
news:ev9prZIuDHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> The below result i get from running xp_msver, but is this information
> showing any SQL service pack information?
> How can i get it?
>
> 1,ProductName,NULL,Microsoft SQL Server
> 2,ProductVersion,524288,8.00.760
> 3,Language,1033,English (United States)
> 4,Platform,NULL,NT INTEL X86
> 5,Comments,NULL,NT INTEL X86
> 6,CompanyName,NULL,Microsoft Corporation
> 7,FileDescription,NULL,SQL Server Windows NT
> 8,FileVersion,NULL,2000.080.0760.00
> 9,InternalName,NULL,SQLSERVR
> 10,LegalCopyright,NULL,© 1988-2003 Microsoft Corp. All rights reserved.
> 11,LegalTrademarks,NULL,Microsoft® is a registered trademark of Microsoft
> Corporation. Windows(TM) is a trademark of Microsoft Corporation
> 12,OriginalFilename,NULL,SQLSERVR.EXE
> 13,PrivateBuild,NULL,NULL
> 14,SpecialBuild,49807360,NULL
> 15,WindowsVersion,143851525,5.0 (2195)
> 16,ProcessorCount,1,1
> 17,ProcessorActiveMask,1,00000001
> 18,ProcessorType,586,PROCESSOR_INTEL_PENTIUM
> 19,PhysicalMemory,511,511 (536264704)
> 20,Product ID,NULL,NULL
>
>
> --
> Kzeto Ng
>

How to get Report in text format in SSRS?

Hi,

Can aybody help me in the below requirement.

I have a report like below:

<<Name Of Compnay>>

<<Name Of Report>>

<<Todays Date>>

<<ReportPeriod>> From Date - To date

The above is header and then the data in the report should come as the <<Data>> and # to be used as separator for columns.

163 #GXXX #ABC Comp Ltd. #DTDC#PPPP ABC XYZ ETC #111

*End Of Report*

How can I get this exported as it is to the .txt file?

Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.

How can this be achived using SSRS? Is any custome code required?

Thanks in advacne for your help.

Regards,

Virendra

Note that I have designed the report int he above fashion and I can see this in the browser. I want this to be stored as txt file in the same format as the report is displayed. CSV will not do.

Any help will be appreciated in this problem.

Virendra

|||

There are a couple of different ways to do this, none perfect. The hardest one is the most perfect, as is usually the case <g>, but every case what you want to do is subject to certain layout limitations, which I am sure you already recognized.

OK:

* -- hardest: write your own delivery extension. Now you're in charge of what comes out to the text file and there is nothing in the way.

* -- not actually hard at all but most people don't want to do it, and I wish I understood why <sigh>: write an XSLT that takes your data from the XML export version of the report and converts it to text. Not very hard to make generic, FWIW. To get your custom headers, if you want to do this with no code at all and just attach the XSLT to the data properties of the report, you probably need have another little table that exposes your parameters in the report body. If you apply the XSLT externally (very little code) you can parameterize the XSLT with these values instead.

* -- do the same thing as above but use the DOM instead of XSLT. Can't be attached to the report and is not as performant, but as I said, people don't like it when I suggest XSLT so ... <g>

* -- write some code, or a different XSLT, to grab the HTML version instead of the XML, and strip the tables down to what you're looking for. I don't like to do it this way but if you need the page headers for some reason you will probably need the "complete" output rather than the data-xml output as your source.

* -- the easiest way, and surprisingly good, although wacky <g>: print to file using the generic text printer driver. You may think this is crazy, and you're not likely to hear this suggestion from anybody but me <g>. But remember that the people who built these drivers (which come with the OS) have already faced the problem you're facing and made sensible compromises for the various layout problems. In some ways, everything else we do to re-solve this problem is re-inventing the wheel.

HTH,

>L<

|||

Hi,

Thanks for your reply. It will definitely help me to decide on my approach to develop this functionality.

I think the way I have done this in SSIS is very easy than the way this can be done in SSRS. Do you think this will cause any issues for any reasons like Performace, maintenenace etc?

BTW, can you provide some link to see how the rendering functionality can be extended?

Regards,

Virendra

|||

I have no idea what issues of performance or maintenance will be caused by your SSIS approach -- and if you're happy with it you shouldn't change. However, it really is easy to use the generic printer driver <s>. Have you tried that?

As far as "extending the rendering functionality", you don't so much "extend" the existing rendering as provide a new extension output type. RS provides several, you're writing a new one. Start here

http://msdn2.microsoft.com/it-it/library/ms154606.aspx

.. but I"m trying to tell you that you will be doing something that the generic printer driver already does <s>.

>L<

|||

Hi,

I tried the priter option and it is very easy to do this. I have two problems though. Can you futher help in this?

1. When I keep some space between two rows in my report, it prits the garbage character like three samll squares (Which I do not see if I open it using worpad). Can this be avoided?

2. How can I directly write the report to harddisk through my .NET code?

Regards,

Virendra

|||

1. the "garbarge" is line break characters (CR's, LF's, or some combination of those characters) that your viewing application does not understand. The way to fix is:

* -- analyze the characters to figure out exactly what ones are causing the problem

* -- translate those characters to a sequence your viewing application is happer with, before saving to disk, or

* -- fix the viewing application <s>.

2. You will find a printer delivery extension sample as part of the SQL Server sample set. You will also find something similar (printing from a console app) here: http://gotreportviewer.com. In both cases you will probably need to do a bit of revision, but you will see that you can specify printer or device settings as part of the process. In this case, one of the settings you want to specify is the filename to go to, typically a temporary/generated one. (Don't just set up your printer setup to an explicit filename, IOW, that wouldn't be thread safe.)

HTH,

>L<

|||

Thanks for your help. I will try the options mentioned by you.

The application, I am trying to use for viewing the file is notepad. I don't know why the problem is with the notepad but not with the wordpad.

Thanks again for your reply. I will try those options to compare against my SSIS approach.

Virendra

|||

In Notepad the results will often be different depending on the font. If you want to send me a text file to check the characters -- if for some reason you can't figure out what they are -- you can email to me (site is in my sig, contact details are on my site) or post somewhere where I can d/l an example.

>L<

|||

Hi Lisa,

I have sent you the text file generated from the report using the text generic printer.

Do you know what this character is and how to avoid this ?

Virendra

|||

hi Virenda,

The character in your text file (I assume that you are talking about the one at the end of line 20?) is an FF (form feed), chr(12) in ASCII.

some helpful background on how to do this yourself:

When you have characters like this, and they come out as plain squares, you can usually copy the character and interrogate it in an application that will give you the ascii value -- use whatever you like <s>.

Sometimes the Windows clipboard buffer will not take the character in question, in which case you can still find its ascii value without copying and pasting. Read the file, or a truncated copy of it, into a string variable programmatically and interrogate its characters instead of using the clipboard.

It also often helps to change the font in notepad to an OEM/DOS font or other script that will change the character to something more recognizable, too. In this case, you might see a little "female symbol" (circle with plus underneath) for chr(12).

Please refer to http://www.asciitable.com/ or similar charts for other whitespace characters that may be interpreted differently in different environments...

The thing you need to do, for your uses, to get the right result, depends on how you would like to represent that formfeed or other whitespace character in your text file. Usually you represent a formfeed by one or more extra blank lines. In your file hard returns appear to be represented by CRLF combinations (CHR(13) + CHR(10)), so you replace the FF by as many CRLFs as you like to represent your "extra space" in the text file version of your output.

I hope this helps,

>L<

|||

Hi Virendra,

Modify the config file '<Extension Name="CSV" ' node to

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>some delimiter (unfortunately Tab char doesn't work)</FieldDelimiter>
<Extension>TXT</Extension>
</DeviceInfo></Configuration>
</Extension>

After modifying, restart the report server and navigate to report via Reportserver. Open the report. Now download the report by selecting CSV file type. This will save the file or open the file in text directly.

Let me know if this solve the problem.

Thanks,

S Suresh

|||

Suresh and other folks reading (so nobody gets confused),

The text file is not created by the CSV extension -- because Virenda wants to preserve the physical layout of his elements in his text file <s>.

The mechanism is a generic text printer driver and the character in question is not a field delimiter; it is a form feed.

If one wants to do this with an intermediate format it is, frankly, easier to do it with the XML export than the CSV export -- but the generic text printer driver really does most of the work for you. Whether parsing the CSV as an intermediate format *or* using XML-XSLT to do the same thing, one needs to reference the RDL metadata to achieve V's desired result (preserving his physical layout on the page with appropriate white space between elements).

I hope this helps,

>L<

>L<

|||

Lisa and All (who contributed to this post),

After checking all the possibilities, it looks like that after getting the report in the text format as I want, I need to write another piece of code. This will be for getting read of the FF,CR,LF......(squares) characters from the notepad.

With SSIS, I don't have to do that. I was thinking of not to use SSIS as there was some IO involved for each file generation. If the same way I have to do the IO using this SSRS option, then I think it is better to either extending the rendering functionality or use SSIS.

I tired in SSRS different ways to get clean new line or line feed, but it alwyas shows me the squares.

Do let me know if you have any thoughts on this.

Virendra

|||

Sorry for the delay in reply -- I have been away --

They are not "squares" and they are not "from SSRS". They are from the printer driver and they represent formfeeds <s>. The only way to get rid of them is to tell the printer driver that you want a page size which eliminates the formfeeds -- and as I think you said something about "when I have put extra space in my RDL" -- meaning explicit page breaks -- this really won't work for you <s>.

Myself, I don't really see any problems with doing a simple Replace for the FF character. I also don't really understand your concern about IO -- whether in SSIS or other code -- here.

Basically even when it is behind the scenes in streams or whatever there is IO if the classes handling your file deem that temporary files are required to perform an operation. It often depends on the file size and memory available, and it is often beyond your control. Since there is clearly something you are worried about here I will assume that your file sizes are large -- but that makes me wonder in what way you think you would be avoiding IO even if there were no FFs involved.

As a case in point: I don't actually have an preference for doing this one way or another, unless it happens to be my preference for using XML/XSLT for this type of thing. I offered that solution for you but you did not seem interested at all -- and many developers are not, so I don't like to push that on people even though I do like it <s>. My point in bringing it up now is simply that there could be IO here also. I don't think that this fact would be my deciding factor in doing something using one method or another, because it is always potentially present.

Virendra -- honestly -- maybe we should be talking about why you are hesitating over the IO required for your current (SSIS) solution so that we can weigh this type of thing more accurately. If you don't want to get more specific about what you are doing, that's okay, but there is no way we can talk about performance and efficiency with the information we have so far...

Good luck,

>L<

How to get Report in text format in SSRS?

Hi,

Can aybody help me in the below requirement.

I have a report like below:

<<Name Of Compnay>>

<<Name Of Report>>

<<Todays Date>>

<<ReportPeriod>> From Date - To date

The above is header and then the data in the report should come as the <<Data>> and # to be used as separator for columns.

163 #GXXX #ABC Comp Ltd. #DTDC#PPPP ABC XYZ ETC #111

*End Of Report*

How can I get this exported as it is to the .txt file?

Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.

How can this be achived using SSRS? Is any custome code required?

Thanks in advacne for your help.

Regards,

Virendra

Note that I have designed the report int he above fashion and I can see this in the browser. I want this to be stored as txt file in the same format as the report is displayed. CSV will not do.

Any help will be appreciated in this problem.

Virendra

|||

There are a couple of different ways to do this, none perfect. The hardest one is the most perfect, as is usually the case <g>, but every case what you want to do is subject to certain layout limitations, which I am sure you already recognized.

OK:

* -- hardest: write your own delivery extension. Now you're in charge of what comes out to the text file and there is nothing in the way.

* -- not actually hard at all but most people don't want to do it, and I wish I understood why <sigh>: write an XSLT that takes your data from the XML export version of the report and converts it to text. Not very hard to make generic, FWIW. To get your custom headers, if you want to do this with no code at all and just attach the XSLT to the data properties of the report, you probably need have another little table that exposes your parameters in the report body. If you apply the XSLT externally (very little code) you can parameterize the XSLT with these values instead.

* -- do the same thing as above but use the DOM instead of XSLT. Can't be attached to the report and is not as performant, but as I said, people don't like it when I suggest XSLT so ... <g>

* -- write some code, or a different XSLT, to grab the HTML version instead of the XML, and strip the tables down to what you're looking for. I don't like to do it this way but if you need the page headers for some reason you will probably need the "complete" output rather than the data-xml output as your source.

* -- the easiest way, and surprisingly good, although wacky <g>: print to file using the generic text printer driver. You may think this is crazy, and you're not likely to hear this suggestion from anybody but me <g>. But remember that the people who built these drivers (which come with the OS) have already faced the problem you're facing and made sensible compromises for the various layout problems. In some ways, everything else we do to re-solve this problem is re-inventing the wheel.

HTH,

>L<

|||

Hi,

Thanks for your reply. It will definitely help me to decide on my approach to develop this functionality.

I think the way I have done this in SSIS is very easy than the way this can be done in SSRS. Do you think this will cause any issues for any reasons like Performace, maintenenace etc?

BTW, can you provide some link to see how the rendering functionality can be extended?

Regards,

Virendra

|||

I have no idea what issues of performance or maintenance will be caused by your SSIS approach -- and if you're happy with it you shouldn't change. However, it really is easy to use the generic printer driver <s>. Have you tried that?

As far as "extending the rendering functionality", you don't so much "extend" the existing rendering as provide a new extension output type. RS provides several, you're writing a new one. Start here

http://msdn2.microsoft.com/it-it/library/ms154606.aspx

.. but I"m trying to tell you that you will be doing something that the generic printer driver already does <s>.

>L<

|||

Hi,

I tried the priter option and it is very easy to do this. I have two problems though. Can you futher help in this?

1. When I keep some space between two rows in my report, it prits the garbage character like three samll squares (Which I do not see if I open it using worpad). Can this be avoided?

2. How can I directly write the report to harddisk through my .NET code?

Regards,

Virendra

|||

1. the "garbarge" is line break characters (CR's, LF's, or some combination of those characters) that your viewing application does not understand. The way to fix is:

* -- analyze the characters to figure out exactly what ones are causing the problem

* -- translate those characters to a sequence your viewing application is happer with, before saving to disk, or

* -- fix the viewing application <s>.

2. You will find a printer delivery extension sample as part of the SQL Server sample set. You will also find something similar (printing from a console app) here: http://gotreportviewer.com. In both cases you will probably need to do a bit of revision, but you will see that you can specify printer or device settings as part of the process. In this case, one of the settings you want to specify is the filename to go to, typically a temporary/generated one. (Don't just set up your printer setup to an explicit filename, IOW, that wouldn't be thread safe.)

HTH,

>L<

|||

Thanks for your help. I will try the options mentioned by you.

The application, I am trying to use for viewing the file is notepad. I don't know why the problem is with the notepad but not with the wordpad.

Thanks again for your reply. I will try those options to compare against my SSIS approach.

Virendra

|||

In Notepad the results will often be different depending on the font. If you want to send me a text file to check the characters -- if for some reason you can't figure out what they are -- you can email to me (site is in my sig, contact details are on my site) or post somewhere where I can d/l an example.

>L<

|||

Hi Lisa,

I have sent you the text file generated from the report using the text generic printer.

Do you know what this character is and how to avoid this ?

Virendra

|||

hi Virenda,

The character in your text file (I assume that you are talking about the one at the end of line 20?) is an FF (form feed), chr(12) in ASCII.

some helpful background on how to do this yourself:

When you have characters like this, and they come out as plain squares, you can usually copy the character and interrogate it in an application that will give you the ascii value -- use whatever you like <s>.

Sometimes the Windows clipboard buffer will not take the character in question, in which case you can still find its ascii value without copying and pasting. Read the file, or a truncated copy of it, into a string variable programmatically and interrogate its characters instead of using the clipboard.

It also often helps to change the font in notepad to an OEM/DOS font or other script that will change the character to something more recognizable, too. In this case, you might see a little "female symbol" (circle with plus underneath) for chr(12).

Please refer to http://www.asciitable.com/ or similar charts for other whitespace characters that may be interpreted differently in different environments...

The thing you need to do, for your uses, to get the right result, depends on how you would like to represent that formfeed or other whitespace character in your text file. Usually you represent a formfeed by one or more extra blank lines. In your file hard returns appear to be represented by CRLF combinations (CHR(13) + CHR(10)), so you replace the FF by as many CRLFs as you like to represent your "extra space" in the text file version of your output.

I hope this helps,

>L<

|||

Hi Virendra,

Modify the config file '<Extension Name="CSV" ' node to

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>some delimiter (unfortunately Tab char doesn't work)</FieldDelimiter>
<Extension>TXT</Extension>
</DeviceInfo></Configuration>
</Extension>

After modifying, restart the report server and navigate to report via Reportserver. Open the report. Now download the report by selecting CSV file type. This will save the file or open the file in text directly.

Let me know if this solve the problem.

Thanks,

S Suresh

|||

Suresh and other folks reading (so nobody gets confused),

The text file is not created by the CSV extension -- because Virenda wants to preserve the physical layout of his elements in his text file <s>.

The mechanism is a generic text printer driver and the character in question is not a field delimiter; it is a form feed.

If one wants to do this with an intermediate format it is, frankly, easier to do it with the XML export than the CSV export -- but the generic text printer driver really does most of the work for you. Whether parsing the CSV as an intermediate format *or* using XML-XSLT to do the same thing, one needs to reference the RDL metadata to achieve V's desired result (preserving his physical layout on the page with appropriate white space between elements).

I hope this helps,

>L<

>L<

|||

Lisa and All (who contributed to this post),

After checking all the possibilities, it looks like that after getting the report in the text format as I want, I need to write another piece of code. This will be for getting read of the FF,CR,LF......(squares) characters from the notepad.

With SSIS, I don't have to do that. I was thinking of not to use SSIS as there was some IO involved for each file generation. If the same way I have to do the IO using this SSRS option, then I think it is better to either extending the rendering functionality or use SSIS.

I tired in SSRS different ways to get clean new line or line feed, but it alwyas shows me the squares.

Do let me know if you have any thoughts on this.

Virendra

|||

Sorry for the delay in reply -- I have been away --

They are not "squares" and they are not "from SSRS". They are from the printer driver and they represent formfeeds <s>. The only way to get rid of them is to tell the printer driver that you want a page size which eliminates the formfeeds -- and as I think you said something about "when I have put extra space in my RDL" -- meaning explicit page breaks -- this really won't work for you <s>.

Myself, I don't really see any problems with doing a simple Replace for the FF character. I also don't really understand your concern about IO -- whether in SSIS or other code -- here.

Basically even when it is behind the scenes in streams or whatever there is IO if the classes handling your file deem that temporary files are required to perform an operation. It often depends on the file size and memory available, and it is often beyond your control. Since there is clearly something you are worried about here I will assume that your file sizes are large -- but that makes me wonder in what way you think you would be avoiding IO even if there were no FFs involved.

As a case in point: I don't actually have an preference for doing this one way or another, unless it happens to be my preference for using XML/XSLT for this type of thing. I offered that solution for you but you did not seem interested at all -- and many developers are not, so I don't like to push that on people even though I do like it <s>. My point in bringing it up now is simply that there could be IO here also. I don't think that this fact would be my deciding factor in doing something using one method or another, because it is always potentially present.

Virendra -- honestly -- maybe we should be talking about why you are hesitating over the IO required for your current (SSIS) solution so that we can weigh this type of thing more accurately. If you don't want to get more specific about what you are doing, that's okay, but there is no way we can talk about performance and efficiency with the information we have so far...

Good luck,

>L<

How to get Report in text format in SSRS?

Hi,

Can aybody help me in the below requirement.

I have a report like below:

<<Name Of Compnay>>

<<Name Of Report>>

<<Todays Date>>

<<ReportPeriod>> From Date - To date

The above is header and then the data in the report should come as the <<Data>> and # to be used as separator for columns.

163 #GXXX #ABC Comp Ltd. #DTDC#PPPP ABC XYZ ETC #111

*End Of Report*

How can I get this exported as it is to the .txt file?

Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.

How can this be achived using SSRS? Is any custome code required?

Thanks in advacne for your help.

Regards,

Virendra

Note that I have designed the report int he above fashion and I can see this in the browser. I want this to be stored as txt file in the same format as the report is displayed. CSV will not do.

Any help will be appreciated in this problem.

Virendra

|||

There are a couple of different ways to do this, none perfect. The hardest one is the most perfect, as is usually the case <g>, but every case what you want to do is subject to certain layout limitations, which I am sure you already recognized.

OK:

* -- hardest: write your own delivery extension. Now you're in charge of what comes out to the text file and there is nothing in the way.

* -- not actually hard at all but most people don't want to do it, and I wish I understood why <sigh>: write an XSLT that takes your data from the XML export version of the report and converts it to text. Not very hard to make generic, FWIW. To get your custom headers, if you want to do this with no code at all and just attach the XSLT to the data properties of the report, you probably need have another little table that exposes your parameters in the report body. If you apply the XSLT externally (very little code) you can parameterize the XSLT with these values instead.

* -- do the same thing as above but use the DOM instead of XSLT. Can't be attached to the report and is not as performant, but as I said, people don't like it when I suggest XSLT so ... <g>

* -- write some code, or a different XSLT, to grab the HTML version instead of the XML, and strip the tables down to what you're looking for. I don't like to do it this way but if you need the page headers for some reason you will probably need the "complete" output rather than the data-xml output as your source.

* -- the easiest way, and surprisingly good, although wacky <g>: print to file using the generic text printer driver. You may think this is crazy, and you're not likely to hear this suggestion from anybody but me <g>. But remember that the people who built these drivers (which come with the OS) have already faced the problem you're facing and made sensible compromises for the various layout problems. In some ways, everything else we do to re-solve this problem is re-inventing the wheel.

HTH,

>L<

|||

Hi,

Thanks for your reply. It will definitely help me to decide on my approach to develop this functionality.

I think the way I have done this in SSIS is very easy than the way this can be done in SSRS. Do you think this will cause any issues for any reasons like Performace, maintenenace etc?

BTW, can you provide some link to see how the rendering functionality can be extended?

Regards,

Virendra

|||

I have no idea what issues of performance or maintenance will be caused by your SSIS approach -- and if you're happy with it you shouldn't change. However, it really is easy to use the generic printer driver <s>. Have you tried that?

As far as "extending the rendering functionality", you don't so much "extend" the existing rendering as provide a new extension output type. RS provides several, you're writing a new one. Start here

http://msdn2.microsoft.com/it-it/library/ms154606.aspx

.. but I"m trying to tell you that you will be doing something that the generic printer driver already does <s>.

>L<

|||

Hi,

I tried the priter option and it is very easy to do this. I have two problems though. Can you futher help in this?

1. When I keep some space between two rows in my report, it prits the garbage character like three samll squares (Which I do not see if I open it using worpad). Can this be avoided?

2. How can I directly write the report to harddisk through my .NET code?

Regards,

Virendra

|||

1. the "garbarge" is line break characters (CR's, LF's, or some combination of those characters) that your viewing application does not understand. The way to fix is:

* -- analyze the characters to figure out exactly what ones are causing the problem

* -- translate those characters to a sequence your viewing application is happer with, before saving to disk, or

* -- fix the viewing application <s>.

2. You will find a printer delivery extension sample as part of the SQL Server sample set. You will also find something similar (printing from a console app) here: http://gotreportviewer.com. In both cases you will probably need to do a bit of revision, but you will see that you can specify printer or device settings as part of the process. In this case, one of the settings you want to specify is the filename to go to, typically a temporary/generated one. (Don't just set up your printer setup to an explicit filename, IOW, that wouldn't be thread safe.)

HTH,

>L<

|||

Thanks for your help. I will try the options mentioned by you.

The application, I am trying to use for viewing the file is notepad. I don't know why the problem is with the notepad but not with the wordpad.

Thanks again for your reply. I will try those options to compare against my SSIS approach.

Virendra

|||

In Notepad the results will often be different depending on the font. If you want to send me a text file to check the characters -- if for some reason you can't figure out what they are -- you can email to me (site is in my sig, contact details are on my site) or post somewhere where I can d/l an example.

>L<

|||

Hi Lisa,

I have sent you the text file generated from the report using the text generic printer.

Do you know what this character is and how to avoid this ?

Virendra

|||

hi Virenda,

The character in your text file (I assume that you are talking about the one at the end of line 20?) is an FF (form feed), chr(12) in ASCII.

some helpful background on how to do this yourself:

When you have characters like this, and they come out as plain squares, you can usually copy the character and interrogate it in an application that will give you the ascii value -- use whatever you like <s>.

Sometimes the Windows clipboard buffer will not take the character in question, in which case you can still find its ascii value without copying and pasting. Read the file, or a truncated copy of it, into a string variable programmatically and interrogate its characters instead of using the clipboard.

It also often helps to change the font in notepad to an OEM/DOS font or other script that will change the character to something more recognizable, too. In this case, you might see a little "female symbol" (circle with plus underneath) for chr(12).

Please refer to http://www.asciitable.com/ or similar charts for other whitespace characters that may be interpreted differently in different environments...

The thing you need to do, for your uses, to get the right result, depends on how you would like to represent that formfeed or other whitespace character in your text file. Usually you represent a formfeed by one or more extra blank lines. In your file hard returns appear to be represented by CRLF combinations (CHR(13) + CHR(10)), so you replace the FF by as many CRLFs as you like to represent your "extra space" in the text file version of your output.

I hope this helps,

>L<

|||

Hi Virendra,

Modify the config file '<Extension Name="CSV" ' node to

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>some delimiter (unfortunately Tab char doesn't work)</FieldDelimiter>
<Extension>TXT</Extension>
</DeviceInfo></Configuration>
</Extension>

After modifying, restart the report server and navigate to report via Reportserver. Open the report. Now download the report by selecting CSV file type. This will save the file or open the file in text directly.

Let me know if this solve the problem.

Thanks,

S Suresh

|||

Suresh and other folks reading (so nobody gets confused),

The text file is not created by the CSV extension -- because Virenda wants to preserve the physical layout of his elements in his text file <s>.

The mechanism is a generic text printer driver and the character in question is not a field delimiter; it is a form feed.

If one wants to do this with an intermediate format it is, frankly, easier to do it with the XML export than the CSV export -- but the generic text printer driver really does most of the work for you. Whether parsing the CSV as an intermediate format *or* using XML-XSLT to do the same thing, one needs to reference the RDL metadata to achieve V's desired result (preserving his physical layout on the page with appropriate white space between elements).

I hope this helps,

>L<

>L<

|||

Lisa and All (who contributed to this post),

After checking all the possibilities, it looks like that after getting the report in the text format as I want, I need to write another piece of code. This will be for getting read of the FF,CR,LF......(squares) characters from the notepad.

With SSIS, I don't have to do that. I was thinking of not to use SSIS as there was some IO involved for each file generation. If the same way I have to do the IO using this SSRS option, then I think it is better to either extending the rendering functionality or use SSIS.

I tired in SSRS different ways to get clean new line or line feed, but it alwyas shows me the squares.

Do let me know if you have any thoughts on this.

Virendra

|||

Sorry for the delay in reply -- I have been away --

They are not "squares" and they are not "from SSRS". They are from the printer driver and they represent formfeeds <s>. The only way to get rid of them is to tell the printer driver that you want a page size which eliminates the formfeeds -- and as I think you said something about "when I have put extra space in my RDL" -- meaning explicit page breaks -- this really won't work for you <s>.

Myself, I don't really see any problems with doing a simple Replace for the FF character. I also don't really understand your concern about IO -- whether in SSIS or other code -- here.

Basically even when it is behind the scenes in streams or whatever there is IO if the classes handling your file deem that temporary files are required to perform an operation. It often depends on the file size and memory available, and it is often beyond your control. Since there is clearly something you are worried about here I will assume that your file sizes are large -- but that makes me wonder in what way you think you would be avoiding IO even if there were no FFs involved.

As a case in point: I don't actually have an preference for doing this one way or another, unless it happens to be my preference for using XML/XSLT for this type of thing. I offered that solution for you but you did not seem interested at all -- and many developers are not, so I don't like to push that on people even though I do like it <s>. My point in bringing it up now is simply that there could be IO here also. I don't think that this fact would be my deciding factor in doing something using one method or another, because it is always potentially present.

Virendra -- honestly -- maybe we should be talking about why you are hesitating over the IO required for your current (SSIS) solution so that we can weigh this type of thing more accurately. If you don't want to get more specific about what you are doing, that's okay, but there is no way we can talk about performance and efficiency with the information we have so far...

Good luck,

>L<

How to get Report in text format in SSRS?

Hi,

Can aybody help me in the below requirement.

I have a report like below:

<<Name Of Compnay>>

<<Name Of Report>>

<<Todays Date>>

<<ReportPeriod>> From Date - To date

The above is header and then the data in the report should come as the <<Data>> and # to be used as separator for columns.

163 #GXXX #ABC Comp Ltd. #DTDC#PPPP ABC XYZ ETC #111

*End Of Report*

How can I get this exported as it is to the .txt file?

Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.

How can this be achived using SSRS? Is any custome code required?

Thanks in advacne for your help.

Regards,

Virendra

Note that I have designed the report int he above fashion and I can see this in the browser. I want this to be stored as txt file in the same format as the report is displayed. CSV will not do.

Any help will be appreciated in this problem.

Virendra

|||

There are a couple of different ways to do this, none perfect. The hardest one is the most perfect, as is usually the case <g>, but every case what you want to do is subject to certain layout limitations, which I am sure you already recognized.

OK:

* -- hardest: write your own delivery extension. Now you're in charge of what comes out to the text file and there is nothing in the way.

* -- not actually hard at all but most people don't want to do it, and I wish I understood why <sigh>: write an XSLT that takes your data from the XML export version of the report and converts it to text. Not very hard to make generic, FWIW. To get your custom headers, if you want to do this with no code at all and just attach the XSLT to the data properties of the report, you probably need have another little table that exposes your parameters in the report body. If you apply the XSLT externally (very little code) you can parameterize the XSLT with these values instead.

* -- do the same thing as above but use the DOM instead of XSLT. Can't be attached to the report and is not as performant, but as I said, people don't like it when I suggest XSLT so ... <g>

* -- write some code, or a different XSLT, to grab the HTML version instead of the XML, and strip the tables down to what you're looking for. I don't like to do it this way but if you need the page headers for some reason you will probably need the "complete" output rather than the data-xml output as your source.

* -- the easiest way, and surprisingly good, although wacky <g>: print to file using the generic text printer driver. You may think this is crazy, and you're not likely to hear this suggestion from anybody but me <g>. But remember that the people who built these drivers (which come with the OS) have already faced the problem you're facing and made sensible compromises for the various layout problems. In some ways, everything else we do to re-solve this problem is re-inventing the wheel.

HTH,

>L<

|||

Hi,

Thanks for your reply. It will definitely help me to decide on my approach to develop this functionality.

I think the way I have done this in SSIS is very easy than the way this can be done in SSRS. Do you think this will cause any issues for any reasons like Performace, maintenenace etc?

BTW, can you provide some link to see how the rendering functionality can be extended?

Regards,

Virendra

|||

I have no idea what issues of performance or maintenance will be caused by your SSIS approach -- and if you're happy with it you shouldn't change. However, it really is easy to use the generic printer driver <s>. Have you tried that?

As far as "extending the rendering functionality", you don't so much "extend" the existing rendering as provide a new extension output type. RS provides several, you're writing a new one. Start here

http://msdn2.microsoft.com/it-it/library/ms154606.aspx

.. but I"m trying to tell you that you will be doing something that the generic printer driver already does <s>.

>L<

|||

Hi,

I tried the priter option and it is very easy to do this. I have two problems though. Can you futher help in this?

1. When I keep some space between two rows in my report, it prits the garbage character like three samll squares (Which I do not see if I open it using worpad). Can this be avoided?

2. How can I directly write the report to harddisk through my .NET code?

Regards,

Virendra

|||

1. the "garbarge" is line break characters (CR's, LF's, or some combination of those characters) that your viewing application does not understand. The way to fix is:

* -- analyze the characters to figure out exactly what ones are causing the problem

* -- translate those characters to a sequence your viewing application is happer with, before saving to disk, or

* -- fix the viewing application <s>.

2. You will find a printer delivery extension sample as part of the SQL Server sample set. You will also find something similar (printing from a console app) here: http://gotreportviewer.com. In both cases you will probably need to do a bit of revision, but you will see that you can specify printer or device settings as part of the process. In this case, one of the settings you want to specify is the filename to go to, typically a temporary/generated one. (Don't just set up your printer setup to an explicit filename, IOW, that wouldn't be thread safe.)

HTH,

>L<

|||

Thanks for your help. I will try the options mentioned by you.

The application, I am trying to use for viewing the file is notepad. I don't know why the problem is with the notepad but not with the wordpad.

Thanks again for your reply. I will try those options to compare against my SSIS approach.

Virendra

|||

In Notepad the results will often be different depending on the font. If you want to send me a text file to check the characters -- if for some reason you can't figure out what they are -- you can email to me (site is in my sig, contact details are on my site) or post somewhere where I can d/l an example.

>L<

|||

Hi Lisa,

I have sent you the text file generated from the report using the text generic printer.

Do you know what this character is and how to avoid this ?

Virendra

|||

hi Virenda,

The character in your text file (I assume that you are talking about the one at the end of line 20?) is an FF (form feed), chr(12) in ASCII.

some helpful background on how to do this yourself:

When you have characters like this, and they come out as plain squares, you can usually copy the character and interrogate it in an application that will give you the ascii value -- use whatever you like <s>.

Sometimes the Windows clipboard buffer will not take the character in question, in which case you can still find its ascii value without copying and pasting. Read the file, or a truncated copy of it, into a string variable programmatically and interrogate its characters instead of using the clipboard.

It also often helps to change the font in notepad to an OEM/DOS font or other script that will change the character to something more recognizable, too. In this case, you might see a little "female symbol" (circle with plus underneath) for chr(12).

Please refer to http://www.asciitable.com/ or similar charts for other whitespace characters that may be interpreted differently in different environments...

The thing you need to do, for your uses, to get the right result, depends on how you would like to represent that formfeed or other whitespace character in your text file. Usually you represent a formfeed by one or more extra blank lines. In your file hard returns appear to be represented by CRLF combinations (CHR(13) + CHR(10)), so you replace the FF by as many CRLFs as you like to represent your "extra space" in the text file version of your output.

I hope this helps,

>L<

|||

Hi Virendra,

Modify the config file '<Extension Name="CSV" ' node to

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>some delimiter (unfortunately Tab char doesn't work)</FieldDelimiter>
<Extension>TXT</Extension>
</DeviceInfo></Configuration>
</Extension>

After modifying, restart the report server and navigate to report via Reportserver. Open the report. Now download the report by selecting CSV file type. This will save the file or open the file in text directly.

Let me know if this solve the problem.

Thanks,

S Suresh

|||

Suresh and other folks reading (so nobody gets confused),

The text file is not created by the CSV extension -- because Virenda wants to preserve the physical layout of his elements in his text file <s>.

The mechanism is a generic text printer driver and the character in question is not a field delimiter; it is a form feed.

If one wants to do this with an intermediate format it is, frankly, easier to do it with the XML export than the CSV export -- but the generic text printer driver really does most of the work for you. Whether parsing the CSV as an intermediate format *or* using XML-XSLT to do the same thing, one needs to reference the RDL metadata to achieve V's desired result (preserving his physical layout on the page with appropriate white space between elements).

I hope this helps,

>L<

>L<

|||

Lisa and All (who contributed to this post),

After checking all the possibilities, it looks like that after getting the report in the text format as I want, I need to write another piece of code. This will be for getting read of the FF,CR,LF......(squares) characters from the notepad.

With SSIS, I don't have to do that. I was thinking of not to use SSIS as there was some IO involved for each file generation. If the same way I have to do the IO using this SSRS option, then I think it is better to either extending the rendering functionality or use SSIS.

I tired in SSRS different ways to get clean new line or line feed, but it alwyas shows me the squares.

Do let me know if you have any thoughts on this.

Virendra

|||

Sorry for the delay in reply -- I have been away --

They are not "squares" and they are not "from SSRS". They are from the printer driver and they represent formfeeds <s>. The only way to get rid of them is to tell the printer driver that you want a page size which eliminates the formfeeds -- and as I think you said something about "when I have put extra space in my RDL" -- meaning explicit page breaks -- this really won't work for you <s>.

Myself, I don't really see any problems with doing a simple Replace for the FF character. I also don't really understand your concern about IO -- whether in SSIS or other code -- here.

Basically even when it is behind the scenes in streams or whatever there is IO if the classes handling your file deem that temporary files are required to perform an operation. It often depends on the file size and memory available, and it is often beyond your control. Since there is clearly something you are worried about here I will assume that your file sizes are large -- but that makes me wonder in what way you think you would be avoiding IO even if there were no FFs involved.

As a case in point: I don't actually have an preference for doing this one way or another, unless it happens to be my preference for using XML/XSLT for this type of thing. I offered that solution for you but you did not seem interested at all -- and many developers are not, so I don't like to push that on people even though I do like it <s>. My point in bringing it up now is simply that there could be IO here also. I don't think that this fact would be my deciding factor in doing something using one method or another, because it is always potentially present.

Virendra -- honestly -- maybe we should be talking about why you are hesitating over the IO required for your current (SSIS) solution so that we can weigh this type of thing more accurately. If you don't want to get more specific about what you are doing, that's okay, but there is no way we can talk about performance and efficiency with the information we have so far...

Good luck,

>L<

How to get info in SELECT directly instead of UPDATE? Thanks.

Greetings!

I have the 3 raw data tables below, and would like to extract some
data. My current query is really cumbersome, does anyone know a better
way to get all the info (attach the right state to the lowest zip code)
in one step? Thanks a lot! Here are the details.

- Ideal Output, for each person , keep
(1) person_id (From Table A)
(2) The earliest open_date of accounts starting with 2 (From Table A)
(3) ssn (From Table B)
(4) Zip and state info (From Table C), but only keep the lowest zip and
state.

-Table A -
person_idaccountopen_date
10001220000015/15/2003
10001220000026/20/2004
10001300000012/2/2002
10002220000038/12/2004
10002220000049/15/2004
10002300000022/16/2005

-Table B -
person_idzip state
1000111111AA
1000122222CC
1000233333BB

--Table C -
person_idssn
100011234567
100022345678

-Ideal Output-
person_idmin_openssnzipstate
100015/15/2003123456711111AA
100028/12/2004234567833333BB

Here is what I did:
Select a.person_id, min(a.open_date), b.ssn, min(c.zip) as zip
,cast (0 as varchar) as state
Into output
>From TableA as a
Join TableB as b
on a.person_id=b.person_id
Join TableC as c
on a.person_id=c.person_id
Where a.account like '2%'
Group by a.person_id, b.ssn
Order by a.person_id

Update output
Set output.state=b.state
>From output as a
Join TableC as b
on a.person_id=b.person_id
and a.zip=b.zip(rong.guo@.gmail.com) writes:
> I have the 3 raw data tables below, and would like to extract some
> data. My current query is really cumbersome, does anyone know a better
> way to get all the info (attach the right state to the lowest zip code)
> in one step? Thanks a lot! Here are the details.
> - Ideal Output, for each person , keep
> (1) person_id (From Table A)
> (2) The earliest open_date of accounts starting with 2 (From Table A)
> (3) ssn (From Table B)
> (4) Zip and state info (From Table C), but only keep the lowest zip and
> state.

For these kind of these questions, it's always recommendable to post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer to
develop a tested query. Since your sample data was not in this form,
this query is untested:

SELECT a1.person_id, a1.min_open, c.ssn, a1.min_zip, b.state
FROM (SELECT a.person_id, min_open = MIN(a.open_date),
min_zip = MIN(b.zip)
FROM table_a a
JOIN table_b b ON a.person_id = b_person_id
GROUP BY a.person_id) AS a1
JOIN table_b b ON a1.person_id = b.person_id
AND a1.min_zip = b.zip
JOIN table_c c ON a1.person_id = c.person_id

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland.

Because I am not familiar with CREATE and INSERT, I didn't use them
this time. Sorry for the inconvenience. I will certainly try it next
time.

Thanks again!