Attach command failes

Hi
I have just received data files ( .mdf and .ldf ) and I need to attach them
to a sql express 2005. My machine is WinXP vers. 2002 SP2.
I downloaded the varius files (.NET 2, sql 2005, sql management studio
express) and installation was ok. No errors. No warnings
I am trying now to attach the data from management studio console (Database
- Attach).
But when adding .mdf file location I receive the following message:
"Failed to retrieve data.. CREATE FILE encounted operating system error 5
while attempting to open/create... Error 5123"
Do you miss anything? Any idea?
Thank you for your suggestions.Francesco wrote:
> Hi
> I have just received data files ( .mdf and .ldf ) and I need to attach them
> to a sql express 2005. My machine is WinXP vers. 2002 SP2.
> I downloaded the varius files (.NET 2, sql 2005, sql management studio
> express) and installation was ok. No errors. No warnings
> I am trying now to attach the data from management studio console (Database
> - Attach).
> But when adding .mdf file location I receive the following message:
> "Failed to retrieve data.. CREATE FILE encounted operating system error 5
> while attempting to open/create... Error 5123"
> Do you miss anything? Any idea?
> Thank you for your suggestions.
Google says this is "access denied":
operating system error 5 !!!|||On Feb 14, 8:01=A0am, Zarko Jovanovic
<mind_lessIsHsAsTsEsSsPsA...@.inet.hr> wrote:
> Francesco wrote:
> > Hi
> > I have just received data files ( .mdf and .ldf ) and I need to attach t=hem
> > to a sql express 2005. My machine is WinXP vers. 2002 SP2.
> > I downloaded the varius files (.NET 2, sql 2005, sql management studio
> > express) and installation was ok. No errors. No warnings
> > I am trying now to attach the data from management studio console (Datab=ase
> > - Attach).
> > But when adding .mdf file location I receive the following message:
> > "Failed to retrieve data.. CREATE FILE encounted operating system error =5
> > while attempting to open/create... Error 5123"
> > Do you miss anything? Any idea?
> > Thank you for your =A0suggestions.
> Google says this is "access denied":
> operating system error 5 !!!- Hide quoted text -
> - Show quoted text -
To explain, SQL Management Studio is trying to look for a file where
you do not have permissions. This can be caused by (simple) NT file
permissions or (not so simple) something else is holding the .mdf
file. The latter problem is usually some type of anti-virus software
trying to scan the .mdf and .ldf files.
If you've double-checked the directory permissions, you may want to
use Sysinternals Process Explorer (which is a free download from
Microsoft) and search for handles on the .mdf - that will tell you if
something else strange is going on.|||Make sure SQL Management Studio is running as admin. Setting full
permissions on the database file alone wasn't sufficient for me. Sign on as
admin in Win XP. If using Vista, right click on the SQL Studio application
link and select Run as Administrator. Then you will be able to attach a
database from anywhere in the file system.

Attach a database

Yesterday, I found I could not do a select into in my development staging
database. I received the following message...
Server: Msg 9001, Level 21, State 3, Line 266
The log for database 'DB_ST' is not available.
Connection Broken
I found that there was no log file!!! I have no idea why not. So I decided
to try to detach the database and re-attach using sp_attach_single_file_db a
s
follows and received...
USE MASTER
EXEC sp_attach_single_file_db @.dbname = 'DB_ST',
@.physname = 'D:\Databases\DB_ST_DATA.mdf'
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'D:\Databases\QUINN_ST_DATA.mdf' may be incorrect.
Now I am stumped with no database and log. I have just called the
applications manager who thinks we may need to restore from another
environment. I question whether he backed up the development environment!!!!
!
Is this the only option I have?Yes. sp_attach_single_file_db might is only guaranteed to work if you detach
properly the database with sp_detach_db first, that is with the log file
still there. In other situation, like the one you have, it might work
sometimes, but is far from guaranteed. In that case you need to restore from
a backup.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:EDB0B871-2BFD-4720-9896-395234B36662@.microsoft.com...
> Yesterday, I found I could not do a select into in my development staging
> database. I received the following message...
> Server: Msg 9001, Level 21, State 3, Line 266
> The log for database 'DB_ST' is not available.
> Connection Broken
> I found that there was no log file!!! I have no idea why not. So I decided
> to try to detach the database and re-attach using sp_attach_single_file_db
> as
> follows and received...
> USE MASTER
> EXEC sp_attach_single_file_db @.dbname = 'DB_ST',
> @.physname = 'D:\Databases\DB_ST_DATA.mdf'
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'D:\Databases\QUINN_ST_DATA.mdf' may be incorrect.
> Now I am stumped with no database and log. I have just called the
> applications manager who thinks we may need to restore from another
> environment. I question whether he backed up the development
> environment!!!!!
> Is this the only option I have?|||Thanks Jacco... I thought that would be the case.
"Jacco Schalkwijk" wrote:

> Yes. sp_attach_single_file_db might is only guaranteed to work if you deta
ch
> properly the database with sp_detach_db first, that is with the log file
> still there. In other situation, like the one you have, it might work
> sometimes, but is far from guaranteed. In that case you need to restore fr
om
> a backup.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:EDB0B871-2BFD-4720-9896-395234B36662@.microsoft.com...
>
>sql

attach Help

I received three Files .mdf .ldf .ndf and a text how to use attach
but i always receive an error message "database is write protect ......"
What I do wrong ?
byeFirst of all, what did you do?|||Are the three files read-only at the O/S level? This can happen if the three files were sent as attachments in e-mail.|||could it be that the database already exists and in read-only mode?|||Are we even talking about SQL Server?

And when does the damn weekend start already.....

DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 16 2004 17:00:00'
SELECT DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margaritaville|||Sorry for mistakes
I'll start again
i have 3 Files xxx.mdf,xxx.ndf,xxx.ldf from originally sql7 ,from network and I'd like to atach to Sqlserver 2000; on sql server 2000 is the database not present
With Query Analyzer I tested i did:
Exec sp_attach_db
@.dbname="xxx",
@.filename1= "xxx.mdf",
@.filename2= "xxx.ndf",
@.filename3= "xxx.ldf"

With Sql 2000 Manager I use The menu attach

In both case i received this message "The Database is write Protect you
have to unptrotect.......

bye|||Right click on each file in succession, go to properties, and see if any of them have the read-only checkbox marked. If so, uncheck that box.|||I checked the free file , they all are not write protect|||As a test, see if you can rename one of the files. Maybe backup software, or virus scan has it open?

I just tried a test of re-attaching a readonly filegroup, and it worked. Although, I do not have access to a 7.0 machine, anymore to try out a readonly filegroup with an upgrade.

That sparks a thought. Do you have access to the database in it's original state? That is, can you see if the original database (on SQL 7.0) is read only or had read only filegroups?|||I can rename the files, and I open the file .ndf with an Hex editor
there are more line with read only inside the file

bye

Assistance please using txt

I have received a table of data that has a field containing date
information. Unfortunately it was derived from a MainFrame dump and
originated as a txt file and was then ported into an Access MDB file
before it became an SQL table. The date format is vchar(50) and
actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
need to run a select statement for a range of dates such as 010104
thru 030104. Unfortunately being a charecter field this returns
incorrect results under a majority of cases. Back in my dBase days
there was a VAL() that could be used in this case but I have been
unable to find anything comperable in SQL. Can anyone help me please?

Thanks in advance

Steve"Steve" wrote:
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Steve,

I've never worked with dBase, but I assume that VAL() would convert to an
integral data type, so that wouldn't work either: 123103 would be greater
than 010104. Also, if you have any dates before Y2K, you'll have issues
there.

One more issue: you mentioned big iron... if the date field in the mainframe
file was PIC 9(6), you might want to verify that somewhere between the file,
Access, and SQL Server, you still have all leading and trailing zeroes...

select min(len(somefld)), max(len(somefld))
from sometable

...Anyhoo, I would recommend using string functions (like LEFT, RIGHT, and
SUBSTRING) and the CONVERT function to create a real datetime column.
Barring that, you could convert the data to a datetime for the query.

Assuming that every field is 6 characters in the format mmddyy, here's a
little test I cobbled together. Note that I'm assuming you're using SQL
Server 2K: otherwise the table variable won't work. Also, I chose what SQL
Server BOL calls the ANSI date format, but YMMV depending on your regional
date/time settings...

set nocount on

declare @.test table (
src varchar(50),
dest datetime
)

insert @.test values ('123198', null)
insert @.test values ('010199', null)
insert @.test values ('123103', null)
insert @.test values ('010104', null)

--Use 2: the ANSI (yy.mm.dd) style for conversion (the
--final parameter in the convert call)
update @.test
set dest = convert(
datetime,
right(src, 2) + '.' +
left(src, 2) + '.' +
substring(src, 3, 2),
2
)

--Out of whack
select * from @.test order by src

--In whack
select * from @.test order by dest

Craig|||Steve,
Datetime fields (there are no Date fields) in SQL Server need to be in
the format yyyymmdd, so you need to format the field first before inserting
it into your database. Do that like this:

declare @.myDate as varchar(50)

set @.myDate = '010104'

select cast(
'20' +
right(@.myDate,2) +
substring(@.myDate,3,2) +
left(@.myDate,2) as datetime)

I hard-coded '20' as the century.
Also, the way to import the file would probably to create a DTS package and
load the data into a temp table, then create a procedure using the above
statement as part of the stored procedure.
OK, now for the pitch. If you are new to SQL Server 2000, a great way to
get up to speed in just a few hours is with our video series on SQL Server
2000 at www.TechnicalVideos.net. Our videos give tips and tricks from
experts in the field, while they show you on the screen just how to do them.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Steve" <shull@.dpd.dallascityhall.com> wrote in message
news:6318603c.0403041803.7a69d4f4@.posting.google.c om...
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

ASP.Net Server Setup Exception (reqsql.exe)

I was attampting to add application services to a remote server to my ASP database and received and exception.

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated.

-
Details of failure
-

SQL Server:
Database: [consecdevdb]
SQL file loaded:
InstallCommon.sql

Commands failed:

CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)

INSERT INTO #aspnet_Permissions
EXEC sp_helprotect

IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions

SQL Exception:
System.Data.SqlClient.SqlException: String or binary data would be truncated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Can anyone help me with this?

The problem is that the "Action" column is defined as varchar(20) but it actually needs more space. Change the script to something like 200 and it should work...

Hope this helps!

-- Scott Whigham|||

Scott Whigham, This really help a lot, first I think is access problem, because i using sql to run the script, it give the error msg is

Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.

I keep on setting on the access right problem , still cannot solve it.

InstallCommon.SQL is default file in framework folder, how come got this kind of error?

hope microsoft can solve it....

-- Shiro Amada

ASP.Net Server Setup Exception (reqsql.exe)

I was attampting to add application services to a remote server to my ASP database and received and exception.

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated.

-
Details of failure
-

SQL Server:
Database: [consecdevdb]
SQL file loaded:
InstallCommon.sql

Commands failed:

CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)

INSERT INTO #aspnet_Permissions
EXEC sp_helprotect

IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions

SQL Exception:
System.Data.SqlClient.SqlException: String or binary data would be truncated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Can anyone help me with this?

The problem is that the "Action" column is defined as varchar(20) but it actually needs more space. Change the script to something like 200 and it should work...

Hope this helps!

-- Scott Whigham|||

Scott Whigham, This really help a lot, first I think is access problem, because i using sql to run the script, it give the error msg is

Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.

I keep on setting on the access right problem , still cannot solve it.

InstallCommon.SQL is default file in framework folder, how come got this kind of error?

hope microsoft can solve it....

-- Shiro Amada