Newbie Question about views

I have created a view by using following statement:-

ALTER VIEW vw_ctotalweeks AS
select DISTINCT allweek from hr_depts WHERE allweek > DateAdd(day,
-28, GetDate()) order by allweek desc

But I am getting follwoing error message:-

The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.

My question is, can I use Order by class in Views if not, how can sort
dates
in view

Please helpViews are like tables - their data is not logically ordered, which is why
ORDER BY isn't valid.

The way to sort a view is in the SELECT statement when you retrieve data
from it:

SELECT allweek
FROM vw_ctotalweeks
ORDER BY allweek DESC

--
David Portas
----
Please reply only to the newsgroup
--|||It is possible to order data in a view use the following select statment:

SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY CustomerID

The top 100 percent option makes the order by posible|||...so the create view statement would look something like this:

Create view v_orders as
SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY CustomerID|||Some people do suggest using this "trick" to order views. I believe that
there are good reasons to avoid doing this.

This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of defining the
TOP x PERCENT so intuitively you would not expect it to apply to the result
of a SELECT from the view. 99% of the time it *may* work but there is no
guarantee that it will always continue to work.

A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:

CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT NULL)

GO

CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y

GO

SELECT x FROM foo_view

The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimiser therefore has a choice either to ignore
the ORDER BY and retrieve an unsorted result set or to force a sort which
gives a sub-optimal execution plan.

As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which could
break your code if it relies on an undefined feature.

In short, don't use undocumented tricks as a substitute for good design and
if you do use this feature be aware of its limitations and risks.

SELECT * FROM view ORDER BY ...

Hope this helps.

--
David Portas
----
Please reply only to the newsgroup
--

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.
Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:

> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.
|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/Transacti...leGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?
|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:

> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/Transacti...leGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?
|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.

> How can I check which recovery mode is it in.
sp_helpdb
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...[vbcol=seagreen]
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:
> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:
> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.
> How can I check which recovery mode is it in.
sp_helpdb
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:
> > If you are talking about the database free space in enterprise manager then
> > that is data and log so it would probably be the logs that are causing the
> > problem.
> > If the database is in full recovery mode then transaction logs will fill up
> > until truncated or baced up. This does not happen until after the first
> > backup though (log is automatically truncated until then as the backups are
> > useless without a full backup). Maybe your truncate is causing the same
> > effect and you would start using more space after the next full backup.
> >
> > Have a look at
> > http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> >
> > If it's to do with just data then it's probably fragmentation or the way you
> > are checking the file space.
> > 30 - 90 sounds a lot though.
> > How is the audit trail taken? Do you have something that depends on the
> > previous data?

Newbie Question

How do I create a report in SQL?
I have a database created with a table that has names
I would like to print the table out by last name in ascending order.
Any ideas or help?
Thanks
Tom
----
I am using the free version of SPAMfighter for private users.
It has removed 61 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!If I'm understanding you correctly, you will want to open the Business
Intelligence Development environment, create a new reporting services
project and follow the report creation wizard and for the query enter
something like:
select * from NamesTable order by names
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Thomas Grassi wrote:
> How do I create a report in SQL?
> I have a database created with a table that has names
> I would like to print the table out by last name in ascending order.
> Any ideas or help?
> Thanks
> Tom
> ----
> I am using the free version of SPAMfighter for private users.
> It has removed 61 spam emails to date.
> Paying users do not have this message in their emails.
> Try SPAMfighter for free now!|||The way you have asked, I can make out your are very new... to SSRS..
Anyways the great way to start SSRS is to go to SQL 2005 online help
find "Reporting Services Tutorial" from the left pane and select the
"Creating a Basic Report" and go through all the 6 lessons, they are very
simple and you can start using RS for your purpose.
Amarnath, MCTS.
"Thomas Grassi" wrote:
> How do I create a report in SQL?
> I have a database created with a table that has names
> I would like to print the table out by last name in ascending order.
> Any ideas or help?
> Thanks
> Tom
> ----
> I am using the free version of SPAMfighter for private users.
> It has removed 61 spam emails to date.
> Paying users do not have this message in their emails.
> Try SPAMfighter for free now!
>
>

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?
Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> Hi
> Have you checked
> http://support.microsoft.com/default...b;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> to
>
|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.[vbcol=seagreen]
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
as[vbcol=seagreen]
to[vbcol=seagreen]
operation[vbcol=seagreen]
[OLE/DB[vbcol=seagreen]
0x8004d01b].
>
|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
sql

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> Hi
> Have you checked
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this
>> from the view designer also returns a nice result. However, if I try to
> to
>> save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributedtransaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>>
>> Anyone know what I'm doing wrong?
>>
>|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> > Hi
> >
> > Have you checked
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> >
> > John
> > <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> >> Hi. I've created a linked server to Oracle 8i. I want to save a view
as
> >> follows:
> >>
> >> SELECT *
> >> FROM ORACLE8I..SCOTT.EMP EMP_1
> >>
> >> From SQL Query Analyser, this returns a nice set or records. Running
> >> this
> >> from the view designer also returns a nice result. However, if I try
to
> > to
> >> save the view, I get the following error message:
> >>
> >> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
> >> could not be performed because the OLE DB provider 'MSDAORA' was unable
> >> to
> >> begin a distributedtransaction.
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB
> >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned
0x8004d01b].
> >>
> >>
> >>
> >> Anyone know what I'm doing wrong?
> >>
> >>
> >
> >
>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this from the view designer also returns a nice result. However, if I
>> try to to save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributed transaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trac
e [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?Hi
Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||Hi
Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> Hi
> Have you checked
> http://support.microsoft.com/defaul...kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> to
>|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
as[vbcol=seagreen]
to[vbcol=seagreen]
operation[vbcol=seagreen]
[OLE/DB[vbcol=seagreen]
0x8004d01b].[vbcol=seagreen]
>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>

Newbie problem: Saving a view from a linked server wont work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:

SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1

From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].

Anyone know what I'm doing wrong?Hi

Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106

John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?|||Hi

Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106

John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?|||None of the stuff in that article seems to help. Same error message occurs.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> Hi
> Have you checked
> http://support.microsoft.com/defaul...kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>>
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>>
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this
>> from the view designer also returns a nice result. However, if I try to
> to
>> save the view, I get the following error message:
>>
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributedtransaction.
>>
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>>
>>
>>
>> Anyone know what I'm doing wrong?
>>
>>|||Hi

This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd

John

<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> > Hi
> > Have you checked
> > http://support.microsoft.com/defaul...kb;EN-US;280106
> > John
> > <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> >> Hi. I've created a linked server to Oracle 8i. I want to save a view
as
> >> follows:
> >>
> >> SELECT *
> >> FROM ORACLE8I..SCOTT.EMP EMP_1
> >>
> >> From SQL Query Analyser, this returns a nice set or records. Running
> >> this
> >> from the view designer also returns a nice result. However, if I try
to
> > to
> >> save the view, I get the following error message:
> >>
> >> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
> >> could not be performed because the OLE DB provider 'MSDAORA' was unable
> >> to
> >> begin a distributedtransaction.
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB
> >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned
0x8004d01b].
> >>
> >>
> >>
> >> Anyone know what I'm doing wrong?
> >>
> >>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].

I assume that with "view designer" you mean what's in Enterprise Manager.

I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.

Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.

But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>>
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>>
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this from the view designer also returns a nice result. However, if I
>> try to to save the view, I get the following error message:
>>
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributed transaction.
>>
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?

Newbie problem using Windows Groups and Schema

I created a Windows Group in Active Directory ("Database1Users"). populated it with users, and planned to allow everyone in it to have access to a sql 2005 database.

I went to the sql server, Security (at the general level), Logins, New login, and created the Login "<domain name>\Database1Users". I assigned "Database1" as Default database, selected the database and assigned it to the above user name (which is actually a group name). I also typed in a default schema of "dbo" and gave the user account the role "db_owner" (just learning....) . Pressing OK gave me this error message:

>>>>

The DEFAULT_SCHEMA clause can not be used with a Windows Group or with principals mapped to certificates or an asymmetric keys."

>>>>

Oh..... how am I supposed to map a Windows group to give the users the access they need?

TIA,

barkingdog

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159533&SiteID=1.

You can map a Windows group, just don't try setting a default schema for it.

Thanks
Laurentiu

sql

newbie needs simple help

my database is on the attachment. Im decent with vb programming but suck with databases! Basically Ive to accomplish the following. I created code for a calendar that allow a user to enter a value for any date in the calendar. I need to take the values they entered into my vb calendar and save the dates with a value to my database. If I were working with one table it wouldnt be a problem. Unfortunately Im using ADO which only allows for 1 table connection at a time. To compensate for this i used 2 ado connections. 1 for each table. The details table shold store the date of an infraction for the employee(infraction-Date that employee screwed their attendance up by a no-call/no-show, coming in late, etc)

My problem is if an infraction is entered it might be the first time that employee got an infraction and i get an error because im moving both tables at the same time. I know basically im supposed to use an if exists clause for this and then next time the details page is available then re synchronize the tables according to employee id. My prob is I only know what it says in a book. Ive no practical experience. I guess what is like to see is a very simple
vb program hooked up to a database with 2 table a main table and a details table. Then I would be fine As i can pick apart the code to see what it does.

Normally Id just attach my project as a whole but as thier are very complicated calculations in it, Im not yet done with my error checking for these calulations.

Also from what I do know of databases and structure I currently have my table setup correctly I wanted to verify that at this point and hopefully ...see a sample program of this as stated above.You can only have one DATABASE per ADO Connection but the connection CAN operate on multiple tables as long as they all reside in the same database.|||Here's a VERY Q&D example that uses the Northwind DB and lists the products ordered for May 1998.

'******************************

Dim ado As New ADODB.Connection

Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

Dim sql As String

Private Sub Form_Load()

ado.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=GRAHAMT"

ado.Open

sql = "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate >= '1998/05/01' Order By OrderID"

rs1.Open sql, ado, adOpenForwardOnly, adLockReadOnly

If Not rs1.EOF Then
While Not rs1.EOF
sql = "SELECT ProductID From [Order Details] Where OrderID=" & CStr(rs1!OrderID)
rs2.Open sql, ado, adOpenForwardOnly, adLockReadOnly

If Not rs2.EOF Then
While Not rs2.EOF
Debug.Print rs1!OrderID, rs2!ProductID
rs2.MoveNext
Wend
End If

rs2.Close

rs1.MoveNext
Wend
End If

rs1.Close
ado.Close

Set rs1 = Nothing
Set rs2 = Nothing
Set ado = Nothing

End Sub|||... and before anybody says it, I know the example is lousy coding, it's just to show that you CAN have multiple tables open through one connection. Obviously a JOIN would be more efficient in the select but I'll leave the syntax of that for one of the SQL experts.

:)

newbie IIS & MS SQL connection problem ;-(

Have successfully installed MS SQL on my Win XP Pro machine, created tables,
and a dsn, which is working fine in Dreamweaver. So far so good.

When I run the page on IIS I get the following:-

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'MenuPlanner'. Login fails.

I think it's permissions - anyone got any clues as to where to look?

TIA

GrantBuzby wrote:
> Have successfully installed MS SQL on my Win XP Pro machine, created
tables,
> and a dsn, which is working fine in Dreamweaver. So far so good.
> When I run the page on IIS I get the following:-
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'MenuPlanner'. Login fails.
> I think it's permissions - anyone got any clues as to where to look?

You could see if the login MenuPlanner has got permissions to the
database you are logging in to. That would be a start.

--
David Rowland
For a good user and performance monitor, check DBMonitor
http://dbmonitor.tripod.com

> TIA
> Grant|||"dbmonitor" <dbmonitor_support@.hotmail.com> wrote in message
news:1107519068.678569.226760@.o13g2000cwo.googlegr oups.com...
> Buzby wrote:
>> Have successfully installed MS SQL on my Win XP Pro machine, created
> tables,
>> and a dsn, which is working fine in Dreamweaver. So far so good.
>>
>> When I run the page on IIS I get the following:-
>>
>> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
>> requested in login 'MenuPlanner'. Login fails.
>>
>> I think it's permissions - anyone got any clues as to where to look?
> You could see if the login MenuPlanner has got permissions to the
> database you are logging in to. That would be a start.

This is what has got me - permissions are set up. I've created a dsn, which
I can run queries in Dreamweaver and filter results just fine. I'm having
trouble when running the page on my webserver (IIS which is working fine)

Stumped ;-(|||Buzby wrote:
> "dbmonitor" <dbmonitor_support@.hotmail.com> wrote in message
> news:1107519068.678569.226760@.o13g2000cwo.googlegr oups.com...
> > Buzby wrote:
> >> Have successfully installed MS SQL on my Win XP Pro machine,
created
> > tables,
> >> and a dsn, which is working fine in Dreamweaver. So far so good.
> >>
> >> When I run the page on IIS I get the following:-
> >>
> >> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
database
> >> requested in login 'MenuPlanner'. Login fails.
> >>
> >> I think it's permissions - anyone got any clues as to where to
look?
> > You could see if the login MenuPlanner has got permissions to the
> > database you are logging in to. That would be a start.
> This is what has got me - permissions are set up. I've created a dsn,
which
> I can run queries in Dreamweaver and filter results just fine. I'm
having
> trouble when running the page on my webserver (IIS which is working
fine)
> Stumped ;-(

Is MenuPlanner the database name or the login name?

If it is the database name, are you connecting to the database via a
userid/password or are you connecting with Windows interactive UserID?
--
David Rowland
For a good user and performance monitor, check DBMonitor
http://dbmonitor.tripod.com|||Buzby (gb@.pumpupthe.net) writes:
> Have successfully installed MS SQL on my Win XP Pro machine, created
> tables, and a dsn, which is working fine in Dreamweaver. So far so
> good.
> When I run the page on IIS I get the following:-
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'MenuPlanner'. Login fails.
> I think it's permissions - anyone got any clues as to where to look?

Sounds like the login has a default db which does not exist, or the login
does have access to. Use sp_helplogins to check, use sp_defaultdb to change.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 2/4/05 5:37 PM, in article Xns95F3F003A2514Yazorman@.127.0.0.1, "Erland
Sommarskog" <esquel@.sommarskog.se> wrote:

> Buzby (gb@.pumpupthe.net) writes:
>> Have successfully installed MS SQL on my Win XP Pro machine, created
>> tables, and a dsn, which is working fine in Dreamweaver. So far so
>> good.
>>
>> When I run the page on IIS I get the following:-
>>
>> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
>> requested in login 'MenuPlanner'. Login fails.
>>
>> I think it's permissions - anyone got any clues as to where to look?
> Sounds like the login has a default db which does not exist, or the login
> does have access to. Use sp_helplogins to check, use sp_defaultdb to change.

Yea, but a DSN is being used so we have to believe that MenuPlanner is the
name of the DSN Connection. I am assuming that the DSN was created on the
IIS server and connects successfully when you "test connection" in the ODBC
dialog.

I highly recommend using a DSN-less connection in your ASP pages. There is
lots of documentation on ADODB.

If you need some sample connection strings let me know.|||Gregory Dean (gdean@.datapex.com) writes:
> On 2/4/05 5:37 PM, in article Xns95F3F003A2514Yazorman@.127.0.0.1, "Erland
> Sommarskog" <esquel@.sommarskog.se> wrote:
>> Buzby (gb@.pumpupthe.net) writes:
>>> Have successfully installed MS SQL on my Win XP Pro machine, created
>>> tables, and a dsn, which is working fine in Dreamweaver. So far so
>>> good.
>>>
>>> When I run the page on IIS I get the following:-
>>>
>>> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
>>> requested in login 'MenuPlanner'. Login fails.
>>>
>>> I think it's permissions - anyone got any clues as to where to look?
>>
>> Sounds like the login has a default db which does not exist, or the
>> login does have access to. Use sp_helplogins to check, use sp_defaultdb
>> to change.
> Yea, but a DSN is being used so we have to believe that MenuPlanner is
> the name of the DSN Connection. I am assuming that the DSN was created
> on the IIS server and connects successfully when you "test connection"
> in the ODBC dialog.

Not sure what you mean, but since SQL Server does not know what a DSN
is, MenuPlanner cannot be the name of the DNS. But it can be the
login name specified in the DSN.

> I highly recommend using a DSN-less connection in your ASP pages.

I echo that. DSN is a concept that I never understood the point with.
An extra layer that only causes hassle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks one and all - it turned out it was an IIS permissions issue - however
I've taken on board what you have suggested and dsn less is the way we are
going!

Cheers

Buzby

Newbie help: SQL Server does not exist or access denied

I have created a simple ASP page, but when I call it up, I receive the following error:
SQL Server does not exist or access denied
I am using SQL Server 2000 and Win2K. Any idea what may be causing this error?What are you supplying for your connection string?

1) SQL Server name
2) Username
3) Password
4) Database

If you use a trusted connection remember that the ASP page will connect to SQL Server as the user account that IIS is running under.|||Hi,

I am getting the same error message:

"SQL Server does not exist or access denied"

when I attempt to register a SQL Server in SQL Server 2000.

Any ideas?

Thanks,

Chris|||'csf' can you ping the server from your workstation

c:\> ping MyServer

I have a feeling that you can not see the server by its user friendly name. Can you ping it by it's IP address.

c:\ ping xxx.xxx.xxx.xxx

If so, then use the Client Network Utility for SQL Server and define an Alias to this server. Set the network library to TCP/IP, set the server alias name to the server name and enter the IP address for the server name.|||Thanks for the reply.

I will try this when I get home this evening :)

newbie - Stored procedure

I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...One proc should do it. You can have parameters with default values, if you
so choose:
create proc MyProc
(
@.id int
, @.x char (5) = 'ALFKI'
)
as
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Boonaap" <Boonaap@.discussions.microsoft.com> wrote in message
news:415B2831-6626-457A-98D4-42ED64F2C7F7@.microsoft.com...
I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...

Newbie - Querying SQL Server Express 2005 database from Excel

Sorry if this is a stupid question, but I created a database using SQl Server Express 2005 and I would like to query one of its tables from an Excel spreadsheet.

Here's what I want to do in pseudo code.

cell A2.value = select OLIGO_ID from table OLIGO where SEQUENCE = 'content of cell D2, a string'

In other words, I want to search the database for a string that is in a cell and retrieve its associated ID number into another cell. I need to do this on many cells.

Any help is appreciated. Thanks.

I guess you can do that with VBA.

As far as SQL is concerned I do something like that to extract data from a cube down to Excel. You will just have to build the connection string for SQL Express (get it from the macro recorder) and adjust the query to a more TSQL like query rather than OLAP. You can build any string you want.

Function to query SQL with a query string

-

Private Sub ado(Connection As String, Query As String, destination As String)
Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset

Set cnnConnect = New ADODB.Connection
cnnConnect.Open Connection

Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:=Query, _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText

With ActiveSheet.QueryTables.Add( _
Connection:=rstRecordset, _
destination:=Range(destination))
.FieldNames = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
cnnConnect.Close
Set cnnConnect = Nothing
Set rstRecordset = Nothing
End Sub

-

-- OLAPMENU

OlapMenu = _
"Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Location=analysis.onsemi.com;Initial Catalog=" & InitialCatalog & ""

--

call to the function

Query = "select" & _
"[data Switch].[Switch].members on axis(0)," & _
" Filter( [Region].[Rep Sales Region Desc].members, [Data Switch].[On] >0 ) on axis(1)" & _
"from [ST_Crawl]"
Application.StatusBar = "Now Populating Pull-down Region, please wait..."
Application.Cursor = xlWait
Call ado(OlapMenu, Query, "A3")

|||Yes, I would go as far as say write a function in Excel that accepts the input parameter, then uses ADO to fetch the data. Then you can pass in the value from the spreadsheet into the function, the function takes the parameter and uses an ADODB.Command with params to execute the SQL statement, then the function returns the output as a string.

Newbie - Changing ordered list

Hi,

I would like to programmatically move rows up or down an ordered list. I
have created a column to ORDER BY and filled with ascending integer values.
Swapping the column values with the row above or the row below changes the
row position OK but things start getting complicated when rows are deleted
or new rows are added (e.g. what column value to assign to the new row). Is
there a simple way of doing this?

ThanksJackT (turnbull.jack@.ntlworld.com) writes:
> I would like to programmatically move rows up or down an ordered list. I
> have created a column to ORDER BY and filled with ascending integer
> values. Swapping the column values with the row above or the row below
> changes the row position OK but things start getting complicated when
> rows are deleted or new rows are added (e.g. what column value to assign
> to the new row). Is there a simple way of doing this?

I am sorry, but you need to explain a lot more of what you are doing.
Are you moving rows in a table, or are they rows in a screen form?

Generally, for many types of question in this newsgroup it is a good
idea to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result with the sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

CREATE TABLE [CategoryList] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryIndex] [int] NULL ,
[CategoryName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('First',0)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Second',1)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Third',2)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Fourth',3)
GO
SELECT * FROM [CategoryList] ORDER BY CategoryIndex
GO

Gives Output

1 0 First
2 1 Second
3 2 Third
4 3 Fourth

UPDATE [CategoryList]
SET [CategoryIndex]=2 WHERE [CategoryID] = 2
UPDATE [CategoryList]
SET [CategoryIndex]=1 WHERE [CategoryID] = 3
SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
GO

Gives Output

1 0 First
3 1 Third
2 2 Second
4 4 Fourth

Is this the best method of setting up a list so you can swap the ordering
programmatically?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns953BACDF25089Yazorman@.127.0.0.1...
> JackT (turnbull.jack@.ntlworld.com) writes:
> > I would like to programmatically move rows up or down an ordered list. I
> > have created a column to ORDER BY and filled with ascending integer
> > values. Swapping the column values with the row above or the row below
> > changes the row position OK but things start getting complicated when
> > rows are deleted or new rows are added (e.g. what column value to assign
> > to the new row). Is there a simple way of doing this?
> I am sorry, but you need to explain a lot more of what you are doing.
> Are you moving rows in a table, or are they rows in a screen form?
> Generally, for many types of question in this newsgroup it is a good
> idea to include:
> o CREATE TABLE statements for the involved tables.
> o INSERT statements with sample data.
> o The desired result with the sample data.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JackT (turnbull.jack@.ntlworld.com) writes:
> UPDATE [CategoryList]
> SET [CategoryIndex]=2 WHERE [CategoryID] = 2
> UPDATE [CategoryList]
> SET [CategoryIndex]=1 WHERE [CategoryID] = 3
> SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
> GO
> Gives Output
> 1 0 First
> 3 1 Third
> 2 2 Second
> 4 4 Fourth
> Is this the best method of setting up a list so you can swap the ordering
> programmatically?

OK, so what you basically after is a sorter value who tells you in
which order to present the data?

I can't think of any radically different way of doing this, although
some varitions are possible, for instance using 100, 200 etc as values
initially. On the other hand, having a contiguous series, can actually
make it easier to maintain the list.

Insert a value at point n:

BEGIN TRANSACTION

UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex >= @.Indexfornew

INSERT CategoryList(CategoryName, CategoryIndex)
VALUES (@.newname, @.Indexfornew)

COMMIT TRANSACTION

Delete an entry:

BEGIN TRANSACTION

SELECT @.indexforold = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtodelete

DELETE CategoryIndex FROM CategoryList WHERE CategoryID = @.idtodelete

UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.indexforold

COMMIT TRANSACTION

Move an entry to position @.n:

BEGIN TRANSACTION

SELECT @.currentindex = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtomove

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.idtomove

IF @.n > @.currentindex
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.currentindex AND CategoryIndex <= @.n
END
ELSE
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex < @.currentindex AND CategoryIndex >= @.n
END

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.n

COMMIT TRANSACTION

Here I have assumed that you don't to have ties, and thus a UNIQUE
constraint on the index column is a good idea.

All the above is untested - you should have some fun too! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

This looks pretty much what I'm after. I'll try it out ASAP and tune as
required. Grateful thanks.

Jack

newbee question on function

Hi,
I created some functions in SQL server 2000 server. Every time I use the
functions I created I have to prefix them with dbo., say dbo.myFunction. Is
there a way to get around it?
TIANope. Qualifying scalar UFD with owner is mandatory. In fact, owner qualifyi
ng in general is a very
good thing to do, so you should get into the habit of always doing it...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny Ni" <dnd@.yahoo.com> wrote in message news:%23KV4rzSaFHA.2128@.TK2MSFTNGP14.phx.gbl...

> Hi,
> I created some functions in SQL server 2000 server. Every time I use the
> functions I created I have to prefix them with dbo., say dbo.myFunction. I
s
> there a way to get around it?
> TIA
>

Newb question

I am attempting to retrieve specific task related data from a Project server
environment for input to an Excel 2003 spreadsheet and have created the
following SQL view using:
SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
dbo.MSP_TASKS.TASK_IS_MILESTONE
FROM dbo.MSP_PROJECTS CROSS JOIN
dbo.MSP_TASKS
WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
(dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
My logic here is that this query will pull back all tasks that are
milestones from 'project 92' only. However, this query pulls back all
milestones from all projects... I have no idea why, could someone please
shed some light on this newb.
Many thanks,
Nock (SQL Newb, Australia)I think the CROSS JOIN might be a clue. I would just use a JOIN
"Nock" wrote:

> I am attempting to retrieve specific task related data from a Project serv
er
> environment for input to an Excel 2003 spreadsheet and have created the
> following SQL view using:
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS CROSS JOIN
> dbo.MSP_TASKS
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> My logic here is that this query will pull back all tasks that are
> milestones from 'project 92' only. However, this query pulls back all
> milestones from all projects... I have no idea why, could someone please
> shed some light on this newb.
> Many thanks,
> Nock (SQL Newb, Australia)|||it is because of the CROSS JOIN used to connect the MSP_PROJECTS and
MSP_TASKS tables. A cross join between two tables produces what is known as
a
Cartesian product, which is a table that contains all of the possible
combinations between the rows between the input tables. In other words, a
cross join between two tables X and Y with x and y rows respectively will
contain 1 row for each y rows for each row in X, for a total of x times y
rows.
What you probably want is something like this
SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
dbo.MSP_TASKS.TASK_IS_MILESTONE
FROM dbo.MSP_PROJECTS INNER JOIN
dbo.MSP_TASKS ON dbo.MSP_PROJECTS.PROJ_ID =
dbo.MSP_TASKS.PROJ_ID
WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
(dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
"Nock" wrote:

> I am attempting to retrieve specific task related data from a Project serv
er
> environment for input to an Excel 2003 spreadsheet and have created the
> following SQL view using:
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS CROSS JOIN
> dbo.MSP_TASKS
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> My logic here is that this query will pull back all tasks that are
> milestones from 'project 92' only. However, this query pulls back all
> milestones from all projects... I have no idea why, could someone please
> shed some light on this newb.
> Many thanks,
> Nock (SQL Newb, Australia)|||Nock,
What is the common column between the tables? n other words, does MSP_TASKS
have a foreign Key column to the Primary Key column on MSP_PROJECTS, or
vice-versa.
Whichever is the key column you should then use a standard JOIN statement.
If you want just the Tasks that are associated with Project 92, then you
should use the following...
SELECT proj.PROJ_NAME,
task.TASK_NAME,
task.TASK_IS_MILESTONE
FROM dbo.MSP_PROJECTS proj LEFT JOIN
dbo.MSP_TASKS task ON proj.{PK} = task.{FK}
WHERE (proj.PROJ_ID = 92)
AND (task.TASK_IS_MILESTONE = 1)
The LEFT Join will ensure you get all Project Data back together with any
Task data that is relevent, or NULL values if none present. If you use INNER
join then there will need to be at least one reacord in each table.
Enjoy,
"Nock" wrote:

> I am attempting to retrieve specific task related data from a Project serv
er
> environment for input to an Excel 2003 spreadsheet and have created the
> following SQL view using:
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS CROSS JOIN
> dbo.MSP_TASKS
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> My logic here is that this query will pull back all tasks that are
> milestones from 'project 92' only. However, this query pulls back all
> milestones from all projects... I have no idea why, could someone please
> shed some light on this newb.
> Many thanks,
> Nock (SQL Newb, Australia)|||Thanks Mark et al, much appreciated and great explanation.
I'm in one of those situations where I've been asked to become a 'SQL
person' in a day...
Loving life :)
Cheers,
Nock
"Mark Williams" wrote:
> it is because of the CROSS JOIN used to connect the MSP_PROJECTS and
> MSP_TASKS tables. A cross join between two tables produces what is known a
s a
> Cartesian product, which is a table that contains all of the possible
> combinations between the rows between the input tables. In other words, a
> cross join between two tables X and Y with x and y rows respectively will
> contain 1 row for each y rows for each row in X, for a total of x times y
> rows.
> What you probably want is something like this
> SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME,
> dbo.MSP_TASKS.TASK_IS_MILESTONE
> FROM dbo.MSP_PROJECTS INNER JOIN
> dbo.MSP_TASKS ON dbo.MSP_PROJECTS.PROJ_ID =
> dbo.MSP_TASKS.PROJ_ID
> WHERE (dbo.MSP_PROJECTS.PROJ_ID = 92) AND
> (dbo.MSP_TASKS.TASK_IS_MILESTONE = 1)
> --
> "Nock" wrote:
>|||Try these sites for a general SQL overview...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
"Nock" <Nock@.discussions.microsoft.com> wrote in message
news:05979A9F-A3BC-401B-867F-CDC7114D2CE1@.microsoft.com...
> Thanks Mark et al, much appreciated and great explanation.
> I'm in one of those situations where I've been asked to become a 'SQL
> person' in a day...
> Loving life :)
> Cheers,
> Nock
> "Mark Williams" wrote:
>
as a
a
will
y
server
the
please