Dynamically Writing & Rendering Report

I have a report which will change almost everytime it is requested. I am
building the query and writing the RDL on the fly. I don't want to save the
RDL to the report server as it will be of no future use. Is there a way to
pass the RDL as perhaps a string or something similar and ask the
ReportServer to render that instead of a saved report.
Many thanks in advance for any help.
SimonThis functionality is not supported in the current release but is on wish
list for a future release. For now, you'll need to publish the report in
order to render it, i.e., call CreateReport() and then call Render(). You
can always delete it once you're done.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:%23VX8qckbEHA.2544@.TK2MSFTNGP10.phx.gbl...
> I have a report which will change almost everytime it is requested. I am
> building the query and writing the RDL on the fly. I don't want to save
the
> RDL to the report server as it will be of no future use. Is there a way to
> pass the RDL as perhaps a string or something similar and ask the
> ReportServer to render that instead of a saved report.
> Many thanks in advance for any help.
> Simon
>|||Thanks Ravi,
Thats a bit of pain as I could have 100's if not 1000's of stray reports on
the server that would only ever be used once. I now need to write a cleanup
routine to peridoically remove the unused reports from the server. A majorly
inefficient process but looks like the only option.
Do you have any idea when the next iteration of SQL Reporting is planned for
that will include this functionality?
Simon
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> This functionality is not supported in the current release but is on wish
> list for a future release. For now, you'll need to publish the report in
> order to render it, i.e., call CreateReport() and then call Render(). You
> can always delete it once you're done.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services|||The next release of reporting services is in SQL Server 2005. This feature
is on wish list for that release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:ee0IXHxbEHA.796@.TK2MSFTNGP09.phx.gbl...
> Thanks Ravi,
> Thats a bit of pain as I could have 100's if not 1000's of stray reports
on
> the server that would only ever be used once. I now need to write a
cleanup
> routine to peridoically remove the unused reports from the server. A
majorly
> inefficient process but looks like the only option.
> Do you have any idea when the next iteration of SQL Reporting is planned
for
> that will include this functionality?
> Simon
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> > This functionality is not supported in the current release but is on
wish
> > list for a future release. For now, you'll need to publish the report in
> > order to render it, i.e., call CreateReport() and then call Render().
You
> > can always delete it once you're done.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
>

Dynamic USE <YourDBNAME>

I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

I have a script (or at least I'm trying to have a script) that will create (install) a database for me, then create the tables in the database.

I'm using a variable for the database name because the same database may be used for different projects on the same server. I only wanted to set the name of the database once.

I was hoping that after I created the database that I could somehow tell the script to start using that database.

Use @.mydbname is incorrect and if I use the stored proc to execute dynamically, it is not maintained after executing the proc. (Some silly thing about scope).

Does anyone have any ideas I could use?

ThanksWhere is your script stored? As a file, or as a stored procedure, or in a table?

If you are using an interface, like VB, then the simplest solution is to have the interface execute one script to create the database, switch to the new database, and then execute a second script to create the objects.

If you are running a stored procedure or an sql file, you could package the entire code as dynamic SQL and issue the USE statement in your code. sp_Execute can handle multiple statements, including USE. It is limited in size, I believe, so you would probably have to break your code up by object and inclued the USE statement in each section.

This question has come up before, so you could browse previous answers, but I have to say I have never seen a multi-database design like this that didn't result in an ongoing mess of version discrepancies, data duplication, and fragile patchworks of DTS packages to keep the whole thing moving. You'll probably tell me that this is not an option, but build scalability into your database design from day one and you will avoid a multitude of problems.|||Originally posted by rmillman
I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

Thanks

I guess, the solution you are looking for is to use dynamic SQL. You will have to use the EXECUTE statement. Paste this code in the query analyzer, and run it:

USE master
GO

DECLARE @.DB AS VarChar(50)
SET @.DB = 'YourNewDB'
EXECUTE('CREATE DATABASE ' + @.DB)
EXECUTE('CREATE TABLE ' + @.DB + '.dbo.YourFirstNewTable(YourFirstCol int)')
-- add your other stuff
GO

Is this you are looking for?|||Originally posted by rmillman
I have a script [...] that will create (install) a database for me, then create the tables in the database.


Just checking: have you tried the -d <dbname> switch in isql ? Just like your sentence above, your batch file would have two parts. First create the database, then swith to it and use it. I would structure the BAT file as follows:

set DBNAME=mydb
isql -Q "create database .%DBNAME% ..." -S ... -U ...

isql -S ... -U ... -d %DBNAME% -i script.sql

The above executes the create database statement on the command line, so you caan substitute the variable. In the second invocation, the "script.sql" is where you create all your objects. This is pure command-line mode, so it is easily repeatable.

Dynamic Table Name creation...

Hi there,

I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called

@.TABLENAME VARCHAR(16)

and when i say SELECT * FROM @.TABLENAME

it is giving me an error and I think I cannot declare @.TABLENAME as a table variable because I do not want to create a temp table of sorts.

I hope I am clear.

thanks,

Murthy here

can you post the error generated by SQL server

and the code for generation of tables names , I am curious to see how we can achieve it :)

|||

Are you looking for something like this:

Declare @.tempTable table (
TableName varchar(16)
)

Insert into @.tempTable
Select 'Apples'
union
Select 'Pears'

Select * from @.tempTable

Results:
TableName
------
Apples
Pears

|||

Hi there,

I am posting a portion of my code:

SET @.TABLENAME = 'FISCURRENT.dbo.DC67TRANSCYR' + '0' + CAST((CAST((SUBSTRING(@.FISCALYEAR1,3,2)) AS INT) - 1) AS VARCHAR) + SUBSTRING(@.FISCALYEAR1,3,2)

SELECT DESCRIPTION, TRANSTYPE , BE, CATEGORY, FID , OCA, '67' + ORGL2L5 , EO, AMOUNT, GL, TRANSDATE, MACHINEDATE, 'N' AS BMS, THEYEAR
FROM @.TABLENAME
WHERE ((TRANSTYPE = '20') AND (GL = '92200')) OR
((TRANSTYPE = '21') AND (GL IN ('91100', '92100'))) OR
((TRANSTYPE = '22') AND (GL IN ('13100', '12200'))) AND (BE IN (SELECT DISTINCT BE FROM REF_BUDGETENTITY WHERE FISCALYEAR = @.FISCALYEAR))
And the error message is :

Must declare the table variable "@.TABLENAME".

thanks,

Murthy here

|||

Hi there,

I do not have to create any temp tables. Actually the tables already exist with sql server. based on 1 field from 1 table I have to select data from 1 or more different tables.

The select statement has to different for different tables so I am generating the table names which already exist on the fly.

Hope I am clear.

thanks,

Murthy here

|||

I think you will need to use sp_execsql to execute the dynamic sql you build that way

Keep in mind that many people discourage the use of dynamic sql because of security and performance

1. SQL injection attacks

2. Having to compile the statement every time

3. YOu need to grant permissions on the underlying tables, not on the proc

|||

If you are trying to do something like this:

Declare @.table varchar(20)
set @.table = Northwind.dbo.Products

Select * from @.table

You can't. It's looking for a table afterfromso @.table would have to be a table variable.

To do what you need, asdbland07666 mentioned, you need to use dynamic sql. Caveat Emptor. Here is a link that might help you:

http://www.nigelrivett.net/SQLTsql/TableNameAsVariable.html

dynamic SQL Create Table

The following dynamic SQL script works for creating a table on the fly but if I change the select @.tmpTblNm = 'tmpABC' to select @.tmpTblNm = '#tmpABC'
it will not create the temp table. Can anyone help on creating a temp table dynamiclly?

declare @.tmpTblNm varchar(40),
@.str varchar(1000)

select @.tmpTblNm = 'tmpABC'
select @.str = ''

-- Create a temp table to hold the current page of data
-- Add an ID column to track the current row
select @.str = 'Create Table '+ @.tmpTblNm +
' ( row_id int IDENTITY PRIMARY KEY,
customerID int,
customerName varchar(40),
address1 varchar(40),
city varchar(25) )'

exec (@.str)The problem is that it did exactly what you requested, just not what you wanted.

A temporary table lasts for the duration of its creator. For a script, that is as long as you have the session open (until you close the session). For a stored procedure, the duration is as long as the procedure runs. For dynamic SQL, the duration is the dynamic execute.

When you build a temp table dynamically, the table exists for as long as the statement runs. You can use a global (aka ##) table, but that brings its own problems to play. You can create a table within your script or procedure, but then it isn't very dynamic.

-PatP