How To Create Linked Servers For Multi Data Source
May 6, 2008
I have a report my business users need from from two data sources, which are on two different servers (both are oracle DBs). I have a common Primary key between both. I've read through a ton of posts and it looks like there is no way to join the data using Datasets, that you need to do something called Linked Servers.
but what I couldn't find is where do I go to create a Linked Server and how. I'm new to SS, so I need help and details.
There are 2 sql servers : A and C. I have to grab data on server A and update records with this data on server C. It has to run every night, so it has to be scheduled as a job. I created linked server on server A and ran query select... It works. I can create Update query. What are my steps next? I read how to create a job, but not understand how to incorporate the query into it. I am totaly unexpirenced user, please explain step by step or give me a link to a good explanation. I also do not understand how and whether DTS should be used in all this. Appreciate any help.
my problem is about the slow execution of a query.
I have two database located in two different servers. The servers are linked with the linked server connection made with the enterprice manager.
The databases structure is the same and I copy some data from a database to the other with the following query:
INSERT INTO DATASERVER.RESULTS.dbo.Tab_Tests SELECT * FROM LINESERVER.WIP.dbo.Tab_Tests WHERE ID_Result = '{76271FC1-9470-4EF7-A403-000CF75C2215}' OR ID_Result = '{08C8EEB9-CD22-4CF9-8269-8C1CC58C752B}' OR ID_Result = '{B7B7EED1-36E4-48FA-9636-ABBE62AAE04D}' OR ID_Result = '{8F7BA69E-D2CE-4EB2-BBC4-AFFA165CE0CE}' OR ID_Result = '{273CB805-945F-4EFA-AE20-BC4E0898A19F}'
Everything works till the database grows up more than 400 MB.
Also if I select one result, that consist of some records, the query is very slow and the CPU is working over the 75 %.
I ask you if my working way is corret or if you have some solution or some explanation for me.
My customer has a .NET application that reads meta data from SQL Server, Oracle, DB2, and several propritary databases. Because each DBMS stores the meta data using various techniques, they have written custom code for each DBMS. They are working on a generic ODBC/OLEDB suppport, but in the interim I was trying to use SQL Server to link to an Access database. The Access linked server works fine for queries in Query Analyzer, but I would like to be able to programatically read the metadata for an Access DB (tables, columns, types, etc) via the linked server. SQL Server's usual mechanism for storing meta-data in the Master database aparently is not used for Linked Servers.
Does SQL Server expose Linked Server meta data? How would one retrieve this meta data if it is exposed?
When i run above script, it prompt me an error message; "Microsoft OLE DB Provider for SQL Server The requested operation could not be performed because OLE DB provider "IBMDA400" for linked server "TESTDB2" does not support the required transaction interface."
If i run it without "begintrans" and "committrans", it update the data successfully.
hi everybody, i want to create data source and data source view for data mining, with using C Sharp. i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully, and have i build data source and data source view, how to do it.?
Today I was making a few reports. When I tested the reports in Visual Studio, they worked great: I got the expected result. But when I deployed the reports to our reportserver the problem started. When I click on the directory in which my reports are deployed, I got my 4 reports. Till now everything worked correct. But when I click on a report to view the results it went wrong. I got an error: "Cannot create a connection to data source 'Live'" (Live is the name of our data source).
We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work. I also have tried it with all the roles assigned on my account, but then it still won't work.
When I modify the data source, and set it to another server en database it works. The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server. Maybe that is the problem?
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
I need help in creating a Data Source within SQL Server Reporting Services 2005 to connect to a Microsoft Access database. I'm sure the problem is an authentication issue, because the report works fine when previewed through Visual Studio 2005. But, through the report server browser front end I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'AssignedEmployees'. (rsErrorExecutingCommand) The Microsoft Jet database engine cannot open the file '...'. It is already opened exclusively by another user, or you need permission to view its data.
The Access database happens to reside on the same server as the SQL Server instance. The query that it is invoking uses tables linked to other Access databases including one that is on the same server and another that is on a completely different server. The error listed above references the database that is linked from the main Access database and that is on the same server.
I tried various things to get it to work, but what it comes down to is that I do not know the proper way to create the Data Source to an Access database from SRS (especially where the Access database links to other Access databases). Can anyone provide the steps to do this properly?
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
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :
table MOTHER (MOTHER_ID, MOTHER_NAME)
table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)
After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.
My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".
The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...
Is there a solution to chain two insertions with a foreign key constraint in a data flow?
I have this very big text file (2.5 GB - which of course I am not able to open). This resides on a server which has SQL 2005.
I have created a linked server for this text file on my SQL database, and when I try to query the table, I get an error:
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "txtsrv".
Why am I getting this error? When I make a dummy linked server on my local machine and test the steps it all works OK. but when I go to this server where the file and it's SQL instance is lying, I get this error. Please can someone provide me with some help on this?
Many thanks, ~S
P.S. Here is how I created the linked server (I also have the Schema.ini lying under the same folder):
As I try to create a Data Source for SQL-server I get an error message saying that either the Database does´nt exist or that access is denied. The obviously existst, so I guess there has to be a problem with the access? Does anyone know what is wrong? Thanks in advance!
I need to connect to a sap bw cube and when i try to create Data Source everything is going well but i can't see my cubes in the catalog field. any one have experience with it??
I have two servers, DEV and PROD. Now my DEV server works just great, I can connect to the linked server, query, etc... all is well.
So I'm setting up my PROD server and when I go to add the linked server I get:
Cannot initialize the data source object of OLE DB provider "SQLNCLI".... and Unable to complete login process due to delay in opening server connection.
Now I am running SQL Server 2005 and connecting to an SQL 2000 server.
The odd part is that this works just fine on DEV.
When I go to create the linked server I set:
Linked Server: "LinkedServerName"
Server Type: "SQL Server"
and that's it.
I go to Security and enter my DOMAINUSER.ACCOUNT and then enter the login creds for the linked server.
When I click "OK" I get the above mentioned error code.
3.Create linked server with scripts: exec sp_addlinkedserver N'MyOracle1', 'Oracle', 'ORAOLEDB.Oracle', N'//10.154.14.235', N'FetchSize=2000', '' exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'root', @rmtpassword='Sqlexp!23' 4. After create successful and testing the connection ,I got the error below.(My windows firewall is turn off)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle".OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA". (Microsoft SQL Server, Error: 7303)
in the following i'm trying to create a sqldatasource and link it to a drop down list, is it possible? Protected Sub DDL_Selections_SelectedIndexChanged1(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDL_Selections.SelectedIndexChanged If Me.DDL_Selections.SelectedItem.Text = "All" ThenDBconn = New System.Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") DBcmd = New System.Data.SqlClient.SqlCommand("SELECT [DateEntered], [From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs] ORDER BY [From]", DBconn)DBSDA = New System.Data.SqlClient.SqlDataAdapter("SELECT [DateEntered], [From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs] ORDER BY [From]", DBconn) Me.DDL_Selections.DataSourceID = "DBSDA" End If End Sub
I am trying to view report that i deployed to reporting service but when i try to view it, it gives error:
An error has occurred during report processing. Cannot create a connection to data source 'INVC'. For more information about this error navigate to the report server on the local server machine, or enable remote errors
Why is it that sometimes I get this error and sometimes not after I deploy the same shared data souce to my Report Server?
Even if I try deleting the datasouce through Report Manager the redeploy it from VS 2005, it still errors out when I run my report. I am able to run my report fine in VS.
An error has occurred during report processing.
Cannot create a connection to data source 'mydatasourcenamehere'.
For more information about this error navigate to the report server on the local server machine, or enable remote error
I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?
Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run
An error has occurred during report processing.
Cannot create a connection to data source 'datasourcename'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I posted the same issue before, but I couldn't find the solution yet. I am posting this agian cos I tried almost everything and I am not able to solve the issue.
Let me explain what is happening here:
Server A - is my development local machine
Server B - is the remote reporting server, where all reports are deployed
Server C - is where the databases are located
- After successfully deploying the project (the reports and datasources) on the remote server and try to run the report , I am getting the following error message
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
- I tried to deploy and run the same report on my local machine report server(Server A), which is still accessing the same database from Server C. This works fine.
So from this we can understand that A ==> B ==> C is not working, however A ==>C works fine(using Server A as a report server).
I almost tried all the possible solutions that are posted on the forum, such as storing the credential information on the report server (server B), created a data source, which is not a shared data source, on the report server level e.t.c. and I ended up with nuthin.
The weired thing is that there are existing reports that are already created and deployed(by somebody else) in the report server (server B) , which access the database on Server C. These existing reports run with no problem, even I can create a report which uses the existing data source and runs perfectly. However, when I create a new report which uses a different data source (from Server C), and deploy it to Server B and tried to run it, the above error message comes.
Hi! I've created a project includen 3 reports based on following code on 3 server. The report includes 3 different datasources an 3 different report. All 3 reports are grouped on Database and summerizad zise.
SELECT
SD.name [Database],
SMF.name [File],
SMF.size [Size]
FROM
sys.master_files SMF INNER JOIN sys.databases SD
ON SD.database_id = SMF.database_id
I can run all reports in Visual Studio (preview). But I can only run one the reports after depolying the project! I get this message when I raun the other 2 reports:
An error has occurred during report processing.
Cannot create a connection to data source 'uasql02_datasource'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I tried running the report from an server (where reportingservices is installed) finding that all the reports works as they should. I logged in another server with the same credentials. I got same message on the server as I get on my own camputer, "Cannot create a connection to data source ..."
Is there anybody out there having same problem as I, or someone who has any suggestion?
I have a number of reports deployed on the report server. I want to point all these reports to another data source. Currently, what I am doing is go to the individual report on the report server and change the data source property to point to the new data source. It is very tedious to do the same thing for all of the reports.
So, Is there any way to point all the reports under a project at one time.
We have more than 10 databases on the same server. When I try to set up ODBC connection through my desktop to a specific database (DB1) using SQL authentication, it acts weird:
-- I give the login (testkz) access to DB1, DB1 does not show up in the default database list; I tried to type in the database name, it is not taken
-- If I give 'testkz' access to two other databases (and only these two), even though I did not give access to DB1, DB1 together with some other databases show up on the default database list. Seems to me that there are some dependency among the dbs.
I successfully created the a Report model and deployed it to the server. When i run the a simple report on the SQL Server where reporting Services is installed it runs fine. When I run the report from anywhere else, I get the error below. I don't have a datasource called 'dataSource1'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors ---------------------------- Cannot create a connection to data source 'dataSource1'. ---------------------------- An error has occurred during report processing.
I have installed Operations Manager on one server and Reporting Services for Operations Manager on another. When I attempt to run reports from Operations Manager, I receive the following message: Cannot Create a Connection to Data Source "DataSource1" Any suggestions?
I have setup SSRS with 3 data models using 3 different datasources on one server. I can create reports ok, but if any of the other users I want to access these reports tries to open the report I created or run there own reports (they can create a report), it gives them the error:
Report Execution Error
Cannot create a connection to the data source 'datasource1'
I do not recieve this error, but the other users do. I have set the other users as System administrator in the site-wide permissions, so they have access to everything.
Everything is set to integrated windows authentication.
On the Reporting Server Configuration Manager, the Unattended Execution Account is not selected and is blank.
What could cause these users not to be able to run existing or new reports they create?
I have recently installed Microsoft SQL Server 2005 Express. The installation seemed to go OK (Dell Optiplex I86, Win2K).
The SQL Server Management Studio Express (also installed OK) appears to connect to the database server and shows the database tree as I would expect, consisting of Databases, Security, Server Objects, Replication, and Management.
However, when I attempt to create a new data source (Control Panel->Administrative Tools->Data Sources(ODBC ->ODBC Data Source Administrator -> Add->SQL Server)
Name: MYSQLS1
Descr: Test SQL Server
Server: (local)
(Next->)
x With Windows NT Authentication using the network login ID
[Client Configuration]
Server Alias: MYSQLS1
Server Name: MYSQLS1
x TCP/TP
x Dynamically determine port [1433]
(Next->)
RESULT:
Connection failed: SQLState: '01000' SQL Server Error: 11001 [Microsoft][ODC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()). Connection failed: SQLState: '08001' SQL Server Error: 6 [Microsoft][ODC SQL Server Driver][Shared Memory]Specified SQL Server not found.
Can anyone please tell me what's going on here ? What am I doing wrong?
No matter how many times I redeploy my report and data sources from my project to Report Manager, I keep getting this error when trying to run the report from Reprt manager. It runs fine in VS 2005 though. I don't know what else to do. I see no way to delete the report from Report Manager in attempts to try to redeploy it from scratch either.
An error has occurred during report processing.
Cannot create a connection to data source 'myservername'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.
I am using SQL 2005, when I run
select * into #import1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webjeff2.xls', 'select * from [jeff2$]')
I get
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
when I try to compile a SP with that statement in it, I get the same error, like
create stored procedure test
as begin
select * into #import1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webjeff2.xls', 'select * from [jeff2$]')
end
so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?
On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.
I have the same problem at two places, both use SQL 2005.
So far there are three questions
1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.
2, why it gives error in compile stage?
3, why two dbs in different Enviroment has the same problem
The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.
created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection) System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
web server has oracle client 10g installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)
