Objective: The below script is used to extract data from sql table in the file and transfer to the remote location.
use DBMONITOR
go
DECLARE @DBNAME VARCHAR(50)
DECLARE @SELECTQUERY VARCHAR(MAX)
DECLARE @SERVERNAME VARCHAR(50)
DECLARE @return_value INT
Declare @strFileName Varchar(MAX)
DECLARE @SQL VARCHAR(8000)
DECLARE @idInc INT
Set @strFileName = '\\RemoteLocation\backup\TEST\TEST.DAT'
Set @Servername='IP Address or Servername' -- To get servername - Go to SQL Server - Query - Select @@servername
Set @DBNAME='Databasename'
set @SELECTQUERY='select top 10 * from Tablename'
SET @SQL ='Sqlcmd -S '+ cast(@SERVERNAME as varchar(50)) +' -E -d '+ cast(@DBNAME as varchar(50)) +' -Q "'+CAST(@SELECTQUERY AS nVARCHAR(MAX))+' " -o '+ cast(@strFileName as varchar(MAX))+' -s"," -W'
Print @SQL
EXECUTE @return_value = master..xp_cmdshell @SQL
