data dump - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

data dump

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-17 : 09:33:12

One of the user asked to provide some tables data.

In our production database, we have 250+ tables. So just wondering to know the options to provide the required data/tables/schema.

earlier I used to provide the data in excel (storing sql query data in excel)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-17 : 09:48:12

BCP is a good option

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 09:50:42

I would transfer the data to a new, temporary, database and send them that:


SELECT *
INTO TempDatabase.dbo.TableXXX
FROM MyDatabase.dbo.TableXXX

repeat for each table.

Excel is not a reliable transfer tool as it will interfere with data formatting - e.g. it will format things that look like dates as dates, it will drop any leading zero on anything that looks like a number, and so on.

You could also transfer as a file - one per table. For example CSV files (but make sure both you and the user are agreed about how you handle embedded comma's in field data etc.)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 10:05:26

if sql 2008, you can script out objects along with data and send it across

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-17 : 10:24:15

Thanks russell and kristen for replies.

User requires the data on monthly basis. Is there any way to setup this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 10:29:15

quote:
Originally posted by laddu

Thanks russell and kristen for replies.

User requires the data on monthly basis. Is there any way to setup this ?

yup. you could schedule a sql agent job that executed monthly for doing this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 11:41:47

Are they happy to have it in a SQL Database?

If so could you just provide them with a Linked Server to "pull" it from?

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-17 : 11:48:56

Kristen- I am waiting for the user reply on which format he needs the data, it seems he needs everything, is it okay to provide the backup file.

How to configure Linked server? client needs SQL Server on his machine?

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 12:00:52

I would be shy about providing a backup file of the actual database. That will contain all the other aspects of your database application - constraints, stored procedures, triggers, indexes, ... are they paying to have that insider-information / your Intellectual Property, given to them on-a-plate?

But if you copy the tables to a temporary database (as I suggested earlier) then I see no difficulty in giving that to the client - it is just data. (You might want to create a PK / Index(es), or leave that up to the client)

"How to configure Linked server? client needs SQL Server on his machine?"

Yes, but they need to have SQL installed if you are sending them a backup

although I expect that SQL Express (free version) would be enough. Both of you will also need to create a port on the firewall that the data can travel through ... but other than that it will be easy. If the data is huge you probably won't want that loading on your external connection - just ZIP up a backup and send it to the client

- bit harder to automate all the steps though ...

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-17 : 12:37:30

I think creating tempdb and transfer required tables is the best option.

When you say Linked server, we have 5 user databases, can i restrict client to access only one database

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-17 : 13:29:04

Hi Kristen,

Just got reply from user. He needs specific tables data. He has STATA software with him (It converts files from one format to another format).

My question is can I import specific tables to a MSAccess file and send it him

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 13:30:32

quote:
Originally posted by laddu

Hi Kristen,

Just got reply from user. He needs specific tables data. He has STATA software with him (It converts files from one format to another format).

My question is can I import specific tables to a MSAccess file and send it him

you can use export import wizard to do that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 13:48:26

"When you say Linked server, we have 5 user databases, can i restrict client to access only one database"

Yes, just set up a SQL server login, and then grant it access to only the one DB they are allowed to see.

He should be able to connect an Access database to your Server - if you want to go down that route. If you can easily automate exporting to an Access database and stick that on a CD, or ZIP it for Email / Download, then I think that would be preferable (no chance of anyone hacking in etc)

Mind you ... that seems to be how our government transfer data with sensitive personal information, and its forever turning up forgotten on trains, in rubbish tips, etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources