Best Approach For Pushing Records To MS Access
Mar 8, 2007
All,
I am new to DTS/SSIS and have a couple of questions about using it to solve a problem. We have an application running on SQL Server 2005 where status records are written to a status table. I need to be able to send those records over to a status table in a legacy application running on Access.
Originally, I thought about writing a custom c# stored proc and accessing Access from it and then someone pointed me to DTS/SSIS.
Is there a way to exectute the package based on a trigger event that a row was inserted or updated? If not and I take a scheduled approach (every 3 minutes, etc.) do I have to maintain a column for the records that get processed so they are not picked up again.
In general is using SSIS the approach to take? The overall business requirements are straight forward, but I am not sure if SSIS is overkill for this or not.
Thanks,
Steve
View 1 Replies
ADVERTISEMENT
Aug 16, 2006
I am trying to create a dimension table and I am pulling in data from two tables to create it. I need all records from table A, any records from table B that are not in table A, and I need to use the fields from B for those records that do match. What would be the best way to approach this, merge join + derived columns, union all + aggrigation? Any suggestions?
It seems like it's harder to do this in ssis rather then just doing it in the database.
View 1 Replies
View Related
Aug 7, 2006
Hi all.
I am not a programer but managed to piece together an Access database that is the backbone of my company.
My IT group advised me to move to an SQL server back end and move to a web based front end.
My question. I am very comfortible with Access and modify the databases regularly. What front end is most like Access for modifying forms, reports, macros, etc.
Thanks!
View 2 Replies
View Related
May 16, 2007
Hi,
I have a production SQL2000 in Location A with Database A. I have another SQL2000 in Location B. I want to copy Database A to Location B. Any changes in Database A in Location A gets pushed to Location B.
Can I do this in 2000? TIA
View 3 Replies
View Related
Jun 8, 2007
I am someting of a newbie to SQL Server, but I am an experienced C++/C# programmer.
I have a SQL Server 2005 DB to which are connected 50 or so clients.
The clients are running my software, written in C# and connected to the server by an OleDBConnection.
These connections are long-lived; in some cases they remain for days or more.
Now and again, one of the clients causes a change to the DB (not structural, just normal adds, update and deletes).
But most of the time the clients are just viewing data in the DB.
What I need to happen is that when any item of information changes, the display of that information on any of the clients which are viewing it must be updated quickly.
It doesn't have to be instant, but a delay or more than, say, 5 seconds is unacceptable.
It is like a share-dealing or foreign exchange system (although that is not what it is) where the traders' screens have to have up-to-date information at all times.
Note that the information received by the clients does not have to describe the change in detail, just a hint to the client as to what change has happened so that the client can refresh its views.
I can think of a way of doing this but it is really nasty.
The hints would be recorded in a time-stamped table. Each client would poll every couple of seconds by calling a SP, which would return the hints which have been added since the last time that client polled.
But polling is horrible and I am sure that there must be a better way.
A route that looks promising is InfoMessages. But, reading around the subject, it looks like these cannot be unsolicited and spontaneous; they only arrive on a Connection as a result of some action on that Connection.
I may have missed something, or maybe InfoMessages are not the way to go.
Can anyone offer me any advice, please
TIA,
Paul
View 3 Replies
View Related
Oct 26, 2007
Guys,
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
Any suggestions and inputs would help
Thanks
View 5 Replies
View Related
Oct 6, 2007
I have a SQL CE database that is synching up with a SQL Express database (therefore no replication).
I do a pull down (with RDA) Table XYZ with no rows.
I fill out the rows on the PDA
I push the changes back to SQL Express
What I would like to do now is selectively delete some rows from the CE Database. BUT, I don't want those rows to delete from the SQL Express master database.
How can I do this?
View 8 Replies
View Related
Apr 15, 2004
I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below
dbserver2.Old_Processing: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
dbserver2.Old_Processing: {? = call dbo.sp_replsqlqgetrows (N'DBSERVER', N'Old_Processing', N'Old_Processing')}
[4/15/2004 3:59:47 PM]dbserver2.distribution: exec dbo.sp_helpdistpublisher @publisher = N'DBSERVER'
Connecting to DBSERVER 'DBSERVER.Old_Processing'
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 21)}
SQL Command : <exec [dbo].[sp_MSsync_ins_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 15)}
SQL Command : <exec [dbo].[sp_MSsync_ins_NightlyProcess_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', '2004-04-15 15:56:44.623000000', 'Begin ProcessIQ2KSystem', 'AB14E5D7-C81D-4A39-A8F5-51F1C48227B0', '17E5D98F-EDF0-41D0-9991-97511B850720', 1>
SQL Command : <exec [dbo].[sp_MSsync_upd_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 7)}
SQL Command : <exec [dbo].[sp_MSsync_upd_TheatreProcess_1] N'dbserver2', N'Old_Processing', '072175', 1, '2004-03-19 00:00:00.000', 1, NULL, '79A114D6-FF31-4E37-AC2D-90C0A0114F40', '072175', 1, '2004-03-19 00:00:00.000', 0, NULL, 'AF61A098-44A3-45D7-B25B-E8EA9CD464A1', 0x2800, 1>
Failed while applying queued message to publisher
Disconnecting from DBSERVER 'DBSERVER'
Worker Thread 692 : Task Failed
Disconnecting from dbserver2 'dbserver2'
Processed 3 queued trans, 3 cmds, 0 conflicts
Queue Reader aborting
In the sql server logs I am getting this message:
Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher.
Error: 14151, Severity: 18, State: 1
Any help would be greatly appreciated
View 4 Replies
View Related
May 25, 2006
I am having a small problem.
When I generate a report with a large amount of text data (Paragraphs) the report will not split the text between two pages.
It will move the entire text box to the next page leaving a large amount of space on the previous page.
I tried every control on the page to render this. I tried textboxes, tables, lists, rectangles, subreports, etc..
The data is stored in a SQL table using a text data type (there can be a large amount of data entered into these database.
Any help on this would be great. I like reporting services, but there are just a few bugs.
I am using Reporting Services 2000 with Service Pack 2 and Hotfix
http://support.microsoft.com/?kbid=912424 (That fixed a different PDF rendering issue) and this problem was occuring prior to this hotfix.
Thanks in advance
View 5 Replies
View Related
Jul 2, 2015
I have package which pulls data from db table and creates a excel file extract.The flow is like this - A excel file template sits in the input folder folder for processing .The package starts by dropping excel sheet in the excel(which is clearing any data and columns available) once that is done it has script task which creates a new columns for the sheet and gives a sheet name as well .Then a execute sql task runs and pumps data into a table which serves as a source for the excel extract process .The excel extract process involves pulling data from the table and doing data conversion before it moves it into the oledb destination (excel file on file server).When I run the package I go and see that data is pushed down . I see top rows say 100 are empty and data appears after say 100 rows .
I tried deleting excel file and replacing with new one empty with columns and sheet name only but still it doesnt work?I am trying to understand what is making ssis behave like this and what can I do overcome the problem ?I read on google that we need to bring in file system task will move a template to working directory which is input folder but I dont want it to incorporate that logic as we need to push this package to production ASAP with very minimal change.
View 3 Replies
View Related
Jun 11, 2005
I need to take the records from my MS Access DB and migrate them to SQL. I have both db table structures somewhat mapped out and its only about 43 records or lines. The data in Access is all kept in a single table and in SQL I need to have it populate seperate individual tables.
View 1 Replies
View Related
May 8, 2008
Hi,
I am facing a big problem in data transfer from Access To SQL. Well the problem is i want to transfer nr about 50 lacs record from access to sql. at my side where sql server is complete install i could not find any problem and it transfer all data in 5 to 7 mins. now problem is at client side we not install entire sql but we install sql runtime which is available with installshied. and whenever i start to transfer data there is another exe called DLLHost.exe is run and it ate near about 70% to 75% Memory of system. well i use sql OPENROWSET statement and as soon as that statement fire at machine where entire sql is not install and only runtime install it starts to eat system memory so instead of transfer all data in 5 to 10 min it take more then 6 hrs bcaz dllhost.exe eat memory. is there any better way to transfer this records more fast pls let me know.
pls. help me to get resolve this problem.
thanks for help ,
pls help me.
View 1 Replies
View Related
Aug 17, 2006
Hi,
I have a question to ask. I'm new to SQL Server but have been trying to learn it. I have just installed the SQL Server 2000 SP4 and have a dummy Access database that need to be migrated to SQL Server 2000 for a test. I used the DTS wizard provided by the SQL Server 2000. It was successful - no error appears. I have also successfully done the link tables from Access to SQL Server. Now, my question is how do i add/edit/delete some records in SQL Server via Access. I need your help or guidance on this.
Thank you in advance
View 2 Replies
View Related
Nov 30, 2004
How can I get MS ACCES database records from an sql Stored Procedure. Idea is I have a SQL stored procedure, which wants to calculate a few things using some data from an MS access DB. Is it possible or am I talking against any DB principles?
View 1 Replies
View Related
Jun 24, 2004
Hi, does anyone know how to retrieve mistmatched records across 2 tables.
To clarify, I have table A with 1175 records and Table B with 894 records.
The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc
but that doesn't seem to work.
*desparate*
View 2 Replies
View Related
Jan 25, 2007
Please help I am trying to do an access query using an inner join...I am using an or to seperate the statements after the inner join...Can this not be done? I need a way to pull the records just once and right now it is duplicating them...Any help would be great!
<cfquery name="formWait" datasource="#datasource#">
SELECT distinct demographics.colors, demographics.colors2, formApprasialStateP.rec_ID, formApprasialStateP.keyName,
formApprasialStateP.form_id, formApprasialStateP.teacherID as sid, formApprasialStateP.teacherStatus,
formApprasialStateP.teacherStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.cycleYear as
ty, formApprasialStateP.cycle as tdc, formApprasialStateP.saved, formApprasialStateP.status
FROM formApprasialStateP INNER JOIN demographics ON formApprasialStateP.status = demographics.colors or formapprasialstatep.status=demographics.colors2 where teacherStatus = 2 and appraiserStatus = 1 and teacherID=#id#
</cfquery>
View 7 Replies
View Related
Mar 17, 2014
Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?
Code:
INSERT INTO dbTarget.dbo.tblTarget
(All fields)
SELECT
(All Fields)
FROM dbSource.dbo.tblSource
WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)
View 6 Replies
View Related
Jan 20, 2008
Hi Respected,
I am working in .net, and according my client he want to see all record from table uniquely, and does not want to give order also.
for doing this i did use DISTINCT, but my problem is when i used DISTINCT the query return sorted records that is why, i need guide which will help to make DISTINCT and physical stored record from the table.
thanks.
bay
regards Rajat.
View 1 Replies
View Related
Oct 26, 2007
Guys,
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
Any suggestions and inputs would help
Thanks
View 1 Replies
View Related
Jan 31, 2008
hi,
I want to Display the image records of each employee of having 10 images of each.the number of records are more then one billion.......
please provid me the optimized query that can i used in SP and display in Gridview.
View 7 Replies
View Related
Oct 4, 2007
Running SQL 2005 on a dedicated Dual Xeon 8MB RAM AND 6x 160GB HDDS RAID-5. Front end App Access 2002. I have to main concerns. 1. When users are enter data and saving all of a sudden all users get stuck upon saving and requires to reboot the server to operate again. Th eproblem is that this happens 3 to 5 times a day. Also searching records with one word takes very long time sometimes even minutes, but queries having two or more words come up fast. Also single word queries come up with erratic results, sometimes 200 titles and than a second search comes up with 31,000. *** Can anyone come up with any ideas "off the cuff" on how I canstart to resolve these issues. Can these issues be related?
View 5 Replies
View Related
May 5, 2015
I have two tables. Users and records. I need to select only the users that not has lines recorded in the other table. How could I do that?
Example:
ID| Name| Access 1|Access 2|
----------------------------
1 | Axel  | True     |False  |
2 | Ivan | False    |False  |
3 | Bob | True      |False  |
4 | Sue | False     |False  |
ID| Points| Month| Year|User_1|User_2|
--------------------------------------
1 |Â 2Â Â Â Â |Â Â Â 5 | 2015|Â Â 2Â |Â Â 1Â |
2 |Â 5Â Â Â Â |Â Â Â 5 | 2015|Â Â 2Â |Â Â 1Â |
3 |Â 1Â Â Â Â |Â Â Â 5 | 2015|Â Â 3Â |Â Â 1Â |
Then I want to run a select in the users table, only with the users that hasn't records in the second table.
In the example, the second table has User_1 as the user who receives the points and the User_2 is the user who give the points. Then I would know what user didn't receive 'points' yet.
View 3 Replies
View Related
May 12, 2007
Hi all :
I hope this is an easy one for some of you - this is the scenario :
1) I have an Access Application that has been upsized to SQL
2) We are now replicating it with a second office
3) When we started doing the Merge Replication, the ROWGUID was added to each table
4) When we attempt to add a new record to the table, it allows us to add 3 fields, then the record 'jumps' to the indexed location of the ROWGUID
5) The 'jump' is undesirable as we then have to resort the table by key and finish its input.
5) We have our own primary identity field that SQL seems to be ignoring.
Any help would be really great
View 4 Replies
View Related
Mar 6, 2008
I am trying to filter the records in an Access2003 project form using data from a control in a different form. I define the variable in form1 as RegKeyWord. The record source for form2 ends with WHERE CustomerName Like RegKeyWord. I receive the error Invalid column 'RegKeyWord'
Can anyone help?
View 1 Replies
View Related
May 28, 2008
Hi Friends,
I have created the procedure in sql server 2005 for retriving email addresses from table based on date_expiry and concatinating all email addresses in to @tolist as shown below
Declare @tolist varchar(8000)
set @tolist = ''SELECT @tolist = @tolist + ';' + COALESCE(email, '')
FROM awc_register
WHERE DATEDIFF(day, date_expiry, GETDATE())='3'print @tolist
set @tolist = substring(@tolist, 2, len(@tolist))
and I passed this @tolist to another procedure which should send mails for email addresses present in @tolist variable.
Problem is I need to send the mail to each email address separatley.( not bulkly)
Thanks,
View 1 Replies
View Related
Sep 1, 2015
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as
UserID=1, FName = John,
LName=Abraham and Industry = 2. I have a
Industry table with data as
ID=1 and Name= Sports,
ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
FName =John,
LName =Â Abraham and
Industry=Â Film
How to write query?
View 2 Replies
View Related
Nov 18, 2006
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
View 3 Replies
View Related
Jul 17, 2015
IF EXISTS (SELECT 1 FROM RoleUser WHERE User_Id = 12346 AND Role_Code = 'CRC')
UPDATERoleUser
SETAccess= 1,
Worklist= 0,
Supervisor= 0
WHERERole_Code= 'CRC'
[Code] ....
View 3 Replies
View Related
Mar 30, 2004
Let me see if I can explain this.
I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.
Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.
Step 2
The next tables data needs to be limited from the data retrieved in step 1 (I’d like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.
Step 3
The returned rows here, need to be limited to key values returned from step 2
Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.
What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.
I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.
What is the best approach?
View 2 Replies
View Related
Mar 20, 2006
I've been working on this project, and had it working in MySQL, but it was badly done and couldn't last more than a few hours without growing so large that everything slowed way down. I don't expect anyone to tell me exactly what to do, just please provide an outline of what the best way to approach this in SQL Server 2005 is.
To simplify it, I have one table "Items" and another table "ItemPrices". Items has an id and a name. Each row in ItemPrices has an id for the item, a price and two datestamps (added, last updated).
On average, there's about 15,000 active items, 50% of them have new prices every couple minutes, so I'm looking at what seems like a ton of data being constantly imported. There's probably a good way to do this but I only know the bad way :)
So.. what I want to be able to do is have maybe a stored procedure (?) that takes the item name and price as parameters. (In MySQL I was using "INSERT... ON DUPLICATE KEY UPDATE")
A. If it's a new item name, it will add a row to the Items table and a row to ItemPrices
B. If it's an existing item with the same price as the current price (the most recent price for that item in ItemPrices) it will update the "last updated" date field
C. If it's a new price it will insert a row into ItemPrices for that item
Also, I want historical pricing data, but if I ever release this, 95% of the users will just be looking at current prices. I need the current prices to be very fast to query, in my MySQL version I was using something like this: "SELECT... join on lastupdated=(SELECT Max(lastupdated) FROM ItemPrices ...", after I had 300k price updates querying a list of items took like 15 seconds.. there's got to be a better way? What should I do to make this faster?
Does this make any sense? Hopefully someone can lead me in the right direction. Thank you very much!
View 3 Replies
View Related
May 24, 2008
Hello all.
I use MDX on a cube which provides data about animal population.
The cube contains the keyfigure "ANIMALS" that takes the number of animals.
The cube has a dimension "VERSION" which is used to identify the keyfigure
as a target or an actual value (possible values: "actual" and "target")
The cube has another dimension "ZONE" for the population zones.
Possible values for zones: "A", "B", "C" and "D".
Now I want to create an MDX statement, that gives me a result row like this:
Actual number of animals (as sum of all 4 zones) in column no. 1,
Target number of animals (as sum of all 4 zones) in column no. 2,
Achieved percentage (as actual number / target number * 100) in column no. 3.
Until here my statement works and it looks like this:
WITH MEMBER [VERSION].[achieved] AS '[VERSION].[actual] / [VERSION].[target] * 100'
SELECT {[VERSION].[actual], [VERSION].[target], [VERSION].[achieved]} on COLUMNS
FROM [$MYCUBE]
WHERE ([Measures].[ANIMALS])
It surely is possible that the achieved value for all zones together is equal to or greater than 100%,
while single zones might have an achieved values less than 100%.
In order to account on this, i would like column no. 4 to display one of these words:
"ok" if none of the single zones has an achieved value smaller than 100%,
"warning" if any of the single zones has an achieved value between 96 and 99%,
"alert" if any of the single zones has an achieved value smaller than 95%.
That means, i want e.g. the word "yellow" if the lowest achieved value
of the 4 zones is between 96 and 99. I want to have "red" if the lowest value
is smaller than 95.
I am quite new to MDX and I have struggled quite a long time with this.
I would be grateful for a hint on how i have to modify / enhance my MDX statement.
Regards. Peter.
View 1 Replies
View Related
Sep 27, 2006
I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along.
Source server SQL 2000 database allows NULL values in columns.
Destination Server also SQL 2000 but the database required a value in each column.
So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever.
I believe I should use a Derived Column and an expression ISNULL to accomplish what I want.
Maybe there is a better way. Suggestion and comment appreciated.
Ryan
View 1 Replies
View Related
May 18, 2006
Greetings my friends
I am attempting to solve the following problem using SSIS, actually I am attempting to convert a SQL Server 2000 DTS package in to a SSIS package.
The package does the following :
1) Retrieve the maximum Price_ID (PK) from a PRICE dimension table.
2) Populate a staging table with data coming from a source system where the PRICE_ID > (Price_ID from above)
3) Update the actual DIM table with the new data help in the staging table.
For this task I want to learn the use of the Lookup component which I think is appropriate.
My questions are as follows :
If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?!
I am totally confused... I don't even know where to start with this.
Your help would be appreciated.
Thanks SQL friends.
View 6 Replies
View Related
