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


ADVERTISEMENT

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

Transact SQL :: Server Script To Run On Multiple Servers

Nov 4, 2015

I have a backup checking script on my server. I want to change the script to run on multiple servers, and below are my requirements,

1. I have an input file with my sql script which i want to run in command prompt and i have a list of servers in an another input file.
2. I need a script to take the input from the input file , process my sql script and produce an output for each servers.
3. Or can we use SSMS or some sql agent script to make it possible ?

View 4 Replies View Related

Transact SQL :: How To Compare Columns On 2 Databases On 2 Different Servers

Aug 12, 2015

I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.

I'm looking for added columns in dbase 1.

Is there a system T-SQL script that can be used for this?

View 4 Replies View Related

Transact SQL :: Looping Through Multiple Servers And Databases In Stored Procedure

Aug 27, 2015

I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.

I have table "dbo.instances" with list of instances. 
I have databases from "sys.databases". 

How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.

View 3 Replies View Related

Transact SQL :: Powershell Script To Fetch Database Usage Details For Multiple Servers (report)

Oct 28, 2015

Is there a way to fetch database usage details for multiple SQL servers (report) usirng powershell script.

Details: servername, databasename, datafile usage, logfile usage, free % age...etc.

View 3 Replies View Related

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

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

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 :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 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

Transact SQL :: Use Query Results As Select Criteria For Another Query

Jul 10, 2015

I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.

While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.

View 6 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Transact SQL :: Query Plan Shows Table Not Even In Query?

Jul 22, 2015

I am trying to optimize a stored procedure in SQL 2008.  When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.

I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it?  I can't just change the query script because the table in question isn't there.

View 10 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:bituniqueidentifier================================================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







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