Automated Data Export
Feb 12, 2004
Here's my process:
1. I have a form where someone enters certain criteria for some data…
2. The form than populates a record in a table with all the criteria…
3. I create a query based on the selected criteria for exporting…
How can I automate the process (maybe using DTS) where SQL server will automatically export the data for me using a stor proc? Create a file, populate the file etc…. or will I need to create an external App to do this?
View 2 Replies
ADVERTISEMENT
Oct 19, 1999
Hi. I am using SQL 7.0.
Here is the situation... there are random (.csv) files being dropped into a specific directory on a daily basis. Is it possible to have SQL server automatically pick up this file and run it through the Import/Export data option so that it creates a SQL table from the original data? I need this whole sequence to be automated. Is this possible? Somebody please help... I will appreciate it greatly. Thanks a lot!
Alan
View 2 Replies
View Related
Nov 2, 2006
Hello, Everyone: Greetings!
I am new to Sql Server [Express version] and am not even sure I'm making the right choice. So here I am, seeking advice.
My database needs are nothing sophisticated. They just involve:
(a) create tens of thousands of separate data files each under a unique file name of up to 8 characters, with file names read in from a pre-determined file name list.
(b) store/insert VOLUMINOUS numerical data into each of the data files, with the data indexed by date&time, plus maybe one or two additional character or string fields.
(c) for each data file, retrieve a subset of its data, perform extensive numerical calculations, and then store the results in one or more separate corresponding files, e.g. if a file name in (b) is F12345, (c) creates F12345R1, F12345R2, F12345R3, etc. which stores different sets of calculated results.
Thus, this is purely a console application, doing a batch job, and requiring no graphical user interface. Both automation and speed are important here, due to the large number of data files that must be created and/or updated, and the very extensive numerical calculations on the data.
The goal is to automate the daily or weekly creation of each of the tens of thousands of Sql Server database files, insert fresh data (read in from a fresh ASCII file) into each file, numerically process the data and then store the results in one or more separate, corresponding result data files, with all the steps automated and without need for GUI. Once coding is done, the entire data processing session is expected to run for many hours, or even days, in an automated manner, and without human intervention.
What would be the most efficient way of doing this under Visual Basic Express (which is what I'm learning to use) by directly calling Sql Server Express without having to go through GUI to create database files? What is the proper interface utility or library to use to enable direct database function calls without the need to learn SQL language? Is Visual Basic and/or Sql Server even good choices for what I want to do? I want to be able to call the basic, simple database functions directly and simply from program code in a non-GUI, non-interactive manner for the tens of thousands of separate data files that will be used.
I really miss the good old days when one can do a straightforward batch job via a console application, with simple, direct calls to create new data files, insert and index fresh data, retrieve any subset of data to do extensive calculations, create new files to store the results, etc. all under automated program control and iterating through unlimited number of data files, until the job is finished, all without human intervention during processing.
Or am I missing something because all this can still be done simply and easily under VB and Sql Server? I've several books here about Visual Basic 2005 and Visual Basic 2005 Express, all showing how to create a database via a GUI utility. That's fine if one needs to create just one or two databases, but not hundreds, or even tens of thousands (as in my case) of them on the fly.
So, I am looking for the simplest and most direct database interface that will allow me to do the above under VB program code alone, and easily. For something as simple as I have described above, I don't think I should have to learn the SQL language or manually create each database file.
As you can see, I just want to get some heavy duty numerical processing job done over tens of thousands of data files as simply and efficiently as possible, and with as little fanciful detour as possible. So, ironically, I am trying to use Visual Basic without being cluttered by having to learn its "Visual" aspects, yet GUI is what most VB books devote to or emphasize heavily. Similarly, I would much rather use simple, "lean and mean", direct database function calls than having to learn a new vocabulary of "English-like" SQL language.
Yes, I'm not used to this tedious detour of learning the GUI aspect of VB, or learning the Structured Query Language of Sql Server, just to try to do something simple that I need to do in batch mode via a console application.
Are there any good books or other helpful URLs that will help a guy like me? Am I even using the wrong language and the wrong database to do what I want to do? What are the better alternatives, if any? Any advice, experience and pointers on any of the above issues raised would be very much appreciated. Thank you!
Regards,
ConsoleApp
View 1 Replies
View Related
Mar 2, 2015
We have an SSAS project that we want to auto deploy. I am not sure how to handle the external data sources in the project. This one in particular has a single external data source defined to Microsoft SQL Server.
I would like to be able to change the data source based on the environment. In SSIS projects I can do this by setting up environments in the SSISDB and linking them to project parameters in the SSIS project but SSAS projects don't seem to have a similar mechanism.
How to handle this? I would like to be able to have the build/deployment agent pass in server / database information to the data source based on environment (dev, QA, production).
The only way to automate this that I've discovered is to have an intermediary process that executes after the build that updates the generated .asdatabase and .configsettings files in the bin folder replacing the connection string information.
View 0 Replies
View Related
Jun 14, 2006
Hi, all here,
Would please any expert here give me any guidance about what Data Mining tasks can be automated and scheduled via Integration Services Packages? Also, If we automated the tasks, can we also automatically save the results of the tasks somewhere? Like if we automate assessing the accuracy of a mining model, then we wanna know the mining model accuracy later, therefore, we need to save all these results from the automated actions. Is it possible to realize this?
Thanks a lot in advance for any guidance and help for this.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Feb 23, 2008
Hi ,
I'm just new in this SQL 2005, and I do not reallly sure the subject is right or not but as example in this link below
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008
Date To: 24/02/2008
Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name
-------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John
2 2/22/2008 4:16:45 PM Dean
3 2/23/2008 4:16:45 PM Rick
4 2/24/2008 4:16:45 PM Van
thanks to all of your corcern to help me
Regards;
View 13 Replies
View Related
Jan 4, 2008
How can I Export Database with foreing Key and primary key.
Operation is that
SQL2005 Management Studio/Database/Tasks/Export Data
Before Version is SQL2000 we can Selected Copy Object and data between server and then Use Default Options click checked and Select Copy Index, Copy Foreing Primary key vs vs
But this options is not found in the SQL2005 Management Studio/Database/Tasks/Export Data wizard or I can't found it.
How can I export foreing Key and primary key with SQL2005 Management Studio/Database/Tasks/Export Data wizard.
Best Regards,
Athena.
View 1 Replies
View Related
Oct 4, 2007
Hi all,
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Your prompt response is greatly appreciated.
Thanks!!
Tram
View 8 Replies
View Related
Feb 24, 2007
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
View 4 Replies
View Related
Sep 16, 2015
One of my report has different data types like decimal,percentage and integer values.
When I exported the report to excel , all the values are showing as "general" data type.
How to get excel data type same as ssrs report data type by default when exported to excel?
View 2 Replies
View Related
Feb 17, 2014
Background: In my current company the business users maintain a huge quantity of master data using excel. Then a series of SSIS jobs are edited and manually executed.
Goal: the challenge is to replace this process using MDS. One of the requested features is the possibility for the users to edit or insert new master data using the Web UI or the Excel Add-in and when they are done perform a merge of the master data in the target, in this case in the reporting DB.
The perfect solution for me is something like trigger the execution of a SSIS package to export the data from the subscription views to the reporting DB after the business rules are apply to a specific entity.
View 1 Replies
View Related
Nov 14, 2007
I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,
Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.
View 4 Replies
View Related
Oct 28, 2005
Hi everyone,i'm new to ado.net and need some help. My problem:got one DB with real bad tablestructureand one new DB (structured!)i need do do an automated import from the old to the new DB, with check if the data row is new or updated.I try to do it this way-> 2 datasets merged together ->Problem: new entries in destination dataset have row state unchangedother way i tryed add rows from source to destination with add funktion -> problem no check if data exist and maybe is changedSo how can i get the merge working or how can i implemt an effective funktion to solve this problem. Thanks in advance!
View 5 Replies
View Related
Apr 3, 2000
Hi, looking for some paths to take on this one. I have to backup the database to a LS120 floppy
drive. It has to be automated so the user can just click on a desktop icon and away it goes. Probably
an easy script buy i sure would appreciete some help on this one.
Thanks
Troy
View 1 Replies
View Related
Aug 28, 1998
I am trying to automate the installation process of our Help
Desk software `Expert Advisor`. It uses a MS SQL Server 6.5
database. I can figure out the automation of Expert Advisor,
but is it possible to automate the installation of the
SQL drivers for each workstation? I don`t want or need any of the
utilities such as ISQL/w installed along with the driver--just the
SQL driver itself. Is there a runtime version of the database I should
use? I`m fairly new to SQL so please forgive me if I seem naive.
Any suggestions? Any help would be appreciated.
Toni
View 2 Replies
View Related
Jun 29, 2005
I don't know what happened on that first thread, but here it is again.
Thanks for your help.
Hello,
I have set up a .bat file to automatically export all of my server logs to a directory on my SQL server. From there I am trying to import those files into an event_log table that I have set up. (from there I am going to use Cold Fusion to parse the table each day and e-mail me with anything I should look at) The estimated ROI for my company is roughly $14.5k, it's easy so if you don't already have an error log monitoring solution, check this out!
Anyway I tried to use BCP in my .bat file but it keeps coming back with an unexpected end of file error.
Can I get some help with either:
1. Find a command line tool to import these .txt files with
-or-
2. Help me with the BCP issue?
Thanks in advance.
View 3 Replies
View Related
Jul 2, 2004
I am an intern at a university and we are looking for a way to automatically document tables, queries, etc. ColdFusion is the platform used, and SQL Server 2000. Is there a way to have the documentation performed that will capture tables, constraints, etc.? I have found some, but so far, none are working. Please help if you can!!! :confused:
View 4 Replies
View Related
Oct 17, 2005
Hi,
I have 2 databases, one called "ManagementDB" and the other called "ManagementDBYesterday".
Basically I get asked very frequently to look at yesterdays data for various reasons and I thought it'd be a good idea to always have yesterdays data to hand instead of restoring all the time.
I have tried DTS but this takes too long, I've also thought of replication however the database is modified too frequently for this to be easliy implemented.
Restoring from a backup is quite quick so I thought if I could somehow automate this, I'd have a readily available copy of yesterdays data to hand.
I have a mon-sun daily backup so I need to write some SQL script to restore from the correct backup to the "yesterday" database.
I've only used restore via the enterprise manager and I was wondering if anyone here could help.
kind regards,
Mike,
View 1 Replies
View Related
Jan 6, 2004
I have a customer who insists on emailing an excel spreadsheet to me to be uploaded to a sql database. I came across a script at one point that laid out how I would script this out. Basically it was in parts, 1. open email program and get attachment. 2.After saving attachment closing email program in order to be used the next day or next time an attachment was sent. 3. import the attached spreadsheet into SQL database. I would also like to be able to schedule this to run with the built in Windows 2000 scheduler.
Can anyone help me out on this one??
Thanks,
Will
View 5 Replies
View Related
Apr 11, 2006
Hi,
I'm working on a program that is some kind of organizer for all the tasks that employees have to carry out. A part of this program should check the deadline from the tasks, and send a mail if the deadline is in less than 5 days.
I'm using asp .net 2.0 & sql server 2000
Can anyone tell me how I can do that, or put me on track with some key words to google for?
thx.
View 4 Replies
View Related
Oct 21, 2007
Hi guys,
I'm pretty new in SQL Server and trying to build up a BI Solution for my firm. We have got monthly data about revenue headcounts and so, which should be integrated into a DW.
I was wondering whether there is an automated way to import this csv data in SQL like in a batch, because the users are not familiar with SQL Server at all. The structure of the data will be the same all the time, since it comes from legacy systems.
YOur help would be highly appriciated:)
demon
View 2 Replies
View Related
Nov 8, 2007
Hi All,
I have around 80 databases in my one of sql server instance.
we hve to regularly send the database wise health report to the client.
The following fields mentioned by the client in excel sheet
Name of database
Logical file name of .MDF
.Mdf File location drive
Size of the file in MB
Physical file location(full path of .mdf file)
Maxsize
Growth
Now a days we have to check databases one by one.
Could anyone provide me the script which automatically fetch all of required fields information from all of the databases.
Urgent help will be appreciable.
Frozen
View 2 Replies
View Related
Jan 28, 2008
Was unsure where to place this post.
Windows Server 2003R2 w/ Sp2
MS SQL 2005
I currently use MS SQL 2005 to host my Citrix Datastore DB. I manually run a daily back up (Tasks>Backup).
How difficult would this be to automated? I have googled this without luck.
Any insight appreciated.
Thanks,
Kerry
View 2 Replies
View Related
Jun 12, 2007
Unfortunately don't I know, how I can adjust this or whether SQL code is needed and if, where?? I do not know myself unfortunately yet so well with the subject out, from therefore am grateful I for each assistance. Many greetings M-l-g
Hello,
I have a problem with an integration services project of SQL server 2005. I have a text file with various data, which can be read out easily with a dataflow task and a flat file source. The data shall be transferred into a target database which I developed according to a star pattern. The fact table can be generated automatically if and only if all data belonging to a dimension table is inserted by myself. Of course, this procedure is not reasonable while having a vast amount of data. That is the reason why I want to generate the dimension tables automatically, too. A dimension is build e.g. like this: ApplicationID | ApplicationName. So, only two columns are available. But I don't know how to insert an automated ID.
I would be thankful for any help.M-l-G
View 7 Replies
View Related
Apr 15, 2008
Hi,
how would i unzip a password protected file after downloading it from sftp server.
thanks
View 2 Replies
View Related
Jul 17, 2001
What is the best way to do the following.....
I want to publish a database on the web and enable to end users to click a button on the web page which will export the data from a table into an Excel spreadsheet which is then saved onto the user's machine.
Is this possible with SQL Server 7 (dts/sp/???) or do I need to use other technologies (maybe something like COM?)
Any pointers would be greatly appreciated (even better if u can provide coding hints/samples)
Thanks
View 1 Replies
View Related
Nov 17, 2000
Dear all
Any one knows of away to export data out of all tables in SQL7.0, to a text file and import them back to another DB
View 1 Replies
View Related
Sep 19, 2003
Hi,
I need to export data from SQL Server 2000 table into XL Doc.
These data have 4 creteria and need to be store each creteria in each sheet.
This XL document should create new for everymonth.
Any Advice Please?.
Thanks,
Ravi
View 5 Replies
View Related
Jun 14, 2006
Hi,
I usually export data from my sqlserver db to a mdb file. I know there are different destinations like csv, txt, etc...
But however, how can I export data in a sql script file???? I know i should do it with the sentence "insert into table" but i would like to know if the sqlserver has some option to do this without i have to write the sentence for each rows to insert.
I know this option in Oracle.
Thanks for all.
View 5 Replies
View Related
Jun 4, 2007
Hi everyone,
Every day I have to export data from SQL server 2005 to Microsoft access. (with export data wizard)
There is an automatic function that allow to overwrite the data?
because every time that I do it the tables goes to add to the
existing Access database. So every time before start the job I have to delete the file and create a newone.
I appreciate your help
View 2 Replies
View Related
Jan 11, 2008
Hellow--
Is it possible to use stored procedure to export data?-- I know u can use pcb utility and DTS pkg (sql 2000)/SSIS, but I want to see if there is some other options. If it can be done using stored procedure--please, let me know
Thk
Josephine
View 7 Replies
View Related
Apr 9, 2007
Hello,I have some questions on my options available.I have to export some tables to csv files to enable another departmentto process the files. What I need is a way to do this in ms sqlthough a stored proc with quoted identifiers and column names asheads. I cannot figure out how to do this.Can anybody give me some options that would be the best options.I am using ms sql 2000.Thank you for your time.
View 2 Replies
View Related
Oct 5, 2007
How can I export data from a Sql Server 2005 Express table to CSV format?
Thanks in advance for any help provided in solving this.
View 4 Replies
View Related
