I'd like to know what the best means available is to enable distributed
queries from a SQL Server 2000, on tables in BOTH SQL Server 2000 and a
remote DB2 server (the latter is not yet available for testing).
So, I would be wanting to write queries or SP's like:
select t1.field1, t1.field5, t1.field6, t2.field55, t2.field22
from MySQLServer.mydb.dbowner.tableXXX as t1
inner join ThatDB2Server.thatdb.thatowner.tableYYY as t2 on t2.field11
= t1.field99
where t1.field44 = @foobar
I'm aware of at least two ways to go here:
1) in SQL Server 2000, create a linked server to the remote DB2
server, either using the wizard or sp_addlinkedserver, and using either
an OLEDB or ODBC connection;
2) using MS Host Integration Server (HIS).
Since I've only just today learnt about HIS, I don't know very much
about it.
Questions:
(a) are options (1) and (2) mutually exclusive, or does one depend on
the other?
(b) can I do (1) without having to bother with (2)? If this, where
would I get hold of the required OLEDB/ODBC Provider?
(c) Is there another way(s) to go about this task?
I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)
The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.
When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.
BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.
When trying to link to an SQL table in Access 2003, the software appears to be malfunctioning.
The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases().
The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source.
Observations: This function has worked normally in the recent past and works on other computers running Access 2003. One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade).
Has anyone experienced anything like this? What's going on?
There is a way to create a link from a SQL Server database to a table located on a MSAccess database? I mean like creating links from MSAccess to other databases. The requested table is updated many times/day, and I dont want to import the table each time an update happens. Thanks, Richard
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
Can any one tell me what I did wrong? Tried to set up link server from SQL 7.0 to Oracle.I did it as it was instruct in BOL. When I tried to click on the table, I got this message "error 7399: OLE DB provider "MSDAORA" reported error. Thanks.
I have created a linked server using an userid that have access to 2 different databases. The linked was created successfully but I am only able to see tables in 1 database but not both. The tables of the databases that I can access is the default database of that userid. Is there a way to see all the tables from more than 1 database that the user is authorized to? Thanks.
I am working from within an SQL database trying to get data from an MS Access Database as a linked server. I can "successfully" add the linked server, but when I try to open it from SQL EM I get "Error 7399. . . " from Microsoft Jet. When I try to query it from QA I get "Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. [OLE/DB provider returned message: Not a valid account name or password.]"
I tried both Access 2000 databases and '97. Same results for each. Big project pending . . . any help is appreciated!
Is it possible to use database links on MS SQL-Server 2000, like on Oracle? If its possibe, what is the syntax and can i create a database link from Microsoft SQL Server 2000 to Oracle?
I have 2 servers A and B DB1 is the database in A and DB2 is the database in B
want to fetch the records from the server B in DB2. My query is
select * from B.DB2.dbo.table1
when i issued this statement in server A from database DB1 got the following error... OLE DB provider "SQLNCLI" for linked server "B" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'sa'.
How to solve this issue.
(Note - login for SERVER A - sa/<nopassword> login for SERVER B - sa/master)
I just reinstalled SQLExpress and all the dbs that I created are no more linked. The files still exist in the folder where the current version stores dbs (...Microsoft SQL ServerMSSQL.1MSSQLData) but they are not "linked" (don't know the right term).
Is there anybody who could help me how to "relink" these dbs to the actual .SQLExpress instance?
Hi all, I am having a problem in sql2000. I am having two databases.One is having all the tables. Another one is having the stored procedures. I ve to use the tables in first one in my stored procedure with out using fully qualified Name.
I work extensively with Access; I have a new project with very large tables (as many as 15 million records). Is it worth my while to link Access to SQL Express if both are on the same machine?
I've downloaded SQL Express and installed it, but it won't let me link to Access 2003. Is there an update or patch to Access 2003 that will make this work?
If I do make the link successfully, will it be transparent - that is, can I work in Access as I always have, or would I have to learn how to create databases and queries in SQL? (I do know that I can make tables in Access and migrate them to SQL Server).
Where would I find simple step-by-step instructions to do this - my interest is more in the substance of my data analysis than in programming per se.
Hi All may be my post sounds stupid but i have on idea about it.
can some one there please clear what is relationship between MDAC and SQL Server. id MDAC part of SQL Server or it is optional tool for some thing else. what it is excatly and links to useful tutorial etc.
Hi, Im using SQL server.I need to get a table from a database in oracle server from my friend. She sent me the database by exporting. But the contents in the database will not be updated if she updated her databse right. What to do if I want the database that she sent me to be updated each time she updates her database? Help me.
I am working on a databse on my local box, my source data is onanother. How can I link the database and table from one server toanother? Currently I am using DTS to just transfer the records!
If I bring up a report in the report server (http://reportserver/Reports/Pages/.... and I set the parameters to see the information I want to see and say to myself, hey, my boss would really like to see this, how would I go about sending him the link to the report with the parameters already set. Ideally when I view the report, the parameters would show in the URL automatically as other reporting products do, however, SSRS does not provide this. I do not even see a function in the report designer to display the link on the report itself so I can cut and paste from it. Anyone know how to do this without having to custom craft a URL or set a one time subscription?
Does Microsoft plan to extend the number of Data Mining algorithms in AS in the future releases? The question is motivated by the task of so called "link analysis" where one should determine how data attributes are related to each other or how and in what extent they influence each other in probabilistic terms. A good solution would be to build a Bayesian Network which gives an insight to how data attributes are related by means of directed acyclic graph. But this approach is not yet implemented in AS2005.
Existing algorithms such as association rules or decision trees might be used but they are far from being ideal for this task (association rules are designed for determining frequent boolean sets in data like Name=Attribute, decision trees work good for classification tasks but perform poor by design for the tasks of revealing attributes direct and inderect influence).
It would be interesting to know what algorithms and approaches Microsoft plans to develop in the future.
Hi, not sure if this is the correct forum to post in?
I am trying to connect to a csv file to import into a table to process thru my vb.net coded application.
I see from BOL that I should not allow ad ho distributed queries and should create a linked server to the data I require.
I am trying to create a linked server to the csv file selected by the user but am coming unstuck as I do not know the correct info to put in the connection settings etc.
Code Snippet
Dim srvAdminServer As Server = Nothing
Dim cnSQLConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.MMConnectionString)
Dim scnServerConnection As ServerConnection = Nothing
Dim lsrv As LinkedServer
cnSQLConnection.Open()
scnServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(cnSQLConnection)
srvAdminServer = New Server(scnServerConnection)
lsrv = New LinkedServer(srvAdminServer, "CSV-IMPORT")
lsrv.ProductName = "MSDASQL"
lsrv.Create()
The code above does not error until I call the create method when I get an unknown product or provider error?
This is all new to and I have searched and searched without success!
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
Step 5: Put the table into a list Add a list to your report and drag the table into it
Step 6: Group by a number of rows Right-click on the list and select Properties. Then click on Edit Details Group. Enter this for the group expression: =Ceiling(RowNumber(Nothing)/3) This will cause the list to group on every three rows. So you'll get a separate table for every three rows.
Step 7: Adjust the group expression in the matrix Edit the column group expression in your matrix and change the RowNumber argument to be the list group name. For example: =RowNumber("list1_Details_Group")
So according to the Step 5 I created a list and add table inside the list... and then i followed Step 6 and then followed Step 7 but when i ran my report
I got this error
[rsInvalidGroupExpressionScope] A group expression for the grouping €˜matrix1_ColumnGroup1€™ uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group.
I have had an unusual request and I thought to ask youreal smart folks out there for a hand. I am working with a non microsoft application that writes links to an html file. I need to extrapolate the link and pull it into a database table on our MS-SQL 2005 server. I believe that this woould best be done with a stored procedure.
I guess that I will get the files and save them on a given directory on the W2K3 Enterprise server and run the proceedure there.
Hi, is correct add a relation to Asp.net_User to one another table(example orders)With column relactioned the table users with the table Orders , UserId , UserName , I am a little confused I need help!. Thank you
I would like to run some queries on two tables that reside in two different database (located on the same server). The databases are both MS SQL 2000.How do I create a database link (or a table link??) so that I can create the queries from the Enterprise Manager or the Query Analyzer? Thanks a lot, Christian
hello. I have a windows 2003 server with sql server 2000 and a public IP address and domain name
when i am on the same network as the server I can link to the sql server with just the dowmain name www.omghelp.com but when I take that same access adp file or mdb file home it says "database can not be found " what do i need to get it to work...help please
