AARGGHH Fulltext indexing woes...

I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ----
---
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ----
---
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
> dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied =
> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
--
>
>
>We're feverishly in QA mode here getting ready to rollout production to
SQL2K5...
There is this one query which is returning the following error:
Msg 7617, Level 16, State 1, Line 1
Query does not reference the full-text indexed table or indexed view.
I'm really at a lost here. What is wrong with this first query'
I don't actually do select tblStock.* in the production version but it
succeeds/fails consistently either way so to shorten it up....
Here is the DDL used to create the catalog...
----
----
CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc, strImagePath,
strSampleMedia, strCreators) KEY INDEX PK_tblStock;
ALTER FULLTEXT INDEX ON tblStock ENABLE;
----
----
Here is the query that does NOT work (followed by one that does...)
SELECT DISTINCT tblStock.*
FROM dbo.tblStock LEFT OUTER JOIN
dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
dbo.tblCategories ON
dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
LEFT OUTER JOIN
dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
dbo.tblToolTypes.lngToolTypePK
WHERE (dbo.tblPriceFile.dtmDateApplied =
(SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
(dbo.tblStock.blnActiveItem = 1)
and freetext(tblStock.*,'marriage')
----
(the next one works)
select distinct * from tblStock where freetext(tblStock.*,'marriage')
----
--|||I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ----
---
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ----
---
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =
> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =
> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =
> dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied =
> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
--
>
>
>

AA_DB is this sql error?

Hi all,

Our version of application is throwing AA_DB error while working on it... at first start. we are using sql 2000 with sp3a on win 2003 platform. with 4 Xenon processors and 8 GB ram.

is this error is related with sql or something else...

I checked this on net and found..(actionapps or something related with authentication of user)

pls help to rectify if possible..

Best Regards,its a new version of application we launched... earlier in old versions this error was not there...|||could you clarify the error in more detail so that someone will be able to help you.
regards,
Harshal.|||i have two server's in domain, with same configuration sql 2000 sp3a same db name, same application package... but while working on one sever application...in one module its showing me aa_db error after 10 to 15 mins randomly... we have tested sql installation, service pack reinstalled, applicaiton installation tested.
though the applicaiton is very well tested by vendor side but here we are confuse that why we are facing it..|||What's the errorlog showing?

A weird problem of Reporting Service

My company is using SQL 2000, Reporting Service SP2, Server 2003, .NET framework 1.1.

We have an ASP.NET application, working properly on one of our test machine, but when we test on another test machine, it would have the following problem:

When we want to view the PDF or EXCEL report generated by Reporting Services, we get prompted to download an ASPX file. If we choose [Open], it will use Visual Studio.NET to open it. But if we choose [Save], and change the extenstion name, it's actually the correct report file.

That means, the report is generated correctly, but we can't open it.

We have added the Content-Disposition to indicate a correct extension name, but that machine still have this weird problem. Frustrately, it works perfectly on our another machine, even without Content-Disposition.

So, we are thinking probably it's related some file system security configuration. Could anyone give me a little hint about it?

Thank you very much!

Hi, we have the same problem. Did you get anywhere with this?|||are u still looking for answer of it?

that's because the server turns on the http compression, stupid IE doesn't understand it.

one thing to work it around is, turn off the compression for aspx, it might affect too much. If that's a concern, change your report generator file from ASPX to another extension, then that extension file won't have compression on. And you can enable asp.net to handle that new extension, which is only used to generate report.

this just works fine for us

A weird problem of Reporting Service

My company is using SQL 2000, Reporting Service SP2, Server 2003, .NET framework 1.1.

We have an ASP.NET application, working properly on one of our test machine, but when we test on another test machine, it would have the following problem:

When we want to view the PDF or EXCEL report generated by Reporting Services, we get prompted to download an ASPX file. If we choose [Open], it will use Visual Studio.NET to open it. But if we choose [Save], and change the extenstion name, it's actually the correct report file.

That means, the report is generated correctly, but we can't open it.

We have added the Content-Disposition to indicate a correct extension name, but that machine still have this weird problem. Frustrately, it works perfectly on our another machine, even without Content-Disposition.

So, we are thinking probably it's related some file system security configuration. Could anyone give me a little hint about it?

Thank you very much!

Hi, we have the same problem. Did you get anywhere with this?|||are u still looking for answer of it?

that's because the server turns on the http compression, stupid IE doesn't understand it.

one thing to work it around is, turn off the compression for aspx, it might affect too much. If that's a concern, change your report generator file from ASPX to another extension, then that extension file won't have compression on. And you can enable asp.net to handle that new extension, which is only used to generate report.

this just works fine for us

A weird problem of Reporting Service

My company is using SQL 2000, Reporting Service SP2, Server 2003, .NET framework 1.1.

We have an ASP.NET application, working properly on one of our test machine, but when we test on another test machine, it would have the following problem:

When we want to view the PDF or EXCEL report generated by Reporting Services, we get prompted to download an ASPX file. If we choose [Open], it will use Visual Studio.NET to open it. But if we choose [Save], and change the extenstion name, it's actually the correct report file.

That means, the report is generated correctly, but we can't open it.

We have added the Content-Disposition to indicate a correct extension name, but that machine still have this weird problem. Frustrately, it works perfectly on our another machine, even without Content-Disposition.

So, we are thinking probably it's related some file system security configuration. Could anyone give me a little hint about it?

Thank you very much!

Hi, we have the same problem. Did you get anywhere with this?|||are u still looking for answer of it?

that's because the server turns on the http compression, stupid IE doesn't understand it.

one thing to work it around is, turn off the compression for aspx, it might affect too much. If that's a concern, change your report generator file from ASPX to another extension, then that extension file won't have compression on. And you can enable asp.net to handle that new extension, which is only used to generate report.

this just works fine for us
sql

A tricky query

I have to write a query to generate a report over some interesting
data. It's basically scheduling which days people are working. The
data looks like this:
Employee StartDate EndDate Roster
-- -- -- --
Bob 12-Jun-06 24-Jun-06 _*___**
Mary 12-Jun-06 24-Jun-06 *_*__*_
The trick is, the roster field contains a string with a _ or *
depending on wether the person is scheduled to work that day or not,
but the first character always starts on the sunday. The startdate and
enddate can be any day of the w.
In the example above, the 12-jun is a monday, so monday corresponds to
the second character in the roster string, so Bob's working and Mary's
not. The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here! Now, this
roster string could be 7, 10, 14 days long. The startDate -> endDate
could be the length of the roster string or less (only show a subset of
the roster data).
So! I need to write a query to feed a report to format this into
something like:
Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
Friday 16-Jun
-- --
-- --
--
Bob Mary
Bob
Mary
I could get the report out if I can write a query to get it to this:
Employee DateWorking
-- --
Bob 12-Jun
Bob 16-Jun
Mary 13-Jun
Mary 16-Jun
Any ideas?
Thanks!
DaveDave
Can I ask you , why not just doing such reports on the client side? T-SQL
is not good for such things
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||>> I have to write a query to generate a report over some interesting data.
<<
First of all, you are not using ISO-8601 format dates. You might want
to do that, since what you did post was ambigous as well as
non-standard, I hope you do not think that ORACLE is a standard.
Next, you might want to read a book on programming principles. We do
not do reports in the database in a tiered architecture. This is more
fundamental than SQL.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. I
would love to see the LOGICAL definition of that silly bar chart you
labeled "roster" in your narrative since it is pure display.
CREATE TABLE Roster
(employee_name VARCHAR(20) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (employee_name, start_date));
This is usually done with a Calendar table:
SELECT R.employee_name, C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Roster AS R
ON C.cal_date BETWEEN R.start_date AND R.end_date
WHERE C.cal_date BETWEEN @.my_start_date AND @.my_end_date;
Google for other uses of the Calendar and auxiliary tables.|||Dude, you don't do things by halves... <g>
I can't think of any way to do it without iterating over each employee...
step 1:
select employee, startDate, abs(datediff(day, startDate, endDate)) as
dateIterations, Roster
into #tmp1
from theTable
step 2:
-- loop the temp table
-- for each row
-- loop the roster string from 1 to dateIterations
-- if substring(roster, index, 1) = '*'
-- insert a row for employee, dateadd(day, index, startdate)
step 3:
join Thetable to #tmp1 by employee
It sounds like you need to denormalize the results so you could also start
by creating a date table from min(startDate) to max(endDate) first with all
the dates inbetween,
then outer join to that by date to #tmp1 and theTable
[Sidenote]
This is one of the reasons why normalization is a good thing. It's
extremely hard to pull peices-parts of data from a column that contains
multiple data points...
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||This data arrangement - I was going to say design, but that did not
seem applicable - is sheer madness, of course.
One bit of your description has me particularly . Look at
these three bits of the description:
- "the first character always starts on the sunday"
- "The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here!"
- " this roster string could be 7, 10, 14 days long."
With a string of length 7 or 14 I can see how it can wrap around and
still start on Sunday. With a length of 10 I can not see how this is
possible.
A second point of confusion is the sample data. The date ranges give
are far longer than the strings . This makes no sense.
Anyway, I believe that once those issues have been resolved the
following will work. Note that I changed the date range from the test
data you provided so that it did not exceed the length of the string.
CREATE TABLE Numbers
(nbr int not null)
INSERT Numbers
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT
5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT
10 UNION
SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '12 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '12 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
--A view that lines up the string with the StartDate
CREATE View Madness_V
AS
SELECT Employee, StartDate, EndDate,
SUBSTRING(RTRIM(Roster) + Roster,
Datepart(wday,StartDate),
DateDiff(day,StartDate,EndDate)) as Shifted
FROM Madness
GO
--Lets see what the view gives us
SELECT *
FROM Madness_V
--Now for the desired results
SELECT M.Employee,
DATEADD(Day,Nbr-1,StartDate) as WorkingDate
FROM Madness_V as M
JOIN Numbers as N
ON Nbr <= Datediff(day,M.StartDate,M.EndDate)
WHERE SUBSTRING(Shifted,Nbr,1) = '*'
order by 1, 2
Roy Harvey
Beacon Falls, CT
On 21 Jun 2006 06:00:05 -0700, "Dave Newman" <ddangerous@.gmail.com>
wrote:

>I have to write a query to generate a report over some interesting
>data. It's basically scheduling which days people are working. The
>data looks like this:
>
>Employee StartDate EndDate Roster
>-- -- -- --
>Bob 12-Jun-06 24-Jun-06 _*___**
>Mary 12-Jun-06 24-Jun-06 *_*__*_
>The trick is, the roster field contains a string with a _ or *
>depending on wether the person is scheduled to work that day or not,
>but the first character always starts on the sunday. The startdate and
>enddate can be any day of the w.
>In the example above, the 12-jun is a monday, so monday corresponds to
>the second character in the roster string, so Bob's working and Mary's
>not. The roster string wraps around, so the first character of the
>roster string actually corresponds with the enddate here! Now, this
>roster string could be 7, 10, 14 days long. The startDate -> endDate
>could be the length of the roster string or less (only show a subset of
>the roster data).
>So! I need to write a query to feed a report to format this into
>something like:
>Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
>Friday 16-Jun
>-- --
>-- --
>--
>Bob Mary
> Bob
> Mary
>I could get the report out if I can write a query to get it to this:
>Employee DateWorking
>-- --
>Bob 12-Jun
>Bob 16-Jun
>Mary 13-Jun
>Mary 16-Jun
>
>Any ideas?
>Thanks!
>Dave|||I don't understand your definition of how a roster day maps to an actual
date so I can't calculate that, but you can use recursion to "unflatten"
roster string into a row for each '*' and go from there. I also don't know
what the primary key of your table is, but I assume that name+start date
will work for that.
Create table #sched
(
name NVARCHAR(20),
start DATETIME,
[end] DATETIME,
roster NVARCHAR(14)
)
--TRUNCATE TABLE #sched
INSERT INTO #sched VALUES (N'bob', '12-JUN-06', '24-JUN-06', N'_*--**')
INSERT INTO #sched VALUES (N'mary', '12-JUN-06', '24-JUN-06', N'*_*__*_')
WITH workingDays
AS
(
-- get the first roster day for every entry in table
SELECT name, CHARINDEX(N'*', roster) AS rosterDay, start, roster FROM #sched
WHERE CHARINDEX(N'*', roster, 0) > 0
UNION ALL
-- now get subsequent roster days
SELECT S.name, CHARINDEX(N'*', S.roster, W.rosterDay + 1) AS rosterDay,
S.start,
S.roster from #sched AS S
JOIN workingDays AS W ON W.name = S.name and W.start = S.start AND
CHARINDEX(N'*', S.roster, W.rosterDay + 1) > 0
)
SELECT name, rosterDay, roster, start,
DATEADD(day, rosterDay-1, start) as DateWorking
FROM workingDays
ORDER BY name, rosterDay
name rosterDay roster start Date
Working
-- -- -- -- --
--
bob 2 _*--** 2006-06-12 00:00:00.000 2006
-06-13
00:00:00.000
bob 6 _*--** 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
bob 7 _*--** 2006-06-12 00:00:00.000 2006
-06-18
00:00:00.000
mary 1 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-12
00:00:00.000
mary 3 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-14
00:00:00.000
mary 6 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
If you replace the DateWorking column with a UDF that takes as input a roste
rDay
and start and produces a working date I think you will have what you want.
Dan

> I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate
> and enddate can be any day of the w.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset
> of the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
> Any ideas?
> Thanks!
> Dave
>|||Hi Dave,
It would have been great if you had given the ddl. Anyways, here is the
solution.
Let me know if this works..
The ddl and ur sample data first
create table t1 (Employee varchar(10), StartDate datetime,
EndDate datetime, Roster varchar(10))
insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
'_*___**')
insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
'*_*__*_')
And here is the query. You need a temp table so that I don't loop through
that roster or whatever
--I have assumed that the size of the roster is 30 characters. If yit can be
100 or 1000 increase it accordingly
select top 30 identity(int,0,1) as num into #temp from sysobjects
--query
-- This is to remove any ambiguity
set datefirst 7
select Employee, StartDate - datepart(dw,startdate) + num + 1
from t1, #temp
where
substring(Roster,num%len(roster) + 1,1) = '*'
and StartDate - datepart(dw,startdate) + num + 1 between startdate and endda
te
Let me know if this was what you wanted.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi There,
I think this may solve your problem
Try it and let me know if it helped
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '13 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '13 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
Select identity(int,1,1) myid into Numbers from sysobjects
Select Case When myid<pos Then startdate+myid-1
Else startdate+pos-datepart(dw,startdate)
end ,
Employee, Case when substring(roster,pos,1)='*' then 'Working' else
'Not' End
From
(
Select myid,
case when (datepart(dw,startdate)+myid-1)%datalength(roster)= 0 then
datalength(roster) else
(datepart(dw,startdate)+myid-1)%datalength(roster) end Pos,
Employee,
Startdate,
Enddate,
Roster
>From Madness,Numbers
where myid <= datalength(Roster)
) XY order by 2,1
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Omnibuzz wrote:
> Hi Dave,
> It would have been great if you had given the ddl. Anyways, here is the
> solution.
> Let me know if this works..
> The ddl and ur sample data first
>
> create table t1 (Employee varchar(10), StartDate datetime,
> EndDate datetime, Roster varchar(10))
> insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
> '_*___**')
> insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
> '*_*__*_')
>
> And here is the query. You need a temp table so that I don't loop through
> that roster or whatever
> --I have assumed that the size of the roster is 30 characters. If yit can
be
> 100 or 1000 increase it accordingly
> select top 30 identity(int,0,1) as num into #temp from sysobjects
>
> --query
> -- This is to remove any ambiguity
> set datefirst 7
> select Employee, StartDate - datepart(dw,startdate) + num + 1
> from t1, #temp
> where
> substring(Roster,num%len(roster) + 1,1) = '*'
> and StartDate - datepart(dw,startdate) + num + 1 between startdate and end
date
> Let me know if this was what you wanted.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/sql

A transport-level error has occurred when sending the request to the server

I've tried to search on the web for a solution for this error but i didn't find any working solutions for this problem. We have 2 servers. The first one is the server we use to develop our ASP.NET 2 application. On this server we don't have this error. On the other server (use by our client) we have this error sometime and i don't know why. Both servers have the same configuration and both application have the same web.config file.

I've tried to add a try-catch and retry the query when the error occured. This seems to be working but we don't want to have to change all our connections and since we don't have this problem on the other server we want to find the source of the problem.

Any idea ?

Thanks !

The most frequent cause of this error is restarting SQL Server. Another known cause is altering a database, e.g. from READ_ONLY to READ_WRITE, or taking a database offline/online.

Do any of these apply to your case?

A temporary database

Hi !

I use Sql 2000 Server as a database and an ASP.NET application with VB.NET language.

Now I am working with three pages with one form in every page that allows to register a user. To accomplish the registration the user needs to fill all the three pages, but now I am sending the data to the database in every page, so if a user leaves the process before reaching the third page it will have an invalid user entry into the database that I don t want. To avoid this I was recommended to store the in a temporary database file. I have been searching information about this but I have not found it.

Somebody can help me finding the necessary documentation to achieve it please?

ThanksOriginally posted by cesark29
Hi !

I use Sql 2000 Server as a database and an ASP.NET application with VB.NET language.

Now I am working with three pages with one form in every page that allows to register a user. To accomplish the registration the user needs to fill all the three pages, but now I am sending the data to the database in every page, so if a user leaves the process before reaching the third page it will have an invalid user entry into the database that I don t want. To avoid this I was recommended to store the in a temporary database file. I have been searching information about this but I have not found it.

Somebody can help me finding the necessary documentation to achieve it please?

Thanks

I have not worked on asp or any other front ends but what i feel is, u should hold all the data in these previous pages in some hidden variables or something and pass it when the user completes the whole registration process.|||I know that passing the data from one page to another is an option, but I think that is better to pass the minimum data as you can between pages. Anyway if you think that is a good option explain me in which cases a temporary database is used.

Thanks !|||Originally posted by cesark29
I know that passing the data from one page to another is an option, but I think that is better to pass the minimum data as you can between pages. Anyway if you think that is a good option explain me in which cases a temporary database is used.

Thanks !
using database for storing these values will make the page slower since it will have to go to the database three times.Instead, it is very common to use sessions for such scenarios.
Regarding temperory databases, they are used internally to hold temperory tables and temperory stored procedures.they are used to store any work tables or temperory tables used while processing
to know more about the temp database check out BOL under System databases and data.|||Good harshal, thank you very much.

A strange problem with updatable partitioned view.

Hi all,
I am working on a distributed database. I defined linked
servers, partitioned views etc. I can delete/insert/update
data from the view. Now the problem is if I add more ID
ranges to the partitioning column in the check, sometimes
it worked or sometimes it didn't. See the following sample
code:
-- Create linked server SERVER0,SERVER1 on two SQL
servers.
-- SERVER0 one one machine
exec sp_addlinkedserver 'SERVER0', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC OUT', @.optvalue='TRUE'
-- SERVER1 on another machine
exec sp_addlinkedserver 'SERVER1', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC OUT', @.optvalue='TRUE'
-- Create database Test_DB on each server.
-- ON SERVER1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
[ObjectID] <= 100)
GO
-- ON Server1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 )
GO
-- ON SERVER0: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
GO
--ON SERVER1: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
01-01',1)
It succeeds
-- ON both server, drop the checks
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 )
GO
-- On server1, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 )
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
01-01',1)
It succeeds
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
GO
-- On Server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
600)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
01-01',1)
It succeeds
-- On any server,
Delete vwtest
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
[ObjectID] between 40075 and 50074)
GO
-- On server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
[ObjectID] BETWEEN 50075 and 60074)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
01-01',1)
It will fail. the error message is "UNION ALL view vwtest
is not updatable becuase a partitioning column is not
found."
I am totally lost. Anyone knows how SQL server decides one
column is a partitioning or not. Here I used the same rule
but the result is different.
Any ideas? Thanks in advance.Peter,
did not go through your detailed post. However, I bet that you did your
modification with EM. It is known that when you do such changes in EM to
updateable partitioned view the EM does not do it right. Try use QA. If it
does not work, try recreate the view in QA.
HTH
Quentin
"Peter" <phe@.Visualplant.com> wrote in message
news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> Hi all,
> I am working on a distributed database. I defined linked
> servers, partitioned views etc. I can delete/insert/update
> data from the view. Now the problem is if I add more ID
> ranges to the partitioning column in the check, sometimes
> it worked or sometimes it didn't. See the following sample
> code:
> -- Create linked server SERVER0,SERVER1 on two SQL
> servers.
> -- SERVER0 one one machine
> exec sp_addlinkedserver 'SERVER0', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- SERVER1 on another machine
> exec sp_addlinkedserver 'SERVER1', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- Create database Test_DB on each server.
> -- ON SERVER1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> [ObjectID] <= 100)
> GO
> -- ON Server1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 )
> GO
>
> -- ON SERVER0: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> GO
> --ON SERVER1: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
> 01-01',1)
> It succeeds
> -- ON both server, drop the checks
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 )
> GO
> -- On server1, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 )
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
> 01-01',1)
> It succeeds
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> GO
> -- On Server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
> 600)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
> 01-01',1)
> It succeeds
> -- On any server,
> Delete vwtest
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
> [ObjectID] between 40075 and 50074)
> GO
> -- On server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
> [ObjectID] BETWEEN 50075 and 60074)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
> 01-01',1)
> It will fail. the error message is "UNION ALL view vwtest
> is not updatable becuase a partitioning column is not
> found."
> I am totally lost. Anyone knows how SQL server decides one
> column is a partitioning or not. Here I used the same rule
> but the result is different.
>
> Any ideas? Thanks in advance.
>|||Thanks for your reply.
However, I didn't change the constraint from EM. What I
did is that drop the constraint for all servers, then
create the constraint for all servers from QA. It worked
in some cases. It seems if I have more ID ranges or I have
ID ranges with negative value, it will fail. I tried to
recreate the view, it didn't work too.
The code I posted is exactly what I ran in QA.
>--Original Message--
>Peter,
>did not go through your detailed post. However, I bet
that you did your
>modification with EM. It is known that when you do such
changes in EM to
>updateable partitioned view the EM does not do it right.
Try use QA. If it
>does not work, try recreate the view in QA.
>HTH
>Quentin
>"Peter" <phe@.Visualplant.com> wrote in message
>news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> Hi all,
>> I am working on a distributed database. I defined
linked
>> servers, partitioned views etc. I can
delete/insert/update
>> data from the view. Now the problem is if I add more ID
>> ranges to the partitioning column in the check,
sometimes
>> it worked or sometimes it didn't. See the following
sample
>> code:
>> -- Create linked server SERVER0,SERVER1 on two SQL
>> servers.
>> -- SERVER0 one one machine
>> exec sp_addlinkedserver 'SERVER0', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- SERVER1 on another machine
>> exec sp_addlinkedserver 'SERVER1', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- Create database Test_DB on each server.
>> -- ON SERVER1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> [ObjectID] <= 100)
>> GO
>> -- ON Server1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 )
>> GO
>>
>> -- ON SERVER0: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> --ON SERVER1: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(10,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(110,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server, drop the checks
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 )
>> GO
>> -- On server1, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 )
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(11,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(111,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> GO
>> -- On Server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
>= -
>> 600)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(13,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(113,'2003-
>> 01-01',1)
>> It succeeds
>> -- On any server,
>> Delete vwtest
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
or
>> [ObjectID] between 40075 and 50074)
>> GO
>> -- On server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
OR
>> [ObjectID] BETWEEN 50075 and 60074)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(17,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(117,'2003-
>> 01-01',1)
>> It will fail. the error message is "UNION ALL view
vwtest
>> is not updatable becuase a partitioning column is not
>> found."
>> I am totally lost. Anyone knows how SQL server decides
one
>> column is a partitioning or not. Here I used the same
rule
>> but the result is different.
>>
>> Any ideas? Thanks in advance.
>
>.
>|||Peter,
Oops.
I saw you used Alter Table to add the constraint. Did you try to create the
constraint together with the table creation? Try that.
Quentin
"peter" <phe@.VisualPlant.com> wrote in message
news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
> Thanks for your reply.
> However, I didn't change the constraint from EM. What I
> did is that drop the constraint for all servers, then
> create the constraint for all servers from QA. It worked
> in some cases. It seems if I have more ID ranges or I have
> ID ranges with negative value, it will fail. I tried to
> recreate the view, it didn't work too.
> The code I posted is exactly what I ran in QA.
>
> >--Original Message--
> >Peter,
> >
> >did not go through your detailed post. However, I bet
> that you did your
> >modification with EM. It is known that when you do such
> changes in EM to
> >updateable partitioned view the EM does not do it right.
> Try use QA. If it
> >does not work, try recreate the view in QA.
> >
> >HTH
> >
> >Quentin
> >
> >"Peter" <phe@.Visualplant.com> wrote in message
> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> >> Hi all,
> >>
> >> I am working on a distributed database. I defined
> linked
> >> servers, partitioned views etc. I can
> delete/insert/update
> >> data from the view. Now the problem is if I add more ID
> >> ranges to the partitioning column in the check,
> sometimes
> >> it worked or sometimes it didn't. See the following
> sample
> >> code:
> >>
> >> -- Create linked server SERVER0,SERVER1 on two SQL
> >> servers.
> >> -- SERVER0 one one machine
> >> exec sp_addlinkedserver 'SERVER0', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >> -- SERVER1 on another machine
> >> exec sp_addlinkedserver 'SERVER1', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >>
> >> -- Create database Test_DB on each server.
> >> -- ON SERVER1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> >> [ObjectID] <= 100)
> >> GO
> >>
> >> -- ON Server1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 )
> >> GO
> >>
> >>
> >> -- ON SERVER0: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> --ON SERVER1: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (10,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (110,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server, drop the checks
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 )
> >> GO
> >> -- On server1, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 )
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (11,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (111,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> >> GO
> >> -- On Server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
> >= -
> >> 600)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (13,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (113,'2003-
> >> 01-01',1)
> >> It succeeds
> >>
> >> -- On any server,
> >> Delete vwtest
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
> or
> >> [ObjectID] between 40075 and 50074)
> >> GO
> >> -- On server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
> OR
> >> [ObjectID] BETWEEN 50075 and 60074)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (17,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (117,'2003-
> >> 01-01',1)
> >>
> >> It will fail. the error message is "UNION ALL view
> vwtest
> >> is not updatable becuase a partitioning column is not
> >> found."
> >>
> >> I am totally lost. Anyone knows how SQL server decides
> one
> >> column is a partitioning or not. Here I used the same
> rule
> >> but the result is different.
> >>
> >>
> >> Any ideas? Thanks in advance.
> >>
> >
> >
> >.
> >|||The result is the same. I have other aprtitioned tables
that work well. But the partitioned column of this table
has negative IDs. I think this is the reason.
>--Original Message--
>Peter,
>Oops.
>I saw you used Alter Table to add the constraint. Did
you try to create the
>constraint together with the table creation? Try that.
>Quentin
>
>"peter" <phe@.VisualPlant.com> wrote in message
>news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
>> Thanks for your reply.
>> However, I didn't change the constraint from EM. What I
>> did is that drop the constraint for all servers, then
>> create the constraint for all servers from QA. It worked
>> in some cases. It seems if I have more ID ranges or I
have
>> ID ranges with negative value, it will fail. I tried to
>> recreate the view, it didn't work too.
>> The code I posted is exactly what I ran in QA.
>>
>> >--Original Message--
>> >Peter,
>> >
>> >did not go through your detailed post. However, I bet
>> that you did your
>> >modification with EM. It is known that when you do
such
>> changes in EM to
>> >updateable partitioned view the EM does not do it
right.
>> Try use QA. If it
>> >does not work, try recreate the view in QA.
>> >
>> >HTH
>> >
>> >Quentin
>> >
>> >"Peter" <phe@.Visualplant.com> wrote in message
>> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> >> Hi all,
>> >>
>> >> I am working on a distributed database. I defined
>> linked
>> >> servers, partitioned views etc. I can
>> delete/insert/update
>> >> data from the view. Now the problem is if I add more
ID
>> >> ranges to the partitioning column in the check,
>> sometimes
>> >> it worked or sometimes it didn't. See the following
>> sample
>> >> code:
>> >>
>> >> -- Create linked server SERVER0,SERVER1 on two SQL
>> >> servers.
>> >> -- SERVER0 one one machine
>> >> exec sp_addlinkedserver 'SERVER0', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >> -- SERVER1 on another machine
>> >> exec sp_addlinkedserver 'SERVER1', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >>
>> >> -- Create database Test_DB on each server.
>> >> -- ON SERVER1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> >> [ObjectID] <= 100)
>> >> GO
>> >>
>> >> -- ON Server1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 )
>> >> GO
>> >>
>> >>
>> >> -- ON SERVER0: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> --ON SERVER1: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (10,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (110,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server, drop the checks
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 )
>> >> GO
>> >> -- On server1, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 )
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (11,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (111,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> >> GO
>> >> -- On Server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and
[ObjectID]
>> >= -
>> >> 600)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (13,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (113,'2003-
>> >> 01-01',1)
>> >> It succeeds
>> >>
>> >> -- On any server,
>> >> Delete vwtest
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
>> or
>> >> [ObjectID] between 40075 and 50074)
>> >> GO
>> >> -- On server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and
40074
>> OR
>> >> [ObjectID] BETWEEN 50075 and 60074)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (17,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (117,'2003-
>> >> 01-01',1)
>> >>
>> >> It will fail. the error message is "UNION ALL view
>> vwtest
>> >> is not updatable becuase a partitioning column is not
>> >> found."
>> >>
>> >> I am totally lost. Anyone knows how SQL server
decides
>> one
>> >> column is a partitioning or not. Here I used the same
>> rule
>> >> but the result is different.
>> >>
>> >>
>> >> Any ideas? Thanks in advance.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I have the same problem. My view is local and I can
insert using table names, but only read using view.

A significant part of sql server process memory has been paged out.

A significant part of sql server process memory has been paged out.
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?
Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>
|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:

>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?
|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>
|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?
|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.
|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegro ups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>
|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> 1) You did not answer my question about other services/apps running on this
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegro ups.com...
>
>
>
>
>
>
>
>
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel

A significant part of sql server process memory has been paged out.

A significant part of sql server process memory has been paged out.
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:
>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
--
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> > This may result in a performance degradation. Duration: 0 seconds.
> > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> > 50%.
> > Has anyone been able to solve this problem onSQLServerStd Edition ?
> > I know the OS is trimming theMemorybut apparently I have been
> > unable to find a way for this to stop.
> > "lock pages inmemory" is disregarded in Std Edition.
> > Any thoughts ?
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > Are other processes running on theserver? This is suboptimal.
> > How much RAM total? If < max allowed, add ram.
> > Make sure your drivers and OS andsqlserverare fully patched. There are
> > numerous known bugs in the wild that will flush ram.
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > <isyourfri...@.gmail.com> wrote in message
> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> > > This may result in a performance degradation. Duration: 0 seconds.
> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> > > 50%.
> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
> > > I know the OS is trimming theMemorybut apparently I have been
> > > unable to find a way for this to stop.
> > > "lock pages inmemory" is disregarded in Std Edition.
> > > Any thoughts ?
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
--
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
>> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>>
>> > Are other processes running on theserver? This is suboptimal.
>> > How much RAM total? If < max allowed, add ram.
>> > Make sure your drivers and OS andsqlserverare fully patched. There are
>> > numerous known bugs in the wild that will flush ram.
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> > <isyourfri...@.gmail.com> wrote in message
>> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
>> > > This may result in a performance degradation. Duration: 0 seconds.
>> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
>> > > 50%.
>> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
>> > > I know the OS is trimming theMemorybut apparently I have been
>> > > unable to find a way for this to stop.
>> > > "lock pages inmemory" is disregarded in Std Edition.
>> > > Any thoughts ?
>> The Box has 12GB of RAM.
>> SQL Server max memory is set to 8GB.
>> Windows is patched to SP2.
>> I am copying though "very large" backup files (around 70~100GB ) from
>> and to the box.
>> Is there *anything* I can do?
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) You did not answer my question about other services/apps running on this
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> > On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> >> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> >> > Are other processes running on theserver? This is suboptimal.
> >> > How much RAM total? If < max allowed, add ram.
> >> > Make sure your drivers and OS andsqlserverare fully patched. There are
> >> > numerous known bugs in the wild that will flush ram.
> >> > --
> >> > TheSQLGuru
> >> > President
> >> > Indicium Resources, Inc.
> >> > <isyourfri...@.gmail.com> wrote in message
> >> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> >> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> >> > > This may result in a performance degradation. Duration: 0 seconds.
> >> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> >> > > 50%.
> >> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
> >> > > I know the OS is trimming theMemorybut apparently I have been
> >> > > unable to find a way for this to stop.
> >> > > "lock pages inmemory" is disregarded in Std Edition.
> >> > > Any thoughts ?
> >> The Box has 12GB of RAM.
> >> SQL Server max memory is set to 8GB.
> >> Windows is patched to SP2.
> >> I am copying though "very large" backup files (around 70~100GB ) from
> >> and to the box.
> >> Is there *anything* I can do?
> > I have to correct the data:
> > The Box has 16GB of RAM.
> > SQL Server max memory is 10GB
> > The rest is the same.
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel