bcp queryout strange behaviour

I use bcp fairly often in SQLServer2000 but have never run across this before. In a 512 SQLCHAR column containing notes, when two spaces are encountered (i.e. ' '), bcp is replacing ' ' with '\r\n'.

I figured it was a problem with my format file, but I have not found enough specific info on the MSDN site to resolve this.

This is my bcp command:

bcp "SELECT DivCode,CommCode,ContactID,substring(Notes, 0, 512) from frep.dbo.BeBackExtract" queryout "D:\BeBackDetail.dat" -f "D:\BeBackDetail.fmt" -e "D:\BeBackDetailErrors.dat" -U user -P pass -S server

And format file:

8.0
4
1 SQLCHAR 0 2 "" 1 DIVCODE ""
2 SQLCHAR 0 3 "" 2 COMMCODE ""
3 SQLCHAR 0 10 "" 3 CONTACTID ""
4 SQLCHAR 0 512 "\r\n" 4 NOTES ""

Has anyone seen this?

Incidentally I also tried using the REPLACE function which seems to work great until trying to replace 2 spaces with 1 space, in which case it doesn't do anything.

Thanks for any ideas -BTW the site trimmed my ' ' strings - those are supposed to appear as two spaces. So i.e. "...bcp is replacing ' ' with '\r\n'." should contain 2 spaces within the first string.

BCP Beginner - Please Help

Hi,

I am completely new to the BCP utility and fairly new to SQL Server

I am learning from a book and I am trying the following example (the server
I'm learning on is called contractor and a password has not been give to the
sa)

bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor

When I run this in Query Analyser i get the error message..

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I have tried puuting quotes around the database and the table name as
follows-

bcp "pubs..authors" out authors.txt -C -r \n -t, -U sa -P -S contractor

and get the error

Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a stored procedure.

Please can anybody help me to get this first bit working?

Thanks in anticipation.In your bcp statement, is the -C supposed to indicate that it is a
character file? If so, it needs to be lowercase (-c), otherwise it
indicates the code page, which expects a value. Also, I don't know if
this makes a difference, but I've never used spaces between the options
and the values (for example, -Usa instead of -U sa). I thought I
remembered both working, but in the help I didn't see anything about
the space being allowed and the samples given do not have spaces.

HTH,
-Tom.|||Stevie D (Steve@.127.0.0.1) writes:
> I am completely new to the BCP utility and fairly new to SQL Server
> I am learning from a book and I am trying the following example (the
> server I'm learning on is called contractor and a password has not been
> give to the sa)
> bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor
> When I run this in Query Analyser i get the error message..

You don't BCP from Query Analyzer. You run it from a command-line
window.

And, as Thomas pointed out, you should use -c and not -C. The
spacing should be OK, though.

> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.

(The reason you get this syntax error is that bcp is not an SQL
command. Everything that is an identifier, but not a command is
taken as a stored procedure command. Next token is pubs. When calling
stored procedures quotes around strings that follow identifier syntax
are optional. (This is to permit us to write "sp_help tbl", instead
of "EXEC sp_help 'tbl'".) When the dot comes there is no longer
any saver.)

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

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

basics with stored procedures.....please

Hi there, I'm still fairly new to asp.net / c#

I've setup my stored procedure, but now what?
I want to display the information (only one record it will return) on the page / NOT using a datagrid or list, just simple....I guess using an asp:label???
Could someone tell me what I need to do!!!

Also, are there any proper resources out there that just show you right out of the box howto insert / update / delete with sp's?, show / view, just proper simple source code?

THANKS!

my code:
// Creating the database connection
SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_STRING_conn_main"]);

// Creating the stored procedure 4 the webpage
// Instantiate a Sql Command
SqlCommand webpage_view = new SqlCommand("stpr_webpage_view", objConn);
// Declare that the T-SQL statement that will be executed is a stored procedure
// Use a CommandType.Text to execute a T-SQL Query
webpage_view.CommandType = CommandType.StoredProcedure;
SqlParameter webpage_id_parm = webpage_view.Parameters.Add("@.webpage_id", SqlDbType.Int);
webpage_id_parm.Value = "1";
// Create a SqlDataAdapter to manage the connection and retrieve the data
SqlDataAdapter da_1 = new SqlDataAdapter();
// Instantiate a Dataset that will hold the data retrieved from the database using the select command
DataSet ds_webpage = new DataSet();
da_1.SelectCommand = webpage_view;
// Retrieve the data into the dataset using the SqlDataAdapter.Fill command
da_1.Fill( ds_webpage, "webpage_title" );
DataView dv2 = ds_webpage.Tables[0].DefaultView;
if ( !this.IsPostBack ) {
this.DataBind ( );
}

// C#
// To place a single value from a recordset into a label
// drag an ASP:Label onto the Web form and give it an ID of myLabel
myLabel.Text = "No data";
// Be compulsive and make sure there is a datatable in the dataset and that it has at least one row
if( ds_webpage.Tables.Count > 0 && ds_webpage.Tables[0].Rows.Count > ) ){
 myLabel.Text = ds_webpage.Tables[0].Rows[0].ToString();
}

The simplest way to see how stored procedures can be used to select and update data is to let VS.Net create it for you.

1. Go to Design View for a web form.
2. Drag an SqlDataAdapter onto the form. A DataAdapter Configuration Wizard will open
3. Select your database connection and click Next.
4. On the Choose a Query Type screen select the second option "Create a new Stored Procedure". Click Next
5. On the Generate a Stored Procedure screen enter a select statement for the fields you want to use. For example, just something like Select * from SomeTable. Click Next.
6. On the Create the Stored Procedures screen enter appropriate stored procedure names. Be sure to click the "Yes, create them in the database for me" radio button.

Look at the code behind generated for this data adapter to see how it all works. Use it like the one you created above for the select. For the update, after you changed data in the data set just call:
da_1.Update( ds_webpage, "webpage_title" );

Basic SQL Cluster Question

I am fairly new to administering SQL Server in a cluster environment,
I was kinda confused this morning when I was reading a post where a
individual installed SP2 for SQL 2005 on both the noeds of the
cluster. My understanding was you install the SP2 in just one node and
the cluster service takes care of it. Am I wrong? Do we really need to
install it in both the nodes?
Thanksshub wrote:
> I am fairly new to administering SQL Server in a cluster environment,
> I was kinda confused this morning when I was reading a post where a
> individual installed SP2 for SQL 2005 on both the noeds of the
> cluster. My understanding was you install the SP2 in just one node and
> the cluster service takes care of it. Am I wrong? Do we really need to
> install it in both the nodes?
> Thanks
>
actualy, SP2 instalation does it on other node(s) automaticaly, it just
asks you for a administrator login/password on the other node(s).
Finaly, sp2 instalation asks you for the restart of the node you're
running it on and reminds you to restart other node(s) manualy|||It depends on what you are patching. Some components are cluster-aware and
update all nodes. Some components require updating on each node
individually.
This blog post covers what goes where.
http://weblogs.sqlteam.com/geoffh/archive/2007/10/04/To-Cluster-or-Not-to-Cluster-That-SSIS-the-Question.aspx
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193403686.136330.93900@.o3g2000hsb.googlegroups.com...
>I am fairly new to administering SQL Server in a cluster environment,
> I was kinda confused this morning when I was reading a post where a
> individual installed SP2 for SQL 2005 on both the noeds of the
> cluster. My understanding was you install the SP2 in just one node and
> the cluster service takes care of it. Am I wrong? Do we really need to
> install it in both the nodes?
> Thanks
>

Basic SQL Cluster Question

I am fairly new to administering SQL Server in a cluster environment,
I was kinda confused this morning when I was reading a post where a
individual installed SP2 for SQL 2005 on both the noeds of the
cluster. My understanding was you install the SP2 in just one node and
the cluster service takes care of it. Am I wrong? Do we really need to
install it in both the nodes?
ThanksHi
You would not need to do this normally, you would need to post a link and
give more information.
John
"shub" wrote:
> I am fairly new to administering SQL Server in a cluster environment,
> I was kinda confused this morning when I was reading a post where a
> individual installed SP2 for SQL 2005 on both the noeds of the
> cluster. My understanding was you install the SP2 in just one node and
> the cluster service takes care of it. Am I wrong? Do we really need to
> install it in both the nodes?
> Thanks
>