View Spanning 2 SQL 6.5 Servers
May 20, 1999
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
View 1 Replies
ADVERTISEMENT
Apr 27, 1999
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
View 2 Replies
View Related
May 20, 2008
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
View 6 Replies
View Related
Apr 6, 2006
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
View 3 Replies
View Related
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
Nov 2, 2007
I 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
View 1 Replies
View Related
Dec 4, 1998
Hello Everyone,
I have a SQL 6.5 database that is about to grow beyond the size of its current volume. I have 3 volumes of 20GB each, 2 of which aren't being used. What do I need to do to ensure that I can expand the device across multiple volumes?
Thanks in advance for your help,
Terry
View 1 Replies
View Related
Apr 23, 2008
Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example
empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008
How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?
Thanks in advance for any advice?
View 11 Replies
View Related
Oct 3, 2007
How do you add a table to a report that will span over two columns before going to the next page?
View 3 Replies
View Related
Oct 22, 2007
I am creating a report that contains a table. The table only has one column containing 10 characters so there is a lot of wasted space on the page. I would like the columns to wrap to at least two columns on each page. I have tried to set the number of columns on the report properties to two, however it seems as if the second column is disabled and the data does not wrap into the second column when the report is run.
Any suggestions would be GREATFULLY appreciated!
Thank You
Kaysie
View 2 Replies
View Related
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
May 15, 2008
I have a report that returns multiple tables in a group. I'm grouping by project manager which has multiple projects, each project contains data in a table. My problem is that when I print, my tables are being cut off so that the headers and first few rows are on one page and the rest of the table and totals are on the next page. There was a command in access that would allow me to keep my tables together so that if a table wouldn't fit on a page it would move the whole table to the next page. No table is larger that a page, but I'll get 2 and 1/2 tables on page 1 and 3 and 1/3 on the second page, I'd like to have 2 on the first page, move table 3 to page 2 and move on from there. Is this possible?? Thanks for any help you can give me.
View 3 Replies
View Related
Oct 5, 2006
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and
'17:00:00' order by [DateTime]
However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and
'09:00:00' order by [DateTime]
I need a way to indicate that the start and end times span two days. Can anybody help with this?
View 5 Replies
View Related
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
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
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
Jul 23, 2005
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 Related
Jul 24, 2012
Write 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]...
View 2 Replies
View Related
Aug 24, 2007
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!
View 3 Replies
View Related
Mar 9, 2006
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 Related
Aug 14, 2000
I 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.
View 1 Replies
View Related
Feb 17, 2006
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
View 4 Replies
View Related
Oct 27, 2006
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 Related
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
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
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
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
Feb 11, 2007
(Spam Removed)
View 3 Replies
View Related
Jan 29, 2008
I 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?
View 3 Replies
View Related
Aug 8, 2007
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
View 4 Replies
View Related
Aug 17, 2006
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 Related
Sep 5, 2007
hello 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
View 3 Replies
View Related
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
