How To Dynamically Update Existing 50 Databases' Sp?
May 21, 2002
I have 50 MSDE SQL2k servers, each server has around 10 customer databases.
There are 5 stored procecures need to update to 50*10 = 500 databases.
These 5 stored procedures each has many 'Go' keywords and 4 of 5 with more than 8000 characters.
What might be the best way to loop execute them automatically, instead of
isql/w to each database connection to run the script?
I had bumpped by 'Go' keywords error and limitation of max varchar of 8000 error.
thanks for the help
David
I have an exiting excel workbook say master.xls. Now I need to dynamically create and append a new worksheet to the above master.xls every month end using the Reporting services.
Could you please guide me how dynamically creating the worksheets task can be achieved using the reporting services?
Your any guidance or help in this matter will be highly appreciated.
Hi, We are developing products to target thousands of clients. Front-End : C#.Net 2005 Back-End : SQL-Server 2005
We started an Inventry control project (Product) with 10% percent of data as we didn't meet any clients (User requirements). I mean, I could not get complete user requirements. version by version, I need to change the database dynamically, and it should support backward compatiblity. While altering the database the existing primary keys are affecting. It seems I need to complete reconstruction of data Layer, Business Layer and Presentation Layer. How to avoid reconstructions in such scenerios? How database designers designing databases for a product like this? Or should I need to create "Table_Name_Serial_Number" (identity data type) column for each table and column to be primary key of that table?. Please help me!
I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.
I need the correct syntax, the following won't work:
DECLARE DBS CURSOR FOR SELECT dbname FROM #helpdb ORDER BY dbname
OPEN DBS
FETCH NEXT FROM DBS INTO @dbname
WHILE @@FETCH_STATUS = 0 BEGIN
USE @dbname
The last line - the "USE" statement - is invalid. The following for example works:
USE master
But when supplied a declared variable a syntax error results for the use command because it expects an identifier.
So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?
What's the proper way to delete an existing database through native c++ code?
When I try DeleteFile(filename), I get an error about another process using that file. I hadn't created a connection to that file in my own program, so I assume that lock is from the sql engine.
We have a couple of databases on separate servers that have exactly the same schema, and we would like to set up merge replication between them.
Is that possible? The few times we've experimented on test databases, the subscriber database has been trashed and rebuilt using data from the publisher, rather than preserving data from both databases.
I'm really new to replication in general, so any kind of advice would be helpful.
I have a formview with name, email, and password. I bind all fields to sql except the password which is blank. In my sqldatasource, I define parameters for name, email and id: UpdateCommand="UPDATE UserProfile SET Name = @Name,Email = @Email WHERE (ID = @ID)"><UpdateParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Email" /><asp:Parameter Name="ID" /></UpdateParameters> In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event. Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.UpdatingDim password As TextBox = FormView1.FindControl Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not password.Text.ToString & "" = "" Then SqlProfile.UpdateParameters.Add(New Parameter("@Password", TypeCode.String, password.Text.ToString)) End If
I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.
For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.
The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same????! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.
Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?
Or is there a command that tells MSSQL to ignore all relationships for the next query?
I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.
I need to create an SSIS package that updates columns in a table from columns in another database where the keys match. What's the best way to do this?
IUpdating existing rows in a SQL server 2005 destination table
--I have a CSV file and every row needs to perform an update on a production table
The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ???
That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow??
All of these dates are based on the update of our data warehouse. This stored procedure runs a 5 step process and produces data for 8 - 10 monthly reports.
I was wondering if these variables can be updated dynamically and if they can how it is done.
HelloI used to work in a Sybase database environment. When I had to insert/update records in the database, I always used "insert on existingupdate", in this way, you didn't have to check whether a recordalready existed (avoid errors) and you were always sure that afterrunning the scripts, the last version was in the database.Now I'm looking for the same functionality in MS SQL Server, asked afew people, but nobody knows about such an option.Does anybody here knows the SQL Server counterpart of "insert onexisting skip/update"? If this doesn't exist, this is a minus forMS ;).Greetz,Bart
I'm building a package to import data from a flat file into a Customer table, and I have set up a Lookup to check if that customer already exists in this table, and if so, perform an Update command instead of the bulk load. I don't expect many updates, if any, this is why i just used the OLE DB Command instead of using a staging table.
I've a bit of a problem executing this within a transaction and having the lock table option set on the SQL Server Destination. Is there anyway I can get Transaction support for this Data Flow working, as I want to be able to rollback a complete file/import if possible.
Hello all, Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012' when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013' when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014' when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015' when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016' when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017' End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
I have a field where all of the data is 5 characters in length. The last character denotes a status and will always be an F, H, or T. I want to add a new field (which I will do manually) and populate the new field with the last character from the "old" field. Once that is complete, I want to eliminate that 5th character from the old field.
I am building a data warehouse. I have many columns I want to populate in a fact table using integration services. Sample fields are: companyID, companyName, companyNumberOfAccounts, company, NumberOfUsers.
In the integration services package, I would like to keep this fact table in place and populated with data and to build integration services packages that update each of the existing row's specific columns (e.g. companyNameOfAccounts) one by one. For example, I have a source of data that has companyID and companyNumberOfAccounts data.
Is it possible to use the SQL Server Destination or OLE DB Destination Integration Services elements to import companyID and companyNumberOfAccounts data so that existing records just have their companyNumberOfAccounts column updated?
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
I am trying my first bulk update to an existing SWL table from a CSV text file,The text file naming is exacrtly the same as the SQL table, with the same attributes
The statements: BULK INSERT [Jedox_prod].[dbo].[B_BP_Customer] FROM 'c:Baanjedox_dailyjdcom4401.txt' WITH
[code]....
The error message is: [size=1Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (BP_Country). Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".size=1]..The have checked and re-checked the BP_Country field ( the 1st field after the key) and I am not seeing any mismatches.
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t Set CASE **WHEN Isnull(td.IsPosted, 0) = 0 THEN t.AODYD = td.ODYD** *ELSE t.DAODYD = td.ODYD* END From #ReportData As t Join @CIR AS tmp On t.RowId = tmp.Max_RowId
Have installed SQL Server 2008 R2 Express (includes SSMS tool) on Windows server 2008 R2 sp1 without any issues.Database created with no issues, full text catalog created via the wizard also with no issues but cannot run the process as a scheduled task of updating the catalog because the SQL agent is not available in the express version.
The full text index information is already being populated and updated by a third party application so this leaves just the catalog to be updated as and when new full text information is available.
I have a third party SQL scheduler which will run SQL scheduled tasks but requires a script to run the full text catalog update process
Is it possible to extract a script from the existing full text catalog to run the update process or how to create a script from scratch to do the same update catalog process in the third party scheduler?
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1 SET FeeInvalid = 'Y' FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID WHERE t1.CustID = t2.CustID AND t2.OrderDate > t1.OrderDate AND t2.FeeDate > t1.CancelledDate CREATE TABLE [dbo].[MockData]( [OrderID] [float] NULL,
Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.
1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282) failed with error code 0xC0202009. 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.
2)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
3)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
I develop ASP.NET applications and use MS SQL 2000 Enterprise.
Each customer has one database and one custom design application. And, there is a gateway and a central database to get data from all databases.
Supposed there are 10 DBs. I want the data in those 10 databases updating to the central database directly. So, the gateway can gather all data of 10 DBs from a central DB.
Now, my question is how should I do it in MS SQL 2000? Can I do it with a stored procedure (SP)? If so, how does the SP like?
Or, SP can only perform on it's own DB, but not execute across DBs? If so, how can I do that?
I am new to programming in SQL and in VB. I use VWD to create web applications with a Hosted SQL database, locally though the databases are created in SQLExpress through Visual Web Developer. I have used the SQL Publishing Wizard to upload my local data to the web sql server. But, I'd like to have the same local database as is on the web, which is being updated by users, both data and user / membership data but am not sure how to create an easy, or even programmatical way to update both databases periodically. Thank you for your help. Cjsteury
Hiya! I'm developing an app. using SQL Server 7 (as the back end) for a company supporting approx. 200 unrelated clients, all in different locations. There is no LAN or WAN connection between them, so we'd probably need to use TCP/IP. The problem is as follows: We have dynamic reports in our app. which are run based on data in two tables. Whenever we add a new report we'd like to send it to all of our clients i.e. update their tables to reflect the additional rows of new report info. How would this best be handled, by DTS or Replication or BCP? And how could it be done with either?
I need assigning a group of users read, write, update (not delete) permission on all user databases. is there any way to do so instead of creating role on each database separately.
I have to DBs. One is the original and the other is for testing. They both have similar data structure. Wanted to know if there is a way to update tables contents in one db with data that is in the other one.
Is there a way to compare two databases one being an olddatabase and the second being a new database, I willupdate the old database's objects to match the new database'sobjects?Is there SQL code that could do this easily?How do you do this if you deal with the same scenario?Thank you
we have a new requirement that calls for also (in addition to RS's automatic directory updates) updating an organizational application database with metatdata about instances of scheduled reports, or alternatively having our app query RS's metadata for such info.
So, let's say we have a report called Sales Detail, and it runs at 8am every day of the week for client A thru the scheduling mechanism that comes with RS. Each time it runs to completion, an entry would have to be made in our organizational database representing a new run (instance) of the report under the client for whom it was run, in this case A.
Forgetting for the moment potential issues with a reporting system interfering with the transactional, or vice versa, can datasets in RS also do updates?...or can the scheduling mechanism in RS be extended to also log info to other places?...or is the metadata kept by RS (about instances of reports) easy to get to and decipher, and possibly be extended to record for which client this particular instance was run?
I would like to write a query that will join two tables in separate databases (same server). Then update a field in one table using data and criteria from the other.
Its easy to do this in Microsoft Access by linking the tables using ODBC, but I found its not so simple to do directly in SQL Server
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
