Error when updating

I get the following error when I execute the statement below. 'Invalid
length parameter passed to the substring function'
Update Tbl_Txn_UT
Set Fxd_Units =
CASE
WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%' and TxnUnitsDur like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%')
THEN TxnUnitsBase
WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
TxnUnitsBase
Else TxnUnits
END
Where TxnUnits is not Null
It took a while to get this code correct with help and now evidently one of
the answers from the math causes an error. Any help is appreciated.Hi
Posting DDL an example data would help to see what you are trying to achive
and help re-create the problem. The statement is assuming that TxnUnitsDur i
s
at least one character which could is probably causing the problem.
John
"Job" wrote:

> I get the following error when I execute the statement below. 'Invalid
> length parameter passed to the substring function'
> Update Tbl_Txn_UT
> Set Fxd_Units =
> CASE
> WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%' and TxnUnitsDur like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
> WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%')
> THEN TxnUnitsBase
> WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
> TxnUnitsBase
> Else TxnUnits
> END
> Where TxnUnits is not Null
>
> It took a while to get this code correct with help and now evidently one o
f
> the answers from the math causes an error. Any help is appreciated.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Ju
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Hi
You seem to have some problem posting?
John
"Job" <Job@.nomail.com> wrote in message
news:uUH$jpCoFHA.708@.TK2MSFTNGP09.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>|||John, your correct. I included the case statement if len(TxnUnitsDur )
= 0 then TxnUnitsBase and it worked perfectly.
Cheers!
Job wrote:
> Ju
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>
>
>

Error when executing Report

Hi all,
I have created about 40 reports that access our core ERP solution. All
these reports function perfectly. In the last day I have created 2 new
reports that access a different server/database thus a new shared datasource
was created. However, I am intermittantly receiving the following error
when I attempt to execute the two new reports:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
a.. Cannot create a connection to data source 'Blah2'.
(rsErrorOpeningConnection) Get Online Help
a.. SQL Server does not exist or access denied.
The reports function everytime in Visual Studio. What is going on?
Thanks
ClintSounds like a security issue. What does your datasource specify for a
username/password?
Steve
"AshVsAOD" wrote:
> Hi all,
> I have created about 40 reports that access our core ERP solution. All
> these reports function perfectly. In the last day I have created 2 new
> reports that access a different server/database thus a new shared datasource
> was created. However, I am intermittantly receiving the following error
> when I attempt to execute the two new reports:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> a.. Cannot create a connection to data source 'Blah2'.
> (rsErrorOpeningConnection) Get Online Help
> a.. SQL Server does not exist or access denied.
> The reports function everytime in Visual Studio. What is going on?
> Thanks
> Clint
>
>|||I thought that too. I have changed the datasource to be both shared and
custom. The username and password are correct. I am at a loss..
Especially seeing how it is so intermittant.
"SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> Sounds like a security issue. What does your datasource specify for a
> username/password?
> Steve
> "AshVsAOD" wrote:
> > Hi all,
> >
> > I have created about 40 reports that access our core ERP solution. All
> > these reports function perfectly. In the last day I have created 2 new
> > reports that access a different server/database thus a new shared
datasource
> > was created. However, I am intermittantly receiving the following error
> > when I attempt to execute the two new reports:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
Get
> > Online Help
> > a.. Cannot create a connection to data source 'Blah2'.
> > (rsErrorOpeningConnection) Get Online Help
> > a.. SQL Server does not exist or access denied.
> > The reports function everytime in Visual Studio. What is going on?
> >
> > Thanks
> > Clint
> >
> >
> >|||Not sure whether this will help. When u deploy the shared datsource to the
reportserver, try inputting the connection info and then run the report. I
had the same issue, and the connection trying the dba changed pointed to a
different instance..
Suresh
"AshVsAOD" wrote:
> I thought that too. I have changed the datasource to be both shared and
> custom. The username and password are correct. I am at a loss..
> Especially seeing how it is so intermittant.
> "SteveIrwin" <SteveIrwin@.discussions.microsoft.com> wrote in message
> news:6EC53430-5F17-43A6-BF6C-426CF018CDBC@.microsoft.com...
> > Sounds like a security issue. What does your datasource specify for a
> > username/password?
> >
> > Steve
> >
> > "AshVsAOD" wrote:
> >
> > > Hi all,
> > >
> > > I have created about 40 reports that access our core ERP solution. All
> > > these reports function perfectly. In the last day I have created 2 new
> > > reports that access a different server/database thus a new shared
> datasource
> > > was created. However, I am intermittantly receiving the following error
> > > when I attempt to execute the two new reports:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> Get
> > > Online Help
> > > a.. Cannot create a connection to data source 'Blah2'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > a.. SQL Server does not exist or access denied.
> > > The reports function everytime in Visual Studio. What is going on?
> > >
> > > Thanks
> > > Clint
> > >
> > >
> > >
>
>sql

Error when creating a table based function

I'm trying to create a table based function using SMO. The problem is that I get an error saying that I need to set the DataType property on the function before it can be created. However, the documentation clearly says that the DataType property is null for Table based functions. I can't even set this value to null either!

Here's a code snippet that shows what I'm trying to do.

if (returnType.ToLower() != "table")

{

Type clrDataType = Type.GetType(returnType);

returnSqlDataType = GetSqlDataType(clrDataType);

function.FunctionType = Smo.UserDefinedFunctionType.Scalar;

function.DataType = returnSqlDataType;

}

else

{

function.FunctionType = Smo.UserDefinedFunctionType.Table;

function.TableVariableName = xmlNode.Attributes["tableVariableName"].Value;

XmlNode returnTableDescription = GetChildOfParent(xmlNode, "ReturnTableDescription");

XmlNode columnsNode = GetChildOfParent(returnTableDescription, "Columns");

foreach (XmlNode columnNode in columnsNode.ChildNodes)

{

function.Columns.Add(CreateUdfColumn(function, columnNode));

}

}

The xmlNode that is being referenced is an XmlNode that is describing how to create the table. returnType is a variable that is set from one of the xmlNode's attributes.

When I call on function.Create() then I get the error that I posted above. Any help anyone can give is appreciated. One thing I also tried doing was to connect to an already existing Table based function and look at its SMO properties. Sure enough, its DataType property was null.

MSFT: Is this a bug?

Never mind on this post.....it was my mistake. The DataType property not being set wasn't on the function itself, but rather on the columns collection.

Problem solved.

Error using scalar valued function in Stored Procedure

Hi,

I am getting the following error msg while executing a stored procedure (getAllCollections) which uses a scalar valued function.

error msg:

Could not find server 'Select dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Stored procedure:

ALTER PROCEDURE [dbo].[getAllCollections]

(

@.pCids varchar(7500)

)

AS

declare @.cmd varchar(8000)

set @.cmd='Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (' +@.pCids + ')'

print @.cmd

exec @.cmd

Where as the 'dbo.getCollectionList' is the scalar valued function. If i execute the query separately as below it works fine:

Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (1234)

I believe i am doing some silly mistake somewhere. Any help will be much appreciated.

Thanks.

Saran:

I think all you need to do is change this:

exec @.cmd

to this:

exec ( @.cmd )

Dave

|||

When you do exec @.cmd it takes the contents of @.cmd as a stored procedure name. Since you had 3 periods in your string, it was thinking 'SELECT dbo' was a server name, and 'collection_id) as cList from CollectionT c WHERE c' as a database.

Like Dave said, use exec (@.cmd)

|||

You should replace the dynamic SQL with a safer solution. You can use a TSQL table-valued function to split the list of IDs and use it like:

Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c

WHERE exists(

select * from split_str as s

where s.value = c.collection_id

)

Please see the link below for some solutions that show how to split a string into a table or list of values.

http://www.sommarskog.se/arrays-in-sql.html

|||

Thanks a lot. It solved the issue. Silly me. :)

- Saran.

Error trapping of datasource control

Hello,

I encountered an interesting situation. I have a gridview and a sqldatasource. It has delete function. When I delete a record an error of foreign key violation is raised. I would like to trap this error and give a user friendly message to the user.

If I use ADO.Net I can use Try/Catch, but it seems there is no way to do the same thing using datasource. Anyone knows?

Thank you,

J

void CustomersGridView_RowDeleted(Object sender, GridViewDeletedEventArgs e)
 {
// Display whether the delete operation succeeded.
if(e.Exception ==null)
 {
 Message.Text ="Row deleted successfully.";
 }
else
 {
 Message.Text ="An error occurredwhile attempting to delete the row.";
 e.ExceptionHandled =true;
 }
 }

Take a look@.MSDNhttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowdeleted.aspx

|||

Hosam,

Thank you for the reply. It helps, but I would like to get more specific message and give users a friendly message based on that. That is, how can I know the exception is raised due to foreign key violation specifically for example?

Or is there any way to trap the error message passed by a stored procedure?

Thank you,

J

|||

What about check forConstraintException

http://msdn2.microsoft.com/en-us/library/system.data(VS.71).aspx

http://msdn2.microsoft.com/en-us/library/system.data.constraintexception(VS.71).aspx

|||

That is a good suggestion. I will read them.

By the way, thinking for a little time your previous answer does not make sense. The exception is handled after row is ALREADY deleted?

|||

But note that if this exception was thrown nor record will be deleted as the constraint will prevent this.

So if the exception exists no records actually deleted.

|||

Thank you, Hosam.

I tested with detailsview's inserted event, but it did not work. However, what you were saying makes sense as well. I will look into details. If I find more new things, I wll post it here. Thank you.

J

Error running report after deploying

I have created a function that queries the database for a value if conditions
are met. IE the query in the function runs less than 2% of report. In preview
mode in VS.net, the report works correctly. When I use Report Manager to
upload the report, when the query runs, it places #Error in the report field.
This is the begining of the function:
Public Function SKU(rootSerial as String, _
currentSKU as Guid)as String
Dim currentSKUStr as String
currentSKUStr=currentSKU.ToString()
If rootSerial = "" Then
Else
Dim arset As Guid
Dim oConn as New System.Data.SqlClient.SqlConnection
oConn.ConnectionString = "Data Source=PPNS1;Initial
Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
oConn.Open()
Dim oCmd as New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandText = "SELECT New_ProductId FROM
New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
arset = oCmd.ExecuteScalar()
currentSKUStr = arset.ToString()
oConn.Close()
End IF
There is additional code after End If, but that code is run for each data
row. I also have verified in the IDE via msgbox, that the correct value is
being returned from the query.
1. Is it possible to deploy a function that contains its own query as I have
done?
2. What could be the cause of it working in the IDE but not when deployed?
TIA, JimUpdate:
I have traced the problem to:
Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
How do I set security for embeded code?
"JHPArizona" wrote:
> I have created a function that queries the database for a value if conditions
> are met. IE the query in the function runs less than 2% of report. In preview
> mode in VS.net, the report works correctly. When I use Report Manager to
> upload the report, when the query runs, it places #Error in the report field.
> This is the begining of the function:
> Public Function SKU(rootSerial as String, _
> currentSKU as Guid)as String
> Dim currentSKUStr as String
> currentSKUStr=currentSKU.ToString()
> If rootSerial = "" Then
> Else
> Dim arset As Guid
> Dim oConn as New System.Data.SqlClient.SqlConnection
> oConn.ConnectionString = "Data Source=PPNS1;Initial
> Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
> oConn.Open()
> Dim oCmd as New System.Data.SqlClient.SqlCommand
> oCmd.Connection = oConn
> oCmd.CommandText = "SELECT New_ProductId FROM
> New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
> arset = oCmd.ExecuteScalar()
> currentSKUStr = arset.ToString()
> oConn.Close()
> End IF
>
> There is additional code after End If, but that code is run for each data
> row. I also have verified in the IDE via msgbox, that the correct value is
> being returned from the query.
> 1. Is it possible to deploy a function that contains its own query as I have
> done?
> 2. What could be the cause of it working in the IDE but not when deployed?
> TIA, Jim|||Update 2:
I solved the problem and will post again after I have refined it. I am not
happy at this time with the solution as I have set the default permission
behavior from "Nothing" to "FullTrust". This is OK for my use as the web
server is private so I am less concerned about malicious code. However, I
would like to have it working with tighter permissions
"JHPArizona" wrote:
> Update:
> I have traced the problem to:
> Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> How do I set security for embeded code?
> "JHPArizona" wrote:
> > I have created a function that queries the database for a value if conditions
> > are met. IE the query in the function runs less than 2% of report. In preview
> > mode in VS.net, the report works correctly. When I use Report Manager to
> > upload the report, when the query runs, it places #Error in the report field.
> >
> > This is the begining of the function:
> >
> > Public Function SKU(rootSerial as String, _
> > currentSKU as Guid)as String
> > Dim currentSKUStr as String
> > currentSKUStr=currentSKU.ToString()
> > If rootSerial = "" Then
> > Else
> > Dim arset As Guid
> > Dim oConn as New System.Data.SqlClient.SqlConnection
> > oConn.ConnectionString = "Data Source=PPNS1;Initial
> > Catalog=Persyst_Development_MSCRM;Integrated Security=SSPI;"
> > oConn.Open()
> >
> > Dim oCmd as New System.Data.SqlClient.SqlCommand
> > oCmd.Connection = oConn
> > oCmd.CommandText = "SELECT New_ProductId FROM
> > New_PersystSerialNumberExtensionBase WHERE (New_SerialNumber = N'4320401')"
> > arset = oCmd.ExecuteScalar()
> > currentSKUStr = arset.ToString()
> > oConn.Close()
> > End IF
> >
> >
> > There is additional code after End If, but that code is run for each data
> > row. I also have verified in the IDE via msgbox, that the correct value is
> > being returned from the query.
> >
> > 1. Is it possible to deploy a function that contains its own query as I have
> > done?
> > 2. What could be the cause of it working in the IDE but not when deployed?
> >
> > TIA, Jim