bcp to xml

can someone please advise me on the correct syntax to bcp export executing a
stored procedures results to xml
I know it begins
bcp "exe sp_procedure" queryout
but unser on the rest of the syntax
many thanks
bcp "SELECT * FROM TABLE FOR XML RAW" queryout
c:\table.xml -Sserver -Uusername -Ppassword -c -r -t
"Matthew Butler" <matthew.butler@.srjdebt.co.uk> wrote in message
news:KuZ%i.24820$6v.5502@.newsfe2-gui.ntli.net...
> can someone please advise me on the correct syntax to bcp export executing
> a stored procedures results to xml
> I know it begins
> bcp "exe sp_procedure" queryout
> but unser on the rest of the syntax
> many thanks
>

bcp to xml

can someone please advise me on the correct syntax to bcp export executing a
stored procedures results to xml
I know it begins
bcp "exe sp_procedure" queryout
but unser on the rest of the syntax
many thanksbcp "SELECT * FROM TABLE FOR XML RAW" queryout
c:\table.xml -Sserver -Uusername -Ppassword -c -r -t
"Matthew Butler" <matthew.butler@.srjdebt.co.uk> wrote in message
news:KuZ%i.24820$6v.5502@.newsfe2-gui.ntli.net...
> can someone please advise me on the correct syntax to bcp export executing
> a stored procedures results to xml
> I know it begins
> bcp "exe sp_procedure" queryout
> but unser on the rest of the syntax
> many thanks
>sql

BCP Handling

Hi,

I am executing script like this. How to check for the errors if "master..xp_cmdshell @.bcpCommand" fails. Is there any way to verify that BCP is completed successfully

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)

SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P'

EXEC master..xp_cmdshell @.bcpCommand

Thanks in Advance,declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

|||Is is possible...........|||

In addition, you can also specify an error file for your bcp command, and then check it afterwards for any content:

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P -ee:\myBCPerror.txt'

declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

CREATE TABLE #bcperr(input varchar(255) null)
INSERT #bcperr(input) EXEC master..xp_cmdshell 'type e:\myBCPerror.txt'
IF EXISTS(SELECT * FROM #bcperror WHERE input IS NOT NULL)
RAISERROR('There was an error with the BCP command.', 16, 1)
DROP TABLE #bcperr

You can also just do a SELECT * FROM #bcperr to get the actual error rows.

|||

thanks a lot..

any idea how to trap the number of rows that were transffered during the bcp out process...

|||

If you also specify an output file with the -o parameter, you can 'parse' it and grab the line with the rows total in it.

declare @.rc int
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'

output
NULL
- C:\MYBCPOUTPUT.TXT
23 rows copied.
NULL

(4 row(s) affected)

=;o)

/Kenneth

|||Hey how do I capture this in a table... betn thanks !!!|||

Here's an example.

create table #bcpResult
( result varchar(50) null )
go

declare @.rc int

insert #bcpResult
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'
go
select * from #bcpResult where result like '%rows copied%'
go
drop table #bcpResult
go

=;o)
/Kenneth

bcp error when queryout is stored procedure

I am receiving an error when executing bcp out and queryout is “exec stored
procedure”. Please see details below:
use workarea
go
if object_Id('P_TMP') is not null drop proc P_TMP
go
create PROC dbo.P_TMP
AS
BEGIN
SET NOCOUNT ON
SELECT * INTO #TMP FROM SYSOBJECTS
SELECT * FROM #TMP
END
go
exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
"C:\Temp\TMP.TXT" -c -Ssqldev -N'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#TMP'.
[code]
alter PROC dbo.P_TMP
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM SYSOBJECTS
END
go
[/code]
It should work.
Cristian Lefter, SQL Server MVP
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:BB661426-5BEE-4420-A122-C875A48500A5@.microsoft.com...
>I am receiving an error when executing bcp out and queryout is "exec stored
> procedure". Please see details below:
> use workarea
> go
> if object_Id('P_TMP') is not null drop proc P_TMP
> go
> create PROC dbo.P_TMP
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT * INTO #TMP FROM SYSOBJECTS
> SELECT * FROM #TMP
> END
> go
> exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
> "C:\Temp\TMP.TXT" -c -Ssqldev -N'
> --
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '#TMP'.
>
|||Hi,
Scope of temp (#) table will be lost in next session . So try using global
temp (##) table.
Thanks
Hari
SQL Server MVP
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:BB661426-5BEE-4420-A122-C875A48500A5@.microsoft.com...
>I am receiving an error when executing bcp out and queryout is "exec stored
> procedure". Please see details below:
> use workarea
> go
> if object_Id('P_TMP') is not null drop proc P_TMP
> go
> create PROC dbo.P_TMP
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT * INTO #TMP FROM SYSOBJECTS
> SELECT * FROM #TMP
> END
> go
> exec MASTER..XP_CMDSHELL 'BCP "EXEC workarea.dbo.P_TMP" QUERYOUT
> "C:\Temp\TMP.TXT" -c -Ssqldev -N'
> --
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '#TMP'.
>
|||I posted previous code just as an example, what I think is incorrect BCP
behavior. Applications (including bcp) should not be aware how stored
procedure produces result set.
Real stored procedure is very large and it populates temporary table through
its all execution. I will consider use of global or "permanent" tables to
work around this, but it will not be trivial, because the stored procedure
will be executed in multiple processes at same time.
Does anybody know how I can submit bug fix (improvement) request to Microsoft?
Thank you for your responses!
Vygandas
MCDBA, MCSD
|||BCP tries to get how the result set will look like in order to generate the file format correctly.
It uses SET FMTONLY ON for this. You can try adding SET FMTONLY OFF in the beginning of he "query
(proc)" you will execute so that the temp table will actually be created, but be aware that the proc
then will be executed twice!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vygandas" <vygandas@.discussions.microsoft.com> wrote in message
news:424A4746-2C0C-4D00-B529-82FE4856E371@.microsoft.com...
>I posted previous code just as an example, what I think is incorrect BCP
> behavior. Applications (including bcp) should not be aware how stored
> procedure produces result set.
> Real stored procedure is very large and it populates temporary table through
> its all execution. I will consider use of global or "permanent" tables to
> work around this, but it will not be trivial, because the stored procedure
> will be executed in multiple processes at same time.
> Does anybody know how I can submit bug fix (improvement) request to Microsoft?
> Thank you for your responses!
> Vygandas
> MCDBA, MCSD
>

BCP Error Message

Hi All,
I am getting the below error message when I am executing the BCP command
BCP command
==========
xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_db o_tbl_CheckServerRoleMembers_20040331161058 in "c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_tbl_CheckS erverRoleMembers.bcp" /SSQLSER /UDUser /Ptest /c /b1000 /m1'
Error Message
==========
SQLState = 08001, NativeError = 6
Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
Thanks & Regards
Balaji
Balaji (anonymous@.discussions.microsoft.com) writes:
> I am getting the below error message when I am executing the BCP command
> BCP command
>==========
> xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_db o_tbl_CheckServerRoleMembers_20040331161058 in "c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_tbl_CheckS erverRoleMembers.bcp" /SSQLSER /UDUser /Ptest /c /b1000 /m1'
> Error Message
>==========
> SQLState = 08001, NativeError = 6
> Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
> SQLState = 01000, NativeError = 53
> Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
That means that BCP cannot find the server you are trying to connect to.
Why it cannot do so, I have no idea. To start with, you have not provided
any information on why you should be able to connect to this server.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -T
Please post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here's
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -TPlease post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is
I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here'
s
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -TPlease post the error which you are getting.
"cheilig" wrote:
> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here's
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

Batch Query Execution?

Hi,

I am executing a set of queries. i.e, a batchExecution of queries. I need to know when the batch execution is over so that i can follow up some other process.
Is there any method or event in SQL which can keep track of these things.?
My primary focus is, in knowing the process/BatchQuery completion.
Thanks in AdvanceThere is a SQL Profiler event called SQL:BatchCompleted

See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_204z.asp

You might also be able to query sysprocesses, for example, and look for changes in "status" or "last_batch".

Be certain you understrand the ins-and-outs of batch processing. See

Batch Processing
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_204z.asp

Also note that in SQL 2005, multiple interleaved batches are allowed on a single connection. This is the so-called "MARS" feature (Multiple Active Result Sets). The new system views needed in SQL 2005 for accurately monitoring MARS activity go beyond what is available in the "sysprocesses" view.

See

Multiple Active Result Sets (MARS) in SQL Server 2005
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.asp

Regards,
Clifford Dibble
Program Manager, SQL Server