3

I am using SQL Server 2014 and I have the following T-SQL query running on my local PC:

USE [MyDatabase]

EXEC [xxx.yyy.10.23].[Emp_LIVE].[dbo].[procBIInterfaceEmployee]

[xxx.yyy.10.23] is a linked server. When I execute this T-SQL code, it runs fine and returns the expected results.

I would like to wrap the above codes into a new T-SQL query that would write the results into a csv file on my local PC.

Searching StackOverflow.com for a solution, I have landed on these 2 questions:

  1. Creating a CSV file in a SQL Server stored procedure
  2. How to write using BCP to a remote SQL Server?

So, I came up with the following (based on the answers from the above 2 questions):

DECLARE @string AS NVARCHAR(4000)

SELECT @string = 'BCP "exec [xxx.yyy.10.23].[Emp_LIVE].[dbo].[procBIInterfaceEmployee]" QUERYOUT c:\testfolder\empdata.csv -c -T -t, -S"xxx.yyy.10.27\BI-SQL"'

EXEC master.dbo.xp_cmdshell @string

xxx.yyy.10.27\BI-SQL is the remote server where [MyDatabase] is hosted. BI-SQL is the instance of that server.

Running my new set of code, I get the following errors:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
NULL

How do I move forward with this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3115933
  • 4,303
  • 15
  • 54
  • 94

1 Answers1

0

Try this:

  1. Insert your sp result in a local table
  2. and then export from your local table.

USE [MyDatabase]
GO


CREATE TABLE [dbo].[procBIInterfaceEmployee](
--whats in the table
)



INSERT INTO [dbo].[procBIInterfaceEmployee]
EXEC [xxx.yyy.10.23].[Emp_LIVE].[dbo].[procBIInterfaceEmployee]



EXEC master.dbo.xp_cmdshell 'BCP "[MyDatabase].[dbo].[procBIInterfaceEmployee]" OUT c:\testfolder\empdata.csv -c -T -t, -S"LOCALHOST" '

Note: if you have instance, you will need to do -S"LOCALHOST\SQLEXPRESS"

See the bcp page for all parameters. https://learn.microsoft.com/en-us/sql/tools/bcp-utility

e-Fungus
  • 321
  • 3
  • 17