Alter table problem

Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.What exactly is not working after applying that hotfix? Can you post the
commands that are failing? Any error messages? Or are you trying to alter
tables from Enterprise Manager and getting errors?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0ttp0$506$1@.sunce.iskon.hr...
Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.|||I can not find that KB article on MS website

Alter table problem

Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.What exactly is not working after applying that hotfix? Can you post the
commands that are failing? Any error messages? Or are you trying to alter
tables from Enterprise Manager and getting errors?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0ttp0$506$1@.sunce.iskon.hr...
Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.|||I can not find that KB article on MS website

alter login on Windows user groups

Hi,
I have problem with respect to "alter login" on Windows user groups (created
via Active Directory).
If I execute the statement "alter login [domain\groupname] enable" I get
an
exception saying (the exception also occurs with "alter login
[domain\groupname] disable"):
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login of 'domain\groupname', because it does not exist or
you do not have permissions.
If if try to enable the login via SQL Server Management GUI I get the same
exception.
Is this because it is not at all possible to alter the logins of windows
user groups? Or is it a bug in SQL Server?
I am running MS SQL Server 2005 SP1 (compatibility level 90).
The user I am executing the statement as is "sysadmin" on the server.
Thanks in advance.
Kind Regards,
Rune Jensen
ETIAFAIK you can only disable/enable logins that represent individual windows
accounts, not windows groups.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Server App" <aaa@.yes.dk> wrote in message
news:%23VF$pTC%23GHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have problem with respect to "alter login" on Windows user groups
> (created
> via Active Directory).
> If I execute the statement "alter login [domain\groupname] enable" I g
et
> an
> exception saying (the exception also occurs with "alter login
> [domain\groupname] disable"):
> Msg 15151, Level 16, State 1, Line 1
> Cannot alter the login of 'domain\groupname', because it does not exist or
> you do not have permissions.
> If if try to enable the login via SQL Server Management GUI I get the same
> exception.
> Is this because it is not at all possible to alter the logins of windows
> user groups? Or is it a bug in SQL Server?
> I am running MS SQL Server 2005 SP1 (compatibility level 90).
> The user I am executing the statement as is "sysadmin" on the server.
> Thanks in advance.
> Kind Regards,
> Rune Jensen
> ETI
>
>

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all databases.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all database
s.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all databases.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Also cannot display other windows to.

I use sql server 2005 developer edition with service pack 1.

When i right click on a database and i select properties an error occured with the folowing stack trace

===================================

Cannot show requested dialog.

===================================

Cannot show requested dialog. (SqlMgmt)

Program Location:

at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc)
at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

===================================

Object reference not set to an instance of an object. (System.Data)

Program Location:

at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.DatabaseData..ctor(CDataContainer context, String databaseName)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.LoadDefinition(String newName)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype..ctor(CDataContainer context)
at Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet..ctor(CDataContainer context)

Accroding to the reflected sources:

public DatabaseData(CDataContainer context, string databaseName)
{
this.mirrorSafetyLevel = MirroringSafetyLevel.Off;
this.witnessServer = string.Empty;
Database database1 = context.Server.Databases[databaseName];

There might be a problem in getting the information from the database collection. So do the following steps:

-Run the profiler to the when the execution of the command stops. (Guess it has to do something with the database name)
-Select the database name from the sysdatabases and post it here

SELECT Name, DATALENGTH(Name),LEN(Name) from sys.databases

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

When i try to create a new view, in any of the databases i saw an error

Object reference not set to an instance of an object. (SQLEditors)

Program Location:

at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname)
at Microsoft.SqlServer.Management.Smo.Database.get_DefaultSchema()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GetDefaultSchema(Server server, String databaseName)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GenerateNewObjectUrn()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.SetObjectAndParentUrns(Urn originalUrn)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode..ctor(Urn urn, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode.Allocate(Urn origUrn, DocumentType editorType, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

I think that there is a problem with the server installation. I will try to reinstalle it.

|||If you want to solve the problem, follow the mentioned steps to reproduce the executed script on the server. That might also help others to solve their problems and help to improve the product itself.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Please make sure the database exists and is not deleted by clicking refresh on the server node and see if you can still see the database whose properties you were not able to access. I believe the database was dropped by some other means and your SSMS window was not refreshed after that.

Hope this helps.

Thanks,

Sravanthi.

|||Stack trace dump means there might be a probelm with the Windows, a virus or mismatch of hotfix/service pack on operating system. Make sure to check what has been changed since this was working correctly in previous state, if not you might try testing the same on other machine.|||All databases are in place. i can open tables and see their data.|||

thats what i believe too.

The problem appears after an update from microsoft windows update which find that my windows sql server installation need the service pack 1 update. I selected and install it.

after that i download and install service pack 2 for sql server 2005 but of course this doesn't correct anything.

I have in the same computer a sqlserver express edition installed and from microsoft sql server managment studio i can work properly with this instance without a problem (in case i thought that was a problem from microsoft sql server managment studio).

About response from Jens K. Suessmeyer .

Whene i execute the line

SELECT 'AdventureWorks', DATALENGTH('AdventureWorks'),LEN('AdventureWorks') from sys.databases

i get

'An error occurred while executing batch. Error message is: Object reference not set to an instance of an object.'

with any database there are in this installation.

|||

Hi,

From where are you running the queries? Please run the query SELECT Name, DATALENGTH(Name),LEN(Name) from sys.databases (run it as is as Jens K. Suessmeyer has given, dont replace Name in the query, if you want it specific to AdventureWorks just add a where clause). Run this query from new query window in SSMS and let us know the output. From the error that you are getting "Object reference..." looks like you are trying to run the query programmatically. Just run it from a SSMS query window and let us know the output. Make sure the database that is causing all these issues comes up in the query result.

Sravanthi

|||If you believe its SSMS tools problem, try to reinstall them again.|||

All databases causing that issues

The result after executing the above query is

NAME (no column name) (no column name)

master 12 6
tempdb 12 6
model 10 5
msdb 8 4
ReportServer$MAIMOY2005 46 23
ReportServer$MAIMOY2005TempDB 58 29
BASE DE DATOS ORIGINAL 44 22
ALKI 8 4
aspnetdb_ALKI 26 13
AdventureWorks 28 14
AdventureWorksDW 32 16

(11 row(s) affected)

|||After a full uninstall and reinstall everything seems to work perfect. Aftes installation i install also service pack 2 downloaded and installed locally and everything works properly.|||

somehow, i missed this thread and I am sure if I could furnish this information bit earlier it would have been helpful. nevertheless, i think i should share my experience in this regards. the story is as follows :)

One of our development server had the same problem and I have documented this error. But at that time I was on the tows and somehow I was to get rid of this problem and I did the same trick - reinstalling the SQL Server. But I was not satisfied by this solution. when I did the postmortem of the process then I realized that our TL used to synchronies the Development database from Visio. There were many connection used to connect to different database (from visio) and one of them was to connect to master database. He used the master connection , and Visio automatically detects the objects in the connected database which are not there in the Model and it ask whether u want to delete those object or not. He selected Yes and Visio deleted all the objects from master database which are not there in the model. I verified the objects between two instances Master databases. There were five system tables missing , the missing tables were spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_value. Then I created the script of these tables from other instance and run on the problem server, but those tables were not having owner , though it shows owner as DBO. Actually these tables comes under System Tables tree but when I created these by the script those created as user table. I was pretty sure that these problem were because of these tables got deleted. But I was not having time to do R&D on this and I reinstalled the instance.

(a) How come Visio able to delete system tables (the irony is that , in SSMO these tables are shown as System Tables , but if u use sp_help it is shown as Usertable).

(b) IF somehow these tables got deleted, how can we restore these table and revert back to normal stage without reinstalling anything.

Also question to Antonisk, is something like this was happened in your side…

I think we need to dig out the root of this problem. If these tables are so critical , then these should not be deletable from anywhere. If it is a bug the we need to report this to MS..

Thanks for the time

Madhu

|||I don't use Visio at all. Also i can't check if this was the problem (system tables missing) because I reinstall the Sql Server.

Already open DataReader Error

I am getting the following error when running some of my reports that use a Report Model on a recently built Windows 2003 R2 server with SQL Server 2005 SP2 intalled. The reports run fine our SQL Server 2005 RTM server.

W3wp!webserver!7!01/09/2007-12:57:58:: e

ERROR: Reporting Services

error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:

An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > System.InvalidOperationException:

There is already an open DataReader associated with this Command which must be closed first.

Any help would be appreciated.

Rick

HI Rick,

Did you manage to get a solution for this? I am experiencing the very same problem.

Already open DataReader Error

I am getting the following error when running some of my reports that use a Report Model on a recently built Windows 2003 R2 server with SQL Server 2005 SP2 intalled. The reports run fine our SQL Server 2005 RTM server.

W3wp!webserver!7!01/09/2007-12:57:58:: e

ERROR: Reporting Services

error Microsoft.ReportingServices.Diagnostics.Utilities.RSException:

An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > System.InvalidOperationException:

There is already an open DataReader associated with this Command which must be closed first.

Any help would be appreciated.

Rick

HI Rick,

Did you manage to get a solution for this? I am experiencing the very same problem.

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only acces
s
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete record
s.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a databas
e
> for a specific user.
> Basically, I have an application which uses Windows Authentication to acce
ss
> a database on SQL server 2000. I only want this user to have read only acc
ess
> to this database. I have added the user into the Logins in SQL server (i.e
.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete reco
rds.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allow Multiple Parameters form Windows Application

I have been working through a solution to allow multiple parameters to be
passed into a SQL RS report. I have created a windows application that calls
sql reporting services. The problem that I am having now is passing the
multiple values the user selects from the dropdown to sql reporting services.
I am setting: returnValues.Value
When I try to set this parameters to 1;2;3, report fails. However, setting
that value to 1 works.
Is this possbile?RS 2000 does not support multiple selections. For instance,
select * from blah where somefield in (@.Param)
will not work. What you can do is use either an expression or call a stored
procedure that takes the parameter and handles appropriately).
This will work:
= "select * from blah where somefield in (" & Parameters!Paramname.value &
")"
Note that this assume you have dealt with putting in all the proper syntax
like single quotes around charater type parameters, etc and that this will
be a valid query when done.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>I have been working through a solution to allow multiple parameters to be
> passed into a SQL RS report. I have created a windows application that
> calls
> sql reporting services. The problem that I am having now is passing the
> multiple values the user selects from the dropdown to sql reporting
> services.
>
> I am setting: returnValues.Value
> When I try to set this parameters to 1;2;3, report fails. However,
> setting
> that value to 1 works.
> Is this possbile?|||I have been able to get the mulitple selection parameters to work if I call
the SQL RS report from a web page. I basically created dropdown listboxes
and have the form post to the url of the page I want to run. The multiple
selection values are sent to the report and the report works fine. However,
I am trying to call SQL RS report from a Windows Application. I have my
report created in a way that it uses a stored procedure to parse the multiple
values passed to in and joins to those values from a temp table.
"Bruce L-C [MVP]" wrote:
> RS 2000 does not support multiple selections. For instance,
> select * from blah where somefield in (@.Param)
> will not work. What you can do is use either an expression or call a stored
> procedure that takes the parameter and handles appropriately).
> This will work:
> = "select * from blah where somefield in (" & Parameters!Paramname.value &
> ")"
> Note that this assume you have dealt with putting in all the proper syntax
> like single quotes around charater type parameters, etc and that this will
> be a valid query when done.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >I have been working through a solution to allow multiple parameters to be
> > passed into a SQL RS report. I have created a windows application that
> > calls
> > sql reporting services. The problem that I am having now is passing the
> > multiple values the user selects from the dropdown to sql reporting
> > services.
> >
> >
> > I am setting: returnValues.Value
> >
> > When I try to set this parameters to 1;2;3, report fails. However,
> > setting
> > that value to 1 works.
> >
> > Is this possbile?
>
>|||OK, so you are doing the stored procedure method. That a good way to do it.
This whole thing work if from a web page passing in the multiple selections,
the only difference is that you are doing this from a windows app?
How are you integrating your windows app? URL integration or web services?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>I have been able to get the mulitple selection parameters to work if I call
> the SQL RS report from a web page. I basically created dropdown listboxes
> and have the form post to the url of the page I want to run. The multiple
> selection values are sent to the report and the report works fine.
> However,
> I am trying to call SQL RS report from a Windows Application. I have my
> report created in a way that it uses a stored procedure to parse the
> multiple
> values passed to in and joins to those values from a temp table.
> "Bruce L-C [MVP]" wrote:
>> RS 2000 does not support multiple selections. For instance,
>> select * from blah where somefield in (@.Param)
>> will not work. What you can do is use either an expression or call a
>> stored
>> procedure that takes the parameter and handles appropriately).
>> This will work:
>> = "select * from blah where somefield in (" & Parameters!Paramname.value
>> &
>> ")"
>> Note that this assume you have dealt with putting in all the proper
>> syntax
>> like single quotes around charater type parameters, etc and that this
>> will
>> be a valid query when done.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >I have been working through a solution to allow multiple parameters to
>> >be
>> > passed into a SQL RS report. I have created a windows application that
>> > calls
>> > sql reporting services. The problem that I am having now is passing
>> > the
>> > multiple values the user selects from the dropdown to sql reporting
>> > services.
>> >
>> >
>> > I am setting: returnValues.Value
>> >
>> > When I try to set this parameters to 1;2;3, report fails. However,
>> > setting
>> > that value to 1 works.
>> >
>> > Is this possbile?
>>|||I actually need the ability to write the report to a file or display the
report in a web browser on the screen. In both cases I have to set the
Parameter values using an array.
"Bruce L-C [MVP]" wrote:
> OK, so you are doing the stored procedure method. That a good way to do it.
> This whole thing work if from a web page passing in the multiple selections,
> the only difference is that you are doing this from a windows app?
> How are you integrating your windows app? URL integration or web services?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
> >I have been able to get the mulitple selection parameters to work if I call
> > the SQL RS report from a web page. I basically created dropdown listboxes
> > and have the form post to the url of the page I want to run. The multiple
> > selection values are sent to the report and the report works fine.
> > However,
> > I am trying to call SQL RS report from a Windows Application. I have my
> > report created in a way that it uses a stored procedure to parse the
> > multiple
> > values passed to in and joins to those values from a temp table.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS 2000 does not support multiple selections. For instance,
> >> select * from blah where somefield in (@.Param)
> >>
> >> will not work. What you can do is use either an expression or call a
> >> stored
> >> procedure that takes the parameter and handles appropriately).
> >>
> >> This will work:
> >>
> >> = "select * from blah where somefield in (" & Parameters!Paramname.value
> >> &
> >> ")"
> >>
> >> Note that this assume you have dealt with putting in all the proper
> >> syntax
> >> like single quotes around charater type parameters, etc and that this
> >> will
> >> be a valid query when done.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
> >> message
> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >> >I have been working through a solution to allow multiple parameters to
> >> >be
> >> > passed into a SQL RS report. I have created a windows application that
> >> > calls
> >> > sql reporting services. The problem that I am having now is passing
> >> > the
> >> > multiple values the user selects from the dropdown to sql reporting
> >> > services.
> >> >
> >> >
> >> > I am setting: returnValues.Value
> >> >
> >> > When I try to set this parameters to 1;2;3, report fails. However,
> >> > setting
> >> > that value to 1 works.
> >> >
> >> > Is this possbile?
> >>
> >>
> >>
>
>|||What I was trying to clarify is what does and does not work.
It sounds like calling the report from a web page using URL integration does
work. But, you are trying to use web services from your windows app (as an
alternative you can embed an IE control and use URL integration). Since you
say that a single value selected work, I wonder if some seperator character
is causing a problem. In your windows app try having a textbox that you key
in the correct value and see if that works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:A07E6166-50E6-44D8-8ED4-3E3C279EAB1E@.microsoft.com...
>I actually need the ability to write the report to a file or display the
> report in a web browser on the screen. In both cases I have to set the
> Parameter values using an array.
> "Bruce L-C [MVP]" wrote:
>> OK, so you are doing the stored procedure method. That a good way to do
>> it.
>> This whole thing work if from a web page passing in the multiple
>> selections,
>> the only difference is that you are doing this from a windows app?
>> How are you integrating your windows app? URL integration or web
>> services?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>> >I have been able to get the mulitple selection parameters to work if I
>> >call
>> > the SQL RS report from a web page. I basically created dropdown
>> > listboxes
>> > and have the form post to the url of the page I want to run. The
>> > multiple
>> > selection values are sent to the report and the report works fine.
>> > However,
>> > I am trying to call SQL RS report from a Windows Application. I have
>> > my
>> > report created in a way that it uses a stored procedure to parse the
>> > multiple
>> > values passed to in and joins to those values from a temp table.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> RS 2000 does not support multiple selections. For instance,
>> >> select * from blah where somefield in (@.Param)
>> >>
>> >> will not work. What you can do is use either an expression or call a
>> >> stored
>> >> procedure that takes the parameter and handles appropriately).
>> >>
>> >> This will work:
>> >>
>> >> = "select * from blah where somefield in (" &
>> >> Parameters!Paramname.value
>> >> &
>> >> ")"
>> >>
>> >> Note that this assume you have dealt with putting in all the proper
>> >> syntax
>> >> like single quotes around charater type parameters, etc and that this
>> >> will
>> >> be a valid query when done.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >>
>> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >> >I have been working through a solution to allow multiple parameters
>> >> >to
>> >> >be
>> >> > passed into a SQL RS report. I have created a windows application
>> >> > that
>> >> > calls
>> >> > sql reporting services. The problem that I am having now is passing
>> >> > the
>> >> > multiple values the user selects from the dropdown to sql reporting
>> >> > services.
>> >> >
>> >> >
>> >> > I am setting: returnValues.Value
>> >> >
>> >> > When I try to set this parameters to 1;2;3, report fails. However,
>> >> > setting
>> >> > that value to 1 works.
>> >> >
>> >> > Is this possbile?
>> >>
>> >>
>> >>
>>

Allow both Anonymous Access and Windows Integrated Access on RS

How can I configure IIS to allow anonymous and integrated requests to a
single Reporting Services 2005 installation?
If I try to mix the two securities (anonymous access and Windows integrated
authentication), all requests run as anonymous (under the IUSR account) and
therefore I get an access denied errors when trying to access a report/folder
that does not allow IUSR as a report "browser").
I have tried to set up a separate web site on the IIS box that points to the
Reporting Sever folders that implements either security; but with out
success. It appears that RS 2005 requires to be installed on the "Default
Web Site"?
--
LulkTwo different questions here. The two websites that need to be running as
integrated are: reports and reportserver. Your other websites can run
anonoymous.
Your second question: As far as the default website. The install for 2000
made it very difficult to not have it as the default. However, with 2005
this should be possible. I haven't done it. For this question (how to
install where not the default website), I suggest another posting so someone
will know your question and can answer it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lulk" <Lulk@.discussions.microsoft.com> wrote in message
news:05F07710-5C48-47F2-B1BC-EA8E8D768A29@.microsoft.com...
> How can I configure IIS to allow anonymous and integrated requests to a
> single Reporting Services 2005 installation?
> If I try to mix the two securities (anonymous access and Windows
> integrated
> authentication), all requests run as anonymous (under the IUSR account)
> and
> therefore I get an access denied errors when trying to access a
> report/folder
> that does not allow IUSR as a report "browser").
> I have tried to set up a separate web site on the IIS box that points to
> the
> Reporting Sever folders that implements either security; but with out
> success. It appears that RS 2005 requires to be installed on the "Default
> Web Site"?
> --
> Lulk|||I have a single web site set up to strictly to access Reporting Services.
One I want to run Integrated and allow Anonymous. I tried creating a separate
web site that points to the same app directories, but that's not working
cleanly.
--
Lulk
"Bruce L-C [MVP]" wrote:
> Two different questions here. The two websites that need to be running as
> integrated are: reports and reportserver. Your other websites can run
> anonoymous.
> Your second question: As far as the default website. The install for 2000
> made it very difficult to not have it as the default. However, with 2005
> this should be possible. I haven't done it. For this question (how to
> install where not the default website), I suggest another posting so someone
> will know your question and can answer it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lulk" <Lulk@.discussions.microsoft.com> wrote in message
> news:05F07710-5C48-47F2-B1BC-EA8E8D768A29@.microsoft.com...
> > How can I configure IIS to allow anonymous and integrated requests to a
> > single Reporting Services 2005 installation?
> > If I try to mix the two securities (anonymous access and Windows
> > integrated
> > authentication), all requests run as anonymous (under the IUSR account)
> > and
> > therefore I get an access denied errors when trying to access a
> > report/folder
> > that does not allow IUSR as a report "browser").
> > I have tried to set up a separate web site on the IIS box that points to
> > the
> > Reporting Sever folders that implements either security; but with out
> > success. It appears that RS 2005 requires to be installed on the "Default
> > Web Site"?
> > --
> > Lulk
>
>|||You cannot run both anonymous and integrated. That just doesn't work. Once
you are anonymous you are anoymous.
If you are in an intranet environment, most domains have a generic Users
group which is everybody in that domain. That would allow you to let
everyone access to the report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lulk" <Lulk@.discussions.microsoft.com> wrote in message
news:E2CC088F-2D45-4BD1-91C7-409823061287@.microsoft.com...
>I have a single web site set up to strictly to access Reporting Services.
> One I want to run Integrated and allow Anonymous. I tried creating a
> separate
> web site that points to the same app directories, but that's not working
> cleanly.
> --
> Lulk
>
> "Bruce L-C [MVP]" wrote:
>> Two different questions here. The two websites that need to be running as
>> integrated are: reports and reportserver. Your other websites can run
>> anonoymous.
>> Your second question: As far as the default website. The install for 2000
>> made it very difficult to not have it as the default. However, with 2005
>> this should be possible. I haven't done it. For this question (how to
>> install where not the default website), I suggest another posting so
>> someone
>> will know your question and can answer it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lulk" <Lulk@.discussions.microsoft.com> wrote in message
>> news:05F07710-5C48-47F2-B1BC-EA8E8D768A29@.microsoft.com...
>> > How can I configure IIS to allow anonymous and integrated requests to a
>> > single Reporting Services 2005 installation?
>> > If I try to mix the two securities (anonymous access and Windows
>> > integrated
>> > authentication), all requests run as anonymous (under the IUSR account)
>> > and
>> > therefore I get an access denied errors when trying to access a
>> > report/folder
>> > that does not allow IUSR as a report "browser").
>> > I have tried to set up a separate web site on the IIS box that points
>> > to
>> > the
>> > Reporting Sever folders that implements either security; but with out
>> > success. It appears that RS 2005 requires to be installed on the
>> > "Default
>> > Web Site"?
>> > --
>> > Lulk
>>

Allow authenticated access without a log on

I have reporting services set up and working, using Windows Authentication
and a web page with hyperlinks to the reports
(i.e. /Reports/Pages/Report.aspx?ItemPath=%2fReports%2MyReport).
This is on an Intranet and users who have not authenticated against the
domain get a username and password prompt (as we would expect).
I turns out that none of the users will authenticated and so they will all
get the log on prompt.
Is there any way to avoid the log in prompt ? (a simple way - I know I can
Implement my own Security Extension - but this is overkill for my needs).
I can't use anonymous access as different groups of people need to see
different reports (and be denied access to the others).
There are 4 groups and I already know which group each user is in. ( they do
a forms based sign in to a gateway application first)
Is it possible for instance to pass a username and password to a report and
have it authenticate that way ?
Someone suggested using the API:
dim lgn as new
Microsoft.SqlServer.ReportingServices2005.ReportingService2005()
lgn.LogonUser("Myusername" , "myPassword" , "" )
Is this possible ? if so how would the report then be launched ?
I don't mind hard coding a generic username and password in the asp.net code
behind for the page. ( I can set up 4 generic accounts, one for each group).
--
MartyYou should be able to do this. If the user is logged in to your PC in the
same domain as the RS server then they should not be prompted.
Anything special about the server? I.e. is it a domain controller. Is it
running sharepoint? Does it have a fixed IP address?
I ask about the fixed IP address because I saw this problem once when I had
a test server that was using DHCP.
Also, try adding your domain users and groups to a local group and then use
that local group when setting up your roles.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marty" <smarty@.community.nospam> wrote in message
news:9B1A741B-88BB-494A-AA71-67C058D8C7D7@.microsoft.com...
>I have reporting services set up and working, using Windows Authentication
> and a web page with hyperlinks to the reports
> (i.e. /Reports/Pages/Report.aspx?ItemPath=%2fReports%2MyReport).
> This is on an Intranet and users who have not authenticated against the
> domain get a username and password prompt (as we would expect).
> I turns out that none of the users will authenticated and so they will all
> get the log on prompt.
> Is there any way to avoid the log in prompt ? (a simple way - I know I can
> Implement my own Security Extension - but this is overkill for my needs).
> I can't use anonymous access as different groups of people need to see
> different reports (and be denied access to the others).
> There are 4 groups and I already know which group each user is in. ( they
> do
> a forms based sign in to a gateway application first)
> Is it possible for instance to pass a username and password to a report
> and
> have it authenticate that way ?
> Someone suggested using the API:
> dim lgn as new
> Microsoft.SqlServer.ReportingServices2005.ReportingService2005()
> lgn.LogonUser("Myusername" , "myPassword" , "" )
> Is this possible ? if so how would the report then be launched ?
> I don't mind hard coding a generic username and password in the asp.net
> code
> behind for the page. ( I can set up 4 generic accounts, one for each
> group).
> --
> Marty|||"Also, try adding your domain users and groups to a local group and then use
that local group when setting up your roles."
Ah but my issue is that the users have not logged in to the domain when they
use my application. If I get them to log in to the domain first (as a test)
it all works seamlessley, with no prompts. However this is not how they
normally work (don't ask !).|||On Jan 8, 3:13=A0am, Marty <sma...@.community.nospam> wrote:
> "Also, try adding your domain users and groups to a local group and then u=se
> that local group when setting up your roles."
> Ah but my issue is that the users have not logged in to the domain when th=ey
> use my application. If I get them to log in to the domain first (as a test=)
> it all works seamlessley, with no prompts. However this is not how they
> normally work (don't ask !).
Along the same line, is there a way to use Windows Auth to not require
the log on screen every time we open
the Reporting Services web page rpt-serv/reports? The report server
is in domain I created a local group and added
mydomain\domain users to it? Hope this did not hijack your thread
Marty :)
Bob|||Hello Smart,
Since the reporting services is designed for the windows auth, you may need
to specify the domain account and password to log on to the domain.
So it will prompt the dialog.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||This makes absolutely no sense. You want them to be authenticated in RS
without logging in BUT they haven't logged in period. Then how would any
system in the world know who they are?
Sorry, this is just plain silly. If you aren't logged into a domain then you
aren't logged in and you are not authenticated and there is no way to be
authenticated without logging in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marty" <smarty@.community.nospam> wrote in message
news:ABBE4EB2-43DE-424F-9655-32D3FF57559D@.microsoft.com...
> "Also, try adding your domain users and groups to a local group and then
> use
> that local group when setting up your roles."
> Ah but my issue is that the users have not logged in to the domain when
> they
> use my application. If I get them to log in to the domain first (as a
> test)
> it all works seamlessley, with no prompts. However this is not how they
> normally work (don't ask !).
>

All SSIS packages failing after Windows Update!

Starting saturday all of our SSIS packages on a server (64-bit) starting failing (hundreds of them) the error is:

Precompiled script failed to load. Attempting to reload the script with updated data. For more information, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).

That Knowledgebase link talks about SP2 fixing the issue but we have SP2 already on the server. The sysdtslog90 table is just packed with these as each script inside each package is getting the same error. Looking at the system log the following were installed as part of windows update shortly before the errors started occuring:

- Update for Windows Server 2003 x64 Edition (KB936357)

- Security Update for Windows Server 2003 x64 Edition (KB926122)

- Microsoft .NET Framework 3.0: x64 (KB928416)

- Security Update for Microsoft .NET Framework, Version 2.0 (KB928365)

- Security Update for Excel 2003 (KB936507)

- Update for Outlook 2003 Junk Email Filter (KB936557)

If I open the package and manually recompile the scripts it works fine.... if I need to do that for every script that is going to likely be several days of just opening the packages one by one and recompiling each script (over 100 packages, each with at least 5 script objects).

|||

I think this is the problem: - Security Update for Microsoft .NET Framework, Version 2.0 (KB928365)

See the sticky post at the top of this forum. I suspect that's what caused the issue, and of course, it's not uninstallable. There's a hotfix listed in that post that you could try applying.

|||

I read that post when I first got the error - but it sounds like that is only if you do not have SP2 install... Am I reading that KB/sticky wrong?

I'll try applying the mentioned hotfix anyway - sure beats massive recompiling by hand

|||Won't let me install those updates since I already have a newer hotfix installed..|||

Hi Chris,

You're saying that those packages fail with "

Precompiled script failed to load. Attempting to reload the script with updated data. For more information, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).

"

But that message is actually a warning not an error. It should not fail the package just warn that we are trying to workaround a problem we have in the scripts due to the .net change mentioned in the KB article. Can you paste more of the package execution output to this thread so I can try and understand what error is triggered?

Thanks,

Silviu Guea [MSFT], SQL Server Integration Services

|||

Correct... sorry the packages were not failing - however I have everything setup to email on warnings (sometimes those warnings are very useful, for example when it detects dupes in a lookup) so everyone has been getting hit with tons of email.

So .. basically are you saying I can ignore that warning? (I can hard code pkg to ignore that particular warning)

|||

Hi Chris,

You should ignore the warning. We put it there to let you know we are doing something with the script and to help diagnose the fact that the machine has the .net framework change mentioned in the KB article from the announcement found at the top of the SQL Server IS thread.

Thanks,

Silviu Guea [MSFT] SQL Server Integration Services

all of sudden cant connect with SQL server authentication

Can connect with Windows NT authentication, but not with SQL server auth. So
all my
code has stopped working ...
When I try to make a new SQL Server registration
I get server does not exist or access denied...Barrett Bonden wrote:
> Can connect with Windows NT authentication, but not with SQL server auth.
So
> all my
> code has stopped working ...
> When I try to make a new SQL Server registration
> I get server does not exist or access denied...
>
Make sure your SQL Server is configured for mixed mode Authentication.
It sounds like it is only configured for Windows Authentication. If you
have sysadmin authority just fire up Enterprise Manager, right-click
your server, and then click Properties. On the Security tab, under
Authentication, click Mixed Mode.
If you are not a sysadmin, you have to update the Registry directly. I
believe the correct key will be called:
HKLM\Software\Microsoft\MSSqlserver\MSSq
lServer\LoginMode
change the DWORD value to 2
If you have a named instance installed the Registry path will be
something like:
HKEY_LOCAL_MACHINE\Software\Microsoft\Mi
crosoft SQL Server\Instance
Name\MSSQLServer\LoginMode
Good luck ...|||Did somebody switched the authentication mode to "Windows Auth" only ?
The mixed Authentication allows Windows Auth. as well as SQL Server
Authentication. try to log on with an adminstrative account on the SQL
Server and set the Authentication mode back to mixed authentication
then.
HTH, Jens Suessmeyer.

all of sudden cant connect with SQL server authentication

Can connect with Windows NT authentication, but not with SQL server auth. So
all my
code has stopped working ...
When I try to make a new SQL Server registration
I get server does not exist or access denied...
Barrett Bonden wrote:
> Can connect with Windows NT authentication, but not with SQL server auth. So
> all my
> code has stopped working ...
> When I try to make a new SQL Server registration
> I get server does not exist or access denied...
>
Make sure your SQL Server is configured for mixed mode Authentication.
It sounds like it is only configured for Windows Authentication. If you
have sysadmin authority just fire up Enterprise Manager, right-click
your server, and then click Properties. On the Security tab, under
Authentication, click Mixed Mode.
If you are not a sysadmin, you have to update the Registry directly. I
believe the correct key will be called:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode
change the DWORD value to 2
If you have a named instance installed the Registry path will be
something like:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
Good luck ...
|||Did somebody switched the authentication mode to "Windows Auth" only ?
The mixed Authentication allows Windows Auth. as well as SQL Server
Authentication. try to log on with an adminstrative account on the SQL
Server and set the Authentication mode back to mixed authentication
then.
HTH, Jens Suessmeyer.

All my money in dollars please?

I have a Windows 2003 server, with SQL 2005. How do i change the culture/regional options for the database as on my local test machine (which seems to be exactly the same in terms of windows regional settings and SQL server settings) the currenency fields show with a ￡ signs as expected, however on my production box they are $. I know this should be simple but its the night before a deadline and my brain is screwed. Please help!!

It 'should' be as simple as changing the regional settings using [Control Panel], [Regional and Language ...]

How is yours set?

|||

Everything is United Kingdom (except SQL Server Properties (installed language is English US but i have set login def language to British English (as fas as i was concerned that should be that) but still dollar signs and american date formats?

|||

Neil,

SQL Server does not 'store' currency symbols. From Books Online, Topic: 'Using Monetary Data':

SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.

Displaying a dollar sign or a sterling sign is a function of the client application, often in conjunction with the OS Regional settings. You may wish to check the cultureUI settings for the application and verify that it is set to English-British. Otherwise, since it isn't really a SQL Server issue, I suggest that you may wish to post in one of the .NET related forums.

|||

OK, have done some more config checks, as follows

Windows 2003 Regional Options: All United Kingom (including server default)

SQL Server 2005 Region: United States

.NET Framework v2.x - Global en-GB Application en-GB

Application User (Database) British English

And it still shows the site in dollars, i have checked every bloody variable and setting i can think of, on my local box all i have to do is change the database user language and that is that, i can change ot as i see fot and the site locally reflects these changes, apply the exact same changes on the server and it soes nothing?

Help me obi-wan you're my only hope

|||

Since SQL Server is not the culprit here, it has to be some setting in your application. (For example, if you use SSMS, and you look at the raw data, you 'should' not see any currency symbol. If there is one stored, then the application is storing it as a character field with the currency symbol intact.)

This issue is in the presentation layer: IIS, Browser, .NET CultureUI setting, a format statement in the application.

All free memory gone...

Hi!
I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
server with 16Gb memory. Total available memory is typically ~200MB
and pretty stable. I use Max Server Memory of 15700MB. Today I could
see a very unusual behavior: the available memory gone from 200MB to
4MB in few seconds. I decreased the Max Server Memory by 200MB:
sp_configure [max server memory (MB)], 15500
reconfigure with override
go
It did not help much: it has 8MB available memory now. The server has
a lot of paging.
What is happening to the server?
Thanks.You should run Windows performance monitor and see who is using the
memory... It is in the processes section...
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> Hi!
> I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> server with 16Gb memory. Total available memory is typically ~200MB
> and pretty stable. I use Max Server Memory of 15700MB. Today I could
> see a very unusual behavior: the available memory gone from 200MB to
> 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> sp_configure [max server memory (MB)], 15500
> reconfigure with override
> go
> It did not help much: it has 8MB available memory now. The server has
> a lot of paging.
> What is happening to the server?
> Thanks.|||AWE memory is not dynamic so changing max server memory won't have any
effect until you restart the service. You really need to leave more room for
the OS, try setting max server memory to 14 GB and monitoring its stability.
Also make sure the /3GB switch is not in boot.ini - just use the /PAE
switch. There is a fair bit of OS overhead in managing AWE memory so you
need to give the OS room to breathe.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0312010553.2bf2d450@.posting.google.com...
Hi!
I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
server with 16Gb memory. Total available memory is typically ~200MB
and pretty stable. I use Max Server Memory of 15700MB. Today I could
see a very unusual behavior: the available memory gone from 200MB to
4MB in few seconds. I decreased the Max Server Memory by 200MB:
sp_configure [max server memory (MB)], 15500
reconfigure with override
go
It did not help much: it has 8MB available memory now. The server has
a lot of paging.
What is happening to the server?
Thanks.|||Performance monitor does not show processes that eat that much memory.
It does not show memory usage correctly with AWE enabled.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message news:<uZllf7BuDHA.3536@.tk2msftngp13.phx.gbl>...
> You should run Windows performance monitor and see who is using the
> memory... It is in the processes section...
>
> "Roust_m" <roustam@.hotbox.ru> wrote in message
> news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> > Hi!
> >
> > I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> > server with 16Gb memory. Total available memory is typically ~200MB
> > and pretty stable. I use Max Server Memory of 15700MB. Today I could
> > see a very unusual behavior: the available memory gone from 200MB to
> > 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> >
> > sp_configure [max server memory (MB)], 15500
> > reconfigure with override
> > go
> >
> > It did not help much: it has 8MB available memory now. The server has
> > a lot of paging.
> >
> > What is happening to the server?
> >
> > Thanks.|||It did work fine for 3 months with 200MB available memory.
This article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;274750
states that you need 1GB for OS only if you have 32+GB RAM:
"When you allocate SQL Server AWE memory on a 32 GB system, Windows
2000 may require at least 1 GB memory to manage AWE. "
As for /3G switch, I also seen an article stating that you need it for
up to 16GB RAM.
Anyway it did work fine...
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<OFS92FEuDHA.3436@.tk2msftngp13.phx.gbl>...
> AWE memory is not dynamic so changing max server memory won't have any
> effect until you restart the service. You really need to leave more room for
> the OS, try setting max server memory to 14 GB and monitoring its stability.
> Also make sure the /3GB switch is not in boot.ini - just use the /PAE
> switch. There is a fair bit of OS overhead in managing AWE memory so you
> need to give the OS room to breathe.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Roust_m" <roustam@.hotbox.ru> wrote in message
> news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> Hi!
> I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> server with 16Gb memory. Total available memory is typically ~200MB
> and pretty stable. I use Max Server Memory of 15700MB. Today I could
> see a very unusual behavior: the available memory gone from 200MB to
> 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> sp_configure [max server memory (MB)], 15500
> reconfigure with override
> go
> It did not help much: it has 8MB available memory now. The server has
> a lot of paging.
> What is happening to the server?
> Thanks.|||Windows 2000 Datacenter Server Does Not Locate Memory Greater Than 16 GB
http://support.microsoft.com/default.aspx?scid=kb;EN-US;292934
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<OFS92FEuDHA.3436@.tk2msftngp13.phx.gbl>...
> AWE memory is not dynamic so changing max server memory won't have any
> effect until you restart the service. You really need to leave more room for
> the OS, try setting max server memory to 14 GB and monitoring its stability.
> Also make sure the /3GB switch is not in boot.ini - just use the /PAE
> switch. There is a fair bit of OS overhead in managing AWE memory so you
> need to give the OS room to breathe.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Roust_m" <roustam@.hotbox.ru> wrote in message
> news:a388fd78.0312010553.2bf2d450@.posting.google.com...
> Hi!
> I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a
> server with 16Gb memory. Total available memory is typically ~200MB
> and pretty stable. I use Max Server Memory of 15700MB. Today I could
> see a very unusual behavior: the available memory gone from 200MB to
> 4MB in few seconds. I decreased the Max Server Memory by 200MB:
> sp_configure [max server memory (MB)], 15500
> reconfigure with override
> go
> It did not help much: it has 8MB available memory now. The server has
> a lot of paging.
> What is happening to the server?
> Thanks.

All DB maintenance plans fail

MS SQL 2000 SP3 on a Windows 2003 SP1 Server. Both SQL services are running
under an Administrator account. The database maintenance plans to backup
the server databases were also created when that account was logged onto the
server.
When I originally setup the maintenance plan backups with the wizard they
worked fine, but now they do not. I have repeatedly deleted them and tried
other domain admin accounts and local admin accounts without any luck. When
I review the log under the jobs it says access denied. I have attached the
DB Maintenace Plan......txt log data to show what it is recording.
===========================
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'DATABASE' as 'MYDOMAIN\Administrator' (trusted)
Starting maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:06:04 AM
[1] Database Manufacturing Tracking: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 54 secs **
[2] Database Northwind: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[3] Database pubs: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'QualityProposed'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[4] Database QualityProposed: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'QualityTracking'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[5] Database QualityTracking: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[6] Database Manufacturing Tracking: Database Backup...
Destination: [\\.\Tape0]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC SQL
Server Driver][SQL Server]Access is denied due to a password failure
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[7] Database Northwind: Database Backup...
Destination: [\\.\Tape0]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC SQL
Server Driver][SQL Server]Access is denied due to a password failure
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[8] Database pubs: Database Backup...
Destination: [\\.\Tape0]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC SQL
Server Driver][SQL Server]Access is denied due to a password failure
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[9] Database QualityProposed: Database Backup...
The backup was not performed since data verification errors were found.
[10] Database QualityTracking: Database Backup...
The backup was not performed since data verification errors were found.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:08:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Jordon,
Sounds like you are trying to put the database into single user mode which
it doesn't like.
Remove the Check Database Integrity option and you can run the Backup,
Re-index and re-calc stats elements in multi user mode.
Cheers
Andy
"Jordan" <none@.here.com> wrote in message
news:OjgzTJ05GHA.696@.TK2MSFTNGP06.phx.gbl...
> MS SQL 2000 SP3 on a Windows 2003 SP1 Server. Both SQL services are
> running under an Administrator account. The database maintenance plans to
> backup the server databases were also created when that account was logged
> onto the server.
> When I originally setup the maintenance plan backups with the wizard they
> worked fine, but now they do not. I have repeatedly deleted them and
> tried other domain admin accounts and local admin accounts without any
> luck. When I review the log under the jobs it says access denied. I have
> attached the DB Maintenace Plan......txt log data to show what it is
> recording.
>
> ===========================
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'DATABASE' as 'MYDOMAIN\Administrator' (trusted)
> Starting maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:06:04 AM
> [1] Database Manufacturing Tracking: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 54 secs **
> [2] Database Northwind: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [3] Database pubs: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'QualityProposed'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database QualityProposed: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'QualityTracking'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [5] Database QualityTracking: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [6] Database Manufacturing Tracking: Database Backup...
> Destination: [\\.\Tape0]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Access is denied due to a password failure
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [7] Database Northwind: Database Backup...
> Destination: [\\.\Tape0]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Access is denied due to a password failure
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [8] Database pubs: Database Backup...
> Destination: [\\.\Tape0]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3279: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Access is denied due to a password failure
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [9] Database QualityProposed: Database Backup...
> The backup was not performed since data verification errors were found.
> [10] Database QualityTracking: Database Backup...
> The backup was not performed since data verification errors were found.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan' on 10/3/2006 9:08:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
|||I have also tried to break up all the actions into single jobs. For
instance on the backups I had the maintenance schedule just backup the
non-system databases at 2 in the morning and they still failed.
"Andrew Copeland" <andycuk@.community.nospam> wrote in message
news:%23oH4ud05GHA.4608@.TK2MSFTNGP03.phx.gbl...
> Jordon,
> Sounds like you are trying to put the database into single user mode which
> it doesn't like.
> Remove the Check Database Integrity option and you can run the Backup,
> Re-index and re-calc stats elements in multi user mode.
> Cheers
> Andy
> "Jordan" <none@.here.com> wrote in message
> news:OjgzTJ05GHA.696@.TK2MSFTNGP06.phx.gbl...
>
|||Jordon,
Your order database is still in use so you really need to ensure that all
actions are stopped as the ALTER DATABASE command issued here needs to
change the database into single user mode.
Can you check the monitor to see what is using the database out of hours?
Regards
Andy
"Jordan" <none@.here.com> wrote in message
news:e4YwwaB6GHA.3732@.TK2MSFTNGP05.phx.gbl...
>I have also tried to break up all the actions into single jobs. For
>instance on the backups I had the maintenance schedule just backup the
>non-system databases at 2 in the morning and they still failed.
>
> "Andrew Copeland" <andycuk@.community.nospam> wrote in message
> news:%23oH4ud05GHA.4608@.TK2MSFTNGP03.phx.gbl...
>
|||I managed to track it down to the owner name on the tasks being
Administrator@.mydomain.com rather than MYDOMAIN\Administror.
The funny thing is that the services start as MYDOMAIN\Administrator and I
log in as that to manage maintenace plans but the individual tasks in the
plan all have that @.mydomain.com user. Changing these to the x\x format
took care of the problem
Thanks .
"Andrew Copeland" <andycuk@.community.nospam> wrote in message
news:eUkdSfT6GHA.4132@.TK2MSFTNGP05.phx.gbl...
> Jordon,
> Your order database is still in use so you really need to ensure that all
> actions are stopped as the ALTER DATABASE command issued here needs to
> change the database into single user mode.
> Can you check the monitor to see what is using the database out of hours?
> Regards
> Andy
> "Jordan" <none@.here.com> wrote in message
> news:e4YwwaB6GHA.3732@.TK2MSFTNGP05.phx.gbl...
>