Error during printing

I have a report that when I view it in IE works fine. But I try to print it
using custom report printing I am getting an error: An error occured diring
printing. Here is a log entry for that:
aspnet_wp!cache!f28!06/22/2007-15:40:54:: i INFO: Item not cacheable: -
PersistStreams /Accident Reports/AccidentReport
aspnet_wp!chunks!f28!06/22/2007-15:40:54:: i INFO: ###
GetReportChunk('RenderingInfo_IMAGE', 2), chunk was not found!
this=5c9a3ba2-add9-4dcf-b40d-162ec96e65ca
aspnet_wp!reportrendering!f28!06/22/2007-15:40:54:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An unexpected error has occurred trying to parse the device information XML
structure. Details: The value of the parameter 'OutputFormat' is invalid., ;
Info:
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An unexpected error has occurred trying to parse the device information XML
structure. Details: The value of the parameter 'OutputFormat' is
invalid. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
unexpected error has occurred trying to parse the device information XML
structure. Details: The value of the parameter 'OutputFormat' is invalid.
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseFormat(String
enumValue, FormatEncoding defaultValue)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseDeviceInfo(NameValueCollection
deviceInfo)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport.RenderReport(Report
report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions
evaluateHeaderFooterExpressions, CreateAndRegisterStream
createAndRegisterStreamCallback)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
-- End of inner exception stack trace --
aspnet_wp!library!f28!06/22/2007-15:40:54::
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An unexpected error has occurred trying to parse the device information XML
structure. Details: The value of the parameter 'OutputFormat' is
invalid. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
unexpected error has occurred trying to parse the device information XML
structure. Details: The value of the parameter 'OutputFormat' is invalid.
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseFormat(String
enumValue, FormatEncoding defaultValue)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseDeviceInfo(NameValueCollection
deviceInfo)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport.RenderReport(Report
report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions
evaluateHeaderFooterExpressions, CreateAndRegisterStream
createAndRegisterStreamCallback)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Library.RenderAsyncExecutionBase.PerformAsyncWork()
at
Microsoft.ReportingServices.Library.AsyncExecution.AsyncStartMain(Object
state)
aspnet_wp!webserver!154c!06/22/2007-15:40:54:: e ERROR: Reporting Services
error
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An unexpected error has occurred trying to parse the device information XML
structure. Details: The value of the parameter 'OutputFormat' is
invalid. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
unexpected error has occurred trying to parse the device information XML
structure. Details: The value of the parameter 'OutputFormat' is invalid.
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseFormat(String
enumValue, FormatEncoding defaultValue)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseDeviceInfo(NameValueCollection
deviceInfo)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport.RenderReport(Report
report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions
evaluateHeaderFooterExpressions, CreateAndRegisterStream
createAndRegisterStreamCallback)
at
Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
-- End of inner exception stack trace --
at Microsoft.ReportingServices.Library.AsyncExecution.StartAsyncThread()
at
Microsoft.ReportingServices.Library.RenderAsyncExecutionBase.StartAsyncRender(RSService
rs, CatalogItemContext reportContext, ClientRequest session)
at
Microsoft.ReportingServices.Library.RenderNextAsyncExecution.RenderNext(RSService
rs, CatalogItemContext reportContext, ClientRequest session)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory
streamFactory)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport()
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType
itemType)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Any idea what the problem is?I fixed it removing RS Print AcitveX. First print will install it. After
that it worked.
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:uGvqU5QtHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have a report that when I view it in IE works fine. But I try to print it
>using custom report printing I am getting an error: An error occured diring
>printing. Here is a log entry for that:
> aspnet_wp!cache!f28!06/22/2007-15:40:54:: i INFO: Item not cacheable: -
> PersistStreams /Accident Reports/AccidentReport
> aspnet_wp!chunks!f28!06/22/2007-15:40:54:: i INFO: ###
> GetReportChunk('RenderingInfo_IMAGE', 2), chunk was not found!
> this=5c9a3ba2-add9-4dcf-b40d-162ec96e65ca
> aspnet_wp!reportrendering!f28!06/22/2007-15:40:54:: e ERROR: Throwing
> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
> An unexpected error has occurred trying to parse the device information
> XML structure. Details: The value of the parameter 'OutputFormat' is
> invalid., ;
> Info:
> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
> An unexpected error has occurred trying to parse the device information
> XML structure. Details: The value of the parameter 'OutputFormat' is
> invalid. -->
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
> unexpected error has occurred trying to parse the device information XML
> structure. Details: The value of the parameter 'OutputFormat' is invalid.
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseFormat(String
> enumValue, FormatEncoding defaultValue)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseDeviceInfo(NameValueCollection
> deviceInfo)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport.RenderReport(Report
> report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions
> evaluateHeaderFooterExpressions, CreateAndRegisterStream
> createAndRegisterStreamCallback)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report
> report, NameValueCollection reportServerParameters, NameValueCollection
> deviceInfo, NameValueCollection clientCapabilities,
> EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
> CreateAndRegisterStream createAndRegisterStream)
> at
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
> createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
> -- End of inner exception stack trace --
> aspnet_wp!library!f28!06/22/2007-15:40:54::
> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
> An unexpected error has occurred trying to parse the device information
> XML structure. Details: The value of the parameter 'OutputFormat' is
> invalid. -->
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
> unexpected error has occurred trying to parse the device information XML
> structure. Details: The value of the parameter 'OutputFormat' is invalid.
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseFormat(String
> enumValue, FormatEncoding defaultValue)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseDeviceInfo(NameValueCollection
> deviceInfo)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport.RenderReport(Report
> report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions
> evaluateHeaderFooterExpressions, CreateAndRegisterStream
> createAndRegisterStreamCallback)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report
> report, NameValueCollection reportServerParameters, NameValueCollection
> deviceInfo, NameValueCollection clientCapabilities,
> EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
> CreateAndRegisterStream createAndRegisterStream)
> at
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
> createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
> -- End of inner exception stack trace --
> at
> Microsoft.ReportingServices.Library.RenderAsyncExecutionBase.PerformAsyncWork()
> at
> Microsoft.ReportingServices.Library.AsyncExecution.AsyncStartMain(Object
> state)
> aspnet_wp!webserver!154c!06/22/2007-15:40:54:: e ERROR: Reporting Services
> error
> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
> An unexpected error has occurred trying to parse the device information
> XML structure. Details: The value of the parameter 'OutputFormat' is
> invalid. -->
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
> unexpected error has occurred trying to parse the device information XML
> structure. Details: The value of the parameter 'OutputFormat' is invalid.
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseFormat(String
> enumValue, FormatEncoding defaultValue)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ContextSettings.ParseDeviceInfo(NameValueCollection
> deviceInfo)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport.RenderReport(Report
> report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions
> evaluateHeaderFooterExpressions, CreateAndRegisterStream
> createAndRegisterStreamCallback)
> at
> Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report
> report, NameValueCollection reportServerParameters, NameValueCollection
> deviceInfo, NameValueCollection clientCapabilities,
> EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
> CreateAndRegisterStream createAndRegisterStream)
> at
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
> createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
> -- End of inner exception stack trace --
> at Microsoft.ReportingServices.Library.AsyncExecution.StartAsyncThread()
> at
> Microsoft.ReportingServices.Library.RenderAsyncExecutionBase.StartAsyncRender(RSService
> rs, CatalogItemContext reportContext, ClientRequest session)
> at
> Microsoft.ReportingServices.Library.RenderNextAsyncExecution.RenderNext(RSService
> rs, CatalogItemContext reportContext, ClientRequest session)
> at
> Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory
> streamFactory)
> at
> Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport()
> at
> Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType
> itemType)
> at
> Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
> at
> Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
> Any idea what the problem is?
>sql

Error Creating Data Source View in VS 2005 BI Tool for SQL Server 2005 View with > 32 Columns

I keep getting an error message in Visual Studio 2005 (SQL Server Business Intelligence Development Studio) = "Cannot have more than 32 columns" when I use the wizard to create a data source view using a view I created in a SQL Server 2005 database, which I use as a data source. The view does have more than 32 columns; however, I have been successful in creating data source views for other DB views with more than 32 columns. It seems that for some DB views with more than 32 columns the wizard ignores this problem, but for others - admittedly with a larger number of columns - it does not.

Any insights and potential workarounds? Thanks!

Please file a bug on http://connect.microsoft.com/SQLServer/Feedback and we'll look into this. If possible, please include a SQL DDL script that will create the source database which causes problems.

As a possible work around, you can try creating the DSV without this table and then adding the table in the DSV editor and see if that works.

Thanks

|||

Thanks. I will file a bug report, as you suggested. Regarding your suggestion, I forgot to mention that I tried that too and got the same error message. But it gave me another idea to try, which worked. I created the data source view using the wizard without selecting a table or a view from the data source, and then I used the New Named Query menu option to re-create the view (i.e., using the same SQL statement to create the view in the SQL Server 2005 DB). That did the trick! (BTW, there is another issue with the New Named Query pop-up window when you enter a SQL query and click on the OK button without entering a name for the query.)

FYI, below are the message and the program location details for the error related to the maximum number of columns in a data source view:

Message:

Cannot have more than 32 columns. (Microsoft Visual Studio)

Program Location:

at System.Data.DataKey..ctor(DataColumn[] columns, Boolean copyColumns)
at System.Data.UniqueConstraint.Create(String constraintName, DataColumn[] columns)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()

Error Creating Data Source View in VS 2005 BI Tool for SQL Server 2005 View with > 32 Col

I keep getting an error message in Visual Studio 2005 (SQL Server Business Intelligence Development Studio) = "Cannot have more than 32 columns" when I use the wizard to create a data source view using a view I created in a SQL Server 2005 database, which I use as a data source. The view does have more than 32 columns; however, I have been successful in creating data source views for other DB views with more than 32 columns. It seems that for some DB views with more than 32 columns the wizard ignores this problem, but for others - admittedly with a larger number of columns - it does not.

Any insights and potential workarounds? Thanks!

Please file a bug on http://connect.microsoft.com/SQLServer/Feedback and we'll look into this. If possible, please include a SQL DDL script that will create the source database which causes problems.

As a possible work around, you can try creating the DSV without this table and then adding the table in the DSV editor and see if that works.

Thanks

|||

Thanks. I will file a bug report, as you suggested. Regarding your suggestion, I forgot to mention that I tried that too and got the same error message. But it gave me another idea to try, which worked. I created the data source view using the wizard without selecting a table or a view from the data source, and then I used the New Named Query menu option to re-create the view (i.e., using the same SQL statement to create the view in the SQL Server 2005 DB). That did the trick! (BTW, there is another issue with the New Named Query pop-up window when you enter a SQL query and click on the OK button without entering a name for the query.)

FYI, below are the message and the program location details for the error related to the maximum number of columns in a data source view:

Message:

Cannot have more than 32 columns. (Microsoft Visual Studio)

Program Location:

at System.Data.DataKey..ctor(DataColumn[] columns, Boolean copyColumns)
at System.Data.UniqueConstraint.Create(String constraintName, DataColumn[] columns)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()

Error Creating an Indexed View.

I have trouble creating an indexed view, sample scripts attached. Any help/workaround much appreciated. Thx.

Create Sample Table

Create Table dbo.Test_IndexTable

(NumericSchoolDBN int NOT NULL,

SchoolYear smallint NOT NULL,

TermId tinyint NOT NULL,

CourseCode Varchar(10) NOT NULL,

SectionID smallint NOT NULL,

PeriodID smallint NOT NULL,

CycleDay smallint NOT NULL,

PRIMARY KEY

(

NumericSchoolDBN, SchoolYear,TermId,CourseCode,SectionID,PeriodID, CycleDay

)

)

Populate Sample Table

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 0)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 1)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 2)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 3)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 1, 3, 4)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 0)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 1)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 2)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 3)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 2, 7, 4)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 0)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 1)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 2)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 3)

Insert Into Test_IndexTable

Values (1010448, 2005, 1, 'AC3', 3, 8, 4)

Create View

--Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON

GO

-- Drop View dbo.Test_IndexTable_InxVw

-- Create view.

CREATE VIEW dbo.Test_IndexTable_InxVw

WITH SCHEMABINDING

AS

select NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID,

sum(power(2, cycleday)) As AsIS_CycleDayBinaryString,

COUNT_BIG(*) As NumofRows

From dbo.Test_IndexTable

Group By NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID

GO

if ObjectProperty(object_id('Test_IndexTable_InxVw'),'IsIndexable') = 1

Print 'Test_IndexTable_InxVw Is Indexable'

Else

Print 'Test_IndexTable_InxVw Is NOT Indexable'

--Create index on the view.

CREATE UNIQUE CLUSTERED INDEX UCX_Test_IndexTable_InxVw ON dbo.Test_IndexTable_InxVw (NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID)

GO

Error…

Server: Msg 8662, Level 16, State 1, Line 1

An index cannot be created on the view 'Test_IndexTable_InxVw' because the view definition includes an unknown value (the sum of a nullable expression).

Is a potential option here to leave out the SUM column and create the indexed view without this quantity and iff this quantity is needed it can then be computed as needed?|||It beats the purpose i.e. the only reason I would like to create an indexed view it to be able to represent it in a binary/string value based on the sum function.|||

I am not a big fan of "encoding" values like this, not for the least reason that if you have duplicates it means you get spurious results, but you could do this like:

case cycleday
when 0 then 1
when 1 then 2
when 2 then 4
when 3 then 8
when 4 then 16
when 5 then 32 else -999 end

The else -999 just ensures Non-nullability, and a way out of whack answer to enact some error handling.

|||

Are you running this on SQL Server 2000? You have hit a limitation there in terms of expression evaluation. You can workaround by creating a computed column on the table like:

alter table dbo.Test_IndexTable add CycleDay_p as isnull(power(2, CycleDay), 0)

And using it in the indexed view definition like:

CREATE VIEW dbo.Test_IndexTable_InxVw

WITH SCHEMABINDING

AS

select NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID,

sum(CycleDay_p) As AsIS_CycleDayBinaryString,

COUNT_BIG(*) As NumofRows

From dbo.Test_IndexTable

Group By NumericSchoolDBN, SchoolYear, TermId, CourseCode, SectionID, PeriodID

GO

Btw, you should be able to use the ISNULL expression directly in the indexed view in SQL Server 2005. I tried it there and it works fine.

Error converting data type varchar to numeric

Hi,

Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.

Here is the syntax that I am currently using:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]

FROM dbo.F_Report_Data AS a

WHERE (szF3 <> 'Blank')

When I open the view I get the error message about converting varchar to numeric.

Thanks,

Erik

Try running this query:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time],
szF2 AS [User Name],
-- CAST(szF3 AS numeric) AS [Documents Indexed],
szF3,
CAST(szF4 AS datetime) AS [Logout Date/Time],
-- CAST(szF5 AS numeric) AS [Documents Sent to QC],
-- CAST(szF6 AS numeric) AS [Documents Reconciled],
szF5,
szF6,
szF7 AS [Reject Reason],
CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
where isNumeric (szF3 + 'D0') = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0

And post any results that get returned.|||

I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.

|||

It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.

-Sue

|||

I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a

select top 5 * from F_Report_Data

|||

The 3 columns that I want to cast as numeric have only numbers in them.

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

|||

The 3 columns that I want to cast as numeric have only numbers in them.

Code Snippet

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).

I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.

|||

Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:

Code Snippet

select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0

|||

Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!

|||

Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.

|||

Here are the results:

Code Snippet

01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 20070124
01/24/2007 09:59:03 AM admin 0 01/24/2007 04:09:25 PM 1 0 Unable to read case numbe 20070124
01/24/2007 09:56:03 AM admin 0 01/24/2007 04:26:33 PM 0 3 20070124
01/25/2007 01:55:19 PM admin 3 01/25/2007 03:32:51 PM 0 0 20070125

01/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125

| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|

I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||

Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.

Erik:

You can try this query; it will exhibit which test is failing:

Code Snippet

select isDate(rtrim(szF1)) as szF1isDate,
isNumeric (rtrim(szF3) + 'D0') as szF3IsNumeric,
isDate(rtrim(szF4)) as szF4IsDate,
isNumeric (rtrim(szF5) + 'D0') as szF5IsNumeric,
isNumeric (rtrim(szF6) + 'D0') as szF6IsNumeric,
isDate(rtrim(szF8)) as szF8isDate,
left(szF1, 30) as szF1,
left(szF2, 30) as szF2,
left(szF3, 30) as szF3,
left(szF4, 30) as szF4,
left(szF5, 30) as szF5,
left(szF6, 30) as szF6,
left(szF7, 30) as szF7,
left(szF8, 30) as szF8
from dbo.F_Report_data
where isDate(rtrim(szF1)) = 0
or isNumeric (rtrim(szF3) + 'D0') = 0
or isDate(rtrim(szF4)) = 0
or isNumeric (rtrim(szF5) + 'D0') = 0
or isNumeric (rtrim(szF6) + 'D0') = 0
or isDate(rtrim(szF8)) = 0

I feel like I need a fresh set of eyes on this at this point. Help?

|||

Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?

0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518

|||

Erik:

To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:

0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)

1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)

]

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Error converting data type varchar to numeric

Hi,

Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.

Here is the syntax that I am currently using:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]

FROM dbo.F_Report_Data AS a

WHERE (szF3 <> 'Blank')

When I open the view I get the error message about converting varchar to numeric.

Thanks,

Erik

Try running this query:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time],
szF2 AS [User Name],
-- CAST(szF3 AS numeric) AS [Documents Indexed],
szF3,
CAST(szF4 AS datetime) AS [Logout Date/Time],
-- CAST(szF5 AS numeric) AS [Documents Sent to QC],
-- CAST(szF6 AS numeric) AS [Documents Reconciled],
szF5,
szF6,
szF7 AS [Reject Reason],
CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
where isNumeric (szF3 + 'D0') = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0

And post any results that get returned.|||

I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.

|||

It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.

-Sue

|||

I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a

select top 5 * from F_Report_Data

|||

The 3 columns that I want to cast as numeric have only numbers in them.

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

|||

The 3 columns that I want to cast as numeric have only numbers in them.

Code Snippet

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).

I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.

|||

Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:

Code Snippet

select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0

|||

Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!

|||

Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.

|||

Here are the results:

Code Snippet

01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 20070124
01/24/2007 09:59:03 AM admin 0 01/24/2007 04:09:25 PM 1 0 Unable to read case numbe 20070124
01/24/2007 09:56:03 AM admin 0 01/24/2007 04:26:33 PM 0 3 20070124
01/25/2007 01:55:19 PM admin 3 01/25/2007 03:32:51 PM 0 0 20070125

01/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125

| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|

I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||

Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.

Erik:

You can try this query; it will exhibit which test is failing:

Code Snippet

select isDate(rtrim(szF1)) as szF1isDate,
isNumeric (rtrim(szF3) + 'D0') as szF3IsNumeric,
isDate(rtrim(szF4)) as szF4IsDate,
isNumeric (rtrim(szF5) + 'D0') as szF5IsNumeric,
isNumeric (rtrim(szF6) + 'D0') as szF6IsNumeric,
isDate(rtrim(szF8)) as szF8isDate,
left(szF1, 30) as szF1,
left(szF2, 30) as szF2,
left(szF3, 30) as szF3,
left(szF4, 30) as szF4,
left(szF5, 30) as szF5,
left(szF6, 30) as szF6,
left(szF7, 30) as szF7,
left(szF8, 30) as szF8
from dbo.F_Report_data
where isDate(rtrim(szF1)) = 0
or isNumeric (rtrim(szF3) + 'D0') = 0
or isDate(rtrim(szF4)) = 0
or isNumeric (rtrim(szF5) + 'D0') = 0
or isNumeric (rtrim(szF6) + 'D0') = 0
or isDate(rtrim(szF8)) = 0

I feel like I need a fresh set of eyes on this at this point. Help?

|||

Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?

0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518

|||

Erik:

To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:

0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)

1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)

]

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Error converting data type varchar to numeric

Hi,

Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.

Here is the syntax that I am currently using:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]

FROM dbo.F_Report_Data AS a

WHERE (szF3 <> 'Blank')

When I open the view I get the error message about converting varchar to numeric.

Thanks,

Erik

Try running this query:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time],
szF2 AS [User Name],
-- CAST(szF3 AS numeric) AS [Documents Indexed],
szF3,
CAST(szF4 AS datetime) AS [Logout Date/Time],
-- CAST(szF5 AS numeric) AS [Documents Sent to QC],
-- CAST(szF6 AS numeric) AS [Documents Reconciled],
szF5,
szF6,
szF7 AS [Reject Reason],
CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
where isNumeric (szF3 + 'D0') = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0

And post any results that get returned.|||

I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.

|||

It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.

-Sue

|||

I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a

select top 5 * from F_Report_Data

|||

The 3 columns that I want to cast as numeric have only numbers in them.

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

|||

The 3 columns that I want to cast as numeric have only numbers in them.

Code Snippet

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).

I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.

|||

Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:

Code Snippet

select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0

|||

Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!

|||

Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.

|||

Here are the results:

Code Snippet

01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 20070124
01/24/2007 09:59:03 AM admin 0 01/24/2007 04:09:25 PM 1 0 Unable to read case numbe 20070124
01/24/2007 09:56:03 AM admin 0 01/24/2007 04:26:33 PM 0 3 20070124
01/25/2007 01:55:19 PM admin 3 01/25/2007 03:32:51 PM 0 0 20070125

01/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125

| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|

I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||

Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.

Erik:

You can try this query; it will exhibit which test is failing:

Code Snippet

select isDate(rtrim(szF1)) as szF1isDate,
isNumeric (rtrim(szF3) + 'D0') as szF3IsNumeric,
isDate(rtrim(szF4)) as szF4IsDate,
isNumeric (rtrim(szF5) + 'D0') as szF5IsNumeric,
isNumeric (rtrim(szF6) + 'D0') as szF6IsNumeric,
isDate(rtrim(szF8)) as szF8isDate,
left(szF1, 30) as szF1,
left(szF2, 30) as szF2,
left(szF3, 30) as szF3,
left(szF4, 30) as szF4,
left(szF5, 30) as szF5,
left(szF6, 30) as szF6,
left(szF7, 30) as szF7,
left(szF8, 30) as szF8
from dbo.F_Report_data
where isDate(rtrim(szF1)) = 0
or isNumeric (rtrim(szF3) + 'D0') = 0
or isDate(rtrim(szF4)) = 0
or isNumeric (rtrim(szF5) + 'D0') = 0
or isNumeric (rtrim(szF6) + 'D0') = 0
or isDate(rtrim(szF8)) = 0

I feel like I need a fresh set of eyes on this at this point. Help?

|||

Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?

0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518

|||

Erik:

To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:

0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)

1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)

]

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Error converting data type varchar to numeric

Hi,

Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.

Here is the syntax that I am currently using:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]

FROM dbo.F_Report_Data AS a

WHERE (szF3 <> 'Blank')

When I open the view I get the error message about converting varchar to numeric.

Thanks,

Erik

Try running this query:

Code Snippet

SELECT CAST(szF1 AS datetime) AS [Login Date/Time],
szF2 AS [User Name],
-- CAST(szF3 AS numeric) AS [Documents Indexed],
szF3,
CAST(szF4 AS datetime) AS [Logout Date/Time],
-- CAST(szF5 AS numeric) AS [Documents Sent to QC],
-- CAST(szF6 AS numeric) AS [Documents Reconciled],
szF5,
szF6,
szF7 AS [Reject Reason],
CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
where isNumeric (szF3 + 'D0') = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0

And post any results that get returned.|||

I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.

|||

It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.

-Sue

|||

I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a

select top 5 * from F_Report_Data

|||

The 3 columns that I want to cast as numeric have only numbers in them.

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

|||

The 3 columns that I want to cast as numeric have only numbers in them.

Code Snippet

1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM

The items in bold are thecolumns that I am trying to covnert/cast as numeric.

Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).

I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.

|||

Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:

Code Snippet

select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0

|||

Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!

|||

Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.

|||

Here are the results:

Code Snippet

01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 20070124
01/24/2007 09:59:03 AM admin 0 01/24/2007 04:09:25 PM 1 0 Unable to read case numbe 20070124
01/24/2007 09:56:03 AM admin 0 01/24/2007 04:26:33 PM 0 3 20070124
01/25/2007 01:55:19 PM admin 3 01/25/2007 03:32:51 PM 0 0 20070125

01/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125

| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|

I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.

|||

Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.

Erik:

You can try this query; it will exhibit which test is failing:

Code Snippet

select isDate(rtrim(szF1)) as szF1isDate,
isNumeric (rtrim(szF3) + 'D0') as szF3IsNumeric,
isDate(rtrim(szF4)) as szF4IsDate,
isNumeric (rtrim(szF5) + 'D0') as szF5IsNumeric,
isNumeric (rtrim(szF6) + 'D0') as szF6IsNumeric,
isDate(rtrim(szF8)) as szF8isDate,
left(szF1, 30) as szF1,
left(szF2, 30) as szF2,
left(szF3, 30) as szF3,
left(szF4, 30) as szF4,
left(szF5, 30) as szF5,
left(szF6, 30) as szF6,
left(szF7, 30) as szF7,
left(szF8, 30) as szF8
from dbo.F_Report_data
where isDate(rtrim(szF1)) = 0
or isNumeric (rtrim(szF3) + 'D0') = 0
or isDate(rtrim(szF4)) = 0
or isNumeric (rtrim(szF5) + 'D0') = 0
or isNumeric (rtrim(szF6) + 'D0') = 0
or isDate(rtrim(szF8)) = 0

I feel like I need a fresh set of eyes on this at this point. Help?

|||

Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?

0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518

|||

Erik:

To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:

0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)

1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)

]

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)

Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Error connecting to localhost site to view SQL server Mobile Agent on Device

I have set up a locahost site in IIS using the "Configure Web Synchronization Wizard". One of the first test I was asked to do was to open a broweser and browse to http://localhost/MobileTest/sqlcesa30.dll

If all is well I should get a web page with the following line:

SQL Server Mobile Server Agent 3.0

But unfortunately I get an error stating :
"The server has encountered an error while loading an application during the processing of your request. Please refer to the event log for more detail information. Please contact the server administrator for assistance."

I checekd the Event log and found this error related the IIS launch process on my machine.

DCOM got error "Logon failure: unknown user name or bad password. " and was unable to logon .\IWAM_Rataq in order to run the server:

where Rataq is the name of my machine. Could my IWAM_Rataq account be corrupted, my other sites on the machine work.

Infact if i put in a Test.htm page in the virtual directory MobileTest I am able to browse to the page.

Can the Account be re-set? is there another problem that I am not aware of. I have Norton Internet 2006 running could that be the reason? Could Norton be blocking the dll from loading? But the event log states it is a Account error.

Any ideas why this might occur? any help is always appreciated

It is unlikely to be that the account is corrupt. It is more likely to be a permissions issue somewhere between authenticating the user against the site (which from what you are saying is simply the anonymous user) and pulling data out of the database. I would suggest that in the "Configure Web Sync Wizard" that you select Windows authentication and see if that allows you to sync - this will use your current logon credentials (when you access the url) instead of the anoymous user.

Error after migrating to SQL Server 2005

I'm trying to migrate MS Access 2003 query into SQL Server 2005 view.
I'm getting error for the following query.

ERROR: Incorrect syntax near the keyword 'LEFT'

MS-Access query:
SELECT tbl.*, tblP.ParticipantLastName, tblP.ParticipantFirstName, tblP.ParticipantAmount, tblP.SocialSecurityNumber, tblP.Comment
FROM tbl LEFT JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE ToBeProcessed='YES' and [Cancel]=0;

SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl AS LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)

Drop the AS before the LEFT. your migration has assumed that LEFT was an alias for tbl rather than part of the JOIN clause for tblp

I assume this is still supported and so I would send a comment to the email address as this is clearly a bug.

|||SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)|||I have one more query that is not getting migrated/ converted to SQL

MS Access query:
SELECT qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived
FROM qryByDept
GROUP BY qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived;

Error Message:
* SSMA error messages:
* A2SS0058: Following SQL statement is not supported and cannot be converted:
*
|||

Could you please send email to accssma@.microsoft.com or post these problems in the microsoft.public.access.sqlupsizing newsgroup? See link below for more details:

http://www.microsoft.com/sql/solutions/migration/access/ssma_access_faq.mspx

You will get more help on the SSMA related issues there.

|||Your column qryByDept.Authorization is the problem. Authorization is a reserved word in t-sql. You can either alias the column with qryByDept.Authorization AS 'Authorization' or use qryByDept.Authorization [Authorization]. It also looks like you could get rid of the group by statement since you have no aggregation happening.|||I'm trying to modify the keyword and make this query work.

I need help with another query:

SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
Date() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between [forms]![frmTaxUnitMain]![txtAutonumberFrom] And [forms]![frmTaxUnitMain]![txtAutonumberTo]) AND ((tblCurrent.Archived)=-1));

Error on lines : Line 11 ans 20
|||I tried aliasingqryByDept.Authorization to
qryByDept.Authorization AS 'Authorization'
qryByDept.Authorization [Authorization]

But both didnt work. The error remains.
|||You can't do what you are trying. You are refering to fields on your forms which SQL doesn't know anything about. I think the solution is to create stored procedure that accepts the values you are passing and uses them as variables in the SP.|||ok.Is there any other alternative for getting the same results?
I'm new to SP and I'm trying to write a stored procedure to one the Access Queries.
But I'm not sure how to check if there are more records to check or not in the while loop.

ACCESS QUERY:

UPDATE
tbChckNum
INNER JOIN tblHtry ON tbChckNum.Auto = tblHtry.Auto
SET tblHtry.CheckAmt = ([tbChckNum].[amount1]),
tblHtry.ChckNum = [tbChckNum].[chcknum],
tblHtry.ChckDt = [tbChckNum].[chckdt],
tblHtry.AccntNum = [tbChckNum].[AcctNum],
tbChckNum.Updated = "YES"
WHERE (((tblHtry.ChckAmt) Is Null Or (tblHtry.CheckAmt)=0) AND ((tblHtry.ChckNum) Is Null) AND ((tblHtry.ChckDt) Is Null) AND ((tblHtry.AcctNum) Is Null));

STORED PROCEDURE:

CREATE PROCEDURE UpdChkNum

AS
BEGIN
SET NOCOUNT ON;

/*declare variable*/
DECLARE @.AcctNum1 varchar
DECLARE @.amt1 money
DECLARE @.ChkDt1 datetime
DECLARE @.Auto1 int
DECLARE @.ChkNum1 float

/*SELECT stmts for procedure*/
WHILE
BEGIN

SELECT
amount1,
chknum,
checkdate,
AcctNum,
Auto
Into
@.amt1,
@.chknum1,
@.chkdt1,
@.AcctNum1,
@.Auto1
from tblChkNum

UPDATE tblHtry
SET tblHtry.ChkAmt = @.amt1, @.chknum, @.chkdt, @.AcctNum where tblHtry.Auto = @.Auto

and (((tblHtry.ChkAmt) Is Null Or (tblHtry.ChkAmt)=0) AND
((tblHtry.ChkNum) Is Null) AND ((tblHtry.ChkDt) Is Null) AND
((tblHtry.AcctNum) Is Null))

BEGIN
IF (Auto = @.Auto)
UPDATE tblChkNum
SET tblChkNum.Updated = "YES"
--WHERE Auto = @.Auto
END

fetch next
END
END
GO

Error after migrating to SQL Server 2005

I'm trying to migrate MS Access 2003 query into SQL Server 2005 view.
I'm getting error for the following query.

ERROR: Incorrect syntax near the keyword 'LEFT'

MS-Access query:
SELECT tbl.*, tblP.ParticipantLastName, tblP.ParticipantFirstName, tblP.ParticipantAmount, tblP.SocialSecurityNumber, tblP.Comment
FROM tbl LEFT JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE ToBeProcessed='YES' and [Cancel]=0;

SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl AS LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)

Drop the AS before the LEFT. your migration has assumed that LEFT was an alias for tbl rather than part of the JOIN clause for tblp

I assume this is still supported and so I would send a comment to the email address as this is clearly a bug.

|||SQL Server View:
CREATE VIEW dbo.[qryReport]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
* Contact accssma@.microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/

SELECT
tbl.*,
tblP.ParticipantLastName,
tblP.ParticipantFirstName,
tblP.ParticipantAmount,
tblP.SocialSecurityNumber,
tblP.Comment
FROM tbl LEFT
JOIN tblP ON tbl.AutoNumber = tblP.RecordNumber
WHERE (ToBeProcessed = 'YES' AND [Cancel] = 0)|||I have one more query that is not getting migrated/ converted to SQL

MS Access query:
SELECT qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived
FROM qryByDept
GROUP BY qryByDept.AutoNumber, qryByDept.InputDate, qryByDept.FN, qryByDept.CompanyName, qryByDept.TotalAmount, qryByDept.TaxYear, qryByDept.Reason, qryByDept.Department, qryByDept.TaxType, qryByDept.AccountID, qryByDept.Payee, qryByDept.Address1, qryByDept.Address2, qryByDept.Address3, qryByDept.Participant, qryByDept.Username, qryByDept.Extension, qryByDept.ToSSC, qryByDept.Authorization, qryByDept.ManagersAuthorization, qryByDept.CheckNumber, qryByDept.CheckDate, qryByDept.Cancel, qryByDept.ParticipantInfo, qryByDept.ToBeProcessed, qryByDept.CheckAmount, qryByDept.Archived;

Error Message:
* SSMA error messages:
* A2SS0058: Following SQL statement is not supported and cannot be converted:
*|||

Could you please send email to accssma@.microsoft.com or post these problems in the microsoft.public.access.sqlupsizing newsgroup? See link below for more details:

http://www.microsoft.com/sql/solutions/migration/access/ssma_access_faq.mspx

You will get more help on the SSMA related issues there.

|||Your column qryByDept.Authorization is the problem. Authorization is a reserved word in t-sql. You can either alias the column with qryByDept.Authorization AS 'Authorization' or use qryByDept.Authorization [Authorization]. It also looks like you could get rid of the group by statement since you have no aggregation happening.|||I'm trying to modify the keyword and make this query work.

I need help with another query:

SELECT
tblCurrent.AutoNumber,
tblCurrent.TaxYear,
tblCurrent.TaxType,
tblCurrent.FEIN,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
tblCurrent.AccountID,
tblCurrent.ParticipantInfo,
'Refund' AS FileName,
Date() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Payee,
tblCurrent.Address1,
tblCurrent.Address2,
tblCurrent.Address3,
tblCurrent.Department,
tblCurrent.ToSSCRTT
FROM tblCurrent
WHERE (((tblCurrent.AutoNumber) Between [forms]![frmTaxUnitMain]![txtAutonumberFrom] And [forms]![frmTaxUnitMain]![txtAutonumberTo]) AND ((tblCurrent.Archived)=-1));

Error on lines : Line 11 ans 20|||I tried aliasing qryByDept.Authorization to
qryByDept.Authorization AS 'Authorization'
qryByDept.Authorization [Authorization]

But both didnt work. The error remains.
|||You can't do what you are trying. You are refering to fields on your forms which SQL doesn't know anything about. I think the solution is to create stored procedure that accepts the values you are passing and uses them as variables in the SP.|||ok.Is there any other alternative for getting the same results?
I'm new to SP and I'm trying to write a stored procedure to one the Access Queries.
But I'm not sure how to check if there are more records to check or not in the while loop.

ACCESS QUERY:

UPDATE
tbChckNum
INNER JOIN tblHtry ON tbChckNum.Auto = tblHtry.Auto
SET tblHtry.CheckAmt = ([tbChckNum].[amount1]),
tblHtry.ChckNum = [tbChckNum].[chcknum],
tblHtry.ChckDt = [tbChckNum].[chckdt],
tblHtry.AccntNum = [tbChckNum].[AcctNum],
tbChckNum.Updated = "YES"
WHERE (((tblHtry.ChckAmt) Is Null Or (tblHtry.CheckAmt)=0) AND ((tblHtry.ChckNum) Is Null) AND ((tblHtry.ChckDt) Is Null) AND ((tblHtry.AcctNum) Is Null));

STORED PROCEDURE:

CREATE PROCEDURE UpdChkNum

AS
BEGIN
SET NOCOUNT ON;

/*declare variable*/
DECLARE @.AcctNum1 varchar
DECLARE @.amt1 money
DECLARE @.ChkDt1 datetime
DECLARE @.Auto1 int
DECLARE @.ChkNum1 float

/*SELECT stmts for procedure*/
WHILE
BEGIN

SELECT
amount1,
chknum,
checkdate,
AcctNum,
Auto
Into
@.amt1,
@.chknum1,
@.chkdt1,
@.AcctNum1,
@.Auto1
from tblChkNum

UPDATE tblHtry
SET tblHtry.ChkAmt = @.amt1, @.chknum, @.chkdt, @.AcctNum where tblHtry.Auto = @.Auto

and (((tblHtry.ChkAmt) Is Null Or (tblHtry.ChkAmt)=0) AND
((tblHtry.ChkNum) Is Null) AND ((tblHtry.ChkDt) Is Null) AND
((tblHtry.AcctNum) Is Null))

BEGIN
IF (Auto = @.Auto)
UPDATE tblChkNum
SET tblChkNum.Updated = "YES"
--WHERE Auto = @.Auto
END

fetch next
END
END
GO

Error accessing report from anywhere but local server

I am trying to view a report that i created and I get an error everywhere but on the server, where it runs fine. the report uses the sharepoint lists.asmx web service as a datasource, and I'm using Windows Authentication on the datasource. I'm logged on to my desktop with the same account i use on the server, but on the server it runs and on the desktop i get the following error:

"An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'SiteTripList'. (rsErrorExecutingCommand) Get Online Help Failed to execute web request for the specified URL. (rsXmlDataProviderError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors "

After checking the event log I found a System.FormatException being thrown with the message "Input String was not in a correct format."

Any help would be greatly appreciated!!

josh meyer

What does the Properties -> Security look like?
|||I added my account specifically with full permissions. i just remembered that the list web service i'm accessing is actually not on the same server as reporting services. is this related to the "double-hop" issue? is there some workaround?|||Try this, I had problems connecting on my PC also until I ensured that my data source was shared. I thought I had done so when I specified it to be a shared data source when creating it in VS 2005 but I also had to do this:

In Report Manager

1) Click on Properties
2) Click on Data Sources
3) Make your Data Source Shared|||

Hi

It does not allow making it share as it throws error.Also even in my case muy reporting services is on other machine and sharepoint on other machine.Hence unable to get access to datasource(maybe).

Will certainly like to get soln to this problem

Error accessing report from anywhere but local server

I am trying to view a report that i created and I get an error everywhere but on the server, where it runs fine. the report uses the sharepoint lists.asmx web service as a datasource, and I'm using Windows Authentication on the datasource. I'm logged on to my desktop with the same account i use on the server, but on the server it runs and on the desktop i get the following error:

"An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'SiteTripList'. (rsErrorExecutingCommand) Get Online Help Failed to execute web request for the specified URL. (rsXmlDataProviderError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors "

After checking the event log I found a System.FormatException being thrown with the message "Input String was not in a correct format."

Any help would be greatly appreciated!!

josh meyer

What does the Properties -> Security look like?
|||I added my account specifically with full permissions. i just remembered that the list web service i'm accessing is actually not on the same server as reporting services. is this related to the "double-hop" issue? is there some workaround?|||Try this, I had problems connecting on my PC also until I ensured that my data source was shared. I thought I had done so when I specified it to be a shared data source when creating it in VS 2005 but I also had to do this:

In Report Manager

1) Click on Properties
2) Click on Data Sources
3) Make your Data Source Shared|||

Hi

It does not allow making it share as it throws error.Also even in my case muy reporting services is on other machine and sharepoint on other machine.Hence unable to get access to datasource(maybe).

Will certainly like to get soln to this problem

Error accessing report from anywhere but local server

I am trying to view a report that i created and I get an error everywhere but on the server, where it runs fine. the report uses the sharepoint lists.asmx web service as a datasource, and I'm using Windows Authentication on the datasource. I'm logged on to my desktop with the same account i use on the server, but on the server it runs and on the desktop i get the following error:

"An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'SiteTripList'. (rsErrorExecutingCommand) Get Online Help Failed to execute web request for the specified URL. (rsXmlDataProviderError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors "

After checking the event log I found a System.FormatException being thrown with the message "Input String was not in a correct format."

Any help would be greatly appreciated!!

josh meyer

What does the Properties -> Security look like?
|||I added my account specifically with full permissions. i just remembered that the list web service i'm accessing is actually not on the same server as reporting services. is this related to the "double-hop" issue? is there some workaround?|||Try this, I had problems connecting on my PC also until I ensured that my data source was shared. I thought I had done so when I specified it to be a shared data source when creating it in VS 2005 but I also had to do this:

In Report Manager

1) Click on Properties
2) Click on Data Sources
3) Make your Data Source Shared|||

Hi

It does not allow making it share as it throws error.Also even in my case muy reporting services is on other machine and sharepoint on other machine.Hence unable to get access to datasource(maybe).

Will certainly like to get soln to this problem