I would like to know how to change the default data directory in SQL Server Express. I have a partitioned hard disk with SQL Installed on the C: drive. I would like to move the data to d: drive. I have just installed SQL and do not have any databases. I have installed SQL with advanced services.
Thanks in advance for your response.
Hi,
I had SQL Server 2000 installed like below:
Installation Directory = C:Program FilesMicrosoft SQL Server
Default data directory = D:
Default log directory = D:
I found out that the data is stored in D:Program FilesMicrosoft SQL ServerMSSQLdata and backup is done in D:Program FilesMicrosoft SQL ServerMSSQLackup
However, Microsoft says we should never put the ..ackup and ..data folders in the same partition.
My question is: How can I move the path of the backup directory?
I checked the registy but this is what I found:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer
..BackupDirectory=C:Program FilesMicrosoft SQL ServerMSSQLackup (<-- note it is pointing to C: not D:)
..DefaultData=D:
Can somebody help please?
I tried to add volume to default data & log directory of "new data default location" of database setting at SQL server properties, but it never be saved, when I get in properties again, the setting is disappear.
Does somebody could advise me how to save it?
Thank you
Ting
Hi, I've installed SQL 2005 and failed to change the default datalocation during installation. Now I want to change it. Can I do thiswithout having to uninstall and reinstall? And furthermore, is there asystem stored procedure I can use to move existing data and log files(master, msdb, AdventureWorks, etc.)? I seem to remember doing justthis with SQL 2000 some time back.Thanks,Eric
View 2 Replies View RelatedI've created a number of very large databases in Ms Access that I would like to upsize to SQL Server Express. I changed the default data directory location to point to a separate drive with over 400GB of free space on my home pc. However, everytime I try to upsize or link databases using the MS Access Upsizing Wizard the data is saved in the Data directory on my current hard drive and is not saved in the default location I specified. If I create a new database in SQL Express it is saved to the new location. Any ideas on how to force upsizing or uplinked data to go to the new default data location?
View 1 Replies View RelatedWE HAVE RECENTLY MOVED THE MSSQLBACKUP FOLDER TO EMC DRIVES E:, BUT SQL IS STILL DEFAULTING BACKUPS TO THE ORIGINAL DEFAULT LOCATION WHICH WAS ON THE D: DRIVE. DOES ANYONE KNOW HOW TO CHANGE THIS? I KNOW THE ORIGINIAL LOCATION IS STORED IN THE REGISTRY, BUT I DON'T KNOW IF I CAN CHANGE THE REGISTRY OR IF THERE IS A DIFFERENT PLACE. CAN SOMEONE PLEASE HELP.
THANKS
I went ahead and installed RS even though it was clear Microsoft install was confused (re: preivous post). Now I get to experience the thrill of configuration hell.
Report Server Virtual Directory had a green check. Unfortunately the Name block says <Not Set>. When I try to create a New... default directory I get the error message: "The virtual directory could not be created. The previously set virtual directory will still be used."
Does anyone know where the <Not Set> virtual directory is stored on the computer?
addl info:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified already exists. Specify a different name.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)
(Vista Ultimate)
I'm requesting of our dba that he create a database with recovery simple for my peer and I to start using. I'm asking him to give us db owner on this db so we can create schemas, tables, views, procs, do table inserts, deletes etc etc. what sql permission (if any) would allow my peer and I to do a backup once in a while to the default sql directory for backups? And for that matter a restore from there.
View 7 Replies View RelatedI want to change the default directory for my stored procedures. It is extremely inconvenient for me that they are placed in documents and settings... I cannot find a place in the Tools where I can change it.
Thanks.
I am getting a backup file in default directory by using this script
Code Block
BACKUP DATABASE CPHONE
TO DISK = 'CPHONE.Bak' -- send to default pathaway
WITH FORMAT,
NAME = 'Full Backup of CPHONE'
Is there any way to find where is the default path of the SQL server, in order to show to the user from a VB program in which pathway the database back up is stored
Hi folks,
Can you please tell me how should I change the default root directory (used for db storage) in SQL 2005 Std. Edition. ?
Currently its configured to:
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
and I want to change the drive letter here.
Thanks in advance.
Cyk
I am trying to find a solution to a thorny problem I have encountered...
I am a newbie in Sql Server 2000, but have quite a bit of experience in databases, both on PC and mainframe.
I am currently writing a program, which needs, among other things, to create Sql Server databases, to be located NOT in the default Mssql2000 directory, but in the user's home directory.
I am using an ADO connection and trying to execute it, as follows:
The text of the relevant part of the program goes like this:
Dim strCreateDatabase As String
Dim MyCn As ADODB.Connection
Set MyCn = New ADODB.Connection
With MyCn
.ConnectionTimeout = 25
.Provider = "sqloledb"
.Properties("Data Source").Value = "MOP"
.Properties("Initial Catalog").Value = ""
.Properties("Integrated Security").Value = "SSPI"
.Open
End With
strCreateDatabase = "Create Database Rimon on (" _
& "Name = Rimon, Filename = '\mopusersMR_DiazsalpmsdataRimon_Data.mdf', " _
& "Size = 20, MaxSize = 100, FileGrowth = 5) " _
& "Log on (Name = 'Rimon', Filename = 'c:usersMR_DiazsalpmsdataRimon_Log.ldf', " _
& "Size = 2, MaxSize = 10, FileGrowth = 1)"
MyCn.Execute strCreateDatabase
Both the name of the Sql Server and the Win2K server is MOP.
All the directories exist, and I have full access to them.
When I try to run this command, I get the following message:
File
'\mopusersMR_DiazsalpmsdataRimon_Data.mdf' is on a network device not supported for database files.
When I omit this location, it works just fine.
If I run it on the server, using another directory, it works just fine.
Do you have any idea what I am doing wrong?
Thanks in advance for any help!
Rimon Rafiah
Hi all,
I have some problem about Reporting Service integrated for SharePoint 2007.
At first, I created 1 user named "User1" under "Domain1". User1 has role "SharePointAdmin" Group and Local Admin Group.
Then I created application pool named "RSAppPool". Its identy was "Domain1User1".
After that, I install Reporting Service and SP2.
Then I config Reporting Service to Integrated Sharepoint Mode below.
Report Server Directory Setting: "ReportServer" in "Default Web Site".
Window Service Identity:
Service Account : "Domain1User1" (Windows Accout)
Web Service Identity:
Report Server: "RSAppPool" (that created above)
Report Manager: Empty
Database Setup:
Database name: Database1 (Server Mode: SharePoint Integrated)
Credential Type : Windows Credentials
Account Name: Domain1User1
After configuration, I reset IIS and test browse to http://servername/ReportServer/ , then there is a Windows Login dialog.
I enter "Domain1User1" and User1's password. But User1 cannot right to access to "ReportServer" virtual directory.
This is error message
You are not authorized to view this page
You do not have permission to view this directory or page using the credentials that you supplied.
HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials.
Internet Information Services (IIS)
I'm not sure. Where is I config wrong? ....
Please help me.
Thank you very much.
There was a 2008 SSRS service running on server "abc".
I have a .Net UI that uses the wsdl at [URL] ....
They deployed SSRS 2012 service on a new server "xyz".
DNS points abc to xyz. Nslookup looks right shows both xyz and abc alias to the same IP.
when I try to go to [URL] .... it redirects to xyz as expected for the host name but the HTTP GET /ReportServer is appended with GET /Reports/ReportServer which doesn't exist and I get an 404 error.
I have no admin rights to see anything and I have no idea if it was an inplace upgrade or a new server. I assume it's new since the actual server name changed in nslookup. Why would it append the default virtual directory when it makes the hop to server "xyz"?
I look for and try to get data from Active Directory to MSSQL Server, but have same error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM 'LDAP://office.experter.group'
WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
for the code:
select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://office.experter.group''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
I create linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'office.experter.group'
I've changed security setting, format of LDAP. Please advise me
How can I copy data like firstname, lastname, email from Active Directory into a SQL Server table?
View 4 Replies View RelatedI have compile my application into a Smart Device CAB and also including SQLCE CAB file for SQL.
I have users who choose to install SQLCE into Storage Cards instead and causes the application not to function, when asked which destination to install.
I've done some search and found out about "wceload.exe /noaskdest" but I'm using NSIS to compile into a single .exe file, and its calling "CEAppMgr.exe"
How can I force SQLCE to install into its default directory without asking user?
Thanks!
I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.
I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?
Thanks,
Nanci
Is there a way to change the default data type in SQL Server? If I import a database into SQL Server it makes every non-numeric field a nvarchar field. Is there a way to change this so that these fields would be varchar or char by default? I cannot find any way to redo these as a batch/group update and have to modify every single field manually. Any insights greatly appreciated.
View 4 Replies View RelatedHi,
I need to populate some SqlParameter but I temporarily need to populate with a default value for the data type in question. Of course I can work them out, but I was wondering if there is already a way of doing this, from the .Net Classes or from SQL Server but without making a trip to the DB.
Thanks
John
Hi everyone,
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies View Related@pvColumnName VARCHAR(100) = Default,
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
Hi,
I am searching for how getting the data directory where default mdf files are based.
(C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData)
My goal is to deploy mdf file in this folder during installation.
Thanks
When sql 2005 is created a cert is also created in the Data directory of that instance. What is that cert? Is it the Service Master Key? I noticed that I can movecopydelete it while sql server is running so it doesn't appear to be very secure..
TIA,
Barkingdog
I am currently deploying a desktop application that uses SQL Express as a local data source. I load data from other sources into the local database and then use that data for my application. Currently I'm deploying the MDF file into a Data folder within my application folder. I'm finding that most of the users that will be using this application has only power user rights, not admin rights.
My question is this. Is using a data folder within my application folder the ideal place to store this data or is there other more benefitial places within the users computer that would help reduce rights and permissions issues? When users upgrade to Vista will this cause any problems that another location might help me avoid?
Appreaciate any opinions.
Hi,
Is there anyway to load all the raw data in one directory into the database? l've several files with same format but different filename in one folder which l do not know their filename before hand.
Is SSIS For Each Loop can help me to accomplish the file loading? If so, where can l find the example?
Thanks you.
Regards,
Yong Boon, Lim
Hi,
Active Directory stores Information regarding Users, Groups & Policies etc.
Is it possible to export Complete User Information from active directory to text file?
If yes, please provide the steps. Thanking you.
Is there an Admin script that yields the following directories?
Data
Data Root
Log
TempDB
Shared Feature
Backup
Currently looping through the set of flat files like CHK0604, CHK0611, CHK0618, and CHK0625 from the source folder C:SOURCE
OBJECTIVE within the flat file if any records/rows cause error i have to move the bad data into separate folder C:ERROR
STEPS TAKEN
1) In FOREACH LOOP component i specified the variable User:: sourceFilePath for my source file CHK0604 etc. location C:SOURCE. The loop walkthrough each file in C:SOURCE and if no error then moves the flat file into another folder C:ARCHIVED. This task is perfectly working.
2) Within the dataflow I am diverting the the bad rows from "conditional component" into "Flat File Destination" Component.
3) "Flat File Destination" Connection manager i set the expressions as @[User:: sourceFilePath] +"_Error.TXT".
ISSUE
Because of point (3) the error file is created in the SOURCE flat file location C:SOURCE.
QUESTION
1) My error file name should be CHK0604_Error, CHK0611_Error, CHK0618_Error, CHK0625_Error created in another folder C:ERROR.
2) How to move the bad data into another directory while looping through a set of FLAT FILES ?
3) If i have to create another variable like @[User:: ErrorFilePath] where to create ? How to use the source file title as the title of error file.?
Thanks for the help
Hello,
I am fairly new to SQL 2005 and Reporting Services.
We are trying to create a report that will display sales data based on group membership from Active Directory.
For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.
Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.
Please let me know if anyone has a good way to tackle this.
Thanks,
Justin
Hello,
I have recently upgraded my the server that runs SQL Server to an Active Directory Domain Controler. Now I can't connect to the SQL Server from ASP.NET Applications when the application is not located on the local machine. The error message I get is SQL Server does not exist or access is denied.
I have no problems connecting with QueryAnalyer and Enterprise Manager from my workstation. I have added the Sql Server to the directory via the "Active Directory"-tab in the Property window for my Sql Server Registration i Enterprise Manager.
If I copy a directory from the wwwroot on my workstation to the server the application has no problem to connect so the connectionstring seams to work fine.
Any ideas?
Regards,
Kalle
I have a question regarding BCP. We are doing a BCP operation from one machine to another machine. (i.e) Flat file from the web server are BCP on to the DB server. For this operation we have mounted the FTP directory of the web server on to the DB server. Say G: drive of the DB server is mapped to the Ftp directory of the web server.
Now when we run the BCP we get the following error.
SQLState = 08001, NativeError = 6
Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Create File()).
The connection is using NAMED PIPES.
Say when we BCP out the above error message comes but the flat file is generated with out any records in it.
Are we missing any parameter settings.
Please help.