i Get rsReportServerNotActivated error message although it is activated.

Hi
I get this error when I want to view a report in a browser:
Reporting Services Error
----
--
The report server installation is not initialized. Check the documentation
for more information. (rsReportServerNotActivated) Get Online Help
----
--
Microsoft Reporting Services
But when I run rsactivate I get the following message:
Failure initializing remote NT Service:
The service is already activated
Any suggestions?
My OS is WinXP Pro if it is of any use.Problem solved - uninstall an re-install did the trick.
"Howzit" <hrbotha@.absa.co.za> wrote in message
news:OW2Sn8PZEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
> I get this error when I want to view a report in a browser:
> Reporting Services Error
> ----
--
> --
> The report server installation is not initialized. Check the documentation
> for more information. (rsReportServerNotActivated) Get Online Help
> ----
--
> --
> Microsoft Reporting Services
> But when I run rsactivate I get the following message:
> Failure initializing remote NT Service:
> The service is already activated
> Any suggestions?
> My OS is WinXP Pro if it is of any use.
>

I can't view the list of databases on remote site

I have been asked to work on a remote site. I started my EM and tried to
connect to the SQL server on the remote site. I connected just fine (server
had userid/password). But when I open the "Databases" folder I get a "(no
items)" instead of a list of databases. BUT, when I view other folders I
see various items. For example, Security/Login lists lots of login entries.
Just to see what would happen I right-clicked on Databases and clicked All
Tasks/Backup Database and got a list of databases that I was NOT allowed to
backup! But, still no list of databases.
Also, I can view and manipulate the database using Query Analyzer.
Any ideas?
BTW, I am using SQL Server 2000 EM.
TIA,
Larry Woods
P.S. I cross-posted in sqlserver.server because I need an answer ASAP.
Sorry.Hi
Public probably does not have rights to master..sysdatabases but you
probably have a permitted user for the databases but without the permissions
to backup.
John
"lwoods" <larry@.lwoods.com> wrote in message
news:e%23nZpPe0FHA.1032@.TK2MSFTNGP12.phx.gbl...
> I have been asked to work on a remote site. I started my EM and tried to
> connect to the SQL server on the remote site. I connected just fine
> (server
> had userid/password). But when I open the "Databases" folder I get a "(no
> items)" instead of a list of databases. BUT, when I view other folders I
> see various items. For example, Security/Login lists lots of login
> entries.
> Just to see what would happen I right-clicked on Databases and clicked All
> Tasks/Backup Database and got a list of databases that I was NOT allowed
> to
> backup! But, still no list of databases.
> Also, I can view and manipulate the database using Query Analyzer.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
> P.S. I cross-posted in sqlserver.server because I need an answer ASAP.
> Sorry.
>
>

I can't view the list of databases on remote site

I have been asked to work on a remote site. I started my EM and tried to
connect to the SQL server on the remote site. I connected just fine (server
had userid/password). But when I open the "Databases" folder I get a "(no
items)" instead of a list of databases. BUT, when I view other folders I
see various items. For example, Security/Login lists lots of login entries.
Just to see what would happen I right-clicked on Databases and clicked All
Tasks/Backup Database and got a list of databases that I was NOT allowed to
backup! But, still no list of databases.
Any ideas?
BTW, I am using SQL Server 2000 EM.
TIA,
Larry WoodsHi,
Could you refresh the database option and see.
As well as login to Query analyzer and execute the below command to get the
databases:-
SP_HELPDB
For backing up try
BACKUP Database <dbanme> to disk='d:\backup\dbname_Oct16.bak' with
INIT,STATS=5
If you get a permission issue contact the database admin.
Thanks
Hari
SQL Server MVP
"lwoods" <larry@.lwoods.com> wrote in message
news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>I have been asked to work on a remote site. I started my EM and tried to
>connect to the SQL server on the remote site. I connected just fine
>(server had userid/password). But when I open the "Databases" folder I get
>a "(no items)" instead of a list of databases. BUT, when I view other
>folders I see various items. For example, Security/Login lists lots of
>login entries. Just to see what would happen I right-clicked on Databases
>and clicked All Tasks/Backup Database and got a list of databases that I
>was NOT allowed to backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>|||Please try to connect usin Query Analyzer and execute the following
use master
go
select *
from information_schema.schemata
select *
from sysdatabases
This will enable you to display all databases on a server
--
Best Regards
Krystian Zieja / mob
"lwoods" wrote:
> I have been asked to work on a remote site. I started my EM and tried to
> connect to the SQL server on the remote site. I connected just fine (server
> had userid/password). But when I open the "Databases" folder I get a "(no
> items)" instead of a list of databases. BUT, when I view other folders I
> see various items. For example, Security/Login lists lots of login entries.
> Just to see what would happen I right-clicked on Databases and clicked All
> Tasks/Backup Database and got a list of databases that I was NOT allowed to
> backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>
>|||Hi
See answer in .programming. Please do not multi-post.
John
"lwoods" <larry@.lwoods.com> wrote in message
news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>I have been asked to work on a remote site. I started my EM and tried to
>connect to the SQL server on the remote site. I connected just fine
>(server had userid/password). But when I open the "Databases" folder I get
>a "(no items)" instead of a list of databases. BUT, when I view other
>folders I see various items. For example, Security/Login lists lots of
>login entries. Just to see what would happen I right-clicked on Databases
>and clicked All Tasks/Backup Database and got a list of databases that I
>was NOT allowed to backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>|||Will do...
Please note that I DON'T usually do this, but I needed an answer FAST so I
tried to connect to multiple groups. I understand the problem...
Larry
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23SZCJEk0FHA.2600@.TK2MSFTNGP10.phx.gbl...
> Hi
> See answer in .programming. Please do not multi-post.
>
> John
> "lwoods" <larry@.lwoods.com> wrote in message
> news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>>I have been asked to work on a remote site. I started my EM and tried to
>>connect to the SQL server on the remote site. I connected just fine
>>(server had userid/password). But when I open the "Databases" folder I
>>get a "(no items)" instead of a list of databases. BUT, when I view other
>>folders I see various items. For example, Security/Login lists lots of
>>login entries. Just to see what would happen I right-clicked on Databases
>>and clicked All Tasks/Backup Database and got a list of databases that I
>>was NOT allowed to backup! But, still no list of databases.
>> Any ideas?
>>
>> BTW, I am using SQL Server 2000 EM.
>> TIA,
>> Larry Woods
>sql

I can't view the list of databases on remote site

I have been asked to work on a remote site. I started my EM and tried to
connect to the SQL server on the remote site. I connected just fine (server
had userid/password). But when I open the "Databases" folder I get a "(no
items)" instead of a list of databases. BUT, when I view other folders I
see various items. For example, Security/Login lists lots of login entries.
Just to see what would happen I right-clicked on Databases and clicked All
Tasks/Backup Database and got a list of databases that I was NOT allowed to
backup! But, still no list of databases.
Any ideas?
BTW, I am using SQL Server 2000 EM.
TIA,
Larry WoodsHi,
Could you refresh the database option and see.
As well as login to Query analyzer and execute the below command to get the
databases:-
SP_HELPDB
For backing up try
BACKUP Database <dbanme> to disk='d:\backup\dbname_Oct16.bak' with
INIT,STATS=5
If you get a permission issue contact the database admin.
Thanks
Hari
SQL Server MVP
"lwoods" <larry@.lwoods.com> wrote in message
news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>I have been asked to work on a remote site. I started my EM and tried to
>connect to the SQL server on the remote site. I connected just fine
>(server had userid/password). But when I open the "Databases" folder I get
>a "(no items)" instead of a list of databases. BUT, when I view other
>folders I see various items. For example, Security/Login lists lots of
>login entries. Just to see what would happen I right-clicked on Databases
>and clicked All Tasks/Backup Database and got a list of databases that I
>was NOT allowed to backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>|||Please try to connect usin Query Analyzer and execute the following
use master
go
select *
from information_schema.schemata
select *
from sysdatabases
This will enable you to display all databases on a server
--
Best Regards
Krystian Zieja / mob
"lwoods" wrote:

> I have been asked to work on a remote site. I started my EM and tried to
> connect to the SQL server on the remote site. I connected just fine (serv
er
> had userid/password). But when I open the "Databases" folder I get a "(no
> items)" instead of a list of databases. BUT, when I view other folders I
> see various items. For example, Security/Login lists lots of login entrie
s.
> Just to see what would happen I right-clicked on Databases and clicked All
> Tasks/Backup Database and got a list of databases that I was NOT allowed t
o
> backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>
>|||Hi
See answer in .programming. Please do not multi-post.
John
"lwoods" <larry@.lwoods.com> wrote in message
news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>I have been asked to work on a remote site. I started my EM and tried to
>connect to the SQL server on the remote site. I connected just fine
>(server had userid/password). But when I open the "Databases" folder I get
>a "(no items)" instead of a list of databases. BUT, when I view other
>folders I see various items. For example, Security/Login lists lots of
>login entries. Just to see what would happen I right-clicked on Databases
>and clicked All Tasks/Backup Database and got a list of databases that I
>was NOT allowed to backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>|||Will do...
Please note that I DON'T usually do this, but I needed an answer FAST so I
tried to connect to multiple groups. I understand the problem...
Larry
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23SZCJEk0FHA.2600@.TK2MSFTNGP10.phx.gbl...
> Hi
> See answer in .programming. Please do not multi-post.
>
> John
> "lwoods" <larry@.lwoods.com> wrote in message
> news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>

I can't view the list of databases on remote site

I have been asked to work on a remote site. I started my EM and tried to
connect to the SQL server on the remote site. I connected just fine (server
had userid/password). But when I open the "Databases" folder I get a "(no
items)" instead of a list of databases. BUT, when I view other folders I
see various items. For example, Security/Login lists lots of login entries.
Just to see what would happen I right-clicked on Databases and clicked All
Tasks/Backup Database and got a list of databases that I was NOT allowed to
backup! But, still no list of databases.
Any ideas?
BTW, I am using SQL Server 2000 EM.
TIA,
Larry Woods
Hi,
Could you refresh the database option and see.
As well as login to Query analyzer and execute the below command to get the
databases:-
SP_HELPDB
For backing up try
BACKUP Database <dbanme> to disk='d:\backup\dbname_Oct16.bak' with
INIT,STATS=5
If you get a permission issue contact the database admin.
Thanks
Hari
SQL Server MVP
"lwoods" <larry@.lwoods.com> wrote in message
news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>I have been asked to work on a remote site. I started my EM and tried to
>connect to the SQL server on the remote site. I connected just fine
>(server had userid/password). But when I open the "Databases" folder I get
>a "(no items)" instead of a list of databases. BUT, when I view other
>folders I see various items. For example, Security/Login lists lots of
>login entries. Just to see what would happen I right-clicked on Databases
>and clicked All Tasks/Backup Database and got a list of databases that I
>was NOT allowed to backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>
|||Please try to connect usin Query Analyzer and execute the following
use master
go
select *
from information_schema.schemata
select *
from sysdatabases
This will enable you to display all databases on a server
Best Regards
Krystian Zieja / mob
"lwoods" wrote:

> I have been asked to work on a remote site. I started my EM and tried to
> connect to the SQL server on the remote site. I connected just fine (server
> had userid/password). But when I open the "Databases" folder I get a "(no
> items)" instead of a list of databases. BUT, when I view other folders I
> see various items. For example, Security/Login lists lots of login entries.
> Just to see what would happen I right-clicked on Databases and clicked All
> Tasks/Backup Database and got a list of databases that I was NOT allowed to
> backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>
>
|||Hi
See answer in .programming. Please do not multi-post.
John
"lwoods" <larry@.lwoods.com> wrote in message
news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>I have been asked to work on a remote site. I started my EM and tried to
>connect to the SQL server on the remote site. I connected just fine
>(server had userid/password). But when I open the "Databases" folder I get
>a "(no items)" instead of a list of databases. BUT, when I view other
>folders I see various items. For example, Security/Login lists lots of
>login entries. Just to see what would happen I right-clicked on Databases
>and clicked All Tasks/Backup Database and got a list of databases that I
>was NOT allowed to backup! But, still no list of databases.
> Any ideas?
>
> BTW, I am using SQL Server 2000 EM.
> TIA,
> Larry Woods
>
|||Will do...
Please note that I DON'T usually do this, but I needed an answer FAST so I
tried to connect to multiple groups. I understand the problem...
Larry
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23SZCJEk0FHA.2600@.TK2MSFTNGP10.phx.gbl...
> Hi
> See answer in .programming. Please do not multi-post.
>
> John
> "lwoods" <larry@.lwoods.com> wrote in message
> news:eG$Insd0FHA.2924@.TK2MSFTNGP15.phx.gbl...
>

I cannot view table content in MS SQL Server Express

I was able to view table content easily before, but after I
reinstalled everything, I cannot find the option to view table content
in MS SQL Server Express. I can define table with no problem. Check
out the screen snapshot below, from which you'll see that the popup
menu frrom right-clicking on the table name does not have the "Open
table" option. What is going on?

http://farm1.static.flickr.com/167/...839620d0b_o.pngIt seems you are not connected to an SQL Server Express Edition
server. Instead, you are connected to a SQL Server Compact Edition
server, which is a different thing.

Razvan|||On Mar 19, 1:55 am, "Razvan Socol" <rso...@.gmail.comwrote:

Quote:

Originally Posted by

It seems you are not connected to an SQL Server Express Edition
server. Instead, you are connected to a SQL Server Compact Edition
server, which is a different thing.
>
Razvan

Yes, yes, you are very right. I also notice that. But how come? I
did download the package from the SQL Server Express website. It is
the one which says "Microsoft SQL Server 2005 Express Edition with
Advanced Services". The installation file has a name SQLEXPR_ADV.EXE,
which is 256M.

Is this the right one? Well, at installation, I did custom install
and made all components available from the local machine.

Pls give me hint, how do I properly install the Express Edition
instead of the compact Edition?

Thanks.|||I guess that both SQL Express and SQL Compact are installed now. It's
only a matter of connecting to the desired one. When the "Connect to
Server" dialog appears in Management Studio Express, make sure you
choose "Database Engine" in the "Server type" combo (instead of "SQL
Server Compact Edition").

Razvan|||On Mar 19, 12:07 pm, "Razvan Socol" <rso...@.gmail.comwrote:

Quote:

Originally Posted by

I guess that both SQL Express and SQL Compact are installed now. It's
only a matter of connecting to the desired one. When the "Connect to
Server" dialog appears in Management Studio Express, make sure you
choose "Database Engine" in the "Server type" combo (instead of "SQL
Server Compact Edition").
>
Razvan

You are right again. Yes, both "Database Engine" and "Sql Server
Compact Edition" show up in the combo. The problem is that I cannot
connect to .\SQLEXPRESS with Database Engine selected, using Windows
Authentication.|||[...] I cannot connect to .\SQLEXPRESS with Database Engine selected,

Quote:

Originally Posted by

using Windows Authentication.

What error message do you get (below "Cannot connect to .
\SQLEXPRESS") ?
a) "An error has occurred while establishing a connection to the
server [...] Error Locating Server/Instance Specified"
b) "Login failed for user 'COMPUTERNAME\username'"

I guess that a). In this case, go to Start / Programs / SQL Server
2005 / Configuration Tools / SQL Server Configuration Manager. Under
"SQL Server 2005 Services" do you have any entry with the "SQL Server"
as the "Service Type" ?

If yes, try to connect to the instance name specified there (if it's
the default instance, just try to connect to ".").

If no, then the Database Engine seems to be not installed; when you
reinstall SQL Server, make sure that "Data files" and "Shared tools"
under "Database Services" are selected. Then remember what choice you
make in the Instance Name screen.

Razvan|||On Mar 19, 3:44 pm, "Razvan Socol" <rso...@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

[...] I cannot connect to .\SQLEXPRESS with Database Engine selected,
using Windows Authentication.

>
What error message do you get (below "Cannot connect to .
\SQLEXPRESS") ?
a) "An error has occurred while establishing a connection to the
server [...] Error Locating Server/Instance Specified"
b) "Login failed for user 'COMPUTERNAME\username'"
>
I guess that a). In this case, go to Start / Programs / SQL Server
2005 / Configuration Tools / SQL Server Configuration Manager. Under
"SQL Server 2005 Services" do you have any entry with the "SQL Server"
as the "Service Type" ?
>
If yes, try to connect to the instance name specified there (if it's
the default instance, just try to connect to ".").
>
If no, then the Database Engine seems to be not installed; when you
reinstall SQL Server, make sure that "Data files" and "Shared tools"
under "Database Services" are selected. Then remember what choice you
make in the Instance Name screen.
>
Razvan

Hey, Razvan, you really rock!

Exactly that was the problem. I checked the configuration manager.
It looks like that I don't have SQLEXPRESS. The instance name seems
to be MSSQLSERVER.

My computer name is EMACHINEXPPRO and I can connect to the SQL Server
by typing in this computer name as the Database Engine or like you
said, simply put a dot (.) instead.

Connecting to MSSQLSERVER or .\MSSQLSERVER results in error (error
code 53 or error code 87). See the screen snapshot below.

http://farm1.static.flickr.com/175/...8f5145e32_o.png
Good that I can connect to it using my computer name now. Many
thanks.

AL

I can't get this view faster

Hey guys I can't get this view to be faster. It is causing me serious problems. I am trying to get all this data gathered to return to the client but I can't make it any faster. My Low return time is 24 seconds and my High return time is 77 Seconds. But the it will take 24 Seconds even if no data is returned.

What can i do?

I have indexed every foreign key.

i have pictures of my execution path that you can look at at the following address

http://salessimplicity.dnsalias.com/ssnet/needhelp/needhelp.htm

If you need anything to help me let me know I am more than willing to help to get this thing going faster..

The Main view is below

Code Snippet

create VIEW dbo.vuFollowUpTriggerAll AS
SELECT
Client.ID,
SubdivFU.ID AS FollowUpSubdivID,
FollowUpDone.ID AS FollowUpID,
Case
When SubdivFU.DeptID is Not Null then Agent1.ID
Else Agent2.ID
END As SalesAgentID,
-- Agent2.ID AS SalesAgentID,
Subdiv.ID AS SubdivisionID,
Subdiv.MarketingName AS SubdivisionName,
Buyer.FirstName,
Buyer.LastName,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As SalesAgentFirst,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As SalesAgentLast,
-- Agent2.FirstName AS SalesAgentFirst,
-- Agent2.LastName AS SalesAgentLast,
FollowUpDone.DateItemEntered,
FollowUpDone.DateItemComplete AS DateComplete,
FollowUpDone.Custom,
--Removed ClientStatus.Status - didn't see it used in code.
SubdivFU.ContactDesc AS TaskDesc,
FollowUpDone.Removed,
--For Contingencies and Deposit Reminders, we subtract the days rather then add them.
DATEADD(Day, TE.Multiplier * ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate) AS DueDate,
ClientDate.DepositID,
SubdivFU.ContactType AS TaskType,
SubdivFU.MasterReportID,
SubdivFU.EmailLetterID,
Buyer.Email,
Buyer.DoNotSendEmail,
Buyer.DoNotSendLetter,
SubdivFU.OverrideOptOut,
FollowUpDone.UserID,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As AssignedToFirstName,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As AssignedToLastName
FROM
dbo.tbSubdivision Subdiv
INNER JOIN dbo.tbSubdivisionFollowUp SubdivFU ON SubdivFU.SubdivisionID = Subdiv.ID
-- tbTrigger Event is the table that helps us put this whole spaghetti together
--it joins the Event type to the Date Type to the Multiplier.
INNER JOIN dbo.tbTriggerEvent TE ON TE.ID = SubdivFU.TriggerEvent
--tbDateType is what connects vuClient Dates the appropriate date field.
-- INNER JOIN dbo.tbDateType DT ON DT.ID = TE.DateTypeID
INNER JOIN dbo.tbClient Client ON Subdiv.ID = Client.SubdivisionID
AND ((SubdivFU.ClientRankID = Client.ClientRankID AND TE.UseRank = 1)
OR (TE.UseRank = 0))
--vuCLientDates replaces what we were going to use as tbClientDates but it is almost as fast to generate this on the fly
--as it is to generate it on the fly.
INNER JOIN dbo.vuClientDates ClientDate ON ClientDate.DateTypeID = TE.DateTypeID and Client.ID = ClientDate.ClientID
INNER JOIN dbo.tbMember Member ON Member.ClientID = Client.ID
AND Member.MemberTypeID = 1 --Buyer Type
INNER JOIN dbo.tbIndividual Buyer ON Member.IndividualID = Buyer.ID
--vuFollowUpTriggerGetAgent is a view we use to connect Clients to their Agents in each Department.
Left Outer Join vuFollowUpTriggerGetAgent GetAgent on GetAgent.DeptID = SubdivFU.DeptID and Client.ID = GetAgent.ClientID and GetAgent.SubdivisionID = Subdiv.ID
Left Outer JOIN dbo.tbUser Agent1 ON Agent1.ID = GetAgent.UserID
Inner JOIN dbo.tbUser Agent2 ON Agent2.ID = Client.UserID
LEFT OUTER JOIN dbo.tbFollowUp FollowUpDone ON FollowUpDone.SubdivFollowUpID = SubdivFU.ID
AND FollowUpDone.ClientID = Client.ID
AND isNull(FollowUpDone.DepositID, 0) = isNull(ClientDate.DepositID, 0)
WHERE (FollowUpDone.DateItemComplete Is Null)
AND (isnull(FollowUpDone.Removed,0) = 0)
AND (SubdivFU.Active = 1)
AND ((SubdivFU.TriggerEvent = 'R' AND DATEDIFF(DAY, ClientDate.EventDate, GETDATE()) < 0 )
OR (SubdivFU.TriggerEvent = 'O' AND (FLOOR(CAST(GETDATE() AS float)) >= DATEADD(Day, ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate)) )
OR (SubdivFU.TriggerEvent <> 'R' and SubdivFU.TriggerEvent <> 'O')
)
AND (SubdivFU.OverrideOptOut = 1
OR ((SubdivFU.ContactType = 'O')
OR (SubdivFU.ContactType = 'L' AND Buyer.DoNotSendLetter = 0)
OR (SubdivFU.ContactType = 'E' AND Buyer.DoNotSendEmail = 0)
OR (SubdivFU.ContactType = 'C' AND Buyer.DoNotCall = 0)))
AND (((SubdivFU.DeptID IS Not Null) and (Agent1.ID is Not Null)) or (SubdivFU.DeptID is Null) )

It joins to a few other views defined below

Code Snippet

ALTER View dbo.vuFollowUpTriggerGetAgent AS
-- 5/14/07 DebP Added link to tbDept in order to exclude inactive departments.

SELECT SFU.DeptID, tbClient.ID AS ClientID,
ISNULL(tbClientDept.UserID, ISNULL(tbSubdivisionDept.UserID, Null)) AS UserID,
tbSubdivision.ID as SubdivisionID
FROM tbSubdivision
INNER JOIN dbo.tbClient ON tbClient.SubdivisionID = tbSubdivision.ID
INNER Join
(SELECT SubdivisionID, DeptID
FROM dbo.tbSubdivisionFollowUp
INNER JOIN dbo.tbDept ON dbo.tbSubdivisionFollowUp.DeptID = dbo.tbDept.ID
AND dbo.tbDept.Active <> 0
GROUP BY SubdivisionID, DeptID)
SFU ON SFU.SubdivisionID = tbSubdivision.ID
-- The following is no longer needed since we're doing an inner join to tbDept.
--AND SFU.DeptID is Not Null
LEFT OUTER JOIN dbo.tbSubdivisionDept ON tbSubdivisionDept.DeptID = SFU.DeptID
AND tbSubdivisionDept.SubdivisionID = tbSubdivision.ID
AND tbSubdivisionDept.UserID is not Null
LEFT OUTER JOIN dbo.tbClientDept ON tbClientDept.DeptID = SFU.DeptID
AND tbClient.ID = tbClientDept.ClientID
AND tbClientDept.UserID is not Null

and

Code Snippet

ALTER view dbo.vuClientDates as

SELECT tbClient.ID as ClientID,
Case
When tbClient.ClientStatusID = 4 then 1
When tbClient.ClientStatusID = 1 then 2
When tbClient.ClientStatusID = 2 then 4
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then 11
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then 5
End as DateTypeID,

Case
When tbClient.ClientStatusID = 4 then VisitDate
When tbClient.ClientStatusID = 1 then ContractDate
When tbClient.ClientStatusID = 2 then ActualCloseDate
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then VisitDate
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then CancellationDate
End
as EventDate,
Null as DepositID, Null as StageID, Null as EscrowStageID From tbClient

UNION ALL

SELECT tbClient.ID as ClientID, 3 as DateTypeID, ExpirationDate as EventDate, Null as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
where (tbClient.Contingency = 1)
AND (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in ( 1, 2, 4)
)

UNION ALL

SELECT tbClient.ID as ClientID, 6 as DateTypeID, DateDue as EventDate, Deposits.ID as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
INNER JOIN dbo.tbDeposits Deposits ON Deposits.ClientID = tbClient.ID
AND Deposits.DateDue IS NOT NULL
AND Deposits.DatePaid IS NULL
Where (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in( 1,2,4)
)

These are the indexes i have added for this

Code Snippet

CREATE INDEX [IX_tbIndividual] on [dbo].[tbIndividual] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient] on [dbo].[tbClient] ([ClientStatusID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_1] on [dbo].[tbClient] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_2] on [dbo].[tbClient] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember] on [dbo].[tbMember] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_1] on [dbo].[tbMember] ([IndividualID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_2] on [dbo].[tbMember] ([MemberTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbDeposits] on [dbo].[tbDeposits] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_2] on [dbo].[tbSubdivisionFollowUp] ([TriggerEvent]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp] on [dbo].[tbSubdivisionFollowUp] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_1] on [dbo].[tbSubdivisionFollowUp] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_3] on [dbo].[tbSubdivisionFollowUp] ([ContactType]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_2] on [dbo].[tbFollowUp] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp] on [dbo].[tbFollowUp] ([SubdivFollowUpID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_1] on [dbo].[tbFollowUp] ([MasterReportID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_3] on [dbo].[tbFollowUp] ([DepositID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionDept] on [dbo].[tbSubdivisionDept] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision] on [dbo].[tbUserSubdivision] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision_1] on [dbo].[tbUserSubdivision] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClientDept_1] on [dbo].[tbClientDept] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent] on [dbo].[tbTriggerEvent] ([DateTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent_1] on [dbo].[tbTriggerEvent] ([TriggerEventDesc]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([AvailablePlanOptionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([RoomPlanID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([Username]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_LoginID] on [dbo].[tbUser] ([LoginID]) WITH FILLFACTOR = 95

Hmm... Three table scans (at 25% of the execution plan each) seem to indicate you're missing appropriate indexes somewhere.|||Ok but where?
|||

You have a lot of joins in your query. Have you tried using temporary tables instead to store intermediate results and then join them ?

|||

Well, two of them are on the tbClient with CancelationDate and ClientStatusID, and one of those also looks at Contingency.

(Those are tables 2 and 3 with the 25% costs on the far right of the plan).

You're only indexing on ClientStatusID

You're also using a lot of functions in the filtering which will pop the plan back to table scans.

You may want to develop and "grow" this incrementally. Just do the first two tables and get them honed in, then add in the next table.

Get a good clean manageable baseline to grow from.

|||Also, to go along with Dale, you'll want to create indexes based on the composite key of the join. Don't just create an index for each column individually. Do as he says and "grow" the view. Each time you add something new, review the execution plan again to ensure nothing has snuck up on you.|||

These are all great ideas...

Thanks

|||How do i get rid of a hash match left?

I can not see my Tables !

Hello,

I am using " SqlDataSource " and I can not see Talbles of memberships ! I can not only see View or see manule tables !!!

for this when I am using any tool I can not use edit or add fetures :(

Where is problem ?

SqlDataSource is used to retrieve data from database and then be used as datasource of some other control such as GridView. Here is an example:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource(d=ide).aspx

To see database objects in VWD, you can open Server Explorer and add connection to some database on the server.

I asked this in Sql Express but hoping to get a different answer here.

Here is my goal please let me know if it is possible.

I have installed sql express on clients machines. I don't want them to be able to view the sp's or the functions. I would like to go as far as not allowing them to see the tables. I tried with encrption but this is still breakable by the user using the DAC.

Does CE

Support transactionscope?

What would I use to create tables and sp's for my sdf database? Server Managment Studio or something else?

Thanks

Stored procedures are not available in SQL CE.

The next version of CE (3.5) will support transactionscope on the desktop (not of Windows Mobile)

Create tables from SSMS, yes, or use the CREATE TABLE / CREATE INDEX statements (in SSMS Query window)

You may find this useful: http://blogs.msdn.com/stevelasker/archive/2007/03/31/creating-your-sql-server-compact-edition-database-and-schema-in-code.aspx

|||

Is there any whitepapers on performance of Sql CE compared to Sql Express?

Is there a way to use profiler to see whats going on in SQL CE?

can I see the database in SMS if it has a passowrd on it and has been encrypted?

Is there a conversion tool to convert from Express or 2005 to CE db?

Thanks

|||

You have a lot of questions!! :-)

1: Yes, there is some info here: http://msdn2.microsoft.com/en-us/library/bb380177.aspx

2: You can use Management Studio - Query analyzer with SQL CE, to see which indexes are used etc. Profiler is not available

3: Yes, you will be prompted for the password (having a password == being encrypted)

4: Yes, there are 3rd party tools, and a data migration wizard is in the works in the Visual Studio "Orcas" timeframe.

|||

Is there any reason that varchar is not supported?

Is there a plan to support this in the next version?

Thank you for your help this is really looking like it will work well for me.

|||

Do you have any links for the 3rd party tools for the data migration?

I think I know the answer is No but I will still ask.

Are scalar functions available in CE?

|||

I will try to answer more questions for you:

> Is there any reason that varchar is not supported?

I am not sure in understand this. nvarchar() is supported (as SQL CE uses only unicode, varchar is not), but you can always convert from varchar til nvarchar. I doubt there are any plans to support varchar in a future version.

> Do you have any links for the 3rd party tools for the data migration?

DataPort wizard from www.primeworks-mobile.com

> Are scalar functions available in CE?

Yes, some functions are, for a full list see http://msdn2.microsoft.com/en-us/library/ms174077.aspx

|||

Thank you for the answers that product worked great. One thing I noticted and wondering if I am missing something. Is there no way to create a clustered index in ce?

|||One more question is there a way to scrip the whole db.|||

Is there any Microsoft Application blocks for CE?

|||

if i want to run 2 selects in the same call to the db is this possible or would I have to do 2 calls to and get 2 different SQLCeResultSets back.

|||

I will again try to answer some of your questions - I you find my answers useful, please be kind and mark them as being so, thanks.

> One thing I noticted and wondering if I am missing something. Is there no way to create a clustered index in ce?

No, only Non-clustered indexes are supported - see http://msdn2.microsoft.com/en-us/library/ms345331.aspx

> One more question is there a way to scrip the whole db.

Do you mean scripting an existing SDF file. If so, not without 3rd party tools (see reply above). You may find this useful:

http://blogs.msdn.com/stevelasker/archive/2007/03/31/creating-your-sql-server-compact-edition-database-and-schema-in-code.aspx

> Is there any Microsoft Application blocks for CE?

Sure is - the "Mobile Client Software Factory" contains a SQL CE Data Access block: http://www.microsoft.com/downloads/details.aspx?familyid=F9176708-9F57-4C0F-97FB-F9C65A9BBF22&displaylang=en

> if i want to run 2 selects in the same call to the db is this possible or would I have to do 2 calls to and get 2 different SQLCeResultSets back.

You must make two calls, only on SQL stament is supported per command.

Hope you found my reply useful.

|||

Select count(distinct(column)) from table

Any way to get this to work or will I have to just select out distinct values and manully count them in C# when I return it out.

|||

How can I do a select like this in CE?

SELECT
col1
FROM
table1 as A
JOIN
(SELECT
col1
FROM
table2
) B
ON
A.col1 = B.col1

Thanks

|||Nested FROM queries are not supported by SQL CE. You will have to rephrase or use 2 calls. Nested queries will be available in the next version of SQL CE (3.5)

I am struggling through views

Hi there, it's me again. I am having trouble with a view again. I am
trying to do a calculation, but there are some checks that need to be
taken into consideration. Maybe a view is not the right way to deal
with this. I don't know.

This is the beginning of my query.

SELECT coalesce(f.filenumber, i.filenumber) as filenumber,
i.InvoiceNumber, i.InvoiceValue, il.lineid, MPF = .21 * (il.UnitCost *
il.UnitQty + il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt)

FROM tblFILE f inner join tblINVOICE i on (f.filenumber =
i.filenumber) left outer join tblINVOICE_LINE il on (i.Invoiceid =
il.invoiceid)

This works just as it should. However, if the Sum of all MPFs per
file total less than 25.00 or more than 485.00 then each MPF has to be
recalculated as:

Percentage of TotalEnteredValue = (InvoiceValue / (il.UnitCost *
il.UnitQty + il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt)

Percentage of TotalEnteredValue * 25.00 = MPF or
Percentage of TotalEnteredValue * 485.00 = MPF

Can you do something like this in a View? Or do I need to do
something like a trigger?

I greatly appreciate all help. I am struggling to get a foothold on
views. I am getting there.Well, I am getting desperate. I must have explained myself poorly. I
apologize. I absolutely cannot figure out which approach would best
suit my need because I don't know about any. I am still a newbie. I
am pretty good at the basics of sql server 2000, but I am totally an
idiot when it comes to anything complicated or outside of what I know,
which is tables, a little stored procedure and some view stuff. I
have been looking on Books Online but still cannot figure out what my
best approach should be.

I have tables and a view...

CREATE TABLE tblFILE (
FileNumber bigint not null primary key
)

CREATE TABLE tblINVOICE(
InvoiceID bigint not null primary key
identity,
InvoiceNumber varchar(35) not null,
FileNumber bigint not null,

CONSTRAINT FK_tblFILE FOREIGN KEY (FileNumber) REFERENCES
tblFILE(FileNumber)
)

CREATE TABLE tblINVOICE_LINE (
LineID bigint not null primary key
identity,
InvoiceID bigint not null,
UnitCost money,
UnitQty money,
AddMMV money,
MinusMMV money,
MinusNDC money,
ErrorAmt money,

CONSTRAINT FK_tblINVOICE FOREIGN KEY (InvoiceID) REFERENCES
tblINVOICE(InvoiceID)

)

CREATE VIEW TEV_view AS (

SELECT i.FileNumber, COALESCE (i.InvoiceID, il.InvoiceID) AS
InvoiceID, i.InvoiceNumber, il.LineID, il.UnitQty, il.UnitCost,
il.AddMMV, il.MinusNDC, il.ErrorAmt, il.UnitQty * il.UnitCost +
il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt AS LineEV, .0021 *
(il.UnitQty * il.UnitCost + il.AddMMV - il.MinusMMV - il.MinusNDC +
il.ErrorAmt) AS MPFbyLine
FROM dbo.tblINVOICE i INNER JOIN
dbo.tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
)

So, my MPFbyLine is where my headache, confusion, frustration,
desperation is coming from. A file has many invoices...each invoice
has many lines. Each line has an MPFbyLine value. This is all fine
and dandy...except if the sum of all MPFbyLine values for a file is
less than 25.00 or more than 485.00.

If this is the case then the MPFbyLine for each line in that file must
be recalculated using a formula other than the (.0021 * LineEV).
Instead, the sum of all LineEVs for the file must be deteremined, and
then each LineEV for the file must be divided by the Sum of all to get
each line's percentage of value of the file. Then the new MPFbyLine
is calculated as the line's percentage of value * 25.00min or
485.00max to get its new value.

So, I figured I must need to use Transact SQL somehow, possibly in a
trigger. Or maybe i need a calculated field, which I have found
reference to. Either way I am stumped. I have been messing with the
below trigger, but I feel like I am on the wrong track. I am sorry if
this is longwinded...I really need help. I think I am better off
doing this in sql than programming it directly into by vb application.
I would really, really appreciate any help.

CREATE TRIGGER CalcMPF_trg ON [dbo].[TEV_view]
FOR INSERT, UPDATE, DELETE
AS

DECLARE @.Minmoney,
@.Max money,
@.TEVmoney,
@.TotMPF money,
@.PercentOfTEV float

Set @.Min = 25.00
Set @.Max = 485.00

Select SUM(MPFByLine), SUM(LineEV) from dbo.TEV_view
Group by Filenumber
Set @.TEV = SUM(LineEV)
Set @.TotMPF = SUM(MPFByLine)

If @.TotMPF < @.Min then
@.PercentOfTEV = @.TEV|||Without any DDL, all anyone can do is guess. And under reasonable
assumptions, the code looks bad. For example, why are you writing
"COALESCE(F.file_nbr, I.file_nbr) AS file_nbr" when you join the two
tables on (F.file_nbr = I.file_nbr) so neither can be NULL??

Why do you do a left outer join on invoices and invoice lines do you
have invoices without any lines? As an aside, I hope that your data
model is not so screwed up that you mimicked the PHYSICAL layout of
the paper order form and actually copied the lines from the form
instead of putting each product in a row in an "Invoice Details" or
"Invoice Items" table. But with things like "line_id", it certainly
looks like it!

Think about the same item appearing in multiple PHYSICAL lines of a
paper form and all the screws up it causes.

Why do you have both an "invoice_nbr" and an "invoiceid"? Never use
two names for one data element.

Why do you have "add_mmv" and "minus_mmv", but no plain, simple, mmv?
Why is "unit_qty" not plain old "qty_ordered"? In fact try to define
what "unit_qty" means. Data element names like this make no sense;
please read the ISO-11179 rules.

Please tell me that "I.invoice_value" is not a computed value, like
the total of the items, mixed into the same level of aggregation as
its components.

You mix Standard SQL and proprietary code, use silly prefixes, and
singular names for tables, etc. in short, this needs some clean up
so I made few guesses

Since a change to any order could cause you to re-calculate
everything, put this in a VIEW and then filter the view when you use
it. First calculate everything you will need let the optimizer
worry about factoring sub-expressions.

CREATE VIEW Foobar
(file_nbr, invoice_nbr, invoice_value, line_id, normal_mpf, low_mpf,
high_mpf)
AS
SELECT F.file_nbr, I.invoice_nbr, I.invoice_value, L.line_id,
(0.21 * (L.unit_cost * L.qty_ordered + L.add_mmv - L.minus_mmv -
L.minus_ndc + L.error_amt) AS normal_mpf,

((I.invoice_value / (L.unit_cost *
L.unitqty + L.add_mmv - L.minus_mmv - L.minus_ndc + L.error_amt)
* 25.00) AS low_mpf,

((I.invoice_value / (L.unit_cost *
L.unitqty + L.add_mmv - L.minus_mv - L.minus_ndc + L.error_amt)
* 485.00) AS high_mpf

FROM Files AS F, Invoices AS I, Invoice_items AS L
WHERE F.file_nbr = I.file_nbr
AND I.invoice_nbr = L.invoice_nbr);

Now the view needs to do a self-reference, and you could make this
into a VIEW also.

SELECT file_nbr, invoice_nbr, invoice_value, line_id,
CASE WHEN (SELECT SUM(normal_mpf) FROM Foobar AS F1
WHERE F1.file_nbr = F0.file_nbr) < 25.00
THEN low_mpf
WHEN (SELECT SUM(normal_mpf) FROM Foobar AS F1
WHERE F1.file_nbr = F0.file_nbr) > 485.00
THEN high_mpf
ELSE normal_mpf END AS mpf
FROM Foobar AS F0;

Was that sufficiently brutal?|||[posted and mailed, please reply in news]

Rowan (phantomtoe@.yahoo.com) writes:
> So, my MPFbyLine is where my headache, confusion, frustration,
> desperation is coming from. A file has many invoices...each invoice
> has many lines. Each line has an MPFbyLine value. This is all fine
> and dandy...except if the sum of all MPFbyLine values for a file is
> less than 25.00 or more than 485.00.
> If this is the case then the MPFbyLine for each line in that file must
> be recalculated using a formula other than the (.0021 * LineEV).
> Instead, the sum of all LineEVs for the file must be deteremined, and
> then each LineEV for the file must be divided by the Sum of all to get
> each line's percentage of value of the file. Then the new MPFbyLine
> is calculated as the line's percentage of value * 25.00min or
> 485.00max to get its new value.

First, I am sorry that the answer you got from Joe Celko was so completely
inappropriate. He knows his SQL (which is the the same SQL that you and
I use), but his social capabilities is lacking. And apparently he has not
the slightest understanding that some people who post here have a fairly
weak experience of SQL.

Anyway, I looked at your problem, and I think I have a solution. I could
completely make out the exact rules when the total exceeded 485 or was
below 25. But hopefully this gives you some help to find out the rest
yourself.

You discuss the possibility of a computed column, and I have introduced
one. Not so much that it helps us to write the view, but only to make
it less verbose. So this is how tblINVOICE_LINE looks like:

CREATE TABLE tblINVOICE_LINE (
LineID bigint not null primary key identity,
InvoiceID bigint not null,
UnitCost money,
UnitQty money,
AddMMV money,
MinusMMV money,
MinusNDC money,
ErrorAmt money,
LineEV AS UnitQty * UnitCost + AddMMV - MinusMMV - MinusNDC + ErrorAmt,
CONSTRAINT FK_tblINVOICE FOREIGN KEY (InvoiceID) REFERENCES
tblINVOICE(InvoiceID)

)

(By the way, I recommend to always include explicit NULL and NOT NULL
indicators for all columns.)

And here is the view:

CREATE VIEW TEV_view AS
SELECT i.FileNumber, COALESCE (i.InvoiceID, il.InvoiceID) AS
InvoiceID, i.InvoiceNumber, il.LineID, il.UnitQty, il.UnitCost,
il.AddMMV, il.MinusNDC, il.ErrorAmt, il.LineEV,
MPFbyLine = CASE WHEN t.totalEV <= 25 THEN il.LineEV / t.totalEV * 25
WHEN t.totalEV >= 485 THEN il.LineEV / t.totalEV * 480
ELSE 0.0021 * il.LineEV
END
FROM tblINVOICE i
JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
JOIN (SELECT i.FileNumber, totalEV = SUM(il.LineEV)
FROM tblINVOICE i
JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
GROUP BY i.FileNumber) AS t ON i.FileNumber = t.FileNumber

I've added a derived table which gives you the totalEV per file.
Derived tables is a very powerful feature in SQL. You can see them
as temporary tables within the query. Logically that is. They are
never materialized, and the optimizer may perform shortcuts with
the rest of the query as long as the result is correct.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you very much, Erland. Your answer here and in previous posts
have been greatly appreciated.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93E0E4AED352Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Rowan (phantomtoe@.yahoo.com) writes:
> > So, my MPFbyLine is where my headache, confusion, frustration,
> > desperation is coming from. A file has many invoices...each invoice
> > has many lines. Each line has an MPFbyLine value. This is all fine
> > and dandy...except if the sum of all MPFbyLine values for a file is
> > less than 25.00 or more than 485.00.
> > If this is the case then the MPFbyLine for each line in that file must
> > be recalculated using a formula other than the (.0021 * LineEV).
> > Instead, the sum of all LineEVs for the file must be deteremined, and
> > then each LineEV for the file must be divided by the Sum of all to get
> > each line's percentage of value of the file. Then the new MPFbyLine
> > is calculated as the line's percentage of value * 25.00min or
> > 485.00max to get its new value.
> First, I am sorry that the answer you got from Joe Celko was so completely
> inappropriate. He knows his SQL (which is the the same SQL that you and
> I use), but his social capabilities is lacking. And apparently he has not
> the slightest understanding that some people who post here have a fairly
> weak experience of SQL.
> Anyway, I looked at your problem, and I think I have a solution. I could
> completely make out the exact rules when the total exceeded 485 or was
> below 25. But hopefully this gives you some help to find out the rest
> yourself.
> You discuss the possibility of a computed column, and I have introduced
> one. Not so much that it helps us to write the view, but only to make
> it less verbose. So this is how tblINVOICE_LINE looks like:
> CREATE TABLE tblINVOICE_LINE (
> LineID bigint not null primary key identity,
> InvoiceID bigint not null,
> UnitCost money,
> UnitQty money,
> AddMMV money,
> MinusMMV money,
> MinusNDC money,
> ErrorAmt money,
> LineEV AS UnitQty * UnitCost + AddMMV - MinusMMV - MinusNDC + ErrorAmt,
> CONSTRAINT FK_tblINVOICE FOREIGN KEY (InvoiceID) REFERENCES
> tblINVOICE(InvoiceID)
> )
> (By the way, I recommend to always include explicit NULL and NOT NULL
> indicators for all columns.)
> And here is the view:
> CREATE VIEW TEV_view AS
> SELECT i.FileNumber, COALESCE (i.InvoiceID, il.InvoiceID) AS
> InvoiceID, i.InvoiceNumber, il.LineID, il.UnitQty, il.UnitCost,
> il.AddMMV, il.MinusNDC, il.ErrorAmt, il.LineEV,
> MPFbyLine = CASE WHEN t.totalEV <= 25 THEN il.LineEV / t.totalEV * 25
> WHEN t.totalEV >= 485 THEN il.LineEV / t.totalEV * 480
> ELSE 0.0021 * il.LineEV
> END
> FROM tblINVOICE i
> JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
> JOIN (SELECT i.FileNumber, totalEV = SUM(il.LineEV)
> FROM tblINVOICE i
> JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
> GROUP BY i.FileNumber) AS t ON i.FileNumber = t.FileNumber
> I've added a derived table which gives you the totalEV per file.
> Derived tables is a very powerful feature in SQL. You can see them
> as temporary tables within the query. Logically that is. They are
> never materialized, and the optimizer may perform shortcuts with
> the rest of the query as long as the result is correct.

I am not able to view RS folders and reports.

I created reports and deployed but i am not able to see on IE my RS folders
I used this path and also i used instead of localhost i used my machine name;
http://localhost/reports$SQL2005/Pages/Folder.aspx
I checked IIS and RS, it's running.
Also i stoped and start again.
It still not working.
What else should i do?
But it did work couple days ago.
Please help.
Thanks.On Jul 26, 11:44 am, GGill <GG...@.discussions.microsoft.com> wrote:
> I created reports and deployed but i am not able to see on IE my RS folders
> I used this path and also i used instead of localhost i used my machine name;
> http://localhost/reports$SQL2005/Pages/Folder.aspx
> I checked IIS and RS, it's running.
> Also i stoped and start again.
> It still not working.
> What else should i do?
> But it did work couple days ago.
> Please help.
> Thanks.
The only other thing I can think of are to stop and restart the SQL
Server service (sqlserver.exe) and the RPC service (remote procedure
call). If this doesn't work, you may need to reinstall SSRS. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant

I a view in Access Project I see only "all columns"

I try to create a view in Access Project but in design view I only see "all columns" in a table. The individual columns are not listed. This happens only with a certain SQL Server database. Any suggestions ?See: http://support.microsoft.com/kb/255041