I am trying to attach to aMDF and LDF file that are on the remote location. When you try to do this though getting an error..
"File filename.mdf is on a network device not supported for database files."
How to do this.
Could you please help me to sort out this problem.
Thanks in advance
WillSmith
mail2willsmith@yahoo.com
Hello,
Completely new to this stuff, so sorry if I'm asking
something that is painfully simple to resolve!!! Also, not sure whether
there are standard formats for code, errors etc.
OK. When I create a Login.aspx file in VWD Express on my local Hard Disk Drive, the file works and runs correctly, allowing
me to log in etc.
However, when I do exactly the same thing using a remote Network Drive connection to an Intranet server, when you enter the
Username and Password, and Click the Log In Button, the following error message appears:-
The file "[Network Drive]AuthorsApp_Dataaspnetdb.mdf" is on a network path that is not supported for database files.An
attempt to attach an auto-named database for file
S:AuthorsApp_Dataaspnetdb.mdf failed. A database with the same name
exists, or specified file cannot be opened, or it is located on UNC
share.
Then I get the following information - which I'm sure is supposed to help, but doesn't!!!
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
The file "S:AuthorsApp_Dataaspnetdb.mdf" is on a network path that
is not supported for database files.An attempt to attach an
auto-named database for file S:AuthorsApp_Dataaspnetdb.mdf failed. A
database with the same name exists, or specified file cannot be opened,
or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): The file "S:AuthorsApp_Dataaspnetdb.mdf" is on a network path that is not supported for database files.An attempt to attach an auto-named database for file S:AuthorsApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +56 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1083 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +687 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +82 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +558 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +126 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +651 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +160 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +114 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +225 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +157 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +68 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +100 System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +100 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +113 System.Web.UI.WebControls.Login.AttemptLogin() +178 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +134 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +107 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +178 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3838
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
All assistance greatly appreciated!!!
I have two, probably related, problems:I have a very simple site. It will be used on the local intranet only. I want to use the windows authentication so users do not have to log on. The aspnetdb has been created using the configuration tool. If I use Server Explorer in vs to work on the site on my local machine, I can open aspnetdb, look at its tables, etc. If I use Server Explorer in vs to work on the site on the server, aspnetdb will not open and gives this error message: ..."network path that is not supported for database files"...Other sites on the same server give the same message, but the sites work. I think this is because they can read the file but not write it; but that is a guess. We have tried everything; my head is ready to explode. We are using SQLServerExpress. It resides on the server's C drive (as does our data database). The web site, and hence the aspnetdb are on the F: drive of the same machine. Does SQLServerExpress treat the F: drive as a UNC drive? If so, are we in deep doo doo? I'm at a loss.As far as logging in: If I debug from vs, the login works beautifully both on my local machine and running from the server using vs. Login does not work when accessing the site from iexplorer. Mystified by that one, too. I tried to implement profiles and they crash, too; that led me to consider a write problem.Don't know what to do; any help is appreciated.
View 1 Replies View RelatedAny specific risks in placing database files on network shares on a windows server (2008 R2). I have read its a bad design, but unsure why, or what problems such a setup may cause?
View 4 Replies View RelatedCan anyone help me to recreate the .NDF file if it was lost and not restorable? I have a stored procedure written that will then help me rebuild all indexes on all tables, but I can't seem to figure out how to create the index file again so I can run it. I have tried index files from other databases to no avail. There is no backup to go to.
View 10 Replies View RelatedI need to backup a remote Database to a local device. Does anyone know how I do this . How do I define a remote Network device/ what priviliges are needed
How I do set up the Backup job....
I have several clients who are reporting a negative file size on their database devices in Enterprise Manager. The sp_helpdevice procedure reports the size correctly. Any suggestions?
View 1 Replies View RelatedHi All,
I need to refresh my cube daily and once this processed is done, i will update in ETL table with lastcubeprocessedtime in my timestamp column. Actually i am using IBM db2 provider since my ETL table resides in IBM DB2 source.
In my first task i am using script task where i will check whether my timestamp column value is NULL or not.
I am getting error like "The script threw an exception : keyword not supported: 'network port'." when this script task executes.
Moreover i am getting another error like "The execution succeeded, but the number of error raised(1) reached the maximum allowed(1); resulting in failure. This occurs when the number of errors reaches the number of specified in MaximumErrorCount. Change the MaximumErrorCount or fix this errors." For this error i have changed MaximumErrorCount value from 1 to 100 in dataflow task.
Can anyone tell me what might be the problem for these two errors?
Thanks in advance
Anand Rajagopal
Can multiple instances of SQL 2005 Express attach to the same database files on a network share? I have seen this done before with MSDE where the database files are stored on the server, but instead of having a SQL server running on the network and then connecting to it, only the database files exist on the network share and the users connect through MSDE running on the local machine. Is this possible with SQL2005Express? I do not have the ability to share an SQL instance from one workstation to another nor do I have the ability to install an instance on the corporate server. Is it as simple as creating the database and storing the files on the share then attaching the database to the SQL Instance on each workstation?
View 3 Replies View RelatedHello,
I have Sql Server in one pc and I want to attach one databse that is in another computer that doesnt have SQL Server. Sql Server doesnt permit UNC path when attaching database files. Is there anyway to workarounf this. Is Trace Flag 1807 of any use?
Thanks
I recently created a program that connects to a Microsoft SQL database that was stored on my computer and it worked fine. As soon as I tried to connect to the same database via a network drive I got an error stating that "The file Y:Filename.mdf is on a network path that is not supported for database files.". I can't seem to get it to work, if anybody has any ideas what I'm doing wrong I would appreciate your help.
Don
I have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
View 6 Replies View RelatedI have a requirement to move files from HOLD folder to input folder. In HOLD folder I receive multiple files starting with af, ai, ar i.e. af*.txt , ai*.txt, ar*.txt . I need to move one file at a time to input folder as each file is to be loaded into database before next file is processed. In all the files the SSIS has to look at ai*.txt files first followed by af*.txt and lastly ar*.txt. If there are multiple files of same group the file with oldest date has to be moved first. How do I achieve this?
View 5 Replies View RelatedNeed to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
Hi All
I have taken ownership of a SQL 7 Server that has many DB's with each DB's devices spread all over the 6 disks that exists in the Server.
e.g.
DB - Reference has
Data devices on D (20mb), E(40mb) , F(60mb) drives
and Logs having a similar spread.
Now what I want to do is to restore a full backup of the database into a new db but with only one data device and one log. So that basically the server is tidied up. The reason the devices are spread is not due to performance reasons.
The QUESTION IS : How do I restore a full back up of a db with many devices in to a NEW db with only 1 data device and 1 log device.
Thanks
Steve
sfarmer@edfman.com
When you create a dump device, then add backups with the NOINIT arg, the space taken up on the device grows, is there a way of removing individual backups from the device without (i guess) purging the whole lot by running WITH INIT?
I can get a list of the files by running the RESTORE WITH HEADERONLY, but I can't see how to remove individual ones.
I created a backup device and a job to backup a full dbs and transaction logs. When I first created the backup statement it was created with RETAIN DAYS = 10
Its been 15 days and the ones created after 10 days appear to be marked for deletiong but I am obviously missing a statement in my backup statement or in another job I need to schedule to come through and delete the old files from the backup device. Please help me figure out what this statement needs to be.
All the backups and T-log backups are written to one device which is one file.
Thanks
Scott
Hi all,
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
Thanks,
Scott Chang
Hello everybody
I was just wondering if anybody knows the correct way to write a query that will output to a text file in 2005
Right now i'm doing
select * from tablename TO FILE 'c: hefile.txt'
But I keep getting a syntax error near TO
All the examples i've seen use this but i don't think they are for 2005.
Any help will be appreciated
Thx
Doesn't appear you can do this.
Am I wrong?
Please tell me I am.
Hi All,
I am trying to automate a rather complicated file import routine using a DTS package. I'm getting on ok with it but have now reached the limits of my knowledge.
At the moment I am sent a file every month e.g. "perf04-05m1.csv". I then rename it to "import.csv" and run the DTS package. I would like to make the process more dynamic so that the the DTS package takes in the most recent file no matter it's filename e.g. I don't have to re-name the file to "import.csv" in order for the DTS package to run.
I have had a look at BOL and think that I need to use either a Dynamic Properties Task or a Global variable but I'm not sure how to tie it all together, can anyone offer any pointers or know of any good articles I can have a look at. I may also need to dynamically alter some of the SQL statements within the DTS package as well.
Cheers
Hello
I'm using the ForEach File task and masking the files I want to process as ABC??.TXT.
It all works well until I drop a file there like ABC12 Copy.TXT, which I don't expect to be process, but the loop still picks it up. Am I using the wrong wildcards?
newstar1860
I would like to add to it the actual file size in mb or gb of each file to the results.
select sd.name,mf.name as logical_name,mf.physical_name,
case
when dm.mirroring_state is null then 'No'
else 'Yes'
end as Mirrored
from sys.sysdatabases sd JOIN
sys.master_files mf on sd.dbid = mf.database_id
join sys.database_mirroring dm on sd.dbid = dm.database_id
The sp_spaceused procedure does a nice job on it's own giving me what I want (only one db though), plus a bonus allocated space column. How can I combine this sp with my other query, or is there a better way to ad this information?
I'm guessing the best way / only way to store a filename is to store it as a varchar?
I'm using it as a reference to pictures wefor a gallery
I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.
I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.
Any ideas or suggestions?
The MDF and LDF files are placed in SSD drive and tempdb files are placed in HDD drive. Snapshot isolation is enabled on the database. When a script is executed to insert data with NULL value to a table which has NOT NULL column, the transaction fails and then a log undo happens which fails and takes the database to suspect mode.
But when the MDF and LDF files are placed in HDD drive all this do not happen. The transaction just fails.
My task is to write an SSIS package that picks up just one file from a directory and loads it into a database table. The filename is defined as being "ABC*.txt". So I must pick up only one file that matches that wildcard.
I can see two ways of doing this, but I can't get either to work:-
1. Use a Flat File Source connection and put the wildcard in the ConnectionString.
i.e. ConnectionString = "C:\mydirABC*.txt"
But SSIS doesn't seem to support that.
2. Use a Foreach Loop Container with a Foreach File Enumerator, and configure the enumerator as:-
Folder = c:mydir
Files = ABC*.txt
This works well, but loops round for as many files match the wildcard. Is there any way of forcing it to drop out after the first time round the loop?
Or am I missing a much easier solution?
Thanks.
I was wondering if there is a way to copy files from a network to an ip site where you would need to log in at. What I am trying to do is make a SP that would take all .csv files from the network drive and place them in the other ip address folder however you would need to use the login information.
View 5 Replies View RelatedI have several databases that have grown to 300 GB and would like to distribute the data into multiple files across multiple drives. Can I create a new database that is spread across the new drives and use a full backup to restore or am I stuck with unloading the data table by table?
View 3 Replies View RelatedI've just applied CU7 to a set of SQL2005 SP2 servers with no problems, except on one server.
I've now fixed the problem, but couldn't find description of it elsewhere so post it here in case it helps anyone else with the same problem.
Here's the problem I had. The server is Windows 2003 with two instances of SQL 2005 SP2 (9.00.3186)
1) Ran the patch (SQLServer2005-KB949095-x86-ENU.exe)
2) C: drive ran out of space whilst patch was running against instance A
3) Cleared down space on C: drive
4) Patch had hung, after 45 minutes of no activity I killed it
5) Re-ran patch, it reported instance A as upgraded already and just upgraded instance B
6) After reboot instance A failed to start. Instance B started with no problem.
The service was failing to start with the following recorded in the log:
Server Microsoft SQL Server 2005 - 9.00.3239.00 (Intel X86)
[]
spid5s The resource database build version is 9.00.3186. This is an informational message only. No user action is required.
spid5s Error: 5173, Severity: 16, State: 1.
spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
spid5s Error: 5173, Severity: 16, State: 1.
spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
spid5s Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAmssqlsystemresource.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
spid5s The log cannot be rebuilt when the primary file is read-only.
spid5s Error: 945, Severity: 14, State: 2.
spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I think that the patch must have got part-way through instance A, enough to mark it as being patched, but not far enough to patch the mssqlsystemresource system database (note the version numbers in the log above - 9.00.3239 vs 9.00.3186).
The hotfix log records an error opening the registry, but the detailed log was overwritten by the subsequent (successful) run of the hotfix so theres no more detail available.
To fix this I copied in the mssqlsystemresource mdf and ldf files from instance B (which wasnt patched the first time round, and was subsequently patched successfully). From what I understand mssqlsystemresource is read-only and holds copies of system sprocs etc, so a copy will suffice. Plus I am assuming the SQL Server is clever enough to complain if its not happy with the instance of the file.
Note that the problem recorded elsewhere for this error, where mssqlsystemresource or other system DBs have been moved from their original installation folders, was not the cause here.
Not sure if this is the 'correct' fix, but it got Instance A up and running. Obviously YMMV
I have an XML file that contains a field that has over 8000 characters in it. I cannot use a (n)varchar for it, I must use a Text for it (although as a last resort I could split the string into several (n)varchar columns). I want to set the external column of the XML column to a DT_TEXT and I receive and error message that states :
Error at Import XFFD Data [xffd [1]]: The SSIS Data Flow Task data type "DT_TEXT" on the external metadata column "reviewText" (32411) is not supported for the component "xffd" (1).
I've tried converting the nvarchar into a text stream with the use of a Data Conversion Transform, but I think the Validating steps are truncating the field.
HELP. I've been beating my head against this for a couple hours a day.
Thanks,
Scott
Hi,
I am adding a file to a filegroup on a remote system, and i don't know the direcotry structure of that machine, how can i provide the path for FILENAME param. :eek:
ALTER DATABASE TESTDB ADD FILE (NAME = N'TESTFILE', FILENAME = N'physicalfilepath.ndf') TO FILEGROUP TESTFILEGROUP
Warm Regards,
Mushq
I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:Test.txt.
When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\Text.txt" ... so apparently XMLA is adding the escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.
What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.