BCP within a Trigger Freezes

Hey all,

Has anybody run into an issue where a call to BCP within a trigger freezes Query Analyzer? When I try to stop the execution of the update statement, the program then crashes.

I have no problem running BCP from within Query Analyzer itself.

Here is the code I am using
----
CREATE TRIGGER tr_ack ON [dbo].[Track_tb]
FOR UPDATE
AS
if update(AckStatus)
insert into ins_hold
select e.dc + e.po, e.emp,
case AckStatus
when 2 then 'Acknowledged'
when 6 then 'Overdue'
end
from eack as e, document_tb as d, inserted
where (inserted.DocumentKey = d.DocumentKey) and
(d.DocumentName = e.DocNum)

exec master..xp_cmdshell 'bcp ins_hold out c:\temp\testfile.txt -c -T -STestServer'

drop table ins_hold
------

I have also tried this with global temp tables rather than a permanent one, doing a select query and using queryout from BCP and receive the same type of crash.

I can do a select * from ins_hold within the trigger and the results display correctly. I can do bcp out from within Query Analyzer using the data from the ins_hold table within the trigger and it works correctly.

The update statement I'm using within Query Analyzer to test is:
----
update Track_tb
Set AckStatus = 2
where DocumentKey = (
select DocumentKey
from Document_tb
where DocumentName = '0018830')
---

Thanks for any help you can provide, I'm greatly appreciative.

-Gregwhen issuing the command:

exec master..xp_cmdshell 'bcp ins_hold out c:\temp\testfile.txt -c -T -STestServer'

you need to use a three part naming convention for the table <DB NAME>.<OWNER NAME>.<TABLE NAME>.

What version of SQL Server are you using?|||Using SQL 2000 for this (sorry I didn't include that originally)

I did try doing what you suggested before and ran into the same problem.

Thanks for the reply.|||Okay, go back to using a three part name, BCP will not work otherwise.

xp_cmdshell runs under the SQL Server Agent account. HAve you created that account on your SQL server?

I just tried issueing a BCP command via xp_cmdshell (Cut & past of your code) and everything worked.

have you tried xp_cmdshell'dir c:\temp\*.*'? I tried it on my server and get a nasty access denied message.

This might be a bugger to track down so hang in there!|||Hey Paul,

Went back to the 3 part naming convention. The Server Agent account is set up on the system. I also tried doing the xp_cmdshell 'dir c:\temp' from within the trigger and it returned the results without any hitches.

When you say you tried the BCP command of my code, do you mean that you tried that within a trigger or just from Query Analyzer? It seems to work fine for me within Query Analyzer, just gets hitched up when it tries to run from within a trigger.

I'm going to run it again today and put all the traces on to see if I can come up with something in there. I tried this before, but didn't see anything indicative of why it may be freezing up.

For the time being, I've been using a simple VBScript program on a 2 hour schedule to accomplish the same task, but I'd much rather have it running within the trigger so the results are instantaneous rather than having a potential 2 hour wait.

Thanks again for the help,|||Okay, did some more testing.

I also tried to do an out from an ISQL command.

It worked fine from Query Analyzer, but when I tried to have it fire from within the trigger, it again hung and eventually froze.

That command was:
----
declare @.str
set @.str = 'isql -Q"select * from ins_hold" -E -oc:\temp\testfile.txt'
exec master..xp_cmdshell @.str
----

So I was wondering if perhaps it isn't configured for write access through sa, so I then did a:
----
exec master..xp_cmdshell 'dir c:\temp > c:\temp\testfile.txt'
----
within the trigger and it executed correctly and didn't freeze.

Doing more research for the time being to see if I can find anything on the 'net related to this (haven't had any luck so far).

Thanks,

Greg|||No, I did not try the BCP via a trigger. I do think you hit apon the problem though.

What happens if you add th "-o" parameter to your BCP command? My WAG is that if any results are returned form xp_cmdshell the trigger will hang. The "-o" will redirect output from BCP to a file eliminating any data returned from xp_cmdshell. Worth a shot!|||Okay, tried using the -o option to see if I can get something returned from BCP, but nothing gets generated (in either the output or log file). When I run from within Query Analyzer, it posts the return text, so it's again limited to being an issue with the Trigger itself.

So back to the drawing board (or Internet if you prefer).

Regardless of whether I get this worked out or not, thanks Paul--you've been a tremendous help thus far.

-Greg|||Okay, I couldn't find anything that said you couldn't do this so I tried setting up a table and trigger to simulate the problem...

Code:
------------------------------------------
exec master..xp_cmdshell 'dir f:\MSSQL2k'
go
create table TestTable(f1 int)
go
create trigger Tigger on dbo.TestTable
for INSERT, UPDATE, DELETE
AS
exec master..xp_cmdshell 'bcp dba.dbo.ProcedureUsage out f:\MSSQL2k\temp.txt -c -T -S hgw2db17 -o f:\MSSQL2k\temp.out', no_output
go
insert into TestTable values(1)
go
exec master..xp_cmdshell 'dir f:\MSSQL2k'
go
------------------------------------------

Results:
------------------------------------------
output
----------------------------
Volume in drive F is New Volume
Volume Serial Number is 183C-AD6B
NULL
Directory of f:\MSSQL2k
NULL
08/21/2002 11:54a <DIR> .
08/21/2002 11:54a <DIR> ..
02/22/2002 03:23p <DIR> MSSQL
08/21/2002 12:02p 108 temp.out
08/21/2002 12:02p 4,319 temp.txt
2 File(s) 4,427 bytes
3 Dir(s) 12,744,605,696 bytes free
NULL

output
----------------------------
Volume in drive F is New Volume
Volume Serial Number is 183C-AD6B
NULL
Directory of f:\MSSQL2k
NULL
08/21/2002 11:54a <DIR> .
08/21/2002 11:54a <DIR> ..
02/22/2002 03:23p <DIR> MSSQL
08/21/2002 12:02p 108 temp.out
08/21/2002 12:02p 4,319 temp.txt
2 File(s) 4,427 bytes
3 Dir(s) 12,744,605,696 bytes free
NULL
------------------------------------------
Any chance you could try the above code on your server? 'F:\MSSQL2K' is the "root" for my SQL Server install. Also, I remembered that you can add the "no_output" parameter to xp_cmdshell, maybe that would help.|||Okay, ran through the code which you had there (couple of changes to reflect my setup obviously). Worked perfectly--within the trigger and everything.

So that being said, I did the same thing with the table I had created in the previous trigger and it worked fine as well.

So now I'm wondering where in my old code I'm getting hitched up at since it is not the BCP actually freezing up (though I was able to do selects from within the trigger previously and display the data I was attempting to write out via BCP).

I also tried having two triggers:
One on track_Tb off of updates which inserts the values into ins_hold
One on ins_hold off of inserts which bcp's itself out to a data file.

Sames results with those.

Both processes seem to work correctly independent of one another correctly, but freeze up whenever you attempt to use them together.

Thanks again,

Greg|||Glad to hear you got everything to work. Sometimes the bloody forest blocks the view of the trees! My advice, press on to your final solution!|||I don't know if this TRIGGER is used in production and is being fired by an event within a client application. If it is you may want to rethink executing BCP from within a TRIGGER. The event that fired the TRIGGER will hold locks on the records until the TRIGGER has completed. Which means that locks are held until the BCP has finished. This could and most likely will degrade performance and cause potential deadlocks.|||Originally posted by achorozy
I don't know if this TRIGGER is used in production and is being fired by an event within a client application. If it is you may want to rethink executing BCP from within a TRIGGER. The event that fired the TRIGGER will hold locks on the records until the TRIGGER has completed. Which means that locks are held until the BCP has finished. This could and most likely will degrade performance and cause potential deadlocks.

It is in fact going to be used in a production environment, with the table being updated from a client application. However, the table being updated is Track_tb, and I am inserting data into ins_hold and running BCP off of the ins_hold table. Will this table be locked as well?

Also, when the insert is performed on the ins_hold table from within the trigger, will the trigger hold this lock until the end of the execution of it? If so, that would explain why the system seems to hang up when I try to do a BCP with that data.

Thanks for the advice and help,

Greg|||What about using DTS and a scheduled job to create the text file every 5 minutes or so? You could even go a bit further and use the trigger to record the last time a change was made to the table and the job could check to see if the DTS was even needed!

OR

Set up a DTS package to export the data to a test file. Set up a job to launch the DTS package. Create a trigger on the ins_hold table to launch the job two or so minutes in the future. This will allow you to get in and out of your trigger quickly, keep your text file current, and prevent excessive data io when frequent updates are performed. I would probably add a table to record the last time the job ran so I could guarantee a text file write every ten minutes or so during peek times.|||To answer your question - yes. When an SQL statement (INSERT/UPDATE/DELETE) causes a trigger to fire and that trigger performs a database operation that in turn fires another trigger, etc., etc.. This become one big, long transaction and all locks are held.

You can have the trigger insert data into ins_hold table, but you'll need to create a scheduled job that will peform the BCP on a time interval. You can use DTS as Paul has stated or you can modify the current code that does the BCP into a stored procedure and schedule it with SQL Server Agent. Either way is fine.|||Hey guys,

Thanks for all the help. I hadn't even thought of the locks on the table when I was trying to do this. I already have a VBScript program working which essentially does what I was trying to do, so I will continue to utilize this and tack it on to NT Services.

Thanks again,

Greg|||Wow, Success!

Thanks to your guys help and pointing me in the direction of locking, I got the sucker working perfectly now.

If I just put WITH (NOLOCK) in the query from BCP, it will do a dirty read (which is fine because this table exists only for this trigger) and BCP executes without any problem.

So what it looks like now

--
exec master..xp_cmdshell 'bcp "select * from ins_hold WITH (NOLOCK)" queryout c:\temp\testfile.txt -c -T -STestServer'
--

Works like a charm...

Can't thank you guys enough.

-Gregsql

bcp utility

I am trying to create a flat file from SQL server and here is
what my query looks like:
bcp "select intakeid from tblintake" queryout d:\out.txt -c
I got this error: "Line 1: Incorrect syntax near 'queryout'."
when I run it in query analyzer. Can anyone please tell me what
I did wrong? I
Thanks.
MLPMLP,
> I am trying to create a flat file from SQL server and here is
> what my query looks like:
> bcp "select intakeid from tblintake" queryout d:\out.txt -c
> I got this error: "Line 1: Incorrect syntax near 'queryout'."
> when I run it in query analyzer. Can anyone please tell me what
> I did wrong? I
Bcp is a command line utility. You need to run it from a command
prompt (aka DOS window). It cannot be run in Query Analyzer.
Linda|||If you want to run BCP from QA, you need to use
xp_cmdshell.
>--Original Message--
>I am trying to create a flat file from SQL server and
here is
>what my query looks like:
>bcp "select intakeid from tblintake" queryout
d:\out.txt -c
>I got this error: "Line 1: Incorrect syntax
near 'queryout'."
>when I run it in query analyzer. Can anyone please tell
me what
>I did wrong? I
>Thanks.
>MLP
>.
>

bcp utilities

Hi
When I used the bcp utilities to output a query to a text file, the date
field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can I
correct this?
Thanks!
ChrisYou could use :-
bcp with the queryout option and specifiy a select statement that
converts the date to your format
or
create a view that returns the data as you require and then bcp out throught
the view
or
specifiy a format file for the bcp file to use
--
HTH
Ryan Waight, MCDBA, MCSE
"ChrisM" <cma1@.mail.com> wrote in message
news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> Hi
> When I used the bcp utilities to output a query to a text file, the date
> field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can
I
> correct this?
> Thanks!
> Chris
>|||Ryan
Thanks for your replied. Could you give me some example on how the queryout
option converts the date to the format I need? Below is the bcp command I
use.
bcp pubs..titles out "C:\bcp_test
Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Thanks!
Chris
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> You could use :-
> bcp with the queryout option and specifiy a select statement that
> converts the date to your format
> or
> create a view that returns the data as you require and then bcp out
throught
> the view
> or
> specifiy a format file for the bcp file to use
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "ChrisM" <cma1@.mail.com> wrote in message
> news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > When I used the bcp utilities to output a query to a text file, the date
> > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
can
> I
> > correct this?
> >
> > Thanks!
> >
> > Chris
> >
> >
>|||You would have to use the CONVERT statement which converts the dates into a
string, and with which you can define a format for the date (the 3:rd
parameter to the function).
bcp "SELECT CONVERT(char(10), ord_date, 101) + ' ' + CONVERT(char(8),
ord_date, 108) FROM pubs..sales" out
"C:\bcp_test\Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Alternatively, you can create a view with the query and CONVERTS and then
export from the view.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ChrisM" <cma1@.mail.com> wrote in message
news:esqf0M%23pDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Ryan
> Thanks for your replied. Could you give me some example on how the
queryout
> option converts the date to the format I need? Below is the bcp command I
> use.
> bcp pubs..titles out "C:\bcp_test
> Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
> Thanks!
> Chris
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> > You could use :-
> > bcp with the queryout option and specifiy a select statement that
> > converts the date to your format
> >
> > or
> >
> > create a view that returns the data as you require and then bcp out
> throught
> > the view
> >
> > or
> >
> > specifiy a format file for the bcp file to use
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "ChrisM" <cma1@.mail.com> wrote in message
> > news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > When I used the bcp utilities to output a query to a text file, the
date
> > > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
> can
> > I
> > > correct this?
> > >
> > > Thanks!
> > >
> > > Chris
> > >
> > >
> >
> >
>

BCP queryout

Hello,
How can I include the columns titles in a bcp SELECT QUERYOUT command? (I
wish I could obtain the same result than in Query Analyzer)
Is there an other way to export the result of a select statement?
Best regardswhy don't use a view to bcp out!
Edwar Bishara
www.sqlcare.com
edwarb@.sqlcare.com|||BCP will not output the column headings. Only the data. You can get the
column headings by using command line osql with the -h parameter.
Rand
This posting is provided "as is" with no warranties and confers no rights.

BCP problem running from EM and Query analyzer

I am running on W2K server with Sql Server 7. I am having a lot of
problems getting some of my stuff to run. I suspect it has to do with
permissions.
But in this case I am trying to run the following command from EM as a
Job as well as directly from Query Analyzer.
BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
I get the following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
I copied and pasted it into a cmd window and it worked fine.
What is the problem here? It is getting very frustrating.
Thanks,
TomHi,
You cant execute directly a BCP command from Query analyzer. As a job you
can execute it but the job type should be "operating system command".
Otherwise use xpcmdshell to execute the BCP from query analyzer
master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
sa -S DINO'
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:3FC704D7.1030207@.deltanet.com...
> I am running on W2K server with Sql Server 7. I am having a lot of
> problems getting some of my stuff to run. I suspect it has to do with
> permissions.
> But in this case I am trying to run the following command from EM as a
> Job as well as directly from Query Analyzer.
> BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
> I get the following error.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> I copied and pasted it into a cmd window and it worked fine.
> What is the problem here? It is getting very frustrating.
> Thanks,
> Tom
>|||Hari wrote:
> Hi,
> You cant execute directly a BCP command from Query analyzer. As a job you
> can execute it but the job type should be "operating system command".
> Otherwise use xpcmdshell to execute the BCP from query analyzer
> master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
> sa -S DINO'
>
That was it.
I was having trouble getting it to work in EM, where I don't use the
xp_cmdshell SP and just copied it directly into the query analyzer.
All works fine now
Thanks,
Tom.
> Thanks
> Hari
> MCDBA
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:3FC704D7.1030207@.deltanet.com...
>>I am running on W2K server with Sql Server 7. I am having a lot of
>>problems getting some of my stuff to run. I suspect it has to do with
>>permissions.
>>But in this case I am trying to run the following command from EM as a
>>Job as well as directly from Query Analyzer.
>>BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
>>I get the following error.
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near '.'.
>>I copied and pasted it into a cmd window and it worked fine.
>>What is the problem here? It is getting very frustrating.
>>Thanks,
>>Tom
>>
>

BCP Out each result of a query in a saperate file

hi,

i am trying to BCP out the result of a query to a seperate file. i am trying to create a seperate file for each record. could somebody please help me out?

Pankaj

Do you really mean a seperate file for each row?
Or a separate file for each table?

If it's for each row, then how many rows are there?
..and why?

/Kenneth

|||

yes,

seperate file for each row. just trying to send file as per required by customer. by the way i am trying with CURSORS.

Pankaj

|||

Sorry for the late answer, it's busy times ;o)

It seems a very odd request from your customer...
In a query, how many rows are you expecting to do this on?

/Kenneth

BCP Once again...

I have to output into an external file for more than one query out put. Presently what i am doing is :-

bcp "SELECT l.Name, j.Name FROM msdb..sysjobs j INNER JOIN master..syslogins l ON l.sid = j.owner_sid" queryout %5\\JOBS.xls -c -S%1 -U%3 -P %4

bcp "select l.name from master..syslogins l with (nolock) inner join %2..tbluser j on l.name = j.tloginname" queryout %5\\SQLUSERS.xls -c -S%1 -U%3 -P %4

The above are the two queries and i export into two different excel files.

Is there any way i can append the second select query data into the already created excel file?

--Sandui don't think you can do it straight from bcp, but you can add a third line to your batch file:

copy first_file.xls + second_file.xls third_file.xls|||Originally posted by ms_sql_dba
i don't think you can do it straight from bcp, but you can add a third line to your batch file:

copy first_file.xls + second_file.xls third_file.xls

That was cool solution...

Now a few more questions :-)

1. Can i give any spacing between the first and second file data in the third file? Or can i give any heading before the data of the first and the second file?

2. After copying the data into the third file how can i delete the first and the second file through the batch file?

--Sandu_Bangalore|||1. echo. >> third_file.xls

2. del first_file.xls & del second_file.xls

bcp Limitation

Is there any limitation on the length of the query used? When the query
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
MadhivananYou can use a view and then bcp from it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115982145.425540.79910@.o13g2000cwo.googlegroups.com...
Is there any limitation on the length of the query used? When the query
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
Madhivanan|||Can you post the exact command you are excuting?. BCP expects double
quotation marks around the query and single quotation marks around anything
embedded in the query. For some reason, sql server is interpreting the query
as an identifier and the max length for identifiers is 128. Check database
option "quoted identifier" using sp_dboption or use SET QUOTED_IDENTIFIER in
the batch.
AMB
"Madhivanan" wrote:

> Is there any limitation on the length of the query used? When the query
> exceeds 128 characters, I get the error
> Server: Msg 103, Level 15, State 7, Line 1
> The identifier that starts with 'Select Id,Name,Descrip, Address,
> others,
> case when len((Select distinct Street from mydb..testw2 where w2' is
> too long. Maximum length is 128.
>
> Madhivanan
>

BCP in Stored Procedure

I am trying to set up a stored procedure to run BCP. My bcp statement runs fine if I run it from Query Analyzer or from a command prompt. However, when I try to run it from a stored procedure, it will not run. The statement is as follows:

exec master..xp_cmdshell 'bcp FeeScheduleValidation..FS_PhysicianCOSMOSSystemFSFile in C:\FeeScheduleValidationTool\Data\testfile.txt -c -t~ -r\n -S(local) -Ufeescheduleuser -Pfeescheduleuser'

Is there something special that needs to be done to allow bcp to run from a stored procedure?Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

I think you should check the permissions of that stored procedure to make sure it allows "execute".|||Have you looked at using the Bulk Insert command in a stored proc rather than using the command shell...?

BCP in stored procedure

Hi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram GHi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram G

It seems many things are missing,plz provide full code of the stored pro.
Joydeep|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = '"C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = '"SELECT * FROM DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE"'
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

END|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)

Hi ,
Try this ...

create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = 'C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = 'SELECT * FROM test.dbo.PACK_DELETION_LOG WHERE convert(varchar(12),delete_dt,101) =convert(varchar(12),getdate(),101)'
print @.query

SELECT * FROM PACK_DELETION_LOG WHERE day(DELETE_DT) = day(@.L_CURRENTDATE)

SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+' -c -P '
print @.query
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY

end

Joydeep

Bcp header

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppasswor
d
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be writte
n
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksSee this thread:
http://groups.google.ca/groups?selm...0%40tkmsftngp12
Anith|||Use a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:

> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassw
ord
> The command works beautifully except that it overwrites the header column
of
> the excel file as well. Is there anyway in bcp to have the results be writ
ten
> leaving the header column intact. Alternatively, I would like the results
to
> be written along with the column names.
> Thanks

bcp Format files help?

I am using bcp to execute a query and would like to use the -f option for format_file so I am going thourgh the books on line and trying to make some sense out of it,no quite clear on it at the moment.

How do I find out the version of bcp utility and would somebody have an example with this options just to see how it works.

ThanksFormat files? Jeez, it's been a while...

As I recall there is an option you can include that will create a format file for you. Run the BCP once with this option and then edit the file it creates. That's the LAZY DBA way...|||You can "cook your own" sample using something like:bcp master.dbo.sysdatabases format sd.dat -Usa -c -SC1126582-B -fsd.fmtThis will create a format file that is properly formatted for the source table, using conventional character output.

A lazy dba is a good dba!

-PatP|||What the hell is -B?|||What the hell is -B?Part of the workstation name, not a parameter!

-PatP

BCP Errors

I execute a store procedure like
Sploadnew 'may'
The may is char type which is converted to a datetime and the query is
something like and I create a tem table
SELECT into ##tetable SUM(sales)+SUM(sales1)
FROM table
group by state
where datepart(mm,prod_date)=datepart(mm,@.datenew)
I want to BCP this result to a server location from within the store
procedure and I did
exec master..xp_cmdshell BCP ##tetable
OUT "//server/folder/rsult.txt"
-S server
-U sa
-P Password
I get this error
erver: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '##tetable'
what is wrong in this approach
Any Answers?I'm not quite sure where the error is occurring here, but
one way to help to determine this is instead of using the
actual BCP command syntax within the xp_cmdshell call
(which may be what is causing the SQL parser to find an
error), put your BCP command in a Windows NT .cmd command
file. Then use xp_cmdshell to execute this .cmd file.
This should eliminate any wacky allowable syntax
differences between what BCP allows and what the SQL
parser allows.
Also, I don't know if you left this out just in your post
or if you left it out in your stored proc, but you might
want to try enclosing the entire BCP command syntax in
single quotes:
exec master..xp_cmdshell 'BCP ##tetable
OUT "//server/folder/rsult.txt"
-S server
-U sa
-P Password'
generally, with xp_cmdshell, if your command syntax
includes spaces, the commnad syntax needs to be within
single quotes.
I hope that this helps.
Matthew Bando
BandoM@.CSCTechnologies (remove) . com
>--Original Message--
>I execute a store procedure like
>Sploadnew 'may'
>The may is char type which is converted to a datetime
and the query is
>something like and I create a tem table
>SELECT into ##tetable SUM(sales)+SUM(sales1)
>FROM table
>group by state
>where datepart(mm,prod_date)=datepart(mm,@.datenew)
>I want to BCP this result to a server location from
within the store
>procedure and I did
>exec master..xp_cmdshell BCP ##tetable
>OUT "//server/folder/rsult.txt"
>-S server
>-U sa
>-P Password
> I get this error
>erver: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '##tetable'
>what is wrong in this approach
>Any Answers?
>
>.
>

BCP Error

Hi All,
I am getting the following error when I execute the following BCP command fr
om query analyser.
BCP Command
=========
xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAd
min_dbo_tbl_CheckServerRoleMembers_20040
331161058 in "c:\Temp\DBAdmin_Fwd_MA
XDRBUS02_DBAdmin_tbl_CheckServerRoleMemb
ers.bcp" /S204.5.2.11,1942 /UDBAdmi
nUser /PDtyrimby0 /c /b1000
/m1'
Error
===
xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAd
min_dbo_tbl_CheckServerRoleMembers_20040
331161058 in "c:\Temp\DBAdmin_Fwd_MA
XDRBUS02_DBAdmin_tbl_CheckServerRoleMemb
ers.bcp" /S204.5.2.11,1942 /UDBAdmi
nUser /PDtyrimby0 /c /b1000
/m1'
Can any help me how solve this problem
Thanks & Regards
BalajiHi,
Replace '/' with '-' (Hyphen) in the BCP command.
-S204.5.2.11,1942 -UDBAdminUser -PDtyrimby0 -c -b1000 -m1
Thanks
Hari
MCDBA
"Balaji" <anonymous@.discussions.microsoft.com> wrote in message
news:4C7F90DC-EDAB-474A-B62E-5E6C72700BD8@.microsoft.com...
> Hi All,
> I am getting the following error when I execute the following BCP command
from query analyser.
> BCP Command
> =========
> xp_cmdshell 'c:\MSSQL7\BINN\BCP
SQLManager.dbo. z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_dbo_tbl
_CheckServerRoleMembe
rs_20040331161058 in
" c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_t
bl_CheckServerRoleMembers.bcp"
/S204.5.2.11,1942 /UDBAdminUser /PDtyrimby0 /c /b1000 /m1'
> Error
> ===
> xp_cmdshell 'c:\MSSQL7\BINN\BCP
SQLManager.dbo. z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_dbo_tbl
_CheckServerRoleMembe
rs_20040331161058 in
" c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_t
bl_CheckServerRoleMembers.bcp"
/S204.5.2.11,1942 /UDBAdminUser /PDtyrimby0 /c /b1000 /m1'
> Can any help me how solve this problem
> Thanks & Regards
> Balaji

bcp copying,but no file

DECLARE @.query NVARCHAR(2000)
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query

I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL

and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.

Could somebody please help meWhere are you looking for the file?

bcp column names

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
Thanks
Use a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:

> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>

bcp column names

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>

bcp column names

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppasswor
d
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be writte
n
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:

> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassw
ord
> The command works beautifully except that it overwrites the header column
of
> the excel file as well. Is there anyway in bcp to have the results be writ
ten
> leaving the header column intact. Alternatively, I would like the results
to
> be written along with the column names.
> Thanks
>

BCP - Hint exceeds

Query hints exceed maximum command buffer size of 1023 bytes(1029 bytes input).
i tried a long query in BCP because i have to insert a character to one of its fields.
hope somebody had encountered and solved this (sql server 2000). thanks in advance..what's your bcp command line?|||here...

bcp "Select 'A'+Customer_Code, PFW_Customer_Code, Newsys_Customer_Code, SBT_Customer_Code, BM_Customer_Code,

Customer_Class_Code, Customer_Company_Name, Customer_Market_Name, Contact_Person_Name, Contact_Person_Position,

Contact_Person_Contact_Number, Contact_Person_Fax_Number, Contact_Person_Email_Address, Customer_Delivery_Address,

Customer_Market_Type_Code, Customer_Market_Cluster_Code, Customer_Level_Code, Customer_Location_Code,

Customer_Route_Name, Customer_CreditLimit, Customer_Status, Customer_Status_Decription, Customer_Auto_Approve,

Customer_Set_Code, Customer_Receipt_Type, Customer_Outstanding_Balance, Customer_Available_Balance,

Customer_Last_Order_Date, Customer_Last_Order_Amount, NewSys_Customer_Salesman_Code, OnHold_Status, OnHold_Date,

OnHold_Remarks, AR_CashAccountCode, AR_CashAccountDesciption, Create_User_Code, Create_Date, Modify_User_Code,

Modify_Date, Status, SpareField1, SpareField2, Remarks1, Remarks2, newsys_terms_fpm, newsys_terms_gp,

newsys_terms_ham from CDO_MAIN..GenMKT_Customer_Masterfile" out c:\GenMKT_Customer_Masterfile.txt -t, -f

c:\GenMKT_Customer_Masterfile.xml -S10.10.1.8 -Usa -Psa|||Perhaps you could make that into a view in SQL Server, and just bcp out the view?|||you should be using queryout, not out.|||wrong code, i already replaced it with queryout but still fails...thus view in sql server runs bcp?|||in that case I would do as mcrowley suggests. create a view, and then run this bcp:

bcp CDO_MAIN..YourView out c:\GenMKT_Customer_Masterfile.txt -t, -f c:\GenMKT_Customer_Masterfile.xml -S10.10.1.8 -Usa -Psa|||Thanks a lot MCrowley & jezemine!!!|||-Usa -Psa
But first you should change the sa-password, put it in a vault somewhere and only use it in emergencies... :angel:

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