Connect to another server with sql syntax

[andrewsmd]

I need to connect to server b from server a using sql syntax to truncate a table. I cannot add it as a linked server. I've seen you can use openrowset but I need to truncate a table and I don't know how to do that. Any ideas?

[Ocean10000]

I have never tried it but SQL Server Integration Services [en.wikipedia.org] might due what your looking for.

[msdn.microsoft.com...]

[Ocean10000]

Scratch the previous post. I think I found a much much better way for you to do this ("OpenDataSource").

[msdn.microsoft.com...]

Example:

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
 'Data Source=London\Payroll;Integrated Security=SSPI')
 .AdventureWorks2012.HumanResources.Employee

[andrewsmd]

Yes, I was actually going to post back today, that's exactly what I used. I just had to create a stored procedure because I needed to truncate tables. So create the sp to truncate the tables on the actual database, and then from the other server EXEC OPENDATASOURCE('SQLNCLI','Data Source=SERVER\INSTANCE;Initial Catalog=dbName;User Id=user;Password=pass').[dbName].dbo.spName