Error in query; "Invalid length parameter passed to the substring function"

Hi
i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :)
colums:
VLF_InfectionDestination is nvarchar 254

SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
FROM tb_AVVirusLog INNER JOIN
__CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName
WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @.fyear + @.fmonth + @.fday AND @.tyear + @.tmonth + @.tday) AND
(__CustomerMachines002.folder_id = @.folderId) [OR
(CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @.fyear + @.fmonth + @.fday AND @.tyear + @.tmonth + @.tday) AND
(tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@.%')]
GROUP BY tb_AVVirusLog.VLF_VirusName
HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie'))
ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESCGenerally, you get this error when you pass in a bad parameter, like a negative startpoint, or a length parameter that takes you out of bounds of the string. Maybe instead of convert (varchar), you should try convert(varchar(10)). I don't see substring in there.|||naa cant get it to work... that damn error pop up no matter what i do...|||is i reverse the order and put in a AND insteed it works... like this
[AND
(CONVERT (tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@.%')] AND (varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @.fyear + @.fmonth + @.fday AND @.tyear + @.tmonth + @.tday)]

how come o got that error the other way?

//Mr|||Read the sticky at the top of this forum..Post us the DDL and some sample data and what the expected results are suppose to be...|||How about this?

DECLARE @.date1 datetime, @.date2 datetime
SELECT @.date1 = CONVERT(datetime, @.fyear + @.fmonth + @.fday)
, @.date2 = CONVERT(datetime, @.tyear + @.tmonth + @.tday)

SELECT TOP 10 a.VLF_VirusName
, COUNT(a.VLF_VirusName) AS number
FROM tb_AVVirusLog a
INNER JOIN __CustomerMachines002 b
ON a.CLF_ComputerName = b.FalseName
WHERE ( a.CLF_LogGenerationTime > @.Date1 AND a.CLF_LogGenerationTime <= @.Date2
AND b.folder_id = @.folderId)
OR ( a.CLF_LogGenerationTime > @.Date1 AND a.CLF_LogGenerationTime <= @.Date2
AND a.VLF_InfectionDestination LIKE N'%@.%')
GROUP BY a.VLF_VirusName
HAVING a.VLF_VirusName NOT LIKE N'cookie'
ORDER BY COUNT(a.VLF_VirusName) DESC|||thx, i manage this to work, and i got some new ideas how to do things... :)

//Mr|||thx, i manage this to work, and i got some new ideas how to do things... :)

//Mr

...and you're not gonna share it with us...I'll never gety thos 5 minutes back...

Error in function argument

I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm.

I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the
wkdy20070416-a.rm filename so that when it's all said and done that entry would read:

WeekdayA20070416.rm
WeekdayB20070416.rm
Conversation20070416.rm

Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement.

Code Snippet

UPDATE T_Programs_TestCopy
(SET RealAudioLink = REPLACE(RealAudioLink, '-a', '')
AND
(SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA')
WHERE (RealAudioLink LIKE 'wkdy%'))

I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer.

Thanks

Here is an example of creating a custom function to make the changes, and then using that function in updating the table.

Code Snippet

CREATE FUNCTION dbo.fnCleanMyData
( @.BadDataIn varchar(2000) )
RETURNS varchar(2000)
AS
BEGIN
SET @.BadDataIn =
CASE
WHEN patindex( '%-a.rm', @.BadDataIn ) > 0
THEN replace( replace( stuff( @.BadDataIn, 5, 1, 'A' ), 'wkdy', 'Weekday' ), '-a', '' )
WHEN patindex( '%-b.rm', @.BadDataIn ) > 0
THEN replace( replace( stuff( @.BadDataIn, 5, 1, 'B' ), 'wkdy', 'Weekday' ), '-b', '' )
WHEN patindex( 'conv%', @.BadDataIn ) > 0
THEN replace( @.BadDataIn, 'conv', 'Conversation' )
END
RETURN @.BadDataIn
END
GO

DECLARE @.MyTable table
( FileNames varchar(100) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'wkdy20070416-a.rm' )
INSERT INTO @.MyTable VALUES ( 'wkdy20070416-b.rm' )
INSERT INTO @.MyTable VALUES ( 'conv20070416.rm' )

SELECT FileNames
FROM @.MyTable

FileNames
-
wkdy20070416-a.rm
wkdy20070416-b.rm
conv20070416.rm

UPDATE @.MyTable
SET FileNames = dbo.fnCleanMyData( FileNames )

SELECT FileNames
FROM @.MyTable

FileNames
--
WeekdayA0070416.rm
WeekdayB0070416.rm
Conversation20070416.rm

Change the function name to whatever you like, and I have used a table variable in this example. Use your own table and column names.

Error in convert function

I use convert to work with Hijri functions
when i write
select convert (datetime,'29-10-1426',131)
the result is correct
2005-12-01 00:00:00.000

when I increase the date by one
select convert (datetime,'30-10-1426',131)
I get
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Why and how i solve itAs far as I know, the month 10 in Hijri calendar only has 29 days, so the second statement is supposed to fail. The next day to convert (datetime,'30-10-1426',131) is

select convert (datetime,'1-11-1426',131)

Regards,
Jun|||

The 10 month in Hijri calender is 30 days so
In Hijri calender any month can be 29 or 30 days
thanks

Error in ActiveX script in DTS

Hi,
When i m trying to execute the following function i m getting
Error Message is Invalid Procedure Call or arguement DTSSource

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("acct_id") = DTSSource("Account_ID")
DTSDestination("acct_nm") = DTSSource("Account_Name")
DTSDestination("acct_type") = 1
DTSDestination("acct_sts") = DTSSource("Enabled")

Main = DTSTransformStat_OK
End Function

thnks in advanceis DTSDestination("acct_type") of type integer,numeric or decimal?

if not, I think you have to add double quotes around the "1". Hope it helps :)

error in accessing stored procedure

i'm trying to insert into db sql 2000 through a stored procedure .. i got this error " Procedure or function newuser has too many arguments specified "

this is the procedure :

ALTER PROCEDURE newuser

(@.username_1 [nvarchar](80),

@.email_2 [nvarchar](50),

@.password_3 [nvarchar](256),

@.Country_ID_4 [int],

@.city_id_5 [nvarchar](10),

@.gender_6 [nvarchar](50),

@.age_7 [int],

@.fname_8 [nvarchar](50),

@.lname_9 [nvarchar](50),

@.birthdate_10 [datetime])

AS INSERT INTO [Brazers].[dbo].[users]

( [username],

[email],

[password],

[Country.ID],

[city.id],

[gender],

[age],

[fname],

[lname],

[birthdate])

VALUES

( @.username_1,

@.email_2,

@.password_3,

@.Country_ID_4,

@.city_id_5,

@.gender_6,

@.age_7,

@.fname_8,

@.lname_9,

@.birthdate_10)


& that 's the code in asp page :

Dim param As New SqlClient.SqlParameter

SqlConnection1.Open()

param.ParameterName = "@.username_1"

param.Value = TextBox1.Text

param.Direction = ParameterDirection.Input

SqlCommand1.Parameters.Add(param)

SqlCommand1.ExecuteNonQuery()


plz .. waiting any solve for this problem immediatelyit looks to me like you have too FEW arguments specified. the sproc takes 10 params, none of them optional, and you are only passing ONE.|||sorry jazmine but i don't know wt to do to make it works .. plz tell me steps

Error in a Sinmple Function

Hi,
I am using the following Code in the Report Properties. This function
displays the manufactuere logo when brand parameter is null and when brand
is not null it should display the brand logo.
Function ImageDisp(byVal a as String)as String
Select Case a
case ""
return
"https://192.168.1.16/dev/ReportsImages/"&First(Fields!IMAGEORIG.Value,
"DataSet1");
case else
return
"https://192.168.1.16/dev/ReportsImages/"&First(Fields!BrandLogo.Value,
"DataSet1");
End Select
End Function
In Image2 value is put =Code.ImageDisp(Parameters!Brand.Value) . Now the
problem is that on previewing I am getting the error "There is an error in
line 33 of custom code. Name First is not declared."
Any help is appreciated.
Thanks
--
pmudI believe when the custom code is executed, you cannot refer to fields in the
dataset or use RDL functions. You will have to add a parameter to your
function to pass in the FIRST(Field) value.
"pmud" wrote:
> Hi,
> I am using the following Code in the Report Properties. This function
> displays the manufactuere logo when brand parameter is null and when brand
> is not null it should display the brand logo.
> Function ImageDisp(byVal a as String)as String
> Select Case a
> case ""
> return
> "https://192.168.1.16/dev/ReportsImages/"&First(Fields!IMAGEORIG.Value,
> "DataSet1");
> case else
> return
> "https://192.168.1.16/dev/ReportsImages/"&First(Fields!BrandLogo.Value,
> "DataSet1");
> End Select
> End Function
> In Image2 value is put =Code.ImageDisp(Parameters!Brand.Value) . Now the
> problem is that on previewing I am getting the error "There is an error in
> line 33 of custom code. Name First is not declared."
> Any help is appreciated.
> Thanks
> --
> pmud