John W. Colby
jwcolby at colbyconsulting.com
Mon Nov 8 06:27:23 CST 2004
Thanks Jim, I'll look at that.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence (AccessD)
Sent: Monday, November 08, 2004 1:16 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Dynamic from Access
Hi John:
You can either hand the request off to a (1) stored procedure or send it off
direct through a (2) 'execute' request. The processes assume ADO-OLE
referenced and Access unbound connection.
Given; assuming Windows authentication:
gstrConnection = "Provider=SQLOLEDB;Initial Catalog=MyDatabaseName;Data
Source=MyServerName;Integrated Security=SSPI"
1. Something Like; where results are required:
Dim objCmd As ADODB.Command
Dim rsMyResultRecordset As ADODB.Recordset
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = gstrConnection
.CommandText = "MyStoredProcedure"
.CommandType = adCmdStoredProc
' If Parameters are required
.Parameters.Append .CreateParameter("@intPKNumber", adInteger,
adParamInput, , MyPKNumberField) .Parameters.Append
.CreateParameter("@chvWholeNameField", adVarChar, adParamInput, 250,
MyWholeNameField) End With
Set rsMyResultRecordset = New ADODB.Recordset
With rsMyResultRecordset
.CursorLocation = adUseClient
.Open objCmd, , adOpenDynamic, adLockOptimistic
If .BOF = False Or .EOF = False Then .MoveLast
End With
Set objCmd = Nothing
...or...
2. Something like; where no return is necessary:
Dim objConn As ADODB.Connection
Dim strSQL As String
strSQL = "UPDATE MyTable " & _
"SET MyFirstField = " & intActive & " " & _
"WHERE MySecondField = True"
Set objConn = New ADODB.Connection
objConn.Open gstrConnection
objConn.Execute strSQL
This is just the bare bones layout. There is no error controls like
'BeginTrans' or 'RollBackTrans' or how the receiving Stored procedure should
be set up or how a variable is returned etc. but I hope this is enough to
get you started.
Jim
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of John W.
Colby
Sent: Sunday, November 07, 2004 7:50 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Dynamic from Access
I think I have figured out a strategy for the nVLDB - 64 million records,
~640 fields. The deal is that the table consists of sets of fields,
typically 10-20 fields, where the "subject" of the field is the same,
perhaps age, income, people in household, ethnicity, drugs taken, types of
music listened to, types of books read etc. I have started breaking out
these sets of fields into individual tables, with a PK field that is the PK
of the main table.
In other words I will now have a table with perhaps 12 fields that are yes /
no / null listens to music type x, y, z. Another table with perhaps 20
fields with yes / no / null takes zocor, etc. etc.
I will end up breaking the 640 fields down into perhaps 30 tables where each
table holds a set of fields from the original table. This allows me to
index each field which was not possible with the big table simply because
any given table can only contain 240 indexes.
The idea now is to be able to select a handful of these subtables, perhaps
income, age, music, reading and autos, join them back using the PK, then do
a where income >20k and <40K, age > 20 and <30, music = blues or rock,
etc... Then do a count(pk) to get a count of people having those
characteristics.
Additionally I can now get a count on the values in each field, 2 million
no, 250k yes, 60 million null "listens to rap" etc.
Sound feasible?
The issue now is that I need to do this stuff programmatically from Access
or perhaps a web environment (eventually). IOW, I need to build up a query
joining the income, age, music, reading and autos tables on PK, throw in the
where(s), and get a count of PK. I need to do this dynamically from VB (or
access), where I can let the user select tables to join (sets of related
fields), hand back lists of fields to select where values from, and lists of
values that are actually in those fields.
I can do all of that stuff, where I need assistance is how to hand the
finished SQL statement off to SQL Server and have it run the query, handing
back the count. I can't have Access (Jet) brute force it simply because the
tables each have 64 million records in them. Just getting the PKs back to
JET would take days.
So, how do I hand SQL Server a dynamically built SQL statement, ask it to
evaluate the SQL statement and return a single count value?
Eventually I also need to have SQL take that same where clause and hand me
back a set of fields / records from the main name / address subtable for
sending to the bulk mail client.
I am considering doing this in an ADP environment, where I build and save
the SQL as a UDF or something like that- not that I've ever done anything
like that before.
Or get out of Access entirely (though the data environment sure is nice) and
use .NET or something.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com