How To Query Data From To Servers

Query Across Servers

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.

SQL Query Over 2 Sql Servers

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

Query Between Different Servers

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.

How Can I Query This On Linked Servers...

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.

Update Query Across Servers

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

Query From Multiple Servers

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

Query Remote Servers On 6.5?

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?

Two Servers Same Query Different Timings

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

2 Databases On 2 Servers, 1 Query?

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.

Transact SQL :: Run Query On 2 Servers?

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 ?

OPEN QUERY: - LINKED SERVERS

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!

Query From Interbase Using Linked Servers

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?

How To Query Two Tables In Two Different Servers In SSIS

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

How To Reference Different Databases On Different Servers In One Query

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

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?

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

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 ?

Data Transfer Between Two Or More SQL SERVERS

Hello,
I want to transfer data in between two or more sql servers. Actually One server is on the web while others are in small networks in diffrent locations.
I want to update the main server with the changes in local servers. My problem is if  the connection goes failed in between process of data transfer then how will it be recovered that how much of data is uploaded and how much is left.
Please I need help on this isue.
Thanks in advance
pronov 
 
 
 

Getting Data From Remote Servers

 
 am an authenticated user of a remote data base
I can log in using sql server management studio and add tables and
even I can back up the database .
Good.

 But I want to get a copy of the
database to my local computer
Is there any way to do
this?The problem is that i don't have access  to the directory of sql sever  pleases tel me a way that is possible just by using sql server or any add on on it 

Data Access Across Servers

Hello,
I have a developer here who wants to be able to access two databases on different servers with the same query statement.
Both servers have SQL Server 7.0 ??? Any suggesstions?

Joanna

Replicating Data Across Servers

We have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you.
W.

Accessing Data Across 6.5 Servers

Hi,

I need to access data across my sql servers . All the servers are 6.5. Think MS DTC is the solution. But how to implement the same. Can somebody give me step by step instructions.

Thanks in Advance.

Cheers

Transfer Data Across Servers

Hi,

Not sure thisis the right forum for my question.
I have to insert data from one table into another. The problem is that the 2 tables are on different servers and computers for that matter. I only need data that is in one specific table.
What is the easiest and most efficient way to do that.

If I access the 2 computers via remote desktop from my computer and actually go and copy the data from the table in computer 1 and paste it inside the table in computer 2 it does paste the data.However, for some reason it doesn't paste all the data. The column datatype is ntext.
I mean if the entry is for example 'hello world how are you today? blah bla blah'
It will only paste 'hello world how are'

I will appreciate your help.

Many thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View Data From 2 Servers

Guys,

How do I create a view that will pull data from dbases on 2 different servers ?

I have 2 server names....

svr10mbr01
svr11mbr02

2 Databases...

PWBstaff on svr10mbr01
PWBdata on svr10mbr02

I need to create a view in PWBdata that will do something like....

Select * from PWBstaff.dbo.staff

Is this possible, if so what is the syntax.

Ta
Bob

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

Transfer Data Between SQL Servers

Hi,

I've got a quick question here. I'm still quite new to SQL Server. My question is how to transfer file between SQL Servers. For example, i have been working on the database - updating or editing some tables in the SQL Server 2000 at my end. Then, i need to pass whichever tables got updated on to my client who also uses the SQL Server 2000. So we have two separate SQL Servers 2000. i wonder how to transfer data like tables between the two SQL Servers.

Thank you in advance

Data Collection From Many To One Servers

I need to feed head office sql server with the data from regional servers. Servers are spread through all continents
Data input done locally on Head office server as well and plus need to ship data from other servers.
So clarify this - Head office server is not standby one. Mirroring is out of the picture, I think..
Initially, I thought ship a log every 15 min and restore on Head office server but is this going to create an issue for the local data processing?

Any bright ideas welcome!

Transferring Data Between Servers

I need to append data from a database on one server to a table in a databaseon a different server. Both servers are running SQL 7. How can that be done?Thanks.

Sync Data Between Two SQL Servers

Ok..here is my problem. I have two SQL servers that I need to haveidentical, at least semi-real time data on. One is in a public DMZwith full access to and from the internet, and one is behind acorporate firewall and the box can get out to the internet but cannotbe hit directly from the internet in. The two are on seperatenetworks and cannot be connected via lan. Is there a way for me tohave semi-real time data transfers between the two to keep bothservers identical? I have thought that the server behind the firewallcould initiate an XML session with my dmz sql server and processupdates, but I am unfamiliar with how it would know what has changedon the remote server and how the remote server would know what haschanged on the server behind the corporate firewall. Any good ideas?ThanksDan Hirsch

Synchronize Data Between Servers

What is the easiest way to synchronize all data and database objects between my development machine and hosting server? Is there any SSIS free script I can use?

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

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

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

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,

After SQL 2005 SP2, Query To Apartment Linked Servers Fails

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.

Query MOM Database To Find Servers Uptime And Downtime.

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############################################################################