I'm trying to write an update query across two servers but getting getting two errors.
The first server is called: blatweb2
The second server is called: blbr-teamserv
This is the query I'm trying to use:1 update blatweb2.dbo.knousefoods.products a
2 Set LuckyLeafPieFillingSort =
3 (Select LuckyLeafPieFillingSort from blbr-teamserv.dbo.knouse.products b where a.productname = b.productname) I'm getting the following errors. Do you know what could be wrong? Thanks
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '-'.
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,
Does anyone have the general syntax for updating one database on one server with info from another database on a different server? Both are SQL Server 7.0.
Hi when I run this sql script: I get an error message update server.dbname.DBO.ap_payment set pay_flag = -2 -- means were not updated in vi approved table where ISNUMERIC(venid)<>1 and pay_flag =0
I am not sure what this means, can anyone help me. thanks
Server: Msg 7306, Level 16, State 2, Line 1 Could not open table '"CESI"."DBO"."ap_payment"' 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. [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
This is my first time posting here so thanks for you assistance in advance.
I have a project I am working on where I need to use database triggers to upon update update a table on another SQL server. Below is how it would work.
Server A (SQL 2000) Table A is updated (execute trigger to update Server B, Table B)
Server B (SQL 2005) Table B (Tabled updated by SQL 2000, server).
I don't know if this can be done using the SQL server technology itself or how I would do it. Is a .NET or other program service have to be used in this scenario?
Hi!I have a stored procedure that takes 22 minutes to run in oneenvironment, that only takes 1 sec or so to run in anotherenvironment. Here is the exact situation:Database 1 on Server 1 vs. Database 2 on Server 2 - the data isexactly the same, and the tables and index structures are exactly thesame. Implicit transactions are turned off on both databases.Stored procedure:BEGIN TRANSACTION--step 1TRUNCATE myTable--step 2INSERT INTO myTable VALUES ('myValues')--step 3UPDATE aSET rating=AVG(someValues)FROM myTable aJOIN otherTable bON a.column1=b.column1GROUP BY someColumnsCOMMIT TRANSACTIONThe update statement on the problem server is the only step that takesforever. While it is running, I don't see anything that could beblocking the statement. I used the following queries to determine ifthere was another process blocking it:select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring(nt_username, 1, 15), dbid, physical_io,substring(hostname, 1, 15), program_name, lastwaittype, waitresource,memusagefrom master.dbo.sysprocesses where blocked <0order by waittime descselect dbid, name from sysdatabases where dbid in (select dbid frommaster.dbo.sysprocesses where blocked <0)select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,substring(hostname, 1, 15), program_name, lastwaittype, waitresource,memusagefrom master.dbo.sysprocesses where spid in (select blocked frommaster.dbo.sysprocesses where blocked <0)order by waittime descWhen I change the UPDATE statement to a SELECT, it still takes longerthan it does on the test server (1 min 35 sec vs. severalmilliseconds).What could be causing the UPDATE to take forever on one server/database, and run without a problem on another?I am at a loss! Any help would be greatly appreciated.
When i run above script, it prompt me an error message; "Microsoft OLE DB Provider for SQL Server The requested operation could not be performed because OLE DB provider "IBMDA400" for linked server "TESTDB2" does not support the required transaction interface."
If i run it without "begintrans" and "committrans", it update the data successfully.
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
I am currently trying to write a SELECT that returns the difference between 2 identical tables in 2 different SQL databases on 2 different SQL servers.
I believe I have setup the linked servers properly.
Here is the select I wrote;
select * from [dbnameA].dbo.Item where not exists (select * from [dbnameB].dbo.ItemSD Where [dbnameA].dbo.Item.IMA_ItemID = [dbnameB].dbo.ItemSD.IMA_ItemID)
The error I receive is;
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbnameB.dbo.ItemSD'.
Any suggestions are greatly appreciated!! Thanks, Michiel
i have two servers where i have logged in, hr and transport
i want to write a query in query analyser of the hr server to retrieve information in the transport server. how will i do it
i tried with select * from servername.databasename.permission.tablename
i get a message
Server: Msg 7202, Level 11, State 2, Line 1 Could not find server 'transport' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
For example I have 15 records in which accountID is a PK. AccountID 1,2,3,4,5 is linked on server1. I want to find out records that are not linked. So its record 6 - 15. Can someone show the stored procs for this scenario? Or the query statement to do this... Im new to linked servers. Thanks alot.
I know you can execute stored procedures on remote servers in SQL Server 6.5, but can you run regular queries as well using the 4 part name qualification?
Example: select * from server1.database1.dbo.table1
If not, is there another way I can manipulate data on remoter servers?
Please could I have some advice I am running a query on the live node of a cluster it takes well over an hour to run even at a quite ish time when CPU is bellow 50% memory disk queues all normal. But when I run it the other node it take less than a minute.
I know it would probably take longer on the live due to having users on but not that much longer, there is no blocking why is there such a huge differences.
We have adedicated MS-SQL Server for our CRM database and we have another MS-SQL database server for in house applications, written in c#.Net 2.0. To avoid duplicating information already in our CRM database, it would be nice if we could build a query that combined columns from both data sources.Example
Code Snippet
CRM database on Server1 Table Accounts
Columns AccountID UniqueIdentifier Primary Key
Name NvarChar(50)
Application Database on Server2 Table Transaction
Columns
TransactionID UniqueIdentifier Primary Key
AccountID UniqueIdentifier Amount Money
Would it be possible to create a T-SQL statement that will select
From Server1.CRM.Accounts INNER JOIN Server2.Application.Transaction
ON (Server1.CRM.Accounts.AccountID = Server2.CRM.Transaction.AccountID)
I appreciate there will need to be 2 connections and therefore some credential information would need to be passed too.
Is anything like this possible? I do not wish to copy the Name field from Server1.CRM.Accounts to Server2..Application.Transaction as this is duplication and any change to the value of Server1.CRM.Accounts.Name would not be reflected in the results.
I have successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query
************************************************** *********************8 CREATE PROCEDURE qse_check_label_projectid @projectid char(18) AS
Select * from openquery(MRTEST32,'SELECT Project_id FROM DBO.MRT_PROJECT WHERE Project_id = @PROJECTID ') ************************************************** *************************** Please I believe there should be a way to pass a value through this open query function!
select top 10 * from dccs_danville.INTDCCS_DV.gdb.tblload select top 10 * from dccs_danville.INTDCCS_DV.dbo.tblload select top 10 * from dccs_danville.INTDCCS_DV...tblload select top 10 * from dccs_danville.INTDCCS_DV.tblload select top 10 * from dccs_danville...tblload select top 10 * from dccs_danville.INTDCCS_DV.tblload
also tried EXEC sp_tables_ex 'dccs_danville' tblload is able to be queried.
I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.
I am trying to create a query that will get data from two diffrent SQL servers. I am trying to link the two servers by using the sp_addlinkedserver. This is the code I use:
USE master; GO EXEC sp_addlinkedserver   N'SC00SRVERP501R1',   N'SQL Server'; GO
and it executes successfully. But when I try to run my query I get the error message "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'."As far as I understand I need to provide some credentials to the linked server, but how do I do that ?
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username 2 FROM users 3 WHERE user_username LIKE '%[!-)]%';
I have a query that I need to run where I join two tables that both reside on different servers. I use an INNER JOIN statement to attempt to join these tables, but for some reason I am getting the following error message...
"
Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation FROM mrcsmis.INFORMATION_SCHEMA.TABLES INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME) WHERE (TABLE_TYPE = 'BASE TABLE')
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1. After applying SP2, we get the following msg when running a query against the linked server. I cannot find anything in the SP2 doc that indicates a change of behavior for linked servers. Any ideas ?
Bill
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I need a query to find out the server uptime and downtime of the server from MOM database, i don't know in which tables MOM actually stores this infomation.
I need this very urgently.
Thanks in advance
You can use this code to find out the information stored in the MOM tables:- ############################################################################ create PROC [dbo].[SearchMyTables]
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions. After applying SP2, we get the following error message when running a query against the linked server. I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers. Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. Toby
I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
Greetings,I have 3 servers all running SQL Server 2000 - 8.00.818. Lets callthem parent, child1, and child 2.On parent, I create a view called item as follows:CREATE view Item asselect * from child1.dbchild1.dbo.Item union allselect * from child2.DBChild2.dbo.ItemOn child1 and child2, I have a table "item" with a column named "id"datatype uniqueidentifier (and many other columns). There is anon-clustered index created over column "id".When I connect to the parent server and select from the viewSelect id, col1, col2, …. From item where id =‘280A33E0-5B61-4194-B242-0E184C46BB59'The query is distributed to the children "correctly" (meaning itexecutes entirely (including the where clause) on the children serverand one row is returned to the parent).However, when I select based on a list of idsSelect id, col1, col2, …. From item where id in(‘280A33E0-5B61-4194-B242-0E184C46BB59',‘376FA839-B48A-4599-BC67-25C6820FE105')the plan shows that the entire contents of both children item tables(millions of rows each) are pulled from the children to the parent,and THEN the where criteria is applied.Oddly enough, if I put the list of id's I want into a temp tableselect * from #bv1id------------------------------------280A33E0-5B61-4194-B242-0E184C46BB59376FA839-B48A-4599-BC67-25C6820FE105and thenSelect id, col1, col2, …. From item where id in (select * from #bv1)the query executes with the where criteria applied on the childrendatabases saving millions of rows being copied back to the parentserver.So, I have a hack that works (using the temp table) for this case, butI really don't understand the root cause. After reading online books,in a way I am confused why ANY of the processing is done on thechildren servers. I quote:================================================Remote Query ExecutionSQL Server attempts to delegate as much of the evaluation of adistributed query to the SQL Command Provider as possible. An SQLquery that accesses only the remote tables stored in the provider'sdata source is extracted from the original distributed query andexecuted against the provider. This reduces the number of rowsreturned from the provider and allows the provider to use its indexesin evaluating the query.Considerations that affect how much of the original distributed querygets delegated to the SQL Command Provider include:•The dialect level supported by the SQL Command ProviderSQL Server delegates operations only if they are supported by thespecific dialect level. The dialect levels from highest to lowest are:SQL Server, SQL-92 Entry level, ODBC core, and Jet. The higher thedialect level, the more operations SQL Server can delegate to theprovider.Note The SQL Server dialect level is used when the providercorresponds to a SQL Server linked server.Each dialect level is a superset of the lower levels. Therefore, if anoperation is delegated to a particular level, then Queries involvingthe following are never delegated to a provider and are always it isalso delegated to all higher levels.evaluated locally:•bit•uniqueidentifier================================================This suggests to me that any query having where criteria applied to adatatype uniqueidentifier will have the where criteria applied AFTERdata is returned from the linked server.Any ideas on the root problem, and a better solution to get the queryand all the where criteria applied on the remoted linked server?Thanks,Bernie
