Microsoft Access Data Types

The following table shows the Microsoft Access data types, data types used to create tables, and ODBC SQL data types.

Microsoft Access data type Data type (CREATETABLE) ODBC SQL data type
BIGBINARY[1] LONGBINARY SQL_LONGVARBINARY
BINARY BINARY SQL_BINARY
BIT BIT SQL_BIT
COUNTER COUNTER SQL_INTEGER
CURRENCY CURRENCY SQL_NUMERIC
DATE/TIME DATETIME SQL_TIMESTAMP
GUID GUID SQL_GUID
LONG BINARY LONGBINARY SQL_LONGVARBINARY
LONG TEXT LONGTEXT SQL_LONGVARCHAR[2] SQL_WLONGVARCHAR[3]
MEMO LONGTEXT SQL_LONGVARCHAR[2] SQL_WLONGVARCHAR[3]
NUMBER (FieldSize= SINGLE) SINGLE SQL_REAL
NUMBER (FieldSize= DOUBLE) DOUBLE SQL_DOUBLE
NUMBER (FieldSize= BYTE) UNSIGNED BYTE SQL_TINYINT
NUMBER (FieldSize= INTEGER) SHORT SQL_SMALLINT
NUMBER (FieldSize= LONG INTEGER) LONG SQL_INTEGER
NUMERIC NUMERIC SQL_NUMERIC
OLE LONGBINARY SQL_LONGVARBINARY
TEXT VARCHAR SQL_VARCHAR[1] SQL_WVARCHAR[2]
VARBINARY VARBINARY SQL_VARBINARY

via: http://msdn.microsoft.com/en-us/library/ms714540%28VS.85%29.aspx

via: http://www.sunfinedata.com/tips/create-an-ms-access-database-by-asp-vbscript/

Change or Alter multiple database table schema

Sometime as a database developer, we might come across changing the schema for the whole database

This is one way to do it; by generating multiple queries in the query editor:

SELECT ‘EXEC(”sp_changeobjectowner @objname = ””oldDBO.’ + name +’ ”” , @newowner = dbo”) ‘

FROM sys.tables

WHERE schema_id = SCHEMA_ID(‘oldDBO’);

Also below is the drop multiple tables by query from the sys.objects.

In this change where is by order date:

SELECT * INTO #tables from sys.objects where create_date > ‘2009-10-27 21:47:20.993′

while (SELECT count(1) FROM #tables) > 0

begin

declare @sql varchar(max)

declare @tbl varchar(255)

SELECT top 1 @tbl = name FROM #tables

SET @sql = ‘drop table ‘ + @tbl

exec(@sql)

DELETE FROM #tables where name = @tbl

end

DROP TABLE #tables;

SQL Server 2008 Proximity Search With The Geography Data Type

SQL Server 2008 Proximity Search With The Geography Data Type
by SQLDenis

Permalink 11 Feb 2009 13:27 , Categories: Data Modelling & Design, Microsoft SQL Server Tags: geography, geomapping, spatial data, sql server 2008

George and I decided to show you how you can do simple radius searches based on Zip Codes, he did the SQL 2005 and before version here: SQL Server Zipcode Latitude/Longitude proximity distance search and I will do the SQL Server 2008 version.

The first thing we need to do is load our data. There are various sources for this data, ranging from free to expensive monthly subscriptions. One source of free data is: http://download.geonames.org/export/zip/ make sure to grab the US.ZIP file for this demo. Unrar/Unzip the file and you will have a US.txt file.

Once you have downloaded your data, the next step is to import it in to your database. You can use the following script to do it.

1. CREATE TABLE ZipCodesTemp(
2.     [Country] [VARCHAR](2) NULL,
3.     [ZipCode] [VARCHAR](5) NULL,
4.     [City] [VARCHAR](200) NULL,
5.     [STATE] [VARCHAR](50) NULL,
6.     [StateAbbreviation] [VARCHAR](2) NULL,
7.     [County] [VARCHAR](50) NULL,
8.     [Unused1] [VARCHAR](5) NULL,
9.     [Unused2] [VARCHAR](1) NULL,
10.     [Latitude] [DECIMAL](8,5) NULL,
11.     [Longitude] [DECIMAL](8,5) NULL,
12.     [Unused3] [VARCHAR](1) NULL
13.     )
14.
15.
16. DECLARE @bulk_cmd VARCHAR(1000)
17. SET @bulk_cmd = ‘BULK INSERT ZipCodesTemp
18.   FROM ”C:\YourFolder\US.txt”
19.   WITH (FIELDTERMINATOR=”\t”, ROWTERMINATOR = ”’+CHAR(10)+”’)’
20.
21. EXEC(@bulk_cmd)

Code is hidden, SHOW

Now you will create this table

1. CREATE TABLE ZipCodes(
2.     [Country] [VARCHAR](2)  NULL,
3.     [ZipCode] [VARCHAR](5) NOT NULL,
4.     [City] [VARCHAR](200)  NULL,
5.     [STATE] [VARCHAR](50)  NULL,
6.     [StateAbbreviation] [VARCHAR](2)  NULL,
7.     [County] [VARCHAR](50) NULL,
8.     [Latitude] [DECIMAL](8,5) NOT NULL,
9.     [Longitude] [DECIMAL](8,5) NOT NULL,
10.     [GeogCol1] [GEOGRAPHY]  NULL,
11.     [GeogColTemp] [VARCHAR](100) NULL
12.     )

Code is hidden, SHOW

There is at least one duplicate row in this file so we will import only uniques

1. INSERT  ZipCodes (Country,ZipCode,City,STATE,StateAbbreviation,County,Latitude,Longitude)
2. SELECT DISTINCT Country,ZipCode,City,STATE,StateAbbreviation,County,Latitude,Longitude
3. FROM  ZipCodesTemp

Code is hidden, SHOW

Our next step will be to update the GeogCol1 table with something that SQL server can understand.
Here is some sample code that displays the format of this datatype

1. DECLARE @h geography;
2. SET @h = geography::STGeomFromText(‘POINT(-77.36750 38.98390)’, 4326);
3. SELECT @h

Code is hidden, SHOW

output
———————————————-
0xE6100000010C6744696FF07D4340EC51B81E855753C0

As you can see it is some binary data. This data is using the World Geodetic System 1984 (WGS 84)

To see if this is supported in your database you can run this query

1. SELECT * FROM sys.spatial_reference_systems

Code is hidden, SHOW

And yes in my database it has a spatial_reference_id of 4326

1. SELECT * FROM sys.spatial_reference_systems
2. WHERE spatial_reference_id = 4326

Code is hidden, SHOW

Here is the meta data

GEOGCS[“WGS 84″, DATUM[“World Geodetic System 1984″,
ELLIPSOID[“WGS 84″, 6378137, 298.257223563]], PRIMEM[“Greenwich”, 0],
UNIT[“Degree”, 0.0174532925199433]]

Back to our code, in order to have this data in our table

SET @h = geography::STGeomFromText(‘POINT(-77.36750 38.98390)’, 4326);

We need to do some things, first we update our temp column

1. UPDATE zipcodes
2. SET GeogColTemp= ‘POINT(‘ + CONVERT(VARCHAR(100),longitude)
3. +’ ‘ +  CONVERT(VARCHAR(100),latitude) +’)’

Code is hidden, SHOW

Now we can update out geography column

1. UPDATE zipcodes
2. SET GeogCol1 =  geography::STGeomFromText(GeogColTemp,4326)

Code is hidden, SHOW

We can drop the temp column now

1. ALTER TABLE zipcodes DROP COLUMN GeogColTemp

Code is hidden, SHOW

Now we have to add a primary key, this is needed because otherwise we won’t be able to create our spatial index and the following message would be displayed

Server: Msg 12008, Level 16, State 1, Line 1
Table ‘zipcodes’ does not have a clustered primary key as required by the spatial index. Make sure that the primary key column exists on the table before creating a spatial index.

1. ALTER TABLE zipcodes ADD
2.     CONSTRAINT [PK_ZipCode] PRIMARY KEY  CLUSTERED
3.     (
4.         Zipcode,
5.         Longitude
6.     ) WITH  FILLFACTOR = 100

Code is hidden, SHOW

Create the spatial index

1. CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col1
2.    ON zipcodes(GeogCol1);

Code is hidden, SHOW

first I will show you an example to calculate the distance that you can execute

1. DECLARE @g geography;
2. DECLARE @h geography;
3. SET @h = geography::STGeomFromText(‘POINT(-77.36750 38.98390)’, 4326);
4. SET @g = geography::STGeomFromText(‘POINT(-77.36160 38.85570)’, 4326);
5. SELECT @g.STDistance(@h)/1609.344;

Code is hidden, SHOW

as you can see the distance is 8.8490611480890067

Now I want to see all the zipcode which are within 20 miles of zipcode 10028 (yes I used to live there)

Here is a way that will take a long time since it is not sargable, this will take about 2 seconds

1. SELECT h.*
2. FROM zipcodes g
3. JOIN zipcodes h ON g.zipcode <> h.zipcode
4. AND g.zipcode = ‘10028’
5. AND h.zipcode <> ‘10028’
6. WHERE g.GeogCol1.STDistance(h.GeogCol1)/1609.344 <= 20

Code is hidden, SHOW

Now we all know functions on the left side of the operator are bad, here is how this is optimized, we switch the calculation to the right side of the = sign

1. SELECT h.*
2. FROM zipcodes g
3. JOIN zipcodes h ON g.zipcode <> h.zipcode
4. AND g.zipcode = ‘10028’
5. AND h.zipcode <> ‘10028’
6. WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344)

Code is hidden, SHOW

that ran in between 15 and 60 milliseconds

To find everything between 10 and 20 miles you can use this

1. SELECT h.*
2. FROM zipcodes g
3. JOIN zipcodes h ON g.zipcode <> h.zipcode
4. AND g.zipcode = ‘10028’
5. AND h.zipcode <> ‘10028’
6. WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344)
7. AND g.GeogCol1.STDistance(h.GeogCol1)>= (10 * 1609.344)

Code is hidden, SHOW

As you can see doing stuff like this on SQL Server 2008 is fairly easy because of the geograpy data type

via: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-2008-proximity-search-with-th

and http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr

How to enable 32-bit web server on 64-bit IIS

C:\>cscript C:\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
C:\>cd windows\microsoft.net\framework\v2.0.50727
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis.exe -i
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>NET STOP W3SVC /y & NET START W3SVC

ERRORS:
Microsoft OLE DB Provider for ODBC Drivers error ‘80004005’
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Run C:\Windows\SysWOW64\odbcad32.exe to setup DSN

Restore & Backup IIS 7

> %windir%\system32\inetsrv\appcmd.exe add backup “My Backup Name”

to restore that backup, run this command:

> %windir%\system32\inetsrv\appcmd.exe restore backup “My Backup Name”

to delete a backup, run this command:

> %windir%\system32\inetsrv\appcmd.exe delete backup “My Backup Name”