Is it not possible to access sql 2005 database, without attaching it first in management s

Hi All ...

I am not facing a problem while connecting to sql server 2005 databse, when i attach that first, in management studio, then I can access that from localhost, its fine at this point. but when i detach that database from management studio i am facing the follwing error while accessing it from localhost i.e.

System.Data.SqlClient.SqlException: Unable to open the physical file "D:\database\Hunzian.mdf". Operating system error 5: "5(Access is denied.)". An attempt to attach an auto-named database for file D:\database\Hunzian.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The connection string to access that database is

undefined=@."Data

I will be happy to have your replies .

I am confused over " detach that database from management studio " - are just closing the connection for SQL Management Studio or are you detaching it.

Where is the database located w.r.t. to the localhost you refer to - is it on one of its drives or on a network share?

|||

Hi ... thanks for the reply ...

I am actually detaching the database from management studio, that database is actually situated at one of my local drive D:\Database\database.mdf. When I detach that database I am facing the above mentioned problem.

Regards

|||

Hi,

Is this error message thrown when you click detach? I think it might be generated when you connect to this database. Please check if you have granted proper permission on that folder to ASPNET account or Network Service account.

|||

Thanks,

When i detach the database from Management Studio, acctually i get this error while i try to connect to the database through localhost.

I am able to connect to the database when i attach database first from management studio then connect through localhost

What i want is:

I have detached the database from management studio, its fine uptill now, but i want to connect to that database from localhost without attaching it in management studio.

I hope you understood the scenario, the connection string works just fine while testing that in visual studio built in web server. I have already given permissions to ASPNET user.

Regards,

Is it a SQL Server 2000 bug

Hi, I'm currently using SQL Server 2000 SP3.
I'm facing some problems which make me think of a SQL Server BUG.

When executing a query, it seems that SQL Server tries to convert data
even if not in the resultset -> This leads to SQL Server error.

Here is a very easy sample to reproduce it :

SET NOCOUNT ON
create table tempdb..test ( coldate varchar(30) )

insert into tempdb..test values ( '1900/01/01 00:00:00' )
insert into tempdb..test values ( '2005/01/01 00:00:00' )
insert into tempdb..test values ( 'Invalid date' )

select * from tempdb..test
where isdate( coldate ) = 1
and convert( datetime, coldate ) > GETDATE()

SQL Server output is the following :

Server: Msg 241, Level 16, State 1, Line 10
Syntax error converting datetime from character string.

SQL Server considers as an error the 'Invalid date' even if a filter
ISDATE = 1 is applied...
I guess that I am not able to determine the order in which filters are
applied as it is SQL Server optimizer job... However, this should lead
to an error only if conversion fails on a line of the resultset after
application of all other filters...

What am I doing wrong ?

Thanks
PatrickNot a bug. The execution order is determined by the query optimizer.
There is no reason to suppose that the ISDATE expression will always
execute first.

What are you doing wrong? Firstly, you are writing queries against
dates stored as strings. If at all possible you should convert the
dates to use a proper DATETIME column. If you really cannot do that
then you should be able to rewrite your query using a derived table.

The second thing wrong here is that you are relying on an implicit
conversion from a non-standard date format. Those conversions are
sensitive to local server and connection settings so avoid them. Try
the following:

SELECT coldate
FROM
(SELECT REPLACE(REPLACE(coldate,'/','-'),' ','T') AS coldate
FROM tempdb..test
WHERE ISDATE(REPLACE(REPLACE(coldate,'/','-'),' ','T'))=1) AS T
WHERE CAST(coldate AS DATETIME) > CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--|||Thanks for the answer.
However, I thought that the optimizer was designed only to optimize the
query but resultset was independant of the order of execution of
filters.
Here, it appears that depending on the order of application of filters,
result is not the same...
I guess that this is a special case due to the abuse of conversion
use...

Thanks
Patrick

*** Sent via Developersdex http://www.developersdex.com ***|||Hi, Patrick

For details about this problem and possible solutions, see this article
by Itzik Ben-Gan, SQL Server MVP:
http://www.windowsitpro.com/Windows...148/pg/1/1.html

Razvan|||No, an SQL Statement is a specification of the result, and
theoretically, it is created "all at once". It is not good enough that
you will get correct results if the query is executed "left to right,
top to bottom", because that is not how SQL works.

A solution for your problem could be to place the two related predicates
in a case expression. A case expression always has to be evaluated from
left to right.

select * from tempdb..test
where CASE WHEN isdate( coldate ) = 0 THEN 0
WHEN convert( datetime, coldate ) > GETDATE() THEN 1
ELSE 0 END = 1

Hope this helps,
Gert-Jan

Patrick Fiche wrote:
> Thanks for the answer.
> However, I thought that the optimizer was designed only to optimize the
> query but resultset was independant of the order of execution of
> filters.
> Here, it appears that depending on the order of application of filters,
> result is not the same...
> I guess that this is a special case due to the abuse of conversion
> use...
> Thanks
> Patrick
> *** Sent via Developersdex http://www.developersdex.com ***|||Here is another way:

select * from tempdb..test where case when isdate( coldate ) = 1
then convert( datetime, coldate ) else getdate() end > getdate()

Razvan|||Thanks all for your help.