Database/Table Structure and Sample Data 1

[njp1]

I have created a report that prompts the user for a table name (i.e. JC10) then it lists the column id and column names within the table.

Using SQL Server 2000 and Crystal Reports Developer 8.5

It uses the following SQL query:
SELECT
sysobjects."name",
syscolumns."name", syscolumns."colorder",
suser_sname()
FROM
{ oj "CRCDATA"."dbo"."sysobjects" sysobjects INNER JOIN "CRCDATA"."dbo"."syscolumns" syscolumns ON
sysobjects."id" = syscolumns."id"}
WHERE
sysobjects."name" = 'JC10'
ORDER BY
sysobjects."name" ASC

I would like to create a subreport that takes the table name (i.e. JC10) and retrieves the first (or last record) in the table. Each column name would display that column's sample value beside it. I need help on the subreport and the subreport's links to the main report.

 

[vidru]

I think if you really want to do this, you'll have to do it with a subreport based on a stored procedure, using dynamic sql.

The procedure would take 2 parameters: @TableName and @ColName (linked from the main report). Then to get some sample data out of the column, you'd use dynamic sql to get a sample:

EXEC('SELECT TOP 1 ' + @ColName + ' FROM ' + @TableName
 + ' WHERE ' + @ColName  + ' IS NOT NULL')

Hope that helps get you started at least.

-dave

 

[njp1]

I am not very familiar with stored procedures but I gave it a try.

I should explain why I want this: It is for database testing. I do a lot of database testing and I use reporting to verify that an application is working properly. I would like an easy way to get a list of a table's columns along with some sample data. Yes I could use Enterprise Manager but copying, pasting and formatting the query results into a text editor is tedious. When I am assisted in my testing by others who are less experienced in databases, it takes too much time to educate the tester on SQL and Enterprise Manager. A report would allow them to document their test results more easily. There are probably tools out there that do this but our budget is very limited.

I created the following stored procedure:

CREATE PROCEDURE [dbo].[ct_tablecolumn]
@TableName varchar(10)='',
@ColName varchar{30}=''
AS
set nocount off
EXEC('SELECT TOP 1 * ' + ' FROM ' + @TableName
' ORDER BY '+@ColName+ ' DESC ')
GO

I am trying to get the stored procedure to return the last record in the table.

Two problems:
1. I created a subreport that contains formulas containing the parameters for the stored procedure. I used the formulas to link to my main report. (I did this because the parameters do not appear when use the option "Select Data in subreport based on field.&quot
However when I press F5 to prompt for new parameters the prompts for the stored procedure parameters continue to appear (this means you have to enter the parameters twice).
I tried using shared variables and that didn't work either.

Do you know how to link the subreport for the stored procedure to the main report so the "extra" prompts go away?
2. The stored procedure appears to be caching the results. When your run the stored procedure subreport the first time it seems to work (gives you the desired columns in Visual Linking Expert) then when you rerun the report with different parameters CR just shows you the same columns in Visual Linking Expert.

Do you have to save the results to a temporary table before you can report on the results?

Do you have to add a DROP PROCEDURE or ALTER PROCEDURE statement to the stored procedure to get rid of any cached results?

 

[synapsevampire]

There's a lot of SQL out there to do this sort of thing, I was on a SQL Server contract where I D/L'd the SQL to do this, and then used a Crystal Report against a slightly modified version of that SQL.

-k

 

[njp1]

I found the answer to my second issue on

http://www.sql-server-performance.com/stored_procedures.asp

I have to use the WITH RECOMPILE option in my stored procedure.

Thanks for the reminder that alternative resources exist out there.

 

[vidru]

Is it not letting you link the parameters, or did you just not set up the links?

To link the subreport to the main report, right click on the subreport and go to 'Change Subreport Links'. The 'Field(s) to link...' list should have your parameters. When you select them, you should be able to click the 'Subreport parameter field to use' dropdown to link the main report parameter to the corresponding subreport parameter.

-dave

 

[njp1]

I tried two ways to link the subreport to the main report
1. When adding the subreport initially and I tried using the Link tab of the Add Subreport window and the parameters for the stored procedure do not appear.
2. Using Change Links I tried to link the subreport and again the parameters for the stored procedure do not appear.

 

[vidru]

That's usually a datatype issue. Verify that the datatype of the parameters you're passing are the same type that the subreport is expecting.

-dave

 

[njp1]

The SELECT is returning string data (this database stores all records as strings) and the stored procedure parameters are strings. The main report parameters are strings.

I found a white paper on stored procedures on the Crystal Decisions support site. It recommends that any variables or functions be assigned field aliases. I am going to try modifying my stored procedure to output to a temporary table using generic field aliases like 1,2, 3 etc. and see if that works. Thanks for the help and back to the drawing board...