| Author |
 Topic  |
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
 Posted - 08 March 2007 : 07:25:07
|
Hmmm we seem to be hiting some versioning confusion - there are so many tools out there too, even from MS - technically you can even connect via telnet!
The confusion with windows and mixed/sql authentication is as follows -
>>>Windows authentication is certainly the best bet if you're logged into the same domain as your SQL Server resides - all your actions then run under your ceredentials and priveleges
>>>SQL/Mixed Authentication has to be used if you connect via the internet (unless you're on a VPN. If you don't know what a VPN is, you're not on one) to your database server. This way, you'll be required to enter a username and password.
If you connect via SQL Authentication over your (usually) corporate network (and you're logged into the domain), then SQL Server will see you as a different user from your domain account login - this means you may have different priveleges, etc. One important point here though, is that if you create objects in the database (tables, etc), they have your accountname as a part of their name - for instance sqlserver001.asnitzdb.pdrg.tblmembers - this is what Carlos is referring to in point 10 - problem is a full explanation of the way SQL Server stores data and tables is a bit like 'explaining windows' (there's a near-infinite number of permutations and possibilities, which is why DBA / DB Developer are two different and very specialised jobs!)< |
 |
|
|
cobrachen
Starting Member
48 Posts |
Posted - 27 April 2007 : 10:51:15
|
What if the hosting company does not provide remote connection from EM to the database server and my Access database contains languages other than English. I tried the CSV importing tool they provide but not work with tables contains other encoding. There is no function on the hosting company's SQL server manage panel.
I tried to write my own code but ADO would not accept Recordset update due to Identity column issue and could not enable Identity_Insert from ADO connection. I was able to import archive topics and reply with Recordset update, so I know this would work if I could enable Identity_Insert on the SQL server.
Any suggestions other than write complicate convert code? Thanks.< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 27 April 2007 : 12:38:22
|
I thought you were using MySQL...
Anyway, I wouldn't work with a company that does not support outside access using EM. It's that simple... You can ask them what use is there for the database if they give you no strategy to import the needed data.
The syntax for identity insert is SET IDENTITY INSERT ON tablename. It should work with ADO, AFAIK.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
cobrachen
Starting Member
48 Posts |
Posted - 27 April 2007 : 15:25:44
|
quote:
Originally posted by ruirib
I thought you were using MySQL...
Anyway, I wouldn't work with a company that does not support outside access using EM. It's that simple... You can ask them what use is there for the database if they give you no strategy to import the needed data.
The syntax for identity insert is SET IDENTITY INSERT ON tablename. It should work with ADO, AFAIK.
Due to time constraints and demands, I have to put my forum back on line as soon as possible. I dealt with customer service of GoDaddy yesterday and they insisted it's my side issue. So I gave up for now and use SQL server, which has size limitation to 200 MB (My current Access is 173MB). So I could get more time on the connection issues.
I did google search, MS has a KB described this ADO issues as :you have to use SQL insert into statement.
Since you mentioned, any paid ASP hosting company you know provide remote EM connection and has disk space more than 3 GB?
Thanks.< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
cobrachen
Starting Member
48 Posts |
Posted - 28 April 2007 : 01:04:17
|
I just completed transferring my forum from Access to SQL Server without remote connection capabilities. Also, I have total more than 70K rows of records moved and due to the transaction log space issue, had to move with batches. I wrote my own program to move data since my forum is not in English.
I document some steps and lesson learned with some coding from this time. If you don't mind, I am more than happy to share with all you guys.
Thanks.< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 April 2007 : 05:27:22
|
Each person chooses what he/she thinks is best. SQL Server was meant to be managed from a remote connected client, regardless of what client you plan to use.
Personally I would refuse to even consider a host that wouldn't allow me to properly manage my Db, be that Db MySQL or SQL Server, for that matter. If that's ok with you, fine, you will be the one using the hosting account.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
shlomot
Starting Member
16 Posts |
Posted - 04 May 2007 : 03:24:41
|
Thank you, Carlos Miranda Levy, so very much for your mint production.
Do you carry such pain-killing remedies for other challenges in life? :)< |
Cheers ya' all,
shlomot
http://www.topsynergy.com/ |
Edited by - shlomot on 04 May 2007 03:25:18 |
|
|
|
bwunited
Starting Member
United Kingdom
25 Posts |
Posted - 06 May 2007 : 20:40:55
|
Just like to say I just upgraded from access to SQL and found this step by step guide spot on.
Really simple to follow and defo one for anyone thats not as database orientated as I am to follow.
A suggestion would be to perform a test case on another (smaller) access database first before trying to bring your entire forum over. I was lucky in the fact I had to do another one first but found that in doing so I was able to figure out all the relevant import settings beforehand.
Apart from that, great tutorial :)< |
regards
Brian ]:-) |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 03 August 2007 : 10:44:25
|
I am in the process of moving my >100MB Access DB to Sql Server Express (I know... waaaay overdue).
I have made tons of custom mods over the years, not to mention the fact that I have two different forums running from the same database. This is making Step 9 very difficult.
Are there any programs or scripts that will look at my Access Db tables and mirror them in Sql Server?
--Aaron
< |
DOWNLOAD GREAT NEW MODS HERE |
Edited by - Aaron S. on 03 August 2007 10:44:51 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2007 : 10:54:27
|
The Access upgrade wizard would do that, but I guess it depends on the Office version you have. It's never a good option to let the wizard do it all, specially regarding indexes, but also identity values and default values.
What I'd recommend would be running setup.asp, install all the dbs files you have and then compare each of the tables, to make sure all is correct, manually adding the changes that may be required.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 03 August 2007 : 19:38:37
|
I am migrating from Access 2002 to Sql Server Express 2005.
What is the best way to move the data over?
The difficulty for me so far is that the autonumber fields start over at 1 if I paste in the data from Access.
--Aaron< |
DOWNLOAD GREAT NEW MODS HERE |
Edited by - Aaron S. on 04 August 2007 10:37:24 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 05 August 2007 : 18:24:39
|
Jackpot! 
If you download SQL Express 2005 SP1 and the Toolkit, you can navigate to DTS Wizard using Windows Explorer. It is located here:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"
It works perfectly.
--Aaron
< |
DOWNLOAD GREAT NEW MODS HERE |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 09 August 2007 : 22:52:08
|
To let everyone know what I had to do to upgrade...
1. Use DTS Wizard to move all the MS Access tables to a blank SQL Express database.
2. Move the data from the new database to another SQL Express database that has the clean install tables.
This worked well for me. I was having problems moving from Access to the clean install template, and if I just did step 1 the indexes, autonumbers and keys were not set right.
--Aaron
< |
DOWNLOAD GREAT NEW MODS HERE |
|
|
|
Topic |
|
Topic Locked
