Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
Vinoth
It really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>
|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ? So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
>
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards :)
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>> Hi,
>> I have Instead of Delete Trigger in my Table. When i delete any row
>> from the table it shows the No of Rows affected ex:- 1 Row affected
>> But when i look into the table the Deleted Row is still Present.
>> My Question is If there is Instead of Delete Trigger then is it not
>> possible to delete row from Original table. and if i want to delete the
>> row using Instead of trigger what i have to do?
>> If anybody knows the solution Please let me know?
>>
>> Thanks,
>> Vinoth
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 711 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dksql

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothHi ,
You cannot delete rows from a table which has a instead of delete trigger
configured.
Though, We can put delete statement for the table inside instead of trigger
but we need to keep RECURSIVE_TRIGGERS database option set correctly. This D
B
option will not cause trigger to fire again.
But still I am not sure why do you want to delete row from a trigger which
has instead of delete trigger configured.
--
Vishal Khajuria
9886170165
IBM Bangalore
"vinoth@.gsdindia.com" wrote:

> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in inserting value in view

Hi There,

i have created one view on an exsisting table, but when i am inserting value in it, it is giving the following error msg:

Derived table 'View Name' is not updatable because a column of the derived table is derived or constant.

Thanx

Is this related to SSIS? Well the error is coming straight from SQL Server itself, and is fairly clear to me.

You cannot update a column that is not really a column. So what columns are being updated and can you honestly say they are unadulterated columns, and have not been derived or manipulated in any way before being returned from the view.?

If you want a specific answer then post the UPDATE statement and a the DDL (CREATE VIEW...) for the view.

problem in inserting an integer into a database table

i am using visual web developer 2005 and SQL Express 2005 with VB as the code behind

i want to insert an integer into my database table and this is my code

i = i + 1
productionstatus.UpdateCommand = "UPDATE productionprogressbar SET completedprocess = i WHERE order_id = 10"
productionstatus.Update()

"i" is declared at the top of the page like this

Partial Class production_processlist
Inherits System.Web.UI.Page
Dim i As Integer = 0

when i run the program i get the error

input string is not in the correct format

what is wrong in my code ?

please help me

you are treating your variable "i" as a literal character

try it like this:

"UPDATE productionprogressbar SET completedprocess =" & i.ToString & "WHERE order_id = 10"

problem in inserting a record whose values are of date and time format.

hello,

I am trying to insert date and time into my table.

insert into <table_name> values('12/12/2006','12:23:04');

but it displays error at " ; "

can anyone help me to figure out the problem

Thanks a lot in advance.

Regards,

Sweety

What happens if you remove ";"?

|||

hi,

thx for responding..i figured out the problem and i solved it..

bye

Sweety

Problem in Insert in Table

Dear Friends
I have a table Which is giving problem for the Insert.
Insert ERROR:
Column Name or number of supplied values does not match table Definations
The same table is in replication. I have updated our Servicepack to 4 in
last w.
Please suggest how i can solve the problem.
Thanks and best regards
ShaileshYou have given less number of columns but included values for all the
columns
Post the query you used
Madhivanan|||hi sailesh
there might be difference between the columns and values passed in the
insert query..
eg:
INSERT INTO <TABLE>(ID, Name) VALUES (1, 'Chandra', 28)
please post the query so that we can give a better solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***

Problem in Insert

Dear Friends,
I am having one table which is giving problem while inserting the records
which is as follows:oops! u missed to explain ut problem
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***

problem in inner join

Hey,

I want to update the price in the items table to be equal to the corrosponding price in the books table but my problem is in the "on stmt", i cant set items.id=books.id because in the items table the id is saved as nvarcharex:b1234 where b identifies a book and in the books table it is saved as int as follows 1234 and this is the only primary key ! so how can i solve it and how can i write the query to update the price in the items table??

Thank you

Hiba

You can try to use some string functions to do so. Fo r example you can try RIGHT(items.id,4)=books.id if both ids' column is nvarchar column. Or

SUBSTRING( items.id, 2,LEN( items.id))=CONVERT(NVARCHAR,books.id)) if the books id is not nvarchar.

|||

Actually you can use RIGHT or SUBSTRING to get you data like RIGHT(book,4) if you are sure that your ID will be no longer then 4 digits or

if you know that you will always have 1 character to identify book you can use syntax like

where items.ID= LEFT(items.ID,1)+books.id

actually it is the same like using

where items.ID= SUBSTRING(items.ID,0,1)+books.id

Because Left just call substring to do its work.

Thanks

JPazgier

|||

hi.

are u familiar with adobc?

how can i use dataadapter using adobc?

my code is:

Dim sqldataadapter As New SqlClient.SqlDataAdapter(stringQuery, MyConn)
Dim ds As New DataSet()
Dim foundrow, temprow As DataRow
Dim ds2 As New DataSet
Dim temp_data_table As New DataTable
sqldataadapter.Fill(ds, "TT0001")

Dim sqldataadapter2 As New SqlClient.SqlDataAdapter(stringQuery2, MyConn)
sqldataadapter2.Fill(ds2, "tempo_db")
Dim date_ctr As Integer

but it doesnt accept sqlClient....what can u suggest?

|||

Hey, Anyways thank you but I sloved it in this way:

update

items

set

items.weight=books.weight,items.profit=books.profit,items.alldiscount=books.alldiscount

from

(booksjoin itemson'b'+cast(books.idAsnvarchar(12))=items.id)

where

items.center='lb'and items.ordernum>47000

Thanx

Hiba

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 Function ?

Create FUNCTION FUNCTION_NAT

(

@.F_BRANCH_CODE CHAR,

@.F_COUNTRY CHAR,

@.F_CLIENT_VALUE CHAR

)

RETURNS TABLE

AS

RETURN

(

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = @.F_BRANCH_CODE

AND

COUNTRY = @.F_COUNTRY

AND

CLIENT_VALUE = @.F_CLIENT_VALUE

)

This is my function . when i run it as follows

select * from FUNCTION_NAT('450','British','BRI')

i did not get any value it is empty .

But when i run this query

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = '450'

AND

COUNTRY = 'British'

AND

CLIENT_VALUE = 'BRI'

it works fine . What is the problem in my function .

My knee-jerk reaction is that this might be a problem with the type definitions of the function parameters; hold on and I will try to verify. Look at this:

Code Snippet

alter FUNCTION FUNCTION_NAT
(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)
RETURNS TABLE
AS
RETURN
( select @.f_branch_code as branch_code,
@.f_country as country,
@.f_client_value as client_value
)

go

select * from function_nat('450','British','BRI')

/*
branch_code country client_value
-- -
4 B B
*/

You need to alter your type definitions from this

Code Snippet

(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)

to something like this:

Code Snippet

(
@.F_BRANCH_CODE CHAR(xx),

@.F_COUNTRY CHAR(yy),
@.F_CLIENT_VALUE CHAR(zz)
)

where xx, yy, and zz are the maximum number of characters that will be passed through each argument.

|||

Kent - i think you've hit the nail on the head.

From BOL: When n is not specified in a data definition or variable declaration statement, the default length is 1

SQL will not throw an error but merely cut the string off at the length. From the look of at least one of your variables, you may be better off with the VARCHAR datatype.

HTH!

sql

problem in freetext search in MS SQL Server

hi
i have problem in fulltex search, when i search any
english word it give me perfect result.
In my table i have define fulltext search on a column
name content_html, data type of column is ntext it
stores htlm content.
now when i search any unicode character(gujarati word)
it shows me content which not contain the search
value.
if anyone know the solution
pls help urgent
What word breaker are you using? You really should store your data in
columns of the image data type and have a document type column containing
the value of htm or html.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mahesh" <mahesh.anjani@.gmail.com> wrote in message
news:1141816487.482238.60840@.j33g2000cwa.googlegro ups.com...
> hi
> i have problem in fulltex search, when i search any
> english word it give me perfect result.
> In my table i have define fulltext search on a column
> name content_html, data type of column is ntext it
> stores htlm content.
> now when i search any unicode character(gujarati word)
> it shows me content which not contain the search
> value.
> if anyone know the solution
> pls help urgent
>

problem in expression of a check constraint

Hi all,

I wrote the expression below in a table's check constraint

(CustomerTypeID = 0 and ContentSiteID is null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null ) or

( CustomerTypeID = 1 and ContentSiteID is not null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null )

It saved it ok, but when I re-opened and viewed the expression again it was converted to:

([CustomerTypeID] = 0 and [ContentSiteID] is null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null

[CustomerTypeID] = 1 and [ContentSiteID] is not null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null )

This is a different logic because the OR is not prioritised above the AND.

Any solution or should I move my condition to the table's Update and Insert triggers?

Thanks!

Ofer

You are safe because of the precedence of the operators. A reading from the Books On-Line:

When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic (and bitwise) operators are handled before logical operators.

If you swap the Ands and the OR in your expression, you will see that the evaluation order is preserved with parens.

Here is a quick sample with the results shown.

drop table tester

go

create table tester(

one int null,

two int null,

three varchar(10) null

)

go

alter table tester

Add Constraint AndOrAnd Check ( (one = 1 and three is null ) or (two = 0 and three is not null ) )

alter table tester

Add Constraint OrAndOr Check ( (one = 1 or three is null ) and (two = 0 or three is not null ) )

exec sp_helptext AndOrAnd

-->([one] = 1 and [three] is null or [two] = 0 and [three] is not null)

exec sp_helptext OrAndOr

-->(([one] = 1 or [three] is null) and ([two] = 0 or [three] is not null))|||

Thanks for your reply but it doesn't answer this problem.

I need the check constraint to have a codition of ( (x=1 and y is null) or (x=2 and y is not null) )

Replacing the or-s with and-s does a totally different check.

The SQL Server just drops the inner paranthesis, again changing the condition.

Is there a way to check my condition ?

Thanks.

|||

Sorry that my response was not clear. Your expression is being evaluated in the way you desire. It happens because any of the ANDs are being evaluated first. The server is removing the parenthesis precisely because they are redundant to the evaluation.

The example of swapping the conditions was only to show that the server will preserve the parenthesis if they are contrary to normal order of evaluation.

sql

Problem in DTS

Hi ,
I have a transaction table in my ERP Database.
The table size is 3 GB means (50,000,00)*6 records
Now i am transfering to Access database but it give error
exceeds maximium number of rows.
Can any one tell me how can i tarnsfer the data.I dont want to transfer
data to bcp file.
i have sp3 installed on my machine.
from
KillerWhat is creating the error, Access or the tool you are using for the
transfer? DTS should be able to do this transfer for you without any
problems.
--Brian
(Please reply to the newsgroups only.)
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>|||This is not a SQL Server issue but an Access one. According to
http://support.microsoft.com/defaul...b;en-us;302524, Access has a
2GB limitation.
Hope this helps.
Dan Guzman
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>

Problem in DTS

Hi ,
I have a transaction table in my ERP Database.
The table size is 3 GB means (50,000,00)*6 records
Now i am transfering to Access database but it give error
exceeds maximium number of rows.
Can any one tell me how can i tarnsfer the data.I dont want to transfer
data to bcp file.
i have sp3 installed on my machine.
from
Killer
What is creating the error, Access or the tool you are using for the
transfer? DTS should be able to do this transfer for you without any
problems.
--Brian
(Please reply to the newsgroups only.)
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegro ups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>
|||This is not a SQL Server issue but an Access one. According to
http://support.microsoft.com/default...;en-us;302524, Access has a
2GB limitation.
Hope this helps.
Dan Guzman
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegro ups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>
sql

Problem in DTS

Hi ,
I have a transaction table in my ERP Database.
The table size is 3 GB means (50,000,00)*6 records
Now i am transfering to Access database but it give error
exceeds maximium number of rows.
Can any one tell me how can i tarnsfer the data.I dont want to transfer
data to bcp file.
i have sp3 installed on my machine.
from
KillerWhat is creating the error, Access or the tool you are using for the
transfer? DTS should be able to do this transfer for you without any
problems.
--
--Brian
(Please reply to the newsgroups only.)
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>|||This is not a SQL Server issue but an Access one. According to
http://support.microsoft.com/default.aspx?scid=kb;en-us;302524, Access has a
2GB limitation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1125672105.203993.37080@.g49g2000cwa.googlegroups.com...
> Hi ,
> I have a transaction table in my ERP Database.
> The table size is 3 GB means (50,000,00)*6 records
> Now i am transfering to Access database but it give error
> exceeds maximium number of rows.
> Can any one tell me how can i tarnsfer the data.I dont want to transfer
> data to bcp file.
> i have sp3 installed on my machine.
> from
> Killer
>

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 deleting rows in table

hi there!

I have encounter the problem when trying to delete the rows..with the error message

http://i5.photobucket.com/albums/y151/kangalert/untitled.jpg

http://i5.photobucket.com/albums/y151/kangalert/1231234.jpg

with contain no primary key, and no dependency

What task/component are you using to do the deletion?

Please provide as much information as you can, in a concise manner, when posting issues.

-Jamie

|||

i just click on the row/rows i want to delete in SQL Server..

But it wont let me update and delete the row!

|||

Is this at all related to SSIS?

If not then you are in the wrong forum. Try the Tools or T-SQL forum.

-Jamie

sql

Problem in Creating Trigger Sql Server 2000

Friends,
I have problem to creating a trigger.
In a trigger, i have check, if both inserted and deleted table has
row, then Check containt in both tables are same or not like this
Select Count(*) from inserted i , deleted d where i.cola = d.cola
and i.colb = d.colb
I have done it in some dynamically way, which can work for my all
tables, As code follows.
########
My Problem is, when following trigger execute, its return inserted
and deleted table does not exits.
#######
Update tm_empmastR1
Set empstatus = 'A'
Alter Trigger tr_tm_empmastR1 on tm_empmastR1
For Insert, Update, Delete
As
Declare @.ColName as VarChar(255),
@.Cmd as VarChar(8000)
Declare TCur Cursor For
Select SC.name
From sysobjects SO
Inner join syscolumns SC On SC.id = SO.id
And SO.name = 'tm_empmastR1'
order by colid
Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
Open TCur
Fetch Next From TCur Into @.ColName
While (@.@.Fetch_Status = 0)
Begin
Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
Fetch Next From TCur Into @.ColName
End
Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
Print (@.Cmd)
Exec (@.Cmd)
Close TCur
Deallocate TCur
#####
Although return Sql is runing perfectly.
#####
Thanks in advance for any reply.
Rahul Verma
FutureSoftDynamically executed SQL has its own context and doesn't have access to thes
e tables. I encourage
you to skip the dynamic SQL if at all possible. If not, you can have the out
er code populate a temp
table and then the inner (dynamic) code can access that temp table.
I would use the system tables/catalog views to generate the trigger code, fo
r instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <verma.career@.gmail.com> wrote in message
news:1172035114.793535.273980@.v33g2000cwv.googlegroups.com...
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>|||Hi Rahul
"Rahul" wrote:

> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>
The dynamic SQL is in a different scope to the trigger, therefore the it
doesn't know about the inserted and deleted tables.
You should check @.@.ROWCOUNT as the first statement in the trigger to make
sure that there has been some changes.
All the cursor is doing is allowing you to avoid having to change the
trigger when your tables structure changes, on a mature system it will be
redundant and could potentially impact performance and could even cause a
bottleneck.
John|||On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Rahul
>
>
> "Rahul" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
> The dynamic SQL is in a different scope to the trigger, therefore the it
> doesn't know about the inserted and deleted tables.
> You should check @.@.ROWCOUNT as the first statement in the trigger to make
> sure that there has been some changes.
> All the cursor is doing is allowing you to avoid having to change the
> trigger when your tables structure changes, on a mature system it will be
> redundant and could potentially impact performance and could even cause a
> bottleneck.
> John- Hide quoted text -
> - Show quoted text -
Hi All
Thanks for reply.
Tibor Karaszi
i am not getting your idea,
trigger is called when we call update/insert/delete command
automatically and just after these statements.
So, we have not enough time to create and use a temp table.
If is it possible or any other idea, please mail me, Because we
require to create this type of modification with 100'th triggers, As i
am creating log.
Jhon,
This is right, if table structure change, cursor may be fail, that's
why i am using sysobject and syscolumns tables to getting all columns.
And when i use print statement, the return value of print statement
is absolutely correct and its working.
The @.@.rowcount only return no of rows in last query, its ok but
before this statement, i have to match all these fields in both table.
####
Actually my logic is
if both inserted and deleted tables contain row (ie row is updated)
and changes are actually made (ie changes must be done in any field in
a table) then insert this row in a log file with a particular flag.
else if only inserted table contain row then it insert with different
flag
same as with deleted.
####
if you have any idea to done it, please reply me
i have more than 1000 tables.
Thanks
Rahul|||Hi Rahul
"Rahul" wrote:

> On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
> Hi All
> Thanks for reply.
> Tibor Karaszi
> i am not getting your idea,
> trigger is called when we call update/insert/delete command
> automatically and just after these statements.
> So, we have not enough time to create and use a temp table.
> If is it possible or any other idea, please mail me, Because we
> require to create this type of modification with 100'th triggers, As i
> am creating log.
>
> Jhon,
> This is right, if table structure change, cursor may be fail, that's
> why i am using sysobject and syscolumns tables to getting all columns.
> And when i use print statement, the return value of print statement
> is absolutely correct and its working.
> The @.@.rowcount only return no of rows in last query, its ok but
> before this statement, i have to match all these fields in both table.
> ####
> Actually my logic is
> if both inserted and deleted tables contain row (ie row is updated)
> and changes are actually made (ie changes must be done in any field in
> a table) then insert this row in a log file with a particular flag.
> else if only inserted table contain row then it insert with different
> flag
> same as with deleted.
> ####
> if you have any idea to done it, please reply me
> i have more than 1000 tables.
> Thanks
> Rahul
I think you have misunderstood both replies. I believe that Tibor is
suggesting that you generate your create trigger statements using the cursor
s
and you will end up with "hardcoded" columns for each table.
The checking of @.@.ROWCOUNT at the very begining of the trigger will mean
that you don't execute the code when not rows are changed, this will cut out
the unnecessary actions of the trigger.
Alter Trigger tr_tm_empmastR1 on tm_empmastR1 For Insert, Update, Delete
As
IF @.@.ROWCOUNT = 0 RETURN
...
John