Database level lock: insert

Showing posts with label insert. Show all posts

Thursday, March 29, 2012

database in SQL2K need help in upading/adding 2 memo columns

I have database in SQL2K. I have a memo column called memo1. I want to
update memo with "dicount % "+memo1.
I want to insert "discount %" in front of memo1.
This is going to be just one time shot. There are more 20,000 records need
to be updated.
I would appreciate if someone can help me.
I can run VB program and script so if anyone can help me to create something
in VB, if it is not possible in SQL2K,
I would really appreciate.
ThanksHi,
IN SQL 2000 you could write a simple Update statement
Update Tablename
set memo1 = 'discount % ' + memo1
This will update all the 20K records in a single shot.
Did I answered your question?
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:u9x4e.14059$Fh2.5711@.trnddc04...
>I have database in SQL2K. I have a memo column called memo1. I want to
>update memo with "dicount % "+memo1.
> I want to insert "discount %" in front of memo1.
> This is going to be just one time shot. There are more 20,000 records
> need to be updated.
> I would appreciate if someone can help me.
> I can run VB program and script so if anyone can help me to create
> something in VB, if it is not possible in SQL2K,
> I would really appreciate.
> Thanks
>|||Hari:
Thanks for your quick response.
I already tried this and I am getting data type error.
Thx
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
> Hi,
> IN SQL 2000 you could write a simple Update statement
> Update Tablename
> set memo1 = 'discount % ' + memo1
> This will update all the 20K records in a single shot.
> Did I answered your question?
> Thanks
> Hari
> SQL Server MVP
> "Mac" <mac@.hotmail.com> wrote in message
> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>|||Mac,
Looks like your datatype is TEXT. Inthat case you need to use UPDATETEXT
command to update.
See the below archive posts:-
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/11549ff4637eff45
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:3Jx4e.25741$k66.15806@.trnddc03...
> Hari:
> Thanks for your quick response.
> I already tried this and I am getting data type error.
> Thx
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
>> Hi,
>> IN SQL 2000 you could write a simple Update statement
>> Update Tablename
>> set memo1 = 'discount % ' + memo1
>> This will update all the 20K records in a single shot.
>> Did I answered your question?
>> Thanks
>> Hari
>> SQL Server MVP
>> "Mac" <mac@.hotmail.com> wrote in message
>> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>>
>

Wednesday, March 21, 2012

Database error number 105

I am using ColdFusion to construct a SQL Server insert statement, the statement appears as expected in the debugger and if I cut and paste it into the Query Analyzer it works as expected. Yet from my code it produces the following error:

Database error number 105
[Macromedia][SQLServer JDBC Driver][SQLServer]Unclosed quotation mark before the character string 'kellyc'.

It almost seems as if somehow ColdFusion is introducing the problem. The server is MX, I similarly constructed Insert Statements that are working.

Here is the code...

From my Action Page...Its the first query that is failing.

<cftransaction action="begin">
<cftry>

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCase"
CaseName = "#form.CaseName#"
Version = "#form.CaseVersion#"
Desc = "#form.CaseDescription#"
Start = "#form.CaseStartDate#"
End = "#form.CaseEndDate#">

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "qGetMaxCase"
CaseName = "#form.CaseName#">

<cfmodule template = "../queries/CaseMgmtQueries.cfm"
queryname = "AddCaseStatus"
CaseID = "#qGetMaxCase.NewCaseID#"
Status = "#form.CaseStatus#">

<cfset HaveError = False>
<cfcatch type="Database">
<cftransaction action="rollback">
<cfset HaveError = True>
<cf_dbErrorDisplay errno = #cfcatch.NativeErrorCode# detail = #cfcatch.Detail#>
</cfcatch>

</cftry>
</cftransaction>

And then from my queries page...

<cfif queryname is "AddCase">
<CFQUERY NAME="AddCase" DATASOURCE=#variables.DSN#>
Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('#PreserveSingleQuotes(attributes.CaseName)#',
'#Attributes.Desc#',
'#Attributes.Version#',
'#Attributes.Start#',
'#Attributes.End#')
</CFQUERY>
</cfif>

This is producing a query that in the debug dump looks like...

Insert into CaseInfo
(CaseName, CaseDescription, CaseVersion, CaseStartDate, CaseEndDate)
Values ('kellyc', 'test', '1.1', '12/10/2003', '12/11/2003')

Which inserts correctly using Query Analyzer.

The main thing that is different about this insert from other inserts that are functioning in the program is the use of transactin processing.

Any help would be most appreciated!

KellyCKellyC,

I'm afraid I don't know a thing about ColdFusion, but here is what I would try: start up SQL Profiler and run a trace on your application. You can probably use the default settings for the trace unless it is a production server and in heavy use (in which case it's a pain to find the specific transaction you're looking for).

You can then identify the format of the T-SQL Statement (or SP) that is being used and test changes in your code to make corrections.

Regards,

hmscott|||i'm betting it's a coldfusion problem

see http://www.dbforums.com/showthread.php?threadid=974731

kelly, please don't cross-post

rudy|||Sorry Rudy,

I didn't realize it was bad form, since not everyone is subsribed to the same forums, and I don't really know if the problem is ColdFusion or SQL related at this point.

hmscott,

I have sent in a request to the DBA to try running the Profiler... I am now in the waiting zone. If it turns up something I will post that here.
Thanks for the suggestion.

KellyC|||Sorry for the crosspost again, but thought I would post my resolution here as well...

OK, as it often turns out, the problem is not always what it seems from the lovely and informative error messages. The unclosed quote was actually in the second query in the transaction. This came to light for me when I started playing with trying to submit a string with a single quote in it and noticed ColdFusion MX does not display the text of an unsucessful query in the debug. (Personally I consider this a deficiency of MX in case any Macromedia folks want my 2 cents).

So... long story short I realized it wasn't the insert query that was throwing the error and fixed the offending query and it now works like a charm.

Thanks again,
KellyC|||Nice of you to post the resolution. This is not meant to be an "I told you so," but it definitely points out an instance where SQL Profiler could have helped to narrow down the cause. Congrats for solving the problem.

Regards,

hmscott

Database error code: 515

We have MS SQL 2000 server. Recently, on our database application we gate
"Databse error code: 515" It says: Cannot insert the value NULL into cilumn
"contact_id", table name, column doesn't allow nulls. INSERT fails. No
changes made to databse. ...
A recent action on this table was, we exported the contact table to Excel,
updated, and imported from Excel.
Would you please advise us what the problem could be and how we can address
this.
Thanks,
Yigedeb.
CBI Inc
Hello,
Just see if you insert NULL into Contact_ID field. Please take a look into
table schema using SP_HELP <TableName> and verify the data you
are trying to insert..
Thanks
Hari
"Yigedeb" <Yigedeb@.discussions.microsoft.com> wrote in message
news:2FBF9D1C-F344-48AA-8172-0FF3E00A00BA@.microsoft.com...
> We have MS SQL 2000 server. Recently, on our database application we gate
> "Databse error code: 515" It says: Cannot insert the value NULL into
> cilumn
> "contact_id", table name, column doesn't allow nulls. INSERT fails. No
> changes made to databse. ...
> A recent action on this table was, we exported the contact table to Excel,
> updated, and imported from Excel.
> Would you please advise us what the problem could be and how we can
> address
> this.
> Thanks,
> Yigedeb.
> CBI

Thursday, March 29, 2012

database in SQL2K need help in upading/adding 2 memo columns

database in SQL2K need help in upading/adding 2 memo columns

database in SQL2K need help in upading/adding 2 memo columns

Wednesday, March 21, 2012

Database error number 105

Database error code: 515

Database level lock

Blog Archive

About Me