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 )
We rewrote one of our legacy C#/asp.net applications that accesses a sql server 2000 database. The new database schema looks very similar to the old one. The major difference is that some of the atributes in the database tables are different. But pretty much we are using the same tables, plus or minus a few. We need to import the old data into the new database. I have never had to undertake this type of thing before as I am not a DBA, but a developer. I feel a little scared about this whole process. My boss is open to us hiring a contractor to help with the process. My biggest concern is the referential integrity of the database. Can someone help me out. Does this sound like something that can be easily done or should I ask for some help. Ralph
Has any body done the porting from oracle to sql server, what were theissues in porting the data bases?Also suggest some resources which can be helpful in the porting projectTIAGolu
My database knowledge are with MySql and Oracle, but recently I was asked to evaluate the migration of an existing (and maybe more) from ms access to sql server. My question is simple, if all of the sql are hard coded into the code ... how well this sql will work, I mean is the sql between access and sql server are plug'n'play ? However in any case, I always rewiew all of the sql.
Hi, I am porting a massive VB6 project to ASP.net 2005. Most of the code is fine, however because the original developers used lots of data controls and my own time restrictions I thought to replicate the functionality by using Sqldatasource controls instead. These data controls are bound to DBtruegrids. The project has has lots of code like... AdodcOrders.Recordset.RecordCount > 0 or If AdodcOrders.Recordset.EOF or AdodcOrders.Recordset.MoveNext() or AdodcDetail.Recordset!FieldName The problem is the sqldatasource control doesn't have a recordset/dataset property which I can access and manipulate, or does it? What should I do to get round these? The project has thousands of lines like this in so its not feasible to rewrite it (although if I could I would!). Any suggestions please gratefully appreciated. many thanks mike
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 ?
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.
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.
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 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.
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?
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 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.
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
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?
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.
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.
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.
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.
I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need.
In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.
When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.
When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'
This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error: Msg 7372, Level 16, State 4, Line 1 Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723 Linked Server Script: /****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true' I've tried searching the Internet for solution but yielded no results. Can anyone help?
I have a sp which works fine from my local database. I move the procedure to a different database and modify it to access my data via linked server and I get the following:
provider "SQLNCLI" for linked server "Production" returned message "String data, length mismatch". Msg 7421, Level 16, State 2, Line 27 Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "Production". .
Now I've seen references to this error message for ODBC, but as I said above, the same SP works fine if it goes against local data views. I am working with rather long concatenated strings using nvarchar(max) as the data type.
I've tried cast() on just about everything to no avail.
I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
