problem with bcp using format file

The running of my bcp (with queryout) is aborting, with the message below. A
t
first I identified that it happened only with column with NULL value, but no
w
I realize this occurrence happened in other field without NULL value.
Anyone has suggestions ? Thanks a lot
My format file has the following contents : 8.0 (version) 2 (number of
columns)
1 SQLCHAR 0 20 "\t" 1 numeroaj Latin1_General_CI_AS
2 SQLCHAR 0 54 "\r\n" 2 acervoespecializada Latin1_General_CI_AS
My bad run :
D:\Users\sql>bcp "SELECT numeroaj, acervoespecializada from
Siga.dbo.vwHerancaJa
cente" queryout C:\dts\hjacente.txt -f d:\users\sql\pgm3.fmt -Smyserver
-Umyuser -Pmypwd
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Erro de E/S ao ler o arquivo no
forma
to BCP ==> Translating... Error of I/O when read the format filewhere do you run this bcp statement, on the client or on the server itself.
"d:\" must be local to where ever you run the bcp statement.
-oj
"Adalberto Andrade" <Adalberto Andrade@.discussions.microsoft.com> wrote in
message news:014C3C25-592D-4A88-A320-325C52D49D2A@.microsoft.com...
> The running of my bcp (with queryout) is aborting, with the message below.
> At
> first I identified that it happened only with column with NULL value, but
> now
> I realize this occurrence happened in other field without NULL value.
> Anyone has suggestions ? Thanks a lot
> My format file has the following contents : 8.0 (version) 2 (number of
> columns)
> 1 SQLCHAR 0 20 "\t" 1 numeroaj Latin1_General_CI_AS
> 2 SQLCHAR 0 54 "\r\n" 2 acervoespecializada
> Latin1_General_CI_AS
> My bad run :
> D:\Users\sql>bcp "SELECT numeroaj, acervoespecializada from
> Siga.dbo.vwHerancaJa
> cente" queryout C:\dts\hjacente.txt -f d:\users\sql\pgm3.fmt -Smyserver
> -Umyuser -Pmypwd
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Erro de E/S ao ler o arquivo no
> forma
> to BCP ==> Translating... Error of I/O when read the format file|||Hi oj,
On the client. And "d:\" is a local drive (in my machine). With
others columns it worked OK.
Thanks
Adalberto Andrade
"oj" wrote:

> where do you run this bcp statement, on the client or on the server itself
.
> "d:\" must be local to where ever you run the bcp statement.
> --
> -oj
>
> "Adalberto Andrade" <Adalberto Andrade@.discussions.microsoft.com> wrote in
> message news:014C3C25-592D-4A88-A320-325C52D49D2A@.microsoft.com...
>
>|||Adalberto,
Please check to see if there is a carriage return at the
end of your format file and that there are no extra tabs
or anything else in it. Also be sure the format file
is not open in an editor when you run the command.
The error message mentions the format file, not the
data file, so I think the problem is with the format file.
Steve Kass
Drew University
Adalberto Andrade wrote:
>Hi oj,
> On the client. And "d:\" is a local drive (in my machine). With
>others columns it worked OK.
>
> Thanks
> Adalberto Andrade
>
>
>"oj" wrote:
>
>|||Steve,
I made a complete revision of all components of my environment.
Things like : cr (carriage return), opened format file and others wrongs
caracters inside of format file are not the problem. I am thankful for yours
suggestions,but the problem continue. Now I substituted the third column of
my output file and the bcp utility started the copy, but the generated file
has mixed data with stranger caracters. This new column has valids datas and
also null values in some registers and it is the great difference between th
e
others columns (first e second ones). Without this third column everything
work 100% OK. I already try to change the value of prefix length (field of
the format file) to -1 or 2 with the hope to solve it, but the output
generated file continue with stranger and mixed caracters (like ASCII
caracters). I really don't have any idea of what I can do to put it to work.
I am not sure, but perhaps I will need to make other configuration in my
format file, but exactly what ? Do you have other help for me ?
Thanks again
Adalberto Andrade
"Steve Kass" wrote:

> Adalberto,
> Please check to see if there is a carriage return at the
> end of your format file and that there are no extra tabs
> or anything else in it. Also be sure the format file
> is not open in an editor when you run the command.
> The error message mentions the format file, not the
> data file, so I think the problem is with the format file.
> Steve Kass
> Drew University
> Adalberto Andrade wrote:
>
>|||Adalberto,
I am not sure what the problem is, but here are three separate
suggestions.
1. Try to use bcp without a format file, since TAB and NEWLINE
are the defaults for bcp. If this creates a Unicode file, you will need
to change the format file to say SQLNCHAR instead of SQLCHAR,
and you will also need to put the Unicode two-byte signature into
the beginning of the file yourself, since bcp does not do this for you.
2. Be sure the format file is saved as ASCII, not Unicode, then try
again with the format file.
3. Verify the data lengths and types of the output,
3A. Run this and provide the output.
select top 1
numeroaj, acervoespecializada
into CheckTypesTable
from Siga.dbo.vwHerancaJacente
select * from CheckTypesTable
3B. In Query Analyzer, refresh the current database
and for [CheckTypesTable] choose "Script Table To
New Window" to verify the data types of these columns
and provide the output.
3C. After doing this, you can DROP the table CheckTypesTable.
It might help if you post the definition of Siga.dbo.vwHerancaJa.
(If it is a view, also post CREATE TABLE statements from the
tables it uses for the columns numeroaj and acervoespecializada
(and the third column, since at one point you mention three columns.
Also, when you have three columns, what is your query?)
SK
Adalberto Andrade wrote:
>Steve,
> I made a complete revision of all components of my environment.
>Things like : cr (carriage return), opened format file and others wrongs
>caracters inside of format file are not the problem. I am thankful for your
s
>suggestions,but the problem continue. Now I substituted the third column of
>my output file and the bcp utility started the copy, but the generated file
>has mixed data with stranger caracters. This new column has valids datas an
d
>also null values in some registers and it is the great difference between t
he
>others columns (first e second ones). Without this third column everything
>work 100% OK. I already try to change the value of prefix length (field of
>the format file) to -1 or 2 with the hope to solve it, but the output
>generated file continue with stranger and mixed caracters (like ASCII
>caracters). I really don't have any idea of what I can do to put it to work
.
>I am not sure, but perhaps I will need to make other configuration in my
>format file, but exactly what ? Do you have other help for me ?
>
> Thanks again
>Adalberto Andrade
>"Steve Kass" wrote:
>
>|||Steve,
Forgive me for delay in my reply. I was very busy. Let 's go. Really
you are correct. When I ran the bcp utility in the prompt without my format
file, it show the message explaining that happened a truncate. In true there
was a difference between the data length of one column and your value define
d
for this size in the format file. Summarizing, the problem is over and your
suggesntions 1 and 3 were very helpful.
Thanks a lot
Adalberto
Andrade
Rio de Janeiro's
City Hall
"Steve Kass" wrote:

> Adalberto,
> I am not sure what the problem is, but here are three separate
> suggestions.
> 1. Try to use bcp without a format file, since TAB and NEWLINE
> are the defaults for bcp. If this creates a Unicode file, you will need
> to change the format file to say SQLNCHAR instead of SQLCHAR,
> and you will also need to put the Unicode two-byte signature into
> the beginning of the file yourself, since bcp does not do this for you.
> 2. Be sure the format file is saved as ASCII, not Unicode, then try
> again with the format file.
> 3. Verify the data lengths and types of the output,
> 3A. Run this and provide the output.
> select top 1
> numeroaj, acervoespecializada
> into CheckTypesTable
> from Siga.dbo.vwHerancaJacente
> select * from CheckTypesTable
> 3B. In Query Analyzer, refresh the current database
> and for [CheckTypesTable] choose "Script Table To
> New Window" to verify the data types of these columns
> and provide the output.
> 3C. After doing this, you can DROP the table CheckTypesTable.
>
> It might help if you post the definition of Siga.dbo.vwHerancaJa.
> (If it is a view, also post CREATE TABLE statements from the
> tables it uses for the columns numeroaj and acervoespecializada
> (and the third column, since at one point you mention three columns.
> Also, when you have three columns, what is your query?)
> SK
> Adalberto Andrade wrote:
>
>

problem with a date parameter in ASP.Net page

The date format in the 'weekending' textbox is 5/5/2007
Here's what I've got:

With cmd.Parameters
.Add(New SqlParameter("@.SQLweekending", SqlDbType.DateTime,weekending.Text))
the error message is:
"Conversion from string "4/8/2007" to type 'Integer' is not valid."

Then, I tried:
.Add(

New SqlParameter("@.SQLweekending", SqlDbType.DateTime,CDate(weekending.Text)))

but, before I even tried it, it gave me a blue squiggly line, with an error message:
"Error 2 Value of type 'Date' cannot be converted to 'Integer'"

How can I get this to work?

Hi,

What is the data type for yourSQLweekending column in your table? Is it a DateTime column or Integer type? You need a DateTime column for this.

|||Nope - it's datetime also|||

Here is sample in VB.NET that inserts date to a datetime column:

Dim MySQLAsString ="Insert into Dates (id, tDate) values (@.id, @.tDate)"Dim myConnAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString.ToString())Dim CmdAsNew SQLCommand(MySQL, MyConn)

Cmd.Parameters.Add(

New SqlParameter("@.id", txtid.Text))

Cmd.Parameters.Add(

New SqlParameter("@.tDate",txttDate.Text))

MyConn.Open()

cmd.ExecuteNonQuery()

MyConn.Close()sql

problem with a clustered index?

This is a multi-part message in MIME format.
--=_NextPart_000_0052_01C4758A.B87F1790
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
Hello,
I have a table with about 5 milion records, and the performance is = un-acceptable (more than 45 seconds for a query). The table was imported = from a text file. I have defined a primary key (two varchar(20) = columns). Then I checked and the database has created a clustered index = on these columns. But I suspect that something is wrong with the index: = When I run a select query, the rows are returned in an arbitrary order, = and not in the order defined by the index. Is it possible that something = is wrong with the index? If yes, how can I fix that?
thanks, David
--=_NextPart_000_0052_01C4758A.B87F1790
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
I have a table with about 5 milion = records, and the performance is un-acceptable (more than 45 seconds for a query). The = table was imported from a text file. I have defined a primary key (two varchar(20) = columns). Then I checked and the database has created a clustered index = on these columns. But I suspect that something is wrong with the index: When I = run a select query, the rows are returned in an arbitrary order, and not = in the order defined by the index. Is it possible that something is wrong with = the index? If yes, how can I fix that?
thanks, = David
--=_NextPart_000_0052_01C4758A.B87F1790--This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C4758B.8E928F10
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
David
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message =news:um00pGXdEHA.3148@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is =un-acceptable (more than 45 seconds for a query). The table was imported =from a text file. I have defined a primary key (two varchar(20) =columns). Then I checked and the database has created a clustered index =on these columns. But I suspect that something is wrong with the index: =When I run a select query, the rows are returned in an arbitrary order, =and not in the order defined by the index. Is it possible that something =is wrong with the index? If yes, how can I fix that?
thanks, David
--=_NextPart_000_000B_01C4758B.8E928F10
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

David
Did you spesify ORDER BY clause when =you run SELECT statement.
What is your search criteria in the query?
"David" =wrote in message news:um00pGXdEHA.3148=@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion =records, and the performance is un-acceptable (more than 45 seconds for a query). =The table was imported from a text file. I have defined a primary key (two =varchar(20) columns). Then I checked and the database has created a clustered =index on these columns. But I suspect that something is wrong with the index: =When I run a select query, the rows are returned in an arbitrary order, =and not in the order defined by the index. Is it possible that something is =wrong with the index? If yes, how can I fix that?
thanks, David

---

--=_NextPart_000_000B_01C4758B.8E928F10--|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
--
David Portas
SQL Server MVP
--|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Try a DBCC DBREINDEX or DBCC INDEXDEFRAG depending upon
your preference, then update the statistics.
It sometimes happends when you do a bulk insert that the
statistics need re-doing (in my opinion).
Peter
>--Original Message--
>Hello,
>I have a table with about 5 milion records, and the
performance is un-acceptable (more than 45 seconds for a
query). The table was imported from a text file. I have
defined a primary key (two varchar(20) columns). Then I
checked and the database has created a clustered index on
these columns. But I suspect that something is wrong with
the index: When I run a select query, the rows are
returned in an arbitrary order, and not in the order
defined by the index. Is it possible that something is
wrong with the index? If yes, how can I fix that?
>thanks, David

problem with convert 2000 server 2000

we want to insert date into table testDate in Datetime or another dataType in field testDate and extract it in hijry Date format And compare it with two Date.
acording to the articles we insert it in to the table in nchar dataType and then we want to extract it in datetime dataType using convert function in sql server :

(( SELECT CONVERT(datetime, ' testDate', 131)
FROM testDate ))
that gives this error:

(( Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string. ))

if we want to insert Date in datetime dataType it enter in gregorian Date format.
while we want Date in hijry format.try this, it may work? You will have to sort the hours and minutes etc...

convert( smalldatetime, ( datename( yy, testdate) + ' '
+ datename( mm, testdate) + ' '
+ datename( dd, testdate)
))

Problem while exporting in MAPI

Hi,

I am using crystal report v8.5.
I click on the export icon & select Acrobat Format (PDF) as Format and MAPI as Destination.
But nothing appeared and i couldn't export report whereas i can succesfully save the same report when select Acrobat Format (PDF) as Format and "disk file" as Destination.
Please help me out?

Thanks,
JasIs this on you development machine or the Client machine? Did you make sure you had the correct MAPI dll installed on the computer?|||this is on my developing m/c. i use outlook express for mailing purpose, which works fine. how to check whether correct mapi dll is intalled?

thanks

jas|||I also use CR 8.5. On my computer (Win2k), all the Export dlls are located in C:\WINNT\Crystal. The MAPI one has the name u2dmapi.dll. If you can't find the right dll, maybe it wasn't installed when you installed Crystal Reports. You may be able to reinstall it or pull the dll from the installation CD.|||i use win xp professional, u2dmapi.dll is present in my C:\WINDOWS\Crystal folder but still i couldn't mail. when i click "ok" after selecting format as Acrobat and destination as MAPI, one window appeared titled as "Export options" which allows to enter page range. But after that nothing happened.|||Do you have Outlook or something setup on your computer? I'm not familiar with MAPI, but while researching one of my exporting problems, I read a post where someone was having trouble exporting with MAPI and it was because Outlook wasn't set up with a user profile.|||yaa, i m using outlook express to send n receive mails and it is working perfectly.|||Outlook express is not connected through MAPI, while Microsoft Outlook uses MAPI, to configure a profile follow these steps

1. Open Mail Control Panel
2. Configure Message Store and Internet mail account
3. then use Microsoft Outlook to send & receive mail, if it works then ur Export will also work.|||Thanx for ur help. I m off from work for 2-3 weeks, got chicken pox :( , will try later.
Thanks again
Jas|||Get well soon