Go Premium for a chance to win a PS4. Enter to Win

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

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.
0
Michael Wolfstone
Asked:
Michael Wolfstone
1 Solution
 
Kelvin SparksCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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