install Office ifilter on SQL Server 2005

Hi there
I might be being dense, but I can't seem to get the Full Text Search
service working for office type documents. The service works OK for
text and RTF files, so I know that the Full Text indexing service is
there, but just doesn't seem to be working for Office docs -
specifially Word .doc files.
Digging a little deeper, it seems that the relevant ifilter isn't
installed. When I run
select * from sys.fulltext_document_types
I get the list of document types that can be indexed and searched by
SQL server 2005. For .the .rtf entry, I get a path of
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\msfte.dll
for the ifilter, version 12.0.6214.0 which is where I would expect it
to be (ie I can see the msfte.dll file in the Binn directory).
For the .doc entry, I get a path of
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\offfilt.dll
for the ifilter, but no version number, and when I look at the Binn
directory, the file is not there.
Is there any way I can install the office ifilter on SQL server 2005?
Note that this is a database server so does not have IIS, Sharepoint,
Desktop search or any other search mechanism installed. I would like
the entire office doc search facility to be confined to the SQL server
2005 installation if possible - and from the path specified in the
fulltext_document_types view I take it that it should work this way.
Any help / pointers would be very gratefully received.
Thanks is advance
Jeremy
Hi Jeremy. I see the same thing on my machine, but it does work on my
machine.
Basically you will find the iFilter in %windir%\system32 which is likely
c:\Windows\System32. I am not sure why it refers to this location. You will
find that the persistent handler associated with the .doc extension points
here.
Can you check your Word Docs to make sure that they are not saved in the
fast save format? the Office iFilter does not understand this format.
Also download filtdump from the platform sdk and run your word docs through
this to make sure the iFilter understands them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeremy Holland" <jeremy.holland@.konetic.com> wrote in message
news:1165402032.796856.43690@.l12g2000cwl.googlegro ups.com...
> Hi there
> I might be being dense, but I can't seem to get the Full Text Search
> service working for office type documents. The service works OK for
> text and RTF files, so I know that the Full Text indexing service is
> there, but just doesn't seem to be working for Office docs -
> specifially Word .doc files.
> Digging a little deeper, it seems that the relevant ifilter isn't
> installed. When I run
> select * from sys.fulltext_document_types
> I get the list of document types that can be indexed and searched by
> SQL server 2005. For .the .rtf entry, I get a path of
> c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\msfte.dll
> for the ifilter, version 12.0.6214.0 which is where I would expect it
> to be (ie I can see the msfte.dll file in the Binn directory).
> For the .doc entry, I get a path of
> c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\offfilt.dll
> for the ifilter, but no version number, and when I look at the Binn
> directory, the file is not there.
> Is there any way I can install the office ifilter on SQL server 2005?
> Note that this is a database server so does not have IIS, Sharepoint,
> Desktop search or any other search mechanism installed. I would like
> the entire office doc search facility to be confined to the SQL server
> 2005 installation if possible - and from the path specified in the
> fulltext_document_types view I take it that it should work this way.
> Any help / pointers would be very gratefully received.
> Thanks is advance
> Jeremy
>
|||Hi Hilary
Thanks for your reply - I'll try as you suggest.
Jeremy
|||Hi Hilary
Thanks for that - I registered the OS ifilters and ignored that view as
you suggested.
It seems to work fine now with Word docs - thanks a lot for that.
Jeremy
Jeremy Holland wrote:

> Hi Hilary
> Thanks for your reply - I'll try as you suggest.
> Jeremy

Install an mdf file

Can anyone give me the command line text to install an mdf file? I have a file called contacts.mdf from a Microsoft book and need to install it to MSDE in order to complete the examples in the book.

Thanks in advance.NOt sure if this will work in MSDE, but try sp_attach_db

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp

Actually, it looks like it will work. Look at step 2 here:

http://support.microsoft.com/kb/q231923/|||Thanks, Chrisrock. I finally got it to work using sp_attach_db.

INSIDER - KNOWLEDGE NEUTRALIZING!

INSIDER - KNOWLEDGE NEUTRALIZING!!
(First Copy this Text in your editor)
Translated Text (sorry if the Text is not in good grammer).
I have earned over 40,000 Euros after participate in this program.
Before short, I have myself in the following mails - list registered.
Later I got an E-MAIL, that said me, how and where I get IMMEDIATELY a money
in opening - gift of more than 20 Euros. In addition I earn WITHOUT EDITIONS
at a system with more than 4 steps. It is obvious that such information is
not told simply cost-free at all world. The mails - list with which I had
registered me, informed me per E-MAIL about this new business.
Now I have you only one of the many money - salaries named, become you over
that informed after registration.
The advantage in THIS mail - list - registration is:
- For the registrations in the E-mail-list I get money
- After the registration receive I knowledge over money and would earn in
addition, more than I in the mail - list - registrations would receive.
I was read had to beginning also sceptical, but as I the letter, knew I that
I get also something good for my money, and have invests above all, MORE
than I.
ONLY if I get something, the other can earn also, is that the logical
answer.
Can if you earn in an investment of 12 Euros / 12 U.S. dollars or 12 British
pounds (sterlings) of thousand and also hundred cable envoy, you not would
transmit yourself information that bring you yet more money?
Therefore it is entirely clear that the applicant sends also the information
REALLY at the registered, unlike in so-called " mail - lists ", where one
waits long on that (usually in vain).
Swell through the further money - is certain, that THIS program functions,
or would become you you in comparison with 100,000 with only 12 Euros /
satisfied give 12 U.S. dollars or 12 British pounds (sterlings)?
I got, above all, more in my investment of 12 Euros already in short time
than that hundredfold thanks to which mail - information.
If you decide to register you in the mail - list, would follow the
directions:
You become now part at the mail order business:
Would follow the directions:
1 stepped:
If you are yet no Paypal-user, a user COST-FREE - account (Premium - basis -
account) opens now in Paypal. Paypal is with more than 96 million users and
with representation in more than 56 countries of one of the most certain
money - transfers - business worldwide.
click up: https://www.paypal.com
(should that be left not active, would copy it please into your browser)
Stepped 2:
Would register you in the " mail - list".
Send 12 Euro / 12 US - Dollar or 12 British Pounds (sterling) at the FIRST
mail-address per mail
- For use-purpose you write: " Service "
- For E-mail-reference you write: " Mail - list "
How you can send money, you find on the web page of Paypal simply declared.
The present list:
(#1) bestwaybook@.yahoo.de
(#2) luxusbook@.yahoo.de
(#3) ecashmail@.yahoo.de
Through the fast payment per enamel and the reception, it lasts only few
Would meet until you get the information per E-MAIL.
As soon as your registration arrived in the receiver, you get further
information / insider - knowledge. Would keep in mind always, ONLY if you
also salary, earn other with.
COMMENT:
Would follow steps 3 FIRST THEN AFTER you received the information /
insiders - knowledge.
Only if you have the information / insiders - knowledge, you can transmit
this also.
Here it does not concern simply only mail - earn collections, but rather
around CORRECT money.
Stepped 3:
Now that would paint see #1 of the list that you above and back the other
names after.
The address at the position #2 you write on position #1
The address at the position #3 you write on position #2
After that you write YOUR address on position #3.
Was ready on an exciting and interesting type, to earn money.Your sister will earn12 Euros from me the next time I'm at Oktoberfest.
;-)
"INSIDER - KNOWLEDGE!!" <new-power@.invest.net> wrote in message
news:e0iIG50UGHA.1688@.TK2MSFTNGP11.phx.gbl...
> INSIDER - KNOWLEDGE NEUTRALIZING!!
> (First Copy this Text in your editor)
> Translated Text (sorry if the Text is not in good grammer).
>
> I have earned over 40,000 Euros after participate in this program.
> Before short, I have myself in the following mails - list registered.
> Later I got an E-MAIL, that said me, how and where I get IMMEDIATELY a
> money in opening - gift of more than 20 Euros. In addition I earn WITHOUT
> EDITIONS at a system with more than 4 steps. It is obvious that such
> information is not told simply cost-free at all world. The mails - list
> with which I had registered me, informed me per E-MAIL about this new
> business.
> Now I have you only one of the many money - salaries named, become you
> over that informed after registration.
> The advantage in THIS mail - list - registration is:
> - For the registrations in the E-mail-list I get money
> - After the registration receive I knowledge over money and would earn in
> addition, more than I in the mail - list - registrations would receive.
> I was read had to beginning also sceptical, but as I the letter, knew I
> that I get also something good for my money, and have invests above all,
> MORE than I.
> ONLY if I get something, the other can earn also, is that the logical
> answer.
> Can if you earn in an investment of 12 Euros / 12 U.S. dollars or 12
> British pounds (sterlings) of thousand and also hundred cable envoy, you
> not would transmit yourself information that bring you yet more money?
> Therefore it is entirely clear that the applicant sends also the
> information REALLY at the registered, unlike in so-called " mail - lists
> ", where one waits long on that (usually in vain).
> Swell through the further money - is certain, that THIS program functions,
> or would become you you in comparison with 100,000 with only 12 Euros /
> satisfied give 12 U.S. dollars or 12 British pounds (sterlings)?
> I got, above all, more in my investment of 12 Euros already in short time
> than that hundredfold thanks to which mail - information.
> If you decide to register you in the mail - list, would follow the
> directions:
> You become now part at the mail order business:
> Would follow the directions:
> 1 stepped:
> If you are yet no Paypal-user, a user COST-FREE - account (Premium -
> basis - account) opens now in Paypal. Paypal is with more than 96 million
> users and with representation in more than 56 countries of one of the most
> certain money - transfers - business worldwide.
> click up: https://www.paypal.com
> (should that be left not active, would copy it please into your browser)
> Stepped 2:
> Would register you in the " mail - list".
> Send 12 Euro / 12 US - Dollar or 12 British Pounds (sterling) at the FIRST
> mail-address per mail
> - For use-purpose you write: " Service "
> - For E-mail-reference you write: " Mail - list "
> How you can send money, you find on the web page of Paypal simply
> declared.
> The present list:
> (#1) bestwaybook@.yahoo.de
> (#2) luxusbook@.yahoo.de
> (#3) ecashmail@.yahoo.de
> Through the fast payment per enamel and the reception, it lasts only few
> Would meet until you get the information per E-MAIL.
> As soon as your registration arrived in the receiver, you get further
> information / insider - knowledge. Would keep in mind always, ONLY if you
> also salary, earn other with.
> COMMENT:
> Would follow steps 3 FIRST THEN AFTER you received the information /
> insiders - knowledge.
> Only if you have the information / insiders - knowledge, you can transmit
> this also.
> Here it does not concern simply only mail - earn collections, but rather
> around CORRECT money.
> Stepped 3:
> Now that would paint see #1 of the list that you above and back the other
> names after.
> The address at the position #2 you write on position #1
> The address at the position #3 you write on position #2
> After that you write YOUR address on position #3.
> Was ready on an exciting and interesting type, to earn money.
>
>

INSERTs given me the BLUES

cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _

cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"

cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"

cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"

cstring = cstring + "service_request, store_number_senditem, register_number, street_address"

cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"

cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"

cstring = cstring + "notes, client_number)"

cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _

this is the error is get, but i did the same thing on a select statement and it works fine...do i need to add something to the string or what i am kinda confused and help would be great.....

Operator '+' is not defined for types 'String' and 'System.Windows.Forms.TextBox'.

It should probably be "txtcenter.Text" and not "txtcenter". It is the second control you use to concatenate the value with.

|||

Well, this is realy not a SQL Server question, but...

my guess is that:

"%','%" + txtcenter + "%'" & _

txtcenter is a textbox and you must do something more like:"%','%" + txtcenter.text + "%'" & _

Also, you seem to have ignored what everyone told you earlier about injection attacts and text entry because you arent using quotename (or Pull_Quotes from your example code.)

Louis

|||

This is a function i wrote that takes care of the quotes, but what do you mean about injection attacts? and where can i read about what an attacts is? i have seemed to miss that part of SQL do you have any links for that topic, and this is how i have the INSERT now will it work this way or was i better off keeping it the other way?

Public Function PrepareStr(ByVal strValue As String) As String

If strValue.Trim() = "" Then

Return "NULL"

Else

Return "'" & strValue.Trim() & "'"

End If

End Function

cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"

cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"

cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"

cstring = cstring + "service_request, store_number_senditem, register_number, street_address"

cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"

cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"

cstring = cstring + "notes, client_number)"

cstring = cstring + "VALUES PrepareStr(txtWatchID.Text),PrepareStr(txtCenter.Text),PrepareStr(txtenvelope.Text),PrepareStr(txtSenditem.Text)" & _

cstring = cstring + "PrepareStr(dtDateofPur.Text), PrepareStr(txtTrans.Text), PrepareStr(txtfname.Text), PrepareStr(txtlname.Text),PrepareStr(txtprdcode.Text)" & _

cstring = cstring + "PrepareStr(txtvalue.Text), PrepareStr(datefail.Text), PrepareStr(dtshipdate.Text), PrepareStr(txtregisternum.Text), "

|||

check here:

http://www.sommarskog.se/dynamic_sql.html#Security2

Amazingly good coverage of the topic and then look at the quote name explanation. Better than I could explain it here :)

|||

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from string "INSERT INTO tblNEW (watch_id, se" to type 'Boolean' is not valid.

Where does the Boolean value come in i thought it was a string....my type in the DB is not boolean where is this coming from any help?

I get this exception when i am trying to excute this Query and i even added my Pull_Quotes Function....here is the query.....

cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"

cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"

cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"

cstring = cstring + "service_request, store_number_senditem, register_number, street_address"

cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"

cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"

cstring = cstring + "notes, client_number)"

cstring = cstring + "VALUES Pull_Quotes(txtWatchID.Text),Pull_Quotes(txtCenter.Text),Pull_Quotes(txtenvelope.Text),Pull_Quotes(txtSenditem.Text)" & _

cstring = cstring + "Pull_Quotes(dtDateofPur.Text), Pull_Quotes(txtTrans.Text), Pull_Quotes(txtfname.Text), Pull_Quotes(txtlname.Text),Pull_Quotes(txtprdcode.Text)" & _

cstring = cstring + "Pull_Quotes(txtvalue.Text), Pull_Quotes(datefail.Text), Pull_Quotes(dtshipdate.Text), Pull_Quotes(txtregisternum.Text)" & _

cstring = cstring + "Pull_Quotes(txtaddress.Text), Pull_Quotes(txtcity.Text),(txtstate.Text), (txtzip.Text), Pull_Qoutes(txtareacode.Text), Pull_Quotes(txtphonenum.Text)" & _

cstring = cstring + "Pull_Quotes(txtproductdesc.Text), Pull_Quotes(txtworkbdone.Text), Pull_Quotes(txtauthnumber),(txtlabor.Text), (txtPart.Text)" & _

cstring = cstring + " Pull_Quotes(txttaxcost.Text), Pull_Quotes(txtTotal.Text), Pull_Quotes(txtNotes.Text), Pull_Quotes(txtClient.Text)"

|||

Just like last time, for help in this group, we need to see the SQL statement that is being sent to the client:

INSERT into tblNEW.. etc. For eerrors like: 'System.InvalidCastException' There isn't much we can do.

I can guess that the & _ is probably not right in this statement:

string = cstring + "Pull_Quotes(txtproductdesc.Text), Pull_Quotes(txtworkbdone.Text), Pull_Quotes(txtauthnumber),(txtlabor.Text), (txtPart.Text)" & _

cstring = cstring + " Pull_Quotes(txttaxcost.Text), Pull_Quotes(txtTotal.Text), Pull_Quotes(txtNotes.Text), Pull_Quotes(txtClient.Text)"

But really no idea. If you can do the messageBox trick again and post the INSERT statement the you will get useful help.

|||

Ok i got rid of the exception i was using to many line continuations thats why i got that exception but here is what the Insert is sending to the server

INSERT INTO tblNEW(watch_id, service_center_num, repair_envelope, store_numberdate_purchase, transaction_num, cust_fname, cust_lname, product_codevalue_watch, failure_date, service_date, failure_code, repair_codeservice_request, store_number_senditem, register_number, street_addresscity, state, zip_code, area_code, phone_num, product_desc, service_centerwork_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_costnotes, client_number, service_ship)VALUES '10000269%','%23432%','%234123%','%2341%''%3/22/2006%','%2412%', '%Demetrius%', '%Powers%','%2341%''%25,000.00%', '%3/22/2006%', '%3/22/2006%', '%34234%''%43534dfggsdg%', '%sdgsdg%','%fg%', '%42342%','%453%', '%435345%''%fgsdfgsd%','%2343%', '%gsdgsdg%', '%345345%',(txtlabor.Text), (txtPart.Text)'%534534%', '%45345%', '%sdgsdgdfgfger%', '%4234%', '%3/22/2006%'

I know some of it is garble but you know the saying Fuctionality first then make is pretty but this is the statement...and it does not work is it something with the ((txtlabor.Text or txtPart.Text)) should i add the Pull_Quotes function for all of them or what....kinda lost any help would be fantastic! thanks

|||

I do not think you should have all these % when you insert a column into the table.

It makes sense when searching for sometime inside a column.

insert into TestTable (sometext) values ('this is a test')

To find a row in TestTable that contains 'test' you do

select * from TestTable where sometext like '%test%'

|||

Ok, the first thing that is wrong is:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '10000269%'.

This is because you need parenthesis around the stuff in the values clause.

Second:

You need a comma between these parts:

'%2341%''%25,000.00%'

Finally:

for the txtLabor.text stuff you need to look at how your quotes are in your statement. You probably have something like:

Pull_Quotes("(txtClient.Text)")

Or something along those lines, but that is a VB question, and I don't know.

|||Yeah, just completely missed that fact. I was so focused on the syntax that I missed the values being inserted :)|||

Well here is what is being sent to the server from my insert statement

WatchTracker

INSERT INTO tblNEW(watch_id, service_center_num, repair_envelope, store_numberdate_purchase, transaction_num, cust_fname, cust_lname, product_codevalue_watch, failure_date, service_date, failure_code, repair_codeservice_request, store_number_senditem, register_number, street_addresscity, state, zip_code, area_code, phone_num, product_desc, service_centerwork_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_costnotes, client_number, service_ship)VALUES '0010000269','345234','342523','3453','3/22/2006','53534', 'Demetrius', 'Powers','45345','25,000.00', '3/22/2006', '3/22/2006', '453245','45324gdsfgsgfd', 'dfgsdgsdg','rt', '43545','454', '43534534','sdfasfdasa','34544', 'sdfasfasd', '345345','43543', '3453','345', '3453', 'ghkweklfklasdfklneklrnkl', '435345', '3/22/2006'

OK

I am still not getting those results i need, is there a tool that comes with the SQL Server 2000 management studio? Or is the code syntax still incomplete?

|||

Those results? Are you getting an error? Yes, I know you are:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '0010000269'.

At the very least you need parens, and you have no comma between repair_code and service_request or store_number_send and item or street_address and city or service_center and work_to_bdone:

INSERT INTO tblNEW(watch_id, service_center_num, repair_envelope, store_numberdate_purchase, transaction_num, cust_fname, cust_lname, product_codevalue_watch, failure_date, service_date, failure_code, repair_code, service_request, store_number_send,item, register_number, street_address, city, state, zip_code, area_code, phone_num, product_desc, service_centerwork_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_costnotes, client_number, service_ship)
VALUES ('0010000269','345234','342523','3453','3/22/2006','53534', 'Demetrius', 'Powers','45345','25,000.00', '3/22/2006', '3/22/2006', '453245','45324gdsfgsgfd', 'dfgsdgsdg','rt', '43545','454', '43534534','sdfasfdasa','34544', 'sdfasfasd', '345345','43543', '3453','345', '3453', 'ghkweklfklasdfklneklrnkl', '435345', '3/22/2006')

Once you get your statement into this form use the query tool to execute the statement until you get a result that works. Fix your code and then test again. Wrap the statement in a transaction and the database won't be affected:

BEGIN TRANSACTION

test statement

ROLLBACK TRANSACTION

|||Why are you performing a direct INSERT from the client code? It has security implications and in most cases a bad practice. Create a stored procedure that performs the insert and then call it from the client.|||

Thanks here is the final query that worked for me thanks for the help!! It was great!!!

INSERT INTO tblNEW " & _

"VALUES (" & _

"'" & Pull_Quotes(txtWatchID.Text) & _

"','" & Pull_Quotes(txtcenter.Text) & _

"','" & Pull_Quotes(txtenvelope.Text) & _

"','" & Pull_Quotes(txtSenditem.Text) & _

"','" & Pull_Quotes(dtDateofPur.Text) & _

"','" & Pull_Quotes(txtTrans.Text) & _

"','" & Pull_Quotes(txtfname.Text) & _

"','" & Pull_Quotes(txtlname.Text) & _

"','" & Pull_Quotes(txtprdcode.Text) & _

"','" & Pull_Quotes(txtValue.Text) & _

"','" & Pull_Quotes(datefail.Text) & _

"','" & Pull_Quotes(dtServiceRecieve.Text) & _

"','" & Pull_Quotes(txtfailurecode.Text) & _

"','" & Pull_Quotes(txtrepaircode.Text) & _

"','" & Pull_Quotes(txtservice.Text) & _

"','" & Pull_Quotes(txtSenditem.Text) & _

"','" & Pull_Quotes(txtregisternum.Text) & _

"','" & Pull_Quotes(txtaddress.Text) & _

"','" & Pull_Quotes(txtcity.Text) & _

"','" & Pull_Quotes(txtstate.Text) & _

"','" & Pull_Quotes(txtzip.Text) & _

"','" & Pull_Quotes(txtareacode.Text) & _

"','" & Pull_Quotes(txtphonenum.Text) & _

"','" & Pull_Quotes(txtproductdesc.Text) & _

"','" & Pull_Quotes(dtshipdate.Text) & _

"','" & Pull_Quotes(txtworkbdone.Text) & _

"','" & Pull_Quotes(txtauthnumber.Text) & _

"'," & (txtLabor.Text) & _

"," & (txtPart.Text) & _

"," & (txtTaxcost.Text) & _

"," & (txtTotal.Text) & _

",'" & Pull_Quotes(txtNotes.Text) & _

"','" & Pull_Quotes(txtClient.Text) & _

"','" & Pull_Quotes(dtshipdate.Text) & "')"

Insert-Retrive-Update in Text datatype.

Can anyone guide how to retrieve, update and insert data in column having 'Text' datatype? I am a newbie and i havent' done this yet.

Thanks in advance.

If you are using SQL Server 2005 consider using a VARCHAR(MAX) or NVARCHAR(MAX) instead of a text datatype. Look up the VARCHAR(MAX) and TEXT datatypes in books online.|||

No. i am using sql 2000 and i wanted to know the method of retriving and updating data usign text datatypes.

thanksk,

|||Use it in the same manner that you would use a normal varchar column. The only difference is that when using it in a where condition you can't use the '=' operator. You have to use 'like'. Other than that inserting, updating and retrieving is exactly the same.

Best regards,
Sami Samir|||

Please see the links below for some samples. For text/ntext/image value manipulation, you can use UPDATETEXT/WRITETEXT/READTEXT or SELECT/INSERT/UPDATE statements depending on your needs. There are functions like DATALENGTH and PATINDEX that will work with these data types too.

http://www.umachandar.com/technical/SQL6x70Scripts/Main53.htm

http://www.umachandar.com/technical/SQL6x70Scripts/Main63.htm

http://www.umachandar.com/technical/SQL6x70Scripts/Main12.htm

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind Regards
For double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting with DTS in IDENTITY table

Hi

I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.

How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?

Bjrnyes.

you just need to not map anything to the primary key column. In the wizard, you need to hit the transform button and make and under the column mappings make sure ignore is selected. If you are using the DTS desginer, you need to get rid of the mapping on your transform data task under the transformations task.

Inserting Unicode data

Hi.

We have a sql server db that we need to store Unicode text in. The fields are of the type nvarchar, ntext and nchar. Our solution uses both Oracle and SqlServer as a backing database. In Oracle there is a connection string switch "Unicode=True" that fixes the problem. Is there something similar in SqlServer? Since the db layer is generic we'ed like to avoid using a N' prefix on text strings in query statements.

Hi Kim,

As far as I can see, when the fields types are set to such as NVarChar and the update parameters have been set to the corresponding type, the data will be updated as Unicode in SQL Server. When updating the database, the N' prefix will be added automatically by ADO.NET.

That means you don't need to add anything additional to achieve this.

Are you getting some problem when updating the data in the way I mentioned? If so, please let me know the problem. Thanks!

|||

Hi Kevin,

Thank you for the response. The problem is that this is a system that's gone into production. The code is not written by me and I'm sorry to say it doesn't use command parameters for transfering variables. I guess the fix will have to wait until the next upgrade.

Inserting Text into table replaces apostrophes with ?

When we insert text into field in a table, SQL SERVER apparently
replaces apostrophes with question marks -- is there a way to not have
this occur? We don't have this happen with the mySQL databases that
we also support.

Much help appreciated.I've encountered users coying and pasting text from MS Word and this exact
issue occurred. My problem was the MS 'smart quotes' so I had to replace
them prior to inserting.

Rob

"TNR" <stage7@.tgs.fr> wrote in message
news:bg3efc$jv5$1@.news-reader3.wanadoo.fr...
> Try to double the apostrophes.
> "kalbee" <kalbee@.einsof.com> wrote in message
> news:45082167.0307280703.29b9d428@.posting.google.c om...
> > When we insert text into field in a table, SQL SERVER apparently
> > replaces apostrophes with question marks -- is there a way to not have
> > this occur? We don't have this happen with the mySQL databases that
> > we also support.
> > Much help appreciated.

Inserting Text Into SQL 2005 Database Padded With Spaces

I have a Detailsview with Insert and Update options connected to a SQL 2005 table with templated textboxes for input. The textboxes have maxlength set to the number of characters in each respective field in the SQL 2005 table. When text is inserted it gets padded with spaces if all the field length is not used. When you try to edit the field the cursor does not move because of the padded spaces. The End key must be pressed to find the end of the string and the padded space removed before adding edited text. I am working in VB.net. If I check the field in SQL Studio Express is shows the text I typed plus blank space for the remainder of the field.

My question is how can I add text to the textboxes without the padded spaces being added when the maxlength of the field is not used?

if you look at the datatype of the data columns in the sql database, you will most likely find that it it set tochar.
the char datatype automatically pads with spaces to completely fill the allocated space.

If you prefer not to get these spaces, then you would want to use thevarchar datatype when setting up your table in sql server.
the varchar datatype will not add the padding.

Note: if you change the datatype of a column in an existing database, you will likely need to trim the existing data as it will still contain the padding. however, new data added to a varchar column will not get the padding.

|||

Thank you that worked like a charm. Three questions before I close this out:

1) How does the type nchar work? It comes up always as the default? Which type is the best?

2. Should I expect any suprises in my code where in some places I have embedded Rtrim statements?

3. When binding a variable to the table is there any format { } string that can be used to achieve the no padding result? I saw in a forum some place about the F parameter to reduce spaces but I did not know how to implement it?

|||

1) nchar is for storing fixed length unicode character data.
char will pad the data if created with ansi_padding set on. nchar always pads. http://msdn2.microsoft.com/en-us/library/ms175055.aspx
Which is best depends on your needs. As you've noticed, char/nchar might require to you to trim off whitespace.
For most text columns, I prefer varchar (or nvarchar for data i need to store as unicode).

2) Trimming the data with RTrim is unaffected by your use of char/varchar - other than it becomes unnecessary if your data is not padded. Note that the String type contains a TrimEnd method (among others) which i prefer over the trim methods from the visualbasic library.

3) I'm not aware of a standard format code that would trim the padding.

Here's an interesting thread:http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=7978

inserting text after a certain record of a recordset

What I'm trying to accomplish is to add some text after the 5th
dispalyed record of a recrod set, I tried using absolute position and
the following:
<%
if rs.absoluteposition = 5 then
Response.Write "Testing"
end if
%>
I'm probably missing something or there is some better way to do this,
thanks for any assistance in advance.counter = 0
do while not rs.eof
counter = counter + 1
if counter = 5 then
response.write "testing"
end if
rs.movenext
loop
<dabootleg@.gmail.com> wrote in message
news:1134328540.390884.113180@.g47g2000cwa.googlegroups.com...
> What I'm trying to accomplish is to add some text after the 5th
> dispalyed record of a recrod set, I tried using absolute position and
> the following:
> <%
> if rs.absoluteposition = 5 then
> Response.Write "Testing"
> end if
> %>
> I'm probably missing something or there is some better way to do this,
> thanks for any assistance in advance.
>

inserting text > 65535

hi,
i want to insert text of size > 65535 in a text column of sql-server. I'm presently using mcp command to do this, but this utility doesn't allow these long files, and work well upto 65 kb files.
is there a way out.
thanks in advanceThis error had been noted back in Version 4.2 of SQL, see technet article
BUG: BCP Cannot Copy Text/Image Data > 64K from SQL Server (Q111921) (http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q111921)

Try setting the packet size on BCP (flag -a) to the max which is 65535|||That doesn't work... I set the size 100000. When I inserted small files that were loaded smoothly. But for bigger files it says max packaet size is 65k.

I rephrase my problem again:::::
=====================
I'm working with sql server.
I need to load large xml files in the databse, fragment it into the
tables.
(1) I don't know a way to load file with the help of script that can load
text value of > 65k. I'm presently doing with the help of "mcp" command.
But this has got size limitation. So how do u load big file in some of the
"text" field of a table.
(2) I need to do fragmentation of this file. But I cannot declare text
type variable in the procedure. I am able to parse files of upto 8000
characters, but how do I do it if I have a document > 8000 char stored in
a "text" field. I just don't know how to call sp_xml_preparedocument with
a text attribute. I think u cannot say something like
sp_xml_preparedocument @.idoc out,(select textfield from doctable)
where idoc and textfield are integer and text respectively.

INSERTING TEXT

Hello,
Can someone tell me how to insert text into a table containing a TEXT field?
How to update?
I would like to create a stored procedure that take a text parameter and
inserts it into a new column in my table. Then do the same stored procedure
but this time to update the TEXT field.
HELP!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can someone tell me how to insert text into a table containing a TEXT
field?
> How to update?
> I would like to create a stored procedure that take a text parameter and
> inserts it into a new column in my table. Then do the same stored
procedure
> but this time to update the TEXT field.
> HELP!
> Yama
>|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...
> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long
text
> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.
As
> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even
manipulate
> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track
them
> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and
replace
> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > Can someone tell me how to insert text into a table containing a TEXT
> field?
> > How to update?
> >
> > I would like to create a stored procedure that take a text parameter and
> > inserts it into a new column in my table. Then do the same stored
> procedure
> > but this time to update the TEXT field.
> >
> > HELP!
> >
> > Yama
> >
> >
>|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.
> I thought you had to use the WRITETEXT command?
I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...
> > Are you sure the INSERT will work even with more than 8000 characters?
> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
> > I thought you had to use the WRITETEXT command?
> I've never used the WRITETEXT command in production systems.
www.aspfaq.com
> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

INSERTING TEXT

Hello,
Can someone tell me how to insert text into a table containing a TEXT field?
How to update?
I would like to create a stored procedure that take a text parameter and
inserts it into a new column in my table. Then do the same stored procedure
but this time to update the TEXT field.
HELP!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hello,
> Can someone tell me how to insert text into a table containing a TEXT

---

field?

quote:

---

> How to update?
> I would like to create a stored procedure that take a text parameter and
> inserts it into a new column in my table. Then do the same stored

---

procedure

quote:

---

> but this time to update the TEXT field.
> HELP!
> Yama
>

---

|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...

quote:

---

> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long

---

text

quote:

---

> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.

---

As

quote:

---

> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even

---

manipulate

quote:

---

> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track

---

them

quote:

---

> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and

---

replace

quote:

---

> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> field?
> procedure
>

---

|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.

quote:

---

> I thought you had to use the WRITETEXT command?

---

I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
>
> I've never used the WRITETEXT command in production systems.

---

www.aspfaq.com

quote:

---

> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

---

Inserting Text

Could someone help me, please?

How do I insert text, something like a header, before the result of the query presents itself?

I want the text to appear before the result. Not like an alias, that I know how to do.

Thanks.

SELECT 'This is my Header Message'

SELECT colList FROM MyTable

OR

SELECT
'This is My Header Message',
Col1,
Col2,
etc
FROM MyTable

Inserting special characters

Hi
I have a web page where users insert some comments into a text area. users
are able to insert any character into this area. When they submit the
character ' and " causes problems. How can i allow these characters to be
inserted into the database( sql server 2000)?
I am using Java server page to process and insert the comments to the
database.
thanksHi,
Not quite sure what you are trying exactly. Just try using NVARCHAR data
type and see if it helps.
Thanks
Hari
SQL Server MVP
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||This would be less of a problem if you were using stored procedures. Since
you are probably sending query strings from the web page to the SQL Server,
you may be very vulnerable to SQL Injection attacks. (Write to me off line
and I will give you more information about your vulnerability.)
For the single quote, if you must store them, have the application double
(two single quotes) them before sending to SQL Server. The double quote
shouldn't be a problem -please confirm how you are experiencing the problem.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||HI,
I am trying to insert a ' charcerter. However the SQL syntax for inserting
nvarchar or strings use the ' character to determine the begining and the
end.
How do i go about inserting a ' character into the database?
"Hari Prasad" wrote:
> Hi,
> Not quite sure what you are trying exactly. Just try using NVARCHAR data
> type and see if it helps.
> Thanks
> Hari
> SQL Server MVP
> "panda" <panda@.discussions.microsoft.com> wrote in message
> news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> > Hi
> >
> > I have a web page where users insert some comments into a text area. users
> > are able to insert any character into this area. When they submit the
> > character ' and " causes problems. How can i allow these characters to be
> > inserted into the database( sql server 2000)?
> >
> > I am using Java server page to process and insert the comments to the
> > database.
> >
> > thanks
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_05F8_01C6C63E.8066A380
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Use two of them, for example:
CREATE TABLE #MyTable
( RowID int IDENTITY
, MyStringValue varchar(100)
)
INSERT INTO #MyTable VALUES ('"This isn''t so obvious, is it?", said =Bill O''Shea to Terrance O''Donald.')
SELECT MyStringValue FROM #MyTable
DROP TABLE #MyTable
Hope that this helps...
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message =news:26E49E21-4F45-4F5E-ABD2-BD5E5D8771C4@.microsoft.com...
> HI,
> > I am trying to insert a ' charcerter. However the SQL syntax for =inserting > nvarchar or strings use the ' character to determine the begining and =the > end. > > How do i go about inserting a ' character into the database?
> > "Hari Prasad" wrote:
> >> Hi,
>> >> Not quite sure what you are trying exactly. Just try using NVARCHAR =data >> type and see if it helps.
>> >> Thanks
>> Hari
>> SQL Server MVP
>> >> "panda" <panda@.discussions.microsoft.com> wrote in message >> news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
>> > Hi
>> >
>> > I have a web page where users insert some comments into a text =area. users
>> > are able to insert any character into this area. When they submit =the
>> > character ' and " causes problems. How can i allow these characters =to be
>> > inserted into the database( sql server 2000)?
>> >
>> > I am using Java server page to process and insert the comments to =the
>> > database.
>> >
>> > thanks >> >> --=_NextPart_000_05F8_01C6C63E.8066A380
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Use two of them, for =example:
CREATE TABLE =#MyTable ( =RowID int IDENTITY , MyStringValue varchar(100) )
INSERT INTO #MyTable VALUES ('"This isn''t so obvious, is it?", =said Bill O''Shea to Terrance O''Donald.')
SELECT MyStringValue FROM #MyTable
DROP TABLE #MyTable
Hope that this helps...
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"panda" wrote in message news:26E49E21-4F45-4F5E-ABD2-BD5E5D8771C4@.microsoft.com...> =HI,> > I am trying to insert a ' charcerter. However the SQL syntax =for inserting > nvarchar or strings use the ' character to determine =the begining and the > end. > > How do i go about =inserting a ' character into the database?> > "Hari Prasad" =wrote:> > Hi,> > Not quite sure what you are =trying exactly. Just try using NVARCHAR data > type and see if it helps.> > Thanks> Hari> SQL =Server MVP> > "panda" wrote in message > news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...> > Hi> =>> > I have a web page where users insert some comments into a text area. users> > are able to insert any character into this area. =When they submit the> > character ' and " causes problems. How =can i allow these characters to be> > inserted into the =database( sql server 2000)?> >> > I am using Java server =page to process and insert the comments to the> > =database.> >> > thanks > > >

---

--=_NextPart_000_05F8_01C6C63E.8066A380--

Inserting special characters

Hi
I have a web page where users insert some comments into a text area. users
are able to insert any character into this area. When they submit the
character ' and " causes problems. How can i allow these characters to be
inserted into the database( sql server 2000)?
I am using Java server page to process and insert the comments to the
database.
thanksHi,
Not quite sure what you are trying exactly. Just try using NVARCHAR data
type and see if it helps.
Thanks
Hari
SQL Server MVP
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||This would be less of a problem if you were using stored procedures. Since
you are probably sending query strings from the web page to the SQL Server,
you may be very vulnerable to SQL Injection attacks. (Write to me off line
and I will give you more information about your vulnerability.)
For the single quote, if you must store them, have the application double
(two single quotes) them before sending to SQL Server. The double quote
shouldn't be a problem -please confirm how you are experiencing the problem.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message
news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
> Hi
> I have a web page where users insert some comments into a text area. users
> are able to insert any character into this area. When they submit the
> character ' and " causes problems. How can i allow these characters to be
> inserted into the database( sql server 2000)?
> I am using Java server page to process and insert the comments to the
> database.
> thanks|||HI,
I am trying to insert a ' charcerter. However the SQL syntax for inserting
nvarchar or strings use the ' character to determine the begining and the
end.
How do i go about inserting a ' character into the database?
"Hari Prasad" wrote:

> Hi,
> Not quite sure what you are trying exactly. Just try using NVARCHAR data
> type and see if it helps.
> Thanks
> Hari
> SQL Server MVP
> "panda" <panda@.discussions.microsoft.com> wrote in message
> news:CF0A3B21-D97F-44C6-800B-88B3B54E501C@.microsoft.com...
>
>|||Use two of them, for example:
CREATE TABLE #MyTable
( RowID int IDENTITY
, MyStringValue varchar(100)
)
INSERT INTO #MyTable VALUES ('"This isn''t so obvious, is it?", said Bill O'
'Shea to Terrance O''Donald.')
SELECT MyStringValue
FROM #MyTable
DROP TABLE #MyTable
Hope that this helps...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"panda" <panda@.discussions.microsoft.com> wrote in message news:26E49E21-4F45-4F5E-ABD2-BD5E
5D8771C4@.microsoft.com...[vbcol=seagreen]
> HI,
>
> I am trying to insert a ' charcerter. However the SQL syntax for inserting
> nvarchar or strings use the ' character to determine the begining and the
> end.
>
> How do i go about inserting a ' character into the database?
>
> "Hari Prasad" wrote:
>

Inserting records and displaying them

Before I start driving myself nuts, I'd like to make sure my approach is correct.

I want to create a simple job posting board.
I have a text boxes for company name, email, job title, and job description, and a submit button.
I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"

When someone fills out the fields and clicks submit, it will insert the new records.

So,
1. Is this the correct approach so far?

2. What is the best way to display the job listings? A grid view?

3. At submit time, how can I include that day's date?

4. How can I get the records in the database to delete after 90 days?

Thanks.

1. If it did what you want, it's correct to you

2. You can try SqlDataSource+GridView, see

3. You can add a datetime column (e.g. CreateDate) to the table, whose default value is getdate() so that when a new record being inserted you don't need to specify date for the column:

ALTER TABLE mytable ADD CreateDate DATETIME DEFAULT GetDate()

4. You can use such command to delete records older than 90 days:

DELETE FROM mytable WHERE DATEDIFF(d,CreateDate,GETDATE())>90