MDAC 2.8 and cursor FETCH NEXT

it seems that starting with MDAC 2.8 the FETCH NEXT statement for cursors requires an INTO clause. otherwise an unspecified error is returned.
so it's not possible to scroll through the records anymore without storing the values of the fields into local variables?Can you post the error code/description ?|||the following code executes from a connection object without issues using ado 2.6 and 2.7:

DECLARE c8281 CURSOR FOR
SELECT * From Users WHERE ( Users.User_ID = 17 )
OPEN c8281
FETCH NEXT FROM c8281
UPDATE [Users] SET
User_Name = 'Unknown'
WHERE CURRENT OF c8281
CLOSE c8281
DEALLOCATE c8281

however, if I use ADO 2.8 it returns error code 80004005 (unspecified error) and native error = 0. furthermore, it actually executes at the database server.

I made it work without errors by modifying the code in the following way:

DECLARE @.username VARCHAR
DECLARE c8281 CURSOR FOR
SELECT User_Name From Users WHERE ( Users.User_ID = 17 )
OPEN c8281
FETCH NEXT FROM c8281 INTO @.username
UPDATE [Users] SET
User_Name = 'Unknown'
WHERE CURRENT OF c8281
CLOSE c8281
DEALLOCATE c8281

which is closer to ansi92 I guess. but I just really need to scroll through the cursor. no need for local variables.

Maximum size of the contains_search_condition clause in a CONTAINS statement

Hi,
We're using the SQL Server Full Text Engine to implement a search engine. Users can enter queries which are then executed using the CONTAINS statement. I can't find any documentation that indicates the maximum allowable size of the contains_search_conditi
on clause. Our users' search terms can be quite complex with many Boolean predicates. As a result they can become quite long: several hundred or even a few thousand characters long. What is the upper size limit?
Many thanks,
Dan
Dan,
There was an early bug in SQL Server 2000 RTM that limited the max character
length to 1023 (1K -1), but it was fixed in SP1.
I believe that the max character limit was extended to the max length of
char/varchar datatype length of 8000.
Regards,
John
"Dan Crow" <anonymous@.discussions.microsoft.com> wrote in message
news:A46B5192-072F-4DF0-8D9E-F44D955EDB58@.microsoft.com...
> Hi,
> We're using the SQL Server Full Text Engine to implement a search engine.
Users can enter queries which are then executed using the CONTAINS
statement. I can't find any documentation that indicates the maximum
allowable size of the contains_search_condition clause. Our users' search
terms can be quite complex with many Boolean predicates. As a result they
can become quite long: several hundred or even a few thousand characters
long. What is the upper size limit?
> Many thanks,
> Dan

Maximum Numbers of Tables and colums to access in select statement

Hi

I want to get the
Maximum Numbers of Tables and colums to access in select query statement

Regards
AruneshCitations from SQL Books Online:columns: The maximum number of expressions that can be specified in the select list is 4096

maximum number of tables per select?

Does anyone know what is the maximum number of tables per select statement
in Oracle and SQL Server 2000?
Thanks a lot,
LixinI don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > Does anyone know what is the maximum number of tables per select
statement
> > in Oracle and SQL Server 2000?
> >
> > Thanks a lot,
> > Lixin
> >
> >
>

Maximum length sql statement that can be run in SQL CE

Hello
Can anyone tell me what the maximum length sql statement SQL CE can run is?
Thank you
Gráinne

Please ask in SQL Mobile forum. Thanks.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1

|||moving thread to correct forum.|||Thanks for moving the posting Mark.
To expand the question:
I have a table that has a column storing an SQL statement. The sql is set by the customer and I want to ensure that the column size is no bigger than the maximum SQL string that can be executed by SQL CE.
If there is no absolute upper limit, then is there a rule of thumb for the maximum number of characters in a SQL CE statement?
Thanks again
Gráinne
|||

There is no maximum limit on the number of characters that can be part of a SQL statement in SQL Mobile. Please review the SQL Mobile Books Online under the index heading 'maximum database object sizes' for more information.

Darren

Maximum length of return in Query Analyzer

I am executing a SELECT statement that has about 500 characters of literal characters concatenated with the contents of a field from a table. I am then storing the result to be run as dynamic SQL. I am finding that when run this as select statement in query analyzer, the last part of the literal gets truncated. When I run it as a cursor and store it in a varchar(1000) variable and print the variable everything works fine. In addition when I put the select statement in a stored procedure and return this to a ADO recordset, the resultset is fine as well. But running the stored procedure in query analyzer truncates the results as well. The issue seems to be getting the results of the SELECT in query analyzer. Even running the stored procedure in the SQL area of Enterprise Manager returns a proper result. Has anyone heard of a maximum return from a select in query analyzer?Yes, check out: Tools, Options, the Results-tab. "Maximum characters per column".|||Yes, and I should probably post a FAQ entry for it.

1. Shift-Ctrl-o for options
2. Click the Results tab
3. Near the middle, Maximum characters per column..

-PatP|||Manj Tak
Danke Sha
merci beaucoup
mucho gracias|||Yes, and I should probably post a FAQ entry for it.

-PatP

Can you put one on there for making DBA coffee?

1. 4 heaping tbsp coffee grounds/8oz water
2. Pinch of salt
3. Add water tapped from boiler/radiator
4. Brew in pot that's never been washed using yesterday's filter
5. Serve in cup that's never been washed

Thanks,

hmscott

Maximum length of a select statement??

Hi there,
I am a novice. I need some information regarding what could be the maximum length of a select statement, esp the where clause.there is no actual limit. Possible limit would be what your programming language can store in a string.|||A limit that I ran into was using stored procedures where you pass a variable.

If you need to pass a variable or use a declared variable in the procedure you will be limitted by the 8000 character variable limit.

I have had times when I was passing a "WHERE" clause as a variable and reached the 8000 character limit. I haven't found another way to do it within a stored procedure yet, so I have been passing the SQL statement directly to the database. This was an ASP application pulling historical records. I had to use OR rather than Between becuase of some other criteria.

Otherwise the SQL statement passed directly to the database has no hard limit as far as I know.

Maximize Data Throughput for Network Application

Contradictory statement exists in BOL which is

version SQL 2005 SP1:

Local:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29ce373e-18f8-46ff-aea6-

15bbb10fb9c2.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-

0a0a440eb443.htm

Online:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

http://msdn2.microsoft.com/en-us/library/ms179301.aspx

Both documents address the setting "Maximize data throughput for network applications".

While both documents state "This option may limit memory available to SQL Server for normal operation". Then one document advises to enable and the other to

disable.

I would like to know if this is simply an oversight or if the contradiction is somehow correct and due to the context by which each document is presented.

I don't see where they contradict - they seem to say the same thing:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

"Maximize Data Throughput for Network Applications
If the Maximize data throughput for network applications network connection option is selected, the operating system gives priority to applications that perform buffered I/O operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

http://msdn2.microsoft.com/en-us/library/ms179301.aspx
"Maximize Data Throughput for Network Application
If the Maximize data throughput for file sharing option is selected in Network Connection, the operating system gives priority to applications that perform buffered input/output (I/O) operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

Can you elaborate?

Thanks -

Buck Woody

|||

But there is contradictory. You said in the first paragraf "If the Maximize data throughput for network applications network connection option is selected" and in the second paragraf you said "If the Maximize data throughput for file sharing option is selected "

What is the correct setting?

|||I see that now. It may be because one deals with SQL Server with no options, the other with AWE memory. When you enable AWE memory the large pool is a bit different. Sorry for the confusion.

Maximize Data Throughput for Network Application

Contradictory statement exists in BOL which is

version SQL 2005 SP1:

Local:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29ce373e-18f8-46ff-aea6-

15bbb10fb9c2.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-

0a0a440eb443.htm

Online:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

http://msdn2.microsoft.com/en-us/library/ms179301.aspx

Both documents address the setting "Maximize data throughput for network applications".

While both documents state "This option may limit memory available to SQL Server for normal operation". Then one document advises to enable and the other to

disable.

I would like to know if this is simply an oversight or if the contradiction is somehow correct and due to the context by which each document is presented.

I don't see where they contradict - they seem to say the same thing:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

"Maximize Data Throughput for Network Applications
If the Maximize data throughput for network applications network connection option is selected, the operating system gives priority to applications that perform buffered I/O operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

http://msdn2.microsoft.com/en-us/library/ms179301.aspx
"Maximize Data Throughput for Network Application
If the Maximize data throughput for file sharing option is selected in Network Connection, the operating system gives priority to applications that perform buffered input/output (I/O) operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

Can you elaborate?

Thanks -

Buck Woody

|||

But there is contradictory. You said in the first paragraf "If the Maximize data throughput for network applications network connection option is selected" and in the second paragraf you said "If the Maximize data throughput for file sharing option is selected "

What is the correct setting?

|||I see that now. It may be because one deals with SQL Server with no options, the other with AWE memory. When you enable AWE memory the large pool is a bit different. Sorry for the confusion.

Maximize Data Throughput for Network Application

Contradictory statement exists in BOL which is

version SQL 2005 SP1:

Local:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29ce373e-18f8-46ff-aea6-

15bbb10fb9c2.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-

0a0a440eb443.htm

Online:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

http://msdn2.microsoft.com/en-us/library/ms179301.aspx

Both documents address the setting "Maximize data throughput for network applications".

While both documents state "This option may limit memory available to SQL Server for normal operation". Then one document advises to enable and the other to

disable.

I would like to know if this is simply an oversight or if the contradiction is somehow correct and due to the context by which each document is presented.

I don't see where they contradict - they seem to say the same thing:

http://msdn2.microsoft.com/en-us/library/ms178067.aspx

"Maximize Data Throughput for Network Applications
If the Maximize data throughput for network applications network connection option is selected, the operating system gives priority to applications that perform buffered I/O operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

http://msdn2.microsoft.com/en-us/library/ms179301.aspx
"Maximize Data Throughput for Network Application
If the Maximize data throughput for file sharing option is selected in Network Connection, the operating system gives priority to applications that perform buffered input/output (I/O) operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation."

Can you elaborate?

Thanks -

Buck Woody

|||

But there is contradictory. You said in the first paragraf "If the Maximize data throughput for network applications network connection option is selected" and in the second paragraf you said "If the Maximize data throughput for file sharing option is selected "

What is the correct setting?

|||I see that now. It may be because one deals with SQL Server with no options, the other with AWE memory. When you enable AWE memory the large pool is a bit different. Sorry for the confusion.