Hi there,
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nico
sp_configure 'user connections', 0
go
reconfigure
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico
|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
concurrent queries, since they are basically free software. If you want to get rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico
|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:
> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>
>
|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico
|||See my other reply. You need to buy and install "a proper" SQL Server to get rid of this performance
throttling.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...[vbcol=seagreen]
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimized
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
|||ok thanks. You are right, the server is installed with the personnal edition.
We have the standard edition, I should reinstall it with the standard edition.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:
> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
> concurrent queries, since they are basically free software. If you want to get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>
|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex. Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...[vbcol=seagreen]
> ok thanks. You are right, the server is installed with the personnal edition.
> We have the standard edition, I should reinstall it with the standard edition.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
|||Thank you
"Tibor Karaszi" wrote:
> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more complex. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
>
sql
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Friday, March 23, 2012
How to increase the number of allowed queries?
Hi there,
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nicosp_configure 'user connections', 0
go
reconfigure
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. The
se are constrained to 8
concurrent queries, since they are basically free software. If you want to g
et rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edi
tion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:
> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>
>|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||See my other reply. You need to buy and install "a proper" SQL Server to get
rid of this performance
throttling.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...[vbcol=seagreen]
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimiz
ed
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
>|||ok thanks. You are right, the server is installed with the personnal edition
.
We have the standard edition, I should reinstall it with the standard editio
n.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:
> Seems you are running either MSDE (Desktop Engine) or Personal Editions. T
hese are constrained to 8
> concurrent queries, since they are basically free software. If you want to
get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise E
dition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files
(safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex.
Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...[vbcol=seagreen]
> ok thanks. You are right, the server is installed with the personnal editi
on.
> We have the standard edition, I should reinstall it with the standard edit
ion.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
>|||Thank you
"Tibor Karaszi" wrote:
> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database file
s (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more comple
x. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Serv
ers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
>
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nicosp_configure 'user connections', 0
go
reconfigure
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. The
se are constrained to 8
concurrent queries, since they are basically free software. If you want to g
et rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edi
tion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:
> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>
>|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||See my other reply. You need to buy and install "a proper" SQL Server to get
rid of this performance
throttling.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...[vbcol=seagreen]
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimiz
ed
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
>|||ok thanks. You are right, the server is installed with the personnal edition
.
We have the standard edition, I should reinstall it with the standard editio
n.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:
> Seems you are running either MSDE (Desktop Engine) or Personal Editions. T
hese are constrained to 8
> concurrent queries, since they are basically free software. If you want to
get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise E
dition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files
(safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex.
Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...[vbcol=seagreen]
> ok thanks. You are right, the server is installed with the personnal editi
on.
> We have the standard edition, I should reinstall it with the standard edit
ion.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
>|||Thank you
"Tibor Karaszi" wrote:
> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database file
s (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more comple
x. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Serv
ers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
>
How to increase the number of allowed queries?
Hi there,
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nicosp_configure 'user connections', 0
go
reconfigure
go
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
concurrent queries, since they are basically free software. If you want to get rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:
> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> > Hi there,
> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> > queries, how can I increase the number of simultaneous queries?
> >
> > Thanks
> > Nico
>
>|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||See my other reply. You need to buy and install "a proper" SQL Server to get rid of this performance
throttling.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimized
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
>> Hi there,
>> our SQL server 2000 running on Windows 2000 server is optimised for 8
>> queries, how can I increase the number of simultaneous queries?
>> Thanks
>> Nico|||ok thanks. You are right, the server is installed with the personnal edition.
We have the standard edition, I should reinstall it with the standard edition.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:
> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
> concurrent queries, since they are basically free software. If you want to get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> > Hi there,
> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> > queries, how can I increase the number of simultaneous queries?
> >
> > Thanks
> > Nico
>|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex. Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
> ok thanks. You are right, the server is installed with the personnal edition.
> We have the standard edition, I should reinstall it with the standard edition.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
>> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to
>> 8
>> concurrent queries, since they are basically free software. If you want to get rid of the
>> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Nico" <Nico@.discussions.microsoft.com> wrote in message
>> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>> > Hi there,
>> > our SQL server 2000 running on Windows 2000 server is optimised for 8
>> > queries, how can I increase the number of simultaneous queries?
>> >
>> > Thanks
>> > Nico
>>|||Thank you
"Tibor Karaszi" wrote:
> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more complex. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
> > ok thanks. You are right, the server is installed with the personnal edition.
> > We have the standard edition, I should reinstall it with the standard edition.
> >
> > Is there anything I should do before uninstalling the personnal edition et
> > reinstalling with the Standard edition?
> >
> > Thank you
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to
> >> 8
> >> concurrent queries, since they are basically free software. If you want to get rid of the
> >> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> >> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> >> > Hi there,
> >> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> >> > queries, how can I increase the number of simultaneous queries?
> >> >
> >> > Thanks
> >> > Nico
> >>
> >>
>
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nicosp_configure 'user connections', 0
go
reconfigure
go
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
concurrent queries, since they are basically free software. If you want to get rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:
> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> > Hi there,
> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> > queries, how can I increase the number of simultaneous queries?
> >
> > Thanks
> > Nico
>
>|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||See my other reply. You need to buy and install "a proper" SQL Server to get rid of this performance
throttling.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimized
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
>> Hi there,
>> our SQL server 2000 running on Windows 2000 server is optimised for 8
>> queries, how can I increase the number of simultaneous queries?
>> Thanks
>> Nico|||ok thanks. You are right, the server is installed with the personnal edition.
We have the standard edition, I should reinstall it with the standard edition.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:
> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
> concurrent queries, since they are basically free software. If you want to get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> > Hi there,
> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> > queries, how can I increase the number of simultaneous queries?
> >
> > Thanks
> > Nico
>|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex. Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
> ok thanks. You are right, the server is installed with the personnal edition.
> We have the standard edition, I should reinstall it with the standard edition.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
>> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to
>> 8
>> concurrent queries, since they are basically free software. If you want to get rid of the
>> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Nico" <Nico@.discussions.microsoft.com> wrote in message
>> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>> > Hi there,
>> > our SQL server 2000 running on Windows 2000 server is optimised for 8
>> > queries, how can I increase the number of simultaneous queries?
>> >
>> > Thanks
>> > Nico
>>|||Thank you
"Tibor Karaszi" wrote:
> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more complex. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
> > ok thanks. You are right, the server is installed with the personnal edition.
> > We have the standard edition, I should reinstall it with the standard edition.
> >
> > Is there anything I should do before uninstalling the personnal edition et
> > reinstalling with the Standard edition?
> >
> > Thank you
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to
> >> 8
> >> concurrent queries, since they are basically free software. If you want to get rid of the
> >> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> >> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> >> > Hi there,
> >> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> >> > queries, how can I increase the number of simultaneous queries?
> >> >
> >> > Thanks
> >> > Nico
> >>
> >>
>
Wednesday, March 7, 2012
How to implement OR clause in MDX Queries
Hi
How to implement OR clause in MDX Queries so that i can write a query with OR condition on two dimensions.
If possible please post some example.
Regards;
Rakesh
Sets containing either members or tuples are now supported in the WHERE clause of an MDX statement.
Here is an example:
SELECT { [Time].[Calendar].[Quarter].&[2005904] } ON COLUMNS ,
NON EMPTY { TOPCOUNT( { DESCENDANTS( [Customer].[Customer].[All Customer], [Customer].[Customer].[Customer] ) }, 10, ( [Time].[Calendar].[Quarter].&[2005904], [Measures].[Sales Amt] ) ) } ON ROWS
FROM [Sales]
WHERE (
{ ([Product].[Family].[Business PCs], [Geography].[City].[Albany] )
,([Product].[Family].[Business PCs], [Geography].[City].[New York] )
,([Product].[Family].[Home PCs], [Geography].[City].[Albany] ) },
[Measures].[Sales Amt] )
The result of this query will be the top 10 customers based on Q4 2005 sales for customers who:
Bought a "Business PC" and live in "Albany"
OR
Bought a "Business PC" and live in "New York"
OR
Bought a "Home PC" and live in "Albany"
HTH,
- Steve
|||Hi Steve
Thanks for your reply. The solution you provided doesn't work at my end. Am i making any mistake in impletementing the same? Here is the my case:
Let us take an example to illustrate this. Let say we have four tables; Users, UserAccessBranch, Branch and FactLoan. User’s access to particular loan is based on his access to loan’s Branch (BranchID or OperatingBranchID). If user has access to at-least one of the two branches (BranchID and OperatingBranchID) then the loan is accessible to the user.
Users.UserIDà BranchAccess(UserID,BranchID)?Branch.BranchIDàFcatLoan.BranchID
àFactLoan.OperationalBranchID
In case of T-SQL it’s very easy to implement. In case of MDX it’s easy to implement if there is only one column of BranchID is there in FactLoan. I have implemented this case (having one BranchID) by having Many-to-Many relationship in the “Dimension Usage” of cube designer. How can we implement the logic for two Branch access (either Or) in MDX and cube design.
The MDX queries i am trying are:
This works for BranchID check
SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([BranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])
This works for OperatingBranchID check
SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([OperatingBranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])
This DOESN'T works for both
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {(([BranchUsers].[Users].&[1.]),([OperatingBranchUsers].[Users].&[1.]))}
Any suggestion is most welcome. Thanks a lot.
|||Rakesh,
Try the following:
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].DefaultMember),
([BranchUsers].[Users].DefaultMember,[OperatingBranchUsers].[Users].&[1.])}
The tuples should have the same "signature". So what you are seeing is "BranchUsers = 1 AND OperatingBranchUsers = Any" OR "BranchUsers = Any AND OperatingBranchUsers = 1".
HTH,
Steve
|||Steve
Thanks for you prompt reply. Sorry, but its giving me following error.
#Error Arbitrary shape is not allowed when its elements cross a reference dimension.
Am I missing something? Is it because i am using Many-to-Many relationship between Fact table and Users dimension?
Please suggest.
Rakesh
|||Rakesh,
I have not tried this with a Many-to-Many dimension relationship involved and you may have found a bug. I don't think you are missing anything, so you should report this to support and hopefully it is already fixed in SP2.
HTH,
Steve
|||Yes - this is fixed in SP2 - both for many-to-many and for reference dimensions, and not only for arbitrary shapes in WHERE clause but also for arbitrary shapes in subselects. I briefly mentioned it here: http://www.sql.ru/forum/actualthread.aspx?tid=360352#3400111
HTH,
Mosha (http://www.mosha.com/msolap)
|||You might like to look at the Analysis Services Stored Procedure Project http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures. I built a function called AsymmetricSet to facilitate the easy construction of sets like these. So your query would look like the following.
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE ASSP.AsymmectricSet([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].&[1.])
|||
Hi Mosha
Thanks for your post on the forum.
On SP1 the MDX script gives me the error “#Error Arbitrary shape is not allowed when its elements cross a reference dimension”. Whereas on SP2 it displays the result. So; I think they have made the required changes for referenced dimension or M2M relationships. But I still believe that there is some issue with this kind of query and cube design. The result of the query
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
(([Users].[Users].&[2]),([Operating Users].[Operating Users].DefaultMember)),
(([Users].[Users].DefaultMember),([Operating Users].[Operating Users].&[2]))
}
is not as per expectations. Following is the SQL query:
Select Count(*)
From FactLoan r
Where
( CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.OperatingBranchID and UserID = 2 ) > 0 THEN
1
ELSE
CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.BranchID and UserID = 2 ) > 0 THEN
1
ELSE
0
END
END
)=1
The output of the MDX query doesn’t matches with the SQL query output.
Please help out in understanding the cause for this. Thanks a lot. ?
Thanks & Regards;
Rakesh
|||
Hi Darren
Thanks for your post on the forum.
I have successfully installed the ASSP DLL but the output of the query
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE [ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.])
doesn’t give the correct result. Am I missing some thing?
Regards,
Rakesh
|||It's hard to say without knowing what result you are getting and what you were expecting.
You can display the output of the function by running the following query. This should let you validate if the function is generating the same set as the one coded by hand.
WITH Member [Measures].[OrSetStr] AS SetToStr([ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.]))
SELECT [Measures].[OrSetStr] on 0
FROM [SANDBOX]
|||
Rakesh,
I just had a read of your reply to Mosha's post and have the following observations.
If you are using a many-to-many relationship this would imply that one branch can have many users, and that one user can belong to many branches.
Therefore there would have to be a many-to-many "bridge table" which would be set up in the UDM as a measure group which would be used in the many-to-many relationship. In your SQL query the dimension table links straight to the fact table and there does not appear to be any joins to the table (or view) that would be facilitating the many-to-many relationship in the UDM. This may be why you are seeing different results between the MDX and the SQL.
|||Hey Darren
Thnaks for you time and reply to this question.
Actually I have the "bridge table" in my UDM and that "bridge table"is being used as "fact less" fact table. I have got the correct MDX (that meets my requirement) from Chris. The correct query that matches with my requirement is:
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
( [Operating Branch].[Operating Branch].defaultmember *
exists([Branch].[Branch].[Branch].members, [Users].[Users].&[2], "User Access Branch")
),
( exists([Operating Branch].[Operating Branch].[Operating Branch].members, [Operating Users].[Operating Users].&[2], "Operating Branch Access")
* [Branch].[Branch].defaultmember
)
}
Thanks again for all your support and advice. :)
Regards;
Rakesh
Having seen Rakesh's data model offline this approach won't work: going through the two m2m dimensions he set up it only seemed possible to get an AND and not an OR. Doing an Exists through the two intermediate measure groups that each User dimension was using to join to the main measure group as in my query above, so you found all the Branches each user had a relationship with, was the only thing that worked.
Chris
How to implement OR clause in MDX Queries
Hi
How to implement OR clause in MDX Queries so that i can write a query with OR condition on two dimensions.
If possible please post some example.
Regards;
Rakesh
Sets containing either members or tuples are now supported in the WHERE clause of an MDX statement.
Here is an example:
SELECT { [Time].[Calendar].[Quarter].&[2005904] } ON COLUMNS ,
NON EMPTY { TOPCOUNT( { DESCENDANTS( [Customer].[Customer].[All Customer], [Customer].[Customer].[Customer] ) }, 10, ( [Time].[Calendar].[Quarter].&[2005904], [Measures].[Sales Amt] ) ) } ON ROWS
FROM [Sales]
WHERE (
{ ([Product].[Family].[Business PCs], [Geography].[City].[Albany] )
,([Product].[Family].[Business PCs], [Geography].[City].[New York] )
,([Product].[Family].[Home PCs], [Geography].[City].[Albany] ) },
[Measures].[Sales Amt] )
The result of this query will be the top 10 customers based on Q4 2005 sales for customers who:
Bought a "Business PC" and live in "Albany"
OR
Bought a "Business PC" and live in "New York"
OR
Bought a "Home PC" and live in "Albany"
HTH,
- Steve
|||Hi Steve
Thanks for your reply. The solution you provided doesn't work at my end. Am i making any mistake in impletementing the same? Here is the my case:
Let us take an example to illustrate this. Let say we have four tables; Users, UserAccessBranch, Branch and FactLoan. User’s access to particular loan is based on his access to loan’s Branch (BranchID or OperatingBranchID). If user has access to at-least one of the two branches (BranchID and OperatingBranchID) then the loan is accessible to the user.
Users.UserIDà BranchAccess(UserID,BranchID)?Branch.BranchIDàFcatLoan.BranchID
àFactLoan.OperationalBranchID
In case of T-SQL it’s very easy to implement. In case of MDX it’s easy to implement if there is only one column of BranchID is there in FactLoan. I have implemented this case (having one BranchID) by having Many-to-Many relationship in the “Dimension Usage” of cube designer. How can we implement the logic for two Branch access (either Or) in MDX and cube design.
The MDX queries i am trying are:
This works for BranchID check
SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([BranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])
This works for OperatingBranchID check
SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([OperatingBranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])
This DOESN'T works for both
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {(([BranchUsers].[Users].&[1.]),([OperatingBranchUsers].[Users].&[1.]))}
Any suggestion is most welcome. Thanks a lot.
|||Rakesh,
Try the following:
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].DefaultMember),
([BranchUsers].[Users].DefaultMember,[OperatingBranchUsers].[Users].&[1.])}
The tuples should have the same "signature". So what you are seeing is "BranchUsers = 1 AND OperatingBranchUsers = Any" OR "BranchUsers = Any AND OperatingBranchUsers = 1".
HTH,
Steve
|||Steve
Thanks for you prompt reply. Sorry, but its giving me following error.
#Error Arbitrary shape is not allowed when its elements cross a reference dimension.
Am I missing something? Is it because i am using Many-to-Many relationship between Fact table and Users dimension?
Please suggest.
Rakesh
|||Rakesh,
I have not tried this with a Many-to-Many dimension relationship involved and you may have found a bug. I don't think you are missing anything, so you should report this to support and hopefully it is already fixed in SP2.
HTH,
Steve
|||Yes - this is fixed in SP2 - both for many-to-many and for reference dimensions, and not only for arbitrary shapes in WHERE clause but also for arbitrary shapes in subselects. I briefly mentioned it here: http://www.sql.ru/forum/actualthread.aspx?tid=360352#3400111
HTH,
Mosha (http://www.mosha.com/msolap)
|||You might like to look at the Analysis Services Stored Procedure Project http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures. I built a function called AsymmetricSet to facilitate the easy construction of sets like these. So your query would look like the following.
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE ASSP.AsymmectricSet([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].&[1.])
|||
Hi Mosha
Thanks for your post on the forum.
On SP1 the MDX script gives me the error “#Error Arbitrary shape is not allowed when its elements cross a reference dimension”. Whereas on SP2 it displays the result. So; I think they have made the required changes for referenced dimension or M2M relationships. But I still believe that there is some issue with this kind of query and cube design. The result of the query
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
(([Users].[Users].&[2]),([Operating Users].[Operating Users].DefaultMember)),
(([Users].[Users].DefaultMember),([Operating Users].[Operating Users].&[2]))
}
is not as per expectations. Following is the SQL query:
Select Count(*)
From FactLoan r
Where
( CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.OperatingBranchID and UserID = 2 ) > 0 THEN
1
ELSE
CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.BranchID and UserID = 2 ) > 0 THEN
1
ELSE
0
END
END
)=1
The output of the MDX query doesn’t matches with the SQL query output.
Please help out in understanding the cause for this. Thanks a lot. ?
Thanks & Regards;
Rakesh
|||
Hi Darren
Thanks for your post on the forum.
I have successfully installed the ASSP DLL but the output of the query
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE [ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.])
doesn’t give the correct result. Am I missing some thing?
Regards,
Rakesh
|||It's hard to say without knowing what result you are getting and what you were expecting.
You can display the output of the function by running the following query. This should let you validate if the function is generating the same set as the one coded by hand.
WITH Member [Measures].[OrSetStr] AS SetToStr([ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.]))
SELECT [Measures].[OrSetStr] on 0
FROM [SANDBOX]
|||
Rakesh,
I just had a read of your reply to Mosha's post and have the following observations.
If you are using a many-to-many relationship this would imply that one branch can have many users, and that one user can belong to many branches.
Therefore there would have to be a many-to-many "bridge table" which would be set up in the UDM as a measure group which would be used in the many-to-many relationship. In your SQL query the dimension table links straight to the fact table and there does not appear to be any joins to the table (or view) that would be facilitating the many-to-many relationship in the UDM. This may be why you are seeing different results between the MDX and the SQL.
|||Hey Darren
Thnaks for you time and reply to this question.
Actually I have the "bridge table" in my UDM and that "bridge table"is being used as "fact less" fact table. I have got the correct MDX (that meets my requirement) from Chris. The correct query that matches with my requirement is:
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
( [Operating Branch].[Operating Branch].defaultmember *
exists([Branch].[Branch].[Branch].members, [Users].[Users].&[2], "User Access Branch")
),
( exists([Operating Branch].[Operating Branch].[Operating Branch].members, [Operating Users].[Operating Users].&[2], "Operating Branch Access")
* [Branch].[Branch].defaultmember
)
}
Thanks again for all your support and advice. :)
Regards;
Rakesh
Having seen Rakesh's data model offline this approach won't work: going through the two m2m dimensions he set up it only seemed possible to get an AND and not an OR. Doing an Exists through the two intermediate measure groups that each User dimension was using to join to the main measure group as in my query above, so you found all the Branches each user had a relationship with, was the only thing that worked.
Chris
Friday, February 24, 2012
How to identify most executed queries?
Hi All,
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don′t want to waste money on it. Any suggestion?
Thanks
Celio.
Run a trace and dump and import the trace files into a table and run some
queries.
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I dont want to waste money on it. Any suggestion?
> Thanks
> Celio.
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don′t want to waste money on it. Any suggestion?
Thanks
Celio.
Run a trace and dump and import the trace files into a table and run some
queries.
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I dont want to waste money on it. Any suggestion?
> Thanks
> Celio.
How to identify most executed queries?
Hi All,
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don′t want to waste money on it. Any suggestion?
Thanks
Celio.Run a trace and dump and import the trace files into a table and run some
queries.
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I dont want to waste money on it. Any suggestion?
> Thanks
> Celio.
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don′t want to waste money on it. Any suggestion?
Thanks
Celio.Run a trace and dump and import the trace files into a table and run some
queries.
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I dont want to waste money on it. Any suggestion?
> Thanks
> Celio.
How to identify most executed queries?
Hi All,
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don´t want to waste money on it. Any suggestion?
Thanks
Celio.Run a trace and dump and import the trace files into a table and run some
queries.
--
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I don´t want to waste money on it. Any suggestion?
> Thanks
> Celio.
is there a way to identify which are the most executed queries or stored
procedures in a Sql Server 2000? I know that there are some commercial tools
that does it but I don´t want to waste money on it. Any suggestion?
Thanks
Celio.Run a trace and dump and import the trace files into a table and run some
queries.
--
Andrew J. Kelly SQL MVP
"Cmsn Brazil" <Cmsn Brazil@.discussions.microsoft.com> wrote in message
news:4E45B85B-5EE4-41A9-AF3A-C3A1FAC72DE5@.microsoft.com...
> Hi All,
> is there a way to identify which are the most executed queries or stored
> procedures in a Sql Server 2000? I know that there are some commercial
> tools
> that does it but I don´t want to waste money on it. Any suggestion?
> Thanks
> Celio.
Subscribe to:
Posts (Atom)