Updates To Live Web Server
May 30, 2006
I'm developing a web app using ASP.NET and SQL Server 2005 Express. So far it's all been on my local computer, it hasn't gone live yet, so if I need to add a column to a table or make some other schema change I just do it right in Visual Studio, nice and simple. If I have to delete all the old content and start over, no problem. When I deploy it to a staging server I just overwrite the existing file with my new one, losing its data in the process. But soon enough I'll be deploying this to a public web server, there will be real live data in the db, people using it when I need to make updates.
What are some common strategies for updating the schema of a database on a live server?
It's obvious that when I need to update the db I'll have to shut down the site temporarily. But my biggest question is how to keep the existing data from the live db? I obviously can't overwrite it with my local copy. I want to overwrite its schema, without touching its data. How's that done?
Thanks for some advice!
Nate
View 1 Replies
ADVERTISEMENT
Mar 9, 2000
I have a problem in that I have a live SQL 7.0 server as a back end to a web server for a large company in Germany and of course we also have a development server locally which is more or less the same apart from data. The ASP developers here want to upload some data to the live server and also download live data from the live server so that we have more or less the same data on both systems.
I have thought about doing this with DTS adn writing SQL scripts to merge the tables and then upload back to the live server etc. but before I do I wondered if anyone had experience of doing this before (I am sure) and could either tell me what are the best methods or package to do or point me or mail me an article that covers this sort of thing.
Of course extreme paranioa creeps in at the thought of copying down large tables from the live server etc. etc. so if anyone can help - great!
Jeff Cresswell in Hamburg Germany
View 2 Replies
View Related
Jul 20, 2015
I am trying to understand an environment and provide a solution to Banking system so that they can enter user data (transactions) online and at the same time we can provide users online reporting as well. Using same sql server or server/hardware on other machine.
There are so many branches/customers/ATM machines accessing online data as well as updating their balances. I want to understand how can we provide online reporting. Through replication, transaction log backup, log shipping or what other solution is available. I need to understand this and provide a solution that is already implemented running/successfully. Need some proposals and their pros and cons. cost and maintenance are the constraints with the real time reporting on live system/database.
View 5 Replies
View Related
Aug 19, 2007
I am experiencing a situation where certain functions work perfectly when I run it on my local machine under MVS, but when I upload it to the server, then it does not?!
Here is an example:
I am using a drop down box (in a FormView) that is databound in an online submission form. When I run the application in MVS, then I can edit the records by opening the form, and selection the new value in the drop down list. The new value is then also saved to the database when I hit the update button. When I upload the code to the server, then the drop down list shows the correct information (so the databinding to the control seem to work correctly), but the new value is not saved to the database.
Here is the code for the drop down list:
"DropDownList1" runat="server" DataSourceID="SqlDataSource3"DataTextField="UserName" DataValueField="UserId" SelectedValue='<%# Bind("UserId") %>'CssClass="text">"SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT [UserName], [UserId] FROM [vw_aspnet_Users] ORDER BY [UserName]"> Here is the code updating the database with the record (I have removed some records as well as the Insert and Delete parts):
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tourism_connect1 %>"UpdateCommand="UPDATE Resorts SET typeid = @typeid, destrictid = @destrictid, UserId = @UserId, WHERE (resortid = @resortid)"OnInserted="SqlDataSource1_Inserted">
<UpdateParameters><asp:Parameter Name="typeid" /><asp:Parameter Name="destrictid" /><asp:Parameter Name="UserId" /><asp:Parameter Name="resortid" /></UpdateParameters></asp:SqlDataSource>
What I can mention further, is that if I connect to the database that is on the live server (so the application runs in MVS on my local machine, but it then retrieves the info from the online database), then it also works fine. It is just giving this issue when the online application is trying to update the values.
There is also no errors during the process.
I will appreciate any advise on how to overcome this, as I really do not know where to look anymore...
Thanks in advance!
Regards
Jan
View 3 Replies
View Related
May 17, 2002
At Sql Server Live, I saw Sharon Dooley using the debugger in Query Analyzer, but now that I've tried it I can't step an sp, instead, I get....Quote:
SP debugging may not work properly if you log on as 'Local
System account' while SQL Server is configured to run as a
service.
You can open Event Viwer to see details.
Do you wish to continue?
End Quote:
I'm logged in at a W2Kpro box connectig via client tools
to a W2KSVR box via Integrated NT security. SS2K is NOT
installed locally.
View 1 Replies
View Related
Jul 20, 2006
I need to restart (becouse of the Database Mail :( ) SQL Server Agent on a live server which acts as a distributor for a lot of replications. I know that it shouldn't cause any problem, but I want to confirm that it want couse a subscriptions to be reinitiated.
Thanks in advance for quick reply
View 6 Replies
View Related
Jun 27, 2005
This is a bit confusing but here goes: I need to access data in SAP via OLE DB. I can't go direct to the back end database (Oracle), we have to use RFC or BAPI calls to access the SAP data. That's part works, we have a DLL that accesses the SAP data we need.
View 1 Replies
View Related
Sep 14, 2005
First, I apologize if this is a dual post; I had a problem with my original posting.
View 1 Replies
View Related
Dec 20, 2007
Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1.
Both has SQL server 2005 running and has visual studio 2005
Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server.
Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this.
Thanks
George
View 5 Replies
View Related
Apr 13, 2007
I have created a site using VWDExpress and now that I’ve finished testing have moved it over to the server which runs SQLServer 2000. Part of the site requires login, so I created the membership using the ASP.net web configuration tool and when testing locally worked well.
Now though that I’ve copied the web site over, when I try to log in I get the error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
Obviously its some sort of configuration issue, but I don’t know what.
What do I have to change to make this work from a test machine to a live server?
Thanks
View 19 Replies
View Related
Feb 5, 2008
Hi,Ive been building a project on a local server and now need to transfer the files and database over to the live server. I have managed to transfer the files quite easily, however I am having a few problems transferring the database using Microsoft SQL Server Management Studio. When I try importing data to the live database, all the data and tables are brought across however some of the values for the fields are lost. For example all the fields which had a primary key no longer have one, and all the set default values for the fields are also lost. Ive been considering just going through every table in the database and re-entering the correct values for the fields, but this seems a bit time consuming and I'm also concerned about possible errors which could arise as there will be an inconsistency.Another method I tried was copying the SQL query across which created all the tables and the correct values for the fields. However when I tried to import the data an error was produced as you cannot import data into a table which has read only fields etc..Does anyone know a solution?Thanks for your time.
View 2 Replies
View Related
May 18, 2000
I have a live server running windows Nt and sql server We brought a new
backup server of higher configuration we wanted to swap the data we installed nt and sql server in the new server will a cut paste of the data directory of the live server to the backup server is sufficient or the data directory to be restored to the respective files through sql enterprise manager please do clarify me
Regards
Ramesh
View 1 Replies
View Related
Aug 17, 2007
Hi,
Im fairly new to this so apologies if Ive put this in the wrong place.
I have just rented out server for a new asp.net site. Ive been developing the site on my local machine and the database in Sql Enterprise. Ive recently put a copy of the site on the live server and got everything configured.
However everytime I make a change to my local database, or add values into my look up tables I have to go do the same on the live server and its proving a bit of a pain in the ass, so Im guessing there has got to be a much smarter way of doing this. I dont want (if possible) to open up the live sql server to allow remote connections, but if thats what neccesary so be it.
So my question is basically what are my options for keeping my live server up to date with my development server. As I add new features on my local server, should i be saving sql scripts of all the changes I make?
All opinions much appreciated.
C
View 4 Replies
View Related
Dec 17, 2005
I need to live update my SQL-Server 2000 database from AS400 database.
I usually use Access 2002 database between the AS400 database and the SQL-Server 2000 database to reflect any live update.
I use the (Link Tables...) option when creating a new table in access and link it with the ODBC to connect with the AS400 database.
After that I work with the Access link table.
Is there any tool in SQL-Server 2000 to show the live transactions in AS400 databasee so I can work on it?
Please advise
View 1 Replies
View Related
Jan 31, 2015
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master
GO
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
GO
3- Rename Newdb to current DB.
USE master
GO
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)
View 4 Replies
View Related
May 29, 2007
Hi,
Is it advisable to use full text search on asp.net live applications? Will live database servers allow full text search?. If so what are the problems we could face in future.
My DB server is SQL Server2000 and ASP version is 1.1.
Thanks in advance
Hamlin Stephen
View 4 Replies
View Related
Jan 22, 2006
Do you know of anyone who is running SQL Server Express in a live production web server?
Thanks
WD
View 1 Replies
View Related
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View 4 Replies
View Related
Aug 5, 2006
Hello.
I am doing a proof of concept and part of the solution is to configure Common Live Server. However, I am running into problems and do not have that much experience in SQL Server 2005.
I have downloaded and installed the trial version and it seems to be running. I run the configuration manager and see the instance running. When I continue to configure my Live Communications Server - Creating an Enterprise Edition pool, it prompts me for the pool name, FQDN and Instance. I fill these fields in and it fails immediately. I think that I have not completely installed and configured SQL 05. Problem is, I don't know how to connect to the database as well. I found an sqlservr.exe in the MSSQL/Binn directory, but when I run that it pops up with a dialog saying that my installation may be corrupt. I have re-installed the thing again - but same thing happens.
Any clues/tips on how I should approach debugging this even resolving the problem.
Thankyou
View 1 Replies
View Related
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
Feb 6, 2001
If I have 5000 rows to update with different values based upon the primary key, is there a better way to do it than 5000 separate update statements?
View 2 Replies
View Related
Oct 11, 2007
Does anyone know what the best way to do mass updates in SQL server is? I am currently using the methodology suggested in this article
http://www.tek-tips.com/faqs.cfm?fid=3141
But the article is assuming that once I update a field it is going to have a value that is NOT NULL. So I can loop through and update the rows that have a NOT NULL value. But my updated rows do contain NULL values, in this case what is the best way to go about this???
***************************************
Here is my code. I want to avoid using Upd_flag becos
after the following code runs I need to reset that flag
before I run my next query
***************************************
--Set rowcount to 50000 to limit number of inserts per batch
Set rowcount 50000
--Declare variable for row count
Declare @rc int
Set @rc=50000
While @rc=50000
Begin
Begin Transaction
--Use tablockx and holdlock to obtain and hold
--an immediate exclusive table lock. This usually
--speeds the insert because only one lock is needed.
update t_PGBA_DTL With (tablockx, holdlock)
SET t_PGBA_DTL.procedur = A.[Proc code],
t_PGBA_DTL.Upd_flag = 1
FROM t_PGBA_DTL
INNER JOIN CPT_HCPCS_I9_PROC_CODES A
ON t_PGBA_DTL.PROC_CD
= A.[Proc code]
WHERE t_PGBA_DTL.Upd_flag = 0
--Get number of rows updated
--Process will continue until less than 50000
Select @rc=@@rowcount
--Commit the transaction
Commit
End
View 4 Replies
View Related
Dec 17, 2007
MERRY CHRISTMAS EVERYONE :)
I need to update a table on our Test Server which is GCSQLTEST, with another table thats on our live server GCSQL. How would I go about doing that in a stored procedure??
CREATE PROCEDURE [InsertRevised_MainTable]
AS
INSERT INTO dbo.RevisedMainTable
([IR Number], [Date], [I/RDocument], [Violation Type])
SELECT [Incident Report No], [Date], [I/RDocument], TypeOfIncident
FROM dbo.RevisedMainTable
WHERE NOT EXISTS (SELECT * FROM dbo.RevisedMainTable
WHERE [IR Number] = [IR Number])
View 3 Replies
View Related
Jul 28, 2006
Our IT guy insists that he must be present anytime the SQL Server automatic updates from Microsoft are installed on the server or the server will crash. What has he been tinkering with to cause this to happen or is he just giving us a line?
View 8 Replies
View Related
Aug 8, 2007
Here's the scenario:
* I have a database with a table
* I have a C# program which displays information about the data in the database
* If the data changes in the database, I wish for the client to pick it up and report the change
Is there a way to have some form of 'Event Handling' where the client will react to UPDATE queries on a table?
My thanks in advance.
View 2 Replies
View Related
Jul 15, 1999
Is it possible to perform a cascading delete and update using TRIGGERS on a table referenced by a foreign key constraint.?To be more specific.. if the primary key is deleted does the delete trigger on the primary table deletes the record in the foreign key table or does it return an error??
if possible please send us the T SQL Statements .
Thanks in Advance
Geenu
Ajaz Dawre
View 2 Replies
View Related
Dec 7, 2005
Can some one give me an over view of what I need to do:
Lets say I have a web form with the field: Arrival Date
A web user enters an arrival date of 2/10/06
How do I do a stored procedure that accepts that arrival date and lets me know (by an alert or email) 24 hrs in advance of the arrival date?
View 2 Replies
View Related
May 21, 2007
I'm using MS SQLEXPRESS 2005. Everyting worked fine: I could connect to it from Access and VS 2005. After updates (SQLEXPRESS SP2) I can't connect to it. Error:
Connection failed:
SQLState: 01000
SQL Server error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect))
Connection failed:
SQLState: `08001
SQL Server error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exists or access denied
It applies to SQL SERVER too.
Pls help.
View 1 Replies
View Related
Apr 14, 2006
Dear MSDN Support,
Here i am back with an inquiry about the last process of Merge Replication between SQL Mobile 2005 and SQL Server 2005.
Well i have performed all the steps found in the tutorial of the SQL Mobile Edition Books Online, and everything went on so fine and smooth. Now when i deploy my application on the Pocket PC emulator all possible SQL queries work and modify the data only in the emulator, i had installed ActiveSync 4.0 and it synchronizes the SQL Mobile data with the SQL Mobile server only when i copy the database from the emulator to my desktop, and this way is not efficient to my project, i need a better way to synchronize the data between the database on the emulator and that on the SQL Mobile Server.
I'll be looking forward to getting your help as soon as possible.
Thank you for your attention.
Best Regards;
View 6 Replies
View Related
Feb 28, 2006
Hello!
We just upgraded to SQL Server 2005 from SQL Server 2000. The DB was backed up using Enterprise Manager and restored with SQL Server Management Studio Express CTP. Everything went as expected (no errors, warnings, or any other indicator of problems).
The DB resides in a DB Server (Server1) and the application we are running is a Client/Server system where the AppServer resides on Server2.
During the application's operation all read, create, and delete transactions work fine but no update works. When viewing details in Trace Log I see this message after attempting any update:
Could not find server 'Server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (7202)
Any help is greatly appreciated,
Lucio Gayosso
View 19 Replies
View Related
Aug 6, 2015
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
View 5 Replies
View Related
Oct 30, 2007
Hi All,
I'm a relative novice on SQL Server and am a complete beginner at SQL, so am looking for a little help.
I currently use a DTS package to perform inserts / updates to a "production" table.
The DTS package transforms a comma separated file into a "temporary" table that is truncated / cleared before the load starts.
The temporary table has a column denoting Insert or Update. The production table is almost identical, however, doesn't contain the Insert / Update column. The DTS package then, depending upon the Insert / Update flag, either inserts data into the production table or updates data in the production table.
When the DTS package has completed, I'd like to be able to run an SQL Query that validates everything in the "temporary" table is identical to that in the "production" table, which it should be.
I have managed to do some queries to verify that everything has loaded / updated i.e. select primary_key from temporary table where primary_key not in (select * from production table), however, what I haven't been able to do is verify that all the columns on the temporary table match the values in the production table (excluding the Insert / Update flag).
I tried concatenating the columns in each table and comparing the concatenated values, however, this failed due to the different data-types, i.e. decimal, text etc.
Any help will be greatly appreciated.
Many thanks.
Cheers,
David
View 8 Replies
View Related
Mar 28, 2006
When one process has one record locked in Update-Mode then an other process can't update any other records on that table with some queries, other queries that access the same records (but with a different WHERE statement) will execute.
This problem occurs with SQL Server 2005, but it didn't with SQL Server 2000 (or any other database).
The problem:
-Process 1 locks a record in the table, and keeps it in Update-Mode, because the user is editing it. (Using OLEDB Pessimistic Cursor-locking)
-Process 2 wants to update an other record, buts gets a "Lock timeout" when using one query but not with another.
For example this query will work :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id = 110;
But this qeury will give me a "Lock timeout" :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';
But it is the same record!!
The record with name = 'REC_2' has the row_id = 110, both values are unique in the table.
The data:
The table [gwseqnumber] has the following CREATE statement:
CREATE TABLE GWSEQNUMBER
(
NEXTSEQNR INTEGER,
NAME CHAR (20),
ADMINISTRATIONCODE INTEGER,
FINDHIGHESTNUMBER CHAR (1),
CLOSEDYN CHAR (1),
ROW_ID INT IDENTITY(1,1) NOT NULL
);
CREATE INDEX KEY_1 ON GWSEQNUMBER (NEXTSEQNR);
CREATE UNIQUE KEY_2 ON GWSEQNUMBER (ADMINISTRATIONCODE, NAME, IDENTIFIER);
CREATE UNIQUE INDEX KEY_3 ON GWSEQNUMBER (ROW_ID);
Both KEY_2 and KEY_3 are unique, KEY_1 is not.
If I remove the index on the NEXTSEQNR column (the index named KEY_1) then both these queries will work, so it is obviously related to the index.
Altough the index is obsolete and can be removed from this table, it should not result in bogus locking errors.
Even when i removed the index not all the queries will work. (With work i mean not run into a locking error, while the record is not even locked.)
Solutions tried:
- Set the compaitiblity level of the databsae back to 80 (for SQL Server 2000 compatibility).
- I have already tried to disable Page-Locking on all the indexes of this table.
So why does the index lock my updates in SQL Server 2005?
And how do i fix it so my database does not run into these locks?
View 14 Replies
View Related
