Having problems with text datatype!

Hi,

pls can anyone help me to solve the error generated by this query,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spAP_PS_VENDOR_CONVER]

AS

SET NOCOUNT ON

SELECT UPPER(SETID) AS SETID

,UPPER(VENDOR_ID)AS VENDOR_ID

,CONVER_DT

,CONVER_SEQ_NUM

,CNTCT_SEQ_NUM

,UPPER(CONVER_TOPIC) AS CONVER_TOPIC

,UPPER(OPRID)AS OPRID

,REVIEW_DAYS

,REVIEW_DATE

,REVIEW_NEXT_DATE

,UPPER(KEYWORD1) AS KEYWORD1

,UPPER(KEYWORD2) AS KEYWORD2

,UPPER(KEYWORD3) AS KEYWORD3

,CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)) AS DESCRLONG

,PROCESS_INSTANCE

,MAX(EY_SF_UPDATE_DTTM) AS EY_SF_UPDATE_DTTM

,PROCESS_DTTM

,CREATED_DTTM

,UPPER(EY_SF_ACTN_FLG) AS EY_SF_ACTN_FLG

,UPPER(EY_SF_STATUS) AS EY_SF_STATUS

FROM Metastorm.dbo.AP_PS_VENDOR_CONVER

WHERE EY_SF_STATUS='N'

GROUP BY SETID,VENDOR_ID,CONVER_DT,CONVER_SEQ_NUM,CNTCT_SEQ_NUM,CONVER_TOPIC,OPRID

,REVIEW_DAYS,REVIEW_DATE,REVIEW_NEXT_DATE,KEYWORD1,KEYWORD2,KEYWORD3,DESCRLONG

,PROCESS_INSTANCE,PROCESS_DTTM,CREATED_DTTM,EY_SF_ACTN_FLG

,EY_SF_STATUS

SET NOCOUNT OFF

Msg 306, Level 16, State 2, Procedure spAP_PS_VENDOR_CONVER, Line 4

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Regards,

Sg

sorry forgot to tell that the descrlong is a text column

Regards,

sg

|||

You couldn't use text, ntext, image datatype in group by.

Try use following statement in group by:

Code Snippet

GROUP BY CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)), .....

|||

Hi Konstantin,

Thanks a lot.

Regards,

Sg

have SQL Server Mgmt Studio save alter table script when change table?

Is there a way to have SQL Server Management Studio save the script for
everything necessary to alter a table when changing the design of a table?
The older Visual Studio interfaces would allow this - maybe I can discover w
here
to turn that on in Visual Studio 2005?
--
Thanks in advance, Les CaudleHello Les,
If I understand the issue correctly, you'd like to save the script for
alterring a table when you change the design of the table in Managment
Studio.
You could use the following method to see if it meets your requirement:
1. Right click a table->Design to open the table in design view.
2. Do the change you want for the table.
3. Right click the blank area of the design view->Genegrate Change Script.
Also, If you have any feedback or wishes on SQL Server, I encourage you
submit via the link below and our product team would like to hear your
voice:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further qusetions. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Les,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

have SQL Server Mgmt Studio save alter table script when change table?

Is there a way to have SQL Server Management Studio save the script for
everything necessary to alter a table when changing the design of a table?
The older Visual Studio interfaces would allow this - maybe I can discover where
to turn that on in Visual Studio 2005?
Thanks in advance, Les Caudle
Hello Les,
If I understand the issue correctly, you'd like to save the script for
alterring a table when you change the design of the table in Managment
Studio.
You could use the following method to see if it meets your requirement:
1. Right click a table->Design to open the table in design view.
2. Do the change you want for the table.
3. Right click the blank area of the design view->Genegrate Change Script.
Also, If you have any feedback or wishes on SQL Server, I encourage you
submit via the link below and our product team would like to hear your
voice:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Please let's know if you have any further qusetions. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Les,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

have SQL Server Mgmt Studio save alter table script when change table?

Is there a way to have SQL Server Management Studio save the script for
everything necessary to alter a table when changing the design of a table?
The older Visual Studio interfaces would allow this - maybe I can discover where
to turn that on in Visual Studio 2005?
--
Thanks in advance, Les CaudleHello Les,
If I understand the issue correctly, you'd like to save the script for
alterring a table when you change the design of the table in Managment
Studio.
You could use the following method to see if it meets your requirement:
1. Right click a table->Design to open the table in design view.
2. Do the change you want for the table.
3. Right click the blank area of the design view->Genegrate Change Script.
Also, If you have any feedback or wishes on SQL Server, I encourage you
submit via the link below and our product team would like to hear your
voice:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Please let's know if you have any further qusetions. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Les,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Happy New Year and Christmas everybody! Another question:

Can i alter a table included in a Publication?

I am having problems with that.

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

With this, you can alter a table and add/drop/alter a column and this DDL action will be propagated to the subscribers.

|||

For DDL opertion on replicated object tables , in SQL 2000 you can use sp_repladdcolumn/sp_repldropcolumn to add/remove the table columns

|||

Mahesh Dudgikar wrote:

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

But if my compatibility level is set to 80 - can I change it to 90, call ALTER TABLE, and then change it back to 80? Will it cause any problems?

|||

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

|||If your compatibility is set to 80 then you are running in SQL 2000 mode. So you have to use the sp_repladdcolumn/sp_repldropcolumn to add/drop columns.|||

Mahesh Dudgikar wrote:

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

1. Is compatibility level defined separately for DATABASE and for PUBLICATION?

2. If not: I have tried to change the compatibility level for unpublished database (both upgrading and downgrading), and it succeeded. Does publication lock downgrading of compatibility level of the database?

Thanks in advance!

|||

Sorry for not being clear.

Above, I meant @.publication_compatibility_level of the publication setting, not the database compat level.

They are separate settings. The database compat level has nothing to do with the DDL method (sp_repladdcolumn and alter table)

Hanging during READ_COMMITTED_SNAPSHOT ON

When issuing the below command on any of our databases, it just hangs
forever.

ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.

This is a pain though and has to be done after hours. Is there a way to
issue the command while the system is in use, possibly taking just that
database offline (and not all other Dbs on the server) for a short time
and then returning it back to use using just scripting?pb648174 (google@.webpaul.net) writes:
> When issuing the below command on any of our databases, it just hangs
> forever.
> ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
> I realize that all connections expect the query window need to be
> closed and that is the case I think, or at least we are resetting the
> web server and still see the issue. The only way I have been able to
> fix it is to completely stop and restart the database server, then
> issue the command and it returns immediately.

Did you use sp_who to see what other connections to the database that
were active?

You can use

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

as a guick way to get everyone out. Don't forget to set it back to
multi user when you are done.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The problems happen intermittently, so I can't really execute the
sp_who statement when there is an error. Theoretically, how could it
ever block? Shouldn't it just get the version of the row before the
transaction started for the read operation?|||Whoops, wrong message, nevermind|||In case anyone is interested, here is the final solution we came up
with. It assumes the current DB is the one you want to set for snapshot
mania and will execute only on SQL 2005 without throwing errors on SQL
2000.

if(charindex('Microsoft SQL Server 2005',@.@.version) > 0)
begin
declare @.sql varchar(8000)
select @.sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK
IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;
'
Exec(@.sql)
end
go|||pb648174 (google@.webpaul.net) writes:

> In case anyone is interested, here is the final solution we came up
> with. It assumes the current DB is the one you want to set for snapshot
> mania and will execute only on SQL 2005 without throwing errors on SQL
> 2000.
> if(charindex('Microsoft SQL Server 2005',@.@.version) > 0)

A somewhat simpler test:

if serverproperty('ProductVersion') not like '[1-8].%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||if CAST(serverproperty('ProductVersion') as varchar) not like '[1-8].%'

Erland Sommarskog wrote:
> pb648174 (google@.webpaul.net) writes:
> > In case anyone is interested, here is the final solution we came up
> > with. It assumes the current DB is the one you want to set for snapshot
> > mania and will execute only on SQL 2005 without throwing errors on SQL
> > 2000.
> > if(charindex('Microsoft SQL Server 2005',@.@.version) > 0)
> A somewhat simpler test:
> if serverproperty('ProductVersion') not like '[1-8].%'
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi *,
Is there any simpler way to set Isolation level means not
using the PL/SQL block.

~Vivek

--
sharma_vivek_us
----------------------
sharma_vivek_us's Profile: http://www.dbtalk.net/m441
View this thread: http://www.dbtalk.net/t309038|||sharma_vivek_us (sharma_vivek_us.29osdz@.no-mx.forums.yourdomain.com.au)
writes:
> Is there any simpler way to set Isolation level means not
> using the PL/SQL block.

Could you clarify what you want to achieve?

"PL/SQL block" is not something you need to use with SQL Server - or even
can. PL/SQL is Oracle or DB2.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What's you hardware conf.? You may want to check the controller's
behavior...The versionning behind READ_COMMITTED_SNAPSHOT imposes super
heavy IO overhead on the controller...Set up a trace on Physical Disk:
Average sec per/write
Physical Disk: Average sec per/read and monitor until next
problem...Intermittent behaviors often result of a extreme disphase
between physical resources and logical need...

pb648174 wrote:
> When issuing the below command on any of our databases, it just hangs
> forever.
> ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
> I realize that all connections expect the query window need to be
> closed and that is the case I think, or at least we are resetting the
> web server and still see the issue. The only way I have been able to
> fix it is to completely stop and restart the database server, then
> issue the command and it returns immediately.
> This is a pain though and has to be done after hours. Is there a way to
> issue the command while the system is in use, possibly taking just that
> database offline (and not all other Dbs on the server) for a short time
> and then returning it back to use using just scripting?|||FYI
READ_COMMITED_SNAPSHOT is the early MS attempts at implementing ORACLE
Read Consistency versionning...

sharma_vivek_us wrote:
> Hi *,
> Is there any simpler way to set Isolation level means not
> using the PL/SQL block.
>
> ~Vivek
>
> --
> sharma_vivek_us
> ----------------------
> sharma_vivek_us's Profile: http://www.dbtalk.net/m441
> View this thread: http://www.dbtalk.net/t309038