MS SQL Server :: Incorrect Query Plan With Partitioned View On SQL 2000

Query Against Partitioned View Is Not Optimized Due To CONVERT_IMPLICIT
We have a situation where queries against a partitioned view ignore a suitable index and perform a table scan (against 200+MB of data), where the same query on the underlying table(s) results in a 4 page index seek. I can€™t find any mention of the situation, so I€™m trying a post here.

We€™re running SQL Server 2005 Enterprise edition sp2 on Windows 2003 Enterprise Edition sp1 on a two node cluster, and it also occurs on a stand-alone development box with Developer edition. We have four tables, named Options#0, Options#1, Options#2, and Options#3. All are almost identical (script generated by SSMS and edited down a bit):

SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Options#0](
[ControlID] [tinyint] NOT NULL CONSTRAINT [DF_Options#0__ControlID] DEFAULT ((0)),
[ModelCode] [char](8) NOT NULL,
[EquipmentID] [int] NOT NULL,
[AdjustmentContextID] [int] NOT NULL,
[EquipmentCode] [char](2) NOT NULL,
[EquipmentTypeCode] [char](1) NOT NULL,
[Description] [varchar](50) NOT NULL,
[DisplayOrder] [smallint] NOT NULL,
[IsStandard] [bit] NOT NULL,
[Priority] [tinyint] NOT NULL,
[Status] [bit] NOT NULL,
[Adjustment] [int] NOT NULL,
CONSTRAINT [PK_Options#0] PRIMARY KEY CLUSTERED
(
[ModelCode] ASC,
[EquipmentID] ASC,
[AdjustmentContextID] ASC,
[ControlID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Options#0] WITH CHECK ADD CONSTRAINT [CK_Options#0__ControlID] CHECK (([ControlID]=(0)))

ALTER TABLE [dbo].[Options#0] CHECK CONSTRAINT [CK_Options#0__ControlID]

The only differences between the tables are in the names and in the value defaulted to and CHECKed, which matches the table name (to support the partitioned view, of course).

We receive and load data ever week and every two month, and use an unlikely algorithm to load and manage its availability by running an ATLER on the view (to maintain the access rights defined for the hosting environment). Scripted out via SSMS, the view looks like:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[Options] AS select * from Options#1 union all select * from Options#3

The problem is that when we issue a query like

SELECT count(*)
from Options
where ControlID = 1
and ModelCode = '2004NIC9'

The resulting query (as checked via the query plan and SET STATISTICS IO on) will get €œpartitioned€?, running against the proper table, but it will ignore the query, perform a table scan, and churn through 200+MB of data. A Similar query run against the underlying table

SELECT count(*)
from Options#1
where ControlID = 1
and ModelCode = '2004NIC9'

(with or without the ControlID = 1 clause) will perform a Clustered Index Seek and read maybe 4 pages.

Analyzing the execution plan shows that the table query work like you€™d think, but for the query against the view we get a Clustered Index Scan, with predicate:

[DBName].[dbo].[Options#1].[ControlID]=(1) AND CONVERT_IMPLICIT(char(8),[ DBName].[dbo].[Options#1].[ModelCode],0)=€™2004NIC9€™

I get the same results when explicitly listing all columns in the view. The code page on the view and tables is the same (as determined by checking properties via SSMS).

Why is the table data column being implicitly converted to the data type that it already is? Why does this occur when working with the partitioned view but not with the actual table? Can this behavior be controlled or modified without losing the (incredibly useful) data loading management benefits of the partitioned view? I€™m guessing (and hoping) it€™s some subtle quirk or mis-setting, please set me on the right path!

Philip Kelley

View Replies !

Incorrect Query Plan
I was doing a demo last night, something that I've done hundreds of times already. Last night was the first time that it has failed to work. I was trying to show what the sys.dm_db_missing_index_* DMVs can provide.

AdventureWorks database

I'm running the following query:

select city from person.address where city like 'A%'

This is supposed to produce a table scan which in turn will obviously cause SQL Server to detect that an index could be beneficial. However, it does a clustered index scan (yes, I know, basically the same thing) instead and I see absolutely nothing appear in the DMVs. I pulled the data out into a dummy table that did not have a primary key either using the following:
select * into person.tmpaddress from person.address

I then execute the same query and get a table scan which is expected:

select city from person.address where city like 'A%'

However, it does not matter how much I execute that query or any other permutation of explicit query, absolutely nothing at all gets logged into the sys.dm_db_missing_index_* DMVs. I have also tried this same type of thing with several other tables in the AW database and can not find a single query which will cause anything to be logged to these DMVs. It seems that something is broken, but for the life of me, I can't figure out what is wrong. No weird settings, I'm running as sa, etc.

I can run queries like this in other databases and stuff gets immediately logged to the DMVs as expected. Any ideas?

View Replies !

SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This 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.

View Replies !

Query Server To Find All Partitioned Tables, Partition Name, Column Used, Partitioned By
I want to find a way to get partition info for all the tables in all the databases for a server. Showing database name, table name, schema name, partition by (maybe; year, month, day, number, alpha), column used in partition, current active partition, last partition (for date partitions I want to know if the partition goes untill 2007, so I can add 2008)

all I've come up with so far is:

Code Block

SELECT distinct o.name From sys.partitions p
inner join sys.objects o on (o.object_id = p.object_id)
where o.type_desc = 'USER_TABLE'
and p.partition_number > 1

View Replies !

(SQL 2000) Incorrect Results When Using An Outer Join And A View!
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.

View Replies !

Odd Query Plan For View
I have a SQL 2000 table containing 2 million rows of Trade data. Hereare some of the columns:[TradeId] INT IDENTITY(1,1) -- PK, non-clustered[LoadDate] DATETIME -- clustered index[TradeDate] DATETIME -- non-clustered index[Symbol] VARCHAR(10)[Account] VARCHAR(10)[Position] INTetc..I have a view which performs a join against a security master table (togather more security data). The purpose of the view is to return allthe rows where [TradeDate] is within the last trading days.The query against the view takes over around 30 minutes. When I viewthe query plan, it is not using the index on the [TradeDate] column butis instead using the clustered index on the [LoadDate] column... Theodd thing is, the [LoadDate] column is not used anywhere in the view!For testing purposes, I decided to do a straight SELECT against thetable (minus the joins) and that one ALSO uses the clustered index scanagainst a column not referenced anywhere in the query.There is a reason why I have not posted my WHERE clause until now. Thereason is that I am doing what I think is a very inefficient clause:WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())The function calculates the proper trade date based on the specifieddate (in this case, the current date). It is my understanding that thefunction will be called for all rows. (Which COULD explain theperformance issue...)However, this view has been around for ages and never before caused anysort of problems. The issue actually started the day after I had torecreate the table. (I had to recreate the table because some columnswhere added and others where renamed.)On a side note, if I replace the WHERE clause with a hard-coded date(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine butSTILL uses the clustered index on the [LoadDate] column.

View Replies !

View Query Execution Plan

Hi,

I am developing an application (VB) that should present a query estimated execution plan.

Using the SQL Server Management Studio, I should execute the following commands to see the query's estimated execution plan:

SET SHOWPLAN_XML ON

go

MyQuery
go

SET SHOWPLAN_XML OFF

go

The query is not executed. The result is the query execution plan.

In my application, I call Connection.Execute to execute the 'SET SHOWPLAN_XML ON'. Then, I use a Resultset submit the query. The query is executed and the execution plan is not returned.

Does anyone have any ideas?

Thanks

View Replies !

Partinioned View And Query Plan: Oddity?
Hello all,

   I'm going to implement an inverted index data structure that relies, for performance consideration, on partinioned views.

I've created a set of tables with a column named "PartitionKey" that deserve to partition the view. Then I've created a view using the UNION ALL as prescribed. The partinion works.

Now I'm devising the right SELECT query to benefit from the partition but I'm getting strange query plan.

"word" is the partinioned view and "vocabulary" is a referenced table.

If I write something like this:

select * from

   vocabulary v

   inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey

where

   word = 'foobar'

   and w.partitionkey = 1    -- It works correctly even with the condition v.partitionkey = 1

The query plan, correctly, shows that the only table actual accessed is "word1", the one related to the partinionkey value 1. ==> OK

If I write something like this, that appears more intuitively:

select * from

   vocabulary v

   inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey

where

   word = 'foobar'

I get a concatenation operation that involved all the "wordXX" tables. I deduce that the engine is not so "clever" to benefit from the join hint.

So, I rewrite the previous as the following, thinking to feed a better infotmation to the engine:

select * from

   vocabulary v

   inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey

where

   word = 'foobar'

   and w.partitionkey = (select partitionkey from vocabulary where word = 'foobar')

That resembles the first example but... it doesn't work: I still get a concatenation operation.

I'm very frustrated and than rewrite again the query in a T-SQL script such this:

declare @partitionkey as int

set @partitionkey = (select partitionkey from vocabulary where word = 'foobar')

select * from

   vocabulary v

   inner join word w on v.wordid = w.wordid and v.partitionkey = w.partitionkey

where

   word = 'foobar'

   and w.partitionkey = @partitionkey

And I still get a concatenation operation.

I can understand the behaviour of the second and third example (the query optimizer doesn't have an actual value for the partition key) but the fourth is unbeliavable. Even if I'm feeding a query with a fixed value it cannot benefit of it at all.

Where am I wrong? Is so "silly" the qwery optimizer? How can I force the expected behaviour?

I would say more that the "partitionkey" is generated randomly when a new word is initially inserted into the parent table "vocabulary" so I cannot compute it before to access the "word" table.

I've tested on SQL2005. If anyone is interested I can provide the queryplans generated for all the above examples.

View Replies !

Partitioned View
Hi! This is my first post and I really need help with Partitioned View. I'm using Sql Server 2000 and I created a partitioned view using 6 tables and now a need to create the table '7' and alter the view. But when i'm trying to insert new data i'm receiving the message: :eek:
"Server: Msg 4416, Level 16, State 5, Line 1
UNION ALL view 'tb_sld_cob_pap' is not updatable because the definition
contains a disallowed construct."

My code is:

drop VIEW tb_sld_cob_pap
GO
CREATE TABLE dbo.tb_sld_cob_pap_7 (
cod_operacao int NOT NULL ,
cod_contrato int NOT NULL ,
sequencial_duplicata int NOT NULL ,
data_sld_pap smalldatetime NOT NULL CHECK ([data_sld_pap] >= '20060201'),
liqex_dia_nom_outros float NULL ,
liqex_dia_moe_outros float NULL,
constraint pk_pap7 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
)
GO
CREATE INDEX IdxSldCobPap7_1 ON dbo.tb_sld_cob_pap_7(cod_titulo, seq_titulo, data_sld_pap)
GO

CREATE INDEX IdxSldCobPap7_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, seq_ctr_sacado, sequencial_duplicata, data_sld_pap)
GO

ALTER TABLE dbo.tb_sld_cob_pap_6
DROP CONSTRAINT CK__tb_sld_co__data___6C190EBB
GO

ALTER TABLE dbo.tb_sld_cob_pap_6 ADD CONSTRAINT
CK__tb_sld_co__data___6C190EBB CHECK (((([data_sld_pap] >= '20051201') and ([data_sld_pap] < '20060201'))))
GO

create VIEW tb_sld_cob_pap
as
select * from tb_sld_cob_pap_1
union all
select * from tb_sld_cob_pap_2
union all
select * from tb_sld_cob_pap_3
union all
select * from tb_sld_cob_pap_4
union all
select * from tb_sld_cob_pap_5
union all
select * from tb_sld_cob_pap_6
union all
select * from tb_sld_cob_pap_7

My table tb_sld_cob_pap_6 does NOT have data with ([data_sld_pap] >= '20060201').
I'm using this script in other database and I don't have this problem.

Thank you...

View Replies !

Partitioned View
USE Northwind
GO

CREATE TABLE myTable99_1 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/1999' and PostDate < '12/31/1999 23:59:59'))
CREATE TABLE myTable99_2 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2000' and PostDate < '12/31/2000 23:59:59'))
CREATE TABLE myTable99_3 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2001' and PostDate < '12/31/2001 23:59:59'))

CREATE INDEX myTable99_1_IX ON MyTable99_1
(Account, Ledger)
CREATE INDEX myTable99_2_IX ON MyTable99_2
(Account, Ledger)
CREATE INDEX myTable99_3_IX ON MyTable99_3
(Account, Ledger)
GO

CREATE VIEW myView99
AS
SELECT Account
, Ledger
, PostDate
FROM myTable99_1
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_2
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_3
GO

SELECT * FROM myView99 WHERE Account = 1 AND Ledger = 1
GO

DROP VIEW myView99
DROP TABLE myTable99_1, myTable99_2, myTable99_3
GO

OK, so I thought I knew this, but I'm looking for parallelism...not only am I no getting it, I'm getting an Index scan....is it becuse I didn't put any data in the table? I thought it would stil show my index seek with parallelism

What up, homey?

View Replies !

Partitioned View
I have a big table with about 40 million rows. Questions I have are:
1. Do I need to split this table into several small tables and then create a partitioned view or would one big table good enough for performance?
2. If I create a partitioned view then does that mean I have to continually adding tables to this view since there will be new records that need to be added? Is there a way to automatically create new tables to be added to the partitioned view?

View Replies !

Partitioned View On SS2000
There has been a functional change under SS2000 such that a partitioned view needs to be partitioned on a primary key.
Under SS7 we had the data logically divided into separate tables based on an identifier. As a single table this is over 70Gb, but this breaks down into 18 individual tables within a view.
The performance under SS7 was very good as the query would only look at relevant tables, but SS2000 now looks at all the tables in the view.
There is an example of the problem below and I would very much appreciate any constructive contributions towards finding a resolution to this.
Create table table1 (f1 char(10), f2 int)
Create table table2 (f1 char(10), f2 int)
GO
Alter table table1 with check add constraint chk_table1_f2 check (f2 = 1)
Alter table table2 with check add constraint chk_table2_f2 check (f2 = 2)
GO
insert into table1 values ('aaa',1)
insert into table1 values ('bbb',1)
insert into table1 values ('ccc',1)
insert into table2 values ('xxx',2)
insert into table2 values ('yyy',2)
insert into table2 values ('zzz',2)
GO
create view tableview as
select * from table1 union all
select * from table2
GO
-- the execution plan under SS7 shows that only table1 will be scanned
-- for the following query, whereas under SS2000, both tables are
-- scanned.
select * from tableview where f2=1

View Replies !

More Than One Column In Partitioned View??
Hello..

Im having trouble to make a partitioned view when I use more than one column in check constraint!

Should I use check constraint on all the primary keys? or is't possible to have 5 primary and check constraints on 2 of them ?

I've read
Creating a partitioned view (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp?frame=true)

But i cannot see limitations on this???

View Replies !

Partitioned View Misbehaving!
Hi all,

I have a partitioned view on SQL Server 2000 containing 4 tables (example below)

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

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 - it includes the partition key?

update sales_national
set total = 100
where ordernum = 23456 and
region = 'South';

I would be very grateful for any advice

Thanks

Jaishel.

View Replies !

Partitioned View Problem
Hi all,

I am designing 3 p:artitioned views for 3 tables. Those tables grow up in 1.5 millions of rows per month (each one), so I decided to partition those tables monthly. The issue is that if I want to create the views with more than 256 months (256 tables) SQL Server says: 'Server: Msg 106, Level 15, State 1, Procedure Jugadas, Line 258Too many table names in the query. The maximum allowable is 256.'

Is there any workaround for this?

Another solution maybe?

PD1: I've tested with less than 256 tables and it works fine, I can update and query the tables (except for a couple of querys where I've got to join 2 or more of the involucred views in which case I got a similar error saying about a 260 table limit).

View Replies !

Partitioned View Question

I have a table that I'm trying to scale out into a partitioned view. It's about 30 million rows. It's a workflow table and I have a taskID in the table. Originally the table was partitioned on this column but performance still wasn't what I wanted it to be, so we figured out how we could partition on a bit flag of IsOpen.

Question #1) Anyone know a best practice for creating apartitioned views on multi-columns?

What I'd like to try to do to lower the complexity of the original partitioned view is to create a view of partitioned views. Is this even possible (This is Q#2, BTW).

View Replies !

Updating A Partitioned View In A Cursor
I have a partitioned view defined by a UNTION ALL of member tables. I can update the member tables through the view without any problem. However, when I declare a cursor on this partitioned view and try to update the view using WHERE CURRENT OF, I get an error saying 'The target object type is not updatable through a cursor'. Does anyone know if it's the case that updating a partitioned view through cursor is not supported in SQL Server 2000?

Thanks

View Replies !

Partitioned View Performance Question
I'm fairly new to partitioned views, but am implementing one in test to see if it would be a worthy candidate for production.

The test server is basically a workstation with 2 drives, one for data, one for logs. There is a fairly large table (87M rows) and a partitioned view written against multiple tables with columns matching those in the 87M row table.

To mirror a typical day in production, I needed to insert about 2.5M rows into each (the big table, and the view). However, when doing so, I didn't get the performance increase I'd expected.

Inserting the 2.5M records into the 87M row table took approximately 6.5 minutes. The insert into the view took approximately 2 hours and 52 minutes.

This seems absurd. The data added is heavily based on one particular date (ie; most of the data will be for one date, with maybe 1% of the data being of other dates). The tables behind the view are broken up by date. So, most of the data would have went into one table, with a small percentage going into other tables.

Default Fill Factors were used, no out of the ordinary tuning done anywhere. There's an index on the date field in the 87M row table, but that's about it.

I'm confused, any ideas?

View Replies !

Bulk Insert In To A Partitioned View?
Greetings once again my SQL friends,

I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :

Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation

How to solve this problem?

View Replies !

Partitioned View && Computed Column..
Hello,

please enlighten me regarding an issue with partitioned view... There are 3 tables in my DB of a similar structure:

CREATE TABLE Table1 (value1 varchar(1))
CREATE TABLE Table2 (value1 varchar(1))
CREATE TABLE Table3 (value1 varchar(1))

INSERT INTO Table1 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'

INSERT INTO Table2 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'

INSERT INTO Table3 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'

As sometimes we need to access all data from these tables, a view has been created:

CREATE VIEW AllData AS
SELECT value1, '1' as table_id from Table1
UNION ALL
SELECT value1, '2' as table_id from Table2
UNION ALL
SELECT value1, '3' as table_id from Table3

The problem is that while running a query like

SELECT * from AllData WHERE value1 = 'a' and table_id = '3'

I see a table scan being performed on all 3 tables, not just table3 - i.e optimisation engine doesn't care for my table_id computed column and for that fact that required data is located ONLY in Table3.

Is there any way to force optimiser to consider this column andrrebuild a plan? If not - how can I rebuild a view (I can't modify tables) to achieve that? Maybe create an index for a view?

Thanks in advance. RTFM and search don't seem to clarify this for me...

View Replies !

Partitioned View With Computed Column
Using SQL Server 2005. Defined partitioned view with computed column. Computed column was a constant varchar. Ran a SELECT. According to Query Execution Plan, SQL did recognize the computed column as the partitioning column and used it to optimize the query.

However MSDN says a computed column cannot be used as the partitioning column.

Could someone from MS clarify?

View Replies !

Distributed Partitioned View With RPC (or DTC) Problem
I am setting up 3 Linked Servers (SERVER_A, SERVER_B and SERVER_C) in an isolated local network. They are all running SQL Server 2005 Developer Edition, all on XP SP2. On each server, I have a distributed partitioned view named WAREHOUSE_ALL that basically is the UNION of all WAREHOUSE tables.

I am having trouble in running write (INSERT, UPDATE or DELETE) queries on the distributed partitioned view. The error returned was (run from SERVER_B)

OLE DB provider "SQLNCLI" for linked server "SERVER_A" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 7The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SERVER_A" was unable to begin a distributed transaction.

However, executing a read (SELECT) query ran smoothly without error.

I have done all the steps required as described in the article at http://support.microsoft.com/?kbid=873160 . Note that the only difference between the situation and our situation is the provider (SQLOLEDB and SQLNCLI), which I guess does not important. Unfortunately, the error still comes out.

After reading heaps of other article, I suspected that there is something wrong with MSDTC. As far as I know, all the settings for MSDTC were set accordingly. Then, I ran DTCPing - http://www.microsoft.com/downloads/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&DisplayLang=en and the error returned was
DTCping log file: C:Documents and SettingsAdministratorDesktoplSERVER_B2496RPC server is ready
Please Start Partner DTCping before pinging
++++++++++++Validating Remote Computer Name++++++++++++
Please refer to following log file for details:
   C:Documents and SettingsAdministratorDesktoplSERVER_B2496.log
Invoking RPC method on SERVER_C
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed

And here is the log file:

Platform:Windows XP
IP Configure Information
    Host Name . . . . . . . . . : SERVER_B
    DNS Servers . . . . . . . . : 129.78.99.2
    Node Type . . . . . . . . . :
    NetBIOS Scope ID. . . . . . :
    IP Routing Enabled. . . . . : no
    WINS Proxy Enabled. . . . . : no
    NetBIOS Resolution Uses DNS : no

Ethernet adapter {4404F3CB-F4B7-4990-912C-E69721C885B1}:

    Description . . . . . . . . : 3Com EtherLink XL 10/100 PCI TX NIC (3C905B-TX) #2 - Packet Scheduler Miniport
    Physical Address. . . . . . : 00-01-02-85-B8-A9
    DHCP Enabled. . . . . . . . : no
    IP Address. . . . . . . . . : 172.19.102.35
    Subnet Mask . . . . . . . . : 255.255.255.0
    Default Gateway . . . . . . : 172.19.102.250
    DHCP Server . . . . . . . . : 255.255.255.255
    Primary WINS Server . . . . : 0.0.0.0
    Secondary WINS Server . . . : 0.0.0.0
    Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
    Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970

++++++++++++lmhosts.sam++++++++++++

++++++++++++hosts      ++++++++++++
127.0.0.1       localhost

++++++++++++++++++++++++++++++++++++++++++++++
     DTCping 1.9 Report for SERVER_B
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
10-05, 17:10:54.769-->Start DTC connection test
Name Resolution:
    SERVER_C-->172.19.102.36-->SERVER_C
10-05, 17:11:09.781-->Start RPC test (SERVER_B-->SERVER_C)
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed

I guess it could be due to port problem, which I have already opened in the Windows Firewall. There is one article which is confusing me -> Update to automatically open port 135 in Windows Firewall when a TCP or a UDP RPC server registers with the endpoint mapper at http://support.microsoft.com/kb/838191 (This article shows automatic opening of port 135!)

Please help me. Thanks.

View Replies !

MS SQL 2000 SP Query Plan
I have a couple of complex stored procedures that work well and quicklyonce they have compiled. The problem I am running into is that everyonce in a while they want to refresh thier execution plans, and whenthat happens it takes about 1 minute and 30 seconds for them torebuild, well of course my application is set up to time out commandsafter 30 seconds so basicly the stored procedure never completes andhangs up all of my subsequent stored procdures.I have tried to useOPTION KEEP FIXEDPLANon all of my select statments but I was wondering what else could bedone to stop a stored procedure from it's need to rebuild.-Adam

View Replies !

Triggers On Tables Underlying A Partitioned View
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.

View Replies !

Unable To Create A Partitioned Indexed View
Hi,

While creating an indexed view with the command :

create unique clustered index idx_atrid on account_transactions (policy)

there is a check constraint on the policy column of the tables used in this view.

The following error is encountered

Cannot index the view 'test.dbo.account_transactions'. It contains one or more disallowed constructs.

Can anyone help?

View Replies !

Insertion Faild In Partitioned View In Sql Server 7.0
I have a problem while I try to insert data into a partioned view I am
getting the following error.

Server: Msg 4436, Level 16, State 12, Line 9
UNION ALL view 'sales_all' is not updatable because a partitioning column
was not found.

Any thoughts

USE pubs

CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO

ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO

ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO

View Replies !

Partitioned View Broken After Moving Table To New Filegroup
I am using SQL Server 2000, SP3.I created an updatable partitioned view awhile ago and it has beenrunning smoothly for some time. The partition is on a DATETIME columnand it is partitioned by month. Each month a stored procedure isscheduled that creates the new month's table, and alters the view toinclude it. Again... working like a charm for quite some time.This past weekend I moved some of the first tables onto a new filegroup. I did this through Enterprise Manager, by going into designmode for the table, then going into the properties for the table andchanging the file group there as well as in all of the indexes. Nowthe partitioned view is no longer updatable. It gives the errormessage: "UNION ALL view '<view name>' is not updatable because apartitioning column was not found."I have extracted the DDL for all of the partition tables and comparedthem and they all look the same. I checked and then double-checked theCHECK constraints to make sure that they were all valid and they are.If I remove the tables that I moved to the new file group from theview, then it is once again updatable, but when I put them back in itfails again.Any ideas? If you would like samples of the code then I can send italong, but it's rather large, so I have not included it here.Thanks!Thomas R. Hummel

View Replies !

System.Data.SqlClient.SqlException: Incorrect Syntax Near The Keyword 'Plan'.
I'm having Some Problem with my code....Whenever i try to insert from using a Insert button page gives me this error
"System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Plan'."
Can somebody help me What's the Problem...for your convenience i'm giving my code

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
        End If
End Sub
Sub doInsert(Source as Object, E as EventArgs)
Dim myConn As SqlConnection = New SqlConnection(strConn)Dim MySQL as string = "Insert into Activities (ActDate, Activity, Plan, Completed) values (@ActDate, @Activity, @Plan, @Completed);"
        Dim Cmd as New SQLCommand(MySQL, MyConn)
        cmd.Parameters.Add(New SQLParameter("@ActDate", Textbox2.Text))        cmd.Parameters.Add(New SQLParameter("@Activity", Label5.TExt))        cmd.Parameters.Add(New SQLParameter("@Plan", Textbox3.text))        cmd.Parameters.Add(New SQLParameter("@Completed", Textbox4.text))      ' cmd.Parameters.Add(New SQLParameter("@Comments", Text11.text))        MyConn.Open()        cmd.ExecuteNonQuery()
        BindData()        MyConn.Close()        label12.text = "Your data has been received!"    ' else    '    label12.text = "Data Already Enter For This Item-Name for This Date"
    ' end ifEnd Sub

View Replies !

SQL SERVER 2000 Partitioned Views Bug
Thanks in advance in reading this post ! I'm facing a situation in sql server 2000 sp4 with partitioned views.

I have a partition views that joins about 10 tables, in each table there is a check constraint.

For example, if a exec a select count(*) from VIEW where col1 = '20080101' , it goes for the table that has data for '20080101' .

If I exec a select col1,col2,col3,col4 from VIEW where col1='20080101', it goes to all tables and make an index seek.

I want the beaviour of query 1, beause it is just looking on 1 table and not one the 10.

Thanks in advance !

View Replies !

Incorrect Columns Returned By View
When I create a view consisting of an inner join between a view and atable the columns refernced in the the view are returned incorrectly,exampleselect id.itemcode, id.description, iv.linevalue, iv.vatvalue fromdbo.tbl_itemdetails id inner join dbo.vw_invoices iv wheredbo.tbl_itemdetails.itemcode = dbo.vw_invoices.itemcodeThe actual value that is returned from the view is the columnimmediately to the left of the linevalue column, ie stockroom

View Replies !

MS Access Creates Incorrect WITH Clauses In View
When I create a view in a MSAccess Project SQLServer or MSAccess creates WITH clauses with Chinese characters. The view was created by pasting the T/SQL code from the SQL Query Analyzer in the New View Window in MSAccess. A typical WITH clause looks like:
....
WITH ê‰¤åˆ“ ›£ å‡¶æŽ±å‡¸ë±™å‡´äŸ„å‡³ê†™åˆ“ ¢? å‡¶ë¯»å‡´ ï¿½ï¿½ å‡´ ï¿½ï¿½ å‡´ê…¾åˆ“ î‰‰ å‡² ·¼ å‡¶èŽŠå‡´ì°?å‡²ê‰«åˆ“êŠ¯åˆ“ ¥µ å‡¶ä¦šå‡¶ìš å‡¸ê¶…å‡´ì³¼å‡² §— å‡¶ä“¸å‡³ê†ªåˆ“ä’‹Ð¤ä‚‹ì‰ è†‹Éœ LEFT OUTER JOIN
....

Where do these WITH clauses come from and how do I prevent these from appearing?

View Replies !

Another Problem With Query Performance Against A Partitioned Table
I have a issue which is similar to other postings about performance problems with a query that crosses partitions.

What makes this strange is that it runs perfectly when executed from a linked server.

There is a known issue that has been fixed with a hotfix and in SP2 (we are using SP1) but I'm not sure it will cure it because selecting a single [detail_date] did not help.
http://support.microsoft.com/kb/923849/

I am unable to get a better trace about what's going on from the remote server (PROD) when running from DEV. All ShowPlan says is "Remote Query" and I don't have permission to run profiler on PROD.

Note that [year_source] is a computed column based on the two digit year from [detail_date] as well as the two digit [source_key]. This works great for loading because the sources come in one at a time during the night, and we don't have to wait for them all to arrive before processing or building indexes. This table is reloaded every night for the current year but prior years are only reloaded on weekends.

The actual version of this simplified code is only run monthly fortunately. It's noticeably long because similar selects are run many times so the whole routine takes 3 hours. Our workaround will either be to run from another linked prod server or use dynamic sql. Either way it involves considerable coding and work.

I hope there is some way to force the same plan when running locally.

--This takes ONE SECOND to run from the DEV server remotely against a table on the PROD server and return 900k rows .

---------------------------------------------------------------------------------------------

select count(*)

from PRODSERVER.db_template.dbo.rpt_template with (nolock)

where detail_date between '02/01/2007' and '02/23/2007'

---------------------------------------------------------------------------------------------

--This takes SEVEN MINUTES to run on the PROD server locally.

---------------------------------------------------------------------------------------------

select count(*)

from db_template.dbo.rpt_template a with (nolock)

where detail_date between '02/01/2007' and '02/23/2007'

---------------------------------------------------------------------------------------------

--If you add [year_source] for partition elimination, you are back to one second but you have to do all the sources.

---------------------------------------------------------------------------------------------

select count(*)

from db_template.dbo.rpt_template a with (nolock)

where detail_date between '02/01/2007' and '02/23/2007'

and year_source = '0701'

---------------------------------------------------------------------------------------------

--This also works so it would be a workaround for looping through the sources

---------------------------------------------------------------------------------------------

declare

@s char(8),

@e char(8),

@ys char(4),

@sql nvarchar(max)

select

@s = '20070201',

@e = '20070223',

@ys= '0701'

select @sql = '

select count(*)

from db_template.dbo.rpt_template a with (nolock)

where detail_date between "' + @s + '" and "' + @e + '"

and year_source = "' + @ys + '"'

exec sp_executesql @sql

---------------------------------------------------------------------------------------------

CREATE PARTITION SCHEME [YearSource2007PScheme] AS

PARTITION [YearSourceRange2007PFN] TO

(

[fg_template_2], -- 2005 Data

[fg_template_1], -- 2006 Data

[fg_template_0], -- 2007 Source 1

[fg_template_0], -- 2007 Source 2

[fg_template_0], -- 2007 Source 3

-- Sources 4 through 29, of which we are using 12

[fg_template_0], -- 2007 Source 30

[fg_template_0] -- Nothing

)

GO

CREATE PARTITION FUNCTION [YearSourceRange2007PFN](char(4)) AS

RANGE LEFT FOR VALUES

(

N'0599', -- 2005 Data

N'0699', -- 2006 Data

N'0701', -- 2007 Source 1

N'0702', -- 2007 Source 2

N'0703', -- 2007 Source 3

-- Sources 4 through 29, of which we are using 12

N'0730' -- 2007 Source 30

-- Nothing

)

GO

CREATE TABLE [dbo].[rpt_template](

[source_key] [tinyint] NOT NULL,

[detail_date] [smalldatetime] NOT NULL,

[year_month] [smalldatetime] NOT NULL,

-- One Hundred and Five Reporting Columns here

[ys_id] [int] IDENTITY(1,1) NOT NULL,

[year_source] AS (CONVERT([char](4),CONVERT([char](2),right(datepart(year,[detail_date]),(2)),(0))+right('00'+CONVERT([varchar](2),[source_key],(0)),(2)),(0))) PERSISTED NOT NULL

) ON [YearSource2007PScheme]([year_source])

GO

CREATE UNIQUE CLUSTERED INDEX [ix_year_source_ys_id] ON [dbo].[rpt_template]

(

[year_source] ASC,

[ys_id] ASC

) ON [YearSource2007PScheme]([year_source])

GO

CREATE NONCLUSTERED INDEX [idx0] ON [dbo].[rpt_template]

(

[year_source] ASC,

[detail_date] ASC

) ON [YearSource2007PScheme]([year_source])

GO

CREATE NONCLUSTERED INDEX [idx1] ON [dbo].[rpt_template]

(

[year_source] ASC,

[year_month] ASC

)ON [YearSource2007PScheme]([year_source])

GO

-- several other indexes

(This is not easy to format)

View Replies !

Database Not In View In New Maintenance Plan SQL 7 (options)
I recently migrated my database to SQL2005 from SQL7. It was simply a restore from a backup and was cake.

But now when I go to run a new maintenance plan I do not see my database.

I found that the database options are set to SQL7. If I change it to SQL 2005 I see the database.

What impact will this have on my data?

View Replies !

Different Query Plans For View And View Definition Statement
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View Replies !

SQL 2005 V9.0.2047 (SP1) - The Query Processor Could Not Produce A Query Plan
Hi Everyone:

*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error

"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I would have searched the forums myself, but at this moment in time, search is broken :(

If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.

It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.

fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.

Thanks in advance for other suggested examination paths.

View Replies !

Selct Distinct Returns Incorrect Results In SQL 2000
Hi, I am seeking desperate help.
We recently upgraded our SQL server from 7.0 TO 2000. Our user are using Bussines objects tools for the reports. When they use the following query " Select distinct (snapshotdate) from tablename group by snapshotdate" returns incorrect results in the meaning of the dates are missing. Whe we use different set of query " that is by using snapshotdate >= date " fundtion it returns correct information. Data is there. Why am I getting this discripence.

I ran update statistics, rebuild indexes. Optimization and integrity everything performed.
SQL 2000 service pack 2 is also applied to the server and client.

All suggestion are welcome. Seeking help sincierly.

Thanks,
Ramakrishna

View Replies !

SQL Query Incorrect Syntax Help
I am getting a SQLExcepetion error near , in this query string...so obvicously my query string is wrong...
could someone help me get this query string right please...

Thanks
"Select OrgID, OrgName From aspnet_OrgNames Where UserID = @UserID, OrgID = @OrgID"

View Replies !

Incorrect Syntax Near '4' - But No '4' In Query
I'm using the following vbScript and T-SQL and receiving a seemingly strange error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '4'.

vb Code:

Original
- vb Code

intUserID = 124

Set quizCmd = Server.CreateObject("ADODB.Command")
Set SQLConn = Server.CreateObject("ADODB.Connection")
SQLConn.Open Application("DBCONNECTION")

quizCmd.ActiveConnection = SQLConn
quizCmd.CommandText = "checkComplete"
quizCmd.CommandType = adCmdStoredProc
quizCmd.Parameters.Append quizCmd.CreateParameter("@userID", adInteger, adParamInput, 4, intUserID)
quizCmd.Parameters.Append quizCmd.CreateParameter("@status", adVarChar, adParamOutput, 6, 0)
quizCmd.Execute

intUserID = 124 Set quizCmd = Server.CreateObject("ADODB.Command")Set SQLConn = Server.CreateObject("ADODB.Connection")SQLConn.Open Application("DBCONNECTION") quizCmd.ActiveConnection = SQLConnquizCmd.CommandText = "checkComplete"quizCmd.CommandType = adCmdStoredProcquizCmd.Parameters.Append quizCmd.CreateParameter("@userID", adInteger, adParamInput, 4, intUserID)quizCmd.Parameters.Append quizCmd.CreateParameter("@status", adVarChar, adParamOutput, 6, 0)quizCmd.Execute

sql Code:

Original
- sql Code

CREATE PROCEDURE [dbo].[checkComplete]

@userID int,
@status varchar (6) output

AS

declare @complete int

Set @complete = (SELECT Count(pass) as num FROM tblAttempts WHERE userID = @userID AND pass = 1)

If (@complete = 24)
Begin
Set @status = 'OK'
End
Else
Begin
Set @status = 'NOT OK'
End
GO

CREATE PROCEDURE [dbo].[checkComplete] @userID int,@status varchar (6) output AS DECLARE @complete int SET @complete = (SELECT COUNT(pass) AS num FROM tblAttempts WHERE userID = @userID AND pass = 1) IF (@complete = 24) BEGIN SET @status = 'OK' ENDELSE BEGIN SET @status = 'NOT OK' ENDGO

For reference, I have tried changing intUserID to be a different value (e.g. 13) to ensure it is not that '4' in question - likewise for @complete = 24 (e.g. 23).

Any ideas where the 4 is coming from and what is wrong here?

View Replies !

Incorrect Query Text

I have this as my query text

SELECT MachNumber, ShiftNumber, HeatNumber, PartNumber,

     RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
     Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
     OverTime, CycleTime, ChangeTime, Date
FROM            HeatInfo
WHERE        (ShiftNumber = @ShiftNumber)

AND (Date >= @StartDate) AND (Date <= @EndDate)

This correctly returns 4 rows, which is great except that I would like to have these records Sorted by the Date. So I added the OrderBy command to the bottom of the query, like this

SELECT MachNumber, ShiftNumber, HeatNumber, PartNumber,

     RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
     Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
     OverTime, CycleTime, ChangeTime, Date
FROM            HeatInfo
WHERE        (ShiftNumber = @ShiftNumber)

AND (Date >= @StartDate) AND (Date <= @EndDate)
ORDER BY Date

This query returns 181 rows! whis is not great. What am I doing wrong here, I suppose that it is something extreamly obvious or I would probably have figured it out.

View Replies !

SQL Server 2000, Select Distinct Returns Incorrect Results.
I recently upgraded SQL 7.0 to SQL 2000. I performed update statistics and all the maintenances. When user try to use " select distinct snapshotdate from xxxxx group by snapshotdate" - the results returns with incorrect results. He is missing some date ranges in the data. I performed indexrebuild also. This didn't correct the problem.
Somebodyh please, kindly expalin to me, what is missing.

Help is greatly appreciated.

Thanks,
Ramakrishna:(

View Replies !

Backup Plan SQL 2000
Hello all,
I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.
Any help would be greatly appreciated.

thanks
Jason

View Replies !

Possibly Incorrect Query Result
/* Test table */
create table test (c1 char(1), c2 varchar(1));
insert into test values ('','');

/* Query */
select
c1,
len(c1) len_c1,
c2,
len(c2) len_c2
from test

The result of the len(c1) expression is 0. I would expect the correct result to be 1, since "c1" is a fixed-length character string type and the values are right-padded with spaces to fit the defined length, in this case 1.

I'm using SQL Server 2005.

Regards,
Ole Willy Tuv

View Replies !

Need A Reliable Backup Plan Fro SQL 2000
I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.

As a separate strategy we want to have a second backup done nightly that would involve
#1 detach the db(s)
#2 copy the mdf(s) and ldf(s) to L:xxx
#3 attach the db(s) again
#4 zip up the db(s)
#5 copy the files to a different server for storage.

I created #1, and #3 in query analyzer and saved the script.

I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?

This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.

Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.

I'm know I'm asking a lot for a first timer.
It seems we want to handle disaster recovery BEFORE we even have our first system crash.

many thanks

View Replies !

Report Services 2000 Totals On Group Are Incorrect. Summing Duplicate Values
Hello Everyone
I've created a report with a simple dataset that is similar to this

City     ,        RequestID,         Request Amount,        ClaimID,            ClaimAmount
El Monte          791                      52,982.00                 2157                    41,143.75
El Monte          3691                     11,838.00                 3140                   8,231.14
El Monte          3691                     11,838.00                 3141                   990.00
El Monte          3691                      11,838.00                3142                   2,615.00

So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:

For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.

I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.

View Replies !