Hi! I'm using SQL Server 6.5 and have a question for you regarding
views. Is it possible to create a view pulling information from
2 databases -- on 2 different servers? If not, is there a way to work
around this?
Thanks for any help!
Toni
Is it possible to create a view that spans over 2 servers? It uses 1
database from the first server and 1 database from the second. I don't
know how to specify the location of the second database. If I put
'Server2.MISProd.dbo.work_orders', I get an error that "this object
contains more than the maximum number of prefixes. The maximum number
is 2"
I do have remote servers set up so that from each server, I am able to
see the other. Is there any way to work through this?
I would appreciate any thoughts. Thanks!
Toni
I have two machines running SQL server. I need to create a view that contains data from both servers on Server 2.
ex:
Server 1 contains:
database (Employees)
Table (EmpMaster)
Column (EmpNumber)
column (EmpName)
Server 2 contains:
database (Training)
Table (TrainingEntry)
Column (EmpNumber)
Column (TrainingDate)
Column (TrainingDescription)
If the two tables were on the same server and in the same database, I'd just write the following:
select
TrainingEntry.EmpNumber
,Employee.EmployeeName
,TrainingEntry.TrainingDate
,TrainingEntry.TrainingDescription
from TrainingEntry
Left Outer Join Employee on TrainingEntry.EmpNumber =
Employee.EmpNumber
The question is how do I handle going between two databases, and more importantly, how do I handle linking between two servers?
All examples are appreciated.
Thanks
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
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 RelatedI was wondering if someone could help explaining how the SQL Server handles all incoming connections, I and a friend started a discussion about using the connection pool in ADO.NET or not in a specific case.
Usually when creating like an ASP.NET Page it's recommended to use the connection pool, because it optimizes the performance, by using already created connection, so we don't have to recreate the connection all the time. I have always thought that the connection in the connection pool had an open connection on the server. So 5 connections in the connection pool, would be 5 open connections at the server. But after having the discussion with my friend I am not so sure anymore.
Say that I would create a client application (.NET using ADO.NET) that connects to the database and work with that data. If I then have 1.000 clients and each client have a connection pool with 5 connections in it (I think that the default numbers for the connection pool), then there would be like 5.000 open connections on the SQL Server, where most of the connections actually never do anything more than hanging around and waiting... And then the connectionpool is not 1 per client but 1 per connectionstring.
So if my client scenario would access data from 2 different databases, there would actually be 10.000 open connections at the SQL Server. So now I think that there must be a server connection pool or something to handle the connection from the clients. So that there would only be like 10-50 open connections at the server for the 1.000 Clients that was connected.
How is it? Is there 1 open connection in the server for every connection in the connection pool? If that's the case, it would be better for the SQL Server if I don't not use the connection pool in the client, but instead open the connection when we need it, and then close it, and taking that little performance hit every time, to help the performance on the SQL Server.
Best regards
- Magnus
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 RelatedI 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!
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
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
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
View 1 Replies View RelatedHi,
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
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 RelatedHi,
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
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
(Spam Removed)
View 3 Replies View RelatedI have SQL Server 2005 running on my local computer (OS Windows 2000). I also have SLQ Server 2005 running on a server (64 bit OS Windows 2003)
I need to copy a table from a DB on my local server to a DB on the server.
On my local computer I can see and connect to the DB on the server. No problem.
But when I remote desktop to the server and try to connect to the server on my local computer I get the error:
Error has occurred trying to establish connection to the server… under default settings server does not allow remote connections…
Both servers are set to the default settings. What settings do I need to change so that I can connect to my local server from the other server?
I've just downloaded and installed SQL Server 2005 Trial Edition in an HP 2660rx Itanium server. I can see from the Configuration Manager that there is a service running a database instance which I created during the installation but when I try to connect to the new database thru the SQL Server Management Studio it doesn't find any (when Database Engine is the selected server type) server names. In SSMS console only when I select Integration Services as the server type it detects the server name.
What could be wrong? In my server I just made a clean installation of Windows 2003 Server and SQL Server is the only software I have installed since that.
Any help availabe?
Einomies
Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?
View 1 Replies View Relatedhello all
I have a web application(asp.net) and Database(sql server 2005) .
we have installed them on several servers.
now we want to have a connection between apllication on one server to a database on another server .
for example we have Server(A) and Server(B) the DataBases and Web apllications on two server are the same.
sometimes the Apllication on server(A) must connect to database on server(B).
whats the solution plz?
note that the number of servers can be inceased in the future this mean the number of servers are not fixed.
thanks
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 Relatedcan anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005.
any help on this would be highly appreciated.
I have two sql servers, A and B. each one has one database, A and B. I am trying to write a stored porcedure in database A to retrieve the data in database B. how do I do it? can you give me a simple example. (like "select")
View 6 Replies View RelatedHello All,
I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?
Thanks
I have two db instances on different SQL Servers. One is my test db and the other the prod. db. Each has two tables that hold lookUp values for the entire application. I would like to keep these tables in sync. in both dbs. The prod. db would be the master. I was trying to design a trigger to insert, update, or delete the appropriate records in the test db as they were added or deleted from the prod db.
I'm getting too many prefixes errors on the script when trying to reference the table on the test db using four part naming convention.
Any ideas on how to resolve this issue? Do I need to set up a linked server to accomplish this functionality? If so, how do I setup security for a linked server?
Thanks in advance.
Oscar
need some help!
Im doing an asp.net project on my laptop. im using the web matrix and sql server. my laptop is on its last legs so i need to start using my PC in college.
Now here is where it gets complicated. Last year we had accces to a server in the college to run .net pages across the network. But there has always been some problem with that server so there was no way of connecting to sql server. At the moment i have heard that some students use an orion web server to connect their pages to an access database,which wont do me.
Here is my plan, im going to install sql server on my computer(could get in trouble though!). i just need a web server on my machine to test my pages out without having to go accross the network of the college.
Any ideas?
i have cassini but have never used it.
There is no IIS on the computer so that is out the window
I currently have a main SQL Server which had a column on the majority of the tables. This column also had a check contraint on it. I dropped the column and the constraint and I now get the following message when trying to query the tables through a linked server 'OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.'.
Can anyone help