Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Avoid MSG 8152?


Hi, all!

Having a table with a varchar(10) column, I try to insert a row where the data for that column is more than 10 characters long. No surprise, I get a MSG 8152 error.

What I want is to get the overlong data clipped to the maximum column width, and no error. Is that possible?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Error 8152 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Can't seem to get around this problem?

Error:
Server: Msg 8152, Level 16, State 9, Procedure Statement_proc, Line 97
String or binary data would be truncated.
The statement has been terminated.

Exec Statement_Proc '2002-04-01 00:00:00.000','2002-07-03 23:59:59.000','PRLL-0000000020'

Alter Procedure Statement_Proc

@Startdate As datetime,
@Enddate As datetime,
@Customer_no As char(15)

As
Begin

declare @loanno as char(15)
declare @transaction_date as datetime
declare @transaction_type as char(3)
declare @reference as varchar(20)
declare @notes as varchar(255)
declare @transaction_amount as decimal (9,2)
declare @transaction_description as varchar(50)

declare @debit_amount as decimal (9,2)
declare @credit_amount as decimal (9,2)
declare @counter as int
declare @balance as decimal (9,2)
declare @user_changed as char(8)

Declare c2 CURSOR FOR
SELECT distinct(loan_no)
FROM loan
where customer_no = @Customer_No
ORDER BY loan_no

OPEN c2
FETCH NEXT FROM c2 INTO @loanno

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

declare c1 CURSOR FOR
SELECT transaction_record.loan_no,
transaction_record.transaction_date,
transaction_record.transaction_type,
transaction_record.reference_no,
transaction_record.notes,
transaction_record.transaction_amount,
transaction_type.[description]
FROM transaction_record
inner join transaction_type
on transaction_type.transaction_type = transaction_record.transaction_type
where loan_no = @loanno and transaction_Date between @startdate and @enddate and transaction_amount <> 0
ORDER BY transaction_date

OPEN c1
FETCH NEXT FROM c1
INTO @loanno,
@transaction_date,
@transaction_type,
@reference,
@notes,
@transaction_amount,
@transaction_description

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
If (@transaction_amount < 0)
Begin
set @credit_amount = @transaction_amount
set @debit_amount = 0
End
Else
Begin
set @debit_amount = @transaction_amount
set @credit_amount = 0
End

If (@counter = 0)
Begin
set @balance = @transaction_amount
End
Else
Begin
set @balance = @balance + @transaction_amount
End

insert into Statement (customer_no,
loan_no,
transaction_date,
transaction_type,
transaction_description,
reference,
notes,
debit_amount,
credit_amount,
balance,
user_changed,
transaction_no)

Values (@Customer_No,
@loanno,
@transaction_date,
@transaction_type,
@transaction_description,
@reference,
@notes,
@debit_amount,
@credit_amount,
@balance,
@user_changed,
@counter)

set @counter = @counter+1

FETCH NEXT FROM c1
INTO
@loanno,
@transaction_date,
@transaction_type,
@reference,
@notes,
@transaction_amount,
@transaction_description

End
CLOSE c1
DEALLOCATE c1

FETCH NEXT FROM c2 INTO @loanno
End
CLOSE c2
DEALLOCATE c2
END

View Replies !   View Related
Error Handling - 8152
First Question:
I have a script that can run successfully via the Query Analyzer even though it reports a few errors with error number 8152

"Server: Msg 8152, Level 16, State 2, Procedure IndexDensity, Line 85
String or binary data would be truncated.
The statement has been terminated."

Why does the script continue to finish in the Query Analyzer but fail immediately when it encounters the same error as a scheduled Job?



Second Question:
The serverity is 16 (user managed) so I would like to simply skip over the problem code/row and contiue with the next row to be modified.

How can I handle the error and yet have the script continue when it is scheduled as a job?


Additional Background:
I would like to do something like this:
If this line...

"SELECT @scan_dnsty = convert(decimal(5,2),RTRIM(substring(density_str, charindex('%',density_str) - 6, 6)))
FROM #Density_Out_Tbl"

...generates the error, then simply (skip it and move on to the next row)Fetch the next row.

I hope that is clear,
Thank you in advance.

View Replies !   View Related
Error 8152 / 22001
Hi,

I have an incredibly annoying problem, for which I can find very little help. If anyone could assist I'd be most grateful.

When updating a table varchar(1000) column via isql with a value greater than 256 characters I receive no error. When performing the same query via an ODBC connection, I receive an ODBC error 22001. When performing the same query but with the update value of less than 256 characters, there's no problem.

My search for help has led me to believe it may be something to with ANSI_DEFAULTS? but thats as far as I get, as for the remedy?!

Access 2000 has a bug where text in columns of character value > 256 is truncated and wrap when displayed in forms / reports, and this is rectified with Office SP2. I'm wondering if I have a similar problem.

Any help / comments greatly appreciated.

Stevo.

View Replies !   View Related
Help : Server: Msg 8152, Level 16, State 9, Line 1
When I try to update one table from another I get the following error
message, Does anyone know what may be the cause of the error :

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Thanks

KUL

View Replies !   View Related
Server: Msg 8152, Level 16, State 6, Line 196
I am experiencing some frustration in regards to parsing an email that I need to work with.
(I don't have control over the format of the email I am receiving)

Any help would be appreciated.
Thank you
Jake

Here is the error I get, when I use the SQL Query Analyzer to process my Stored Procedure. (Not every email gets this error, just some, others process just fine.)

String or binary data would be truncated.
The statement has been terminated.
Stored Procedure: eResponse.dbo.spParser
Return Code = -6

My Code is as follows:

CREATE PROCEDURE spParser
@_id nvarchar(50)
AS
--assumes the email is no longer than 4000 characters. if it might be longer, bump this number up.
-- Common Varriables
DECLARE @body nvarchar(4000)
DECLARE @i int
DECLARE @phone nvarchar(50)
DECLARE @phonestart int
DECLARE @phoneend int
DECLARE @email nvarchar(50)
DECLARE @emailstart int
DECLARE @emailend int
DECLARE @comments nvarchar(3000)
DECLARE @commentsstart int
DECLARE @commentsend int
DECLARE @OrigID varchar(50)
-- Jose Ole Variables
DECLARE @firstname nvarchar(50)
DECLARE @firstnamestart int
DECLARE @firstnameend int
DECLARE @lastname nvarchar(50)
DECLARE @lastnamestart int
DECLARE @lastnameend int
DECLARE @address1 nvarchar(50)
DECLARE @address1start int
DECLARE @address1end int
DECLARE @address2 nvarchar(50)
DECLARE @address2start int
DECLARE @address2end int
DECLARE @city nvarchar(50)
DECLARE @citystart int
DECLARE @cityend int
DECLARE @state nvarchar(5)
DECLARE @statestart int
DECLARE @stateend int
DECLARE @zip nvarchar(15)
DECLARE @zipstart int
DECLARE @zipend int
DECLARE @country nvarchar(50)
DECLARE @countrystart int
DECLARE @countryend int
DECLARE @phone2 nvarchar(50)
DECLARE @phone2start int
DECLARE @phone2end int
DECLARE @productname nvarchar(50)
DECLARE @productnamestart int
DECLARE @productnameend int
DECLARE @upccode nvarchar(50)
DECLARE @upccodestart int
DECLARE @upccodeend int
DECLARE @datecode nvarchar(50)
DECLARE @datecodestart int
DECLARE @datecodeend int
DECLARE @purchaseat nvarchar(50)
DECLARE @purchaseatstart int
DECLARE @purchaseatend int
DECLARE @purchasecity nvarchar(50)
DECLARE @purchasecitystart int
DECLARE @purchasecityend int
DECLARE @datepurchased nvarchar(50)
DECLARE @datepurchasedstart int
DECLARE @datepurchasedend int
DECLARE @dateopened nvarchar(50)
DECLARE @dateopenedstart int
DECLARE @dateopenedend int
DECLARE @subject nvarchar(50)
DECLARE @subjectstart int
DECLARE @subjectend int
-- Windosr Variables
DECLARE @name nvarchar(50)
DECLARE @namestart int
DECLARE @nameend int
DECLARE @company nvarchar(50)
DECLARE @companystart int
DECLARE @companyend int

--First replace all ASCII carriage returns and line feeds and assign the whole chunk of data to a variable.
SET @body = (select replace( REPLACE(convert(varchar(4000), message),CHAR(10),' '),CHAR(13),'') from message where mailid = @_id)
--below is the sample syntax for the individual replacements of line feeds and carriage returns, but it's combined into one statement above.
--SELECT REPLACE(@jb,CHAR(10),'') as firstpass
--SELECT REPLACE(@jb,CHAR(13),'') as secondpass
IF left(@body,47) = 'Fromubject:contact info sent from samplesite.com'
--If this is true.. then it is a sample company.
BEGIN
--The 12 in the start is compensating for the 12 Characters of "First Name: "
--Below is collecting First Name
SET @firstnamestart = (CHARINDEX('First name:',@body) + 11)
SET @firstnameend = (CHARINDEX('Last name:',@body) - 1)
SET @firstname = SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart)
----Below is collecting Last Name
SET @lastnamestart = (CHARINDEX('Last name:',@body) + 10)
SET @lastnameend = (CHARINDEX('Address 1:',@body) - 1)
SET @lastname = SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart)
--Below is collecting Address1
SET @address1start = (CHARINDEX('Address 1:',@body) + 10)
SET @address1end = (CHARINDEX('Address 2:',@body) - 1)
SET @address1 = SUBSTRING(@body,@address1start,@address1end-@address1start)
--Below is collecting Address2
SET @address2start = (CHARINDEX('Address 2:',@body) + 10)
SET @address2end = (CHARINDEX('City:',@body) - 1)
SET @address2 = SUBSTRING(@body,@address2start,@address2end-@address2start)
--Below is collecting city information
SET @citystart = (CHARINDEX('City:',@body) + 5)
SET @cityend = (CHARINDEX('State/Prov',@body) -1)
SET @city = SUBSTRING(@body,@citystart,@cityend-@citystart)
--Below is collecting state information
SET @statestart = (CHARINDEX('State/Province:',@body) + 15)
SET @stateend = (CHARINDEX('Zip/Postal',@body) -1)
SET @state = SUBSTRING(@body,@statestart,@stateend-@statestart)
--Below is collecting zip information
SET @zipstart = (CHARINDEX('Zip/Postal Code:',@body) + 16)
SET @zipend = (CHARINDEX('Country',@body) -1)
SET @zip = SUBSTRING(@body,@zipstart,@zipend-@zipstart)
--Below is collecting country information
SET @countrystart = (CHARINDEX('Country:',@body) + 8)
SET @countryend = (CHARINDEX('E-mail address',@body) -1)
SET @country = SUBSTRING(@body,@countrystart,@countryend-@countrystart)
--Below is collecting email information
SET @emailstart = (CHARINDEX('E-mail address:',@body) + 15)
SET @emailend = (CHARINDEX('Daytime',@body) -1)
SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart)
--Below is collecting phone information
SET @phonestart = (CHARINDEX('Daytime Phone:',@body) + 14)
SET @phoneend = (CHARINDEX('Evening Phone:',@body) -1)
SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart)
--Below is collecting phone2 information
SET @phone2start = (CHARINDEX('Evening Phone:',@body) + 14)
SET @phone2end = (CHARINDEX('Product Name',@body) -1)
SET @phone2 = SUBSTRING(@body,@phone2start,@phone2end-@phone2start)
--Below is collecting Product Name information
SET @productnamestart = (CHARINDEX('Product Name:',@body) + 12)
SET @productnameend = (CHARINDEX('UPC Code:',@body) -1)
SET @productname = SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart)
--Below is collecting UPC Code information
SET @upccodestart = (CHARINDEX('UPC Code:',@body) + 9)
SET @upccodeend = (CHARINDEX('Date Code:',@body) -1)
SET @upccode = SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart)
--Below is collecting Date Code information
SET @datecodestart = (CHARINDEX('Date Code:',@body) + 10)
SET @datecodeend = (CHARINDEX('Purchased At',@body) -1)
SET @datecode = SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart)
--Below is collecting Purchase At information
SET @purchaseatstart = (CHARINDEX('Purchased At',@body) + 26)
SET @purchaseatend = (CHARINDEX('Purchase City:',@body) -1)
SET @purchaseat = SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart)
--Below is collecting Purchase City information
SET @purchasecitystart = (CHARINDEX('Purchase City:',@body) + 14)
SET @purchasecityend = (CHARINDEX('Date Purchased',@body) -1)
SET @purchasecity = SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart)
--Below is collecting Date Purchased information
SET @datepurchasedstart = (CHARINDEX('Date Purchased:',@body) + 15)
SET @datepurchasedend = (CHARINDEX('Date Opened',@body) -1)
SET @datepurchased = SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart)
--Below is collecting Date Opened information
SET @dateopenedstart = (CHARINDEX('Date Opened:',@body) + 12)
SET @dateopenedend = (CHARINDEX('E-mail Subject:',@body) -1)
SET @dateopened = SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart)
--Below is collecting Email Subject information
SET @subjectstart = (CHARINDEX('E-mail Subject:',@body) + 15)
SET @subjectend = (CHARINDEX('Comments:',@body) -1)
SET @subject = SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart)
--Below is collecting message information
SET @commentsstart = (CHARINDEX('Comments:',@body) + 10)
SET @commentsend = len(@body)
SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)
--below is the part that actually writes the data out to the Output table
INSERT INTO OutputEmails
(FirstName, LastName, Address1, Address2, City, State, Zip, Country, eMail, Phone, Phone2, ProductName, UPCCode, DateCode, PurchaseAt, PurchaseCity, DatePurchased, DateOpened, Subject, Comments, OrigID)
SELECT
ltrim(SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart)) as FirstName,
ltrim(SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart)) as LastName,
ltrim(SUBSTRING(@body,@address1start,@address1end-@address1start)) as Address1,
ltrim(SUBSTRING(@body,@address2start,@address2end-@address2start)) as Address2,
ltrim(SUBSTRING(@body,@citystart,@cityend-@citystart)) as City,
ltrim(SUBSTRING(@body,@statestart,@stateend-@statestart)) as State,
ltrim(SUBSTRING(@body,@zipstart,@zipend-@zipstart)) as Zip,
ltrim(SUBSTRING(@body,@countrystart,@countryend-@countrystart)) as Country,
ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail,
ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone,
ltrim(SUBSTRING(@body,@phone2start,@phone2end-@phone2start)) as Phone2,
ltrim(SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart)) as ProductName,
ltrim(SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart)) as UPCCode,
ltrim(SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart)) as DateCode,
ltrim(SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart)) as PurchaseAt,
ltrim(SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart)) as PurchaseCity,
ltrim(SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart)) as DatePurchased,
ltrim(SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart)) as DateOpened,
ltrim(SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart)) as Subject,
ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments,
@_id as OrigID
FROM dbo.message
WHERE (mailId = @_id)
END
ELSE BEGIN
--below prints the whole value in the debugger (SQL Query Analyzer)
--print @body
--below prints the length of the whole value
--print len(@body)
--below prints the location of 'Callers Name:'
--Print CHARINDEX('Name: ',@body)
--Below is collecting the Name information
--The 6 in the start is compensating for the 6 Characters of "Name: "
SET @namestart = (CHARINDEX('Name:',@body) + 5)
SET @nameend = (CHARINDEX('Email:',@body) - 1)
SET @name = SUBSTRING(@body,@namestart,@nameend-@namestart)
--Below is collecting email information
SET @emailstart = (CHARINDEX('Email:',@body) + 6)
SET @emailend = (CHARINDEX('Title:',@body) -1)
SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart)
--Below is collecting Company information
SET @companystart = (CHARINDEX('Company:',@body) + 8)
SET @companyend = (CHARINDEX('Phone Number:',@body) -1)
SET @company = SUBSTRING(@body,@companystart,@companyend-@companystart)
--Below is collecting phone information
SET @phonestart = (CHARINDEX('Phone Number:',@body) + 13)
SET @phoneend = (CHARINDEX('Comments:',@body) -1)
SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart)
--Below is collecting message information
SET @commentsstart = (CHARINDEX('Comments:',@body) + 9)
SET @commentsend = len(@body)
SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)
--below is the part that actually writes the data out to the Output table
INSERT INTO OutputEmails
(Name, eMail, Company, Phone, Comments, OrigID)
SELECT
ltrim(SUBSTRING(@body,@namestart,@nameend-@namestart)) as Name,
ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail,
ltrim(SUBSTRING(@body,@companystart,@companyend-@companystart)) as Company,
ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone,
ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments,
@_id as OrigID
FROM dbo.message
WHERE (mailId = @_id)
END
--At the very end when you're satisfied that the data has been processed properly, delete the appropriate message record from the message table
DELETE FROM MESSAGE Where message.mailID = @_ID
GO

Sample Data that has problems

Fromubject:contact info sent from samplesite.comBody:First name: Sample
Last name: Name
Address 1: 123 Testing Road
Address 2: Don't filled
City: Park Forest
State/Province: FL
Zip/Postal Code: 12345-1234
Country: USA
E-mail address: validemail@email.com
Daytime Phone: 123-123-1234
Evening Phone: Don't filled
Product Name: Sample Product with Cheese
UPC Code: 12345-89521
Date Code: 1251237 D PST . 5290
Purchased At (Store Name): StoreName
Purchase City: Store City
Date Purchased: 12/8/2007
Date Opened: 12/14/2007
E-mail Subject: nail in product
Comments: when I purchased your product, there was something in it. Obviously I am not happy about this. Please contact me. Thank you John Q Public


View Replies !   View Related
I Wish To Avoid ...
Hi All,I am working on Web Application which deals with history data forreports and keeping track of changes.Current Solution :1) For each Entity I am having a column TID (Tracking ID) whichkeep on increasing for an instance of Entity. (so history and presentdata in the same table) . It makes querying the data difficult.2) I am also using month year table for Managing data i.e<TableNameMMYYYY> for a given month and yearPurposed Soluion :1) Using a seperate table so that history and present data isplaced seperately .2) Store All data in one table <TableName>Please guide me on advantages and disadvantages you pin point in thetwo approaches.With warm regardsJatinder

View Replies !   View Related
Avoid Sp_executesql With ADO.NET
 In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.

View Replies !   View Related
How To Avoid Duplicate Value
Hello,

I have the following query,

SELECT GroupInfo.GroupID, GroupInfo.GroupName
FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID)
INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber )

It's out put is as follow:

Group ID GroupName
0 Abc
1 Beta
0 Abc
0 Abc
0 Abc
1 Beta
2 Alpha

Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like,


Group ID GroupName
0 Abc
1 Beta
2 Alpha

Hope I explained what I need to see in result pane.

Thanks,

Junior

View Replies !   View Related
Avoid Using Cursors
Hi All,I want to avoid using cursors and loops in stored procedures.Please suggest alternate solutions with example (if possible).Any suggestion in these regards will be appreciated.Thanks in advance,T.S.Negi

View Replies !   View Related
Trying To Avoid Using Cursors!
i'm trying to write a batch that will perform a complex task usingset-based selects instead of a row-based cursor. let me know if you canhelp me figure out how.description of what i'm trying to do:there is TABLE1, TABLE2, and TABLE3i want to select each row from TABLE1, do some analysis on the data ofthat row, and then perform an insert of some data into TABLE2, and somedata into TABLE3how do i do this in a T-SQL batch?

View Replies !   View Related
I Want To Avoid Using A Cursor, Please!
Application is a Work Tracking/Timesheet database.The increments of work are stored in the TimesheetItem table. Thiscontains, inter alia, the Work Code, the Start and the Duration thatthe employee spent that day on a particular project.Some employees in the Network Support Department don't complete astandard 7.5 hour day for various reasons, so for every Network Supportperson I need to update these particular days with an amount to bringthe total day's hours to 7.5.This SQL will get me a list of all TimesheetItem records for thepersonnel concerned.SELECTTimesheetItem.TypeID,[Work].WorkCode,TimesheetItem.Start AS Start,SUM(CAST(TimesheetItem.DurationMins AS float) / 60) AS HoursFROMTimesheetItem LEFT OUTER JOIN[Work] ON TimesheetItem.WorkID = [Work].WorkIDWHERE(TimesheetItem.EmployeeID IN(SELECT EmployeeID FROM Employee WHERE DepartmentID = 2))GROUP BYTimesheetItem.TypeID, TimesheetItem.Start, [Work].WorkCodeHAVING(TimesheetItem.Start >= @FromDate) AND (TimesheetItem.Start <= @ToDate)ORDER BYTimesheetItem.StartWhat I need is to group these records by EmployeeID where theaccumulated hours per day are < 7.5, so that I can then insert anincrement to make up the difference.I'm writing this from home and I don't have access to the tables toprovide a script, but there's nothing untoward there.Hope someone can help!ThanksEdward

View Replies !   View Related
How To Avoid Cursors
I have a long sql batch that does this:1. Gets a list of all tables in user database that start with name COREdeclare@tablenamevarchar(30),@commandvarchar(2000),@cntintegerdeclare GetCOREOids cursor forselect sysobjects.namefrom sysobjectswhere ( OBJECTPROPERTY(sysobjects.id,N'IsUserTable')=1 ) and( sysobjects.name like 'CORE%' or sysobjects.name ='CMNSTRStructGeomBasicPort') and( sysobjects.name not like 'CORESpatialIndex%') and (sysobjects.namenot like 'COREDeletedObjects%') and( sysobjects.name not in('CORERelationOrigin','CORERelationDestination') ) and( sysobjects.id in ( select id from syscolumns where name = 'oid') )for read only2. Populates a temporary table with distinct oids from the list.create table [#tSP3DCoreOid] (oid UNIQUEIDENTIFIER)open GetCOREOidsfetch GetCOREOids into @tablenamewhile ( @@fetch_status = 0)beginselect @command = 'insert into [#tSP3DCoreOid] select distinct oid from' + @tablenameexecute(@command)--print @tablename + ' rows: ' + convert(char,@@rowcount)fetch GetCOREOids into @tablenameend /* while */--Clean upclose GetCOREOidsdeallocate GetCOREOids3. Creates a cursor to get "invalid" oids from 2 other tablesdeclare DanglingRelation cursor forselect oid from [dbo].[CORERelationOrigin] RO where not exists( select oid from [#tSP3DCoreOid] where oid = RO.oid )unionselect oid from [dbo].[CORERelationDestination] RD where not exists( select oid from [#tSP3DCoreOid] where oid = RD.oid )4. Loops thru. the cursor examining each oid and then calls aStoredProc to update another tabledeclare @objectOid uniqueidentifierdeclare @tempOid uniqueidentifieropen DanglingRelationfetch DanglingRelation into @ObjectOidwhile ( @@fetch_status = 0)beginset @tempOid='00000000-0000-0000-0000-000000000000'if left(@ObjectOid,8)='00000002'select @tempOid=oid from COREToDoList where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000003'select @tempOid=oid from COREToDoRecord where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000004'elseselect @tempOid=oid from COREBaseClass where Oid=@ObjectOidif @tempOid = '00000000-0000-0000-0000-000000000000'BEGINexec CORESetObjectIntegrity @ObjectOid, 2ENDfetch DanglingRelation into @ObjectOidendclose DanglingRelationdeallocate DanglingRelationdrop table [#tSP3DCoreOid]Now the #tSP3DCoreOid temp table can sometimes get as many as 7.5million rows and this is making the batch fail on SQLServer as it runsout of memory.I think the problem is the cursor here and would like somefeedback/tips on how to best optimise it.thanks a lotSunit

View Replies !   View Related
To Avoid Looping
Hi All,

Very Ad-hoc requirement I am having.

I've got one table with two columns.
Column Name Data Type
1) Id Integer Identity
2) RemDate DateTime

I've to write one SP/JOB in that there will be an integer input parameter @numofday.

Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month.

This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping.

Thanks in advance,
Jai

View Replies !   View Related
Trying To Avoid A Cursor...
Happy new year to all! Now a question...

I added a new column to StagePayments table - Activity - which is supposed to end up being the same as the JobActivityID from the JobActivities table. Basically, I need to get JobActivities (JA) info and put it in the StagePayment (SP) column. Problem is there are duplicate JA/Descriptions and SP/Activities, so what I need is to take the first sequence SP/Activity and grab the first JA/JobActivityID that matches for a particular JobID. Then get the next one of each and so on and so on...

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Tables...

CREATE TABLE [dbo].[StagePayment] (
[PaymentID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Paid] [bit] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Amount] [decimal](10, 2) NOT NULL ,
[Comment] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
[Activity] [varchar] (30) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
[ActivityID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
CONSTRAINT [PK__StagePayment__457442E6] PRIMARY KEY CLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
CONSTRAINT [DF__StagePayme__Paid__4668671F] DEFAULT (0) FOR [Paid]
GO

CREATE INDEX [IX_StagePayment] ON [dbo].[StagePayment]([JobID], [Sequence]) ON [PRIMARY]
GO

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

CREATE TABLE [dbo].[JobActivities] (
[JobActivityID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Activity_Status] [char] (1) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[Description] [char] (30) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
[PlanStartDate] [datetime] NULL ,
[PlanEndDate] [datetime] NULL ,
[ActEndDate] [datetime] NULL ,
[AmountDue] [decimal](10, 2) NOT NULL ,
[CanDelete] [bit] NOT NULL ,
[Comments] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
CONSTRAINT [PK_JobActivity] PRIMARY KEY CLUSTERED
(
[JobActivityID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
CONSTRAINT [DF_JobActivities_JobActivityID] DEFAULT (newid()) FOR [JobActivityID]
GO

CREATE INDEX [IX_JobActivity] ON [dbo].[JobActivities]([JobID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_JobActivity_1] ON [dbo].[JobActivities]([JobID], [Activity_Status]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_JobActivity_2] ON [dbo].[JobActivities]([JobID], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_JobActivities] ON [dbo].[JobActivities]([JobID], [PlanEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [JobActivities53] ON [dbo].[JobActivities]([JobID], [Description], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [JobActivities50] ON [dbo].[JobActivities]([JobID], [Description], [PlanEndDate], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_JobActivities_6] ON [dbo].[JobActivities]([JobActivityID], [Activity_Status], [Description]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_JobActivities_1] ON [dbo].[JobActivities]([JobID], [Sequence], [Description], [JobActivityID]) ON [PRIMARY]
GO

CREATE INDEX [IX_JobActivities_2] ON [dbo].[JobActivities]([JobID], [Sequence], [ActEndDate]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobActivities] ADD
CONSTRAINT [FK_JobActivity_Job] FOREIGN KEY
(
[JobID]
) REFERENCES [dbo].[Jobs] (
[JobID]
) ON DELETE CASCADE
GO

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

My example for one single JobID...

In JA -

JobActivityID Seq Description
34969C69-FF70-4313-91CC-43921DB3D74D 18 FOLLOWUP
48ACFEEE-3349-4F16-83E0-58F9B19E28E5 16 INSTALL
22507D44-6D0D-42DE-A211-9C23FDFCD19D 5 PLOTPLAN
BA88D04E-EBAE-40DB-A2C9-F909463D7F22 1 THANKU
83C48207-895B-4775-A62D-07059D8DEB62 10 NOTCUST
DBB8DF00-E26B-4E6F-9482-08E8CFE1588D 11 ROOMORD
BF621E91-E819-4F84-B507-0AA644D5C3F6 0 DWNPAY
6F595880-59D9-4E55-845D-19B477E8B179 2 THANKU
86D0A650-3B72-47E1-BDC2-2CA177DC3D53 12 NOTCSTRM
B0ABCC4C-A626-41C2-890C-3B9580326774 13 ROOMREC
F131C6FF-A86C-4527-A580-60FF7D3F0164 19 1YRFLWUP
7132625C-8E8B-4748-9176-6F06E8D0F20F 17 ARCMNT
AE06A938-323B-46EA-BA11-7D17B0985ACC 15 24HRCALL
DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A 14 24HRCALL
EBC15C77-95D5-4C42-AD88-861F9DD7688B 9 RECPERMT
97D3D755-4B6F-4564-842B-A06945AA8890 8 SUBPERMT
CAACAACA-3B97-41D5-9A4A-A4E3E963D0BF 6 SUBCAD
0C3CB2E0-F4E9-43CD-81E4-ACE9F4022033 3 PHONCALL
42D498BE-308A-413E-965D-ADE7A7A21B97 4 MEASURE
7654C5E3-BED5-4F78-ADC8-DD4E283ADDEE 7 RECCAD

In SP -

Seq Activity ActivityID
1 NULL
2 24HRCALL
3 24HRCALL
4 INSTALL

I need to get SP to end up looking like this -

Seq Activity ActivityID
1 NULL NULL
2 24HRCALL DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A
3 24HRCALL AE06A938-323B-46EA-BA11-7D17B0985ACC
4 INSTALL 48ACFEEE-3349-4F16-83E0-58F9B19E28E5


I have tried various versions of this...
begin transaction
update StagePayment
set Activity = (Select J.JobActivityID
from JobActivities J (nolock)
inner join StagePayment SP (nolock) on J.JobID = SP.JobID and J.Description = SP.Activity)
where Activity is not NULL

way too basic as I get this error...

Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I know somehow I have to get the duplicate Activities in the same sequence as the Descriptions, but that's where I'm stuck?!?

If anyone is able to offer suggestions on how to get this to update correctly, I'd be very happy to hear about it!
Thanks in advance!
Tiffanie

View Replies !   View Related
Would Like To Avoid Cursor, Please Help
Hi,

I need to query a database for a recordset and insert this into another database row-by-row.

For each record I want to provide a time of insert, but for each insert this time has to be incremented by 1, the time format is HH:MM:SS.0000 so for each inserted record the last decimal point would increment by 1.

Can I do this without a cursor ?

Any additional info I will provide gladly.

Thanks!

View Replies !   View Related
How To Avoid Deadlocks
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data:

Transaction (Process ID 499) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?

Any advise would be much appreciated. Thanks!

View Replies !   View Related
How To Avoid .LDF Grow Too Big
In SQL Server 2000, we have a database which is frequently updated.
The .mdf file of the Database is around few hundred mbs where as the .ldf (Transaction Log) is few Gbs. Eveyday the size of the file is growing big and the idea is to avoid this.

I was trying to do a few things.

1) Auto Shrink the database size
2) Truncate the transaction log.

Could you suggest which is the best way to avoid this problem, since I am not able why the database should grow so big within a few days..

View Replies !   View Related
To Avoid Fragmentaion
I found some fragmented data pages in my database tables.
I have found out using DBCC Showcontig commands.
I want to remove data fragmentation.
My tables are replicated. Is it possible to remove fragmentation
without using export and import. Please help me in fixing this problem.

Is there any way to fix replication failure with out recreating it.
My replication has failed saying is timout error.error is could not connect
subscriber. But I can able to ping the subscriber and if I create a new publisher and subscriber it is working fine. But existing one is not working.
What might be the possible reason. We have recreated the replication. We droped the existing one and recreated it. It took around 14 hours to sync.
Is there any way to resync with out recreate publication. We are using transaction replication and merge replication.

Vani

View Replies !   View Related
How To Avoid For..each When There's No Data


Hi everyone,

As first task I've got Data Flow which loads a set of data into a .NET recordset.
After that, inmediately flow executiongoes to For..Each Loop. I'd like to avoid go in that direction when Data Flow returnszero rows.

How can I do such thing?

I've tried this on Precedence Constraint Editor but it doesn't work..It doesn't recognize EOF keyword..

@[User::ResulSet] == EOF


Thanks in advance for your input,

Enric

View Replies !   View Related
How To Avoid 0/0 Error


Hi All,

I use a report expression something like this.


= SUM(Fields!MTD_TotNetCOAmt.Value)/SUM(Fields!TotBalance.Value)* 12



When Both fields have 0 vlaues, it gives a error massege. How do I avoid this?

Thanks

View Replies !   View Related
How To Avoid Cursor


hi all,
i have a huge database and i am using Cursor to retrieve data and with each fatched data i m doing some operation....as my database is increasing the time duration to execute the cycle is also increation hugely....how to solve it??

thanks,

View Replies !   View Related
How To Avoid Duplicates In Two Tables?
Hi all,
I have 10 tables with unique values such as mobile no: and message in each table.But now the problem is that this same mobile no: may be there in other tables.How can i eliminate the records from other tables.Can anyone tel me a suggestion.
Thank U.
 

View Replies !   View Related
Can I Avoid Temp Tables, Etc.
I need help on two questions:1. Is temp table the only way to pass recordsets from a nested storedprocedure to a calling stored procedure? Can we avoid temp tables inthis case?2. Are operations in a stored procedure are treated as a transaction?Any help will be greatly appreciated.Background: We need to use temp table to pass recordsets from a nestedstored procedure to a calling stored procedure. Our understanding isthat in this case, we have no choice but to use temp tables. So, weneed to optimize the performance as much as possible. To do this, wewanted to find out whether operations in a stored procedure are treatedas a transaction. We are using SQL 2000 SP4. I could not find anyanswers so I did the following experiment.Experiment 1:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASUpdateArticlesSETIsUpdate = 20whereArticlesId < 80000SELECT * from ArticlesGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO"SELECT * from Articles" takes a long time (about 40 seconds) tocompleteBefore executing the SP, the IsUpdate attribute for all articles is 30.Then I executed this SP. Before the SP is finished, I end the SPmanually. I checked the IsUpdate attribute again, and found that allArticles's (ArticlesId < 80000) Isupdate attribute is now 20. Theoperations did not rollback. I interpret this to mean that the whole SPis not treated as a transaction.Then, I did experiment 2 below. This time, I explicitly declared thetransaction.SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)drop procedure [dbo].[Wiz_SP_Transaction_Test]GOCREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]ASBEGIN TRANSACTIONUpdateArticlesSETIsUpdate = 50whereArticlesId < 80000SELECT * from ArticlesIF @@ERROR <0 ROLLBACK TRANSACTIONCOMMIT TRANSACTIONGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOBefore this second SP, the IsUpdate attribute is 20 (set in the firstexperiment). I run this second SP and ended it manually before itfinished. I checked the IsUpdate attributes for all Articles's(ArticlesId < 80000), but their Isupdate attribute is 50. So theoperation did not rollback either. But we have declared the transactionexplicitly. Does this mean that the SP is still not treated as atransaction?

View Replies !   View Related
How To Avoid Reference Problems
Hi,I have some tables which have references between them.If i delete the reors in the child tables , since it references thecolumn of the parent , i get error msg in my front end (quitenatural).do u tell me how to delete the child table records with out affectingthe relation ships or can recreate the relations with out affecting mydata & structure and the constraints.With thanksRaghu

View Replies !   View Related
How Do I Avoid This Corelated Subquery?
Hello,I have a situation which would essentially use a co-related subquery.I am trying to avoid using a co-related subquery due to its slowperformanc and use a join statement instead.Here is what I am trying to do:Tables:========Limit-------RelID ProdID Days Amt1 Amt2----- ------ ---- ---- ------148 2 30 15000 30000148 2 7 1000 2500Temp Limits-----------RelID ProdID MaxDays Amt1 Amt2----- ------ ----- ------ ------148217500 10001482717500 5001482302500 200014829025000 75014821807500 -300I want the totals of Amt1 and Amt2 in the TempLimits table added toeach row in the Limits table where the Days in the Limits Table >=MaxDays in TempLimitsSo, when I run the query with the above dataI need to getRelID ProdID Days Amt1 Amt2----- ------ ---- ------ ------148 2 30 42500 (15000+7500+17500+2500) 33500(30000+1000+500+2000)148 2 7 26000 (1000+7500+17500) 4000(2500+1000+500)************************************************** **************************Here is the query that I have right now...update Limitset Amt1 = isnull(Amt1,0) + IsNull(temptable.temp_Amt1, 0), Amt2 = isnull(Amt2,0) + IsNull(temptable.temp_Amt2, 0)from Limit inner join(select Templimits.relationship_id relationship_id,Templimits.product_id product_id, sum(Templimits.Amt1) temp_Amt1,sum(Templimits.Amt2) temp_Amt2from TempLimitsWHERE TEMPLIMITS.MAXDAYS <= LIMIT.DAYSgroup by limit.relationship_id, limit.product_id) temptableon Limit.relid = temptable.relid andLimit.prodid = temptable.prodid************************************************** ****************************As you would see here, I am trying to use the condition for Days(listed in the query in CAPS) inside the inner query which would notwork... Is there any way I can do this apart from using temp tablesand co-related subqueries?Thanks in advanceKannan

View Replies !   View Related
How To Avoid Phantom Inserts?
How do you avoid phantom inserts? Can you recommend a strategy (or other posts, articles) to avoid "phantom inserts" with the default SQL Server isolation level ("Read Committed").

Thanks!

View Replies !   View Related
Avoid Increasing The Log File
Hello All,

I have faced a network problem during some days, what forced one of our replications to be stopped.
The Publisher database is a high volume database.
After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted.

My concern is the way it is working, there will be no enough space for the log or for its backup files.

So, I have created a TSQL job within the following commands:

BACKUP LOG database_name
WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (database_name,TRUNCATEONLY)

It's running every 20 minutes, however the transaction log remains increasing.

I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process.

Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication?

Thanks a lot!
Regards,
Felicia Schimidt
felicia.schimidt@br.flextronics.com

View Replies !   View Related
Filling Systlogs ??? How To Avoid This
Msg 1105, Level 17, State 1
Can't allocate space for object 'nbcctactxxt' in database 'SDNETPRO' because the 'default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

Thas was the error messege I got when I tried to create index using this code:
CREATE CLUSTERED INDEX Index_key ON dbo.nbcctactxxt (Ord_Nbr,Ord_Type,Ord_Supp,Locn,
CCT_Nbr,CCT_Leg,Activ,Gen_Seq) WITH FILLFACTOR=100,ALLOW_DUP_ROW

I ran this code:
DBCC CHECKTABLE(syslogs)

and got back :
Checking syslogs
The total number of data pages in this table is 1.
*** NOTICE: Space used on the log segment is 0.00 Mbytes, 0.00.
*** NOTICE: Space free on the log segment is 819.20 Mbytes, 100.00.
Table has 34 data rows.
DBCC execution completed. If DBCC printed error messages, see your System Administrator.

Well, what seem to be the problem?, I have plenty of space... I even checked truncate Log on check point , yet I am having the same error again.Any one can help. ..... thanks

Ali

View Replies !   View Related
How To Avoid A Growing Transaction Log
I have to admit, I'm usually using the MySql database, but in this particular case I have to use MSSQL 2000.

Over to my problem.

I'm building a web-based system (who isn't these days) in which the user types arbitrary information that is published when the user pushes the save button. Nothing new about that.

Here comes the tricky part, when the user wants to edit an existing item I copy all information in the database and sets the id of the 'edit-copy' to the negative value ( id 45 becomes id -45 for the edit-copy). This is also done on all items in other tables that is connected to the main item.

This way I get a copy that the user may edit without messing up the published information. When the user is done I either delete all the negative items (cancel) or delete the positive items and update the negative to become positive (save).

So far so good, allmost... my problem is that the transaction log grows tremendously.

Is there any other way to accomplish a safe edit that doesn't affect the transaction log as much as my current solution?

Could I be doing something wrong when updateing or deleteing my items?

View Replies !   View Related
Avoid Sql Class 2733a
hate to gripe, but this was bad. I have taken several sql server classes over the years, and have always been happy with the class, but avoid the 2733a (upgrading you database administration skills to sql 2005). Class is poorly devloped and incomplete. Half of the examples were to do things such as mirroring and replication from a command prompt. I thought I was in an Oracle 8 class. It was clear much of this functionality is not complete. In fact most of the class was command prompt based. They had absolutley nothing about the upgrade process from SQL 2000 to SQL 2005. I hope MS is not taking a step backwards, I always thought the strong suite for MS was the enterprise manager tool suite. The instructor did tell me there was an upgrade to the class that just came out, but it has a long way to go.

View Replies !   View Related
How To Avoid Display Of Duplicates
Hi,
I'd really appreciate advice on now to avoid displaying duplicates in a resultset like the one below, only displaying the first field once and all the rows that correspond to that field all listed listed below. Distinct doesn't work. Thanks
Customer Name Address
============= =======

Big Bob's Books 123 Stone Lane
456 Seaside Drive
789 Waterhouse Square
1234 Mystery Drive
5678 Simple St

Tiny Tim's Tools

Tiny Tim's Tools

View Replies !   View Related
Want To Avoid Cursors ... Need Help With Query
Hi,

I have a customer who is using vba to pull a result set from an sql server stored procedure into excel. She wants a calculated column added to the result set that gives:
The number days (datediff) between the end date (autend_dte) on one row and the begin date (autbeg_dte) on the next row for each client (clt_num). The rows are to be ordered by client and begin date. The number should be associated with the second row used to calculate the date diff. The first row for each client will have a date diff of 0.

I could do this using a cursor in the stored procedure or a loop in the vba, but I would prefer to do it with the select, but I don't even know where to start.

See expected results below.


CREATE TABLE #testit (
clt_num int NOT NULL ,
autbeg_dte datetime NULL ,
autend_dte datetime NULL)

INSERT INTO #testit (clt_num, autbeg_dte, autend_dte)
SELECT 510, '2004-09-01 00:00:00.000', '2005-09-30 23:59:00.000' UNION ALL
SELECT 510, '2005-10-01 00:00:00.000', '2006-04-06 23:59:00.000' UNION ALL
SELECT 600, '2006-08-01 00:00:00.000', '2006-11-06 23:59:00.000' UNION ALL
SELECT 2529, '2006-01-13 00:00:00.000', '2006-04-11 23:59:00.000' UNION ALL
SELECT 2529, '2005-11-30 00:00:00.000', '2005-12-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-03-29 00:00:00.000', '2006-05-02 23:59:00.000' UNION ALL
SELECT 2602, '2005-11-12 00:00:00.000', '2006-02-27 23:59:00.000' UNION ALL
SELECT 2602, '2006-05-26 00:00:00.000', '2006-06-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-06-18 00:00:00.000', '2006-06-28 23:59:00.000'

SELECT * FROM #testit
order by clt_num,autbeg_dte

Expected result:

clt_numautbeg_dte autend_dte Days Diff
5102004-09-01 00:00:00.0002005-09-30 23:59:00.0000
5102005-10-01 00:00:00.0002006-04-06 23:59:00.0001
6002006-08-01 00:00:00.000 2006-11-06 23:59:00.000 0
25292005-11-30 00:00:00.0002005-12-12 23:59:00.0000
25292006-01-13 00:00:00.0002006-04-11 23:59:00.00044
26022005-11-12 00:00:00.0002006-02-27 23:59:00.0000
26022006-03-29 00:00:00.0002006-05-02 23:59:00.00030
26022006-05-26 00:00:00.0002006-06-12 23:59:00.00024
26022006-06-18 00:00:00.0002006-06-28 23:59:00.0006


Thanks,

Laurie

View Replies !   View Related
Unable To Avoid The Deadlock
Hi,

Please help with the below deadlock problem :

We are using SQL Server 2000. Our application is a J2EE based. We use Java v 1.5 ,EJB 3.0 ,JBoss-4.2.0 and have sqljdbc.jar v 1.1 driver (to connect to sql server from jboss).

We have a JMS Queue which is unloading a java.util.List object at a time. This List has 5 value objects.

In the session bean I iterate through this list and do the following :

while(importListPeople.hasNext()){
Person value = (Person)importListPeople.next();

//select statement to check if the Person already exists in DB
Person prsn =entityManager.query("SELECT from Person where...");

if(prsn !=null){ //do an update if person already exists

// update person record
// update PersonAV table
// do an insert into personIndexingQueue
// increment the person update counter.

}else{//do a create

entityManager.persist(value);
//insert into personAv table
//do an insert into personIndexingQueue
//increment the person create counter.
}

}

I get the below error when we are running the application
13:04:41,904 WARN [JDBCExceptionReporter] SQL Error: 1205, SQLState: 40001
13:04:41,904 ERROR [JDBCExceptionReporter] Transaction (Process ID 63) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

For log ,sqlprofiler I could narrow down that the deadlock is occurring on the below select statement in the code.

//select statement to check if the Person already exists in DB
Person prsn =entityManager.query("SELECT from Person where...");

This code works if there is only one object in the list.

The above code works on mysql with 20 objects in the list.

Please help.

View Replies !   View Related
Avoid Default Datetime
Hi All,

i have a table as below
create table temp
(
Number int,
DateError datetime
);

insert into temp values(13,' ');

i get
Number DateError
------------------
13 1900-01-01 00:00:00.000

Now, i want "1900-01-01 00:00:00.000" and just want NULL or blank and ofcourse not 1900-01-01 00:00:00.000 for my further processing.
I have a problem, i have to formath string send it as second value which is simple ' '.
How can i modify my Table definition above to achieve this.
Thanks in advance.

View Replies !   View Related
No Unique Key / Avoid Dups
I have a table that I cannot create a unique key for because there is nothing unique and I can concatenate anything together to create one. I am looking for a way to import the data daily and have it only import what is not already in the table like a unique index would normally do. I don't want a sequencial number because that would do me any good. I need the record only in there once but I want to get all of the records in the new table. I have a date field with this if that at all helps. I am wondering if I could create a couple of feeder table to make this work but i am stuck. Any ideas on what to do? Thanks!!!

View Replies !   View Related
How To Avoid Copying Multiples?
Our application isfor load balancing in master/slave way. After processing the required job each slave has to update the master database with their database details.

I created aSSIS package usingSQL server import and export server to copy the database from slave to master. Thenused this package in code to execute whenever there is some operation performed which makes changes to the database.

Whilecreating the packageI selected the append rows to the destination table.

First time when I execute slave copied the database.

But when I execute the package second time it is again copying the rows which italready copied before. Is there any way to copy only those rows which are not exising in the master.

Any help would be appreciated.

Thanks in advance









View Replies !   View Related
Avoid Package Failure
Hello everyone

i'm using a Foreach Container to run certain tasks, these ones are inside a Sequence Container. I'd like to know if there is a way to have a task failure without a foreach failure, i don't need the hole process to fail, just the current iteration so i can log it.

Thanks a lot!

View Replies !   View Related
HOw Can Avoid Duplicated Records
HI EveryBody,


I have 2gb Text file to transfer the data into Our database through oledb destination

Here my prob is in the text file some duplicate records is there how can i avoid those records


plz help me




View Replies !   View Related
How To Avoid Cursors And Loops
hi,

My problem is basically i need to call a stored proc for each entry in a table, i.e, basically a for loop calling stored procs with parameter coming from the table. I know two ways of doing this .. using cursor and using while loop with temp table. I dont like both approaches. Is there any good practice for this situation..

declare mycur cursor fast_forward forselect ID from sometable


open mycur
FETCH NEXT FROM mycur
INTO @AID

WHILE @@FETCH_STATUS = 0
begin
exec dbo.storedproc @AID

FETCH NEXT FROM mycurINTO @AID
end

CLOSE mycur
DEALLOCATE mycur





View Replies !   View Related
How To Avoid Credential Prompt
hi all
any one can tell me to avoid credential prompt i.e. uid & pwd when the first time going to render report from the reporting server

View Replies !   View Related
How To Avoid PrimaryKey Duplication?
Hi, I having a problem with my query...

I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...

I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...

What i'm doing is like this:

Use osa (Destination Database)
Go

DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)

My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.

How is this possible sir? Anyone care to help? Thanks in advance! More Power!

Best Regards

View Replies !   View Related
How To Avoid Indexes Become Fragmented


Last week i found on my database that almost all indexes had more than 70% of fragmentation. I rebuilt the indexes to fix the "problem" but today i found out that in one particular table (one with little more than 1 million records") the indexes are again 99,80%fragmented.

DidI dosomethin wrong? If you ask me if there are a lot of transactions running over that table, the answer is NO.Thereis one procces that could append (insert)between 500 and 1000 records but thishappens just once a month.



View Replies !   View Related
How To Avoid Casts In Stored Procedures?
Hi!Every time I do a drop down list from a stored procedure (in order to get a list of items) I get this error: System.InvalidCastException: Specified cast is not valid.in objectName = DataRecord.GetInt32(DataRecord.GetOrdinal("fieldName")); I'm enforced to make CAST(fieldName AS Int) in the specific stored procedure so that the error can go away (I used Int in this example, but the error appears with other types too).So, I was wondering... is there any way to avoid these casts or they are the only solution to my problem?Thanks, 

View Replies !   View Related
How To Avoid Concurrency In Sql Server 2000?
I have a database in sql server 2000. In this i have used identity(seed,increment) property so that unique ids may be available. Now if requests from diffrent users arrives at same time on the server, will there be a conflict because of ids? 

View Replies !   View Related
Avoid Duplicate Primary Key Error
How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ?
Thanks in advance !
stephen
 

View Replies !   View Related
How To Avoid 'invalid Connection' Error ?
hi all,
i got following error when i try to connect my asp.net page that is uploaded on ftp server,
then...



'Invalid connection'
'System.Data.SqlClient.SqlException: Invalid connection.'


any one can detect and solve it then it would be great help.

thx.

View Replies !   View Related
Need To Avoid Repeated Data In A DataGrid
Hi there :)

I am developing a system for my uni course and I am stuck a little problem...

Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.

I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.

How can I stop the repeats?

Heres my sql select code in my cs file:

string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id ";
SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection);
sqlConnection.Open();
lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection);
lecturersDG.DataBind();

And here is a pic of my Data Model:
Data Model Screenshot

Any ideas? Many thanks :) !

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved