Print Date and Time

I am currently using the global execution time in my reports, but I would
also like to show the user what time they view the report. I guess in
Crystal they distinguish between the two as "Data Date" which would be the
execution time and then "Print Date" which is the date and time the report
is viewed. Does any one know if there a way to show this is RS?
Thanks,
LisaPlease review .Net's DateTime functionality (Search on DateTime in MSDN).
In your case your could place, in a textbox, an expression like
=DateTime.Now.
You could also use the Now() function.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>|||Thank you!
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:O%23oBowEZEHA.712@.TK2MSFTNGP11.phx.gbl...
> Please review .Net's DateTime functionality (Search on DateTime in MSDN).
> In your case your could place, in a textbox, an expression like
> =DateTime.Now.
> You could also use the Now() function.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
> news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am currently using the global execution time in my reports, but I
would
> > also like to show the user what time they view the report. I guess in
> > Crystal they distinguish between the two as "Data Date" which would be
the
> > execution time and then "Print Date" which is the date and time the
report
> > is viewed. Does any one know if there a way to show this is RS?
> >
> > Thanks,
> >
> > Lisa
> >
> >
>|||You can try =now().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>

Print button not displayed in report viewer

When i design my report under BIDS(Business Intelligence development studio) i can view the print button of the report

when i use a webapplication i use the reportviewer control installed from C:\\Program files\Microsoft Sql Server\Reporting services...etc.

when i execute the application i dont see the print button displayed

when i checked with the property of reportviewer control the displayprintbutton was set to true but then too the button not present on the form

anything wrong with my installation

plz do help me

thanks

Rama

You are probably printing the report as a LocalReport which does not have a print button the ServerReport does though

|||We had the same issue and installed Reporting services SP2 and it worked.|||

sounded good to me but

I have version 9.00.3054 which I guess is sp2

true I can set the property but I don't get the button

print / export SSRS report from t-sql

Greetings,

I have a report that I would like to execute (export to PDF) from t-sql. I can view it interactively from the SSRS website; I can view it from my asp.net application; I can also create the PDF programmatically from the asp.net front-end, but I need to run a batch type query from a stored proc with a report(PDF) as the output...

For example, I want to create and send out a PDF based on a trigger....

How do I do this from t-sql?

Any ideas?

Thanks.

Dan

Web Service calls are only hard to implement in TSQL, close to say that this is not possible. Would could be done is to write an application and execute this application on a trigger base with passing the needed parameters to it..BUT..Keep in mind that triggers behave synchronously, which means that the execution and the lock will be hold until the processing of the trigger is finished. So you have to be careful if you really want to do this on a trigger base rather than starting a job e.g. every minute.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks Jens -- I think I'll just fall back to using the front end application to initiate it. Maybe MS will help out in the future....

Thanks again.

Primary Key in View

how to create view in sql server 2005 that recognize Identity And Primary key of base tableif you want to know about the metadata in SQL Server Server like Constraints / Tables etc. you can use the INFORMATION_Schema Views, in this case you would query the INFORMATION_SCHEMA.Constraints view.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

SELECT COLS.table_schema
,COLS.Table_name
,COLS.COLUMN_NAME
,cols.CONSTRAINT_NAME
,ac.IS_identity
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
JOIN sys.all_columns ac
ON OBJECT_NAME(ac.object_id) = COLS.table_name
AND ac.name=COLS.COLUMN_NAME
WHERE CONS.CONSTRAINT_TYPE LIKE 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

as i wrote this pretty quick and didnt test it fully.. using it is on your own risk ;)

Guldmann, platon.dk

primary key in aggregated view

Been pulling my hair out with this one for some time now ... hope someone out there can help :)

I have a database view which is an aggregated view of a number of tables. Trouble is I need to create what would effectively be a primary key for the view.

Can anyone suggest a sound way of doing this other than moving to Oracle :)If you already have an alternative, - may god help you (it won't be my god though ;))

Why do you need a PK on a view? Using it instead of a table?|||A view (should) "inherit" all the contraints that are expressed in the uderlying query. Unfortuneately, because SQL allows duplicates, the result set of the view cannot be guareented to contain a key.

As a possible work around, look at Indexed views if you have SQL2K.. you may be able to add a unique index to the view if it conforms...|||Yeah, why do you need a primary key on your view?

Truth is, I hardly ever use views any more. I find that either table variables, temporary tables, or table functions are more effective.|||The view aggregates a number of child tables which contain amounts by currency etc

I also have a second view which is effectively a breakdown of the contents of the aggregation in the first view.

Because the second view is a child of the first, I needed a consistent primary key to link them together.

I solved it by creating a function that took the MIN primary key of the instances that would be in the breakdown view and passed this back to the first view.

It works a treat, just not sure how it will perform yet :)

Thanks for your help|||THAT's what I was waiting for!

Primary key

Is it possible to define a primary key in a view? My View is a 5 tables.
Table 1 holds a primary key that is used as foreign key in the other 4.
It is a view i have made on an SQL 2000 server
best regards
Trond>> Is it possible to define a primary key in a view? <<
No. A VIEW is defined as a virtual table with a SELECT statement. You
can make the rows in the VIEW unique by proper coding. You can modify
it with an INSTEAD OF trigger that changes the underlying base tables.

Previous Row Calculations (sql server 2000)

if anyone know a way that you can look up a value from the previous row in a View to do a calculation on (in sql server 2000)
for example:
expr1=PreviousRow.Expr1/30 + expr2 - expr3 =100

so the next row is

expr1=100/30 + expr2 - expr3 =300

so the third row is

expr1=300/30 + expr2 - expr3 =100
or tell me if it not possible please

and special thanks to Umachandar Jayachandran - MS for help.

Hi,

Im not quite sure if this is possible in a view. But you can do this in a stored proc. You can use a cursor and manually traverse your records...

cheers,

Paul June A. Domag

|||Thank you my frind for your help but I need give me some examples of the crusors to understand that...

if you can.....
|||

Hi,

Here's a sample in transact sql:

-- Declare the variables to store the values returned by FETCH.
DECLARE @.au_lname varchar(40), @.au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- here is where you would do your calculations

-- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @.au_fname + ' ' + @.au_lname

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

BTW, this would be placed inside a stored proc...

cheers,

Paul June A. Domag

|||

Bear with me here, as the info is worth it!!!

do you really need a table for this?

Ahhh, memories of my analysis classes!!!

This is what is called a Discrete Dynamic Equation (also called a Difference equation as oppposed to a differential equation) ; specifically, a Discrete first-order Affine Dynamical System

The form is:

A_n = R A_n-1 + B

The general solution for which is:
A_k=CR^k + B/(1-R)

The particular solution for n=0 is

A₀=CR⁰+ B/(1-R)=C+B/(1-R)

Solve for C

C=A₀-B(1-R)

Therefore, the particular solution for the dynamical system is:

A_k=R^k(A₀-B/(1-R))+B/(1-R)
in sql:

Create function AffineDynamic(@.A0 real, @.R decimal(16,16), @.B real, @.k int) returns decimal(32,16)
as
begin
declare @.result decimal(32,16)
if @.k = 0
set @.result = @.A0
else
set @.result Power(@.R,@.k) * (@.A0 - (@.B * Power(1-@.R, -1))) + (@.B * Power(1-@.R, -1))
return @.result
end
usage:


-- a random expr1 for row 1
DECLARE @.initial real
set @.initial = 25

-- the rate: 1/30
DECLARE @.rate decimal(16,16)
set @.rate = 0.03333333

-- a random expr2
DECLARE @.expr2 real
set @.expr2 = 23

-- a random expr3
DECLARE @.expr3 real
set @.expr3 = 7

-- First row
select 0 Iter, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,0) Expr1
union
-- Second row
select 1, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,1)
union
-- Third row
select 2, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,2)
union
-- Fourth row
select 3, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,3)
union
-- Fifth row
select 4, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,4)
union
-- Sixth row
select 5, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,5)
union
-- Sixth row
select 6, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,6)


Yields the following:

Iter Expr1
0 25.0000000000000000
1 16.8333320617675780
2 16.5611095428466800
3 16.5520362854003910
4 16.5517330169677730
5 16.5517234802246090
6 16.5517234802246090

Now, note the affinity towards 16.5517234802246090. This is called a point of stability. Any iteration after 5 for A0=25, R=1/30, B= (23-7) = 16 yeilds this number.

To wit:

select 1000 Iter, dbo.AffineDynamic(25, 0.03333333, 16,1000) Expr1

Yields (as expected):

Iter Expr1
1000 16.5517234802246090

Reference:
Discrete Dynamical Modeling, Sandefur, Oxford University Press, 1993, ISBN 0-19-508438-1

|||

Another plain and practical alternative to cursors, and/or esoteric alternates that concentrate on remembering the previous values by storing them on a temporary table that is later listed as show in sample code in the "PreviousRowCalculation" SP.

create table nums(a int, b int)
insert into nums values (0,1)
insert into nums values (1,2)
insert into nums values (2,3)
insert into nums values (3,4)
insert into nums values (4,5)
go
-- drop procedure PreviousRowCalculation
create procedure PreviousRowCalculation
as
begin
set nocount on
create table #tmp (recno int identity, a int, b int, pv int)
insert into #tmp (a,b,pv)
select a,b,0 from nums order by a
declare @.t int,@.c int
set @.t = (select count(*) from #tmp)
set @.c = 1
while @.c <= @.t
begin
update #tmp
set pv = isnull((select pv from #tmp x where x.recno=@.c-1),0) -- expr1
+ (11 + b) - (10 + a) -- + expr2 - expr3
where recno=@.c
set @.c = @.c + 1
end
select * from #tmp
drop table #tmp
end
go

PreviousRowCalculation
go

|||I want to reiterate that this only works with dynamic equations of the form:
A_n = R A_n-1 + B

This is called a First-Order Homogeneous Dynamical Equation

Which is what your request is:

========================
B = expr2 - expr3
expr1_n= [(1/30) * expr1_n-1] + B
========================

This assumes that expr2 - expr3 are constant expressions and not variable values, such as columns in the table.

There are different solutions for other forms - First-Order Non-Homogenous Dynamical Equations, N-Order Homogeneous/Non-Homogenous Dynamical Equations and Systems of Dynamical Equations, just to name a few.

Google:
Logistic Equation
Markov Chains

The point I am trying to make, if there is a distinct causal relationship between values of sequential iterations of a formula, that is:

F(x)_n = G(F(x)_n-1)

There is often an dual formula H(x , k) such that

F_k(x) = H(F(x)₀ , k)

where k is the iteration you want to calculate and F(x)₀ is the intial value, thereby eliminating the need to iterate all the values.

The trick is being able to recognize the form of the system (is it solvable?) and then applying the proper dual.

Ah, the difference between a degree and a certificate!|||

or, using ed's nums table and exprnext = exprPrev +(11 +b) - (10+a)

select cast(null as int) pv, identity(int) id, * into #temp from nums

update #temp set pv = 0 where id = 1

loophere:
update #temp
set pv = calc
from #temp, ( select top 1 curr.ID, prev.pv + (11 + prev.b) - (10+prev.a) calc
from #temp curr inner join #temp prev on curr.id = prev.ID+1 where curr.pv is null ) thecalc
where #temp.ID = thecalc.ID
if @.@.ROWCOUNT <> 0 goto loophere

select * from #temp
drop table #temp

|||sweet.

Just a last note, on Blair last alternate solution is the need to evaluate the expression for the first row alone (if actually needed) since the loop does require to start in "recno 1" + 1 therefore missing its evaluation at row 1.|||the second line is the initial expr1:

update #temp set pv = 0 where id = 1

Preview is better than web browser in this case

I like the functionality in the preview - that if all the parameters
have a valid value... it starts the query without pressing 'View
Report'.
Whereas in a published report in a browser, I always have to press the
'view report'.
In some cases one is better than the other - specially if the query is
small... but navigation needs to be faster.
I have repeatedly asked this question - without any answers.
But if it works in Preview... why not in the browser...'
Seems like there is setting... but no documentation...
I think we need both the options.Yes, we have struggled to answer this questions ourselves. We keep coming
back to this and will take your comments into consideration for sure. To
explain:
The problem is that on the server, there is really no way to stop the
execution of the query that was spawned by requesting a particular report.
So if you have all the defaults specified, then (for a live report) the
server will immediately kick off a report execution. This will inturn
execute the query. That query could take some time to run. Now, if the
user wants to change the values from default before the report finishes
executing, s/he might just click stop on the browser, change the paramters,
and click view report. Now due to how the browser works, the server
doesn't know the user has changed their minds. To the server it is just
another request. So now you have 2 report executions occuring for the same
user. The orignal request eventually will be killed on the report server
(once we realize the connection is closed/stale) but that still leaves the
long running query on the DB server.
So in effect by automatically running the report with the default parameters
you're increasing load significantly on the server. Now imagine how this
affects the system when you scale to 1000's of users.
In the designer, you don't have the same problem so you're more free to do
increase responsivness.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407221045.28cb83ac@.posting.google.com...
>I like the functionality in the preview - that if all the parameters
> have a valid value... it starts the query without pressing 'View
> Report'.
> Whereas in a published report in a browser, I always have to press the
> 'view report'.
> In some cases one is better than the other - specially if the query is
> small... but navigation needs to be faster.
> I have repeatedly asked this question - without any answers.
> But if it works in Preview... why not in the browser...'
> Seems like there is setting... but no documentation...
> I think we need both the options.|||I would say - give those controls to the designer.
Let him/her decide -
1. On Default parameters - should execute immediately or not
2. On Change selection - should execute immediately or not
- parameter wise control. I.e. decide whether a particular
parameter should execute the report immediately or not
Provide with a control with each parameter.
Cascading parameters should have that option also - Specially if
selection of Parameter 1 leads to default of Parameter 2.
- In which case - first of all the "dependency" should be explicitly
defined.
- And then - whether to execute the report immediately or not - could
also be a designers choice.
Just some thoughts...

preventing update thru view

Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs
Hi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs
|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:

>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs

preventing update thru view

Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgsHi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:

>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.
4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs

preventing update thru view

Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgsHi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
>.
>|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
> >Hi ,
> >
> > A view can actually update the data of a table.
> >
> > Is there any way for me to create a read-only view ?
> >
> >thks & rdgs
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs

Preventing access to DataBase & encrypt meta-data and data of tables

How can I encrypt Sql Server table meta-data (table name,column
name,releations and so on...)
I can encrypt Sql server stored-procedure,view... but I can not enrypt
the meta-data of data table! I do not want Sql Administrator to read
myDatabase's tables,releations,column names,actually I do not want to
open any tables
How can I do that?
Thanks !Oney
I am afraid you cannot do that.
But permit me ask you , why do you need to hide your data from DBA?
"Oney" <ozcankanbur@.yahoo.com> wrote in message
news:cc5e237f.0405232338.551596e0@.posting.google.com...
> How can I encrypt Sql Server table meta-data (table name,column
> name,releations and so on...)
> I can encrypt Sql server stored-procedure,view... but I can not enrypt
> the meta-data of data table! I do not want Sql Administrator to read
> myDatabase's tables,releations,column names,actually I do not want to
> open any tables
> How can I do that?
> Thanks !|||Hi,
DBA is the person who secure and protect your database, so hiding
information from him is too difficult and not good.
As far as I know there is not tool to encrypt the Tables , constraints and
Indexes.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekjSISWQEHA.2248@.TK2MSFTNGP12.phx.gbl...
> Oney
> I am afraid you cannot do that.
> But permit me ask you , why do you need to hide your data from DBA?
> "Oney" <ozcankanbur@.yahoo.com> wrote in message
> news:cc5e237f.0405232338.551596e0@.posting.google.com...
> > How can I encrypt Sql Server table meta-data (table name,column
> > name,releations and so on...)
> >
> > I can encrypt Sql server stored-procedure,view... but I can not enrypt
> > the meta-data of data table! I do not want Sql Administrator to read
> > myDatabase's tables,releations,column names,actually I do not want to
> > open any tables
> >
> > How can I do that?
> >
> > Thanks !
>

Preventing access to DataBase & encrypt meta-data and data of tables

How can I encrypt Sql Server table meta-data (table name,column
name,releations and so on...)
I can encrypt Sql server stored-procedure,view... but I can not enrypt
the meta-data of data table! I do not want Sql Administrator to read
myDatabase's tables,releations,column names,actually I do not want to
open any tables
How can I do that?
Thanks !
Oney
I am afraid you cannot do that.
But permit me ask you , why do you need to hide your data from DBA?
"Oney" <ozcankanbur@.yahoo.com> wrote in message
news:cc5e237f.0405232338.551596e0@.posting.google.c om...
> How can I encrypt Sql Server table meta-data (table name,column
> name,releations and so on...)
> I can encrypt Sql server stored-procedure,view... but I can not enrypt
> the meta-data of data table! I do not want Sql Administrator to read
> myDatabase's tables,releations,column names,actually I do not want to
> open any tables
> How can I do that?
> Thanks !
|||Hi,
DBA is the person who secure and protect your database, so hiding
information from him is too difficult and not good.
As far as I know there is not tool to encrypt the Tables , constraints and
Indexes.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekjSISWQEHA.2248@.TK2MSFTNGP12.phx.gbl...
> Oney
> I am afraid you cannot do that.
> But permit me ask you , why do you need to hide your data from DBA?
> "Oney" <ozcankanbur@.yahoo.com> wrote in message
> news:cc5e237f.0405232338.551596e0@.posting.google.c om...
>