Hi, I'm using Sql 2000 and I have a linked server called SRVSRV.
The link works because I can list all tables.
How can I access the data i'm trying:
SELECT * FROM [SRVSRV].Database.Table
And it don't works.
What am i doing bad?
Thanks.
Hi All,
I have MS Access 97 database and SQL Server 2005 Express Edition (or SQL Server 2005 Developer Edition).
I want online get data from MS Access and upload to SQL Server.
Help me, which way I select ?
Regards
Hi!!
I need some help to importing Acces file to SQL 2005.
Thank's
Hi,
I m totally new in SSIS Programming.
I want to transfer ABC.mdb table to my oit_imp_temp table.
Can you send me refrence with (above requiments) code that wroks for u !
thanks a lot !
Hi,
I m totally new in SSIS Programming.
I want to transfer ABC.mdb table to my oit_imp_temp table.
Can you send me refrence with (above requiments) code that wroks for u !
thanks a lot !
I've got a website with authentication mode "forms". Now I need access to the database to authenticate the user. The SQL Server is located in another domain than IIS.I don't know how to establish a trusted connection with the SQL Server. How can I establish this?
View 2 Replies View RelatedHi
I would like to acces a SQL Server that is on a web server.
I want to to this to be able to control the sql server and database to create table, trigger...
For "MySql" i use DBTools to acces my database via the web, is there something i can use like BdTools to acces the Sql Server 2000 database
I'm new to SQL so can somebody can tell me what to do!
Thanks
I try to access an OLAP Server from a remote compute, using ADO MD Library with following string conection :
"Datasource=SERVNAME; Provider=msolap; Initial Catalog=FoodMart 2000;"
I recive the following error code: -2147467259 (0x80004005), "Unspecified error"
Can sombody help me
Hi there.
How can i convert a Ms Access Database file (.mdb) to an SQL Server 2005 Database ????
is there any tools ?? or what ever can help me .
Thank you.
After an internet search I have some questions regarding SQL Server access from a mobile device via LAN / WLAN.
I will write a application in C that write data to an SQL server. First if I correct that's not possible to acces an SQL server via ODBC on Windows CE? What alternatives are there?
Stef
I am new to SQL Server but have been using Access to provide Multi-User acces to a central data store. We are evaluating the replacement of Access with SQL Server. With Access we don't need any special installation; I believe that the Jet Database Engine installed locally on each user's PC allows them to work with this in a disconnected manner; we use ADO.net 2.0 with VB.net 2005.
If each user has a locally installed SQL Server Express can they all attach to a database that resides on a shared network location?
In another post on the Forum I see the following regarding multi-user access:
Our application You will need to pick a central computer that everyone will be using the access the data and attach the file to the parent instance of SQL Express on that comuter, giving it a name that you can use to access it. Then you will need to modify your connection string to connect to the named database on the central server...
Can multiple locally installed SQL Server Express instances attach to a single data store that resides on a shared network location?
If not, what permissions do I need to create a parent instance of SQL Server Express?
Thank you for helping me understand how to implement our solution.
JudiR
What is the fastet connection between access 2003 to sql server 2005?
View 2 Replies View RelatedI am developing an asp.net app using visual web developer. I am using a sql server 2005 db for the back end. I have learned that I have to restart my machine if I want to work with the db in SQL Server 2005 Express after working with it in Visual Web Developer.
I am wondering why this is. I am assuming that the db is locked when it is used with VWD. Is this correct? Am I refering to it's status of "locked" correctly? Should I call this something else?
-thanks to anyone that can help
I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password
I can then see the tables in EM, but when I try to query them in QA I get this
Select * from db2db...customer
Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'
I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server
My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data
any ideas ? I think I need to enter something in the catalog box, but what ?
thanks in advance.
I am trying to run a select statements against linked server.
I have NT server running SQL7 Enterprice and Rumba2000. I used Rumba driver to configure a DSN (ODBC based) to a AS400/DB2. Then I created a linked server. Good news are : I can see a list of tables.
Bad news : when I am trying to run a select statement either through stored procedures or directly in SQL analyzer I am receiving error message Object 'linkedservername.databasename.tablename' doesn't exist.
What am I missing here?
I have a linked server that was built on PostgreSQL database connection. When I try to query one of the tables in the linked server it is giving attached error.
View 1 Replies View RelatedGood Morning
the available oledb providers for mysql don't seem to work with sqlserver 2005.
So whats the best way these days of exposing data from within sql server from a mysql data base.
What I wanted to do was create a linked server connection to the mysql server using either oledb or odbc, but neither seem to work.
There is a .net provider for mysql, but that won't work to create a linked server connection.
any ideas appreciated.
What I want to do is write some scheduled proceedual sql from within sql server to query some tables in mysql and update some sqlserver tables.
thanks
David Hills
Hello,
I have a server that is used to query other servers to ensure that things are functioning correctly. On some of our servers we are running ServiceBroker and some of the monitoring involves querying the SYS.CONVERSATION_ENPOINTS view on these servers.
When I query the SYS.CONVERSATION_ENPOINTS view over a linked server it returns zero rows, though when I run the same query locally it returns data. I initially thought it was a permission issue but it seems I can see data across the linked server to other system views within the sys schema (ie sys.all_objects).
Is there something different/special about SYS.CONVERSATION_ENDPOINTS that prevents me from seeing its data across linked servers or have I simply got permission problems.
Thanks
Ian
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
I wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
Hi,
I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.
Please let me know.
Thanks
Hello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.
I'm trying to set up a linked server as in the article
http://www.databasejournal.com/features/mssql/article.php/10894_3085211_3
I use the following SQL to set up a linked server with Excel and attempt to retrieve data. The c: empauthors.xls is downloaded from the second page of the article above.
EXEC sp_addlinkedserver 'EXCEL',
'Excel',
'Microsoft.Jet.OLEDB.4.0',
'c: empauthors.xls',
NULL,
'Excel 8.0',
NULL
GO
SELECT * FROM EXCEL...Sheet1$
GO
The query sets up the linked server OK, and retrieves the field names, but no data is returned. The results in Management Studio are a list of column names:
au_id au_lname au_fname phone address city state zip
contract
then an error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Unknown".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT `Tbl1002`.`au_id` AS `Col1004`,`Tbl1002`.`au_lname` AS `Col1005`,`Tbl1002`.`au_fname` AS `Col1006`,`Tbl1002`.`phone` AS `Col1007`,`Tbl1002`.`address` AS `Col1008`,`Tbl1002`.`city` AS `Col1009`,`Tbl1002`.`state` AS `Col1010`,`Tbl1002`.`zip` AS `Col1011`,`Tbl1002`.`contract` AS `Col1012` FROM `Sheet1$` `Tbl1002`" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL".
Does anyone know how to resolve this? I'm using Windows Authentication to connect to a SQL server (9.0.3054) instance on localhost, and am running from a test database query window.
Thanks!
Hi All,
I have problems while using the Linked Server in MS SQL Server 2000 for data porting.
The Scenario :
I have about 900 hundred tables created in SQL Server database. These tables are freshly created and has no records.
I have created a Linked Server with a DSN connecting to a Sybase database from which the data has to be ported to the newly created tables in SQL Server.
The database creation as well as data porting is done by a Delphi application by executing the scripts in several .sql files.
I have shown an example script below which does the data porting.
INSERT INTO TEST_DATA (COL1,COL2,COL3)
SELECT COL1,COL2,COL3 FROM [LINK_SYBASEDB]..[DBA].[TEST_DATA]
The Issue :
I often get the below error which stops the data porting process ( the error is logged in the Errorlog by the Delphi application )
D:DBPortTEST_DATA.SQL
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
NOTE : This error is NOT COMING CONSISTENTLY.Often I get this error and sometimes all the data is ported without this error.
It will be great if any of you can help me to resolve this issue.
Thanks in advance !!!
regards,
Hari Haran Arulmozhi
I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.
I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):
Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:
EXEC procStmtDataToReno 'B'
and some snippets of the SP:
CREATE PROCEDURE [dbo].[procStmtDataToReno]
@CoCode as varchar(2)
AS
--various declarations and such
DELETE
FROM [10.1.1.4].OnlineAR.dbo.StatementData
WHERE CompanyID = @CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )
--code to build temp table
INSERT INTO [10.1.1.4].OnlineAR.dbo.StatementData
SELECT * FROM #Statement
GO
TIA
Hi,
Please bear with me if my vocabulary or phraseology is wrong (and if it is, I wouldn’t mind being corrected).
Background:
We have an application that uses a Cache database (which is a complete black box for me). The data is exposed to us through ODBC. We have set up a linked server that I can see the names of the tables and views in Enterprise Manager and can query on them with Query Analyzer.
Problem:
There are a lot of extra long string fields. I can see the data in both Crystal Reports and Access (in Crystal they are shown defined as sting [32395] and in Access they are shown as a Memo field).
I cannot get Query Analyzer to display the data. I have tried the following select statements:
select definition_text from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as ntext) from cwsavpmtest_live..system.problem_def_data
select convert(ntext,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as text) from cwsavpmtest_live..system.problem_def_data
select convert(text,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as varchar(8000)) from cwsavpmtest_live..system.problem_def_data
select convert(varchar(8000), definition_text) from cwsavpmtest_live..system.problem_def_data
… and get the same error for every one:
quote:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[problem_def_data].definition_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
So my question is: Am I out of luck and have to be content with looking at the data in Access or Crystal or is there some way I can look at these fields through Query Analyzer?
Thanks,
Laurie
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?Thanks a lot.
View 3 Replies View Related
Hi
While trying to run a query from SQLServer 2005 on linked server (Oracle 10G) using Open query, I receive followin error message:
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "EDWORA".
It runs for some hours before giving the mentioned error.
My query is something like :
select * from openquery (EDWORA, 'select * from Tablex')
Any idea how can I solve this issue?
Regards
Elham
Hi,
I am trying to access the View through linked server connection .The linked server connection is between two sql server 2005.
The problem is I am not able to access 2 columns of nvarchar datatype of length 255, from the view through select statement.
I do a SELECT col1,col2 from VIEW,then getting the following error .
Cannot get the data of the row from the OLE DB provider "SQLNCLI" for linked server "SRVXPR". Could not convert the data value due to reasons other than sign mismatch or overflow.
Then I tried with
Select cast(Col1 as nvarchar) ,
cast(col2 as nvarchar) from VIEW,
I am able to get the values.
The collation type is same for all underlying tables and also for the servers.
Is there any restrictions in handling nvarchar data thru linked server/ anyother size limitation.
I don't want to use cast as this will lead to performance problem.
Thanks,
Philip
Hi,
Here is the problem:
I have a server that is linked to two other servers. The server I am linking has SQL Epress installed, whereas the servers I am linking to (and getting the data from) are enterprise versions of SQL. I used the following code to link both the servers (changing the server name and the datasrc field) and both worked fine.
Code Block
EXEC sp_addlinkedserver
@ Server='SALEEN',
@ srvproduct = 'SQL OLEDB Provider',
@ provider = 'MSDASQL',
@ datasrc = 'Debtsolv_Test'
GO
EXEC sp_addserverlogin 'SALEEN', 'false', 'NULL', 'sa', '**********'
One of the links is fine and I have no error messages when trying to access the data, but the second link throws this error when I attempt to get any data from the linked server:
Code Block
OLE DB provider "MSDASQL" for linked server "SALEEN" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider for "MSDASQL" for linked server "SALEEN".
I am pretty new to SQL and am totally stumped! If any one has any ideas I would be greatful.
Thanks