Problem with "SQL Server does not exist" error

I have a couple of databases developed and being used in MSDE. The
db's are used and maintained from a couple of programs written in
Visual Basic 6 (using an ADO connection) (these programs run on a
separate computer).
I've just set up a new computer and installed SQL Server Express 2005
on it. I copied the .MDF files from the MSDE unit and attached them to
SQLExpress.
Now, when I try to connect to the databases, I get the "SQL Server
does not exist or access is denied" error. I have, of course, modified
the connection string such that the Data Source property is using the
correct computer name.
I've looked at everything I can think of but obviously I'm missing
something fundamental here. Any suggestions as to what I might be
doing wrong?
Thanks.
On Mon, 20 Nov 2006 16:22:39 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Martin,
>Martin wrote:
>please verify the instance name is correct (in the form
>ComputerName\InstanceName), the remote computer can access the lan and the
>remote SQLExpress installed computer, firewall (both) have been set with the
>appropriate exception(s) allowing connectivity and trafic (both the DBEngine
>used port and the SQLBrowser, if used), the SQLExpress network protocols
>have been enabled, the Surface Area Configuration ahs been set to allow
>remote connections...
Thanks, Andrea. I've gone thru your list but still no luck.
* the name of the new computer is "CATServer" (it's not actually a
"server" - it's running WinXP Pro with all current service packs and
updates). I've tried setting the Data Source to both "CATSERVER" and
"CATSERVER\SQLEXPRESS". Neither way seemed to make any difference.
* the remote computer CAN access CATServer. I have one folder on
CATServer shared and it can be viewed in Windows Explorer. Also, I can
ping CATServer successfully.
* there are no firewalls running on either computer.
* the network protocols: Shared Memory, Named Pipes and TCP/IP are
enabled; VIA is not enabled.
* the Surface Area Configuration was something I did not know anything
about. Remote Connections were set to "Local Connections Only". I
changed that to "Local and Remote" (for both TCP/IP and Named Pipes)
and then stopped and restarted the server service. It made no
difference. I also re-booted the computer but that, too, made no
difference.
Any other thoughts?
|||On Mon, 20 Nov 2006 18:20:18 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Martin,
>Martin wrote:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql
Thanks again. I've gone through that fairly well but still haven't
been able to get connected.
I did stumble on to something though: I started the "SQL Server
Browser". Now, when my VB6 program tries to connect, I'm getting a
different error message: "Login failed for user 'userid'" (where
userid is the userid that's in my connect string).
Does this mean that SQL Server Browser has to be running in order for
my program to connect?
Also, when I posted earlier, I was using msado 2.5 in my VB program.
One of the things in the page you referenced mentioned that I needed
at least version 2.6. I have 2.7 (and 2.8) on the machine where VB is
running so I changed the reference to 2.7. That did not seems to make
any difference however. Does that shed any light on the problem I
might be up against?
|||On Mon, 20 Nov 2006 07:58:39 -0700, Martin <martinvalley@.comcast.net>
wrote:

>I have a couple of databases developed and being used in MSDE. The
>db's are used and maintained from a couple of programs written in
>Visual Basic 6 (using an ADO connection) (these programs run on a
>separate computer).
>I've just set up a new computer and installed SQL Server Express 2005
>on it. I copied the .MDF files from the MSDE unit and attached them to
>SQLExpress.
>Now, when I try to connect to the databases, I get the "SQL Server
>does not exist or access is denied" error. I have, of course, modified
>the connection string such that the Data Source property is using the
>correct computer name.
>I've looked at everything I can think of but obviously I'm missing
>something fundamental here. Any suggestions as to what I might be
>doing wrong?
>Thanks.
I finally got this working - sorta...
I had to do three things: I added "\SQLExpress" to the Data Source
name in the client program's connection string. Also in the client
program, I upgraded to version 2.8 of MSADO. On the server side of
things, I added the Login that I was trying to use from the client
side.
With these changes, I am now able to access the data on the server as
needed from the client programs.
With one major exception:
After I got everything working, I recompiled one of the VB programs
and installed it on another computer (call it computer B for
reference). But, when I run it, it encounters the exact same error
condition that I had originally! IOW, this thing works from computer A
but not from computer B.
Yes, computer B has version 2.8 of MSADO on it (I downloaded and
installed a verification tool and checked it out). Computer B has
access to the server computer where SQL Express is running (they're
all on the same LAN).
I'm stumped. Anyone have any ideas what the problem might be?
|||>I finally got this working - sorta...
>I had to do three things: I added "\SQLExpress" to the Data Source
>name in the client program's connection string. Also in the client
>program, I upgraded to version 2.8 of MSADO. On the server side of
>things, I added the Login that I was trying to use from the client
>side.
>With these changes, I am now able to access the data on the server as
>needed from the client programs.
>With one major exception:
>After I got everything working, I recompiled one of the VB programs
>and installed it on another computer (call it computer B for
>reference). But, when I run it, it encounters the exact same error
>condition that I had originally! IOW, this thing works from computer A
>but not from computer B.
>Yes, computer B has version 2.8 of MSADO on it (I downloaded and
>installed a verification tool and checked it out). Computer B has
>access to the server computer where SQL Express is running (they're
>all on the same LAN).
>I'm stumped. Anyone have any ideas what the problem might be?
>
OK, got it going...
Apparently, I have to have the SQL Server Browser service running in
order to connect from computer "B".
Why the Browser service is not needed by computer "A", I have no idea.
|||On Thu, 23 Nov 2006 12:09:01 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Martin,
>Martin wrote:
>SQLBrowser is not needed for local connections as they are not resolved over
>the TCP/IP stack but via shared memory and the port is directly resolved via
>local access...
>try modifying the network protocol (SQL Server Management Studio Express,
>connection dialog, "Options", "Network Protocol" = TCP/IP... you will not
>ba able to connect from local client as well..
I assume by "local connections" you mean "on the same physical
computer where SQL Server is running"? If so, then that is NOT the
case here. Both computer "A" and computer "B" in my situation are
"remote" - that is, they are separate computers on the LAN.
Again, it would seem to me that if one can connect, then the other
should be able to also.

Problem updating table1 with data from table 2

Hi,

I'm trying to loop through 2 tables and match the records from table1 to table2 and update a couple of columns in table2 if there is a match. The below code updates everyone in table2 with the same data from only 1 record. How can I keep looping through both tables?

USE GradData

GO

Declare @.ssn nvarchar(10)

Declare @.testdesc nvarchar(10)

Declare @.testcode nvarchar(10)

Declare @.testdate datetime

Declare @.testscore nvarchar(50)

Declare @.testsource nvarchar(10)

Declare @.testsortkey nvarchar(10)

Declare @.fcode nvarchar(10)

Declare @.ssn2 nvarchar(10)

Declare @.testdesc2 nvarchar(10)

Declare @.testcode2 nvarchar(10)

Declare @.testdate2 datetime

Declare @.testscore2 nvarchar(50)

Declare @.testsource2 nvarchar(10)

Declare @.testsortkey2 nvarchar(10)

Declare @.fcode2 nvarchar(10)

Declare mycursor Cursor For

Select ssn,testDesc1,testCode1,testDate1,testScore1,testSource1,Fcode1,sortkey

From TestScoresRpt

Open mycursor

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

Declare mycursor2 Cursor For

Select ssn,test_Desc,test_Code,test_Date,test_Score,test_Source_code,Fcode,test_sort_key

From ScoresAll

Open mycursor2

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.ssn <> @.ssn2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey = @.testsortkey2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2

Update TestScoresRpt

set ssn2=@.ssn2,testDesc2=@.testDesc2,testCode2=@.testCode2,testDate2=@.testDate2,testScore2=@.testScore2,testSource2=@.testSource2,Fcode2=@.fcode2,sortkey2=@.testsortkey2

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

END

CLOSE mycursor

CLOSE mycursor2

DEALLOCATE mycursor

DEALLOCATE mycursor2

If you are updating all rows where (@.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2), then you would have improved performance using a Set based operation.

Please verify that the end result is two (2) virtually duplicate rows.

|||

THE END RESULT WILL BE A SSN WITH 2 DIFFERENT SORT KEYS FOR EACH SSN IN THE TABLE. WHAT DO YOU MEAN BY SET BASED OPERATION?

THANKS,

|||

My mistake for not completely reading your code.

A Set based operation would be making these updates in one query.

Try something like:

UPDATE t
SET
t.ssn2 = s.ssn,
t.testDesc2=s.testDesc2,
t.testCode2=s.testCode2,
t.testDate2=s.testDate2,
t.testScore2=s.testScore2,
t.testSource2=s.testSource2,
t.Fcode2=s.fcode2,
t.sortkey2=s.testsortkey2
FROM TestScoresRpt t
JOIN ScoresAll s
ON t.ssn = s.ssn
WHERE t.testsortkey <> s.testsortkey2

(This is untested, so please test, test.)