Xsupport - SQL Server FAQ

SQL Server FAQ

What software is used ?

Microsoft's SQL server version 7 service pack 1 is employed. See Microsoft's SQL site for revision information.
Can I site my application on the server?

There are two levels of SQL service. The first is a dedicated SQL server. This allows you to site your databases and developers can move most Transact-SQL statements that originate from Access queries (SELECT, INSERT, UPDATE, and DELETE) into stored procedures.
The second SQL service is a shared one allowing only databases to be sited on a server. ODBC & OLEDB calls can be made to your database on the shared server. This provides a 24 X 7 scalable service that can take workplace databases onto the web as scaleable applications.
What tools do I need?

SQL server ships with client tools which allow data transformation services. Wizards enable upsizing of legacy data such as Microsoft Access databases. Once up-sized Query analyser and Enterprise manager allow manipulation of data and structures natively.
Other tools such as Microsoft Interdev can be enabled to hook directly into the power of your SQL database.
Should I not be using Oracle for scalability?

Industry is currently watching Microsoft SQL server and Oracle for NT slug it out. Rather than replicate arguments for both sides we suggest you read up on the battle yourself. Suffice to say, if the products were not competing then the arguments would not exist!

Microsoft

Oracle

Problem Connecting to SQL Server Via Enterprise Manager?

Some people experience problems using Microsoft's SQL Server client tools such as the Enterprise Manager to connect to their SQL Server database. The error that will most commonly occur is:
Unable to Connect To Server (reason: [DB-Library] Unable to Connect: SQL Server is unavailable or does not exist. Specified SQL Server not found). Register anyway?
There are several problems that may cause this. The first is to make sure that you are using the correct IP address (we suggest using the IP address to register the server) and have the correct login name and password. If you are unsure of any of the above, please email support@xhost.co.uk. to confirm the information.
If the above is correct, the problem may exist in your installation of Microsoft's client utilities. A major issue is that the utilities install with the communication protocol of Named Pipes selected as the default protocol. This must be changed to TCP/IP. To do this:

Go to your Menu Bar and select the SQL Client Configuration Manager from the SQL Server program group.
Select the Net Library tab.
Select TCP/IP as the Default Network
Click on the Done button.

What database scripting technologies can I use with SQL?

We support Allaire's Cold Fusion, Microsoft's Active Server Pages and Microsoft's Internet Database Connector scripting languages. There are differences between Microsoft SQL Server and Microsoft Access query syntax:

Access SQL Server

ORDER BY in queries ORDER BY in views not supported

DISTINCTROW DISTINCT

String concatenation with "&" String concatenation with "+"

Supported Clauses/Operators:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
WITH OWNERACCESS
Not Supported:
COMPUTE, FOR BROWSE, OPTION
Supported Clauses/Operators:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
COMPUTE
FOR BROWSE
OPTION
Not Supported:
WITH OWNERACCESS

Aggregate functions:
AVG
COUNT(column)
COUNT(*)
MIN
MAX
FIRST
LAST
STDEV, STDEVP
SUM
VAR, VARP
Aggregate functions:
AVG([ALL | DISTINCT] expression)
COUNT([ALL | DISTINCT] expression)
COUNT(*)
GROUPING (column_name)
MAX(expression)
MIN(expression)
STDEV, STDEVP
SUM([ALL | DISTINCT] expression)
VAR, VARP
Not supported:
FIRST, LAST

TRANSFORM
(SELECT statement)
PIVOT WITH ROLLUP, WITH CUBE on SELECT statements

MAKE TABLE, ALTER TABLE
Supported clauses:
CONSTRAINT
ADD COLUMN
DROP COLUMN
DROP INDEX
Also,
Stand-alone statement:
DROP INDEX
CREATE TABLE, ALTER TABLE
Supported clauses:
CONSTRAINT
ADD COLUMN
DROP COLUMN

Stand-alone statement:
DROP INDEX

What is the maximum size of database I can have?

SQL server can accomodate a database of any size. With support for a potential 32 processors and 64 GB of memory the bottlenecks will lie within the web enabling technology and wide area network.

Will it support Access 2000?

Microsoft Access 2000 can access SQL Server directly, enabling native, transparent client/server interaction.

Should I use Access or SQL ?

That Depends. Access is cheaper. It is easy to create tables and modify them as needed. But in a production environment Access has some major drawbacks:

a) It is not designed to handle many concurrent writes into the database.
b) It is hard to modify an Access database without taking it offline.
c) Locking doesn't work as well as in SQL Server
d) Using SQL Server constraints and transactions makes it easier to protect the database from logical corruption.

My wildcard search that worked with Access fails with SQL ?

This is normally because you can use * and ? as wildcards with Access while you should use % and _ respectively when using a SQL Server. For instance:

SELECT PhoneNumber WHERE NAME LIKE 'John*'

should be replaced with:
SELECT PhoneNumber WHERE NAME LIKE 'John%'

when using SQL Server. The % and _ also work with Access, so may just as well always use them.

Why should I store NULL in a text field rather than " ".

Because storing that one space in the text fields will result in SQL Server allocating a complete page (2 k) for the field, wheras NULL will require no extra storage.