Problem with [Left] in SQL Server 2005?

We have a table that contains a column named "Left". (Yea, I know, this was a bad idea - but it is not within my power to change it.)

Our stored procedures access it using [Left] without any problems in Sql Server 2000 and Sql Server Express Edition. HOWEVER, it does NOT work with SQL Server 2005. It generates the error: Column 'Left' is read-only.

Has anyone seen this? Is there a problem with SQL Server 2005 and reserved words using [ ] notation?

Thanks!

This is not a problem with the identifier (quoted or bracketed). The error message indicates that you are trying to modify the column using a query expression (CTE, view or derived table) and it is seen as read-only. Can you post a simple repro showing the problem? Or can you post the statement that is generating the error message.|||

We just tried running the stored procedure directly, and it works fine as well. So this must be a problem with ADO? Does that mean that I should be posting this question elsewhere? (Again, it works fine if we just reset the connection string to point to the database in SQL Server 2000 or SQL Server Express.)

This is a HUGE application that uses a form of the MS data block so it may be challenging (i.e. time consuming) to pull together a reasonably sized repro. But I'll see what I can get approval for ...

|||It could be something wrong in the client side. The client might be trying to update the query result (from query or view or SP). For example, you could be using wrong cursor type in the client code that might make the application think that a result is updateable. You could run a SQL Profiler trace to determine the statement from the client that is causing the error on the server. And there is no need to post the repro involving the client and the exact code. You should try to reproduce the problem independently using smaller set of tables and sample data. This is often the best way to isolate and understand the problem.|||

Found the problem. It had nothing to do with the [Left] actually. The stored procedure was doing a Union. The union seems to produce an updatable dataset in SQL Server 2000 and in SQL Server Express but NOT in Sql Server 2005.

Interesting ...

Problem while giving Quarter function

Sir,

When I am giving the command like Quarter(DateOfOrder) in new named dimension expression column , one error is showing ' Quarter is not recognised built in function ' . Please help me to find out the quarter of Date of Order column..

Thanks in advance..

Regards

Polachan

You need to use an expression such as

DATEPART(quarter,DateOfOrder)

To get the result you require

|||

Dear Sir,

Thanks a lot

Sir Please can u give me a help for the following problem while deployment of the project

Error 4

When I am deploying the project I got the following error. Please help me sir

Errors in the OLAP storage engine: An error occurred while processing the 'Product Tran Header' partition of the 'Product Tran Header' measure group for the 'Test' cube from the productreport database. 0 0

I done the following steps

1. added new measure selecting new source table

2. selected one column dateoforder

3. Add new dimension as without using data source

4. selected server time dimension

5. Selected Year/Month/Quarter/date

6 Selected fiscal year

7.Selected dimension usage in cube desgn

8. selected time dimension and selected regular relation, Granulary attribute as Date

9. Measure group table 'Product tran header' new measure group

10 selected measure group column as dateoforder.

after that while deploying the above mentioned error will come

Please help

|||As far as I know, I answered your question. Why have you answered that with a completely different question ? |||

Sir,

I wan to know how to give relationship Servertime Dimension with the column in the table DateOfOreder. When I am giving this with the mentioned step I got the error. Please help me sir...

Problem when creating a new named calculation

I have a Customer dimension in my model, and now I want to count the # unique customers in one of my fact tables.

Therefore I created a new named calculation in the fact table which does a count distinct of the customerkey in the fact table.

And when I try to add a new report model i get the following error message, even when I don't select anything in the Select report model generation rules screen:

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Command:
SELECT
COUNT([DEPARTURE]),
COUNT(DISTINCT [DEPARTURE]),
COUNT((SELECT
DISTINCT(
COUNT(CLIENTKEY)) AS Number_of_Persons
FROM
FCT_TABLE AS FCT_TABLE)),
COUNT(DISTINCT (SELECT
DISTINCT(
COUNT(CLIENTKEY)) AS Number_of_Persons FROM
FCT_TABLE
FCT_TABLE)),
COUNT(Arrival),
COUNT(DISTINCT Arrival)
FROM [dbo].[FCT_TABLE] t

BUTTONS:

OK

What should I do?

As I understand you have defined one of named calculations as

(SELECT DISTINCT(COUNT(CLIENTKEY)) AS Number_of_Persons
FROM
FCT_TABLE AS FCT_TABLE)

Yes, this named calculation will not work in some cases. But it will work if you push it into a named query. Redefine you FCT_TABLE table in DSV editor as a named query:

SELECT
*,
(SELECT DISTINCT(COUNT(CLIENTKEY)) FROM FCT_TABLE) AS Number_of_Persons
FROM FCT_TABLE

Problem when connecting to a named sql 2000 instance

Hi,
I have a problem when connecting to a named sql 2000 instance . The instance
is running in a 2 node cluster configuration. The second instance on the
other node works fine.
When I try to connect with a client on another pc, the connection only works
when I set the correct (static) port number.
But if I leave it to dynamically determine ports it is not able to connect.
There is no firewall between the client pc and the database server
and udp port 1434 is listening.
Stranger is that this problem also occurs on the server itself. When i
connect to the servernode where the instance of sql is running,
i have to set an alias with the portnumber to be able to connect to the
instance. Without this the connection fails.
Anybody any idea(s)?
Regards,
CihatUnfortunately no response. Here my response so that others with same problem
maybe solve it more quickly. When this happens and all checks appear okey,
you have to failover the group and reboot the server where this problem
occur. After the reboot it worked fine for me. Still dont know the cause, bu
t
it works now.
"Cihat" wrote:

> Hi,
> I have a problem when connecting to a named sql 2000 instance . The instan
ce
> is running in a 2 node cluster configuration. The second instance on the
> other node works fine.
> When I try to connect with a client on another pc, the connection only wor
ks
> when I set the correct (static) port number.
> But if I leave it to dynamically determine ports it is not able to connect
.
> There is no firewall between the client pc and the database server
> and udp port 1434 is listening.
> Stranger is that this problem also occurs on the server itself. When i
> connect to the servernode where the instance of sql is running,
> i have to set an alias with the portnumber to be able to connect to the
> instance. Without this the connection fails.
> Anybody any idea(s)?
> Regards,
> Cihat
>

Problem when connect to SQL Server 2005 (SQL Native Client + Named instance)

Hi,
I have an application which previous connect to SQL Server 2000.
Now, I installed SQL Server 2005, and try to conncet to it.
The previous connection string is:
sString.Format("DSN=%s;uid=%s;pwd=%s", sDb, sUser, sPassword);
In order to support SQL Native Client driver in SQL Server 2005, I
changed the connection string to:
sString.Format("Provider=SQLNCLI;
DataTypeCompatibility=80;DSN=%s;uid=%s;pwd=%s", sDb, sUser,
sPassword);
I have a default instance and a named instance in my database. So i
created two DSN:
(1) Use SQL Native Client as the driver and connect to the default
instance.
The application could work well under this configuration.
(2) Use SQL Native Client as the driver and connect to the named
instance.
The application could not work successfully under this
configuration.
It could not connect to the database.
I have started the SQL Server Browser service.
Why I could not connect to the database when i use SQL Native Client +
Named instance
Who can help me?
Thanks in advance.
Did you enable the Allow Remote Connections option? Have you run the
Surface Area Configurator and the SQL Server Configuration Manager?
Andrew J. Kelly SQL MVP
<yangrudy@.126.com> wrote in message
news:1140155887.725187.43270@.g44g2000cwa.googlegro ups.com...
> Hi,
> I have an application which previous connect to SQL Server 2000.
> Now, I installed SQL Server 2005, and try to conncet to it.
> The previous connection string is:
> sString.Format("DSN=%s;uid=%s;pwd=%s", sDb, sUser, sPassword);
> In order to support SQL Native Client driver in SQL Server 2005, I
> changed the connection string to:
> sString.Format("Provider=SQLNCLI;
> DataTypeCompatibility=80;DSN=%s;uid=%s;pwd=%s", sDb, sUser,
> sPassword);
> I have a default instance and a named instance in my database. So i
> created two DSN:
> (1) Use SQL Native Client as the driver and connect to the default
> instance.
> The application could work well under this configuration.
> (2) Use SQL Native Client as the driver and connect to the named
> instance.
> The application could not work successfully under this
> configuration.
> It could not connect to the database.
> I have started the SQL Server Browser service.
> Why I could not connect to the database when i use SQL Native Client +
> Named instance
> Who can help me?
> Thanks in advance.
>

Problem when connect to SQL Server 2005 (SQL Native Client + Named instance)

Hi,
I have an application which previous connect to SQL Server 2000.
Now, I installed SQL Server 2005, and try to conncet to it.
The previous connection string is:
sString.Format("DSN=%s;uid=%s;pwd=%s", sDb, sUser, sPassword);
In order to support SQL Native Client driver in SQL Server 2005, I
changed the connection string to:
sString.Format("Provider=SQLNCLI;
DataTypeCompatibility=80;DSN=%s;uid=%s;p
wd=%s", sDb, sUser,
sPassword);
I have a default instance and a named instance in my database. So i
created two DSN:
(1) Use SQL Native Client as the driver and connect to the default
instance.
The application could work well under this configuration.
(2) Use SQL Native Client as the driver and connect to the named
instance.
The application could not work successfully under this
configuration.
It could not connect to the database.
I have started the SQL Server Browser service.
Why I could not connect to the database when i use SQL Native Client +
Named instance
Who can help me?
Thanks in advance.Did you enable the Allow Remote Connections option? Have you run the
Surface Area Configurator and the SQL Server Configuration Manager?
Andrew J. Kelly SQL MVP
<yangrudy@.126.com> wrote in message
news:1140155887.725187.43270@.g44g2000cwa.googlegroups.com...
> Hi,
> I have an application which previous connect to SQL Server 2000.
> Now, I installed SQL Server 2005, and try to conncet to it.
> The previous connection string is:
> sString.Format("DSN=%s;uid=%s;pwd=%s", sDb, sUser, sPassword);
> In order to support SQL Native Client driver in SQL Server 2005, I
> changed the connection string to:
> sString.Format("Provider=SQLNCLI;
> DataTypeCompatibility=80;DSN=%s;uid=%s;p
wd=%s", sDb, sUser,
> sPassword);
> I have a default instance and a named instance in my database. So i
> created two DSN:
> (1) Use SQL Native Client as the driver and connect to the default
> instance.
> The application could work well under this configuration.
> (2) Use SQL Native Client as the driver and connect to the named
> instance.
> The application could not work successfully under this
> configuration.
> It could not connect to the database.
> I have started the SQL Server Browser service.
> Why I could not connect to the database when i use SQL Native Client +
> Named instance
> Who can help me?
> Thanks in advance.
>