Update Sql Server With Data From A Different Sql Server
Aug 1, 2006I have two different sql server databases on different server.
I need to do a select from one database and based on the results, update a record on the sql server.
thanks,
ADVERTISEMENT
SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows
Dec 12, 2014I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
SQL Server 2014 :: Update Old Data With New Data
Jun 5, 2014I got this script but all I get are the errors:
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
script:
UPDATE artikelen
SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,
verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,
gewijzigd = getDate()
[code]...
A Update Data Optimization Job Freezed The Server
Jul 2, 2001Hi, everyone,
I have a SQL2000(SP1)/Windows2000, and a database - 4GB. The server physical memory is 1GB, and about 900MB is allocated to the SQL2000 through the memory dynamical configuration screen.
A scheduled database maintenance job was created using the standard maintenance planner tool. In this job, only Updata Data Optimization was included. Everytime, this job freezed the whole server - all screens, SQL client accesses, and, even the keyboard. A server machine reboot had to be made.
My understanding about Optimizatoin is that it does reindex for each index of each table in the database. So I picked the largest table in the database - about 1.5 million rows - to run DBCC DBREINDEX manually against each index one at a time, using the Query Analyzer. They were all running through, without causing server lockup. However, I noticed that each time, the DBREINDEX statement made the CPU and Physical I/O climbed extreamlly high. I suspected that, since the Optimization job put all tables together to DBREINDEX, it might exhaused the system resource that caused the server problem, but I am not 100% sure, neither do I know where to confirm this.
Confusing enough, other databases on the same server didn't have the problem. They all have maintance plan and run ok. I did all sorts of DBCC checks against this database which I had problem with, they were no error reported. I even loaded the database, using the backup, to another SQL 2000 server, and run Optimization there, and it had no problem.
I am not sure it is a server issue? or a database issue? or a SQL bug? Anybody had same experience?
Appreicate!
Regards! Yan Li
SQL Server 2008 :: Update One Row With Data From Different Row In Same Table
Sep 11, 2015I need to update the Denominator column in one row with the value from the Numerator column in a different row. For example the last row in the table is
c010A92NULL
I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.
This value is 668 so, the row should look like
c010A92668
create table dbo.TEST
(
MeasureID varchar(10),
GroupID char(1),
Numerator float,
Denominator float
)
[Code] .....
How To Update User Input Data Into Sql Server
May 28, 2007hi ,
i am new to this.
how should i update the user input values into sql server database?
i am using asp.net and c#
FIELDS are-
userId,
name,
description,
startTime,
endTime,
audiencePassword,
presenterPassword
i know it must be simple...but i haven't worked on this before.
Jaimin
Automatic Data Update From SERVER To All CLIENTS Connected ???
Jul 20, 2005Hi,i have a problem about the CLIENT-SERVER architecture procedure.Well , i have an application in VB with ADO connection to a table in adatabase on a SQLSERVER 7.0 .Is possible to do that when a client updates a data in a field of my table ,the SERVER communicates to all clients connected to my table that this dataare updated , without the client do anything , for example without aclient-timer to control the data in the server ???thanks
View 2 Replies View RelatedData Warehousing :: Bulk Update With Server And SAP BODS
Oct 16, 2015I'm having issues with bulk update in SQL Server.I'm using SAP BODS as ETL tool and have some 20000 updates.target table has approx 0.5 million records and it has clustered index on id column.I have selected upsert option in BODS.Same setup is also done for Sybase IQ , IQ has bulk update option which is giving very ood performance.
In IQ same update load is finishing in some 9 minutes where SQL is taking more than 2 hours for same, this doesn't seem right.When I look at update is causing whole package to go slow.Sybase is creating query where is ID is present then do update else insert.Is there any way to make bulk update work faster in SQL environment?
Update Server Table Based On Flat File Data
Feb 13, 2014I have a table dbo.Sales that contains all sales records. There is a column in that table called ItemNumber that I'd like to match with ItemNumber in a flat file and update the ItemCost based on the ItemCost column in the flat file.
So while there will be many sales records for each ItemNumber, I need to loop through and update the ItemCost in that sales record based on the corresponding ItemCost in the flat file. Does this make sense? I really need this for court and I can't figure out how to do it. I took a SQL course about 7 years ago but have forgotten everything.
Database Name: BTData
Database Table: dbo.Sales
Database Columns: ItemNumber (match on this), ItemCost (update this)
FlatFile Name: InventoryCosts.txt
FlatFile Columns: ItemNumber, ItemCost
There will be many sales records for each ItemNumber in the database table. I need to update each one with correct cost based on the item number and cost mapping from flat file.
Using Custom Query To Retrieve And Update Data From Sql Server In SharePoint
Apr 5, 2007is it possible to make a custom query to fetch and update data from sql server 2005 in SharePoint designer
i make a new data source library and use a custom query to get data but don€™t know how to configure update custom command
can any buddy help me out
SQL Server 2008 :: SSIS To Import And Update Data If Exist On The Destination?
May 25, 2015I am working on SSIS wehre I need to work on a flat file as a source and needed to import it to database. If the destination table have the record already, I need to update it and if not exist, I just need to import the whole data.
View 9 Replies View RelatedHow To Update Selected Columns Of A Table In SQL Server Db Using Data From A Excel File?
Apr 4, 2007Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies View RelatedHow To Pick Up Data From SQL Server And Update A Oracle Database Using A SSIS Package
Mar 5, 2007Hi,
I have a table in SQL Server 2005 which has [Id] and [Name] as its columns.
I also have a Oracle database which has a similar table.
What I want to do is as follows:
In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Thanks.
SQL Server 2008 :: Normalizing Data Prior To Migration (Update String To Remove Special Characters)
Aug 21, 2015I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes
Jan 2, 2008What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages
Nov 11, 2007I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE
Apr 16, 2015If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
Update Function: Why SQL Server Update An Empty String With 0?
May 13, 2008I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
SQL Server 2014 :: Synchronize Table On Remote Server Via Update Trigger Failing
Jul 21, 2015We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
[Code] ....
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
Update Server Name When Move SQL To New Server?
Jan 7, 2001Hi all,
To move SQL server on SRV1 to SRV2, I use following steps:
1. Detach all my user-databases on SRV1
2. Stop SQL Server on SRV1, SRV2
3. Copy all my user-databases files on SRV1 to SRV2
4. Copy MASTER files on SRV1 overwrite to MASTER files on SRV2
5. Restart SQL on SRV2
6. Attach all my user-databases o SRV2
then when I use SELECT @@SERVERNAME on SRV2, I get result SRV1. Which the best way to update system with right server name?
Thanks for any help,
PTH
Inserting Existing Data From One Server To Another Server FOR MS-SQL Server
Nov 14, 2006hi guys!
i have this task to do, well lets say i have a DB in a Server lets say Server1 and i want to copy or insert this data to the Server2.
Is there any easy way to do this? copying the Keys automaticly? What is the best way to copy the data?
Thanks a lot. novelle
SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server
Sep 11, 2015We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
Best Practice To Pull Data From Sql Server 2000 To Sql Server 2005 With Dynamic Queries
May 3, 2007Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
How Can I Get The 'entire' Database, Structure And Data, From SQL Server 2008 To SQL Server 2005?
Jun 20, 20081) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
SQL Server 2008 :: Import Table Data From Production To Development Server
Feb 18, 2015Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedSQL Server 2008 :: How To Write A SELECT Statement To Get Data From A Linked Server
Feb 23, 2015I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
SQL Server 2014 :: How To Copy Data Rows From Hosting To Local Server
Mar 11, 2015I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:
We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.
Here is what I want to do:
I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.
SQL Server 2014 :: How To Import Data To Server From Access Database Using Script
Sep 22, 2015I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
[code]...
The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.
Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')
Sql Server Compact Edition Data Source Missing In Server Explorer
Apr 3, 2007Trying to learn how to use Sql Server CE, Tutorial says to open server explorer, add connection, new connection, select data source as .net sql ce (words to that effect). On my visual studio, it ain't there and I can't figure out how to get it there. I have uninstall all of the Sql Ce stuff and reinstalled it. So I'm missing some key link. What are the magic incantations to get to first base?
Ed Warren
Problems Reading Data From Linked Server To Excel In SQL Server 2005
Oct 4, 2007I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
Cant Sort Data In Exported Excel File From Sql Server Reporting Server
Feb 7, 2008
Hello,
I am new to this sql server reporting server technology. I have a requirement like the table header should repeat in all pages of the exported excel's print preview. So I have created the header columns with fixed lenths as same as table's header in the page header of the report. Then only the Header will repeat in all pages of the print preview.
But the problem is when doing like this, many of the columns have been merged together. The report layout is ok. But cant sort the data. It says a message "This operation requires the merged cells to be identically sized."
In some forms i have found the header control's edges need to be sized identically with the tables' columns edges. I did it. But still the columns are merged when exporting.
Please help me out from this.
Thanks in advance.
Error While Subsciber Syncronizing Data With Sql Server 2005 Database Through IIS Server
Jan 16, 2008
internal error: HTTP header information is either corrupted or missing in the transport message. It could be a network transmission error or an IIS problem.(28035).
Any one of you have any suggestions??
What Is DT_UI8 Equivalent Data Type In SQL Server 2005 For Integration Server
Aug 13, 2007Hello,
I'm trying to build a integration service package importing data from XML files and directs this data to different MS SQL server 2005 Database tables.
Can someone suggest me what is equivalent(mapping) data type of DT_UI8 in Sql server 2005 for Integration Services.
Or how to consume DT_UI8 fields in SQL server 2005.