Invalid value for key attachdbfilename.

HI,

We upgraded to SQL Server 2005 Standard Edition for our ASP.NET 2.0 website. We were using SQL Server Express 2005. That worked fine. Now we are unable to connect to the database. I have googled, but I just cann't figure out what is going on. Any help is appreciated. Here is the error.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

In our firewall sqlbrowser.exe and sqlservr.exe are allowed.

Thanks Matt

Matt,

I have seen this error everytime I install MS SQL 2005, you need to enable TCP/IP as by default is not

Open the program -> SQL Server Surface Configuration

Go To -> Surface Area Configuration and Connections

Go To -> Remote Connections

and change Local Connections only to

Local and Remote connections

|||

AL,

Thank you for the quick reply. I did what you said and it was configured to,

Local and Remote Connections

Using both TCP/IP and named pipes,

already.

My databases reside inside my App_Data folder in my ASP.NET 2.0 application.

Do you have any other ideas?

Could it be my connection string?

Thank you again.

Matt

|||

Matt,

My biggest apologies, I jump to the answer as I have seen this message more than once. Again sorry.

If you already configured that, and you open all the ports on the firewall, I do not remember the port number but you can test from one computer to the other by opening a dos box and doing

telnet servername portnumber

If connects, you are good!! If does not ... open the port in the firewall

|||

Al,

Thank you again for helping me. I think that it is my fault. I don't think I have given you enough information about my situation.

I am trying to move my asp.net 2.0 website from sql server express to sql server 2005 standard edition.

So far all I have done is uninstall sql express and then installed sql server 2005.

I configured sql server 2005 with, what I assume to be the standard setup. I just used the wizard and did not change anything else. Those settings from the previous post were what was already checked when you told me to look at them. I did not set them.

I think from the little bit of research I have done, that I cann't use the AttachDbFile feature that sql express uses for sql server 2005?

At this point I am completely lost and frustrated. I am fairly new to this and trying hard to figure it out.

I do appreciate the help.

Any suggestion or ideas?

Thank You Again

Matt

|||

Actually attaching the database is the best solution. Also check on the logins after attaching that the username and password are correct. You can use VS2005 to connect to a database to see if that test works, there is a great wizard with great errors that might tell you what the problem is.

Besides that I am pretty lost, should work :-(

|||

Al,

Thank you for the help. I got it to work with one database. I used the wizrd and it worked great.

I also created a user in sql server 2005. I gave it a bunch of permissions just to get it to work. I think I should narrow thoses down to only the ones it needs.

My only other question is, I ran the aspnet_regsql.exe and it created a database in sql server 2005. But I was unable to use the ASP.NET Configuration tool to create roles and users. I made the same type of connection as with my other database. Error below.

The following message may help in diagnosing the problem:A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Any ideas?

Thank You Again

Matt

|||When you use ASP.NET Configuration, make sure you give it a connection string that will work on the NEW DATABASE for users and roles. If you connect to the new database, you'll see the providers tab|||

Al,

Thanks again. I got it all working now. It was the connection string.

Thanks

Matt

|||

Did you figure it by going to ASP.NET Configuration?

Glad you fix it! Stick around!

|||

No, I worked through it on a post by scottgu. I had .mdf in my connectionstring. When I removed .mdf, it made the connection.

Thanks Matt

|||Ah! Mr Guthrie's blog is like a gold mine!

Invalid Primary Key error during table linking

Hi,

Something strange has happened to my table. I used Enterprise Manager today to delete 3 columns. When I went to re-link the table using Access Linked Table Manager, it gave me an error. I then deleted the link to the table, and tried to Link it again using 'Get External Data--Link Tables'. I am getting an error (no surprise!):

" 'dbo.tblSpaceUse.PK_RoomID' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long".

When I go into Enterprise Manager to 'manage Indexes' on the table, it shows me that the existing index is in fact dbo.tblSpaceUse.PK_RoomID.

About a month ago, I had to rename the index, because it had been pointing to the wrong table. The SQL I used to rename it (in Query Analyzer) is:
EXEC sp_rename 'dbo.tblSpaceUse.PK_RoomID', 'tblSpaceUse.PK_RoomID', 'INDEX'

I have been using the table successfully since then, until today. I have not done anything with the index; the only change I attempted was to delete 3 columns (not related to the index). I do not think I have made any changes to the table since I renamed the index.

I tried to run the rename SQL again (a desperate attempt!) and get the error message:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 192
Either the parameter @.objname is ambiguous or the claimed @.objtype (INDEX) is wrong.

Any ideas on what went wrong and what I can do to fix it?

Thanks,
Lorihave fixed the problem by creating the table anew. thanks anyway.

Introducing FOREIGN KEY constraint

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

ive a foreign key reference on the table APPLICANT MASTER
of the form
FOREIGN KEY (SEXCODE) REFERENCES
APPLICANTSEX(SEXCODE)
ON DELETE NO ACTION
ON UPDATE CASCADE,

can any one help me WHAT THE MESSAGE MEANS ?Originally posted by baburajv
"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

ive a foreign key reference on the table APPLICANT MASTER
of the form
FOREIGN KEY (SEXCODE) REFERENCES
APPLICANTSEX(SEXCODE)
ON DELETE NO ACTION
ON UPDATE CASCADE,

can any one help me WHAT THE MESSAGE MEANS ?

hi,
this link below explains it all:
http://lists.evolt.org/archive/Week-of-Mon-20030421/139403.html
harshal.|||Hi,

that was useful information, but i have doubts,

my table is

ApplicantSex
(
SexCode tinyint,
Sex varchar(6),
constraint pkApplicantSex primary key(SexCode)
)

Applicant
(
AppId bigint,
Name varchar(30),
SexCode tinyint,
constraint pkApplicant PRIMARY KEY (AppId),
constraint fkApplicant FOREIGN KEY (SexCode)
references ApplicantSex(SexCode)
on delete no action
on update cascade
)


here, i can see no "cycles" or "multiple "cascade paths"

then why sql server says

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

pl give some info|||Originally posted by baburajv
Hi,

that was useful information, but i have doubts,

my table is

ApplicantSex
(
SexCode tinyint,
Sex varchar(6),
constraint pkApplicantSex primary key(SexCode)
)

Applicant
(
AppId bigint,
Name varchar(30),
SexCode tinyint,
constraint pkApplicant PRIMARY KEY (AppId),
constraint fkApplicant FOREIGN KEY (SexCode)
references ApplicantSex(SexCode)
on delete no action
on update cascade
)


here, i can see no "cycles" or "multiple "cascade paths"

then why sql server says

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

pl give some info
when do u get this message while delete or while update??
while updation there is a cascade from applicant master to applicant to applicant sex.|||I GET THIS MESSAGE WHILE RUNNING MY SCRIPT FILE (.SQL FILE) FROM THE QUERY ANALYZER.

ONE MORE THING,

"while updation there is a cascade from applicant master to applicant to applicant sex."

i need to know one thing, the "applicantmaster" referes to sexcode in "applicantsex" and

" ON UPDATE CASCADE" means any updation in applicant sex must be cascaded
and not the reverse..(am i correct?)

i hope i made my point clear

thanks for the advice