Activation

I was testing around with a sample service broker app using activation, and came across an interesting question. The little app sends a series of four messages to a queue, either on the same conversation or on seperate ones. Each message invokes one stored procedure in my activation procedure. All the procedure does is enter a record into a test table and then wait for an allotted amount of time. In my example, the first message called a proc that waited 20 sec, the 2nd one that waited 10 seconds, the third 5 seconds, and the 4th 1 second. I am using internal activation on the queue. It seemed that in both scenarios (sending on 4 separate conversations and on one conversation) the procedures executed "almost" sequentially. "Almost" meaning that the first procedure was done before the last one started executing. It makes sense to me that this would happen where I sent them on the same conversation, but not really when I sent them on 4 seperate ones. Is it because when I call a procedure from my activation procedure it locks the queue so that another message cannot be processed (I'm processing a message at a time)? How could I make it so that the 4th procedure (the one that only waits 1 second) returns before the 1st procedure (the one that waits 20 seconds)?MAX_QUEUE_READERS = ?|||

Sorry, my max_queue_readers where 5 in both situations.

Tim

|||

With one message per conversation then new instances of the stored procedure may be launched, so a message that arrived later may be processed sooner by a new instance activated procedure.

Also, even locally, the message order is only guaranteed within a conversation, so 5 messages sent on 5 conversation may arrive on the target queue in any order (unlikely to arive in different order, but one should not assume order between conversations)

|||You reference that new instances of a sproc may be launched for one message per convo, but what determines if it will or not? Will it only happen if some threshold is exceeded on the queue? The reason I was doing the little test was because I haven't seen any articles out there really detailing good ways to run large processing queries asynchronously for one application, so I was wanting to write one. My main curiosity was if there was any way to modify any of the settings for the procedure activation (which I don't think that there is).|||I just read your blog entry on Parallel Activation...neat stuff. Although, I doubt that it is something I would ever need to implement. I changed my procedures a bit so that they delay for a longer time period, and it appears that a new activation occurs ~ 5 seconds.

Accessing SQL2005

Lots of possibilities here - Missing index, insufficient RAM on DB or Client,
Network storm. What's the app doing during the hang? Use Profiler to see
what the DB is doing. Does 2005 SP1 or SP2 work any better?
"Timbo" wrote:

> I have a fairly heavy duty app that can use SQL2005, however I am getting
> some rather strange behaviour, if MSDE or SQL2000 is used, this doesn't
> appear to happen. Here's what happens:
> The VB6 application makes fairly regular calls on the database (only when
> the user requests data). At what appears to be completely random times
> (when the user makes a request for data) the application will lock up for
> anything up to 1 minute before carrying on. Obviously the user thinks the
> machine has crashed and starts clicking everywhere, which does make the
> application crash!
> This does not appear to happen with MSDE or SQL2000 databases. My
> application opens and maintains only one connection to the database, where
> all the SQL requests are piped through. I'm wondering if this is good
> practice? Also my application never maintains open recordsets, I always use
> SELECT to populate a screen, and only when the user hits a save button does
> the database get updated with UPDATE and INSERT SQL commands.
> If no one can shed any light on this specific problem, does anyone have a
> guide for best practices when it comes to connection states.
> Kind Regards
> Tim
>
>
I don't know the answer to this new question. I think you should create a
new post for this question. Maybe the SQL Server OLEDB group is probably
better.
"Timbo" wrote:

> I think I may have the answer.
> After reading about connection pooling, I think I might have my answer...
> Am I right in saying....
> At the moment my application opens a OLEDB connection when the user starts
> the application. At no point is the connection closed until the application
> quits (even then I don't think I have a close command), however I ALWAYS
> close and release recordsets and commands. If I was to close the connection
> every time I retrieved a recordset or executed a command (or whatever),
> because of connection pooling the performance wouldn't be hit, but each
> command will have a nice new fresh connection to use.
> What do you reckon?
>
> "Dave Michaud" <DaveMichaud@.discussions.microsoft.com> wrote in message
> news:4E6D21D8-087E-4894-98D8-460F6ABAA58F@.microsoft.com...
>
>
|||"Timbo" <me@.home.com> wrote in message
news:%236IXdlO4HHA.5724@.TK2MSFTNGP05.phx.gbl...
> I think I may have the answer.
> After reading about connection pooling, I think I might have my answer...
> Am I right in saying....
> At the moment my application opens a OLEDB connection when the user starts
> the application. At no point is the connection closed until the
application
> quits (even then I don't think I have a close command), however I ALWAYS
> close and release recordsets and commands. If I was to close the
connection
> every time I retrieved a recordset or executed a command (or whatever),
> because of connection pooling the performance wouldn't be hit, but each
> command will have a nice new fresh connection to use.
> What do you reckon?
>
<snipped>
That sounds backwards.
I think you meant you didn't release the ADODB.Connection object until the
Application quits. You should be opening and closing "connections" before
and immediately after you use one, the same as for Commands and Recordsets.
Amplification (or confusion <g>) follows...
When using Connection Pooling in ADO if you Open a Connection the pool
provides one. Period. Whether it gets the last one in a queue, or a new one,
is all dependent on what is going on. If there is no activity for a period
of time (it used to be like a second, it is now far less) then the
connection is closed automatically.
So when using ADO Connection Pooling, standard practice is to create one
ADODB.Connection object and open it when ever one is needed. And close it
when it is not. (Again don't destroy it, just open/close) You're never
actually "Closing" the connection, you merely signalling the POOL that it
can if it wants to. ie, helps manage the Pool manage its pool.
[Just to make sure we are talking about the same thing here.
If I create an object as in...
Dim cnn As ADODB.Connection: Set cnn = New ADODB.Connection.
I have created an object which has an ADO Pool associated with it.
This object creation does all the real grunt work, it figures out the
various middle layer components that are going to be used, and checks to
make sure they are available. It then seeks out the store and asks
permission to talk. ADO and the store work out the details. ADO then sets up
a virtual wire from your app to the store. But doesn't plug it in. With all
of the real time consuming stuff out of the way. ADO announces the
Connection object ready and then waits for instructions. When it receives an
Open then it plugs the wire in, on close, it unplugs it, on long delays it
unplugs the wire rather that waste a socket.
When I do a ...
cnn.Open blah blah
I am only asking the Pool for an open connection]
Thus forget about trying to micro-manage "Connections" if using the Pool.
Just let the Pool do its job. It is likely better at it than you are. <g>
What OLEDB provider are you using. You may need to upgrade.
hth
-ralph
|||Depending on the data source, getting a connection can take some time,
however, in my experience, acquiring a SQL Server connection (after the
first) takes less than a second--whether or not you're pooling.
And no, I don't agree that connection pooling makes sense or is at all
necessary for Windows Forms applications. I agree that it's essential for
ASP-based applications or XML Web Services, but not for Windows Forms. I
recommend opening the Connection and leaving it open for the life of the
application. Does this reduce scalability? Sure, to about 3000
connections--far more than any MSDE engine could handle.
I doubt if it's connection overhead that's causing the problem, but it would
be easy to tell. Set the ConnectionTimeout to a low value (say, 10 seconds)
and trap the error/exception. If it goes off you have something to debug.
Consider that SQL Server does not pre-allocate space in the database. From
time to time as new rows are added, it must go off an build extents and
expand the database. On a wimpy system or one that's being used for other
operations (where SQL Server must share resources with other processes),
this can take 10-30 seconds. To prevent the user from clicking and
restarting, be sure to "entertain" them with a progress bar--and make sure
to disable the mouse or the ability to click again in the form. However,
I've found that if the user sees an hourglass, a progress-bar or some other
indication that the app is running they won't reboot. Of course in some
cultures, the propensity to reboot is far shorter (as in New York) or far
longer (as in Arkansas) so your success-rate may vary.
I discuss these approaches in detail in my book.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Timbo" <me@.home.com> wrote in message
news:%236IXdlO4HHA.5724@.TK2MSFTNGP05.phx.gbl...
>I think I may have the answer.
> After reading about connection pooling, I think I might have my answer...
> Am I right in saying....
> At the moment my application opens a OLEDB connection when the user starts
> the application. At no point is the connection closed until the
> application quits (even then I don't think I have a close command),
> however I ALWAYS close and release recordsets and commands. If I was to
> close the connection every time I retrieved a recordset or executed a
> command (or whatever), because of connection pooling the performance
> wouldn't be hit, but each command will have a nice new fresh connection to
> use.
> What do you reckon?
>
> "Dave Michaud" <DaveMichaud@.discussions.microsoft.com> wrote in message
> news:4E6D21D8-087E-4894-98D8-460F6ABAA58F@.microsoft.com...
>
|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:u2%237GMQ4HHA.4436@.TK2MSFTNGP03.phx.gbl...
> Depending on the data source, getting a connection can take some time,
> however, in my experience, acquiring a SQL Server connection (after the
> first) takes less than a second--whether or not you're pooling.
> And no, I don't agree that connection pooling makes sense or is at all
> necessary for Windows Forms applications. I agree that it's essential for
> ASP-based applications or XML Web Services, but not for Windows Forms. I
> recommend opening the Connection and leaving it open for the life of the
> application. Does this reduce scalability? Sure, to about 3000
> connections--far more than any MSDE engine could handle.
> I doubt if it's connection overhead that's causing the problem, but it
would
> be easy to tell. Set the ConnectionTimeout to a low value (say, 10
seconds)
> and trap the error/exception. If it goes off you have something to debug.
> Consider that SQL Server does not pre-allocate space in the database. From
> time to time as new rows are added, it must go off an build extents and
> expand the database. On a wimpy system or one that's being used for other
> operations (where SQL Server must share resources with other processes),
> this can take 10-30 seconds. To prevent the user from clicking and
> restarting, be sure to "entertain" them with a progress bar--and make sure
> to disable the mouse or the ability to click again in the form. However,
> I've found that if the user sees an hourglass, a progress-bar or some
other
> indication that the app is running they won't reboot. Of course in some
> cultures, the propensity to reboot is far shorter (as in New York) or far
> longer (as in Arkansas) so your success-rate may vary.
> I discuss these approaches in detail in my book.
> hth
>
<snipped>
Whether ADODC.Connections should be Open/Closed is one of the few things I
mildly disagree with you on. I say mildly, because it is tough to defend
doing something that "probably" isn't all that necessary. The average
single-threaded client app likely doesn't need to open and close the
connection, since unless you are in a closed loop occasions where you would
ever need more than one connection at a time, seldom come up. And with the
quick time-out it would be equally difficult to keep one open anyway.
It reminds me of the same situation of whether or not to Set references
dim'd locally to Nothing, or just let the OS take care of it when the
procedure exits. Some will defend an explicit release arguing that one is
saving some clicks. (And perhaps measurable if you call the routine about
30,000 times. Others argue for completeness, that while it is unnecessary
99.9% of the time - there is always that one object, that migration to a
different environment, or chance encounter with a less attentive maintainer
who expands the routine, &etc.
I tend to side with the latter.
Both sides rarely, if ever, reach an agreement. <g>
-ralph

Accessing SQL Server with VB6

I need to write a VB app (VB6) to access and manipulate data within a MS SQL Sever 2000 database. I have experience doing this with MS Access. Is it much different/more difficult to do this with SQL Server?
ThanksNothing to it! Just change your connection string to something like...

Driver={SQL Server};Database=MyDB;UID=MyName;PWD=MyPwd;Server= MyServer

Likely won't need to change anything else.|||There are some differences in SQL format, though. The SQL delimiter for dates is the single apostrophe ('); Access uses the hash mark "#"

Also, if you've been using SQL functions within any of your queries, you may need to chenge them to T-SQL equivalents.|||True. As well, Access 97 (or rather Jet 3.5) used the asterisk (*) as its wildcard character. That changed to the percent sign (%) in Jet 4.0.|||Hey guys,

Thanks for the input. I just wanted to make sure that I wasn't signing up for anything too tricky.

Thanks for all the help|||At first it wouldn't seem that way ;)

Accessing SQL data from an ACCESS app...

I have just imported into SQL server all of my Access 2000 tables. In my
front end Access app, I now am using linked tables to access the data in the
SQL server db.
The speed isn't the greatest.
Is there a better connection type I can use in Access - other than ODBC that
provides better speed?
Thanks,
Brad"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I have just imported into SQL server all of my Access 2000 tables. In my
> front end Access app, I now am using linked tables to access the data in
the
> SQL server db.
> The speed isn't the greatest.
> Is there a better connection type I can use in Access - other than ODBC
that
> provides better speed?
> Thanks,
> Brad
The speed problem isn't ODBC - it's your code. You've only just begun the
journey of porting an Access app to SQL Server. For instance, you open a
recordset in code on two large tables using a JOIN. You only want to add one
record. But Access will fetch all records from both tables across the
network (locking them in the process), do the JOIN, move to the end, and
insert the field, then send it all back. If you turn on ODBC tracing and
excute a query, you'll see what I'm talking about (this will make it
*really* crawl - don't do it for long!). You need to do a lot of optimizing
and change many of your queries to SQL Pass-Throgh. I recommend you get a
good book - my favorite is Microsoft Access Developer's Guide to SQL Server
by Chipman and Baron, SAMS Publishing. Good luck!|||What is the difference /benefits of a pass-through query and any other SQL
query? I have never used a pass through query before..
Thanks,
Brad
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> the
> that
> The speed problem isn't ODBC - it's your code. You've only just begun the
> journey of porting an Access app to SQL Server. For instance, you open a
> recordset in code on two large tables using a JOIN. You only want to add
one
> record. But Access will fetch all records from both tables across the
> network (locking them in the process), do the JOIN, move to the end, and
> insert the field, then send it all back. If you turn on ODBC tracing and
> excute a query, you'll see what I'm talking about (this will make it
> *really* crawl - don't do it for long!). You need to do a lot of
optimizing
> and change many of your queries to SQL Pass-Throgh. I recommend you get a
> good book - my favorite is Microsoft Access Developer's Guide to SQL
Server
> by Chipman and Baron, SAMS Publishing. Good luck!
>|||A pass-through query passes the SQL statement to the server so that the
entire statement gets processed there, rather than returning lots of raw
data so that Access can process the query at the client computer. I'm
pretty sure you can find more information in the Access or SQL Server help.
I recently had surprisingly good results after upsizing an Access 2000
database to SQL Server, using only ODBC linked tables to SQL server 2000.
My users can now use the application at an acceptable speed from remote
high-speed VPN connected locations. Access Front-End/Back-end could never
have done that.
One thing I've done a lot of in Access is to write VBA functions to use in
Query criteria so I could fill in parameters in code without using SendKeys
to fill in parameter prompts. I use this method for Forms and Reports, and
I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
driver broke-down my queries so they were sent to SQL server with literal
parameters, and I got back only the records I was looking for. You can use
SQL Server Profiler to see the SQL statements that get sent to your server
from Access - that can give you a lot of insight into what's going on in you
app.
I'm pretty sure filters still get applied locally, so you don't want to rely
on those to be your initial data filters on large recordsets.
Another way to bring improved performance would be to use and Access data
project file (.ADP), which provides a more true Client/Server application.
I thought I was going to have to go that route so that my application would
work for remote high-speed VPN users, but my app worked so well with ODBC
linked tables that I didn't have to go to the work of largely re-doing my
application.
Another book set I would recommend is "Access Developer's Handbook Set" by
Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> one
> optimizing
> Server
>|||You really should only be calling stored procedures from pass-through
queries. In addition, you shouldn't have linked tables at all if you want a
really scalable enterprise application. Use the VBA recordset and
passthrough queries calling stored procedures. Let the database server
perform the database work most efficiently using stored procedures.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
my[vbcol=seagreen]
in[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
> one
> optimizing
a[vbcol=seagreen]
> Server
>|||So, you are saying do not use linked tables at all. Could you give me a
snippet of Access code that opens an SQL Server Db and calls a stored
procedure to do something simple such as select * from a table?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||That's great information...
When you are referring to writing an Access query that uses a VBA function
for it's criteria to fill in the parameters (I've never even used SendKeys
to fill in parameter prompts before), are you referring to replacing things
like "[Enter Customer Name]" as a row criteria with a function such as
GetCustName()' where function GetCustName() would display a screen where
the user enters the customers name they are looking for and then you set
GetCustName = txtCustName?
Also what is the ADP you referred to? Is this a reference you add in to the
project? Never even heard of that one!!
Thanks,
Brad
That sounds interesting and is likely something I'd want to do...
"Dynamo" <noone@.nowhere.com> wrote in message
news:uPsFHJPuEHA.3152@.TK2MSFTNGP14.phx.gbl...
> A pass-through query passes the SQL statement to the server so that the
> entire statement gets processed there, rather than returning lots of raw
> data so that Access can process the query at the client computer. I'm
> pretty sure you can find more information in the Access or SQL Server
help.
> I recently had surprisingly good results after upsizing an Access 2000
> database to SQL Server, using only ODBC linked tables to SQL server 2000.
> My users can now use the application at an acceptable speed from remote
> high-speed VPN connected locations. Access Front-End/Back-end could never
> have done that.
> One thing I've done a lot of in Access is to write VBA functions to use in
> Query criteria so I could fill in parameters in code without using
SendKeys
> to fill in parameter prompts. I use this method for Forms and Reports,
and
> I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
> driver broke-down my queries so they were sent to SQL server with literal
> parameters, and I got back only the records I was looking for. You can
use
> SQL Server Profiler to see the SQL statements that get sent to your server
> from Access - that can give you a lot of insight into what's going on in
you
> app.
> I'm pretty sure filters still get applied locally, so you don't want to
rely
> on those to be your initial data filters on large recordsets.
> Another way to bring improved performance would be to use and Access data
> project file (.ADP), which provides a more true Client/Server application.
> I thought I was going to have to go that route so that my application
would
> work for remote high-speed VPN users, but my app worked so well with ODBC
> linked tables that I didn't have to go to the work of largely re-doing my
> application.
> Another book set I would recommend is "Access Developer's Handbook Set" by
> Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
a[vbcol=seagreen]
>|||Can you also give me a simple example (and code) of a passthrough SQL query
calling a stored procedure at the SQL Database level?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
> a
>|||"Brad Pears" <donotreply@.notreal.com> wrote in message
news:eHmgt0RvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Can you also give me a simple example (and code) of a passthrough SQL
query
> calling a stored procedure at the SQL Database level?
> Thanks,
> Brad
I have a sub called SQLExecute that I use for Pass-Throughs. Here it is:
Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionaly returns
records
On Error GoTo Error_SQLExecute
Dim qdf As QueryDef
Dim errAny As error
Set qdf = DBEngine(0)(0).CreateQueryDef("")
qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL
If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
End If
Exit_SQLExecute:
Set qdf = Nothing
Exit Sub
Error_SQLExecute:
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " = " &
errAny.Description, vbCritical, "Error " & errAny.Number & " - SQLExecute"
Next
Resume Exit_SQLExecute
End Sub
In this sub, what makes it a Pass-Trhough is setting the QueryDef's Connect
property. In my case, the Connect string is set on app open to a global
variable called "gstrODBC". To get a value to sote in gstrODBC, open the
Debug window in your Access app (Ctrl-G) and type this followed by the Enter
key:
?CurrentDb.TableDefs("any_linked_table_name").Connect
The returned string should be used as the Connect property for the query
def. For the SQL parameter to SQLExecute, use any valid SQL statement or
query, including stored procedures with parameters. The one caveat is it
must be SQL syntax that the *server* understands, *not* Access's SQL syntax.
The error handler will return messages from the SQL Server if there is a
syntax error, etc.

> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
want[vbcol=seagreen]
> a
> SQL
> In
> data
begun[vbcol=seagreen]
open[vbcol=seagreen]
> a
> add
the[vbcol=seagreen]
> and
> and
> get
>

Accessing SQL data from an ACCESS app...

I have just imported into SQL server all of my Access 2000 tables. In my
front end Access app, I now am using linked tables to access the data in the
SQL server db.
The speed isn't the greatest.
Is there a better connection type I can use in Access - other than ODBC that
provides better speed?
Thanks,
Brad
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I have just imported into SQL server all of my Access 2000 tables. In my
> front end Access app, I now am using linked tables to access the data in
the
> SQL server db.
> The speed isn't the greatest.
> Is there a better connection type I can use in Access - other than ODBC
that
> provides better speed?
> Thanks,
> Brad
The speed problem isn't ODBC - it's your code. You've only just begun the
journey of porting an Access app to SQL Server. For instance, you open a
recordset in code on two large tables using a JOIN. You only want to add one
record. But Access will fetch all records from both tables across the
network (locking them in the process), do the JOIN, move to the end, and
insert the field, then send it all back. If you turn on ODBC tracing and
excute a query, you'll see what I'm talking about (this will make it
*really* crawl - don't do it for long!). You need to do a lot of optimizing
and change many of your queries to SQL Pass-Throgh. I recommend you get a
good book - my favorite is Microsoft Access Developer's Guide to SQL Server
by Chipman and Baron, SAMS Publishing. Good luck!
|||What is the difference /benefits of a pass-through query and any other SQL
query? I have never used a pass through query before..
Thanks,
Brad
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> the
> that
> The speed problem isn't ODBC - it's your code. You've only just begun the
> journey of porting an Access app to SQL Server. For instance, you open a
> recordset in code on two large tables using a JOIN. You only want to add
one
> record. But Access will fetch all records from both tables across the
> network (locking them in the process), do the JOIN, move to the end, and
> insert the field, then send it all back. If you turn on ODBC tracing and
> excute a query, you'll see what I'm talking about (this will make it
> *really* crawl - don't do it for long!). You need to do a lot of
optimizing
> and change many of your queries to SQL Pass-Throgh. I recommend you get a
> good book - my favorite is Microsoft Access Developer's Guide to SQL
Server
> by Chipman and Baron, SAMS Publishing. Good luck!
>
|||A pass-through query passes the SQL statement to the server so that the
entire statement gets processed there, rather than returning lots of raw
data so that Access can process the query at the client computer. I'm
pretty sure you can find more information in the Access or SQL Server help.
I recently had surprisingly good results after upsizing an Access 2000
database to SQL Server, using only ODBC linked tables to SQL server 2000.
My users can now use the application at an acceptable speed from remote
high-speed VPN connected locations. Access Front-End/Back-end could never
have done that.
One thing I've done a lot of in Access is to write VBA functions to use in
Query criteria so I could fill in parameters in code without using SendKeys
to fill in parameter prompts. I use this method for Forms and Reports, and
I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
driver broke-down my queries so they were sent to SQL server with literal
parameters, and I got back only the records I was looking for. You can use
SQL Server Profiler to see the SQL statements that get sent to your server
from Access - that can give you a lot of insight into what's going on in you
app.
I'm pretty sure filters still get applied locally, so you don't want to rely
on those to be your initial data filters on large recordsets.
Another way to bring improved performance would be to use and Access data
project file (.ADP), which provides a more true Client/Server application.
I thought I was going to have to go that route so that my application would
work for remote high-speed VPN users, but my app worked so well with ODBC
linked tables that I didn't have to go to the work of largely re-doing my
application.
Another book set I would recommend is "Access Developer's Handbook Set" by
Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> one
> optimizing
> Server
>
|||You really should only be calling stored procedures from pass-through
queries. In addition, you shouldn't have linked tables at all if you want a
really scalable enterprise application. Use the VBA recordset and
passthrough queries calling stored procedures. Let the database server
perform the database work most efficiently using stored procedures.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
my[vbcol=seagreen]
in[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
> one
> optimizing
a
> Server
>
|||So, you are saying do not use linked tables at all. Could you give me a
snippet of Access code that opens an SQL Server Db and calls a stored
procedure to do something simple such as select * from a table?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a[vbcol=seagreen]
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get
> a
>
|||That's great information...
When you are referring to writing an Access query that uses a VBA function
for it's criteria to fill in the parameters (I've never even used SendKeys
to fill in parameter prompts before), are you referring to replacing things
like "[Enter Customer Name]" as a row criteria with a function such as
GetCustName()? where function GetCustName() would display a screen where
the user enters the customers name they are looking for and then you set
GetCustName = txtCustName?
Also what is the ADP you referred to? Is this a reference you add in to the
project? Never even heard of that one!!
Thanks,
Brad
That sounds interesting and is likely something I'd want to do...
"Dynamo" <noone@.nowhere.com> wrote in message
news:uPsFHJPuEHA.3152@.TK2MSFTNGP14.phx.gbl...
> A pass-through query passes the SQL statement to the server so that the
> entire statement gets processed there, rather than returning lots of raw
> data so that Access can process the query at the client computer. I'm
> pretty sure you can find more information in the Access or SQL Server
help.
> I recently had surprisingly good results after upsizing an Access 2000
> database to SQL Server, using only ODBC linked tables to SQL server 2000.
> My users can now use the application at an acceptable speed from remote
> high-speed VPN connected locations. Access Front-End/Back-end could never
> have done that.
> One thing I've done a lot of in Access is to write VBA functions to use in
> Query criteria so I could fill in parameters in code without using
SendKeys
> to fill in parameter prompts. I use this method for Forms and Reports,
and
> I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
> driver broke-down my queries so they were sent to SQL server with literal
> parameters, and I got back only the records I was looking for. You can
use
> SQL Server Profiler to see the SQL statements that get sent to your server
> from Access - that can give you a lot of insight into what's going on in
you
> app.
> I'm pretty sure filters still get applied locally, so you don't want to
rely
> on those to be your initial data filters on large recordsets.
> Another way to bring improved performance would be to use and Access data
> project file (.ADP), which provides a more true Client/Server application.
> I thought I was going to have to go that route so that my application
would[vbcol=seagreen]
> work for remote high-speed VPN users, but my app worked so well with ODBC
> linked tables that I didn't have to go to the work of largely re-doing my
> application.
> Another book set I would recommend is "Access Developer's Handbook Set" by
> Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
a
>
|||Can you also give me a simple example (and code) of a passthrough SQL query
calling a stored procedure at the SQL Database level?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a[vbcol=seagreen]
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get
> a
>
|||"Brad Pears" <donotreply@.notreal.com> wrote in message
news:eHmgt0RvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Can you also give me a simple example (and code) of a passthrough SQL
query
> calling a stored procedure at the SQL Database level?
> Thanks,
> Brad
I have a sub called SQLExecute that I use for Pass-Throughs. Here it is:
Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionaly returns
records
On Error GoTo Error_SQLExecute
Dim qdf As QueryDef
Dim errAny As error
Set qdf = DBEngine(0)(0).CreateQueryDef("")
qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL
If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
End If
Exit_SQLExecute:
Set qdf = Nothing
Exit Sub
Error_SQLExecute:
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " = " &
errAny.Description, vbCritical, "Error " & errAny.Number & " - SQLExecute"
Next
Resume Exit_SQLExecute
End Sub
In this sub, what makes it a Pass-Trhough is setting the QueryDef's Connect
property. In my case, the Connect string is set on app open to a global
variable called "gstrODBC". To get a value to sote in gstrODBC, open the
Debug window in your Access app (Ctrl-G) and type this followed by the Enter
key:
?CurrentDb.TableDefs("any_linked_table_name").Conn ect
The returned string should be used as the Connect property for the query
def. For the SQL parameter to SQLExecute, use any valid SQL statement or
query, including stored procedures with parameters. The one caveat is it
must be SQL syntax that the *server* understands, *not* Access's SQL syntax.
The error handler will return messages from the SQL Server if there is a
syntax error, etc.
[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
want[vbcol=seagreen]
> a
> SQL
> In
> data
begun[vbcol=seagreen]
open[vbcol=seagreen]
> a
> add
the
> and
> and
> get
>

accessing report server programmatically

Hi friends
I have .net app ,where i display reports from my report server in a screen .,which works fine.
I added a web reference to my report server ,to my solution to get it work.

since i know my report server name on machine i added webreference to my project. but when i deploy at site ,as you can guess, the server name can be different.

is it possible add web reference programmaically so that i dont need to hard code report server name ?

am using sql server standard 2005,VS2005 standard edition.
Thank you very much for your help

in the place where you instantiate the webservice client:

MyReportServer rs = new MyReportServer();
rs.Url = "http://myservername/reportserver/blabla.asmx";

|||

Alexandre

Thanks for the post.

do you happen to know any sample code i can refer to.

Thanks for your help again.

|||Alexandre
i cant find "MyReportServer" namespace!
what library i need to give reference to?
Thanks for your help
|||try web.config file... i think u got it... define it in web.config file and change it whenever the server name changes|||

When you add a web reference VS will add a C# file to your project with web service endpoint definition. Then in some place in your program you make a call to the web service. the web service will be represented by an object. I refered to the type of the object MyReportServer, mostlikely it will be called differently in your app. This object has Url property that you may assign any new report server URL you want. Just find the place where the object is created.

By default, "MyReportServer" class pulls webservice URL from your config file. You can reset it by assigning new url via Url property

|||

You can use the following in your web.config:

<appSettings>
<add key="ServerURL" value=http://someServer/path/method.aspx />

</appSettings>

And in your code:

reportService.Url = ConfigurationSettings.AppSettings["ServerURL"];

|||Alexandre
if i understood you correctly, i just need to add a web reference to one of in-house report servers ,to our solution but when using it at client sites i need update "URL" property of the web service object by reading either from a config file or from a database field.
is this correct ?
Thanks for your help|||Hi Jay
its a windows app and i dont have a web.config.|||

>> if i understood you correctly

yes.

|||Thanks Alexandre
i already tried and it works nicely.
Thank you very much for your help :)

Accessing Replication Logs via RMO

Hello! I am writing an app with C# that is using SQL Server 2005's RMO objects. I want to display a tree control with say a log history (last successful sync, dates, etc...). I know that the server stores this kind of info somewhere but I'm not sure how to access this via RMO.

Can anybody point me in the right direction? Thanks!

Hi,

The following page in BOL might include the information you need: "How to: prorammatically Monitor Replication (RMO programming)" (http://msdn2.microsoft.com/en-us/library/ms147926.aspx).

-Peng

|||That's it thank you!!!|||

You may also find this helpful...

http://msdn2.microsoft.com/en-US/library/ms146899.aspx