June 11, 2009 at 12:03 pm
Hey guys,
I have been asked to build an client-server application. The server application rests on the sql server and the client rests on user machines. the point of the application code on the server-side is to create a file that is formated with the result of a query against the sql server. I would like to know what advice you can give me on a good way to implement this. thank you
June 11, 2009 at 1:26 pm
Pretty vague specs from which to write an application. What else can you tell us? Specifics would be really appreciated.
June 11, 2009 at 1:49 pm
That is in vague.
Does the file reside on the SQL Server? Does it need to? Typically SQL Server doesn't write to the file system. It can, but that's not something you do. There are all sorts of ways to do this, BCP, SSIS, SQLCMD. More specs or requirements would help us to recommend something.
June 15, 2009 at 7:08 am
I am dealing with sql server 2000. One of the things that the application must do is to generate a flat file by pulling some data from sql server. The data must reside on the server side. I am not sure how to go about doing this. Either using ASP.NET or vb script or console application or whatever suggestion you might have. But I would love to hear your advices.
Also what would be the best way to implement this. Is it efficient to have users run the executable .exe application on the server or to have a client application connecting to the server application which will then access sql server and generate that file?
June 15, 2009 at 12:43 pm
What would be the "trigger" to generate the file?
e.g. user's choice, time schedule, based on a data constellation within the SQL server etc.?
If it's something SQL Server could "figure out" like using an insert/update trigger on a specific table, a scheduled job or anything like that then no addtl. tool is required.
If it's users choice and there's already a connection to the database I'd simply call a SQL proc that would generate and save the file (including some addtl. handling like delete/move an already existing file, check if someone else running the job at the same time aso.)
Calling a proc could be from almost any app, even Excel could do it.
June 15, 2009 at 12:59 pm
If you just need to extract data server side, I'd look at SSIS. Or DTS in SQL 7/2000. You can use the data export wizard to create the basic package and then mess with it to get what you need.
June 15, 2009 at 1:32 pm
the trigger would be user's choice when connection is met with the sql server. Originally I was thinking of a stored procedure to perform the tasks and generate the file exactly to what you suggested. But I wanted to hear what other advice because I don't know if an ASP application make more sense to this problem or maybe write a client-server application!!! you know I am always looking for simple, lesser in problems and efficient:unsure:
June 15, 2009 at 1:47 pm
I'm still trying to figure out what is ti you are trying to accomplish. The requirements, in my mind, are still extremely vague? Why are you creating a flat file for the application instead of having the application query the data directly from the database?
June 15, 2009 at 2:01 pm
And if it's for a client app, why not client side.
I just thought as well that SSRS might be the simplest way to do this if clients are going to download it.
You want to avoid triggers doing anything outside of SQL Server. Everyone thinks they need things in real time or immediately and then they realize that this can cause problems with your server, and application.
You don't normally want the server to do things for the application.
June 15, 2009 at 2:06 pm
I am not creating a flat file from the application, the application that I am working on will generate the flat file once a button is pressed. A call to the stored procedure will be established and a flat file is generated obviously. This flat file will be stored in a folder on the server side and will then be imported by the user manually into our manufacturing software as an order. the manufacturing software requires it to be in certain format. so I will need to be able to do some formatting from the stored procedure. this is basically how I pictured it but I still thought maybe I can get better ideas out there.;-)
June 15, 2009 at 2:08 pm
Lynn Pettis (6/15/2009)
I'm still trying to figure out what is ti you are trying to accomplish. The requirements, in my mind, are still extremely vague? Why are you creating a flat file for the application instead of having the application query the data directly from the database?
I am not creating a flat file for the application, the application that I am working on will generate the flat file once a button is pressed. A call to the stored procedure will be established and a flat file is generated obviously. This flat file will be stored in a folder on the server side and will then be imported by the user manually into our manufacturing software as an order. the manufacturing software requires it to be in certain format. so I will need to be able to do some formatting from the stored procedure. this is basically how I pictured it but I still thought maybe I can get better ideas out there. [Wink]
June 15, 2009 at 2:39 pm
Based on that, I think you want the application to generate the flat file and then save it to the server. It doesn't need to have SQL Server generate it.
June 15, 2009 at 2:47 pm
If we're talking about a production related (or even production critical) process, I strongly recommend that you think about a process design not related to human interface.
Would you please name your manufacturing software? Maybe there's someone around who'd be able to tell you on how to link this software to SS2K*.
Under the now given background I also don't understand that there would be any user interaction required...
What information would that user add to the process other than starting the job to get the file created?
In order to provide more helpful information I'd like you to provide the following information:
- approx. number of orders / number of "created files" per day
- name and vendor of MES
- brief description of the SQL Server purpose (what kind of business information is stored)
- brief description of information that needs to be transferred
- information added from the user when creating the file
- business rules the user will have to follow on how often or after what event he need's to press the button
From my point of view business crucial steps such as order transmission to an MES should require as little human interaction as possible.
Edit: wording changed
June 15, 2009 at 5:52 pm
Okay, we started with this:
I have been asked to build an client-server application. The server application rests on the sql server and the client rests on user machines. the point of the application code on the server-side is to create a file that is formated with the result of a query against the sql server. I would like to know what advice you can give me on a good way to implement this. thank you
Then to this:
I am dealing with sql server 2000. One of the things that the application must do is to generate a flat file by pulling some data from sql server. The data must reside on the server side. I am not sure how to go about doing this. Either using ASP.NET or vb script or console application or whatever suggestion you might have. But I would love to hear your advices.
Also what would be the best way to implement this. Is it efficient to have users run the executable .exe application on the server or to have a client application connecting to the server application which will then access sql server and generate that file?
Then this:
the trigger would be user's choice when connection is met with the sql server. Originally I was thinking of a stored procedure to perform the tasks and generate the file exactly to what you suggested. But I wanted to hear what other advice because I don't know if an ASP application make more sense to this problem or maybe write a client-server application!!! you know I am always looking for simple, lesser in problems and efficient
And then this:
I am not creating a flat file from the application, the application that I am working on will generate the flat file once a button is pressed. A call to the stored procedure will be established and a flat file is generated obviously. This flat file will be stored in a folder on the server side and will then be imported by the user manually into our manufacturing software as an order. the manufacturing software requires it to be in certain format. so I will need to be able to do some formatting from the stored procedure. this is basically how I pictured it but I still thought maybe I can get better ideas out there.
All of this is still rather vague, but we are slowly learning a little more each time. Unfortunately, I am still not in a position to really help as I still haven't figured out the WHAT you need done well enough to give you a suitable HOW.
June 16, 2009 at 5:38 am
Here is a more detailed process of what is happening:
we have manufacturing orders coming as EDI files. the orders are entered manually by two users at the moment into our manufacturing software which is really old and its called vantage and it is version 6 (not even the .NET version). My task is to eliminate this process and to save time by mapping the EDI file (using a mapping software) to our sql database and from then I have to pull the order information from sql database and create a flat file with the information in the format required by vantage. then a user can just import this flat file into out vantage software manually. I have done the mapping and tested a demo flat file made by me and it went fine. Now it is time to construct this application that will do all of this and I wanted to hear some ideas before I start:-D
the EDI files that are coming are put in a folder called "inbox" before. My original thinking was to let the user know how many there are in the inbox that have not yet been processed. Also to let the user know what type of EDI file. is it a GM order? Chrysler? etc...then once the user click a button it will perform all the above I explained and generate a flat file somewhere in a folder. Then this flat file is imported into vantage and basically an order has been placed.:-)
I hope I explained this more in detail. As fas as how I know if an order is from GM or Chrysler I just simply check the file header information. It tells me there.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply