bcp, queryout and pipe delimiter

When I execute the following string within a stored procedure:
(The string has been modified to mask company info)

bcp "Exec dbname.dbo.GetSalesCodesContracts" queryout d:\ftp\sfa\SalescodeContract-1.txt -m1 -c -t| -Uxxxxx -Pxxxxx -Sxxxxx

I get the following error: The name specified is not recognized as an
internal or external command, operable program or batch file.

But if I change -t| to -t, (comma) it works ok and produces a comma delimited file.

The pipe character seems to be the problem. The help file on delimiters says you can use the pipe.

This is running under SQL Server 7.0.

Any help would be appreciated.

RichardAre you using bulk insert or the bcp command line utility?

'|' is used for command line re-direction, most commonly to more, find and sort. It can also be used to re-direct the output of one application to another application. I suspect the command interpriter sees the '|' and tries to re-direct the result of your bcp command to the '-UXXXXX' application which as you have found out does not exist.

Hope this helped.|||Try using -t'|'|||Try using -t'|'

This did not work for me. Instead, I finally discovered, -t^| did the trick. (The DOS shell interpreter uses the ^ character to escape the | and interpret the whole thing as a literal to be passed to bcp, rather than as part of the DOS commandline itself.)|||I would do this with t-sql and xp_cmdshellsql

Bcp utility with stored procedure

Guys,

I have stored proc sp_generate_insert which will generate insert scripts for the tables. When I run the stored Proc
from the management studio it runs fine. But when I run through stored proc as part of BCP utility I get this error.

'SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.'

Execute dev.dbo.sp_generate_inserts 'auth' runs fine from management studio and generates inserts for auth table.

When I run the same proc as part of the following stored proc with bcp utility I get the error.

alter PROCEDURE INSERTTEST2 ( @.FILEPATH NVARCHAR(50))
AS
DECLARE @.cmd varchar(2000)
BEGIN
set @.cmd = 'bcp.exe "EXEC dev.dbo.SP_GENERATE_INSERTS auth" '
+ 'QUERYOUT' + ' ' +@.filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3' + ' -q ' + ' -c -T -e' + @.filePath+'.log -o '
+ @.filePath+ '_out.log'
select @.cmd -- + '...'
EXEC master.dbo.xp_cmdShell @.cmd
END

Any suggestions or inputs would help.

Thanks

The problem lies within the proc, so we need to see that code.

Though usually, this error comes from statements where the length parameter in SUBSTRING becomes negative.

If you're dynamically trying to set how large chunk substring should take, and that variable becomes negative, then this error happens.

Since the problem seems to occur or not depending on method of connecting, it may suggest that there are different settings that may be the root cause.. (ie ANSI DEFAULTS etc)

Could this be it perhaps?

/Kenneth

|||kenneth,

Thank you for you reply.

I dont know if the problem is setting defaults on the database or the connection, more so since the stored proc - sp_generate_scripts runs fine from the managment studio.

Anyways the code for stored proc is available at the following link

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Any suggestions/inputs would help

Thanks

|||

I played around a bit with the proc and found some 'interesting' stuff...

I think your problem may be that you don't use the -d parameter in your bcp command, so you're not ending up in the right db.

The reason this matters may be the same that I found, but didn't notice at first...

(I tried it on SQL Server 2000).
First when compiled, there was a msg about not finding sys.sp_MS_marksystemobject, but the proc compiled anyway, so I tried it out.

Got the same message as you a couple of times, but found that only if I was in a db other than master. Made a usertable in master, then it worked. =/

So, fixed the 'sys.sp_MS_marksystemobject' to 'sp_MS_marksystemobject' and recompiled (since the former doesn't exist in 2000, only in 2005) and tried again. Now all is smooth, and it works like it's supposed to.

Apparently, the proc needs to be marked as a systemobject, else you may get these 'db-scope' issues, so check out if this is the problem.

/Kenneth

BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP to export an SP which uses Temporary Tables not working - SQL Server 2005

Hi

I am trying to export the data from a stored procedure via bcp export. The SP uses temporary tables although the actual data in the temp tables is not the data being exported they are just used to help get the final data.

When running the BCP Export I get an error message that the Object does not exist however if I change the SP to use real tables as opposed to temporary then it runs fine.

I have read that there is no problem exporting from temporary tables with BCP but I am not exporting the data in the temporary tables is this what is causing the problem, it seems a but strange that you can only use them if the data contained is what is being exported.

Can anyone explain?

Thanks

Paul

Maybe you can post some code.

I'm somewhat confused. You say it's throwing an error about the temporary tables, but you say you're exporting from the temporary tables.

|||

Paul:

If the temp tables are created inside the stored procedures then what you are doing is not going to be possible because the temp tables will go out of scope when you exit the stored procedure. This is also logically consistent with the fact that if you use permanent tables instead of temporary tables that the BCP works.

It seems to me that the most likely scenario for you to get your stored procedure operate on a temp table and then have BCP also operate on the same temp table is if (1) your temp table is a global temp table -- that is, it starts with ## instead of # -- that is created before the the stored procedure is call by the connection that also calls the stored procedure and (2) the connection is maintained and the global temp table is not dropped. Under these circumstances you should be able to use BCP on the global temp table.

It would be simpler if you could convert your stored procedure to a function or a view.

|||

Hi Kent

Thanks for the reply I have tried using global temp tables and i get the same error but with ## before the object name so it would appear that it is not going to work with temp tables at all. This may not be a problem as I can always create them then drop them so in effect they are temporary.

Problem with converting to anything else is the whole routine is someone elses that they have been working on for many months I was just trying to help with the BCP aspect, it is a large amount of code and apart from the temp tables it does work we were just trying to understand why it wouldnt work so it can be documented or if possible we could have fixed it.

I can see the problem with the local temp tables thanks to your help but dont see why the global ones wouldnt work I even tried without dropping them at the end of the SP. Oh wait a moment are you saying that the global temp tables would need to be created before the SP is executed and therefore my order of events would be

1. Create Global Temp Tables

2. Execute SP in the BCP export Command

3. Once all is done and happy with the results send in another SQL statement to Drop the Global Tables

If so that should be a good enough reason for us to create real tables and drop them instead.

Thanks for your help

Paul

bcp syntax error help?

I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
ThanksI have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
Thanks

I have realized how to go around this thanks to all

BCP Syntax

I am trying to use the BCP utility to copy a stored procedure out to a text
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith

bcp right truncation: how to ignore

Hi,
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.
I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?
I couldn't find such an option in the documentation.
Any help is greatly appreciated.
Thanks,
Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?
Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.
One possibility would be to write a program that reads the file, and
truncates the over-long rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.
Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).
Kind regards
robert

bcp right truncation: how to ignore

Hi,

I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?

I couldn't find such an option in the documentation.

Any help is greatly appreciated.

Thanks,

Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?

Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
>> I'm trying to upload a large number of log entries currently stored as
>> text files into a database table using bcp. For a few rows I get a
>> "right truncation" error and the offending rows are not uploaded to the
>> table.
>>
>> I don't want to increase the size of the table varchar fields because
>> it's only about a dozen out of almost million rows that have this
>> problem ... I want to provide an override - i.e. if a row will result
>> in truncated data, truncate but still bulk copy the offending row. Is
>> that possible?
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.

Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).

Kind regards

robert

BCP QUERYOUT from a procedure?

Anyone ever BCP QUERYOUT from a stored procedure?

Something like this? Is this even possible?

BCP "exec usp_MyProc 1, N'SomeString'" QUERYOUT c:\Output.txt -SMyServer -T -N

Did you try it before posting the question? It worked for me.

|||Sure. We do this all the times.|||

Yep. I did. No luck.

It works if I do this:

Code Snippet

bcp "declare @.str nvarchar(255); set @.str = N'SomeVar'; exec usp_MyProc 1, @.str" ...

something odd there...hmm...

|||

Works for me like this.

bcp "exec db4.dbo.get_employee 1" queryout c:\emp.txt -SSankar-P -T -c

sql

BCP Order on SQL Server

I have two SQL Server 2000 machines. The same file is sent nightly to
each server and a stored proc uses BULK INSERT to load it into a
staging table for processing.

Once I've bcp'ed it in, I put it into a temp table with an IDENTITY
column appended to it. (I need this identity column to group by later
on to remove duplicates.)

ie

select tempo.*,
IDENTITY(int, 1,1) AS ID_Num
into #test1
from tempExtract tempo

My question is : can I expect the ID_Num and the corresponding line of
the file copied to the table to be the same on each server? Ie will
each BCP into the staging table occur in the same order on both
servers given that the file, the BULK INSERT command and the indexes
are the same on each server.Thomas Richards (tom.richards@.rocketmail.com) writes:
> I have two SQL Server 2000 machines. The same file is sent nightly to
> each server and a stored proc uses BULK INSERT to load it into a
> staging table for processing.
> Once I've bcp'ed it in, I put it into a temp table with an IDENTITY
> column appended to it. (I need this identity column to group by later
> on to remove duplicates.)
> ie
> select tempo.*,
> IDENTITY(int, 1,1) AS ID_Num
> into #test1
> from tempExtract tempo
> My question is : can I expect the ID_Num and the corresponding line of
> the file copied to the table to be the same on each server? Ie will
> each BCP into the staging table occur in the same order on both
> servers given that the file, the BULK INSERT command and the indexes
> are the same on each server.

No, you would need to have the identity column on the table you load
the file into. I don't know for sure that you can trust the IDENTITY
value to match the input file exactly, and if it works, it is likely
to by mere chance. That is, there is no committment from Microsoft
that it should work, and it could change in a future version of SQL
Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9556E943F45FAYazorman@.127.0.0.1>...
> Thomas Richards (tom.richards@.rocketmail.com) writes:
> > I have two SQL Server 2000 machines. The same file is sent nightly to
> > each server and a stored proc uses BULK INSERT to load it into a
> > staging table for processing.
> > Once I've bcp'ed it in, I put it into a temp table with an IDENTITY
> > column appended to it. (I need this identity column to group by later
> > on to remove duplicates.)
> > ie
> > select tempo.*,
> > IDENTITY(int, 1,1) AS ID_Num
> > into #test1
> > from tempExtract tempo
> > My question is : can I expect the ID_Num and the corresponding line of
> > the file copied to the table to be the same on each server? Ie will
> > each BCP into the staging table occur in the same order on both
> > servers given that the file, the BULK INSERT command and the indexes
> > are the same on each server.
> No, you would need to have the identity column on the table you load
> the file into. I don't know for sure that you can trust the IDENTITY
> value to match the input file exactly, and if it works, it is likely
> to by mere chance. That is, there is no committment from Microsoft
> that it should work, and it could change in a future version of SQL
> Server.

Thanks for that. The problem that I'm trying to get round is that I
have a key field and then one or more addresses. The key field and the
fields that make up the address are all chars/varchars. I want to pick
one arbitrary address to associate with the key and put in another
table. There are no business rules (eg always take the one with the
lowest street number) that will always identify just one of the
addresses. Originally, I thought group by key and line number and pick
the one with the lowest number. I would prefer to do this as it would
get the first entry in the file which would more than likely give me
the better address details. However as you've pointed out I can't
depend on the order when bcp'ed in. Is there any other way to do this
or would I have to get line number added to the file before SQL Server
processes it?|||Thomas Richards (tom.richards@.rocketmail.com) writes:
> Thanks for that. The problem that I'm trying to get round is that I
> have a key field and then one or more addresses. The key field and the
> fields that make up the address are all chars/varchars. I want to pick
> one arbitrary address to associate with the key and put in another
> table. There are no business rules (eg always take the one with the
> lowest street number) that will always identify just one of the
> addresses. Originally, I thought group by key and line number and pick
> the one with the lowest number. I would prefer to do this as it would
> get the first entry in the file which would more than likely give me
> the better address details. However as you've pointed out I can't
> depend on the order when bcp'ed in. Is there any other way to do this
> or would I have to get line number added to the file before SQL Server
> processes it?

The only way to be sure is to add the line numbers yourself. This can be
done in two ways: 1) Manipulate the file, by running it through a program
that adds a line number. 2) Instead of writing a to file, have the program
to insert the data. In fact, you can still use bulk load, but you would
bulk from variables, using the BCP API.

However, BULK INSERT into a table with an IDENTITY gives you fairly good
odds, and as I understand your case, it does not seem to be a disaster,
if number is not what you expect. So I would go for that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

I'm going to have problems adding the line numbers to the file unless
there's an easy way to do it that uses windows built-in functionality.
Unfortunately the file comes from a mainframe extract so the I can't
change the program either.

Just to be clear about what I'm doing, I bulk insert into a table that
has a KEY field and then one or more addresses eg:

KEY, ADDRESS1, ADDRESS2
----------
FRED, HOG STREET, HOGLAND
FRED, HOG STREET, HOGLANDIO

I need to take the key (ie FRED) and one address (doesn't matter
which) and put it into another table. However, I have to get the same
address on each server. That's more important than trying to get the
first one in the file.

I'm going to try out the bulk insert with identity. If I put a
clustered key on the table that is bulk inserted to on all columns, I
would assume that would force the order in the table to be the same on
both servers - what do you think?

Cheers
Tom

> The only way to be sure is to add the line numbers yourself. This can be
> done in two ways: 1) Manipulate the file, by running it through a program
> that adds a line number. 2) Instead of writing a to file, have the program
> to insert the data. In fact, you can still use bulk load, but you would
> bulk from variables, using the BCP API.
> However, BULK INSERT into a table with an IDENTITY gives you fairly good
> odds, and as I understand your case, it does not seem to be a disaster,
> if number is not what you expect. So I would go for that.|||My latest thinking on this is to create a table the same as the table
holding the 'key' and address components but with an extra identity
type field.

Then insert into this table ordering by key + all columns. This will
force the sequence number to match the same row on each server and the
'key' fields to be sequentially next to each other. Then I can do a
group by, picking up the lowest sequence number.

eg

SELECT KEY,
ADDRESS1,
ADDRESS2,
IDENTITY(int,1,1) as Seq
INTO tempTable
FROM tempExtract
WHERE 1=2

INSERT INTO tempTable
SELECT KEY,
ADDRESS1,
ADDRESS2
FROM tempExtract
ORDER BY KEY,
ADDRESS1,
ADDRESS2

-- Finally get a key with just one address
SELECT KEY,
ADDRESS1,
ADDRESS2
FROM tempTable
WHERE SEQ = (SELECT MIN (Seq)
FROM tempTable sub
WHERE sub.KEY = tempTable.KEY)

Can you see any holes in that?!

Cheers
Tom|||Thomas Richards (tom.richards@.rocketmail.com) writes:
> I'm going to have problems adding the line numbers to the file unless
> there's an easy way to do it that uses windows built-in functionality.

Adding such line numbers is a very simple program that can be written
VBscript, Perl, C or whatever your preference is. The one catch is that
this is not very effcient if the file is huge.

> I'm going to try out the bulk insert with identity. If I put a
> clustered key on the table that is bulk inserted to on all columns, I
> would assume that would force the order in the table to be the same on
> both servers - what do you think?

What matters is the order that the rows hit the tables. My guess is that
a completely indexless table is better.

> INSERT INTO tempTable
> SELECT KEY,
> ADDRESS1,
> ADDRESS2
> FROM tempExtract
> ORDER BY KEY,
> ADDRESS1,
> ADDRESS2
>...
> Can you see any holes in that?!

The sad case is that neither is there any guarantee with an INSERT
statement that the IDENITTY value will reflect the ORDER BY statement.
But if you add OPTION (MAXDOP 1) to the query it usually works. MAXDOP 1
turns off parallelism which is the major reason the ORDER BY gets messed up.

But maybe you should rethink completely. Maybe you should bulk into one
server, remove the duplicates, and the replicate the result to the
second server. This could be done by a linked query, or bulking out and
in again.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||My latest thinking on this is to create a table the same as the table
holding the 'key' and address components but with an extra identity
type field.

Then insert into this table ordering by key + all columns. This will
force the sequence number to match the same row on each server and the
'key' fields to be sequentially next to each other. Then I can do a
group by, picking up the lowest sequence number.

eg

SELECT KEY,
ADDRESS1,
ADDRESS2,
IDENTITY(int,1,1) as Seq
INTO tempTable
FROM tempExtract
WHERE 1=2

INSERT INTO tempTable
SELECT KEY,
ADDRESS1,
ADDRESS2
FROM tempExtract
ORDER BY KEY,
ADDRESS1,
ADDRESS2

-- Finally get a key with just one address
SELECT KEY,
ADDRESS1,
ADDRESS2
FROM tempTable
WHERE SEQ = (SELECT MIN (Seq)
FROM tempTable sub
WHERE sub.KEY = tempTable.KEY)

Can you see any holes in that?!

Cheers
Tom|||Thomas Richards (tom.richards@.rocketmail.com) writes:
> My latest thinking on this is to create a table the same as the table
> holding the 'key' and address components but with an extra identity
> type field.
> Then insert into this table ordering by key + all columns. This will
> force the sequence number to match the same row on each server and the
> 'key' fields to be sequentially next to each other. Then I can do a
> group by, picking up the lowest sequence number.
>...

That seems to the same suggestion, to which I answered once, so I
simply repear that answer:

The sad case is that neither is there any guarantee with an INSERT
statement that the IDENITTY value will reflect the ORDER BY statement.
But if you add OPTION (MAXDOP 1) to the query it usually works. MAXDOP 1
turns off parallelism which is the major reason the ORDER BY gets messed up.

But maybe you should rethink completely. Maybe you should bulk into one
server, remove the duplicates, and the replicate the result to the
second server. This could be done by a linked query, or bulking out and
in again.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sorry, what I meant to post was:

I'm thinking of doing the following to ensure I get the same row on
each server (assuming identical collations).
It is supposed to only bring back the row that matches the TOP value
of all the fields concatenated. I've tested it on identical servers
and it appears to work and the theory seems fine to me. Can you see
anything wrong with this??

Thanks
Tom

SELECT KEY,
ADDRESS1,
ADDRESS2
FROM TABLE main
WHERE ISNULL(KEY,'Z') +
ISNULL(ADDRESS1, 'Z') +
ISNULL(ADDRESS2, 'Z') =
(SELECT TOP 1 ISNULL(KEY, 'Z') +
ISNULL(ADDRESS1, 'Z') +
ISNULL(ADDRESS2, 'Z') =
FROM TABLE sub
WHERE sub.KEY = main.KEY
ORDER BY ISNULL(KEY,'Z') +
ISNULL(ADDRESS1, 'Z') +
ISNULL(ADDRESS2, 'Z'))|||Thomas Richards (tom.richards@.rocketmail.com) writes:
> I'm thinking of doing the following to ensure I get the same row on
> each server (assuming identical collations).
> It is supposed to only bring back the row that matches the TOP value
> of all the fields concatenated. I've tested it on identical servers
> and it appears to work and the theory seems fine to me. Can you see
> anything wrong with this??
> Thanks
> Tom
> SELECT KEY,
> ADDRESS1,
> ADDRESS2
> FROM TABLE main
> WHERE ISNULL(KEY,'Z') +
> ISNULL(ADDRESS1, 'Z') +
> ISNULL(ADDRESS2, 'Z') =
> (SELECT TOP 1 ISNULL(KEY, 'Z') +
> ISNULL(ADDRESS1, 'Z') +
> ISNULL(ADDRESS2, 'Z') =
> FROM TABLE sub
> WHERE sub.KEY = main.KEY
> ORDER BY ISNULL(KEY,'Z') +
> ISNULL(ADDRESS1, 'Z') +
> ISNULL(ADDRESS2, 'Z'))

You could get duplicates if you have some really weird data which
gives the same result for two concatenations, but I guess that is
a calculated risk. And you would get the same duplicates on both
servers.

Ah, there is one more catch - you must make sure that both databases
have the same collation. But since you can specify the collation per
column when you create the table, you can take care of that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

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

I have a stored procedure, which loops through a database and runs a BCP
statement (with changing criteria), as follows:
exec master..xp_cmdshell 'bcp "SELECT myfields FROM Database..Viewname WHERE
criteria ORDER BY criteria" queryout "C:\File.txt" -S ServerName -T -c'
When I run this, I get the following error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
'MY DATABASE NAME' in sysservers. Execute sp_addlinkedserver to add the
server to sysservers.
I'm telling it which server to use, but it's reading the database name as a
server, for some reason. The server, however DOES show in sysservers. I've
also tried running this with -U sa -P ... with the same results. What would
be causing this? Thanks for advance for any help you can offer.
BariYou have to register that Server on the server you are running the query on.
You register the Server by executing
sp_addlinkedserver YOURSERVERNAME
Otherwise it won't be in systables.
Pain if you wanna distribute this code.

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 export stored procedure with a date in the statement

Hi

Please can someone help me with the statement below. I am trying to export, via bcp a stored procedure which requires two dates and cannot seem to work out the correct way of typing it into the statement. I know that the dates are meant to have an ' around them but cant work out how to get this concatenated correctly.

Any help would be appreciated.

Paul

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP 05/01/07, 01/01/07" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'
exec master..xp_cmdshell @.sq

use the following query...

Code Snippet

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

|||Thanks very much for your help

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 Entire DB in MYSQL 2000

I have a stored proc that currently BCP 27 tables into a MS SQL 2000 DB. The original script was created to import FIXED-WIDTH. I have made the changes to TAB-DELIMITED. But the source db that outputs the TAB files is enclosing every field within double quotes("). I am getting a 'Invalid character value for cast specification' error on the first row which is an integer. Is there a parameter that I can set for bcp to strip out the "...".

Thanks,
JNunezNYCWhy don't you use DTS where you can specify the required settings for data.

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 and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_revlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column
I would appreciate any incite on the solution to this problem as well as the cause.
Thank you in advance.
Robert
If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>
|||If all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at
least one column
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>

BCP and Stored Procedure

I am trying to use BCP to generate a textfile with the results of sp_help_re
vlogins.
xp_cmdshell 'bcp "execute sp_help_revlogin" queryout c:\test\test.txt -c -S"
testserver" -U"sa" -P"test"'
The error I receive is the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must conta
in at least one column
I would appreciate any incite on the solution to this problem as well as the
cause.
Thank you in advance.
RobertIf all you want is the output of sp_help_revlogin to be placed in a file try
using OSQL. Something like this:
OSQL -S<yourserver> -U<login> -P<password> -Q"exec
sp_help_revlogin" -oc:\test\test.txt
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Robert M." <Robert M.@.discussions.microsoft.com> wrote in message
news:0C360F11-4D5A-4BC4-976E-35650510E775@.microsoft.com...
> I am trying to use BCP to generate a textfile with the results of
sp_help_revlogins.
> xp_cmdshell 'bcp "execute sp_help_revlogin" queryout
c:\test\test.txt -c -S"testserver" -U"sa" -P"test"'
> The error I receive is the following:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at[
/vbcol]
least one column[vbcol=seagreen]
> I would appreciate any incite on the solution to this problem as well as
the cause.
> Thank you in advance.
> Robert
>
>