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 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 partitioned view + procedure

If i have a view such as
Create view Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
And if one server say server 2 is unavailable, will the view fail to run ?
If so , how can i still let the stored proc run
and same for a stored procedure
Create proc Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
What happens in this case if server2 is unavailable ? And also a way to let
it run should any server be made unavailable> And if one server say server 2 is unavailable, will the view fail to run ?
For a query where the optimizer realizes it has to hit server 2 will fail.
> If so , how can i still let the stored proc run
Have redundancy on the servers.
Same goes for stored procedures.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OHe5POhkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> If i have a view such as
> Create view Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
>
> And if one server say server 2 is unavailable, will the view fail to run ?
> If so , how can i still let the stored proc run
> and same for a stored procedure
> Create proc Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
> What happens in this case if server2 is unavailable ? And also a way to let
> it run should any server be made unavailable
>
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
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/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
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/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
Reshma
Hi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/de...qd_10_2z4x.asp
http://www.microsoft.com/sql/evaluat...s/distpart.asp
http://msdn.microsoft.com/library/de...qd_12_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
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/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects. Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr S
How to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.gbl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects.
Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr SHow to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.g
bl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Displaying Date in SQL View

Hi,
I am a preety newbie to SQL and was wondering how to get the date from the SQL to show up as Format(Date, "dd/mm/yyyy").
TIA.
RohitIt depends on your DBMS. For Oracle, it is TO_CHAR( date, 'DD/MM/YYYY' )|||Thanks a lot sir,

My problem was to get this value somehow.

Thanks a lot for your help.

rohit|||if it is SQL Server you can use Convert function to convert the date into any format for ex: Select Convert(Varchar(10), GetDate() ,101)
will give you the date in mm/dd/yyyy format.
it depends upon the No you provide in the convert function, 101 stands for "mm/dd/yyyy" format

Balaji

:)

Displaying a SQL Server Report 2005 in a PDA....

Hello All,

Good day!

I have developed a report using SQL Server Reporting Services.

I can view the report in a Browser, but when I try to view the Report in a Pocket PC 2003 Emulator, the report displays but without the data or the contents of the report....

I tried appending &rc:=HTML3.2 to the end as suggested by in one of the posts but no luck....

Any suggestion or directions will be higly appreciated....

Regards,

Srikanth Pai

Hello,

To specify a rendering extension on a report server URL you need to use this syntax:

rs:Format=HTML3.2

Also, as far as I know PPC 2003 browsers support HTML 4.0. Why do you want to render to HTML 3.2?

Thanks,

Chris

Displaying a SQL Server Report 2005 in a PDA....

Hello All,

Good day!

I have developed a report using SQL Server Reporting Services.

I can view the report in a Browser, but when I try to view the Report in a Pocket PC 2003 Emulator, the report displays but without the data or the contents of the report....

I tried appending &rc:=HTML3.2 to the end as suggested by in one of the posts but no luck....

Any suggestion or directions will be higly appreciated....

Regards,

Srikanth Pai

Hello,

To specify a rendering extension on a report server URL you need to use this syntax:

rs:Format=HTML3.2

Also, as far as I know PPC 2003 browsers support HTML 4.0. Why do you want to render to HTML 3.2?

Thanks,

Chris

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."

Display truncating on SQL Profiler

Greetings,

I am having a problem debugging an XML error we are getting in our production environment because I can't view the entire call to the stored procedure in Profiler. I have successfully traced the error, but when I go to the line with the call to the SP that caused the error, it doesn't show me the entire call. It only shows me the 'exec sproc_name and then the first 16 characters of the XML string parameter that is being passed to the proc. For some reason it's doing this to ONLY the stored procs that have XML parameters...on procs that use standard parameters, it displays the entire call correctly.

I have looked for some type of setting that controls this, but haven't been able to find it. I also have looked through many forums for this issue but to no avail. Does anyone know why this is happening? And, is there a workaround/fix?

Thanks in advance...

SBIs it because it's a text column? Do you see anny other calls using text, ntext or image?

Display stored procedure results in a view

If a stored procedure returns a table, is it possible to return that as a
view. Something like this (although I know that this SQL doesn't work)...
CREATE VIEW vw_Equipment
AS
EXEC proc_Equipment_List
Thanks,
CraigYou could use OPENROWSET or OPENQUERY, like:
CREATE VIEW vw_Equipment
AS
SELECT ... FROM OPENQUERY(..., 'EXEC proc_Equipment_List')
I wouldn't use this for production code, though, I find it to be a bit of a
hack.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
> If a stored procedure returns a table, is it possible to return that as a
> view. Something like this (although I know that this SQL doesn't work)...
> CREATE VIEW vw_Equipment
> AS
> EXEC proc_Equipment_List
>
> Thanks,
> Craig|||Hi Craig,
You just need to remember to set the dataaccess bit or the server to on
using the sp_serveroption. By default the server instance has data access
set off
Greg O
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OgZC9EbmFHA.3816@.tk2msftngp13.phx.gbl...
> You could use OPENROWSET or OPENQUERY, like:
>
> CREATE VIEW vw_Equipment
> AS
> SELECT ... FROM OPENQUERY(..., 'EXEC proc_Equipment_List')
> I wouldn't use this for production code, though, I find it to be a bit of
> a hack.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
>|||Here is the example i just posted in another thread.
exec sp_serveroption 'srv','data access','true'
go
if object_id('_v','v') is not null
drop view _v
go
create view _v
as
select *
from openquery(srv,'set fmtonly off; exec sp_lock')x
go
select * from _v
go
-oj
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:061063E2-ADE5-420E-8D23-C48D7F3B29F0@.microsoft.com...
> If a stored procedure returns a table, is it possible to return that as a
> view. Something like this (although I know that this SQL doesn't work)...
> CREATE VIEW vw_Equipment
> AS
> EXEC proc_Equipment_List
>
> Thanks,
> Craig

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need a
nice autonumber number)
Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)
|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)
|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:

> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
>
>

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need a
nice autonumber number)Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
--
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:
> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> > Hi there,
> >
> > I have the following problem. I would like to add an key to my view in the
> > form of a rowid. My view displays values that can come more then once.
> >
> > In Oracle there is one database kolumn called RowId what can be used in
> > any
> > select statements. Is there also something in SQL Server 2000?
> >
> > Or what can I do to get the rownumbers? There are no keys in my view and
> > the
> > values can be come more then once (so nothing is unique.. that's why I
> > need a
> > nice autonumber number)
>
>

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need
a
nice autonumber number)Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
--
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:

> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
>
>

Display date

Hi all

column "datetime" in table i need to create view and display only date

any formate do this

thanks&regards

Code Snippet

SELECT CONVERT(varchar(10), datecolumn, 101)

See CONVERT in BOL for more options than 101.

|||Look up CAST and CONVERT in books online.