sql error: max

Showing posts with label max. Show all posts

Saturday, February 25, 2012

"No more data available to read" error

Hi All
"No more data available to read" error is thrown by database driver when
connections max out on SQL Server. Does any one know what this means and ho
w
can i resolve this issue? An application developed in Java uses SQL Server
for data storage. Thank you in advance.This is not a sql server error message. It is an error message that is
raised by the jdbc driver itself.
It could be caused by several things that I know of, but each is sort of
application dependent.
1) Thread contention (non-thread safe) code within the app. 2 thread
competing for the same data in memory on the server
2) BLOB types, I read a bit that having BLOB types within your result set
can lead to this type of problem
3) Trying to scroll backward, or through a forward-only result set for a
second time.
I'd recommend contacting the JDBC driver manufacturer, as they would have
more details. I would be surprised if it was really related to not enough d
b
connections.
Sorry I can't be of more help.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MittyKom" wrote:

> Hi All
> "No more data available to read" error is thrown by database driver when
> connections max out on SQL Server. Does any one know what this means and
how
> can i resolve this issue? An application developed in Java uses SQL Server
> for data storage. Thank you in advance.
>

"max text repl size" safety

I just increased the max size of text replication to 524288 bytes with below
Sql statements. The system allows me to do that but I'm wondering if there
is any issue/drawback that I should know about pumping the max size up by so
much. Thank you in advance for your advice.
EXEC sp_configure 'max text repl size', '524288'
go
RECONFIGURE WITH OVERRIDE
goBasically it means that SQL can replicate data where a row contains more
than 524288 bytes. This will cause performance degradation on your system as
rows this size are going through.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>I just increased the max size of text replication to 524288 bytes with
>below
> Sql statements. The system allows me to do that but I'm wondering if
> there
> is any issue/drawback that I should know about pumping the max size up by
> so
> much. Thank you in advance for your advice.
> EXEC sp_configure 'max text repl size', '524288'
> go
> RECONFIGURE WITH OVERRIDE
> go
>|||If you have a separate distributor server where does this setting need to be
issued. At the distributor or the publisher?
If the system is moving large files (2- 10 MB) what kind of impact will it
have? What impact will it have on both servers?
Thanks
"Hilary Cotter" wrote:
> Basically it means that SQL can replicate data where a row contains more
> than 524288 bytes. This will cause performance degradation on your system as
> rows this size are going through.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
> >I just increased the max size of text replication to 524288 bytes with
> >below
> > Sql statements. The system allows me to do that but I'm wondering if
> > there
> > is any issue/drawback that I should know about pumping the max size up by
> > so
> > much. Thank you in advance for your advice.
> >
> > EXEC sp_configure 'max text repl size', '524288'
> > go
> > RECONFIGURE WITH OVERRIDE
> > go
> >
> >
>
>|||I believe its on the publisher.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:2DB417F1-5573-48EA-B1AC-C10B8760D75E@.microsoft.com...
> If you have a separate distributor server where does this setting need to
> be
> issued. At the distributor or the publisher?
> If the system is moving large files (2- 10 MB) what kind of impact will it
> have? What impact will it have on both servers?
> Thanks
> "Hilary Cotter" wrote:
>> Basically it means that SQL can replicate data where a row contains more
>> than 524288 bytes. This will cause performance degradation on your system
>> as
>> rows this size are going through.
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>> "Zeng" <Zeng5000@.hotmail.com> wrote in message
>> news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>> >I just increased the max size of text replication to 524288 bytes with
>> >below
>> > Sql statements. The system allows me to do that but I'm wondering if
>> > there
>> > is any issue/drawback that I should know about pumping the max size up
>> > by
>> > so
>> > much. Thank you in advance for your advice.
>> >
>> > EXEC sp_configure 'max text repl size', '524288'
>> > go
>> > RECONFIGURE WITH OVERRIDE
>> > go
>> >
>> >
>>

"Max row" in each group

I'm having trouble developing a query that will group on a given set of
columns and, for each group, will also display the other columns in the
"max row" for that group. Let me illustrate what I mean:
CREATE TABLE [dbo].[GroupTest] (
[testID] [int] IDENTITY (1, 1) NOT NULL ,
[office] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startDate] [datetime] NOT NULL ,
[status] [int] NOT NULL ,
[endDate] [datetime] NULL ,
[reportDate] [datetime] NOT NULL ,
[amount] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupTest] ADD
CONSTRAINT [PK_GroupTest] PRIMARY KEY CLUSTERED
(
[testID]
) ON [PRIMARY]
GO
Each row in this table represents a status update in an issue tracking
system. (The actual table has more columns. This is a subset for
illustrative purposes.) None of the columns is unique other than testID.
I am being asked to produce a query for each combination of {office,
startDate} will include the row matching that combination that has the
greatest value in the status column. So if office='ABC' and
startDate='2005-11-30' and there are two rows matching these values, one
with status=3 and one with status=4, the query should include the row
having status=4.
If the combination {office, startDate, status} were unique in this
table, the following query would do the job:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
order by G0.office, G0.startDate
But that's not the case. I asked the customer what to do. For their
purposes, the report will be fine if it can show them an "illustrative"
row--in other words, if more than one row in the table matches a given
combination {office, startDate, status}, the query should return one of
them arbitrarily. But I'm not figuring out how to do that. I guess I'd
like to figure out how to retrieve the "maximum row" for each {office,
startDate} combination, or something similar. Can anyone help?
I could punt and go with just displaying the maximum over each of the
columns on the right-hand side (status, endDate, reportDate, amount),
each taken independently:
SELECT
G0.office, G0.startDate, G0.status,
G0.endDate, G0.reportDate, G0.amount
FROM GroupTest G0
INNER JOIN
(SELECT office, startDate, max(status) maxStatus,
max(endDate) maxEndDate,
max(reportDate) maxReportDate,
max(amount) maxAmount
FROM GroupTest GROUP BY office, startDate) G1
ON G0.office = G1.office
AND G0.startDate = G1.startDate
AND G0.status = G1.maxStatus
AND G0.endDate = G1.maxEndDate
AND G0.reportDate = G1.maxReportDate
AND G0.amount = G1.maxAmount
order by G0.office, G0.startDate
But the rows produced by that query aren't "coherent": For a given
{office, startDate} combination, the status could be from one row, the
startDate could be from another, the endDate from another, etc. Each row
in my query should match an actual row in the table.I've moved on from my previous question, having figured out an approach.
However, I'm stuck on that approach because I'm getting unexpected
results. I'm asking about that in a new thread.

Saturday, February 11, 2012

"Datetime field overflow" w/ MAX in subselect?

Hey guys--hoping you can help with a problematic query. When I run it, I get an error, copied here from the ColdFusion application of which it's a part: "ODBC Error Code = 22008 (Datetime field overflow) [MERANT][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type."

And here's the query:

SELECT *
FROM Stories
WHERE (StoryID IN
(SELECT MAX(PublishTime) AS PublishTime
FROM Stories
WHERE (Fixture_Code IN ('test1', 'test2'))
GROUP BY Fixture_Code))

The subselect works great when run by itself; it's only when it's a subselect that I get type errors. PublishTime is a smalldatetime column. The above query actually runs without an error if my values for WHERE IN are values that don't exist in the the Fixture_Code column anywhere--obviously I get back an empty set, but no error.

In case it's not clear, I'm trying in the subselect to pick the single most recent record for each of a list of fixture codes, so that I can then go back and get full information on each of those stories. If someone can tell me a better way to, essentially, do a query where the result set contains only one row (the most recent) for each of my "fixture codes", I'm open to that too.

Thanks again for any help.The error occurs because you are comparing PublishTime (smalldatetime) from the subselect with StoryID (I assume integer or character). Like compare apples to oranges. You could try this:
SELECT s.*
FROM Stories s,
(
SELECT MAX(PublishTime) AS PublishTime,
Fixture_Code
FROM Stories
GROUP BY Fixture_Code
) x
WHERE s.Fixture_Code = x.Fixture_Code
AND s.PublishTime = x.PublishTimeSo this will return the most recent record for each Fixture_Code.|||Ahh, okay. That makes perfect sense. Your code works great... thanks a lot for the help; as you may have guessed I'm still learning the elusive art of subselects :)|||Although, when I tweak the query to specify only the list of fixtures I want, plus bring in another table join to get a fixture name in addition to the fixture code, I get one row that's duplicated in the results.

SELECT s.*, Fixture_Name
FROM Stories s INNER JOIN
(SELECT MAX(PublishTime) AS PublishTime, Fixture_Code
FROM Stories
GROUP BY Fixture_Code) x
JOIN FixtureCodes ON x.Fixture_Code = FixtureCodes.Fixture_ID
ON s.Fixture_Code = x.Fixture_Code AND s.PublishTime = x.PublishTime AND s.Fixture_Code IN ('test1','test2','test3','test4','test5','test6',' test7','test8')

Very easily taken care of with DISTINCT but if anyone cared to further edify me with the reason that's happening, I'd be grateful :) Thanks again to all.

Saturday, February 25, 2012

"No more data available to read" error

"max text repl size" safety

"max text repl size" safety

"max text repl size" safety

"max text repl size" safety

"Max row" in each group

Saturday, February 11, 2012

"Datetime field overflow" w/ MAX in subselect?

sql error

Blog Archive

About Me