INVALID LENGTH PARAMETER PASSED....

I have the follwoing stored procedure:

ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on

declare@.s varchar(500)
declare @.i int

set @.s = ''
select @.s = @.s + EventType + ','-- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...'-- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'

set nocount off
end --procedure
GO

When I run this and pass in a value of NULL, things work fine. When I
pass 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 the
datatypes look okay to me.

Any suggestions would be greatly appreciated.

Thanks!!On Jun 4, 12:43 pm, ansonee <anso...@.yahoo.comwrote:

Quote:

Originally Posted by

I have the follwoing stored procedure:
>
ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on
>
declare @.s varchar(500)
declare @.i int
>
set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x
>
set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass 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 the
datatypes look okay to me.
>
Any suggestions would be greatly appreciated.
>
Thanks!!

increase the value of @.s from 500 to 5000 maybe and test it ?|||ansonee (ansonee@.yahoo.com) writes:

Quote:

Originally Posted by

set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

I'm afraid that this relies on undefined behaviour. It may produce what
you want today. It might not tomorrow. If you are on SQL 2000, you
will need to run a cursor. On SQL 2005 there exists an option with
XML. See SQL Server MVP Antith Sen's article on
http://www.projectdmx.com/tsql/rowconcatenate.aspx for more information.

Quote:

Originally Posted by

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too
long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass 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

No, but there is LEFT, which is just a shortcut for SUBSTRING.

More to the point, you have failed to handle the case that the query
does not find any events, and @.i is the empty string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Invalid descriptor index

Good day
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:

> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid Column Name in SELECT

This select works as I expect:
SELECT exp_AcctNum,
exp_Amount,
CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
However, I am concerned about it having to call the function 3 times for
each row. Will SQL Server 2000 be smart enough to know that it is the same
call each time?
I tried using column aliases, but got errors.
For example, in Access I am use to doing something like:
SELECT Amount AS [Amt],
AMT as Amt2
FROM [tbl Expenses];
When I try to do the same thing in SQL Server,
SELECT exp_Amount AS [Amt],
AMT as Amt2
FROM Expenses
Go
it gives me a Invalid column name 'AMT'.
I had wanted to call the function once and create a column alias and then
use that alias for the CAST and SUBSTRING, but cannot get by the column
issue.
Something like
SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS 'stvalue',
CAST(Left(stvalue,2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'stvalue'.
I would really appreciate some guidance on this one!
Thanks.On Thu, 1 Jun 2006 17:40:15 -0500, MikeV06 wrote:

>This select works as I expect:
>SELECT exp_AcctNum,
> exp_Amount,
> CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
> SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
>However, I am concerned about it having to call the function 3 times for
>each row. Will SQL Server 2000 be smart enough to know that it is the same
>call each time?
Hi Mike,
Unfortunately, no.
(snip)
>I had wanted to call the function once and create a column alias and then
>use that alias for the CAST and SUBSTRING, but cannot get by the column
>issue.
>Something like
>SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS 'stvalue',
> CAST(Left(stvalue,2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
You can't do it this way. A column alias can only be used in the ORDER
BY clause, nowhere else in the query.
You can use a derived table, though:
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
FROM (SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
Hugo Kornelis, SQL Server MVP|||On Fri, 02 Jun 2006 01:00:44 +0200, Hugo Kornelis wrote:
[snip]

> You can't do it this way. A column alias can only be used in the ORDER
> BY clause, nowhere else in the query.
> You can use a derived table, though:
> SELECT exp_AcctNum,
> exp_Amount,
> stvalue,
> CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
> FROM (SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS stvalue
> FROM Expenses) AS d
Thank you very much. I was miles away from this solution and had already
spent 1 day trying several things that did not work. It needs a little
touch up; however, what I finally came up from your template works. What I
really do like is that the call to the function is only made once.
I guess I could put the derived table in a view and have this view call
that view (which ends up being called by another view to get the final
stratification result table). I am not sure I see any benefit in taking
that approach, but may try it just to see what happens. Uhm, maybe another
UDF instead of a View ... too tired to really think at this point.
You have made my day -- I think I will give it up for the day. Thanks.
Mike.
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'prestrat')
DROP VIEW prestrat
GO
CREATE VIEW prestrat
AS
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc],
posrecs,
posdols,
negrecs,
negdols,
[posrecs]+[negrecs] AS totrecs,
[posdols]+[negdols] AS totdols,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType
FROM (SELECT exp_AcctNum,
exp_Amount,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType,
CASE WHEN exp_Amount>=0 THEN 1 ELSE 0 END AS posrecs,
CASE WHEN exp_Amount>=0 THEN exp_Amount ELSE 0 END AS
posdols,
CASE WHEN exp_Amount<0 THEN 0 ELSE 1 END AS negrecs,
CASE WHEN exp_Amount<0 THEN 0 ELSE exp_Amount END AS negdols,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
WHERE Exp_RecordType = '2'
Go

Invalid Column Name error,

Hi,

I'm having trouble with the following query.

select convert(datetime, convert(int, audit_timestamp - 0.5)) as auditdate, database_name, sum(FileSize) as FileSize, sum(fileUsed) as FileUsed, sum(FileFree) as FileFree
from tbl_dbSize
where auditdate > getDate() -7
and lower(server_name) = 'xxx'
group by auditdate, database_name

Basically what I am trying to do is convert my records in the select statement (as I don't want to update the actual data) which were recorded on the same day (however with different times, i.e. 27/12/2003 00:01:03 , 27/12/2003 00:01:03) to be the same (i.e. 27/12/2003 00:00:00).

I keep getting the error,

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'auditdate'.

Any help appreciated.First, use this to truncate your datetime values:

cast(Convert(varchar(10), audit_timestamp, 120) as datetime) as auditdate

Second, you can't reference AuditDate by name; you have to reference it by the formula:

where cast(Convert(varchar(10), audit_timestamp, 120) as datetime) > getDate() -7
.
.
.
group by cast(Convert(varchar(10), audit_timestamp, 120) as datetime), database_name

It would be nice if TSQL allowed you to define the formula once and then refer to it by name, but the name isn't assigned until the query is completed and so is not available to the parser. (The exception is if you query is a subquery of another query, but that is another discussion...).

blindman|||reference to a column alias is allowed only in order by clause|||Thanks for the help.

It's working now!

Intersection of N sets

Imagine a table that enumerates membership of items to some set:
create table sets
(
setId int not null,
itemId int not null
);
some data:
set 1 = {1,3,5,7,9}
set 2 = {1,2,3,4,5}
set 3 = {4,5,6,7}
translated to this model:
setId, itemId
1,1
1,3
1,5
1,7
1,9
2,1
2,2
2,3
2,4
2,5
3,4
3,5
3,6
3,7
Consider another table containing an enumeration of sets to calculate the
intersection of:
create table setIntersection
(
setId int not null
);
If the setIntersection table contains the records {1,2,3}, I'd like to calcu
late
the intersection of the items contained in the sets 1, 2 and 3.
In the example above, this would be the {5}.
While this is trivial to do in a typical imperative fashion (looping inside
an SP), I'm wondering if it's possible to perform this operation in a single
query, perhaps using an CTE.Essentially, it would be a join on the the tables, each representing a set.
SELECT t1.itemid -- or t2.itemid or t3.itemid
FROM tbl t1,
tbl t2,
tbl t3
WHERE t1.itemid = t2.itemid
AND t2.itemid = t3.itemid
AND t1.setid = 1
AND t2.setid = 2
AND t3.setid = 3 ;
Anith|||Taras Tielkes (taras.tielkes@.gmail.com) writes:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
>...
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to
> calculate the intersection of the items contained in the sets 1, 2 and
> 3. In the example above, this would be the {5}.
SELECT itemId
FROM sets s
WHERE EXISTS (SELECT *
FROM setIntersection sI
WHERE sI.setId = s.setId)
GROUP BY itemId
HAVING COUNT(*) = (SELECT COUNT(*) FROM setIntersection)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ah, but I meant to ask the question in a generic way: "how can I writer a
query that will perform the required operation for an arbitrary number of
sets in the example model. Say 100 defined sets :-)

> Essentially, it would be a join on the the tables, each representing a
> set.
> SELECT t1.itemid -- or t2.itemid or t3.itemid
> FROM tbl t1,
> tbl t2,
> tbl t3
> WHERE t1.itemid = t2.itemid
> AND t2.itemid = t3.itemid
> AND t1.setid = 1
> AND t2.setid = 2
> AND t3.setid = 3 ;|||In that case, Erland's response should help. You may also want to search the
archives for "relational division" to find some related examples.
Anith|||Taras Tielkes wrote:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
> translated to this model:
> setId, itemId
> 1,1
> 1,3
> 1,5
> 1,7
> 1,9
> 2,1
> 2,2
> 2,3
> 2,4
> 2,5
> 3,4
> 3,5
> 3,6
> 3,7
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to cal
culate
> the intersection of the items contained in the sets 1, 2 and 3.
> In the example above, this would be the {5}.
> While this is trivial to do in a typical imperative fashion (looping insid
e
> an SP), I'm wondering if it's possible to perform this operation in a sing
le
> query, perhaps using an CTE.
It's relational division:
sets/setIntersection