Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Query not working after SQL Server 2005 upgrade

I moved my database from one server to another as well as upgraded my SQL Server 2005 from 8.0.194 to 9.0.5000.  Now I have some Access queries that will not work.  They come back with an ODBC failed.  The tables are reading fine.  Is there something different between these two versions that I need to account for?
0
bwestly
Asked:
bwestly
  • 5
  • 2
  • 2
1 Solution
 
rockiroadsCommented:
Have you tried taking these access queries (which I assume to be passthru) and running it in sql server mgt studio? (thats if you have it installed)
are you calling any Stored procedures? have you tried compiling them again?
0
 
stephenlecomptejrCommented:
If the ODBC initially was created based on a .dsn file - I would recreate a new .dsn file (or you could edit it via Control Panel - Admin Tools - Data Sources (ODBC)) and point it to the new SQL Server location.
0
 
bwestlyAuthor Commented:
I have tried to run the query in Sql Server mgmt studio and there seems to be a problem with a "merged" field.  Before in access I had [dbo_WORK_ORDER].[BASE_ID] & "-" & [dbo_WORK_ORDER].[SUB_ID] & "/" & [dbo_WORK_ORDER].[LOT_ID] but Sql Server does not like the - or /.

I changed my existing ODBC to point to the new location and the tables are working beautifully and most of the queries are working.  The ones I'm having trouble with work sometimes and then when I go back in, they have the ODBC-failed again.  Don't understand.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
stephenlecomptejrCommented:
Sorry had to be sure of the obvious and probably have to ask more obvious questions:

Has the permissions changed for the user within SQL Server from what it was before?
What version of Access (2003? 2007? 2010?)
Is Access in 32-bit or 64-bit?

As regards testing in SQL Server --- you have to change out the & symbol with pluses +
per - http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html
see heading - Handling Strings which suggests to be mindful of nulls and maybe using COALESCE
0
 
stephenlecomptejrCommented:
Sorry I see the Tag is Access 2003.
0
 
stephenlecomptejrCommented:
There are some interesting problems connecting Access 2003 to SQL Server 2005 -

http://stackoverflow.com/questions/1704305/using-ms-access-2003-to-connect-to-sql-server-2005
http://msdn.microsoft.com/en-au/library/bb188204.aspx

Some also say not to use the sa account login but creaet a new.
0
 
stephenlecomptejrCommented:
The first link just to iterate recommends putting the port number with the servername as your connection in the DNS file which I will believe solve your problem.

Here's a highly comprehensive take on trying to solve a similar issue in finding the cause which at the end states the fix:
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-connect/4850/Access-2007-SQL-Server2005-connection-was-forcibly-closed
I always highly respect Mary Chipman on anything she says.
0
 
rockiroadsCommented:
these queries that use access string concatenation i.e.  & "/"

what table are they querying from?

does it query from another query which might be a Stored Procedure call?

eg
qry1   call mysp....
qry2  select * from qry1

check that
0
 
bwestlyAuthor Commented:
This was a really difficult fix to find.  Eventually I had to rebuild a couple of tables and was finally able to get past the errors.  Thank you for all the helpful information on the differences between Access and SQL Server 2005.  Now I can port my queries between the two to see what the problems are.  It was strange to me that I had problems with these two tables even though I was going from 2005 to 2005.  You just never know....
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now