It sounds like you might need to create and query linked database servers in SQL Server At the moment you’ve created a query that’s going between different databases using a 3 part name mydatabase.dbo.mytable but you need to go up a level and use a 4 part name myserver.mydatabase.dbo.mytable, see this post on four part … Read more
I have MS Sql server 2012, and Office 2013. This seems to be very finicky, so you may have to adjust to your particular versions. Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version found here: https://www.microsoft.com/en-us/download/details.aspx?id=13255 Install it on your server. Check the user running SQL Server and make sure that user has access to … Read more
As suggested by Scott , you cannot use expressions in OPENROWSET.Try creating a dynamic sql to pass the parameters Declare @ID int Declare @sql nvarchar(max) Set @ID=1 Set @sql=”SELECT * FROM OPENROWSET( “‘SQLNCLI”, ”DRIVER={SQL Server};”, ”EXEC dbo.usp_SO @ID =’ + convert(varchar(10),@ID) + ”’)’ — Print @sql Exec(@sql)
This is for my reference, as I encountered a variety of SQL error messages while trying to connect with provider. Other answers prescribe “try this, then this, then this”. I appreciate the other answers, but I like to pair specific solutions with specific problems Error …provider did not give information…Cannot initialize data source object… Error … Read more