• PHP
  • Ruby on Rails
  • MySQL
  • Linux
    • SELINUX
    • Fedora
    • debian
  • Apache
  • nginx
  • AJAX
Albertech.net

Category Archives: Sql Server

Fix: Editing tables in Access 2010 with Microsoft SQL Server 2008 R2 connection

February 19, 2013 5:28 pm / Albertech.net

On a recent migration from Access 2010 to SQL Server 2008 R2, a client was having the following issue when inserting a row in the middle of a table or modifying the field size within the Design View. They were connecting to the SQL Server via a ADP file.

Unable to modify table. 

ADO error: Incorrect syntax near ‘TABLOCKX’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

This is due to Microsoft changing the syntax for the ALTER statement in SQL Server  2008 to require a WITH keyword. Access 2010 does not generate SQL statements with the “WITH” keyword.

DISCLAIMER: This is for informational purposes only. We are providing absolutely no warranty for this information. Use this information at your own risk. Always make a copy of the database and make the changes on the test database first.

Solution: On the SQL Server 2008 R2 SSMS (SQL Server Management Studio), set the database compatibility to “SQL Server 2000 (80)“. Right click on the database, click on “Properties”, select the “Options” page. There should be a dropdown menu to select the “Compatibility level”. Change it from SQL Server 2008 to 2000. This allows older SQL commands to be used with the SQL server. The main difference with changing compatibility from 2008 to 2000 is the way the SQL optimizer processes the queries.

Reference: http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/70836032-f1d1-4ce9-9b38-86982941aec6

 

 

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: SQL Server / Tagged: access 2010, sql server

Microsoft SQL Server 2008 R2 Firewall Ports

January 18, 2013 12:23 pm / Albertech.net

Required ports for SQL Server 2008 R2. Enable these ports in the Windows Firewall.
135  Transact SQL Debugger
1433  SQL Traffic
1434  SQL Browser Traffic
2383  SQL Analytics Traffic
4022– SQL Broker Traffic

Note: The SQL Server 2008 R2 installation doesn’t enable TCP/IP and Named Pipes Protocols by default, so it needs to be enabled in SQL Server Configuration Manager. This is located in Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager. After enabling the protocols, restart the SQL Server service. This will allow clients to connect to the SQL Server.

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: SQL Server

Categories

  • AJAX
  • Android
  • Apache
  • Canon Cameras
  • Cloud
  • CMS
  • Computer Mods
  • Conferences
  • Deals
  • debian
  • Fedora
  • Flash
  • Frameworks
  • git
  • Hardware
  • HTML
  • IDE
  • iPhone
  • iPhone App Review
  • jQuery
  • Linux
  • Mac OS X
  • MySQL
  • nginx
  • PHP
  • portfolio
  • Puppet
  • Ruby on Rails
  • Script Reviews
  • SELINUX
  • Software
  • Software Review
  • SQL Server
  • statistics
  • Tech
  • Tomcat
  • Uncategorized
  • VMWARE
  • VPS
  • Windows
  • wordpress
  • Zend Framework

Blogroll

  • DragonAl Flickr
  • Dropbox – Free 2GB Account
  • James' Blog
  • Javascript Compressor
  • PHP Builder Community
  • PHP-Princess.net
  • Rubular – Regular Expression Validator
  • The Scale-Out Blog
  • Tiny MCE

Tags

activation AJAX android antec Apache AWS awstats canon coda codeigniter debian enclosure external free G1 install vmware tools Internet Explorer iphone 5 jquery Linux mx-1 MySQL office 2007 OSX photoshop PHP plugin plugins portfolio redesigned website review rewrite script security SELinux ssh tinymce tutorial upgrade VMWARE vmware server wordpress wordpress mu XSS zend framework
© Copyright 2013 Albertech.net
Infinity Theme by DesignCoral / WordPress
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.