Extracting Sql: working

Showing posts with label working. Show all posts

Thursday, March 29, 2012

Failed to open a rowset Warning/Error

Hi there

I am working in CR 9.0 and have included subreports in the main report.

Whenever I run the main report and have to give the Login ID & password for the " HiT ODBC / 400 Driver" (DataSource - AS400) I get the following warning/error.

"Failed to open a rowset.
Details: HY000:{HiT][HiT ODBC/400][SQL/400][ODBS Error][SQL0128] Use of NULL is not valid"

If I click the OK button the report is generated.

If I run the same report the second/third time - where I do not have to specify the Login ID & password the error/warning does not occur.

How do I get rid of this warning/error msg.

rkpYou need to pass them in the Front End coding

Tuesday, March 27, 2012

Failed to notify 'Operator' via email

32-bit SQL 2005 sp2 jobs recieve this error when trying to send an email to an Operator. Database Mail is setup and is working for all SSIS jobs where the email is sent through code or Send email task but simply trying to notify on job completion will not work even though the Agent is running & configured to use Database Mail profile with proper permissions.

Make sure sql agent configured properly and restart sql agent after mail configuration and test the mail from agent..

Make sure operator email is correct...and check if there are any other errors in mail log.

|||Enable DatabaseMail on the SQL Server Agent -> properties -> Alert System (check the checkbox), and then restart SQL Server Agent.

Failed to get the call stack! error

I'm working with a team on a migration of a database from Sybase to SQL
Server 2000.
Currently I'm in the process of debugging a sequence of stored procedure
calls wich are relatively long and nested.
In the process of returning from the stored procedures, after the job done
in the lowest level of calls (within transactions) , I get the message:
Server: Msg 3701, Level 11, State 5, Procedure PCRMQDD00vInt, Line 771
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table
'#TRMCQDL0', because it does not exist in the system catalog.
Failed to get the call stack!
the temporary table was created at that stored procedure before some
subsequent calls. I just want to make sure I got the message right. Does thi
s
message mean the processe run out of memory? Is there a way to parametrize
the use of more memory or we need more RAM ?
thanks in advance
Jo?o CostaHi
You may be seeing this sort of issue (although this KB talks about 6.5!)
http://support.microsoft.com/defaul...kb;en-us;157570
http://support.microsoft.com/defaul...kb;en-us;827448
You may want to look at blocking [url]http://support.microsoft.com/kb/271509/EN-US/[/ur
l]
and possibly reducing the number of temporary tables you are using (possibly
using derived tables!)
John
"Jo?o Costa" wrote:

> I'm working with a team on a migration of a database from Sybase to SQL
> Server 2000.
> Currently I'm in the process of debugging a sequence of stored procedure
> calls wich are relatively long and nested.
> In the process of returning from the stored procedures, after the job done
> in the lowest level of calls (within transactions) , I get the message:
> Server: Msg 3701, Level 11, State 5, Procedure PCRMQDD00vInt, Line 771
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table
> '#TRMCQDL0', because it does not exist in the system catalog.
> Failed to get the call stack!
>
> the temporary table was created at that stored procedure before some
> subsequent calls. I just want to make sure I got the message right. Does t
his
> message mean the processe run out of memory? Is there a way to parametrize
> the use of more memory or we need more RAM ?
> thanks in advance
> Jo?o Costa

Failed to get distributed transaction startet

Hi
I'm trying to get distributed transactions working between 2 machines within
2 different windows domains.
I can't get around following error:
Server: Msg 7391, Level 16, State 1, Line 1
OLE DB provider "SQLNCLI" for linked server <our server> returned message
"No transaction is active.".
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server <our server> was unable to begin a distributed transaction.
That the environment:
- server A is Win2k3 + SP 1
- server B is Win2k3 + SP 1 (within other windows domain; connection through
VPN)
- both servers running SQL 2005 + SP1
- Distributed Transaction Coordinator service is enabled on both servers
(security set to: 'No authentication required')
- The services are running with 'Log On As' Network Service
- windows firewall is disabled on both servers
- all ports are opened (bidirectional) between both domains
- @.@.servername is set correctly on both servers
That's what I try from server A:
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
but
begin tran
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
commit tran
When running this query from another ServerC within the same domain of
ServerB everything works fine. So, I guess there's some limitations across
the domains ?!
Do you have any further suggestions ?
Many thanks in advance
MichaelHi Michael
Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
John
"micpauls" wrote:
> Hi
> I'm trying to get distributed transactions working between 2 machines within
> 2 different windows domains.
> I can't get around following error:
>
> Server: Msg 7391, Level 16, State 1, Line 1
> OLE DB provider "SQLNCLI" for linked server <our server> returned message
> "No transaction is active.".
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server <our server> was unable to begin a distributed transaction.
> That the environment:
> - server A is Win2k3 + SP 1
> - server B is Win2k3 + SP 1 (within other windows domain; connection through
> VPN)
> - both servers running SQL 2005 + SP1
> - Distributed Transaction Coordinator service is enabled on both servers
> (security set to: 'No authentication required')
> - The services are running with 'Log On As' Network Service
> - windows firewall is disabled on both servers
> - all ports are opened (bidirectional) between both domains
> - @.@.servername is set correctly on both servers
>
> That's what I try from server A:
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> but
> begin tran
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> commit tran
> When running this query from another ServerC within the same domain of
> ServerB everything works fine. So, I guess there's some limitations across
> the domains ?!
> Do you have any further suggestions ?
>
> Many thanks in advance
> Michael|||Yes, I already tried, but without success. I still get the same error message.
"John Bell" wrote:
> Hi Michael
> Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> John
> "micpauls" wrote:
> > Hi
> >
> > I'm trying to get distributed transactions working between 2 machines within
> > 2 different windows domains.
> >
> > I can't get around following error:
> >
> >
> > Server: Msg 7391, Level 16, State 1, Line 1
> > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > "No transaction is active.".
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server <our server> was unable to begin a distributed transaction.
> >
> > That the environment:
> > - server A is Win2k3 + SP 1
> > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > VPN)
> > - both servers running SQL 2005 + SP1
> > - Distributed Transaction Coordinator service is enabled on both servers
> > (security set to: 'No authentication required')
> > - The services are running with 'Log On As' Network Service
> > - windows firewall is disabled on both servers
> > - all ports are opened (bidirectional) between both domains
> > - @.@.servername is set correctly on both servers
> >
> >
> > That's what I try from server A:
> >
> > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> >
> > but
> >
> > begin tran
> > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > commit tran
> >
> > When running this query from another ServerC within the same domain of
> > ServerB everything works fine. So, I guess there's some limitations across
> > the domains ?!
> >
> > Do you have any further suggestions ?
> >
> >
> > Many thanks in advance
> > Michael|||Hi
Have you checked out http://support.microsoft.com/kb/839279? I would also
try changing the service to be a domain account.
John
"micpauls" wrote:
> Yes, I already tried, but without success. I still get the same error message.
> "John Bell" wrote:
> > Hi Michael
> >
> > Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> >
> > John
> >
> > "micpauls" wrote:
> >
> > > Hi
> > >
> > > I'm trying to get distributed transactions working between 2 machines within
> > > 2 different windows domains.
> > >
> > > I can't get around following error:
> > >
> > >
> > > Server: Msg 7391, Level 16, State 1, Line 1
> > > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > > "No transaction is active.".
> > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > linked server <our server> was unable to begin a distributed transaction.
> > >
> > > That the environment:
> > > - server A is Win2k3 + SP 1
> > > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > > VPN)
> > > - both servers running SQL 2005 + SP1
> > > - Distributed Transaction Coordinator service is enabled on both servers
> > > (security set to: 'No authentication required')
> > > - The services are running with 'Log On As' Network Service
> > > - windows firewall is disabled on both servers
> > > - all ports are opened (bidirectional) between both domains
> > > - @.@.servername is set correctly on both servers
> > >
> > >
> > > That's what I try from server A:
> > >
> > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> > >
> > > but
> > >
> > > begin tran
> > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > > commit tran
> > >
> > > When running this query from another ServerC within the same domain of
> > > ServerB everything works fine. So, I guess there's some limitations across
> > > the domains ?!
> > >
> > > Do you have any further suggestions ?
> > >
> > >
> > > Many thanks in advance
> > > Michael|||Hi
windows firewall is completely disabled on both servers.
Security settings for MSDTC were already set apropriate to
"http://support.microsoft.com/kb/839279?" on both servers.
I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
find this account on serverA because this is a domain controller. How can I
do this ? Should I try changing the account for "Distributed Transaction
Coordinator" service to some other domain admin account ?
Michael
"John Bell" wrote:
> Hi
> Have you checked out http://support.microsoft.com/kb/839279? I would also
> try changing the service to be a domain account.
> John
> "micpauls" wrote:
> > Yes, I already tried, but without success. I still get the same error message.
> >
> > "John Bell" wrote:
> >
> > > Hi Michael
> > >
> > > Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> > >
> > > John
> > >
> > > "micpauls" wrote:
> > >
> > > > Hi
> > > >
> > > > I'm trying to get distributed transactions working between 2 machines within
> > > > 2 different windows domains.
> > > >
> > > > I can't get around following error:
> > > >
> > > >
> > > > Server: Msg 7391, Level 16, State 1, Line 1
> > > > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > > > "No transaction is active.".
> > > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > > linked server <our server> was unable to begin a distributed transaction.
> > > >
> > > > That the environment:
> > > > - server A is Win2k3 + SP 1
> > > > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > > > VPN)
> > > > - both servers running SQL 2005 + SP1
> > > > - Distributed Transaction Coordinator service is enabled on both servers
> > > > (security set to: 'No authentication required')
> > > > - The services are running with 'Log On As' Network Service
> > > > - windows firewall is disabled on both servers
> > > > - all ports are opened (bidirectional) between both domains
> > > > - @.@.servername is set correctly on both servers
> > > >
> > > >
> > > > That's what I try from server A:
> > > >
> > > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> > > >
> > > > but
> > > >
> > > > begin tran
> > > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > > > commit tran
> > > >
> > > > When running this query from another ServerC within the same domain of
> > > > ServerB everything works fine. So, I guess there's some limitations across
> > > > the domains ?!
> > > >
> > > > Do you have any further suggestions ?
> > > >
> > > >
> > > > Many thanks in advance
> > > > Michael|||micpauls wrote:
> Hi
> windows firewall is completely disabled on both servers.
> Security settings for MSDTC were already set apropriate to
> "http://support.microsoft.com/kb/839279?" on both servers.
> I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
> find this account on serverA because this is a domain controller. How can I
> do this ? Should I try changing the account for "Distributed Transaction
> Coordinator" service to some other domain admin account ?
>
You shouldn't run SQL under the NetworkService context. Create a real
domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
use that domain user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, for your help. I already run SQL services using a separate domain
(admin) account. Yesterday the problem has been resolved by adding
appropriate hostname/IP address entries to the 'hosts' file and the 'lmhosts'
file, to enable network transport services to resolve the 2 server hostnames
accross the 2 different domains. Adding linked servers by IP address wasn't
sufficient. RPC in/out options within the linked servers properties must by
enabled.
"Tracy McKibben" wrote:
> micpauls wrote:
> > Hi
> >
> > windows firewall is completely disabled on both servers.
> > Security settings for MSDTC were already set apropriate to
> > "http://support.microsoft.com/kb/839279?" on both servers.
> >
> > I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
> > find this account on serverA because this is a domain controller. How can I
> > do this ? Should I try changing the account for "Distributed Transaction
> > Coordinator" service to some other domain admin account ?
> >
> You shouldn't run SQL under the NetworkService context. Create a real
> domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
> use that domain user.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Failed to get distributed transaction startet

Wednesday, March 21, 2012

failed to connect to SQL Server's database!

Hi,
I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
The only thing and the most important that should be working but not, is
application that uses SQL Server as it's main database. Before this, the
application used SQL Server 2000 and working fine.
Everytime users launch the application, there's this error message: "Error
in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user 'sa'."
Although I've allowed Windows Authentication & SQL Server authentication in
SQL Server 2005 logins settings, that error message will still appears and
restrict any users to log-in to the application.
That application uses a "DataAccessLayer.config" file that has this
parameters:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="UseWebService" value="false"/>
<add key="DAL.DataService.DataService" value="http://companyweb"/>
<add key="DBCnnString" value="Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
<add key="server" value="ServerName"/>
<add key="database" value="INVENTORY"/>
<add key="uid" value="sa"/>
<add key="password" value=""/>
</appSettings>
</configuration>
What happened? Have anyone encountered the same symtomps/problem and know
how to work around it?
Please be noted, I attach this "INVENTORY" database into
ServerName\Database; I do not use instances for it.
Thank you, all help is appreciated.ON the Security tab, change to [SQL Server and Windows Authentication].
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dhow" <Dhow@.discussions.microsoft.com> wrote in message
news:9114C9D0-F787-41A2-B2A1-9B84FA635F1B@.microsoft.com...
> Hi,
> I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> The only thing and the most important that should be working but not, is
> application that uses SQL Server as it's main database. Before this, the
> application used SQL Server 2000 and working fine.
> Everytime users launch the application, there's this error message: "Error
> in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user
> 'sa'."
> Although I've allowed Windows Authentication & SQL Server authentication
> in
> SQL Server 2005 logins settings, that error message will still appears and
> restrict any users to log-in to the application.
> That application uses a "DataAccessLayer.config" file that has this
> parameters:
> <?xml version="1.0" encoding="utf-8"?>
> <configuration>
> <appSettings>
> <add key="UseWebService" value="false"/>
> <add key="DAL.DataService.DataService" value="http://companyweb"/>
> <add key="DBCnnString" value="Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> <add key="server" value="ServerName"/>
> <add key="database" value="INVENTORY"/>
> <add key="uid" value="sa"/>
> <add key="password" value=""/>
> </appSettings>
> </configuration>
> What happened? Have anyone encountered the same symtomps/problem and know
> how to work around it?
> Please be noted, I attach this "INVENTORY" database into
> ServerName\Database; I do not use instances for it.
> Thank you, all help is appreciated.|||And did you really install SQL 2005 without a password for the [sa] account?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dhow" <Dhow@.discussions.microsoft.com> wrote in message
news:9114C9D0-F787-41A2-B2A1-9B84FA635F1B@.microsoft.com...
> Hi,
> I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> The only thing and the most important that should be working but not, is
> application that uses SQL Server as it's main database. Before this, the
> application used SQL Server 2000 and working fine.
> Everytime users launch the application, there's this error message: "Error
> in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user
> 'sa'."
> Although I've allowed Windows Authentication & SQL Server authentication
> in
> SQL Server 2005 logins settings, that error message will still appears and
> restrict any users to log-in to the application.
> That application uses a "DataAccessLayer.config" file that has this
> parameters:
> <?xml version="1.0" encoding="utf-8"?>
> <configuration>
> <appSettings>
> <add key="UseWebService" value="false"/>
> <add key="DAL.DataService.DataService" value="http://companyweb"/>
> <add key="DBCnnString" value="Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> <add key="server" value="ServerName"/>
> <add key="database" value="INVENTORY"/>
> <add key="uid" value="sa"/>
> <add key="password" value=""/>
> </appSettings>
> </configuration>
> What happened? Have anyone encountered the same symtomps/problem and know
> how to work around it?
> Please be noted, I attach this "INVENTORY" database into
> ServerName\Database; I do not use instances for it.
> Thank you, all help is appreciated.|||Dhow wrote:
> Hi,
> I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> The only thing and the most important that should be working but not, is
> application that uses SQL Server as it's main database. Before this, the
> application used SQL Server 2000 and working fine.
> Everytime users launch the application, there's this error message: "Error
> in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user 'sa'."
> Although I've allowed Windows Authentication & SQL Server authentication in
> SQL Server 2005 logins settings, that error message will still appears and
> restrict any users to log-in to the application.
> That application uses a "DataAccessLayer.config" file that has this
> parameters:
> <?xml version="1.0" encoding="utf-8"?>
> <configuration>
> <appSettings>
> <add key="UseWebService" value="false"/>
> <add key="DAL.DataService.DataService" value="http://companyweb"/>
> <add key="DBCnnString" value="Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> <add key="server" value="ServerName"/>
> <add key="database" value="INVENTORY"/>
> <add key="uid" value="sa"/>
> <add key="password" value=""/>
> </appSettings>
> </configuration>
> What happened? Have anyone encountered the same symtomps/problem and know
> how to work around it?
> Please be noted, I attach this "INVENTORY" database into
> ServerName\Database; I do not use instances for it.
> Thank you, all help is appreciated.
You are trying to connect to the server using the "sa" login, with no
password!!! You have two problems here:
1. Never, ever, purposely allow an application to connect as "sa", you
need to tightly control the use of this login.
2. If you purposely installed SQL Server with a blank password for
"sa", change it, now. Your server is wide open to anybody who chooses
to login as "sa".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Arnie,
When I installed the SQL Server 2005 for Default Database, I use Windows
Authentication and Local System.
Is there a way to know whether the [sa] has blank password or not?
Further notice: I tried to input [sa] password, and I use the same password
in "DataAccessLayer.config" under "Password=" value; but it still won't work.
The error message is still the same as before.
Any other suggestions perhaps?
Thanks!
"Arnie Rowland" wrote:
> And did you really install SQL 2005 without a password for the [sa] account?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dhow" <Dhow@.discussions.microsoft.com> wrote in message
> news:9114C9D0-F787-41A2-B2A1-9B84FA635F1B@.microsoft.com...
> > Hi,
> > I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> > fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> > The only thing and the most important that should be working but not, is
> > application that uses SQL Server as it's main database. Before this, the
> > application used SQL Server 2000 and working fine.
> > Everytime users launch the application, there's this error message: "Error
> > in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user
> > 'sa'."
> > Although I've allowed Windows Authentication & SQL Server authentication
> > in
> > SQL Server 2005 logins settings, that error message will still appears and
> > restrict any users to log-in to the application.
> > That application uses a "DataAccessLayer.config" file that has this
> > parameters:
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <configuration>
> > <appSettings>
> > <add key="UseWebService" value="false"/>
> > <add key="DAL.DataService.DataService" value="http://companyweb"/>
> > <add key="DBCnnString" value="Persist Security Info=False;User
> > ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> > <add key="server" value="ServerName"/>
> > <add key="database" value="INVENTORY"/>
> > <add key="uid" value="sa"/>
> > <add key="password" value=""/>
> > </appSettings>
> > </configuration>
> >
> > What happened? Have anyone encountered the same symtomps/problem and know
> > how to work around it?
> > Please be noted, I attach this "INVENTORY" database into
> > ServerName\Database; I do not use instances for it.
> > Thank you, all help is appreciated.
>
>|||Arnie,
When I installed the SQL Server 2005 for Default Database, I use Windows
Authentication and Local System.
Is there a way to know whether the [sa] has blank password or not?
Further notice: I tried to input [sa] password, and I use the same password
in "DataAccessLayer.config" under "Password=" value; but it still won't work.
The error message is still the same as before.
Any other suggestions perhaps?
Thanks!
"Arnie Rowland" wrote:
> And did you really install SQL 2005 without a password for the [sa] account?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dhow" <Dhow@.discussions.microsoft.com> wrote in message
> news:9114C9D0-F787-41A2-B2A1-9B84FA635F1B@.microsoft.com...
> > Hi,
> > I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> > fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> > The only thing and the most important that should be working but not, is
> > application that uses SQL Server as it's main database. Before this, the
> > application used SQL Server 2000 and working fine.
> > Everytime users launch the application, there's this error message: "Error
> > in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user
> > 'sa'."
> > Although I've allowed Windows Authentication & SQL Server authentication
> > in
> > SQL Server 2005 logins settings, that error message will still appears and
> > restrict any users to log-in to the application.
> > That application uses a "DataAccessLayer.config" file that has this
> > parameters:
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <configuration>
> > <appSettings>
> > <add key="UseWebService" value="false"/>
> > <add key="DAL.DataService.DataService" value="http://companyweb"/>
> > <add key="DBCnnString" value="Persist Security Info=False;User
> > ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> > <add key="server" value="ServerName"/>
> > <add key="database" value="INVENTORY"/>
> > <add key="uid" value="sa"/>
> > <add key="password" value=""/>
> > </appSettings>
> > </configuration>
> >
> > What happened? Have anyone encountered the same symtomps/problem and know
> > how to work around it?
> > Please be noted, I attach this "INVENTORY" database into
> > ServerName\Database; I do not use instances for it.
> > Thank you, all help is appreciated.
>
>|||Arnie,
When I installed the SQL Server 2005 for Default Database, I use Windows
Authentication and Local System.
Is there a way to know whether the [sa] has blank password or not?
Further notice: I tried to input [sa] password, and I use the same password
in "DataAccessLayer.config" under "Password=" value; but it still won't work.
The error message is still the same as before.
Any other suggestions perhaps?
Thanks!
"Arnie Rowland" wrote:
> And did you really install SQL 2005 without a password for the [sa] account?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dhow" <Dhow@.discussions.microsoft.com> wrote in message
> news:9114C9D0-F787-41A2-B2A1-9B84FA635F1B@.microsoft.com...
> > Hi,
> > I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> > fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> > The only thing and the most important that should be working but not, is
> > application that uses SQL Server as it's main database. Before this, the
> > application used SQL Server 2000 and working fine.
> > Everytime users launch the application, there's this error message: "Error
> > in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user
> > 'sa'."
> > Although I've allowed Windows Authentication & SQL Server authentication
> > in
> > SQL Server 2005 logins settings, that error message will still appears and
> > restrict any users to log-in to the application.
> > That application uses a "DataAccessLayer.config" file that has this
> > parameters:
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <configuration>
> > <appSettings>
> > <add key="UseWebService" value="false"/>
> > <add key="DAL.DataService.DataService" value="http://companyweb"/>
> > <add key="DBCnnString" value="Persist Security Info=False;User
> > ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> > <add key="server" value="ServerName"/>
> > <add key="database" value="INVENTORY"/>
> > <add key="uid" value="sa"/>
> > <add key="password" value=""/>
> > </appSettings>
> > </configuration>
> >
> > What happened? Have anyone encountered the same symtomps/problem and know
> > how to work around it?
> > Please be noted, I attach this "INVENTORY" database into
> > ServerName\Database; I do not use instances for it.
> > Thank you, all help is appreciated.
>
>|||Arnie,
When I installed the SQL Server 2005 for Default Database, I use Windows
Authentication and Local System.
Is there a way to know whether the [sa] has blank password or not?
Further notice: I tried to input [sa] password, and I use the same password
in "DataAccessLayer.config" under "Password=" value; but it still won't work.
The error message is still the same as before.
Any other suggestions perhaps?
Thanks!
"Arnie Rowland" wrote:
> And did you really install SQL 2005 without a password for the [sa] account?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dhow" <Dhow@.discussions.microsoft.com> wrote in message
> news:9114C9D0-F787-41A2-B2A1-9B84FA635F1B@.microsoft.com...
> > Hi,
> > I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> > fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> > The only thing and the most important that should be working but not, is
> > application that uses SQL Server as it's main database. Before this, the
> > application used SQL Server 2000 and working fine.
> > Everytime users launch the application, there's this error message: "Error
> > in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user
> > 'sa'."
> > Although I've allowed Windows Authentication & SQL Server authentication
> > in
> > SQL Server 2005 logins settings, that error message will still appears and
> > restrict any users to log-in to the application.
> > That application uses a "DataAccessLayer.config" file that has this
> > parameters:
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <configuration>
> > <appSettings>
> > <add key="UseWebService" value="false"/>
> > <add key="DAL.DataService.DataService" value="http://companyweb"/>
> > <add key="DBCnnString" value="Persist Security Info=False;User
> > ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> > <add key="server" value="ServerName"/>
> > <add key="database" value="INVENTORY"/>
> > <add key="uid" value="sa"/>
> > <add key="password" value=""/>
> > </appSettings>
> > </configuration>
> >
> > What happened? Have anyone encountered the same symtomps/problem and know
> > how to work around it?
> > Please be noted, I attach this "INVENTORY" database into
> > ServerName\Database; I do not use instances for it.
> > Thank you, all help is appreciated.
>
>|||Arnie,
When I installed the SQL Server 2005 for Default Database, I use Windows
Authentication and Local System.
Is there a way to know whether the [sa] has blank password or not?
Further notice: I tried to input [sa] password, and I use the same password
in "DataAccessLayer.config" under "Password=" value; but it still won't work.
The error message is still the same as before.
Any other suggestions perhaps?
Thanks!
"Arnie Rowland" wrote:
> And did you really install SQL 2005 without a password for the [sa] account?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dhow" <Dhow@.discussions.microsoft.com> wrote in message
> news:9114C9D0-F787-41A2-B2A1-9B84FA635F1B@.microsoft.com...
> > Hi,
> > I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> > fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> > The only thing and the most important that should be working but not, is
> > application that uses SQL Server as it's main database. Before this, the
> > application used SQL Server 2000 and working fine.
> > Everytime users launch the application, there's this error message: "Error
> > in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user
> > 'sa'."
> > Although I've allowed Windows Authentication & SQL Server authentication
> > in
> > SQL Server 2005 logins settings, that error message will still appears and
> > restrict any users to log-in to the application.
> > That application uses a "DataAccessLayer.config" file that has this
> > parameters:
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <configuration>
> > <appSettings>
> > <add key="UseWebService" value="false"/>
> > <add key="DAL.DataService.DataService" value="http://companyweb"/>
> > <add key="DBCnnString" value="Persist Security Info=False;User
> > ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> > <add key="server" value="ServerName"/>
> > <add key="database" value="INVENTORY"/>
> > <add key="uid" value="sa"/>
> > <add key="password" value=""/>
> > </appSettings>
> > </configuration>
> >
> > What happened? Have anyone encountered the same symtomps/problem and know
> > how to work around it?
> > Please be noted, I attach this "INVENTORY" database into
> > ServerName\Database; I do not use instances for it.
> > Thank you, all help is appreciated.
>
>|||Tracy,
I know I input blank password for [sa] user, but that was only for a tryout.
Later on after everything works fine, I'll input new password into it.
When I installed the SQL Server 2005 for Default Database, I use Windows
Authentication and Local System.
Is there a way to know whether the [sa] has blank password or not?
Further notice: I tried to input [sa] password, and I use the same password
in "DataAccessLayer.config" under "Password=" value; but it still won't work.
The error message is still the same as before.
Any other usefull sugesstions?
Thanks!
"Tracy McKibben" wrote:
> Dhow wrote:
> > Hi,
> > I've just installed SQL Server 2005 into Win SBS 2003 R2, everything went
> > fine and other instances (SHAREPOINT & SBSMONITORING) are working as well.
> > The only thing and the most important that should be working but not, is
> > application that uses SQL Server as it's main database. Before this, the
> > application used SQL Server 2000 and working fine.
> > Everytime users launch the application, there's this error message: "Error
> > in DataAccessLayer.DataAccess.CheckUserLogin. Login failed for user 'sa'."
> > Although I've allowed Windows Authentication & SQL Server authentication in
> > SQL Server 2005 logins settings, that error message will still appears and
> > restrict any users to log-in to the application.
> > That application uses a "DataAccessLayer.config" file that has this
> > parameters:
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <configuration>
> > <appSettings>
> > <add key="UseWebService" value="false"/>
> > <add key="DAL.DataService.DataService" value="http://companyweb"/>
> > <add key="DBCnnString" value="Persist Security Info=False;User
> > ID=sa;Password=;Initial Catalog=INVENTORY;Data Source=192.168.16.2"/>
> > <add key="server" value="ServerName"/>
> > <add key="database" value="INVENTORY"/>
> > <add key="uid" value="sa"/>
> > <add key="password" value=""/>
> > </appSettings>
> > </configuration>
> >
> > What happened? Have anyone encountered the same symtomps/problem and know
> > how to work around it?
> > Please be noted, I attach this "INVENTORY" database into
> > ServerName\Database; I do not use instances for it.
> > Thank you, all help is appreciated.
> You are trying to connect to the server using the "sa" login, with no
> password!!! You have two problems here:
> 1. Never, ever, purposely allow an application to connect as "sa", you
> need to tightly control the use of this login.
> 2. If you purposely installed SQL Server with a blank password for
> "sa", change it, now. Your server is wide open to anybody who chooses
> to login as "sa".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||It seemed there's an error in MSTechNet website, everytime I tried to reply
to Arnie & Tracy, the reply window said there's a server error. So I kept
repeating it, and realize that there have been so many replies send, sorry.
I've just Detach the INVENTORY database from default database (MSSQLSERVER),
but the result is quite surprising: I can't logon again to default database
(MSSQLSERVER)!
I don't understand why I can't login again, the SQL Server 2005 only said
that there has been no activity at the other end & after that it said there's
been logon failure...
What happened?
I too tried to creat an Instance in SQL Server 2005 named with INVENTORY,
after the Detach process (like I've mentioned up above), it also can't be
connected again. The error message is the same as above. What really
happening here?
Maybe someone have same experience like mine and now hot to work around it?
Now everytime I launch SQL Server Management Studio, there is always
INVENTORY database engines instance, although I've uninstall it. Can I delete
it from database engines options?
Thanks!

failed to connect to SQL Server's database!

Monday, March 19, 2012

failed laod xml error 0xc00ce556

after i completed working with all my packages finally in my master package i was trying to set ssis->logging

i came up with this error with all my packages how to get back my packages..

Microsoft visual studio is unable to load this document:Failed to load XML due to error 0xC00CE556 "Invalid at the top level of the document. Line 1099, Column 1". This happens when loading a package and the file cannot be opened or loaded correctly into XML document. This can be the result of either providing an incorrect file name to the LoadPackage method or the XML file specified having an incorrect format.

How can i solve this problem please me

Curious, do you use Source Safe or any source control software?|||

I have the project in my local machine.

Everything package and the master package which call the child packages where working just fine.

What i did was after doing all my work in the master package i was try to set ssis logging and all my packages came out with this error...just couldnt open any of my packag...all my packages which i have r the same error..i am just redoing everything...

any help is appreciated...

|||Woops! It looks like the files are corrupted... don't you have copy previous to the changes? I have never see such error. Sorry.

failed job with my user name as the owner.

Hi,
I am working on sql 2000 and one of the DBAs in my team.
All the jobs with me as the owner failed miserablly today
on one of my servers. They have been run fine forever
before.
Message: The job failed. Unable to determine if the owner
(domainname\jwang) of jobname has server access (reason:
Could not obtain information about Windows NT group/user
domainname\jwang'. [SQLSTATE 42000] (Error 8198)).
And when I try to connect to the server through EM on my
laptop, it returns this error message: 'a connection could
not be established to the servername. Reason: 'Cannot
generate SSPI context'.
This actually happened to several people who tried the
same thing.
Why all of a sudden this? We have be applying window
updates and sql patches to some of our servers, but not to
all of them yet. This server is one of the highest patch
level server. Could this cause a problem?
Urgent help please. Many thanks.
JJTry logging on to that server as jwang on that domain, see what happens.
Maybe there is a connectivity issue, you'll get better error messages
possibly from dong that.
--
*************************************
Andy S.
andy_mcdba@.yahoo.com
*************************************
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:086101c379be$b18ed1a0$a101280a@.phx.gbl...
> Hi,
> I am working on sql 2000 and one of the DBAs in my team.
> All the jobs with me as the owner failed miserablly today
> on one of my servers. They have been run fine forever
> before.
> Message: The job failed. Unable to determine if the owner
> (domainname\jwang) of jobname has server access (reason:
> Could not obtain information about Windows NT group/user
> domainname\jwang'. [SQLSTATE 42000] (Error 8198)).
> And when I try to connect to the server through EM on my
> laptop, it returns this error message: 'a connection could
> not be established to the servername. Reason: 'Cannot
> generate SSPI context'.
> This actually happened to several people who tried the
> same thing.
> Why all of a sudden this? We have be applying window
> updates and sql patches to some of our servers, but not to
> all of them yet. This server is one of the highest patch
> level server. Could this cause a problem?
>
> Urgent help please. Many thanks.
> JJ
>|||In article <086101c379be$b18ed1a0$a101280a@.phx.gbl>, "JJ Wang" <jwang@.leapwireless.com> wrote:
>Hi,
>I am working on sql 2000 and one of the DBAs in my team.
>All the jobs with me as the owner failed miserablly today
>on one of my servers. They have been run fine forever
>before.
>Message: The job failed. Unable to determine if the owner
>(domainname\jwang) of jobname has server access (reason:
>Could not obtain information about Windows NT group/user
>domainname\jwang'. [SQLSTATE 42000] (Error 8198)).
>And when I try to connect to the server through EM on my
>laptop, it returns this error message: 'a connection could
>not be established to the servername. Reason: 'Cannot
>generate SSPI context'.
>This actually happened to several people who tried the
>same thing.
>Why all of a sudden this? We have be applying window
>updates and sql patches to some of our servers, but not to
>all of them yet. This server is one of the highest patch
>level server. Could this cause a problem?
>
>Urgent help please. Many thanks.
>JJ
>
Anything going on with your PDC/Active Directory Servers?|||Thank you, Andy, I know that it will work with sql login, there is
something funky going on with the domain authentication on this server.
JJ
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Monday, March 12, 2012

Failed Compile and Deploy

This was working this morning...
My network login was set to expire, so I changed my password.
Now I cannot compile or deploy any report. When I try, I get 1 failed in
the Build Output but no error is displayed in the tasks.
I have tried creating a new solution with no luck.
Another developer can compile and deploy.
Any ideas?Anyone?
I have tried reinstalling, rebooting server. No luck.
I can compile other projects just not reporting.
"JSF" wrote:
> This was working this morning...
> My network login was set to expire, so I changed my password.
> Now I cannot compile or deploy any report. When I try, I get 1 failed in
> the Build Output but no error is displayed in the tasks.
> I have tried creating a new solution with no luck.
> Another developer can compile and deploy.
>
> Any ideas?|||It sounds to me like the server is validating against your old password,
that it has cached it. Try going to the server and loggin in as yourself and
see if it will let you log in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JSF" <JSF@.discussions.microsoft.com> wrote in message
news:B0CFD95D-B974-42E6-93A4-05D06585FAAD@.microsoft.com...
> Anyone?
> I have tried reinstalling, rebooting server. No luck.
> I can compile other projects just not reporting.
>
> "JSF" wrote:
>> This was working this morning...
>> My network login was set to expire, so I changed my password.
>> Now I cannot compile or deploy any report. When I try, I get 1 failed
>> in
>> the Build Output but no error is displayed in the tasks.
>> I have tried creating a new solution with no luck.
>> Another developer can compile and deploy.
>>
>> Any ideas?

Wednesday, March 7, 2012

Fact Table Optimal Design

I have a working model, but looking to optimize it. Particulary the fact table. Here is how fact table looks:

ProductKey ChargeAKey ChargeBKey ChargeCKey ChargeA$ ChargeB$ ChargeC$ TotalABC$

1 1 0 0 2$ 0 0 2$

1 2 0 0 3$ 0 0 3$

1 0 1 0 0 4$ 0 4$

1 0 0 1 0 0 2$ 2$

1 0 0 2 0 0 3$ 3$

1 0 0 3 0 0 3$ 3$

... now some explanation.. granuality of fact table is Product + ChargeType. each charge type (A, B, C) has it's own dimension table with its own unique list of attributes and they all come from different sources. The simplest loading would append each Product + ChargeX to the table. I was thinking of option b - settingup fact table as Product + ChargeType + ChargeKey + Chargeamount, but this design in my opinion is less friendly then the original because user would have to know what charges have to be filtered down first by type to get to specific key and its attributes... what I don't like about option a is that it takes a lot of space and there are fields populated with only meningless 0 vs having everything compressed into few records. e.g. above exmple could be designed to have only 3 records vs. 6 which translates in huge savings when we are talking millions of records.Would anyone have anyexperience with similiar challenge? I would be able to solve this with complex ETL procedures, but there has to be something much simplier. I am still not sure if this is design or ETL solution... any feedback greatly appreciated.

My general recommendation on dimensional design is to stay focused on your business users. What is the business process, activity, or event this table represents? How would your users describe the facts associated with this process/activity/event?

It appears you have incorporated a dimension (charge type) with three values into your model. If each charge type represents a different event/process/activity, then three fact tables are in order. If this is a single event that could be broken down by charge type (of which there are currently three identified), then a single fact table is required with a charge type dimension. (What happens if a fourth charge type is introduced?)

|||

Thank You. That's right I always keep end user in mind because it is for him that data is being presented... and you always think the performance and simplicity. You gave me the answer: "What happens if a fourth charge type is intorduced?".. I will be in a lot of work, if not trouble, to integrate everything into one fact table.. Thanks.

Fact Table Design for Hours Worked

Hi,
I am trying to design a cube with Date, Time and Employee dimension
which can answer following questions:
- how many employees were working at 10am on 15 June 2007? I
- how many man hours were pais between 10am -11am on 15 June 2007. If
somebody has punched in at 10:30 then he should be counted and .5 man
hours.
One solution i can think of is every minute of everyhour for every
employee entered as a row in fact table. In this case if a employees
works for 8 hours that will create 8*60= 480 records in fact table. So
this may not be a good solution.
Ideally it would be better if we can enter one record in fact table
with start time and stop time. But, how can we put corrective
transaction if we need to cancel or change the original transactions.
Any help will be appreciated.
Thanks
Maheshyou can use 2 hour dimension:
Start Time and End Time
to answer:
> - how many employees were working at 10am on 15 June 2007? I
you select the day and all the start times up to 10am and all the end times
after 10am and you have the result.
for the second one, you have to create some formula.
maybe you can create a new measure group which contains only the start time
/ end time , and, for each combination, the duration in minute of the
selected period (ie you select 10h30 and 11h45, the measure returns
75minutes) (call this measure NoMinutesInPeriod)
now, in the cube, you have to create a formula which apply a ratio if the
start time or the end time is between the selected period of time.
measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
the datediff formula will be more complicated then a simple datediff.
but maybe this idea is a good starting point for you.
or anybody else has a better solution... please share your ideas :-)
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
> Hi,
> I am trying to design a cube with Date, Time and Employee dimension
> which can answer following questions:
> - how many employees were working at 10am on 15 June 2007? I
> - how many man hours were pais between 10am -11am on 15 June 2007. If
> somebody has punched in at 10:30 then he should be counted and .5 man
> hours.
> One solution i can think of is every minute of everyhour for every
> employee entered as a row in fact table. In this case if a employees
> works for 8 hours that will create 8*60= 480 records in fact table. So
> this may not be a good solution.
> Ideally it would be better if we can enter one record in fact table
> with start time and stop time. But, how can we put corrective
> transaction if we need to cancel or change the original transactions.
> Any help will be appreciated.
> Thanks
> Mahesh
>|||Thanks Jeje. Your solution seems to be the best solution. The only
problem with this is the difficulty to add corrective transactions.
For example, if hours worked is entered as 8am - 5pm on June 5 for an
employee. On June 25 if it is changed to be 10am - 5pm, how can we
enter a new corrective transaction on June 25 without changing the
original transaction. We do not want to change the original
transaction to use Incrementa Processing on cubes.
Any response will be very much appreciated.
Mahesh
On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
> you can use 2 hour dimension:
> Start Time and End Time
> to answer:> - how many employees were working at 10am on 15 June 2007? I
> you select the day and all the start times up to 10am and all the end time
s
> after 10am and you have the result.
> for the second one, you have to create some formula.
> maybe you can create a new measure group which contains only the start tim
e
> / end time , and, for each combination, the duration in minute of the
> selected period (ie you select 10h30 and 11h45, the measure returns
> 75minutes) (call this measure NoMinutesInPeriod)
> now, in the cube, you have to create a formula which apply a ratio if the
> start time or the end time is between the selected period of time.
> measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
> the datediff formula will be more complicated then a simple datediff.
> but maybe this idea is a good starting point for you.
> or anybody else has a better solution... please share your ideas :-)
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||if you want to "cancel" a transaction already loaded in a cube.
you have to add another "transaction" with "negative values"
for example:
initially you load the cube using this:
June 25 / 8am - 5pm / +1 (+1 work)
you want to "cancel" this transaction, and replace by a new one:
June 25 / 8am - 5pm / -1 (-1 work)
June 25 / 10am - 5pm / +1 (+1 work)
so the total in the cube is:
June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
June 25 / 10am - 5pm / +1
but using this approach you can't use the "non empty" because the cell is
not empty, the cell contains a total of 0.
Also remember to do full processes at a regular basis because an incremental
loading reduce the cube performance.
good luck!
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
> Thanks Jeje. Your solution seems to be the best solution. The only
> problem with this is the difficulty to add corrective transactions.
> For example, if hours worked is entered as 8am - 5pm on June 5 for an
> employee. On June 25 if it is changed to be 10am - 5pm, how can we
> enter a new corrective transaction on June 25 without changing the
> original transaction. We do not want to change the original
> transaction to use Incrementa Processing on cubes.
> Any response will be very much appreciated.
> Mahesh
>
> On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
>|||Thanks you very much for your kind response, Jeje.
Mahesh
On Jun 25, 9:27 am, "Jeje" <willg...@.hotmail.com> wrote:
> if you want to "cancel" a transaction already loaded in a cube.
> you have to add another "transaction" with "negative values"
> for example:
> initially you load the cube using this:
> June 25 / 8am - 5pm / +1 (+1 work)
> you want to "cancel" this transaction, and replace by a new one:
> June 25 / 8am - 5pm / -1 (-1 work)
> June 25 / 10am - 5pm / +1 (+1 work)
> so the total in the cube is:
> June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
> June 25 / 10am - 5pm / +1
> but using this approach you can't use the "non empty" because the cell is
> not empty, the cell contains a total of 0.
> Also remember to do full processes at a regular basis because an increment
al
> loading reduce the cube performance.
> good luck!
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -

Fact table design

Hello.

I am working on an educational data warehouse. I have a semi-additive fact table that deals with student grades and averages. It's as such:

School, Acad Year, Student, Course, Term, Week, Week Grade, Term Average

where Week Grade and Term Average are the measures.

The term average is an average calculated based on a specific formula on top of week grade. It's supposed to appear when the user is standing on All weeks member of Week's dimension.

I have an average calculation problem in the term average...say i want to compare the student term averages.
i don't know if its correct to write an mdx formula to get it or to create a separate table for the Term Average containing the granularity by term rather than by week.

Thanks in advance for your advice

Christina

I think your design has some problems. The biggest one is that you are mixing grain levels in the fact table (terms and weeks) and both are dates. My advice is to first focus on creating the fact table at the most detailed grain level, which I think is probably days (complete day). So

FactGrades: SchoolKey, DateKey, StudentKey, CourseKey, Grade.

The date key should have some week attributes so you know to which week the current day is referring to, and the same with the term. Your Date dimension should look something like this:

DimDate: DateKey, FullDate, CalendarYear, CalendarSemester, CalendarMonth, CalendarDayInMonth, TermYear, TermSemester, TermMonth?, TermWeek, TermDayNumber, etc, etc.

From this you could create an aggregate fact table for the term averages or better yet, derive the term average with an MDX expression when you create your Olap Cube in Analysis Services.

|||

Ok let's say i fix the date issue. i still have a concern. i want to precalculate the term average because the way to get it is based on many factors and i am trying to avoid its complexity. i prefer to add it to this fact table or to create a fact table just for it.

but this measure would be considered at a different granularity..is it correct to add it to this fact table or should i create a fact table just for it....i did the second option and want to make sure its correct.

and honestly that's why i separated the week from the term because i assumed that if i put it in a different fact table, i would need to use only the term key, and not the week.

in AS,i am using the scope function in the Calcuations to change the aggregated value of the Grade measure to TermAverage when the currentmember is at the Term level.

That's one of my concerns.

I have another issue resulting from this one... i would like to share it with you in case you could give me some advice.

1-assume i'm standing at class section level, which is a level in the student dimension (school, class, section, student). for a given course i want to compare the average between sections of the same class to see which section is performing better during this week.

2-i want to do the same but for a term instead of week

in case 1, the measure used should be the Grade.
in case 2, it should be the TermAverage.

when i added these measures to the cube, i set the aggregation property to average..however the avg is semi-additive and will only function on time dimension. i therefore created 2 calculated measures, one for the grade and another for the TermAverage as such:

Grade Average = Grade/count of records in the fact table
TermAverage Average = TermAverage/count of records in the other fact table containing only the TermAverage.

and then modified the scope calculation to put these measures in the aggergation instead of the original Grade and TermAverage because the other will give me Sum while standing at Class or Class Section while what i want is the average.

Am i doing right like that by creating both fact tables, doing the measures like that and then modifying the scope to do this? i would like to mention that the aggregation now is taking much more time to give results because of the calculated measures...

Thanks again for your help

Christina

|||

I really think you should try to compute the term average with the information from the daily grades using MDX. How complex can it be? Keep it as simple as possible. If you still want to have it separately, you should create a different fact table for the term average. The only thing is that this fact table should no longer have a dateKey but a TermKey or put it as a "measure" in the fact table so you don't have to maintain a term dimension. This value is the same as the one defined for that term in the Date dimension. This is for the Data Warehouse. E.g.

DimDate.Term: 200509

FactTermAverage: SchoolKey, StudentKey, CourseKey, Term (200509), Average (19.45)

In this way you can link the term with your date dimension in Analysis Services. Just make sure to set the attribute hierarchical relationships properly for the Date dimension, and the scope for each measure (Checking the term average per calendar day, calendar month, day, etc. doesn't make sense so the term average should be displayed as "NA" in these cases).

In regards to your second question, you should have a single cube in AS for a DW (best practice). This means you would have a count measure in the cube for each fact table. However you should try to design a single calculated measure called "Section Average" which is calculated depending on where the user is in the cube:

Case

When user is doing week comparisons
Grade/count of records in the grade fact table
When user is doing term comparisons
Term Average/count of records in the term fact table
Else
"NA"
End

|||

Thank you very much for your insight.

First i never knew i could link the Term to the Weekly dimension like that.. it saved me a lot.

I will also try to do the measure. The problem is that i'm not that good at MDX and i try to avoid using it..

You wondered how complex the MDX computation could be..well to me it's complex..i will explain it to you maybe you could help me again at this one..
in the time dimension i said i have Academic Year,Term and Week.

the term average=avg(Weeks 1-8)*0.60+avg(week 9)*0.40

The acad year average = Term 1*0.25 + Term 2 *0.25 + Term 3(week1-8)*0.20 + Term 3 (week9)*0.30

Thanks much

Christina

|||

hi .. i am also working on university data warehouse but i am only working on two subjects i.e. Student record and Admission ..if some one can help me in designing of the model ..

thanx

Zeeshan Ali

Thursday, March 29, 2012

Failed to open a rowset Warning/Error

Tuesday, March 27, 2012

Failed to notify 'Operator' via email

Failed to get the call stack! error

Failed to get distributed transaction startet

Failed to get distributed transaction startet

Wednesday, March 21, 2012

failed to connect to SQL Server's database!

failed to connect to SQL Server's database!

failed to connect to SQL Server's database!

Monday, March 19, 2012

failed laod xml error 0xc00ce556

failed job with my user name as the owner.

Monday, March 12, 2012

Failed Compile and Deploy

Wednesday, March 7, 2012

Fact Table Optimal Design

Fact Table Design for Hours Worked

Fact table design

Extracting Sql

Blog Archive

About Me