Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Connecting to a new SQL Server Question 2

Posted on 2014-04-29
2
Medium Priority
?
273 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:Michael Wolfstone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1050 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:Michael Wolfstone
ID: 40035899
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.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 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