BCP OUT with Double Quote(") text qualifier?

How do I tell BCP that I want it to output with Double Quotes around my char fields?

It's easy to do in DTS by setting the connection properties of the Text File destination. The reason I can't use DTS is because my table has 329 fields and when I try to modify the destination properties of the transform task, Enterprise Manager bombs. I'd rather use BCP and a format file anyway.

Thanks,

Ray Metz
Everett, WAI just tried using a format file to define the field seperators as "," but BCP barfed. when I switched to ',' it worked. Maybe you could use this.|||Yeah, I had a similar idea to create a view with select '"' + field + '"' for each of the char columns. But since my export has over 300 columns and many of them char or varchar, I wanted an easier solution than going through each column.

Luckily, the Oracle DBA on the other end was ok with me not sending the quotes. The field seperator was a bar (|), and so far we haven't found any bars in the data so I think we're in good shape.

Thanks for your reply.

Ray

bcp connection problems

Well this one is puzzling me, and there is probably a very simple answer to this but coming from a linux/MySQL background to a windows/MSSQL system I can't figure it out.

Anyways I run bcp with my queryout string which is

bcp "SELECT * FROM edrdata.dbo.tblcomments WHERE siteid=-1" queryout "C:\DKD\" -c -U <username> -P <password> -S WELLDAQ\DBINFO

but I get an ODBC connection error, see below :

SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory] SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared memory]ConnectionOpen (Connect()).

oh and SQL Server Service Manager shows the Server as WELLDAQ\DBINFO and it is running and I can connect to it through other means such as my visual basic applications I'm coding and through Visual Studio 2005 (remotely) so why can't bcp connect to it?

pls check your your surface area configuration if

1. the server is running in mixed mode

2. the server is configured to accept both local and remote connection

|||

Make sure there are no spaces between the -U and the username. Same for -P and -S

Also, specify a filename as well as the directory path and put the -S parameter before the -U and -P

Hope this helps.

BCP connection problem

Hi,
I am trying to use BCP for bulk importing data into the SQL-server. I
created a system DSN that works fine with MS-Query, but bcp keeps returning
an error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Can anybody tell me what's wrong here (or offer an alternative for bulk
importing data)?
thanks in advance, Hans
Hans,
What is the full command that you are using to bcp in your data? There
may be a problem with the bcp syntax that you are using and not with the
DSN.
Thanks,
JD
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:B0315ACB-43DF-4663-B9C1-9460BA7208DE@.microsoft.com...
> Hi,
> I am trying to use BCP for bulk importing data into the SQL-server. I
> created a system DSN that works fine with MS-Query, but bcp keeps
returning
> an error:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> SQLState = 01000, NativeError = 53
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Can anybody tell me what's wrong here (or offer an alternative for bulk
> importing data)?
> thanks in advance, Hans

BCP

HI ALL,
I have a question. If some one in our group (who are the admins, we use
trusted connection to SQL Server 2000) runs BCP and pull the data, is that
can be logged. i have never seen any events in the logs (like bcp ran, DTS
ran).
advise me any good plans.
Thanks,
Jay.John wrote:
> HI ALL,
> I have a question. If some one in our group (who are the admins, we
> use trusted connection to SQL Server 2000) runs BCP and pull the
> data, is that can be logged. i have never seen any events in the logs
> (like bcp ran, DTS ran).
> advise me any good plans.
> Thanks,
> Jay.
The log does not contain select access to data, just data that has
changed in some way. What are you looking see here? Maybe you need to
use Profiler to capture specific types of access to the database.
--
David Gugick
Imceda Software
www.imceda.com

BCP

HI ALL,
I have a question. If some one in our group (who are the admins, we use
trusted connection to SQL Server 2000) runs BCP and pull the data, is that
can be logged. i have never seen any events in the logs (like bcp ran, DTS
ran).
advise me any good plans.
Thanks,
Jay.
John wrote:
> HI ALL,
> I have a question. If some one in our group (who are the admins, we
> use trusted connection to SQL Server 2000) runs BCP and pull the
> data, is that can be logged. i have never seen any events in the logs
> (like bcp ran, DTS ran).
> advise me any good plans.
> Thanks,
> Jay.
The log does not contain select access to data, just data that has
changed in some way. What are you looking see here? Maybe you need to
use Profiler to capture specific types of access to the database.
David Gugick
Imceda Software
www.imceda.com

Batch updates with SQL CLR

Unfortunately batch updates (i.e. setting SqlDataAdapter.UpdateBatchSize to >1) are not possible with ADO.NET using a context connection. Does anyone know why it's not possible? Will it be implemented or allowed in a future version of .NET?

Here's what I'm currently doing:
I have a C# stored procedure, using a context connection, that updates all the records in a table (typically a million records) by repeatedly:
- sequentially reading a group of records (1000 at a time) into a SqlDataAdapter (using a 'select top row_number' -type statement that prevents re-querying processed records);
- performing complex processing on each record and writing the results back to the adapter (several fields of each record are updated);
- updating the table when each group has been processed.

This works, but it's not quite as fast as I'd have hoped for and is only slightly slower if using a non-context connection from an external application. I'd like to enable batch updating to increase performance (I actually get much faster performance from an external application that batch updates using a non-context connection, say 20% faster!)... but of course I can't with SQL CLR.

Any ideas on how I can improve performance of my updating?

Thanks in advance,

Graham

If I understand correctly, you're doing record by record calculation. So all required info is present within the record. If I would try to solve this I would have started with a CLR function, not a SP. So my statement would look something like:

Update BigTable Set CalcField = CLRFunc.ToughCalc(fieldX, fieldY, fieldZ)

I'm not sure if this would be faster, but it would be set based (not all functions will produce set bases solutions)

Basic SQL Connection

Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row.

When it gets to the third line the page crashes with an error.

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True");

SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection);

SqlDataReader reader = cmd.ExecuteReader();

is there somewhere i need to put in my username or password? or is this code just wrong

Many thanks burnside.

-- Edited by longhorn2005

What is your error message?|||

Try the below connection string

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;Integrated Security=True");

And if your database require Username and password check the below link

Building Connection String C#

HC

|||

Thanks for the replys,

The page seems to crash when i uncomment out the third line of sql code "// SqlDataReader rdr = cmd.ExecuteReader();"
"System.Data.SqlClient" %> and i get this slightly unhelpful error message @.http://s152182516.websitehome.co.uk/test/default2.aspx

<%

@.PageLanguage="C#" %>

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SqlClient" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">protectedvoid Page_Load(object sender,EventArgs e)

{

// Define database connectionSqlConnection conn =newSqlConnection("uid=xxxxx;password=xxxxx;Server=mssql07.oneandone.co.uk;Database=db198704784;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);SqlDataReader rdr = cmd.ExecuteReader();

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:TextBoxID="TextBox1"runat="server"OnTextChanged="TextBox1_TextChanged"Height="176px"Width="338px"></asp:TextBox><asp:LabelID="Label1"runat="server"Text="Test Of SQL"></asp:Label></div></form>

</

body>

</

html>|||

Try to open the connection before you call ExecuteReader() example below

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();

HC

--

Mark this post as ANSWERED if it helped you

|||

I've tried the Open command but still no joy. I think i might have to hire someone to go into my MS hosting and check it and the SQL is setup, and make a little Logon aspx with the sql database so i can see the code to access it. If anyone is interested please email me.

|||

Hello,

These are some tutorial for you:

http://www.codeproject.com/aspnet/SQLConnect.asp

http://samples.gotdotnet.com/quickstart/aspplus/doc/adoplusoverview.aspx

HTH

|||

I tried to access the page you provided but unfortunately its unavailable. What do you mean by the page crashes? Can you post the complete error you are getting? Make sure the database exists and the table you trying to query exists in thedatabase.

Note: Never give out the username/password in the posts. replace them with XXXXX

Thanks