bcp_init fails on Windows XP, works fine on Windows 2000

I have the following problem with bcp_int() call: same program that
works just fine on Windows 2000 fails silently on bcp_init() call on
Windows XP. All other conditions are the same: database, table name etc.
Note that program was compiled on Windows 2000. Has anybody else
encountered similar problem?
I had the same problem with XP and Win98.
I have installed MDAC 2.8 download on Microsoft site and it correct the
problem.
But it doesn't correct problem on Win98.
Let me know if this solution resolve your problem
"Yuriy Dudko" wrote:

> I have the following problem with bcp_int() call: same program that
> works just fine on Windows 2000 fails silently on bcp_init() call on
> Windows XP. All other conditions are the same: database, table name etc.
> Note that program was compiled on Windows 2000. Has anybody else
> encountered similar problem?
>

bcp_init fails on Windows XP, works fine on Windows 2000

I have the following problem with bcp_int() call: same program that
works just fine on Windows 2000 fails silently on bcp_init() call on
Windows XP. All other conditions are the same: database, table name etc.
Note that program was compiled on Windows 2000. Has anybody else
encountered similar problem?I had the same problem with XP and Win98.
I have installed MDAC 2.8 download on Microsoft site and it correct the
problem.
But it doesn't correct problem on Win98.
Let me know if this solution resolve your problem
"Yuriy Dudko" wrote:

> I have the following problem with bcp_int() call: same program that
> works just fine on Windows 2000 fails silently on bcp_init() call on
> Windows XP. All other conditions are the same: database, table name etc.
> Note that program was compiled on Windows 2000. Has anybody else
> encountered similar problem?
>

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 import help

I am trying to import data thru a bcp call to pull data from an access database. I am having trouble accessing the access database. Below is the bcp I tried, along with an openrowset attempt. Neither of them are working. Any help would be greatly appreciated.

bcp select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
from Line Report in I:\2007\11\D1107.MDB -q -UXX -PXX

select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'I:\2007\10\D1007.MDB';'XX';'XX', 'Line Report')Are you able to add the mdb as a linked server - you could then simply run SQL statements irectly on the data? I'm afraid I am unable to test this from my current location so I'm not able to check!|||I can add it as a linked server, but then I cant actually query against any of the access tables.

sp_addlinkedserver 'D1107', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\\ctisvr\stats\2007\11\D1107.MDB'

SELECT *
FROM D1107...Line Report

when I run the select statment I then get:
'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.]'|||Do you get the same error message for the OPENROWSET query? Also, what version is the Access database created in?

As a side note, you have probably already learned that BCP is used solely for "flat files". Pure ASCII/UNICODE characters.|||Same error. Not sure what version it was created in as I am importing it from a 3rd party vendor.|||Hi.
why not use a DTS package? and test this SELECT *
FROM D1107...[Line Report]|||Did you try the DTS Wizard?

I don't have a full version of Sql Server, only MSDE. So I didn't have the DTS Wizard. Then I looked in an old Office 2000 disk I've got, and found it. I copied over dtswiz.exe and some other .dll and .rll files to my Sql Server\tools\binn folder and it worked.

BCP call to stored procedure - broke during upgrade from SQL 7.0 to 2000

I have this stored procedure that takes a few parameters like date and
merchant ID, and basically goes through a set of if-then statements to build
a SQL SELECT string.

When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
worked from Query Analyzer, but not in BCP. It used to work in BCP just
fine with 7.0. The error I get now is:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
at least one column

What's really strange is, if I instruct the stored procedure to simply print
the SELECT string, then cut-and-paste it into the end of stored procedure
code (assigning it to the variable that already contains the SELECT string),
then it works from BCP.

Any help would be greatly appreciated.

AstonAston (alau@.selera.com) writes:
> I have this stored procedure that takes a few parameters like date and
> merchant ID, and basically goes through a set of if-then statements to
> build a SQL SELECT string.
> When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
> worked from Query Analyzer, but not in BCP. It used to work in BCP just
> fine with 7.0. The error I get now is:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
> at least one column
> What's really strange is, if I instruct the stored procedure to simply
> print the SELECT string, then cut-and-paste it into the end of stored
> procedure code (assigning it to the variable that already contains the
> SELECT string), then it works from BCP.

If I understand this right you are doing something like:

bcp "exec some_db..some_sp" queryout datafile.bcp -c -T

To find out what columns there are in the query, bcp first submits the
query with SET FMTONLY ON. This command is causes SQL Server to not execute
the statements in the procedure, but return data about any result sets
it finds. However, if you produces a dynamic SQL string and executes it,
there not be anyting executed with FMTONLY ON, and BCP will not find any
result set.

Why this worked in SQL 7, I don't know. (I never worked much with SQL 7,
jumped direct to SQL 2000 from 6.5.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP call to import data from computer other than server is this possible?

Hi,

I have written an application which is being used by only 4 people on different computers. The one with SQL Server installed is comp0, then comp1, .... comp4.

Everything was working fine until today when the only user who is authorised (by my application) to perform the data import of BCP files, tried to import the latest BCP files. I am making my bcp command line string and then calling it using the VB Shell call.

If I run the import option from my app on the server it works fine. So at first I thought, ah, the BCP files won't be in the other computers path. I've copied BCP.EXE and BCP.rll but it still doesn't work.

Is it even possible to run BCP from a computer other than the one with SQL Server installed? I really need the import to work on a computer other than the server.

Thanks in advance,

Paul

Hi Paul,

What is the bcp command you are tyring to call exactly?

Thanks

Cris

|||

Hi Cris,

The calls is as follows:

strBCPCommand = "bcp database_name.schema." & _

table_name & _

" in " & _

full_path_of_bcp file & _

" -n -T -Sserver_name\sql -V65"

It all works fine when called from the application or command line on the server.

Thanks,

Paul

Batch file call to SQL Agent to make Bakcup

Hello.
I've a Backup system that copies my files and I want to copy also my
SQL Server DDBB. I intend to execute the batch file before de File
Backup starts to call de SQL Agent so it makes those Backups and then
copy them to the Backup folder so the Backup system copies everything.
The problem is that I don't know if it is possible to call to the SQL
Agent through a Batch file to make these Backups. If so, how could I
do it?
Thank you.
One suggestion ... instead of having the batch file calling the SQL Agent,
you can use the OSQL utility (it is a command line utility for SQL). You can
backup the database using OSQL.
Thank you
Lucas
"Gurk" <gurkgamer@.gmail.com> wrote in message
news:1181666228.803256.267530@.d30g2000prg.googlegr oups.com...
> Hello.
> I've a Backup system that copies my files and I want to copy also my
> SQL Server DDBB. I intend to execute the batch file before de File
> Backup starts to call de SQL Agent so it makes those Backups and then
> copy them to the Backup folder so the Backup system copies everything.
> The problem is that I don't know if it is possible to call to the SQL
> Agent through a Batch file to make these Backups. If so, how could I
> do it?
> Thank you.
>
|||If the backup script is in a job, you can execute that job
through OSQL, SQLCMD, etc by executing sp_start_job. You can
find more information on sp_start_job in books online
-Sue
On Tue, 12 Jun 2007 09:37:08 -0700, Gurk
<gurkgamer@.gmail.com> wrote:

>Hello.
>I've a Backup system that copies my files and I want to copy also my
>SQL Server DDBB. I intend to execute the batch file before de File
>Backup starts to call de SQL Agent so it makes those Backups and then
>copy them to the Backup folder so the Backup system copies everything.
>The problem is that I don't know if it is possible to call to the SQL
>Agent through a Batch file to make these Backups. If so, how could I
>do it?
>Thank you.

Batch file call to SQL Agent to make Bakcup

Hello.
I've a Backup system that copies my files and I want to copy also my
SQL Server DDBB. I intend to execute the batch file before de File
Backup starts to call de SQL Agent so it makes those Backups and then
copy them to the Backup folder so the Backup system copies everything.
The problem is that I don't know if it is possible to call to the SQL
Agent through a Batch file to make these Backups. If so, how could I
do it?
Thank you.One suggestion ... instead of having the batch file calling the SQL Agent,
you can use the OSQL utility (it is a command line utility for SQL). You can
backup the database using OSQL.
Thank you
Lucas
"Gurk" <gurkgamer@.gmail.com> wrote in message
news:1181666228.803256.267530@.d30g2000prg.googlegroups.com...
> Hello.
> I've a Backup system that copies my files and I want to copy also my
> SQL Server DDBB. I intend to execute the batch file before de File
> Backup starts to call de SQL Agent so it makes those Backups and then
> copy them to the Backup folder so the Backup system copies everything.
> The problem is that I don't know if it is possible to call to the SQL
> Agent through a Batch file to make these Backups. If so, how could I
> do it?
> Thank you.
>|||If the backup script is in a job, you can execute that job
through OSQL, SQLCMD, etc by executing sp_start_job. You can
find more information on sp_start_job in books online
-Sue
On Tue, 12 Jun 2007 09:37:08 -0700, Gurk
<gurkgamer@.gmail.com> wrote:
>Hello.
>I've a Backup system that copies my files and I want to copy also my
>SQL Server DDBB. I intend to execute the batch file before de File
>Backup starts to call de SQL Agent so it makes those Backups and then
>copy them to the Backup folder so the Backup system copies everything.
>The problem is that I don't know if it is possible to call to the SQL
>Agent through a Batch file to make these Backups. If so, how could I
>do it?
>Thank you.

Batch file call to SQL Agent to make Bakcup

Hello.
I've a Backup system that copies my files and I want to copy also my
SQL Server DDBB. I intend to execute the batch file before de File
Backup starts to call de SQL Agent so it makes those Backups and then
copy them to the Backup folder so the Backup system copies everything.
The problem is that I don't know if it is possible to call to the SQL
Agent through a Batch file to make these Backups. If so, how could I
do it?
Thank you.One suggestion ... instead of having the batch file calling the SQL Agent,
you can use the OSQL utility (it is a command line utility for SQL). You can
backup the database using OSQL.
Thank you
Lucas
"Gurk" <gurkgamer@.gmail.com> wrote in message
news:1181666228.803256.267530@.d30g2000prg.googlegroups.com...
> Hello.
> I've a Backup system that copies my files and I want to copy also my
> SQL Server DDBB. I intend to execute the batch file before de File
> Backup starts to call de SQL Agent so it makes those Backups and then
> copy them to the Backup folder so the Backup system copies everything.
> The problem is that I don't know if it is possible to call to the SQL
> Agent through a Batch file to make these Backups. If so, how could I
> do it?
> Thank you.
>|||If the backup script is in a job, you can execute that job
through OSQL, SQLCMD, etc by executing sp_start_job. You can
find more information on sp_start_job in books online
-Sue
On Tue, 12 Jun 2007 09:37:08 -0700, Gurk
<gurkgamer@.gmail.com> wrote:

>Hello.
>I've a Backup system that copies my files and I want to copy also my
>SQL Server DDBB. I intend to execute the batch file before de File
>Backup starts to call de SQL Agent so it makes those Backups and then
>copy them to the Backup folder so the Backup system copies everything.
>The problem is that I don't know if it is possible to call to the SQL
>Agent through a Batch file to make these Backups. If so, how could I
>do it?
>Thank you.