Connecting to a new SQL Server Question 2

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.
Michael WolfstoneDirector of TechnologyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kelvin SparksConnect With a Mentor Commented:
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
 
Michael WolfstoneDirector of TechnologyAuthor Commented:
Thanks,  I'll review and give it a try.
0
All Courses

From novice to tech pro — start learning today.