I am having a Linked server from SQL 2005 to SQL 2000. Linked server is configured with Local account and remote account "remote_user".
When application hits the linked server, it fails with message "login failed for remote_user".
Any idea how to solve this, i don't have access to remote server.
Regards
I have a user who is trying to run a job (call an Stored Procedure) which connects to a Linked Server. He can run it OK using EXEC SP_Name but when he runs from the SQL Jobs it gives him the error: Linked servers cannot be used under impersonation without a mapping for the impersonated login.[SQLSTATE 42000] (Error 7437). The step failed.The Linked Server was setup using another account. Would this be fixed if I add the new user to the Security section of Linked Server without breaking the current configuration?
View 6 Replies View RelatedI want a user to be able to call a stored procedure, that will call an assembly, that will logon on to another SQL Server, perform some functions (calculations), and return the results. I want the user's credantals passed, NOT the SQL Server Account. So in some research, I created this:
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Security.Principal
Public Class SomeName
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub LinkedServer()
Dim cmd As SqlCommand
Dim dr As SqlDataReader
Dim clientId As WindowsIdentity
Dim impersonatedUser As WindowsImpersonationContext
clientId = SqlContext.WindowsIdentity
impersonatedUser = clientId.Impersonate()
Try
Try
impersonatedUser = clientId.Impersonate()
If impersonatedUser IsNot Nothing Then
' as usual, connection strings shouldn't be hardcoded for production code
Using conn As New SqlConnection( _
"Data Source=SERVER1; Initial Catalog=master; Integrated Security=SSPI")
conn.Open()
cmd = New SqlCommand( _
"SOME QUERY", conn)
dr = cmd.ExecuteReader()
SqlContext.Pipe.Send(dr)
End Using
End If
Finally
If impersonatedUser IsNot Nothing Then
impersonatedUser.Undo()
End If
End Try
Catch ex As Exception
SqlContext.Pipe.Send("Error: " & ex.Message)
End Try
End Sub
End Class
Now the issue is that I get this message when I execute this code with the Impersonation code.
Msg 10312, Level 16, State 49, Procedure spr_SQLServerAccess, Line 0
.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.
When I exclude the impersonation code, everything works, BUT executes under the SQL Server Account.
I have used this code to create the Assembly and Stored Procedure:
-- Register the assembly
CREATE ASSEMBLY SQLServerAccess
FROM 'c:linkedserver.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
GO
-- Register the stored-procedure
CREATE PROCEDURE spr_SQLServerAccess
AS
EXTERNAL NAME SQLServerAccess.SomeName.LinkedServer
Any idea's on the error message that is being thrown by SQL WITH the Impersonation code?
Hi,
I like to use impersonation using multiple databases and a user with no login.
I'm working with Powerbuilder 10. I can change users using the command Execute Immediate "EXECUTE AS USER = 'username'". Unfortunately, I can't execute the command 'REVERT' from Powerbuilders Execute Immediate command. The Execute Immediate command prefixes the 'REVERT' command with a exec. ie. exec REVERT.
I thought I could encapsulate the REVERT command in a procedure and run the procedure using Execute Immediate. But, I'm new to SQL Server and I'm not sure if I can.
Does anyone know how to solve this problem? Thanks.
TF
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
Thanks
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
What's the correct way to set up impersonation & SQLExpress
Here's the error I'm getting:Cannot open database "aspnetdb" requested by the login. The login failed. Login failed for user '***ASPDATA'.
SQL Express in installed on C: aspnetdb was set up from aspnet_regsql.exe, on IIS manager - asp.net tab - edit configuration this string is there: data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true The aspnetdb is located in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataThe ASP.Net web is on D: webconfig file has: <add name="LocalSQLServer" connectionString="Server=.SQLEXPRESS;Database=aspnetdb;Trusted_Connection=Yes;" /> <authentication mode="Windows" /> <identity impersonate="true" userName=aspdata@xxx.org password="xxx" />
Should I take a copy of aspnetdb and put it in the web app_data folder?Jess
Hello all-
Before I go any further, I have followed http://msdn.microsoft.com/en-us/library/ms188304.aspx as best possible. I am attempting to send mail through a DML trigger. We'll call the database 'DB', and it is owned by a domain account named 'DOMAINAcct'. The trigger simply blocks any CUD operations on a table which we'll call 'Tbl', and sends an email. Hence, it looks something like...
CREATE TRIGGER [dbo].[TR_Tbl_BlockChanges]
ON [dbo].[Tbl]
WITH EXECUTE AS OWNER
INSTEAD OF INSERT,DELETE,UPDATE
AS
EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'AcctMail', @recipients = 'foo@bar.com', @subject = N'CUD operations not allowed on Tbl', @body = N'Blocked'
AcctMail is a valid profile and operates correctly. I have created the DOMAINAcct user in msdb, given it the AUTHENTICATE permission, and added it to the DatabaseMailUserRole. When the trigger fires, according to the article, the security context should switch to dbo (DOMAINAcct), then be successful when attempting to execute the msdb sproc. Instead I get the usual:
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
Thoughts?
I am installing an application that is a WCF service host running as a windows service under the Network Service account. As part of its configuration I am creating a connectionstring in a config file that will allow the WCF services to access SQL Server. I would like this access to be done using windows authentication not sql server authentication.
connectionString="Server=MYSQLServer;Initial Catalog=MyDatabase;Integrated Security=True;"
So since the windows service is running Logged in under the Network Service account using the above connection string would try to connect to sql server using Network service account. Instead I would like to impersonate another domain account which has has a sql server login and is a user in the database.
Is there a way to configure the connection string to use integrated security but to impersonate another domain user?
Thanks
-- Steven
In the following scenario, I am getting the message 'Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection'.
I am running a Windows Server 2003 with development environment and Sql Server Management Studio in a workgroup on a virtual PC.
My SQL Server 2000 is running on a domain server.
On the virtual Pc I have setup my user login and password to be the same as my domain login and password. Why is the Management Studio not using impersonation and allowing me to connect to the SQL server on the domain?
This is driving me nuts, below is the C# for the proc as well as the runtime error upon calling EXEC on it. Any help would be appreciated. Using UNSAFE Permission Set.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security;
using System.Security.Principal;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void uspExternalConnection()
{
WindowsIdentity newIdentity = null;
WindowsImpersonationContext newContext = null;
try
{
//impersonate the caller
newIdentity = SqlContext.WindowsIdentity;
newContext = newIdentity.Impersonate();
if(newContext != null)
{
using (SqlConnection oConn =
new SqlConnection("Server=.\sqlexpress;" +
"Integrated Security=true;"))
{
SqlCommand oCmd =
new SqlCommand("SELECT * FROM AdventureWorks.HumanResources.Employee", oConn);
oConn.Open();
SqlDataReader oRead =
oCmd.ExecuteReader(CommandBehavior.CloseConnection);
SqlContext.Pipe.Send(oRead);
}
}
else
{
throw new Exception("user impersonation has failed");
}
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
finally
{
if (newContext != null)
{
newContext.Undo();
}
}
}
};
Msg 6522, Level 16, State 1, Procedure uspExternalConnection, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'uspExternalConnection':
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
at StoredProcedures.uspExternalConnection()
I am testing RS2008 CTP6.
When I view a Adventureworks sample report (e.g. company sales) I get this errormessage when I use the option "Impersonate the authenticated user after a connection has been made to the data source" :
Cannot create a connection to data source 'AdventureWorks'.
Must declare the scalar variable "@ImpersonatedUser".
All other connection options without impersonate works fine.
Any idea what can cause this problem?
Thanks in advance.
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
I want to Access External resources inside the CLR Code... But I am getting Security Exception
I have marked Assembly with External Access... here is the way I am doing..
I read articles and MSDN .. everywhere is written to use impersonation like
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext c = id.Impersonate();
//perform operations with external resources and then undo
c.Undo();
}
In above case .. I tried both Windows Authentications and SQL Authentications ...
In case of Windows.. I am have a domain login to logon to my pc, while sql server is at another machine and Active directory is at different machine .. when connect to Database .. it says cannot find user Domainnameuser
and the SqlContext.WindowsIdentity is always null or it has exception User.Toked thew Security exception.
After that .. I tried to user custome Identity .. using IIdentity =GenericIdentity("UserName","Windows");
But there is now difference .. still same exception .. as given below..
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyProcedure()
{
Process[] p = Process.GetProcessesByName("YPager"); //Yahoo messanger exe .. a process
p[0].kill();
}
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyProcedure': System.Security.SecurityException: Request failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at DatFileGenerator.StoredProcedures.'MyProcedure'()
.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].['MyProcedure'].
How could I go ahead... what I should do to accompilsh the task...
Kindlly .. suggestions and ideas..
Thanks,
Muna
As a bit of background first, I'm trying to write a CLR stored proc that will start/stop a Windows Service using the ServiceController class.
The problem I'm having is that the stored proc gets run as NT AUTHORITYNETWORK SERVICE - ie the user the SQLServer Windows Service runs as. This user doesn't have adequate permissions to start/stop a Windows Service (the user only has permission to view the service's status).
The Window's user who is connected to the db - executing the stored proc, does however have adequate permission to start/stop the Windows Service. I'd like to have someway of running the code in the stored proc as if it were this user. If someone could point me in the right direction I'd appreciate it.
I'm having trouble trying to access a network share that comes via a UNIX server running SAMBA. In the first case, I'm running on my local workstation (A), connected to a remote server (B), and attempting to access directory information for a path like:
\a0amsimmsworkseaborg argets11as2981
This path is fully accessible by me from the workstation (A) and the server (B). The files and directories below "work" in the above path are also wide open on the UNIX side (meaning r-xr-xrwx permissions). However, if I attempt to do something like this:
Code Block
WindowsIdentity newID = SqlContext.WindowsIdentity;
WindowsImpersonationContext impersonatedUser = newID.Impersonate();
bool sim_dir_exists = false;
try
{
impusername = Environment.UserName;
Directory.GetFiles(mdcfullpath);
}
catch (Exception e)
{
impersonatedUser.Undo();
SqlContext.Pipe.Send("Exception getting data: " + e.ToString());
SqlContext.Pipe.Send("CWD is: " + Directory.GetCurrentDirectory());
SqlContext.Pipe.Send("User is: " + impusername);
}
finally
{
impersonatedUser.Undo();
}
The "GetFiles" fails with the following exception:
Exception getting data: System.UnauthorizedAccessException: Access to the path '\a0amsimmsworkseaborg argets11as2981' is denied.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at StoredProcedures.mdcinfo(Int32 sim_id, String mdc_base)
CWD is: C:WINDOWSsystem32
User is: amsimms
Initial is: dbserver
Interestingly, if I run the procedure directly on the server (B), I do not get the exception. So this seems to be more of a delegation problem. The server B's sql server instance is running as a domain account (dbserver), which has been enabled for delegation and an spn has been set up. Is there something beyond this either with the impersonate or delegation configuration that I need to do in order for this to work?
Thanks,
--Andrew
When publishing to a file share using Reporting Services (no service pack 2 yet) the following error occurs:
Failure writing file NewFile.mhtml : An impersonation error occurred using the security context of the current user.
I have tried publishing to both Windows XP and Server 2000.
The Reporting Services box is Server 2003.
Publishing account is Local Administrator on both Reporting Services and target boxes.
Logon Locally has been granted on both Reporting Services and target boxes.
Any thoughts?
Hello All,
Login failed for user '(null)'.
I know this issue is all over the forum, however i have not found any posts that help me resolve the issue.
Situation:
I have an ASP.NET 2.0 application hosted currently on XP pro(will be moving to 2003 Server) which connects to a SQL 2000 database that resides on a different server. I have taken the following step to implement my security.
Given my account permissions to the database
Put the following in my web.config
<add name="MyName" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
<authentication mode="Windows" />
<identity impersonate ="true" />
I have set IIS to use integrated authentication and removed anonymous. The application works when run from the web server but not when run from a remote machine.
Thank you for any asistance,
George
Hello--
We have a current situation where analysts will be modeling a variety of problems, all stemming from the same source data (stored in a SQL-Server 2005 relational database).
Analysts that work on the same problem will only have access to:
- A sandbox relational database (which contains views into the same source database). The analyst is db_owner of the sandbox database, so she/he can create data transformations required, etc. The sandbox database contains views to the source database, but the analyst only has read-access to the specific data elements needed from the source DB. So, they are very restricted w.r.t. the source database, but are db_owners of their sandbox relational databases. Note that the analyst will connect to the database via Windows Authentication.
- An Analysis Services sandbox database to use for their modeling, etc. In this AS sandbox db, we've created a role called "Administrator" and checked the permissions: Full control (Administrator), Process database, and Read definition. The analyst's windows account is the "user" associated with this role.
Also, in this situation, the SQL Server 2005 Relational Engine and Analysis Services are running on a single machine. The goal of this security model is to provide analysts with the ability to work in their "workspaces" (both SQL and AS), but not to see other analysts work, etc.
I'm running into a problem when trying to build models using this security model by doing the following:
- Running Visual Studio
- Selecting File -> Open -> Analysis Services Database and choosing the AS DB that I have access to (this is the only one that appears in the drop-down, after specifying the AS server).
- I've created a data source pointing to the relational sandbox DB.
- I've created a data source view choosing the table/view needed for the case table.
- I created a mining structure with a decision tree model
When I process the mining structure, I'm getting the following errors:
- If the data source Impersonation is "Default" -- the error is "The datasource, '<DS name>', contains an ImpersonationMode that is not supported for processing operations."
- If the data source Impersonation is "Use the credentials of the current user" -- the error is the same as "Default" above -- "The datasource, '<DS name>', contains an ImpersonationMode that is not supported for processing operations."
- If I change the data source Impersonation to "Use the service account" and select "OK" in the "Data Source Designer" window, and error comes up with message: "The ImpersonationInfo for '<DS name>' contains an ImpersonationMode that can only be used by a server administrator.
Any suggestions or pointers to help implement this security model to provide analysts with AS and SQL Relational resources for their modeling?
Thanks,
- Paul
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.
i did "Linked server" between To Servers , and it's Working.
---------------------------
For Example :
Server 1 =S1.
Server = S2.
i create table in S1 : name = TblS1
i create same table in S2 : name TblS2
and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2
/****************** trigger Code ***************
CREATE TRIGGER dbo.tr_cpD
ON dbo.TblS1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND
**************************************************
result is :
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.
how i can execute this trigger
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:
<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?
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies View RelatedWe 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 :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
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.
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?
Hi,
I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.
Thank's for all the help/clues you can give me.
i want to run a transaction across mulitpule instances of sqlserver with out linked server.distributed trnasaction can do it with link server , can it do it with out linked server.
View 4 Replies View Related