Is it possible to relocate the data and/or the log files after they have bee
n
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have b
een
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts
Friday, March 30, 2012
MDF and LDF file locations
Is it possible to relocate the data and/or the log files after they have been
assigned a location? Thanks everyone for your help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA
|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.
|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
assigned a location? Thanks everyone for your help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA
|||yes,
1. detach and attach
2. backup and restore
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.
|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
MDF and LDF file locations
Is it possible to relocate the data and/or the log files after they have been
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.sql
assigned a location? Thanks everyone for your help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.
There are several ways of doing this.
One method is to do a backup and restore.
Look at the MOVE TO option in RESTORE in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||yes,
1. detach and attach
2. backup and restore
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"coenzyme" wrote:
> Is it possible to relocate the data and/or the log files after they have been
> assigned a location? Thanks everyone for your help.|||You could run sp_detach_db, move the files to the new location, and then
run sp_attach_db. Another method is to backup and then restore.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:6827AE2A-BCDD-49BC-B98E-4B14F414D465@.microsoft.com...
> Is it possible to relocate the data and/or the log files after they have
been
> assigned a location? Thanks everyone for your help.sql
mdf and ldf file extensions
Hello All,
One of my customers created a database in SQL 2K SP3a. He gave the data
file LDF extension and log file MDF extension.
When I do sp_helpdb 'dbname', the extensions are pointing to the correct
files, meaning LDF is pointing to the data file and MDF is pointing to the
log file just as he designed.
My question is: is it just a standard suggested by Microsoft to name the
data file with MDF extension and log file with LDF and NDF extensions? Is
there going to be any repercussions later on if we name the files with wrong
extensions.
I though it was odd that SQL Server let the files be named with wrong
extensions in the first place.
Any ideas?
Thanks,
BivaThis will not confuse SQL Server. SQL Server doesn't rely
on the file name extension to determine the type of the
file or how it deals with the file. But it may be
confusing to a DBA, and wouldn't be considered best
practice from a naming convention standpoint.
If you don't like the current naming, you can detach the
database, rename the files to the conventional extensions,
and then attach the database using the renamed files.
Linchi
>--Original Message--
>Hello All,
>One of my customers created a database in SQL 2K SP3a.
He gave the data
>file LDF extension and log file MDF extension.
>When I do sp_helpdb 'dbname', the extensions are pointing
to the correct
>files, meaning LDF is pointing to the data file and MDF
is pointing to the
>log file just as he designed.
>My question is: is it just a standard suggested by
Microsoft to name the
>data file with MDF extension and log file with LDF and
NDF extensions? Is
>there going to be any repercussions later on if we name
the files with wrong
>extensions.
>I though it was odd that SQL Server let the files be
named with wrong
>extensions in the first place.
>Any ideas?
>Thanks,
>Biva
>
>.
>|||> My question is: is it just a standard suggested by Microsoft to name the
> data file with MDF extension and log file with LDF and NDF extensions?
The pedantic part of me is tugging to correct this. By default, at least.
Data files have MDF (and NDF extensions, in the case of multiple files).
Log files have LDF extensions (whether singular or multiple).
You can certainly override this behavior. I'm not certain that there are
any advantages of doing so, and I can certainly think of some drawbacks.
But there it is.
--
Aaron Bertrand
SQL Server MVP|||Hi Biva,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
You concern is if the MDF, NDF, LDF is just a suggested standard by
Microsoft and if there problem when change the extension to these files,
right?
When create a database, you should set a filename of the data, log, etc.
These fine name and extension should be specified (they are 'os_file_name'
when you refer it to 'create database' in BOL, SQL Server Books Online).
Although 'os_file_name' can be any valid operating system file name, the
name more clearly reflects the purpose of the file if you use the following
recommended extensions.
You can create a database like this:
USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\saledat.mdfx',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\salelog.ldfx',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
As we know, the system table 'master' will control the user databases and
operation of SQL Server as a whole by keeping track of all information of
them. When you use:
Use master
Go
Select * from sysdatabases
You will get the fileinformation of sales with the extension you defined,
that is
'c:\program files\microsoft sql server\mssql\data\saledat.mdfx'
and you can create table or execute other operations on it.
But if you revise the extension of the file and thenuse the database again,
the SQL Server will notify you that the file is not exist although there
may be someway de recover. It is strongly suggested you to keep the
suggested file extension by Microsoft.
Hope this answered your questions. If you still have more concern about it,
please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
One of my customers created a database in SQL 2K SP3a. He gave the data
file LDF extension and log file MDF extension.
When I do sp_helpdb 'dbname', the extensions are pointing to the correct
files, meaning LDF is pointing to the data file and MDF is pointing to the
log file just as he designed.
My question is: is it just a standard suggested by Microsoft to name the
data file with MDF extension and log file with LDF and NDF extensions? Is
there going to be any repercussions later on if we name the files with wrong
extensions.
I though it was odd that SQL Server let the files be named with wrong
extensions in the first place.
Any ideas?
Thanks,
BivaThis will not confuse SQL Server. SQL Server doesn't rely
on the file name extension to determine the type of the
file or how it deals with the file. But it may be
confusing to a DBA, and wouldn't be considered best
practice from a naming convention standpoint.
If you don't like the current naming, you can detach the
database, rename the files to the conventional extensions,
and then attach the database using the renamed files.
Linchi
>--Original Message--
>Hello All,
>One of my customers created a database in SQL 2K SP3a.
He gave the data
>file LDF extension and log file MDF extension.
>When I do sp_helpdb 'dbname', the extensions are pointing
to the correct
>files, meaning LDF is pointing to the data file and MDF
is pointing to the
>log file just as he designed.
>My question is: is it just a standard suggested by
Microsoft to name the
>data file with MDF extension and log file with LDF and
NDF extensions? Is
>there going to be any repercussions later on if we name
the files with wrong
>extensions.
>I though it was odd that SQL Server let the files be
named with wrong
>extensions in the first place.
>Any ideas?
>Thanks,
>Biva
>
>.
>|||> My question is: is it just a standard suggested by Microsoft to name the
> data file with MDF extension and log file with LDF and NDF extensions?
The pedantic part of me is tugging to correct this. By default, at least.
Data files have MDF (and NDF extensions, in the case of multiple files).
Log files have LDF extensions (whether singular or multiple).
You can certainly override this behavior. I'm not certain that there are
any advantages of doing so, and I can certainly think of some drawbacks.
But there it is.
--
Aaron Bertrand
SQL Server MVP|||Hi Biva,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
You concern is if the MDF, NDF, LDF is just a suggested standard by
Microsoft and if there problem when change the extension to these files,
right?
When create a database, you should set a filename of the data, log, etc.
These fine name and extension should be specified (they are 'os_file_name'
when you refer it to 'create database' in BOL, SQL Server Books Online).
Although 'os_file_name' can be any valid operating system file name, the
name more clearly reflects the purpose of the file if you use the following
recommended extensions.
You can create a database like this:
USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\saledat.mdfx',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\salelog.ldfx',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
As we know, the system table 'master' will control the user databases and
operation of SQL Server as a whole by keeping track of all information of
them. When you use:
Use master
Go
Select * from sysdatabases
You will get the fileinformation of sales with the extension you defined,
that is
'c:\program files\microsoft sql server\mssql\data\saledat.mdfx'
and you can create table or execute other operations on it.
But if you revise the extension of the file and thenuse the database again,
the SQL Server will notify you that the file is not exist although there
may be someway de recover. It is strongly suggested you to keep the
suggested file extension by Microsoft.
Hope this answered your questions. If you still have more concern about it,
please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
MDF and LDF at the same drive
Dear All,
My Database Server is using RAID 5.
I put the mdf and ldf file of my database at the same drive.
Does it affect the SQL Server Performance?
Thanks
Robert Lie
The best thing would be not to use Raid 5 due to performance.
For best overall performance, locate the database files (.mdf) and log files
(.ldf) on separate arrays in your server to isolate potentially conflicting
reads and writes. [6.5, 7.0, 2000] Updated 12-20-2004
*****
To store your database files (.mdf), the best performance is gained by
storing them using RAID 10 arrays. If this is too expensive, then RAID 5 is
the next best bet. Each RAID array (5 or 10) should have as many physical
disks in the array as the controller will support. This allows reads and
writes to be performed simultaneously on each physical drive in the array,
significantly boosting disk I/O. [6.5, 7.0, 2000] Updated 12-20-2004
From: http://www.sql-server-performance.co...rver_setup.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
|||Hi Robert,
yes but it only becomes a problem in very large databases, the ldf (log
file) should be on a drive of its own in a perfect world and using RAID5 for
it is overkill and will slow it's performance as the log file is written to
sequentially and most other things including the mdf file are written to
randomly. Again this is only a concirn is big installations and if you have
a spare physical drive then the ldf file could be put on that.
Regards
Alex White MCDBA MCSE
http://www.intralan.co.uk
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
|||Hi,
It is always recommended to put Data files and Log files in multiple drives,
it will be even better if you put the files in drives belongs to different
disk controllers.
This will help you to reduce the disk I/O and eventually the better
performance.
But incase if your volume pd transaction is less then you may not have any
I/O issues, inthis case you can keep both the files in same drives which
belongs to same disk controller. And monitor the Disk Queue length , all
hardware resorce usage using Perfmon.
If you find every thing is normal then you are good. See the website
http://www.sql-server-performance.com/ for more performanace monitoring and
tuning options.
Thanks
Hari
SQL server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
My Database Server is using RAID 5.
I put the mdf and ldf file of my database at the same drive.
Does it affect the SQL Server Performance?
Thanks
Robert Lie
The best thing would be not to use Raid 5 due to performance.
For best overall performance, locate the database files (.mdf) and log files
(.ldf) on separate arrays in your server to isolate potentially conflicting
reads and writes. [6.5, 7.0, 2000] Updated 12-20-2004
*****
To store your database files (.mdf), the best performance is gained by
storing them using RAID 10 arrays. If this is too expensive, then RAID 5 is
the next best bet. Each RAID array (5 or 10) should have as many physical
disks in the array as the controller will support. This allows reads and
writes to be performed simultaneously on each physical drive in the array,
significantly boosting disk I/O. [6.5, 7.0, 2000] Updated 12-20-2004
From: http://www.sql-server-performance.co...rver_setup.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
|||Hi Robert,
yes but it only becomes a problem in very large databases, the ldf (log
file) should be on a drive of its own in a perfect world and using RAID5 for
it is overkill and will slow it's performance as the log file is written to
sequentially and most other things including the mdf file are written to
randomly. Again this is only a concirn is big installations and if you have
a spare physical drive then the ldf file could be put on that.
Regards
Alex White MCDBA MCSE
http://www.intralan.co.uk
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
|||Hi,
It is always recommended to put Data files and Log files in multiple drives,
it will be even better if you put the files in drives belongs to different
disk controllers.
This will help you to reduce the disk I/O and eventually the better
performance.
But incase if your volume pd transaction is less then you may not have any
I/O issues, inthis case you can keep both the files in same drives which
belongs to same disk controller. And monitor the Disk Queue length , all
hardware resorce usage using Perfmon.
If you find every thing is normal then you are good. See the website
http://www.sql-server-performance.com/ for more performanace monitoring and
tuning options.
Thanks
Hari
SQL server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
MDF and LDF at the same drive
Dear All,
My Database Server is using RAID 5.
I put the mdf and ldf file of my database at the same drive.
Does it affect the SQL Server Performance?
Thanks
Robert LieThe best thing would be not to use Raid 5 due to performance.
For best overall performance, locate the database files (.mdf) and log files
(.ldf) on separate arrays in your server to isolate potentially conflicting
reads and writes. [6.5, 7.0, 2000] Updated 12-20-2004
*****
To store your database files (.mdf), the best performance is gained by
storing them using RAID 10 arrays. If this is too expensive, then RAID 5 is
the next best bet. Each RAID array (5 or 10) should have as many physical
disks in the array as the controller will support. This allows reads and
writes to be performed simultaneously on each physical drive in the array,
significantly boosting disk I/O. [6.5, 7.0, 2000] Updated 12-20-2004
From: http://www.sql-server-performance.com/sql_server_setup.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie|||Hi Robert,
yes but it only becomes a problem in very large databases, the ldf (log
file) should be on a drive of its own in a perfect world and using RAID5 for
it is overkill and will slow it's performance as the log file is written to
sequentially and most other things including the mdf file are written to
randomly. Again this is only a concirn is big installations and if you have
a spare physical drive then the ldf file could be put on that.
--
Regards
Alex White MCDBA MCSE
http://www.intralan.co.uk
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie|||Hi,
It is always recommended to put Data files and Log files in multiple drives,
it will be even better if you put the files in drives belongs to different
disk controllers.
This will help you to reduce the disk I/O and eventually the better
performance.
But incase if your volume pd transaction is less then you may not have any
I/O issues, inthis case you can keep both the files in same drives which
belongs to same disk controller. And monitor the Disk Queue length , all
hardware resorce usage using Perfmon.
If you find every thing is normal then you are good. See the website
http://www.sql-server-performance.com/ for more performanace monitoring and
tuning options.
Thanks
Hari
SQL server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
My Database Server is using RAID 5.
I put the mdf and ldf file of my database at the same drive.
Does it affect the SQL Server Performance?
Thanks
Robert LieThe best thing would be not to use Raid 5 due to performance.
For best overall performance, locate the database files (.mdf) and log files
(.ldf) on separate arrays in your server to isolate potentially conflicting
reads and writes. [6.5, 7.0, 2000] Updated 12-20-2004
*****
To store your database files (.mdf), the best performance is gained by
storing them using RAID 10 arrays. If this is too expensive, then RAID 5 is
the next best bet. Each RAID array (5 or 10) should have as many physical
disks in the array as the controller will support. This allows reads and
writes to be performed simultaneously on each physical drive in the array,
significantly boosting disk I/O. [6.5, 7.0, 2000] Updated 12-20-2004
From: http://www.sql-server-performance.com/sql_server_setup.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie|||Hi Robert,
yes but it only becomes a problem in very large databases, the ldf (log
file) should be on a drive of its own in a perfect world and using RAID5 for
it is overkill and will slow it's performance as the log file is written to
sequentially and most other things including the mdf file are written to
randomly. Again this is only a concirn is big installations and if you have
a spare physical drive then the ldf file could be put on that.
--
Regards
Alex White MCDBA MCSE
http://www.intralan.co.uk
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie|||Hi,
It is always recommended to put Data files and Log files in multiple drives,
it will be even better if you put the files in drives belongs to different
disk controllers.
This will help you to reduce the disk I/O and eventually the better
performance.
But incase if your volume pd transaction is less then you may not have any
I/O issues, inthis case you can keep both the files in same drives which
belongs to same disk controller. And monitor the Disk Queue length , all
hardware resorce usage using Perfmon.
If you find every thing is normal then you are good. See the website
http://www.sql-server-performance.com/ for more performanace monitoring and
tuning options.
Thanks
Hari
SQL server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23xPXnqWSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> My Database Server is using RAID 5.
> I put the mdf and ldf file of my database at the same drive.
> Does it affect the SQL Server Performance?
> Thanks
> Robert Lie
MDF / LDF
Hello,
I recently deleted the directory
C:\Program Files\Microsoft SQL Server\MSSQL\Data
On my production database server.
I managed using undelete software to get back most of the LDF/MDF files
and reattached them.
But there are a few that simply do not reattach.
Ive tried using
sp_attach_db
sp_attach_single_db
But to no avail.
I realise that the databases were not unattached properly.
Is there a way to get the databases back into Enterprise Manager?
Without starting again or using an expensive product like
officerecovery.com SQL to recover?
I appreciate anybodies help on this.
GurdipYou didn't mention backups. Does that mean you don't have any backups?
What does "do not reattach" mean? Do you get an error message? It might
be possible to advise you better if we knew that and the exact
sp_attach statement you used.
Before you do anything at all please, please take a backup!
--
David Portas
SQL Server MVP
--|||(gurdipv@.gmail.com) writes:
> I recently deleted the directory
> C:\Program Files\Microsoft SQL Server\MSSQL\Data
> On my production database server.
> I managed using undelete software to get back most of the LDF/MDF files
> and reattached them.
> But there are a few that simply do not reattach.
> Ive tried using
> sp_attach_db
> sp_attach_single_db
> But to no avail.
> I realise that the databases were not unattached properly.
> Is there a way to get the databases back into Enterprise Manager?
> Without starting again or using an expensive product like
> officerecovery.com SQL to recover?
If the error message are what I fear, the answer is: yes, you may be able
to get the databases back, but those ways are dangerous and can lead to
severe corruption, and requires complete understanding of what you are
up to. If those databases are dear to you, you should open a case with
Microsoft support.
But start with posting the error message, so we can verify that the problem
is not a more trivial one.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
mdf & ldf
What do MDF and LDF stand for?
Thank you.
MDF is the standard data file extension for SQL Server
LDF is the standard log file extension for SQL Server.
Thank you.
MDF is the standard data file extension for SQL Server
LDF is the standard log file extension for SQL Server.
Google is your friend.
You may also want to check out the "Getting started" forum for SQL Server.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=158&SiteID=1|||Perhaps, I should re-phrase my question.
What do letters M-D-F and L-D-F stand for?
|||
foobar101 wrote:
Perhaps, I should re-phrase my question. What do letters M-D-F and L-D-F stand for?
I'm not positive on the "M", but:
M = Master?
D = Database
F = File
L = Log
Try Google though.|||So I thought, but wasn't sure. I figured I'd ask the luminaries that know the BOL better than I.
|||
Perhaps, I should re-phrase my question. What do letters M-D-F and L-D-F stand for?
Main data file
Log data file
Some people give data files the .ndf suffix. Point is, it really doesn't matter what they're called therefore its not really important what those suffixes stand for either.
-Jamie
|||NDF - Not the master data file. A convention used for additional files other than the master of course.
Similarly MDF has always been master data file to me. Not that it matters as it is not a required convention, you can use no extension, or anything you like.
sqlMDF - Basic Question
Is it possible to 'query' an mdf file using MS Jet Provider or
somethnig like jet provider without having to 'attach' the mdf as a
database on SqlServer. Thanks.No. You must have a SQL Server engine to connect to it. You could install
MSDE (SQL Server 2000) or SQL Server Express (SQL Server 2005) and use it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1148033398.353253.150440@.i40g2000cwc.googlegroups.com...
Is it possible to 'query' an mdf file using MS Jet Provider or
somethnig like jet provider without having to 'attach' the mdf as a
database on SqlServer. Thanks.
somethnig like jet provider without having to 'attach' the mdf as a
database on SqlServer. Thanks.No. You must have a SQL Server engine to connect to it. You could install
MSDE (SQL Server 2000) or SQL Server Express (SQL Server 2005) and use it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1148033398.353253.150440@.i40g2000cwc.googlegroups.com...
Is it possible to 'query' an mdf file using MS Jet Provider or
somethnig like jet provider without having to 'attach' the mdf as a
database on SqlServer. Thanks.
MDF
I have a system in Vb using SQL 2000.
happens that MDF has + - 400 mdf mb. And LDF increases reaching 2 Gb in 1
w
a lot!
Does have as creating a routine for everyday to reduce the space of this
file?
Manually, I make the backup log <name table> with truncate only.. and later
I make the shrink database!
An option exists in Enterprise Manager of <Database Maintenance Plans> where
configures the maintenances (backup, shrink, etc)... I Made the tests and it
didn't reduce the size! IS THIS THE CORRECT OPTION FOR THIS?Hi
You can create a maintenance plan to do this, or alternatively craft your
own ot use sqlmaint without an existing plan.
If you log file is not shrinking then check out:
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:uinlXfLLFHA.3064@.TK2MSFTNGP12.phx.gbl...
>I have a system in Vb using SQL 2000.
> happens that MDF has + - 400 mdf mb. And LDF increases reaching 2 Gb in 1
> w a lot!
> Does have as creating a routine for everyday to reduce the space of this
> file?
> Manually, I make the backup log <name table> with truncate only.. and
> later
> I make the shrink database!
> An option exists in Enterprise Manager of <Database Maintenance Plans>
> where
> configures the maintenances (backup, shrink, etc)... I Made the tests and
> it
> didn't reduce the size! IS THIS THE CORRECT OPTION FOR THIS?
>
happens that MDF has + - 400 mdf mb. And LDF increases reaching 2 Gb in 1
w
a lot!Does have as creating a routine for everyday to reduce the space of this
file?
Manually, I make the backup log <name table> with truncate only.. and later
I make the shrink database!
An option exists in Enterprise Manager of <Database Maintenance Plans> where
configures the maintenances (backup, shrink, etc)... I Made the tests and it
didn't reduce the size! IS THIS THE CORRECT OPTION FOR THIS?Hi
You can create a maintenance plan to do this, or alternatively craft your
own ot use sqlmaint without an existing plan.
If you log file is not shrinking then check out:
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:uinlXfLLFHA.3064@.TK2MSFTNGP12.phx.gbl...
>I have a system in Vb using SQL 2000.
> happens that MDF has + - 400 mdf mb. And LDF increases reaching 2 Gb in 1
> w
a lot!> Does have as creating a routine for everyday to reduce the space of this
> file?
> Manually, I make the backup log <name table> with truncate only.. and
> later
> I make the shrink database!
> An option exists in Enterprise Manager of <Database Maintenance Plans>
> where
> configures the maintenances (backup, shrink, etc)... I Made the tests and
> it
> didn't reduce the size! IS THIS THE CORRECT OPTION FOR THIS?
>
MDB via Linked Server
I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!
Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!
Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>
MDB via Linked Server
I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>
MDB via Linked Server
I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online.
Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you a
re using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you n
eed to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we
have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WA
RNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>sql
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online.
Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you a
re using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you n
eed to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we
have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WA
RNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>sql
MDB to SDF
Hello, everyone.
I am a new developer of .NET CF. Currently, I have a project that needs transferring tables from a existing Access database to SQL Server 2005 Mobile database. Does anybody know how to do it?
Thanks in advance.
Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.
However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Thanks for your help.
It looks like that I have to develop a tool myself.
We are really sorry that we could not help you much in this regard. But we would surely take this as feedback and work on this seriously.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Raise an issue (as a suggestion) in the public Feedback Database if you'd like such a tool to be included in a future SQL Server release:
http://lab.msdn.microsoft.com/productfeedback/
|||Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.
However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.
Is there any walkthru that I can perview on exactly how to do that?
MDB to SDF
Hello, everyone.
I am a new developer of .NET CF. Currently, I have a project that needs transferring tables from a existing Access database to SQL Server 2005 Mobile database. Does anybody know how to do it?
Thanks in advance.
Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.
However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Thanks for your help.
It looks like that I have to develop a tool myself.
We are really sorry that we could not help you much in this regard. But we would surely take this as feedback and work on this seriously.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Raise an issue (as a suggestion) in the public Feedback Database if you'd like such a tool to be included in a future SQL Server release:
http://lab.msdn.microsoft.com/productfeedback/
|||Are
you talking about MS Access Database or MS Pocket Access Database. In
any case, we dont have any tools for doing that automatically.
However, you can always open two cursors (Datasets) against two DBs
and transfer the data one row by row. I know its a slow operation but
it is all design time.
Is there any walkthru that I can perview on exactly how to do that?
MDB to SDF
My company has purchased the SQL 2005 Server Enterprise and I have imported the Access database to the SQL Server.
I have built a SQL 2005 mobile database (a sdf file) within VS.net 2005. Can any one tell me how to load it to the SQL server management studio and transfer tables from SQL Server Enterprise database to the SQL 2005 Server Mobile database?
By the way, I could not fine the “SQLMobile.sql” file mentioned in the article named “Step by step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005”, which I downloaded from Microsoft website. Anyone knows?
Thanks in advance.
There is a MSI in that white paper which you need to download to get SQLMobile.sql.
You can use SQL Server Integration Services to transfer the data between different data bases.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Laxmi NRO MSFT ,
Thank you very much for your help.
I downloaded the MSI and finished the "Step by step" exercise. It is very helpful and I have the confidence to finish my project now.
|||
Thanks alot and pleasee feel free to post your comments on the paper so that we can keep updating it for more and more usefulness.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Hi,
I want to import tables and data in them from an access database(.mdb) to .sdf file (i.e SQL 2005 compact Edition).
Could you please guide me the steps to do this.
Thanks and regards
G Sreenaiah <gsreenaiah@.msn.com>
MDB to SDF
My company has purchased the SQL 2005 Server Enterprise and I have imported the Access database to the SQL Server.
I have built a SQL 2005 mobile database (a sdf file) within VS.net 2005. Can any one tell me how to load it to the SQL server management studio and transfer tables from SQL Server Enterprise database to the SQL 2005 Server Mobile database?
By the way, I could not fine the “SQLMobile.sql” file mentioned in the article named “Step by step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005”, which I downloaded from Microsoft website. Anyone knows?
Thanks in advance.
There is a MSI in that white paper which you need to download to get SQLMobile.sql.
You can use SQL Server Integration Services to transfer the data between different data bases.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Laxmi NRO MSFT ,
Thank you very much for your help.
I downloaded the MSI and finished the "Step by step" exercise. It is very helpful and I have the confidence to finish my project now.
|||
Thanks alot and pleasee feel free to post your comments on the paper so that we can keep updating it for more and more usefulness.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Hi,
I want to import tables and data in them from an access database(.mdb) to .sdf file (i.e SQL 2005 compact Edition).
Could you please guide me the steps to do this.
Thanks and regards
G Sreenaiah <gsreenaiah@.msn.com>
mdb query to adp function conversion failed
Good evening everybody! This is my first post here, necessary because I have recently upgraded from Access *.MDB to Access *.ADP :)
I have a problem with a query that does not appear to have converted correctly. Below is the SQL of the *.mdb and the *.adp. I would be very grateful if somebody could indicate the correct format for the ADP code. I cant find an example in the help file :(
Mdb Query SQL ==========================
SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=[Forms]![frmMainQuote]![QuoteLoadSize]));
Adp Function SQL =========================
ALTER FUNCTION qryGetTonne1 (@.Forms___frmMainQuote___CboLorr varchar (255)
)
RETURNS TABLE
AS RETURN (SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=@.Forms___frmMainQuote_ __CboLorr)))
Any comment gratefully receivedUp-sizing problems
I still havent found the correct syntax for a query in an adp file. However I did find two more upsizing problems for which I have found a cure, these can be seen in the attached Access 2000 *.mdb file. Run it in mdb then run the upsize wizard and note the problems.
Change True to 1 and False to 0
The first upsizing problem I had was that in an *.mdb file you can use True or False in an SQL statement. However when you upsize you need to change the True to 1, and the False to 0. It might pay, when you are designing your database to think about using 1 and 0 to indicate true or false situations.
Using forms collection can cause a problem
The other problem related nicely to the query problem mentioned above. I found that some code in my form frmSeeTown did not work because the SQL contained a reference to a combo box via the forms collection in the form of:--
[Forms]![frmSeeTown]![cboSelectCounty]
This relates to my query problem because the query with the problem also contained a reference to a combo box through the forms collection. Therefore I am reasonably sure that you cannot reference a combo box this way in an SQL string in an Access Project file. However Im right at the bottom of the learning curve so please educate me.
Now down to the problem!
If you look at the attached *.mdb file you will see that there are three functions behind the form frmSeeTown they are called:-
fBuildStr1() Assigns data from the combo box with me.cboSelectCounty
fBuildStr2() Assigns data using the forms collection (Dont work in *.asp)
fBuildStr3() Assigns data from a variable.
To view the effect of the different methods change the called function name from fBuildStr3 to fBuildStr2
The following NOTES have been extracted from the form frmSeeTown module
'Both of these functions "fBuildStr1" and "fBuildStr2" work OK in an mdb, but "fBuildStr2" won't work in an adp.
'So there are 4 possible options ------
'1) It maybe I'm using the wrong syntax? -- can't find anything in the help? So I think it's something else.
'2) Reference the combo box control with the Me. function. -- Doing it this way could be a problem with sub-forms
'3) Assign the combo box value to variable and use that in the SQL -- this has got to be the way to do it :)
'4) Something else, embarrassingly simple that I don't know about yet :) Hell that's why I'm posting...
'Since writing the above, I have now added a further function fBuildStr3 which assigns the combo box value to a variable and then uses that variable in the SQL code. This appears to work OK.. :||||Found this just now!!!
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=AxxessXP&Number=137340&Forum=AxxessXP&Words=access%20project&Match=Entire%20Phrase&Searchpage=0&Limit=25&Old=allposts&Main=137340&Search=true#Post137340
So I'm outa the woods at last (Famous last words) :) :)
Whos a happy bunny then!!!|||After several days of head scratching, Ive discovered that my original suspicion that there was a problem with the syntax was correct. If you look at the two lines of code below taken from an SQL statement both line 1) and line 2) work in Access mdb format, but only line 2) works in Access adb format.
1) "WHERE (((tlkpUkTowns.TownOnlyCounty) = [Forms]![frmSeeTown]![cboSelectCounty] )) ORDER BY tlkpTowns.TownTown"
2) "WHERE (((tlkpUkTowns.TownOnlyCounty) = " & Forms![frmSeeTown].cboSelectCounty & ")) ORDER BY tlkpTowns.TownTown"
As you can see the SQL in line one is incorrectly formatted. It appears that the SQL engine for an mdb file is more forgiving, and corrects minor mistakes in the SQL string automatically. So I am inferring from this that an adp file uses a different engine to handle the SQL statements. This would make sense, as there are differences in the SQL between the two access formats. However I say again I am very low down on the learning curve and would welcome any input.sql
I have a problem with a query that does not appear to have converted correctly. Below is the SQL of the *.mdb and the *.adp. I would be very grateful if somebody could indicate the correct format for the ADP code. I cant find an example in the help file :(
Mdb Query SQL ==========================
SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=[Forms]![frmMainQuote]![QuoteLoadSize]));
Adp Function SQL =========================
ALTER FUNCTION qryGetTonne1 (@.Forms___frmMainQuote___CboLorr varchar (255)
)
RETURNS TABLE
AS RETURN (SELECT tlkpLoadSize.LoadSizeID, tlkpLoadSize.LoadSizeDisc, tlkpLoadSize.LoadSizeMinLoad, tlkpLoadSize.LoadSizeMaxLoad
FROM tlkpLoadSize
WHERE (((tlkpLoadSize.LoadSizeID)=@.Forms___frmMainQuote_ __CboLorr)))
Any comment gratefully receivedUp-sizing problems
I still havent found the correct syntax for a query in an adp file. However I did find two more upsizing problems for which I have found a cure, these can be seen in the attached Access 2000 *.mdb file. Run it in mdb then run the upsize wizard and note the problems.
Change True to 1 and False to 0
The first upsizing problem I had was that in an *.mdb file you can use True or False in an SQL statement. However when you upsize you need to change the True to 1, and the False to 0. It might pay, when you are designing your database to think about using 1 and 0 to indicate true or false situations.
Using forms collection can cause a problem
The other problem related nicely to the query problem mentioned above. I found that some code in my form frmSeeTown did not work because the SQL contained a reference to a combo box via the forms collection in the form of:--
[Forms]![frmSeeTown]![cboSelectCounty]
This relates to my query problem because the query with the problem also contained a reference to a combo box through the forms collection. Therefore I am reasonably sure that you cannot reference a combo box this way in an SQL string in an Access Project file. However Im right at the bottom of the learning curve so please educate me.
Now down to the problem!
If you look at the attached *.mdb file you will see that there are three functions behind the form frmSeeTown they are called:-
fBuildStr1() Assigns data from the combo box with me.cboSelectCounty
fBuildStr2() Assigns data using the forms collection (Dont work in *.asp)
fBuildStr3() Assigns data from a variable.
To view the effect of the different methods change the called function name from fBuildStr3 to fBuildStr2
The following NOTES have been extracted from the form frmSeeTown module
'Both of these functions "fBuildStr1" and "fBuildStr2" work OK in an mdb, but "fBuildStr2" won't work in an adp.
'So there are 4 possible options ------
'1) It maybe I'm using the wrong syntax? -- can't find anything in the help? So I think it's something else.
'2) Reference the combo box control with the Me. function. -- Doing it this way could be a problem with sub-forms
'3) Assign the combo box value to variable and use that in the SQL -- this has got to be the way to do it :)
'4) Something else, embarrassingly simple that I don't know about yet :) Hell that's why I'm posting...
'Since writing the above, I have now added a further function fBuildStr3 which assigns the combo box value to a variable and then uses that variable in the SQL code. This appears to work OK.. :||||Found this just now!!!
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=AxxessXP&Number=137340&Forum=AxxessXP&Words=access%20project&Match=Entire%20Phrase&Searchpage=0&Limit=25&Old=allposts&Main=137340&Search=true#Post137340
So I'm outa the woods at last (Famous last words) :) :)
Whos a happy bunny then!!!|||After several days of head scratching, Ive discovered that my original suspicion that there was a problem with the syntax was correct. If you look at the two lines of code below taken from an SQL statement both line 1) and line 2) work in Access mdb format, but only line 2) works in Access adb format.
1) "WHERE (((tlkpUkTowns.TownOnlyCounty) = [Forms]![frmSeeTown]![cboSelectCounty] )) ORDER BY tlkpTowns.TownTown"
2) "WHERE (((tlkpUkTowns.TownOnlyCounty) = " & Forms![frmSeeTown].cboSelectCounty & ")) ORDER BY tlkpTowns.TownTown"
As you can see the SQL in line one is incorrectly formatted. It appears that the SQL engine for an mdb file is more forgiving, and corrects minor mistakes in the SQL string automatically. So I am inferring from this that an adp file uses a different engine to handle the SQL statements. This would make sense, as there are differences in the SQL between the two access formats. However I say again I am very low down on the learning curve and would welcome any input.sql
mdb file support UTF8
i create mdb file in VB.NET project in vs2005 . i want that it support UTF8 for arabic language.
thanks .
There are two ways to do that you can create typed dataset with UTF8 or use some configuration provided by Microsoft, so there is no standard way to do it. Hope this helps.
http://msdn2.microsoft.com/en-us/library/aa662944(office.11).aspx
http://office.microsoft.com/en-us/ork2003/HA011402281033.aspx
MDAC2.71 Service Pack 1 Refresh: mdac_typ.exe ??
Hi all,
I run MDAC Component Check on my SQL Server 2000. None
of the versions of MDAC passed the tests. The closest one
is 2.7 SP1 (2.71.9030.9) with only One error on
msjtes40.dll file
msjtes40.dll,Error,Field 'FileVersion' mismatch:
Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
Expression
Does this mean that I have to install 2.71 Service
Pack 1 Refresh: mdac_typ.exe ?
What files contain in this mdac_typ.exe? Would
installing this file help on Memory Leak?
Thank you sooooo much!!!
P.S.: I think I install 2.7 SP1 via Windows Update. I
didn't apply SQL Sever 2000 SP3 at all.
AnnieThat file almost always checks different, so don't worry.
normally if the version number is higher compared to the closest match it is
OK.
This normally happens because you installed some software with an newer DLL.
( I am assuming msjtes40.dll is installed by MS Office, since I always see
it being different at work.)
Tim S
"Annie" <piyomeme@.yahoo.com> wrote in message
news:05da01c34d8b$f0e63f00$a101280a@.phx.gbl...
> Hi all,
> I run MDAC Component Check on my SQL Server 2000. None
> of the versions of MDAC passed the tests. The closest one
> is 2.7 SP1 (2.71.9030.9) with only One error on
> msjtes40.dll file
> msjtes40.dll,Error,Field 'FileVersion' mismatch:
> Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
> Expression
> Does this mean that I have to install 2.71 Service
> Pack 1 Refresh: mdac_typ.exe ?
> What files contain in this mdac_typ.exe? Would
> installing this file help on Memory Leak?
> Thank you sooooo much!!!
> P.S.: I think I install 2.7 SP1 via Windows Update. I
> didn't apply SQL Sever 2000 SP3 at all.
> Annie
>|||Thank you, Tim.
I don't have MS Office installed on Server, but it could
be something else.
That was helpful. Thank you~
Annie
>--Original Message--
>That file almost always checks different, so don't worry.
>normally if the version number is higher compared to the
closest match it is
>OK.
>This normally happens because you installed some software
with an newer DLL.
>( I am assuming msjtes40.dll is installed by MS Office,
since I always see
>it being different at work.)
>Tim S
>
>"Annie" <piyomeme@.yahoo.com> wrote in message
>news:05da01c34d8b$f0e63f00$a101280a@.phx.gbl...
>> Hi all,
>> I run MDAC Component Check on my SQL Server 2000.
None
>> of the versions of MDAC passed the tests. The closest
one
>> is 2.7 SP1 (2.71.9030.9) with only One error on
>> msjtes40.dll file
>> msjtes40.dll,Error,Field 'FileVersion' mismatch:
>> Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
>> Expression
>> Does this mean that I have to install 2.71 Service
>> Pack 1 Refresh: mdac_typ.exe ?
>> What files contain in this mdac_typ.exe? Would
>> installing this file help on Memory Leak?
>> Thank you sooooo much!!!
>> P.S.: I think I install 2.7 SP1 via Windows Update. I
>> didn't apply SQL Sever 2000 SP3 at all.
>> Annie
>
>.
>
I run MDAC Component Check on my SQL Server 2000. None
of the versions of MDAC passed the tests. The closest one
is 2.7 SP1 (2.71.9030.9) with only One error on
msjtes40.dll file
msjtes40.dll,Error,Field 'FileVersion' mismatch:
Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
Expression
Does this mean that I have to install 2.71 Service
Pack 1 Refresh: mdac_typ.exe ?
What files contain in this mdac_typ.exe? Would
installing this file help on Memory Leak?
Thank you sooooo much!!!
P.S.: I think I install 2.7 SP1 via Windows Update. I
didn't apply SQL Sever 2000 SP3 at all.
AnnieThat file almost always checks different, so don't worry.
normally if the version number is higher compared to the closest match it is
OK.
This normally happens because you installed some software with an newer DLL.
( I am assuming msjtes40.dll is installed by MS Office, since I always see
it being different at work.)
Tim S
"Annie" <piyomeme@.yahoo.com> wrote in message
news:05da01c34d8b$f0e63f00$a101280a@.phx.gbl...
> Hi all,
> I run MDAC Component Check on my SQL Server 2000. None
> of the versions of MDAC passed the tests. The closest one
> is 2.7 SP1 (2.71.9030.9) with only One error on
> msjtes40.dll file
> msjtes40.dll,Error,Field 'FileVersion' mismatch:
> Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
> Expression
> Does this mean that I have to install 2.71 Service
> Pack 1 Refresh: mdac_typ.exe ?
> What files contain in this mdac_typ.exe? Would
> installing this file help on Memory Leak?
> Thank you sooooo much!!!
> P.S.: I think I install 2.7 SP1 via Windows Update. I
> didn't apply SQL Sever 2000 SP3 at all.
> Annie
>|||Thank you, Tim.
I don't have MS Office installed on Server, but it could
be something else.
That was helpful. Thank you~
Annie
>--Original Message--
>That file almost always checks different, so don't worry.
>normally if the version number is higher compared to the
closest match it is
>OK.
>This normally happens because you installed some software
with an newer DLL.
>( I am assuming msjtes40.dll is installed by MS Office,
since I always see
>it being different at work.)
>Tim S
>
>"Annie" <piyomeme@.yahoo.com> wrote in message
>news:05da01c34d8b$f0e63f00$a101280a@.phx.gbl...
>> Hi all,
>> I run MDAC Component Check on my SQL Server 2000.
None
>> of the versions of MDAC passed the tests. The closest
one
>> is 2.7 SP1 (2.71.9030.9) with only One error on
>> msjtes40.dll file
>> msjtes40.dll,Error,Field 'FileVersion' mismatch:
>> Expect '4.00.2927.8' Got '4.00.5914.0',Microsoft Jet
>> Expression
>> Does this mean that I have to install 2.71 Service
>> Pack 1 Refresh: mdac_typ.exe ?
>> What files contain in this mdac_typ.exe? Would
>> installing this file help on Memory Leak?
>> Thank you sooooo much!!!
>> P.S.: I think I install 2.7 SP1 via Windows Update. I
>> didn't apply SQL Sever 2000 SP3 at all.
>> Annie
>
>.
>
Subscribe to:
Posts (Atom)