I have two production servers with two different databases and I was thinking about using Linked Servers, but never did this before.Found this stored procedure
sp_addlinkedserver('servername').Would you just execute this and then run your query after the SP?
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 need to have database access via an
application to both sql server 6.5 & 7
the 6.5 uses mixed mode security,
the 7.0 uses trusted. Using
sp_addlinkedsrvlogin I can get from a client to
the 7.0 server, but not to the 6.5.
Is this even possible?
Two SQL Servers1) "Test"Payroll DBTransaction Tablepr_SubmitTransaction in Payroll DB********************************************2) "Production"Payroll DBTransaction TablePr_SubmitTransaction in Payroll DBAccounts DBPr_VerifyAccounts (@AcctNumber)********************************************I want to use same pr_VeriftAccounts in both test and productionPr_SubmitTransaction stored procedure.The pr_SubmitTransaction in production works fine when I sayExec Payroll.dbo.pr_VerifyAccounts as it is on same server. (WORKSFINE)Exec [PRODUCTION].Payroll.dbo.pr_VerifyAccounts (DOES NOT WORK)Should I use sp_addlinkedServer ?? to do this ?. Please provide me somefeedback.I know I can acheive this by front end, but I was do it in one storedprocedure.
View 7 Replies View Related
We have reporting services on 1 server, while the data comes off of another which does not have rs and on sql server 2000. I was able to build the reports and deploy it. But the report wont render. The error message is shown below.
My question is will linking servers work and if anyone has done this could you pl provide an example?
Thanks in advance
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'sds1'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'm using SQl 7.0. After setting up a linked server. I recieve this
error when attempting to connect to the linked server: 'Error 7302 -
could not create an instance of OLEDB provider 'SQLOLEDB'. Any ideas??
I do not recieve this error when linking from another server.
Thanks,
Steve Bajada
Hi.I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQLServer' to link one to the other and also vise versa.Each server has two users permissioned.My problem is when ever I try to do something that does a remote write I getthe follow error messageMicrosoft OLE DB Provider for SQL Server error '80040e14'[OLE/DB provider returned message: Cannot start more transactions on thissession.]Alsowhen I try and manually run a stored procedure, I get:Remote tables are not updatable. Updatable keyset-driven cursors on remotetables require a transaction with the REPEATABLE_READ or SERIALIZABLEisolation level spanning the cursor.(1 row(s) affected)(1 row(s) affected)(50 row(s) affected)Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line65Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. Anested transaction was required because the XACT_ABORT option was set toOFF.[OLE/DB provider returned message: Cannot start more transactions on thissession.]Can anyone suggest how I resolve this.FYIDatabase Creation Script: (both database are the same scripts but db namesare changed)CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =N'D:ACS_DBdatamsmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON(NAME = N'msmprim_Log', FILENAME = N'D:ACS_DBlogsmsmprim_Log.LDF' , SIZE= 2000, FILEGROWTH = 10%)COLLATE Latin1_General_CI_ASGOexec sp_dboption N'msmprim', N'autoclose', N'false'GOexec sp_dboption N'msmprim', N'bulkcopy', N'false'GOexec sp_dboption N'msmprim', N'trunc. log', N'false'GOexec sp_dboption N'msmprim', N'torn page detection', N'true'GOexec sp_dboption N'msmprim', N'read only', N'false'GOexec sp_dboption N'msmprim', N'dbo use', N'false'GOexec sp_dboption N'msmprim', N'single', N'false'GOexec sp_dboption N'msmprim', N'autoshrink', N'false'GOexec sp_dboption N'msmprim', N'ANSI null default', N'false'GOexec sp_dboption N'msmprim', N'recursive triggers', N'false'GOexec sp_dboption N'msmprim', N'ANSI nulls', N'false'GOexec sp_dboption N'msmprim', N'concat null yields null', N'false'GOexec sp_dboption N'msmprim', N'cursor close on commit', N'false'GOexec sp_dboption N'msmprim', N'default to local cursor', N'false'GOexec sp_dboption N'msmprim', N'quoted identifier', N'false'GOexec sp_dboption N'msmprim', N'ANSI warnings', N'false'GOexec sp_dboption N'msmprim', N'auto create statistics', N'true'GOexec sp_dboption N'msmprim', N'auto update statistics', N'true'GOUser Creation Script:/****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/use [msmprim]GOdeclare @sqlLoginName nvarchar(32) select @sqlLoginName = N'msm'declare @UserPassword nvarchar(32) select @UserPassword = N'wibble'declare @logindb nvarchar(132) select @logindb = N'msmprim'declare @loginlang nvarchar(132) select @loginlang = N'British English'if not exists (select * from master.dbo.syslogins where loginname =@sqlLoginName)BEGINif @logindb is null or not exists (select * from master.dbo.sysdatabaseswhere name = @logindb)select @logindb = N'master'if @loginlang is null or (not exists (select * from master.dbo.syslanguageswhere name = @loginlang) and @loginlang <> N'British English')select @loginlang = @@languageexec sp_addlogin @sqlLoginName, @UserPassword, @logindb, @loginlangEND/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/if not exists (select * from dbo.sysusers where name = @sqlLoginName and uid< 16382)EXEC sp_grantdbaccess @sqlLoginName, @sqlLoginName/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/exec sp_addrolemember N'db_datareader', @sqlLoginName/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/exec sp_addrolemember N'db_datawriter', @sqlLoginName/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/exec sp_addrolemember N'db_owner', @sqlLoginNameGOuse [msmprim]GOdeclare @sqlLoginName nvarchar(32) select @sqlLoginName = N'psm'declare @UserPassword nvarchar(32) select @UserPassword = N'wibble'declare @logindb nvarchar(132) select @logindb = N'msmprim'declare @loginlang nvarchar(132) select @loginlang = N'British English'if not exists (select * from master.dbo.syslogins where loginname =@sqlLoginName)BEGINif @logindb is null or not exists (select * from master.dbo.sysdatabaseswhere name = @logindb)select @logindb = N'master'if @loginlang is null or (not exists (select * from master.dbo.syslanguageswhere name = @loginlang) and @loginlang <> N'British English')select @loginlang = @@languageexec sp_addlogin @sqlLoginName, @UserPassword, @logindb, @loginlangEND/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/if not exists (select * from dbo.sysusers where name = @sqlLoginName and uid< 16382)EXEC sp_grantdbaccess @sqlLoginName, @sqlLoginName/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/exec sp_addrolemember N'db_datareader', @sqlLoginName/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/exec sp_addrolemember N'db_datawriter', @sqlLoginName/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/exec sp_addrolemember N'db_owner', @sqlLoginNameGO
View 1 Replies View RelatedHello all!
How do I go about linking two SQL Server Express servers? I can't seem to find information on this.
Also, I need to import data from MSDE. Is there anyway to do this?
Regards,
Andrew Cappella-Pakos
Hello,
I was wondering whether it is possible to query linked servers in SQL Server Management Studio Express 2005 if those servers linked to are SQL Server 2000 machines?
I have added some linked servers to express and queried them, however the login timeout is expiring. I thought it might be because of an incompatability.
Cheers,
Damien.
Hello,I have a problem. I've linked MySql server to MsSql, in MySql I have atable with Latvian data(character set is ucs2, ucs2_general_ci) andthe problem is that when I use openquery to read data from MySQLserver, some characters are not translated correctly! I receivequestion symbols instead of Latvian special characters.Maybe someone had this kind of problem with collation?
View 2 Replies View RelatedI know this is an easy one for most of you but this is new territory for me.
I am trying to link a production application database to an existing SMS database.
Both of these databases are SQL 2000. I have sa access to both of them.
I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.
For simplicity I will define them both.
Application Database we will call €œA-Database€?
SMS Database we will call €œB-Database€?
I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab.
In the Security Tab I selected the €œBe made using the security context€? and used €œsa€? for the Remote login and the CORRECT sa password for the With password option.
The server options tab remain the same with €?RPC, RPC Out, & Use Remote Collation€? selected.
It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?
Thanks,
Gene Godsey
coycoy wrote:you wanted to join some columns coming from two different tables...right? if that so, use an SQL query. Try using the "inner join" statement.
i wanted to combine the data from two tables. these two tables belong to different servers: table1 is from sql server found in cerebrum station and table2 is from mysql server found in copernicus station. i have this code but the problem is i can only use this code if the two tables belong in the same database server.
</P>
<P>string limitReghrsvalue = "select statuslog.ActId as STATUS_ID,DalsDataNew.ID as MANHOUR_ID,statuslog.ActDate as DATE,statuslog.PrjCode as PROJECT_CODE,statuslog.MapNumber as MAP_NUMBER,statuslog.Activity_Code as ACTIVITY_CODE,DalsDataNew.ActivityMedium as MEDIUM_CODE,statuslog.RegHrs AS REGHOURS,statuslog.OTHrs AS OTHOURS,statuslog.Status AS STATUS,DalsDataNew.Flag,DalsDataNew.Approvedby from statuslog,DalsDataNew where statuslog.PrjCode = DalsDataNew.ProjectCode and statuslog.PIN = DalsDataNew.PIN and statuslog.ActDate = DalsDataNew.Date and statuslog.Activity_Code = DalsDataNew.ActivityCode and statuslog.RegHrs = DalsDataNew.RegHours and statuslog.OTHrs = DalsDataNew.OTHours and statuslog.PIN = 'P120' and statuslog.ActDate >= '"+this.firstdate.Text+"' and statuslog.ActDate <= '"+this.lastdate.Text+"'";</P>
<P>
Sql Server in Cerebrum: database dals
DalsData
ID | Date | PIN | ProjectCode | ActivityCode | ActivityMedium | RegHrs | OTHrs | Approvedby | Flag
123 |9/17/2005| P120| 1234 | B | W(P) | 5.50 | 0.00 | P083 | 1
124 |9/17/2005| P120| 1234 | I | W(PC) | 1.50 | 2.25 | |
MySqlServer in Copernicus: database stat
Statuslog
ActID | ActDate | PIN | ProjectCode | MapNumber | ActivityCode | RegHrs | OTHrs | Status(%)
1 | 2005-9-17 | P120 | 1234 | map01 | B | 5.50 | 0.00 | 100
2 | 2005-9-17 | P120 | 1234 | map01 | I | 1.50 | 2.25 | 75
the output in datagrid should be:
ID | ActID | Date | ProjectCode | ActivityCode | MediumCode | MapNumber | RegHrs | OTHrs | Status | Approvedby | Flag
123| 1 |9/17/2005| 1234 | B | W(P) | map01 | 5.50 | 0.00 | 100 | P083 | 1
124| 2 |9/17/2005| 1234 | I | W(PC) | map01 | 1.50 | 2.25 | 75 | | could someone help me how would i do this?
how best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
this is the query I need help with
select a.RecordID,a.RelocateID,a.WhenConfirmed,b.RequestI D,b.DocumentID,b.RequestWhen from RelocateeDTA a inner join
docRequests b on ({a.RecordID}=' + CONVERT(VARCHAR, a.RecordID)'= b.WhereClause)
because I am trying to link RelocateeDTA that has RecordID to docRequests that has the same piece of data in field WhereClause but is a varchar, and I am getting an error message (syntax error or access violation)
Could somebody help me please
dear experts,
i have two tables, tblcustomer and tblform. in tblcustomer i have cutomerid PK and i have also a field that link these two tables customername. in tblforms i have formname. customername and formname are not PK and customername has a lot of null values.
my question is that i want to link these two tables in customerid not the name and also to get the forms used by this customer. the thing is that i have to write a join query.
any suggestion to do that?
thanks my friends
I know it's possible to link tables between Access and SQL Server using ODBC....however can u use DAO to do this? If there's a way i would appreciate some help. Thanks in advance.
View 2 Replies View RelatedHi I am a complete sql newbie :)
With that being said I am trying for the life of me to link to a table in another sql database.
For some reason its not letting me link the table instead it is creating a query of the table.
I used the Access upsizing wizard and the access front end.
*grumbles - just wish I could find the upgrading handbook that tells you if you used to do it this way now do it this way*
Thanks
Sara
I want to link in a table from another sql server in a view, how is this done?
View 1 Replies View RelatedURL...let's say every student in the left table, has a corresponding table to the right. If we were to take the average of all the classes in the corresponding table, how would you be able to transfer that in the table to the left, assuming there could be thousands of these individual tables.
View 4 Replies View RelatedI am no stranger to Databases, I worked a lot with MySQL but never really cared about proper DB design as long as it worked. Now I am playing with SQL in a ASP.NET project and want to get things done the right way.Let's say I have a Movies database. My movies can have multiple genres so I set my tables up like this:
[Movies]
MovieID
MovieName
MovieRelease
[code]....
Is this the proper way of doing things? The problem with this is when I want to enter a record manually I have to know the ID of the movie and the ID of the Genres of the movie. And what about naming conventions? By default the identifier is always Id, from my MySQL experience I liked naming it like the table, same goes with other columns. This is my T-SQL code for above tables in VS-2013.
CREATE TABLE [dbo].[Movies] (
[MovieID] INT IDENTITY (1, 1) NOT NULL,
[MovieName] VARCHAR (50) NOT NULL,
[MovieRelease] NUMERIC (18) NOT NULL,
CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED ([MovieID] ASC)
[code]....
Hi and thanks for taking the time to read this. I get really confused really quickly when it comes to table linking, and I think I am doing it the hard way. I am going to show my stupidity and ask if there is a better way than what I do, because it takes a LOT of querying and seems like a huge waste.
In reality, I have about 20 tables to join like this, but I'm going to use three for the example:
"Main" Table
AcctNoNameHeightWeight
1Bob13
2Phil21
3Jim15
4Lisa22
"Height" Table
HeightIDHeightName
1Giant
2Tall
3Medium
4Short
"Weight" Table
WeightIDW_Name
1Skinny
2Normal
3Overweight
4Obese
What I have been doing is:
SELECT
Main.AcctNo,
Main.Name,
Height.HeightName,
Weight.WeightName
FROM
Main
INNER JOIN Height ON
Height.HeightID = Main.Height
INNER JOIN Weight ON
Weight.WeightID = Main.Weight
WHERE Main.AcctNo < 3
My question: is doing 20 INNER JOINS really the best way to do this with 20 tables, or is there some other way to link Main.Height to Height.HeightID that is less intensive?
Thanks again for reading, and in advance for any information or insight you can provide.
I have two tables, one for cases and one for inventors. When I query a case linked to the inventor table I get as many rows as there are inventors. Is there a way that I can get just one row for the case and have the inventors lists Doe, John; Doe, Jane; Buck, Jack? I usually export results to Excel and my people want the inventors in one column for certain reports.
I would search for it but I don't know what it's called.
Thanks,
Don
I have two tables, TableA and TableB.Each has a date in smalldatetime format.TableA has a qty field in int format.TableB also has a nvarchar field in which the date is in the formatYYYYMMDDA simple Query on TableA sum Qty returns a result of 100.However, When I do a left outer join from TableA to TableB on Date, thesum of Qty on TableA becomes massivley larger.What could be causing this?Regards,Ciarán
View 2 Replies View RelatedIn a database, I am creating a new db. From there, I am setting up thetables, so that I can eventually create a front end (usually access,but I may attempt to be brave and lose the shell.) Anyway, I want touse a table, read-only for a lookup. It exists in another database onour system. Is there a way for me to link it into this database that Iam working on? I would think of it like in access when you go to do anew table and you choose to link the table from somewhere else. Thatis what I want to do. Can anyone possibly step me through this?I know basics of sql server and like to learn.Thanks!
View 3 Replies View RelatedHi,If you have lookup tables which are used by multiple tables (e.g.'City' lookup table might be used by the 'Employee' and the 'Company'tables) do we need to link it to both tables? Or can it sit by itselfand just referenced?Cheers,Jack
View 2 Replies View RelatedFirst off, I am a novice in regards to SQL server and have the rudimentary skills to navigate, execute, query under SQL Server Manager. So, please bear with me.
I am working on a program that was not developed by myself, it is for my business and the company that developed it has gone under, so I have no technical support. The program uses SQL Server and ASP.net. We had a recent hard drive crash and I am in the predicament of having to re-install a new instance of the server onto a new computer. I have the most recent saved "data" files within "Microsoft SQL server/MSSQL/Data" directory. It includes the master, model, MSDB, etc. files.
Long story short, I have been successful in making the program operational again and I can connect to the server properly. It even works when web connecting with internet explorer. The problem I am facing is that all of the data is missing. When I connect through SQL Server Manager, I can see that there are two databases, I will name them "data1" and "data2". When I run a query of the tables, all the data is in "data2". All the stored procedures and tables are identical in both "data1" and "data2".
I am assuming (hopefully I'm right) that "data1" and "data2" are somehow linked or joined in some way. Any insight in how to get these two tables to link would be very much appreciated. Again, I am a novice, and will need help with specific coding that I can put into a query window and execute.
Thanks in advanced.
I'm new to SQL Server 2000. I decided to purchase this server because my old company (UPS) used the server to help expedite queries and manage on-line reporting. So, now it's up to me to utilize and understand the new server. I have been using MS Access as my front-end for several ad-hoc databases, so I'd like to keep utilizing this method. I want to test my first database using the SQL Server.
I've got everything set up. The IT department has installed the server software on my computer. Within my Access database, I link in tables from a database called "Progress" using a Merant driver. Queries, forms, macros and modules are all pulling from these "linked" tables. Do I need to link these tables into the SQL Server? How do I transistion this database over to SQL? I guess I'm confused about the relationship of how all of this should work.
Right now it looks like this:
PROGRESS ..... linked....... MS Access ....... reporting/results
Where does SQL fit in?
I have an Access database that contains local files and files linked on a SQL server through a DNS connection. I have been using this for years and during that time I have dropped links, added links, changed the DNS server information, etc.
I changed the DNS server information this weekend, deleted all the links, and was ready to start linking the tables with the new DNS information. I click on File >> Get External Data >> Link Tables and am given the traditional file selection dialogue. I go to the bottom (Files of Type) and scroll to the last selection... ODBC Databases().
In the past, after I made that selection, I would then be taken to the ODBC dialogue from my computer to select the connection type and then the actual connection.
Now however, the dialogue immediately stops. It doesn't lock, it just stops and returns me to the Database window from my database as though I wasn't doing anything.
I have reinstalled Office and redone all the updates. Does anyone have any suggestions?
Thanks,
Gary
Can Anyone Help? I have 2 Databases, Database1 and Database2.
Database1 is a content management backend database including a helpdesk and support ticket tracking table.
Database2 is a clients complete website
How do i link the tables from Database1 into Database2 so that the client can view the helpdesk thru their content management system.
(Client1, Client2, Client3 etc... will all have access to the same Database1)
Any help someone could give me would be a big help
I am trying to create a single query from 2 tables on different SQL servers. In Access 2003 I have been able to accomplish this by created a Linked Table via ODBC connection to a table then create my query reference the linked table.
Is something link this possible with SQL? Any direction would be greatly appriciated!
Scenerio:
Server1
Books
Book_ID
Book_Author
Book_Title
Server2
PurchasedBooks
Customer_ID
Book_ID
vPurchasedBooks (View on Server 2)
Customer_ID
Book_Title (Joined on Book_ID on Server1Books)
Is there a way to link MS Access tables with SQL Server tables so the client app sees them as tables from one database?
I have tried linked server but it appears that it gets stale after a while and needs to be re linked from time to time which is not very practical.
Hello,
I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL.
So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using....
SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @CartID ORDER BY ProductID
I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this?
I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing!
I'd really appreciate any help you can offer me.
Many thanks,
Luke
I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int.
Vic Valentic
CEO/President
Open Door
2 Elite Dr. #33
Hamilton, Ontario
L8W 2N3
905-389-7492
http://www.wlu.ca/next/opendoor