Sql 2005 ... Invalid Length Parameter Passed To The SUBSTRING Function.
Oct 9, 2006
Hi,
I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error,
��SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. ��
Through research I have no clue as what I need to do, or where to look.
The package runs without error when I execute the package itself.
Any help is greatly appreciated.
Thanks,
Lori
select substring(ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''),1,charindex(',',ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''))-1) from tbl_lrf_company_details_with_codes
but i am getting the error as "Invalid length parameter passed to the SUBSTRING function." Please advice Thanks In advance
I am using a simple procedure to pivot results (found in another forum and adapted it). It is done on SQL Server 2005 server with all service packs. Procedure: ************** ALTER Procedure [dbo].[EthnicityPivot] @StDate as Datetime, @EndDate as Datetime as begin DECLARE @Teams varchar(2000)
truncate table ForEthnicPivot
INSERT INTO ForEthnicPivot SELECT DISTINCT COUNT(ID), Team, Ethnicity FROM dbo._EthnicityByTeamEpisode where Startdate between @StDate and @EndDate GROUP BY Ethnicity, Team
SET @Teams = '' --// Get a list of the pivot columns that are important to you.
SELECT @Teams = @Teams + '[' + Team + '],' FROM (SELECT Distinct Team FROM ForEthnicPivot) Team --// Remove the trailing comma
SET @Teams = LEFT(@Teams, LEN(@Teams)-1) --// Now execute the Select with the PIVOT and dynamically add the list --// of dates for the columns EXEC( 'SELECT * FROM ForEthnicPivot PIVOT (SUM(countID) FOR Team IN (' + @Teams + ')) AS X' ) end ************
I can call the function: exec EthnicityPivot '01/01/2007','09/09/2007'
and it works fine in SQL analyzer, but when I want to use it in Visual Studio in a new report I am getting this error message:
There is an error in the query. Invalid length parameter passed to the SUBSTRING function. Incorrect syntax near ')'.
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) DESC
An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error: "Invalid length parameter passed to the substring function."
Private Sub setControls(ByVal dr As SqlDataReader) If (dr.Read()) Then '<--*******problem line*******
The SqlDataReader (orderReader) doesn't blow up or anything until I call .Read() (and, as mentioned, this problem only occurs for one order). What could be happening here?
I have the follwoing stored procedure:ALTER procedure [dbo].[up_GetExecutionContext](@ExecutionGUID int = null) asbeginset nocount ondeclare@s varchar(500)declare @i intset @s = ''select @s = @s + EventType + ','-- Dynamically build the list ofeventsfrom(select distinct top 100 percent [event] as EventTypefrom dbo.PackageStepwhere (@ExecutionGUID is null or PackageStep.packagerunid =@ExecutionGUID)order by 1) as xset @i = len(@s)select case @iwhen 500 then left(@s, @i - 3) + '...'-- If string is too long thenterminate with '...'else left(@s, @i - 1) -- else just remove the final commaend as 'Context'set nocount offend --procedureGOWhen I run this and pass in a value of NULL, things work fine. When Ipass in an actual value (i.e. 15198), I get the following message:Invalid length parameter passed to the SUBSTRING function.There is no SUBSTRING being used anywhere in the query and thedatatypes look okay to me.Any suggestions would be greatly appreciated.Thanks!!
update vendor_invoice set de_ftpdownload= (select SUBSTRING(de_ftpdownload,1,CHARINDEX('.',de_ftpdow nload,0)-1)as de_ftpdownload from vendor_invoice) where vendor_invoice_id =931
This get me this error.... so what is the max allowable length of substring function.
Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function. The statement has been terminated. Thanks Al
I'm trying to create a function that splits up a column by spaces, andI thought creating a function that finds the spaces with CHARINDEX andthen SUBSTRING on those values would an approach. I get an errorsaying that the I have an Invalid column 'Course_Number'. Not surewhy but I am very new to User Defined Functions. Here is what I haveso far:CREATE FUNCTION CourseEvalBreakdown(@fskey int)RETURNS @CourseTable TABLE(Col CHAR(2),Area CHAR(4),Number CHAR(4),Section CHAR(4),Term CHAR(3))ASBEGINDECLARE@Ind1 tinyint,@Ind2 tinyint,@Rows intDECLARE @crstbl TABLE (FStaffKey int,Course_Number char(20),Term char(3),Col char(2),Area char(4),Number char(4),Section char(3))INSERT INTO @crstbl (FStaffKey, Course_Number, Term)SELECT FStaffKey, Course_Number, TermFROM EvalWHERE FStaffKey = @fskeySET @Rows = @@rowcountWHILE @Rows 0BEGINSET @Ind1 = CHARINDEX(' ', Course_Number, 4)SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',Course_Number, 4)+1))UPDATE @crstblSET Col = SUBSTRING(Course_Number, 1, 2)WHERE FStaffKey = @fskeyUPDATE @crstblSET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))WHERE FStaffKey = @fskeyUPDATE @crstblSET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-@Ind1)-1))WHERE FStaffKey = @fskeyUPDATE @crstblSET Section = SUBSTRING(Course_Number, @Ind2+1, 3)WHERE FStaffKey = @fskeyENDINSERT @CourseTableSELECT Col, Area, Number, Section, Term FROM @crstblRETURNENDGO
I have a ##temp table which collects command (varchar(120)) info from sysjobsteps table as follows: command ------------------------------ BACKUP DATABASE FDMS_11111_2 To Backup_11111_2_4Thu with init, name = 'Backup of Job_11111_2_4Thu'
Now, I need to extract the database name from the command string: It did return the correct database name but ended with error message.
select substring(command, 17, charindex(' To Backup_',Command)-17) from ##tempServerNameDatabaseNameJobHistory
I have a procedure that calls a SVF to convert an xmldocument. The ultimate purpose is to update the xml in a column in a multi-million row table. The xml is stored as varchar(MAX), it was supposed to carry any type of text, initially at least.
My question is: why is the xml-parsing performed inside the function much slower when i pass the xmldocument as type xml than when it is passed as varchar(MAX) and the CAST to xml is within the function? Does processing the xml input parameter in SlowFunction involve expensive crossing of some context border?
The two versions of the SVF (they return the rowcount in this simplified example):
CREATE FUNCTION [dbo].[FastFunction] ( @inDetaljerText varchar(MAX) ) RETURNS int
[Code] ....
The two versions of the SP
CREATE PROCEDURE [dbo].[FastProcedure] AS BEGIN SET NOCOUNT ON; select dbo.FastFunction(al.Detaljer)
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
I'm using a substring expression in a Derived Column transformation to create a new column (string data) out of a segment of another. The length of the new column data is calculated at run time based on the contents of other columns, and under some circumstances may be zero.
Is this kocher, or will it lead to 'unpredictable' results? Coz unexpected results is what I'm getting, not in the row concerned, but in the rows following......
is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..
I have a table
CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) ) AS BEGIN SELECT KeyName FROM TEST001 WHERE KeyName = @KeyName END KeyName = @KeyName
Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..
IS there any way to find out all such usage on the ALL Procedures in the Database ?
Hi All, We have transactional replication between two SQL Server 2000, SP4 and database is in simple recovery. Occassionally Logreader agent fails with error 9003 (The LSN (164051:119090:22) passed to log scan in database 'ReportDB' is invalid). But in April 2007 this error has occurred multiple times. We have opened the case with PSS in April 2007 but till now there is no concrete solution.
Error details in ErrorLog: 2007-04-24 16:49:09.79 spid59 Error: 9003, Severity: 20, State: 1 2007-04-24 16:49:09.79 spid59 The LSN (164051:119090:22) passed to log scan in database 'ReportDB' is invalid.. Error details in Log reader agent: Repl Agent Status: 3 Publisher: {call sp_repldone ( 0x000280d30001d1320016, 0x000280d30001d1320016, 0, 0)} Publisher: {call sp_replcmds (100, 0)} Status: 0, code: 0, text: 'The process could not execute 'sp_replcmds' on 'BR14D135R17'.'. The process could not execute 'sp_replcmds' on 'BR14D135R17'. Repl Agent Status: 6 Status: 0, code: 9003, text: 'The LSN (164051:119090:22) passed to log scan in database 'ReportDB' is invalid.'.
From above error details it can be seen that sp_repldone is already called for LSN 164051:119090:22 (0x000280d30001d1320016) which means it is distributed. Since LSN is distributed, transaction log of publisher for that LSN can be truncated by checkpoint process. And afterward if logreader issues sp_replcmds for that LSN, we will get 9003 error. So, question is why Logreader is looking for LSN if that is already distributed. When checked "DBCC Opentran", it also reflect oldest distributed LSN as 164051:119090:22.
We tried changing the recovery model to FULL but that has only delayed the occurrence. Once we have noticed that LSN mentioned in Error 9003 was already backed up one day before. And we don't think replication latency to be more than 3 hrs (maximum).
After PSS recommendation, we have applied the hotfix 2187 but error has occurred again after one month. Other recommendation is to disable read cache of hardware controller to avoid stale read(that we are working on). Current setting is 100% read and 0% Write.
Other Details: SQL Server 2000 Enterprise Edition SP4 with Hotfix 2187 Window 2000 Advanced Server. RAID 5 with array controller as HP Smart Array 641 Controller
Please let me know if you have faced such issue and have any idea on this problem.
The subject pretty much sums the problem up. I am trying to get a database installed on Server 2008 RC0. The public information says SQL 2005 SP2 should work, but I cannot get the base SQL 2005 installed in order to apply the service pack. When I run the installation, I immediately get an error when trying to install the pre-requisites. Specifically, the .NET 2.0 Framework errors with code 87, "invalid parameter".
I have found a couple references to this error code, but nothing to do with Server 2008 and RC0. I have tried using a directory with no spaces, and tried the EXE vs ISO installation, but they everything fails. I don't know if there is a way to bypass the .NET 2.0 Framework install - since it's already on the machine anyway.
SELECT SUM(x.qty * ISNULL(p.price_mn,0)) FROM (SELECT [Code] = A.A.value('@code','varchar(20)'), [Qty] = A.A.value('@quantity','INT') FROM @xml.nodes('/DocumentElement/data/item') AS A(A)) X LEFT JOIN productprice_t P ON p.code_id = x.code
The question is, how can I do this if the XML is in a different format & doesn't use any attributes? So it looks like this (which I'm getting from an ADO.Net datatable using .WriteXML): <DocumentElement> <data> <code>ABCDEFG</mercurycode> <quantity>1</quantity> <sort>0</sort> </data> <data> <mercurycode>XCDFEG</mercurycode> <quantity>2</quantity> <sort>0</sort> </data> <data> <code>ABCDEFG</mercurycode> <quantity>10</quantity> <sort>0</sort> </data> </DocumentElement>
I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.
When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects.
However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.
Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ?
I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.
Any help and/or suggestions are appreciated !!! Thanks.
I have a requirement where, I need to get the value of parameter from a query string of url and use it in my report. Ex:- http://www.mysite.com?name=bobby
From the above url, I have to take the value of name and be able to use it in my report query or assign to a parameter.
I have a boolean parameter on my report that the user will enter. Currently, it shows as an option button with True/False beside each option as the words used. Is it possible to have a check box instead of an option button for the user instead? Thanks in advance!
it's early in the morning (well it is here anyway) and i need a shot of logic...
in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning
quick sample of what the data in this column may look like...
declare @t table (col varchar(30)) insert into @t select '2' union all select '[5] [4]' union all select ' [12] [1]'
so i need to get...
col ------- 2 5 12
EDIT- OK, so i get this to work but only if there is actually square brackets
declare @t table (col varchar(30)) insert into @t --select '2'union all select '[5] [4]'union all select ' [12] [1]'
select col ,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1) from @t
when i tried to run a DTS which transfer bulk data between 2 SQL servers, i got following error message: ================================================== ============ Error: -2147467259 (80004005); Provider Error: 4815 (12CF) Error string: Received invalid column length from bcp client. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 ================================================== ===========
if anybody has encounter the same problem before? after testing, i think it's must related with network traffic problem. but i can not figure out how to solve it.
I am trying to pass multiple values as parameters into my update command:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@selectedusersparam)));I develop my parameter (@selectedusersparam) using the following subroutine: Private Sub btnAddUsersToDeployment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUsersToDeployment.ClickDim iVal As Integer = 0Dim SelectedCollection As StringSelectedCollection = ""If (lsbUsersAvail.Items).Count > 1 ThenFor iVal = 0 To lsbUsersAvail.Items.Count - 1If lsbUsersAvail.Items(iVal).Selected = True ThenSelectedCollection = SelectedCollection & "," & lsbUsersAvail.Items(iVal).ValueEnd IfNextSelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))Session.Item("SelectedCollectionSession") = SelectedCollectionSqlDataSource4.Update()ltlUsersMessage.Text = String.Empty 'UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (@selectedusersparam))'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"ElseltlUsersMessage.Text = "Select users before adding to deployment. Hold Control for multiselect"End IfEnd SubFor some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the queryI don't understand why.
I am using two drop downs, like so: <asp:DropDownList ID="ChurchStateDrop" runat="server" DataSourceID="ChurchStateDropData" DataTextField="State" DataValueField="State" AutoPostBack="True" onselectedindexchanged="ChurchStateDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All States" /></asp:DropDownList> <asp:DropDownList ID="ChurchCityDrop" runat="server" DataSourceID="ChurchCityDropData" DataTextField="City" DataValueField="City" AutoPostBack="True" onselectedindexchanged="ChurchCityDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All Cities" /></asp:DropDownList> I have ????? in the value fields because I don't know what value needs to be passed to negate filtering (to choose all). The Dropdowns have the following SQLDatasources:<asp:SqlDataSource ID="ChurchStateDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [State] FROM [ChurchView]" DataSourceMode="DataReader"></asp:SqlDataSource> <asp:SqlDataSource ID="ChurchCityDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [City] FROM [ChurchView] WHERE ([State] = @State)" DataSourceMode="DataReader"> <SelectParameters> <asp:ControlParameter ControlID="ChurchStateDrop" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource> Now, lets say I wanted to pass a value to the WHERE statement in ChurchCityDropData to coincide with 'All States', what would I replace value="??????" with? Now you may think I'm crazy to do such a thing, but this actually has to do with adding a Denomination Dropdown to show Denominations from all states or all cities. I will figure out the best logic for that later, I just want to know the wildcard to pass to the parameter to choose all states (negate filtering).
I am new to sql and very familiar with access. I am using a verylarge database(130M records) in ms sql2000 and think I need to frontend it with access for reports and forms, etc..I have some questions:1) Is there a way in SQL to prompt a user for input at the running ofa query like the [what is your name] construct in an access query?2) I can't seem to create calculated fields in a view in sql. Inaccess I put "total:=hours*rate" and a new column would be createdwhich would contain the total.3) I normally would create a form in access from which the accessqueries would pull their variables and then insert those variables inthe queries or reports, etc. How would this be accomplished usingsql.4) Lastly and probably most basic. How does a "real" sql developercreate reports and forms to interact with the enduser? I am usingaccess as a front end, but am open to suggestions.Thanks,Brad
I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.
Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.
