I have two server defined (S1 and S2) S1 has a link defined to S2. If
I open Query Analyzer and run a query referencing S2 server. My
statement returns data (lets say I am using MSSQL standard
authenitcation). Now I have a ASP.NET application that connects to S1
server using ODBC. The exact same statement returns an error.
Driver][SQL Server]The operation could not be performed because the
OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB
error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
we are having the same code base in both the environment but we have different version of SQL Server Version(Env 1 - SQL 2012, Env 2 - SQL 2008). We are getting the expected result in Env - 1 but not in the ENV -2. So we tried to get the SQL trace for the web request we found that Date format getting changed.
2012 - Date Format in the SQL Trace for the request '2015-09-10 00:00:00' which is working as expected. 2008 - Date Format in the SQL Trace for the request 'Sep 10 2015 12:00:00:000AM' which is Not working.
Complete SQL Trace for Request. Evn - 1 - SQL Server 2012 exec dbo.usp_GetWorkQueue @ClientName='CLT',@HasFilter=1,@FacilityName='',@StartDate='2015-09-10 00:00:00',@EndDate='2015-09-18 00:00:00',@OrderDirection='D',@StartIndex=1,@PageSize=20
I got this message from sql "Server was unable to process request. ---> A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) " Does anyone know what happen? Thanks
Since yesterday the sqlserver service has been falling over, my error logs says the below?
006-04-26 11:28:41.64 spid51 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'sp_MSgetversion'. 2006-04-26 11:28:41.92 spid51 Error: 15457, Severity: 0, State: 1 2006-04-26 11:28:41.92 spid51 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.. 2006-04-26 11:28:42.04 spid51 Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_msver'. 2006-04-26 14:44:08.92 spid2 SQL Server is terminating due to 'stop' request from Service Control Manager.
What do I need to do? Is is just a matter of running the reconfigure script? Yesterday night we had some monitoring installed on the server. Can anyone help?
I am having trouble logging into my sql server from an asp application using SQLOLEDB.
I am running XP, IIS5 and SQL Svr 2000 on my developement machine. I am able to log into the database using localhost/application.
When I port the code and database over to the server (Server 2003, IIS6, SQL Svr 2000), I am unable to connection using the actual url of the site.
I set the login up on the logins for the database manager along with password (using multi, not Windows authentication). Added that user to the database security logins, giving it dbo, srvr admin, etc. Like I said, works on my dev machine.
The SQLOLEDB request is Provider=SQLOLEDB;Data Source=(local);User ID=UID;Password=PWD;Initial Catalog=DBName.
I set the database up as a System DSN in ODBC, using SQL Server Authentication. The connection tests out just fine.
When I attempt to connect, I get an ADO message indicating that the user id that I supplied could not sign in to the server.
This is driving me nuts.
Does anyone out there have any ideas as to what I have missed on the server vs local developement machine?
Hi, i am using Vista and iis6. Evertything seems to be configured ok, but when i try to open this url: http://localhost/Reports/ i get the following error:
Server Error in '/Reports' Application.
Request is not available in this context this error is when Report directory is set for "DefaultAppPool", but in "ClassicAppPool" i get another error:
Error
The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
I have been getting this message in the event viwer and my SQL server goes down. I have to go and start the service once again. Can somebody throw some light as how to solve this problem?
Hi, A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) I am using a direct connection from my Gridview to perform a huge database search. the stored procedure will take 10- 60 seconds to execute the query.
What the problem i am facing is when the sp takes long time (more than 1 minutes), then when i try any other operation like calling another sp frm another page, i am getting the above specified error.
I am new to SQL 2005. I have one issue and it is as follows:
I write a simple query and run it; it works fine.
Now I break the network connection and run the query; in this case it runs fine.
But when I reconnect the network and run the query, then I get an error as follows:
"A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - An unexpected network error occurred.) "
Every thing is local here. So how the network issue comes here?
I am using SQL Server Reporting. All reports are deployed on REPORTING SERVER. I want to implement security roles as same as in Active directory. Currently reports are being accessed using Anonymous user setting in IIS. I have added users in SQL Server security as well as data base level. But when user accesses report following error occurs: The request failed with HTTP status 401: Unauthorized
Please reply with solution as soon as possible. I will be very thankful to you
I've tried to search on the web for a solution for this error but i didn't find any working solutions for this problem. We have 2 servers. The first one is the server we use to develop our ASP.NET 2 application. On this server we don't have this error. On the other server (use by our client) we have this error sometime and i don't know why. Both servers have the same configuration and both application have the same web.config file.
I've tried to add a try-catch and retry the query when the error occured. This seems to be working but we don't want to have to change all our connections and since we don't have this problem on the other server we want to find the source of the problem.
Installing MSDE (SP3) on a clean updated Win-XP system with all the latest fixes installed unsuccesfully. A clean systems means a fresh installed Win-XP, updated all using webupdate. And Excel 2003 installed. Thats all (not even AntiVirus (yet))
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.
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
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??
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:
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".
Okay, I'm a novice! I installed Back Office with NT opperating system on my home computer. I want to learn SQL 7.0. This is incredible, but I can't launch the program. I've tried everything under Start, Programs, SQL, but nothing listed seems to get me into SQL 7.0. What am I missing? Help is most appreciated!!
public int RunSQLQuery(string sqlStatement) { if (_sqlConnection.State != ConnectionState.Open) _sqlConnection.Open();
_sqlCommand = new SqlCommand(sqlStatement,_sqlConnection); return _sqlCommand.ExecuteNonQuery(); }
Now i am stuck on how i will get the ID from the sp (that is @ID) and return it to my C# code as i need it to update the same row further on :)all i am getting till now is the number of rows 'changed' i.e. 1! any thoughts? thanks
Hi,I have quite a complicated request to do in sql.I've got on table with 3 fields (id, field1, field2) and I have toprocess a request on each of the records, one by one, and thendetermine if the status is OK for each recordsFor example, I would check if the sum of field1 and field2 is greaterthan 10.What is the best way to do this ? It has to be done in a storedprocedure. I can't return the status for each one of the records, soshould I store it in a temporary table and do a select on it from mytier application (vb.net) ?ThxSam
Hi,I'm going to explain as clearly as possible:I have two tables:Relationships(relation_id, table1, table2)Relationfields(relation_id, field1, field2)In Relationships, relation_id is the primary keyIn Relationfields, relation_id is the foreign keyI have a front-end interface that allows the user to add records toRelationships and Relationfields as followed:The user selects a table1 and table2 values from listboxes. These arereal table names from sys.objects, so then the user can select fieldsof these tables on which he wants to create a JOIN.Anyway, I can easily insert the table1 and table2 into Relationships(relation_id is an auto-increment). Then I need to get the relation_idof this new Relationship (easy since I know which values I've insertedand table1-table2 associations are unique.Now the PROBLEM :I need to insert into Relationfields all the fields selectioned by theuser for each of the two tables . But the user might have selectedseveral fields from table1 and table2, so I need to pass A LISTPARAMETER to my Stored Procedure as I don't know how many values offield1 and field2 there is going to be.I hope this is clear enough. Is it possible to achieve what I want ?Should I pass an entire concatenated string with values separated bycomma or whatever and then decrypt it in the stored procedure ?Thx
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'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.
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.
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.
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too) EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType". Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC] GO /****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vwSurgicalVolumes] AS SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT * FROM OPENQUERY ([PORTALWEBDB], 'SELECT --AllLists AL.tp_ID AS ALtpID ,AL.tp_WebID as altpwebid ,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.