I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. How would I use my qry to accomplish this task.
I appreciate the help.
Here's my qry:
Select GroupType, Sum(SumRevAmt) as Last12Mnths
from MyQRY
Where Period = '200006'
Group by GroupType
Type Last12Mnths_200006 Prior12Mnths_199906
Airlines 1234.50 '
Concessions 73854.00 '
etc......Russell Verdun wrote:
> I have a query that generates the dataset below, based on the year being
> filtered I get the sum of an amount Group By the type. What I would like t
o
> do is use the exact qry using a differnet date, to generate a third column
> called Prior12Mnths. How would I use my qry to accomplish this task.
> I appreciate the help.
> Here's my qry:
> Select GroupType, Sum(SumRevAmt) as Last12Mnths
> from MyQRY
> Where Period = '200006'
> Group by GroupType
>
>
> Type Last12Mnths_200006 Prior12Mnths_199906
> Airlines 1234.50 '?
?
> Concessions 73854.00 '
> etc......
>
>
It's not clear from your example what this "other date" would be, so
I'll use a different example. Say I have a table containing
transactions, and each transaction consists of an account, a transaction
date, and an amount:
SELECT
account,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
ELSE 0) AS Last12Months,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
THEN amount ELSE 0) AS Prev12Months
FROM table
GROUP BY account
Is that enough to get you started?|||Tracy McKibben wrote:
> Russell Verdun wrote:
> It's not clear from your example what this "other date" would be, so
> I'll use a different example. Say I have a table containing
> transactions, and each transaction consists of an account, a transaction
> date, and an amount:
> SELECT
> account,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
> ELSE 0) AS Last12Months,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
> THEN amount ELSE 0) AS Prev12Months
> FROM table
> GROUP BY account
> Is that enough to get you started?
>
Sorry, those CASE statements are missing ENDs...|||Hi Russell,
I believe you could do something like this:
Select GroupType, Sum(CASE Period=200006 THEN SumRevAmt ELSE 0 END) as
Last12Mnths, Sum(CASE Period=199906 THEN SumRevAmt ELSE 0 END) as
Prior12Mnths
from MyQRY
Where Period = '200006' or Period = '199906'
Group by GroupType
I Dont know if thats the best way, but that is what first comes to
mind.
Paul T.|||>> I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is also helpful if the data elements have good
names.
CREATE TABLE Revenues -- guess at meaningful name
(grp_type INTEGER NOT NULL
REFERENCES GroupTypes(grp_type),
rev_amt DECIMAL(12,2) NOT NULL,
rev_date DATETIME NOT NULL PRIMARY KEY);
In the vague pseudo-code you posted, only some kind of vague date can
be a key
The best trick for this kind of summary is to build a reporting range
table
CREATE TABLE ReportRanges
(range_name CHAR() NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (range_name, start_date));
INSERT INTO ReportRanges
VALUES ('2006-06: Prior12' , '2005-06-01', '2006-06-31' );
INSERT INTO ReportRanges
VALUES ('2006-06: ytd' , '2006-01-01', '2006-06-31' );
SELECT grp_type,
SUM (CASE WHEN R.range_name = '2006-06: ytd'
THEN rev_amt ELSE 0.00 END) AS ytd,
SUM (CASE WHEN R.range_name = '2006-06: Prior12'
THEN rev_amt ELSE 0.00 END) AS Prior12,
etc.
FROM Revenues
GROUP BY grp_type;
Adjust the table as needed.
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Advice on CASE statement
Can someone guide me in the correct direction as to what's going on with
this case statement:
This was derived from and access IIF statement
Access IIF statement:
IIf([meterreading] & ""="" Or [previousreading] & ""="",Null,
(IIf([MeterReading]>=[PreviousReading],[MeterReading]- [previousreading],[MeterReading]+([Rollo
ver]- [PreviousReading]))*IIf(IsNull([MultiFac
tor]),1,[MultiFactor]))+nz([UsageAdjustm
ent],0))
SQL Case statement:
select
case when 10000.0 is null or 1.0 is null
then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
then 1 else 22.0 + isnull(4.0,0) end as CurUsage
Above is a sample of the case statement with the actual values instead of
the field names used in the actual statement so you may test. The result I
require and the result access renders using the exact values is 219982, but
the result I get from SQL is 259974. I believe that my case statement is
formatted incorrectly. If I simply compute all the value that would be
returned base on the case statement conditions I get the value I s
.
select (10000.0 - 1.0) * 22.0 + isnull(4.0,0) = 219982
Any Ideas?
Best Regards,
Advice on Case StatementThe problem is that it will evaluate inside out. So the 22 + 4 is evaluated
prior to the multiplication.
I think just you just need to move your last END statement.
As in the following:
select
case when 10000.0 is null or 1.0 is null
then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
then 1 else 22.0 end + isnull(4.0,0) as CurUsage
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Tim Harvey" wrote:
> Can someone guide me in the correct direction as to what's going on with
> this case statement:
> This was derived from and access IIF statement
> Access IIF statement:
> IIf([meterreading] & ""="" Or [previousreading] & ""="",Null,
> (IIf([MeterReading]>=[PreviousReading],[MeterReading]- [previousreading],[MeterReading]+([Rollo
ver]- [PreviousReading]))*IIf(IsNull([MultiFac
tor]),1,[MultiFactor]))+nz([UsageAdjustm
ent],0))
>
> SQL Case statement:
> select
> case when 10000.0 is null or 1.0 is null
> then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
> else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
> then 1 else 22.0 + isnull(4.0,0) end as CurUsage
> Above is a sample of the case statement with the actual values instead of
> the field names used in the actual statement so you may test. The result I
> require and the result access renders using the exact values is 219982, bu
t
> the result I get from SQL is 259974. I believe that my case statement is
> formatted incorrectly. If I simply compute all the value that would be
> returned base on the case statement conditions I get the value I s.
> select (10000.0 - 1.0) * 22.0 + isnull(4.0,0) = 219982
>
> Any Ideas?
>
> Best Regards,
> Advice on Case Statement
>
>|||1) First thing to learn is that ther is no CASE statement in SQL; there
is a CASE **expression**!! Important!! Expressions have a single data
type. Expressions have nothign to do with control flow; since SQL is a
declarative language, there is no concept whatsoever of control flow!!
This is basic programming concepts.
2) "CASE WHEN 10000.0 IS NULL OR .. " is absurd code; it is always
UNKNOWN. Do you understand the 3-Valued Logic in SQL?
3) Above is a sample of the case statement [sic] with the actual values
instead of the field [sic] names used in the actual statement so you may
test. <<
Columns are not anything like fields; please learn the foundations,
concepts and the right words. Now, how do you expect us to debug code
you will not show us? How do we tell the constants from the columns?
You failed to post even minimal DDL or a spec that makes sense. Try
again, if you really want help.
The CASE expression is an *expression* and not a control statement; that
is, it returns a value of one datatype. SQL-92 stole the idea and the
syntax from the ADA programming language. Here is the BNF for a <case
specification>:
<case specification> ::= <simple case> | <searched case>
<simple case> ::=
CASE <case operand>
<simple when clause>...
[<else clause>]
END
<searched case> ::=
CASE
<searched when clause>...
[<else clause>]
END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN <result>
<else clause> ::= ELSE <result>
<case operand> ::= <value expression>
<when operand> ::= <value expression>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>
The searched CASE expression is probably the most used version of the
expression. The WHEN ... THEN ... clauses are executed in left to right
order. The first WHEN clause that tests TRUE returns the value given in
its THEN clause. And, yes, you can nest CASE expressions inside each
other. If no explicit ELSE clause is given for the CASE expression,
then the database will insert a default ELSE NULL clause. If you want
to return a NULL in a THEN clause, then you must use a CAST (NULL AS
<datatype> ) expression. I recommend always giving the ELSE clause, so
that you can change it later when you find something explicit to return.
The <simple case expression> is defined as a searched CASE expression in
which all the WHEN clauses are made into equality comparisons against
the <case operand>. For example
CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 9 THEN 'N/A'
ELSE NULL END
could also be written as:
CASE
WHEN iso_sex_code = 0 THEN 'Unknown'
WHEN iso_sex_code = 1 THEN 'Male'
WHEN iso_sex_code = 2 THEN 'Female'
WHEN iso_sex_code = 9 THEN 'N/A'
ELSE NULL END
There is a gimmick in this definition, however. The expression
CASE foo
WHEN 1 THEN 'bar'
WHEN NULL THEN 'no bar'
END
becomes
CASE WHEN foo = 1 THEN 'bar'
WHEN foo = NULL THEN 'no_bar' -- error!
ELSE NULL END
The second WHEN clause is always UNKNOWN.
The SQL-92 Standard defines other functions in terms of the CASE
expression, which makes the language a bit more compact and easier to
implement. For example, the COALESCE () function can be defined for one
or two expressions by
1) COALESCE (<value exp #1> ) is equivalent to (<value exp #1> )
2) COALESCE (<value exp #1>, <value exp #2> ) is equivalent to
CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE <value exp #2> END
then we can recursively define it for (n) expressions, where (n >= 3),
in the list by
COALESCE (<value exp #1>, <value exp #2>, . . ., n), as equivalent to:
CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE COALESCE (<value exp #2>, . . ., n)
END
Likewise, NULLIF (<value exp #1>, <value exp #2> ) is equivalent to:
CASE WHEN <value exp #1> = <value exp #2>
THEN NULL
ELSE <value exp #1> END
It is important to be sure that you have a THEN or ELSE clause with a
datatype that the compiler can find to determine the highest datatype
for the expression.
A trick in the WHERE clause is use it for a complex predicate with
material implications.
WHERE CASE
WHEN <search condition #1>
THEN 1
WHEN <search condition #2>
THEN 1
..
ELSE 0 END = 1
Gert-Jan Strik posted some exampels of how ISNULL() and COALESCE() on
2004 Aug 19
CREATE TABLE #t(a CHAR(1));
INSERT INTO #t VALUES (NULL);
SELECT ISNULL(a,'abc') FROM #t;
SELECT COALESCE(a, 'abc') FROM #t;
DROP TABLE #t;
He always use COALESCE, with the exception of the following type of
situation, because of its performance consequences:
SELECT ...,
ISNULL((SELECT COUNT(*) -- or other aggregate
FROM B
WHERE B.key = A.key), 0)
FROM A;
Likewise, Alejandro Mesa cam up with this example:
SELECT 13 / COALESCE(CAST(NULL AS INTEGER), 2.00); -- promote to highest
type (decimal)
SELECT 13 / ISNULL(CAST(NULL AS INTEGER), 2.00); -- promote to first
type (integer)
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***
this case statement:
This was derived from and access IIF statement
Access IIF statement:
IIf([meterreading] & ""="" Or [previousreading] & ""="",Null,
(IIf([MeterReading]>=[PreviousReading],[MeterReading]- [previousreading],[MeterReading]+([Rollo
ver]- [PreviousReading]))*IIf(IsNull([MultiFac
tor]),1,[MultiFactor]))+nz([UsageAdjustm
ent],0))
SQL Case statement:
select
case when 10000.0 is null or 1.0 is null
then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
then 1 else 22.0 + isnull(4.0,0) end as CurUsage
Above is a sample of the case statement with the actual values instead of
the field names used in the actual statement so you may test. The result I
require and the result access renders using the exact values is 219982, but
the result I get from SQL is 259974. I believe that my case statement is
formatted incorrectly. If I simply compute all the value that would be
returned base on the case statement conditions I get the value I s
.select (10000.0 - 1.0) * 22.0 + isnull(4.0,0) = 219982
Any Ideas?
Best Regards,
Advice on Case StatementThe problem is that it will evaluate inside out. So the 22 + 4 is evaluated
prior to the multiplication.
I think just you just need to move your last END statement.
As in the following:
select
case when 10000.0 is null or 1.0 is null
then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
then 1 else 22.0 end + isnull(4.0,0) as CurUsage
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Tim Harvey" wrote:
> Can someone guide me in the correct direction as to what's going on with
> this case statement:
> This was derived from and access IIF statement
> Access IIF statement:
> IIf([meterreading] & ""="" Or [previousreading] & ""="",Null,
> (IIf([MeterReading]>=[PreviousReading],[MeterReading]- [previousreading],[MeterReading]+([Rollo
ver]- [PreviousReading]))*IIf(IsNull([MultiFac
tor]),1,[MultiFactor]))+nz([UsageAdjustm
ent],0))
>
> SQL Case statement:
> select
> case when 10000.0 is null or 1.0 is null
> then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
> else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
> then 1 else 22.0 + isnull(4.0,0) end as CurUsage
> Above is a sample of the case statement with the actual values instead of
> the field names used in the actual statement so you may test. The result I
> require and the result access renders using the exact values is 219982, bu
t
> the result I get from SQL is 259974. I believe that my case statement is
> formatted incorrectly. If I simply compute all the value that would be
> returned base on the case statement conditions I get the value I s
.> select (10000.0 - 1.0) * 22.0 + isnull(4.0,0) = 219982
>
> Any Ideas?
>
> Best Regards,
> Advice on Case Statement
>
>|||1) First thing to learn is that ther is no CASE statement in SQL; there
is a CASE **expression**!! Important!! Expressions have a single data
type. Expressions have nothign to do with control flow; since SQL is a
declarative language, there is no concept whatsoever of control flow!!
This is basic programming concepts.
2) "CASE WHEN 10000.0 IS NULL OR .. " is absurd code; it is always
UNKNOWN. Do you understand the 3-Valued Logic in SQL?
3) Above is a sample of the case statement [sic] with the actual values
instead of the field [sic] names used in the actual statement so you may
test. <<
Columns are not anything like fields; please learn the foundations,
concepts and the right words. Now, how do you expect us to debug code
you will not show us? How do we tell the constants from the columns?
You failed to post even minimal DDL or a spec that makes sense. Try
again, if you really want help.
The CASE expression is an *expression* and not a control statement; that
is, it returns a value of one datatype. SQL-92 stole the idea and the
syntax from the ADA programming language. Here is the BNF for a <case
specification>:
<case specification> ::= <simple case> | <searched case>
<simple case> ::=
CASE <case operand>
<simple when clause>...
[<else clause>]
END
<searched case> ::=
CASE
<searched when clause>...
[<else clause>]
END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN <result>
<else clause> ::= ELSE <result>
<case operand> ::= <value expression>
<when operand> ::= <value expression>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>
The searched CASE expression is probably the most used version of the
expression. The WHEN ... THEN ... clauses are executed in left to right
order. The first WHEN clause that tests TRUE returns the value given in
its THEN clause. And, yes, you can nest CASE expressions inside each
other. If no explicit ELSE clause is given for the CASE expression,
then the database will insert a default ELSE NULL clause. If you want
to return a NULL in a THEN clause, then you must use a CAST (NULL AS
<datatype> ) expression. I recommend always giving the ELSE clause, so
that you can change it later when you find something explicit to return.
The <simple case expression> is defined as a searched CASE expression in
which all the WHEN clauses are made into equality comparisons against
the <case operand>. For example
CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 9 THEN 'N/A'
ELSE NULL END
could also be written as:
CASE
WHEN iso_sex_code = 0 THEN 'Unknown'
WHEN iso_sex_code = 1 THEN 'Male'
WHEN iso_sex_code = 2 THEN 'Female'
WHEN iso_sex_code = 9 THEN 'N/A'
ELSE NULL END
There is a gimmick in this definition, however. The expression
CASE foo
WHEN 1 THEN 'bar'
WHEN NULL THEN 'no bar'
END
becomes
CASE WHEN foo = 1 THEN 'bar'
WHEN foo = NULL THEN 'no_bar' -- error!
ELSE NULL END
The second WHEN clause is always UNKNOWN.
The SQL-92 Standard defines other functions in terms of the CASE
expression, which makes the language a bit more compact and easier to
implement. For example, the COALESCE () function can be defined for one
or two expressions by
1) COALESCE (<value exp #1> ) is equivalent to (<value exp #1> )
2) COALESCE (<value exp #1>, <value exp #2> ) is equivalent to
CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE <value exp #2> END
then we can recursively define it for (n) expressions, where (n >= 3),
in the list by
COALESCE (<value exp #1>, <value exp #2>, . . ., n), as equivalent to:
CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE COALESCE (<value exp #2>, . . ., n)
END
Likewise, NULLIF (<value exp #1>, <value exp #2> ) is equivalent to:
CASE WHEN <value exp #1> = <value exp #2>
THEN NULL
ELSE <value exp #1> END
It is important to be sure that you have a THEN or ELSE clause with a
datatype that the compiler can find to determine the highest datatype
for the expression.
A trick in the WHERE clause is use it for a complex predicate with
material implications.
WHERE CASE
WHEN <search condition #1>
THEN 1
WHEN <search condition #2>
THEN 1
..
ELSE 0 END = 1
Gert-Jan Strik posted some exampels of how ISNULL() and COALESCE() on
2004 Aug 19
CREATE TABLE #t(a CHAR(1));
INSERT INTO #t VALUES (NULL);
SELECT ISNULL(a,'abc') FROM #t;
SELECT COALESCE(a, 'abc') FROM #t;
DROP TABLE #t;
He always use COALESCE, with the exception of the following type of
situation, because of its performance consequences:
SELECT ...,
ISNULL((SELECT COUNT(*) -- or other aggregate
FROM B
WHERE B.key = A.key), 0)
FROM A;
Likewise, Alejandro Mesa cam up with this example:
SELECT 13 / COALESCE(CAST(NULL AS INTEGER), 2.00); -- promote to highest
type (decimal)
SELECT 13 / ISNULL(CAST(NULL AS INTEGER), 2.00); -- promote to first
type (integer)
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***
Advice on ALTER TABLE statement.
Can I use an ALTEER TABLE statement in a stored procedure, I need to drop a
named constraint. Getting complie error.
ThanksTim Gains wrote:
> Can I use an ALTEER TABLE statement in a stored procedure, I need to
> drop a named constraint. Getting complie error.
>
> Thanks
You can use alter table from within a procedure (not sure why you woudl
want to). Maybe you're using dynamic sql and should be using either an
EXEC statement or sp_executesql. What is the reason for doing this from
a stored procedure (you can't inherit alter table rights with a
procedure grant on execute). It seems that if you're not using dynamic
sql for this in the procedure as some type of admin functionality (e.g.
to alter table ddl from your own procedure for admins), then you might
be better served by just executing the alter table as a batch without a
procedure. Can you explain why you need the procedure and post your
code?
David Gugick
Quest Software
www.imceda.com
www.quest.com
named constraint. Getting complie error.
ThanksTim Gains wrote:
> Can I use an ALTEER TABLE statement in a stored procedure, I need to
> drop a named constraint. Getting complie error.
>
> Thanks
You can use alter table from within a procedure (not sure why you woudl
want to). Maybe you're using dynamic sql and should be using either an
EXEC statement or sp_executesql. What is the reason for doing this from
a stored procedure (you can't inherit alter table rights with a
procedure grant on execute). It seems that if you're not using dynamic
sql for this in the procedure as some type of admin functionality (e.g.
to alter table ddl from your own procedure for admins), then you might
be better served by just executing the alter table as a batch without a
procedure. Can you explain why you need the procedure and post your
code?
David Gugick
Quest Software
www.imceda.com
www.quest.com
Sunday, March 25, 2012
AdventureWorksDB
I can't view the AdventureWorksDB database diagram even after the ALTER
AUTHORIZATION statement.
Thanks..>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
Typically the problem is that the database is not in 90 compatibility level.
You might try verifying the compatibility level of the database (EXEC
sp_dbcmptlevel AdventureWorks)
and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
90; ).
If that doesn't solve the problem, you'll need to provide more details such
as the error message your getting.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
> Thanks..|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||> I am logged in using "sa". Is "sa" valid in 2005?
Yes, sa is valid in 2005. I noticed in the Books Online topic
"Understanding Database Diagram Ownership (Visual Database Tools) ", that it
says the following:
"To use Database Diagram Designer it must first be set up by a member of the
db_owner role (a role of Microsoft SQL Server databases) to control access
to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
as an experiment, please use the ALTER AUTHORIZATION statement and change
the ownership to dbo and try again.
BTW, there is no AdventureWorks diagram that comes with the sample database,
but the support objects mentioned in the error message are required to
create a diagram so they get created when you just click on the Database
Diagram folder if they don't alread exist. I mention this just so you know
that once we get this figured out, there won't be a diagram there anyway
(but you can certainly create one on your own). If what you're really
looking for is an existing diagram of AdventureWorks, you can download an
.html or .vsd version from here:
http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the
> Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set
> the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
> "Gail Erickson [MS]" wrote:
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> Typically the problem is that the database is not in 90 compatibility
>> level.
>> You might try verifying the compatibility level of the database (EXEC
>> sp_dbcmptlevel AdventureWorks)
>> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
>> AdventureWorks,
>> 90; ).
>> If that doesn't solve the problem, you'll need to provide more details
>> such
>> as the error message your getting.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "mmc" <mmc@.discussions.microsoft.com> wrote in message
>> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> > Thanks..
>>|||mmc wrote:
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
The error message is complaining about an invalid database owner. What
user is shown as the owner of the AdventureWorks database?|||Thanks. I'll just download the diagram.
"Gail Erickson [MS]" wrote:
> > I am logged in using "sa". Is "sa" valid in 2005?
> Yes, sa is valid in 2005. I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
> BTW, there is no AdventureWorks diagram that comes with the sample database,
> but the support objects mentioned in the error message are required to
> create a diagram so they get created when you just click on the Database
> Diagram folder if they don't alread exist. I mention this just so you know
> that once we get this figured out, there won't be a diagram there anyway
> (but you can certainly create one on your own). If what you're really
> looking for is an existing diagram of AdventureWorks, you can download an
> ..html or .vsd version from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> > The sp_dbcmptlevel AdventureWorks returned a "90".
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the
> > Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set
> > the
> > database owner to a valid login, then add add the database diagram support
> > objects".
> > I am logged in using "sa". Is "sa" valid in 2005?
> > Thanks...
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >>
> >> Typically the problem is that the database is not in 90 compatibility
> >> level.
> >> You might try verifying the compatibility level of the database (EXEC
> >> sp_dbcmptlevel AdventureWorks)
> >> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
> >> AdventureWorks,
> >> 90; ).
> >>
> >> If that doesn't solve the problem, you'll need to provide more details
> >> such
> >> as the error message your getting.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights
> >> Download the latest version of Books Online from
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> >>
> >> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> >> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >> > Thanks..
> >>
> >>
> >>
>
>|||mmc <mmc@.discussions.microsoft.com> wrote :
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > database owner to a valid login, then add add the database diagram support objects".
Gail Erickson [MS] wrote:
> I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
I am able to reproduce the error on SQL Server 2005 Express Edition
SP1, with Management Studio Express SP1. The owner of the database was
a windows login (the windows user that was currently logged-on) which
is a member of the Administrators group. In Security / Logins there is
a login for BUILTIN\Administrators. I followed the instructions
described in the error message to change the database owner to 'sa' and
then the creation of database diagram support objects succeeded.
However, I think it should also work if the database owner is a windows
login (for a user which is member of a windows group that has a SQL
login), because these are the default installation options.
Razvan|||I think you are right about the windows login although I haven't tried it yet
. I am testing this using SQL authentication. It just doesn't make sense to
be able to do almost anything using "sa", but then you have to be in a
windows domain to view the diagram. This is, I guess, one of the subtle
differences between 2000 and 2005.
"Razvan Socol" wrote:
> mmc <mmc@.discussions.microsoft.com> wrote :
> > > The error everytime i try to open the diagram:
> > > "The database diagram support objects can not be installed because because
> > > this database does not have a valid owner. To continue, first use the Files
> > > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > > database owner to a valid login, then add add the database diagram support objects".
> Gail Erickson [MS] wrote:
> > I noticed in the Books Online topic
> > "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> > says the following:
> > "To use Database Diagram Designer it must first be set up by a member of the
> > db_owner role (a role of Microsoft SQL Server databases) to control access
> > to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> > as an experiment, please use the ALTER AUTHORIZATION statement and change
> > the ownership to dbo and try again.
> I am able to reproduce the error on SQL Server 2005 Express Edition
> SP1, with Management Studio Express SP1. The owner of the database was
> a windows login (the windows user that was currently logged-on) which
> is a member of the Administrators group. In Security / Logins there is
> a login for BUILTIN\Administrators. I followed the instructions
> described in the error message to change the database owner to 'sa' and
> then the creation of database diagram support objects succeeded.
> However, I think it should also work if the database owner is a windows
> login (for a user which is member of a windows group that has a SQL
> login), because these are the default installation options.
> Razvan
>|||mmc wrote:
> It just doesn't make sense to
> be able to do almost anything using "sa", but then you have to be in a
> windows domain to view the diagram.
Currently, it's the opposite situation: if you want to create the
diagramming support objects in AdventureWorks and the database owner is
a windows user, you have to change the owner to 'sa'.
However, I tried the same thing on a newly created database and the
creation of diagramming support objects succeeded, even if the database
owner was the same windows user (as the one which was the owner of
AdventureWorks, when the operation initially failed). It looks like
there was a problem with the way AdventureWorks was installed, because
when I looked at "Database Properties / Files / Owner" for the new
database, the owner was specified; but for AdventureWorks, the Owner
textbox (in the Files page of the Database Properties window) was
blank; however, the owner was shown for AdventureWorks in the General
page of the Database Properties window.
Razvansql
AUTHORIZATION statement.
Thanks..>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
Typically the problem is that the database is not in 90 compatibility level.
You might try verifying the compatibility level of the database (EXEC
sp_dbcmptlevel AdventureWorks)
and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
90; ).
If that doesn't solve the problem, you'll need to provide more details such
as the error message your getting.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
> Thanks..|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||> I am logged in using "sa". Is "sa" valid in 2005?
Yes, sa is valid in 2005. I noticed in the Books Online topic
"Understanding Database Diagram Ownership (Visual Database Tools) ", that it
says the following:
"To use Database Diagram Designer it must first be set up by a member of the
db_owner role (a role of Microsoft SQL Server databases) to control access
to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
as an experiment, please use the ALTER AUTHORIZATION statement and change
the ownership to dbo and try again.
BTW, there is no AdventureWorks diagram that comes with the sample database,
but the support objects mentioned in the error message are required to
create a diagram so they get created when you just click on the Database
Diagram folder if they don't alread exist. I mention this just so you know
that once we get this figured out, there won't be a diagram there anyway
(but you can certainly create one on your own). If what you're really
looking for is an existing diagram of AdventureWorks, you can download an
.html or .vsd version from here:
http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the
> Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set
> the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
> "Gail Erickson [MS]" wrote:
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> Typically the problem is that the database is not in 90 compatibility
>> level.
>> You might try verifying the compatibility level of the database (EXEC
>> sp_dbcmptlevel AdventureWorks)
>> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
>> AdventureWorks,
>> 90; ).
>> If that doesn't solve the problem, you'll need to provide more details
>> such
>> as the error message your getting.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "mmc" <mmc@.discussions.microsoft.com> wrote in message
>> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> > Thanks..
>>|||mmc wrote:
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
The error message is complaining about an invalid database owner. What
user is shown as the owner of the AdventureWorks database?|||Thanks. I'll just download the diagram.
"Gail Erickson [MS]" wrote:
> > I am logged in using "sa". Is "sa" valid in 2005?
> Yes, sa is valid in 2005. I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
> BTW, there is no AdventureWorks diagram that comes with the sample database,
> but the support objects mentioned in the error message are required to
> create a diagram so they get created when you just click on the Database
> Diagram folder if they don't alread exist. I mention this just so you know
> that once we get this figured out, there won't be a diagram there anyway
> (but you can certainly create one on your own). If what you're really
> looking for is an existing diagram of AdventureWorks, you can download an
> ..html or .vsd version from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> > The sp_dbcmptlevel AdventureWorks returned a "90".
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the
> > Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set
> > the
> > database owner to a valid login, then add add the database diagram support
> > objects".
> > I am logged in using "sa". Is "sa" valid in 2005?
> > Thanks...
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >>
> >> Typically the problem is that the database is not in 90 compatibility
> >> level.
> >> You might try verifying the compatibility level of the database (EXEC
> >> sp_dbcmptlevel AdventureWorks)
> >> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
> >> AdventureWorks,
> >> 90; ).
> >>
> >> If that doesn't solve the problem, you'll need to provide more details
> >> such
> >> as the error message your getting.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights
> >> Download the latest version of Books Online from
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> >>
> >> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> >> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >> > Thanks..
> >>
> >>
> >>
>
>|||mmc <mmc@.discussions.microsoft.com> wrote :
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > database owner to a valid login, then add add the database diagram support objects".
Gail Erickson [MS] wrote:
> I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
I am able to reproduce the error on SQL Server 2005 Express Edition
SP1, with Management Studio Express SP1. The owner of the database was
a windows login (the windows user that was currently logged-on) which
is a member of the Administrators group. In Security / Logins there is
a login for BUILTIN\Administrators. I followed the instructions
described in the error message to change the database owner to 'sa' and
then the creation of database diagram support objects succeeded.
However, I think it should also work if the database owner is a windows
login (for a user which is member of a windows group that has a SQL
login), because these are the default installation options.
Razvan|||I think you are right about the windows login although I haven't tried it yet
. I am testing this using SQL authentication. It just doesn't make sense to
be able to do almost anything using "sa", but then you have to be in a
windows domain to view the diagram. This is, I guess, one of the subtle
differences between 2000 and 2005.
"Razvan Socol" wrote:
> mmc <mmc@.discussions.microsoft.com> wrote :
> > > The error everytime i try to open the diagram:
> > > "The database diagram support objects can not be installed because because
> > > this database does not have a valid owner. To continue, first use the Files
> > > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > > database owner to a valid login, then add add the database diagram support objects".
> Gail Erickson [MS] wrote:
> > I noticed in the Books Online topic
> > "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> > says the following:
> > "To use Database Diagram Designer it must first be set up by a member of the
> > db_owner role (a role of Microsoft SQL Server databases) to control access
> > to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> > as an experiment, please use the ALTER AUTHORIZATION statement and change
> > the ownership to dbo and try again.
> I am able to reproduce the error on SQL Server 2005 Express Edition
> SP1, with Management Studio Express SP1. The owner of the database was
> a windows login (the windows user that was currently logged-on) which
> is a member of the Administrators group. In Security / Logins there is
> a login for BUILTIN\Administrators. I followed the instructions
> described in the error message to change the database owner to 'sa' and
> then the creation of database diagram support objects succeeded.
> However, I think it should also work if the database owner is a windows
> login (for a user which is member of a windows group that has a SQL
> login), because these are the default installation options.
> Razvan
>|||mmc wrote:
> It just doesn't make sense to
> be able to do almost anything using "sa", but then you have to be in a
> windows domain to view the diagram.
Currently, it's the opposite situation: if you want to create the
diagramming support objects in AdventureWorks and the database owner is
a windows user, you have to change the owner to 'sa'.
However, I tried the same thing on a newly created database and the
creation of diagramming support objects succeeded, even if the database
owner was the same windows user (as the one which was the owner of
AdventureWorks, when the operation initially failed). It looks like
there was a problem with the way AdventureWorks was installed, because
when I looked at "Database Properties / Files / Owner" for the new
database, the owner was specified; but for AdventureWorks, the Owner
textbox (in the Files page of the Database Properties window) was
blank; however, the owner was shown for AdventureWorks in the General
page of the Database Properties window.
Razvansql
Sunday, March 11, 2012
Advance Update Statement Sql Server
I am trying to speed up my update statements by removing inner select
statements.
statements.
Example:
update orders set shipname = (select contactName from
customers where customerid = orders.customerID)
I read some articles which said that I should be able to use an inner
join on the update statement like the following:
update orders set shipname = (select contactName from customers where
customerid = orders.customerID)
But every time that I run this statement I get the follwing error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.
Any Help will be greatly appreciated.
Thank you.I think you posted the wrong UPDATE statement. Both those statements
are identical and valid syntax.
However, if performance is your concern then why not take SHIPNAME out
of the Orders table. It looks like it's redundant there.
--
David Portas
SQL Server MVP
--|||See "Changing Data Using the FROM Clause" and also example C under
UPDATE in Books Online (although it would be better to rewrite it with
INNER JOIN).
Simon|||Both statements were the same ?! I think I know what you meant to say
...
As long as the scalar query expression returns zero or one row, you
will be fine. If you use the proprietary FROM syntax, you will get an
unpredictable result from a multi-row result set.
The real cost of an update is in the physical disk access, not the
code.|||HeadScratcher (mayur@.servicemg.com) writes:
> I am trying to speed up my update statements by removing inner select
> statements.
> Example:
> update orders set shipname = (select contactName from
> customers where customerid = orders.customerID)
> I read some articles which said that I should be able to use an inner
> join on the update statement like the following:
> update orders set shipname = (select contactName from customers where
> customerid = orders.customerID)
> But every time that I run this statement I get the follwing error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'inner'.
Apparently there was some glitch in the editing. Anyway, this is what
you want:
UPDATE Orders
SET ShipName = c.ContactName
FROM Orders o
JOIN Customers c ON c.CustomerID = O.CustomerID
I suspect the problem is that you left out the FROM clause.
I left out INNER here, because this is implied.
I should add your original syntax is in alignment with ANSI standards,
whereas the syntax with FROM JOIN is proprietary to MS SQL Server and
Sybase (and possibly Informix). If you need portability, stick to the
original syntax. As long as you work with SQL Server only, do as you
please. Personally, I find the FROM/JOIN syntax very pleasant, as it
builds on the same paradigm as a regular SELECT statement. It is also
more effecient, if you need to update more than one column.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> However, if performance is your concern then why not take SHIPNAME out
> of the Orders table. It looks like it's redundant there.
Nah, I would not recommend people to drop columns from their
Northwind databases. :-)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I find the FROM/JOIN syntax very pleasant, as it builds on the same paradigm as a regular SELECT statement.<<
Arrrgh! The **consistent meaning in the Standard SQL model** of a
FROM clause is that a temporary working table is constructed, used and
dropped at the end of the statement. You would be updating a temporary
working table, not the base table.
The Sybase, Informix and MS SQL Server syntax might look the same, but
the semantics are all slightly different when you get to a 1:m
relationship. Moving the code is deadly -- it moves over to the next
platform and runs differently. With the ANSI syntax, the vendors have
to follow the same rules. This is a good thing.
Advance Tab not available (Greyed Out)
Hello I am working on a sql express table and while configuring the steps after I select the data source and the selectment statement window shows, I want to use the advanced tab but it is greyed out. I want to be able to add edit and delete my data. I have administrator rights for this project and the workstation so thats not the issue. What I am tryng to accomplish is extending a website to manage it's content and users. Also the table has colums and the colums has test data within them I tested a query and the connection had a successful return. Maybe it's a configuration thing I am unaware of.
DKB
Hi,
you can not edit data directly from Management Studio, refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=178581&SiteID=1&PageID=1
Hemantgiri S. Goswami
|||This is the process of setting up a grid for a new database being implemented. I expected to be able to select, edit, update or, delete for the grid. Although this has been a couple of days ago I will try to recreate the issue and record my actions and report the issue and sbmit it to msdn and see what come up.
DKB
advance subquery question
i wanT a sql select statement like this
select a,b,c from table 1
where x= alias.x and y=alias.y
(select x,y from table2 ) as alias
dont wanna use cursor
thanksTry this:
select a,b,c
from table1 AS t1
INNER JOIN
(select x,y from table2 ) as alias
ON t1.x= alias.x and t1.y=alias.y
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"joeydj" <joeydj@.discussions.microsoft.com> wrote in message
news:B5D657C6-ACDE-4DDD-BB0E-E23C78E811DE@.microsoft.com...
i wanT a sql select statement like this
select a,b,c from table 1
where x= alias.x and y=alias.y
(select x,y from table2 ) as alias
dont wanna use cursor
thanks|||select
table1.a,
table1.b,
table1.c
from
table1
INNER JOIN
table2 as alias
on table1.x = alias.x
and table1.y = alias.y
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"joeydj" wrote:
> i wanT a sql select statement like this
> select a,b,c from table 1
> where x= alias.x and y=alias.y
> (select x,y from table2 ) as alias
> dont wanna use cursor
> thanks
>|||select table1.a,table1.b,table1.c from table1
join table2
on table1.x = table2.x and table1.y = table2.y
"joeydj" wrote:
> i wanT a sql select statement like this
> select a,b,c from table 1
> where x= alias.x and y=alias.y
> (select x,y from table2 ) as alias
> dont wanna use cursor
> thanks
>
select a,b,c from table 1
where x= alias.x and y=alias.y
(select x,y from table2 ) as alias
dont wanna use cursor
thanksTry this:
select a,b,c
from table1 AS t1
INNER JOIN
(select x,y from table2 ) as alias
ON t1.x= alias.x and t1.y=alias.y
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"joeydj" <joeydj@.discussions.microsoft.com> wrote in message
news:B5D657C6-ACDE-4DDD-BB0E-E23C78E811DE@.microsoft.com...
i wanT a sql select statement like this
select a,b,c from table 1
where x= alias.x and y=alias.y
(select x,y from table2 ) as alias
dont wanna use cursor
thanks|||select
table1.a,
table1.b,
table1.c
from
table1
INNER JOIN
table2 as alias
on table1.x = alias.x
and table1.y = alias.y
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"joeydj" wrote:
> i wanT a sql select statement like this
> select a,b,c from table 1
> where x= alias.x and y=alias.y
> (select x,y from table2 ) as alias
> dont wanna use cursor
> thanks
>|||select table1.a,table1.b,table1.c from table1
join table2
on table1.x = table2.x and table1.y = table2.y
"joeydj" wrote:
> i wanT a sql select statement like this
> select a,b,c from table 1
> where x= alias.x and y=alias.y
> (select x,y from table2 ) as alias
> dont wanna use cursor
> thanks
>
Advance SQL Statement Help
I have all my website access statics logging data into a SQL table with
the following structure:
id int identity
ip nvarchar 23
referer nvarchar 512
request nvarchar 512
website nvarchar 15
bytes int
process_time int
access_time datetime
Each time a page is loaded the values are logged. So if a single user
navigates 20 pages, there are 20 records in the database.
What I want to do is generate a sql statement that will return me all
the accesses to a specific website on a given day, grouped by the ip
address and sorted by the access_time.
Ideally it would return the ip addresses in date order based on their
first entry, with the responses per ip in their date order. Therefore
if
IP 216.113.235.52 had three hits at:
12:15:29
12:15:54
12:16:03
IP 216.113.214.190 had three hits at:
12:15:25
12:15:31
12:15:48
It would return a result set like:
216.113.214.190 @. 12:15:25
216.113.214.190 @. 12:15:31
216.113.214.190 @. 12:15:48
216.113.235.52 @. 12:15:29
216.113.235.52 @. 12:15:54
216.113.235.52 @. 12:16:03
What I'm doing now must not be very efficient as it takes several
seconds to return just a small list of data (roughly 3 seconds to
return 50 or so hits).
Currently I use two queries:
Query 1:
SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
<end_date> AND website LIKE '%<website>%' GROUP BY ip
OR
SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
<= <end_date> AND website LIKE '%<website>%'
Either of these gives me a unique list of ips on the given day
(unfortunately they're not sorted in date order :^( )
Then with this list of unique ips, I perform a second query, looping
through the ip addresses from the first query:
SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
This gives me the users path through the website in date order.
My problems are that:
1. The things just too slow.
2. I don't have a sorted list (the first user of the day may not
necessarily be the first listed).
Is it possible to generate a single query that will return the desired
results in order?
FWIW I'm accessing the database through JDBC.
Thanks in advance.SELECT website, ip, access_time
FROM access_log
WHERE
access_time >= '20060207 00:00:00.000'
AND access_time <= '20060208 00:00:00.000'
ORDER BY website, ip, accesstime ASC
This produces a listing of websites that were access by ip's, ordered by the
access_time. If you added the request column to this query, it would also
show you the path that each ip took through the website.
I've been doing a lot of work with analyzing web access logs lately. Let me
know if this was what you were looking for; if not I'll see what else I can
come up with.
"Tom Cole" wrote:
> I have all my website access statics logging data into a SQL table with
> the following structure:
> id int identity
> ip nvarchar 23
> referer nvarchar 512
> request nvarchar 512
> website nvarchar 15
> bytes int
> process_time int
> access_time datetime
> Each time a page is loaded the values are logged. So if a single user
> navigates 20 pages, there are 20 records in the database.
> What I want to do is generate a sql statement that will return me all
> the accesses to a specific website on a given day, grouped by the ip
> address and sorted by the access_time.
> Ideally it would return the ip addresses in date order based on their
> first entry, with the responses per ip in their date order. Therefore
> if
> IP 216.113.235.52 had three hits at:
> 12:15:29
> 12:15:54
> 12:16:03
> IP 216.113.214.190 had three hits at:
> 12:15:25
> 12:15:31
> 12:15:48
> It would return a result set like:
> 216.113.214.190 @. 12:15:25
> 216.113.214.190 @. 12:15:31
> 216.113.214.190 @. 12:15:48
> 216.113.235.52 @. 12:15:29
> 216.113.235.52 @. 12:15:54
> 216.113.235.52 @. 12:16:03
> What I'm doing now must not be very efficient as it takes several
> seconds to return just a small list of data (roughly 3 seconds to
> return 50 or so hits).
> Currently I use two queries:
> Query 1:
> SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
> <end_date> AND website LIKE '%<website>%' GROUP BY ip
> OR
> SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
> <= <end_date> AND website LIKE '%<website>%'
> Either of these gives me a unique list of ips on the given day
> (unfortunately they're not sorted in date order :^( )
> Then with this list of unique ips, I perform a second query, looping
> through the ip addresses from the first query:
> SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
> This gives me the users path through the website in date order.
> My problems are that:
> 1. The things just too slow.
> 2. I don't have a sorted list (the first user of the day may not
> necessarily be the first listed).
> Is it possible to generate a single query that will return the desired
> results in order?
> FWIW I'm accessing the database through JDBC.
> Thanks in advance.
>
the following structure:
id int identity
ip nvarchar 23
referer nvarchar 512
request nvarchar 512
website nvarchar 15
bytes int
process_time int
access_time datetime
Each time a page is loaded the values are logged. So if a single user
navigates 20 pages, there are 20 records in the database.
What I want to do is generate a sql statement that will return me all
the accesses to a specific website on a given day, grouped by the ip
address and sorted by the access_time.
Ideally it would return the ip addresses in date order based on their
first entry, with the responses per ip in their date order. Therefore
if
IP 216.113.235.52 had three hits at:
12:15:29
12:15:54
12:16:03
IP 216.113.214.190 had three hits at:
12:15:25
12:15:31
12:15:48
It would return a result set like:
216.113.214.190 @. 12:15:25
216.113.214.190 @. 12:15:31
216.113.214.190 @. 12:15:48
216.113.235.52 @. 12:15:29
216.113.235.52 @. 12:15:54
216.113.235.52 @. 12:16:03
What I'm doing now must not be very efficient as it takes several
seconds to return just a small list of data (roughly 3 seconds to
return 50 or so hits).
Currently I use two queries:
Query 1:
SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
<end_date> AND website LIKE '%<website>%' GROUP BY ip
OR
SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
<= <end_date> AND website LIKE '%<website>%'
Either of these gives me a unique list of ips on the given day
(unfortunately they're not sorted in date order :^( )
Then with this list of unique ips, I perform a second query, looping
through the ip addresses from the first query:
SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
This gives me the users path through the website in date order.
My problems are that:
1. The things just too slow.
2. I don't have a sorted list (the first user of the day may not
necessarily be the first listed).
Is it possible to generate a single query that will return the desired
results in order?
FWIW I'm accessing the database through JDBC.
Thanks in advance.SELECT website, ip, access_time
FROM access_log
WHERE
access_time >= '20060207 00:00:00.000'
AND access_time <= '20060208 00:00:00.000'
ORDER BY website, ip, accesstime ASC
This produces a listing of websites that were access by ip's, ordered by the
access_time. If you added the request column to this query, it would also
show you the path that each ip took through the website.
I've been doing a lot of work with analyzing web access logs lately. Let me
know if this was what you were looking for; if not I'll see what else I can
come up with.
"Tom Cole" wrote:
> I have all my website access statics logging data into a SQL table with
> the following structure:
> id int identity
> ip nvarchar 23
> referer nvarchar 512
> request nvarchar 512
> website nvarchar 15
> bytes int
> process_time int
> access_time datetime
> Each time a page is loaded the values are logged. So if a single user
> navigates 20 pages, there are 20 records in the database.
> What I want to do is generate a sql statement that will return me all
> the accesses to a specific website on a given day, grouped by the ip
> address and sorted by the access_time.
> Ideally it would return the ip addresses in date order based on their
> first entry, with the responses per ip in their date order. Therefore
> if
> IP 216.113.235.52 had three hits at:
> 12:15:29
> 12:15:54
> 12:16:03
> IP 216.113.214.190 had three hits at:
> 12:15:25
> 12:15:31
> 12:15:48
> It would return a result set like:
> 216.113.214.190 @. 12:15:25
> 216.113.214.190 @. 12:15:31
> 216.113.214.190 @. 12:15:48
> 216.113.235.52 @. 12:15:29
> 216.113.235.52 @. 12:15:54
> 216.113.235.52 @. 12:16:03
> What I'm doing now must not be very efficient as it takes several
> seconds to return just a small list of data (roughly 3 seconds to
> return 50 or so hits).
> Currently I use two queries:
> Query 1:
> SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
> <end_date> AND website LIKE '%<website>%' GROUP BY ip
> OR
> SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
> <= <end_date> AND website LIKE '%<website>%'
> Either of these gives me a unique list of ips on the given day
> (unfortunately they're not sorted in date order :^( )
> Then with this list of unique ips, I perform a second query, looping
> through the ip addresses from the first query:
> SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
> This gives me the users path through the website in date order.
> My problems are that:
> 1. The things just too slow.
> 2. I don't have a sorted list (the first user of the day may not
> necessarily be the first listed).
> Is it possible to generate a single query that will return the desired
> results in order?
> FWIW I'm accessing the database through JDBC.
> Thanks in advance.
>
Thursday, March 8, 2012
Adp sql - help
Hi,
Does anybody knows how to re-write this access sql into access adp query? Access adp does not seem to recognise the WHERE statement where is refers to other forms. Also applies to the IIF statement??
Please help..... Thanks.
Ms Access query (mdb):
---------
SELECT billing.[Job No], IIf([CountAFE]>1,"Multiple",[FirstOfAFE No]) AS [AFE No]
FROM ([Job-2] INNER JOIN billing ON [Job-2].[Job No] = billing.[Job No]) INNER JOIN [JOB COST-Query1 Sub] ON [Job-2].[Job No] = [JOB COST-Query1 Sub].[Job No]
WHERE (((billing.[Job No])=[Forms]![frmJobCostReport]![JobNumber]));Create a stored procedure
Pass the value of your control as an input parameter|||Try (case when) to replace (IIf).
IIF(condition, result1,result2)
is equivalent to:
CASE WHEN [condintion] THEN [result1] ELSE [result2] END
Good luck!
Does anybody knows how to re-write this access sql into access adp query? Access adp does not seem to recognise the WHERE statement where is refers to other forms. Also applies to the IIF statement??
Please help..... Thanks.
Ms Access query (mdb):
---------
SELECT billing.[Job No], IIf([CountAFE]>1,"Multiple",[FirstOfAFE No]) AS [AFE No]
FROM ([Job-2] INNER JOIN billing ON [Job-2].[Job No] = billing.[Job No]) INNER JOIN [JOB COST-Query1 Sub] ON [Job-2].[Job No] = [JOB COST-Query1 Sub].[Job No]
WHERE (((billing.[Job No])=[Forms]![frmJobCostReport]![JobNumber]));Create a stored procedure
Pass the value of your control as an input parameter|||Try (case when) to replace (IIf).
IIF(condition, result1,result2)
is equivalent to:
CASE WHEN [condintion] THEN [result1] ELSE [result2] END
Good luck!
Thursday, February 16, 2012
ADO does not add to Errors collection after the second FETCH NEXT in a SP
I'm having trouble obtaining errors raised in a stored
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server
database:
CREATE TABLE TestTable
(
TestInt int
)
go
INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)
This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.
Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON
declare @.TestInt int
declare @.ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable
open TestCursor
Fetch next from TestCursor into @.TestInt
While @.@.fetch_status<>-1
Begin
select @.ErrMsg = 'Error ' + convert(char, @.testint)
raiserror(@.ErrMsg, 16, 1)
raiserror(@.ErrMsg, 16, 1)
Fetch next from TestCursor into @.TestInt
end
Close TestCursor
DeAllocate TestCursor
return
END
This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:
Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"
Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute
For Each oErr In cn.Errors
MsgBox oErr.Description
Next
End
End Sub
When this code is executed, only two message boxes appear
with the message "Error 1".
Any help on this matter would be greatly appreciated :)Does anybody has any suggestions?|||Hi Ivan
You can bet your bottom dollar the focus of any replies will be on the use of the cursor rather than the ADO errors collection.
I know this is just an example - is this curiosity about a quirk you have spotted or a serious problem for you? If the latter, would you mind briefly explaining what your production cursor does as there are limited instances where it is as efficient as a set based solution. It may be that your sproc can be made more effective and your ADO errors issue made irrelevent.|||Of course, it is a serious problem for me. SP where this mechanism is used is a part of the big accounting system. Thus I can’t change it logic.
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server
database:
CREATE TABLE TestTable
(
TestInt int
)
go
INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)
This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.
Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON
declare @.TestInt int
declare @.ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable
open TestCursor
Fetch next from TestCursor into @.TestInt
While @.@.fetch_status<>-1
Begin
select @.ErrMsg = 'Error ' + convert(char, @.testint)
raiserror(@.ErrMsg, 16, 1)
raiserror(@.ErrMsg, 16, 1)
Fetch next from TestCursor into @.TestInt
end
Close TestCursor
DeAllocate TestCursor
return
END
This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:
Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"
Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute
For Each oErr In cn.Errors
MsgBox oErr.Description
Next
End
End Sub
When this code is executed, only two message boxes appear
with the message "Error 1".
Any help on this matter would be greatly appreciated :)Does anybody has any suggestions?|||Hi Ivan
You can bet your bottom dollar the focus of any replies will be on the use of the cursor rather than the ADO errors collection.
I know this is just an example - is this curiosity about a quirk you have spotted or a serious problem for you? If the latter, would you mind briefly explaining what your production cursor does as there are limited instances where it is as efficient as a set based solution. It may be that your sproc can be made more effective and your ADO errors issue made irrelevent.|||Of course, it is a serious problem for me. SP where this mechanism is used is a part of the big accounting system. Thus I can’t change it logic.
Subscribe to:
Posts (Atom)