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!
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Hello, I have my local development evrioment and my live webserver in colo. The two different machinces both have SQL2005 and copies of roughly the same database. I use the one for developement and the other obviously for production. Currently I read in different connection strings from my web.config file for my live version v my development version so I can have different database names and connection strings. I'm looking at moving to LINQ, but I can't figure out how I'd achive an equivelent flexibility as database names and connection strings seem to be hardcoded in to the class using it's designer. Obviously if modifing this wasn't possible, nobody would be using LINQ, so how do I work around this? Thanks, Joel Barsotti
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?
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.
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
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
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?
I really need your help!! How you manage development server and production server?
My current situation: Our programmers develop new Stored Procedures on development server. When all testing is completed, we then deploy them to production server.
Challenges: Programmer (Eric) modified a Stored Procedure (SP_1) on development server. He did not deploy it on production server because SP_1 is not completed yet.
Programmer (John) got request and needed to modify SP_1 on development server
So now there is a problem in SP_1. if John deploy SP_1 on production server, it will overwrite the current version and cause errors.
Anyone can solve this problem? Will it be better if we have another testing server?
This is driving me nuts: On my development machine the code runs fine but generates an error on the production server. Both are running SQL Server 2000 and ASP.NET 1.1
The datatype of the field in question is datetime. The webform has a calendar for a user to select and automatically insert the date into the textbox. The update command in the webform is:
This works without a hitch on my development system, but on the production server it generates the following error: Cast from string "19-12-1997" to type 'Date' is not valid.
I am trying to update some informaiton on a linked server based on information on the local server, and I get the following error:
Could not open table '"infoimg"."dbo"."queues2"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
The table does exeist, and linked-server selects on it work just fine. It's when I try to run this statement:
update imgtr02.infoimg.dbo.queues2 set o_resource = null, lock_time = 0 from imgtr02.infoimg.dbo.queues2 a, sysid_stuck b where a.id = b.id
I'm looking for ideas on how to write SQL scripts for updates that arepushed out to clients for product updates. Obviously, We could justkeep track of the changes on a pad or write a database that requiresus to input those changes and eventually hand write the updatescripts. I was wondering if anybody has any solutions that may helpautomate this process.Is there a way to write an application that will compare a current(updated) database structure against the last realease that will giveus the fields that need to be changed?As far as creating the scripts for the initial install, thats easy. Wecan do that right from the SQL Enterprise Manager.Call me lazy! Any ideas?Thanks
I have two servers S1 and S2. Inmediately after new data on S1 is available I want to perform some actions on S2.
I can use a trigger on S1, but if S2 is down the transaction on S1 will be lost. I could use database replication but I only need one single table in S1 to report changes to S2
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?
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...
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server. 2) Linking x64 or IA64 servers to x86 servers. 3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are: ------------------------------ TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054) ------------------------------ And ------------------------------ The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB". OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
How do you make a live link in SQL 7 to another database? I currently do such things in Access 97 but how do you do it in SQL? I just want to keep a permanent live link to a table in another database.
Hi, A problem................. Actualy in my local the work is fine.. when i shifted the files to live... it is saying invalid column name ,even though iam having that column in the table..... when i had executed the query in live it is working... The database to which iam connecting is also correct.
Can someone write for me an example query that would select all the constraints that are applied to specific table?
something like:
SELECT FieldThatHasConstraint, FieldTableName, TableToWhitchThisFieldHasConstraint, FieldOfTableToWhitchThisFieldHasConstraint TypeOfConstraint FROM ??? WHERE TableThatIWantToSearchForConstraints='myTable'
I am looking for a good introduction into the handling of certificates in SQL 2005.
I need to sign a procedure to allow it to access a dm view. I do understand the theory and the syntax, but I have trouble coming up with an easy but still secure way to create these certificates on all customer servers without allowing misuse. All articles I could find are going through creating a new database, setting up a certificate with or without password, signing an example proc and then dropping the database. Non seems to care about the problems that occur later on during the life of a certificate.
Hi:I have a site that is getting close to production. I am using SQL Server 2005 express and VWD express. Up to this point, I have been updating the live server by deleting the file and copying the MDF file from my development machine in its place. This clearly won't work once the site goes live - but I still expect to be doing a lot of upgrading of stored procedures etc. Can someone point me to a resource or make any suggestions that would help me in this regard?Thanks,Roger Swetnam
I'm trying to configure log shipping When I retore the full disk dump of my databaseto my standby server, I need to be able to go in and drop and recreate several user logins(so they can do reports, read-only). Once I do this though, I will be unable to restore T-logs, right?
Anyone know a way to get it back into a read only state and able to accept T-log restores? Thanks.
I need to create a DB version upgrade schema on client site that will preserve the existing data. Provide the user with a patch that he can run on his database that wiil apply changes to his DB structure and keep the old data. One approach that I am thinking of is to create a differential DB patch - a and apply that to the client database. Is there any strategy any algorithm to do that. Can it be automated? If there any accomplishments or approaches known on the subject? I would greatly appreciate any ideas you can come up with.
I have a sql express database with a couple tables of information. I have a user modifying one of these tables with a data access page using Office Web Controls.
To backtrack a bit, the sql db used to be an access db. The same user modifying the data the same way. I also had a asp page making odbc calls to the access db to lookup information. But if the data access page was open, the odbc page would error. So i moved the data to sql.
Now, I'm getting ready to setup an application to make odbc calls to the sql db. I still want the user to modify the data using the DAP. Should I allow the user to modify the live data used for the app or should I make a copy of the data everytime its updated?
Hi, sorry if this has been asked before but I'm pretty strapped for time...
I have two tables: [photos] and [photoFolders]
[photoFolders] contains information about photo albums on the site im creating. The information in [photos] lists information about all photos along with which [photoFolder] they belong. When the user logs in, I want to present a list of all 'folders' in their name along with the TOP image with the corresponding folderId...
trying to make a script to view data in a excell spreadsheet.I can get all the data I need except one.select ordernumber, itemcode, quantity, rate, totalfrom dbo.chargeswhere ordernumber = '45676'the problem is there are more than one itemcodeexample: run resultsordernumber, itemcode, quantity, rate, total45676 fuel 123 .10 12.3045676 stops 3 50 150how do I get this data on one line?to look more like this: run resultsordernumber, itemcode, quantity, rate, total itemcode, quantity, rate, total45676 fuel 123 .10 12.30 stops 3 50 150now just one ordernumber appears and its all on one line.hope you can help, thanks for your time...Mike
I have issues around forwarding message being dropped because they exceed the forwarding message time to live.
I cannot find anything in BOL or the net regarding changing the message time to live value to be higher, does anyone know where i change this setting ?
We€™re doing a website for a TV station, and they want to have a live TV broadcast on their website, our experience is basically in CMS and portal solutions, but we never had any experience in live streaming, so this is all new to us, so we€™re looking for a technical and financial proposal with your suggestions and recommendations, this is the details of the project with some of the inquiries we have: -The signal is SDI (Pal 25 fps) -The output (online streaming) 15 fps -The user will have two options to view the movie in: 1. Low: 56K 2. High: 300K -The video should come within a customized designed page, probably as a code we can place inside an html table€™s cell, not as a link for external link -We€™re expecting about 200 users for the video each month; each user€™s logon time expected 10 min. -We have a Red hat Linux on our server that will host the site, and we use php/MySql technology.
Inquires: 1. Why Windows media is better then Real in terms of performance, can you provide us with a comparison table to include it to our client? 2. If we choose Windows media, what are the requirements we need to have, form both; the TV station side, and in our Linux server? And how much would it cost? 4. Is there any hardware required from the TV station side such as DVR, or some sort of encoder, if so; what€™s you€™re recommendations (type/ brand), please note that this is a TV station and it needs a high quality professional hardware 5. How (and this is probably the most ambiguous task) 5.1. Do you broadcast the digital signal coming from the encoder (I guess) to the server? 5.2. And then place this signal on a PHP page? Do you give us a link to embed in our code? 6. If we choose to host the live streaming on a separated server and include the URL in our code, would that still required from our server to support Windows media, or it doesn€™t matter as long as the video streaming is hosted on a Windows streaming server 7. How much disk space does it need to host a live streaming (with the details mentioned above)?
We would really appreciate your comments and suggestions, as this is our 1st time to dealing with live streaming.
I moved one of the ssis packages from dev to prod. Did the same for the configuration file which contains the connectionstring. Now I would like to run the package in live but the error is: Description: The configuration file name "S:ImportsTradesEnvironment.dtsConfig" is not valid. Check the configuration file name. The file does indeed exist and the connectionstring has the correct string. Any thoughts please?
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
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.
