June 25, 2013 at 11:43 pm
Hi Experts
I have a procedure as follows
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 06/26/2013 15:31:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[exporttocsv]
as
BEGIN
DECLARE @sql varchar(8000)
SELECT @sql = 'bcp "select * from dbo.newtable" '
+ 'queryout "C:\inetpub\wwwroot\uploads\cleansed.csv" -c -t, -T -S'
EXEC master..xp_cmdshell @sql
end
when i run this using "exec exporttocsv" it produces the following output even though the table does exist
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'dbo.newtable'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL
any ideas
June 26, 2013 at 1:17 am
You havent specified a database in your BCP connection. Add -dTestData
June 26, 2013 at 4:19 pm
ok i have done that as follows
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 06/27/2013 08:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[exporttocsv]
as
BEGIN
DECLARE @sql varchar(8000)
SELECT @sql = 'bcp "select * from dbo.newtable" '
+ 'queryout C:\inetpub\wwwroot\uploads\cleansed.csv -d TestData -U sa -P sqldba'
EXEC master..xp_cmdshell @sql
end
but now it is showing this
NULL
Enter the file storage type of field software_manufacturer [nvarchar(max)]:
(2 row(s) affected)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply