Problem in Importing data from excel to sql server

I have excel file that has field named Purpose. Its max length is 400 character. I import this file to sql server database table. And also i change the purpose field in sql server database table with nvarchar 400. But when i run this job, it gave me error message:

Error at source for row number 1215. Errors encountereed so far in this task: 1.
Data for source column 18 ('Purpose') is too large for the specified buffer size.

What should i do so that i still can import the data from excel to sql server database?

Thanks for your help.I have far fewer problems when I take spreadsheets and put them in Access and then import them into sql server from access than I do just importing from excel.

Problem in getting SUM of Running Total Field

Hi buddy...

i m creating a report as below

--------------------------
Report Head ==>>> | Field1 |
--------------------------
Field2 Field3 Field4 Field5 Field6 Field7
--------------------------

Detail Section Here.....

--------------------------
sum(Field7)
--------------------------

Field7 is my Running Formula Field. i.e. Field7= (Field1 + Field5 - Field6)
nd thats working well.

Working ::

i m generating a report that nd gets the sum of Field7 as far as there is some record in details.

but when there is no record in the details section Sum of Field7 shows me nothing,
where as i am required to display the value of Field1 in it if there is nothing in the details section.

He who give solution will be considered a well qualified person.

with thanks nd admiration in advance

Silly StarCreate another formula with this :

If IsNull(Field7) then
Field1
else
Field7|||Hey KristyW

You Are Real Genius

Thats Worked Perfectly.

Thank u So much Sir but there r more Problems regarding Crystal Report
May i ask them

Silly Star|||u r a real "Silly Star"|||Hi ...

i m creating a report as below

--------------------------
GroupHeader ==>>> | Field1 |
--------------------------
Field2 Field3 Field4 Field5 Field6 Field7
--------------------------

Detail Section Here.....

--------------------------
GroupFooter==>> sum(Field7)
--------------------------

Field7 is the Running Formula Field. i.e. Field7= (Field1 + Field5 - Field6)
and i reset this formula on GroupChange
nd thats working well.

but when a group is changed it does not work properly (Meaning the desired result is not displayed)

He who give solution will be considered a well qualified person.

with thanks nd admiration in advance

Silly Star|||hai silly star

Edit the running field as

In the summary part give field 7 as the field to summarize and typeof summary is sum

In the evaluate column check the 'for each record' and

In the reset column check the 'on chage of group'

try this and it will work for you i believe

bye

viji

Problem in getting result

I have an account field which has datatype string. I wantto onlyget thosevalues which areinbetween 0-199.

I used

selectsum(t.a_trans_amt) Creditfrom a_account a, a_transaction t

where a.a_account_numbetween'0'and'199'

and

t.a_account_id=a.a_account_id

and t.a_debit_credit_ind='C'

but this query also including thosevalues which have starting 3 digitinbetween 0-199.

I don't know how to fix this problem . Can anybody help me on this issue.

Thanks in Advance.

What about a convert:

WHEREConvert(int,a.a_account_num)between 0and 199

|||

i tried it but it gave me this error

Error converting data type varchar to bigint.

i think becoz some '-' is there in the name

|||

When you do a string comparison, you get what you have now. If you want to use integer to compare, you need to show all your data patterns. You can remove the hyphen if you think that will get correct result. Just post some of your data and let's see what we can do for you.

Problem in Displaying NTEXT field from database?

Hello,

I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.

I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.

any ideas really appriciated.

Thanks

Ram

text and ntext fields should always be either the only data returned or the last item in a select statement.

For example:

SELECT ntextField FROM myTable WHERE something = true

or

SELECT myFirstField, myOtherField, ntextField FROM myTable WHERE something = true

This is a limitation of MSSQL server and (some versions) of MySQL. If you are using MSSQL Server 2005, I would suggest changing the database column to a nvarchar(MAX) as this will store the same number of characters as an ntext column. (I believe they intended to remove text and ntext from the next release of MSSQL Server in favor of varchar(MAX) and nvarchar(MAX), but don't quote me on that.)

|||

I am using SQLserver 2000, What will be the possible solutions for this problem.

Thanks

|||

You will have to select each ntext field individually. So you will need 7 select statements each time.

However, I would recommend changing the database to use a different type of column. If you know the data will never be larger than 8K you can still use nvarchar(8000) on SQL Server 2000. Or you could combine some of the columns and then use delimiters so that you have one huge column with something like ||| between entries (but this may cause other problems.)

In general text, ntext and blob fields should be used as rarely as possible, it is usually easier to put that much data into a file and then store the files name and location in SQL Server.

Let me know if this doesn't really answer your question.

|||

Hi,

Its working for me now..What I did is ...

I have got all ntext fields from the recordset first into some varialbles before acesing non ntext fields and mapped to the form controls.

thats it.

thanks

problem in crystal report printing

i have developed a report in crystal report having field object with multiple lines. preview is perfect but it doesn't print in some dot matrix printer. please help me in this regard. thanks in advance.if printer is different then output may vary. use different printer setting for different printers.

Problem in between clause

Hi to all
i have a table which in which date is storing in three separate fields
all datatype char. Values storing are like
01 in day field Mar in month field and 2005 in year fields
. now when i try to get data between two dates results are not coming as
expected.
i m using following query
SELECT a.station_desc
,sum(b.ttl_appl_visited) Total_Token_Booked
,sum(b.ttl_urgent_tokens_today) Urgent_Token
FROM tbl_Value b
left JOIN dbo.Daily_Report_Station ON
dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
where report_day between '15' and '07' and report_month between 'Feb'
and 'Mar'
and report_year between '2006' and '2006'
group by a.station_desc
if i give report day value like report_day between '10' and '20' it
returns me correct value but when first value is greater no result comes
as in the query
Regards,
Farid
*** Sent via Developersdex http://www.examnotes.net ***For a start something like
where report_year = '2006'
and (( report_month = 2 and report_day >= 15 ) or
( report_month = 3 and report_day < 7 ))
If you're stuck with the design you currently have, write a UDF that takes
year, month and day as arguments and returns a datetime, then use
between dbo.MyGetDate( '2006', 'Feb', '15' ) and dbo.MyGetDate( '2006',
'Mar','07')
"Ghulam Farid" wrote:

>
> Hi to all
> i have a table which in which date is storing in three separate fields
> all datatype char. Values storing are like
> 01 in day field Mar in month field and 2005 in year fields
> . now when i try to get data between two dates results are not coming as
> expected.
> i m using following query
> SELECT a.station_desc
> ,sum(b.ttl_appl_visited) Total_Token_Booked
> ,sum(b.ttl_urgent_tokens_today) Urgent_Token
> FROM tbl_Value b
> left JOIN dbo.Daily_Report_Station ON
> dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
> where report_day between '15' and '07' and report_month between 'Feb'
> and 'Mar'
> and report_year between '2006' and '2006'
> group by a.station_desc
>
> if i give report day value like report_day between '10' and '20' it
> returns me correct value but when first value is greater no result comes
> as in the query
> Regards,
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
I'd say the problem is how you use between clause.
Between translates into pair of statements >= and <=
So first part of your condition would be
where report_day >= 15 and report_day <= 7.
This condition returns false, hence all following conditions are not parsed
afaik.
try this example
select 'test' where 2 between 2 and 4
select 'test' where 3 between 4 and 2
Also, do you want data between 07.02.2006 and 15.03.2006 or between 07 and
15 day of Feb and Mar in 2006?
HTH
Peter|||yes u r right first condition is making result false so wht would b the
apropriate condition using same data structure. and its true i want
result between 07-02-2005 and 15-03-2005 but result is not coming. any
help
*** Sent via Developersdex http://www.examnotes.net ***|||You'll have to convert the months to integers. Might be a good use of a
computed column or view with a case report_month when 'Jan' then 1 etc.
Then if you want between 15 Feb and 07 Apr, after converting the months to
integers you would write something like:
where (
( month = 2 and day >= 15) or /* handle February 15-28 */
( month > 2 and month < 4 ) or /* Mar or any other months inbetween */
(month = 4 and day <= 7 ) /* handle final month Apr */
)
"Ghulam Farid" wrote:

> yes u r right first condition is making result false so wht would b the
> apropriate condition using same data structure. and its true i want
> result between 07-02-2005 and 15-03-2005 but result is not coming. any
> help
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Stop doing this and use a DATETIME data type. One of your problems is
that you are mimicking a Cobol record, which has fields for the date
components. If you understood the concept of a column -- which is
nohting like a field -- you would not make this mistake.sql

problem importing photo

hi all
please i want to now how i can import (Employee Photo) that saved in a folder (Photos) depend on (Employee ID) field.
please i need your helpIt's in the help.
Dynamic images or images, dynamic or something.|||i m sorry , but i can't find it please tell me where i can find Picture command or OLE Object command i went thourgh the help and still ...
please send me examples or more inforamtion at

halbarwani@.hotmail.com

thank you

i need your help|||My Crystal installation has just stopped working so I can't be exact but as far as I recall you put any old picture onto the report (helps if it's the same size etc. ) and then right click the image. Choose the 2nd or 3rd option (graphic properties?) and go to the common tab, I think. There's a check box called something like graphic location - click on the formula box next to it and enter the formula there.

It's definately in the help.
Type Images into the help index, and I'm pretty sure it's a sub-heading called Dynamic that explains this.|||Got Crystal working again, so
1) Choose Insert, Picture menu option and browse for a picture. Put it on the report.
2) Right click the picture and choose Format Graphic
3) Go to the Picture tab
4) Click the formula box for Graphic Location
5) Enter your formula.|||thank you very very much Mr.JaganEllis

but still i can't see Graphic Location at the Picture tab,
i'm sorry to upset u but it's very importent for me to display report with employee photo.
i'm using (cr 9.2.0) with lotus notes database, is this version support??!

thank you and sorry again|||Sorry, I've no idea if CR 9.2.0 supports this, but if there's no option on your Picture tab (or any other tab within Format Graphic) and if you can't find it in the help then maybe it's not.

Anyone else using 9.2.0 confirm this, or able to suggest anything else?|||See if you find it here
http://support.businessobjects.com/

Problem importing data from an Access memo field into a SQL Server ntext field.

I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.
I'd appreciate any insights into what may be causing this problem, and what I can do about it.
Thanks in advance for any help!
255 is the default change the settings to accept more than 255, Access memo must be either image to store the file in Word or Text both can grow to 2gigs because Varchar 8000, Char 8000, NVarchar 4000 and NChar 4000 maybe too small for Access memo column. Hope this helps.|||

Thank you for your reply.
If you don't mind, what "settings" are you referring to and how do I change them? For example, are you talking about settings in Access, SQL Server and/or DTS? If I have to change settings via DTS, I've been using the DTS Wizard. Can I change the settings you're referring to with this Wizard?
I haven't been able to find any information on this problem, so I could use all the details you're able to provide.
Thanks again!

|||Try this link for a fix because it is a bug and keep the link because the site is run by a DTS expert. Hope this helps.
http://www.sqldts.com/?297|||

I finally was able to find the cause of my problem, and thought I'd put it out there for somebody who might suffer the same confusion one day. It turns out that Microsoft Query Analyzer will only display the first first 255 (or 256) characters of a field (this may be common knowledge - but I didn't know that). I had assumed that, since I couldn't see the entire result, DTS had truncated the data when importing from Microsoft Access. That wasn't the case. Everything was always fine.
Thanks again for the feedback!

problem importing csv delimited text file into a sql server 2005 table

I am using the Bulk Insert command and trying to import a CSV delimited text file into a table and I am having problems with the quote field delimiters ", " The command below works but it takes in all the "" quotes as well and the field delimiter comma , works only if the commas are the separators only. If I have a comma within a address field for example then the data gets imported into the wrong fields. What can I use to identify that the text qualifier is ". I don't see where I can use the bulk insert command to determine this. Is there another command that I can use or am I using this command incorrectly. I thank you in advance for any response or suggestion you may have.

BULK INSERT AdventureWorks.dbo.MbAddress

FROM 'a:\mbAddress.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR=',',

ROWTERMINATOR='\n',

CODEPAGE = '1252',

KEEPIDENTITY,

KEEPNULLS,

FIRSTROW=2)

Here is a sample ascii file I am importing as well you can see that 6330 has a extra comma in the address line.

"AddressAutoID","Memkey","Type","BadAddress","Address1","Address2","Address3","City","State","Zip","Foreign","CarrierRoute","Dpbc","County","CountyNo","ErrorCode","ChangeDate","UserID"
6317,26517,1,0,"1403 W. Kline Ave","","","MILWAUKEE","WI","53221","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6318,26225,1,0,"501 Dunford Dr","","","BURLINGTON","WI","53105","","",0.00,"RACINE",101,"",1/25/2006 0:00:00,"admin"
6319,20101,1,0,"2115 Cappaert Rd #35","","","MANITOWOC","WI","54220","","",0.00,"MANITOWOC",71,"",1/25/2006 0:00:00,"admin"
6320,23597,1,0,"728 Woodland Park Dr","","","DELAFIELD","WI","53018","","",0.00,"WAUKESHA",133,"",1/25/2006 0:00:00,"admin"
6321,23392,1,0,"7700 S. 51st St","","","FRANKLIN","WI","53132","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6322,26537,1,0,"W188 S6473 GOLD DRIVE","","","MUSKEGO","WI","53150","","",0.00,"WAUKESHA",133,"",1/26/2006 0:00:00,"admin"
6323,25953,1,0,"3509 N. Downer Ave","","","MILWAUKEE","WI","53211","","",0.00,"MILWAUKEE",79,"",1/26/2006 0:00:00,"admin"
6324,19866,1,0,"10080 E. Mountain View Lake Rd. #145","","","SCOTTSDALE","AZ","85258","","",0.00,"MARICOPA",13,"",1/27/2006 0:00:00,"admin"
6325,25893,1,0,"W129 N6889 Northfield Dr. Apt 114","","","MENOMONEE FALLS","WI","53051-0517","","",0.00,"WAUKESHA",133,"",1/27/2006 0:00:00,"admin"
6326,26569,1,0,"8402 64th Street","","","KENOSHA","WI","53142-7577","","",0.00,"KENOSHA",59,"",1/27/2006 0:00:00,"admin"
6327,24446,4,0,"83 Sweetbriar Br","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",1/30/2006 0:00:00,"admin"
6328,19547,1,0,"4359 MERCHANT AVENUE","","","SPRING HILL","FL","34608","","",0.00,"HERNANDO",53,"",2/8/2006 0:00:00,"admin"
6329,26524,1,0,"264 Lakeridge Drive","","","OCONOMOWOC","WI","53066","","",0.00,"WAUKESHA",133,"",2/10/2006 0:00:00,"admin"
6330,23967,1,0,"3423 HICKORY ST","100 Tangerine Blvd., Brownsville, TX 78521-4368","Texas Phone Number: 956-546-4279","SHEBOYGAN","WI","53081","","",0.00,"SHEBOYGAN",117,"",2/15/2006 0:00:00,"admin"
6331,25318,1,0,"3960 S. Prairie Hill Lane Unit 107","","","Greenfield","WI","53228","","",0.00,"MILWAUKEE",79,"",2/20/2006 0:00:00,"admin"
6332,24446,1,0,"83 Sweetbriar BR","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",2/21/2006 0:00:00,"admin"
6333,26135,1,0,"P.O. Box 8 127 Main Street","","","CASCO","WI","54205","","",0.00,"KEWAUNEE",61,"",2/21/2006 0:00:00,"admin"

Hi

You can do this by using a formatfile. You can define each field in a formatfile and specify a differend field terminator for each field.

For example:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="""," MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="""\n" MAX_LENGTH="30"/>

[....]

</RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLNVARCHAR"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT>

=== Edited by Hatzi74 @. 25 Apr 2006 2:49 PM UTC===
Edit2: You could also use the Data Import Wizzard... Select "Flatfile" as source, and then match the fielddelimiters for every field the way you need them to be...

For example... After a Text -> Number you would need --",-- as a delimiter if you go from text -> text you will need --","-- as a delimiter and when coming from number -> text you need --,"--

Hope this makes sense. Also the row delimiter could be adjusted to match --"\n-- if the last field is a text field. But this will leave the field "dirty" (It will include a tailing ")

|||

Thank you for your quick reply. I did try a non xml format file using the bcp command and it came out like the info below. However, It worked on importing but I still had the same problem with the commas as the delimiter. I wanted to add the quotes as a delimiter but I could not figure out how to do that in this type of file.

I will try a xml format this time but I am not sure by your instructions how to tell it that the file has quotes for the text qualifiers. I think you are saying I need a "," as a delimiter

Thank you so much for your time and effort. I am going to try it.

Most sincere,

Sarah

my format file below (.fmt type)

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "," 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "," 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "," 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "," 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "," 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "," 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "," 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "," 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 "," 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 "," 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 "," 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

|||

Try this:

3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",""" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 """," 5 Address1 SQL_Latin1_General_CP1_CI_AS

|||

I am trying that, but it does not seem to be working.

Sarah

|||

I think it is getting confused with the quotes because they seem to be reserved for the program or something. I get an error.

do you know the bcp command I need to get the xml format? I will try that as well.

Sarah

|||

Hi

I just wanted to thank you for your reply and that you somewhat directed me into the right direction.

I just wanted to let you know that I finally figured it out. I had to strip the " quotes out of the ascii file and replace them with | pipes and then I created the fmt (format file) and imported the data. Here is a copy of the fmt file just for your own information. I had to tell it exactly where the pipes started and ended for each field delimter. I tried setting up the format file to look for the quotes but it had a problem with that because " " were being used in the format file.

Anyways I got it and now I am having a problem accessing the A:\ drive, using the bulk insert command. sql server thinks I am accessing the actual servers A:\ and not my local drive. wow what a nightmare just to read a simple ascii file into a table. Unfortunatley the operator has to import data from floppy diskettes. I will repost this problem.

Anyways, thank you very much for your imput.

Most sincere,

Sarah Diane Reid

Sample Format File below:

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",|" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "|,|" 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "|,|" 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "|,|" 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "|,|" 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "|,|" 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "|,|" 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "|,|" 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "|," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 ",|" 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "|," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 ",|" 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "|," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 ",|" 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "|\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
mark
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark
|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!
|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J

>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>
|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
mark

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
markWhat medium are you using to transfer the data and from
what source.
Do the field sizes of the to and from match ?
J
>--Original Message--
>having a problem with one single field
>its coming up with the error message "data for source
column 2 ('general
>notes') is too large for the specified buffer size"
>how would i fix this ?
>thanks
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> > What medium are you using to transfer the data and from
> > what source.
> >
> > Do the field sizes of the to and from match ?
> >
> > J
> >
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J
>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>> What medium are you using to transfer the data and from
>> what source.
>> Do the field sizes of the to and from match ?
>> J
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
marksql

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
mark"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J

>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
mark

Problem formatting currency to different decimal places

I'm trying to format a field value to currency on a SQL report.

I need to allow for 4 decimal places to the right of the decimal if the field value contains those digits. (ie. $6.8484). Most of the time I will not have 4 places to the right of the decimal and would like to format according to the amout of decimal places. For example, If the value is 1 dollar, I don't want to format as $1.0000. I need a way to format the values according to the amount of digits to the right of the decimal. (ie. 1 dollar = $1.00, 3.453 = $3.453, 2.4453 = $2.4453)

Is there an easy way to do this using C2,C3, and C4? Please help.

When you say "SQL report", I suppose you mean you are using SQL Server Reporting Services.

In that case, you can just set the Format property of the textbox where you display the numeric field value to C4.

Note: if the query returns the value as string (instead of a numeric value), you will need to explicitly convert the value into its numeric representation first. You can do this in the query or directly in the report textbox by using the CDbl() function to convert a string to a double, e.g. =CDbl(Fields!SomeString.Value).

-- Robert

|||The C4 format will work for the four decimal place values but how do handle the three and two decimal situations. The report values should not display as $4.4200 if the initial value is $4.42. This also applies to three digit values( $4.421 should not display as $4.4210 on the report).|||I'm not sure if there are any standard functions to determine how many decimal places the value has (if not, you can write a custom function to do it). Suppose we have such a function called GetDecimalPlaces, you can set the format property to =IIF(GetDecimalPlaces(<fieldvalue>) <= 2, C2, GetDecimalPlaces(<fieldValue>) = 3, "C3", "C4").

Problem exporting to a .pdf

I have a column that contains a field I use for grouping.
The field prints in both the page header and footer, but not the detail section.

Hidden for the column is true.
Hidden for the textbox (field) is false
DataElementOutput for the textbox (field) is Output.
DataElementStyle for the textbox is ElementNormal ( I also tried AttributeNormal but no joy)

Everything works except when I export to acrobat, in which case the field does not print in the page header or footer.

If column is hidden, how will it ever work and print in any format?

What exactly is your problem?

|||

What version of Reporting Services are you using? I would expect the field to be present in PDF on SQL Server 2005 SP2.

Does exporting to TIFF work? Can you please file a bug on http://connect.microsoft.com and include a copy of your RDL?

Thank you.

|||Also noticed that the group field displays when the page is rendered on screen but does not print. Am using sp2. Is this a bug?|||>>What version of Reporting Services are you using?
SS 2005 SP2
>>Does exporting to TIFF work?
Nay

Problem DTSing into table with Identity field

Hello everyone,

I have a table X that has two columns (names have been changed to protect the guilty and innocent alike):
ID which is an identity column, and
Data which is a varchar.

I am trying to DTS data from a text file into this table. I want the identity column ID to auto-populate and each row of information from the file to be stored in the Data field.

I am creating the DTS package using the wizard in Enterprise Mgr. No matter what I try, I keep getting the following error:

Cannot insert the value NULL into column 'ID', table 'X'; column does not allow nulls. INSERT fails.

Any advice on what I need to do to get this working? It seems like such a simple thing and I'm getting very frustrated. :eek:

Thanks in advance.
CathyDTS sucks.

Are you using the wizard. Have you tried hitting the transform button and ignore on the source column.|||Does every record in the text file have an ID value? Sounds like there must be NULLs.

ddave|||Hello again,

Thrasymachus,
Yes, I'm using the wizard and setting the transformation to <ignore> the identity column. And yes, DTS does suck! :-)

dolfandave,
My file does not have values for the identity column, I'm trying to get them to be auto-generated by SQL Server, like they would if I did an insert into the table in isql.

Thanks,
Cathy|||did u try checking the "Enable Identity insert" checkbox in the Tranform Data Task properties?

Problem doing a count query using 2 tables

I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
adding date component to my earlier query can have query as shown in
following example.
ex:
create table emp_master(name varchar(25),city varchar(25), dt datetime)
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1', getdate())
insert into emp_master values('name2','city1', getdate())
insert into emp_master values('name3','city2', getdate() -1)
insert into emp_master values('name4','city2', getdate() -1)
insert into emp_master values('name5','city2', getdate() -2)
insert into emp_master values('name7','city4', getdate() -2)
insert into emp_master values('name8','city5', getdate() -3)
insert into emp_master values('name9','city5', getdate() -3)
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
where a.dt between '20041016' and '20041018 23:59:59'
group by b.state
If above does not satisfy your requirement post DDL/some sample records and
expected result-set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Problem doing a count query using 2 tables

I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
If i understand your requirement correctly, following example may help you.
Please post relevent table structure/sample records/expected result set to
understand your problem correctly.
ex:
create table emp_master(name varchar(25),city varchar(25))
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1')
insert into emp_master values('name2','city1')
insert into emp_master values('name3','city2')
insert into emp_master values('name4','city2')
insert into emp_master values('name5','city2')
insert into emp_master values('name7','city4')
insert into emp_master values('name8','city5')
insert into emp_master values('name9','city5')
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
group by b.state
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

problem creating varbinary(max) data type

I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

problem creating varbinary(max) data type

I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?

Thank you.

create table images (filename nvarchar(250) primary key,photo varbinary(max))
The T-SQL folks may be able to help here.|||

Sounds like compatibility level is set to 80(2000) instead of 90 (2005). You can get the level by right clicking on your database and going to properties\options page.

You can use the system procedure sp_dbcmptlevel to adjust your database. See BOL here for more detail. http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||Whitney:

Your suggestion worked. Thank you very much!

Problem converting to a date...

I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
Try:
select
convert (datetime, '20040301', 112)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
|||Populate a datetime column with the data...
ALTER TABLE YourTable ADD YourDatetimeCol DATETIME
GO
UPDATE YourTable SET YourDateTimeCol=CONVERT(DATETIME, YourNonDateTimeCol)
GO
Optionally, remove the old column, make the new column non-nullable, etc...
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>
>
|||hi atley,
if the format of datetime string is yyyymmdd then just run following query:
select convert(datetime,'20040301') dt
Vishal Parkar
vgparkar@.yahoo.co.in
|||Hi,
Have a look into the below code,
declare @.col1 varchar(20)
declare @.dt datetime
set @.col1 ='20040301'
select @.dt=convert(datetime,@.col1)
select @.dt
Tahnks
Hari
MCDBA
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>
>