Adding a not null column to replicated table

Hi,

I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:

ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)

which executed OK. When I went to synchronize the db on the mobile device I got the following error:

Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]

Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?

Regards,

Greg

I doubt this is a replication problem, but I don't have a SQL Compact db handy, can you try to create a new table on the compact db and run the TSQL command to see if SQL Compact even allows this?|||

Hi Greg,

Thanks for your reply. I created a new database on the device (not replicated), created a table "Activity", added some records and then executed the same script above in Query Analyzer and it worked fine. It only seems to be a problem when the command is run by the replication agent. Any ideas?

Regards,

Greg

|||can you tell me what version of ce you're using?|||

Hi,

I'm running SQL Server Compact (3.1) on Windows Mobile 5.0 (HP iPaq), and the server is SQL Server 2005 SP2.

Regards,

Greg

|||

Some extra information: I tried to get the subscriber to synchronize by reinitializing all subscriptions (with a new snapshot and "upload unsynchronized changes" set to yes). I had thought that this would regenerate the database at the subscriber after uploading subscriber changes, but it still comes up with the original error message. It's trying to add the not null column but seems to ignore the default constraint.

Any help on this would be greatly appreciated. At this stage I'm not really able to make any db schema changes on the server without deleting subscriber dbs and starting from scratch.

Regards,

Greg

|||We're trying to track down some answers for you, so please be patient.|||

This is a confirmed bug which we're trying to nail the root cause, thanks for raising this issue.

To get around your problem, you should do the following:

1. Reinitialize your subscriptions

2. Generate a new snapshot

3. Rerun your sync agents to apply the new snapshot

If you do this in order, you shouldn't hit the error because the new snapshot should generate a table with the new column. let me know if this still doesn't work.

|||

Thanks Greg,

As per my previous post, the main issue is that this problem is not resolved by reinitializing subscriptions with a new snapshot. I still get the error. The only solution I've found so far is to delete the subscriber database and synchronize again.

Regards,

Greg

|||

Hi,

This problem was seriously affecting the development and testing of this application, so I opened a support incident with Microsoft. The tech advised that I should use the stored procedure sp_repladdcolumn to add the column. It worked! Despite the documentation saying that this method is deprecated and should only be used with SQL Server 2000, it seems that this is only way to add a not null column with a default value to a SQL Server 2005 db table and have it successfully replicate to SQL Mobile/Compact (if anyone has any alternatives to this, please advise). This also seems to be a solution to another problem I've had with "could not enumerate changes at the subscriber" error when synchronizing after changing the db schema. Using the old stored procedures instead of "alter table" commands seems to fix this.

I hope MS will add a KB article about this so others won't have spend as much time as I have trying to get an answer.

Regards,

Greg

|||It's a bug in CE, not replication. Earlier I asked if you could run the alter table statement on your CE device to see if that failed, you said it passed but I think you ran it on your SQL Server 2005 server and not the CE. Running the alter table statement explicitly on the CE device will fail, if you remove the default name, then it works - CE should not choke like this. I'm glad you found a solution, a bug was already file, CE folks will be fixing this hopefully soon.

Adding a new field to an existing table with image field

Hi,
I have a concern about adding a new field to an existing table with image
field - which is huge.
Will there be a problem with some databases, where they have a hard time
locating data correctly after such a large field? I was advised to put all
the big fields at the end of the table.
Thanks> I was advised to put all
> the big fields at the end of the table.
I don't know why you were given this advice. The position of columns within
a row does not affect performance.
Unless you specify the 'test in row' option, a row contains only a pointer
to a separate area containing the image.
Hope this helps.
Dan Guzman
SQL Server MVP
"eeyore21" <u25822@.uwe> wrote in message news:6566a618c09ff@.uwe...
> Hi,
> I have a concern about adding a new field to an existing table with image
> field - which is huge.
> Will there be a problem with some databases, where they have a hard time
> locating data correctly after such a large field? I was advised to put
> all
> the big fields at the end of the table.
> Thanks
>|||Thanks. I was given this advice as this problem seem to occur before.
Hmm.. what is this "test in row" option?
Dan Guzman wrote:[vbcol=seagreen]
>I don't know why you were given this advice. The position of columns withi
n
>a row does not affect performance.
>Unless you specify the 'test in row' option, a row contains only a pointer
>to a separate area containing the image.
>
>[quoted text clipped - 7 lines]|||> Thanks. I was given this advice as this problem seem to occur before.
I would ask the person who gave you this advise the underlying rationale.

> Hmm.. what is this "test in row" option?
That's a typo :-) I meant to say 'text in row'.
With the 'text in row' table option, values smaller than the specified size
are stored in the data row itself rather than in separate text/image pages.
This can help performance when your images are small enough to fit on the
data page and are most often selected along with the other data. I doubt
this appropriate in your case since you mentioned that the values are large.
See the Books Online for a complete description of the 'text in row' table
option.
Hope this helps.
Dan Guzman
SQL Server MVP
"eeyore21" <u25822@.uwe> wrote in message news:6566ff6f0d4fc@.uwe...
> Thanks. I was given this advice as this problem seem to occur before.
> Hmm.. what is this "test in row" option?
> Dan Guzman wrote:
>|||The concern to put all big fields at the end of the table wasn't about
performance - but rather the pointers in the db getting 'lost' when they hav
e
to find a column's data that was located after a very, very big column.
Even though i not sure if this really happens
Dan Guzman wrote:[vbcol=seagreen]
>I would ask the person who gave you this advise the underlying rationale.
>
>That's a typo :-) I meant to say 'text in row'.
>With the 'text in row' table option, values smaller than the specified size
>are stored in the data row itself rather than in separate text/image pages.
>This can help performance when your images are small enough to fit on the
>data page and are most often selected along with the other data. I doubt
>this appropriate in your case since you mentioned that the values are large
.
>See the Books Online for a complete description of the 'text in row' table
>option.
>
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||> Even though i not sure if this really happens
Perhaps the person experienced some sort of corruption and attributed the
cause to the position of large columns. As far as I know, this concern is
not warranted. There is no reason to place large columns (text or
otherwise) at the end of the row.
Hope this helps.
Dan Guzman
SQL Server MVP
"eeyore21 via droptable.com" <u25822@.uwe> wrote in message
news:6578e9948dd22@.uwe...
> The concern to put all big fields at the end of the table wasn't about
> performance - but rather the pointers in the db getting 'lost' when they
> have
> to find a column's data that was located after a very, very big column.
> Even though i not sure if this really happens
>
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>|||eeyore21 via droptable.com wrote:
> The concern to put all big fields at the end of the table wasn't about
> performance - but rather the pointers in the db getting 'lost' when they h
ave
> to find a column's data that was located after a very, very big column.
> Even though i not sure if this really happens
>
Sounds like old advice from a dBase or Foxpro person... Doesn't apply
to SQL Server...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Adding a new field to an existing table with image field

Hi,
I have a concern about adding a new field to an existing table with image
field - which is huge.
Will there be a problem with some databases, where they have a hard time
locating data correctly after such a large field? I was advised to put all
the big fields at the end of the table.
Thanks> I was advised to put all
> the big fields at the end of the table.
I don't know why you were given this advice. The position of columns within
a row does not affect performance.
Unless you specify the 'test in row' option, a row contains only a pointer
to a separate area containing the image.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eeyore21" <u25822@.uwe> wrote in message news:6566a618c09ff@.uwe...
> Hi,
> I have a concern about adding a new field to an existing table with image
> field - which is huge.
> Will there be a problem with some databases, where they have a hard time
> locating data correctly after such a large field? I was advised to put
> all
> the big fields at the end of the table.
> Thanks
>|||Thanks. I was given this advice as this problem seem to occur before.
Hmm.. what is this "test in row" option?
Dan Guzman wrote:
>> I was advised to put all
>> the big fields at the end of the table.
>I don't know why you were given this advice. The position of columns within
>a row does not affect performance.
>Unless you specify the 'test in row' option, a row contains only a pointer
>to a separate area containing the image.
>> Hi,
>[quoted text clipped - 7 lines]
>> Thanks|||> Thanks. I was given this advice as this problem seem to occur before.
I would ask the person who gave you this advise the underlying rationale.
> Hmm.. what is this "test in row" option?
That's a typo :-) I meant to say 'text in row'.
With the 'text in row' table option, values smaller than the specified size
are stored in the data row itself rather than in separate text/image pages.
This can help performance when your images are small enough to fit on the
data page and are most often selected along with the other data. I doubt
this appropriate in your case since you mentioned that the values are large.
See the Books Online for a complete description of the 'text in row' table
option.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eeyore21" <u25822@.uwe> wrote in message news:6566ff6f0d4fc@.uwe...
> Thanks. I was given this advice as this problem seem to occur before.
> Hmm.. what is this "test in row" option?
> Dan Guzman wrote:
>> I was advised to put all
>> the big fields at the end of the table.
>>I don't know why you were given this advice. The position of columns
>>within
>>a row does not affect performance.
>>Unless you specify the 'test in row' option, a row contains only a pointer
>>to a separate area containing the image.
>> Hi,
>>[quoted text clipped - 7 lines]
>> Thanks
>|||The concern to put all big fields at the end of the table wasn't about
performance - but rather the pointers in the db getting 'lost' when they have
to find a column's data that was located after a very, very big column.
Even though i not sure if this really happens
Dan Guzman wrote:
>> Thanks. I was given this advice as this problem seem to occur before.
>I would ask the person who gave you this advise the underlying rationale.
>> Hmm.. what is this "test in row" option?
>That's a typo :-) I meant to say 'text in row'.
>With the 'text in row' table option, values smaller than the specified size
>are stored in the data row itself rather than in separate text/image pages.
>This can help performance when your images are small enough to fit on the
>data page and are most often selected along with the other data. I doubt
>this appropriate in your case since you mentioned that the values are large.
>See the Books Online for a complete description of the 'text in row' table
>option.
>> Thanks. I was given this advice as this problem seem to occur before.
>[quoted text clipped - 15 lines]
>> Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||> Even though i not sure if this really happens
Perhaps the person experienced some sort of corruption and attributed the
cause to the position of large columns. As far as I know, this concern is
not warranted. There is no reason to place large columns (text or
otherwise) at the end of the row.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eeyore21 via SQLMonster.com" <u25822@.uwe> wrote in message
news:6578e9948dd22@.uwe...
> The concern to put all big fields at the end of the table wasn't about
> performance - but rather the pointers in the db getting 'lost' when they
> have
> to find a column's data that was located after a very, very big column.
> Even though i not sure if this really happens
>
> Dan Guzman wrote:
>> Thanks. I was given this advice as this problem seem to occur before.
>>I would ask the person who gave you this advise the underlying rationale.
>> Hmm.. what is this "test in row" option?
>>That's a typo :-) I meant to say 'text in row'.
>>With the 'text in row' table option, values smaller than the specified
>>size
>>are stored in the data row itself rather than in separate text/image
>>pages.
>>This can help performance when your images are small enough to fit on the
>>data page and are most often selected along with the other data. I doubt
>>this appropriate in your case since you mentioned that the values are
>>large.
>>See the Books Online for a complete description of the 'text in row' table
>>option.
>> Thanks. I was given this advice as this problem seem to occur before.
>>[quoted text clipped - 15 lines]
>> Thanks
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||eeyore21 via SQLMonster.com wrote:
> The concern to put all big fields at the end of the table wasn't about
> performance - but rather the pointers in the db getting 'lost' when they have
> to find a column's data that was located after a very, very big column.
> Even though i not sure if this really happens
>
Sounds like old advice from a dBase or Foxpro person... Doesn't apply
to SQL Server...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Adding a new field to a table with image field

Hi,

I have a concern about adding a new field to a table with image field - which is huge.

Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?

Previously this happened to me, and what was advised to put all the big fields at the end of the table.

Thanks.

I have not heard of such impact caused by the order of columns. When SQL search table to locate a row, it either uses table scan (when no index exists on the table) from the IAM page, or utilize indexes (clusetered/nonclustered) if there is any on the table. To learn more about table structuer, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp

I recommend you to read <Programming a Microsoft® SQL Server™ 2000 Database> if you're interesting in this, which includes a topic "How SQL Server Retrieves Stored Data".

Adding a column to a table dynamically against a database?

I have the folowing databases DB1,DB2,DB3,D4,DB5......

I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on.....)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.

can someone show me the script to do it?

Thanks.

This script should do the trick, the specific line that does the ALTER TABLE is commented you would have to remove the comment. Additionaly, I also added a commented line that lets you specify which databases to search, if this line is documented it will search all databases except system databases. IMPORTANT: The script is written for SQL 2005, if you need it to work in 2000 just reply on the thread the change should be fairly simple.

 -------------------------------------------

DECLARE ALL_DATABASESCURSORREAD_ONLYFOR SELECT nameFROM SYS.DATABASESWHERE name NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION')
--ANDname IN ('DB1','DB2','DB3') -- UNCOMMENT THIS LINE TO SPECIFY SPECIFIC DATABASES OTHERWISE ALLDECLARE @.DB_NAMENVARCHAR(255)DECLARE @.TABLE_NAMENVARCHAR(255)-- OTHERDECLARE @.SQL1NVARCHAR(MAX)DECLARE @.SQL2NVARCHAR(MAX)-- TEMPORARY TABLE TO HOLD ALL TABLES, FOR A DATABASEDECLARE @.TEMPORARY_SYS_TABLESTABLE (TABLE_NAMENVARCHAR(255))OPEN ALL_DATABASESFETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- OUTPUT DATABASE NAMEPRINT'BEG------ (' + @.DB_NAME + ') --------'-- BUILD QUERY FOR TABLESSET @.SQL1 = 'SELECT name FROM ['+ @.DB_NAME +'].[SYS].[TABLES]WHERE NAMELIKE''%DOCUMENTS%'''-- TRUNCATEDELETE @.TEMPORARY_SYS_TABLES-- LOAD WITH DATAINSERT @.TEMPORARY_SYS_TABLES(TABLE_NAME)EXECUTE SP_EXECUTESQL @.SQL1DECLARE ALL_MMR_TABLES CURSORREAD_ONLYFOR SELECT TABLE_NAMEFROM @.TEMPORARY_SYS_TABLESOPEN ALL_MMR_TABLESFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- BUILD SQLSET @.SQL2 = 'USE' + @.DB_NAME + ';GO;ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'-- PRINTPRINT @.SQL2-- EXECUTE-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMNENDFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEENDCLOSE ALL_MMR_TABLESDEALLOCATE ALL_MMR_TABLES-- OUTPUT DATABASE NAMEPRINT 'END------ (' + @.DB_NAME + ') --------'FETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEENDENDCLOSE ALL_DATABASESDEALLOCATE ALL_DATABASES

-------------------------------------------

Hope this helps,

|||

Thanks for replying.

I am using SQL Server 2000.

I tried executing the statement as per your script above, in master database

SET @.SQL2 = 'USE' + @.DB_NAME + ';GO;ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'
PRINT @.SQL2
EXECUTE SP_EXECUTESQL @.SQL2

and it is throwing this error:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'GO'.

|||

Apparently all that is required is that you remove the GO. The documentation for the Stored Procedure SP_EXECUTESQL says it can only operate within a single BATCH, I guess that is why GO is not allowed.

SET @.SQL2 = 'USE' + @.DB_NAME + ';ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'

Full Script for SQL 2000, and you should definitely run from 'master' database.

___________________________________________________________________________________________

DECLARE ALL_DATABASESCURSORREAD_ONLYFOR SELECT nameFROM MASTER.DBO.SYSDATABASES--WHERE name NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION')--AND name IN ('DB1','DB2','DB3') -- UNCOMMENT THIS LINE TO SPECIFY SPECIFIC DATABASES OTHERWISE ALLDECLARE @.DB_NAMENVARCHAR(255)DECLARE @.TABLE_NAMENVARCHAR(255)-- OTHERDECLARE @.SQL1NVARCHAR(MAX)DECLARE @.SQL2NVARCHAR(MAX)-- TEMPORARY TABLE TO HOLD ALL TABLES, FOR A DATABASEDECLARE @.TEMPORARY_SYS_TABLESTABLE (TABLE_NAMENVARCHAR(255))OPEN ALL_DATABASESFETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- OUTPUT DATABASE NAMEPRINT'BEG------ (' + @.DB_NAME + ') --------'-- BUILD QUERY FOR TABLESSET @.SQL1 = 'SELECT TABLE_NAMEFROM ['+ @.DB_NAME +'].[INFORMATION_SCHEMA].[TABLES]WHERE TABLE_NAMELIKE''%DOCUMENTS%'''-- TRUNCATEDELETE @.TEMPORARY_SYS_TABLES-- LOAD WITH DATAINSERT @.TEMPORARY_SYS_TABLES(TABLE_NAME)EXECUTE SP_EXECUTESQL @.SQL1DECLARE ALL_MMR_TABLES CURSORREAD_ONLYFOR SELECT TABLE_NAMEFROM @.TEMPORARY_SYS_TABLESOPEN ALL_MMR_TABLESFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- BUILD SQLSET @.SQL2 = 'USE' + @.DB_NAME + ';ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'-- PRINTPRINT @.SQL2-- EXECUTE-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMNENDFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEENDCLOSE ALL_MMR_TABLESDEALLOCATE ALL_MMR_TABLES-- OUTPUT DATABASE NAMEPRINT 'END------ (' + @.DB_NAME + ') --------'FETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEENDENDCLOSE ALL_DATABASESDEALLOCATE ALL_DATABASES-------------------------------------------

Hope this helps,

|||

Although the script complies successfully, changes do not take place in the desried table.

Any clue?

|||

Just remember to remove the comment from the following line of code.

-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMN

It should look like this.

EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMN

|||

Thanks!

sql

Add users to logshipping database on secondary server

Hi Folks,
I need to add new read only users to our databases on seconday server of
logshipping.
As all those db's are readonly, How can I add users to it?
It is on sql 2000 sp3
Please help.
Kumar> As all those db's are readonly, How can I add users to it?
You can't. You can't do anything inside a log shipped or mirror database. So this has to be
performed in the source database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:61086CE0-9F44-495B-AEBC-F8D8E9BBCD48@.microsoft.com...
> Hi Folks,
> I need to add new read only users to our databases on seconday server of
> logshipping.
> As all those db's are readonly, How can I add users to it?
> It is on sql 2000 sp3
> Please help.
> Kumar

Add users to logshipping database on secondary server

Hi Folks,
I need to add new read only users to our databases on seconday server of
logshipping.
As all those db's are readonly, How can I add users to it?
It is on sql 2000 sp3
Please help.
Kumar
> As all those db's are readonly, How can I add users to it?
You can't. You can't do anything inside a log shipped or mirror database. So this has to be
performed in the source database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:61086CE0-9F44-495B-AEBC-F8D8E9BBCD48@.microsoft.com...
> Hi Folks,
> I need to add new read only users to our databases on seconday server of
> logshipping.
> As all those db's are readonly, How can I add users to it?
> It is on sql 2000 sp3
> Please help.
> Kumar

Add users to logshipping database on secondary server

Hi Folks,
I need to add new read only users to our databases on seconday server of
logshipping.
As all those db's are readonly, How can I add users to it?
It is on sql 2000 sp3
Please help.
Kumar> As all those db's are readonly, How can I add users to it?
You can't. You can't do anything inside a log shipped or mirror database. So
this has to be
performed in the source database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:61086CE0-9F44-495B-AEBC-F8D8E9BBCD48@.microsoft.com...
> Hi Folks,
> I need to add new read only users to our databases on seconday server of
> logshipping.
> As all those db's are readonly, How can I add users to it?
> It is on sql 2000 sp3
> Please help.
> Kumar

add users in a transaction SQL Server 2000

I need to be able to add users to roles in serveral databases and if one of
the inserts fails I need to be able to roll back the whole transaction. I am
getting the following error:
The procedure 'sp_grantlogin' cannot be executed within a transaction.
The procedure 'sp_defaultdb' cannot be executed within a transaction.
The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
The procedure 'sp_addrolemember' cannot be executed within a transaction.You will have to use your own logic to make it RollbackAble:
"sp_grantlogin cannot be executed within a user-defined transaction."
http://msdn2.microsoft.com/en-us/library/ms173449.aspx
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"maddog" <maddog@.discussions.microsoft.com> wrote in message
news:2B94A051-19CC-41F1-96B6-AB51D1E5C3B3@.microsoft.com...
>I need to be able to add users to roles in serveral databases and if one of
> the inserts fails I need to be able to roll back the whole transaction. I
> am
> getting the following error:
> The procedure 'sp_grantlogin' cannot be executed within a transaction.
> The procedure 'sp_defaultdb' cannot be executed within a transaction.
> The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
> The procedure 'sp_addrolemember' cannot be executed within a transaction.

add new user with all rights to all dbases

Kind of new to Enterprize Manager, just wondering what the steps are to add
a
new user that will have all rights to all databases? Thanks.
--
Paul G
Software engineer."Paul" <Paul@.discussions.microsoft.com> wrote in message
news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
> Kind of new to Enterprize Manager, just wondering what the steps are to
> add a
> new user that will have all rights to all databases? Thanks.
> --
> Paul G
> Software engineer.
Add your new user normally. Either as a Windows Authenticated account or as
a SQL User account.
Add that new user to the sysadmins SQL Server group inside SQL Server.
Just a side note on security here. I hope that this is the user account
that you will be connecting with moving forward and not something you are
planning on giving to everyone who uses the database. ;-)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response. Actually I was trying to set up an account for a
database administrator that could administer all dbases. This would only be
used by one individual.
"Rick Sawtell" wrote:

> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
> Add your new user normally. Either as a Windows Authenticated account or
as
> a SQL User account.
> Add that new user to the sysadmins SQL Server group inside SQL Server.
>
> Just a side note on security here. I hope that this is the user account
> that you will be connecting with moving forward and not something you are
> planning on giving to everyone who uses the database. ;-)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||If it is OK to have sysadmins privileges for the person, just add a login fo
r the person, and make
it sysadmin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6401A53E-E9C0-41F9-9E08-C58B82148BD0@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Actually I was trying to set up an account for a
> database administrator that could administer all dbases. This would only
be
> used by one individual.
> "Rick Sawtell" wrote:
>|||ok thanks for the additional information.
"Tibor Karaszi" wrote:

> If it is OK to have sysadmins privileges for the person, just add a login
for the person, and make
> it sysadmin.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6401A53E-E9C0-41F9-9E08-C58B82148BD0@.microsoft.com...
>
>

add new user with all rights to all dbases

Kind of new to Enterprize Manager, just wondering what the steps are to add a
new user that will have all rights to all databases? Thanks.
Paul G
Software engineer.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
> Kind of new to Enterprize Manager, just wondering what the steps are to
> add a
> new user that will have all rights to all databases? Thanks.
> --
> Paul G
> Software engineer.
Add your new user normally. Either as a Windows Authenticated account or as
a SQL User account.
Add that new user to the sysadmins SQL Server group inside SQL Server.
Just a side note on security here. I hope that this is the user account
that you will be connecting with moving forward and not something you are
planning on giving to everyone who uses the database. ;-)
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks for the response. Actually I was trying to set up an account for a
database administrator that could administer all dbases. This would only be
used by one individual.
"Rick Sawtell" wrote:

> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
> Add your new user normally. Either as a Windows Authenticated account or as
> a SQL User account.
> Add that new user to the sysadmins SQL Server group inside SQL Server.
>
> Just a side note on security here. I hope that this is the user account
> that you will be connecting with moving forward and not something you are
> planning on giving to everyone who uses the database. ;-)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||If it is OK to have sysadmins privileges for the person, just add a login for the person, and make
it sysadmin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6401A53E-E9C0-41F9-9E08-C58B82148BD0@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Actually I was trying to set up an account for a
> database administrator that could administer all dbases. This would only be
> used by one individual.
> "Rick Sawtell" wrote:
|||ok thanks for the additional information.
"Tibor Karaszi" wrote:

> If it is OK to have sysadmins privileges for the person, just add a login for the person, and make
> it sysadmin.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6401A53E-E9C0-41F9-9E08-C58B82148BD0@.microsoft.com...
>
>

add new user with all rights to all dbases

Kind of new to Enterprize Manager, just wondering what the steps are to add a
new user that will have all rights to all databases? Thanks.
--
Paul G
Software engineer."Paul" <Paul@.discussions.microsoft.com> wrote in message
news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
> Kind of new to Enterprize Manager, just wondering what the steps are to
> add a
> new user that will have all rights to all databases? Thanks.
> --
> Paul G
> Software engineer.
Add your new user normally. Either as a Windows Authenticated account or as
a SQL User account.
Add that new user to the sysadmins SQL Server group inside SQL Server.
Just a side note on security here. I hope that this is the user account
that you will be connecting with moving forward and not something you are
planning on giving to everyone who uses the database. ;-)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response. Actually I was trying to set up an account for a
database administrator that could administer all dbases. This would only be
used by one individual.
"Rick Sawtell" wrote:
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
> > Kind of new to Enterprize Manager, just wondering what the steps are to
> > add a
> > new user that will have all rights to all databases? Thanks.
> > --
> > Paul G
> > Software engineer.
> Add your new user normally. Either as a Windows Authenticated account or as
> a SQL User account.
> Add that new user to the sysadmins SQL Server group inside SQL Server.
>
> Just a side note on security here. I hope that this is the user account
> that you will be connecting with moving forward and not something you are
> planning on giving to everyone who uses the database. ;-)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||If it is OK to have sysadmins privileges for the person, just add a login for the person, and make
it sysadmin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:6401A53E-E9C0-41F9-9E08-C58B82148BD0@.microsoft.com...
> Thanks for the response. Actually I was trying to set up an account for a
> database administrator that could administer all dbases. This would only be
> used by one individual.
> "Rick Sawtell" wrote:
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
>> > Kind of new to Enterprize Manager, just wondering what the steps are to
>> > add a
>> > new user that will have all rights to all databases? Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> Add your new user normally. Either as a Windows Authenticated account or as
>> a SQL User account.
>> Add that new user to the sysadmins SQL Server group inside SQL Server.
>>
>> Just a side note on security here. I hope that this is the user account
>> that you will be connecting with moving forward and not something you are
>> planning on giving to everyone who uses the database. ;-)
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>|||ok thanks for the additional information.
"Tibor Karaszi" wrote:
> If it is OK to have sysadmins privileges for the person, just add a login for the person, and make
> it sysadmin.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:6401A53E-E9C0-41F9-9E08-C58B82148BD0@.microsoft.com...
> > Thanks for the response. Actually I was trying to set up an account for a
> > database administrator that could administer all dbases. This would only be
> > used by one individual.
> >
> > "Rick Sawtell" wrote:
> >
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:FF651560-0FE5-4B24-B9C2-C66E227CD0FB@.microsoft.com...
> >> > Kind of new to Enterprize Manager, just wondering what the steps are to
> >> > add a
> >> > new user that will have all rights to all databases? Thanks.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >> Add your new user normally. Either as a Windows Authenticated account or as
> >> a SQL User account.
> >>
> >> Add that new user to the sysadmins SQL Server group inside SQL Server.
> >>
> >>
> >> Just a side note on security here. I hope that this is the user account
> >> that you will be connecting with moving forward and not something you are
> >> planning on giving to everyone who uses the database. ;-)
> >>
> >>
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >>
> >>
>
>

Add days to all datetime columns in the database

Hi,
I got a request from our Sr. Director regarding demo data.
He's stated that demo databases tend to age with time. As
you enter orders they become older and older as time
passes.
Anyways, he's asking how difficult would it be to write a
SQL utility that asks the user for a number of days and
then ran through the ENTIRE database looking for EVERY
Date/Time column and add the number of days to the data
content of the field.
I've sort of created already somewhat a DML command that
will autogenerate for me the update statement, but is
there a way for me to prompt the user in Query Analyzer
for a value to set the variable? Does it have to be done
via command prompt?
I've copied below my DML statement that I've quickly
written up.
select 'update ' + OBJECT_NAME(id) + ' set ' + substring
(name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
OBJECT_NAME(id)
from syscolumns
where name like '%Date%'
order by OBJECT_NAME(id)
TIA,
BettinaYou can make a template in Query Analyzer ...
You can insert tags like: <myTag, varchar(20), 'abc'> (Structure is:
<TagName, DataType, Default>)
Then just hit CTRL-SHIFT-M and it will prompt for replacement. Very handy.
You can also save your template in the <SQL Server
Root>\80\Tools\Templates\SQL Query Analyzer\ folder, as a .TQL file, and
when you click on the Templates tag in Query Analyzer it will be there for
you to use.
Or you could just make a stored procedure...
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>|||In the databases I see you will pick up lots of things that don't correspond
to an SQL datetime data type.
If you get this syscolumns.xtype contain they system type of the column.
syscolumns.xtype in (58, 61) will pick all datetime columns.
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>

Activity on a database?

Try seting up a trace, but just put in the connections,
then look at the databases connected to.
J

>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>
Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...[vbcol=seagreen]
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> databases and I want to
> kind of activity on
> used.
|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
>
|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
>

Activity on a database?

Hi everyone, I have an SQL2000 with quiet a lot databases and I want to
delete some of them. How do I know if there has been any kind of activity on
a certain database, so I dont delete one that is being used.
Thanks a lot,
Ivan MckenzieTry seting up a trace, but just put in the connections,
then look at the databases connected to.
J
>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>|||Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribió en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> >--Original Message--
> >Hi everyone, I have an SQL2000 with quiet a lot
> databases and I want to
> >delete some of them. How do I know if there has been any
> kind of activity on
> >a certain database, so I dont delete one that is being
> used.
> >
> >Thanks a lot,
> >
> >Ivan Mckenzie
> >
> >
> >.
> >|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
--
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
> >
> > Try seting up a trace, but just put in the connections,
> > then look at the databases connected to.
> >
> > J
> >
> >
> > >--Original Message--
> > >Hi everyone, I have an SQL2000 with quiet a lot
> > databases and I want to
> > >delete some of them. How do I know if there has been any
> > kind of activity on
> > >a certain database, so I dont delete one that is being
> > used.
> > >
> > >Thanks a lot,
> > >
> > >Ivan Mckenzie
> > >
> > >
> > >.
> > >
>|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribió en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > Thanks, I suppose this is done with SQL profiler.
> >
> >
> >
> > "Julie" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
> > >
> > > Try seting up a trace, but just put in the connections,
> > > then look at the databases connected to.
> > >
> > > J
> > >
> > >
> > > >--Original Message--
> > > >Hi everyone, I have an SQL2000 with quiet a lot
> > > databases and I want to
> > > >delete some of them. How do I know if there has been any
> > > kind of activity on
> > > >a certain database, so I dont delete one that is being
> > > used.
> > > >
> > > >Thanks a lot,
> > > >
> > > >Ivan Mckenzie
> > > >
> > > >
> > > >.
> > > >
> >
> >
>

Activity on a database?

Try seting up a trace, but just put in the connections,
then look at the databases connected to.
J

>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx
.gbl...[vbcol=seagreen]
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> databases and I want to
> kind of activity on
> used.|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx
.gbl...
>|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
>

Activity Monitor: SQL 2005

Is this supposed to work with SQL Server 2000 databases? I get the following
message when trying to launch it on one of these:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions.
(ActivityMonitor)
Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
For help, click:
http://go.microsoft.com/fwlink?ProdN...8&LinkId=20476
BUTTONS:
OK
It works fine when connected to 2005 servers.
Thanks,
TomT
Hi Tom,
Welcome to MSDN newsgroup.
Regarding on the launching Activity Monitor problem you mentioned, I think
it is the expected behavior since Activity Monitor is a SQL Server 2005
specific tool that targeting server /use activity monitoring. So for
sqlserver 2000 instances they're not supported to use this tool though many
common client components in sql 2005 managment studio are still available
and can be launched for sql 2000 instance. In addition, for sqlserver
2000, there exists other performance and activity monitoring tools:
# Monitoring Server Performance and Activity
http://msdn.microsoft.com/library/en...0mr.asp?frame=
true
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Activity Monitor: SQL 2005
| thread-index: AcYEtqv3CT7/oApERimnOFGHHQ7OsQ==
| X-WBNR-Posting-Host: 68.67.208.184
| From: "=?Utf-8?B?VG9tVA==?=" <tomt@.newsgroup.nospam>
| Subject: Activity Monitor: SQL 2005
| Date: Mon, 19 Dec 2005 08:10:04 -0800
| Lines: 36
| Message-ID: <35FAA28A-3693-4385-A3DF-CE3474AB6B85@.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.tools
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.tools:29191
| X-Tomcat-NG: microsoft.public.sqlserver.tools
|
| Is this supposed to work with SQL Server 2000 databases? I get the
following
| message when trying to launch it on one of these:
|
| TITLE: Microsoft SQL Server Management Studio
| --
|
| Cannot show requested dialog.
|
| --
| ADDITIONAL INFORMATION:
|
| Unable to execute requested command.
|
| --
|
| Unable to launch Activity Monitor. You may not have sufficient
permissions.
| (ActivityMonitor)
|
| --
|
| Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
|
| For help, click:
|
http://go.microsoft.com/fwlink?ProdN...dVer=08.00 .2
039&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
|
| --
| BUTTONS:
|
| OK
| --
|
| It works fine when connected to 2005 servers.
|
| Thanks,
|
| TomT
|
|||It work fine on my machine. Are you sysadmin on the 2000 machine? Perhaps this is the problem and AM
doesn't give a very helpful error message?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:35FAA28A-3693-4385-A3DF-CE3474AB6B85@.microsoft.com...
> Is this supposed to work with SQL Server 2000 databases? I get the following
> message when trying to launch it on one of these:
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Unable to execute requested command.
> --
> Unable to launch Activity Monitor. You may not have sufficient permissions.
> (ActivityMonitor)
> --
> Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...8&LinkId=20476
> --
> BUTTONS:
> OK
> --
> It works fine when connected to 2005 servers.
> Thanks,
> TomT
|||In SQL BOL it states "To view the Activity Monitor the user needs SELECT
permission to the sysprocesses and syslocks tables in the master database on
a SQL Server 2000 server. Permission to view these tables is granted by
default to the PUBLIC database role."
I use two different SQL Server 2000 servers, one I can use Activity Monitor
on the other I get the same error, my securty for both servers is identical.
The only difference I can find is that the server I cannot use Activity
Monitor on has SQL Reporting Services installed.
So TomT is the SQL Server 2000 server you are trying to use Activity Monitor
on have SQL Server Reporting Services installed?
Harolds
"Steven Cheng[MSFT]" wrote:

> Hi Tom,
> Welcome to MSDN newsgroup.
> Regarding on the launching Activity Monitor problem you mentioned, I think
> it is the expected behavior since Activity Monitor is a SQL Server 2005
> specific tool that targeting server /use activity monitoring. So for
> sqlserver 2000 instances they're not supported to use this tool though many
> common client components in sql 2005 managment studio are still available
> and can be launched for sql 2000 instance. In addition, for sqlserver
> 2000, there exists other performance and activity monitoring tools:
> # Monitoring Server Performance and Activity
> http://msdn.microsoft.com/library/en...0mr.asp?frame=
> true
> Thanks,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
> --
> | Thread-Topic: Activity Monitor: SQL 2005
> | thread-index: AcYEtqv3CT7/oApERimnOFGHHQ7OsQ==
> | X-WBNR-Posting-Host: 68.67.208.184
> | From: "=?Utf-8?B?VG9tVA==?=" <tomt@.newsgroup.nospam>
> | Subject: Activity Monitor: SQL 2005
> | Date: Mon, 19 Dec 2005 08:10:04 -0800
> | Lines: 36
> | Message-ID: <35FAA28A-3693-4385-A3DF-CE3474AB6B85@.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.tools
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.tools:29191
> | X-Tomcat-NG: microsoft.public.sqlserver.tools
> |
> | Is this supposed to work with SQL Server 2000 databases? I get the
> following
> | message when trying to launch it on one of these:
> |
> | TITLE: Microsoft SQL Server Management Studio
> | --
> |
> | Cannot show requested dialog.
> |
> | --
> | ADDITIONAL INFORMATION:
> |
> | Unable to execute requested command.
> |
> | --
> |
> | Unable to launch Activity Monitor. You may not have sufficient
> permissions.
> | (ActivityMonitor)
> |
> | --
> |
> | Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> |
> | For help, click:
> |
> http://go.microsoft.com/fwlink?ProdN...dVer=08.00 .2
> 039&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
> |
> | --
> | BUTTONS:
> |
> | OK
> | --
> |
> | It works fine when connected to 2005 servers.
> |
> | Thanks,
> |
> | TomT
> |
>
|||Harolds (harolds@.community.nospam) writes:
> In SQL BOL it states "To view the Activity Monitor the user needs SELECT
> permission to the sysprocesses and syslocks tables in the master
> database on a SQL Server 2000 server. Permission to view these tables is
> granted by default to the PUBLIC database role."
> I use two different SQL Server 2000 servers, one I can use Activity
> Monitor on the other I get the same error, my securty for both servers
> is identical.
Given the error message you get:
Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
My guess is that your default database on this server is not the master
database.
I made an experinment by connecting to an SQL 2000 with a user that
had tempdb as its default database, and indeed I got this exact message.
The part of the message that talks about insuffcient permission is
bogus. On SQL 2005, you cannot tell whether an error is due to lack
of permission or whether the object is really missing. But SQL 2000
will always tell you the exact reason.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Given the error message you get:
> Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> My guess is that your default database on this server is not the master
> database.
> I made an experinment by connecting to an SQL 2000 with a user that
> had tempdb as its default database, and indeed I got this exact message.
> The part of the message that talks about insuffcient permission is
> bogus. On SQL 2005, you cannot tell whether an error is due to lack
> of permission or whether the object is really missing. But SQL 2000
> will always tell you the exact reason.
And since you should be able to use Activity Monitor even with a
different default database than master, I've filed a bug for this:
http://lab.msdn.microsoft.com/Produc...ckId=FDBK43531
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks, that fixed the problem.
Harolds
"Erland Sommarskog" wrote:

> Harolds (harolds@.community.nospam) writes:
> Given the error message you get:
> Invalid object name 'sysdatabases'. (Microsoft SQL Server, Error: 208)
> My guess is that your default database on this server is not the master
> database.
> I made an experinment by connecting to an SQL 2000 with a user that
> had tempdb as its default database, and indeed I got this exact message.
> The part of the message that talks about insuffcient permission is
> bogus. On SQL 2005, you cannot tell whether an error is due to lack
> of permission or whether the object is really missing. But SQL 2000
> will always tell you the exact reason.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>

Activity in one DB causes time-outs in another

We have several SQL 2000 databases on one server.
One of the applications I'm responsible for has batch jobs that run for an hour; all activity is on the database. During this hour, other applications that use other databases on the same server experience time-outs. One of my coworkers did a count(*) on an empty table and it took 11 seconds.

We pay people to keep our servers up and running. Is this something they might solve by reconfiguring the server? It seems strange to me that a single database is allowed to hog all server resources.
We are meeting with them later this week, and I'd like to have some knowledge about this; we don't want to BS'ed into buying a new server.personally, I would look at optimizing your hour long batch process if at all possible. If you follow the directions in Brett's sticky at the top, I am sure some one would be happy to look over your code for you.|||There's that,yes. Right now it's cursor driven, because, "you know, if we just make some changes to the previous version we'll be done quicker."

Data is read from a file (created at a specific point in time) into a single table, and the batch process consists of two stages: stage one is comparing the new data to what we already have, and create an entry in the log table for every difference. Stage two is changing our data so that it reflects the data in the file, for that particular point in time.

I'm pretty sure that stage one can be done set-based, but I'm not so sure about stage two. The actions required for each row in the new data depend on what's already there in our db.