Is there a database size limitation with the desktop SQL edition'SQL Server Express is designed to meet the needs of simple applications. It
is limited to using 1 CPU and up to 1GB RAM, with a 4GB maximum database
size.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Richard Douglass" wrote:
| Is there a database size limitation with the desktop SQL edition'
|
|
||||The size of a database cannot exceed 2 GB when using the SQL Server
2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Richard Douglass wrote:
> Is there a database size limitation with the desktop SQL edition'
Showing posts with label limitation. Show all posts
Showing posts with label limitation. Show all posts
Monday, March 12, 2012
Limits
Is there a database size limitation with the desktop SQL edition'The size of a database cannot exceed 2 GB when using the SQL Server
2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Richard Douglass wrote:
> Is there a database size limitation with the desktop SQL edition'|||SQL Server Express is designed to meet the needs of simple applications. It
is limited to using 1 CPU and up to 1GB RAM, with a 4GB maximum database
size.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Richard Douglass" wrote:
| Is there a database size limitation with the desktop SQL edition'
|
|
|
2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Richard Douglass wrote:
> Is there a database size limitation with the desktop SQL edition'|||SQL Server Express is designed to meet the needs of simple applications. It
is limited to using 1 CPU and up to 1GB RAM, with a 4GB maximum database
size.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Richard Douglass" wrote:
| Is there a database size limitation with the desktop SQL edition'
|
|
|
Wednesday, March 7, 2012
limitations of express edition
What are the limitations of sql server express edition? For example oracle released Oracle express and its limitation is of maximum user data size of 4 gb.
Have a look at the following document, which compares the SQL Server editions:SQL Server 2005 Features Comparison|||Thanks a lot. This is exactly what I was looking for.|||SQL Server 2005 Features Comparison - this chart is not so detailed as I need. In comparison table for SQL 2000 was item about http/https connectivity (supported only by SQL 2000 Enterprise Edition) but in the comparison table for SQL 2005 is missing. Could you help me - I'd like to now if I need SQL 2005 Enterprise Edition for http/https connection to Analysis Services or it is already possible with Standard Edition.
Thanks
limitations of express edition
What are the limitations of sql server express edition? For example oracle released Oracle express and its limitation is of maximum user data size of 4 gb.
Have a look at the following document, which compares the SQL Server editions:SQL Server 2005 Features Comparison|||Thanks a lot. This is exactly what I was looking for.|||SQL Server 2005 Features Comparison - this chart is not so detailed as I need. In comparison table for SQL 2000 was item about http/https connectivity (supported only by SQL 2000 Enterprise Edition) but in the comparison table for SQL 2005 is missing. Could you help me - I'd like to now if I need SQL 2005 Enterprise Edition for http/https connection to Analysis Services or it is already possible with Standard Edition.
Thanks
Friday, February 24, 2012
Limitation on Number of Parameters??
Hi,
I would like to know, while running the report in the Report Manager, do we
have any limitation on number of parameters or specifically in number of drop
down parameters?
One of my report is having 35 parameters for the user to enter, it is
working fine in Designer's Preview mode, but when i deploy it to server and
when i run the report from the server, its not showing the output and no
errors also. If i reduce the number of parameters its working fine. Why is
this strange behaviour, your help is highly appreciated in this.
TIA,
Sen KHi
it seems to me that the limitation is not in the number of parameters but in
the length of the calling sql statement. I don't know how to explain
exactly... I had about 14 parameters with dependencies among them. And one
value in the combobox was extremely long. Report behaved in the same way. The
only workaround i found - place a shorter name for this long value and in the
main stored procedure simply "decode" it to its longer variant.
Ailil
"Sen K" wrote:
> Hi,
> I would like to know, while running the report in the Report Manager, do we
> have any limitation on number of parameters or specifically in number of drop
> down parameters?
> One of my report is having 35 parameters for the user to enter, it is
> working fine in Designer's Preview mode, but when i deploy it to server and
> when i run the report from the server, its not showing the output and no
> errors also. If i reduce the number of parameters its working fine. Why is
> this strange behaviour, your help is highly appreciated in this.
> TIA,
> Sen K
I would like to know, while running the report in the Report Manager, do we
have any limitation on number of parameters or specifically in number of drop
down parameters?
One of my report is having 35 parameters for the user to enter, it is
working fine in Designer's Preview mode, but when i deploy it to server and
when i run the report from the server, its not showing the output and no
errors also. If i reduce the number of parameters its working fine. Why is
this strange behaviour, your help is highly appreciated in this.
TIA,
Sen KHi
it seems to me that the limitation is not in the number of parameters but in
the length of the calling sql statement. I don't know how to explain
exactly... I had about 14 parameters with dependencies among them. And one
value in the combobox was extremely long. Report behaved in the same way. The
only workaround i found - place a shorter name for this long value and in the
main stored procedure simply "decode" it to its longer variant.
Ailil
"Sen K" wrote:
> Hi,
> I would like to know, while running the report in the Report Manager, do we
> have any limitation on number of parameters or specifically in number of drop
> down parameters?
> One of my report is having 35 parameters for the user to enter, it is
> working fine in Designer's Preview mode, but when i deploy it to server and
> when i run the report from the server, its not showing the output and no
> errors also. If i reduce the number of parameters its working fine. Why is
> this strange behaviour, your help is highly appreciated in this.
> TIA,
> Sen K
Limitation on Multiple parameters with Multi-Select and default values
Dear Anyone,
I created 6 parameters in my report. Each of these parameters are multi-select enabled. Each of their available values comes from a data set. Their default values also comes from a data set in order to have the select all check box selected by default.
My problem is only the first 2 parameters gets to have the select all selected. And the rest just have blank values. I was wondering if RS parameters have some sort of limitation that I cannot enable data set default values in all of my parameters.
Thanks,
JosephThere seems to be a bug in RS2005 in the parameters section. Filter the data set your using for the parameters to not include blanks. This is aparently whats causing your problem.|||
Hello,
I need to create reports with parameters that allow for multi-selection values.
How can I make them multi-select enabled?
Appreciate your reponse.
SK
Limitation on Multiple parameters with Multi-Select and default values
Dear Anyone,
I created 6 parameters in my report. Each of these parameters are multi-select enabled. Each of their available values comes from a data set. Their default values also comes from a data set in order to have the select all check box selected by default.
My problem is only the first 2 parameters gets to have the select all selected. And the rest just have blank values. I was wondering if RS parameters have some sort of limitation that I cannot enable data set default values in all of my parameters.
Thanks,
JosephThere seems to be a bug in RS2005 in the parameters section. Filter the data set your using for the parameters to not include blanks. This is aparently whats causing your problem.|||
Hello,
I need to create reports with parameters that allow for multi-selection values.
How can I make them multi-select enabled?
Appreciate your reponse.
SK
Limitation on Conn.Execute in classic .ASP & ADO with SQL Server
Hello,
I have a project (using classic ASP & SQL Server) which adds one execute sql statement at a time to a temporary array, and then I join that array with a chr(30) (record separator), to a string variable called strSQL. I then run the following line of code:
conn.execute(strSQL)
I was wondering if there was any limitation to how large the strSQL variable can be? Reason I ask is because thru log writes I can see all of my sql execute lines exist in the variable strSQL prior to running the "conn.execute(strSQL)" command; however, not all of the lines run at the time of execution. Remember, this bug only is occuring whenever I have say over 600 sql lines to execute.
My understanding is that there was no limitation on the size of the string strSQL; however, in the interest of getting the bug fixed quick enough, I decided to just run a loop for each sql statment and run "conn.execute(strSQL)" every 50 times. This, in turn, has solved the problem and I do save all of my data; however, my original bug still exists.
Does anyone know why I have to split the sql commands and execute.com every 50 times instead of just being able to do it once ?
Please let me know. Thanks in advance.It's probably your data provider that is limiting you. Which one are you using, OleDb for SQL Server (SQLOLEDB)? The batch separator for that provider is a semicolon (;) as far as I know. Maybe you'd have better luck with that? I am doubting it but it would be worth a try.
Terri|||I am using ADO thru an ASP page to connect to SQL Server. I have tried numerous separator's (comma,vertibar) with still no luck. That's why I ended up going with the chr(30).
Doing the loop and execute.conn every 50 times through the loop has seemed to definately fix the bug. I am still just curious as to why I couldn't execute the entire command immediately.
What is strange is that I can use ADO and write a general Visual Basic 6.0 application and execute the sql string just fine. Considering ADO is used in both the VB6.0 and .ASP applications you would think it would work?
Oh well, thanks for the thought. Have a good one.|||But which PROVIDER are you using? Are you using the same provider in your VB program that you are in your ASP application (ie, what does your connection string look like for both)? Is the same MDAC version on both machines?
Terri|||Terri,
Yes, MDAC 2.8 is on the web server using the .ASP & ADO connection. However, the VB Project I've created to test out this large sql string is only on my local machine. It does not use a web server or anything like that.|||Sorry forgot to mention that the execute statement is the same in both.
Execute.Conn sqlstr
Where "sqlstr" is representing the hundreds and hundreds of insert statements I need to execute.|||But which PROVIDER are you using? What does the Conn.ConnectionString look like?
Terri|||the connection string looks like:
"DRIVER=SQL Server;SERVER=ServerName;User ID=UserID;PASSWORD=PASSWORD;DATABASE=Database"
I hope this is what you are referring too. Sorry for the confusion.|||You're using the ODBC provider. Use the OleDB provider instead and see if it makes a difference, with semicolons (;) separating your commands.
Your connection string would look like this:
"Provider=sqloledb;Data Source=ServerName;Initial Catalog=Database;User Id=UserID;Password=PASSWORD;"
Seehttp://www.connectionstrings.com for more help on connection strings.
Terri
Limitation of updating views
Are there any limitations that can render a view "un-updatable"?There are a lot of restrictions, look for 'updatable view' in Books Online and you can find the limitations of updatable views
Limitation of sql standard
Hello there
In one of my clients who have SQL Standard has past the 2Gb data file.
In this office there are approxsimatly 80 users
Since then a lot of problems have happened in the database:
1. tables are locked more often and we need to reset the sql server at least
once a w
(Very bad)
2. when we update one data another data is being deleted
3. the workflow of the program has been damaged and all the applications
that use sql server (Access and VB)
become much more slower.
Does this sideffects are the result of the limitation of Sql standard? (up
to 2Gb and more then 50 users)
If so, can i get official documentation of microsoft about this limitation ,
so i can call my client and tell him that is need to upgrate the sql server
to Enterprise edition?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy,shalom
Database that has 2GB nowadays is really small.
I'd recommed you start tuning your queries ,making sure that you have
appropriate indexes define on the table
That has nothing to do ( in your case) with SQL Server Standatd Edition
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uhtv2iHDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hello there
> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at
> least once a w (Very bad)
> 2. when we update one data another data is being deleted
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
> If so, can i get official documentation of microsoft about this limitation
> , so i can call my client and tell him that is need to upgrate the sql
> server to Enterprise edition?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Roy Goldhammer wrote:
> Hello there
> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at le=
ast
> once a w (Very bad)
> 2. when we update one data another data is being deleted
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
> If so, can i get official documentation of microsoft about this limitatio=
n ,
> so i can call my client and tell him that is need to upgrate the sql serv=
er
> to Enterprise edition?
> --
> =F8=E5=F2=E9 =E2=E5=EC=E3=E4=EE=F8
> =F2=FA=E9=E3 =E4=F0=E3=F1=FA =FA=E5=EB=F0=E4
> =E8=EC' 03-5611606
> =F4=EC=E0' 050-7709399
> =E0=E9=EE=E9=E9=EC: roy@.atidsm.co.il
You are mistaken. There is no set limit of 2GB or 50 users for Standard
Editon. I'd say that 2GB is small for a Standard Edition installation.
The documented maximum capacities are here:
http://msdn.microsoft.com/library/d...en-us/architec=
/8_ar_ts_8dbn.asp
Performance issues are usually caused by poor design or maybe by
underspecified or poorly configured hardware. See:
http://support.microsoft.com/defaul...kb;en-us;224587
--=20
David Portas=20
SQL Server MVP=20
--|||> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at
> least once a w (Very bad)
What does "locked" mean? Can you describe the actual symptom? Are you
using optimistic concurrency? Are transactions being orphaned? Are you
running select * from huge table in the default isolation level?
> 2. when we update one data another data is being deleted
This sounds like an application problem. SQL Server is not nearly as adept
at corrupting data as Access once was. There's no way that SQL Server is
suddenly deciding, "hey, your database is now bigger than 2GB, so I'm going
to update this row you told me about, and delete this other row over here
behind your back..."
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
Slow usually points to problems in the design, such as indexing strategies,
normalization, etc. I have 800GB databases that are pretty fast.
None of these problems could possibly have been caused by merely passing a
2GB size threshold. As Uri commented, 2GB is pretty small by today's
standards. 80 users is not all that impressive, either.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
There is no such limitation in SQL Standard. I have much larger databases
with more people using them and the symptoms are not as you describe. My
guess is that it comes down to the design.|||I'm not sure, but I think SQL Server 2000 Standard is limited to 2 GB of
RAM, but that's still as large as the total size of your database. Other
than that, I don't know of any scalability limitations with the Standard
edition. You mentioned 80 users of this system, but how many average and
peak (concurrent) users?
The symptoms that you are describing below (blocking processes, unexplained
deletion of rows, "damaged workflow?"), actually sound like issues with the
application or query / stored procedure design not the scalability of the
database. Using techniques from the links below, take a more systematic
approach to auditing the performance of your database server and analyzing
the programming of your applications.
Checklist: SQL Server Performance
http://msdn.microsoft.com/library/d...
etcheck08.asp
How to Perform a SQL Server Performance Audit
http://www.sql-server-performance.c...mance_audit.asp
Optimizing Performance in SQL Server Solutions
http://msdn.microsoft.com/SQL/2000/...rf/default.aspx
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Microsoft Access Performance FAQ
http://www.granite.ab.ca/access/performancefaq.htm
Specifically consider the following:
1. Are users running reporting type queries (daily / month end close,
Excel pivot tables, OLAP, etc.) against the operational transaction
processing (OLTP) database? If so, then consider implementing a seperate
database (replicated or restored from backup) for the purpose of running
reports.
2. Use the Show Execution Plan of Query Analyzer to determine if your
indexes optimized.
3. Are your stored procedures using cursors? If so, then consider finding
set based alternatives.
4. Are your applications using recorsets with pessimistic record locking?
If so, then consider how this is impacting concurrency with other processes.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uhtv2iHDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hello there
> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at
> least once a w (Very bad)
> 2. when we update one data another data is being deleted
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
> If so, can i get official documentation of microsoft about this limitation
> , so i can call my client and tell him that is need to upgrate the sql
> server to Enterprise edition?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
In one of my clients who have SQL Standard has past the 2Gb data file.
In this office there are approxsimatly 80 users
Since then a lot of problems have happened in the database:
1. tables are locked more often and we need to reset the sql server at least
once a w
(Very bad)2. when we update one data another data is being deleted
3. the workflow of the program has been damaged and all the applications
that use sql server (Access and VB)
become much more slower.
Does this sideffects are the result of the limitation of Sql standard? (up
to 2Gb and more then 50 users)
If so, can i get official documentation of microsoft about this limitation ,
so i can call my client and tell him that is need to upgrate the sql server
to Enterprise edition?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy,shalom
Database that has 2GB nowadays is really small.
I'd recommed you start tuning your queries ,making sure that you have
appropriate indexes define on the table
That has nothing to do ( in your case) with SQL Server Standatd Edition
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uhtv2iHDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hello there
> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at
> least once a w
(Very bad)> 2. when we update one data another data is being deleted
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
> If so, can i get official documentation of microsoft about this limitation
> , so i can call my client and tell him that is need to upgrate the sql
> server to Enterprise edition?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Roy Goldhammer wrote:
> Hello there
> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at le=
ast
> once a w
(Very bad)> 2. when we update one data another data is being deleted
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
> If so, can i get official documentation of microsoft about this limitatio=
n ,
> so i can call my client and tell him that is need to upgrate the sql serv=
er
> to Enterprise edition?
> --
> =F8=E5=F2=E9 =E2=E5=EC=E3=E4=EE=F8
> =F2=FA=E9=E3 =E4=F0=E3=F1=FA =FA=E5=EB=F0=E4
> =E8=EC' 03-5611606
> =F4=EC=E0' 050-7709399
> =E0=E9=EE=E9=E9=EC: roy@.atidsm.co.il
You are mistaken. There is no set limit of 2GB or 50 users for Standard
Editon. I'd say that 2GB is small for a Standard Edition installation.
The documented maximum capacities are here:
http://msdn.microsoft.com/library/d...en-us/architec=
/8_ar_ts_8dbn.asp
Performance issues are usually caused by poor design or maybe by
underspecified or poorly configured hardware. See:
http://support.microsoft.com/defaul...kb;en-us;224587
--=20
David Portas=20
SQL Server MVP=20
--|||> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at
> least once a w
(Very bad)What does "locked" mean? Can you describe the actual symptom? Are you
using optimistic concurrency? Are transactions being orphaned? Are you
running select * from huge table in the default isolation level?
> 2. when we update one data another data is being deleted
This sounds like an application problem. SQL Server is not nearly as adept
at corrupting data as Access once was. There's no way that SQL Server is
suddenly deciding, "hey, your database is now bigger than 2GB, so I'm going
to update this row you told me about, and delete this other row over here
behind your back..."
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
Slow usually points to problems in the design, such as indexing strategies,
normalization, etc. I have 800GB databases that are pretty fast.
None of these problems could possibly have been caused by merely passing a
2GB size threshold. As Uri commented, 2GB is pretty small by today's
standards. 80 users is not all that impressive, either.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
There is no such limitation in SQL Standard. I have much larger databases
with more people using them and the symptoms are not as you describe. My
guess is that it comes down to the design.|||I'm not sure, but I think SQL Server 2000 Standard is limited to 2 GB of
RAM, but that's still as large as the total size of your database. Other
than that, I don't know of any scalability limitations with the Standard
edition. You mentioned 80 users of this system, but how many average and
peak (concurrent) users?
The symptoms that you are describing below (blocking processes, unexplained
deletion of rows, "damaged workflow?"), actually sound like issues with the
application or query / stored procedure design not the scalability of the
database. Using techniques from the links below, take a more systematic
approach to auditing the performance of your database server and analyzing
the programming of your applications.
Checklist: SQL Server Performance
http://msdn.microsoft.com/library/d...
etcheck08.asp
How to Perform a SQL Server Performance Audit
http://www.sql-server-performance.c...mance_audit.asp
Optimizing Performance in SQL Server Solutions
http://msdn.microsoft.com/SQL/2000/...rf/default.aspx
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Microsoft Access Performance FAQ
http://www.granite.ab.ca/access/performancefaq.htm
Specifically consider the following:
1. Are users running reporting type queries (daily / month end close,
Excel pivot tables, OLAP, etc.) against the operational transaction
processing (OLTP) database? If so, then consider implementing a seperate
database (replicated or restored from backup) for the purpose of running
reports.
2. Use the Show Execution Plan of Query Analyzer to determine if your
indexes optimized.
3. Are your stored procedures using cursors? If so, then consider finding
set based alternatives.
4. Are your applications using recorsets with pessimistic record locking?
If so, then consider how this is impacting concurrency with other processes.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uhtv2iHDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hello there
> In one of my clients who have SQL Standard has past the 2Gb data file.
> In this office there are approxsimatly 80 users
> Since then a lot of problems have happened in the database:
> 1. tables are locked more often and we need to reset the sql server at
> least once a w
(Very bad)> 2. when we update one data another data is being deleted
> 3. the workflow of the program has been damaged and all the applications
> that use sql server (Access and VB)
> become much more slower.
> Does this sideffects are the result of the limitation of Sql standard? (up
> to 2Gb and more then 50 users)
> If so, can i get official documentation of microsoft about this limitation
> , so i can call my client and tell him that is need to upgrate the sql
> server to Enterprise edition?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
Limitation of RB?
If there is a need to add a new column to the table, how would you do it to add it to an EXISTING report using RB? I don't want to create a new RB report. I want to add the new column to an existing RB report.
p.s. Anybody knows how to replace a Report Model on the report server when re-deploying it? Currently I manually delete the Report Model and re-deploy it onto the server. Otherwise, it gives me an error for duplicate IDs. Is there a switch that can be turned on? Thanks.
If there is a need to add a new column to the table, how would you do it to add it to an EXISTING report using RB? I don't want to create a new RB report. I want to add the new column to an existing RB report.
-- You can open an existing report and edit it. Select the Open icon in the upper left corner and navigate to the report in question with Report Builder.
p.s. Anybody knows how to replace a Report Model on the report server when re-deploying it? Currently I manually delete the Report Model and re-deploy it onto the server. Otherwise, it gives me an error for duplicate IDs. Is there a switch that can be turned on? Thanks.
-- If the ID of the model has changed, you cannot overwrite the model because it has the potential to break all existing reports. You would have to delete and replace the model. This is a safe guard for existing reports.
|||This is what I did:
-On the Data Source View, right click on the table and select refresh. The new field will be brought into the DSV
-Right click in the Report Mode atributes, New - Source Field. Bind to the new field in Property.
-Rebuild and redeploy the Report Model.
-Open the report using RB. Then drag the new field onto the report.
-Run it. Voila! The new field is in there!
Limitation of having column text column
Hi,
I am having table with text column which holds table creation scripts whose
lenght exceeding 8000 charactres..Is there is anyway i can take this value
one by one and execute it dynamically in another db..
Logig should be as follows..
cursor for select text from table
for each value
exec 'text value'
end
deallocate cursor..
Regards
rect
Yes, you can pull off pieces of a text field using the TextPTR function...
Search for this in Books on line and there are some examples...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:A98A0AED-4FCB-49A1-B1EB-9CCD849FF9ED@.microsoft.com...
> Hi,
> I am having table with text column which holds table creation scripts
whose
> lenght exceeding 8000 charactres..Is there is anyway i can take this value
> one by one and execute it dynamically in another db..
> Logig should be as follows..
> cursor for select text from table
> for each value
> exec 'text value'
> end
> deallocate cursor..
> Regards
> rect
I am having table with text column which holds table creation scripts whose
lenght exceeding 8000 charactres..Is there is anyway i can take this value
one by one and execute it dynamically in another db..
Logig should be as follows..
cursor for select text from table
for each value
exec 'text value'
end
deallocate cursor..
Regards
rect
Yes, you can pull off pieces of a text field using the TextPTR function...
Search for this in Books on line and there are some examples...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:A98A0AED-4FCB-49A1-B1EB-9CCD849FF9ED@.microsoft.com...
> Hi,
> I am having table with text column which holds table creation scripts
whose
> lenght exceeding 8000 charactres..Is there is anyway i can take this value
> one by one and execute it dynamically in another db..
> Logig should be as follows..
> cursor for select text from table
> for each value
> exec 'text value'
> end
> deallocate cursor..
> Regards
> rect
Limitation of having column text column
Hi,
I am having table with text column which holds table creation scripts whose
lenght exceeding 8000 charactres..Is there is anyway i can take this value
one by one and execute it dynamically in another db..
Logig should be as follows..
cursor for select text from table
for each value
exec 'text value'
end
deallocate cursor..
Regards
rectYes, you can pull off pieces of a text field using the TextPTR function...
Search for this in Books on line and there are some examples...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:A98A0AED-4FCB-49A1-B1EB-9CCD849FF9ED@.microsoft.com...
> Hi,
> I am having table with text column which holds table creation scripts
whose
> lenght exceeding 8000 charactres..Is there is anyway i can take this value
> one by one and execute it dynamically in another db..
> Logig should be as follows..
> cursor for select text from table
> for each value
> exec 'text value'
> end
> deallocate cursor..
> Regards
> rect
I am having table with text column which holds table creation scripts whose
lenght exceeding 8000 charactres..Is there is anyway i can take this value
one by one and execute it dynamically in another db..
Logig should be as follows..
cursor for select text from table
for each value
exec 'text value'
end
deallocate cursor..
Regards
rectYes, you can pull off pieces of a text field using the TextPTR function...
Search for this in Books on line and there are some examples...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:A98A0AED-4FCB-49A1-B1EB-9CCD849FF9ED@.microsoft.com...
> Hi,
> I am having table with text column which holds table creation scripts
whose
> lenght exceeding 8000 charactres..Is there is anyway i can take this value
> one by one and execute it dynamically in another db..
> Logig should be as follows..
> cursor for select text from table
> for each value
> exec 'text value'
> end
> deallocate cursor..
> Regards
> rect
Limitation of having column text column
Hi,
I am having table with text column which holds table creation scripts whose
lenght exceeding 8000 charactres..Is there is anyway i can take this value
one by one and execute it dynamically in another db..
Logig should be as follows..
cursor for select text from table
for each value
exec 'text value'
end
deallocate cursor..
Regards
rectYes, you can pull off pieces of a text field using the TextPTR function...
Search for this in Books on line and there are some examples...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:A98A0AED-4FCB-49A1-B1EB-9CCD849FF9ED@.microsoft.com...
> Hi,
> I am having table with text column which holds table creation scripts
whose
> lenght exceeding 8000 charactres..Is there is anyway i can take this value
> one by one and execute it dynamically in another db..
> Logig should be as follows..
> cursor for select text from table
> for each value
> exec 'text value'
> end
> deallocate cursor..
> Regards
> rect
I am having table with text column which holds table creation scripts whose
lenght exceeding 8000 charactres..Is there is anyway i can take this value
one by one and execute it dynamically in another db..
Logig should be as follows..
cursor for select text from table
for each value
exec 'text value'
end
deallocate cursor..
Regards
rectYes, you can pull off pieces of a text field using the TextPTR function...
Search for this in Books on line and there are some examples...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:A98A0AED-4FCB-49A1-B1EB-9CCD849FF9ED@.microsoft.com...
> Hi,
> I am having table with text column which holds table creation scripts
whose
> lenght exceeding 8000 charactres..Is there is anyway i can take this value
> one by one and execute it dynamically in another db..
> Logig should be as follows..
> cursor for select text from table
> for each value
> exec 'text value'
> end
> deallocate cursor..
> Regards
> rect
Limitation of 1023 Characters
I am using ASP to query a SQL Server 2000 database.
Whenever I try to insert large pieces of data (column is
declared as varchar), the data allowed is limited to 1023
characters. I have installed SP3, and still the same
error. It is not down to any syntax in the cell as trying
to insert 1024 instances of "b" (bbbbb etc. etc.) will
error on the insertion of the 1024th. I am sure this must
be a configuration conflict somewhere.
Anybody out there able to help me rectify this?
Many thanks
Martin Burford> Whenever I try to insert large pieces of data (column is
> declared as varchar),
VARCHAR(what?)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Sorry. Varchar(4000) in this case.
Martin
>--Original Message--
>> Whenever I try to insert large pieces of data (column
is
>> declared as varchar),
>VARCHAR(what?)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||All right, so can you show the statement that ASP is trying to run (e.g. if
you are using conn.execute sqlStatement, use response.write sqlStatement
instead, and show us the result).
Also, the exact error message might be helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> Sorry. Varchar(4000) in this case.
> Martin
> >--Original Message--
> >> Whenever I try to insert large pieces of data (column
> is
> >> declared as varchar),
> >
> >VARCHAR(what?)
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >.
> >|||I think I have actually determined the error. I was
entering my data through Enterprise Manager, which I'm
guessing can't accept more than a byte of info (1,024
chars a bit of a coincedence). Entering the data through
INSERT statements in Query Analyzer works upto 4000 chars
which is fine. This however causes a slight problem...is
there an easier way rather than creating custom insert
statements for each record? Otherwise, I'll just create a
basic admin area interface, and insert the code through
submitted form values (textareas).
Many thanks
Martin Burford
>--Original Message--
>All right, so can you show the statement that ASP is
trying to run (e.g. if
>you are using conn.execute sqlStatement, use
response.write sqlStatement
>instead, and show us the result).
>Also, the exact error message might be helpful.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
>> Sorry. Varchar(4000) in this case.
>> Martin
>> >--Original Message--
>> >> Whenever I try to insert large pieces of data
(column
>> is
>> >> declared as varchar),
>> >
>> >VARCHAR(what?)
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >http://www.aspfaq.com/
>> >
>> >
>> >.
>> >
>
>.
>|||> I think I have actually determined the error. I was
> entering my data through Enterprise Manager,
Don't do that! Enterprise Manager should be used for management, not data
entry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||http://www.aspfaq.com/search.asp?q=enterprise+manager+1023+characters
:-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Martin Burford" <martin_burford@.hotmail.com> wrote in message
news:065101c3c314$97ee3980$a401280a@.phx.gbl...
> I think I have actually determined the error. I was
> entering my data through Enterprise Manager, which I'm
> guessing can't accept more than a byte of info (1,024
> chars a bit of a coincedence). Entering the data through
> INSERT statements in Query Analyzer works upto 4000 chars
> which is fine. This however causes a slight problem...is
> there an easier way rather than creating custom insert
> statements for each record? Otherwise, I'll just create a
> basic admin area interface, and insert the code through
> submitted form values (textareas).
> Many thanks
> Martin Burford
> >--Original Message--
> >All right, so can you show the statement that ASP is
> trying to run (e.g. if
> >you are using conn.execute sqlStatement, use
> response.write sqlStatement
> >instead, and show us the result).
> >
> >Also, the exact error message might be helpful.
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> >> Sorry. Varchar(4000) in this case.
> >>
> >> Martin
> >>
> >> >--Original Message--
> >> >> Whenever I try to insert large pieces of data
> (column
> >> is
> >> >> declared as varchar),
> >> >
> >> >VARCHAR(what?)
> >> >
> >> >--
> >> >Aaron Bertrand
> >> >SQL Server MVP
> >> >http://www.aspfaq.com/
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Whenever I try to insert large pieces of data (column is
declared as varchar), the data allowed is limited to 1023
characters. I have installed SP3, and still the same
error. It is not down to any syntax in the cell as trying
to insert 1024 instances of "b" (bbbbb etc. etc.) will
error on the insertion of the 1024th. I am sure this must
be a configuration conflict somewhere.
Anybody out there able to help me rectify this?
Many thanks
Martin Burford> Whenever I try to insert large pieces of data (column is
> declared as varchar),
VARCHAR(what?)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Sorry. Varchar(4000) in this case.
Martin
>--Original Message--
>> Whenever I try to insert large pieces of data (column
is
>> declared as varchar),
>VARCHAR(what?)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||All right, so can you show the statement that ASP is trying to run (e.g. if
you are using conn.execute sqlStatement, use response.write sqlStatement
instead, and show us the result).
Also, the exact error message might be helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> Sorry. Varchar(4000) in this case.
> Martin
> >--Original Message--
> >> Whenever I try to insert large pieces of data (column
> is
> >> declared as varchar),
> >
> >VARCHAR(what?)
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >.
> >|||I think I have actually determined the error. I was
entering my data through Enterprise Manager, which I'm
guessing can't accept more than a byte of info (1,024
chars a bit of a coincedence). Entering the data through
INSERT statements in Query Analyzer works upto 4000 chars
which is fine. This however causes a slight problem...is
there an easier way rather than creating custom insert
statements for each record? Otherwise, I'll just create a
basic admin area interface, and insert the code through
submitted form values (textareas).
Many thanks
Martin Burford
>--Original Message--
>All right, so can you show the statement that ASP is
trying to run (e.g. if
>you are using conn.execute sqlStatement, use
response.write sqlStatement
>instead, and show us the result).
>Also, the exact error message might be helpful.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
>> Sorry. Varchar(4000) in this case.
>> Martin
>> >--Original Message--
>> >> Whenever I try to insert large pieces of data
(column
>> is
>> >> declared as varchar),
>> >
>> >VARCHAR(what?)
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >http://www.aspfaq.com/
>> >
>> >
>> >.
>> >
>
>.
>|||> I think I have actually determined the error. I was
> entering my data through Enterprise Manager,
Don't do that! Enterprise Manager should be used for management, not data
entry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||http://www.aspfaq.com/search.asp?q=enterprise+manager+1023+characters
:-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Martin Burford" <martin_burford@.hotmail.com> wrote in message
news:065101c3c314$97ee3980$a401280a@.phx.gbl...
> I think I have actually determined the error. I was
> entering my data through Enterprise Manager, which I'm
> guessing can't accept more than a byte of info (1,024
> chars a bit of a coincedence). Entering the data through
> INSERT statements in Query Analyzer works upto 4000 chars
> which is fine. This however causes a slight problem...is
> there an easier way rather than creating custom insert
> statements for each record? Otherwise, I'll just create a
> basic admin area interface, and insert the code through
> submitted form values (textareas).
> Many thanks
> Martin Burford
> >--Original Message--
> >All right, so can you show the statement that ASP is
> trying to run (e.g. if
> >you are using conn.execute sqlStatement, use
> response.write sqlStatement
> >instead, and show us the result).
> >
> >Also, the exact error message might be helpful.
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> >> Sorry. Varchar(4000) in this case.
> >>
> >> Martin
> >>
> >> >--Original Message--
> >> >> Whenever I try to insert large pieces of data
> (column
> >> is
> >> >> declared as varchar),
> >> >
> >> >VARCHAR(what?)
> >> >
> >> >--
> >> >Aaron Bertrand
> >> >SQL Server MVP
> >> >http://www.aspfaq.com/
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Limitation of # cubes inside a base Analysis Services
Does anyone
have any idea which is the limitation of # cubes inside a base Analysis Services,
because I created 50 cubes, and when I tried to connect to the base, i was
enable to do that because the CPU was at
100 %.
I think
that is a problem when analysis services tried to read the xmla and print all the cubes.
Tks
Well. There is a point where number of cubes and size of your data requre you to go and get a bigger machine.
It would be wrong to assume you can create any number of cubes and expect from Analysis Services not to use more CPU or memory trying to handle them all. The question what is reasonable. If you think that you got poweful machine with multiple processors and fast I/O and you see Analysis Services cannot handle large number of objects, there could be somehting. As a reference you can use Project REAL that handle large number of objects. http://www.microsoft.com/sql/solutions/bi/projectreal.mspx
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
Hallo Calacean,
the problem, you have accounted is not a SSAS with its limitations, but the client you use.
Some clients read copletely all metadata (available cubes, dimensins, hierarhies, levels, measures etc.) immediately after connection to the database.
Take a time to monitor with the SQL Profiler what commands and queries are sent from your client to the sever. You do find some interesting for you.
|||
Thanks, is
very useful, but i have another question if i don't bother you.
I have a data warehouse with 50 mil. dates. And i have 6 measures group with
relations many 2 many. When i query the cube, first is very slow( and i design aggregations
for all the measures), and second (I have proactive caching setup), when i want to
modify some data from the tables of proactive caching , the proactive caching
is working very god, but it decrease very much the performance of the server,
and the performance of the applications. I forget to mention that I have P4
2Ghz(2CPU), 2GB Memory, and I run Sql Server 2005 and Analysis Services in the
same server.
Limitation in number of fields a Table published by push merge Replication may have
SQL 2000 SP3: There is a table with more than 254 Fields in it. (please
don't ask why :-) )
we observed, that replication on fields which are beyond the magic limit of
254 simply do not replicate.
( Enterprise manager says Replication takes place, but data do not show up
at destination.
Is this a known documented limitation ? Is it a bug with perhaps an
existing patch?
This is a known limitation. I'd consider splitting up the table into 2
separate related tables related and replicate them both. This can be made
transparent to the users by presenting them with a view which has the
original table's name and an InsteadOf trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I consider this to be a bug:
The Enterprisemanager accepts the publikation,
The replication Monitor says "1 Insert Done"
but the data is lost.
I would have no objections if the enterprise manager said:
"are you mad this is nothing you should publish I can't publish tables that
are so large / task rejected"
But If It accepts the task It should accomplish the task.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uieZiLGFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> This is a known limitation. I'd consider splitting up the table into 2
> separate related tables related and replicate them both. This can be made
> transparent to the users by presenting them with a view which has the
> original table's name and an InsteadOf trigger.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
don't ask why :-) )
we observed, that replication on fields which are beyond the magic limit of
254 simply do not replicate.
( Enterprise manager says Replication takes place, but data do not show up
at destination.
Is this a known documented limitation ? Is it a bug with perhaps an
existing patch?
This is a known limitation. I'd consider splitting up the table into 2
separate related tables related and replicate them both. This can be made
transparent to the users by presenting them with a view which has the
original table's name and an InsteadOf trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I consider this to be a bug:
The Enterprisemanager accepts the publikation,
The replication Monitor says "1 Insert Done"
but the data is lost.
I would have no objections if the enterprise manager said:
"are you mad this is nothing you should publish I can't publish tables that
are so large / task rejected"
But If It accepts the task It should accomplish the task.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uieZiLGFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> This is a known limitation. I'd consider splitting up the table into 2
> separate related tables related and replicate them both. This can be made
> transparent to the users by presenting them with a view which has the
> original table's name and an InsteadOf trigger.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Limitation for UDAs
When user-defined aggregates are discussed, there are two usage scenarios that are often mentioned. One is calculating the median value of a set of numbers, and the other is concatenating a set of strings into a comma-separated list.
However, I can not see how it would be possible to create an aggregate function for either of these problems. Because MaxSizeBytes can never be more than 8000 bytes these functions would only be able to operate on a limited number of values, since all values need to be kept for the entire execution. I have added this as a suggestion at the Product Feedback centre but have not yet received any feedback.
Or am I just missing something here, something that I have misunderstood?You aren't missing anything. For the reason you point out, neither of these examples are particularly good. (Of course, string concatenation won't work for more reasons than the size limit: we also can't guarantee the order of the concatenation right now.) This may change in future versions of SQL Server, but for now it's a limitation we're stuck with.
Regarding string concatenation I generally agree it is not a particularly useful operation. But if it is to be done I strongly feel that it should be implemented just the way it would be today, apart from the size limitation. Guaranteeing any specific order of the values would be undesirable and confusing behaviour, unless the specific order is specified by the query executing the aggregate. In fact, if size was unlimited it would of course be possible for the UDA implementor to sort the rows before returning, if they wanted a specific order.|||
However, I can not see how it would be possible to create an aggregate function for either of these problems. Because MaxSizeBytes can never be more than 8000 bytes these functions would only be able to operate on a limited number of values, since all values need to be kept for the entire execution. I have added this as a suggestion at the Product Feedback centre but have not yet received any feedback.
Or am I just missing something here, something that I have misunderstood?You aren't missing anything. For the reason you point out, neither of these examples are particularly good. (Of course, string concatenation won't work for more reasons than the size limit: we also can't guarantee the order of the concatenation right now.) This may change in future versions of SQL Server, but for now it's a limitation we're stuck with.
There are cases where UDAs could be used, but you're right that they cannot be aggregates which need to store all of the inputs until they're done. One example would be to find the bitwise 'or' or 'and' of a set of bitfields.
Incidently, the median of a set of items can be computed using a single T-SQL query. Assume you have a table 'sales' with the schema (city int, number int), you can find the median value of the 'number's for each 'city' by using the rank() function as follows:
select middles.city, avg(convert(float, number)) as median
from (
select city, floor((count(*) + 1) / 2.0) as lowmiddle, ceiling((count(*) + 1) / 2.0) as highmiddle
from sales
group by city
) as middles
join (
select city, number, rank() over (partition by city order by number) as rank
from sales
) as ranks
on (middles.city = ranks.city) and
((middles.lowmiddle = ranks.rank) or (middles.highmiddle = ranks.rank))
group by middles.city
Cheers,
- Isaac
Regarding string concatenation I generally agree it is not a particularly useful operation. But if it is to be done I strongly feel that it should be implemented just the way it would be today, apart from the size limitation. Guaranteeing any specific order of the values would be undesirable and confusing behaviour, unless the specific order is specified by the query executing the aggregate. In fact, if size was unlimited it would of course be possible for the UDA implementor to sort the rows before returning, if they wanted a specific order.|||
Regarding order, what would be desirable is for the query writer to be able to specify the order in which items were input to an order-sensitive UDA. One can imagine cases in which order would be significant, but in which the state does not have to be maintained in the UDA if the input is guaranteed to be ordered. This could be a win.
Cheers,
-Isaac
Subscribe to:
Posts (Atom)