LIMIT problems: February 2012

Friday, February 24, 2012

Limitations in SQL Server 2005 Express

Is there any information on any limits in the facilities available in SQL
Server 2005 Express (compared with Standard edition)?
For example: database size, maximum number of users etc.
What features are not available in the Express edition?
Thanks,
Steve.
There is a 4GB limit for database size in SQL Server Express. As far as
number of users I do not think there is any limitation. Here is the complete
feature comparison chart for the different editions:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Hello,
Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
OF COST. SQL Express have below limitations:-
1. Maximum 4 GB database size
2. Allow only 1 GB RAM
3.Allow only 1 CPU
4. No Profiler
5. No SQL Agent to schedule jobs
The list goes on and on...
Thanks
Hari
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
> Is there any information on any limits in the facilities available in SQL
> Server 2005 Express (compared with Standard edition)?
> For example: database size, maximum number of users etc.
> What features are not available in the Express edition?
> Thanks,
> Steve.
>
|||Many thanks for your comments and the link to Ms.
Steve.
"Hari Prasad" wrote:

> Hello,
> Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
> OF COST. SQL Express have below limitations:-
> 1. Maximum 4 GB database size
> 2. Allow only 1 GB RAM
> 3.Allow only 1 CPU
> 4. No Profiler
> 5. No SQL Agent to schedule jobs
> The list goes on and on...
> Thanks
> Hari
>
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
>
>

Limitations in SQL Server 2005 Express

Is there any information on any limits in the facilities available in SQL
Server 2005 Express (compared with Standard edition)?
For example: database size, maximum number of users etc.
What features are not available in the Express edition?
Thanks,
Steve.There is a 4GB limit for database size in SQL Server Express. As far as
number of users I do not think there is any limitation. Here is the complete
feature comparison chart for the different editions:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hello,
Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
OF COST. SQL Express have below limitations:-
1. Maximum 4 GB database size
2. Allow only 1 GB RAM
3.Allow only 1 CPU
4. No Profiler
5. No SQL Agent to schedule jobs
The list goes on and on...
Thanks
Hari
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
> Is there any information on any limits in the facilities available in SQL
> Server 2005 Express (compared with Standard edition)?
> For example: database size, maximum number of users etc.
> What features are not available in the Express edition?
> Thanks,
> Steve.
>|||Many thanks for your comments and the link to Ms.
Steve.
--
"Hari Prasad" wrote:
> Hello,
> Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
> OF COST. SQL Express have below limitations:-
> 1. Maximum 4 GB database size
> 2. Allow only 1 GB RAM
> 3.Allow only 1 CPU
> 4. No Profiler
> 5. No SQL Agent to schedule jobs
> The list goes on and on...
> Thanks
> Hari
>
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
> > Is there any information on any limits in the facilities available in SQL
> > Server 2005 Express (compared with Standard edition)?
> >
> > For example: database size, maximum number of users etc.
> > What features are not available in the Express edition?
> >
> > Thanks,
> > Steve.
> >
>
>

Limitations in Report Builder?

I'm using Report Builder. I have a NameAddress entity that I want to have printed on the report. There is only one "Drag and drop column fields" box on the "design report" screen. Every field I drop in there goes from left to right. Is there a way to reposition the headers/fields, so that I can have something in the following format:

First Name: xxxxxx Last Name:

Address 1

xxxxx

Address 2

xxxx

City: xxxxx State: xx Zip: xxxxx

Everything I drop in there goes from left to right. I want to position things "anywhere" on the screen, and be able to split the column headers apart from the data.

Basically, how do I get this tool to work like access report designer. Just a simple access report?

Thanks,

Michael

Background:

I have upsized an Access DB into sql server 2005.

I created a model and published it, and now I am trying to simulate a user's day to day ad-hoc report creating activities. They previously used access 2003 reports to do this.

We want to users to use Report Builder, because we don't want them in bi-dev.

Report Builder has only basic layout support in this release. Multi-row or freeform detail layout is not supported, although it is on the list for a future release.

|||

Thanks.

Limitations in a query !

Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

I've read at some documentation that there is a limitation for Columns
in a table and i.e. we can have only 1024 columns per table, is this
true?
Plz help me !!

Thanx in advance.

Kind Regards,
HarryOn 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
>on "IN" clause in select query.
>for example :
>"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
>('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.

Quote:

Originally Posted by

>I've read at some documentation that there is a limitation for Columns
>in a table and i.e. we can have only 1024 columns per table, is this
>true?

Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.

Quote:

Originally Posted by

>Plz help me !!
>
>Thanx in advance.
>
>Kind Regards,
>Harry

Roy Harvey
Beacon Falls, CT|||Thanx alot Roy. :)

Roy Harvey wrote:

Quote:

Originally Posted by

On 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:
>

Quote:

Originally Posted by

>
There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.
>

Quote:

Originally Posted by

I've read at some documentation that there is a limitation for Columns
in a table and i.e. we can have only 1024 columns per table, is this
true?

>
Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.
>

Quote:

Originally Posted by

Plz help me !!

Thanx in advance.

Kind Regards,
Harry

>
Roy Harvey
Beacon Falls, CT

|||Harsha, See this post

http://groups.google.com/group/comp...2da17b97723fe15
harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

Thanx alot Roy. :)
>
Roy Harvey wrote:

Quote:

Originally Posted by

On 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.

Quote:

Originally Posted by

>I've read at some documentation that there is a limitation for Columns
>in a table and i.e. we can have only 1024 columns per table, is this
>true?

Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.

Quote:

Originally Posted by

>Plz help me !!
>
>Thanx in advance.
>
>Kind Regards,
>Harry

Roy Harvey
Beacon Falls, CT

|||(harsha.21aug@.gmail.com) writes:

Quote:

Originally Posted by

I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

To add to Roy's response, be aware of that when the list grows very
long (1000 elements) compile times on SQL 2000 can be absymal. SQL 2005
appears to do better in this regard.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

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.

Limitation on Multiple parameters with Multi-Select and default values

Dear Anyone,

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.

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 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

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.

-- 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.

-- 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

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

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

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/
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

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)
>

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.

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

|||Isaac, thanks for the answer and the nice query for median. It is definitely a lot better than the alternatives that are available in earlier versions of SQL Server. It would still not be able to match an aggregate in performance though.
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

Limitaions of MSDE

Hi,
Is the MSDE database still limited to 2GB. If yes is that the size of the .mdf file ?
Thanks,
-VJHi,
I believe that it is the mdf file (I don't know why they would car about how large you log files are!). I assume that you know that size isn't the only limitation to MSDE. Try searching the MS site, as I know they have a section devoted to MSDE with some good links to available enterprise manager work-alikes, etc... Might also want to look at the express vesrion of SQL Server 2005. You can also search these forums for info on that. BRN..|||MSDE(micorosft data engine) is limited to 2gig in capacity, ten concurrent write users and with caching 2500 or more read users not very sure. You could get the developer edition and install it as a named instance and register the MSDE so you can manage it with Enterprise manager.
MDF(microsoft data file) is not the log file it is the data file, the log file is LDF(log data file) both files are in the Data subfolder in Microsoft SQL Server folder under programs.
And there is very little need to grow 2gig log file because you can always create a job to run DBCC ShrinkFile after long operations that can grow the log file. DBCC shrinkFile returns space less what SQL Server calls Active log. Hope this helps.|||

thanks guys. I got what I needed. Chreers.

-VJ

|||Hi,
I'm sure the original poster appreceates the info you were able to pass along re: MDSE. BTW, I (and I'm guessing the OP), know that the .ldf is the (a), log file for SQL Server/MSDE. My comment about MS not caring about how large a log file you could have implied that the size restriction must be on the .mdf (data), file. It's been a while since I set up my MSDE installation, but I recall that I had a tough time getting the mdf and ldf assigned to different drives. The MSDE didn't make that simple. I'm sure you know how much more effecient it is when the two file types are on seperate drives - even if both are on a IDE controller.
BTW, can you have multiple named instances of the server with the developer edition of SQL Server 2000, on the same licence? That would help in some testing I was planning. Thanks, BRN..|||Hi Brian,
I am sorry did not intend to offend you and I think you can install multiple licenses but not multiple instances because the developer edition is only $37.00 on the web. Try the thread below for more info. Hope this helps.
http://forums.asp.net/1076159/ShowPost.aspx|||

Hi,

Thanks for the link; I'll check it out. BRN..

Limit? Rowid?

Hi,
I am writing a "universal" db-Interface and just finished the postgres
and mysql part. I got somehow stuck on the MS SQL Server part (via
ODBC).
I have no access to the SQL Server CD or the machine where it is
running, I access the database only via a VPN tunnel and ODBC.
My question is:
How can I use something like the "LIMIT " command?
How can I get the actual "_rowid" (not the primary key, but the row-id
of the result set.)
Something like:
Select * from article Limit 5,10
Select _rowid from article where id = "XYZ-123"
Update article set amount = 15 where _rowid = 23
And how to get the table definition, something like "Describe article;"
oder "Show create table article;" ?
Is there a simple online reference for this SQL dialect that SQL Server
via ODBC uses?
Thank you for your help
RobertRobert
What vesrion are you using?
SQL Server 2005
There is new function ROW_NUMBER()
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
columns
FROM Table
) AS Der WHERE rownum BETWEEN 5 AND 10
SQL Server 2000
SELECT * FROM Table WHERE (SELECT COUNT(*) FROM Table t WHERE
t.PK<=Table.PK)
BETWEEN 5 AND 10
pk-Primary keys
Actualy if it does not help you ,please post DDL+ sample data + an expected
result
"Robert Eisig" <quantumchaos666@.gmx.net> wrote in message
news:1143380427.955422.222930@.u72g2000cwu.googlegroups.com...
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>|||Robert Eisig wrote:
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
There is no such "rowid" concept in SQL Server. An exposed row number
based on anything other than values in the table violates the
relational Information Principle. So it's not a feature we should
expect or desire from a well-designed SQL DBMS - something you should
bear in mind if you want to create an app that is portable across many
database products.
SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
functions, which derive their values from the column(s) specified with
the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
as to guarantee a deterministic result.
See my signature for a link to the SQL Server docs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Limit is what we use in mysql no?
We can consider TOP clause of Sql Server similar to LIMIT.
Like,
Select * from article Limit 5
select top 5 * from article
For a specific range...
Select * from article Limit 5,10
Playing with 'IN' clause you can get LIMIT 5-10.
select TOP 5 * from article where articleid in (Select TOP 10 articleid
from article ) ORDER BY DESC
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Robert Eisig" wrote:

> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>|||The concept of rowid or recid on other database products and row_number() in
SQL Server have nothing in common. rowid is permanent on a record event afte
r
database shutdown. row_number() in SQL Server is just a sequential number
within a partition of a result set.
Ben Nevarez, MCDBA, OCP
Database Administrator
"David Portas" wrote:

> Robert Eisig wrote:
> There is no such "rowid" concept in SQL Server. An exposed row number
> based on anything other than values in the table violates the
> relational Information Principle. So it's not a feature we should
> expect or desire from a well-designed SQL DBMS - something you should
> bear in mind if you want to create an app that is portable across many
> database products.
> SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
> functions, which derive their values from the column(s) specified with
> the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
> as to guarantee a deterministic result.
> See my signature for a link to the SQL Server docs.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Ben Nevarez wrote:
> The concept of rowid or recid on other database products and row_number()
in
> SQL Server have nothing in common. rowid is permanent on a record event af
ter
> database shutdown. row_number() in SQL Server is just a sequential number
> within a partition of a result set.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
That's correct. A permanent "rowid" violates the Information Principle.
A ROW_NUMBER() function (provided it is generated deterministically
from data in the table) does not.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Limit? Rowid?

Hi,
I am writing a "universal" db-Interface and just finished the postgres
and mysql part. I got somehow stuck on the MS SQL Server part (via
ODBC).
I have no access to the SQL Server CD or the machine where it is
running, I access the database only via a VPN tunnel and ODBC.
My question is:
How can I use something like the "LIMIT " command?
How can I get the actual "_rowid" (not the primary key, but the row-id
of the result set.)
Something like:
Select * from article Limit 5,10
Select _rowid from article where id = "XYZ-123"
Update article set amount = 15 where _rowid = 23
And how to get the table definition, something like "Describe article;"
oder "Show create table article;" ?
Is there a simple online reference for this SQL dialect that SQL Server
via ODBC uses?
Thank you for your help
Robert
Robert
What vesrion are you using?
SQL Server 2005
There is new function ROW_NUMBER()
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
columns
FROM Table
) AS Der WHERE rownum BETWEEN 5 AND 10
SQL Server 2000
SELECT * FROM Table WHERE (SELECT COUNT(*) FROM Table t WHERE
t.PK<=Table.PK)
BETWEEN 5 AND 10
pk-Primary keys
Actualy if it does not help you ,please post DDL+ sample data + an expected
result
"Robert Eisig" <quantumchaos666@.gmx.net> wrote in message
news:1143380427.955422.222930@.u72g2000cwu.googlegr oups.com...
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>
|||Robert Eisig wrote:
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
There is no such "rowid" concept in SQL Server. An exposed row number
based on anything other than values in the table violates the
relational Information Principle. So it's not a feature we should
expect or desire from a well-designed SQL DBMS - something you should
bear in mind if you want to create an app that is portable across many
database products.
SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
functions, which derive their values from the column(s) specified with
the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
as to guarantee a deterministic result.
See my signature for a link to the SQL Server docs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Limit is what we use in MYSQL no?
We can consider TOP clause of Sql Server similar to LIMIT.
Like,
Select * from article Limit 5
select top 5 * from article
For a specific range...
Select * from article Limit 5,10
Playing with 'IN' clause you can get LIMIT 5-10.
select TOP 5 * from article where articleid in (Select TOP 10 articleid
from article ) ORDER BY DESC
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Robert Eisig" wrote:

> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>
|||The concept of rowid or recid on other database products and row_number() in
SQL Server have nothing in common. rowid is permanent on a record event after
database shutdown. row_number() in SQL Server is just a sequential number
within a partition of a result set.
Ben Nevarez, MCDBA, OCP
Database Administrator
"David Portas" wrote:

> Robert Eisig wrote:
> There is no such "rowid" concept in SQL Server. An exposed row number
> based on anything other than values in the table violates the
> relational Information Principle. So it's not a feature we should
> expect or desire from a well-designed SQL DBMS - something you should
> bear in mind if you want to create an app that is portable across many
> database products.
> SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
> functions, which derive their values from the column(s) specified with
> the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
> as to guarantee a deterministic result.
> See my signature for a link to the SQL Server docs.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Ben Nevarez wrote:
> The concept of rowid or recid on other database products and row_number() in
> SQL Server have nothing in common. rowid is permanent on a record event after
> database shutdown. row_number() in SQL Server is just a sequential number
> within a partition of a result set.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
That's correct. A permanent "rowid" violates the Information Principle.
A ROW_NUMBER() function (provided it is generated deterministically
from data in the table) does not.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Limit? Rowid?

Hi,
I am writing a "universal" db-Interface and just finished the postgres
and mysql part. I got somehow stuck on the MS SQL Server part (via
ODBC).
I have no access to the SQL Server CD or the machine where it is
running, I access the database only via a VPN tunnel and ODBC.
My question is:
How can I use something like the "LIMIT " command?
How can I get the actual "_rowid" (not the primary key, but the row-id
of the result set.)
Something like:
Select * from article Limit 5,10
Select _rowid from article where id = "XYZ-123"
Update article set amount = 15 where _rowid = 23
And how to get the table definition, something like "Describe article;"
oder "Show create table article;" ?
Is there a simple online reference for this SQL dialect that SQL Server
via ODBC uses?
Thank you for your help
RobertRobert
What vesrion are you using?
SQL Server 2005
There is new function ROW_NUMBER()
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
columns
FROM Table
) AS Der WHERE rownum BETWEEN 5 AND 10
SQL Server 2000
SELECT * FROM Table WHERE (SELECT COUNT(*) FROM Table t WHERE
t.PK<=Table.PK)
BETWEEN 5 AND 10
pk-Primary keys
Actualy if it does not help you ,please post DDL+ sample data + an expected
result
"Robert Eisig" <quantumchaos666@.gmx.net> wrote in message
news:1143380427.955422.222930@.u72g2000cwu.googlegroups.com...
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>|||Robert Eisig wrote:
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
There is no such "rowid" concept in SQL Server. An exposed row number
based on anything other than values in the table violates the
relational Information Principle. So it's not a feature we should
expect or desire from a well-designed SQL DBMS - something you should
bear in mind if you want to create an app that is portable across many
database products.
SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
functions, which derive their values from the column(s) specified with
the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
as to guarantee a deterministic result.
See my signature for a link to the SQL Server docs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Limit is what we use in MYSQL no?
We can consider TOP clause of Sql Server similar to LIMIT.
Like,
Select * from article Limit 5
select top 5 * from article
For a specific range...
Select * from article Limit 5,10
Playing with 'IN' clause you can get LIMIT 5-10.
select TOP 5 * from article where articleid in (Select TOP 10 articleid
from article ) ORDER BY DESC
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Robert Eisig" wrote:
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>|||The concept of rowid or recid on other database products and row_number() in
SQL Server have nothing in common. rowid is permanent on a record event after
database shutdown. row_number() in SQL Server is just a sequential number
within a partition of a result set.
Ben Nevarez, MCDBA, OCP
Database Administrator
"David Portas" wrote:
> Robert Eisig wrote:
> > Hi,
> >
> > I am writing a "universal" db-Interface and just finished the postgres
> > and mysql part. I got somehow stuck on the MS SQL Server part (via
> > ODBC).
> >
> > I have no access to the SQL Server CD or the machine where it is
> > running, I access the database only via a VPN tunnel and ODBC.
> >
> > My question is:
> >
> > How can I use something like the "LIMIT " command?
> >
> > How can I get the actual "_rowid" (not the primary key, but the row-id
> > of the result set.)
> >
> > Something like:
> >
> > Select * from article Limit 5,10
> >
> > Select _rowid from article where id = "XYZ-123"
> >
> > Update article set amount = 15 where _rowid = 23
> >
> > And how to get the table definition, something like "Describe article;"
> > oder "Show create table article;" ?
> >
> > Is there a simple online reference for this SQL dialect that SQL Server
> > via ODBC uses?
> >
> > Thank you for your help
> > Robert
> There is no such "rowid" concept in SQL Server. An exposed row number
> based on anything other than values in the table violates the
> relational Information Principle. So it's not a feature we should
> expect or desire from a well-designed SQL DBMS - something you should
> bear in mind if you want to create an app that is portable across many
> database products.
> SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
> functions, which derive their values from the column(s) specified with
> the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
> as to guarantee a deterministic result.
> See my signature for a link to the SQL Server docs.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Ben Nevarez wrote:
> The concept of rowid or recid on other database products and row_number() in
> SQL Server have nothing in common. rowid is permanent on a record event after
> database shutdown. row_number() in SQL Server is just a sequential number
> within a partition of a result set.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
That's correct. A permanent "rowid" violates the Information Principle.
A ROW_NUMBER() function (provided it is generated deterministically
from data in the table) does not.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

limit years from cube?

I have 10 years in my time dimension but I only want to display 5 of those
years in certain cube. How would I limit the number of years from a
dimension?Easiest way would be to use a standard SQL view for your dim in AS.
Ray Higdon MCSE, MCDBA, CCNA
--
"JJ" <swrothfuss@.hotmail.com> wrote in message
news:%23$avJRhBEHA.1544@.TK2MSFTNGP09.phx.gbl...
> I have 10 years in my time dimension but I only want to display 5 of those
> years in certain cube. How would I limit the number of years from a
> dimension?
>

Limit View in SQL 2005

When adding users to the SQL Server, is there a way to limit what DB's the
user can actually view when using Enterprise Manager?
i.e. When User1 logs into Enterprise Manger, they can only see DB1 in the
list of DB's, they can't see Master, Model, or any others?
The objective is to let a company connect to their Database, without see
what other clients/databases are hosted on the same DB server.
ThanksHi Kevin,
This is indeed possible in SQL Server 2005 but the master and tempdb
databases will be visible to all users - there is no way you can get
around that. Every user needs to have access to these databases. It's
just the way SQL Server works.
In order to explain how to make USER databases invisible to others I am
attaching the following script. It simulates a situation with three
databases, each belonging to different companies. Each company has its
own login.
create database company1_db
create database company2_db
create database company3_db
go
create login company1 with password = 'password1234*'
go
create login company2 with password = 'password1234*'
go
create login company3 with password = 'password1234*'
go
--At this point, all three logins (company1, company2 and company3)
--can see all user databases (company1_db, company2_db and company3_db).
use master
go
deny view any database to public
--At this point, all three logins (company1, company2 and company3)
--can see just the master and tempdb databases.
use company1_db
go
sp_changedbowner 'company1'
use company2_db
go
sp_changedbowner 'company2'
use company3_db
go
sp_changedbowner 'company3'
--At this point, the three logins (company1, company2 and company3)
--can see the master and tempdb databases as well as their own database.
Hope this helps!
Jonathan
Kevin A wrote:
> When adding users to the SQL Server, is there a way to limit what DB's the
> user can actually view when using Enterprise Manager?
> i.e. When User1 logs into Enterprise Manger, they can only see DB1 in the
> list of DB's, they can't see Master, Model, or any others?
> The objective is to let a company connect to their Database, without see
> what other clients/databases are hosted on the same DB server.
> Thanks
>|||JPD (jpd@.nospamplease.com) writes:
> use master
> go
> deny view any database to public
> --At this point, all three logins (company1, company2 and company3)
> --can see just the master and tempdb databases.
> use company1_db
> go
> sp_changedbowner 'company1'
>
> use company2_db
> go
> sp_changedbowner 'company2'
>
> use company3_db
> go
> sp_changedbowner 'company3'
But the apparent problem with that is there for every company are several
logins.
What SQL 2005 offers is clearly not sufficient. For this reason I submitted
this entry on Connect a while back:
https://connect.microsoft.com/SQLSe...=2738
30
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

limit user account to a table or database or view

Hello I think i have read in other posts that there is not way in
sql200 to limit access to a view to a certain user. What work arounds
are there for this, Can I schedule a dts export to a different database
or table and the assign rights only to data database or table? Can
anyone give some steps to create a secure login to a single sql view?
thanksIn SQL Server 2000, a user in a database does not have any access to an
object (table, view, proc, etc.) unless that user 1) owns it, 2) they
have been granted explicit permissions to it or 3) they have implicit
permissions to it via their role membership within the DB.
If you want a single DB user to only have access to a single view in a
database and no other database objects in that database, you just grant
them the appropriate permissions to that view with the GRANT statement
(presumably we're talking about SELECT permissions here), make sure the
"public" role in the database has no explicit permissions to anything
(which, by default, it doesn't) and make sure the user is not a member
of any other role in the DB (every user in a database is a member of
"public", you can't change that). This will guarantee that the only
object the user can interact with in the DB is that view.
Is that your goal? (Also, are you talking about SQL 2000 or SQL 2005?)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
joe wrote:

>Hello I think i have read in other posts that there is not way in
>sql200 to limit access to a view to a certain user. What work arounds
>are there for this, Can I schedule a dts export to a different database
>or table and the assign rights only to data database or table? Can
>anyone give some steps to create a secure login to a single sql view?
>thanks
>
>|||Hi,
Nope its not possible to in SQL 2000 but you can restrict your users to
access it using sp_revokedbaccess , offcourse they can see that this db
exists in dblist but they wont able to access it . this is a fixed in SQL
2005.
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"joe" wrote:

> Hello I think i have read in other posts that there is not way in
> sql200 to limit access to a view to a certain user. What work arounds
> are there for this, Can I schedule a dts export to a different database
> or table and the assign rights only to data database or table? Can
> anyone give some steps to create a secure login to a single sql view?
> thanks
>|||I think we need some clarification from the OP as to what he's trying to
achieve. Joe, what exactly are you trying to do? Are you trying to
*hide* a database from various SQL server logins in SQL Enterprise
Manager (SQL 2000) on a particular server (Andy's reply)? Or are you
trying to make sure a particular database user *can only access a
single*, specific object within the database (my original reply)? Or
are you trying to make sure that a particular database user *cannot*
access a particular database object within the database (this can be
done with the DENY statement)?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Andy Davis wrote:

>Hi,
>Nope its not possible to in SQL 2000 but you can restrict your users to
>access it using sp_revokedbaccess , offcourse they can see that this db
>exists in dblist but they wont able to access it . this is a fixed in SQL
>2005.
>Regards
>|||Hi,
true we need to wait for his response.
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Mike Hodgson" wrote:

> I think we need some clarification from the OP as to what he's trying to
> achieve. Joe, what exactly are you trying to do? Are you trying to
> *hide* a database from various SQL server logins in SQL Enterprise
> Manager (SQL 2000) on a particular server (Andy's reply)? Or are you
> trying to make sure a particular database user *can only access a
> single*, specific object within the database (my original reply)? Or
> are you trying to make sure that a particular database user *cannot*
> access a particular database object within the database (this can be
> done with the DENY statement)?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Andy Davis wrote:
>
>|||I know this question is old and that comments are not likely to be helpful,
but my interpretation of the question was that Joe wants a particular user
to be able to access a specific view but all other users should not have
access to the view. My understanding of the requirement is that the
requirement does not prevent a single user from having access to anything
else.
It is certainly best to get the requirements clarified before attempting to
suggest solutions.
I did not quote Mike's comment because it is posted using HTML format and
when I replied to it Outlook Express did not provide for it the quotation
symbols at the left of quoted text.

Limit total # of records used in a report

For performance purposes, is it possible to limit the # of records
returned for use in a report? I know you can do SELECT TOP 1000, but in
instances where the report contains several subselects, you have to
perform that function with every SELECT statement. This also has the
potential of making maintenance hectic.
Is there a way to globally limit the rendered rows?
Thanks!
MikeHi Mike,
There is no direct properties of records per page.FOr controlling the
records per page you need to specify the height and accordingly number of
recrds are set to the report.
Thanks
Jasvinder
"Bassist695" wrote:
> For performance purposes, is it possible to limit the # of records
> returned for use in a report? I know you can do SELECT TOP 1000, but in
> instances where the report contains several subselects, you have to
> perform that function with every SELECT statement. This also has the
> potential of making maintenance hectic.
> Is there a way to globally limit the rendered rows?
> Thanks!
> Mike
>

limit to where claus

Is there a maximum limit the WHere clause in sql server? How about when you
use the like operator? where can i find this information?Maximum limit? You mean a maximum limit to the number of AND's and OR's you
can include in a WHERE clause? What type of maximum limit are you looking
for specifically?
"Marty" <mbills@.ttecht(removethis).com> wrote in message
news:EC1F78AD-AB8F-4688-BCEA-13FF1CCA3865@.microsoft.com...
> Is there a maximum limit the WHere clause in sql server? How about when
> you
> use the like operator? where can i find this information?
>|||maximum limit of characters.
specifically with the LIKE operator.
"Mike C#" wrote:

> Maximum limit? You mean a maximum limit to the number of AND's and OR's y
ou
> can include in a WHERE clause? What type of maximum limit are you looking
> for specifically?
> "Marty" <mbills@.ttecht(removethis).com> wrote in message
> news:EC1F78AD-AB8F-4688-BCEA-13FF1CCA3865@.microsoft.com...
>
>|||> Maximum limit? You mean a maximum limit to the number of AND's and OR's
> you can include in a WHERE clause? What type of maximum limit are you
> looking for specifically?
(Just guessing) He might mean the size of the where clause or complexity. I
have answered similar questions from lots of Access people. See, Access has
a whole list of idiotic limits like, "The SQL Statement cannot be longer
than xxx KB of text". I think they did that just to make those backend
reporting apps extra fun to try to develop.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:edbpLrVkGHA.5020@.TK2MSFTNGP02.phx.gbl...
> (Just guessing) He might mean the size of the where clause or complexity.
> I have answered similar questions from lots of Access people. See, Access
> has a whole list of idiotic limits like, "The SQL Statement cannot be
> longer than xxx KB of text". I think they did that just to make those
> backend reporting apps extra fun to try to develop.
Ahhh. I'm not aware of any limit, and have seen humongous (albeit poorly
designed) WHERE clauses. There might be limits on the size of queries that
different tools and database connection API's can handle, although I don't
know them off-hand.

limit to the number of rows a dataset can store?

hI,

I am using visual c# 2003 and sqlserver 2000 and i am trying to query a column in the sql server and store it into a dataset but i got an error msg:

The number of rows for this query will output 90283 rows.

------------------------
Query :

SELECT L_ExtendedPrice, COUNT (*) AS Count FROM LINEITEM GROUP BY L_ExtendedPrice ORDER BY Count DESC";

------------------------
Error msg :

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.
-------------------------

is there a limit to the number of rows a dataset can store?That's a lot of rows....

Can I ask what you plan to do with them?

And it's not really a sql server question...|||hi,

It is because I am a university student and i am doing my final year project on querying and i need these data to proceed on. I am researching on Small Group Sampling.

I was thinking that i am using sql server 2000 and maybe i can post this question in here as I am really in need of help for this.

thks|||Whatever you thought you wanted to do with the "dataset", why don't you want to do it on the server? All this "C#" and stuff is good, but only when used appropriately. Having a front-end with 90K+ rows is not a good idea, unless it's a report. But if that's the case, - it shouldn't be going into a "dataset."|||Originally posted by sunspot
hi,

It is because I am a university student and i am doing my final year project on querying and i need these data to proceed on. I am researching on Small Group Sampling.

I was thinking that i am using sql server 2000 and maybe i can post this question in here as I am really in need of help for this.

thks

OK, I'll byte...what's your major going to be in....|||Hi,

It is because I need to use the data in thwe dataset to do something else. This question is actually related to a question I posted on :

visual studio.net and sql server 02-12-04 22:01

I do not know what is the wrong things that I have done and that is why I am asking how many rows a dataset can store as I got error msg when I run the program.

Limit to one value in fld b for given value in field a

Okay, i have a problem, which would be easy in a well-normalized db,
but im working in a legacy app here, and can't normalize the db because
it would break the existing apps around it.
I have a manifest table which essentially acts as both a header & line-items
table.
There is a MANIFESTID field which serves to link the lineitems together onto
one
manifest. There is also an ACTION field, which serves to indicate whether
the
manifest is for delivery or for pickup.
I dont know if it really is this hard, or if im just brain-freezing here,
but here is what
i'm trying to do... i want to put in some sort of constraint so that for all
the lines which
have any given MANIFESTID, the ACTION field is the same on all of them.
Thanks in advance,
- Arthur Dent.Not sure if I fully understood your problem, but in SQL Server, you cannot
create a constraint declaratively which can enforce some logic based on
multiple rows.
A general alternative is to use a after trigger with a rollback statement.
This is somewhat prohibitive in high performance systems since the logic can
turn out to be complex depending on your schema. In rare cases, you might be
able to work it out with a scalar udf used in a check constraint as well.
Anith|||As example for clarification,
ACTION is declared with a check as either D or P or Delivery or Pickup.
I might have 5 rows where the ManifestID is <10>. What i want is to be able
to say
For All Rows Where MANIFESTID = 10,
ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
must be 'D' or ALL must be 'P'
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23Y4JaqG7FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Not sure if I fully understood your problem, but in SQL Server, you cannot
> create a constraint declaratively which can enforce some logic based on
> multiple rows.
> A general alternative is to use a after trigger with a rollback statement.
> This is somewhat prohibitive in high performance systems since the logic
> can turn out to be complex depending on your schema. In rare cases, you
> might be able to work it out with a scalar udf used in a check constraint
> as well.
> --
> Anith
>|||On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:

>As example for clarification,
>ACTION is declared with a check as either D or P or Delivery or Pickup.
>I might have 5 rows where the ManifestID is <10>. What i want is to be able
>to say
>For All Rows Where MANIFESTID = 10,
>ACTION on ALL 5 Rows can be 'D' or 'P', but they ALL
>must be 'D' or ALL must be 'P'
Hi Arthur,
This might be one of those cases where a UDF + CHECK constraint will
work:
CREATE FUNCTION dbo.CheckNoOthers
(@.ManifestID int, @.Action char(1))
RETURNS char(3)
AS
BEGIN
DECLARE @.Result char(3)
IF EXISTS (SELECT *
FROM YourTable
WHERE ManifestID = @.ManifestID
AND Action <> @.Action)
BEGIN
SET @.Result = 'No'
END
ELSE
BEGIN
SET @.Result = 'Yes'
END
RETURN @.Result
go
ALTER TABLE YourTable
ADD CONSTRAINT NoOthers
CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yah, that looks like it would probably work. Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qgq1o1pc00til4dvnk7h2jk7nssscm6vk2@.
4ax.com...
> On Fri, 18 Nov 2005 13:53:12 -0500, Arthur Dent wrote:
>
> Hi Arthur,
> This might be one of those cases where a UDF + CHECK constraint will
> work:
> CREATE FUNCTION dbo.CheckNoOthers
> (@.ManifestID int, @.Action char(1))
> RETURNS char(3)
> AS
> BEGIN
> DECLARE @.Result char(3)
> IF EXISTS (SELECT *
> FROM YourTable
> WHERE ManifestID = @.ManifestID
> AND Action <> @.Action)
> BEGIN
> SET @.Result = 'No'
> END
> ELSE
> BEGIN
> SET @.Result = 'Yes'
> END
> RETURN @.Result
> go
> ALTER TABLE YourTable
> ADD CONSTRAINT NoOthers
> CHECK (dbo.CheckNoOthers(ManifestID, Action) = 'Yes')
> go
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Limit to number of functions in a DB

Hello,
Is there a limit to the number of user-defined functions one can have in a
database (i.e. 500 udfs per db)?
Thanks in advance for your help,
-CollinThe limit, AFAIK, is the number of objects (including tables, stored
procedures etc), which is the datatype for object id in sysobjects, which is
in, which mean about 2 billion. From BOL:
Objects in a database 2,147,483,6474 2,147,483,6474
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\arc
hitec.chm::/8_ar_ts_8dbn.htm
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"cc_gardner" <collin_gardner@.hotmail.com> wrote in message
news:buouvr$bjq12@.kcweb01.netnews.att.com...

quote:

> Hello,
> Is there a limit to the number of user-defined functions one can have in a
> database (i.e. 500 udfs per db)?
> Thanks in advance for your help,
> -Collin
>

Friday, February 24, 2012

Limitations in SQL Server 2005 Express

Limitations in SQL Server 2005 Express

Limitations in Report Builder?

Limitations in a query !

Limitation on Number of Parameters??

Limitation on Multiple parameters with Multi-Select and default values

Limitation on Multiple parameters with Multi-Select and default values

Limitation on Conn.Execute in classic .ASP & ADO with SQL Server

Limitation of updating views

Limitation of sql standard

Limitation of RB?

Limitation of having column text column

Limitation of having column text column

Limitation of having column text column

Limitation of 1023 Characters

Limitation of # cubes inside a base Analysis Services

Limitation in number of fields a Table published by push merge Replication may have

Limitation for UDAs

Limitaions of MSDE

Limit? Rowid?

Limit? Rowid?

Limit? Rowid?

limit years from cube?

Limit View in SQL 2005

limit user account to a table or database or view

Limit total # of records used in a report

limit to where claus

limit to the number of rows a dataset can store?

Limit to one value in fld b for given value in field a

Limit to number of functions in a DB

LIMIT problems

Blog Archive

About Me