Can some one please help to find out that how can i implement the materialized view in sql server 2000? thanx for consideration.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
I have a view that joins a dozen tables with a million rows added per year by an application. I want to materialize it. The view is always filtered by date first on reports, then there are a few key transaction keys, but then many other fields required to make each row unique. I don't want to add these columns since they are large, many, not used for sorting or filtering, and may not define uniqueness in a future application design. I need a uniqueifier that is application agnostic. I prefer a bigint. So to store the materialized view ideally for reporting, I want to add the following clustered index to materialize the view:
CREATE unique CLUSTERED INDEX idx1
ON [dbo].[myview](myDate, key1, key2, key3, id bigint identity(1,1) NOT NULL)
And I get this error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bigint'.
Can I do what I want? If so, how?
Hi,
Oracle query :
create materialized view view1 as select *from test
Is there any equivalent for the above query in SQL Server (specifically for "materialized views")
Please advice,
Thanks,
Sam
Hi!I have an select on a view that takes too much time. The view include joins.I tried index-analysis and explain-plan, but this don't help.But it helped when making a real table (select * into realtable frombigview) and afterwardsmaking my select on realtable...?Suggestions? Where to read more about it?GreetingsBjørn
View 2 Replies View RelatedDo not trust values returned by materialized views under SQL Serverwithout frequently checking underlying tables!!!I already posted this message under microsoft.public.sqlserver.serverand I'm amazed nobody from Microsoft answered about this problem. Byinserting lots of data into our two main tables for about 30 minutes,we can fail our materialized view that performs a count_big on thosetwo tables.Executing (after of course having stopped inserting rows in our twotables)[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView option(expand views)[/color]DOES NOT RETURN the same value than:[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView with (noexpand)[/color]The second call - using the materialized view - returns a smallernumber (as if counts were lost during our bulk insert)As our data has to be accurate, we cannot use Materialized viewsanymore. This problem does not occur when the amount of data insertedis smaller. Rebuilding the clustered index on the view fixes theproblem; do we have to constantly be rebuilding the index to keep theview synchronize !?!!?!Is there a way to tell that our view is not synchronized? Justcomparing values returned by our view does not work for us as data isconstantly been inserted.System: SQL server 2000 SP3 Enterprise EditionVincent LIDOU
View 6 Replies View RelatedI am working in Powerbuilder and SQL Server 2000. Within the application I dynamically Drop then recreate a view named view_selection_list. When another user accesses any screen using view_selection_list the screen will hang on the statement "If Exists (SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list".
I also went directly onto the database ran select * from view_selection_list from Query Analyzer. It hangs when the original user creating the view is still active. I know that the issue is locking. I don't know how to fix it.
For example ;
String ls_sql
ls_sql="If Exists ( SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list "
Execute Immediate :ls_sql;
ls_sql="Create View view_selection_list as "
Case 'State'
ls_sql+=" Select distinct proj_id,'State - '+proj_state title from project where proj_state='"+is_data+"'"
Case 'Project'
ls_sql+=" Select distinct proj_id,'Project - '+proj_nam title from project where proj_id='"+is_data+"'"
Case 'All Active Projects'
ls_sql+=" Select proj_id,'Project -' +proj_nam title from project where proj_status = 4 and signed_acq_agmt = 'Y' "
End Choose
Execute Immediate :ls_sql;
The SQL Server connection in the application is:
SQLCA.DBMS = "OLE DB"
SQLCA.ServerName="acq"
SQLCA.LogPass ="*******"
SQLCA.LogId = "acq"
SQLCA.Lock = "RU"
SQLCA.AutoCommit = False
SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='FSRFIN103'"
Thank You
Stanley
Hello,
Do you know how can i create a materialized view in sqlserver like oracle.
Because i want to store in a table the result of a query.
Hi,I have an application that's running fine on development servers (weband database-sql server 2000). I'm updating a record through a thirdparty component but I don't think the component is the problem. What'shappening is that I'm updating fields that are part of view. I'm onlyupdating fields in one table of the view and this works fine in thedevelopment environment.What happens in the production environment when I try to update(using the third party component) I get the following message:"Current recordset does not support updating. This may be a limitationof the provider or of the selected locktype."As an experiment I took the same code but removed the view, leavingonly the table I want to update as the record source. In that case theupdate worked. So it seems that something in the production databasedoesn't like me updating a view. However I can do that in the databasein the development environment.The third party component is dbnetgrid which works fine in thedevelopment environment. I can only conclude it's something about thedatabase that prevents me from updating this same table if it's in aview. I've talked to our DBA but he says there's no difference betweenthe databases. Any ideas would be appreciated.Neil
View 2 Replies View Related
I would like to do this for materialized view
insert value '00466045730060107' through View c. it will insert into table a.
But i got this error when i try to insert into view "C"
Msg 4436, Level 16, State 12, Line 3
UNION ALL view 'X.dbo.c' is not updatable because a partitioning column was not found.
insert into c
values('00466045730060107')
--------------------------------------------------------------------------------------------------
CREATE TABLE a
(
chara char(17)
CONSTRAINT PKA PRIMARY KEY CLUSTERED
CHECK (right(inta,6) between '060101' and '060131' )
)
CREATE TABLE b
(
chara char(17)
CONSTRAINT PKb PRIMARY KEY CLUSTERED
CHECK (right(inta,6) between '060201' and '060228' )
)
create view c
as(
select inta from a
union all
select inta from b
)
-----------------------------------------------------------------------------------------
Hello, without going into the politics of why I'm asking, does anyone have a compiled list of functionality that only sysadmin's could do in 2000 that VIEW SERVER STATE permission opens up in 2005?
From the documentation, so far I've found fn_get_sql (and it's dm view) but I'd really like a complete list.
When using Sql Server Enterprise Manager and viewing a Database / Tables section, most of the tables if not all have a create date of 11/5/2004.Except for one, DNN_Users, has a creation date of 7/10/2007What factor could have caused that create date to have changed?What factors go into the date being set on that column in the database design? Does the date get updated say if I were go go in and change a datatype in a table?
View 5 Replies View RelatedHi everyone,
we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?
Thanks
Cannot see the Colums in the "design view" of Queries. All i see when i want to design a new query is *columns
This happens in only one database, in other databases using same server i can see the colums and can tick them to view then in the query.
In enterprise manager i see all the columns.
Using SQL 2000 and MSAccess 2000
The table I want to query contains data structure like so
tblText
-------
VisitId(int)CodeId(int) ChrText VarChar()
1 2 'Text Code2'
1 3 'Text Code3'
2 1 'Text Code1'
3 3 '*TextCode3*'
4 1 'Text Code1'
4 4 'Text COde4'
What I want is for my View to Look Like this
VisitId Code1 Code2 Code3 Code4
1 N/A Text Code2 Text Code3 N/A
2 Text Code1 N/A N/A N/A
3 n/a n/a *TextCode3* n/a
4 Text Code1 n/a n/a Text COde4
I am sure I Group by VisitId, but do not know the correct function to construct the rest of the Select Query
Create View vw_tblText
As
Select VisitId,
Case(intCodeId=1 Then chrText Else 'N/A' End) As Code1
Case(intCodeId=2 Then chrText Else 'N/A' End) As Code2
etc..
From tblText
Group by intVisit
Any Ideas much appreciated
I created a new user (Joe_Reader) and gave this user the db_datareader Role.I then went and specified what tables and queries would this user see. Thisworks fine. I however keep on creating tables and views on the database andautomatically these objects are viewable by the user. I have to manually goand deny the access for each object I create. Is there a way that I canonly let Jo_Reader see the tables that I originally assigned and that noneof the other queries or views are viewable by himThanks
View 1 Replies View RelatedJust wonder how many of you are using or had used indexed view in SQL 2000? Please, share any details/info.
We are currently using in and having problems as we are getting Errors 644, 'Couldn't find index...' every time we try to update the underlying tables.
I have created a view in MS SQL2000 as followed:
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;
my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.
Any HELP would be GREAT.
Thank You!!
Hi all,In MS SQL Management Console I can right-click on any Table and I havethe option All Tasks > Export Data where I can export the table toExcel. In a View however this isn't there. I have many views I wantto simply export to Excel, but the only way I've found to do it iscreating an ODBC connection to the MS SQL database from MS Access,linking the Views to Access Tables, and exporting from Access. surelythere's someway to export a View to Excel within MS SQL easily likeexporting a table...Thanks ---Alex
View 1 Replies View RelatedHi allWe have some tables with a couple of layers of very simple views built ontop. In the table are maybe 6 columns and about 15000 records. The firstview cobines the data in the table with some other data from a lookuptable. The second view does some sorting on the first view using certaindates . They have worked fine for well over a year now.Until this morning that is... the views stopped returning the full set ofresults- even the very simple one that sits just above the table. The viewreturned the core of the data from the main table, but nothing from thelookup table.In order to get them to work we had to delete each view (using access frontend to do this), and then recreate it with exactly the same SQL text. I amguessing this causes SQL Server to recompile it. As soon as view 1 had beenrecreated it worked, but view 2 still failed, again rebuilding view 2 itstarted working.The only thing I can think of is that this morning I added 2 new fields tothe base table, but I'm sure I've done this before without any (noticable)problems.any thoughts as to why it happened would be welcome, I am a bit nervousnow...thanksAndy
View 5 Replies View RelatedHi there,
Not sure if this is possible but anyways. Need to restrict access on systems for users
and at the moment they are limited to being server and process administrators only.
This gives them enough room to do what they need to do, however the taskpad view in Enterprise manager only shows them the Log space used, not the datafiles.
The only way to get the TaskPad view to show the datafiles is to add them to the database creators role, which already gives them more permissions than they need.
So, just a shot in the dark but is there someway to get Taskpad view to show all the info there about the database?
I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT *FROM VMRawUsageWHERE (ACCOUNT = @VMBox) AND (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results. The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?
Hi ALL,
How to create index in the view on MS SQL 2000?
Thank you very much!
This works fine in SQL 2000, but not in SQL 2005
View 12 Replies View RelatedI have the following a view on a SQL2K box that uses the following SELECT statement:
SELECT
SF.SKU,
SAT.PublicationDate AS SATPubDate,
SAM.PublicationDate AS SAMPubDat
FROM SkuFlags SF
LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN
LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)
The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.
My question is why this works in SQL2K and not SQL2K5?
Thanks,
Kevin
Hello,
I have a query that seems to take a while to execute and I'm looking into using an indexed view to see if this helps. I use the script below to create the view but when I query it's indexability using:
(select ObjectProperty(object_id('GetMessageQueueDetails'), 'IsIndexable'))
it always return '0'.
Here is a very cut down version of the view, only selects the uid! from a single table
IF OBJECT_ID ('GetMessageQueueDetails', 'view') IS NOT NULL
DROP VIEW GetMessageQueueDetails ;
GO
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
GO
CREATE VIEW GetMessageQueueDetails
WITH SCHEMABINDING
AS
SELECT Uid
FROM dbo.MyTable
GO
I see from See http://msdn.microsoft.com/en-us/library/aa933148(SQL.80).aspx that the pre-requsites for indexed views are pretty strict, I have been through this list and think I have everything covered, except for :
"The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view."
Is there an easy way to find out if ANSI_NULLS was ON or OFF when the table was created. If it was OFF can I do an ALTER TABLE to turn it on and will that make the view indexable? If so how do I do this with out trashing the data in the table?
Or am I doing something else wrong?
Can anybody offer any help?
Thanks
Hi all,
I'm trying to use a SQL 2000 view in one of my sources. The view isnt anything special --- just three tables that have been unioned together. All these three tables have the EXACT same datatypes as well as column names. There are no constraints on these tables (yet). There is an identity seed on the first ID column. However, when I try to access this view, it generates the following error:
Server: Msg 4502, Level 16, State 1, Procedure MyView, Line 5
View or function 'MyView' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'MyView' because of binding errors.
Does anybody have any experience with this?
I have a partitioned view containing 4 tables (example follows at end)
The query plan generated on a select correctly accesses just one of the tables
The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area
Note that the behaviour is the same with SP1 on SQL2000
I would be very grateful for any advice
Thanks
Stefan Bennett
Example follows
--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO
INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO
--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO
--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'
-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;
I have a table:
CREATE TABLE [dbo].[tx1]( [f1] [nvarchar](50) , [seq] [int] IDENTITY(1000,1) NOT NULL ) ON [PRIMARY]
SELECT *
FROM dbo.tx1
ORDER BY seq DESC
go
f1 seq
zz 1003
uu 1002
kk 1001
yy 1000
create view vx1 as
SELECT top 100 percent *
FROM dbo.tx1
ORDER BY seq DESC
go
select * from vx1
yy 1000
kk 1001
uu 1002
zz 1003
Hi,
I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.
However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.
Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.
Here's the query to reproduce this error.
Code Snippetcreate table t1 (pk1 int not null,primary key (pk1))
create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1))
go
insert into t1 values (1)
insert into t2 values (2, 'XXXXX')
go
create view V as
select pk1, 'ZZZZ' as label1 from t2
go
select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1
from t1 as A left outer join V as B on A.pk1 = B.pk1
go
This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".
I just want to confirm that this is an issue and no fix is available for this so far.
Regards,
Naresh Rohra.
Hello --Is there a way to configure 2000 so it will save a view layout after Ichange it in the diagram pane?Thanks for any help.Larry Mehl
View 2 Replies View RelatedThis one has me stumped.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
View 5 Replies View RelatedHi All,
I would like to know, how the datetime will be stored in the sqlserver datetime column.
Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy.
while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in
yyyy/mm/dd format.
So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database?
Thanks in Advance.
Regards,
Dhanasekaran. G