Error when applying a filter to a table

Table Properties, Filter Tab.
Added a filter.
Expression: =Fields!LastActionDate.Value
Operator: = Value: "7/1/2004"
Receive following when previewing report:
"An error has occured during report processing. The processing of
filter expression for the table 'table1' cannot be performed. The
comparision failed. Please check the data type returned by the data
expression."
I tried Expression: =CDate(Fields!LastActionDate.Value)
I tried removing quotes from Date value.
No Luck.
Can someone help?
Thank you!You'll also need to cast the filter Value: =CDate("7/1/2004")
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:s6puh0pvgceg530k3sfagtefa3qogid418@.4ax.com...
> Table Properties, Filter Tab.
> Added a filter.
> Expression: =Fields!LastActionDate.Value
> Operator: => Value: "7/1/2004"
> Receive following when previewing report:
> "An error has occured during report processing. The processing of
> filter expression for the table 'table1' cannot be performed. The
> comparision failed. Please check the data type returned by the data
> expression."
> I tried Expression: =CDate(Fields!LastActionDate.Value)
> I tried removing quotes from Date value.
> No Luck.
> Can someone help?
> Thank you!
>
>

Error when accessing cell value in a cellset

When iterating through a cellset using the following code, I get an error when accessing the value of a particular cell. Any tips of what caused this error? It seems to only happen on a particular mdx query. If I run that mdx query in Management Studio, I get a value for that particular cell.

Error:

"File system error: The record ID is incorrect. Physical file: \\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\[name].db\b356fd10-9f2e-42e3-9756-774f5d15f4bd.0.dim\35.b356fd10-9f2e-42e3-9756-774f5d15f4bd.b356fd10-9f2e-42e3-9756-774f5d15f4bd2.ostore. Logical file: ."

Code:

For c As Integer = 0 To cs.Axes(0).Set.Tuples.Count - 1

For r As Integer = 0 To cs.Axes(1).Set.Tuples.Count - 1

hash.add(cs.Cells(c,r).Value)

Next

Next

This could either be a bug in AS or data corruption.

Error using Multi-value Report Parameter

I've checked the Multi-value in a Report Parameter. When running the report
if I check more than one value in the parameter dropdown I get the
following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'ActivityDetail'.
(rsErrorExecutingCommand)
For more information about this error navigate to the report server on the
local server machine, or enable remote errors
Does anyone know what can be causing this error or what I'm supposed to look
for after I "navigate to the report server"? How do I enable remote errors?
Thanks - Ed TPost your SQL, that will help us know what is going on.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ed T" <etroche@.yahoo.com> wrote in message
news:X27Of.12$422.7@.fed1read10...
> I've checked the Multi-value in a Report Parameter. When running the
> report if I check more than one value in the parameter dropdown I get the
> following error:
>
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'ActivityDetail'.
> (rsErrorExecutingCommand)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
>
> Does anyone know what can be causing this error or what I'm supposed to
> look for after I "navigate to the report server"? How do I enable remote
> errors?
>
> Thanks - Ed T
>

Error updating a DateTime field

Hi, I'm having trouble updating a DateTime field in my SQL database. Here is what I'm trying to do...I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable. Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. "). There is a ton more than this but here are the relevant snippets:
<code>
Dim CompDate As DateTime
Dim aComm As SQLCommand
Dim aReader As SQLDataReader
Dim bSQL,bConn As String
bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39)
bConn = serverStuff aConn = New SQLConnection(bConn)
aComm = New SQLCommand(bSQL,aConn)
aConn.Open()
result = aComm.ExecuteReader()
'fills controls with data
While result.Read()
CompDate = result("CompleteDate")
PreviousMarkoutStatus.Text = result("StatusOfMarkout")
End While
result.Close()
aConn.Close()
sSqlCmd ="Update OneCallTickets CompleteDate=@.CompleteDate, StatusOfMarkout=@.StatusOfMarkout WHERE TicketFileName=@.TicketFileName"
dim SqlCon as New SqlConnection(serverStuff)
dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon)
If Flag1List.SelectedItem.Value = "No Change" Then
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = PreviousMarkoutStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CompDate
Else
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = CurrentStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = Today()
End If
SqlCon.Open()
SqlCmd.ExecuteNonQuery()
SqlCon.Close()
</code>
Can anybody help me with this? Thanks a bunchIs your CompDate a DateTime or is it a string? #1 make sure it is a DateTime.
You might also try using the Convert function in your procedure to convert the argument to a SQL DateTime and see what happens.|||

Yes, the value I'm reading from the database is 100% a datetime. Could you give me an example of how to convert? Thanks

|||

I think you need the DateDiff function of both VB .NET and SQL Server and also change your data type to SmallDateTime it has less resolution if the seconds are not important. Try the links below for sample code using TimeSpan with DateDiff. Hope this helps.
http://blogs.msdn.com/vbfaq/

http://www.stanford.edu/~bsuter/sql-datecomputations.html

|||Hmm, I guess I don't understand. I read through those links and all I could find was how to calculate the difference between two dates. I couldn't really find an answer in there?|||CONVERT(DATETIME, Your_Date_Field)|||

When I try that here:
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CONVERT(DATETIME, CompDate)
I get a Compiler Error Message: BC30684: 'CONVERT' is a type and cannot be used as an expression.
Should I be using it somewhere else?

|||I was assuming you are using a stored procedure to do the work. The CONVERT function is a T_SQL function and should be done inside of a stored procedure or SQL statement.

Error trying to retreive lastlogon value through LDAP ADSI

I am trying to extract the value of the lastLogon attribute for users in
Active Directory by using LDAP ADSI. Setup is SQL Server 2000 with a linked
server named ADSI, using Query Anaylyzer.
I run the query
SELECT [name], lastlogon
FROM OPENQUERY( ADSI,
'SELECT name, samAccountName, lastlogon
FROM ''LDAP://hostname/OU=BIS Portal Users,DC=bis,DC=dhs,DC=local''
WHERE objectClass = ''user''')
which produces this error:
Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADSDSOObject'.
Could not convert the data value due to reasons other than sign mismatch or
overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned
0x40eda: Data status returned from the provider: [COLUMN_NAME=lastlogon
STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=name STATUS=DBSTATUS_S_OK]
].
I did a search on this forum for threads related to this error, and found a
thread discussing problems with querying mult-valued attributes through
OPENQUERY. According to MSDN's section on the AD schema, lastLogon is a
single-valued, 64-bit integer representing the 100 nanosecond intervals sinc
e
January 1, 1601.
I get the same error when I try to query other 64-bit attributes, such as
accountExpires, isAccountLocked, and others that are 100 nanosecond counters
.
I can successfully read badPwdCount, which is a 32-bit counter.
Is there any way to extract those 64-bit counters out of AD using OPENQUERY?Did you ever find a solution to this problem? I'm trying to do the samethin
g.

error trying to move data from 1 table to another.

Hi I am getting the following error
Syntax error converting the nvarchar value 'null' to a column of data type int
while attempting to move data from 1 table to another with the script,
INSERT INTO [dbase1].dbo.DML$table1
([Data_Item_ID])
SELECT CAST([Data_Item_ID]as int)
from [DML1].dbo.dataitemlog
I have allow not NULL for both source and destination fields, so do not see
how I could be getting this error, also did not see any null values in the
source field. The source data type is
(nvarchar (4) not null)
and the destination field data type is (int, not null)
any suggestions? thanks.
--
Paul G
Software engineer.this problem has been addressed in a previous post. thanks, Paul.
"Paul" wrote:
> Hi I am getting the following error
> Syntax error converting the nvarchar value 'null' to a column of data type int
> while attempting to move data from 1 table to another with the script,
> INSERT INTO [dbase1].dbo.DML$table1
> ([Data_Item_ID])
> SELECT CAST([Data_Item_ID]as int)
> from [DML1].dbo.dataitemlog
> I have allow not NULL for both source and destination fields, so do not see
> how I could be getting this error, also did not see any null values in the
> source field. The source data type is
> (nvarchar (4) not null)
> and the destination field data type is (int, not null)
> any suggestions? thanks.
> --
> Paul G
> Software engineer.

error trying to move data from 1 table to another.

Hi I am getting the following error
Syntax error converting the nvarchar value 'null' to a column of data type i
nt
while attempting to move data from 1 table to another with the script,
INSERT INTO [dbase1].dbo.DML$table1
([Data_Item_ID])
SELECT CAST([Data_Item_ID]as int)
from [DML1].dbo.dataitemlog
I have allow not NULL for both source and destination fields, so do not see
how I could be getting this error, also did not see any null values in the
source field. The source data type is
(nvarchar (4) not null)
and the destination field data type is (int, not null)
any suggestions? thanks.
--
Paul G
Software engineer.this problem has been addressed in a previous post. thanks, Paul.
"Paul" wrote:

> Hi I am getting the following error
> Syntax error converting the nvarchar value 'null' to a column of data type
int
> while attempting to move data from 1 table to another with the script,
> INSERT INTO [dbase1].dbo.DML$table1
> ([Data_Item_ID])
> SELECT CAST([Data_Item_ID]as int)
> from [DML1].dbo.dataitemlog
> I have allow not NULL for both source and destination fields, so do not se
e
> how I could be getting this error, also did not see any null values in the
> source field. The source data type is
> (nvarchar (4) not null)
> and the destination field data type is (int, not null)
> any suggestions? thanks.
> --
> Paul G
> Software engineer.

error trying to move data from 1 table to another.

Hi I am getting the following error
Syntax error converting the nvarchar value 'null' to a column of data type int
while attempting to move data from 1 table to another with the script,
INSERT INTO [dbase1].dbo.DML$table1
([Data_Item_ID])
SELECT CAST([Data_Item_ID]as int)
from [DML1].dbo.dataitemlog
I have allow not NULL for both source and destination fields, so do not see
how I could be getting this error, also did not see any null values in the
source field. The source data type is
(nvarchar (4) not null)
and the destination field data type is (int, not null)
any suggestions? thanks.
Paul G
Software engineer.
this problem has been addressed in a previous post. thanks, Paul.
"Paul" wrote:

> Hi I am getting the following error
> Syntax error converting the nvarchar value 'null' to a column of data type int
> while attempting to move data from 1 table to another with the script,
> INSERT INTO [dbase1].dbo.DML$table1
> ([Data_Item_ID])
> SELECT CAST([Data_Item_ID]as int)
> from [DML1].dbo.dataitemlog
> I have allow not NULL for both source and destination fields, so do not see
> how I could be getting this error, also did not see any null values in the
> source field. The source data type is
> (nvarchar (4) not null)
> and the destination field data type is (int, not null)
> any suggestions? thanks.
> --
> Paul G
> Software engineer.

error sql 2000

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
Line 53
Cannot insert the value NULL into column '', table '';
column does not allow nulls. INSERT fails.
The statement has been terminated.HI,
You might get this error if you have an invalid database owner. Execute the
below script to verify,
SELECT name, SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
Incase if you have any invalid users , execute sp_changedbowner (refer books
online) stored procedure.
Thanks
Hari
MCDBA
"harold apolinar" <anonymous@.discussions.microsoft.com> wrote in message
news:f7b201c40cf5$533b69e0$a501280a@.phx.gbl...
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
> Line 53
> Cannot insert the value NULL into column '', table '';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||Are you running sp_helpdb with a database name or without? If the latter,
does it work if you explicitly set a database name that you have access to?
"harold apolinar" <anonymous@.discussions.microsoft.com> wrote in message
news:f7b201c40cf5$533b69e0$a501280a@.phx.gbl...
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
> Line 53
> Cannot insert the value NULL into column '', table '';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.

Error saving diagram

I'm trying to save a diagram but get this error:
"Cannot insert the value NULL into column 'version',
table 'Hernan.dbo.dtproperties'; column does not allows
nulls. INSERT fails.
The statement has been terminated
The 'dt_adduserobject' procedure attempted to return a
status of NULL, wich is not allowed. A status of 0 will be
returned instead."
can you help me ?
cannot save any diagram.
thanks in advance,
Hernanwithout the table DLL I'm afraid there's not much help for you.
Did you try to add a column with a null value and make it PK?
"Hernan" <hvaldes@.frisa.com> wrote in message
news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> I'm trying to save a diagram but get this error:
> "Cannot insert the value NULL into column 'version',
> table 'Hernan.dbo.dtproperties'; column does not allows
> nulls. INSERT fails.
> The statement has been terminated
> The 'dt_adduserobject' procedure attempted to return a
> status of NULL, wich is not allowed. A status of 0 will be
> returned instead."
> can you help me ?
> cannot save any diagram.
> thanks in advance,
> Hernan|||my bad.
I thought you had this error on a user table.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:Of2NBtRYDHA.1900@.TK2MSFTNGP10.phx.gbl...
> without the table DLL I'm afraid there's not much help for you.
> Did you try to add a column with a null value and make it PK?
>
> "Hernan" <hvaldes@.frisa.com> wrote in message
> news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> > I'm trying to save a diagram but get this error:
> >
> > "Cannot insert the value NULL into column 'version',
> > table 'Hernan.dbo.dtproperties'; column does not allows
> > nulls. INSERT fails.
> > The statement has been terminated
> > The 'dt_adduserobject' procedure attempted to return a
> > status of NULL, wich is not allowed. A status of 0 will be
> > returned instead."
> >
> > can you help me ?
> > cannot save any diagram.
> >
> > thanks in advance,
> > Hernan
>|||Hernan,
Whats the SQL Server version? The below workaround is mentioned in
BooksOnLine for this error:
ALTER TABLE dbo.dtproperties ADD uvalue NVARCHAR(255) NULL
GO
IF EXISTS(SELECT * FROM dbo.dtproperties) EXEC('UPDATE dbo.dtproperties SET
uvalue = CONVERT(NVARCHAR(255), value)')
GO
If it still doesnt help, read on.
>> cannot save any diagram.
Does that mean you dont have any database diagrams? If so, try dropping the
dtproperties table.This table is recreated automatically every time you try
to save a database diagram.You have to unmark the table as a system
object.Since this is undocumented, the usual warnings exist:
dbo.sp_msUnmarkschemaobject dtproperties
drop table dtproperties
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Hernan" <hvaldes@.frisa.com> wrote in message
news:02e301c3610f$7a565db0$a501280a@.phx.gbl...
> I'm trying to save a diagram but get this error:
> "Cannot insert the value NULL into column 'version',
> table 'Hernan.dbo.dtproperties'; column does not allows
> nulls. INSERT fails.
> The statement has been terminated
> The 'dt_adduserobject' procedure attempted to return a
> status of NULL, wich is not allowed. A status of 0 will be
> returned instead."
> can you help me ?
> cannot save any diagram.
> thanks in advance,
> Hernan

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