Hi,
I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.
I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.
Many thanks in advanceMark,
this may be a situation where you want to take a step backwards in the requirements definition process and look at the problem from a different context: what is it that your users need? While there are ways to copy data/files from point A to point B using SQL Server, there may be an entirely different approach that would be more in keeping with best practices.
As a first step, I might suggest investigating DTS (Data Transformation Services). I would also look at SQL Reporting Services (though you need a bit of experience setting this up; I wouldn't recommend it for a newbie).
You may also want to consider Access Data Projects (ADP); I don't recommend them for development, but they do provide serviceable reporting tools.
Regardless, focus on the user requirements (not the way they've always dones things, but rather what they really use the information for and how they use it). All that being said, of course you want to meet whatever need in the shortest amount of time possible. Be prepared to take an incremental approach and "lead" your users to a better solution down the road.
Regards,
hmscott|||Thanks for getting back to me.
What I have to do is take data extracts suplied to me by our IT dept and manipulate them, then supply various extracts out of this back to various teams in the company. Because of the way our place works I'd export to another database, zip it, and then move it to a shared (or sometimes protected) location each team has access to.
I used to do this via Access, but now want to up my skills and improve the way I do things.
My theory was:
1. DTS import the data (18 files)
2. Managet the data
3. Export the data
4. Zip the files and move to locations
To be honest I was just hoping it was as simple as a FileCopy but I was just missing it in the ActiveX.
If it's just not feasible yet for me at my skill level I can appreciate that, just thought I'd ask the question.
Many thanks|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.
DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'|||I think a light bulb has just come on.
Is it not just the File Transfer Protocol Task.
Just tested it and seems to work fine.|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.
DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'
You can use this method across the network if:
1. You use UNC naming conventions (\\SERVER\Share\folder\file.zip)
2. You SQL Server is running under a service account (not LOCALSYSTEM) and this account has write permissions on the target server.
Still, you might want to consider some other (more scalable/reliable) solutions such as:
1. ADP (already mentioned)
2. Replication to an Access database (never tried it myself, but it's supposed to work)
3. Use DTS to generate the data to the target file (DTS has export connections for Excel, MS Access and other target file types).
Regards,
hmscott
Showing posts with label fairly. Show all posts
Showing posts with label fairly. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
Copy Database - Same SQL Server
Hello.
I am fairly new to SQL Server. I have a hosted sql server with unlimited licensing and database has been created on it from outsourced vendor.
I need to create a duplicate database, on same server, different directory, with a different name for development and testing.
I have been told to simply to a backup and restore with MOVE, however in my research I have come across things to be aware of and even errors. I am a newbie dba for small company and I do not want to take chances ruining their current database.
Does anyone have any directions-specific advice?
Your help will be greatly appreciated!Issues you were warned about are real, but harmless in your case. Backup/restore will work for you just fine. Just make sure to change the name of the database prior to clicking on Restore. As a precaution, also make sure that "Force restore over existing database" is NOT selected.|||You can also:
1. detach the database
2. copy the database to the new location
3. attach both databases|||The time you really need to be carefull with the Backup/Restore method is when you are restoring to a different server, where user logins may not match those in your database. This should not be an issue in your case.
By the way, why do you need to have the database in a different directory?|||...wait a minute, there's (at least) one more thing you need to be aware of:
If any of the code in your database reference objects using a fully qualified name that includes that name of the database along with the name of the object, this code will execute against the objects in your original database rather than the differently-named copy. For instance, lets say your databases are called DBORIGINAL and DBCOPY, this code:
select * from YOURTABLE
will run fine in your copied database. But this code:
select * from DBORIGINAL..YOURTABLE
-or-
select * from DBORIGINAL.OWNER.YOURTABLE
will continue to select from the original database, not your copy.
You should check your stored procedures, views, and functions to look for cases like this.|||The application being tested will have to be looked over before you get too far. If the application uses ODBC, and you can not change the DSN in the application's connection strings, then you can not test (except on production). Also, if the connection strings specify a database, then you are back in the above situation. Lastly (and here is my favorite), does the user that is being used have a default database assigned? Likely you will not be able to change that on production.
Ideally, a second DB server (or a named instance) would be better for testing purposes.|||Good point blindman - that goes for any object that uses fully qualified names. Even something as simple as copying/scripting an object from one database to another.|||I used the Backup/Restore method to duplicate a database with different database name for our clients' testing. There are following things you need to do:
1) Like blindman said, you must make sure to change the original database name into the copied database name in your scripts if there is the original database name in your scripts.
2) Create new user login for the copied database.
3) Create the ODBC for your connection using a new DSN
4) Change you connection string in your code to the copied database.|||Good practice that I acquired was to preceed your application with logon screen that captures the essentials of the environment that the application will execute in. This way you avoid the pain of having hard-coded connection strings to be changed every time you move your database to a different server or having to rename your database all together.|||The more experience you get, the more parameters you use!|||In ASP, you can use Application variable to store the connection string. So you only need to change one file which is global.asa.|||I use conn.inc.asp with a
Function oc()
End Function
Place that in top of ya asp pages and only have to change 1 file.|||Different ways can get the same target, but the important thing is to use a better way.|||ok, i've had to do this today for development work.
tried thru Trans-SQL didn't get very far, used Enterprise Manager...
In enterprise manager, create new database on desired server, "Tel_Copy", original "Tel".
Now right-click on "Tel-Copy", select options, set to restricted/single-user access, click ok to close dialog.
Now right-click on "Tel-Copy", select Restore Database.
Make sure "Tel-Copy" is what it says in Restore Database As...
Now Choose backup device, from file, add old backup file. now goto next tab, select "force restore over existing database".
Now in the list below, you'll see Tel_Data -> C:\tel_data.mdf, and Tel_Log -> C:\tel_log.ldf
change c:\tel_data.mdf to c:\tel_copy_data.mdf
(remember to not change the logical dataname here)
change c:\tel_log.mdf to c:\tel_copy_log.mdf
(remember to not change the logical dataname here)
hit restore and the Tel backup will be restored as Tel-Copy
worked here.
I am fairly new to SQL Server. I have a hosted sql server with unlimited licensing and database has been created on it from outsourced vendor.
I need to create a duplicate database, on same server, different directory, with a different name for development and testing.
I have been told to simply to a backup and restore with MOVE, however in my research I have come across things to be aware of and even errors. I am a newbie dba for small company and I do not want to take chances ruining their current database.
Does anyone have any directions-specific advice?
Your help will be greatly appreciated!Issues you were warned about are real, but harmless in your case. Backup/restore will work for you just fine. Just make sure to change the name of the database prior to clicking on Restore. As a precaution, also make sure that "Force restore over existing database" is NOT selected.|||You can also:
1. detach the database
2. copy the database to the new location
3. attach both databases|||The time you really need to be carefull with the Backup/Restore method is when you are restoring to a different server, where user logins may not match those in your database. This should not be an issue in your case.
By the way, why do you need to have the database in a different directory?|||...wait a minute, there's (at least) one more thing you need to be aware of:
If any of the code in your database reference objects using a fully qualified name that includes that name of the database along with the name of the object, this code will execute against the objects in your original database rather than the differently-named copy. For instance, lets say your databases are called DBORIGINAL and DBCOPY, this code:
select * from YOURTABLE
will run fine in your copied database. But this code:
select * from DBORIGINAL..YOURTABLE
-or-
select * from DBORIGINAL.OWNER.YOURTABLE
will continue to select from the original database, not your copy.
You should check your stored procedures, views, and functions to look for cases like this.|||The application being tested will have to be looked over before you get too far. If the application uses ODBC, and you can not change the DSN in the application's connection strings, then you can not test (except on production). Also, if the connection strings specify a database, then you are back in the above situation. Lastly (and here is my favorite), does the user that is being used have a default database assigned? Likely you will not be able to change that on production.
Ideally, a second DB server (or a named instance) would be better for testing purposes.|||Good point blindman - that goes for any object that uses fully qualified names. Even something as simple as copying/scripting an object from one database to another.|||I used the Backup/Restore method to duplicate a database with different database name for our clients' testing. There are following things you need to do:
1) Like blindman said, you must make sure to change the original database name into the copied database name in your scripts if there is the original database name in your scripts.
2) Create new user login for the copied database.
3) Create the ODBC for your connection using a new DSN
4) Change you connection string in your code to the copied database.|||Good practice that I acquired was to preceed your application with logon screen that captures the essentials of the environment that the application will execute in. This way you avoid the pain of having hard-coded connection strings to be changed every time you move your database to a different server or having to rename your database all together.|||The more experience you get, the more parameters you use!|||In ASP, you can use Application variable to store the connection string. So you only need to change one file which is global.asa.|||I use conn.inc.asp with a
Function oc()
End Function
Place that in top of ya asp pages and only have to change 1 file.|||Different ways can get the same target, but the important thing is to use a better way.|||ok, i've had to do this today for development work.
tried thru Trans-SQL didn't get very far, used Enterprise Manager...
In enterprise manager, create new database on desired server, "Tel_Copy", original "Tel".
Now right-click on "Tel-Copy", select options, set to restricted/single-user access, click ok to close dialog.
Now right-click on "Tel-Copy", select Restore Database.
Make sure "Tel-Copy" is what it says in Restore Database As...
Now Choose backup device, from file, add old backup file. now goto next tab, select "force restore over existing database".
Now in the list below, you'll see Tel_Data -> C:\tel_data.mdf, and Tel_Log -> C:\tel_log.ldf
change c:\tel_data.mdf to c:\tel_copy_data.mdf
(remember to not change the logical dataname here)
change c:\tel_log.mdf to c:\tel_copy_log.mdf
(remember to not change the logical dataname here)
hit restore and the Tel backup will be restored as Tel-Copy
worked here.
Friday, February 10, 2012
Converting MS Access to SQL Server 2K
Hi,
I am fairly new to SQL Server and would appreciate if anyone could solve my
problem.
I have SQL server 2k installed on the server, say server1. My website is
also hosted on the same server. I have another instance installed on my
client machine.
Now I am connecting to the remote SQL server instance and
migrating/importing data from MS Access DB. The database migrated
successfully. I changed the ADO connection properties in my ASP code to
access data from SQL server.
Now the problem...
1) I am connecting to the DB but have to prefix all my table names with the
DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
2) Also the recordset does not return any record
please help
regards
Using the 2-part name is optional... When you select * from mytable. SQL
first looks to see if YOU own a table with that name. ( ie wayne.mytable).
If such a table does NOT exist, then it searches for and returns a table
like this owned by the dbo... ( ie dbo.mytable)...
So you shouldn't have to use the 2-part name...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fuzail" <Fuzail@.discussions.microsoft.com> wrote in message
news:AB8DC0C4-29A8-48A1-99AC-B89F9668DBDA@.microsoft.com...
> Hi,
> I am fairly new to SQL Server and would appreciate if anyone could solve
my
> problem.
> I have SQL server 2k installed on the server, say server1. My website is
> also hosted on the same server. I have another instance installed on my
> client machine.
> Now I am connecting to the remote SQL server instance and
> migrating/importing data from MS Access DB. The database migrated
> successfully. I changed the ADO connection properties in my ASP code to
> access data from SQL server.
> Now the problem...
> 1) I am connecting to the DB but have to prefix all my table names with
the
> DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
> 2) Also the recordset does not return any record
> please help
> regards
|||
>2) Also the recordset does not return any record
what is your query, and what is the dB structure?
I am fairly new to SQL Server and would appreciate if anyone could solve my
problem.
I have SQL server 2k installed on the server, say server1. My website is
also hosted on the same server. I have another instance installed on my
client machine.
Now I am connecting to the remote SQL server instance and
migrating/importing data from MS Access DB. The database migrated
successfully. I changed the ADO connection properties in my ASP code to
access data from SQL server.
Now the problem...
1) I am connecting to the DB but have to prefix all my table names with the
DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
2) Also the recordset does not return any record
please help
regards
Using the 2-part name is optional... When you select * from mytable. SQL
first looks to see if YOU own a table with that name. ( ie wayne.mytable).
If such a table does NOT exist, then it searches for and returns a table
like this owned by the dbo... ( ie dbo.mytable)...
So you shouldn't have to use the 2-part name...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fuzail" <Fuzail@.discussions.microsoft.com> wrote in message
news:AB8DC0C4-29A8-48A1-99AC-B89F9668DBDA@.microsoft.com...
> Hi,
> I am fairly new to SQL Server and would appreciate if anyone could solve
my
> problem.
> I have SQL server 2k installed on the server, say server1. My website is
> also hosted on the same server. I have another instance installed on my
> client machine.
> Now I am connecting to the remote SQL server instance and
> migrating/importing data from MS Access DB. The database migrated
> successfully. I changed the ADO connection properties in my ASP code to
> access data from SQL server.
> Now the problem...
> 1) I am connecting to the DB but have to prefix all my table names with
the
> DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
> 2) Also the recordset does not return any record
> please help
> regards
|||
>2) Also the recordset does not return any record
what is your query, and what is the dB structure?
Converting MS Access to SQL Server 2K
Hi,
I am fairly new to SQL Server and would appreciate if anyone could solve my
problem.
I have SQL server 2k installed on the server, say server1. My website is
also hosted on the same server. I have another instance installed on my
client machine.
Now I am connecting to the remote SQL server instance and
migrating/importing data from MS Access DB. The database migrated
successfully. I changed the ADO connection properties in my ASP code to
access data from SQL server.
Now the problem...
1) I am connecting to the DB but have to prefix all my table names with the
DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
2) Also the recordset does not return any record
please help
regardsUsing the 2-part name is optional... When you select * from mytable. SQL
first looks to see if YOU own a table with that name. ( ie wayne.mytable).
If such a table does NOT exist, then it searches for and returns a table
like this owned by the dbo... ( ie dbo.mytable)...
So you shouldn't have to use the 2-part name...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fuzail" <Fuzail@.discussions.microsoft.com> wrote in message
news:AB8DC0C4-29A8-48A1-99AC-B89F9668DBDA@.microsoft.com...
> Hi,
> I am fairly new to SQL Server and would appreciate if anyone could solve
my
> problem.
> I have SQL server 2k installed on the server, say server1. My website is
> also hosted on the same server. I have another instance installed on my
> client machine.
> Now I am connecting to the remote SQL server instance and
> migrating/importing data from MS Access DB. The database migrated
> successfully. I changed the ADO connection properties in my ASP code to
> access data from SQL server.
> Now the problem...
> 1) I am connecting to the DB but have to prefix all my table names with
the
> DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
> 2) Also the recordset does not return any record
> please help
> regards|||>2) Also the recordset does not return any record
what is your query, and what is the dB structure?
I am fairly new to SQL Server and would appreciate if anyone could solve my
problem.
I have SQL server 2k installed on the server, say server1. My website is
also hosted on the same server. I have another instance installed on my
client machine.
Now I am connecting to the remote SQL server instance and
migrating/importing data from MS Access DB. The database migrated
successfully. I changed the ADO connection properties in my ASP code to
access data from SQL server.
Now the problem...
1) I am connecting to the DB but have to prefix all my table names with the
DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
2) Also the recordset does not return any record
please help
regardsUsing the 2-part name is optional... When you select * from mytable. SQL
first looks to see if YOU own a table with that name. ( ie wayne.mytable).
If such a table does NOT exist, then it searches for and returns a table
like this owned by the dbo... ( ie dbo.mytable)...
So you shouldn't have to use the 2-part name...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fuzail" <Fuzail@.discussions.microsoft.com> wrote in message
news:AB8DC0C4-29A8-48A1-99AC-B89F9668DBDA@.microsoft.com...
> Hi,
> I am fairly new to SQL Server and would appreciate if anyone could solve
my
> problem.
> I have SQL server 2k installed on the server, say server1. My website is
> also hosted on the same server. I have another instance installed on my
> client machine.
> Now I am connecting to the remote SQL server instance and
> migrating/importing data from MS Access DB. The database migrated
> successfully. I changed the ADO connection properties in my ASP code to
> access data from SQL server.
> Now the problem...
> 1) I am connecting to the DB but have to prefix all my table names with
the
> DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
> 2) Also the recordset does not return any record
> please help
> regards|||>2) Also the recordset does not return any record
what is your query, and what is the dB structure?
Converting MS Access to SQL Server 2K
Hi,
I am fairly new to SQL Server and would appreciate if anyone could solve my
problem.
I have SQL server 2k installed on the server, say server1. My website is
also hosted on the same server. I have another instance installed on my
client machine.
Now I am connecting to the remote SQL server instance and
migrating/importing data from MS Access DB. The database migrated
successfully. I changed the ADO connection properties in my ASP code to
access data from SQL server.
Now the problem...
1) I am connecting to the DB but have to prefix all my table names with the
DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
2) Also the recordset does not return any record
please help
regardsUsing the 2-part name is optional... When you select * from mytable. SQL
first looks to see if YOU own a table with that name. ( ie wayne.mytable).
If such a table does NOT exist, then it searches for and returns a table
like this owned by the dbo... ( ie dbo.mytable)...
So you shouldn't have to use the 2-part name...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fuzail" <Fuzail@.discussions.microsoft.com> wrote in message
news:AB8DC0C4-29A8-48A1-99AC-B89F9668DBDA@.microsoft.com...
> Hi,
> I am fairly new to SQL Server and would appreciate if anyone could solve
my
> problem.
> I have SQL server 2k installed on the server, say server1. My website is
> also hosted on the same server. I have another instance installed on my
> client machine.
> Now I am connecting to the remote SQL server instance and
> migrating/importing data from MS Access DB. The database migrated
> successfully. I changed the ADO connection properties in my ASP code to
> access data from SQL server.
> Now the problem...
> 1) I am connecting to the DB but have to prefix all my table names with
the
> DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
> 2) Also the recordset does not return any record
> please help
> regards|||
>2) Also the recordset does not return any record
what is your query, and what is the dB structure?
I am fairly new to SQL Server and would appreciate if anyone could solve my
problem.
I have SQL server 2k installed on the server, say server1. My website is
also hosted on the same server. I have another instance installed on my
client machine.
Now I am connecting to the remote SQL server instance and
migrating/importing data from MS Access DB. The database migrated
successfully. I changed the ADO connection properties in my ASP code to
access data from SQL server.
Now the problem...
1) I am connecting to the DB but have to prefix all my table names with the
DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
2) Also the recordset does not return any record
please help
regardsUsing the 2-part name is optional... When you select * from mytable. SQL
first looks to see if YOU own a table with that name. ( ie wayne.mytable).
If such a table does NOT exist, then it searches for and returns a table
like this owned by the dbo... ( ie dbo.mytable)...
So you shouldn't have to use the 2-part name...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fuzail" <Fuzail@.discussions.microsoft.com> wrote in message
news:AB8DC0C4-29A8-48A1-99AC-B89F9668DBDA@.microsoft.com...
> Hi,
> I am fairly new to SQL Server and would appreciate if anyone could solve
my
> problem.
> I have SQL server 2k installed on the server, say server1. My website is
> also hosted on the same server. I have another instance installed on my
> client machine.
> Now I am connecting to the remote SQL server instance and
> migrating/importing data from MS Access DB. The database migrated
> successfully. I changed the ADO connection properties in my ASP code to
> access data from SQL server.
> Now the problem...
> 1) I am connecting to the DB but have to prefix all my table names with
the
> DBO, eg.. [DBO].tablename. Is there any way I can avoid this?
> 2) Also the recordset does not return any record
> please help
> regards|||
>2) Also the recordset does not return any record
what is your query, and what is the dB structure?
Subscribe to:
Posts (Atom)