Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2002
    Location
    Sydney
    Posts
    21

    Unanswered: SQL Server 7 dropping primary keys

    Hi all.

    I table a table which has an integer field which is the primary key. Updates to the tables from the management console work OK, as do updates via ADO from Access. However when I go to link the table in Access, I am asked which field has the unique record, as if SQL Server isn't telling Access about the primary key.

    This wouldn't be a problem normally (I can just select the field which has the primary key and then Access can get hold of the table), but I'm editing the tabledef connection string in code when the database starts up (we're testing running Access under Crossover Office, which is a commercial port of Wine, which allows you to run Windows apps under Linux) and when the connection is made in VB, you can't (that I can tell) specify which is the primary key.

    I would rather not drop the table and re-create it as last time I did that I remember being VERY sorry (referential integrity has never been quite the same since).

  2. #2
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76

    Question

    I remember that same exact thing happening to me about 4 years ago (yeah, that dialog box is a pain if you are trying to automate something.)

    I'm racking my brain trying to remember what I did to fix it. But, I think it had something to do when we converted the databases from SQL Server 6.5 to 7.0.

    Maybe we did an EXEC sp_updatestats on the databases...I don't recall for sure.

    On the Access end, I do remember trying send keys and other things to try to set and get rid of that dialog box, but I vaguely remember I couldn't get it to work.

    I don't know how you are doing the tabledef in code, maybe you want to examine different routes to bypass that situation???

    HTH!

    Kael

  3. #3
    Join Date
    Feb 2002
    Location
    Sydney
    Posts
    21

    Fixed it!

    Thanks for the reply Kael.

    I managed to fix the problem by moving the table and a bunch of related tables into MySQL. We are part-way through migrating to MySQL anyway, and this has been the motivation we needed to pick up the pace a bit.

  4. #4
    Join Date
    Feb 2003
    Posts
    109

    mysql

    can you plz talk a little more about how you're doing mysql with access, how you ilke it; how it scales, etc?

    ive been studying mysql for a few weeks now (i have to learn oracle and mysql at the same time.. so it goes a little slow)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  5. #5
    Join Date
    Feb 2002
    Location
    Sydney
    Posts
    21

    MySQL rocks actually...

    MySQL has gotten us out of more than 1 such problem. With 50 or so users connecting via Access (XP), SQL Server started to give us record locking issues about 18 months ago. It seems to be because Access requests a row-level lock, but SQL Server isn't able to give one, and instead issues page-level or table-level locks. I initially moved affected tables into MyISAM tables in MySQL (MySQL supports various table handlers which have a variety of features - it allows you to get maximum performance in tables where you don't need, for example, transaction support, or row-level locking, etc, but also to get those features in places where you need them), which uses table-level locks, and surprisingly this worked well for each table for about another 2-3 months. Then the locking issues came up again, so I moved stuff into InnoDB tables, which uses row-level locks, and haven't had any problems since. The affected tables have trippled in size since then...

    We've also found MySQL to be much faster than SQL Server, especially when the tables get larger. We have a table with a LOT of telecommunications data that 10 or so girls are entering data into non-stop. In SQL Server, it would take 15 or 20 seconds to open the data entry screen, and 2-3 seconds to insert each record. In MySQL (with the InnoDB table handler), it takes 2 seconds to open the data entry screen and no time at all to append records.

    We are also getting very impressive performance by setting up pass-through queries in Access (as well as using ADO for more advanced stuff). I use VB to modify the pass-through query's SQL and use them the same way I use SQL Server stored procedures.

    Basically the only thing holding us back from an all-out migration to MySQL is our heavy use of stored prodecures and views in our billing system. If you break one table away from SQL Server you have to rewrite a substantial number of queries. We have learned our lesson though, and are doing all new developement in MySQL and slowing moving our billing data across. The issue I had (original post) was one of those cases that caused a major reorganisation of queries, but turned out to be the only option that worked.

    I would certainly recommend MySQL to developers. It's free, incredibly fast, very stable, cross-platform, and secure. And the pace of development lately has been very encouraging. Definitely the database server to watch...

  6. #6
    Join Date
    Feb 2003
    Posts
    109

    you had sql locking problems

    so you're saying you had locking problems on sql server using mdb with linked tables or SPT?

    i personally would have just gotten rid of mdb and moved to adp-- it is worlds simpler/faster than mdb.

    did you try using the 'WITH (NOLOCK) clause in SQL Server?

    or something like that?

    i do a bunch of data warehousing on sql server and i had some batches that were taking an hour. which wasnt that big of a deal-- but still it kinda sucked to test..

    so yesterday, i rewrote about a dozen (out of 200) views with the WITH (NOLOCK) clause and it worked like a charm.

    my processing time went from an hour to 8 minutes. i was pretty stoked about that.

    re: 'I use VB to modify the pass-through query's SQL and use them the same way I use SQL Server stored procedures.'

    this means that you have a different copy of each MDB on each desktop, right?

    because people cant edit the same SPT in the same MDB at the same time, right?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  7. #7
    Join Date
    Feb 2002
    Location
    Sydney
    Posts
    21
    so you're saying you had locking problems on sql server using mdb with linked tables or SPT?

    Linked tables

    i personally would have just gotten rid of mdb and moved to adp-- it is worlds simpler/faster than mdb

    Yes I would like to move away from Access. I've ported out sales system to LAMP (Linux, Apache, MySQL, PHP) so our sales people can access the database on the road. At first I thought it would takes AGES to do, but once you get going it only takes about twice as long as doing it in Access, and the rewards far outweight the extra time it takes. The problem for us is that there is about 10 man-years worth of development in our Access front-ends, and it would take a very long time to convert everything. A very, very long time. I had a look at Borland's Delphi / Kylix, but at the time it (Kylix) was kinda unstable and rough around the edges. Apparently it's much better now. But I'm learning Python / Perl, and they seem to be just what I need. After a few more months of playing around at home, I'm going to try making a Python / GTK interface. Python is a very nice (cross-platform) language with a big community (though not a large as perl). And GTK is a very powerful (also cross-platform) GUI toolkit. They both go together to make an incredibly lightweight, fast, stable GUI front-end that blows Access away. Access is just too big and unstable and slow and buggy and expensive. And then there's the old Microsoft lock-in. Yes Access has to go. Soon...

    did you try using the 'WITH (NOLOCK) clause in SQL Server?

    Not an option. We need record locking. We have people who can potentially be editing the same record. And it's a very dodgy workaround for a problem that SQL Server simply shouldn't have to start with. I'd rather take MySQL's row-level locking ... which is what we've done. MySQL is win:win for us in this area.

    re: 'I use VB to modify the pass-through query's SQL and use them the same way I use SQL Server stored procedures.

    this means that you have a different copy of each MDB on each desktop, right?'


    That's right. We have 2 licences for Office XP developer and use the 'packaging wizard' to create run-time versions of Access which we can deploy on any computer we wish. And yes each person has their own local mdb file. Even though you can have more than 1 person accessing an mdb at a time I consider that quite foolish. Access and the mdb file is designed for the desktop only. Any success at getting it to work multi-user is due purely to luck.

  8. #8
    Join Date
    Feb 2003
    Posts
    109

    more about locking

    more about locking--

    just for the record, you could have used the 'WITH (ROWLOCK)' clause in SQL Server to get around it. I thought that was the default.

    i just dont trust Access MDB at all and
    --------------------------------------------------------
    i feel for you about the huge investment in Access VBA-- Right now, I work for a company that has 1000s' of MDBs that were poorly written with no naming conventions. At least from the 40-50 ive seen so far.

    after that, we have thousands of beancounters sitting around recording Excel Macros--

    ((for some reason, i am suddenly SO GLAD that Access doesnt allow you to record macros...))

    im basically redoing everything in sql server with DTS and OLAP (which is AWESOME). i love OLAP more than anything else in the whole wide world.

    i know that SQL Server OLAP is 10x faster than anything that mySql can bring to the table. I know it. End of story.

    i dont think that mySql has anything like olap or dts, right?
    or stored procs, views, indexes, parameters, etc--

    can you survive without these things? How??

    trust me, i am a fan of mySql-- i want to believe in the power of the people--

    i have wanted to run off into fantasy land where mySql can compete-- i believe in the idea of hundreds of desktops with linux and mysql-- i just dont think that its going to happen for me for another 5 years or something.

    plz explain more!!!! this is the most exciting conversation ive had in months...

    ps - did you have the mysql and sql server on the same type of hardware? are you sure its' really faster??
    is it a hard adjustment?

    did you have to learn java?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  9. #9
    Join Date
    Feb 2002
    Location
    Sydney
    Posts
    21
    i know that SQL Server OLAP is 10x faster than anything that mySql can bring to the table. I know it. End of story.

    MySQL doesn't currently support OLAP. It is planned for the next release (4.1) which is available is source form now:

    http://www.mysql.com/doc/en/TODO_MySQL_4.1.html

    i dont think that mySql has anything like olap or dts, right?
    or stored procs, views, indexes, parameters, etc--


    OLAP - no. See above.

    DTS - yes and no. See my example at the bottom. MySQL supports exporting and importing from every format you can throw at it with the 'mysqlimport' and 'mysqlexport' utilities. When we import customer data (telecommunications call data) via SQL Server's DTS, it takes about 4 times longer than mysqlimport. Admittedly there is no gui, but it doesn't take long to build a set of scripts to handle all situations. I don't mind coding - I'm a programmer, after all. MySQL doesn't support connecting to other databases directly, however you can use a 3rd party tool, or Access (God forbid) to act as the go-between.

    Stored Procedures - we use VB / ADO to do the complicated stuff. This works fine. MySQL is picking up stored procedures (Oracle style) at the end of the year, in MySQL-5.0:

    http://www.mysql.com/doc/en/TODO_MySQL_5.0.html

    Views - we use Access pass-through queries with dynamically updated SQL (from VB) for simple views. For more complicated ones, we use MySQL's temporary tables (which are unique to each connection). It's a bit of a dodgy workaround for now. Views are coming in version 5 (see above).

    Indexes - WTF? Of course MySQL has indexes!

    Parameters - yes. Apparently. But I don't use them.

    ps - did you have the mysql and sql server on the same type of hardware? are you sure its' really faster??

    SQL Server 7 (service pack 4) is running on a Dual 400Mhz Xeon with 256MB on Windows NT4 (service pack 6).

    MySQL 4.0.12 is running on a single 500Mhz AMD K6-2 (!) with 256MB on Linux-2.4.20 (distro = Slackware 8.0).

    Despite having 1 less CPU, MySQL is between 2 and 3 times faster in most tasks - opening tables, opening ADO connections, running pass-through queries, inserting records. In tables where I use the MyISAM table handler (not transactional, no foreign key constraints, table-level locking) I get between 5 and 10 times the performance. So yes, I'm sure MySQL is MUCH faster. I can't wait until I get some REAL hardware for it...

    plz explain more!!!! this is the most exciting conversation ive had in months...

    OK. For our sales database (which is purely based on LAMP - Linux, Apache, MySQL, PHP), our salespeople wanted to see their performance stats (being $$$ driven) so I'm using a graphing package called JPGraph to create dynamic graphs for them. JPGraph is a php-based graphing library that is far more feature-packed and stable than the Microsoft Graph version that is packed into Access XP. We were using Access' graphing package to create some bar graphs with nice alpha-blended bars. But if you open the graph with, for example, 4 different bars, change the colours to your liking, save it, close it, open it with data which gives you less bars this time, say 3, then when you go back to the data that gives 4 bars, the last bar has lost all its settings (not just colour - everything). Not very smart. JPGraph and PHP allowed us to create a proffesional looking site with live data that can be accessed securely from any computer with internet access. Our sales system will certainly be staying with PHP.

    I've converted ALL of our Telecom supplier databases and DTS packages to MySQL. DTS was too slow for large imports (we do a fair few of them) and SQL Server was becoming quite unresponsive due to the large number of tables with large amounts of temporary data:

    mysql> use ebills;
    Database changed
    mysql> show tables;
    +-----------------------------------+
    | Tables_in_ebills |
    +-----------------------------------+
    | AAPT_CallData |
    | AAPT_CallTypeIntegration |
    | AAPT_Discounts |
    | AAPT_RebillData |
    | AAPT_Service |
    | DigiPlus_Data |
    | DigiPlus_IntegrationMap |
    | EBS_CallTypeIntegration |
    | EBS_LineTypeIntegration |
    | EBS_MobileCallTypeMapping |
    | EBS_OrphanedLineMapping |
    | EBS_OrphanedOCnCMapping |
    | LCR_CallData |
    | LCR_CallTypeIntegration |
    | LCR_Lines |
    | LineTypeCodes |
    | MacquarieCSV_CallData |
    | MacquarieCSV_Integration |
    | MacquarieCSV_LineTypeIntegration |
    | Macquarie_CallDetails |
    | Macquarie_LineTypeIntegration |
    | Macquarie_Summary |
    | Macquarie_TLP_Integration |
    | Network_Codes |
    | Network_MobileCodes |
    | Optus_BIL |
    | Optus_BIL_Integration |
    | Optus_Integration |
    | Optus_Itemisation |
    | Optus_SAE |
    | Panacea_CallData |
    | Powertel_CCD_AccountCharges |
    | Powertel_CCD_CallDetails |
    | Powertel_CCD_CallTypeIntegration |
    | Powertel_CCD_CallingCard |
    | Powertel_CCD_Everything |
    | Powertel_CCD_Header |
    | Powertel_CCD_Inbound |
    | Powertel_CCD_Internet |
    | Powertel_CCD_LineList |
    | Powertel_CCD_LineTypeIntegration |
    | Powertel_CCD_LocalCalls |
    | Powertel_CCD_Miscellaneous |
    | Powertel_CCD_Mobile |
    | Powertel_CCD_ServiceCharges |
    | Powertel_CCD_Teleconferencing |
    | Powertel_CallData |
    | Powertel_Integration |
    | Primus_BillHeader |
    | Primus_CallTypeIntegration |
    | Primus_CallUse |
    | Primus_CodeMap |
    | Primus_LineTypeIntegration |
    | RSLCom_Data |
    | RSLCom_Integration |
    | RSLCom_LineTypeIntegration |
    | Telstra_BR_Accounts |
    | Telstra_BR_BillSummary |
    | Telstra_BR_BillSummaryTypes |
    | Telstra_BR_CallData |
    | Telstra_BR_Codes |
    | Telstra_BR_Exclusions |
    | Telstra_BR_Integration |
    | Telstra_BR_ItemisationIntegration |
    | Telstra_BR_LineIntegration |
    | Telstra_BR_SNE |
    | Telstra_BR_ServSumm |
    | Telstra_BR_Service |
    | Telstra_BR_Xref |
    | Telstra_WebData_Integration |
    | Vodaphone_Data |
    | Vodaphone_RSPCA |
    | WorldCom |
    | WorldExchange_Data |
    +-----------------------------------+
    74 rows in set (0.05 sec)

    mysql>
    So instead of using DTS, I'm using a simple little function I wrote in VB to handle the import with MySQL's 'load data infile' command:

    Function MySQLImport(source As String, Destination As String, _
    FieldTerminator As String, Optional OptionalWrapper As String, _
    Optional IgnoreLines As Integer, Optional LineTerminator As String, _
    Optional NoTruncate As Boolean, Optional KeepPrimaryKey As Boolean, _
    Optional KeepMyStamp As Boolean)

    Dim myconn As ADODB.connection, mycommand As ADODB.command, sqlstr As String

    sqlstr = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=vortex;" _
    & "DATABASE=ebills;" _
    & "UID=??????;" _
    & "PASSWORD=??????"

    Set myconn = New ADODB.connection
    With myconn
    .ConnectionString = sqlstr
    .Open
    End With
    Set mycommand = New ADODB.command
    mycommand.ActiveConnection = myconn

    If NoTruncate = False Then
    sqlstr = "truncate table " & Destination
    mycommand.CommandText = sqlstr
    mycommand.Execute
    End If

    ' Drop primary key & timestamp for import...
    If KeepPrimaryKey And KeepMyStamp Then
    ' Nothing...
    Else
    sqlstr = "alter table " & Destination
    If Not KeepPrimaryKey Then
    sqlstr = sqlstr & " drop primary key," _
    & " drop DanPK,"
    End If
    If Not KeepMyStamp Then
    sqlstr = sqlstr & " drop MyStamp"
    End If
    mycommand.CommandText = sqlstr
    mycommand.Execute
    End If

    ' Import...
    sqlstr = "load data infile" _
    & " '/usr/local/bills_on_disk/ElectronicBills2MySQL/" & source & "'" _
    & " into table " & Destination _
    & " fields terminated by '" & FieldTerminator & "'" _
    & IIf(Len(OptionalWrapper) = 0, "", " optionally enclosed by '" & Chr(34) & "'") _
    & IIf(Len(LineTerminator) = 0, "", " lines terminated by '" & LineTerminator & "'") _
    & IIf(Len(IgnoreLines) = 0, "", " ignore " & IgnoreLines & " lines")

    mycommand.CommandText = sqlstr
    mycommand.Execute

    ' Add Primary Key & Timestamp so Access can get hold of it...
    If KeepPrimaryKey And KeepMyStamp Then
    ' Nothing
    Else
    sqlstr = "alter table " & Destination
    If Not KeepPrimaryKey Then
    sqlstr = sqlstr & " add DanPK mediumint(8) unsigned NOT NULL auto_increment first," _
    & " add primary key (DanPK), "
    End If
    If Not KeepMyStamp Then
    sqlstr = sqlstr & " add MyStamp timestamp(14) not null after DanPK"
    End If
    mycommand.CommandText = sqlstr
    mycommand.Execute
    End If

    myconn.Close
    Set myconn = Nothing
    Set mycommand = Nothing

    End Function
    We use this function to import into every one of the supplier data tables above.

    So you see converting to MySQL isn't hard and doesn't involve sacrificing features. It requires a little more attention to detail (currently) but frankly I like it like that - more control.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •