HI all,
I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
In DB2 I use a view which uses some joins against DB1 like:
select * from MyTable left outer join DB1.dbo.users on ...
This works, but I get a time problem: the view is very slow.
The reason seems to be the connection to DB1.
In SQL Management Studio I can reproduce the situation: when I´m in DB2 and
try to "USE DB1" it takes something like >=2 seconds on my local machine.
After making the "USE DB1" the view works great - very fast (SQL Server
seemed to cache the connection).
But the connection caching works only for some minutes - when "falling back"
the view is slow again.
So, I´m looking for a solution for my problem (I can´t change the
database-concept, some data from DB1 is needed also in DB2). Maybe open the
connection DB1 for ... some hours? Or somebody has better ideas?
Thanks,
TonyI assume you have two databases in the same SQL Server instance (which is what it sounds like from
your description). Check if the database DB1 has the database property autoclose turned on. If so,
turn it off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
> HI all,
> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
> In DB2 I use a view which uses some joins against DB1 like:
> select * from MyTable left outer join DB1.dbo.users on ...
> This works, but I get a time problem: the view is very slow.
> The reason seems to be the connection to DB1.
> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
> takes something like >=2 seconds on my local machine.
> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
> connection).
> But the connection caching works only for some minutes - when "falling back" the view is slow
> again.
> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data from
> DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody has
> better ideas?
> Thanks,
> Tony
>|||HI Tibor,
thanks for your reply.
Yes, Autoclose=ON is standard.
Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume
it will work).
Does Autoclose=OFF have any other effects...?
Help of SQL2005 says:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.
The AutoClose property exposes server behavior for databases not accessed by
a user.
Thanks, Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>I assume you have two databases in the same SQL Server instance (which is
>what it sounds like from your description). Check if the database DB1 has
>the database property autoclose turned on. If so, turn it off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>|||> Yes, Autoclose=ON is standard.
Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this turned
on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes your problem.
> Does Autoclose=OFF have any other effects...?
I hope you don't have other programs that tries to do anything with the database files while no user
is connected? If you do, then they will be affected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
> HI Tibor,
> thanks for your reply.
> Yes, Autoclose=ON is standard.
> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
> Does Autoclose=OFF have any other effects...?
> Help of SQL2005 says:
> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature
> in new development work, and plan to modify applications that currently use this feature.
> The AutoClose property exposes server behavior for databases not accessed by a user.
> Thanks, Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like from
>>your description). Check if the database DB1 has the database property autoclose turned on. If so,
>>turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
>> takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>|||HI Tibor,
yes, with Autoclose=OFF the App works perfect!
But I only don´t really understand
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
which all use database DB1 - and some other database-stored procs/views.
Will they be affected?
Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any
side-effects)?
Thanks again,
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
> Standard? In your shop or in SQL Server? I believe that some edition of
> SQL Server has this turned on by default, which was a bad (IMO) choice.
> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server.
>> Avoid using this feature in new development work, and plan to modify
>> applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not accessed
>> by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is
>>what it sounds like from your description). Check if the database DB1 has
>>the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>>
>|||The application uses the database *through* SQL Server. I.e., they don't access the database files
directly.
I recommend that you have autoclose set to off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
> HI Tibor,
> yes, with Autoclose=OFF the App works perfect!
> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0) which all use database
> DB1 - and some other database-stored procs/views. Will they be affected?
> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any side-effects)?
> Thanks again,
> Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this
>> turned on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes
>> your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this
>> feature in new development work, and plan to modify applications that currently use this
>> feature.
>> The AutoClose property exposes server behavior for databases not accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
>> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like
>>from your description). Check if the database DB1 has the database property autoclose turned on.
>>If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1"
>> it takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>>
>|||Hi Tibor,
yes, I also did a little reasearch on AutoClose - schould always be off on
server and productive environments!
Thanks again for your hint! This was my solution!
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:ePINj6U6HHA.5164@.TK2MSFTNGP05.phx.gbl...
> The application uses the database *through* SQL Server. I.e., they don't
> access the database files directly.
>
> I recommend that you have autoclose set to off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
>> HI Tibor,
>> yes, with Autoclose=OFF the App works perfect!
>> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
>> which all use database DB1 - and some other database-stored procs/views.
>> Will they be affected?
>> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about
>> any side-effects)?
>> Thanks again,
>> Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of
>> SQL Server has this turned on by default, which was a bad (IMO) choice.
>> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and plan to
>> modify applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not
>> accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which
>>is what it sounds like from your description). Check if the database
>>DB1 has the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in
>> DB2 and try to "USE DB1" it takes something like >=2 seconds on my
>> local machine.
>> After making the "USE DB1" the view works great - very fast (SQL
>> Server seemed to cache the connection).
>> But the connection caching works only for some minutes - when
>> "falling back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe
>> open the connection DB1 for ... some hours? Or somebody has better
>> ideas?
>> Thanks,
>> Tony
>>
>>
>
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Incorrect syntax with Declare
Good morning
I hope this will be a simple one, but I am trying to write a view to query my SQL server database, setting a variable using "declare". The code runs fine and returns a number of records, but when I try to save it, it comes up with "Incorrect syntax near the keyword DECLARE", and will not save. I am a bit of a novice when it comes to SQL, but I don't understand why it runs, but won't save. Here is my variable:
DECLARE @.ANCHORDATE AS DATETIME
SET @.ANCHORDATE = CASE WHEN datepart(mm, getdate()) BETWEEN 4 AND 9 THEN dateadd(mm, 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) ELSE dateadd(mm, - 9,
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) END
I hope this will be a simple one, but I am trying to write a view to query my SQL server database, setting a variable using "declare". The code runs fine and returns a number of records, but when I try to save it, it comes up with "Incorrect syntax near the keyword DECLARE", and will not save. I am a bit of a novice when it comes to SQL, but I don't understand why it runs, but won't save. Here is my variable:
DECLARE @.ANCHORDATE AS DATETIME
SET @.ANCHORDATE = CASE WHEN datepart(mm, getdate()) BETWEEN 4 AND 9 THEN dateadd(mm, 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) ELSE dateadd(mm, - 9,
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) END
Thanks for any help.I am not 100% sure but you can try not to use DECLARE and try and save it. Let me know if it helps.|||I have replaced all the instances of the variable with the actual definition, and the view runs and saves fine. I was hoping to be able to keep it simple by not having to type in the variable definition each time. I don't understand why it can run, but saving it returns an error.
Monday, March 26, 2012
Incorrect syntax near the keyword Declare.
Dear Group,
I am trying to create a view and keep getting the Incorrect syntax near the
keyword 'Declare'" error.
Here is the code I am writing.
Create view fixed_airs (sid, fad_a2, fad_a3) as
Declare @.sid int,
@.fad_a2 int,
@.fad_a3 int
select @.sid=cast(substring(subject_id,1,8)as int) ,
@.fad_a2 =cast (substring(fad_2_4,1,1) as int),
@.fad_a3=cast(substring(fad_2_4,2,1) as int)
from parentpacket.
Thanks for the help in advance.
Jeff MagouirkJeff Magouirk wrote:
> Dear Group,
> I am trying to create a view and keep getting the Incorrect syntax near the
> keyword 'Declare'" error.
> Here is the code I am writing.
> Create view fixed_airs (sid, fad_a2, fad_a3) as
> Declare @.sid int,
> @.fad_a2 int,
> @.fad_a3 int
> select @.sid=cast(substring(subject_id,1,8)as int) ,
> @.fad_a2 =cast (substring(fad_2_4,1,1) as int),
> @.fad_a3=cast(substring(fad_2_4,2,1) as int)
> from parentpacket.
> Thanks for the help in advance.
> Jeff Magouirk
You keep getting syntax errors because you're using illegal syntax in
your CREATE VIEW statement. :D You can't use DECLARE, nor can you pass
in variables to a view. Check Books Online for proper syntax. But, in a
nutshell, you can only use a SELECT statement in a view.
Zachsql
Incorrect syntax near 'Go'
Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
--
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
--
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
Incorrect syntax near 'Go'
Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
Arek
GO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>
|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
Arek
GO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>
|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
Incorrect syntax near 'Go'
Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
Friday, March 23, 2012
Incorrect syntax near ?.
Hi Guys,
I have moved my asp.net app from access db over to MS SQL 2005 DB.
And I have got a slight problem when I go to view any product
for example if I type in the url ofhttp://domain.com/catalog/Details.aspx?AdNum=1
I get this error
Server Error in '/catalog' Application.
------------------------
Incorrect syntax near '?'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '?'.
I have attached the details.aspx.
I await for some suggestions.
Thanks
Matthew
------
1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>23<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">4Ad Detail - <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />5</asp:Content>67<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >89<script runat="server">10Sub Page_Load(ByVal SenderAs Object,ByVal EAs EventArgs)11If Not IsPostBackThen12 If Request.QueryString("AdNum") =""Then13 Response.Redirect("default.aspx")14End If15 EditLink.NavigateUrl ="confirm.aspx?AdNum=" & Request.QueryString("AdNum")16End If17 End Sub1819 Protected Sub DetailsView1_PageIndexChanging(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewPageEventArgs)2021End Sub22</script>2324 25 <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"26 CellPadding="4" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" ForeColor="#333333"27 GridLines="None" Height="65px" Width="100%" Font-Names="Arial" Font-Size="8pt" OnPageIndexChanging="DetailsView1_PageIndexChanging">28 <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />29 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />30 <Fields>31 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />32 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />33 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />34 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />35 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />36 <asp:TemplateField HeaderText="Email">37 <ItemTemplate>38 <asp:HyperLink ID="HyperLink1" runat="server" Text=Email NavigateUrl='<%# Eval("Email", "mailto:{0}") %>' />39 </ItemTemplate>40</asp:TemplateField>41 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />42 </Fields>43 </asp:DetailsView>44<p><i>To respond to this ad, just click the email address45above to send the poster46 a message.</i></p>47If you created this ad, you can48<asp:hyperlink id="EditLink" runat="server" >edit or delete it.</asp:hyperlink> <br>49 <asp:SqlDataSource ID="SqlDataSource1" runat="server"50 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"51 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"52 SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = ?)">53 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 </asp:SqlDataSource>5758</asp:content>When you use SQLDataSource, you need to use the named parameter instead of the "?" which is correct when you were using Access db. Change your SelectCommand to: SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = @.AdNum)">|||Thanks for your help, it now works.
|||This piece of code is suppose to allow me to edit / delete records, I can update the info and press update but it doesn't update the database. And I can press Delete record and it doesn't delete the record out of the database.
I don't get any error messages.
Below is the code:
1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>2<%@. ImportNamespace="System.Data" %>3<%@. ImportNamespace="System.Data.SqlClient" %>45<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">6Edit Ad</asp:Content>78<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >910<script runat="server">11Protected Sub DetailsView1_ItemUpdated(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewUpdatedEventArgs)12 Response.Redirect("default.aspx")13End Sub1415 Protected Sub DetailsView1_ItemDeleted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewDeletedEventArgs)16 Response.Redirect("default.aspx")17End Sub1819 Protected Sub DetailsView1_ItemCommand(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewCommandEventArgs)20If e.CommandName ="Cancel"Then21 Response.Redirect("default.aspx")22End If23 End Sub24</script>2526To make changes, click Edit, make your changes, then click Update.To delete27 this ad, just click the Delete button.28 <br />29 <br />30<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="100%" AutoGenerateRows="False" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None" OnItemUpdated="DetailsView1_ItemUpdated" OnItemDeleted="DetailsView1_ItemDeleted" OnItemCommand="DetailsView1_ItemCommand">31 <Fields>32 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />33 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />34 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />35 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />36 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />37 <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />38 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />39 <asp:BoundField DataField="UserPassword" HeaderText="UserPassword" SortExpression="UserPassword" />40 <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />41 </Fields>42 <RowStyle BackColor="#FFFBD6" />43 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />44</asp:DetailsView>45 46<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"47 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"48 DeleteCommand="DELETE FROM [Ads] WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND
= @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword"49 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"50 SelectCommand="SELECT [AdNum], [Title], [Category], [Description], [Price], [Phone],, [State], [UserPassword] FROM [Ads] WHERE ([AdNum] = @.AdNum)"51 UpdateCommand="UPDATE [Ads] SET [Title] = @.Title, [Category] = @.Category, [Description] = @.Description, [Price] = @.Price, [Phone] = @.Phone, = @.Email, [State] = @.State, [UserPassword] = @.UserPassword WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND = @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword">5253 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 <DeleteParameters>57 <asp:Parameter Name="original_AdNum" Type="Int32" />58 <asp:Parameter Name="original_Title" Type="String" />59 <asp:Parameter Name="original_Category" Type="String" />60 <asp:Parameter Name="original_Description" Type="String" />61 <asp:Parameter Name="original_Price" Type="Decimal" />62 <asp:Parameter Name="original_Phone" Type="String" />63 <asp:Parameter Name="original_Email" Type="String" />64 <asp:Parameter Name="original_State" Type="String" />65 <asp:Parameter Name="original_UserPassword" Type="String" />66 </DeleteParameters>67 <UpdateParameters>68 <asp:Parameter Name="Title" Type="String" />69 <asp:Parameter Name="Category" Type="String" />70 <asp:Parameter Name="Description" Type="String" />71 <asp:Parameter Name="Price" Type="Decimal" />72 <asp:Parameter Name="Phone" Type="String" />73 <asp:Parameter Name="Email" Type="String" />74 <asp:Parameter Name="State" Type="String" />75 <asp:Parameter Name="UserPassword" Type="String" />76 <asp:Parameter Name="original_AdNum" Type="Int32" />77 <asp:Parameter Name="original_Title" Type="String" />78 <asp:Parameter Name="original_Category" Type="String" />79 <asp:Parameter Name="original_Description" Type="String" />80 <asp:Parameter Name="original_Price" Type="Decimal" />81 <asp:Parameter Name="original_Phone" Type="String" />82 <asp:Parameter Name="original_Email" Type="String" />83 <asp:Parameter Name="original_State" Type="String" />84 <asp:Parameter Name="original_UserPassword" Type="String" />85 </UpdateParameters>8687 </asp:SqlDataSource>8889</asp:content>
I appricate your help
Thanks Matthew
|||Hi,
You can get information through these links:
http://www.asp.net/learn/dataaccess/tutorial50vb.aspx?tabid=63
http://forums.asp.net/thread/1172520.aspx
Wednesday, March 21, 2012
incorrect settings: ANSI_NULLS., QUOTED_IDENTIFIER.
Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
error after creating a view.
error after creating a view.
We wanted a composite unique constraint that ignored nulls, so we set
up a view using the following script:
/* -- start -- */
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
GO
CREATE VIEW vw_MyView
WITH SCHEMABINDING
AS
SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL
GO
/* -- end -- */
and then added the constraint to the new view
/* -- start -- */
CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ON
vw_MyView(Col1, Col2)
GO
/* -- end -- */
I thought we were doing fine, 'til we started running some DELETE
stored procedures and got the above error. The error also cited
ARITHABORT as an incorrect setting until we ran this script:
/* -- start -- */
USE master
DECLARE @.value int
SELECT @.value = value FROM syscurconfigs
WHERE config = 1534
SET @.value = @.value | 64
EXEC sp_configure 'user options', @.value
RECONFIGURE
/* -- end -- */
TIA to anyone kind enough to shed some light on this for me. Is there
something we should have done differently in creating the view and
index? If not, what's the procedure for working through these
settings errors?
I've read through some other threads on this subject, but didn't
really find what I was looking for. Thanks again for any help. Would
be appreciated.
-mattmatty2112@.hotmail.com (Matt Rink) wrote in message news:<1b11065c.0310262201.4f2ba70a@.posting.google.com>...
> Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
> error after creating a view.
> We wanted a composite unique constraint that ignored nulls, so we set
> up a view using the following script:
> /* -- start -- */
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> GO
> CREATE VIEW vw_MyView
> WITH SCHEMABINDING
> AS
> SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL
> GO
> /* -- end -- */
> and then added the constraint to the new view
> /* -- start -- */
> CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ON
> vw_MyView(Col1, Col2)
> GO
> /* -- end -- */
> I thought we were doing fine, 'til we started running some DELETE
> stored procedures and got the above error. The error also cited
> ARITHABORT as an incorrect setting until we ran this script:
> /* -- start -- */
> USE master
> DECLARE @.value int
> SELECT @.value = value FROM syscurconfigs
> WHERE config = 1534
> SET @.value = @.value | 64
> EXEC sp_configure 'user options', @.value
> RECONFIGURE
> /* -- end -- */
> TIA to anyone kind enough to shed some light on this for me. Is there
> something we should have done differently in creating the view and
> index? If not, what's the procedure for working through these
> settings errors?
> I've read through some other threads on this subject, but didn't
> really find what I was looking for. Thanks again for any help. Would
> be appreciated.
> -matt
You need to have those SET options in force not only when you create
the view and indexes, but also when you query it. So your client
application has to use the same settings in its code - OLE DB/ODBC
does this automatically, with the exception of ARITHABORT. In BOL,
Microsoft recommend to set this on at the server level, as you've done
already.
If you still have errors when using the indexed view, it is most
likely that you have some stored procedures which have been created
with ANSI_NULLS and QUOTED_IDENTIFIER off, not on - those settings are
fixed when the procedure is created. You can recreate the procedures
with the correct SET options, and it should work fine, although of
course that change could affect other code, so you need to test it.
Simon|||Hi Matt
As Doug and Simon have said, stored procedures created with certain SET
options enabled will always run with those options, even if you SET them
differently in the batch that calls the procedure. The only two that are
stored this way are "ANSI_NULLS" and "QUOTED_IDENTIFIER". I call these
'sticky' options, because their values 'stick' to the stored procedure.
Since these are the two you are getting messages about, it seems likely that
your procedure was created with the wrong values for these options,.
You can verify whether these options are set with the procedure by using the
OBJECTPROPERTY FUNCTION:
SELECT OBJECTPROPERTY(object_id('proc name'), 'ExecIsAnsiNullsOn' )
SELECT OBJECTPROPERTY(object_id('proc name'), 'ExecIsQuotedIdentOn')
If the functions return 1, the property was set, if they return 0, it was
NOT set for the procedure, and you MUST recreate the procedure to use it
with an indexed view.
(If the function returns NULL, it means you typed something wrong. :-) )
--
HTH
------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matt Rink" <matty2112@.hotmail.com> wrote in message
news:1b11065c.0310262201.4f2ba70a@.posting.google.c om...
> Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
> error after creating a view.
> We wanted a composite unique constraint that ignored nulls, so we set
> up a view using the following script:
> /* -- start -- */
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> GO
> CREATE VIEW vw_MyView
> WITH SCHEMABINDING
> AS
> SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL
> GO
> /* -- end -- */
> and then added the constraint to the new view
> /* -- start -- */
> CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ON
> vw_MyView(Col1, Col2)
> GO
> /* -- end -- */
> I thought we were doing fine, 'til we started running some DELETE
> stored procedures and got the above error. The error also cited
> ARITHABORT as an incorrect setting until we ran this script:
> /* -- start -- */
> USE master
> DECLARE @.value int
> SELECT @.value = value FROM syscurconfigs
> WHERE config = 1534
> SET @.value = @.value | 64
> EXEC sp_configure 'user options', @.value
> RECONFIGURE
> /* -- end -- */
> TIA to anyone kind enough to shed some light on this for me. Is there
> something we should have done differently in creating the view and
> index? If not, what's the procedure for working through these
> settings errors?
> I've read through some other threads on this subject, but didn't
> really find what I was looking for. Thanks again for any help. Would
> be appreciated.
> -matt|||Thank you all for your responses. I was able to get past the error by
adding
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
to the top of my trouble Stored Procedures in EM. What a maintainance
nightmare! We make all our changes to DB table structure using change
scripts, so that we can execute the batch of scripts on any of our
development/test/production databases. These manual changes needed to
make a new view work definitely monkeys things up. I suppose I'm going
to have to give this some more thought.
I'm surprised this is not a larger issue. Leads me to wonder what I'm
doing wrong...
thanks again,
-matt
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message news:<eIuw$yJnDHA.1072@.TK2MSFTNGP09.phx.gbl>...
> Hi Matt
> As Doug and Simon have said, stored procedures created with certain SET
> options enabled will always run with those options, even if you SET them
> differently in the batch that calls the procedure. The only two that are
> stored this way are "ANSI_NULLS" and "QUOTED_IDENTIFIER". I call these
> 'sticky' options, because their values 'stick' to the stored procedure.
> Since these are the two you are getting messages about, it seems likely that
> your procedure was created with the wrong values for these options,.
> You can verify whether these options are set with the procedure by using the
> OBJECTPROPERTY FUNCTION:
> SELECT OBJECTPROPERTY(object_id('proc name'), 'ExecIsAnsiNullsOn' )
> SELECT OBJECTPROPERTY(object_id('proc name'), 'ExecIsQuotedIdentOn')
> If the functions return 1, the property was set, if they return 0, it was
> NOT set for the procedure, and you MUST recreate the procedure to use it
> with an indexed view.
> (If the function returns NULL, it means you typed something wrong. :-) )
> --
> HTH
> ------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Matt Rink" <matty2112@.hotmail.com> wrote in message
> news:1b11065c.0310262201.4f2ba70a@.posting.google.c om...
> > Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
> > error after creating a view.
> > We wanted a composite unique constraint that ignored nulls, so we set
> > up a view using the following script:
> > /* -- start -- */
> > BEGIN TRANSACTION
> > SET QUOTED_IDENTIFIER ON
> > SET ARITHABORT ON
> > SET NUMERIC_ROUNDABORT OFF
> > SET CONCAT_NULL_YIELDS_NULL ON
> > SET ANSI_NULLS ON
> > SET ANSI_PADDING ON
> > SET ANSI_WARNINGS ON
> > COMMIT
> > GO
> > CREATE VIEW vw_MyView
> > WITH SCHEMABINDING
> > AS
> > SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL
> > GO
> > /* -- end -- */
> > and then added the constraint to the new view
> > /* -- start -- */
> > CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ON
> > vw_MyView(Col1, Col2)
> > GO
> > /* -- end -- */
> > I thought we were doing fine, 'til we started running some DELETE
> > stored procedures and got the above error. The error also cited
> > ARITHABORT as an incorrect setting until we ran this script:
> > /* -- start -- */
> > USE master
> > DECLARE @.value int
> > SELECT @.value = value FROM syscurconfigs
> > WHERE config = 1534
> > SET @.value = @.value | 64
> > EXEC sp_configure 'user options', @.value
> > RECONFIGURE
> > /* -- end -- */
> > TIA to anyone kind enough to shed some light on this for me. Is there
> > something we should have done differently in creating the view and
> > index? If not, what's the procedure for working through these
> > settings errors?
> > I've read through some other threads on this subject, but didn't
> > really find what I was looking for. Thanks again for any help. Would
> > be appreciated.
> > -matt
Incorrect Schedule Displayed
Hi,
I am creating a data driven subscription via code using a shared schedule
and it seems fine, but when I view the subscription in Report Manager it
shows the wrong schedule.
I have checked the MatchData field in the Subscriptions table and the
ScheduleID in the ReportSchedules table and both of these have the correct
ID.
any ideas?
thanks
MattOh, I should add that the subscription runs correctly. It just displays a
different schedule to the one I have assigned on the "Specify When the
subscription is processed." page. Note, if I set the schedule here, it
seems to remember it.
thanks again
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am creating a data driven subscription via code using a shared schedule
> and it seems fine, but when I view the subscription in Report Manager it
> shows the wrong schedule.
> I have checked the MatchData field in the Subscriptions table and the
> ScheduleID in the ReportSchedules table and both of these have the correct
> ID.
> any ideas?
> thanks
> Matt
>|||Is it possible for you to share your code? You can send it directly to me
if you want and I can take a look. Just remove the online from my address.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:#NVQ#z2gEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Oh, I should add that the subscription runs correctly. It just displays a
> different schedule to the one I have assigned on the "Specify When the
> subscription is processed." page. Note, if I set the schedule here, it
> seems to remember it.
> thanks again
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I am creating a data driven subscription via code using a shared
schedule
> > and it seems fine, but when I view the subscription in Report Manager it
> > shows the wrong schedule.
> >
> > I have checked the MatchData field in the Subscriptions table and the
> > ScheduleID in the ReportSchedules table and both of these have the
correct
> > ID.
> >
> > any ideas?
> >
> > thanks
> >
> > Matt
> >
> >
>
I am creating a data driven subscription via code using a shared schedule
and it seems fine, but when I view the subscription in Report Manager it
shows the wrong schedule.
I have checked the MatchData field in the Subscriptions table and the
ScheduleID in the ReportSchedules table and both of these have the correct
ID.
any ideas?
thanks
MattOh, I should add that the subscription runs correctly. It just displays a
different schedule to the one I have assigned on the "Specify When the
subscription is processed." page. Note, if I set the schedule here, it
seems to remember it.
thanks again
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am creating a data driven subscription via code using a shared schedule
> and it seems fine, but when I view the subscription in Report Manager it
> shows the wrong schedule.
> I have checked the MatchData field in the Subscriptions table and the
> ScheduleID in the ReportSchedules table and both of these have the correct
> ID.
> any ideas?
> thanks
> Matt
>|||Is it possible for you to share your code? You can send it directly to me
if you want and I can take a look. Just remove the online from my address.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:#NVQ#z2gEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Oh, I should add that the subscription runs correctly. It just displays a
> different schedule to the one I have assigned on the "Specify When the
> subscription is processed." page. Note, if I set the schedule here, it
> seems to remember it.
> thanks again
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I am creating a data driven subscription via code using a shared
schedule
> > and it seems fine, but when I view the subscription in Report Manager it
> > shows the wrong schedule.
> >
> > I have checked the MatchData field in the Subscriptions table and the
> > ScheduleID in the ReportSchedules table and both of these have the
correct
> > ID.
> >
> > any ideas?
> >
> > thanks
> >
> > Matt
> >
> >
>
Incorrect Results Left Join View
Hi, I have what I believe to be a bug in Sql Server 2000.
The symptoms are as per described in KB 321541, although that particular bug
was fixed in SP3. I have installed SP4 beta and still have wrong result.
My query is quite complex, and when I have tried to create a simple example
I cannot reproduce the behaviour.
To describe the situation, I have a left join between a table and a view,
where that view is based on another view that is based on a third view. In
this deepest view, one of the SELECTed columns contains a (case when sum(xyz
)
is null the 'Some Text' else sum(xyz) end) expression.
This expression appears to be getting executed at the end of the execution
plan, rather than before the join is performed. I would expect all fields
from the view to be null if the join conditions are not met, in a left join.
Hence I am getting fields that are non null being returned from the view. i.
e.
tbl1.fld1 tbl1.fld2 view1.fld1 view1.fld2 view1.fld3
23 'Test' NULL 'Some Text' NULL
Looking in the execution plan, the compute scalar for that calculated field
is being performed last, AFTER the left join. This to me seems very wrong.
As I said, I have been unable to recreate with a simple join.
This problem is not stopping my query from working, it is just seems to be
an annoying bug which COULD potentially have serious consequences if you
weren't aware of.
Any ideas?> My query is quite complex, and when I have tried to create a simple
example
> I cannot reproduce the behaviour.
> To describe the situation
Nobody here is going to be able to reproduce your cenario or confirm this
bug unless you can provide an actual repro (see my signature).
Regardless of how complex the query is, if you can go to a new database,
create the required set of tables with a small amount of data, and run the
query and reproduce the problem, then you can post that here and other
people can try to reproduce the same issue. You don't have to worry that
people have to be able to inspect your query and completely understand it at
first glance... the point is that they can copy and paste the code into
their own database, run it, and see what happens. (Being able to compare it
against what you *expect* to happen is a big help, too.)
It's possible that the bug is in your query, and the only way anyone is
going to be able to find it (complexity only increases the amount of time
required to find the problem, it doesn't preclude it), is if we can actually
see your query. Describing the query doesn't help us at all.
It's also possible that the bug wasn't completely eradicated with SP3.
But without the above, all we can do is speculate and guess.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I'm obviously not looking for someone to reproduce it at this stage, just a
general description that someone may relate to as something they have
encountered.
If I don't get any useful responses I will endeavour to create an example
(not easy on employer's time), although as I stated it is not a bug that is
critical for me at this stage.
You would agree that a left join between a table and a view where no columns
match should result in all NULL values from the view?
"Aaron [SQL Server MVP]" wrote:
> example
> Nobody here is going to be able to reproduce your cenario or confirm this
> bug unless you can provide an actual repro (see my signature).
> Regardless of how complex the query is, if you can go to a new database,
> create the required set of tables with a small amount of data, and run the
> query and reproduce the problem, then you can post that here and other
> people can try to reproduce the same issue. You don't have to worry that
> people have to be able to inspect your query and completely understand it
at
> first glance... the point is that they can copy and paste the code into
> their own database, run it, and see what happens. (Being able to compare
it
> against what you *expect* to happen is a big help, too.)
> It's possible that the bug is in your query, and the only way anyone is
> going to be able to find it (complexity only increases the amount of time
> required to find the problem, it doesn't preclude it), is if we can actual
ly
> see your query. Describing the query doesn't help us at all.
> It's also possible that the bug wasn't completely eradicated with SP3.
> But without the above, all we can do is speculate and guess.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>|||Lachlan,
If your view does contain the expression
case when sum(xyz) is null the 'Some Text' else sum(xyz) end
then I suspect something is wrong with the view. If that expression
is evaluated when sum(xyz) is null, it will raise an error, since the type
of the expression is a number type, and 'Some Text' can't be converted
into any number type.
Even without posting a repro script, is it possible you could verify
that this is the kind of expression you have in the view or not?
Steve Kass
Drew University
Lachlan wrote:
>Hi, I have what I believe to be a bug in Sql Server 2000.
>The symptoms are as per described in KB 321541, although that particular bu
g
>was fixed in SP3. I have installed SP4 beta and still have wrong result.
>My query is quite complex, and when I have tried to create a simple example
>I cannot reproduce the behaviour.
>To describe the situation, I have a left join between a table and a view,
>where that view is based on another view that is based on a third view. In
>this deepest view, one of the SELECTed columns contains a (case when sum(xy
z)
>is null the 'Some Text' else sum(xyz) end) expression.
>This expression appears to be getting executed at the end of the execution
>plan, rather than before the join is performed. I would expect all fields
>from the view to be null if the join conditions are not met, in a left join
.
>Hence I am getting fields that are non null being returned from the view. i
.e.
>tbl1.fld1 tbl1.fld2 view1.fld1 view1.fld2 view1.fld3
>23 'Test' NULL 'Some Text' NULL
>Looking in the execution plan, the compute scalar for that calculated field
>is being performed last, AFTER the left join. This to me seems very wrong.
>As I said, I have been unable to recreate with a simple join.
>This problem is not stopping my query from working, it is just seems to be
>an annoying bug which COULD potentially have serious consequences if you
>weren't aware of.
>Any ideas?
>|||My apologies I actually have something like:
case when sum(xyz) is null then 'No Score' else str(sum(xyz)) end
I am now trying to break this down to the simplest form that will cause the
error, so I can post an example, I am determined to get an answer to this...
The query I have runs fine and returns results, I just think that some
values should be Null when they are not...
Anyway, if all goes well I will attempt to post some script in the next hour
or two.
"Steve Kass" wrote:
> Lachlan,
> If your view does contain the expression
> case when sum(xyz) is null the 'Some Text' else sum(xyz) end
> then I suspect something is wrong with the view. If that expression
> is evaluated when sum(xyz) is null, it will raise an error, since the ty
pe
> of the expression is a number type, and 'Some Text' can't be converted
> into any number type.
> Even without posting a repro script, is it possible you could verify
> that this is the kind of expression you have in the view or not?
> Steve Kass
> Drew University
> Lachlan wrote:
>
>|||OK here is some script:
Execute all this to build the structures, then run stored procedure SP1.
To me, the two result sets returned should be identical... (preferably the
latter).
- Lachlan
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table1]
GO
CREATE TABLE [dbo].[Table2] (
[fldID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table1] (
[fldID] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VIEW2
AS
SELECT fldID,
case when fldID is null then 'Null ID' else str(fldID) end as strFldID
FROM dbo.Table2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VIEW1
AS
SELECT fldID, strFldID
FROM view2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE SP1 AS
SELECT *
FROM table1 left join view1 on table1.fldID = view1.fldID
SELECT *
FROM table1 left join view2 on table1.fldID = view2.fldID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
INSERT INTO table1 Values (1)
INSERT INTO table1 Values (2)
INSERT INTO table1 Values (3)
INSERT INTO table1 Values (4)
INSERT INTO table1 Values (5)
INSERT INTO table1 Values (6)
INSERT INTO table2 Values (1)
INSERT INTO table2 Values (2)
INSERT INTO table2 Values (3)|||Sure looks like a bug to me. The repro is very helpful, and I'll
pass it on to Microsoft.
If I get any quick feedback, I'll add to this thread, and if
you want me to get back to you later if there's nothing immediate,
send me an e-mail address.
Thanks,
SK
Lachlan wrote:
>OK here is some script:
>Execute all this to build the structures, then run stored procedure SP1.
>To me, the two result sets returned should be identical... (preferably the
>latter).
>- Lachlan
>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
>and OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[SP1]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
>drop view [dbo].[VIEW1]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
>drop view [dbo].[VIEW2]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[Table2]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[table1]
>GO
>CREATE TABLE [dbo].[Table2] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[table1] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE VIEW dbo.VIEW2
>AS
>SELECT fldID,
> case when fldID is null then 'Null ID' else str(fldID) end as strFldID
>FROM dbo.Table2
>
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE VIEW dbo.VIEW1
>AS
>SELECT fldID, strFldID
>FROM view2
>
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS OFF
>GO
>CREATE PROCEDURE SP1 AS
>SELECT *
>FROM table1 left join view1 on table1.fldID = view1.fldID
>SELECT *
>FROM table1 left join view2 on table1.fldID = view2.fldID
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>INSERT INTO table1 Values (1)
>INSERT INTO table1 Values (2)
>INSERT INTO table1 Values (3)
>INSERT INTO table1 Values (4)
>INSERT INTO table1 Values (5)
>INSERT INTO table1 Values (6)
>INSERT INTO table2 Values (1)
>INSERT INTO table2 Values (2)
>INSERT INTO table2 Values (3)
>|||This query shows the same behavior, Using Derived queries instead of Views,
and does in fact seem inconsistent with expected behavior:
Select * FROM table1
Left Join
(Select fldID, strFldID From
(Select fldID,
case when fldID is null
then 'Null ID'
else str(fldID)
end strFldID
From Table2) W) Z
On table1.fldID = Z.fldID
"Lachlan" wrote:
> OK here is some script:
> Execute all this to build the structures, then run stored procedure SP1.
> To me, the two result sets returned should be identical... (preferably the
> latter).
> - Lachlan
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[SP1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[VIEW1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[VIEW2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Table2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[table1]
> GO
> CREATE TABLE [dbo].[Table2] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[table1] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VIEW2
> AS
> SELECT fldID,
> case when fldID is null then 'Null ID' else str(fldID) end as strFldID
> FROM dbo.Table2
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VIEW1
> AS
> SELECT fldID, strFldID
> FROM view2
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> CREATE PROCEDURE SP1 AS
> SELECT *
> FROM table1 left join view1 on table1.fldID = view1.fldID
> SELECT *
> FROM table1 left join view2 on table1.fldID = view2.fldID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> INSERT INTO table1 Values (1)
> INSERT INTO table1 Values (2)
> INSERT INTO table1 Values (3)
> INSERT INTO table1 Values (4)
> INSERT INTO table1 Values (5)
> INSERT INTO table1 Values (6)
> INSERT INTO table2 Values (1)
> INSERT INTO table2 Values (2)
> INSERT INTO table2 Values (3)|||The result is definitely wrong. If you trick the query processor
into thinking 'Null ID' is not a constant, you should
get the right answer. There have been several bugs
of this nature, all of which come from the assumption
that if T.x has a constant value, that value can go anywhere
T.x appears. Oops - except the inner side of an outer
join, which someone forgot to worry about.
I bet replacing 'Null ID' with 'Null ID' +
substring('',1,coalesce(fldID%2+1,1))
will eliminate the bug (and so will lots of simpler things).
SK
CBretana wrote:
>This query shows the same behavior, Using Derived queries instead of Views,
>and does in fact seem inconsistent with expected behavior:
> Select * FROM table1
> Left Join
> (Select fldID, strFldID From
> (Select fldID,
> case when fldID is null
> then 'Null ID'
> else str(fldID)
> end strFldID
> From Table2) W) Z
> On table1.fldID = Z.fldID
>
>"Lachlan" wrote:
>
>
The symptoms are as per described in KB 321541, although that particular bug
was fixed in SP3. I have installed SP4 beta and still have wrong result.
My query is quite complex, and when I have tried to create a simple example
I cannot reproduce the behaviour.
To describe the situation, I have a left join between a table and a view,
where that view is based on another view that is based on a third view. In
this deepest view, one of the SELECTed columns contains a (case when sum(xyz
)
is null the 'Some Text' else sum(xyz) end) expression.
This expression appears to be getting executed at the end of the execution
plan, rather than before the join is performed. I would expect all fields
from the view to be null if the join conditions are not met, in a left join.
Hence I am getting fields that are non null being returned from the view. i.
e.
tbl1.fld1 tbl1.fld2 view1.fld1 view1.fld2 view1.fld3
23 'Test' NULL 'Some Text' NULL
Looking in the execution plan, the compute scalar for that calculated field
is being performed last, AFTER the left join. This to me seems very wrong.
As I said, I have been unable to recreate with a simple join.
This problem is not stopping my query from working, it is just seems to be
an annoying bug which COULD potentially have serious consequences if you
weren't aware of.
Any ideas?> My query is quite complex, and when I have tried to create a simple
example
> I cannot reproduce the behaviour.
> To describe the situation
Nobody here is going to be able to reproduce your cenario or confirm this
bug unless you can provide an actual repro (see my signature).
Regardless of how complex the query is, if you can go to a new database,
create the required set of tables with a small amount of data, and run the
query and reproduce the problem, then you can post that here and other
people can try to reproduce the same issue. You don't have to worry that
people have to be able to inspect your query and completely understand it at
first glance... the point is that they can copy and paste the code into
their own database, run it, and see what happens. (Being able to compare it
against what you *expect* to happen is a big help, too.)
It's possible that the bug is in your query, and the only way anyone is
going to be able to find it (complexity only increases the amount of time
required to find the problem, it doesn't preclude it), is if we can actually
see your query. Describing the query doesn't help us at all.
It's also possible that the bug wasn't completely eradicated with SP3.
But without the above, all we can do is speculate and guess.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I'm obviously not looking for someone to reproduce it at this stage, just a
general description that someone may relate to as something they have
encountered.
If I don't get any useful responses I will endeavour to create an example
(not easy on employer's time), although as I stated it is not a bug that is
critical for me at this stage.
You would agree that a left join between a table and a view where no columns
match should result in all NULL values from the view?
"Aaron [SQL Server MVP]" wrote:
> example
> Nobody here is going to be able to reproduce your cenario or confirm this
> bug unless you can provide an actual repro (see my signature).
> Regardless of how complex the query is, if you can go to a new database,
> create the required set of tables with a small amount of data, and run the
> query and reproduce the problem, then you can post that here and other
> people can try to reproduce the same issue. You don't have to worry that
> people have to be able to inspect your query and completely understand it
at
> first glance... the point is that they can copy and paste the code into
> their own database, run it, and see what happens. (Being able to compare
it
> against what you *expect* to happen is a big help, too.)
> It's possible that the bug is in your query, and the only way anyone is
> going to be able to find it (complexity only increases the amount of time
> required to find the problem, it doesn't preclude it), is if we can actual
ly
> see your query. Describing the query doesn't help us at all.
> It's also possible that the bug wasn't completely eradicated with SP3.
> But without the above, all we can do is speculate and guess.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>|||Lachlan,
If your view does contain the expression
case when sum(xyz) is null the 'Some Text' else sum(xyz) end
then I suspect something is wrong with the view. If that expression
is evaluated when sum(xyz) is null, it will raise an error, since the type
of the expression is a number type, and 'Some Text' can't be converted
into any number type.
Even without posting a repro script, is it possible you could verify
that this is the kind of expression you have in the view or not?
Steve Kass
Drew University
Lachlan wrote:
>Hi, I have what I believe to be a bug in Sql Server 2000.
>The symptoms are as per described in KB 321541, although that particular bu
g
>was fixed in SP3. I have installed SP4 beta and still have wrong result.
>My query is quite complex, and when I have tried to create a simple example
>I cannot reproduce the behaviour.
>To describe the situation, I have a left join between a table and a view,
>where that view is based on another view that is based on a third view. In
>this deepest view, one of the SELECTed columns contains a (case when sum(xy
z)
>is null the 'Some Text' else sum(xyz) end) expression.
>This expression appears to be getting executed at the end of the execution
>plan, rather than before the join is performed. I would expect all fields
>from the view to be null if the join conditions are not met, in a left join
.
>Hence I am getting fields that are non null being returned from the view. i
.e.
>tbl1.fld1 tbl1.fld2 view1.fld1 view1.fld2 view1.fld3
>23 'Test' NULL 'Some Text' NULL
>Looking in the execution plan, the compute scalar for that calculated field
>is being performed last, AFTER the left join. This to me seems very wrong.
>As I said, I have been unable to recreate with a simple join.
>This problem is not stopping my query from working, it is just seems to be
>an annoying bug which COULD potentially have serious consequences if you
>weren't aware of.
>Any ideas?
>|||My apologies I actually have something like:
case when sum(xyz) is null then 'No Score' else str(sum(xyz)) end
I am now trying to break this down to the simplest form that will cause the
error, so I can post an example, I am determined to get an answer to this...
The query I have runs fine and returns results, I just think that some
values should be Null when they are not...
Anyway, if all goes well I will attempt to post some script in the next hour
or two.
"Steve Kass" wrote:
> Lachlan,
> If your view does contain the expression
> case when sum(xyz) is null the 'Some Text' else sum(xyz) end
> then I suspect something is wrong with the view. If that expression
> is evaluated when sum(xyz) is null, it will raise an error, since the ty
pe
> of the expression is a number type, and 'Some Text' can't be converted
> into any number type.
> Even without posting a repro script, is it possible you could verify
> that this is the kind of expression you have in the view or not?
> Steve Kass
> Drew University
> Lachlan wrote:
>
>|||OK here is some script:
Execute all this to build the structures, then run stored procedure SP1.
To me, the two result sets returned should be identical... (preferably the
latter).
- Lachlan
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table1]
GO
CREATE TABLE [dbo].[Table2] (
[fldID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table1] (
[fldID] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VIEW2
AS
SELECT fldID,
case when fldID is null then 'Null ID' else str(fldID) end as strFldID
FROM dbo.Table2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VIEW1
AS
SELECT fldID, strFldID
FROM view2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE SP1 AS
SELECT *
FROM table1 left join view1 on table1.fldID = view1.fldID
SELECT *
FROM table1 left join view2 on table1.fldID = view2.fldID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
INSERT INTO table1 Values (1)
INSERT INTO table1 Values (2)
INSERT INTO table1 Values (3)
INSERT INTO table1 Values (4)
INSERT INTO table1 Values (5)
INSERT INTO table1 Values (6)
INSERT INTO table2 Values (1)
INSERT INTO table2 Values (2)
INSERT INTO table2 Values (3)|||Sure looks like a bug to me. The repro is very helpful, and I'll
pass it on to Microsoft.
If I get any quick feedback, I'll add to this thread, and if
you want me to get back to you later if there's nothing immediate,
send me an e-mail address.
Thanks,
SK
Lachlan wrote:
>OK here is some script:
>Execute all this to build the structures, then run stored procedure SP1.
>To me, the two result sets returned should be identical... (preferably the
>latter).
>- Lachlan
>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
>and OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[SP1]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
>drop view [dbo].[VIEW1]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
>drop view [dbo].[VIEW2]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[Table2]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[table1]
>GO
>CREATE TABLE [dbo].[Table2] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[table1] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE VIEW dbo.VIEW2
>AS
>SELECT fldID,
> case when fldID is null then 'Null ID' else str(fldID) end as strFldID
>FROM dbo.Table2
>
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE VIEW dbo.VIEW1
>AS
>SELECT fldID, strFldID
>FROM view2
>
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS OFF
>GO
>CREATE PROCEDURE SP1 AS
>SELECT *
>FROM table1 left join view1 on table1.fldID = view1.fldID
>SELECT *
>FROM table1 left join view2 on table1.fldID = view2.fldID
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>INSERT INTO table1 Values (1)
>INSERT INTO table1 Values (2)
>INSERT INTO table1 Values (3)
>INSERT INTO table1 Values (4)
>INSERT INTO table1 Values (5)
>INSERT INTO table1 Values (6)
>INSERT INTO table2 Values (1)
>INSERT INTO table2 Values (2)
>INSERT INTO table2 Values (3)
>|||This query shows the same behavior, Using Derived queries instead of Views,
and does in fact seem inconsistent with expected behavior:
Select * FROM table1
Left Join
(Select fldID, strFldID From
(Select fldID,
case when fldID is null
then 'Null ID'
else str(fldID)
end strFldID
From Table2) W) Z
On table1.fldID = Z.fldID
"Lachlan" wrote:
> OK here is some script:
> Execute all this to build the structures, then run stored procedure SP1.
> To me, the two result sets returned should be identical... (preferably the
> latter).
> - Lachlan
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[SP1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[VIEW1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[VIEW2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Table2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[table1]
> GO
> CREATE TABLE [dbo].[Table2] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[table1] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VIEW2
> AS
> SELECT fldID,
> case when fldID is null then 'Null ID' else str(fldID) end as strFldID
> FROM dbo.Table2
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VIEW1
> AS
> SELECT fldID, strFldID
> FROM view2
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> CREATE PROCEDURE SP1 AS
> SELECT *
> FROM table1 left join view1 on table1.fldID = view1.fldID
> SELECT *
> FROM table1 left join view2 on table1.fldID = view2.fldID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> INSERT INTO table1 Values (1)
> INSERT INTO table1 Values (2)
> INSERT INTO table1 Values (3)
> INSERT INTO table1 Values (4)
> INSERT INTO table1 Values (5)
> INSERT INTO table1 Values (6)
> INSERT INTO table2 Values (1)
> INSERT INTO table2 Values (2)
> INSERT INTO table2 Values (3)|||The result is definitely wrong. If you trick the query processor
into thinking 'Null ID' is not a constant, you should
get the right answer. There have been several bugs
of this nature, all of which come from the assumption
that if T.x has a constant value, that value can go anywhere
T.x appears. Oops - except the inner side of an outer
join, which someone forgot to worry about.
I bet replacing 'Null ID' with 'Null ID' +
substring('',1,coalesce(fldID%2+1,1))
will eliminate the bug (and so will lots of simpler things).
SK
CBretana wrote:
>This query shows the same behavior, Using Derived queries instead of Views,
>and does in fact seem inconsistent with expected behavior:
> Select * FROM table1
> Left Join
> (Select fldID, strFldID From
> (Select fldID,
> case when fldID is null
> then 'Null ID'
> else str(fldID)
> end strFldID
> From Table2) W) Z
> On table1.fldID = Z.fldID
>
>"Lachlan" wrote:
>
>
Monday, March 19, 2012
Incorrect Information in SQL Agent\Operator History
Apologies if this is in the wrong forum.
I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.
Not a major bug for me, but does anyone else have this problem?
--
DDL statement for operator.
EXEC msdb.dbo.sp_add_operator @.name=N'Rich',
@.enabled=1,
@.weekday_pager_start_time=90000,
@.weekday_pager_end_time=180000,
@.saturday_pager_start_time=90000,
@.saturday_pager_end_time=180000,
@.sunday_pager_start_time=90000,
@.sunday_pager_end_time=180000,
@.pager_days=0,
@.email_address=N'rich@.rich.net,
@.category_name=N'[Uncategorized]'
Yup.
Same problem here.
Rob
|||There is a hotfix for this.
I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.
|||This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.
Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.
(Please move to the Tools forum)
Incorrect Information in SQL Agent\Operator History
Apologies if this is in the wrong forum.
I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.
Not a major bug for me, but does anyone else have this problem?
--
DDL statement for operator.
EXEC msdb.dbo.sp_add_operator @.name=N'Rich',
@.enabled=1,
@.weekday_pager_start_time=90000,
@.weekday_pager_end_time=180000,
@.saturday_pager_start_time=90000,
@.saturday_pager_end_time=180000,
@.sunday_pager_start_time=90000,
@.sunday_pager_end_time=180000,
@.pager_days=0,
@.email_address=N'rich@.rich.net,
@.category_name=N'[Uncategorized]'
Yup.
Same problem here.
Rob
|||There is a hotfix for this.
I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.
|||This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.
Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.
(Please move to the Tools forum)
Incorrect Information in SQL Agent\Operator History
Apologies if this is in the wrong forum.
I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.
Not a major bug for me, but does anyone else have this problem?
--
DDL statement for operator.
EXEC msdb.dbo.sp_add_operator @.name=N'Rich',
@.enabled=1,
@.weekday_pager_start_time=90000,
@.weekday_pager_end_time=180000,
@.saturday_pager_start_time=90000,
@.saturday_pager_end_time=180000,
@.sunday_pager_start_time=90000,
@.sunday_pager_end_time=180000,
@.pager_days=0,
@.email_address=N'rich@.rich.net,
@.category_name=N'[Uncategorized]'
Yup.
Same problem here.
Rob
|||There is a hotfix for this.
I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.
|||This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.
Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.
(Please move to the Tools forum)
Incorrect information from a "View"
I am created a "view" like the one below
CREATE VIEW Tenant_Yearly AS
SELECT tn_proj as Company,
LTRIM(prj_name) as Company_Name,
tn_id as Tenant_ID,
ISNULL(monthly_rent.trm_amount,0) as Monthly_Rent_Amt,
ISNULL(monthly_other.tro_amount,0) as Monthly_Other_Amt,
ISNULL(monthly_rent.trm_amount*12,0) as Yearly_Rent_Amt,
ISNULL(monthly_other.tro_amount*12,0) as Yearly_Other_Amt,
ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
ISNULL(monthly_other.tro_amount*12/tn_sq_ft,0) as
Yearly_Other_Per_SqFt
FROM tenants
INNER JOIN projects
ON prj_id = tn_proj
LEFT OUTER JOIN
(SELECT tr_proj as trm_proj, tr_id as trm_id,
tr_amount as trm_amount
FROM ten_revenue trm WHERE trm.tr_code = 1)
as monthly_rent
ON trm_proj = tn_proj AND trm_id = tn_id
LEFT OUTER JOIN
(SELECT tr_proj as tro_proj, tr_id as tro_id,
sum(tr_amount) as tro_amount
FROM ten_revenue tro
WHERE tro.tr_code > 1 AND tro.tr_code < 90
GROUP BY tro.tr_proj, tro.tr_id)
as monthly_other
ON tro_proj = tn_proj AND tro_id = tn_id;
I have a program that selects the information from the "view", however
when my program selects the "Yearly_Rent_Per_SqFt", it doesn't select
the correct number. My Tenant's monthly rent amount is 1000.00 and the
Tenant's square feet is 1000, but the value that gets returned for the
Yearly_rent_per_sqft when selecting information from the view, is
20000000000000000{, and it should be 00000000000000120{.
Does anyone have any suggestions of why this is happening?mtt_trcy@.yahoo.com,
> ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
From which table is the column [tn_sq_ft] comming?
Can you include column [tn_sq_ft] in the "select" statement to be sure that
the value is 1000, same with [trm_amount]?
What data type are those columns?
AMB
AMB
"mtt_trcy@.yahoo.com" wrote:
> I am created a "view" like the one below
> CREATE VIEW Tenant_Yearly AS
> SELECT tn_proj as Company,
> LTRIM(prj_name) as Company_Name,
> tn_id as Tenant_ID,
> ISNULL(monthly_rent.trm_amount,0) as Monthly_Rent_Amt,
> ISNULL(monthly_other.tro_amount,0) as Monthly_Other_Amt,
> ISNULL(monthly_rent.trm_amount*12,0) as Yearly_Rent_Amt,
> ISNULL(monthly_other.tro_amount*12,0) as Yearly_Other_Amt,
> ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
> ISNULL(monthly_other.tro_amount*12/tn_sq_ft,0) as
> Yearly_Other_Per_SqFt
> FROM tenants
> INNER JOIN projects
> ON prj_id = tn_proj
> LEFT OUTER JOIN
> (SELECT tr_proj as trm_proj, tr_id as trm_id,
> tr_amount as trm_amount
> FROM ten_revenue trm WHERE trm.tr_code = 1)
> as monthly_rent
> ON trm_proj = tn_proj AND trm_id = tn_id
> LEFT OUTER JOIN
> (SELECT tr_proj as tro_proj, tr_id as tro_id,
> sum(tr_amount) as tro_amount
> FROM ten_revenue tro
> WHERE tro.tr_code > 1 AND tro.tr_code < 90
> GROUP BY tro.tr_proj, tro.tr_id)
> as monthly_other
> ON tro_proj = tn_proj AND tro_id = tn_id;
> I have a program that selects the information from the "view", however
> when my program selects the "Yearly_Rent_Per_SqFt", it doesn't select
> the correct number. My Tenant's monthly rent amount is 1000.00 and the
> Tenant's square feet is 1000, but the value that gets returned for the
> Yearly_rent_per_sqft when selecting information from the view, is
> 20000000000000000{, and it should be 00000000000000120{.
> Does anyone have any suggestions of why this is happening?
>|||tn_sq_ft is from the tenants table.
I put it is the select and it made no difference.
The data type of the Yearly_Rent_Per_SqFt column is decimal(38,17).
I think because the decimal part is so large, that it is contributing
to my problem, but I can't seem to get the decimal number to be a
smaller number. I think it would work if I could get the data type to
be decimal(38,2). I just can't seem to figure out how to do that.
Alejandro Mesa wrote:
> mtt_trcy@.yahoo.com,
>
> From which table is the column [tn_sq_ft] comming?
> Can you include column [tn_sq_ft] in the "select" statement to be sure tha
t
> the value is 1000, same with [trm_amount]?
> What data type are those columns?
>
> AMB
>
> AMB
>
> "mtt_trcy@.yahoo.com" wrote:
>|||Try using cast or convert in the expression,
ISNULL(
cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
numeric(7, 2)), 0) as Yearly_Rent_Per_SqFt,
AMB
"mtt_trcy@.yahoo.com" wrote:
> tn_sq_ft is from the tenants table.
> I put it is the select and it made no difference.
> The data type of the Yearly_Rent_Per_SqFt column is decimal(38,17).
> I think because the decimal part is so large, that it is contributing
> to my problem, but I can't seem to get the decimal number to be a
> smaller number. I think it would work if I could get the data type to
> be decimal(38,2). I just can't seem to figure out how to do that.
>
> Alejandro Mesa wrote:
>|||I tried your suggestion and the view gets created OK, but the data type
is still too big
the data type becomes decimal(22,10)
I tried lowering the numbers, but I could never get the second number
in the data type to be lower than 6. I need it to be 2.
Thank you for all your help. I really appreciate it. If you have any
other advice please send it my way.
Alejandro Mesa wrote:
> Try using cast or convert in the expression,
> ISNULL(
> cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
> numeric(7, 2)), 0) as Yearly_Rent_Per_SqFt,
>
> AMB
> "mtt_trcy@.yahoo.com" wrote:
>|||mtt_trcy@.yahoo.com,
Try casting the result also.
cast(
ISNULL(
cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
numeric(7, 2)), 0), as numeric(8, 2)
) as Yearly_Rent_Per_SqFt,
AMB
"mtt_trcy@.yahoo.com" wrote:
> I tried your suggestion and the view gets created OK, but the data type
> is still too big
> the data type becomes decimal(22,10)
> I tried lowering the numbers, but I could never get the second number
> in the data type to be lower than 6. I need it to be 2.
> Thank you for all your help. I really appreciate it. If you have any
> other advice please send it my way.
>
> Alejandro Mesa wrote:
>|||Alejandro,
Thank you so much. I think that will work for MSDE views.
Do you, by any chance, know if the "cast" will work when creating views
in Sybase 9?
Alejandro Mesa wrote:
> mtt_trcy@.yahoo.com,
> Try casting the result also.
> cast(
> ISNULL(
> cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
> numeric(7, 2)), 0), as numeric(8, 2)
> ) as Yearly_Rent_Per_SqFt,
>
> AMB
> "mtt_trcy@.yahoo.com" wrote:
>|||'CAST' the entire results to the form that you desire.
cast( isnull( monthly_rent.trm_amount * 12.00 / cast(tn_sq_ft as decimal(7,
2)), 0 ), decimal(9,2) )
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<mtt_trcy@.yahoo.com> wrote in message
news:1151673822.716321.294790@.x69g2000cwx.googlegroups.com...
>I tried your suggestion and the view gets created OK, but the data type
> is still too big
> the data type becomes decimal(22,10)
> I tried lowering the numbers, but I could never get the second number
> in the data type to be lower than 6. I need it to be 2.
> Thank you for all your help. I really appreciate it. If you have any
> other advice please send it my way.
>
> Alejandro Mesa wrote:
>
CREATE VIEW Tenant_Yearly AS
SELECT tn_proj as Company,
LTRIM(prj_name) as Company_Name,
tn_id as Tenant_ID,
ISNULL(monthly_rent.trm_amount,0) as Monthly_Rent_Amt,
ISNULL(monthly_other.tro_amount,0) as Monthly_Other_Amt,
ISNULL(monthly_rent.trm_amount*12,0) as Yearly_Rent_Amt,
ISNULL(monthly_other.tro_amount*12,0) as Yearly_Other_Amt,
ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
ISNULL(monthly_other.tro_amount*12/tn_sq_ft,0) as
Yearly_Other_Per_SqFt
FROM tenants
INNER JOIN projects
ON prj_id = tn_proj
LEFT OUTER JOIN
(SELECT tr_proj as trm_proj, tr_id as trm_id,
tr_amount as trm_amount
FROM ten_revenue trm WHERE trm.tr_code = 1)
as monthly_rent
ON trm_proj = tn_proj AND trm_id = tn_id
LEFT OUTER JOIN
(SELECT tr_proj as tro_proj, tr_id as tro_id,
sum(tr_amount) as tro_amount
FROM ten_revenue tro
WHERE tro.tr_code > 1 AND tro.tr_code < 90
GROUP BY tro.tr_proj, tro.tr_id)
as monthly_other
ON tro_proj = tn_proj AND tro_id = tn_id;
I have a program that selects the information from the "view", however
when my program selects the "Yearly_Rent_Per_SqFt", it doesn't select
the correct number. My Tenant's monthly rent amount is 1000.00 and the
Tenant's square feet is 1000, but the value that gets returned for the
Yearly_rent_per_sqft when selecting information from the view, is
20000000000000000{, and it should be 00000000000000120{.
Does anyone have any suggestions of why this is happening?mtt_trcy@.yahoo.com,
> ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
From which table is the column [tn_sq_ft] comming?
Can you include column [tn_sq_ft] in the "select" statement to be sure that
the value is 1000, same with [trm_amount]?
What data type are those columns?
AMB
AMB
"mtt_trcy@.yahoo.com" wrote:
> I am created a "view" like the one below
> CREATE VIEW Tenant_Yearly AS
> SELECT tn_proj as Company,
> LTRIM(prj_name) as Company_Name,
> tn_id as Tenant_ID,
> ISNULL(monthly_rent.trm_amount,0) as Monthly_Rent_Amt,
> ISNULL(monthly_other.tro_amount,0) as Monthly_Other_Amt,
> ISNULL(monthly_rent.trm_amount*12,0) as Yearly_Rent_Amt,
> ISNULL(monthly_other.tro_amount*12,0) as Yearly_Other_Amt,
> ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
> ISNULL(monthly_other.tro_amount*12/tn_sq_ft,0) as
> Yearly_Other_Per_SqFt
> FROM tenants
> INNER JOIN projects
> ON prj_id = tn_proj
> LEFT OUTER JOIN
> (SELECT tr_proj as trm_proj, tr_id as trm_id,
> tr_amount as trm_amount
> FROM ten_revenue trm WHERE trm.tr_code = 1)
> as monthly_rent
> ON trm_proj = tn_proj AND trm_id = tn_id
> LEFT OUTER JOIN
> (SELECT tr_proj as tro_proj, tr_id as tro_id,
> sum(tr_amount) as tro_amount
> FROM ten_revenue tro
> WHERE tro.tr_code > 1 AND tro.tr_code < 90
> GROUP BY tro.tr_proj, tro.tr_id)
> as monthly_other
> ON tro_proj = tn_proj AND tro_id = tn_id;
> I have a program that selects the information from the "view", however
> when my program selects the "Yearly_Rent_Per_SqFt", it doesn't select
> the correct number. My Tenant's monthly rent amount is 1000.00 and the
> Tenant's square feet is 1000, but the value that gets returned for the
> Yearly_rent_per_sqft when selecting information from the view, is
> 20000000000000000{, and it should be 00000000000000120{.
> Does anyone have any suggestions of why this is happening?
>|||tn_sq_ft is from the tenants table.
I put it is the select and it made no difference.
The data type of the Yearly_Rent_Per_SqFt column is decimal(38,17).
I think because the decimal part is so large, that it is contributing
to my problem, but I can't seem to get the decimal number to be a
smaller number. I think it would work if I could get the data type to
be decimal(38,2). I just can't seem to figure out how to do that.
Alejandro Mesa wrote:
> mtt_trcy@.yahoo.com,
>
> From which table is the column [tn_sq_ft] comming?
> Can you include column [tn_sq_ft] in the "select" statement to be sure tha
t
> the value is 1000, same with [trm_amount]?
> What data type are those columns?
>
> AMB
>
> AMB
>
> "mtt_trcy@.yahoo.com" wrote:
>|||Try using cast or convert in the expression,
ISNULL(
cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
numeric(7, 2)), 0) as Yearly_Rent_Per_SqFt,
AMB
"mtt_trcy@.yahoo.com" wrote:
> tn_sq_ft is from the tenants table.
> I put it is the select and it made no difference.
> The data type of the Yearly_Rent_Per_SqFt column is decimal(38,17).
> I think because the decimal part is so large, that it is contributing
> to my problem, but I can't seem to get the decimal number to be a
> smaller number. I think it would work if I could get the data type to
> be decimal(38,2). I just can't seem to figure out how to do that.
>
> Alejandro Mesa wrote:
>|||I tried your suggestion and the view gets created OK, but the data type
is still too big
the data type becomes decimal(22,10)
I tried lowering the numbers, but I could never get the second number
in the data type to be lower than 6. I need it to be 2.
Thank you for all your help. I really appreciate it. If you have any
other advice please send it my way.
Alejandro Mesa wrote:
> Try using cast or convert in the expression,
> ISNULL(
> cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
> numeric(7, 2)), 0) as Yearly_Rent_Per_SqFt,
>
> AMB
> "mtt_trcy@.yahoo.com" wrote:
>|||mtt_trcy@.yahoo.com,
Try casting the result also.
cast(
ISNULL(
cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
numeric(7, 2)), 0), as numeric(8, 2)
) as Yearly_Rent_Per_SqFt,
AMB
"mtt_trcy@.yahoo.com" wrote:
> I tried your suggestion and the view gets created OK, but the data type
> is still too big
> the data type becomes decimal(22,10)
> I tried lowering the numbers, but I could never get the second number
> in the data type to be lower than 6. I need it to be 2.
> Thank you for all your help. I really appreciate it. If you have any
> other advice please send it my way.
>
> Alejandro Mesa wrote:
>|||Alejandro,
Thank you so much. I think that will work for MSDE views.
Do you, by any chance, know if the "cast" will work when creating views
in Sybase 9?
Alejandro Mesa wrote:
> mtt_trcy@.yahoo.com,
> Try casting the result also.
> cast(
> ISNULL(
> cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
> numeric(7, 2)), 0), as numeric(8, 2)
> ) as Yearly_Rent_Per_SqFt,
>
> AMB
> "mtt_trcy@.yahoo.com" wrote:
>|||'CAST' the entire results to the form that you desire.
cast( isnull( monthly_rent.trm_amount * 12.00 / cast(tn_sq_ft as decimal(7,
2)), 0 ), decimal(9,2) )
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<mtt_trcy@.yahoo.com> wrote in message
news:1151673822.716321.294790@.x69g2000cwx.googlegroups.com...
>I tried your suggestion and the view gets created OK, but the data type
> is still too big
> the data type becomes decimal(22,10)
> I tried lowering the numbers, but I could never get the second number
> in the data type to be lower than 6. I need it to be 2.
> Thank you for all your help. I really appreciate it. If you have any
> other advice please send it my way.
>
> Alejandro Mesa wrote:
>
Incorrect columns returned by view
When I create a view consisting of an inner join between a view and a
table the columns refernced in the the view are returned incorrectly,
example
table the columns refernced in the the view are returned incorrectly,
example
select id.itemcode, id.description, iv.linevalue, iv.vatvalue from
dbo.tbl_itemdetails id inner join dbo.vw_invoices iv where
dbo.tbl_itemdetails.itemcode = dbo.vw_invoices.itemcode
The actual value that is returned from the view is the column
immediately to the left of the linevalue column, ie stockroomThe code you have posted isn't correct syntax, you've missed the ON clause.
One possibility for you: Could it be that you have some control characters
such as carriage return/line feed in one of the columns you are returning?
Special characters sometimes cause formatting problems when displaying data
in Query Analyzer.
If you need more help please post some code we can run that will reproduce
the problem: DDL (CREATE TABLE statement(s) for the table(s)), sample data
(INSERT statements) and the actual definition of the view.
--
David Portas
SQL Server MVP
--|||Yeah it was just a quick rehash of the view, limiting the information
for demo purposes the actual view(vw_test) is below:
CREATE VIEW dbo.vw_test
AS
SELECT dbo.vw_Invoices_I.ID_Company_Number,
dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
dbo.vw_Invoices_I.I_Order_Line_Number,
dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
dbo.vw_Invoices_I.I_Delivery_Address_Code,
dbo.vw_Invoices_I.I_Line_Value *
dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
dbo.vw_Invoices_I.I_VAT_Value,
dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
dbo.vw_Invoices_I.I_Line_Cost_Value
FROM dbo.tbl_Lagged_Sales INNER JOIN
dbo.vw_Invoices_I ON
dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
dbo.tbl_Lagged_Sales.Lag_Product_Group =
dbo.vw_Invoices_I.ID_Product_GroupHi
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tbl_Invoices_I_DB_TEST]
GO
CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
[I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Line_Number] [smallint] NOT NULL ,
[I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[I_Line_Value] [numeric](17, 2) NOT NULL ,
[I_VAT_Value] [numeric](17, 2) NULL ,
[I_Discount_Value] [numeric](17, 2) NULL ,
[I_Standard_Cost] [numeric](17, 2) NULL ,
[I_Line_Quantity] [numeric](13, 3) NOT NULL ,
[I_Week_Number] [int] NULL ,
[I_Period_Number] [int] NULL ,
[I_Transaction_Type] [smallint] NULL ,
[I_Transaction_Date] [int] NULL ,
[I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
,
[I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
[I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Parent_Line_Number] [smallint] NULL ,
[I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
NULL ,
[I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Type] [int] NULL ,
[I_Month_Number] [tinyint] NOT NULL ,
[I_Year_Number] [smallint] NOT NULL ,
[I_Transaction_Date_PC] [datetime] NOT NULL ,
[I_Gross_Margin] [numeric](17, 2) NULL ,
[I_Date_Downloaded] [datetime] NULL ,
[Flagged_for_Exception] [bit] NULL
) ON [PRIMARY]
GO
CREATE VIEW dbo.vw_Invoices_I
AS
SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
dbo.vw_Customer_Details.*
FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
dbo.vw_Customer_Details ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Customer_Details.CD_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
dbo.vw_Customer_Details.CD_Customer_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
= dbo.vw_Customer_Details.CD_Dseq INNER JOIN
dbo.vw_Item_Details_ID ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Item_Details_ID.ID_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
dbo.vw_Item_Details_ID.ID_Item_Code
CREATE VIEW dbo.vw_Item_Details_ID
AS
SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
dbo.tbl_Item_Details_ID.ID_Item_Code,
dbo.tbl_Item_Details_ID.ID_Description,
dbo.tbl_Item_Details_ID.ID_STD_Cost,
dbo.tbl_Item_Details_ID.ID_Product_Type,
dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
dbo.tbl_Item_Details_ID.ID_Company,
dbo.tbl_ILU_A_COMP.A_Company_Description,
dbo.tbl_Item_Details_ID.ID_Brand_Type,
dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
dbo.tbl_Item_Details_ID.ID_Brand,
dbo.tbl_ILU_C_BRND.C_Brand_Description,
dbo.tbl_Item_Details_ID.ID_Range,
dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
dbo.tbl_ILU_E_ITEM.E_Item_Description,
dbo.tbl_Item_Details_ID.ID_Function_Description,
dbo.tbl_ILU_F_DESC.F_Description_Description,
dbo.tbl_Item_Details_ID.ID_Det_Fuel,
dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
dbo.tbl_Item_Details_ID.ID_Colour,
dbo.tbl_ILU_H_COLR.H_Colour_Description,
dbo.tbl_Item_Details_ID.ID_TypeCat,
dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
dbo.tbl_Item_Details_ID.ID_DetFunc,
dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
dbo.tbl_Item_Details_ID.ID_Function,
dbo.tbl_ILU_K_FCTN.K_Function_Description,
dbo.tbl_Item_Details_ID.ID_Owner,
dbo.tbl_ILU_L_OWNR.L_Owner_Description,
dbo.tbl_Item_Details_ID.ID_Application,
dbo.tbl_ILU_M_APPL.M_Application_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Group,
dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,
dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
dbo.tbl_Item_Details_ID.ID_Product_Group,
dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
dbo.tbl_Item_Details_ID.ID_Top_Fuel,
dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
FROM dbo.tbl_Item_Details_ID INNER JOIN
dbo.tbl_ILU_P_MODL ON
dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_A_COMP ON
dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
OUTER JOIN
dbo.tbl_ILU_I_TYPECAT ON
dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
LEFT OUTER JOIN
dbo.tbl_ILU_E_ITEM ON
dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
OUTER JOIN
dbo.tbl_ILU_G_DET_FUEL ON
dbo.tbl_Item_Details_ID.ID_Det_Fuel =
dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
dbo.tbl_ILU_J_DETFUNC ON
dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
LEFT OUTER JOIN
dbo.tbl_ILU_N_TFUL ON
dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_O_GROP ON
dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_C_BRND ON
dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
OUTER JOIN
dbo.tbl_ILU_K_FCTN ON
dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
OUTER JOIN
dbo.tbl_ILU_M_APPL ON
dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
LEFT OUTER JOIN
dbo.tbl_ILU_P_PLNSG ON
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
dbo.tbl_ILU_O_PLANG ON
dbo.tbl_Item_Details_ID.ID_Planning_Group =
dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
dbo.tbl_ILU_L_OWNR ON
dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_H_COLR ON
dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_F_DESC ON
dbo.tbl_Item_Details_ID.ID_Function_Description =
dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
dbo.tbl_ILU_D_RANG ON
dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
OUTER JOIN
dbo.tbl_ILU_B_BTYP ON
dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_Z_PTYP ON
dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID
CREATE VIEW dbo.vw_Customer_Details
AS
SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
dbo.tbl_Customer_Details_CD.CD_Customer_Number,
dbo.tbl_Customer_Details_CD.CD_Dseq,
dbo.tbl_Customer_Details_CD.CD_Customer_Name,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
dbo.tbl_Customer_Details_CD.CD_Region,
dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
dbo.tbl_Customer_Details_CD.CD_Phone_Number,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,
dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
dbo.tbl_Credit_Controller.CC_Description,
dbo.tbl_Customer_Details_CD.CD_Customer_Group,
dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
dbo.tbl_Customer_Details_CD.CD_Customer_Parent,
dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
dbo.tbl_Customer_Details_CD.CD_Sales_Region,
dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
dbo.tbl_Customer_Details_CD.CD_Business_Type,
dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,
dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,
dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Marketing_Director,
dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
dbo.tbl_Customer_Details_CD.CD_Sales_Director,
dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
dbo.tbl_Customer_Details_CD.CD_Account_Manager,
dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,
dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
dbo.tbl_CLU_I_CNSL ON
dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
dbo.tbl_Credit_Controller ON
dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
dbo.tbl_CLU_H_AMR ON
dbo.tbl_Customer_Details_CD.CD_Account_Manager =
dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
dbo.tbl_CLU_G_SDR ON
dbo.tbl_Customer_Details_CD.CD_Sales_Director =
dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_F_MDR ON
dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_E_DTYP ON
dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_D_DCSR ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
dbo.tbl_CLU_C_DCNL ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
dbo.tbl_CLU_B_BTYP ON
dbo.tbl_Customer_Details_CD.CD_Business_Type =
dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_A_SRGN ON
dbo.tbl_Customer_Details_CD.CD_Sales_Region =
dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
dbo.tbl_CLU_Y_CPT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
dbo.tbl_CLU_Z_CGT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Group =
dbo.tbl_CLU_Z_CGT.Z_CGT_ID
The above should give you some idea of the data, I haven't include the
scripts for the base tables as this would take considerable time
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi
It is better not to specify * in the view definition especially in
production code. If you insist on doing it I would recomend using the WITH
SCHEMABINDING attribute. You will probably find that dbo.vw_Invoices_I is
not returning the correct data as you have changed the underlying tables.
Look at sp_refreshview in Books online
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_ra-rz_7qnr.htm
John
"John Dennison" <john.dennison@.glendimplex.com> wrote in message
news:407fb2aa$0$204$75868355@.news.frii.net...
> Yeah it was just a quick rehash of the view, limiting the information
> for demo purposes the actual view(vw_test) is below:
> CREATE VIEW dbo.vw_test
> AS
> SELECT dbo.vw_Invoices_I.ID_Company_Number,
> dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
> dbo.vw_Invoices_I.I_Order_Line_Number,
> dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
> dbo.vw_Invoices_I.I_Delivery_Address_Code,
> dbo.vw_Invoices_I.I_Line_Value *
> dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
> dbo.vw_Invoices_I.I_VAT_Value,
> dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
> dbo.vw_Invoices_I.I_Line_Cost_Value
> FROM dbo.tbl_Lagged_Sales INNER JOIN
> dbo.vw_Invoices_I ON
> dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
> dbo.tbl_Lagged_Sales.Lag_Product_Group =
> dbo.vw_Invoices_I.ID_Product_GroupHi
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tbl_Invoices_I_DB_TEST]
> GO
> CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
> [I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Line_Number] [smallint] NOT NULL ,
> [I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [I_Line_Value] [numeric](17, 2) NOT NULL ,
> [I_VAT_Value] [numeric](17, 2) NULL ,
> [I_Discount_Value] [numeric](17, 2) NULL ,
> [I_Standard_Cost] [numeric](17, 2) NULL ,
> [I_Line_Quantity] [numeric](13, 3) NOT NULL ,
> [I_Week_Number] [int] NULL ,
> [I_Period_Number] [int] NULL ,
> [I_Transaction_Type] [smallint] NULL ,
> [I_Transaction_Date] [int] NULL ,
> [I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
> [I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
> ,
> [I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
> [I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Parent_Line_Number] [smallint] NULL ,
> [I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
> NULL ,
> [I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Type] [int] NULL ,
> [I_Month_Number] [tinyint] NOT NULL ,
> [I_Year_Number] [smallint] NOT NULL ,
> [I_Transaction_Date_PC] [datetime] NOT NULL ,
> [I_Gross_Margin] [numeric](17, 2) NULL ,
> [I_Date_Downloaded] [datetime] NULL ,
> [Flagged_for_Exception] [bit] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE VIEW dbo.vw_Invoices_I
> AS
> SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
> dbo.vw_Customer_Details.*
> FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
> dbo.vw_Customer_Details ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Customer_Details.CD_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
> dbo.vw_Customer_Details.CD_Customer_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
> = dbo.vw_Customer_Details.CD_Dseq INNER JOIN
> dbo.vw_Item_Details_ID ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Item_Details_ID.ID_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
> dbo.vw_Item_Details_ID.ID_Item_Code
> CREATE VIEW dbo.vw_Item_Details_ID
> AS
> SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
> dbo.tbl_Item_Details_ID.ID_Item_Code,
> dbo.tbl_Item_Details_ID.ID_Description,
> dbo.tbl_Item_Details_ID.ID_STD_Cost,
> dbo.tbl_Item_Details_ID.ID_Product_Type,
> dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Company,
> dbo.tbl_ILU_A_COMP.A_Company_Description,
> dbo.tbl_Item_Details_ID.ID_Brand_Type,
> dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Brand,
> dbo.tbl_ILU_C_BRND.C_Brand_Description,
> dbo.tbl_Item_Details_ID.ID_Range,
> dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
> dbo.tbl_ILU_E_ITEM.E_Item_Description,
> dbo.tbl_Item_Details_ID.ID_Function_Description,
> dbo.tbl_ILU_F_DESC.F_Description_Description,
> dbo.tbl_Item_Details_ID.ID_Det_Fuel,
> dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
> dbo.tbl_Item_Details_ID.ID_Colour,
> dbo.tbl_ILU_H_COLR.H_Colour_Description,
> dbo.tbl_Item_Details_ID.ID_TypeCat,
> dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
> dbo.tbl_Item_Details_ID.ID_DetFunc,
> dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
> dbo.tbl_Item_Details_ID.ID_Function,
> dbo.tbl_ILU_K_FCTN.K_Function_Description,
> dbo.tbl_Item_Details_ID.ID_Owner,
> dbo.tbl_ILU_L_OWNR.L_Owner_Description,
> dbo.tbl_Item_Details_ID.ID_Application,
> dbo.tbl_ILU_M_APPL.M_Application_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Group,
> dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,
> dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
> dbo.tbl_Item_Details_ID.ID_Product_Group,
> dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
> dbo.tbl_Item_Details_ID.ID_Top_Fuel,
> dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
> dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
> dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
> FROM dbo.tbl_Item_Details_ID INNER JOIN
> dbo.tbl_ILU_P_MODL ON
> dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_A_COMP ON
> dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_I_TYPECAT ON
> dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_E_ITEM ON
> dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_G_DET_FUEL ON
> dbo.tbl_Item_Details_ID.ID_Det_Fuel =
> dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
> dbo.tbl_ILU_J_DETFUNC ON
> dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_N_TFUL ON
> dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_O_GROP ON
> dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_C_BRND ON
> dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_K_FCTN ON
> dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_M_APPL ON
> dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_P_PLNSG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
> dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_O_PLANG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Group =
> dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_L_OWNR ON
> dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_H_COLR ON
> dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_F_DESC ON
> dbo.tbl_Item_Details_ID.ID_Function_Description =
> dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
> dbo.tbl_ILU_D_RANG ON
> dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_B_BTYP ON
> dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_Z_PTYP ON
> dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID
> CREATE VIEW dbo.vw_Customer_Details
> AS
> SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
> dbo.tbl_Customer_Details_CD.CD_Customer_Number,
> dbo.tbl_Customer_Details_CD.CD_Dseq,
> dbo.tbl_Customer_Details_CD.CD_Customer_Name,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
> dbo.tbl_Customer_Details_CD.CD_Region,
> dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
> dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
> dbo.tbl_Customer_Details_CD.CD_Phone_Number,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
> dbo.tbl_Credit_Controller.CC_Description,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group,
> dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent,
> dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
> dbo.tbl_Customer_Details_CD.CD_Sales_Region,
> dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
> dbo.tbl_Customer_Details_CD.CD_Business_Type,
> dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,
> dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,
> dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
> dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director,
> dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
> dbo.tbl_Customer_Details_CD.CD_Sales_Director,
> dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
> dbo.tbl_Customer_Details_CD.CD_Account_Manager,
> dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
> dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
> FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
> dbo.tbl_CLU_I_CNSL ON
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
> dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
> dbo.tbl_Credit_Controller ON
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
> dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
> dbo.tbl_CLU_H_AMR ON
> dbo.tbl_Customer_Details_CD.CD_Account_Manager =
> dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_G_SDR ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Director =
> dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_F_MDR ON
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
> dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_E_DTYP ON
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
> dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_D_DCSR ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
> dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_C_DCNL ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
> dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
> dbo.tbl_CLU_B_BTYP ON
> dbo.tbl_Customer_Details_CD.CD_Business_Type =
> dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_A_SRGN ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Region =
> dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Y_CPT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
> dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Z_CGT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Group =
> dbo.tbl_CLU_Z_CGT.Z_CGT_ID
>
> The above should give you some idea of the data, I haven't include the
> scripts for the base tables as this would take considerable time
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi
It is better not to specify * in the view definition especially in
production code. If you insist on doing it I would recomend using the WITH
SCHEMABINDING attribute. You will probably find that dbo.vw_Invoices_I is
not returning the correct data as you have changed the underlying tables.
Look at sp_refreshview in Books online
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_ra-rz_7qnr.htm
John
"John Dennison" <john.dennison@.glendimplex.com> wrote in message
news:407fb2aa$0$204$75868355@.news.frii.net...
> Yeah it was just a quick rehash of the view, limiting the information
> for demo purposes the actual view(vw_test) is below:
> CREATE VIEW dbo.vw_test
> AS
> SELECT dbo.vw_Invoices_I.ID_Company_Number,
> dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
> dbo.vw_Invoices_I.I_Order_Line_Number,
> dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
> dbo.vw_Invoices_I.I_Delivery_Address_Code,
> dbo.vw_Invoices_I.I_Line_Value *
> dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
> dbo.vw_Invoices_I.I_VAT_Value,
> dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
> dbo.vw_Invoices_I.I_Line_Cost_Value
> FROM dbo.tbl_Lagged_Sales INNER JOIN
> dbo.vw_Invoices_I ON
> dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
> dbo.tbl_Lagged_Sales.Lag_Product_Group =
> dbo.vw_Invoices_I.ID_Product_GroupHi
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tbl_Invoices_I_DB_TEST]
> GO
> CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
> [I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Line_Number] [smallint] NOT NULL ,
> [I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [I_Line_Value] [numeric](17, 2) NOT NULL ,
> [I_VAT_Value] [numeric](17, 2) NULL ,
> [I_Discount_Value] [numeric](17, 2) NULL ,
> [I_Standard_Cost] [numeric](17, 2) NULL ,
> [I_Line_Quantity] [numeric](13, 3) NOT NULL ,
> [I_Week_Number] [int] NULL ,
> [I_Period_Number] [int] NULL ,
> [I_Transaction_Type] [smallint] NULL ,
> [I_Transaction_Date] [int] NULL ,
> [I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
> [I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
> ,
> [I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
> [I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Parent_Line_Number] [smallint] NULL ,
> [I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
> NULL ,
> [I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Type] [int] NULL ,
> [I_Month_Number] [tinyint] NOT NULL ,
> [I_Year_Number] [smallint] NOT NULL ,
> [I_Transaction_Date_PC] [datetime] NOT NULL ,
> [I_Gross_Margin] [numeric](17, 2) NULL ,
> [I_Date_Downloaded] [datetime] NULL ,
> [Flagged_for_Exception] [bit] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE VIEW dbo.vw_Invoices_I
> AS
> SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
> dbo.vw_Customer_Details.*
> FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
> dbo.vw_Customer_Details ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Customer_Details.CD_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
> dbo.vw_Customer_Details.CD_Customer_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
> = dbo.vw_Customer_Details.CD_Dseq INNER JOIN
> dbo.vw_Item_Details_ID ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Item_Details_ID.ID_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
> dbo.vw_Item_Details_ID.ID_Item_Code
> CREATE VIEW dbo.vw_Item_Details_ID
> AS
> SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
> dbo.tbl_Item_Details_ID.ID_Item_Code,
> dbo.tbl_Item_Details_ID.ID_Description,
> dbo.tbl_Item_Details_ID.ID_STD_Cost,
> dbo.tbl_Item_Details_ID.ID_Product_Type,
> dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Company,
> dbo.tbl_ILU_A_COMP.A_Company_Description,
> dbo.tbl_Item_Details_ID.ID_Brand_Type,
> dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Brand,
> dbo.tbl_ILU_C_BRND.C_Brand_Description,
> dbo.tbl_Item_Details_ID.ID_Range,
> dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
> dbo.tbl_ILU_E_ITEM.E_Item_Description,
> dbo.tbl_Item_Details_ID.ID_Function_Description,
> dbo.tbl_ILU_F_DESC.F_Description_Description,
> dbo.tbl_Item_Details_ID.ID_Det_Fuel,
> dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
> dbo.tbl_Item_Details_ID.ID_Colour,
> dbo.tbl_ILU_H_COLR.H_Colour_Description,
> dbo.tbl_Item_Details_ID.ID_TypeCat,
> dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
> dbo.tbl_Item_Details_ID.ID_DetFunc,
> dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
> dbo.tbl_Item_Details_ID.ID_Function,
> dbo.tbl_ILU_K_FCTN.K_Function_Description,
> dbo.tbl_Item_Details_ID.ID_Owner,
> dbo.tbl_ILU_L_OWNR.L_Owner_Description,
> dbo.tbl_Item_Details_ID.ID_Application,
> dbo.tbl_ILU_M_APPL.M_Application_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Group,
> dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,
> dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
> dbo.tbl_Item_Details_ID.ID_Product_Group,
> dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
> dbo.tbl_Item_Details_ID.ID_Top_Fuel,
> dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
> dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
> dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
> FROM dbo.tbl_Item_Details_ID INNER JOIN
> dbo.tbl_ILU_P_MODL ON
> dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_A_COMP ON
> dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_I_TYPECAT ON
> dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_E_ITEM ON
> dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_G_DET_FUEL ON
> dbo.tbl_Item_Details_ID.ID_Det_Fuel =
> dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
> dbo.tbl_ILU_J_DETFUNC ON
> dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_N_TFUL ON
> dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_O_GROP ON
> dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_C_BRND ON
> dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_K_FCTN ON
> dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_M_APPL ON
> dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_P_PLNSG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
> dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_O_PLANG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Group =
> dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_L_OWNR ON
> dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_H_COLR ON
> dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_F_DESC ON
> dbo.tbl_Item_Details_ID.ID_Function_Description =
> dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
> dbo.tbl_ILU_D_RANG ON
> dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_B_BTYP ON
> dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_Z_PTYP ON
> dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID
> CREATE VIEW dbo.vw_Customer_Details
> AS
> SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
> dbo.tbl_Customer_Details_CD.CD_Customer_Number,
> dbo.tbl_Customer_Details_CD.CD_Dseq,
> dbo.tbl_Customer_Details_CD.CD_Customer_Name,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
> dbo.tbl_Customer_Details_CD.CD_Region,
> dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
> dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
> dbo.tbl_Customer_Details_CD.CD_Phone_Number,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
> dbo.tbl_Credit_Controller.CC_Description,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group,
> dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent,
> dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
> dbo.tbl_Customer_Details_CD.CD_Sales_Region,
> dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
> dbo.tbl_Customer_Details_CD.CD_Business_Type,
> dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,
> dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,
> dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
> dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director,
> dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
> dbo.tbl_Customer_Details_CD.CD_Sales_Director,
> dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
> dbo.tbl_Customer_Details_CD.CD_Account_Manager,
> dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
> dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
> FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
> dbo.tbl_CLU_I_CNSL ON
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
> dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
> dbo.tbl_Credit_Controller ON
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
> dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
> dbo.tbl_CLU_H_AMR ON
> dbo.tbl_Customer_Details_CD.CD_Account_Manager =
> dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_G_SDR ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Director =
> dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_F_MDR ON
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
> dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_E_DTYP ON
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
> dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_D_DCSR ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
> dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_C_DCNL ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
> dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
> dbo.tbl_CLU_B_BTYP ON
> dbo.tbl_Customer_Details_CD.CD_Business_Type =
> dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_A_SRGN ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Region =
> dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Y_CPT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
> dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Z_CGT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Group =
> dbo.tbl_CLU_Z_CGT.Z_CGT_ID
>
> The above should give you some idea of the data, I haven't include the
> scripts for the base tables as this would take considerable time
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Subscribe to:
Posts (Atom)