Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.edu
What account is SQL Server running under? The query is run in it's context.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>
|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>>I would like to perform Distributed Queries on Active Directory via views
>>in SQL Server. I have created a linked server by executing:
>> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
>> 'ADSDSOObject', 'adsdatasource'
>> However whenever I run even the simplest query:
>> SELECT *FROM OPENQUERY( ADSI,
>> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
>> I get the following error:
>> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
>> that the user did not have the permission to perform the operation.
>> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
>> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates
>> that the user did not have the permission to perform the operation.].
>> I am logged into the server as an Administrator and logging into query
>> analyzer as sa - so I should have all required permissions.
>> Any ideas? Any help or direction is really appreciated - Thanks in
>> advance,
>> J. Shane Kunkle
>> jkunkle@.vt.edu
>>
>

distributed partitioned views

Hi everyone,
I have some doubts about distributed partitioned views.
When we create a distributed partitioned view whcih include three server, do we have tocreate this same distributed partitioned view in that three server in order to make each server to see adn especially modify it ?

Thanks

Yes, you need to create the DPV on each server if you want to be able to modify the data on one or more of the servers from each one. Ex:

-- server1

create view dpv

as

select ... from dbo.tbl1

union all

select ... from server2.dbo.tbl2

union all

select ... from server3.dbo.tbl3

-- server2

create view dpv

as

select ... from server1.dbo.tbl1

union all

select ... from dbo.tbl2

union all

select ... from server3.dbo.tbl3

-- server3

create view dpv

as

select ... from server1.dbo.tbl1

union all

select ... from server2.dbo.tbl2

union all

select ... from dbo.tbl3

Distributed partitioned views

I'm considering distributing a large table which we
now have, for performance reasons.
We've been doing a join on this large table (11 million
rows), and I don't know whether it would help much to
partition the table across four machines.
When you perform a join on a distributed partitioned view,
where does the "work" take place. ie. If the table just
gets reconstructed at the site of the view, and then the
join operation takes place, I might actually *suffer* in
performance because of the network overhead.
Could someone advise me on this? I may be wrong about how
it works, and I'd like to understand it before undertaking
the task of distributing it.
Thanks in advance,
AndrewAndrew,
you will gain the performance enhancement as you imagined -- the join
condition will direct the work to the member table via the view. You can
verify this with the execution plan.
The key is, that your distribution will do this for all your important
joins. You may have different join conditions. If the partitioning column
is not always in the joining key, you will have to engage multiple member
tables. If this happens not two often, it might be acceptable. So here you
have to make the judgment call.
hth
Quentin
"Andrew" <a@.b.com> wrote in message
news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
> I'm considering distributing a large table which we
> now have, for performance reasons.
> We've been doing a join on this large table (11 million
> rows), and I don't know whether it would help much to
> partition the table across four machines.
> When you perform a join on a distributed partitioned view,
> where does the "work" take place. ie. If the table just
> gets reconstructed at the site of the view, and then the
> join operation takes place, I might actually *suffer* in
> performance because of the network overhead.
> Could someone advise me on this? I may be wrong about how
> it works, and I'd like to understand it before undertaking
> the task of distributing it.
> Thanks in advance,
> Andrew
>|||Thanks Quentin,
I'm glad to hear that the work itself gets distributed.
It seems from your answer that the partitioning column
should be the column on which the join is performed for
optimal performance... did I read that right?
Also, I understand you want to keep as much "closely
related" data together as possible. Unfortunately, there
are several tables on which my large table will be joined,
and I'd like to keep them on the host where I create the
distributed view.
Is this going to be a problem? Or is it advisable to
partition the smaller tables as well?
Thanks in advance,
Andrew
>--Original Message--
>Andrew,
>you will gain the performance enhancement as you
imagined -- the join
>condition will direct the work to the member table via
the view. You can
>verify this with the execution plan.
>The key is, that your distribution will do this for all
your important
>joins. You may have different join conditions. If the
partitioning column
>is not always in the joining key, you will have to
engage multiple member
>tables. If this happens not two often, it might be
acceptable. So here you
>have to make the judgment call.
>hth
>Quentin
>
>"Andrew" <a@.b.com> wrote in message
>news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
>> I'm considering distributing a large table which we
>> now have, for performance reasons.
>> We've been doing a join on this large table (11 million
>> rows), and I don't know whether it would help much to
>> partition the table across four machines.
>> When you perform a join on a distributed partitioned
view,
>> where does the "work" take place. ie. If the table
just
>> gets reconstructed at the site of the view, and then
the
>> join operation takes place, I might actually *suffer*
in
>> performance because of the network overhead.
>> Could someone advise me on this? I may be wrong about
how
>> it works, and I'd like to understand it before
undertaking
>> the task of distributing it.
>> Thanks in advance,
>> Andrew
>
>.
>|||Andrew,
> It seems from your answer that the partitioning column
> should be the column on which the join is performed for
> optimal performance... did I read that right?
You are right. Think of your table you want to partition by lastname, where
a-m go to server1 and the rest goes to server2. If you join or search by
firstname, what will sql server do? it of course will have to go into both
server and search the data for every entry of firstname. Had you
partitioned by firstname, the query will go to server1 finding all records
with firstnames with value a-m, and finding all the others in server2.
> Also, I understand you want to keep as much "closely
> related" data together as possible. Unfortunately, there
> are several tables on which my large table will be joined,
> and I'd like to keep them on the host where I create the
> distributed view.
>
I don't see any problem. The way you want use distributed partitioned view
to improve performance is to effectively retrieve and write -- reducing
physical read and load index/data into memory with reduced paging. For
small tables, this does not present a problem and you can keep them in a
convenient db.
> >--Original Message--
> >Andrew,
> >
> >you will gain the performance enhancement as you
> imagined -- the join
> >condition will direct the work to the member table via
> the view. You can
> >verify this with the execution plan.
> >
> >The key is, that your distribution will do this for all
> your important
> >joins. You may have different join conditions. If the
> partitioning column
> >is not always in the joining key, you will have to
> engage multiple member
> >tables. If this happens not two often, it might be
> acceptable. So here you
> >have to make the judgment call.
> >
> >hth
> >
> >Quentin
> >
> >
> >"Andrew" <a@.b.com> wrote in message
> >news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
> >> I'm considering distributing a large table which we
> >> now have, for performance reasons.
> >>
> >> We've been doing a join on this large table (11 million
> >> rows), and I don't know whether it would help much to
> >> partition the table across four machines.
> >>
> >> When you perform a join on a distributed partitioned
> view,
> >> where does the "work" take place. ie. If the table
> just
> >> gets reconstructed at the site of the view, and then
> the
> >> join operation takes place, I might actually *suffer*
> in
> >> performance because of the network overhead.
> >>
> >> Could someone advise me on this? I may be wrong about
> how
> >> it works, and I'd like to understand it before
> undertaking
> >> the task of distributing it.
> >>
> >> Thanks in advance,
> >>
> >> Andrew
> >>
> >
> >
> >.
> >|||Quentin,
Thanks, you've been a great help. I'll get to designing
this thing.
All the best,
Andrew
>--Original Message--
>Andrew,
>> It seems from your answer that the partitioning column
>> should be the column on which the join is performed for
>> optimal performance... did I read that right?
>You are right. Think of your table you want to
partition by lastname, where
>a-m go to server1 and the rest goes to server2. If you
join or search by
>firstname, what will sql server do? it of course will
have to go into both
>server and search the data for every entry of
firstname. Had you
>partitioned by firstname, the query will go to server1
finding all records
>with firstnames with value a-m, and finding all the
others in server2.
>> Also, I understand you want to keep as much "closely
>> related" data together as possible. Unfortunately,
there
>> are several tables on which my large table will be
joined,
>> and I'd like to keep them on the host where I create
the
>> distributed view.
>I don't see any problem. The way you want use
distributed partitioned view
>to improve performance is to effectively retrieve and
write -- reducing
>physical read and load index/data into memory with
reduced paging. For
>small tables, this does not present a problem and you
can keep them in a
>convenient db.
>> >--Original Message--
>> >Andrew,
>> >
>> >you will gain the performance enhancement as you
>> imagined -- the join
>> >condition will direct the work to the member table via
>> the view. You can
>> >verify this with the execution plan.
>> >
>> >The key is, that your distribution will do this for
all
>> your important
>> >joins. You may have different join conditions. If
the
>> partitioning column
>> >is not always in the joining key, you will have to
>> engage multiple member
>> >tables. If this happens not two often, it might be
>> acceptable. So here you
>> >have to make the judgment call.
>> >
>> >hth
>> >
>> >Quentin
>> >
>> >
>> >"Andrew" <a@.b.com> wrote in message
>> >news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
>> >> I'm considering distributing a large table which we
>> >> now have, for performance reasons.
>> >>
>> >> We've been doing a join on this large table (11
million
>> >> rows), and I don't know whether it would help much
to
>> >> partition the table across four machines.
>> >>
>> >> When you perform a join on a distributed partitioned
>> view,
>> >> where does the "work" take place. ie. If the table
>> just
>> >> gets reconstructed at the site of the view, and then
>> the
>> >> join operation takes place, I might actually
*suffer*
>> in
>> >> performance because of the network overhead.
>> >>
>> >> Could someone advise me on this? I may be wrong
about
>> how
>> >> it works, and I'd like to understand it before
>> undertaking
>> >> the task of distributing it.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Andrew
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

distributed partitioned view pblm

i have the following simple tables and partitioned views ...all is fine
until i try to i/u/d using the distributed part. view...tucker2 and jag are
sqlserver enterprise edition, windows 2k server...
i get ==> union all view 'customers' in not updateable because the defintion
contains
a disallowed construct (42000,4416)
what am i missing' thx, chester
-- On Server1 - tucker2
drop TABLE dbo.Customers_tucker2
CREATE TABLE Customers_tucker2
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 99999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_tucker2 values (11101, 'chet1', 'tx')
insert into customers_tucker2 values (11102, 'chet2', 'xx')
insert into customers_tucker2 values (11103, 'chet3', 'tv')
insert into customers_tucker2 values (11104, 'chet4', 'vv')
insert into customers_tucker2 values (11105, 'chet5', 'zz')
-- On Server2 - jag
drop TABLE Customers_jag;
CREATE TABLE Customers_jag
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 100000 AND 999999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_jag values (111001, 'chet41', 'tt')
insert into customers_jag values (111002, 'chet42', 'rr')
insert into customers_jag values (111003, 'chet43', 'dd')
insert into customers_jag values (111004, 'chet44', 'gg')
insert into customers_jag values (111005, 'chet45', 'ff')
/*************************************
run the following on both servers
************************************/
drop view customers
go
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select top 2 * from jag.pubs.dbo.customers_jag
select top 2 * from tucker2.pubs.dbo.customers_tucker2
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
SET XACT_ABORT ON
go
create view customers
as
select customerid, cust_name, state_cd
from tucker2.pubs.dbo.customers_tucker2
union all
select customerid, cust_name, state_cd
from jag.pubs.dbo.customers_jag
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select min(customerid), max(customerid) from customers
select * from customers
where customerid in (111005,11101)
/* the following 3 update/insert/del fail with msg:
union all view 'customers' in not updateable because the defintion contains
a disallowed construct (42000,4416)
*/
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
update customers
set state_cd = 'la'
where customerid = 11101
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
insert into customers values ( 1122, 'chet', 'tx')
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
delete from customers
where customerid = 11101Chet,
what for version of sql server are you using? Only the Developer and
Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE
operations on partitioned views.
hth
Quentin
"chet gwin" <cgwin@.houston.rr.com> wrote in message
news:SyGVa.120630$XV.6674033@.twister.austin.rr.com...
> i have the following simple tables and partitioned views ...all is fine
> until i try to i/u/d using the distributed part. view...tucker2 and jag
are
> sqlserver enterprise edition, windows 2k server...
> i get ==> union all view 'customers' in not updateable because the
defintion
> contains
> a disallowed construct (42000,4416)
> what am i missing' thx, chester
>
> -- On Server1 - tucker2
> drop TABLE dbo.Customers_tucker2
> CREATE TABLE Customers_tucker2
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 1 AND 99999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
> insert into customers_tucker2 values (11101, 'chet1', 'tx')
> insert into customers_tucker2 values (11102, 'chet2', 'xx')
> insert into customers_tucker2 values (11103, 'chet3', 'tv')
> insert into customers_tucker2 values (11104, 'chet4', 'vv')
> insert into customers_tucker2 values (11105, 'chet5', 'zz')
>
> -- On Server2 - jag
> drop TABLE Customers_jag;
> CREATE TABLE Customers_jag
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 100000 AND 999999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
>
> insert into customers_jag values (111001, 'chet41', 'tt')
> insert into customers_jag values (111002, 'chet42', 'rr')
> insert into customers_jag values (111003, 'chet43', 'dd')
> insert into customers_jag values (111004, 'chet44', 'gg')
> insert into customers_jag values (111005, 'chet45', 'ff')
> /*************************************
> run the following on both servers
> ************************************/
> drop view customers
> go
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select top 2 * from jag.pubs.dbo.customers_jag
> select top 2 * from tucker2.pubs.dbo.customers_tucker2
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> SET XACT_ABORT ON
> go
> create view customers
> as
> select customerid, cust_name, state_cd
> from tucker2.pubs.dbo.customers_tucker2
> union all
> select customerid, cust_name, state_cd
> from jag.pubs.dbo.customers_jag
>
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select min(customerid), max(customerid) from customers
> select * from customers
> where customerid in (111005,11101)
> /* the following 3 update/insert/del fail with msg:
> union all view 'customers' in not updateable because the defintion
contains
> a disallowed construct (42000,4416)
> */
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> update customers
> set state_cd = 'la'
> where customerid = 11101
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> insert into customers values ( 1122, 'chet', 'tx')
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> delete from customers
> where customerid = 11101
>
>sql

Distributed Partioned Views between different SS versions

Can you have a updatable Distributed Partioned Views between SQL
Server 2000 and SQL Server 2005 ? The reason I ask is the down time to
upgrade all 2000 sql servers to 2005 at the same time is prohibitive in
our environment.
Nevermind, I have tested and confirmed this works.

Distributed Partioned Views between different SS versions

Can you have a updatable Distributed Partioned Views between SQL
Server 2000 and SQL Server 2005 ? The reason I ask is the down time to
upgrade all 2000 sql servers to 2005 at the same time is prohibitive in
our environment.Nevermind, I have tested and confirmed this works.

Distributed Partioned Views between different SS versions

Can you have a updatable Distributed Partioned Views between SQL
Server 2000 and SQL Server 2005 ? The reason I ask is the down time to
upgrade all 2000 sql servers to 2005 at the same time is prohibitive in
our environment.Nevermind, I have tested and confirmed this works.

distinct count with multiple partitions?

We analyze much of our data using distinct measures. Some of the underlying sources - i.e. daily page views are heavy and have millions of rows a day. Because of this I've created daily partitions to only process the incremental data that's arrived. However, I'm curious - how does distinct count perform when it needs to rollup over multiple partitions?

IE Say it's Dec 25th, and I have 25 unique partitions for each day of December thus far. Internally, how do the distinct measures correctly accumulate the unique instances of my measure? Are there any significant performance concerns to be aware of?Distinct Count measure will work fine with multiple partitions. AS keeps the distinct count values inside the partitions, and therefore it can correctly aggregate across them. Partition per day should be OK too.|||Arjun: Make sure you are on at least SQL 2005 SP1 QFE rollup, preferably SP2 CTP2. There were a couple of bugs fixed for distinct count measures over multiple partitions.|||Good to know, thanks! Jeff, do you have any details on the nature of the issues with distinct counts over multiple partitions?

Dist. Partitioned Views

I've read that the partitioning column must be part or
all of the primary key.
Is this design advice or a requirement? If it's a
requirement, why?
Any and all help appreciated,
Thanks,
Andrewit is a requirement, so that sql server will know to which
server the new row belongs. This is just to avoid
confilict and overlapping of the data on multiple servers.
>--Original Message--
>I've read that the partitioning column must be part or
>all of the primary key.
>Is this design advice or a requirement? If it's a
>requirement, why?
>Any and all help appreciated,
>Thanks,
>Andrew
>.
>sql

Displaying a relational database as it is displayed in MS Access

Can this be done in ASP.Net, as it stands my database views in my ASP.Net application are just standard

Unlike the view in MS Access which shows the collapsable linked data below the data (from a different table)

Many thanks

Rich

Yes, it's possible to create a GUI similar to the one that comes with Access. It's not a trivial undertaking though. You can use CSS, javascript and Ajax to overcome the inherent limitations of html.|||If your tables/views are relatively narrow and have few rows (the table can be convenantly viewed on a screen without scrolling), then use a DataGrid or GridView, and set it to automatic columns - this will give you a read-only view of the database. Code behind can be written to allow updates, but you do need to enforce any business rules.|||Have a look at http://www.codeproject.com/cs/database/DBViewer.asp A simple database viewer to manipulate SQL Server data types (in particular: image, binary, varbinary and text).|||Also try http://www.codeproject.com/asp/ute.asp "Viewer and Editor for any table in any Database you can reach from your IIS/PWS."