Quering Multiple Servers

Apr 13, 2007

I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.

Any help on this would certainly be appreciated.

Thanks in advance.

View 2 Replies


ADVERTISEMENT

Quering Linked Servers (Oracle)

Dec 22, 1999

Hi!

I created linked server to Oracle 8.0.5 on ODBC data source. When I run query like:
select * from linked_server_name..Schema_name.Table_name
it hangs up
I read in Books Online that the syntax is:
linked_server_name.catalog.schema.object_name
What is the _catalog_?

And also, is there a way to run PASS THROUGH QUERY against linked server, but not using OPENQUERY?

View 2 Replies View Related

Creating A Database From Multiple Databases Accross Multiple Servers

Sep 13, 2007

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a



I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

View 8 Replies View Related

Quering Email Names

Mar 13, 2008

I am trying to write a query to exclude a set of particular users from our Users Table where they have particular domain names

We want to exclude all users that have a domain of @dnb.com & @test.com.

Can anyone suggest anything?

View 1 Replies View Related

Null Values When Quering Sql Server From VC++

Oct 1, 2001

Hello, i´m inserting values in a table defined as above,

/********
create table SERVICE_TYPE (
PK_TYPE numeric(8) not null IDENTITY,
TYPE nvarchar(32) not null unique,
DESCRIPTION nvarchar(256) null default(''),
USER_VISIBLE numeric(1)not null,
constraint PK_SERVICE_TYPE primary key (PK_TYPE)
)
go
**********/

these values are inserted throug ODBC with VC++,
in the case of the DESCRIPTION Column, i´m sending null strings CString(""). this value will be filled after.
What happens is that, when i query the database to show the records in this table, the database shows, for example:

PK_TYPE TYPE DESCRIPTION USER_VISIBLE
--------- ------ ------------------- -------------
1 1 (null) 1

i dont want to show the "(null)" value in the column DESCRIPTION, but a null string like ""

can anyone help me , thanks

Cristovão

View 1 Replies View Related

Quering AS2005 Cube To Generate CSV Or Excel In SISS 2005

Jun 22, 2007

can any one sugest Quering AS2005 Cube to generate CSV or Excel in SISS 2005

i have a mdx query that runs fine in SQLserver 2005 but when use OLEDN source in SSIS and connect to Flatfile source i am getting OLEDB exception is there any work arround i have SQL Server 2005 SP2 on Windows 2003

Any help is really appreciated

thanks in advance

View 4 Replies View Related

Multiple Servers

Nov 15, 2007

Hi All,I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?ThanksBryan 

View 3 Replies View Related

Way To Run Same Db Across Multiple Servers?

Mar 2, 2015

What's the easiest way to run the same db across multiple servers?

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

Replication To Multiple Servers

Jul 12, 2000

suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server?
If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication?
Thank you

View 2 Replies View Related

SQL Security - Multiple SQL Servers

Feb 6, 2001

Question:

How to prevent a user/admin on one SQL 7 server, who knows the system admin password that is common to both servers, from accessing a second SQL 7 server via the SQL Server Group setup process.

Is there a way from shutting off remote access to a SQL server without limiting RPC?

All reading points towards 1) Remote Access Options, 2) Store User Independent, 3) Control Store as options to locking down a server. Would like to hear from someone that ran into this type of issue. Third party software available?

I've tried the following without any success: 1) I have deleted the second SQL server registration from the first SQL server group - but this is too easy to delete and recreate, 2) edit the SQL server registration for the remote server and force authentication - almost there if SQL prevented deletion of the registration without verifying the logon/password of the person who created it (again able to delete and recreate), 3) played around with the client utility to remove the TCP/IP pipe entry.

Anyone else pulling their hair out on this?

TIA!

View 1 Replies View Related

Multiple SQL Servers On Same Network

Nov 12, 1999

Please excuse my niavety on this subject, But I have a simple question. When you have more than one SQl server 7 installation on the same network, each using different machines, does one automatically have the ability/authority to be able to stop the other server at will??

Thanks in advance for any help

Simon

View 2 Replies View Related

Multiple SQL 2005 Servers

Mar 12, 2007

Joe writes "I am trying to find whether I can have multiple SQL 2005 servers but having same security profiles and maintain them grom one place only. I prefer not to have windows authentication.


Thanks a lot"

View 1 Replies View Related

Acces Multiple Servers

Nov 15, 2006

Hi,

I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...

Is there a way to do that...

I need to connect to two servers from the same procedure to get data..

Please get back

Thanks,

View 3 Replies View Related

Querying Multiple DB Servers

Nov 27, 2007

Hi,

I want to perform a join on two tables from two separate database servers.




Code Block
SELECT *
FROM tbl1 AS t1
INNER JOIN
"SERVERASQL2000".Production.tbl2 AS t2
ON t2.UserID = t1.UserID






Why do I get this error...

Invalid object name 'SERVERASQL2000.Production.tbl2'

thanks.

View 10 Replies View Related

Manage SQL Servers On Multiple Domains

Aug 24, 2000

I have to maintain several SQL Server 7 DBs across multiple NT Domains (same network). How do I access the SQL Server on the second domain from enterprise manager?

Thanks!

Lia

View 1 Replies View Related

Copying Sprocs, DTS To Multiple DBs And Servers

May 3, 2007

We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.

We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.

View 4 Replies View Related

Running Multiple Db Servers For One Db (import/exp

Feb 29, 2008

Hi,

I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.

Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.

I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.

I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?

I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.


Thanks very much,
mike123

View 2 Replies View Related

List Of Databases From Multiple Servers Without SMO

Nov 14, 2007



Is it possible to get an aggregate list of databases from multiple servers, without using SMO?

All target servers are running SQL Server 2005.

Ben Aminnia

View 2 Replies View Related

Licensing Multiple Passive Servers

Oct 17, 2007



Hi, I was wondering what the licensing requirement would be for following scenario:

1 active sql server installation installed on a windows failover cluster.

1 passive mirror sql server (single server).

1 passive log-shipped sql server (single server).

In the microsoft white paper it says:

When doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license, provided that the number of processors in the passive server is equal or less than those of the active server. The passive server can take the duties of the active server for 30 days. Afterward, it must be licensed accordingly.

So do I need to buy an enterprise license for each of the additional passive servers...in the above example meaning I would need 3 licenses in total?

Thanks,
Paul

View 3 Replies View Related

Accessing One Database From Multiple SQL Servers

Dec 1, 2006

Apologies if this question has been asked before. I am about to upgrade a multiuser application based on Access97 and VB6 to C# and possibly SQL Server Express.

My customer does not want to use a server based database solution ie a single instance of SQL Server, but prefers to have a common networked dataset which is accessed via user's own individual database engines. This is because of restrictions on the installation of server based apps.

Does this scenario completely rule out the use of SQL Server Express?

Thanks

View 3 Replies View Related

Sending The Same Message To Multiple Servers

Jul 19, 2007

Can anyone outline some best practices for sending the same message out to multiple servers? What is the easiest way to do this?

Currently, I have a message sent from Server A to Server B, which is fired by a table insert, update, delete trigger.

The goal is to keep this table synchronized on many remote servers. It sounds like a tedious exercise to deploy the SSB messages, queues, services, etc to 20+ more sites! I am hoping there is a shortcut of some kind.

Any advice is appreciated.

View 7 Replies View Related

Binary Data On Multiple Servers

Feb 18, 2007

I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.

When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).

--To create my cert and key:

USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO


To encrypt:

OPEN SYMMETRIC KEY MyKey

DECRYPTION BY CERTIFICATE MyCert

WITH PASSWORD = 'same password as above';

Insert my record, use scope_identity to return primary key into @CustomerID.

INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @DataToEncrypt, 1, CONVERT( varbinary, @CustomerID)))

CLOSE SYMMETRIC KEY MyKey

To decrypt:

SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @CustomerID))) as PlainTextData

FROM Customers

WHERE (CustomerID= @CustomerID)

Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.

Thanks in advance for your help!

View 5 Replies View Related

Distributed Partitions (Across Multiple Servers)

Jul 4, 2007

I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.



I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.



Any help would be great.



Thanks!



Eric Elliston

SWFLParent.com

http://www.swflparent.com

View 1 Replies View Related

Retrieving Data From Multiple SQL Servers In Parallel

Nov 9, 2001

I have multiple SQL 2000 servers that hold data which I retrieve using a single SQL stored procedure. Unfortunately, I don't know how to retrieve this data asychronously (in parallel).

For example, I retrieve data from two linked servers using the following:

select * into #temp from openquery(SomeLinkedServer,'exec BigQueryHere')
select * into #temp2 from openquery(OtherLinkedServer,'exec BigQueryThere')
.
.
lots of manipulation of the temporary tables to get what I want, etc.
.
.

The problem I have with this is that there is no reason why the first query should have to finish before the second query begins (serially) because these are on separate servers. Is there a way to execute these so that they run at the same time?

Bill

View 1 Replies View Related

Creating Triggers Among Multiple Database Servers

Nov 14, 2000

Hello,

I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?

Any help is appreciated!!
Thanks,
Lisa

View 1 Replies View Related

Connect Multiple Servers In MS SQL Server Express

Jun 26, 2007

How do I connect DB's on different servers? I can't find a good explanation that works for me.

View 1 Replies View Related

SQL 2012 :: Multiple Database Servers - One Instance Name

Apr 24, 2014

Scenerio : To keep a very large system running optimally in a VM cluster, Take PR01 and make PR02, PR03, PR04. Distribute the 45 databases and 9T+ of disk across multiple VM guest.

Each PR## is a SQL Server 2012 Enterprise guest on a VM 5.1 cluster.

So instead of PR01 needed 16 core and 128g of memory, each one will have 4 core and 32g of memory. Making VM HA more manageable. (yes, DRS rules will apply). Also provides more HBA paths and distributes i/o over more physical disk on the SAN.

Instead of a connection string having to know PR01.dbo.UserDB01, PR02.dbo.UserDB03, ect the connection would be PRDB.dbo.UserDB01. That way if needed 1) UserDB can be moved to any of the PR## 2) new PR05, PR06 can be added as needed. The end user and processes are not allowed to touch system databases, no PR## will have a user DB called the same name.

There are seperate VM guests on other VM clusters and Citrix servers that need access to PRDB. As things expand and move around, none of the connection strings need to be changed.

I am looking into RadWare and modifing level 7 information, but that is iffy and $$$$$$.

View 1 Replies View Related

SQL 2012 :: Combining Multiple Servers Into Instances

Oct 14, 2015

Curious what the industry standard is combining multiple instances on single server ?

Right now i have separate servers for OLTP, SSAS, and SSIS, and for OTP have Development, Test, and production environments. Im considering combining the SSIS and SSAS services into additional instances on each environment servers.

Now (Production)
OLTP
SSAS
SSIS

After

1 Server with 3 separate instances for SSAS, SSIS, OLTP. Then replicate this model through the environments to utilize development and testing.

View 2 Replies View Related

Create View With Data From Multiple Servers

Jul 23, 2005

Hi everyone,I have 5 servers, all with identical databases just different data. Ihave a rather lengthy SQL statement (in a View) to hit one database andpull-in certain data, but I'd like to somehow run this same SQLstatement within the view but hit all 5 servers so we don't have 5different versions of this data to mess with.I'm not opposed to creating an update query in a stored procedure tohit all 5 databases and update a table or even do this within a DTS,but I'd prefer to keep it as simple as possible and as dynamic so theusers can simply run the view and get live data anytime based on all 5tables.Is this possible ???Thanks,rlangly

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

How To Bulk Deploy Reports To Multiple Servers?

Mar 23, 2007

Hi,

I'm just wondering if there is any easy way to deploy reports to multiple servers. My current issue is that I need to deploy tens of reports onto tens of production server. Does anyone know how to do that?

Thanks a lot.

Cheers,

View 1 Replies View Related

Table Snapshots Accross Multiple Servers

Apr 20, 2007

Hi,


Is there a way to take a snapshot of a table at precisely the same time on multiple servers and write the table snapshot to another 'work' database? I'm asking in this forum hoping that perhaps SSIS has a built in tool to help with this. I'm wanting to audit table(s) accross seven different servers. In order to do this I want to make sure that I have the table at the same moment in time on all the servers. I plan on using the tablediff utility to then compare each table on each server against one another. If somebody knows a better method to do this please let me know.





Thanks,

Phil

View 1 Replies View Related







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