External Notification Of Database Updates
Jun 12, 2006
MS-SQL 2005, C# 2.0
I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.
Transfering the data (Web Services, Remoting) is not a problem.
What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).
Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?
Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...
There are some BLOB's involved (1-2 page PDF's), if that makes a difference.
I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.
I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)
Thanks
Rob.
View 1 Replies
ADVERTISEMENT
Nov 7, 2006
I have been going over the samples from PDC 2005 and trying to run the External Activator program in section 2 'CLR Services'. After sending a message to the Inventory Queue, I see that 'dm_broker_queue_monitors' table has a row in the table, with the state column shown as 'NOTIFIED', but the ExternalActivator program never gets notified of the event and therefore never launches the 'InventoryServer' program.
Querying the [ExternalActivatorQueue] shows no rows.
Querying the [Inventory Queue] shows messages are waiting to be received.
Executing the 'activator' command in the ExternalActviator program shows
Notification service 'ExternalActivator' on SQL Server 'DBSERVER' and Database 'In
ventory' is connected to the database and working.
Any Ideas?
Thanks,
-Kuo
View 1 Replies
View Related
May 12, 2004
Looking for suggestions on this one. What I want to do have have a text file that may have any number of rows and cols (with a predefined format) that a user can update or insert into a table. The definition of the row/cols and data mapping etc, has been done, it is the mechanics of actually doing the below I would appreciate help and advice on.
As the user is an 'end-user' (and has no SQL knowledge at all) the text file to import from will be placed in a predefined location and then a small script will be executed from their PC (as it happens, it's a Mac that runs an app that can exec an SQL command on the currently open database) that will in turn run a stored proc which is then reads in (imports or updates) the appropriate tables witht he contents of the external text file.
Sorry the explanation is a bit long winded but if anyone had any practical suggestions and examples, it would be greatly appreciated.
FYI, they are running SQL 2000 on both XP Pro and W2K3 server.
Thanks
Starb
View 2 Replies
View Related
Oct 12, 2015
In SSIS 2008R2, I have a dataflow with an xlsx source and the destination is a SQL Server 2008R2 table. The files are delivered from a location where staff members 'work with' the source files. The files are produced monthly.
The dataflow that contains the file breaks upon the attempt to process subsequent monthly xlsx files with a message similar to the following:
--*************
[TNUQQ [16]] Warning: The external columns for component "TNUQQ" (16) are out of synchronization with the data source columns. The column "F12" needs to be added to the external columns.
The external column "county_taxable_sale_amount" needs to be updated.
The external column "city_taxable_sale_amount" needs to be updated.
The external column "district_taxable_sale_amount" needs to be updated.
The external column "QTY" (62) needs to be removed from the external columns.
--*************
I've noticed that some columns in the file ship with no data. A column with no data can be typed as datetime one month, and then float another month. I've tried to load xlsx to raw to table, but that does not work around this issue.
I've tried to set 'ValidateExternalMetadata' to 'False' on the Excel source, but that does not work either. Aside from going back to the folks who ship the file to us, is there anything that can be done in SSIS to work around this issue, and still wind up with valid data?
View 5 Replies
View Related
Feb 20, 2007
Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.
Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.
View 1 Replies
View Related
Dec 27, 2006
hi to all,
my question is how can the database notify the user if there is a new inserted data on the database...like for example, i inserted a new data then when i go to the display reports page i would see a link saying New Report Recieved and this will take me to the details of the new inserted data or the report sent by another user..is this doable?? and by the way im using asp.net 2.0 and a sql database...thx...
View 1 Replies
View Related
Mar 26, 2001
Hi All,
I am working on a client product that shrinks the database in MS SqlServer. After shrinking the database files, it does not send any email notification to the users.
My question is: Is there any way in SqlServer to sense that the database has been shrunk and send a notification to the users (may be through an Alert or a Job or something). Please let me know the steps to do that.
Thanks
Sri
View 1 Replies
View Related
May 11, 2007
Hi,
I have two servers, namely ServerA and ServerB. These two servers are on the same subnet. ServerB has a database called DatabaseB.
I'm logged in in ServerA and trying to pull data from DatabaseB.
This is the query I do when I'm logged in in ServerB (note im using Microsoft SQL Server Management Studio):
USE DatabaseB
SELECT distinct b.Subject
FROM dbo.F_Class_Exam a
LEFT OUTER JOIN dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = '2071' AND a.Class_Exam_Type = 'FIN' AND b.Term = '2071'
ORDER BY b.Subject
How would I do this same query on ServerA? Ideas? Thoughts?
Thanks,
Mikhial
View 7 Replies
View Related
Aug 21, 2007
I am getting the error
Unable to open the physical file "E:Navision SqlDataForentaSQL_Data.mdf". Operating system error 5: "5(Access is denied.)".
when I try to "attach" to a database located on an external hd. The database files on the external HD were copied from the original server location to the external HD. The file size is 51G+ which is why it is on the external HD.
Can anyone provide me the syntax to do the sp_attach taht will allow me to send the necessary connection string: server, user, pswd etc.
How can I get past this error?
View 1 Replies
View Related
Jan 23, 2002
Hello,
How does one setup the connection to an external SQL/DB2 database? Any help would be appreciated.
Thanks,
Faustina
ifedif@mail.nih.gov
View 1 Replies
View Related
Jul 20, 2005
I have the need to restore a database to a user's laptop on a dailybasis. This has been no problem using Enterprise Manager. I found astored procedure to run the restore and tried to call it from a visualbasic program. While I can connect to the database, I get an errortelling me "exclusive access could not be obtained because thedatabase is in use".Does this mean the only way to run the restore is via EnterpriseManager?Thanks,Tom
View 1 Replies
View Related
May 11, 2006
Hi All,
Quick question ...
Is it possible to add an external server registration using SQL Server Management Studio Express CTP?
Reason being I have been doing some work from home and need to port the data back to the office who are on SQL 2000. I have a database with my web hosting company and wanted to export the data to the 'live' database so I could import to the office database - any ideas?
I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.
Any help would be greatly appreciated as it's been a real pain to get stuff transferred. I've so far had to generate SQL script to create the procs and tables for when I get to work - the proper down side is that I lose the content of the tables which not much right now but will be loads soon!
Many Thanks
--
I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?
View 3 Replies
View Related
Apr 11, 2007
Ok,
I am a webdesigner who at the moment does not know SQL (although, I plan on remedying that) so I am developing a page with a DB designer - he is doing the DB work, I am doing the look/feel, but he asked me the following questions, of which I cannot seem to find an answer and the guys who tend our server are useless - so I am hoping someone here can help me. In general what he needs to know is:
"what is the external address (either in domain format or IP) and the equivalent internal address so that we can access the msSQL running on your server. The internal address is needed for the webpages to talk to it, while the external address is needed for development of the pages in visual studio and the database tools." Also, he later sent me an email asking that when I get this info (from someone) that it would be usefull to get a sample string/query to get access to the DB. I am running SQL 2000 and have enterprise manager. Where can I find this information? or how do I figure this out????
Thank you for all the help -- Please let me know if you need any more info.
View 5 Replies
View Related
Jan 28, 2007
Hi again... I guess this is yet another newbie question (Sorry if its been asked before!)
If I have an IP address for an external SQL DB with valid User & Pass... Can I connect to it using SQLExpress on my machine??
If so can anyone point me towards how to do it, or run through it below (In easy steps as I'm dumb )
View 6 Replies
View Related
Mar 13, 2007
Hi,
I'm trying to find out whether it is possible to integrate an external entity into MS SQL Server in following way:
the entity defines certain set of tables
when user performs a query on one of these tables, the query is sent to the entity for processing and results are returned back to SQL server (and to client)
I thought integration services could be used for this task, but I'm quite unfamiliar with MS SQL server, so I don't know whether it would be a feasible solution...
View 4 Replies
View Related
Jan 16, 2008
Hello all,
I have been reading these forums for a while but I am now a new member and relatively new to SQL (< 1 year exp).
In a project that I am currently working on I would like to include information related to current market news. An example would be news updates for certain companies that are generated from the Wall Street Journal or MarketWatch. The application would only be used internally and information would only be shared within the organization.
What I'd like to find out is:
Has anyone else worked with or developed something like this? If so, can you please share some insight.Would I need to purchase a Corproate License to use this information or would a personal account that can access the information be enough?What would be the best way to feed the database with this information? E-mails to the server, SSIS, etc.
Thank you for your help and I look forward to learning from you insight and experience.
View 3 Replies
View Related
Jul 12, 2007
Hello,
I am a C++/C# developer my SQL skills are very limited.
I have a database set up for mirroring and I would like to get an e-mail notification whenever an Automatic Failover occurs. Can anyone show me how to do this using SQLServer 2005? Please provide T-SQL script sample if possible!
Thanks,
-Agustin
View 7 Replies
View Related
May 8, 2008
I am interested in adding a new row to a table 'Table05' that exists in a SQL Server 2005 database whenever a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?
View 1 Replies
View Related
Jan 3, 2007
I wanted to confirm that when using Database Mirroring that DDL Updates such as table and stored procedure updates on the principle server are replicated to the mirror server.
I thought I read that non-logged operations would not be replicated to the mirror server. What would be some examples of that. At the moment...you would think every entry in the the database would be saved into a table and that would be replicated.
...cordell...
View 3 Replies
View Related
Mar 6, 2005
Hi
I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.
My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').
I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.
Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.
Example of stored procedure on external database:
/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/
CREATE PROCEDURE dbo.AddUser
(
@Username Varchar(20),
@Password Varchar(20)
)
AS
(
Select User_ID From UserList
Where User_Username = @Username
)
GO
Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!
Any help would be greatly appreciated as I am completely stuck.
John
View 2 Replies
View Related
Jul 13, 2015
I have two domains. forest domain abc which has the exchange infrastructure. forest domain xyz which does not have exchange. There is a two way trust set up.
I have set up database mail which sends internally but not externally on the xyz domain its uses mailrelay address to comunicate with exchange. The receive connector is set up correctly on exchange on domain abc as I have managed to send email from a sql server to external contact in the abc domain.
The IP addresses are correct on the receive connector.
The problem is I am unable to send to an external contact from the sql server in the xyz domain. Not sure if this an exchange issue or database mail.
View 2 Replies
View Related
Jan 3, 2008
C# .Net Application as front end
Sql Server2000 as back end
I need to merge an external dataset from .Net app(in XML format) with the information in database with one column in database table as the merging criteria. A situation similar to Left Outer Join, wherein i need all records from external dataset and if matched in database the corresponding values from there too, the only difference here is that the join is not between two Tables its between a table and external dataset.
There is no need to store the external dataset in the database in persistent form, its just a query - merge - response operation.
So, can anyone suggest the best possible solution for this? A table variable / temporary table / some other schema, what and how?
Thanks in advance..
View 8 Replies
View Related
Jun 12, 2015
No error is thrown, but the update is not made? Possibly due to the dreaded inline sql being used? The data structure for these 2 servers is horrific (but that is a story in itself). The current structure (which needs immediate change) is each employee all 10 of them, have their own database. If the userid is 6 or higher they are on server2 if the userid is 5 or below they are on server1. Then they each have their own table that stores the data. (A story for another day, but def needs overhaul). However, here is the sql -- what needs to be altered to work with the current data structure and still get the updates needed? Or we can scratch the update statements entirely if we can get the correct counts needed.
Declare
@employeeID varchar(50)
,@managerID varchar(50)
,@sql varchar(Max)
,@employee varchar(max)
,@itemsold varchar(max)
[Code] ....
View 13 Replies
View Related
Nov 15, 2006
Hola!I'm currently building a site that uses an external database to store all the product details, and an internal database that will act as a cache so that we don't have to keep hitting the external database to retrieve the products every time a customer requests a list.What I need to do is retrieve all these products from External and insert them into Internal if they don't exist - if they do already exist then I have to update Internal with new prices, number in stock etc.I was wondering if there was a way to insert / update these products en-mass without looping through and building a new insert / update query for every product - there could be thousands at a time!Does anyone have any ideas or could you point me in the right direction?I'm thinking that because I need to check if the products exist in a different data store than the original source, I don't have a choice but to loop through them all.Cheers,G.
View 2 Replies
View Related
Sep 24, 2007
Hi
I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005.
Thanks for any help
View 1 Replies
View Related
Mar 29, 2006
I'm trying to work out a database design to make it quicker for my clientprogram to read and display updates to the data set. Currently it reads inthe entire data set again after each change, which was acceptable when thedata set was small but now it's large enough to start causing noticabledelays. I've come up with a possible solution but am looking for others'input on its suitability to the problem.Here is the DDL for one of the tables:create table epl_packages(customer varchar(8) not null, -- package_type char not null, -- primary keypackage_no int not null, -- /dimensions varchar(50) not null default(0),weight_kg int not null,despatch_id int, -- filled in on despatchloaded bit not null default(0),item_count int not null default(0))alter table epl_packagesadd constraint pk_epl_packagesprimary key (customer, package_type, package_no)My first thought was to add a datetime column to each table to record thetime of the last change, but that would only work for inserts and updates.So I figured that a separate table for deletions would make this complete.DDL would be something like:create table epl_packages(customer varchar(8) not null,package_type char not null,package_no int not null,dimensions varchar(50) not null default(0),weight_kg int not null,despatch_id int,loaded bit not null default(0),item_count int not null default(0),last_update_time datetime default(getdate()) -- new column)alter table epl_packagesadd constraint pk_epl_packagesprimary key (customer, package_type, package_no)create table epl_packages_deletions(delete_time datetime,customer varchar(8) not null,package_type char not null,package_no int not null)And then these triggers on update and delete (insert is handled automaticallyby the default constraint on last_update_time):create trigger tr_upd_epl_packageson epl_packagesfor updateas-- check for primary key changeif (columns_updated() & 1792) > 0 -- first three columns: 256+512+1024insert epl_packages_deletionsselectgetdate(),customer,package_type,package_nofrom deletedupdate Aset last_update_time = getdate()from epl_packages Ajoin inserted Bon A.customer = B.customer andA.package_type = B.package_type andA.package_no = B.package_nogocreate trigger tr_del_epl_packageson epl_packagesfor deleteasinsert epl_packages_deletionsselectgetdate(),customer,package_type,package_nofrom deletedgoThe client program would then do the initial read as follows:select getdate()selectcustomer,package_type,package_no,dimensions,weight_kg,despatch_id,loaded,item_countfrom epl_packageswherecustomer = {current customer}order bycustomer,package_type,package_noIt would store the output of getdate() to be used in subsequent updates,which would be read from the server as follows:select getdate()selectcustomer,package_type,package_no,dimensions,weight_kg,despatch_id,loaded,item_countfrom epl_packageswherecustomer = {current customer} andlast_update_time > {output of getdate() from previous read}order bycustomer,package_type,package_noselectcustomer,package_type,package_nofrom epl_packages_deletionswherecustomer = {current customer} anddelete_time > {output of getdate() from previous read}The client program will then apply the deletions and the updated/insertedrows, in that order. This would be done for each table displayed in theclient.Any critical comments on this approach and any improvements that couldbe made would be much appreciated!
View 4 Replies
View Related
Feb 14, 2008
we're in a business where customers often ask for a foolproof scheme that even prevents folks with DB privileges from fraudulently inserting, updating or deleting data. The scheme must be so air tight that a judge can be convinced of its reliability.
What are the most effective schemes out there?
View 9 Replies
View Related
Apr 9, 2007
I need to create a SQL server database and add some tables to it. Then access it with a C# application. The problem is that the new SQL server database and it's tables must reside on an external hard drive. How do I point SQL server to this external drive, so that I can create a database on this drive and then create tables and access data on it?
View 7 Replies
View Related
Oct 19, 2006
OS: Windows XP (SP2)
App: MS Access 2003 (SP2)
DB: MS SQL 2000 (SP4, 2040)
User is using MS Access linked table to query database (using ODBC). User can open the query in MS Access. However when the user goes to export the data to MS Excel format, the user receives the error message:
"Unexpected Error from External Database Driver (22)."
A trace on the db, reveals that the user process is attempting to log in to the server as 'Admin'. However, when the query with the linked table is first opened, the user is prompted for username/password (non Windows authentication).
Any ideas? I googled and found some stuff, but nothing directly related and most of what I found was supposedly fixed with SP1.
Any help appreciated. I am not an Access guru.
Regards,
hmscott
View 1 Replies
View Related
Jul 20, 2005
HiI have have two linked SQL Servers and I am trying to get remote writesworking correctly (fast).I have configured the DB link on both machines to:Point at each others DB.I have security set up to map each others server loginsand Server Options: Collation Compatible, Data Access, RPC, RPC Out, UseRemote Collation all checkedMy problem is that when a SP performsBegin TransactionUpdate Local TableUpdate Remote TableCommit TranIt takes several seconds to complete. (about 7 seconds not acceptable tous)This is due to the remote update - how can I improve the response time?example of a stored procedures that takes timewhere ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
View 2 Replies
View Related
Jul 20, 2005
Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
View 3 Replies
View Related
Oct 23, 2007
Hi all,
This managed application was written to run on a Symbol 3090 Win CE 5.0 scanning device. We are using the symbol provided classes to access the scanning interface, and SQL Compact database on the device to collect the scanned data, and then using merge replication to synchronize scanned data when the device is docked. The problem we have experienced seems to be releated to the performance when inserting and updating records in the database.
We have tested some randomly generated 1000 records and inserting/updatating into a database. At first the time to commit a record increases when the database is flushing into the memory (The flush interval in the connection string property is 10 seconds by default). and then as the database size grows increasing the time to commit every single record which is causing the application to perform slowly as they scan items into the database. However, the device program memory remains consistant as they are scan items. From our tests, I found the time to execute either a update/insert command on 2MB sqlMobile database (upto 10000 records, depending on the size of the columns) is taking nearly 2 to 2 and half seconds to complete. Below is the only code I am executing,
If Not sqlObj.UpdateItem(1061022, itemNo, 1) Then
sqlObj.InsertResultSet(1061022, itemNo, itemObj.Style, itemObj.Color, itemObj.Size, itemObj.Description, 0, 1)
End If
For the notes, I am using prepared updated command and resultset.insert methods to perform update and insert commands into the database.
Any help on this issue is highly appreciated.
Thanks
Ravi.
View 1 Replies
View Related
Mar 4, 2015
I have a stored procedure that does the following
BEGIN TRANSACTION OUTERTXN
BEGIN TRANSACTION
Copy records from live to archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
IF transaction was committed
BEGIN TRANSACTION
Delete records from live the archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back execute sproc to write audit log saying whole process is rolling back End IfMy problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
View 2 Replies
View Related
