select statement to another database on another server
-
Hi all, I have this problem: i want to write a select statement. But i am connected to one server and i want to select on another database on another server???? How do i go about doing so? if i run this script: SELECT a.* FROM OPENROWSET('SQLOLEDB.1','Data Source=;Initial Catalog=;User ID=;password=;','SELECT TOP 1 FROM ..dbo.') a Am i doing something wrong??? i get an error: "Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB.1' reported an error." Please advise :(( "Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
-
Hi all, I have this problem: i want to write a select statement. But i am connected to one server and i want to select on another database on another server???? How do i go about doing so? if i run this script: SELECT a.* FROM OPENROWSET('SQLOLEDB.1','Data Source=;Initial Catalog=;User ID=;password=;','SELECT TOP 1 FROM ..dbo.') a Am i doing something wrong??? i get an error: "Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB.1' reported an error." Please advise :(( "Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
I found this code on the internet BUT i haven't run it yet.
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE go EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1' go RECONFIGURE WITH OVERRIDE go EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE goDo you think this could solve my problem? :confused:"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
-
I found this code on the internet BUT i haven't run it yet.
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE go EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1' go RECONFIGURE WITH OVERRIDE go EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE goDo you think this could solve my problem? :confused:"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
Probably.... Its better practice to add the other server as linked server if you are going to use the connection often enough. It will simplify your query writing (no openrowset crap needed). Redneckin with www.wantedband.com
-
Hi all, I have this problem: i want to write a select statement. But i am connected to one server and i want to select on another database on another server???? How do i go about doing so? if i run this script: SELECT a.* FROM OPENROWSET('SQLOLEDB.1','Data Source=;Initial Catalog=;User ID=;password=;','SELECT TOP 1 FROM ..dbo.') a Am i doing something wrong??? i get an error: "Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB.1' reported an error." Please advise :(( "Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
I have seen that to use OPENROWSET command the first parameter should be MSDASQL the SQLOLEDB is used for OPENDATASOURCE. i have tried this query and working perfectly. Also i hope that you have already enabled the OPENROWSET and OPENDATASOURCE command from the SQL server surface area configuration wizard. SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=yourremoteserver_name;UID=your_remote_user_id;PWD=your_remote_password', your_remote_database_name.dbo.remote_table_name) Neeraj Gupta