I'm attempting to do a clean up on someone else's database (whoopee!),
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegroups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Tuesday, March 27, 2012
copy from sql to access
I'm attempting to do a clean up on someone else's database (whoopee!),
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!
To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegro ups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>
sqlsql
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!
To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegro ups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>
sqlsql
copy from sql to access
I'm attempting to do a clean up on someone else's database (whoopee!),
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegroups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>
and I think the back end is on a SQL server, front end in access. I
know little about SQL and would like to get a copy into Access to work
with it for a while, before messing with the real thing. It sounds like
I can import the tables, or use DTS tool, but how do I get the
connections to the forms in the Front end? Is there any way to put it
in access without moving it into another sql server on my machine? I'm
lost. TIA!To copy database from SQL Server to Access you can do the following
1) Create a DTS Package to move the data between databasess
2) Create a lonked server to Access database (For more details see the
BOL)
3)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="\\servername\AccessData.mdb"; User
ID=Admin;Password='
)...TableName(col1,col2...)
SELECT col1,col2.... FROM TableName
<hmgeri@.gmail.com> wrote in message
news:1166076771.130361.39730@.t46g2000cwa.googlegroups.com...
> I'm attempting to do a clean up on someone else's database (whoopee!),
> and I think the back end is on a SQL server, front end in access. I
> know little about SQL and would like to get a copy into Access to work
> with it for a while, before messing with the real thing. It sounds like
> I can import the tables, or use DTS tool, but how do I get the
> connections to the forms in the Front end? Is there any way to put it
> in access without moving it into another sql server on my machine? I'm
> lost. TIA!
>
Copy files after backup in maintenance plan
Hello,
We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?
Thanks in advance.
Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........
else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.
Thanxx
Deepak
|||Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.
Madhu
|||Thank you both for the fast replys....
From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?
Thanks again in advance!
|||Hi Marty,
|||Excellent...thank you so muchGo to BIDS--> create a New project -- > Open the project.
In solution explorer you can see "SSIS packages" folder --> Right click on the folder
Select "Add existing package"
Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.
Copy files after backup in maintenance plan
Hello,
We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?
Thanks in advance.
Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........
else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.
Thanxx
Deepak
|||Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.
Madhu
|||Thank you both for the fast replys....
From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?
Thanks again in advance!
|||Hi Marty,
|||Excellent...thank you so muchsqlsqlGo to BIDS--> create a New project -- > Open the project.
In solution explorer you can see "SSIS packages" folder --> Right click on the folder
Select "Add existing package"
Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.
Sunday, March 25, 2012
Copy db from office to home back to office
Hello. I am developing an SQL database using MSDE and will be doing
development at the client's office as well as my home office. I need to be
able to take the database back and forth. What are my options for
accomplishing this task? Note that I am new to SQL so as usual, one
question will likely spawn more. I am very familiar with MS Access.
Thanks
GerryIf you are the only developer, and the db size is small enough, use
SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
.ldf files to a flash drive.
I use this technique myself. For a different client, the only thing I do
differntly is use FTP instead of a flash drive (have a sub-contractor
working that one...)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
> Hello. I am developing an SQL database using MSDE and will be doing
> development at the client's office as well as my home office. I need to
be
> able to take the database back and forth. What are my options for
> accomplishing this task? Note that I am new to SQL so as usual, one
> question will likely spawn more. I am very familiar with MS Access.
> Thanks
> Gerry
>|||These should help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
> Hello. I am developing an SQL database using MSDE and will be doing
> development at the client's office as well as my home office. I need to
be
> able to take the database back and forth. What are my options for
> accomplishing this task? Note that I am new to SQL so as usual, one
> question will likely spawn more. I am very familiar with MS Access.
> Thanks
> Gerry
>|||Thanks Kevin and Andrew (next reply) for such a quick response.
Now the questions continue as suspected. LOL
Using MSDE, how do I use SP_detach_db and sp_attach_db? It seems that these
commands are easily done in SQL Server 2000 but I don't have the interface.
Is there a dos command to do the same?
Thanks
Gerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uAQMkLp6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> If you are the only developer, and the db size is small enough, use
> SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
> .ldf files to a flash drive.
> I use this technique myself. For a different client, the only thing I do
> differntly is use FTP instead of a flash drive (have a sub-contractor
> working that one...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:9bTTb.398008$ts4.256106@.pd7tw3no...
> > Hello. I am developing an SQL database using MSDE and will be doing
> > development at the client's office as well as my home office. I need to
> be
> > able to take the database back and forth. What are my options for
> > accomplishing this task? Note that I am new to SQL so as usual, one
> > question will likely spawn more. I am very familiar with MS Access.
> >
> > Thanks
> > Gerry
> >
> >
>|||You should still have oSql. Check out oSql.exe in BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:KPTTb.398535$ts4.141111@.pd7tw3no...
> Thanks Kevin and Andrew (next reply) for such a quick response.
> Now the questions continue as suspected. LOL
> Using MSDE, how do I use SP_detach_db and sp_attach_db? It seems that
these
> commands are easily done in SQL Server 2000 but I don't have the
interface.
> Is there a dos command to do the same?
> Thanks
> Gerry
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:uAQMkLp6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > If you are the only developer, and the db size is small enough, use
> > SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
> > .ldf files to a flash drive.
> >
> > I use this technique myself. For a different client, the only thing I
do
> > differntly is use FTP instead of a flash drive (have a sub-contractor
> > working that one...)
> >
> > --
> > Kevin Hill
> > President
> > 3NF Consulting
> >
> > www.3nf-inc.com/NewsGroups.htm
> >
> > "News" <gerrydyck@.shaw.ca> wrote in message
> > news:9bTTb.398008$ts4.256106@.pd7tw3no...
> > > Hello. I am developing an SQL database using MSDE and will be doing
> > > development at the client's office as well as my home office. I need
to
> > be
> > > able to take the database back and forth. What are my options for
> > > accomplishing this task? Note that I am new to SQL so as usual, one
> > > question will likely spawn more. I am very familiar with MS Access.
> > >
> > > Thanks
> > > Gerry
> > >
> > >
> >
> >
>
development at the client's office as well as my home office. I need to be
able to take the database back and forth. What are my options for
accomplishing this task? Note that I am new to SQL so as usual, one
question will likely spawn more. I am very familiar with MS Access.
Thanks
GerryIf you are the only developer, and the db size is small enough, use
SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
.ldf files to a flash drive.
I use this technique myself. For a different client, the only thing I do
differntly is use FTP instead of a flash drive (have a sub-contractor
working that one...)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
> Hello. I am developing an SQL database using MSDE and will be doing
> development at the client's office as well as my home office. I need to
be
> able to take the database back and forth. What are my options for
> accomplishing this task? Note that I am new to SQL so as usual, one
> question will likely spawn more. I am very familiar with MS Access.
> Thanks
> Gerry
>|||These should help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
> Hello. I am developing an SQL database using MSDE and will be doing
> development at the client's office as well as my home office. I need to
be
> able to take the database back and forth. What are my options for
> accomplishing this task? Note that I am new to SQL so as usual, one
> question will likely spawn more. I am very familiar with MS Access.
> Thanks
> Gerry
>|||Thanks Kevin and Andrew (next reply) for such a quick response.
Now the questions continue as suspected. LOL
Using MSDE, how do I use SP_detach_db and sp_attach_db? It seems that these
commands are easily done in SQL Server 2000 but I don't have the interface.
Is there a dos command to do the same?
Thanks
Gerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uAQMkLp6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> If you are the only developer, and the db size is small enough, use
> SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
> .ldf files to a flash drive.
> I use this technique myself. For a different client, the only thing I do
> differntly is use FTP instead of a flash drive (have a sub-contractor
> working that one...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:9bTTb.398008$ts4.256106@.pd7tw3no...
> > Hello. I am developing an SQL database using MSDE and will be doing
> > development at the client's office as well as my home office. I need to
> be
> > able to take the database back and forth. What are my options for
> > accomplishing this task? Note that I am new to SQL so as usual, one
> > question will likely spawn more. I am very familiar with MS Access.
> >
> > Thanks
> > Gerry
> >
> >
>|||You should still have oSql. Check out oSql.exe in BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:KPTTb.398535$ts4.141111@.pd7tw3no...
> Thanks Kevin and Andrew (next reply) for such a quick response.
> Now the questions continue as suspected. LOL
> Using MSDE, how do I use SP_detach_db and sp_attach_db? It seems that
these
> commands are easily done in SQL Server 2000 but I don't have the
interface.
> Is there a dos command to do the same?
> Thanks
> Gerry
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:uAQMkLp6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > If you are the only developer, and the db size is small enough, use
> > SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
> > .ldf files to a flash drive.
> >
> > I use this technique myself. For a different client, the only thing I
do
> > differntly is use FTP instead of a flash drive (have a sub-contractor
> > working that one...)
> >
> > --
> > Kevin Hill
> > President
> > 3NF Consulting
> >
> > www.3nf-inc.com/NewsGroups.htm
> >
> > "News" <gerrydyck@.shaw.ca> wrote in message
> > news:9bTTb.398008$ts4.256106@.pd7tw3no...
> > > Hello. I am developing an SQL database using MSDE and will be doing
> > > development at the client's office as well as my home office. I need
to
> > be
> > > able to take the database back and forth. What are my options for
> > > accomplishing this task? Note that I am new to SQL so as usual, one
> > > question will likely spawn more. I am very familiar with MS Access.
> > >
> > > Thanks
> > > Gerry
> > >
> > >
> >
> >
>
Copy db from office to home back to office
Hello. I am developing an SQL database using MSDE and will be doing
development at the client's office as well as my home office. I need to be
able to take the database back and forth. What are my options for
accomplishing this task? Note that I am new to SQL so as usual, one
question will likely spawn more. I am very familiar with MS Access.
Thanks
GerryIf you are the only developer, and the db size is small enough, use
SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
.ldf files to a flash drive.
I use this technique myself. For a different client, the only thing I do
differntly is use FTP instead of a flash drive (have a sub-contractor
working that one...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
be
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
be
Now the questions continue as suspected. LOL
Using MSDE, how do I use SP_detach_db and sp_attach_db? It seems that these
commands are easily done in SQL Server 2000 but I don't have the interface.
Is there a dos command to do the same?
Thanks
Gerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uAQMkLp6DHA.2524@.TK2MSFTNGP11.phx.gbl...
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:KPTTb.398535$ts4.141111@.pd7tw3no...
these
interface.
development at the client's office as well as my home office. I need to be
able to take the database back and forth. What are my options for
accomplishing this task? Note that I am new to SQL so as usual, one
question will likely spawn more. I am very familiar with MS Access.
Thanks
GerryIf you are the only developer, and the db size is small enough, use
SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
.ldf files to a flash drive.
I use this technique myself. For a different client, the only thing I do
differntly is use FTP instead of a flash drive (have a sub-contractor
working that one...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
quote:
> Hello. I am developing an SQL database using MSDE and will be doing
> development at the client's office as well as my home office. I need to
be
quote:|||These should help:
> able to take the database back and forth. What are my options for
> accomplishing this task? Note that I am new to SQL so as usual, one
> question will likely spawn more. I am very familiar with MS Access.
> Thanks
> Gerry
>
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:9bTTb.398008$ts4.256106@.pd7tw3no...
quote:
> Hello. I am developing an SQL database using MSDE and will be doing
> development at the client's office as well as my home office. I need to
be
quote:|||Thanks Kevin and Andrew (next reply) for such a quick response.
> able to take the database back and forth. What are my options for
> accomplishing this task? Note that I am new to SQL so as usual, one
> question will likely spawn more. I am very familiar with MS Access.
> Thanks
> Gerry
>
Now the questions continue as suspected. LOL
Using MSDE, how do I use SP_detach_db and sp_attach_db? It seems that these
commands are easily done in SQL Server 2000 but I don't have the interface.
Is there a dos command to do the same?
Thanks
Gerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uAQMkLp6DHA.2524@.TK2MSFTNGP11.phx.gbl...
quote:|||You should still have oSql. Check out oSql.exe in BooksOnLine.
> If you are the only developer, and the db size is small enough, use
> SP_detach_db and sp_attach_db to make it portable, and copy the .mdf and
> .ldf files to a flash drive.
> I use this technique myself. For a different client, the only thing I do
> differntly is use FTP instead of a flash drive (have a sub-contractor
> working that one...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:9bTTb.398008$ts4.256106@.pd7tw3no...
> be
>
Andrew J. Kelly
SQL Server MVP
"News" <gerrydyck@.shaw.ca> wrote in message
news:KPTTb.398535$ts4.141111@.pd7tw3no...
quote:
> Thanks Kevin and Andrew (next reply) for such a quick response.
> Now the questions continue as suspected. LOL
> Using MSDE, how do I use SP_detach_db and sp_attach_db? It seems that
these
quote:
> commands are easily done in SQL Server 2000 but I don't have the
interface.
quote:
> Is there a dos command to do the same?
> Thanks
> Gerry
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:uAQMkLp6DHA.2524@.TK2MSFTNGP11.phx.gbl...
do[QUOTE]
to[QUOTE]
>
Copy db - lose login mapping
Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
Thanks
Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
Thanks
Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>
Copy db - lose login mapping
Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksHi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>sqlsql
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksHi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>sqlsql
Copy db - lose login mapping
Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksThat's because the info to tie the user to the login is gone - the
users are orphaned. There is an sp you can use to fix orphaned users.
I am not where I can look it up but I have a SQL script that takes
care of mapping my users to the logins. I am assuming the logins and
users are the same on both systems. You can also check the
knowledgebase and look at the information on moving users and keeping
thier passwords, etc. when they move.
On Fri, 21 Dec 2007 15:05:00 -0800, ken s
<kens@.discussions.microsoft.com> wrote:
>Whenever I copy my production db back to my development server i lose the
>user mapping for my app account and have to drop the db user, then remap th
e
>login to the correct db. Then I have to reassign permissions to the user.
>How can I prevent this from happening?
>Thanks
>|||Hi
http://dimantdatabasesolutions.blog...on.
html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksThat's because the info to tie the user to the login is gone - the
users are orphaned. There is an sp you can use to fix orphaned users.
I am not where I can look it up but I have a SQL script that takes
care of mapping my users to the logins. I am assuming the logins and
users are the same on both systems. You can also check the
knowledgebase and look at the information on moving users and keeping
thier passwords, etc. when they move.
On Fri, 21 Dec 2007 15:05:00 -0800, ken s
<kens@.discussions.microsoft.com> wrote:
>Whenever I copy my production db back to my development server i lose the
>user mapping for my app account and have to drop the db user, then remap th
e
>login to the correct db. Then I have to reassign permissions to the user.
>How can I prevent this from happening?
>Thanks
>|||Hi
http://dimantdatabasesolutions.blog...on.
html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>
Friday, February 24, 2012
Converts the 5 character string duration (ie hh:nn) to minutes
Dear All,
I wanted to convert 5 characters string duration (ie hh:mm - 10:30) to minutes and convert back the resulting minutes to 5 character string duration using a scalar UDFs in Sqlserver 2000. How do i write the script to obtain the desired output.
I badly need some from someone.
Give few set of example. do you want result 10 *60 + 30 for 10:30..|||For example, if duration is 10 hrs. 30 mins. written in 10:30 format should be convert to minutes, means convert 10 hours into minutes and add 30 mins to it will be the resulting output = 630 minutes this should be convert back into the previous (10:30) format.
Thanks
|||here you go...
Code Snippet
Create Table #times (
[Time] Varchar(100)
);
Insert Into #times Values('10:30');
Insert Into #times Values('12:34');
Insert Into #times Values('15:45');
Select
Datediff(Mi,Cast('00:00' as datetime) ,Cast([Time] as Datetime))
From
#times
--or
Select
Substring([Time],1,Charindex(':',[Time])-1) * 60
+ Substring([Time],Charindex(':',[Time]) + 1,10)
From
#Times
|||Thanks alot for the help. And how about the reverse of that, ie, Converts the resulting minutes to 5 character string duration
|||Yes.. here it is...
Code Snippet
Create Table #mindata (
[Mint] int
);
Insert Into #mindata Values('630');
Insert Into #mindata Values('754');
Insert Into #mindata Values('945');
Select
Cast(Mint/60 as Varchar) + ':' + Cast(Mint%60 as Varchar)as [Time]
From
#mindata
|||
Dear Manivannan.D.Sekaran,
Thank you very very very much.
Tuesday, February 14, 2012
Converting Stored Procedures back to MS Access Queries
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
Any advice would be greatly appreciated.
Many thanks
SmillaYou can't convert any but the simplest sprocs into Access queries. Sprocs can be mult-step, while Access queries are simple statements like SQL Views. You would have to write VB code to simulate the sprocs, and there is no utility I know of that will do this for you.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
Any advice would be greatly appreciated.
Many thanks
SmillaYou can't convert any but the simplest sprocs into Access queries. Sprocs can be mult-step, while Access queries are simple statements like SQL Views. You would have to write VB code to simulate the sprocs, and there is no utility I know of that will do this for you.
Subscribe to:
Posts (Atom)