Restrict Warning Message

I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?Originally posted by subhasishray
I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?

The best way is to reduce the data size of the table to less than 8060 bytes.

other way .. i believe might be to set the option

set ansi_warnings off|||I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish

Clearly ... you are not paying heed to the warning message .. An insert into your table will fail in case the data is more than 8060 bytes . You should take that case into consideration.

Am searching for the other answer .. will get back as soon as i find it|||There is no "other" answer, you've said it all. the only other thing to do would be to split the table into 2 with 1-to-1 relationship.|||I was searching for a server property where i could control the severity level of warnings and errors as is availible in osql with the -m flag.|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish

Whether you know it or not...that comes across with some attitude..

If you can't be flexible...then I don't know who will...

good luck...

Restoring SQL Server2K backup onto SQL Server Express 2005

Is there any way to restore a full database backup made on sql server 2000
to SQL Server Express 2005? When I tried, I received an error message saying
the *.sho file could not be found.
Thanks,
Keith
hi Keith,
keithb wrote:
> Is there any way to restore a full database backup made on sql server
> 2000 to SQL Server Express 2005? When I tried, I received an error
> message saying the *.sho file could not be found.
actually it is... when a SQL Server 2000 database is restored onto a SQL
Server 2005 instance, it's database compatibility setting will not be ported
to 90, and you have some issues to fix (if you want to), like modifying the
compatibility level if you want the new SQL Server 2005 features to be
available for that database, update all statistics with FULL SCAN as they
get invalidated and set the database ownership to a valid login if you need
to access database diagrams..
and usually restoring a SQL Server 2000 database backup is a painless task..
I can only think of a SQL Server 2000 database exceeding the SQLExpress
limit of 4gb.. just that.. but I do not reaaly know what a "*.sho" file is..
how did you tried that?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Restoring of database using SQL-DMO part 2

Hi its me again, I posted a message some time ago concerning restoring of database.

http://www.dbforums.com/showthread.php?postid=3582736#post3582736

My restore module basically restores a backup of a database file.

I found that when I integrate my restore module with a login module, I am getting the same exclusive access error again. I also found out that the connection created for authenticating the user, remains sleeping even after it is closed, which in effect prevents my restore module to get exclusive access.

What am I doing wrong? Any ideas appreciated, thanks for reading!Run This before you restore database statement:
declare @.l_spid varchar(4)
,@.l_hostname varchar(20)

declare kill_cursor scroll cursor
for
select convert(varchar(4), spid), hostname from master..sysprocesses with (nolock)
where db_name(dbid) = 'Your Database Name'

open kill_cursor
select @.@.cursor_rows

fetch next from kill_cursor into
@.l_spid
,@.l_hostname
while (@.@.fetch_status = 0 )
begin
select @.l_hostname Killed
exec ( 'kill ' + @.l_spid)
fetch next from kill_cursor into
@.l_spid
,@.l_hostname
end
close kill_cursor
deallocate kill_cursor

YOUR RESTORE DATABASE STATEMENT|||Thanks suresh. I will try that.sql

Restoring DTS packages

I am receiving the following error message in restoring and saving DTS packages in SQL Server (SS 2000):

"Class not registered"

First time I have encountered this issue and will much appreciate suggestion on how to fix.

You can also try posting in the DTS forum:

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

Thanks for this suggestion.

I did post to the DTS forum, however no responses received

Restoring database with different sorting order

I am trying to restore a database (SQL server 7) with
sort ID 183 on a SQL server 7 with sort order ID 52.
I got this error message:
Microsoft SQL-DMO (ODBC SQLState: 42000):
The database you are attempting to restore was backed up
under different sort order ID(183) than the one currently
running on this server (52), and at least one of them is
a non-binary sort order.
Backup or restore operation terminating abnormally.
Any help please to restore this database on my server.
thanks
Database developerI'm afriad there is no way to restore different sort orders in SQL 7.
You could use DTS to transfer all objects over.
HTH
Ryan Waight, MCDBA, MCSE
"Database Developer" <database_udvikler@.hotmail.com> wrote in message
news:0c8301c38f0e$2d3ef280$a101280a@.phx.gbl...
> I am trying to restore a database (SQL server 7) with
> sort ID 183 on a SQL server 7 with sort order ID 52.
> I got this error message:
> Microsoft SQL-DMO (ODBC SQLState: 42000):
> The database you are attempting to restore was backed up
> under different sort order ID(183) than the one currently
> running on this server (52), and at least one of them is
> a non-binary sort order.
> Backup or restore operation terminating abnormally.
>
> Any help please to restore this database on my server.
> thanks
> Database developer|||Thank you Ryan for your quick reply.
Greetings
Database developer
>--Original Message--
>I'm afriad there is no way to restore different sort
orders in SQL 7.
>You could use DTS to transfer all objects over.
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Database Developer" <> wrote in message
>news:0c8301c38f0e$2d3ef280$a101280a@.phx.gbl...
>> I am trying to restore a database (SQL server 7) with
>> sort ID 183 on a SQL server 7 with sort order ID 52.
>> I got this error message:
>> Microsoft SQL-DMO (ODBC SQLState: 42000):
>> The database you are attempting to restore was backed
up
>> under different sort order ID(183) than the one
currently
>> running on this server (52), and at least one of them
is
>> a non-binary sort order.
>> Backup or restore operation terminating abnormally.
>>
>> Any help please to restore this database on my server.
>> thanks
>
>.
>

Restoring database receives UNICODE error

Hi,
I am having problem while restoring database to another SQL Server. I
receives below given message. My both SQL Servers are 7.0.
MESSAGE
SQL DMO (ODBC SQL Stae 42000)
The database you are attmpting to restore was backup under a different unicode
Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
Locale ID
1033 or Unicode comparion style 196611) Currently running on this server.
Backup or restore Operation terminating abnormally.
Ok Button.
MESSAGE OVER
Anyone who can solve this problem.
Nilkanth Desai
I think that although the error message is from ODBC the issue is in the
tool you're using. Perhaps you would get a better response posting to a
group with knowledge of the tools you're using - or to the SQL Server team
in general.
| Thread-Topic: Restoring database receives UNICODE error
| thread-index: AcXKdpDDNRh+0thOR76Cjqm0RNXUxA==
| X-WBNR-Posting-Host: 61.1.58.32
| From: "=?Utf-8?B?Tmlsa2FudGggRGVzYWk=?="
<NilkanthDesai@.discussions.microsoft.com>
| Subject: Restoring database receives UNICODE error
| Date: Thu, 6 Oct 2005 06:05:03 -0700
| Lines: 23
| Message-ID: <D3F9E543-916B-44A7-ACD4-AD3200C10CA6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2822
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am having problem while restoring database to another SQL Server.
I
| receives below given message. My both SQL Servers are 7.0.
|
| MESSAGE
| SQL DMO (ODBC SQL Stae 42000)
| The database you are attmpting to restore was backup under a different
unicode
| Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
| Locale ID
| 1033 or Unicode comparion style 196611) Currently running on this server.
|
| Backup or restore Operation terminating abnormally.
|
| Ok Button.
|
|
| MESSAGE OVER
|
| Anyone who can solve this problem.
|
| Nilkanth Desai
|
|
|

Restoring database receives UNICODE error

Hi,
I am having problem while restoring database to another SQL Server. I
receives below given message. My both SQL Servers are 7.0.
MESSAGE
SQL DMO (ODBC SQL Stae 42000)
The database you are attmpting to restore was backup under a different unico
de
Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
Locale ID
1033 or Unicode comparion style 196611) Currently running on this server.
Backup or restore Operation terminating abnormally.
Ok Button.
MESSAGE OVER
Anyone who can solve this problem.
Nilkanth DesaiI think that although the error message is from ODBC the issue is in the
tool you're using. Perhaps you would get a better response posting to a
group with knowledge of the tools you're using - or to the SQL Server team
in general.
--
| Thread-Topic: Restoring database receives UNICODE error
| thread-index: AcXKdpDDNRh+0thOR76Cjqm0RNXUxA==
| X-WBNR-Posting-Host: 61.1.58.32
| From: "examnotes"
<NilkanthDesai@.discussions.microsoft.com>
| Subject: Restoring database receives UNICODE error
| Date: Thu, 6 Oct 2005 06:05:03 -0700
| Lines: 23
| Message-ID: <D3F9E543-916B-44A7-ACD4-AD3200C10CA6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2822
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am having problem while restoring database to another SQL Server.
I
| receives below given message. My both SQL Servers are 7.0.
|
| MESSAGE
| SQL DMO (ODBC SQL Stae 42000)
| The database you are attmpting to restore was backup under a different
unicode
| Locale ID (1033) or Unicode Comparision Style 196609) Then the Unicode
| Locale ID
| 1033 or Unicode comparion style 196611) Currently running on this server.
|
| Backup or restore Operation terminating abnormally.
|
| Ok Button.
|
|
| MESSAGE OVER
|
| Anyone who can solve this problem.
|
| Nilkanth Desai
|
|
|

restoring database leaves me with (Restoring...) message

I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)

Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.

Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.

Any advice on how to get this thing finished out?

can u post the script which u r used for backup and restore. did u use with Move option while restoring.

Madhu

|||

Hi,

try

restore database <yourdatabase> with recovery

and as suggested plese post the script you used to restore database.

Hemantgiri S. Goswami