[Show all top banners]

STUPIDA
Replies to this thread:

More by STUPIDA
What people are reading
Subscribers
Subscribers
[Total Subscribers 1]

ashokpahadi09
:: Subscribe
Back to: Computer/IT Refresh page to view new replies
 SQL SERVER & Access Help/ IT
[VIEWED 3865 TIMES]
SAVE! for ease of future access.
Posted on 05-05-08 2:17 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Hello to ALL IT people Out THERE !!!!!

 

MAy be in another couple of weeks , my IT group is gonna assign me a new project.

At presnt-- A small number of people uses Microsoft ACcess 2000 as their front end application to connect to SQL SERVER 2000. The Access is design with some tab buttons , which uses query to get data from sql server. They aslo use Import Export wizard to load data manually from excel to sql server 2000. and then move and archive those files. They say sometimes the file comes late and also format is not consistent.

Need FOR--- We are moving our database to SQL SERVER 2005. I think i can handle this part. ALso we want to get rid of Access as front end, becoz its slower. We are getting over the network to get data. May be write a small front end apllication in VB and connect to our SQL SERVER 2005 database. The store procedures and query used by access are also not optimized for performance.

I was thinking of replacing their manual load by SSIS packages, but according to them its hard becoz we don't know that what time  files will come. also sometimes format of excel file that we receive everyday varies..

I am not good at coding , but i can get help.

SO everyone out there plz help me to approach the problem, and also how can i sound techy and ready for this when i approach with this to my group on thursday,

Thanks

Last edited: 05-May-08 02:28 PM

 
Posted on 05-05-08 3:01 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Do you know the time frame  when the files come? Also, are you trying to automate the job for the load or it is an on demand approach?  I understand what you are trying to do but just wanted to know a little bit in depth so that i might be able to help you on this.

If you do not mind, are you working with Wells Fargo?

Thanks

 


 
Posted on 05-05-08 3:09 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

No I am not.

I am not so much worried about the load part as of rt now. These group knows how to use Export and Import Wizard from Query Analyzer to load data from Excel. The problem is somtimes the format of the excel file (header name, and few things change). Also we don't know exact time when this file get downloads from FTP site. They chacek constantly to see files on folder, and oncethey see the new file, they proceed with manual load.

They are using Access as a front end application to connect to Sql serevr. All teh databases are no SQL SERVER and they use queries to pull data from this databases.

I have few more weeks before i move with these project, But these thursday i ahave meeting, so i just need to sound techy and ready for this project.

I know I can use SSIS packages for load, and also use File Wtacher Task in SSIS for files to download, but these is problem becoz we have to leave our package running 24/7 to use these file Watchet task

 

Thanks acbe for ur response


 
Posted on 05-05-08 5:20 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

I dont know on which programming language you work to make the front end . But for both C# and VB.net the following webcast series of microsoft helps a lot about connecting data with VB or C# form in front end and sql server in back end.

this was quite helpful when I migrated to VB + SQl, rather than access.

http://www.msdnwebcastportal.com/DesktopDefault.aspx?tabindex=5&tabid=24

Last edited: 05-May-08 05:21 PM

 
Posted on 05-05-08 6:36 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Thanks SHivaNagar!!!!! really appreciate that link.

The problem is I am not a code guy. I really don't like or rather best is i don't know coding. I work with SQL . I was thinking -- try to build a small GUI in VB and i think i have 9 or 10 queries and SPs to connect to SQL server.

If u have more link or give me some suggestions to where i can start learning from scrach. I think the tough part will be to build the front end application for me rather than connecting to database.

Tahnks againa.


 
Posted on 05-05-08 8:56 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 
 
Posted on 05-05-08 11:22 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

The excel file not being in a correct format is a big bummer. In sap b1 for export they provide u with a template and you have to fill the data in the excel use their sql tool to insert data. I like the idea but its always a big problem copying data form one excel to another sometime you miss something and everthing screws up so i made my own tool in c# it loads up excel file and then you get to define which column map to which one in the template and sumbit buttons excutes it for appending it. you can add the rows to the table as a transaction so if something fails it rolls-over or u can add one row at a time(which take lots a time) and handle the insert error to output a file with all your problem rows. If i have some more time in future i will making a simulator which will tell you what rows has problems before you excute it.

pointer to make this tool. reading excel file is always slower than csv or a tab separated file. Use Datatable, use background worker process while loading files and while doing all the heavy process, otherwise ur GUI will hang. dont output the whole datatable, mine crashed on 65000 rows, create a preview of just 10 to 50 rows just to check the mapping.

if you are just making reports then try the sql reporting services or just make sprocs and load to one of the .net datagrid


 
Posted on 05-06-08 12:09 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

ThAnks

Cyberdude & Shivanagar.

I guess we have very few people in Sajha that are related to IT or Computer STuffs.

Anyway, Lets make Sajha also one of the IT - computer related forum site.

 


 
Posted on 05-06-08 12:59 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Can they use enterprise manager as the front end? If you make stored procedures that they need, they just have to execute the procedure and they can visualize the data.

I think you can solve the import part using SSIS in 2005. You can define a conditional statement and check for valid data.
As per the triggering the import process, you can do this from scheduler to run every 30mins or so.

 
Posted on 05-06-08 3:23 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Thanks dev/null,

What I was thinking was- --- Use WMI Event Wtacher Task in SSIS to look for files in folder, and then connect this task with my sequence conatiner, where rest of the logic fro load will be build. The problem is we don't know even estimate time that these files will be FTP ed. SO this means i have to execute my package for a very long period of time just to watch for files to come. The biggest issue---the excel file we receive doesn't follow any standard formats. So when we make this excel connection to load to SQL server , it gives all kinds of errors. ( what we are working on rt now is telling our vendor to send those files everyday in standaradized format).

the other issue with myself i cannot code. so i am worried about these GUI that i will build to connect to my SQL SERVER.

I am confused---enetrprise manager?.....we are moving from SQL 2000 to SQL 2005 for database. Now these group of people don't know lot on SQL server, so we have to build something like inetrface where they can enter some value and serach the data from SQL. Now they are using Access 2000 as a front end application.


 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 7 days
Recommended Popular Threads Controvertial Threads
मन भित्र को पत्रै पत्र!
TPS Work Permit/How long your took?
Does the 180 day auto extension apply for TPS?
Travelling to Nepal - TPS AP- PASSPORT
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters