SQL Server

[J Reed]

I am working on a project where I will be selecting an n'th number of
records for 700 reps based on zip codes.

My question is the below sql statement worked on an access table...

sSQL = "Update OA Set OA.RepID = " & rsRep!RepID & " " _
& "Where OA.PrimaryID In (Select top " & lTempRequest & " " _
& "PrimaryID from OA where zip ='" & rsRep(sZipSelect) & "'" _
& "and RepID is null)"

but the above statment doesn't work against a link ODBC connection to
SQL server table. It executes but just hangs up.

I can issue the following statement in T-SQL on SQL server no problem.

update oa set repid = 2 where primaryid in (select top 5000 primaryid
from oa where zip = '30121' and repid is null)

This is basically the exact same command as the access query. Does
anyone know why one works and the other doesn't?

I have got a fix in place by using a stored procedure and calling that
through access. Probably a better way of doing it anyways...

Thanks...

[Rich P]

One issue is that ODBC is old technology, becoming outdated. There may
be other issues, based on your setup, but ODBC technology is one of the
bigger issues. ADO technology is much more reliable and way faster
because it has way more bandwidth than ODBC.

There are two kinds of ADO, com ADO and ADO.Net. For Access you would
use com ADO. For that you need to download Mdac2.5 and Mdac2.6.
Mdac2.5 contains Jet3.51 or 3.6 or something that you need for Jet.
Mdac2.6 is the upgraded version of 2.5 (more reliable). Mdac2.7+ I
believe is for .Net (maybe 2.8, but you don't need this for Access, but
definitely Mdac2.5 and 2.6).

ADO uses disconnected datasets where ODBC is a constant connection to
the datasource, thus ODBC has way lower bandwidth (it takes a lot of
resources to stay connected to the data continuously). With ADO, you
can invoke a sql server stored procedure, retrieve the data and then
close the connection. Here is a sample of ado for getting data from a
sql server sp - invoked from a standard module in an Access97 mdb:

Sub GetdataFromSqlSrv()
Dim cmd As New ADODB.Command, conn As New ADODB.Connection
Dim RSado As New ADODB.Recordset, RSdao As DAO.Recordset
Dim i As Long, j As Long, k As Integer, RetVal As Variant
Dim eDate As Date, sDate As Date, d as date
Dim sTim As Single, eTim As Single, totTim As Single

sTim = Timer
cmd.ActiveConnection="Provider=SQLOLEDB;Data Source=srv1;" _
& "Initial Catalog=myDB;UID=SA;PWD=abc;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_GetData"
d = #1/1/2004#
sDate = Month(d) & "/1/" & Year(d)
eDate = DateAdd("m", 1, sDate) - 1 'get one month of data
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenRecordset("tbl1")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i) 'copy data from each field
Next
RSdao.Update 'update dao recordset
RSado.MoveNext
Loop
cmd.ActiveConnection.Close 'close ado connection
eTim = Timer
totTim = eTim - sTim
totTim = (eTim - sTim) / 60 'get time in minutes
RetVal = SysCmd(acSysCmdSetStatus, "Total Time is " & totTim & " minutes
Or Total Time is " & totTim * 60 & " seconds")

End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Alan Webb]

J Reed,
If you've got it working in T-SQL then leave it there. T-SQL rocks over VBA
& Jet.

"J Reed" <re*********@hotmail.com> wrote in message
news:42**************************@posting.google.c om...

I am working on a project where I will be selecting an n'th number of
records for 700 reps based on zip codes.

My question is the below sql statement worked on an access table...

sSQL = "Update OA Set OA.RepID = " & rsRep!RepID & " " _
& "Where OA.PrimaryID In (Select top " & lTempRequest & " " _
& "PrimaryID from OA where zip ='" & rsRep(sZipSelect) & "'" _
& "and RepID is null)"

but the above statment doesn't work against a link ODBC connection to
SQL server table. It executes but just hangs up.

I can issue the following statement in T-SQL on SQL server no problem.

update oa set repid = 2 where primaryid in (select top 5000 primaryid
from oa where zip = '30121' and repid is null)

This is basically the exact same command as the access query. Does
anyone know why one works and the other doesn't?

I have got a fix in place by using a stored procedure and calling that
through access. Probably a better way of doing it anyways...

Thanks...

[Trevor Best]

J Reed wrote:

I am working on a project where I will be selecting an n'th number of
records for 700 reps based on zip codes.

My question is the below sql statement worked on an access table...

sSQL = "Update OA Set OA.RepID = " & rsRep!RepID & " " _
& "Where OA.PrimaryID In (Select top " & lTempRequest & " " _
& "PrimaryID from OA where zip ='" & rsRep(sZipSelect) & "'" _
& "and RepID is null)"

but the above statment doesn't work against a link ODBC connection to
SQL server table. It executes but just hangs up.

I can issue the following statement in T-SQL on SQL server no problem.

update oa set repid = 2 where primaryid in (select top 5000 primaryid
from oa where zip = '30121' and repid is null)

This is basically the exact same command as the access query. Does
anyone know why one works and the other doesn't?

I have got a fix in place by using a stored procedure and calling that
through access. Probably a better way of doing it anyways...

Thanks...

Use Profiler to trace the SQL being generated, in your original query
against the linked table you will probably see a stream of stored
procedures being prepared then executed, this is typically 1 pair of sp
calls per row in your database, maybe double if the selection goes like
that as well, throw in a join or two and with an infinate timeout it
will probably go on all day and night and some of the following morning.
Hell I bet it's even doing that select top 5000 for each row.

It highlights how inefficient it is to just simply link tables when
upsizing to SQL Server., only the simplest of queries will work well.
You should go ahead and do as you did and execute directly on the server.

BTW, in SQL Server, using "where exists()" is generally more efficient
than using "where in ()", the opposite of what is generally true for Jet
tables. Don't worry about using "exists(select * from...) notation, SQL
Server won't actually do a "select * from" and discard a bunch of
columns, it will translate it to "exists(select 1 from ...).

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[David Schofield]

On 30 Sep 2004 21:19:59 GMT, Rich P <rp*****@aol.com> wrote:

One issue is that ODBC is old technology, becoming outdated. There may
be other issues, based on your setup, but ODBC technology is one of the
bigger issues. ADO technology is much more reliable and way faster
because it has way more bandwidth than ODBC.

(more which I have snipped)

Hi
Yes, all the db vendors take care to make their own native methods the
most efficient way of using their system. The last thing they want is
to make it too easy to move to another product.

This suits developers as well!

Or am I too cynical?

David