How to Refresh a Data Source View and autogenerate/Re-Deploy the corresponding Report Model

Hi everyone!,

Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.

We prefer to do this action through a Agent job ( a diff approach is also welcome).

Are there any stored procs out there?

Appreciate your time.

-PN

Can anybody please tell me if this is possible or not in the current version of SSRS or VS.

Appreciate your time guys.

|||This won't be easy but I think it is possible. There is no eventing that RS provides to help you with this. The DSV schema information gets embedded in the model definition (under the <Schema> element). So, you need to load the model defintion (GetModelDefinition() API), make the changes to the DSV schema, update the model, and save it back (SetModelDefinition). As far subscribing for notifications, you are on your own. Probably CLR DDL trigger will be your best joice to sink the table schema change event.|||

Hi Teo,

Thanks for your answer. I was thinking around the same lines but did not know the Methods to accomplish this task.

|||

Hi,

My project requirements goes the same way. I was trying to refresh the Report Model in the way mentioned by Teo. But the GetModelDefinition will fetch the report model SMDL.

How to get the DSV schema? Is there anything am missing out?

Did any one got the solution for the same?

Thanks.

|||Assuming a model on top of a relational database, the DSV schema gets embedded in the model definition (see <DataSourceView> element) upon deploy.|||Hi Teo,

Thanks for ur help. I tried in the same lines and was able to achieve my task.

I need to know whether it is recomended to change the xml definition of the Report Model.

Thanks

How to Refresh a Data Source View and autogenerate/Re-Deploy the corresponding Report Model

Hi everyone!,

Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.

We prefer to do this action through a Agent job ( a diff approach is also welcome).

Are there any stored procs out there?

Appreciate your time.

-PN

Can anybody please tell me if this is possible or not in the current version of SSRS or VS.

Appreciate your time guys.

|||This won't be easy but I think it is possible. There is no eventing that RS provides to help you with this. The DSV schema information gets embedded in the model definition (under the <Schema> element). So, you need to load the model defintion (GetModelDefinition() API), make the changes to the DSV schema, update the model, and save it back (SetModelDefinition). As far subscribing for notifications, you are on your own. Probably CLR DDL trigger will be your best joice to sink the table schema change event.|||

Hi Teo,

Thanks for your answer. I was thinking around the same lines but did not know the Methods to accomplish this task.

|||

Hi,

My project requirements goes the same way. I was trying to refresh the Report Model in the way mentioned by Teo. But the GetModelDefinition will fetch the report model SMDL.

How to get the DSV schema? Is there anything am missing out?

Did any one got the solution for the same?

Thanks.

|||Assuming a model on top of a relational database, the DSV schema gets embedded in the model definition (see <DataSourceView> element) upon deploy.|||Hi Teo,

Thanks for ur help. I tried in the same lines and was able to achieve my task.

I need to know whether it is recomended to change the xml definition of the Report Model.

Thanks

How to refer from a stored procedure to a table in another dat

I changed the view in my SP dynamically it helped. Thanks.Alexander Korol wrote:
> I changed the view in my SP dynamically it helped. Thanks.
Huh?
What view?
Oh! Are you saying you used dynamic sql?
Hopefully you are aware of and have taken precautions against sql injection
...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hello Bob,
I do not change or create databases dynamically I simply do not know
beforehand how many there is going to be databases, where they reside and ho
w
they are called. This is say maintanance stored procedure in a reporting
database (the one that collects data from many other databases) and YES I
care about SQL injection.
Thanks for your participation. Looks like you hate dynamically built sql. So
do I - but I am not the architect - just solving particular task :)
Moreover if you could advise any better architecture I will greately
appreciate. I have several production databases of similar structure and one
reporting database where data ready to be reported is kept. Data
preprocessing is so heavy that I can not query data directly from production
databases. So in reporting database SPs are to be run daily against differen
t
production databases and their number depends on a customer. That is why
neither IFs can be used neither fixed queried VIEWs. Customer wants to do as
less actions as possible.
Actually to avoid SQL injection i check that database name is a single word.
Could you advise any other solution?
Thanks again for your help.
"Bob Barrows [MVP]" wrote:

> Alexander Korol wrote:
> Huh?
> What view?
> Oh! Are you saying you used dynamic sql?
> Hopefully you are aware of and have taken precautions against sql injectio
n
> ...
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

How to read SQL file ?

Hello,
I recived SQL file (riped database). Can you recommand me any software how
to view this database from SQL file ? Currentlly I don't have any database
software
On May 3, 4:31 pm, "bildos" <bil...@.gmail.com> wrote:
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
I think ripped means zipped. You need to unzip the file and I think
restore .
You should ask your provider , whether this is a backup file . In
that case you have to install SQL Server ( may be Express) and restore
the database . Check with your provider about the details of the file
|||If it is a file with a .sql suffix then it is a collection of commands to
build a database in your system. This is text data so you can look at it
but it won't be very useful until you use it to build a database in SQL
Server.
This would be equivalent to having the source code to Doom - possibly very
interesting but hard to play without a compiler to convert it into a
program.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"bildos" <bildos@.gmail.com> wrote in message
news:OTUuXaXjHHA.3700@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
>
|||Hello,
If it is a SQL Server MDF or LDF file or Backup file you may need to have a
SQL Server instance to attach /restore and read the contents.
First try to understand the format of file(s) and go from there. If it is a
normal .SQL file you can open the file in notepad and read.
THanks
Hari
"bildos" <bildos@.gmail.com> wrote in message
news:OTUuXaXjHHA.3700@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
>

How to read SQL file ?

Hello,
I recived SQL file (riped database). Can you recommand me any software how
to view this database from SQL file ? Currentlly I don't have any database
softwareOn May 3, 4:31 pm, "bildos" <bil...@.gmail.com> wrote:
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
I think ripped means zipped. You need to unzip the file and I think
restore .
You should ask your provider , whether this is a backup file . In
that case you have to install SQL Server ( may be Express) and restore
the database . Check with your provider about the details of the file|||If it is a file with a .sql suffix then it is a collection of commands to
build a database in your system. This is text data so you can look at it
but it won't be very useful until you use it to build a database in SQL
Server.
This would be equivalent to having the source code to Doom - possibly very
interesting but hard to play without a compiler to convert it into a
program.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"bildos" <bildos@.gmail.com> wrote in message
news:OTUuXaXjHHA.3700@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
>|||Hello,
If it is a SQL Server MDF or LDF file or Backup file you may need to have a
SQL Server instance to attach /restore and read the contents.
First try to understand the format of file(s) and go from there. If it is a
normal .SQL file you can open the file in notepad and read.
THanks
Hari
"bildos" <bildos@.gmail.com> wrote in message
news:OTUuXaXjHHA.3700@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
>

How to read SQL file ?

Hello,
I recived SQL file (riped database). Can you recommand me any software how
to view this database from SQL file ? Currentlly I don't have any database
softwareOn May 3, 4:31 pm, "bildos" <bil...@.gmail.com> wrote:
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
I think ripped means zipped. You need to unzip the file and I think
restore .
You should ask your provider , whether this is a backup file . In
that case you have to install SQL Server ( may be Express) and restore
the database . Check with your provider about the details of the file|||If it is a file with a .sql suffix then it is a collection of commands to
build a database in your system. This is text data so you can look at it
but it won't be very useful until you use it to build a database in SQL
Server.
This would be equivalent to having the source code to Doom - possibly very
interesting but hard to play without a compiler to convert it into a
program.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"bildos" <bildos@.gmail.com> wrote in message
news:OTUuXaXjHHA.3700@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
>|||Hello,
If it is a SQL Server MDF or LDF file or Backup file you may need to have a
SQL Server instance to attach /restore and read the contents.
First try to understand the format of file(s) and go from there. If it is a
normal .SQL file you can open the file in notepad and read.
THanks
Hari
"bildos" <bildos@.gmail.com> wrote in message
news:OTUuXaXjHHA.3700@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I recived SQL file (riped database). Can you recommand me any software how
> to view this database from SQL file ? Currentlly I don't have any database
> software
>

How to read or view job information...

I am attempting to view or read job information in SQL
Server 2000. I do not have 'sa' rights to view job
information.
Please advise...
Thanks,
BrettBrett,
SQL Agent jobs are viewable to the job owner (which could be you) and by
sysadmins. If you need to check the contents of a job and you have a
responsive DBA, he could script out its contents for you. (All questions of
security and process within your organization are outside the scope of this
post.)
There is a trick that works with SQL Server 7.0 and 2000, which is to assign
a user in msdb to the TargetServersRole role. This allows anyone in this
role to see the contents of jobs, look at their schedules, and so forth.
However, anyone who is in TargetServersRole can no longer create a job of
his own.
Russell Fields
"Brett" <brett_davs@.ameriquest.net> wrote in message
news:098301c377c5$29b0ef70$a001280a@.phx.gbl...
> I am attempting to view or read job information in SQL
> Server 2000. I do not have 'sa' rights to view job
> information.
> Please advise...
> Thanks,
> Brett
>

How to Read a database Transaction Log?

Is therea built in way to read a database transaction log, so you can
actually view it, and what it is doing?
John Barr wrote:
> Is therea built in way to read a database transaction log, so you can
> actually view it, and what it is doing?
There are some undocumented tricks such as DBCC LOG and fn_dblog.
Google for details of those.
There is also:
http://www.lumigent.com/products/le_sql.html
David Portas
SQL Server MVP

How to Read a database Transaction Log?

Is therea built in way to read a database transaction log, so you can
actually view it, and what it is doing?John Barr wrote:
> Is therea built in way to read a database transaction log, so you can
> actually view it, and what it is doing?
There are some undocumented tricks such as DBCC LOG and fn_dblog.
Google for details of those.
There is also:
http://www.lumigent.com/products/le_sql.html
--
David Portas
SQL Server MVP
--

How to Read a database Transaction Log?

Is therea built in way to read a database transaction log, so you can
actually view it, and what it is doing?John Barr wrote:
> Is therea built in way to read a database transaction log, so you can
> actually view it, and what it is doing?
There are some undocumented tricks such as DBCC LOG and fn_dblog.
Google for details of those.
There is also:
http://www.lumigent.com/products/le_sql.html
David Portas
SQL Server MVP
--

How to query the date whose value is in text format

Hello,

I get a problem in developing the company report. The column of "BATCH" in Oracle view is shared by all departments that need the different entries, so we have to use "Text" data type for this column in order meet all departments needs.

One department uses it as a sample date column. The text entry value is in the format of mm/dd/yyyy.

If I do not do the data converting, when I set the query: Where SampleDate between '01/01/2005' and '01/31/2005', the outcome not only include the January data of 2005 but also include the January data from all privious years, because system evaluates the data in text format rather than date format.

What I have done is to use Oracle To_Date(SampleDate,'mm/dd/yyyy') function to convert the text format to Oracle date format in report dataset.

I have two problems:

1) How to take out the wrong format enties? for example, user enters 1102/2005 instead of 11/02/2005. I find that the wrong formating entries cause the failure of data retrival. Micro T-SQL has a function of IsDate() to flag out the entries that are not in date formating, but it does not work here, I guess the reason may be our data source is Oracle.

2) Even the data is conveted, it still does not work propertly.

For example, I write:

Where To_Date(Batch,'mm/dd/yyyy') between '01/01/2005' and '01/31/2005'

it does not work

I write: Where To_Date(Batch,'mm/dd/yyyy') between To_date('01/01/2005','mm/dd/yyyy') and To_date('01/31/2005','mm/dd,yyyy')

it still does not work.

How to resolve these issues.

Thanks,

Zixing

For problem #1, if you are using Oracle 10g you can use a regular expression in your query so you only get valid matching dates. If you aren't using 10g, then you could use the like operator with some wildcards.

For problem #2, try running your query in an Oracle client first just to make sure that you have the query syntax right. I am not familiar with Oracle tools for performing queries but I am thinking something like MSSQL Query Analyzer.

How to query a view from the master DB?

Is there a way to access your views in x database from the master database?

I'm getting this error

Invalid object name 'v_StatisticsScalars'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.Use the fully qualified path <database>.<owner>.<view
Or you can use "use"|||cant use USE in stored procedures can you?

im using system stored procedures.....and i thought the context stayed at the database calling the system stored procedure? am i wrong?|||i really dont want to use x.y.z because im using these stored procedures for a few different sites. it would be good if i could add the view to the master database. can that be done? i tried....but then it says my tables dont exist. :/ -so now im back at square1|||Now I'm confused too. I understand that you're relucant to use the fully qualified path. I don't understand what you mean by "I'm using system stored procedures". You can create a view to access other database table but we're back to using the fully qualified path again! If the database was on a different server (or server instance) you could setup a linked server. Apart from that I think you're stuck. Well you could construct some dynamic sql and exec that to get around the 'different sites' issue. In fact just have a util prog that generates the procs/views based upon the database names you want to use.