How can i replace some set of white space to one white space in between string

Dear Frnd

I have to remove some set of white space in between the string and i have to replace with one white space

eg:- my name is divakar

I have to change the string to like this

my name is divakar.

so how can write sql query for that

Code Snippet

declare @.var varchar(100)

set @.var = 'my name is divakar'

while charindex(' ', @.var) > 0 --two spaces

begin

set @.var = replace(@.var, ' ', ' ') -- first is two spaces, second is one space

end

select @.var

|||

Having a function (recursive) to use it on your query...

Code Snippet

Create Function dbo.ProperWhiteSpace

(

@.String varchar(1000)

)

Returns Varchar(1000)

as

Begin

If CharIndex('',@.String) <> 0

Return dbo.ProperWhiteSpace(Replace(@.String,'', ' '));

Return @.String

End

Go

Select dbo.ProperWhiteSpace('mynameis divakar')

|||

Thx Dale

|||thx Manivannan.D.Sekaran

How can I put this forum on a news reader like Outlook Express?

Dear Anyone,

I'm used to having my newsgroups on my outlook express. But I cant seem to find a this particular forum on the regular group listings. Is there a way I can get these sql2k5 forums on my outlook express?
Thanks,
Joseph

RSS feeds is the only way you could get instant updates in this forum.

You could aggregate RSS feeds into your Outlook using NewsGator or Attensa, the standard edition of NewsGator is free, you can get it at this site:
https://www.newsgator.com/ngs/Ad_Outlook.aspx
and Attensa is free:
http://www.attensa.com/
Hope this helps,

-chris

How can I obtain all the SQL SERVER's names in my domain

Dear all,
Regarding the aforementioned question I don't know if launching one script
in VB
and retrieving all the names is compulsory.
Is there any way or method for obtain that info via job/sp?
I hope have been sufficiently explicit.
Thanks in advance and regards,Hi,
Try the below command from command prompt.
OSQL -L
Thanks
Hari
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:85E1BA68-3430-4807-BCC0-FD77AB8698A9@.microsoft.com...
> Dear all,
> Regarding the aforementioned question I don't know if launching one script
> in VB
> and retrieving all the names is compulsory.
> Is there any way or method for obtain that info via job/sp?
> I hope have been sufficiently explicit.
> Thanks in advance and regards,|||thanks a lot Hari
"Hari Prasad" wrote:

> Hi,
> Try the below command from command prompt.
> OSQL -L
> Thanks
> Hari
> SQL Server MVP
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:85E1BA68-3430-4807-BCC0-FD77AB8698A9@.microsoft.com...
>
>sql

How can I migrate Sql Server 2000 to Sql Server 2005 ?

Dear everyone,

My company is currently using MS Sql Server 2000 for applications, and in Windows Server 2000. The database holds great amount of information.
Recently, my company needs to migrate database from Sql Server 2000 to 2005. How could I safely migrate the database from sql server 2000 to 2005? Could you all give me the guides?

Also, in what aspect I should pay attention on the performance of sql server 2005?

Thank you for your kind attention.

Best Regards,
David

Hi,

You can find lot of information about the upgrade at here: http://www.microsoft.com/sql/solutions/upgrade/default.mspx

I highly recommend to use the upgrade advisor, it helped me a lot.

Regards,

Janos

|||

you have the following options to migrate to sql 2005,

1.Inplace upgrade > it will overwrite the current sql 2000 instance and a new sql 2005 instance will be created, but as replied by previous member you need to run the upgrade advisor to ensure that everything is fine with your db b4 upgrading

2.Side by Side upgrade > it is the most conventional method, you can use any of these below methods ......i prefer backup and restore

backup your SQL 2000 db and restore it in sql 2005 detach your sql 2000 db and attach it in sql 2005 use copy database wizard to move your sql 2k dbs to sql 2k5

How can I know the "SQL Server" Service is down ?

Dear,
How can I make a SQL Server able to send me an email alert if its SQL Server
Service and/or SQL Server Agent Service is down automatically ?
SQL 2005 Database Mail and SQL 2000 SQL Mail can't do that because they can
monitor themselves.
Are there any other way to monitor them ? Either from Microsoft Windows
system or third party softwares.The simplest solution is to configure MOM (assuming you have deployed it).
Otherwise you need to do this in Windows. In the Computer Management -
Services node, display the properties pages of the services of interest and
configure the recovery page.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:46ff47de$1@.127.0.0.1...
> Dear,
> How can I make a SQL Server able to send me an email alert if its SQL
> Server
> Service and/or SQL Server Agent Service is down automatically ?
> SQL 2005 Database Mail and SQL 2000 SQL Mail can't do that because they
> can
> monitor themselves.
> Are there any other way to monitor them ? Either from Microsoft Windows
> system or third party softwares.
>|||Dear Yudkin,
What does MOM stand for ?
Please tell me, thanks a lot.
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:OUbFU7zAIHA.5752@.TK2MSFTNGP02.phx.gbl...
> The simplest solution is to configure MOM (assuming you have deployed it).
> Otherwise you need to do this in Windows. In the Computer Management -
> Services node, display the properties pages of the services of interest
and
> configure the recovery page.
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:46ff47de$1@.127.0.0.1...
> > Dear,
> >
> > How can I make a SQL Server able to send me an email alert if its SQL
> > Server
> > Service and/or SQL Server Agent Service is down automatically ?
> > SQL 2005 Database Mail and SQL 2000 SQL Mail can't do that because they
> > can
> > monitor themselves.
> >
> > Are there any other way to monitor them ? Either from Microsoft Windows
> > system or third party softwares.
> >
> >
>|||> What does MOM stand for ?
Microsoft Operations Manager. See
http://technet.microsoft.com/en-us/opsmgr/bb498244.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:46ffa2bc$1@.127.0.0.1...
> Dear Yudkin,
> What does MOM stand for ?
> Please tell me, thanks a lot.
> "Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
> news:OUbFU7zAIHA.5752@.TK2MSFTNGP02.phx.gbl...
>> The simplest solution is to configure MOM (assuming you have deployed
>> it).
>> Otherwise you need to do this in Windows. In the Computer Management -
>> Services node, display the properties pages of the services of interest
> and
>> configure the recovery page.
>> "cpchan" <cpchaney@.netvigator.com> wrote in message
>> news:46ff47de$1@.127.0.0.1...
>> > Dear,
>> >
>> > How can I make a SQL Server able to send me an email alert if its SQL
>> > Server
>> > Service and/or SQL Server Agent Service is down automatically ?
>> > SQL 2005 Database Mail and SQL 2000 SQL Mail can't do that because they
>> > can
>> > monitor themselves.
>> >
>> > Are there any other way to monitor them ? Either from Microsoft Windows
>> > system or third party softwares.
>> >
>> >
>>
>|||Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Od9MKe2AIHA.288@.TK2MSFTNGP02.phx.gbl...
> > What does MOM stand for ?
> Microsoft Operations Manager. See
> http://technet.microsoft.com/en-us/opsmgr/bb498244.aspx
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:46ffa2bc$1@.127.0.0.1...
> > Dear Yudkin,
> >
> > What does MOM stand for ?
> > Please tell me, thanks a lot.
> >
> > "Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
> > news:OUbFU7zAIHA.5752@.TK2MSFTNGP02.phx.gbl...
> >> The simplest solution is to configure MOM (assuming you have deployed
> >> it).
> >>
> >> Otherwise you need to do this in Windows. In the Computer Management -
> >> Services node, display the properties pages of the services of interest
> > and
> >> configure the recovery page.
> >>
> >> "cpchan" <cpchaney@.netvigator.com> wrote in message
> >> news:46ff47de$1@.127.0.0.1...
> >> > Dear,
> >> >
> >> > How can I make a SQL Server able to send me an email alert if its SQL
> >> > Server
> >> > Service and/or SQL Server Agent Service is down automatically ?
> >> > SQL 2005 Database Mail and SQL 2000 SQL Mail can't do that because
they
> >> > can
> >> > monitor themselves.
> >> >
> >> > Are there any other way to monitor them ? Either from Microsoft
Windows
> >> > system or third party softwares.
> >> >
> >> >
> >>
> >>
> >
> >
>|||Or (the poor man's approach) you can also write a WMI script using VBScript
that monitors the SQL Server service and sends an email when it goes down.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:46ffb47d$1@.127.0.0.1...
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Od9MKe2AIHA.288@.TK2MSFTNGP02.phx.gbl...
>> > What does MOM stand for ?
>> Microsoft Operations Manager. See
>> http://technet.microsoft.com/en-us/opsmgr/bb498244.aspx
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "cpchan" <cpchaney@.netvigator.com> wrote in message
>> news:46ffa2bc$1@.127.0.0.1...
>> > Dear Yudkin,
>> >
>> > What does MOM stand for ?
>> > Please tell me, thanks a lot.
>> >
>> > "Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
>> > news:OUbFU7zAIHA.5752@.TK2MSFTNGP02.phx.gbl...
>> >> The simplest solution is to configure MOM (assuming you have deployed
>> >> it).
>> >>
>> >> Otherwise you need to do this in Windows. In the Computer Management -
>> >> Services node, display the properties pages of the services of
>> >> interest
>> > and
>> >> configure the recovery page.
>> >>
>> >> "cpchan" <cpchaney@.netvigator.com> wrote in message
>> >> news:46ff47de$1@.127.0.0.1...
>> >> > Dear,
>> >> >
>> >> > How can I make a SQL Server able to send me an email alert if its
>> >> > SQL
>> >> > Server
>> >> > Service and/or SQL Server Agent Service is down automatically ?
>> >> > SQL 2005 Database Mail and SQL 2000 SQL Mail can't do that because
> they
>> >> > can
>> >> > monitor themselves.
>> >> >
>> >> > Are there any other way to monitor them ? Either from Microsoft
> Windows
>> >> > system or third party softwares.
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>

How can I kill sleeping processes in SQL Server?

Dear,

Our ASP.NET scripts send SQL statements (as inline SQL or SP) to process the requested job. After the job execution, the process ID stays in the server and waits for next command with sleeping status.Since this process does not go away, next job adds another process and eventually, the server is overloaded with these processes and dies.

How can I kill this sleeping processes?

Regards,

Echo

Off the top of my head if you

EXECsp_who

it will give you the SPID's for all your processes. Armed with that you could then issue  
KILL 42
which will terminate SPID 42
So I would guess that you could set up a job in SQL Server to get a list of all offending SPID's, schedule it to run every hour for arguments sake, and then step through that list issuing the KILL command.
 

HTH|||

Please do take care when using KILL to kill SQL sessions.SPID<=50 are SQL system processes, so you'd better not kill such processes. Always confirm that the session is no longer useful (has been idle for a long time or invovled in blocking/deadlock) before you KILL it. For more information about the KILL command in T-SQL, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp

|||

Iori_Jay:

Please do take care when using KILL to kill SQL sessions

I very much agree with Iori_Jay, and really should have advised caution in my earlier post.

|||

For example, you can use such query to Kill SQL sessions which are sleeping and have been idle more than 1 hour:

DECLARE @.v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND status='sleeping'
AND DATEDIFF(mi,last_batch,GETDATE())>=60
AND spid<>@.@.spid

OPEN c_Users
FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS=0)
BEGIN
PRINT 'KILLing '+CONVERT(VARCHAR,@.v_spid)+'...'
EXEC('KILL'+@.v_spid)
FETCH NEXT FROM c_Users INTO @.v_spid
END

CLOSE c_Users
DEALLOCATE c_Users

|||

Dear all,

Thank U everybody for replying. But I do not want to run any process at SQL server. Can't I manage it from ADO.NET. Is it possible that after executing a process it will automatically die.

I think the problem may related to connection pooling. Can I stop connection pooling of ADO.NET programatically?

Regards,

Sultan

|||

Why do you want to stop connection pooling...Connection Pooling can improve performance for database connections. If you just want to prevent unexpected remaining connecitons, you can just set the connection timeout when you create the connection in your code.

You can also take a look at this article:

Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

How can I insert a new field between existing field?

Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AUHi,
With out dropping and recreating the table you can not insert a column in be
tween 2 existing columns.
Actually enterprise manager internally does below events while inserting a n
ew field...
1. Pull data out
2. Generate script of table and dependants
3. Drop and recreate the table with new structure
4. Load the data
So while you do a schema change on huge tables; it will result in longer ex
ecution time...
Thanks
Hari
SQL Server MVP
"Alex AU" <acawh@.msn.com> wrote in message news:5003A40C-1603-4C8F-B591-DFFF
69DFC567@.microsoft.com...
Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AU|||The actual field order 'should' not matter. If you add a new column and it i
s at the 'end of the list', you can always retrieve the list of columns as y
ou want -with the new column in the 'middle'. Of course, that gets in the wa
y of using 'SELECT *' -but you shouldn't be doing that anyway!
(There some considerations that SQL Server will take in regard to field/data
type placement in the row on the datapage but that should not interfere with
your getting the data in the fashion you desire.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Alex AU" <acawh@.msn.com> wrote in message news:5003A40C-1603-4C8F-B591-DFFF
69DFC567@.microsoft.com...
Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AU|||Thanks Hari,
If I want to mimic what Enterprise Manager do, what will be the right way?
Can I do as follows:-
- Create a temp table with the new fields inserted
- insert the data from old table to temp table
- drop the old table
- Create the table with the new structure
- insert the data back from temp table
- drop the temp table
Can I rename the temp table to the original table name to replace the last 3
steps?
Thanks a lot.
Regards,
Alex AU
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:uweZCACvGHA.
2260@.TK2MSFTNGP03.phx.gbl...
Hi,
With out dropping and recreating the table you can not insert a column in be
tween 2 existing columns.
Actually enterprise manager internally does below events while inserting a n
ew field...
1. Pull data out
2. Generate script of table and dependants
3. Drop and recreate the table with new structure
4. Load the data
So while you do a schema change on huge tables; it will result in longer ex
ecution time...
Thanks
Hari
SQL Server MVP
"Alex AU" <acawh@.msn.com> wrote in message news:5003A40C-1603-4C8F-B591-DFFF
69DFC567@.microsoft.com...
Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AU|||Alex AU wrote:
> Dear all,
> Is there any way can I insert a new field between existing fields
> through TSQL or other means, without using Enterprise Manager?
> Thanks a lot.
>
> Regards,
> Alex AU
Field order is irrelevant. Given two tables:
TableA: RowID, Value, Description, UpdateDate
TableB: RowID, Value, UpdateDate, Description
The field order means nothing, because your queries should always
specify a field list:
SELECT Value, Description, UpdateDate
FROM TableA
WHERE RowID = 1
SELECT Value, Description, UpdateDate
FROM TableB
WHERE RowID = 1
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Unless you're really lucky, it's far more complicated that than. You'll have
to take account of all constraints - Defaults, FKs etc and all indexes. The
se need to be dropped from the existing table and later readded to the tmp t
able. Also keep an eye out for any views created using select * from tablena
me, as they'll need to be refreshed afterwards. Finally, I'd also question w
hether it's worth it, when there's such little gain. Client apps should acce
ss columns by name rather than position, so typically the main gain is just
neatness for the DBA.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Paul Ibison SQL Server MVP said:
> "Client apps should access columns by name rather than position, so
> typically the main gain is just neatness for the DBA."
...Unless you're trying to do something completely off-the-wall and "crazy"
like writing a custom application to Bulk Load data into your table in which
case you have to specify columns by ordinal position...|||Then why not create a VIEW with the fields in the necessary order, and bulk
load the VIEW?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mike C#" <xyz@.xyz.com> wrote in message
news:4X7Dg.6916$5L4.3133@.newsfe10.lga...
> Paul Ibison SQL Server MVP said:
> ...Unless you're trying to do something completely off-the-wall and
> "crazy" like writing a custom application to Bulk Load data into your
> table in which case you have to specify columns by ordinal position...
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OiQESADwGHA.4460@.TK2MSFTNGP05.phx.gbl...
> Then why not create a VIEW with the fields in the necessary order, and
> bulk load the VIEW?
As long as the columns in the view are in the correct ordinal positions...
Table or view, bulk operations API's reference columns by ordinal position.

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

|||

I have the opposite problem. When using SSMS on the server itself, I cannot see the system databases tree at all. I tet only "Database Snapshots" and my own database.

However, when accessing this server over a VPN connection, I can see and access the system databases.

I am a Sysadmin and would expect to be able to see everything.

And since the server is running Windows Authentication only, I would expect that my credential are the same, no matter where I login from.

My first guess is that I inadvertantly hid the System Database tree in SSMS, but I see no way to do/undo anything remotely like that.

Per your comment, I remain confused as to how to make them visible. Note that the databases are listed in various dialogs where one would normally select a database (Maint Wizard, for example).

"You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu"

|||

Can you see the database entries when you query sys.databases? If the catalogs show you the correct information, but SSMS does not, then the issue should be pursued on the Tools forum.

Thanks

Laurentiu

|||

Yes, I can see master, tempdb, model and msdb via "select * from sys.databases".

Will re-post in the Tools forum, thanks.

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.nameas grantee_name, p.state_desc, p.permission_name
FROMsys.server_permissionsAS p JOINsys.server_principalsAS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name ='VIEW ANY DATABASE';

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

|||

I have the opposite problem. When using SSMS on the server itself, I cannot see the system databases tree at all. I tet only "Database Snapshots" and my own database.

However, when accessing this server over a VPN connection, I can see and access the system databases.

I am a Sysadmin and would expect to be able to see everything.

And since the server is running Windows Authentication only, I would expect that my credential are the same, no matter where I login from.

My first guess is that I inadvertantly hid the System Database tree in SSMS, but I see no way to do/undo anything remotely like that.

Per your comment, I remain confused as to how to make them visible. Note that the databases are listed in various dialogs where one would normally select a database (Maint Wizard, for example).

"You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu"

|||

Can you see the database entries when you query sys.databases? If the catalogs show you the correct information, but SSMS does not, then the issue should be pursued on the Tools forum.

Thanks

Laurentiu

|||

Yes, I can see master, tempdb, model and msdb via "select * from sys.databases".

Will re-post in the Tools forum, thanks.

How can I handle OnError event in my stored proc.

Dear All:
I want to ask how can I handle OnError events in stored procedure in MSSQL.
Actually I wanted to place some Rollback procedure on this.
Can you suggest some methods for me?
KEVINfrom BOL

C. Use @.@.ERROR to check the success of several statements
This example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @.@.ERROR after both statements and are used in a shared error-handling routine for the operation.

USE pubs
GO
DECLARE @.del_error int, @.ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for
-- the DELETE statement.
SELECT @.del_error = @.@.ERROR

-- Execute the INSERT statement.
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @.ins_error = @.@.ERROR

-- Test the error values.
IF @.del_error = 0 AND @.ins_error = 0
BEGIN
-- Success. Commit the transaction.
PRINT "The author information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
IF @.del_error <> 0
PRINT "An error occurred during execution of the DELETE
statement."

IF @.ins_error <> 0
PRINT "An error occurred during execution of the INSERT
statement."

ROLLBACK TRAN
END
GO