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

Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.

basic sql query needed

Ok, I'm a beginner so forgive me for my ignorance. Could someone help
me with this?

tbl_x has two fields xid and xlist
xlist being a list of numbers....1, 5, 6, 8

i want to create a new table from tbl_x that converts each number in
that xlist into a row in tbl_new

so for example
tbl_x has 3 rows
xid xlist
1 1, 4, 5
2 2, 3, 7
3 2, 1, 7

i need a query or sql script that will convert that table with a list
into the following...

tbl_new
id xid xlid
1 1 1
2 1 4
3 1 5
4 2 2
5 2 3
6 2 7
7 3 2
8 3 1
9 3 7is xlist always 7 char width? or will it have some thing like 123,1,333

rekcah@.freeshell.org (G Hopper) wrote in message news:<15b4c9ac.0407271246.5cd91a6b@.posting.google.com>...
> Ok, I'm a beginner so forgive me for my ignorance. Could someone help
> me with this?
> tbl_x has two fields xid and xlist
> xlist being a list of numbers....1, 5, 6, 8
> i want to create a new table from tbl_x that converts each number in
> that xlist into a row in tbl_new
> so for example
> tbl_x has 3 rows
> xid xlist
> 1 1, 4, 5
> 2 2, 3, 7
> 3 2, 1, 7
> i need a query or sql script that will convert that table with a list
> into the following...
> tbl_new
> id xid xlid
> 1 1 1
> 2 1 4
> 3 1 5
> 4 2 2
> 5 2 3
> 6 2 7
> 7 3 2
> 8 3 1
> 9 3 7|||rekcah@.freeshell.org (G Hopper) wrote in message news:<15b4c9ac.0407271246.5cd91a6b@.posting.google.com>...
> Ok, I'm a beginner so forgive me for my ignorance. Could someone help
> me with this?
> tbl_x has two fields xid and xlist
> xlist being a list of numbers....1, 5, 6, 8
> i want to create a new table from tbl_x that converts each number in
> that xlist into a row in tbl_new
> so for example
> tbl_x has 3 rows
> xid xlist
> 1 1, 4, 5
> 2 2, 3, 7
> 3 2, 1, 7
> i need a query or sql script that will convert that table with a list
> into the following...
> tbl_new
> id xid xlid
> 1 1 1
> 2 1 4
> 3 1 5
> 4 2 2
> 5 2 3
> 6 2 7
> 7 3 2
> 8 3 1
> 9 3 7

It's not pretty but, this code works. I normally avoid cursors, but
couldn't get the function to work as a subquery in the from clause.
It should get you started.

create table t_ids
(xid int, xlist varchar(25))
go

create table t_ids2
(xid int, xl int)
go

insert into t_ids
select 1 as xid, '1, 4, 5'
union select 2, '2, 3, 7'
union select 3, '2, 1, 7'

GO

CREATE FUNCTION f_split(@.id int, @.str varchar(255), @.sep char(1))
RETURNS @.vals table (id int, val varchar(25))
as
begin
declare @.start int,
@.found int
set @.start = 1
set @.found = 0
while (@.start > 0) begin
set @.found = charindex(@.sep, @.str, @.start)
if @.found = 0 begin
if @.start > 1
insert into @.vals values(@.id, ltrim(rtrim(substring(@.str, @.start,
len(@.str) - @.start + 1))))
break
end
else begin
insert into @.vals values(@.id, ltrim(rtrim(substring(@.str, @.start,
@.found - @.start))))
set @.start = @.found + 1
end
end

return
end
GO

DECLARE @.id as int, @.xl as varchar(25)

DECLARE c_xl CURSOR FAST_FORWARD
FOR SELECT xid, xlist FROM t_ids
open c_xl
FETCH c_xl into @.id, @.xl
WHILE @.@.Fetch_Status = 0 BEGIN
insert into t_ids2
select * from dbo.f_split(@.id, @.xl, ',')
FETCH c_xl into @.id, @.xl

END
CLOSE c_xl

select * from t_ids2