Incorrect syntax near....?

I keep getting this error...?

Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'c'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Line 1: Incorrect syntax near 'c'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659 System.Web.UI.WebControls.BaseDataList.GetData() +53 System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +267 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56 System.Web.UI.WebControls.BaseDataList.DataBind() +62 System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55 System.Web.UI.WebControls.BaseDataList.CreateChildControls() +62 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

I think it talking about my SQL Query...?? If you could help me it would greatly appreciated! Thanks

strSQLQuery =

"Select c.EmployeeID, c.[FirstName] as UserFirstName, c.[LastName] as UserLastName, eqtype.[Description] as UserEquipType, eq.[SerialNo] as UserSerialNum from EMPLOYEES c LEFT OUTER JOIN EQUIPMENT as eq on eq.EmployeeID = c.EmployeeID LEFT OUTER JOIN EQUIP_TYPE as eqtype on eqtype.EquipTypeID = eq.EquipTypeID"

I could be wrong but the error is the c.EmployeeID because the ID is IDENTITY which is a property not a column. In the mean time download SQL Prompt for free from Red Gate to use intelisense in Management Studio. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

|||You are right in saying that your query is causing the error. But I have to say that the query looks OK to me. Can you show us a little more of the code? Are you further modifying strSQLQuery in any manner?|||

Can you run this query in "Query analyser", it might give you more information

|||

It was a space... :(

|||

strSQLQuery =

"Select c.EmployeeID, c.[FirstName] as UserFirstName, c.[LastName] as UserLastName, eqtype.[Description] as UserEquipType, eq.[SerialNo] as UserSerialNum from EMPLOYEES c LEFT OUTER JOIN EQUIPMENT as eq on eq.EmployeeID = c.EmployeeID LEFT OUTER JOIN EQUIP_TYPE as eqtype on eqtype.EquipTypeID = eq.EquipTypeID " <<<< right after eq.EquipTypeID and "!!

Incorrect Order in rendering report

Hi,

I have this problem on Reporting Services 2005 SP2:

There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important.

If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.

Some one have informations about this issue?

Kind Regards,

Elia.

Did you try sorting in the report? If so doesn't it still sort in the order that you selected.

If not try sorting in the report, within table properties you will see sorting within which you can specify the sort order

|||

Thanks,

I have resolved the problem.

Regards,

Elia.

Inconsistent OLE DB Failure when running SSIS packages

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @.[User::ConString] , "Royalty", @.[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.

I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto

Any ideas welcome

The ideal patch level is 2153 (at this point), available at http://support.microsoft.com/kb/918222/.

Prior to the main data flow , it a useful diagnostic to log/print the connection strings in a script task (there are some examples on the forum of iterating through DTS.Connections). This shows the result of expression/configuration should it have occurred prior to that point. I realize your issue here is intermittent, but perhaps this step can narrowed down matters further.

Also, the access mode of Table/View vs. SQL Command has a different db activity pattern (there are some blog posts to this effect), and anyone can see as much using SQL Server profiler. If the problem persists, you may wish to switch from accessing the source via OPENROWSET mode ("Table/View") [dbo].[RIGDUE_DAY] to accessing via Sql Command access mode "select x,y,z from [dbo].[RIGDUE_DAY]".

|||Thanks for responding, I checked the patch level and we are up to date (2153) I will try inserting a diagnostic step to output the connection string. Re the method of accessing the source, the problem has been with the destination connections, would this still apply?|||I was having intermittent connections fail on one installation. We tracked it down to contention for server resources with other clients (info-point). When we kick out other clients, the server would respond reliably. Not all clients use resources and/or licenses appropriately when connecting to servers. Do you have other clients connecting?|||

I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.

All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem?

|||

Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.

Not 100% sure why this works but it seems to.

Hope this helps

Inconsistent OLE DB Failure when running SSIS packages

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @.[User::ConString] , "Royalty", @.[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.

I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto

Any ideas welcome

The ideal patch level is 2153 (at this point), available at http://support.microsoft.com/kb/918222/.

Prior to the main data flow , it a useful diagnostic to log/print the connection strings in a script task (there are some examples on the forum of iterating through DTS.Connections). This shows the result of expression/configuration should it have occurred prior to that point. I realize your issue here is intermittent, but perhaps this step can narrowed down matters further.

Also, the access mode of Table/View vs. SQL Command has a different db activity pattern (there are some blog posts to this effect), and anyone can see as much using SQL Server profiler. If the problem persists, you may wish to switch from accessing the source via OPENROWSET mode ("Table/View") [dbo].[RIGDUE_DAY] to accessing via Sql Command access mode "select x,y,z from [dbo].[RIGDUE_DAY]".

|||Thanks for responding, I checked the patch level and we are up to date (2153) I will try inserting a diagnostic step to output the connection string. Re the method of accessing the source, the problem has been with the destination connections, would this still apply?|||I was having intermittent connections fail on one installation. We tracked it down to contention for server resources with other clients (info-point). When we kick out other clients, the server would respond reliably. Not all clients use resources and/or licenses appropriately when connecting to servers. Do you have other clients connecting?|||

I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.

All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem?

|||

Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.

Not 100% sure why this works but it seems to.

Hope this helps

Inconsistent OLE DB Failure when running SSIS packages

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @.[User::ConString] , "Royalty", @.[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.

I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto

Any ideas welcome

The ideal patch level is 2153 (at this point), available at http://support.microsoft.com/kb/918222/.

Prior to the main data flow , it a useful diagnostic to log/print the connection strings in a script task (there are some examples on the forum of iterating through DTS.Connections). This shows the result of expression/configuration should it have occurred prior to that point. I realize your issue here is intermittent, but perhaps this step can narrowed down matters further.

Also, the access mode of Table/View vs. SQL Command has a different db activity pattern (there are some blog posts to this effect), and anyone can see as much using SQL Server profiler. If the problem persists, you may wish to switch from accessing the source via OPENROWSET mode ("Table/View") [dbo].[RIGDUE_DAY] to accessing via Sql Command access mode "select x,y,z from [dbo].[RIGDUE_DAY]".

|||Thanks for responding, I checked the patch level and we are up to date (2153) I will try inserting a diagnostic step to output the connection string. Re the method of accessing the source, the problem has been with the destination connections, would this still apply?|||I was having intermittent connections fail on one installation. We tracked it down to contention for server resources with other clients (info-point). When we kick out other clients, the server would respond reliably. Not all clients use resources and/or licenses appropriately when connecting to servers. Do you have other clients connecting?|||

I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.

All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem?

|||

Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.

Not 100% sure why this works but it seems to.

Hope this helps

Inconsistency between Data Source Designer and underlying code

I have 2 data sources that have recently been updated from SQL Express to full versions of 2005. The connection strings have been changed, and the changes appear in the code, but the data source designer still shows the SQLExpress portion of the connection string. This seems to be fouling up SSIS packages that are using these data sources. Has anyone else encountered this? If so, what can I do to fix this issue?Can you recreate them?|||Are you using configurations? If so, have you updated them to reflect the right connect strings?|||Turns out the problem was this: the project was checked out by the person who created the datasources. When he checked the datasources in, he did NOT check the project back in. The project's definition contained the incorrect connection strings. Once the project was checked in, that issue was resolved. Thanks for your responses.

Including SQL Server Allow Nulls fields in updateable controls

When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record! If I remove the Allow Nulls field I can then edit it! What am I missing here?

If your record is going to have null values in it (which is quite common) you have to add a DataSet file to your site, create a TableAdapter and then use the TableAdapter as the data source 'Object' for the data control. ... ... Wait, ... let me breath... ... that's not the end of it... ... ... When choosing the data source for the control, make sure when you get to the Define Parameters dialog that the advanced property ConvertEmptyStringToNull is true. It took me about 12 hours to figure this out but I have also read that it has taken others up to a week so I guess I'm doing good. I REALLY WISH MICROSOFT WOULD HAVE SPELLED THIS OUT MORE CLEARLY IN THEIR HELP FILES.

Including columns with SQL Server Allow Nulls Checked

Why is it that when I include a column from my SQL Server database table, which has it's Allow Nulls checked, in the data source of a control that the record becomes not update-able? How do I get around this?

If your record is going to have null values in it (which is quite common) you have to add a DataSet file to your site, create a TableAdapter and then use the TableAdapter as the data source 'Object' for the data control. ... ... Wait, ... let me breath... ... that's not the end of it... ... ... When choosing the data source for the control, make sure when you get to the Define Parameters dialog that the advanced property ConvertEmptyStringToNull is true. It took me about 12 hours to figure this out but I have also read that it has taken others up to a week so I guess I'm doing good. I REALLY WISH MICROSOFT WOULD HAVE SPELLED THIS OUT MORE CLEARLY IN THEIR HELP FILES.

In Version 2005 : Dimension 'Time 1' : The source is not specified.

Hello,

I have the message 'Dimension 'Time 1' : The source is not specified.' when I processed or deploy my dimension 'Time 1'. This dimension has been create with the Assistant of the dimension.

I have the same problem with a cube !

What do I have to make?

Thanks

Looks like you missed something while designing your dimension. The error you are getting comes at validation stage. BI Dev Studio is trying to validate that all the objects in your database are defined correctly.

Try to run Dimension Wizard again and re-create the dimension.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.