ODBC 3146

Excuse me for my poor english
I have MS access Database linked with SQL SERVER 7.
Only 2 pc not execute a big Query.This query is composed of lot subquery.
i have ODBC ERROR 37000. and this message "Alias and column (MS3) is
incorrect"
With SQL.LOG i read the syntax SQL is not identique with the sql synatx of
good query.
What do you Think of this? the ODBC drivers et Jet DB are the last version.
Thanks for your helpHi
Have you run the query through Query Analyser to check that it will work?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnodbc/html/odbcsql.asp
describes the error 37000 as being possibly due to returning multiple
resultsets or a statement not supported by server side cursors.
Without seeing your SQL Statement it is not possible to know this!
Also
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/HTML/_core_error.3a_.syntax_error_or_access_violation.asp
describes an error occurring on SQL Server SP2, you may want to check it out
even though you may be on a higher version
John
"Sql_NeedHelp" wrote:
> Excuse me for my poor english
> I have MS access Database linked with SQL SERVER 7.
> Only 2 pc not execute a big Query.This query is composed of lot subquery.
> i have ODBC ERROR 37000. and this message "Alias and column (MS3) is
> incorrect"
> With SQL.LOG i read the syntax SQL is not identique with the sql synatx of
> good query.
> What do you Think of this? the ODBC drivers et Jet DB are the last version.
> Thanks for your help
>
>

OCI-22053 - Overflow with ORACLE

I'm executing a query against an ORACLE DB. If I execute this query using
TOAD it works fine...a little slow but I have the result rows.
If I execute this query inside the Report Designer I get the next message:
" An error occurred while reading data from the query result set.
OCI-22053: overflow error "
Any idea'
ThanxI created a new project and the problem solved.
"Pato Connon" wrote:
> I'm executing a query against an ORACLE DB. If I execute this query using
> TOAD it works fine...a little slow but I have the result rows.
> If I execute this query inside the Report Designer I get the next message:
> " An error occurred while reading data from the query result set.
> OCI-22053: overflow error "
> Any idea'
> Thanx
>

Occasional "Timeout expired" message - on SP that should take 1 second

Hi there,
I've got something kind of weird going on. I have a stored procedure that
normally takes less than one second to execute, but it has started taking
over 30 seconds from time to time.
The SP is called by an ASP page on a web server, running on a separate
machine. The database server is running SQL Server 2000. The ASP page
makes an ODBC connection, using TCP/IP and creates & sends the stored
procedure call.
From time to time, something goes haywire, and the ASP page returns
[Microsoft][ODBC SQL Server Driver]Timeout expired
Then, the ASP page will not work no matter what we do, even if we leave it a
day or two. (No locks on the database, sometimes not even any connections!)
The only way to fix it is to run the stored procedure through Query
Analyzer. It will take a little over 30 seconds, but finish successfully.
Then, if I run the exact same SP again, it will complete in 0 seconds, and
the web page will work fine again.
I've seen this before, but I cannot for the life of me remember what we did
to fix it. We tried changing the ODBC connection to Named Pipes instead of
TCP/IP but that didn't help.
Any suggestions?
BeverleyI am having the same issue and the solution provided by SQL crafter in the S
ql Server Timeout expired post is not working. Could someone help us with t
his issue?
Thanks,
Rick|||I have been struggling with a similar problem for a couple of weeks and came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes, ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- Fred
"Beverley" <ali_webitems@.hotmail.com> wrote in message
news:OpxbH$K9DHA.712@.tk2msftngp13.phx.gbl...
> Hi there,
> I've got something kind of weird going on. I have a stored procedure that
> normally takes less than one second to execute, but it has started taking
> over 30 seconds from time to time.
> The SP is called by an ASP page on a web server, running on a separate
> machine. The database server is running SQL Server 2000. The ASP page
> makes an ODBC connection, using TCP/IP and creates & sends the stored
> procedure call.
> From time to time, something goes haywire, and the ASP page returns
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Then, the ASP page will not work no matter what we do, even if we leave it
a
> day or two. (No locks on the database, sometimes not even any
connections!)
> The only way to fix it is to run the stored procedure through Query
> Analyzer. It will take a little over 30 seconds, but finish successfully.
> Then, if I run the exact same SP again, it will complete in 0 seconds, and
> the web page will work fine again.
> I've seen this before, but I cannot for the life of me remember what we
did
> to fix it. We tried changing the ODBC connection to Named Pipes instead
of
> TCP/IP but that didn't help.
> Any suggestions?
> Beverley
>|||I have been struggling with a similar problem for a couple of weeks and
came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred|||ITFRED, have you found a solution to your problem at all'
I have a similar problem, I also get the *occasional timeout*.
Mine is an ASP web application connected to MSDE, and the application
gets a timeout error occasionally when the ASP code tries to update
many records in a table, but smaller tables work fine.
The error message is:
> Error Number -214xxxxxx, ODBC SQL Server Driver timeout expired.
In a testing environment on a dev server, the same ASP application
connects to a SQL2K database and it has NOT encountered any problems.
My conclusion is (thanks to all your posts I have read) that it is in
ODBC connections where the MSDE engine times out.
Can anyone please come up with a solution!'
Is using a different database an option, perhaps mysql or postgreSQL?
Thanks.
huge
ITFred wrote:
> *I have been struggling with a similar problem for a couple of weeks
> and
> came
> to this group in search of a solution. Perhaps a pooling of
> information
> will help.
> In my situation, I have a VB interactive front-end on multiple
> machines
> accessing a SQL2K database through ODBC over TCP/IP. It has been in
> service
> for over 2 years and the master table now has over 750K records in
> it.
> Recently we started receiving occasional "Timeout Expired" errors in
> pretty
> much the same manner as you describe -- a query that would normally
> take
> 1-2 seconds would suddenly take over 90 and crap out. I ran
> database
> integrity checks, reconstructed the indexes, created additional
> indexes,
> ran
> numerous original and reconfigured queries through the Index
> Analyzer. And
> of course spent hours trying various searches in the MS KB. I have
> reached
> the following conclusions:
> The problem (in my case, at least) originates with the Query
> Optimizer. A
> very specific query run through the Query Analyzer will *always*
> select an
> appropriate index or mix of indexes. The *identical* query
> submitted
> through ODBC, however, will sometimes select a completely
> inappropriate
> index mix, or no index at all, wind up executing full-table scans or
> other
> time-wasting substitutes, and time out. This effect, I suspect, is
> a
> permanent feature of MSSQL, but does not manifest on smaller tables
> because
> the malfunction does not cause a timeout on smaller tables, just an
> inexcusable waste of time.
> I proved this theory by adding index hints to my ODBC-originated
> queries
> (which Books Online says should never be necessary), and observing
> the
> performance stabilize. Problem is, I consider this a completely
> hokey
> solution since if I ever decide to reconfigure my indexes, I will
> have
> dozens of coordinating code changes to perform. Also, there are
> several
> queries in which I cannot use index hints because the query requires
> column-level 'OR'-ing with which hints are incompatible.
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment from MS that this is a behavioral issue
> with
> SQL2K that needs to be addressed.
> - ITFred *
huge
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message397556.html

Obtaining different timings for the same process?

Dear fellows,
The following loop bring me differents results, execute one after one (among
them a truncate table, of course):
DECLARE @.loop as integer
set @.loop = 1
while @.loop < 10000
begin
insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
set @.loop = @.loop + 1
end
1st: 26 sec.
2nd: 30 sec.
3rd: 32 sec.
What's happening?
Any input would be much appreciated.
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> Dear fellows,
> The following loop bring me differents results, execute one after one
> (among
> them a truncate table, of course):
> DECLARE @.loop as integer
> set @.loop = 1
> while @.loop < 10000
> begin
> insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
> set @.loop = @.loop + 1
> end
>
> 1st: 26 sec.
> 2nd: 30 sec.
> 3rd: 32 sec.
> What's happening?
> Any input would be much appreciated.
> Enric
What other processes are running on your server while this is running.
Page splitting or allocation of new extents to store your data may be
happening.
Indexes may be getting updated
Statistics may be getting updated
It's hard to say what the real culprit is. These are some things that I
would check however.
Rick Sawtell
MCT, MCSD, MCDBA|||Well, what does your Profiler say?
ML
http://milambda.blogspot.com/|||Thanks to both for the quick responses but I was wondering about the
difference among them. It seems very high. We are talking about 6 or 7
seconds for a total of 35 more or less..
"Rick Sawtell" wrote:

> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> What other processes are running on your server while this is running.
> Page splitting or allocation of new extents to store your data may be
> happening.
> Indexes may be getting updated
> Statistics may be getting updated
>
> It's hard to say what the real culprit is. These are some things that I
> would check however.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Obtaining data from an EXEC

Hi all,

I have a stored procedure which returns a recordset of data. What I want to do is execute this stored procedure in another stored procedure and insert the recordset returned into a temporary table.

ie.

Stored Procedure Test1 may perform the following action: -
SELECT * FROM MyTable1

Stored Procedure Test2 would do something like this: -

EXEC Test1

INSERT INTO #TempTable {The recordset returned from Test1}

Any ideas how I would go about this?

Cheers,

Robchange sp Test2 to:

insert into #TempTable
execute Test1|||Novice Brain Surgeon is an appropriate labal, you're a star.

Cheers,

Rob|||Actually, I hated the "Guru" label and was trying to be cute.