Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.
SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));
I am migrating my database from Access to MS SQL. I successfully migrated most of the Tables but I am going through a hellish time with queries/views
Can somwone tell me how to convert this query from Access to MS SQL: ------------------------------------------------------------------------------------------------------------------- SELECT Manage_Pre_Award.A_ID, Manage_Pre_Award.Event_Name, Manage_Pre_Award.Plan_Date, Manage_Pre_Award.Actual_date, Manage_Pre_Award.Rev_Date, IIf([actual_date] Is Not Null,[actual_date],IIf([rev_date] Is Not Null,[rev_date],IIf([plan_date] Is Not Null,[plan_date],Null))) AS workdate, FROM Manage_Pre_Award WHERE (((Manage_Pre_Award.Rev_Date) Is Not Null)) OR (((Manage_Pre_Award.Actual_date) Is Not Null)) OR (((Manage_Pre_Award.Plan_Date) Is Not Null));
I have already tried this but get: The Query Designer does not support the CASE SQL construct.
SELECT Manage_Pre_Award.A_ID, Manage_Pre_Award.Event_Name, Manage_Pre_Award.Plan_Date, Manage_Pre_Award.Actual_date, Manage_Pre_Award.Rev_Date, CASE WORKDATE WHEN [actual_date] IS NOT NULL THEN [actual_date] WHEN [actual_date] IS NULL AND [rev_date] IS NOT NULL THEN [rev_date] ] ELSE [plan_date] END AS WORKDATE FROM Manage_Pre_Award WHERE (((Manage_Pre_Award.Rev_Date) IS NOT NULL)) OR (((Manage_Pre_Award.Actual_date) IS NOT NULL))OR (((Manage_Pre_Award.Plan_Date) IS NOT NULL));
We have a huge Access database located on a server, but now it is going very slow due to the transport of Mb file to the clients.
We could export the tables to a SQL server and attach the tables to the access clients.
So the question is, will access still treat the tables as it use to or could we expect access SQL querys to request the sql server as normal. Will access process the query on the client side?
We use MS Access 2000 as our database, but run into lots of proplems. So we decided to research the migration to SQL Server.
I used Access Upsizing utitlity to migrate tables and their data to SQL server very easily, and all the relationships, indecies and other information are converted correctly.
Next is to migrate the queries. I found that to be a pain. Is there any tool out there that can do it for me? Any help is greatly appreciated.
Once that's done, then it's the application itself, but I am not worrying that for now.
The company i am working for has been using an Access database for purchasing and inventory. this has become to small/dangerous due to the number of records and frequent 'database needs repair' errors. I am trying to migrate the database to a sql server w/ access front end and am runing into problems with most of the VB proceedures that are built into the database. the most common at the moment is a 'Write Conflict' error saying someone else changed the record i am editing. i know this can't be the case becasue i am working on this in an isolated environment. I have traced it to a VB script that changes a field from 'Entering' to 'Pending' (text field) on the click of the 'new record' button. any ideas?
I am totally new at SQL/SQL express.I have downloaded the sQL express server and wanted to know how i can go about exporting my tables which i created using MS access into SQL express.
Also my application is currently working by using Access and ASP...what will be the changes i would need to incorporate in my code other than the queries and Data connection string.
How would the data connection string in SQL look like if i am using it in my asp code?
Any help or useful links would be a very good help!
Hey guys, I had purchased a program that generates full operational asp.net pages from an access db. I was unfamiliar with the language, and so that was the route I took. After disecting it and studying I've learned a bit and made it work in my environment. However, I now need to migrate everything over to mssql. I got the db portion down and now I'm trying to modify my scripts to point to a sql db rather than an access db. I thought it was going to be relatively simple but it's proving to be quite a challenge for me.Was wondering if anyone could check my work. The original access driven page had the following connection strings.vb page had [ public strConn as string="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("../") & "/db/db.mdb"end class]and in my aspx. pages I hadAccessDataSourceControl1.selectcommand="SELECT * FROM table "AccessDataSourceControl1.ConnectionString = strConn My new page now has the following.I got rid of the .vb page and put this in. Dim myConnection as New SqlConnection("Server=mysqlserver;Database=db;UID=myusername;PWD=my pw;") Const strSQL as String ="SELECT * FROM table" Dim myCommand as New SqlCommand(strSQL, myConnection)
further down I have a wmx data grid. I'm assuming I don't need to touch that but there is a line that has, <wmx:AccessDataSourceControl id="AccessDataSourceControl1" runat="server" ></wmx:AccessDataSourceControl> and I don't know what the equivilant of that is. I know its pointing to the accessdatasoucrcontrol1 that has the select command, but what would be the equivilant ofwmx:accessdatasourcecontrol?Also, is this the way to go? by that i mean, should I just rescript everything? Also, 1) Am I connecting to the sql db properly? 2) some sites say I have to put a connection string in the web.config file, is that true?3) Does any one have any references I can goto, I'm having some trouble finding a good article that lays down exactly what needs to go where in an sql asp.net environment. Thanks guys.
Hi, We are planning to migrate data from access to sqlserver. I have good knowledge on sql server...but never used access to migrate. Can anyone help me with the basics to be kept in mind and methods and errors you get in migrateing data.If possible explain in detail and what is to be done while migrating data.....thank you in advance.And its urgent please.
I have a query in Access that has an iif statement in the select system like so:
SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription, tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%])))) AS [%], 0+round2([Amount]*[%]) AS Allocated, 0+round2([Allocated]*[Salvage%]) AS Salvage FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON ([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND ([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND ([01_qryCommonCost_01].Group = tblPercent_Afe.Group)
But I can't figure out how to nest the iif statement in T-SQL. Any hints?
I need to migrate my access database to SQL2000 Server. I have three tables in my access database. In each table have 3000-6000 records. So how to put all these records in to sql2000 server tables. The table structure in Access database and sql2000 database are same.
Here core work is just insert all these 3000 - 6000 records (rows) from Access database to sql2000 database
Afternoon all,Apologies for cross-posting but as my query covers both Access and SQLServer I thought I'd send it both!I have inherited a project to migrate a fairly complex series ofAccess databases into a single proper SQL database with a web frontend.Its quite a nasty job as people are working on a variety of data setsat several Universities around the world and the data has got verymessy; hence the requirement to put it all on one live web enableddatabase server and provide a web-based front end (particularly assome users insist on using Macs so can't run Access as a front endanyway).If anyone could give me hints on how to perform such a migration or ifanyone knows of any good books or other documents on this I'd begrateful for assistance.Many thanksRich MayMuseum of London
First off, sorry if my cross posting offends anyone. I'm posting thisin Access and SQL Server groups - not sure which one is appropriate.I have a relatively simple ASP.NET/VB.NET application that is nowhitting an Access 2000 database over an intranet. We have to migratethe database to SQL Server 7. My experience with ASP.NET is prettylimited and my experience with SQL Server is nonexistent.We have an MSDN subscription, so I went to the downloads section to getSQL Server 7. Guess what? SQL Server 6.5 is available, as is 2000 and2005, but no SQL Server 7. So my first question is, does anyone know ifit is available for MSDN subscribers?The next question is, does anyone know of a good resource that explainshow to make the transition from Access 2000 to SQL Server 7? I know thedata has to be migrated and the connection between the application andthe database modified, but am really not sure exactly what to first andthe correct way to go about it. The application is a simple productconfigurator. There's not a lot of data and it's not a very complexdatabase.Thanks in advance. If you'd like, please copy responses tolcifers(AT)yahoo.com (AT) = @TIA.Cheers.- Luther
I tried various ways to migrate an access 2003 database with 1 table to sql Express 2005 Edition without success, I can only find the resulted sql database in Sql Server Express Edition 2005 without data. Please advise if any help.
I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.
Hi all, We have access database with lots of data in it. I want to move all the dat to the new database which has completely differenet database structure (e.g different tables, different rows). What I was thinking is that there are 2 ways. one is manually, which will take long tome probably, and the other is to rum queries that will move the data from access to the new sql express. The question is how to that. I know that I used onw CSV files exported for ms access, but now the queries will be much more complex and I will use more then one table for te query. How can I query from 2 different databases, which from one I read data(access), and from the other I write data (express)? Thanks alot.
Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin
I cannot get the SQL Server Upgrade Wizard to work!!
I have one machine with NT 4 (SP4) and SQL 6.5 and another machine with NT 4 (SP4) and SQL 7.0 (new install, never had SQL 6.5 one it). I want to upgrade a database to 7.0.
Each machine can 'see' the other via shares and even trying to register a SQL database in each other machines Enterprize manager (although that fails because they are different versions of SQL) Using the Upgrade Wizard, on the third screen, where you specify the Server Name for the export server, it even has the other server's name in the dropdown list {I never even put it in}. And yet, after I set the export server name and put in the database SA account password and then put in the SA account password for the local server (the one that is running the Upgrade Wizard). After I select next, I get a message that states... 'Unable to connect to Export Server. Please verify that you are an NT administrator on that machine.'
These are standalone servers. Not BDC's. I am logged into each machine as the administrator. I have tried adjusting the SQL services to allow logging as the System Account and the administrator account and a whole bunch of options.... but I can't get it to work. HELP!!! This shouldn't be this hard.... should it???
I run SQL 6.5 on an NT 4.0 platform in a client/server environment with Windows 95 as the clients. I use VB 6.0 as my user interface with Access 97 utilizing DAO (yes, still DAO) for the ODBC connection to the SQL server. When I install SQL 7.0 and move my data to it, can I refresh my links in Access 97 from ODBC to OLE DB and automatically take advantage of the new connection, or do I have to rewrite my queries to take use OLE DB rather than DAO? Thanks for any suggestions and help.
I currently have a MS Access database that will eventually be migrated to MS SQL Server. There won't be an application now, just the database and the reports. My doubts are on how easy this transition will be. 1 - After I build all the database with tables and relationships, is there a tool that helps me to import data? 2 - Is there a way to insert the data on the database other than writing the SQL codes, I mean maybe creating some 'forms'? 3 - Once the data is in, it there a way for me to create report on the SQL Server itself or do I need a report generator like Cristal Reports for example; 4 - If yes, then how easy it is to integrate them; 5 - Is it complicated to generate these reports or it is pretty much the same interface as in Access or Delphi?
I have an ms sql 2000 db which I am migrating from one shared environment to another. How specifcally can I do this?
I have a copy of enterprise but not a full version of SQL on my local desktop. The hosting company from which I am moving has placed the db as a zip file on the root of my site via ftp. I have unzipped it and it appears as a backup copy (.bak).
I appreciate any detail steps, I assume via enterprise, that anyone can give me. Thank you.
I am very new to SQL so please bear with me. I have an SQL 2000 server running on Windows 2000 server in an NT4.0 Domain. We are planning to migrate (not upgrade!) everything to a new WIndows 2003 AD environment using ADMT. I wanted to know if there are any issues with the migration of an SQL server if we are to move forward with this strategy. I have heard that an SQL database keeps domain related information within its database and wanted to know if this is true.
Hi, I need to migrate db,Im planning to use attach & detach option.Is there any cons in migrating the databases(32 bit,not clustered ,sql 2005) to databases(64 bit,clustered,sql 2005).What are the things do i need to take care of?what are the things do i need to do after the migration?
I've developed a website and it's hosted by a hosting company. Lately i've registered a new domain with a new virtual hosting account. I've transfered my web files succesfully. But migrating the sql server failed for a couple of times now. My database containes many tables with many products. My hosting provides doesn't give me the opportuniy to restore my backup which i've backuped from my old server. First I tried to use the import and export tool of sql server 2005. This was very easy and it succeeded. But this tool only migrates the tables and the views. All the common stored procedures of asp.net membership are not exported. So I can't login at the moment. I've tried to generate a sql script with all the stored procedures with sql server management studio. But If i want to run this script on my new db, it fails with various errors. I tried to use the aspnet_regsql tool to create all the membership tables and stored procedures. If this succeeds, I can import and export my old db to the new one. But i'm getting a error while trying to use the aspnet_regsql tool. The error number is 290 and the error is: How can I fix this? Er is een fout opgetreden bij het uitvoeren van het SQL-bestand InstallMembership.sql. Het SQL-foutnummer is 290 en het SqlException-bericht is: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal".----------------------------------------Details van fout----------------------------------------SQL Server: Database: [samet]Geladen SQL-bestand:InstallMembership.sqlOpdrachten mislukt:/*************************************************************//*************************************************************//*************************************************************/DECLARE @ver intDECLARE @version nchar(100)DECLARE @dot intDECLARE @hyphen intDECLARE @SqlToExec nchar(400)SELECT @ver = 8SELECT @version = @@VersionSELECT @hyphen = CHARINDEX(N' - ', @version)IF (NOT(@hyphen IS NULL) AND @hyphen > 0)BEGIN SELECT @hyphen = @hyphen + 3 SELECT @dot = CHARINDEX(N'.', @version, @hyphen) IF (NOT(@dot IS NULL) AND @dot > @hyphen) BEGIN SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen) SELECT @ver = CONVERT(int, @version) ENDEND/*************************************************************/IF (@ver >= 8) EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000/*************************************************************//*************************************************************/IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_CreateUser') AND (type = 'P')))DROP PROCEDURE dbo.aspnet_Membership_CreateUserSQL-uitzondering:System.Data.SqlClient.SqlException: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal". bij System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) bij System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) bij System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) bij System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) bij System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) bij System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) bij System.Data.SqlClient.SqlCommand.ExecuteNonQuery() bij System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
We are looking to migrate an entire server with several databases which collectively contain every user in the company. Obviously, I would prefer to directly migrate the logins without having to make everyone reset their own passwords. Anyone know of a way to do this?
I tried dts'ing the syslogins table but it fails due to password collumn being read only.
Hello! We are planning migrating to different server. I know the answers how to migrate all jobs and DTS packages. But the only question I have is "How to migrate logins and passwords?"
What is the best way to transfer logins? Can't you just export syslogins table into text file and then import it into the new server?
Has anybody been successful migrating userids from one server to another server using SQL7.0? If so, is anyone willing to please provide any sample scripts.
HI all, Could any one pls suggest me, how i have to migrate to sql server 7.0 from db2. Pls also recommend some sites where will i get the information regarding this migration. Thank you all.
When using the script to transfer logins and passwords from one server to another. After doing so... Does the logins and passwords still appear on the server transfering from??? I am doing a parallel install and need to keep the production database logins and passwords still intact. Thanks...
I did a detach on each database/logs. Moved to the exact location on the new server.
This is long post but I really want some feedback on this urgently so please hang in there!
I am migrating a SQL Server over to a new server. I have done the following and although everything appears to be working like a charm I would like to know if there are any gotchas that I have not considered.
These are the steps I used to 'move' the master db.
/* I restored all the users databases onto the new sesrver from backups of the old (file copies and attaches would also do).
I restored a db named masterbak from a backup of the master on the old server. And again into a second db called masterbak2. I then detach masterbak2. I need to use those files later on.
So at this point I now have all the user databases that the old server had but I dont have logins, extended stored proc defs, and any user objects that were created in the old master.
Now I want to put all those missing things into the new master db. These are the steps that I used to achieve this.
*/
-- where doing open heart on sys tables so need this turned on
EXEC sp_configure 'allow updates' , 1 reconfigure WITH OVERRIDE
-- remove all database defs from the copy of the old master delete masterbak..sysdatabases -- and load it with all the database defs from the new master INSERT INTO [masterbak].[dbo].[sysdatabases]([name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename]) SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename] FROM [master].[dbo].[sysdatabases]
-- SYSSERVERS -- remove all the servers defined in the copy of the old master delete masterbak..sysservers -- replacing them with those that are in the new master INSERT INTO [masterbak].[dbo].[sysservers]([srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout]) SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout]FROM [master].[dbo].[sysservers]
-- sysfiles1 -- remove the old delete masterbak..sysfiles1 -- replace with new insert masterbak..sysfiles1 select * From sysfiles1
-- sysdevices -- remove the old delete masterbak..sysdevices -- replace with new insert masterbak..sysdevices select * From sysdevices
-- sysconfigures -- remove the old delete masterbak..sysconfigures -- replace with new insert masterbak..sysconfigures select * From sysconfigures
-- sysaltfiles -- remove the old delete masterbak..sysaltfiles -- replace with new insert masterbak..sysaltfiles select * From sysaltfiles
-- were done -- turn off sys updates EXEC sp_configure 'allow updates' , 0 reconfigure WITH OVERRIDE
/*
this leaves syslogins intact sysmessages intact sysoledbusers intact sysremotelogins intact (no records in mine anyway) all other system tables are left intact as we want to import all the objects into the new server */
/*
now stop the SQL server instance; rename the physical files master.mdf and mastlog.ldf to masterORIG.mdf and mastlogOrig.ldf rename masterbak.mdf and mastlogbak.ldf to master.mdf and mastlog.ldf. rename masterbak2.mdf and mastlogbak2.ldf to masterbak.mdf and mastlogbak.ldf. I wanted to preserve the new master files so didnt rename to masterbak but I suppose you could.
Start up SQL server and everything should be fine.
Configuration settings are as expected, databases started up ok and file locations are ok, logins both SQL and NT are ok, all master user objects are present. User and login SIDs match.
A linked server access that was setup failed but after redoing security on the linked server it was ok? Sysoledbusers holds the security for this but not sure about this one.
Important: You need to check sql log for any errors. It should be clean. If you have auto start procs that reference reg keys that are not on the new server you with be told which key is missing and you can export key from old server and import into new server. Dlls of course will need to come accross but again error messages will indicate the dll that is missing and its path.
So we now have a server that starts up clean but its of a different name of the original of course and since we have a myarid of external servers etc that depend on the server name we are going to keep the old name as well.
So now we change the server name, requires reboot, start up QA and logging into to each 'new' instance name, run the following -- 'SQLSERV02 -> SQLSERV01' sp_helpserver sp_dropserver 'SQLSERV02' sp_addserver 'SQLSERV01', local
and that should be that.
you might get this :
-- Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44 -- There are still remote logins for the server 'SQLSERV02'.
sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE
-- have a look at it select srvid from master.dbo.sysservers where srvname = 'SQLSERV02' select * from master.dbo.sysxlogins where srvid = 0 and not(ishqoutmap = 1 and xstatus&192 = 192 and sid is null and name is null and password is null)
-- get rid of it
delete master.dbo.sysxlogins where srvid = 0 and not(ishqoutmap = 1 and xstatus&192 = 192 and sid is null and name is null and password is null)
sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE
My last step is to get all those jobs and log shipping over to the new server. And I have not yet done this so am not sure if any further open heart is required.
But the theory is that I just need to restore an msdb taken from the old server.
Naturally you will need to put in place the same file path structure on the new server for those scripts / jobs that create files on disk - such as backup files.
I will find out about msdb soon enough as this is going down tonight.
Can anyone see any obvious gotchas in this method?
Hello, all. I need to migrate a visual Foxpro database to SQL server. Is there a way I can import the visual Foxpro data files into Enterprise manager?
