Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22
  1. #1
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    Here's the scenario: I've got a new client who wants to back up his site's SQL Server 2000 database when he chooses to, but he doesn't have SQL Server 2000. He's got WinME and Office 2K (including Access 2K) on a laptop.

    My initial idea is to create a blank replica of the SQL Server 2000 database in Access, and use ASP (or ASP.net) to wipe out the old database, fill the new database, compact it and let him click a hyperlink to download it. This would allow him to keep a backup copy that could easily be re-upsized to SQL Server 2000 again without him having to go out and get SQL Server 2000.

    Would I be correct in thinking this is the best way to back up and maintain an SQL Server 2000 database (since I have nowhere to put it offsite) or would there be another way to do so?
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    No.... you should be using DTS to do the backup.

    You can install MSDE on his laptop and schedule a DTS backup for as often as you want.

    It will take you 10 minutes to install and another 10 to setup DTS, versus days of programming the other way.

    Even if you were planning on doing the backup to Access, you should use DTS to export the DB as an Access database rather than programming a whole application. That way, if any changes are made to the architecture, you're safe.

    Also, be careful when exporting to Access... remember that Stored Procedures, User Defined Functions, Triggers, and some Indexes don't get saved to Access.

    Hope this helps !!!

  4. #3
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    I don't normally use Stored Procedures, User Defined Functions, Triggers and Indexes anyway. I know I should but I don't have any formal training and I'm not spending money on courses and books that have incoherently explained information on the 10-20% of things I don't know.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    UDFs and Triggers are rarely necessary and don't usually increase performance, only functionality.

    Indexes are nice to know, and not too difficult to learn. You can always add them in Enterprise Manager ("Design Table" view). The nice thing about Indexes is that you can turn a 2 minute query into a 2 second query if you place them correctly (assuming that your query is efficiently designed).

    Stored Procedures are a necessity to learn if you want to become a good database programmer. Most people are afraid of them, but they're a piece of cake to create. You can also call them like regular SQL statements if you don't want to create Command objects or Prepared Statement (java) objects in your code.

    Here's an example of a creation script for some basic Stored Procedures (the code you execute on your SQL Server, say in Query Analyzer, to make the procedures):

    Code:
    CREATE PROCEDURE get_all_records AS
        SELECT * FROM record
    GO
    
    CREATE PROCEDURE get_record_by_id (@id AS int) AS
        SELECT *
        FROM record
        WHERE id = @id
    GO
    Now, here's how you could call them in your code (ASP or VB):

    Code:
    <%
        Dim oConn
        Dim oRs
    
        Set oConn = Server.CreateObject("ADODB.Connection")
        Set oRs = Server.CreateObject("ADODB.Recordset")
    
        oConn.Open "ConnectionString"
    
        sql = "get_all_records"
        oRs.Open sql, oConn, 2, , 1
            'Do stuff
        oRs.Close
    
        sql = "get_record_by_id " & Request.Form("record_id")
        oRs.Open sql, oConn, 2, , 1
            'Do stuff
        oRs.Close
    
        oConn.Close
    
        Set oConn = Nothing
        Set oRs = Nothing
    %>

    To call the Stored Procedure using an ADO Command object, do something like this:

    Code:
    <%
        Dim oConn
        Dim oCmd
        Dim oRs
    
        Set oConn = Server.CreateObject("ADODB.Connection")
        Set oCmd = Server.CreateObject("ADODB.Command")
        Set oRs = Server.CreateObject("ADODB.Recordset")
    
        oConn.Open "ConnectionString"
    
        Set oCmd.ActiveConnection = oConn
        oCmd.CommandType = adCmdStoredProc
        oCmd.CommandText = "get_record_by_id"
        oCmd.Parameters.Refresh
        oCmd.Parameters.Item("@id").Value = Request.Form("id")
        Set oRs = oCmd.Execute
            'Do stuff
        oRs.Close
        
        oConn.Close
    
        Set oConn = Nothing
        Set oCmd = Nothing
        Set oRs = Nothing
    %>
    Pretty easy, huh?

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    An example of what you can accomplish using Stored Procedures and Indexes:

    I had a client who was doing Time and Attendance reporting using queries in Crystal Reports (which absolutely sucks, but that's another thread). The table they were querying had about 3 million records. The report was taking up to 2 hours to execute.

    I optimized the report by creating a Stored Procedure (compiled SQL) that used datetime inequality pairing to create the report. Previously, most of the business logic had been processed in Crystal Reports, because the formula for creating the report was very complex. This meant that it had to select all 3 million records into the report and process them internally to find out what it was going to do with them.

    I then created a CLUSTERED INDEX on the datetime field to maximize the database's comparison power for that field. Since I was developing a matrix based upon the datetime and the user_id, with the majority of the hash load resting on the datetime, the datetime was the obvious choice for the clustered index. The user_id was then given a nonclustered index (since you can only have one clustered index per table).

    By the time I was done, the 2 hour report took 45 SECONDS to run !!! Don't underestimate the power of Stored Procedures and Triggers. They could save your database some day !

  7. #6
    Junior Member
    Join Date
    Mar 2003
    Posts
    2
    Member #
    930
    as thegame said
    plus be careful with your method also cuz if u restore from an Access Backup to SqlServer, u will lose certain data structures that are not instantly restored -- importantly for ie the Primary Key data type in Access is Auto --- whereas in SqlServer it's integer and the 'Columns/Indentity' info in SqlServer should be set to 'Yes' -- which a backup this route always overlooks....

    heathrowe

  8. #7
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    Actually, I'm the braindead idiot asking the questions. Gotta give transio the credit, even though I'd like to take it myself. Thanks for the tip though!
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    GAME, I still think you should check out MSDE and DTS. It will save you SO MUCH headache. It's totally automated.

  10. #9
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Read this

    http://msdn.microsoft.com/library/de...s_overview.asp

    Very interesting white paper on DTS.

  11. #10
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    Okay...I can't find the download (sorry to bump an old thread up but the client wants it done today and just told me about it.) Does anyone have the link to the d/l?
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)


Page 1 of 3 1 2 3 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:23 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com