SQL Server 2005 Creating Linked Server With DB2 On ISeries
Dec 18, 2006
Hi,
I am using SQL Express 2005 sp1
When I created a linked Server to the DB2 on iseries ( V4R5) , the SELECT statement fetches me only the first record and the following message appears.
OLE DB provider "MSDASQL" for linked server "DB2TEST" returned message "[IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "DB2TEST".
I am using 'MSDASQL' which refers to the DSN setup on my machine using the ODBC "Client Access Driver 32-bit"
Strangely when I used the same DSN in SQL 2000 within the DTS package, I was able to connect and import data and worked like a charm.
Can anyone advice me where I am going wrong.
Thanks in advance
View 1 Replies
ADVERTISEMENT
May 24, 2006
I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.
How do you get the linked tables to display in the Linked Server folder?
David
View 4 Replies
View Related
Dec 17, 2006
Hi,
I am using SQL Express 2005 sp1
When I created a linked Server to the DB2 on iseries ( V4R5) , the SELECT statement fetches me only the first record and the following message appears.
OLE DB provider "MSDASQL" for linked server "DB2TEST" returned message "[IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "DB2TEST".
I am using 'MSDASQL' which refers to the DSN setup on my machine using the ODBC "Client Access Driver 32-bit"
Strangely when I used the same DSN in SQL 2000 within the DTS package, I was able to connect and import data and worked like a charm.
Can anyone advice me were I am going wrong.
Thanks in advance
View 1 Replies
View Related
Oct 27, 2007
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
View 1 Replies
View Related
May 3, 2006
I have installed the trial enterprise edition of sql server 2005. I am trying to recreate a dts packages. I am using an iseries OLE DB provider. In the data flow the connection to a physical file that has string data gets translated into binary data. Has any one gotten a download to work with the correct data?
View 1 Replies
View Related
Sep 17, 2007
I need to create a linked server to a SQL 2005 server (B) from another SQL 2005 server (A). Problem is that Server B's name has a dash in it, and SQL Server doesn't like dashes in the name when running a query against the linked server. Server B's network name is Server-B. For example, from Mgt Studio on Server A, I've created a link to Server B whose name is Server-B, so in my query from Server A, I issue: SELECT * FROM Server-B, and get the following error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Any ideas,
Thnx
Roz
View 3 Replies
View Related
Mar 15, 2001
I wanted to know whether it is possible to query a database in AS400 server from MS SQL Server 7.0 using linked server. If it is possible, could you show some pointers as to how it can/should be implemented.
Thanks in Advance.
Manojkumar
DMT Team
DaimlerChrysler Capital Services
Phone - 203-845-7326
EMail - manoj.kumar@dcxcapital.com
View 1 Replies
View Related
May 6, 2007
Using Microsoft SQL 2000
When creating a table I want to be able to specify not only the db to create it on but also which server to create it on. I have two servers that are linked together, I can view all data without issue.
Doing further research it looks like with the create table command you can tell it the new table name and the database but you can't tell it which server to use. Is there a way of doing this?
Example :
CREATE TABLE LAPTOP.database.dbo.tableName (a INT) gives the following error:
The object name 'LAPTOP.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I am new to linked servers so basically my question is, how do you point to a server within sql before I execute the create table command?
Tx in advance
Mark
View 2 Replies
View Related
Nov 17, 2005
I have a Merge Replication Setup. With 3 computers. One computer acts has a central publisher. The 2nd one is a subscriber, and republishes its data to the 3rd computer. Every thing is being done using Windows Authentication method. the 1st PC has SQL SERVER 2000 (Enterprise Manager, the works). The other two are SQL Server MSDE (Rel. A). My process is up and running and works, but heres my issue.
I need to script the replication installation process out., For future installations on machines that will be located in remote parts of the world. The will reside on a secure DSL network.
Anyone familiar with Scripting Merge Replication knows that once you've run certain scripts/procedures on your subscriber, you have to run a final procedure on the publisher to activate or initiate the subscriber as a valid one, and the you can start the Merge Agent on the subscriber, and voila! you're good. The procdure you run is called sp_addmergesubscription . It goes something like this :
BEGIN
exec sp_addmergesubscription @publication = @publicationname, @subscriber = @servername, @subscriber_db = @Attachedbname, @subscription_type = N'pull', @subscriber_type = N'global', @subscription_priority = 56.250000, @sync_type = N'automatic'
END
My intention was to embed this system in a stored proc on the publisher and then call it remotely from the subscriber, before starting the Agent. IT FAILS!!! This is my proc:
Create Procedure RunSp_AM_subscription(@servername Varchar(30), @IAdbname Varchar(20), @Attachedbname Varchar(20))
AS
DECLARE @publicationname Varchar(30)
SET @publicationname = 'REPUBLISH-'+@IAdbname+''
BEGIN
exec sp_addmergesubscription @publication = @publicationname, @subscriber = @servername, @subscriber_db = @Attachedbname, @subscription_type = N'pull', @subscriber_type = N'global', @subscription_priority = 56.250000, @sync_type = N'automatic'
END
When I call it locally ot works fine, but not otherwise........... :eek:
When I try to create a linked server SQL Server seems to think that one already exists (because Replication has already created the said server as a remote server). I think the fact that I am not using SQL Authentication might be a problem too.
Does anyone have any ideas? I mean seriously, any suggestions on what to do. This is qute Urgent..............
Thanks.
'Wale
View 1 Replies
View Related
Nov 10, 2006
I would like to create a linked server from SQL Server to Sybase IQ. I have created linked servers before so I know how to do that. However, I dont know the specifics of creating a linked server to Sybase IQ.
What are the parameters that are necessary to link IQ to SQL Server. Which Provider do I use? Do I need to install a special driver?
View 2 Replies
View Related
Jan 13, 2004
I'm trying to create a linked server to an Access database that resides on a separate machine.
On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.
If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.
The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.
I create the linked server using the following command:
exec sp_addlinkedserver
@server = 'AccLinkedServer',
@provider = 'Microsoft.jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\DatabaseServerAccessDatabase.mdb'
If I run the SQL statement:
SELECT * FROM AccLinkedServer...AnyTableYouLike
I get this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\DatabaseServerAccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.
Anyone got any other advice, before I go totally insane with this.
Cheers.
View 2 Replies
View Related
Jul 23, 2005
Hii have created a new database and a new linked server that points to anAccessDB using an ODBC DSN.Now inside that new sql db i have create i need to created a new view soi open EM went to views and paste the followingselect * from openquery (AccessLinkedServer,'select * from mytable')i press run and i see the data ok .but when i try to save the view i getthe followingODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operationcould not be performed because the OLE DB provider 'MSDASQL' was unableto begin a distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returnedmessage: [DataDirect][ODBC dBase driver]Optional feature notimplemented.][Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned0x8004d00a].*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
May 19, 2008
Hello,
I am trying to create the linked server to IBM DB2 server, and getting the following error message. Can some one take a look at this and let me know. Thanks!
TCP Provider: No connection could be made because the target machine actively refused it.
OLE DB provider "SQLNCLI" for linked server "Linked server name" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "linked server name" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 10061)
even if I create the linked server using the management studio I am getting the following error when I try to test the connection for the linked server.
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "DB2OLEDB" has not been registered. (Microsoft SQL Server, Error: 7403)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7403&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
View 1 Replies
View Related
Aug 29, 2000
Hi all,
I hope someone can help me with linked servers.
In one of my applications running on a MSSQL database, I need to issue a select call to an Oracle database to pull information into my application.
To accomplish this,
1. the ORacle Admin has created a view of the Oracle database for me.
2. I have installed the Oracle client on the SQl server box.
3. A friend has told me that one way to accomplish this is create a linked server from SQL to use OLE/DB for Oracle to connect to the Oracle database
4. So I create the linked server successfully; create the server login sp_addlinkedsrvlogin successfully
5. When I go to query the database, I get the error below:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.
You will be unable to use this provider until these components have been installed.]
Anybody understand this?
Thanks in advance,
Faustina
View 4 Replies
View Related
Jul 20, 2005
Hi,Please help, I'm getting desperate. Any ideas warmly welcomed!I'm trying to read from a basic excel file (1000 or so rows fromcolumn A) but am having problems. The code I am using is:Declare @Return IntSET NOCOUNT ONExec @Return= [master]..[sp_addlinkedServer] 'READ_XLS', 'EXCEL','Microsoft.Jet.OleDB.4.0' , 'e:jsbackupRACodes.xls',NULL, 'EXCEL 8.0'print 'set up Return : ' + convert(varchar(10),@Return)--NB E: is the drive as seen oon the serverEXEC sp_addlinkedsrvlogin@rmtsrvname = 'READ_XLS',@useself = 'true'print 'login Return : ' + convert(varchar(10),@Return)When I try to read from the (one) excel sheet in the file, viaSelect * from [READ_XLS]...RACodes$or to list what tables/sheets are available, viaexec sp_tables_ex 'READ_XLS'I get the following error:OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.Authentication failed.[OLE/DB provider returned message: Cannot start your application. Theworkgroup information file is missing or opened exclusively by anotheruser.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80040e4d: Authenticationfailed.].What am I missing?*Many* thanks in advance.Andy
View 1 Replies
View Related
Jun 7, 2007
I am trying to create a linked server in the management studio and am getting an error
"A required operation could not be completed. You must be a member of the sysadmin role to perform this operation"
I have tried giving the user rights via
GRANT ALTER ANY LINKED SERVER TO [DOMuser]
as well as adding them to the setupadmin group. No luck.
I can add the linked server via sp_addlinkedserver as the user.
Any ideas?
View 1 Replies
View Related
Jan 24, 2008
Hellow,
This is the siuation:
I have on server A SQL 2005
on server B i have SQL 2000
I want to create in a database on server A a view which uses a linked server to get data from server B.
The linked server works fine.
But when I want to execute the creation of that view i get this error:
OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Procedure VW_ASSETTABLE, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK". The provider supports the interface, but returns a failure code when it is used.
I have checked and the collations between the to databases are the same.
Anyone an idea on who to do such thing?
The rights that i have an server B or not that much.
Thanx for the info
View 1 Replies
View Related
Sep 15, 2015
Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3
/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'
This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".
How to setup the linked server........... Below are the drivers installed on the SQL server
PostgreSQL35W
PostgreSQL30
View 2 Replies
View Related
Apr 30, 2007
I was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts
EXEC sp_addlinkedserver
@server='REMOTE_OFFICE',
@srvproduct='Jet 4.0',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='F:RealEstate_Office1.mdb'
and
EXEC sp_addlinkedsrvlogin
@rmtsrvname='REMOTE_OFFICE',
@useself='false',
@locallogin='sa',
@rmtuser='Admin',
@rmtpassword=NULL
And while querying the linked server from the query analyzer using the following select command
SELECT *
FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK
I got the following error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].
could you explain why this happen
View 4 Replies
View Related
May 4, 2007
Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" 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 'abc'.
------ Error Message Ends
Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.
View 3 Replies
View Related
Apr 18, 2007
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
View 1 Replies
View Related
Dec 28, 2005
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
<code>
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'
</code>
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
</code>
Any ideas what is happening here?
View 10 Replies
View Related
Aug 30, 2006
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
View 4 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Oct 4, 2007
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?
View 8 Replies
View Related
Jul 30, 2007
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
View 15 Replies
View Related
Jan 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 Replies
View Related
May 27, 2008
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
View 1 Replies
View Related
Jul 1, 2006
I have two problems on creating new SQL server database in Visual Web Developer 2005 Express using SQL server 2005 Express.
The 1st problem: (It is resolved)
After installed SQL Server 2005 express and Visual Web Developer 2005 Express. When I tried to create a SQL server database in VWD the following error occurred:
"connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251"
I was stuck with this problem for few days. I did several times of install/reinstall of VWPExpress and SQL server Express, made very sure following proper de-installation sequence as specified in the release readme. But it did not resolve the problem.
Latter I found out that if I login with another Windows XP user account, I don't get the problem.
Changing the security authority of XP file systems and IIS didn't help.
And to prove that I was doing the installation correctly, I tried a new installation of the VWDExpress with SQL server 2005 express in another machine, it worked and I could create new empty SQL database.
After 2 days of frustrating changing of options, parameters, server names, messing with the registry etc.... Finally, I found a way to overcome this problem.
The solution is:
In Visual Web Developer 2005 Express, select:
Tools -> Options -> Database Tools -> Data Connections,
Change the setting: "SQL Server Instance Name (blank for default)" to "SQLEXPRESS". (where SQLEXPRESS is the default server instance name)
That solved this 1st problem.
I still don't know the exact reason for this problem. But I recalled I did installed the VWDExpress few weeks ago, I might have installed it without selecting SQL server 2005 express at that point. I guess the subsequent install/re-install did not clean up those junk in the registry.
I still have the 2nd problem:
The "Create new SQL database..." option is always grayed out when I right click on the Data connections in the "Database Explorer" tab.
I have a bypass for this difficulty for time being:
I have to do it by using the "Add connection....", put in the server name: "myserver/SQLEXPRESS" and a new database name.
Any input form anyone ? Appreciated!
View 3 Replies
View Related
Feb 19, 2008
Actually i have linked server in SQL 2000 with Provider:Microsoft OLEDB for ODBC which is working perfectly fine. I don't know the password for this linked server too.
Now i want to create same linked server with same provider in SQL server 2005 for Microsoft oledb for ODBC but they don't have this provider in 2005. How can i make it to work.ANy expert who knows this.
and how to use same password in linkedserver in 2005
View 12 Replies
View Related
Jun 7, 2007
Dear Friends,
I am getting error when trying to make excel file as the linked server in my sql server the details are as under:
os: windows xp and also tried on windows server 2003
server: sql server 2005 sp2
excel: office 2003 and also tried with .xls file of office 2007
tried to add the remote as well as file on the same computer as linked server, but error was:
could not find installable ISAM and error no was 7303.
Please help me up.
I got the idea to make it as linked server from microsoft article and followed the neat steps and got the errors.
Thanks,
View 1 Replies
View Related
Jan 14, 2008
Hi,
I created a linked server from SQL server 2005 (cluster) to SQL server 2000. When i try to execute the query from local server i get this error
OLE DB provider "SQLNCLI" for linked server "Rem_serv" 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 'rem_User'.
Please help
Thanks and regards
Helen
View 3 Replies
View Related
Sep 23, 2007
I have several general questions about this but I thought I would describe what I am doing first. My senior project team is developing a web application that will wind up using SQL Server for the database. We are writing it in ASP.net and developing the database on SQL Server Express. My job is to develop and deploy the database. I have an ERD designed and am getting ready to start coding the tables, constraints and stored proceedures but am unsure of a couple of things.
1) How do I create a new schema?
2) I can see how to create tables in the GUI but what I am trying to do is create scripts that can be used to create the tables and objects on the actual server that our client will be using. How do I create scripts in 2005 Express? Do I just go click on the new query button?
3) Are scripts that work for Express going to have any problem executing on SQL Server?
View 1 Replies
View Related
