Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain.
Any ideas on how to keep the data updated on the website?
Dear friends, We have a server put at US side and we are trying to load the data into another related database in India site, now the thing is that we want to update India data at an interval of 5 mins?
Is there any way to do it without effecting the performance of the US server?
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
We have a third party process that runs and updated several SQL tables. Is there any way to find out what tables are being updated and store it in another table?
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End.
Actually I am fetching data from other database(named as retailexcel but in same server). I have wriiten a view (in retailexcel database) to get required data from four table of other database. My problem is when I fetch those data(about 40000 to 50000 record) It taking to much time. I tried catch feature of asp.net2.0. Time reduced but not significantly. So I am trying to import those tables to my database (named Inventory) from that retailexcel.
Now problem is every day or two, record of those tables changes. How can I keep update my tables (which are importing from retailexcel)
How do I insert unrelated statistical data from three tables into another table that already exist with data using an insert or update stored procedure? OR... How do I write an insert/Update stored procedure that has multiple select and a where something = something statements?
This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this....
CREATE PROCEDURE AddDrawStats AS INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)
SELECT WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),
TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),
Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),
RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )
FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID
I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??
I would like to propagate an event signal to an external applicationwhen a table in my MSSql2000 server is updated.Prog A; I have an external application adding records to a table.Prog B; I have another external application using this table as well.When Prog A creates a new record in the Table, how can I have Prog B benotified of the event without polling the table or creating a linkbetween Prog A and Prog B.Thanks.
I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.
So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.
How can i do that in SSIS 2005 using different data flow tasks ?
I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them. I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick. This I would like to do using code within my VB2008 application.
Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user.
I'm working on databases where statistics of some indexes (tables) are changing too frequently. Once I update them manually, one minute after they get 10-20% change, and five minutes after they get over 100% change. Tables get updated very frequently (multiple times in a second).
When I run a query to read from sys.stats, sys.dm_db_stats_properties and other dynamic views, I see that they were last updated when I did it manually, but the change rate overpassed the 500+20% (tables have multiples of 10K rows). Auto create and update statistics are set to true on all databases, and I don't know why sql server does not do that automatically.
i have to migrate data from one sql server(1) to another sql server(2) using SSIS,Which is a bit easy,the concern here is that after doing it the data in server(1) is getting updated due to inserts,now i need to bring the updated data alone.Is anybody having experience in this?can anyone pls help me or give me sugessions on how i need to proceed.
I do not know SQL but learning fast and furious.I am programming an agent and working with a group of existing databases.I would like to able to compare the database before and after an update.The testing databases are relatively small.I have no problem programming some compare but how do I go about it.Should I do this in SQL duplicating the database.I would be happy to write some SQL and dump the databases and do the compareexternally.I would appreciate any suggestion.Andre
I am new to SSIS (though have a decent amount of experience with SQL Server 2000), and am trying to design a data warehouse and eventually a comprehensive reporting solution.
Here is our setup: - We have ~150 studios all operating software which we designed (ie. we have control over the system that will be the Data Warehouse source). - All studios use merge replication back to Head Office (we maintain a duplicate of each of their databases).
From my limited understanding of Data Warehousing methodology, I believe that I should prepare a 'staging area' which contains all needed data from these databases. My question is how to do this in an efficient manner (without resorting to software-maintained timestamps in all the source tables - this would require significant modification to our software).
Since replication is already identifying which rows are new or updated, I'm wondering if there is some way to use this information to limit the information processed during the Data Warehouse updating process. I realize that I would still need to determine whether the row was updated or new, but it would cut down immensely on the number of rows processed.
I feel like I could be missing out on a simple way to do my data extraction from the source DBs. Does anyone have any advice?
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
Client issues query which sends out individual requests to the 2 OLAP servers that are load balanced. The client evaluates the versions of the returned record sets to ensure the consist data being returned for one single query. Otherwise, this error will be seen:
The cube has been updated by the server the data is now obsolete.
These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.
My developer says this worked fine for a long time then just 'started happening'.
My package sets a variable in an ExecSQL task. This variable is then used as a parameter in a DataFlow task that follows. Normally everything works just fine. However, sometimes if the package fails inbetween the step that sets the variable and the DataFlow, the default value of the variable is stored in the checkpoint file. (It is not failing in the step that sets the variable) When the package is restarted I can see that the variable is not set to the data value in the database, but rather it has its default (design time) value.
I am sending a GUID to a form via the query string. If it exists I use helper functions to load most of the form text boxes. However, if it does not then a blank form is presented and the GUID is stored in a hidden field. Regardless, I use this hidden field to populate a grid that is attached to a sqldatasource. If I then add new datarows to the backend database programmatically, I cannot 'requery' the datasource to include those row upon a postback. I cannot seem to find a simple way to force the sqldatasource to rerun the query. Can anyone help.
I created a SSIS package. It imports data from a flat file and then transfer to different data types and load it into destination table. I use look up transformation. Actually before I created final table, I created another intermediate data table for references. Now I get a new source file once in a month. Then I'm supposed to connect new file and run the package. only difference in new source file is the data not data type. But when I connect the new flat file, package does not work. first one and fifth one are red when I run the package. Can anyone help me to fix this?
Thanks
p/s;
I get the following error messages in execution result page.
[Lookup 5 [541]] Error: Row yielded no match during lookup.
[Lookup 5 [541]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Lookup 5" (541)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (543)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup 5" (541) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
[Flat File Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
We are building a near real time application in Windows Forms, that stores data in SQL Compact Edition. The client receives messages via UDP socket and performs insert / update operations to the db accordingly, and the requirement is to update a data grid with the changes.
We tried querying the db and receive a ResultsSet which is sensitive and scrollable, and bind a ResultSetView to the Grid. But, since the updates are made from another thread, the grid is not being refreshed.
What is the best approach for achieving this?
Thanks in advance,
Guy Burstein http://blogs.microsoft.co.il/blogs/bursteg
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
I have a table thats updated daily with monthly data totals:
Month, Total orders1, Total orders2, etc 12/01/2012, 5, 8, etc 11/01/2012, 6, 5, etc
How do I pull data from this table in SQL Server for ONLY the current month? I was thinking using the getdate() function to get the current month, but it doesn't match exactly so I get no results
I need the rows updated today in catalogue table irrelevant of timing. I tried all the below queries.
select * from CATALOGUE where CAT_DATE=CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) select * from CATALOGUE where CAT_DATE= CONVERT(date, getdate()) select * from CATALOGUE where CAT_DATE= cast(GETDATE() as date)
I am getting output only if the information updated on 04/21/2015 00.00.00.000 but not for other timings, For example 04/21/2015 03.30.00.000, 04/21/2015 07.17.00.000 and all.
I am working on a query that referrences a table in a remote database. It seems in Management Studio 2005 anytime you open a new query window you should connect to a specific database instance that's why when I refer to the remote database table using Fully Qualified Name it tells me the database name is unknown.
The only way the job success is if I select the option add rows wich is copying all rows even the ones that are already there, I tried to drop amd recreate the table option but i just dosn't run that way, help please..
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
after upgrade sql from 6.5 to 7.0 , there are some new system table in system database , all are begin with sysremote_.... such as sysremote_tables, i can not find any information about them , does anyone know what these tables for and whether these tables will affect the new sql7.0 application database ?
I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME
I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?
I'm using net2.0 and SQL server express locally and have succeseffully placed my connection string in my sebconfig file and made connection and grids and detail etc, etc. Now I want to publish what I've done. So I got a free account at maximumasp beta 2.0. They give some room on a sql 2000 server and a empty database. Now I want to add the Northwind table to that DB but I don't have Enterprise manager and have no idea how to add the tables.Can someone please help?Thanks!Rich
I have a web hosting account with Network Solutions. I also have my databases hosted on their sql server 2005. (along with a few thousand other database which don't belong to me) . Network Solutions does not allow me to speak to their Technical Support. I must submit a request and they email me their response. Bottom line they are not going to help me with my problem.
I am using SQL Server Management Studio Express to connect remotely to the server.
I have 2 databases. Currently all data is stored in database1
I need to move the tables with the data for my website forum from database1to database2 on the same SQL Server
I have already generated a script which creates the schema for the tables on database2. The problem is moving the data. The web Host does not allow DTS
Here are the things they won't allow:
You can use all features of SQL 2000 (it is 2005) except for the following:
- DTS Packages - Database Replication - Mail Services - XML Support - Distributed Transactions - Database Maintenance Plans - Web Assistant - Multiple Instances
I can't for the life of me figure out how to transfer the data. I have tried copying and pasting the data and run into problems with Identity keys and constraints. I tried a sql generator script to create insert scripts for each line of each table but it won't work. I'm not sure why.
Bottom-line I can't import and export directly. And I desperately need to.
