Showing posts with label ssms. Show all posts
Showing posts with label ssms. Show all posts

Tuesday, March 27, 2012

Can't Attach to DB File using SSMS

Hi all,

I have "SQL Server Express" as well as SSMS-E installed on the same machine. I am trying to get SSMS-E to connect to a database (i.e., an .MDF file) that resides in a folder on the desktop.

After establishing a connection to my "SQL Server Express" instance within SSMS-E, I right-click on "Databases", in the "Object Explorer" window and select "Attach..." and click the "Add..." button. The resulting dialog box opens in the directory:
"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" where I see several MDF files. But as I said, my MDF is not in this directory - it's on the desktop.

If I try to navigate to the proper directory on the desktop I see no MDF files. In fact, I cannot even navigate past my username -- that is, I cannot even navigate to the desktop, let alone folders on the desktop. I don't know why.

Of course I *could* put the MDF file in the SQL-E approved directory but shouldn't I just be able to navigate to it using SSMS-E? Incidentally, I *AM* able to connect to the database on my desktop if I use Visual Studio and I specify "UserInstance=true". Not specifying "UserInstance=true" results in this error:

An attempt to attach an auto-named database for file C:\Documents and Settings\Robin\Desktop\MyCompany.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Can someone explain what might be causing the problem with SSMS-E, and, for that matter, why it's necessary to specify "UserInstance=true" in Visual Studio?

Thanks so much. I hope these are not too many questions.

Robin

You are attempting to attach a file that is located in a 'protected' storage area -specifically your private 'Desktop'. That file would not be available to other users of the computer. You can attach it to a user instance since that user instance will be restricted for your use only and other users could not access it.

If you want to attach the file to the server, move the file from your 'Desktop' to a storage area accessible for all users of SQL Server.

Monday, March 19, 2012

cannot view wsdl of endpoint.

i created this endpoint in SSMS:

Code Snippet

/****** Object: Endpoint [first_Endpoint] Script Date: 06/19/2007 16:39:22 ******/

CREATEENDPOINT [first_Endpoint]

AUTHORIZATION [Domain\username]--scrubbed my username out of post!

STATE=STARTED

ASHTTP(PATH=N'/sql',

PORTS=(CLEAR),

AUTHENTICATION=(NTLM,KERBEROS,INTEGRATED),

SITE=N'sitename,

CLEAR_PORT= 80,

COMPRESSION=DISABLED)

FORSOAP(

WEBMETHOD 'provideInfo'(NAME=N'[adventureworks].[dbo].[uspGetBillOfMaterials]'

,SCHEMA=DEFAULT

, FORMAT=ALL_RESULTS),BATCHES=DISABLED,

SESSIONS=DISABLED,SESSION_TIMEOUT=60,

DATABASE=N'AdventureWorks',

NAMESPACE=N'http://tempuri.org/',

SCHEMA=STANDARD,

CHARACTER_SET=XML)

When i type http://localhost/sql/provideinfo?wsdl into internet explorer, i just get a 404 page cannot be found error.

When i type http://server/sql/provideinfo?wsdl in to the browser i get a Page cannot be displayd error.

I get the same when i type http://myServerName/sql/provideinfo?wsdl

I get the same when i type http://sitename/sql/provideinfo?wsdl

Sql server is running under an account with admin access to the box and sa access to the sql server. what am i doing wrong here that i cannot view my wsdl. oh, the OS is vista.

Could it be a configuration issue that im not seeing?

Can you try the following?
http://localhost/sql?wsdl

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372924&SiteID=1|||i tried http://localhost/sql?wsdl but still get a 401 not found error. as i am not getting the error speicifed in the link you provided, it would make me think that it is unrelated to my issue. thanks for the reply though.|||

when i use http://server/sql?wsdl i get a 502 error, a connection could not be made as the server actively refused it.

Are there some network settings i should change on my machine possibly?

|||Is server here the same name which you specified in SITE?|||no. the server and the site have different names. I have tried the url both ways though. when i specify the servername in the url, i get an iis page not found error. when i try with the sitename, i get a page cannot be displayed error.|||

The way the 'SITE' keyword value is treated is that it is passed to the OS HTTP.sys service. The HTTP.sys service uses a combination of information to determine which process to redirect the HTTP request to. In this scenario, only HTTP requests send to http://sitename/sql are redirected to this SQL Server endpoint. So, unless machine running SQL Server is named 'sitename', it is very unlikely that the HTTP request will get to SQL Server.

If possible, I recommend changing the SITE value to '*' or the actual machine name.

BTW, I believe the default value for the WSDL generation is NONE, so ?wsdl will get you a 502 HTTP error.

Jimmy

|||

Hi Jimmy.


That indeed was the problem!!! i dropped the endpoint, changed the site name to the name of the pc the endpoint is hosted upon and bingo!!!! cant believe i was so stupid in the first place!!!! it makes total sense now that i look at it! I expected to see the page that comes up when you view the definition of an enpoint in IE, but i also realise that that wont happen. i just see a lot of xml making up the wsdl file, and thats what i required. Thanks for the help and suggestions everyone, and Jimmy, for the answer that gave me the required results.