Move Data From Access To Sql Server 7.0 Using Sql Queries
Jan 22, 2008
Hi All,
I hope I am at the right place to post this question:
How can I move the tables with its data from Access to SQL Server 7.0 using SQL queries. Later I might put everything in the store procedure and have a third party running the store-procedure to do it by itself.
Please advice what should I do first? Do I create new tables in the s SQL Server 7 or can I move the data and create the tables at the same time?..Thank you.
View 1 Replies
ADVERTISEMENT
Apr 7, 2004
I'm attempting to move data from an Oracle table to an Access table using an Oracle stored procedure in DTS. The problem is that you can't pass parameters to an Oracle stored proc when its called in a data pump task. Is there a way to pass global variables into an Oracle stored procedure which retrieves data and moves it to an Access database? Maybe in an Active X task? We are required to use an Oracle stored procedure by our DBA's or else I'd just pass the variables into a SQL string and use it in the data pump task.
View 1 Replies
View Related
Jan 12, 2004
Hi,
My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.
The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.
For example, the following code is taken from Access :
SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID
FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID
GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID
ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;
But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?
Thanks
View 5 Replies
View Related
Mar 13, 2013
I am trying to get the same results in SQL server with a query as it works in access
There are two tables:
TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel
Fields in Table:
[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]
[code]...
How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .
View 4 Replies
View Related
Apr 6, 2000
Does anyone out there know of any utility that can Migrate Queries to SQL Server 7. I have over 400 of them and Dread doing them all by Hand.
Thanks,
-Ray
View 2 Replies
View Related
Nov 2, 2000
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?
View 2 Replies
View Related
Dec 14, 2007
hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
thanks
View 5 Replies
View Related
Jun 27, 2001
Hi,
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton
(IT Support)
Durham Aged Mineworkers Homes Association
View 2 Replies
View Related
Feb 23, 2007
Guys,
We are currently moving our environment. I was told that we need to copy all the stuff over. More specifically, we want to move everything except the data, as our data is dynamic and will fill up in a few days by itself.
What is the best way to move everything over from one server instance to another?
My current approach is the following:
1. Create the file groups we have on our current server on the new server
2. Script out all databases with stored procedures, functions, views, priviliges, indexes ...
3. Script out all the jobs
4. Script out all the dts packages (or rather save each in a file)
5. Load all scripts into the new sql server
6. Re-create user accounts (can these be scripted out also and then loaded?)
Am I missing something or is there a wiser alternative?
Thanks a lot
View 3 Replies
View Related
Nov 26, 2007
I have staged my tables in a database which is in the same server as the destination database and they are on sql server 2005.
Now I need to push the data from the staged table to destination.
Which is the best approach in ssis ?
1) using execute sql task to a call stored procedure to push the data to a different database using server.dbo.table name from the stored procedure.
or
2) using dataflows to call a stored procedure and map source and destination.
View 13 Replies
View Related
Jul 29, 2014
I have a table attendance_details in both database DB1 and DB2, i need to move 01/7/14 and 02/7/14 records from db1 to db2, My table contains
employee_no INT,
date_of_attendance datetime,
present varchar(20),
shift_type VARCHAR(20),
marked_by VARCHAR(50)
View 4 Replies
View Related
Nov 19, 2007
Hi,
When i try to use the Microsoft OLE-DB Drive for oracle in import/Export Wizard, it gives me message that Oracle networking driver is missing. Contact Oracle for this information.
Any idea to further move on this. I am wondering, why SQL Server 2005 does not have that driver.
Thanks,
View 1 Replies
View Related
Aug 4, 2015
I need to move data from PDW Server to Sql Server through SSIS.
which component should I used under Data flow for PDW part !
View 2 Replies
View Related
Jun 18, 2015
I have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig db
Copying a backup file is not an option either, and Mailing it on media make it a significant delay.
View 2 Replies
View Related
Feb 16, 2007
Is there any way to import a VistaDB database into SQL Server? From what I can tell, there is not...
Please advise.
Thanks,
MC
View 1 Replies
View Related
Oct 18, 2014
I would like to move the data / findings into a perm table?The reason for this is so clients can connect to the table using excel. I have another stored procedure which is setup in this process already and it works well. I basically have the stored procedure setup on a task to run early in the morning so when clients get up they connect and get their data.
USE [MMAUDIT]
GO
CREATE PROCEDURE [dbo].[MMA_AUDIT_QUESTIONS_SUMM_STG] AS
BEGIN
[code]....
View 4 Replies
View Related
Nov 10, 2014
I'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.
The specification I was given is basically
Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).
I can't use a linked server unfortunately.
I wonder if it possible to move data from tables on a linked server to a "normal database"? What am I doing wrong?
View 4 Replies
View Related
Jun 30, 2007
hello
i am trying to move from Access db to Sql.s
could you please what is rwong in the sql.s syntax:
------Access SP:----------
PARAMETERS [@Date_M_Y] DateTime, [@BIZID] Long;
SELECT SUM(MINUTE(Time_)) AS MM, SUM(HOUR(Time_)) AS HH, COUNT(Date_) AS SHIFTS, SUM(Money_Sum) AS MONEY_TOTAL, AVG(Money_Sum) AS AVG_MONEY_PER_SHIFT
FROM TblTimeSum
WHERE MONTH(Date_)=MONTH([@Date_M_Y]) And YEAR(Date_)=YEAR([@Date_M_Y]) And BIZID=[@BIZID];
----- SQL.S----
CREATE PROCEDURE SP_SUM
@Date_M_Y smalldatetime,
@BIZID int
AS
BEGIN
SELECT SUM(MINUTE(Time_)) AS MM, SUM(HOUR(Time_)) AS HH, COUNT(Date_) AS SHIFTS, SUM(Money_Sum) AS MONEY_TOTAL, AVG(Money_Sum) AS AVG_MONEY_PER_SHIFT
FROM TblTimeSum
WHERE MONTH(Date_)=MONTH([@Date_M_Y]) And YEAR(Date_)=YEAR([@Date_M_Y]) And BIZID=[@BIZID]
END
GO
thanks
View 5 Replies
View Related
Oct 15, 2015
I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.
Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).
View 0 Replies
View Related
Oct 9, 2013
I'm trying to move specific data from three linked tables on a source database to three tables on a destination database by generating dynamic SQL INSERT scripts but am getting stuck on the last set of INSERT statements. I don't think I can use SSIS because the source autonumber fields may already exist on the destination side but I could be wrong.
For simplicity, Table 1 (T1) has one autonumber key of PK1 as well as other fields (A1, B1, etc.).
Table 2 (T2) has one autonumber key of PK2 and a foreign key (FK1) that links back to PK1 as well as other fields (A2, B2, etc.).
Table 3 (T3) has one autonumber key of PK3 and a foreign key (FK2) that links back to PK2 as well as other fields (A3, B3, etc.).
Like this:
T1: PK1, A1, B1, etc.
T2: PK2, FK1, A2, B2, etc.
T3: PK3, FK2, A3, B3, etc.
So, I'm able to query the source database T1 to generate my dynamic SQL INSERT statements that will be run on the destination side. I'm also able to generate my dynamic SQL statements to insert into T2 but when I get to T3 I currently am stuck figuring out how to insert because the destination side is unable to match it's FK2 to the just inserted PK2. It throws the below error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
View 3 Replies
View Related
Aug 4, 2015
Background information: SQL 2014 Ent. highly volatile OLTP environment. We generate 10 - 12 GB compressed transaction log backup files every 15 minutes.
Currently - we have two-node A/P cluster residing on flash array. Need to leverage AlwaysOn to offload processing. Replica server with have Flash storage. Replica node has same CPU and memory footprint. 10GB connection between nodes. Anyone generating such large transaction log for 15/30 minute time period?
View 0 Replies
View Related
May 3, 2007
Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
View 4 Replies
View Related
Jun 10, 2015
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
View 6 Replies
View Related
Oct 30, 2007
Hi guys,
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
Thanks,
CP
View 3 Replies
View Related
Mar 23, 2006
Hi
I am tearing my hair out. I have tried upgrading form access to sql express. I have built the database and written all of my stored procedures. I think I am having problems with connecting to the server. I am storing my connection in the web.config. I am trying to open the datbase and add populate a reader, but i keep getting the error:
Exception Details:
System.Data.OleDb.OleDbException: No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).
Any help would be much appreciated. Code below:
<connectionStrings>
<add name="SQLProducts" connectionString="Provider=SQLOLEDB.1;Server=(local)SQLExpress;Database=laptopsqlexpress.ALLBUTYOUDB;Integrated Security=yes;"/>
</connectionStrings>
Function GetCategoryProductNav(ByVal strCategoryID As String) As OleDbDataReader
Dim objReader As OleDbDataReader
Dim objCmd As New OleDbCommand("spGetCatProductNav", objConn)
objCmd.CommandType = CommandType.StoredProcedure
Dim objParam As OleDbParameter
objParam = New OleDbParameter("@CategoryID", OleDbType.Integer)
objParam.Value = strCategoryID
objCmd.Parameters.Add(objParam)
Try
objConn.Open()
objReader = objCmd.ExecuteReader
Catch ex As Exception
Throw ex
End Try
Return objReader
End Function
View 1 Replies
View Related
Oct 7, 2015
One of my current responsibilities is to export data to 3rd party vendors. Each export can contain many csv files. The exports are all different in terms of what data is being sent.
The way I have it currently setup is each file that needs to be created is a view. An SSIS package gets the data from the view, writes to CSV, and then sftp to 3rd party vendor. This seemed like a good idea at first because the columns are static but the calculations might change. So all I have to do is ALTER VIEW and I don't have to change anything in the package.
Is there a better way of doing this? I was curious to see what other people are doing. What makes it challenging is that all the exports are so different. If they were similar I could have created generic views that cover all the exports instead of each export having its own view. Eventually I'm going to have 100's of views.
View 9 Replies
View Related
Apr 22, 2015
When i am trying to start our hospital software based on SQL server 2000, it shows Following Error.Search Condition is not valid, (DBNETLIB) Connection Open (connect()). SQL server does not exist or excess denied. Due to Fetch data.I run our software in Windows 8.1, while it smothly runs in previous version of Windows XP and 7.
View 2 Replies
View Related
Mar 21, 2000
Hello all,
Does anyone know of a utility to migrate Access 97 Queries to SQL Server 7?
I've over 400 of them and I am trying not to have to do them by hand.
Also I tried to use to migration wizard from MS instead of the DTS and all I got was Error after error. 'Invalid Date/Time', 'Error with YES/NO field' and 'Timed out' were the most common. In the end only 4 tables out of 89 were migrated.
Any suggestions would be greatly Appreciated.
-Ray
View 2 Replies
View Related
Aug 6, 2005
Hello~
I am wondering if anyone can recommend a basic, "How To" article, column or book for someone who has never worked with Access but is familiar with DB programming.
I need to know how to submit queries, and print reports.
Any help is appreciated.
Thank you!
Shelli
View 2 Replies
View Related
Oct 15, 2007
I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)
SELECT tblCalls.*
FROM tblCalls
WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));
SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime
FROM qryFlyTippingStatsSummary1
GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0)
ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);
View 2 Replies
View Related
Nov 7, 2007
I am using SQL2005 (Installed on VISTA). It was running smoothly until today....for some reason I cannot access any tables in queries that I try to run in Management Studio. I can open the tables and see the information, but all queries I try to run result in the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.BEYLEVEL16'.
I tried to run simple select queries on other tables but I get the same error message. Do not understand, eveything was working fine yesterday.
Any suggestions greatly appreciated.
Thanks
View 9 Replies
View Related
Oct 6, 2006
Hi,
I'm working on an electronic register which people use to sign in and out of a building. It uses 3 tables, one to store personal information (Name & Visitor Number), one to store what time they come in, and one to store what time they leave. Visitor Number is the primary key in the Visitor_Info table, and is used as a foreign key in the other 2 tables.
I've been asked to put in an 'emergency print' function where, when you click the print button, it compares the data in the time in database and the time out database and prints a list of people still in the building.
So, what I'm trying to do is create a SQL query that will give me this information. Having never used SQL before, it's a little tricky. I've spent the last few days looking at tutorials on the net and learning about queries and this is what I've come up with:
Select Time_In_Table.Visitor_Number
Not Union
Select Time_Out_Table.Visitor_Number
If I'm right (and I'm probably not), this will give me the people who are in the time in table, but not in the time out table.
Can someone please help me by letting me know what I'm doing wrong in the statement above, and also, advise me as to where it goes since none of the tutorials I've found cover where to put the statement in my programming? At a guess, I'd say it goes in the print button click function.
View 7 Replies
View Related
May 15, 2015
I am using SQL Server report 2008/2012 (SSRS) and my report viewer contains body content with 3 Row groups.
While printing the report, data print with blank space and move to continue data to next page.
Departure flight : 70 rows
First Page : 42 rows printed
Second Page : 23 rows printed [ Supposed to be print 28 , if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Third Page : 5 rows printed
Departure flight : 42 rows
First Page : 42 rows printed [Report max. record allowed to print 42 rows so if total record is 42 then print perfectly ]
Departure flight : 26 rows
First Page : 23 rows printed [Supposed to be print 26, if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Second Page : 3 rows printed
View 3 Replies
View Related
