May 13, 2015 at 2:12 pm
Comments posted to this topic are about the item Dynamic sql for convert SQL statement to txt/csv file
May 28, 2015 at 12:22 am
Why, I always get
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'table'.
Table exists.
Any table return the same error.
May 28, 2015 at 12:42 am
pawel.sowka (5/28/2015)
Why, I always get
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'table'.Table exists.
Any table return the same error.
Hello, pawel.sowka.
Sorry for troubles, you should use full table name in your sql query: DATABASENAME.SCHEMANAME.TABLENAME
I will fix description for procedure running today.
June 2, 2015 at 7:23 am
Thanks for the script. I may have a use for this one. Thanks again.
June 2, 2015 at 8:21 am
Iwas Bornready (6/2/2015)
Thanks for the script. I may have a use for this one. Thanks again.
Thank you for your reply. I updated procedure description on github (for using full name for tables): https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/usp_bcpUnloadSelect.sql
December 9, 2016 at 1:30 am
The procedure kept failing with error - Invalid Object Table Name
This is because the database context is not supplied in the bcp string and it defaults to the master database
see updated script
a. Db name variable is added.
b. This can be passed as a parameter value
or it can be auto supplied based on the current database
c. The executing BCP command is updated to include the database parameter
-- Tested and worked
ps
Procedure call updated to turn on/off xp_cmdshell
-------------------------------------------------
-------------------------------------------------
IF OBJECT_ID('dbo.usp_bcpUnloadSelect', 'P') IS Not NULL
Begin
EXECUTE('Drop PROCEDURE dbo.usp_bcpUnloadSelect ');
IF OBJECT_ID('dbo.usp_bcpUnloadSelect', 'P') IS NULL
Print ' Dropped !!! Procedure dbo.usp_bcpUnloadSelect'
else
Print ' Error !!! Creating Procedure dbo.usp_bcpUnloadSelect'
End
else
Print ' Procedure dbo.usp_bcpUnloadSelect will be created !!!'
GO
Create PROCEDURE dbo.usp_bcpUnloadSelect
(
@outputFilePath VARCHAR(255) -- The path can have from 1 through 255 characters, see documentation
, @serverName SYSNAME = @@SERVERNAME
-- Added Lanre Makinde 2016/12/09
, @dbName Varchar(50) = Null
-- Added Lanre Makinde 2016/12/09
, @sqlCommand VARCHAR(MAX)
, @fileName VARCHAR(300) = ''
, @field_term VARCHAR( 10) = '|'
, @fileExtension VARCHAR( 10) = 'txt'
, @codePage VARCHAR( 10) = 'C1251'
, @row_term VARCHAR( 10) = ''
, @debug BIT = 1
)
AS
/*--
Official bcp documentation: http://technet.microsoft.com/en-us/library/ms162802.aspx
In select statement use full table names: DATABASENAME.SCHEMANAME.TABLENAME
sp_configure xp_cmdshell , 1
reconfigure
go
EXECUTE [dbo].[usp_bcpUnloadSelect]
@outputFilePath = 'd:\'
, @serverName = ''
, @dbName = Null
, @sqlCommand = 'SELECT * FROM dbo.merchant_profiles'
, @fileName = 'file_name'
, @field_term = '|'
, @row_term = ''
, @fileExtension = 'txt'
, @debug = 0; --0|1-- @debug = 1 print only bcp command without executing it.
Go
sp_configure xp_cmdshell , 0
reconfigure
Go
--*/
BEGIN
-- Added Lanre Makinde 2016/12/09
If @dbName is Null or @dbName = '' or len(@dbName) = 0
select @dbName = db_name()
-- Added Lanre Makinde 2016/12/09
BEGIN TRY
DECLARE @filePath VARCHAR(2000) = @outputFilePath +
CASE
WHEN @fileName = '' THEN 'bcp_export_'
ELSE @fileName
END
+ QUOTENAME(REPLACE(CONVERT(VARCHAR, GETDATE(), 126 ), ':', '_'))
+ '.'
+ Case
When @fileExtension is Not Null then @fileExtension
else ''
End;
DECLARE @tsqlCommand VARCHAR(8000) = '';
DECLARE @crlf VARCHAR(10) = CHAR(13) + CHAR(10);
IF @debug = 0
SET NOCOUNT ON
ELSE
PRINT '/******* Start Debug' + @crlf;
/* remove break lines from select statement */
SET @sqlCommand = REPLACE(REPLACE(@sqlCommand, CHAR(13), ' '), CHAR(10), ' ');
/* remove duplicate spaces from select statement */
SET @sqlCommand = REPLACE(REPLACE(REPLACE(@sqlCommand,' ','<>'),'><',''),'<>',' ');
IF @debug = 1
PRINT ISNULL('@filePath = {' + @crlf + @filePath + @crlf + '}', '@filePath = {Null}' + @crlf)
PRINT'@sqlCommand = {' + @crlf + @sqlCommand + @crlf + '}';
SET @tsqlCommand = 'bcp "' + REPLACE(@sqlCommand, @crlf, ' ')
+ '" queryout "' + @filePath
+ '" -d ' + @dbName -- Added Lanre Makinde 2016/12/09
+ ' -T -S ' + @serverName
+ ' -c -' + @codePage
+ ' -t"' + @field_term + '"'
+ ' -r"' + @row_term + '"'
+ @crlf;
IF @debug = 1
PRINT ISNULL( '@tsqlCommand = {' + @crlf + @tsqlCommand + '}' + @crlf, '@tsqlCommand = {Null}');
ELSE
EXECUTE xp_cmdshell @tsqlCommand;
IF @debug = 0
SET NOCOUNT OFF
ELSE
PRINT @crlf + '--End Deubg*********/';
END TRY
BEGIN CATCH
-- EXECUTE dbo.usp_LogError;
-- EXECUTE dbo.usp_PrintError;
Print 'Error -->' + error_message() + ', Error Number :' + str(error_number())
END CATCH
END
go
IF OBJECT_ID('dbo.usp_bcpUnloadSelect', 'P') IS Not NULL
Print ' Success !!! creating Procedure dbo.usp_bcpUnloadSelect'
else
Print ' Error !!! Creating Procedure dbo.usp_bcpUnloadSelect'
GO
December 12, 2016 at 8:10 am
We just had a recent need for this.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply