Solved

Connecting to a new SQL Server Question 2

Posted on 2014-04-29
2
267 Views
Last Modified: 2014-05-05
We replaced a server 2003 SQL 2005 32 bit with a server 2008 R2 SQL 2005 64 bit.  Programs (using ODBC) updated on the new server run fine from the workstations (mostly Win 7 64 bit).  The same ODBC definition is unable to see or connect to the new server from the workstations.  An MS Access ADP is unable to run from the workstations;  it runs fine on the new server.  I have checked the SQL client drivers, comm protocols, and permissions.
Please help.

The ODBC problem is Question 1 - separate.
0
Comment
Question by:BFOG
2 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 350 total points
ID: 40031007
How did you create the users on the new server? You need a script to align the IDs if you created the users from scratch, then restored that bases from backup.

I used this script against each database on the new server

-- AUTO_FIX All users in DB
set nocount on
go

if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users

CREATE TABLE #t_users ( [name] sysname)

INSERT #t_users ( [name] )
SELECT [name] from sysusers where name <> 'dbo' order by name

declare @lc_name sysname

SET @lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @lc_name IS NOT NULL
BEGIN
  IF EXISTS (SELECT * FROM   MASTER..syslogins WHERE  [name] = @lc_name)
  BEGIN
    PRINT 'Fixing ' + @lc_name
    EXEC Sp_change_users_login 'AUTO_FIX' , @lc_name
  END
  ELSE
  PRINT '*** not fixing ' + @lc_name
     
  SET @lc_name = (SELECT Min([name]) FROM   #t_users WHERE  [name] > @lc_name)
END

go


Kelvin
0
 

Author Comment

by:BFOG
ID: 40035899
Thanks,  I'll review and give it a try.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now