SQL server questions: strange

Showing posts with label strange. Show all posts

Thursday, March 22, 2012

A vexing issue

Strange problem that I just noticed. I use a handful of databases in SQL2005
Express for development. All of these databases except the one I've been
using most recently "cannot be opened due to inaccessible files or
insufficient memory'.
All of the databases show "recovery_pending" for status.
I tried to set them online with alter database, but get the message:
"File activation failure. The physical file name "...\mydb_log.ldf" may be
incorrect."
Looking at all of the "recovery_pending" databases, I see that all of the
log files are showing as "may be incorrect."
sp_helpdb gives me the message: "No permission to access database <mydb>"
DBCC CheckDB gives me the same message.
Thinking back through what has occured in the last few months (since these
were last used), I've done some minor system maintenance such as defrag and
Windows Updates, but nothing else has been changed. And the files still
reside in the directory where the log file is pointed to.
I'm baffled as to how to get these out of recovery, so any advice is
appreciated.Recovery Pending means that SQL Server need to do recovery for the database to bring it to a
consistent state. Recovery consists of REDO and UNDO, both parts are based on the entries in the
transaction log (ldf file). SQL Server feel that the ldf files are fishy or something similar, and
hence it cannot do recovery.
In most cases when I see something similar, it was somebody who deleted the log file or used some
other tool to reduce file size. If you are 100% certain that something like this didn't happen, then
you should carefully investigate the SQL Server errorlog file for all related error messages. Also
investigate the Windows eventlog for clues to what has happened to these files. Int he end, if you
cannot get these files back to healthy state, you are in for RESTORE DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Strange problem that I just noticed. I use a handful of databases in SQL2005 Express for
> development. All of these databases except the one I've been using most recently "cannot be opened
> due to inaccessible files or insufficient memory'.
> All of the databases show "recovery_pending" for status.
> I tried to set them online with alter database, but get the message:
> "File activation failure. The physical file name "...\mydb_log.ldf" may be incorrect."
> Looking at all of the "recovery_pending" databases, I see that all of the log files are showing as
> "may be incorrect."
> sp_helpdb gives me the message: "No permission to access database <mydb>"
> DBCC CheckDB gives me the same message.
> Thinking back through what has occured in the last few months (since these were last used), I've
> done some minor system maintenance such as defrag and Windows Updates, but nothing else has been
> changed. And the files still reside in the directory where the log file is pointed to.
> I'm baffled as to how to get these out of recovery, so any advice is appreciated.
>
>|||Thanks for the ideas Tibor. Nothing I could do would get those DBs out of
"recovery pending". But tonight, on the off-chance that it might work, I
went ahead and tried to detach. It gave me an error message that it could
not close the files, but in fact, once I refreshed, it HAD detached the
files! Just to confirm that they were actually detached, I created a new
directory and moved the .mdf and .ldf files in there with no problem. I then
re-attached and the previously in-recovery databases are functioning
normally.
It certainly seems strange that Microsoft did not provide an escape hatch
for this "Recovery Pending", i.e., in limbo, scenario.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
> Recovery Pending means that SQL Server need to do recovery for the
> database to bring it to a consistent state. Recovery consists of REDO and
> UNDO, both parts are based on the entries in the transaction log (ldf
> file). SQL Server feel that the ldf files are fishy or something similar,
> and hence it cannot do recovery.
> In most cases when I see something similar, it was somebody who deleted
> the log file or used some other tool to reduce file size. If you are 100%
> certain that something like this didn't happen, then you should carefully
> investigate the SQL Server errorlog file for all related error messages.
> Also investigate the Windows eventlog for clues to what has happened to
> these files. Int he end, if you cannot get these files back to healthy
> state, you are in for RESTORE DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Earl" <earl@.nospam.com> wrote in message
> news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Strange problem that I just noticed. I use a handful of databases in
>> SQL2005 Express for development. All of these databases except the one
>> I've been using most recently "cannot be opened due to inaccessible files
>> or insufficient memory'.
>> All of the databases show "recovery_pending" for status.
>> I tried to set them online with alter database, but get the message:
>> "File activation failure. The physical file name "...\mydb_log.ldf" may
>> be incorrect."
>> Looking at all of the "recovery_pending" databases, I see that all of the
>> log files are showing as "may be incorrect."
>> sp_helpdb gives me the message: "No permission to access database <mydb>"
>> DBCC CheckDB gives me the same message.
>> Thinking back through what has occured in the last few months (since
>> these were last used), I've done some minor system maintenance such as
>> defrag and Windows Updates, but nothing else has been changed. And the
>> files still reside in the directory where the log file is pointed to.
>> I'm baffled as to how to get these out of recovery, so any advice is
>> appreciated.
>>
>|||> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.
What would the escape hatch be?
It is important to differentiate between a "real" Recovery Pending (RP) situation and a "false" one.
It seems you had a false RP situation. By "false" I mean that SQL server did already complete
recovery work, which is why you could detach and attach the databases without SQL Server
complaining. If you had a "real" RP situation, then SQL Server would *not* allow you to attach a
database for which recovery cannot complete. Why you experienced this false RP, I don't know.
Sometimes I see users who don't refresh the GUI, quite simply. There could be other things as well,
of course, I can't say. One option would have been to open a case with MS and have them sort out
whether it was a real or false RP case and if it was a false one sort out the bug in the product
that lead to this false RP situation.
There is a reason why there isn't an escape hatch for a *real* RP situation (almost, see end of this
paragraph) . It would leave the database in an inconsistent state. The data is inconsistent from a
logical viewpoint (foreign key and other constraints cannot be trusted, half-completed transactions
etc). But also from a physical viewpoint (system tables modifications half-done). Basically you
would have a useless database. This last two weeks I have worked with such a database. I've spent
two weeks to get inconsistent data out of 3 tables (yep, only 3 tables over two weeks). And I petty
the soul who will try to re-integrate this crap into the production database (which was restored
from a 1 month old backup). As you can imagine, this isn't something that MS want to expose to
normal users out there. If you do feel adventurous, you can read up on "Emergency mode". This is
your escape hatch. This should not be used unless you are en expert in SQL server, and you prefer
inconsistent data over your most recent backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:OYbl276jIHA.4940@.TK2MSFTNGP02.phx.gbl...
> Thanks for the ideas Tibor. Nothing I could do would get those DBs out of "recovery pending". But
> tonight, on the off-chance that it might work, I went ahead and tried to detach. It gave me an
> error message that it could not close the files, but in fact, once I refreshed, it HAD detached
> the files! Just to confirm that they were actually detached, I created a new directory and moved
> the .mdf and .ldf files in there with no problem. I then re-attached and the previously
> in-recovery databases are functioning normally.
> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
>> Recovery Pending means that SQL Server need to do recovery for the database to bring it to a
>> consistent state. Recovery consists of REDO and UNDO, both parts are based on the entries in the
>> transaction log (ldf file). SQL Server feel that the ldf files are fishy or something similar,
>> and hence it cannot do recovery.
>> In most cases when I see something similar, it was somebody who deleted the log file or used some
>> other tool to reduce file size. If you are 100% certain that something like this didn't happen,
>> then you should carefully investigate the SQL Server errorlog file for all related error
>> messages. Also investigate the Windows eventlog for clues to what has happened to these files.
>> Int he end, if you cannot get these files back to healthy state, you are in for RESTORE DATABASE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Earl" <earl@.nospam.com> wrote in message news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Strange problem that I just noticed. I use a handful of databases in SQL2005 Express for
>> development. All of these databases except the one I've been using most recently "cannot be
>> opened due to inaccessible files or insufficient memory'.
>> All of the databases show "recovery_pending" for status.
>> I tried to set them online with alter database, but get the message:
>> "File activation failure. The physical file name "...\mydb_log.ldf" may be incorrect."
>> Looking at all of the "recovery_pending" databases, I see that all of the log files are showing
>> as "may be incorrect."
>> sp_helpdb gives me the message: "No permission to access database <mydb>"
>> DBCC CheckDB gives me the same message.
>> Thinking back through what has occured in the last few months (since these were last used), I've
>> done some minor system maintenance such as defrag and Windows Updates, but nothing else has been
>> changed. And the files still reside in the directory where the log file is pointed to.
>> I'm baffled as to how to get these out of recovery, so any advice is appreciated.
>>
>>
>sql

a very very strange sql bug (?)

I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
Felix
I often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov
|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov

a very very strange sql bug (?)

I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId = View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov

a very very strange sql bug (?)

I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK
2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause
.
> Bojidar Alexandrov

A Very Strange Problem

I have a simple query which ran fine for the last 12 months. All of sudden, it's dragging and couldn't finish in 6 hours. I tried to trace down where the problem resided but it's such a simple query there is nothing to break down. By chance, I commented out all the column names and replace with ' SELECT * ', the query finished in 14 seconds. Once I replace the * with column names, it ran over 20 minutes and I had to cancel it because it doesn't seem to be returning any results. Any help would be appreciated. Thanks.DBCC Checktable|||Thanks. I ran the check and there was no erros. Any more that I can try?|||Have a look at the estmated execution plan of both queries. Most likely there is a difference. You may need to either update statistics on some of the underlying tables, or clear the procedure cache.|||The two execution plans are the same. I cleared the procedure cache, but the problem continues. Any more ideas?|||When was the last time the box was bounced?|||If it's so simple, why don't you post the code?|||Are you certain it is the same execution plan? I can not think how the two execution times (< 1 minute vs > 20 minutes) can be reconciled with the same plan. Did you comment out a group by clause, or a bunch of aggregates? Maybe user defined function calls?|||I found what's wrong but not quite sure how to resolve it at this moment. The database is actually replicated from the publisher. There is another subscriber(server) pulling in the exact same data from the publisher. When I ran the same query(with column names) it only took 20 seconds. Then I check the execution plan on that server, sure enough it's totally different. It's 85% bookmark, which means it full use the clustered index. Yet, on the one that runs forever, it is 58% scan. I check all the tables and indexes. There's no difference. I update the stats yet it is still running forever. Any one has any more suggestions? I did restart the box this morning, by the way.|||So on one server the query takes 20 seconds, while on the other, it takes more than 20 minutes? Are the subscriptions exactly the same? Or are both servers getting different slices of the data?|||Both subscriptions are from the same publication, and they are both full subscriptions without any filters at all. One is the main reporting server, the other is a backup right now. The configurations are almost exactly the same. Only the horsepower is different. One is an 8-way box with 20 GB memory, the other is a 4-way box with 8 GB memory. Both run Win 2003 and SQL 2000 sp3a with AWE on. Yet the same query was interpreted differently.|||Can take help of PROFILER to see the activity while running this query.
ANy differences between 2 servers settings such as db-option, recovery model etc.

Tuesday, March 20, 2012

A T-SQL CURSOR issue, need help

Hi there,
I have a very strange problem when using a t-sql cursor, appritiated if someone could explain why.

When executing the SQL string to fill the cursor, everything works fine, nothin wrong so far

But when executing the whole query (traversing the sql string result) I experience the following:

First the code for the cursor

USE pubs
GO

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

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

SET @.iCounter = 1

OPEN authors_cursor

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

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

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN

-- Concatenate and display the current values in the variables.
PRINT "No: " + CAST(@.iCounter AS varchar(3)) + " Author: " + @.au_fname + " " + @.au_lname

SET @.iCounter = iCounter + 1

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

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

As I said the SQL query to fill the cursor works fine, but the result from the whole query above is this!?

No: 1 Author: Abraham Bennet
No: 2 Author: Reginald Blotchet-Halls
No: 3 Author: Abraham Bennet
No: 4 Author: Reginald Blotchet-Halls

No: 6 Author: Reginald Blotchet-Halls
No: 7 Author: Abraham Bennet
No: 8 Author: Reginald Blotchet-Halls

No: 10 Author: Abraham Bennet
No: 11 Author: Reginald Blotchet-Halls
No: 12 Author: Abraham Bennet
No: 13 Author: Reginald Blotchet-Halls

It seams that the query omits som fetches, does anybody know why?

In the BOL it says that WHILE loop only executes if the latest fetches succeeds, but why shouldnt it, the SQL query is ok!?

appritiate some help,
thanx in advance.I ran your code. Could not re-produce the error. Send me the data too. I assume you have fixed some sytax errors in your code.|||It was easier than I thougt, the error I mean.

Yes, I to found some syntax error after submitting the post, but the error was that some fields in my rowset containded a null value, and when the code tried to print that value it failed, which meant that the specific row never showed up.

I fixed by setting the vaule to 0

Monday, March 19, 2012

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 strange problem with SQL query fro getting field names

Hello All,

I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice.
Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.

 Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
 if not page.Ispostback then
 try
 Sqlconnection = New Sqlconnection (connectionString)
 querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs
 WHERE TABLE_NAME = 'Newtable'"
 SqlCommand = New SqlCommand(queryString, Sqlconnection)
 SqlConnection.Open
 dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
 while dataReader.Read()
 Tablefields_txt.text += dataReader.Getstring(0) & ", "
 End while
 catch ex as Exception
 msgbox("An error has occured: " + ex.Message,0, "Error Message")
 finally
 SqlConnection.Close()
 End try
End if

Any help , pleaseCheck this bit. I assume this might have an impact on your problem.

Tablefields_txt.text += dataReader.Getstring(0) & ", "|||I have tried this:
Dim temp as string
while dataReader.Read()

temp += dataReader.Getstring(0) & ", "

End while

Tablefields_txt.text = temp

I think the problem might be from the querystring "select ....." , but i do know how to deal with it . I need help|||As I posted in your other thread, the problem is due to the "handles Mybase.Load". Remove this and you should see the results you expect.

Terri

a strange problem with RDA

Hello,

I have written a program for WinCE with .NET. In one of the forms, program gets data from the sql server with RDA. It works fine..the users get data.one two three.....but at 16th or 17th or 18th try an error occurs:

"SQL Mobile encounteres some problems..."

What does it mean? It gets data 15 times but after that it gets error....What is the problem with that?

I'm really confused...

Thanks in advance.

I tried to free the resources like RDA object and other SQL CE objects and it worked...

a strange problem

I made a some reports. set their parameterss and passed them while invoking report. At report creation time I check the INTEGRATED SECURITY checkbox so it does not ask for password when pressing ther "invoke report" button. (expectds behaviour).

But when I executed the same exe on some other machine. having similar database as mine. by clikcing the invoke report button, a box comes up asking the following informtion.

Server name:
user :
password :
database :

how to solve it...Open the report and do verify database

a strange issue after installing SP3a (SP3a Issue)

when installing "SQL Server Service Pack 3a" on my "SQL Server Personal
Edition", something strange happened to the windows view data in the Tables
and Views, they are showing RightToLeft!!!!?
how can i fix this? and why did it happen?
please help. and if possible email me the solution to ay_tech@.yahoo.com
Thanks!
Ayman
Message posted via http://www.sqlmonster.com
Would Any One help plese on the Issue ?
Message posted via http://www.sqlmonster.com
|||On Wed, 16 Feb 2005 11:05:43 GMT, Ayman AM via SQLMonster.com wrote:

>when installing "SQL Server Service Pack 3a" on my "SQL Server Personal
>Edition", something strange happened to the windows view data in the Tables
>and Views, they are showing RightToLeft!!!!?
>how can i fix this? and why did it happen?
Hi Ayman,
Is this in Enterprise Manager?
Try this: when displaying tables or views, select "Details" from the
"View" menu (I hope that's the English equivalent - I'm using a Dutch
verwion of Windows). Check the left-most heading above the list of table
names - my guess is that you'll find an upward pointing arrowhead in it,
denoting reverse sort on table- or view-name. Click this header once to
select normal sort (the arrowhead will change to point down). Then, use
the "View" menu again to restore your favorite setting (I think, from your
message, that you're using large pictograms - I always have the details
view selected, but that's just personal preference).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

a strange issue after installing SP3a (SP3a Issue)

when installing "SQL Server Service Pack 3a" on my "SQL Server Personal
Edition", something strange happened to the windows view data in the Tables
and Views, they are showing RightToLeft!!!!?
how can i fix this? and why did it happen?
please help. and if possible email me the solution to ay_tech@.yahoo.com
Thanks!
Ayman
--
Message posted via http://www.sqlmonster.comWould Any One help plese on the Issue ?
--
Message posted via http://www.sqlmonster.com|||On Wed, 16 Feb 2005 11:05:43 GMT, Ayman AM via SQLMonster.com wrote:
>when installing "SQL Server Service Pack 3a" on my "SQL Server Personal
>Edition", something strange happened to the windows view data in the Tables
>and Views, they are showing RightToLeft!!!!?
>how can i fix this? and why did it happen?
Hi Ayman,
Is this in Enterprise Manager?
Try this: when displaying tables or views, select "Details" from the
"View" menu (I hope that's the English equivalent - I'm using a Dutch
verwion of Windows). Check the left-most heading above the list of table
names - my guess is that you'll find an upward pointing arrowhead in it,
denoting reverse sort on table- or view-name. Click this header once to
select normal sort (the arrowhead will change to point down). Then, use
the "View" menu again to restore your favorite setting (I think, from your
message, that you're using large pictograms - I always have the details
view selected, but that's just personal preference).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

a strange issue after installing SP3a (SP3a Issue)

when installing "SQL Server Service Pack 3a" on my "SQL Server Personal
Edition", something strange happened to the windows view data in the Tables
and Views, they are showing RightToLeft!!!!?
how can i fix this? and why did it happen?
please help. and if possible email me the solution to ay_tech@.yahoo.com
Thanks!
Ayman
Message posted via http://www.droptable.comWould Any One help plese on the Issue ?
Message posted via http://www.droptable.com|||On Wed, 16 Feb 2005 11:05:43 GMT, Ayman AM via droptable.com wrote:

>when installing "SQL Server Service Pack 3a" on my "SQL Server Personal
>Edition", something strange happened to the windows view data in the Tables
>and Views, they are showing RightToLeft!!!!?
>how can i fix this? and why did it happen?
Hi Ayman,
Is this in Enterprise Manager?
Try this: when displaying tables or views, select "Details" from the
"View" menu (I hope that's the English equivalent - I'm using a Dutch
verwion of Windows). Check the left-most heading above the list of table
names - my guess is that you'll find an upward pointing arrowhead in it,
denoting reverse sort on table- or view-name. Click this header once to
select normal sort (the arrowhead will change to point down). Then, use
the "View" menu again to restore your favorite setting (I think, from your
message, that you're using large pictograms - I always have the details
view selected, but that's just personal preference).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

A stored-procedure becomes slow and needs re-creation

A Stored Procedure runs slow while it's SQL is fast. RECOMPILE won't help

Hi all
I have a SP that beahves strange. Originally it takes about 20 milliseconds
to complete, but sometimes it starts going slow and take about 5-7 seconds.
When this happens, it keeps going slow.
I tried to run the SQL body of the SP in the Query analyzer, and it runs
fast (20 ms), while the SP takes 5-7 seconds
I've tried recompiling the procedure, as well as drop and create it again,
but it doesn't help.
Does anyone knows what can cause this and what is the soloution ?
TIA
Boaz Ben-Porat
Milestone SystemsFirst thing: Google for "Parameter sniffing", make sure you understand that
concept.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message news:OerL4M8ZGHA.3880@.TK2MSFTNGP04.phx
.gbl...
> Hi all
> I have a SP that beahves strange. Originally it takes about 20 millisecond
s
> to complete, but sometimes it starts going slow and take about 5-7 seconds
.
> When this happens, it keeps going slow.
> I tried to run the SQL body of the SP in the Query analyzer, and it runs
> fast (20 ms), while the SP takes 5-7 seconds
> I've tried recompiling the procedure, as well as drop and create it again,
> but it doesn't help.
> Does anyone knows what can cause this and what is the soloution ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>|||Could be parameter sniffing, can you show the code?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi Denis
Disabling parameter sniffing seems to work here. If it is still too slow
I'll send the code (which a bit messy). If not, I wouldn't waist your time.
Thanks
Boaz Be-Porat
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145899699.966141.109220@.y43g2000cwc.googlegroups.com...
> Could be parameter sniffing, can you show the code?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

Thursday, March 8, 2012

A Simple Strange Query..........

I am having in writing a simple select command......
here's My Problem

I am using the ASP SQLDataSources in VS2005... and my need is that i need to show Products details in a Gridview.....
Actually through QueryString a StoreID is being fetched.... and the Products under those StoreID are shown.... if there is nothing in query string then it should show all the results... ...... It means that my querystring should be something like this

select * from tblProducts where StoreID = <xyz>

and <xyz> should be some thing that could show all the rows in that table ...... i mean that it should show all the possible results that can be shown through...

select * from tblProducts

Dhaliwal

1) Your select command should be a in stored procedure which takes the StoreId as an argument.

2) You should never use SELECT * in the query - only the columns being used in the Gridview should be included.

3) The tbl prefix on tblProducts is depreciated. Also it should be in the singular as each row contains information on a single product. A better name would be Product.

4) The switch is much easier to achieve that is commonly imagined. Assuming that StoreId is an Integer primary key then the lowest value will be 1. Your stored procedure will then contain

IF @.StoreId > 0

SELECT A, B, C FROM Product Where StoreId = @.StoreId

ELSE

SELECT A, B, C FROM Product

(If should be noted that this is not very suitable for a database with a high volume of queries - in this case you should use two stored procedures one for all records and one for a list select selective by store.)

5) Run the stored procedure getting a dataset and assign the dataset to the GridView.

|||hi there,
thank you very much for such a quick reply..........

I fully agree with you that using Stored procedured is a good Idea/Methodology
Actually i used to do these task through coding each time but i really want to try these new components and see how they works...(under all kind of situations).... so is it possible without using stored procedure......

and 1 more thing i want to ask...... i couldn't get your 3rd point totally....... what r u trying to say in that.... would you please elaborate it... wht the prefixtbl is depreciated...!!!|||

Here is how you can do it:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:yourConnectionString%>"CancelSelectOnNullParameter="false" SelectCommand="SELECT * FROM tblProducts WHERE productID=ISNULL(@.productID,productID)"> <SelectParameters> <asp:QueryStringParameter Name="productID" QueryStringField="productID" /> </SelectParameters> </asp:SqlDataSource>

|||

Dhaliwal:
hi there,
thank you very much for such a quick reply..........
I fully agree with you that using Stored procedured is a good Idea/Methodology
Actually i used to do these task through coding each time but i really want to try these new components and see how they works...(under all kind of situations).... so is it possible without using stored procedure......
and 1 more thing i want to ask...... i couldn't get your 3rd point totally....... what r u trying to say in that.... would you please elaborate it... wht the prefixtbl is depreciated...!!!

Hi Dhaliwal,
I think that the use of a stored procedure would be good because all your logic would be into the stored procedure. By other hand The SqlDataSource control is a very good control, very easy to use but there is something I do not like, it embeds the SQL code into the presentation layer (aspx). I would suggest that you could take a look atwww.sqlnetframework.com. There is a SqlStoreDataSource control which works in the same way that the SqlDataSource control but it doesn't embed the SQL code into the presentation layer. The SqlStoreDataSource control creates a repository where all your SQL code is stored. You can re-use your SQL code in other application (e.g. Windows app). The SqlDataSource control is very easy to use, to learn and it helps you to create an application with a good architecture.

I hope it helps you in your projects. Yes

Luis Ramirez.
www.sqlnetframework.com

|||Hi Luis....

Thanks for expressing your views about howData should be fetched from Database and as i said earlier i agree that using Store Proc is a better method... but i was just practicing and really wanted to try SQLDatasource completely. Moreover i was kean to know about SQL Query that could result in what i was looking for....... and whatLimno provided was exactly i was looking for...... i.e usingIsNULL in select Query (Nice Trick)

And also Thanks alot for sharing views about the SQLDataSource controls(Benifits/Limitations... etc)|||

There was a time when the prefix was used to make it obvious what were the table names, however in the statement

SELECT Avalue FROM Bert WHERE Id = 1

it is obvious that Bert is a table. The use of the tbl was quite unnecessary. Table name can get quite long enough without using tbl!

There are some contexts where some form of prefix is useful, however given a modern utility such as SQL Server Management Studio, which lists tables separatly from say stored procedures, the tbl prefix is unnecessary.

HTH

A Severe error occured ...

Hi,

i am using the HTML Textbox from activeup.com. I get a strange error when i put to much text inside the HTML textbox and insert it into my SQL Server with an SP. Myquestion is: is there a limit on a field in SQL of the type Text or what could it else be (error in HTML Textbox?)can you post some code.

Saturday, February 11, 2012

a humdinger of a performance problem

Hi all,
Generally i'm helping to answer questions here, but i have strange problem
that I haven't seen before, or at least such a difference in performance of
a query.
Scenario:
I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
SP4.
I have a datawarehouse that currently contains tiny amounts of data, like
3000 rows in 1 table.
All 3 tables in use are 'perfectly' indexed and I have a simple view sitting
on top of them.
If i run a select query against the DB it returns me 3000 rows in under a
second... Now.. here's the killer:
I have EXACTLY the same DB installed on the DWH server which boasts 4GB RAM,
4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
The server is again 'perfectly' tuned with the exact same database ready for
rocking and rolling!
Now - the same query runs in 3 minutes!!!!!!!
If a perform execution plans and traces to my hearts content they ALWAYS
return the same output - but simply a difference in the time taken to return
the data.
I haven't come across a difference like this in 5 years of DBA'ing and am
grateful for any pointers.
A stupid question, would Dev version of SQL perform better indexing of views
(even though no indexed views are in play) than Standard edition which
obviously doesnt support Ind.Views!!'
Baffled! and TIA
ImmyImmy
Check out all SQL Server's settings on both machines
Add "noexpand" hint to the indexed view like
select sum(col) from IndexView with (noexpand)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Why don't you run a perfmon on the server and keep a check on the buffer
cache, I/O, processesor and memory when the query is run and see which is
being used more? that info will help in address the issue faster.
"Immy" wrote:

> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance o
f
> a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view sitti
ng
> on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB RA
M,
> 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready f
or
> rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to retu
rn
> the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of vie
ws
> (even though no indexed views are in play) than Standard edition which
> obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>
>|||Hi Uri,
I mentioned that both my machines are setup exactly the same way. I
installed them both personally.
I don't have indexed views setup as my live server uses Standard Edition of
SQL Server.
Immy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Immy
> Check out all SQL Server's settings on both machines
> Add "noexpand" hint to the indexed view like
> select sum(col) from IndexView with (noexpand)
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
I would expect different execution plans to explain such a big difference.
Take a close look at each plan node to ensure each is the same in both
plans.
Is there blocking or other processes running on the DWH server that might
explain the difference?

> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Developer Edition has the same features as Enterprise Edition.
Consequently, DE will automatically consider view indexes. However, you
would see different execution plans if indexed views was the reason for the
difference in performance. DE/EE also has a merry-go-round scan feature
that can improve concurrent scan performance but that shouldn't be an issue
here.
Hope this helps.
Dan Guzman
SQL Server MVP
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Immy
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Sorry I was referencing to the above statement somehow
Run DBCC FREEPROCCACHE on both machines and see what is going on
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23c130jlfGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Hi Uri,
> I mentioned that both my machines are setup exactly the same way. I
> installed them both personally.
> I don't have indexed views setup as my live server uses Standard Edition
> of SQL Server.
> Immy
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
>|||thanks for the responses guys and i've either already tried most of your
suggestions or tried the rest offered but getting nowehere very fast.
I'm currently on Japanese time zone rolling out a live system so there comes
a time where i need to re-group and take a look after some sleep!
I'm beginning to think my conclusion as well as Dans comments re: auto
indexing is the key here as i've swapped db's around from server to server
and always see the issue on the Standard version of sql.
I'll have to just try alternate methods of providing the data to the users;
however, feel free to post more suggestions that i can try in a few hours
time.
thanks guys
Immy
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||How long has the database been set up on the production server?
Do any other queries take this long to run?
Is it possible that you have a bad processor / memory?
Or, maybe you have a corrupt index.
Lets figure out if one table is causing you the trouble. Take each table in
your query, and create a seperate query, joining the table to itself several
times, on the same columns used for the join in the original query. Run
each of these and see if one of them is slow. I would expect that one of
these would be very slow and the other two would be quite fast. Drop the
indexes on the one that is slow and recreate them.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> thanks for the responses guys and i've either already tried most of your
> suggestions or tried the rest offered but getting nowehere very fast.
> I'm currently on Japanese time zone rolling out a live system so there
comes
> a time where i need to re-group and take a look after some sleep!
> I'm beginning to think my conclusion as well as Dans comments re: auto
> indexing is the key here as i've swapped db's around from server to server
> and always see the issue on the Standard version of sql.
> I'll have to just try alternate methods of providing the data to the
users;
> however, feel free to post more suggestions that i can try in a few hours
> time.
> thanks guys
> Immy
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
problem
+
like
a
am
>|||Jim
the DB is new as of yesterday as is the installation and the query was
running fine during the first few hours of yesterday.
I have also broken down the queries and joined onto 1 table it's fine.
If i add 1 of any other 2 tables to the query it slows down dramatically and
i had also recreated the indexes a few times throughout the day.
Could be right about bad processor as 1 of the processors always peaks for
the duration of the run.
Once again, thanks for your responses. Good to know we're all singing from
the same hymn sheet and i'm sure i'll get to the end of it soon.
Immy
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> How long has the database been set up on the production server?
> Do any other queries take this long to run?
> Is it possible that you have a bad processor / memory?
> Or, maybe you have a corrupt index.
> Lets figure out if one table is causing you the trouble. Take each table
> in
> your query, and create a seperate query, joining the table to itself
> several
> times, on the same columns used for the join in the original query. Run
> each of these and see if one of them is slow. I would expect that one of
> these would be very slow and the other two would be quite fast. Drop the
> indexes on the one that is slow and recreate them.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> comes
> users;
> problem
> +
> like
> a
> am
>|||How is the memory and IO (from perfmon) during the run? This really has my
curiosity peaked now. With only 3000 rows, I would expect very fast
response on anything better than a 386. With identical execution plans, I
am even more puzzled.
Just to confirm, all 3 tables have the same number of rows on both
databases?
Could you post the SQL query and the DDL, just to satisfy our curiosity?
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uzTPfDnfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Jim
> the DB is new as of yesterday as is the installation and the query was
> running fine during the first few hours of yesterday.
> I have also broken down the queries and joined onto 1 table it's fine.
> If i add 1 of any other 2 tables to the query it slows down dramatically
and
> i had also recreated the indexes a few times throughout the day.
> Could be right about bad processor as 1 of the processors always peaks for
> the duration of the run.
> Once again, thanks for your responses. Good to know we're all singing from
> the same hymn sheet and i'm sure i'll get to the end of it soon.
> Immy
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
table
of
the
your
hours
under
4GB
and
of
edition
>

Thursday, March 22, 2012

A vexing issue

A vexing issue

a very very strange sql bug (?)

a very very strange sql bug (?)

a very very strange sql bug (?)

A Very Strange Problem

Tuesday, March 20, 2012

A T-SQL CURSOR issue, need help

Monday, March 19, 2012

A strange problem with updatable partitioned view.

A strange problem with SQL query fro getting field names

a strange problem with RDA

a strange problem

a strange issue after installing SP3a (SP3a Issue)

a strange issue after installing SP3a (SP3a Issue)

a strange issue after installing SP3a (SP3a Issue)

A stored-procedure becomes slow and needs re-creation

A Stored Procedure runs slow while it's SQL is fast. RECOMPILE won't help

Thursday, March 8, 2012

A Simple Strange Query..........

A Severe error occured ...

Saturday, February 11, 2012

a humdinger of a performance problem

SQL server questions

Blog Archive

About Me