convert access queries into sql server stored procedure

Posted on 2006-03-21
Medium Priority
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
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16253993
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

ID: 16254008
LVL 75

Accepted Solution

Anthony Perkins earned 120 total points
ID: 16254101
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

dqmq earned 80 total points
ID: 16254588
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).



Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 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