alter view question

how do I find out when was the last time view got modified.You cannot do that in SQL Server 2000
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
You can't (unless SQL 2005 handles this).
You only have the date it was created.
You can:
drop the view and re-create it when you do a modification
add comments to the view about changes
keep the information in a table
use a third party code source tool|||SQL Server 2000 does not store this information. You can garner this
information from a trace, if you want to leave a lightweight one running; or
from 3rd party tools such as Lumigent's Entegra ("who did what to which data
when?" is their catch-slogan).
If you are using SQL Server 2005,
SELECT modify_date FROM sys.views WHERE name='view_name'
(And in 2005 if you want more information, such as who modified it, what app
they used, etc. then you can set up DDL triggers.)
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||I was wondering if I could use trigger to audit the modified date of view,
as trigger is allowed on a view.
"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server 2000 does not store this information. You can garner this
> information from a trace, if you want to leave a lightweight one running;
or
> from 3rd party tools such as Lumigent's Entegra ("who did what to which da
ta
> when?" is their catch-slogan).
> If you are using SQL Server 2005,
> SELECT modify_date FROM sys.views WHERE name='view_name'
> (And in 2005 if you want more information, such as who modified it, what a
pp
> they used, etc. then you can set up DDL triggers.)
>
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
>
>|||>I was wondering if I could use trigger to audit the modified date of view,
> as trigger is allowed on a view.
That will fire when the actual DATA changes, not the DEFINITION.|||i don't have sql 2005 installed, but out of curiousity, to find out other
objects' modification date, do you just go to: sys.tables, sys.triggers,
sys.functions, etc?
if I use following query,Can I find out when procedure is last altered in
sql 2005?
I know in sql 2000, last_altered is "fake" column ( data is same as created
date).
but I hope sql 2005 is not like that again.
select routine_name, last_altered from information_schema.routines
"Aaron Bertrand [SQL Server MVP]" wrote:

> That will fire when the actual DATA changes, not the DEFINITION.
>
>|||> I know in sql 2000, last_altered is "fake" column ( data is same as
> created
> date).
> but I hope sql 2005 is not like that again.
> select routine_name, last_altered from information_schema.routines
LAST_ALTERED is correct and accurate in SQL Server 2005, however I urge you
to use the catalog views instead.|||I'm not sure if I understand the original request fully, but in 2005 you can
do DDL triggers that will fire off create, alter, drop statements on all
sorts of meta-data type events. Things like CREATE_TRIGGER (triggers on
your triggers?), ALTER_TABLE, etc. Just do search at the MSDN website for
DDL triggers.
Clint
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ecLMVN2%23FHA.2040@.TK2MSFTNGP14.phx.gbl...
> That will fire when the actual DATA changes, not the DEFINITION.
>|||> I'm not sure if I understand the original request fully, but in 2005 you
> can do DDL triggers that will fire off create, alter, drop statements on
> all sorts of meta-data type events.
Yes, I suggested that, however I am not sure the user is using SQL Server
2005.sql

alter view permissions

Hello,
pls help, how can I remove alter view permission for
user who is member of ddladmin db role, select and update
queries shoud remain permited. The user should be able to
edit all other views, stored procedures etc, except this
one view.
thank you for helpHi
Add him to db_datawriter database role but remove him from ddladmin db role
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
> Hello,
> pls help, how can I remove alter view permission for
> user who is member of ddladmin db role, select and update
> queries shoud remain permited. The user should be able to
> edit all other views, stored procedures etc, except this
> one view.
> thank you for help|||Thanks, I have not mentioned about ability to also create
new views, stored procedures etc. This actions are not
permited with db_datawriter role. Any ideas?

>--Original Message--
>Hi
>Add him to db_datawriter database role but remove him
from ddladmin db role
>
>"Gabriel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
update[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
>
>.
>|||One method is to grant CREATE permissions to the user. This will allow the
user to create/alter/drop objects that they own but not objects owned by
other users. You can then change ownership of the view in question to a
different user so that it can't be modified. Other users will need to
owner-qualify object names.
Another approach, which IMHO is better, is to employ a separate database for
those objects you don't want to user to modify. The user can then
db_ddladmin role member in your current database but not in the database
containing the sensitive objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:0bbe01c52fa6$ff2468e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks, I have not mentioned about ability to also create
> new views, stored procedures etc. This actions are not
> permited with db_datawriter role. Any ideas?
>
> from ddladmin db role
> message
> update
> to
> this

Alter View permission?

MSDN says the following at
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_aa-az_2gtz.asp:
"ALTER VIEW permissions default to members of the db_owner and
db_ddladmin fixed database roles, and to the view owner. These
permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with
SELECT permission on the tables, views, and table-valued functions being
referenced in the view, ..."
The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
statement type to grant permissions on. Trying to GRANT ALTER VIEW
doesn't work. How can I give a user ALTER VIEW permission? Does "not
transferable" mean I can't?
Thanks.
David WalkerYou can add the user to the db_owner or db_ddladmin fixed database roles.
AMB
"DWalker" wrote:

> MSDN says the following at
> http://msdn.microsoft.com/library/d...rl=/library/en-
> us/tsqlref/ts_aa-az_2gtz.asp:
> "ALTER VIEW permissions default to members of the db_owner and
> db_ddladmin fixed database roles, and to the view owner. These
> permissions are not transferable.
> To alter a view, the user must have ALTER VIEW permission along with
> SELECT permission on the tables, views, and table-valued functions being
> referenced in the view, ..."
> The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
> statement type to grant permissions on. Trying to GRANT ALTER VIEW
> doesn't work. How can I give a user ALTER VIEW permission? Does "not
> transferable" mean I can't?
> Thanks.
> David Walker
>|||I ran across the DDLAdmin role, but BOL makes it sound like you can
grant ALTER VIEW permissions. Maybe I'm misreading it -- I suppose
those permissions are only given to members of db_owner and db_ddladmin,
and they can't be granted to anyone else.
The phrase "ALTER VIEW permissions default to members of the db_owner
and db_ddladmin fixed database roles" should have "default to" replaced
by "are restricted to" if that's the case.
It would be nice to be able to grant ALTER VIEW permission on one view
to one user or role.
Thanks, AlejandroMesa.
David Walker
"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:668F79B3-3335-4BC3-860F-67F467C12CEF@.microsoft.com:

> You can add the user to the db_owner or db_ddladmin fixed database
> roles.
>
> AMB
> "DWalker" wrote:
>
>|||You cannot give a user (that is not member of db_owner and db_ddladmin
fixed dabase roles) the permission to alter a specific view (that he
does not own). This is exactly what "not transferable" means (at least,
this is what it means to me).
As I see it, you have the following alternatives:
a) make the user a member of db_ddladmin role: this will enable him to
make any modification to the objects in the database, including
creating and deleting other objects (tables, views, procedures, etc)
b) grant the user the "CREATE VIEW" permission; the views that are
created by him will be will be owned by him, and so he will be able to
modify them
c) create the views in his name, by prefixing them with his user name
instead of "dbo" (without granting him the "CREATE VIEW" permission).
He will be able to modify those views (and to delete them), but he
won't be able to create new views (or other objects).
The problem with the b) and c) alternatives is that only that user will
be able to access those views by specifying only the view's name (the
other users must prefix the view's name with the user name).
Razvan|||It would be NICE if I could grant a user permission to alter a specific
view, but we'll take what we can get...
I'll probably add the user to the ddladmin role, although that's more
power than I would like to give. But the other choices aren't great
either. Thanks.
David
"Razvan Socol" <rsocol@.gmail.com> wrote in
news:1112639463.986674.297650@.f14g2000cwb.googlegroups.com:

> You cannot give a user (that is not member of db_owner and db_ddladmin
> fixed dabase roles) the permission to alter a specific view (that he
> does not own). This is exactly what "not transferable" means (at
> least, this is what it means to me).
> As I see it, you have the following alternatives:
> a) make the user a member of db_ddladmin role: this will enable him to
> make any modification to the objects in the database, including
> creating and deleting other objects (tables, views, procedures, etc)
> b) grant the user the "CREATE VIEW" permission; the views that are
> created by him will be will be owned by him, and so he will be able to
> modify them
> c) create the views in his name, by prefixing them with his user name
> instead of "dbo" (without granting him the "CREATE VIEW" permission).
> He will be able to modify those views (and to delete them), but he
> won't be able to create new views (or other objects).
> The problem with the b) and c) alternatives is that only that user
> will be able to access those views by specifying only the view's name
> (the other users must prefix the view's name with the user name).
> Razvan
>

Alter View Hangs - Merge Replication SQL 2005

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

If this view is the only article in the publication, then it is a known issue.

Add a dummy table to the publication and your alter should succeed.

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that table
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.
> Thanks,
> Andre
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>
Don't use SELECT * in views. If you want an alias for a table then use a
synonym instead.
sp_refreshview updates the view metadata but if you avoid SELECT * then
you'll never need to use it!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that tabl
e
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.

> Thanks,
> Andre
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>
Don't use SELECT * in views. If you want an alias for a table then use a
synonym instead.
sp_refreshview updates the view metadata but if you avoid SELECT * then
you'll never need to use it!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that table
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.

> Thanks,
> Andre
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

Alter TABLE column name?

Hi,
I need alter column name of a TRABLE and VIEW... HOw I do this? Thankssp_rename
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message news:unTHkVznFHA.1048@.tk2msftngp13.phx.gbl
..
> Hi,
> I need alter column name of a TRABLE and VIEW... HOw I do this? Thanks
>|||BOl is your friend:
B. Rename a column
This example renames the contact title column in the customers table to titl
e.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Tibor Karaszi" wrote:

> sp_rename
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message news:unTHkVznFHA.1048@.tk2
msftngp13.phx.gbl...
>

Alter more than one view

Hi All,

I am new to this group and this is my first doubt i am facing at
present.

I am doing data migration. In this sequence i need to alter few views.
Alter in the sense, inside the existing query of view i want to include
one more column.

I want to do it inside one single script. If i run the script all views
should get updated.

Any help on this will be greatful.

my mail id is siddu.roy@.gmail.com.

Thanks in advanceYou can include GO batch delimiter following each CREATE VIEW statement.
Tools like OSQL, SQLCMD, SSMS and Query Analyzer send the preceding batch of
SQL statements whenever a GO is encountered.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Siddu" <siddu.roy@.gmail.comwrote in message
news:1167892621.237538.321780@.31g2000cwt.googlegro ups.com...

Quote:

Originally Posted by

Hi All,
>
I am new to this group and this is my first doubt i am facing at
present.
>
>
I am doing data migration. In this sequence i need to alter few views.
Alter in the sense, inside the existing query of view i want to include
one more column.
>
I want to do it inside one single script. If i run the script all views
should get updated.
>
Any help on this will be greatful.
>
>
my mail id is siddu.roy@.gmail.com.
>
Thanks in advance
>

|||SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.

That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.|||--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

BEGIN TRANSACTION
go
CREATE VIEW aaa
AS
SELECT 1 n
go
SELECT n FROM aaa
/*
n
----
1

(1 row(s) affected)
*/
go
CREATE VIEW aab
AS
SELECT n FROM aaa
go
SELECT n FROM aab
/*
n
----
1

(1 row(s) affected)
*/
go
ROLLBACK
go
SELECT n FROM aaa
/*
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'aaa'.
*/
go
DROP VIEW aaa
DROP VIEW aab
/*
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'aaa', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the view 'aab', because it does not exist in the system
catalog.
*/

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Alex Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

>SQL Server is weird on this, but each VIEW statement has to be in a
>batch by itself. The reason is that VIEWs can be built on VIEWs, so
>you need to commit the first VIEW to do this.
>>

>
Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:

CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview

would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.

--
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|||--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.

Hi Joe,

Since we're picking on your answer here, can I also point out that GO
is a keyword for query analyzer (by default) and for the command line
tools. It is *not* a keyword for SQL Server, and is never sent to the
server.

This becomes obvious if ever you try to comment out a batch of code
that includes GOs. Because Comments are intepreted by SQL Server, but
the GOs are interpreted by the tool, you'll get error messages galore
(unterminated comments, unexpected * found, etc), plus whatever is
batched within the GOs within the commented out block still get
executed.

Damien|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Alex Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

>
Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:
>
CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview
>
would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.
>
>
--
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

Yeah, right, his post makes more sence if one replaces 'commit' with
'submit'.

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||
On Jan 4, 3:35 pm, "--CELKO--" <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead.

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

Jamie.

--|||onedaywhen (jamiecollins@.xsmail.com) writes:

Quote:

Originally Posted by

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

And still be the only.

(And I would suggest that ; is not a statement terminator in T-SQL - It's
statement initiator.)

--
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|||On Fri, 5 Jan 2007 23:13:00 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

>onedaywhen (jamiecollins@.xsmail.com) writes:

Quote:

Originally Posted by

>FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
>it must still be "the first statement in a query batch".

>
>And still be the only.
>
>(And I would suggest that ; is not a statement terminator in T-SQL - It's
>statement initiator.)

Hi Erland,

I would have to disagree with that suggestion. The ; is statement
terminator in ANSI, and has been the (optional) statement terminator in
T-SQL since at least SQL Server 2000 (but I think it was allowed in
earlier versions as well). The fact that *some* statements now require
the preceding statement to be terminated doesn't change it into a
statement initiator.

Check out the location of the ; in the syntax diagrams in Books Online.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

ALTER DATABASE and schema bound views.

Hello, everyone!
The question is: Can I alter the collation of a database
inside which there is a schema bound view, under SQL
Server 2000?
If you could help me with it, I'd ve extremely thankful.
We've got a product which is commercialized
internationally.
In order to do so, we have a basic database, and we alter
its collation to fit the target market.
Lately, we added a materialized view, and the ALTER
DATABASE COLLATE... command stopped working.
This is part of the script:
SELECT DATABASEPROPERTYEX('db', 'Collation') --
'SQL_Latin1_General_CP1255_CI_AS'
GO
DROP VIEW [DBO].[MATERIAL_VIEW_001]
GO
CREATE VIEW [DBO].[MATERIAL_VIEW_001]
WITH SCHEMABINDING
AS
SELECT DV.[DEPARTMENT], -- char(10)
DV.[FILENUM], -- tinyint
DV.[FIELDNUM], -- smallint
DV.[FROM_DATE], -- datetime
DV.[VALUE], -- varchar(1024), usually <
20.
EI.[DEP_INF] -- tinyint
FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
ON EI.FILENUM = DV.FILENUM -- tinyint
AND EI.FIELDNUM = DV.FIELDNUM -- smallint
WHERE EI.DEP_INF > 0 -- tinyint
GO
CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
ON DBO.[MATERIAL_VIEW_001]
( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
[DEP_INF], [VALUE] )
GO
-- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
doesn't help.
ALTER DATABASE DB COLLATE French_CI_AS
/*
Server: Msg 5075, Level 16, State 1, Line 1
The object 'MATERIAL_VIEW_001' is dependent on database
collation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of
database 'db' cannot be set to French_CI_AS.
*/
Can you help me with this?
Thanks in advance,
PabloHi
You don't mention if you change the column collations? e.g
http://tinyurl.com/91sg
I would also expect you to check what collation the system is set to:
Select SERVERPROPERTY(N'Collation')
I would expect the way around this is to drop and re-create the view after
you have changed the collation.
John
"Pablo Aliskevicius" wrote:

> Hello, everyone!
> The question is: Can I alter the collation of a database
> inside which there is a schema bound view, under SQL
> Server 2000?
> If you could help me with it, I'd ve extremely thankful.
> We've got a product which is commercialized
> internationally.
> In order to do so, we have a basic database, and we alter
> its collation to fit the target market.
> Lately, we added a materialized view, and the ALTER
> DATABASE COLLATE... command stopped working.
> This is part of the script:
> SELECT DATABASEPROPERTYEX('db', 'Collation') --
> 'SQL_Latin1_General_CP1255_CI_AS'
> GO
> DROP VIEW [DBO].[MATERIAL_VIEW_001]
> GO
> CREATE VIEW [DBO].[MATERIAL_VIEW_001]
> WITH SCHEMABINDING
> AS
> SELECT DV.[DEPARTMENT], -- char(10)
> DV.[FILENUM], -- tinyint
> DV.[FIELDNUM], -- smallint
> DV.[FROM_DATE], -- datetime
> DV.[VALUE], -- varchar(1024), usually <
> 20.
> EI.[DEP_INF] -- tinyint
> FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
> JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
> ON EI.FILENUM = DV.FILENUM -- tinyint
> AND EI.FIELDNUM = DV.FIELDNUM -- smallint
> WHERE EI.DEP_INF > 0 -- tinyint
> GO
> CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
> ON DBO.[MATERIAL_VIEW_001]
> ( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
> [DEP_INF], [VALUE] )
> GO
> -- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
> doesn't help.
> ALTER DATABASE DB COLLATE French_CI_AS
> /*
> Server: Msg 5075, Level 16, State 1, Line 1
> The object 'MATERIAL_VIEW_001' is dependent on database
> collation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of
> database 'db' cannot be set to French_CI_AS.
> */
> Can you help me with this?
>
> Thanks in advance,
> Pablo
>|||Thank you, John, for your answer.
Maybe I wasn't clear enough: the application I'm
supporting has dozens of installations in at least ten
countries (that I know of) spanning three continents (or
four, if you count South America and North America as
two). Two more countries will be added in the next few
months. As a result, the server collation can be just
about any.
The software works on top of SQL Server. Since the
software is alive, the database keeps changing: fields
are added to existing tables, new tables and procedures
are added from time to time, new views appear from time
to time. We have already dozens of tables, and over 300
procedures.
As a result, an automatic upgrade program was written,
which compares the production database at the client's
site, with a 'last model' database. This 'last model'
exists in one place only, with one collation only.
In order to compare the databases, the 'model' database
must assume the 'target' database's collation.
Since there are a LOT of objects, dropping and recreating
objects can be done only as a last resource. A way to
execute ALTER DATABASE even when a schema-bound view
exists would be the best possible solution. After that, I
run a script quite like the one described in the URL you
mention - but the DATABASE_DEFAULT is UNKNOWN until run
time.
Thanks again,
Pablo.

>--Original Message--
>Hi
>You don't mention if you change the column collations?
e.g
>http://tinyurl.com/91sg
>I would also expect you to check what collation the
system is set to:
>Select SERVERPROPERTY(N'Collation')
>I would expect the way around this is to drop and re-
create the view after
>you have changed the collation.
>John
>

almost newbie question:recover view

SQL 2000 sp2
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > SQL 2000 sp2
> > One of my users accidentally deleted one of her views. Using BackupExec
> 8.6
> > I restored the database to another SQL server. After restoring, I used
> the
> > Import DATA wizard to attempt to move the view from the recovered
database
> > to the "live" database.
> > I select "copy objects & data between SQL server dbs."
> > On the next page I uncheck Copy all objects and click on Select Objects.
> I
> > then use the following screen to select the view I wish to recover.
> > I run it and it says it completed successfully. However, when I look at
> the
> > views available, the one that supposedly transferred is not there.
> > Can views be restored or would it be just as easy to recreate it using
the
> > recovered view as a guide?
> >
> >
>

almost newbie question:recover view

SQL 2000 sp2
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> 8.6
> the
database
> I
> the
the
>

Allow users to run jobs

Is there a way to grant users (non SA's) to view and execute Jobs on
Sql Server 2000 and SQL Server 7 without granting SystemAdministrator
previleges?
Thanks
"shub" <shubtech@.gmail.com> wrote in message
news:1139255925.860677.80260@.g43g2000cwa.googlegro ups.com...
> Is there a way to grant users (non SA's) to view and execute Jobs on
> Sql Server 2000 and SQL Server 7 without granting SystemAdministrator
> previleges?
> Thanks
>
For multiple users, create a Role, add the users to the Role. Grant the
role all the permissions needed to execute all of the job steps. When you
create the job, set the job's owner = that role.
Rick Sawtell
MCT, MCSD, MCDBA
|||adding to ricks answer,
If a user who is not a member of the sysadmin role attempts to run a
job that includes jobs including CmdExec or ActiveScripting then job
steps will fail.
by default sysadmin role can execute CmdExec or Microsoft ActiveX=AE
scripting job steps
In this case you have to set up proxy account using
xp_sqlagent_proxy_account or
right click sql server agent--> properties-->job system-->
Regards
Amish Shah
|||And how exactly does one create a role? Is it a server wide role?
(Seems that those can't be changed), or a role in the MSDB database?
Also, it seems impossible to assign a role as an owner:
"[@.owner_login_name =] 'login'
Is the name of the login that owns the job. login is sysname, with a
default of NULL. Only members of the sysadmin fixed server role can
change job ownership."
Any more ideas? I like the solution, but it just doesn't seem to work!
-Sean
|||Also, when I try adding a MSDB role as an owner using TSql, I get:
Server: Msg 515, Level 16, State 2, Procedure sp_update_job, Line 217
Cannot insert the value NULL into column 'owner_sid', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Any ideas?
Thanks!
-Sean

Allow users to run jobs

Is there a way to grant users (non SA's) to view and execute Jobs on
Sql Server 2000 and SQL Server 7 without granting SystemAdministrator
previleges?
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1139255925.860677.80260@.g43g2000cwa.googlegroups.com...
> Is there a way to grant users (non SA's) to view and execute Jobs on
> Sql Server 2000 and SQL Server 7 without granting SystemAdministrator
> previleges?
> Thanks
>
For multiple users, create a Role, add the users to the Role. Grant the
role all the permissions needed to execute all of the job steps. When you
create the job, set the job's owner = that role.
Rick Sawtell
MCT, MCSD, MCDBA|||adding to ricks answer,
If a user who is not a member of the sysadmin role attempts to run a
job that includes jobs including CmdExec or ActiveScripting then job
steps will fail.
by default sysadmin role can execute CmdExec or Microsoft ActiveX=AE
scripting job steps
In this case you have to set up proxy account using
xp_sqlagent_proxy_account or
right click sql server agent--> properties-->job system-->
Regards
Amish Shah|||And how exactly does one create a role? Is it a server wide role?
(Seems that those can't be changed), or a role in the MSDB database?
Also, it seems impossible to assign a role as an owner:
"[@.owner_login_name =] 'login'
Is the name of the login that owns the job. login is sysname, with a
default of NULL. Only members of the sysadmin fixed server role can
change job ownership."
Any more ideas? I like the solution, but it just doesn't seem to work!
-Sean|||Also, when I try adding a MSDB role as an owner using TSql, I get:
Server: Msg 515, Level 16, State 2, Procedure sp_update_job, Line 217
Cannot insert the value NULL into column 'owner_sid', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Any ideas?
Thanks!
-Sean

Allow users to run jobs

Is there a way to grant users (non SA's) to view and execute Jobs on
Sql Server 2000 and SQL Server 7 without granting SystemAdministrator
previleges?
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1139255925.860677.80260@.g43g2000cwa.googlegroups.com...
> Is there a way to grant users (non SA's) to view and execute Jobs on
> Sql Server 2000 and SQL Server 7 without granting SystemAdministrator
> previleges?
> Thanks
>
For multiple users, create a Role, add the users to the Role. Grant the
role all the permissions needed to execute all of the job steps. When you
create the job, set the job's owner = that role.
Rick Sawtell
MCT, MCSD, MCDBA|||adding to ricks answer,
If a user who is not a member of the sysadmin role attempts to run a
job that includes jobs including CmdExec or ActiveScripting then job
steps will fail.
by default sysadmin role can execute CmdExec or Microsoft ActiveX=AE
scripting job steps
In this case you have to set up proxy account using
xp_sqlagent_proxy_account or
right click sql server agent--> properties-->job system-->
Regards
Amish Shah|||And how exactly does one create a role? Is it a server wide role?
(Seems that those can't be changed), or a role in the MSDB database?
Also, it seems impossible to assign a role as an owner:
"[@.owner_login_name =] 'login'
Is the name of the login that owns the job. login is sysname, with a
default of NULL. Only members of the sysadmin fixed server role can
change job ownership."
Any more ideas? I like the solution, but it just doesn't seem to work!
-Sean|||Also, when I try adding a MSDB role as an owner using TSql, I get:
Server: Msg 515, Level 16, State 2, Procedure sp_update_job, Line 217
Cannot insert the value NULL into column 'owner_sid', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Any ideas?
Thanks!
-Sean

all_tab_columns in SQL Server

Hi,
Is there any view (or table) like Oracle all_tab_columns
in SQL Server? Or is there an easy way to get all the
table names, the column names, and the column types for
one schema in SQL Server.
Thank you!
check out the view INFORMATION_SCHEMA.COLUMNS
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"david" wrote:

> Hi,
> Is there any view (or table) like Oracle all_tab_columns
> in SQL Server? Or is there an easy way to get all the
> table names, the column names, and the column types for
> one schema in SQL Server.
> Thank you!
>

all_tab_columns in SQL Server

Hi,
Is there any view (or table) like Oracle all_tab_columns
in SQL Server? Or is there an easy way to get all the
table names, the column names, and the column types for
one schema in SQL Server.
Thank you!check out the view INFORMATION_SCHEMA.COLUMNS
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"david" wrote:

> Hi,
> Is there any view (or table) like Oracle all_tab_columns
> in SQL Server? Or is there an easy way to get all the
> table names, the column names, and the column types for
> one schema in SQL Server.
> Thank you!
>

all_tab_columns in SQL Server

Hi,
Is there any view (or table) like Oracle all_tab_columns
in SQL Server? Or is there an easy way to get all the
table names, the column names, and the column types for
one schema in SQL Server.
Thank you!check out the view INFORMATION_SCHEMA.COLUMNS
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"david" wrote:
> Hi,
> Is there any view (or table) like Oracle all_tab_columns
> in SQL Server? Or is there an easy way to get all the
> table names, the column names, and the column types for
> one schema in SQL Server.
> Thank you!
>

All tables under partitioned union view get locked - how to reduce?

Hi. We are using a partitioned view across a number of tables. They are
partitioned on a single column. We use the view to insert and update rows in
the underlying tables.
When loading all the rows for table XYZ through the view we find that SQL
Server is creating IX locks against all of the tables even though all the
rows are for one table only as identified by the check constraint on the
partitioned column.
Is there any way to get SQL Server to lock only the table that will be
affected?
McGy
[url]http://mcgy.blogspot.com[/url]"McGy" <anon@.anon.com> wrote in message
news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update rows
> in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
>
Partitioned views have serious limitations. You should expect to have to
directly address the underlying tables for many operations. Qu|||Cheers David. I am considering duplicating the load stored procedures, 1 per
table, so as to remove the dependency on the partitioned view. It will make
maintenance a bit more complex but ultimately performance should improve.
Does that sound sensible?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4TVf3khGHA.3860@.TK2MSFTNGP02.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
rows
SQL
the
> Partitioned views have serious limitations. You should expect to have to
> directly address the underlying tables for many operations. Qu
>|||"McGy" <anon@.anon.com> wrote in message
news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
> Cheers David. I am considering duplicating the load stored procedures, 1
> per
> table, so as to remove the dependency on the partitioned view. It will
> make
> maintenance a bit more complex but ultimately performance should improve.
> Does that sound sensible?
>
Yes. And you can always use dynamic SQL to load the table.
David|||The only problem with dynamic SQL is that the stored procedure would be a
nightmare to maintain in that form as it uses lots of variables. Also,
because of the stored procedure's size it takes several seconds to compile.
Presumably we would take that compilation hit each time the dynamic SQL is
constructed and prepared for execution?
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
improve.
> Yes. And you can always use dynamic SQL to load the table.
> David
>|||Not necessarily; if you use sp_executeSQL with variables you are
essentially creating a parameterized SQL statement which increases the
likelihood that the execution plan will be reused.
This issue is interesting to me; we use partioned views as well.
However all of our inserts are done with bulk load methods, so locking
hasn't been a problem (yet). I understand the SQL 2005's partitoned
tables are much better than the the views; yet another reason to
consider upgrading.
Stu
McGy wrote:
> The only problem with dynamic SQL is that the stored procedure would be a
> nightmare to maintain in that form as it uses lots of variables. Also,
> because of the stored procedure's size it takes several seconds to compile
.
> Presumably we would take that compilation hit each time the dynamic SQL is
> constructed and prepared for execution?
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> improve.|||Hmmm. Interesting point on the sp_executesql. I'll take a look.
We use bulk insert too; but we also need to apply a bunch of rules to the
records that get loaded. They get loaded twice: once in to a transaction
table which are all inserts - the other in to an aggregation table that
applies certain rules depending upon what type the transaction is.
Just thought of a problem on the dynamic SQL front. The stored procedure
would be well in excess of the 8000 char limit on variable sizes. Is there a
way to work around that?
McGy
[url]http://mcgy.blogspot.com[/url]
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1149427023.769413.5440@.i39g2000cwa.googlegroups.com...
> Not necessarily; if you use sp_executeSQL with variables you are
> essentially creating a parameterized SQL statement which increases the
> likelihood that the execution plan will be reused.
> This issue is interesting to me; we use partioned views as well.
> However all of our inserts are done with bulk load methods, so locking
> hasn't been a problem (yet). I understand the SQL 2005's partitoned
> tables are much better than the the views; yet another reason to
> consider upgrading.
> Stu
> McGy wrote:
a
compile.
is
procedures, 1
will
>|||McGy (anon@.anon.com) writes:
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update
> rows in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
Are the intent locks causing any real problems? I ran a quick test, and
I was not able detect any locking problems, but I might have missed
something. (I was only testing concurrent SELECT statements.)
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|||Hi Erland. I am not actually sure if those IX locks are causing a problem.
What is the impact of an IX lock? When we were loading multiple files in
production we noticed that some were being blocked waiting for others to
complete. We assumed it was because of the view creating locks against all
the tables - but perhaps not?
McGy
[url]http://mcgy.blogspot.com[/url]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97D9732464F2Yazorman@.127.0.0.1...
> McGy (anon@.anon.com) writes:
> Are the intent locks causing any real problems? I ran a quick test, and
> I was not able detect any locking problems, but I might have missed
> something. (I was only testing concurrent SELECT statements.)
>
> --
> 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|||McGy (anon@.anon.com) writes:
> Hi Erland. I am not actually sure if those IX locks are causing a problem.
> What is the impact of an IX lock? When we were loading multiple files in
> production we noticed that some were being blocked waiting for others to
> complete. We assumed it was because of the view creating locks against all
> the tables - but perhaps not?
The purpose of an intent lock is to tell "I am working here, so don't
try to take all this place for your own".
If a transaction updates a few rows in a table it acquires an X lock
on these rows, and also an IX lock on the table. This prevents other
processes from getting an X-lock on the table. Or put into other words,
the process that wants an exclusive lock on the table, does not need to
check if any rows are currently locked.
In case of the partitioned view the IX locks are there to prevent other
processes from acquire exclusive locks on the other tables, as there
could suddenly appear a row that should be inserted the other tables.
SQL Server cannot conclude that all data that is being inserted goes
only into one table in the view.
So, yes, if you have different insert processes in parallel, they will
block each other. And a process what would try "SELECT COUNT(*) FROM
pview" or anything else that requires a scan would also probably be
blocked, even with a condition that filtered out the table being
loaded.
If you want to run parallel loads at maximum speeds, you will probably
have to load into the underlying tables directly.
I will have to admit that I had read up in Books Online on what an
intent lock really is.
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

ALL IN ONE SQL STATEMENT?

I am using one SQL (View) to get my sums on various fields. I then use a
second SQL (View) to reference the first View to do my calculations. Is ther
e
a way to combine this all in one SQL View? I want to use the second View and
be able to pass it date range variables from a Web page form.
I am new at this and appreciate your help in advance...
View 1.
SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty) A
S
SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
GROUP BY Site, [Date]
ORDER BY Site
View 2.
SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
FROM dbo.View1
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1You can use a derived table for this purpose:
SELECT ...
FROM (SELECT ... FROM ...) AS D
Also, if you need to parameterize the query, instead of using a view, you
can use an inline table-valued function:
CREATE FUNCTION dbo.f1
(
@.from_dt AS DATETIME,
@.to_dt AS DATETIME
)
RETURNS TABLE
AS
RETURN
SELECT ... FROM ... WHERE dt >= @.from_dt AND dt < @.to_dt
GO
SELECT ... FROM dbo.f1('20040101', '20050101') AS F;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615ef24bad050@.uwe...
>I am using one SQL (View) to get my sums on various fields. I then use a
> second SQL (View) to reference the first View to do my calculations. Is
> there
> a way to combine this all in one SQL View? I want to use the second View
> and
> be able to pass it date range variables from a Web page form.
> I am new at this and appreciate your help in advance...
> View 1.
> SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty)
> AS
> SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
> AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
> SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
> SumOfOutOfAdherenceSecsQty,
> SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
> (TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
> SumOfTalkHoldAvailable,
> [Date]
> FROM dbo.[National Call Stats]
> WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
> GROUP BY Site, [Date]
> ORDER BY Site
> View 2.
> SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
> SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
> FROM dbo.View1
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||I appreciate your response, I still need a little more clarification, so
thank you in advance for your patience. In your example are you using
referring to SELECT the code in view 2 FROM(SELECT view 1)? I haven't gotten
into creating functions yet, but thanks though.
Itzik Ben-Gan wrote:
>You can use a derived table for this purpose:
>SELECT ...
>FROM (SELECT ... FROM ...) AS D
>Also, if you need to parameterize the query, instead of using a view, you
>can use an inline table-valued function:
>CREATE FUNCTION dbo.f1
>(
> @.from_dt AS DATETIME,
> @.to_dt AS DATETIME
> )
>RETURNS TABLE
>AS
>RETURN
> SELECT ... FROM ... WHERE dt >= @.from_dt AND dt < @.to_dt
>GO
>SELECT ... FROM dbo.f1('20040101', '20050101') AS F;
>
>[quoted text clipped - 25 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Yes. Something like this:
CREATE VIEW dbo.MyView
AS
SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
FROM
(
SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty)
AS
SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
GROUP BY Site, [Date]
) AS D
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615f8daa4774b@.uwe...
>I appreciate your response, I still need a little more clarification, so
> thank you in advance for your patience. In your example are you using
> referring to SELECT the code in view 2 FROM(SELECT view 1)? I haven't
> gotten
> into creating functions yet, but thanks though.
> Itzik Ben-Gan wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1