SQL Query Over 2 Sql Servers

Jan 24, 2008

Hi,

I am a newbie to SQL and learning as I go.

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

View 7 Replies


ADVERTISEMENT

Query Across Servers

Jan 25, 2005

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?

Thanks and sorry if it's too remedial a question.

View 2 Replies View Related

Query Between Different Servers

Feb 4, 2008

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.

View 2 Replies View Related

How Can I Query This On Linked Servers...

Nov 13, 2006

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.

View 1 Replies View Related

Update Query Across Servers

Jul 10, 2007

Hi,
 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? ThanksMsg 102, Level 15, State 1, Line 1Incorrect syntax near 'a'.Msg 102, Level 15, State 1, Line 3Incorrect syntax near '-'.
 
 

View 2 Replies View Related

Query From Multiple Servers

Jun 3, 2008

hi
Is there a way to have a select query on multiple servers ? (2 databases , each one located on a sql server)

View 2 Replies View Related

How To Query Data From To Servers

Aug 25, 2000

hi,
I have a question. how do query data between the two servers.
thanks
kumar

View 2 Replies View Related

Query Remote Servers On 6.5?

Jul 23, 2002

Hi, all -

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?

View 1 Replies View Related

Two Servers Same Query Different Timings

Apr 25, 2008


Hi,

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.

Many thanks

View 7 Replies View Related

2 Databases On 2 Servers, 1 Query?

Jan 25, 2008

Hello,

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




Code Snippet

Select
Server1.CRM.Accounts.Name,
Server2.Application.Transaction.TransactionID,
Server2.Application.Transaction.AccountID,
Server2.Application.Transaction.Amount



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.


Many thanks for any information provided.

View 3 Replies View Related

Transact SQL :: Run Query On 2 Servers?

Sep 24, 2015

i need to run query that will run on two servers.

one is local (Server1), and one on 192.144.22.22 (Server2)

i try this:

    SELECT  *
    FROM    [Db1].[dbo].table1 A
    INNER JOIN [Server2].[Db1].[dbo].table2 B 
    ON A.Id = B.Id

but this work fine because the two database is on the local machine.

how to do it ?

View 2 Replies View Related

OPEN QUERY: - LINKED SERVERS

Jan 11, 2000

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!

View 2 Replies View Related

Query From Interbase Using Linked Servers

May 8, 2007

created a linked server. But i can't query the table.
i tried different combinations but they dont work.



LS: dccs_danville
DB: intdccs_dv.gdb
table:tblload

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.


anyone know what is wrong?

View 3 Replies View Related

How To Query Two Tables In Two Different Servers In SSIS

May 21, 2008



Hi All,

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.

Hope any one will reply soon.

Thanks,
lmp

View 7 Replies View Related

How To Reference Different Databases On Different Servers In One Query

Apr 28, 2006

 

View 10 Replies View Related

How To Access 2 Databases On Separate Servers From Within The Same Query

Apr 10, 2008

Hi,

Im trying to access data from a database on another server in a SQL 2005 query.


use Bury2k29.ServiceDeskForms
select .......


but I get the message


could not locate entry in sysdatabases for database 'Burky2k29'. No entry found with that name. Make sure that the name is entered correctly.


Bury2k29 is the name of the server, and ServiceDeskForms is the database I want to access.

When I open a blank query and enter only the code to access that database it runs fine.

Any ideas?

View 4 Replies View Related

Transact SQL :: Create A Query From Two Physical Servers?

Oct 15, 2015

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 ?

View 7 Replies View Related

Query Error: Collation Conflict (on Tables On 2 Different Servers)

Jun 30, 2005

Hi all,



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')

Thanks

Tryst

View 3 Replies View Related

Interesting Requirement! How To Query Port Numbers From 120 Servers!

Dec 7, 2004

I need to document the port numbers from nearly 120 sql servers in the network. Is there any query that I can use to get this info??

Thanks,

View 13 Replies View Related

After SQL 2005 SP2, Query To Apartment Linked Servers Fails

Mar 6, 2007

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.

View 4 Replies View Related

Query MOM Database To Find Servers Uptime And Downtime.

Sep 5, 2007

Hi friends,

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]

(

@SearchStr nvarchar(100)

)

AS

BEGIN



CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)



IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

END

END

SELECT ColumnName, ColumnValue FROM #Results

END############################################################################

View 1 Replies View Related

After SQL 2005 SP2, Query To Apartment Linked Servers Fails

Apr 4, 2007

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

View 7 Replies View Related

SQL Server Admin 2014 :: Query Multiple Servers With A Scheduled Job Using CMS?

Mar 13, 2014

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.

View 1 Replies View Related

View Performance, Linked Servers, Query Specifiying Uniqueidentifier

Jul 20, 2005

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

View 5 Replies View Related

Possible To Query Linked Servers WITH OUT Distributed Transaction Coordinator Enabled?

Jan 24, 2008

Is it possible to query linked servers without the Distributed Transaction Coordinator service enabled or allowing network access?

Is that ONLY for transactions? What if I just wanted to read the data and nothing else?

View 13 Replies View Related

Sql Report Works Fine On Internal Servers - Hosed On External Servers - Need Some Help

Nov 21, 2007

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!

View 1 Replies View Related

SQL Server 2012 :: Query Servers From A List For File Storage Information

Oct 13, 2014

I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:

SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB

[Code] ...

How would I best accomplish this if I want to implement it using a TSQL procedure?

View 4 Replies View Related

Development Servers, Auto-update Live Servers

Aug 21, 2001

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,

Thanx,

Darren

View 1 Replies View Related

Linking SQL 2005 Servers To SQL 2000 Servers Problems

Sep 27, 2007

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

View 2 Replies View Related

Two Servers

Jun 19, 2001

Hi,
We have two servers one is the LIVE SERVER and another one is TEST SERVER. The Dumps from LIVE SERVER has been restored onto TESTSERVER. Now, when I try to run some applications in TESTSERVER it starts executing it in LIVE SERVER.
When I checked up the SELECT @@SERVERNAME value in TEST SERVER, it still refers to TEST SERVER only.

Is there any other table or value I need to check.
Can anyone help me on this.

thanks in advance

View 3 Replies View Related

BCP Across Two Servers, Is It Possible..How??

Mar 15, 2000

I need to archive about a years worth of data from 4 tables, but as available disk space is very limited on the source server, I need to copy out the selected records to a seperate server. Is this possible and how.

View 1 Replies View Related

What Sp Are My Servers On?

Feb 22, 2006

Hi,

does anyone have a script that I can adapt to collect the status of all my servers please? I am managing 20+ servers, plus MSDE installs with various applications and its now a nightmare tracking each one. I am building a central database where information such as this can be referenced but i need to populate it and then updated it regularly, hopefully with a scheduled job and some nifty sp's.

any ideas how i can achieve it if the scripts are not already around? i use PRINT @@VERSION currently, can i execute this in a different format to assess a different server from the one running the process?

thanks

FatherJack

View 4 Replies View Related

Where Did My Servers Go??

Jun 11, 1999

Has anyone ever written any stored procedures that unregister all servers
on startup/shutdown of enterprise manager??

I am looking for a way to do this so I can force the user to login to a
server each time they want to access it.

Thanks in advance for any advice!

- J

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved