convert access queries into sql server stored procedure

Posted on 2006-03-21
Last Modified: 2007-11-27
please help me how can i convert access queries into sql server stored procedure. in simplest way please help.
I have sql server 8.5 installed. thanks
god speed...
Question by:JackOfPH
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Unfortunately there is no wizard to do this.  Only hand coding.

    P.S. I suspect you mean SQL Server 2000.  (there is no 8.5)
    LVL 15

    Author Comment

    LVL 75

    Accepted Solution

    In very general terms here are some of the differences:
    MS Access                                    MS SQL Server
    Double quotes for literals            Single quotes for literals
    IIF                                                Case
    IsNull(Expresion)                        Expresion Is Null
    Nz                                                IsNull
    Now()                                          GetDate()
    &                                                +
    Mod()                                          &
    * (wildcard)                              %
    ASC()                                          ASCII()
    Format()                                    Cast(), Convert()
    Fix(), Int, Cint()                        Ceiling(), Floor()
    InStr()                                          CharIndex(), PatIndex()
    ^                                                Power()
    String()                                    Replicate()
    Mid()                                          SubString()
    Chr()                                          Char()
    LCase(), UCase()                        Lower(), Upper()
    Sgn()                                          Sign()
    Sqr()                                          Sqrt()
    Rnd()                                          Rand()

    In addition all the Date functions (DATEADD, DATEDIFF, DATENAME, DATEPART) are slightly syntactically different.
    You cannot use aliases from columns in other expressions or even the WHERE clause.
    The Update statement use a different syntax when joining tables.

    No doubt there are many more differences that I have overlooked.  Try and get hold of an old artticle from MSDN that describes how to migrate from MS Acesss to MS SQL Server.
    LVL 42

    Assisted Solution

    A comparison between an Access query and SQL Server stored procedure is not really meaningful.  The equivalent objects are:

    Access Select Query  --> SQL Server View
    Access Action Query --> TSQL Script
    Access Subroutine -->  SQL Server Stored Procedure
    Access Function --> SQL Server Stored Function

    Acperkins offers a good shortlist, but they are generally differences between the built-in functions and language syntax provided by the database engine and/or scripting language (VBA --> TSQL).



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now