BCP problem on different servers

I=B4m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebasti=E1n1) can you bcp manually from a command prompt?
2) can you connect using sqlcmd and/or SSMS?
Likely issue is that appropriate network/connectivity settings are not there
for SQL2K5.
TheSQLGuru
President
Indicium Resources, Inc.
<spiccolotto@.gmail.com> wrote in message
news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
Im using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebastin|||Yes, I can bcp manually and connect using SSMS.
I will explain more my scenario:
Server A have SQL Server 2000 / Windows 2000.
Here, I have a VB6 DLL and CSV file. Inside this dll I execute BCP
command to send csv file data to Remote Database (On Server B).
BCP command executed from VB6 DLL doesn=B4t work. If I execute same BCP
command but now from command line (cmd) all works ok.
Server B have SQL Server 2005 / Windows 2003.
My questions are:
=BFAny incompatibility between BCP Versions (80 and 90)?
=BFWhy bcp works from command line (cmd)?
=BFI need to modify any permission?
Regards
Sebasti=E1n.
On Apr 18, 4:29 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) can youbcpmanually from a command prompt?
> 2) can you connect using sqlcmd and/or SSMS?
> Likely issue is that appropriate network/connectivity settings are not th=
ere
> for SQL2K5.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <spiccolo...@.gmail.com> wrote in message
> news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
> I=B4m usingBCPcommand inside VB6 DLL to import data from csv file to
> database.
> Normally it works fine, but when my database is installed in a server
> with Windows 2003 and SQL Server 2005 I had problems...bcpnot
> works... In this scenario, If I execute the samebcpcommand but now
> from command line all is ok... what is theproblem?
> Thanks
> Sebasti=E1nsql

BCP problem on different servers

I=B4m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebasti=E1n1) can you bcp manually from a command prompt?
2) can you connect using sqlcmd and/or SSMS?
Likely issue is that appropriate network/connectivity settings are not there
for SQL2K5.
--
TheSQLGuru
President
Indicium Resources, Inc.
<spiccolotto@.gmail.com> wrote in message
news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
I´m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebastián|||Yes, I can bcp manually and connect using SSMS.
I will explain more my scenario:
Server A have SQL Server 2000 / Windows 2000.
Here, I have a VB6 DLL and CSV file. Inside this dll I execute BCP
command to send csv file data to Remote Database (On Server B).
BCP command executed from VB6 DLL doesn=B4t work. If I execute same BCP
command but now from command line (cmd) all works ok.
Server B have SQL Server 2005 / Windows 2003.
My questions are:
=BFAny incompatibility between BCP Versions (80 and 90)?
=BFWhy bcp works from command line (cmd)?
=BFI need to modify any permission?
Regards
Sebasti=E1n.
On Apr 18, 4:29 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) can youbcpmanually from a command prompt?
> 2) can you connect using sqlcmd and/or SSMS?
> Likely issue is that appropriate network/connectivity settings are not th=ere
> for SQL2K5.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <spiccolo...@.gmail.com> wrote in message
> news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
> I=B4m usingBCPcommand inside VB6 DLL to import data from csv file to
> database.
> Normally it works fine, but when my database is installed in a server
> with Windows 2003 and SQL Server 2005 I had problems...bcpnot
> works... In this scenario, If I execute the samebcpcommand but now
> from command line all is ok... what is theproblem?
> Thanks
> Sebasti=E1n

BCP on client machine

Hi,

What is the best practice to use BCP.exe to load text file from client machine to the SQLSERVER database installed in another server machine, without installing SQLSERVER client tools on the client machine.

Any suggestions is appreciated.

Thanks in advance.

-Anand

You can use BCP on the client machine, but you will have to be aware to have a licencse for that. If you do not want to use BPC per se (becasue of this reason) you will have to implement it on your own, or by another third party data pump.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thanks for your reply.

Can anybody help me out in downloading the third party data dump tool. I heard about freetds, thats only for linux.

Any help is appreciated.

-Anand|||

My understanding is that BCP is in the class of 'Client Tools' that are available to install on any client computer that is licensed with a CAL, or to access a SQL Server that is licensed with a Processor license.

For definitive answers about licensing, contact the licensing forks at:

Licensing –Microsoft, Contact
(800) 426-9400

From: http://www.microsoft.com/sql/howtobuy/faq.mspx

Do I need a separate license to run the SQL Server tools and analysis services? A.

No, a separate license is not required. However, any device that has SQL Server tools or technologies installed must have a valid SQL Server license.

Note: Most of the 'Client Tools' are NOT included with SQL Server 2005 Express Edition. There is, however, a special version of SQL Server Management Studio Express available.

BCP install

Hi,

I need to have on a client machine the bcp.exe installed. What components do I need to install from SQL Server 2005. I would like to have the absolute minimal installed on this machine.

Thanks.

Solved the problem:

download the SQLEXPR_TOOLKIT.EXE and installed only the connections component.

BCP in SQL SERVER 2005

Hello folks!
I have installed SQL Server 2005 Express and SQL Server Management Studio Express. Everything looks ok and works fine, but I can't find the Data Import/Export Wizard. Can anyone tell me how to Import/Export data?

Thank you!!!

SQL Server 2005 Express Manager does not offer Import/Export functionality as it is dependent on SQL Server Integration Services (SSIS) and SQL Server Agent. You will need to optain the full version of SQL Server Management Studio.

Peter

|||You can you other means to move data in and out of SQL Express that don't rely on SSIS. BCP is one such tool, you can find more information about BCP at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

If you are working with data in Access, you can use the functionality of Access to directly export the data into SQL Server. Most other programs can generate data in a format that BCP can accept.

Mike|||

I have to say, I have been developing for many years. This time I am truely disappointed in MS. I avoid say this kind of thing, and come to MS's defense almost always. Never bit into the hand that feeds you (karma-ish).

How am I supposed to learn this great technology between jobs, or when I work for a cheap start up company.

We (your users) can not easily import data into this new version of SQL Server. If you are going to put it out there as education; at least make it useful.

I just don't get it. I appologize for sounding disenchanted; but I have become accustom to being able to depend on MS products atleast being usable. For completeness sake maybe I am missing something. And do not say bcp, command lines went out years ago, that iss what Microsoft is founded on.

Enlighten me please; I fear I have just taken the first step to becoming one of them, the na sayers. Prove me wrong. After all that expression of opinion, I hope you find it heartfelt and consructive.

I look forward to your response. Please tell me there is a quick windows orienent approach, not some prehistoric utility.

MKruz

|||

Michael,

Agree with you - what a big disappointment. I'm sure that there are many of us who have data that we would like to bring into a SQL Server Express table and work with - it's natural form being a text or Excel base that comes from a bank or some other source. All the functionality in the world is of little use if the basic data import is not there.

Bill Sullivan

|||I totally agree, SQL 2005 Express is useless if you can't import/export data from a GUI. I'll move back to SQL2000 and DTS until it is fixed.|||

I really want to find out what the missing feature is, there was no GUI for importing excel files in MSDE and there is not one in Express, which is the equivalent SKU.

You can use the eval version for free to eval features, or if it is just for development then the dev edition is < 50 USD.

|||

Yeah, after all the hoopla surrounding the Visual Studio release man I'm bummed out about this. Guess there always has to be a catch. I was excited, I had a program idea mulling in my head for a few years and with Visual Studio I saw a chance to bring it to life.

But for my idea to come to life I need access to Access databases! (Isn't that why it's called "Access"?..) Oh well, as this n00by knows, if there is a will there is a way! Hope I don't bump into my much of this 'fine print' on my travels...

Luke

|||I disagree that command lines went out years ago. They are still the most expedient way to accomplish a whole host of tasks, and I use them daily.

That said, my 30-second review of the bcp link does suggest that importing data into a table for SQL Express may be more complicated than just getting it into CSV format. I'll reserve judgment until I've actually given it a try.
|||There is a company that makes a whole bunch of db converters. I just used one of them to convert from Access to MS SQL Express:
http://www.convert-in.com/acc2mss.htm

They have a demo that will convert the first five records from each of your tables if you'd like to try it out before you buy.
|||

If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

Before you start exporting make sure you have a data base in SQL where you want to export to.

Open your data base in access

Right click on the table you want to export

Select export

A dialog box opens...

at the bottom of this dialog box there is a "Save as Type" drop down box

select it and scroll to the bottom you will find "ODBC Databases () "

select it

Give your table a new name, or leave as is, if it that suits you.... Click OK

A Select Data Source dialog box opens..... Click the "New" button

A create new data source dialog opens

Scroll to the bottom of that box and select SQL Server

click NEXT

give your data source a name...... what ever you like.

click NEXT.... click FINISH

a 'Create New Data Source to SQL Sever' dialog box opens

Give your connection a description

select or type the name of your server in the Server drop down box

Click NEXT...... Click NEXT......

check the 'change the default data base to' check box

select the data base where you want the export your table to.

Click NEXT.... click Finish.

That's all. Now you can select each table from you access data base then select

Export.......

ODBC databases ()

now you can select the data source you created then click OK

return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

Hope this is helpful

|||

Hello Everyone,

Just after reading this form, I tried out some import/ export operation like below

SQL 2000 - SQL 2005 Express (Import/Export)

Excel - SQL 2005 Express (Import/Export)

It worked out very fine. I just used the DTS in SQL 2000 to do this. Any comments on this.

Thanks

Swami

|||Hello,
Try:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
Worked for me!
|||

Wolfey305 wrote:

If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

Before you start exporting make sure you have a data base in SQL where you want to export to.

Open your data base in access

Right click on the table you want to export

Select export

A dialog box opens...

at the bottom of this dialog box there is a "Save as Type" drop down box

select it and scroll to the bottom you will find "ODBC Databases () "

select it

Give your table a new name, or leave as is, if it that suits you.... Click OK

A Select Data Source dialog box opens..... Click the "New" button

A create new data source dialog opens

Scroll to the bottom of that box and select SQL Server

click NEXT

give your data source a name...... what ever you like.

click NEXT.... click FINISH

a 'Create New Data Source to SQL Sever' dialog box opens

Give your connection a description

select or type the name of your server in the Server drop down box

Click NEXT...... Click NEXT......

check the 'change the default data base to' check box

select the data base where you want the export your table to.

Click NEXT.... click Finish.

That's all. Now you can select each table from you access data base then select

Export.......

ODBC databases ()

now you can select the data source you created then click OK

return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

Hope this is helpful

If COnnection Failed..what I want to do?

|||Hi swami,

Could you pls give me more info as to how to run DTS.

I have installed Sql server 2005 Ex Ed.
Is it possible to install only the DTS from SQL 2000.

Tnx

bcp Execution

Hai..

I have two systems. SQL server 2000 is installed in one system (SERVER ) and the client tools are installed in a seperate system (Client)
My question is from which system should i have to execute the bcp command line utility, from the server or from the client. ?

Thanks in advance...

with regards
Sudari would have thought it would be best to run it on the server myself, but it really depends what you are trying to do.|||Hai..

thnks for your reply...

Actually i want to know whether it is possible to execute bcp from client or not and if yes then how.. ?

bcoz there is no argument in bcp to identify the server name..

thnks in advance

With regards
Sudar|||Did you try -s ?|||Thnks kaiowas it's working correctly.....

bcp errors with queryout

I am having problems running bcp with queryout. I believe some of this stems from the fact that I also have the Sybase tools installed on the database servers and it in turn added its binn directory for the Sybase bcp.exe to the environment variables. Seeing this then, I would like to resolve this issue with a solution that does not rely on the order of environment variables.

Here is what is happening:

1) If I just try to run bcp on my server I get...

exec master.dbo.xp_cmdshell 'bcp ?'
/*
output -------------------
'bcp' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/

2) ...so, I add the full path to the location of the bcp program and this command works:

exec master.dbo.xp_cmdshell '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" ?'
/*
output --------------------------usage: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
*/

3) I then attempt a bcp out of a Northwin table... and this works as well

DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" Northwind.dbo.Categories out C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output
------
NULL
Starting copy...
NULL
8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 31
NULL
*/

4) I then attempt a queryout and it fails, indicating that it no longer understand the long filename

DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" queryout C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output --------------------
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/been doing some more testing on my own local SQL Server. It seems that even if the environment variables are all fine, if I fully qualify the bcp exe and include a query (with double-quotes) it fails. So, what I am wondering is, can one not fully qualify the bcp.exe and do a query at the same time?:

--the below works
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = 'bcp "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT

--...while this does NOT work!
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output ---
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/|||try to

SELECT @.cmdline = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT|||sorry, really not works

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

basic Server setup and user accounts

I have just installed SQL server 2005 standard edition - how do I create a
basic server and user accounts in order to then install Project Server?
Hi
Project Server is not yet officially certified to run with SQL 2005.
Postings in the project server news groups discuss how you can work around
this to get it working, but you should not use this on a production system
http://www.microsoft.com/office/comm...=&sloc=en -us
http://blogs.lv0.net/mpatest/archive...11/18/182.aspx
John
"Steve Scott" wrote:

> I have just installed SQL server 2005 standard edition - how do I create a
> basic server and user accounts in order to then install Project Server?

basic Server setup and user accounts

I have just installed SQL server 2005 standard edition - how do I create a
basic server and user accounts in order to then install Project Server?Hi
Project Server is not yet officially certified to run with SQL 2005.
Postings in the project server news groups discuss how you can work around
this to get it working, but you should not use this on a production system
http://www.microsoft.com/office/com...exp=&sloc=en-us
http://blogs.lv0.net/mpatest/archiv.../11/18/182.aspx
John
"Steve Scott" wrote:

> I have just installed SQL server 2005 standard edition - how do I create a
> basic server and user accounts in order to then install Project Server?

basic Server setup and user accounts

I have just installed SQL server 2005 standard edition - how do I create a
basic server and user accounts in order to then install Project Server?Hi
Project Server is not yet officially certified to run with SQL 2005.
Postings in the project server news groups discuss how you can work around
this to get it working, but you should not use this on a production system
http://www.microsoft.com/office/community/en-us/default.mspx?query=SQL+2005&dg=microsoft.public.project.server&cat=en-us-microsoftproject&lang=en&cr=US&pt=a1d023a3-f612-4da2-acb8-fda8f850d645&catlist=&dglist=&ptlist=&exp=&sloc=en-us
http://blogs.lv0.net/mpatest/archive/2005/11/18/182.aspx
John
"Steve Scott" wrote:
> I have just installed SQL server 2005 standard edition - how do I create a
> basic server and user accounts in order to then install Project Server?

Basic security setup

Hi there
Our company has just started using RS and I would like to
know how to set up basic security.
Our setup:
We have RS installed on one of our SQL servers. Our
Analysis department creates reports on their PC's (with
Reporing Services & Visual Studio.net installed on their
local PC). They will then deploy the reports to our RS
server and the Marketing department will access reports
through the RS web interface.
What I am struggling with:
I cannot figure out how to let users see reports without
giving them adminitrator rights on the RS server. I have
tried giving them access to the reportserver database -
this gives them access to the reportserver webpage, but
they cannot see any reports (I gave them access to
specific reports in RS manager).
I'm sure it's something stupid that I'm not doing, but
cannot figure out what it is. Could someone please help
me.
Thanks,
TheresaIf you are a 'Content Manager' on the report server (by default all local
admins are content managers) you can set permission on RS objects. Go to
the object in question, on the property tab there is a security sub tab
which will allow you to give users access rights. You most likely want to
use the Browser role but you will have to investigate to see what fits your
needs.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa dP" <theresa@.discussions.microsoft.com> wrote in message
news:43b401c49035$39843a90$a301280a@.phx.gbl...
> Hi there
> Our company has just started using RS and I would like to
> know how to set up basic security.
> Our setup:
> We have RS installed on one of our SQL servers. Our
> Analysis department creates reports on their PC's (with
> Reporing Services & Visual Studio.net installed on their
> local PC). They will then deploy the reports to our RS
> server and the Marketing department will access reports
> through the RS web interface.
> What I am struggling with:
> I cannot figure out how to let users see reports without
> giving them adminitrator rights on the RS server. I have
> tried giving them access to the reportserver database -
> this gives them access to the reportserver webpage, but
> they cannot see any reports (I gave them access to
> specific reports in RS manager).
> I'm sure it's something stupid that I'm not doing, but
> cannot figure out what it is. Could someone please help
> me.
> Thanks,
> Theresa