| Author |
Topic |
|
anglianthon
Starting Member
18 Posts |
 Posted - 2006-01-12 : 02:26:35
|
Hi guys,This is the first time I ever touched on SQL database. Is there any tutorial or guide online that I can read on. Because at the moment I don't even know where to begin. I don't even know how to create the tables in SQL server. Basically I need to transfer data from MS access tables and save them in the SQL server. Say, hourly basis. But I only want the new data at every hour. So how should I go about the procedure?By the way, is there a limit to the size of the SQL server? or I can have unlimited amount of data?ThxThx a lot!!!! |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-01-12 : 03:13:06
|
| Welcome to SQLTeam. Can I ask what it is that you are trying to achieve by moving the data to SQL in this waysteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
 |
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-12 : 03:45:03
|
| Thank you.This is because I want to keep all the data generated from sybase server.The reason why I want to go from sybase-access is because I want access to accumulate 1-week data so that at the end of the week I can run an access query.and the reason why I want to move the data from access to SQL server is because access database only allows a limit of 2Giga. so eventually I'd need to move the data from access to SQL server. and I reckon to make things neat, I'd like to move the data from access to SQL server in an hourly basis.I hope I answered your curiosity.So any idea how I should go about this procedure?Thx! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-12 : 04:24:02
|
| I am a bit confuse here. You want to move from MS Access to Microsoft SQL Server or Sybase ASE ?Anyway, you can refer to these links on some basic SQLhttp://www.sql-tutorial.net/http://www.firstsql.com/tutor.htm-----------------'KH'Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. |
|
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-12 : 04:32:22
|
| I want to move from MS access to SQL server. |
|
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-12 : 04:40:10
|
| Anyway, at the moment, I'm more lost in how to set up SQL server 2000 to import the data from MS access and to schedule it so that it'll import new data from MS access hourly. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-01-12 : 09:17:40
|
| The reason I asked wasn't pure curiosity. It may be that there is a more efficient way of achieving your objective. You could for example have SQL Server tables linked to an Access database i.e. when the data is entered into the access database it actually goes into the SQL Server. To be honest I am still not clear on why you are using Access at all. If you just want a weeks worth of data at a time you can write a stored procedure or a view to show that. This would be more flexible a solution. Could you give some more detail about what you are trying to achieve?steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2006-01-12 : 13:40:53
|
| I am guessing that you already have a bunch old Access developments and don’t want to have to rebuild them.Note:You can create a SQL backend and still use Access as your front end. With a minimum amount of rebuild.JimUsers <> Logic |
|
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-12 : 19:52:23
|
| yes JimL, you're right. I have developed all the necessary queries in access and hope that I won't need to re-do everything. ok, the whole idea is as such,1. I'm currently already retrieving data from SYBASE server to a local access database. 2. Every week, I'll run queries on this local DB to generate report.3. Since Access only allows a size 2Giga, I now have no choice but to move the data from the local Access DB to an SQL server.4. I need to schedule this procedure (moving data from Access to SQL) in hourly basis.5. The reason I want to do my queries in Access (instead of using stored procedure) is because there are a lot of queries and I don't wish to re-build them again.At the moment, I kind of roughly know how to create a database in SQL server. However, I don't know how to make this hourly transfer (of new data only) from Access to SQL server.I hope now you have an idea of what I wish to achieve. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-12 : 20:23:06
|
| This is a bit strange . . .The data goes from Sybase ASE to Access to SQL Server. Isn't this wasting a lot of time and how about data sync and integrity issue ? Since the souce data is in Sybase ASE, why not just do your queries in Sybase directly ? Rather than moving data from Sybase to Access then to SQL Server. Or alternatively just do your queries in Access accessing data in Sybase ASE ? (I am not familiar with Access but think this should be achievable. Access expert should be able to advice you more on this).FYI, Sybase ASE and MS SQL Server is very similar. Both are using T-SQL. You can make use of scrips from Sybase ASE on SQL Server. It should works (most of the time)-----------------'KH'Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. |
|
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-12 : 21:16:31
|
| ok, I forgot to mention that I'm running all of the procedures (except those involving SQL server) using VBscript. This is because the procedures are automated. I'm no expert at VB nor SQL nor Access. So I'm not sure of all the way to go about doing things with them. However, I found out a way to use VB to automate the importing of data from SYBASE to Access. So I just used it. and so far, everything's been great until Access limit the size to only 2Giga. The reason I don't query straight from SYBASE is the need to log in evertime I query. Somehow it doesn't allow VB to automatically input the user ID and password. (If you know a way, pls do tell). The only way I found possible is to make use of Access to automate the system (i.e. the hourly import of data from SYBASE to Access, the weekly running of queries)Does that explain it better? Again, like I just mentioned, I'm no expert at any of them. So if you know a better way, pls feel free to advise. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-01-13 : 03:21:01
|
| OK I'm certainly a bit clearer on what you are attempting, but I'm afriad I still have some questions/commentsI'm not clear why you want to move the data on an hourly basis but report it on a weekly basis.Are you simply moving the data from Sybase so that you can query it in Access. If so you probably don't need to do that unless you manipulate the data as part of that process. You should be able to link access through to Sybase in the same way as you can to SQL. This would mean you had an access database that showed you all the sybase data and allowed you to run your existing queries and therefore had no need to move any data and no need to worry about the amount of data.As far as querying directly from Sybase is concerned you will have to enter a login name and password for security, the same as you would for SQL server. If you are doing it from code you need to make use of the connection string see [url]http://www.connectionstrings.com/[/url] in access I think you can set this information in the properties of the querysteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-15 : 20:10:59
|
| The reason to move data from Sybase is not only to run queries on them but also to keep a local DB. This is to allow the generation of statistic in the long run. Unfortunately, it's something that must be done and cannot be by-passed.At the moment, I'm trying to kinda set up the SQL server. For e.g., I'm having trouble trying to schedule a job to run an hourly data import from a text file. I think the problem is in the command in the steps. It's because I don't quite know how to write the command. Is the command in SQL or T-SQL? is there any difference in the first place? And does any of you know a good website on the tutorial on either of them (whichever is the one used to write the command)??thkx a lot guys |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-01-16 : 03:25:10
|
| I think you should look at DTS for transferring the data straight from Sybase to SQL server, link the tables in the access d/b to SQL Server and Bob's your uncle. You can schedule the DTS package to run as often as you like. You might want to consider putting the data into a staging table first so that you can do any manipulation you want to it thereHope this helpssteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-16 : 03:54:06
|
| as Steve suggested if you want to move the data from Sybase to Access than to SQL Server, might as well just move direct to SQL Server. Beside DTS, you can also use linked server to copy the data from Sybase ASE to SQL Server. With linked server, you can write queries or SP to do the transfer.I understand that you have existing queries developed under Access and wish to retain that area. My question is, having an additional SQL Server would means additional support and complication to the entire system. The simplest solution that I can think off is to create another Database in Sybase ASE and move the data over to that new database and just point your Access to the new Sybase database. The changes to your access coding should be minimal. Anyway there isn't much different having your data in SQL Server or in Sybase. Both Database Engine would be able to serve your need. My point is, having one less component in the system will make it easier to maintain and transfering data from a Sybase database to another will be piece of cake.-----------------'KH' |
|
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-16 : 21:04:16
|
| thx KH thx Steve!!anyway, if I use the linked server method, does it mean when data is deleted from Sybase, I won't be able to view it in SQL server as well? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-16 : 21:12:55
|
if you don't delete it from Sybase then it is not deleted.From Books OnLinequote:
Configuring Linked ServersA linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages: Remote server access.The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.The ability to address diverse data sources similarly.
With linked server, you can do this in your SQLServer. Like transfering your data from Sybase to the SQLServer. insert into yourtable(col1, col2, ...) select col1, col2, ... from [Sybase].databasename.dbo.tablename Note : Sybase is the named of the linked server setup. See Books Online for details on sp_addlinkedserver for adding a linked server.-----------------'KH' |
|
|
|
anglianthon
Starting Member
18 Posts |
Posted - 2006-01-17 : 20:20:11
|
| Thx a lot KH! I'm going to try to see if it's feasible to link straight from Sybase. The problem is that data in Sybase might be deleted after a period of time. While my objective is to store all data.By the way, is there a limit to the size of database in SQL server? I read up somewhere which stated that it's only up to 2Gig per database. Is there a way to automatically create SQL server database using VBscript??Thank you again! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
creating SQL server database