auto date & time

there is an auto increment function at SQL server.
is there a function to automatically to stamp the date and time of the
record?
is it the only way to do it at application level?
thanks a lot.
TonyHi Tony,
You can set a default of getdate() on a DateTime Column an this will record
the current date and time when you insert a row
Is that what you mean?
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||Sounds like getdate() function. Something like,
insert into table values(getdate())
or when you create a table,
create table table1 (d datetime default getdate())
Pohwan Han. Seoul. Have a nice day.
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||Yes
Thanks Greg & Han
"GregO" <grego@.community.nospam> glsD:e9m7ZKxpFHA.1480@.TK2MSFTNGP10.phx.gbl...[co
lor=darkred]
> Hi Tony,
> You can set a default of getdate() on a DateTime Column an this will
> record the current date and time when you insert a row
> Is that what you mean?
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
>[/color]|||Here is another example:
http://www.mssql.com.au/kb/html/gmg...=psearch_articl
e_text&@.sa_id=63
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||I forgot to mention smalldatetime. Generally the data type is more economic.
Pohwan Han. Seoul. Have a nice day.
"tony wong" <x34@.netvigator.com> wrote in message
news:u%23DBjNxpFHA.764@.TK2MSFTNGP14.phx.gbl...
> Yes
> Thanks Greg & Han
>
> "GregO" <grego@.community.nospam>
> glsD:e9m7ZKxpFHA.1480@.TK2MSFTNGP10.phx.gbl...
>

Auto Data Extraction through ODBC

Hi,

Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).

Thanks

Amir

hi Amir,

please excuse my poor english, but can you please rephrase your question?

thank you and regards

|||

For SQL Express, you would most likely need to do the following:

1. Create a script file using bcp to unload the data.

2. Use SQLCmd.exe to run the script file

3. Use Windows Scheduler to execute SQLCmd.exe on a time schedule.

Look up using [ bcp ] in books Online

Look up using SQLCmd.exe in Books Online.

Look up using Windows Scheduler in Windows Help.

|||

Hi,

Thanks for your reply, I have gone through BCP link http://msdn2.microsoft.com/en-us/library/ms162802.aspx, I am wondering if you can provide a link of Sample "Sample Script of BCP to import" it would be great help for me.

Thanks

Amir

|||

Perhaps this will help you.

http://msdn2.microsoft.com/en-us/library/aa173839(SQL.80).aspx

|||

Thanks for info.

It might be a silly question for you but I am using first time and I have no idea could you please define below also

a) Should I creat Batch file OR Text File for BCP

b) How should I run and test

|||

I normally consider a 'batch file' as an input source, and a 'text file' as an output source.

Look up bcp in Books Online, Topic: bcp Utility.

There are a couple of examples that will execute in your query window.

authentication problem with SQL 2000

Hello guys... I have been struggling for quite some time trying to have the authentication page on my website work :-( and I'm now desperate for some help.

I have used VWD to create a website and I have a login.aspx page that authentiocate users based on their credential... all of it created with the web admin tool.
Now guess what! I have an instance of SQL 2005 running on my PC and it all works smoothly (users are screened and redirected to pages according to their status) but when I'm uploading the website to the company's server which is only authorized to run on SQL 200 I get the following error message:

Server Error in '/newmedical/webtools' Application.

---

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684883 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +890 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +601 System.Data.SqlClient.SqlInternalConnectionTds..ctor(SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +159 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +108 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +445 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +304 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +126 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +239 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

Thanks in advance for your tips and advices
JY

Jeewai:

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

No, you can use aspnet_regsql.exe (should be in C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxx\) to created the database objects required by the membership in any SQL database. Certaintly you can use SQL2000, however you have to do some configuration. Please take a look at this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

|||

Thanks so muchIori_Jay for your link to Scott's weblog. I really appreciate the help.
OK, I have run the aspnet_regsql to change my Application Services to use Sql Server 2000 (not Express) on the company's webserver and modified the web.config file to point the machine config file look up the SQL 2000 db for user credentials. I run VWD locally and the application runs fine and is able to connect to the remote company's server to authenticate users.(the newly created tables...aspnet_member and so on... in the webserver db are getting the credentials I've set up using the asp.net web admin tool)

Now after ftping the app to the company's webserver (running SQl 2000) and trying to login on the browser this time I get :

Server Error in '/newmedical' Application.

---

Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +786258 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684870 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1751 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +37 System.Data.SqlClient.SqlDataReader.get_MetaData() +58 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +213 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +570 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +134 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Do I have to runaspnet_regsql.exe on the web server or check the .NET framework version which my may not be the same as the one in my development environment? I'm at a loss and any tips would be greatly welcome.

Thanks again in advance for your help. I do believe that others may have encountered the same error and then any help would benefit us all.

Jeewai

|||The new error is a .NET error, not related to the database schema. It indicates to excute the 'aspnet_Membership_GetPasswordWithFormat' stored procedure, you need to give values to the parameters. Can you find where you call the 'aspnet_Membership_GetPasswordWithFormat' procedure? Or it is implictly called by some control?|||

Success ! I found out the technical glitch that was preventing the authentication on the company's webserver. Indeed I updated the webserver's framework to version :v 2.0.50727 which was the one used in my development environment. Then I ran the aspnet_reg utility on the webserver to update the tables and voila...the faulty aspnet_Membership_GetPassword... was fixed and now the application is running wonderfully.

So I hope this short explanation will help a lot of struggling programmers regarding the aforementioned issue. Thanks again to Iori_Jay.

JY

Authentication problem

Hi,
First of all I would like to thank you in advance for all your time given to
my problem.
I'll try to explain our problem.
In our network we have several servers all with W2K SERVER.
SERVER 1 is DCP
SERVER 2 is DC
SERVER 3 is running SQL server standalone
SERVER 4 is running SQL server getting data from SERVER 2
SERVER 4 has several users defined as LOCAL and is supposed to get all AD
users but (and here is the problem) it doesn't.What I've found is that
SERVER 4 takes AD users from SERVER 3 and this one has some users missing
(all of them created after an specific date). The big problem is that the
users "most wanted" can't access OLAP cubes and I can't find a way to give
SERVER 4 those users.
Before that "day" SERVER 3 was a DC and now it isn't, so the AD information
is not replied to this server. Is there any way for SQL SERVER (or SERVER 4)
to get the whole AD information directly from SERVER 1 (or 2) without
promoting SERVER 4 to DC.
Thanks again
Alvaro Ruiz
URENDE, S.A.
alvaro.ruiz@.urende.es
Note: I'm not a expert in this so, I beg your pardon if I wrote something
that is not correct or if I have a wrong idea of how this works.Hi
Shutdown Server 3, Reboot Server 4 and see if you are getting the full list.
How was Server 3 turned into a stand alone server,after is was a DC? DCPROMO
or just a re-install without running DCPROMO first? If it was a re-install,
AD will still show it as a DC.
Regards
Mike
"Alvaro Ruiz" wrote:

> Hi,
> First of all I would like to thank you in advance for all your time given
to
> my problem.
> I'll try to explain our problem.
> In our network we have several servers all with W2K SERVER.
> SERVER 1 is DCP
> SERVER 2 is DC
> SERVER 3 is running SQL server standalone
> SERVER 4 is running SQL server getting data from SERVER 2
> SERVER 4 has several users defined as LOCAL and is supposed to get all AD
> users but (and here is the problem) it doesn't.What I've found is that
> SERVER 4 takes AD users from SERVER 3 and this one has some users missing
> (all of them created after an specific date). The big problem is that the
> users "most wanted" can't access OLAP cubes and I can't find a way to give
> SERVER 4 those users.
> Before that "day" SERVER 3 was a DC and now it isn't, so the AD informatio
n
> is not replied to this server. Is there any way for SQL SERVER (or SERVER
4)
> to get the whole AD information directly from SERVER 1 (or 2) without
> promoting SERVER 4 to DC.
> Thanks again
> Alvaro Ruiz
> URENDE, S.A.
> alvaro.ruiz@.urende.es
>
> Note: I'm not a expert in this so, I beg your pardon if I wrote something
> that is not correct or if I have a wrong idea of how this works.
>
>sql

Authentication problem

I am setting up replication for the first time and, as can be expected,
running into problems. The scenario is a server in one domain that will be a
publisher using a remote distributor in a different domain. When trying to
configure the publisher server, I keep getting a 'server does not exist or
access denied' when it tries to configure the remote distributor. I know the
server exists, so I figure I have messed up something in the way they
authenticate with each other. There is a 2-way trust between the domains and
none of the SQL or SQL Agent Services are running under the system accounts.
Any suggestions or links to good info would be most appreciated.
Thanks,
Bob Castleman
DBA Poseur
have a look at this link
http://support.microsoft.com/default...b;en-us;321822
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bob Castleman" <nomail@.here> wrote in message
news:OfeDgG1SFHA.3244@.TK2MSFTNGP15.phx.gbl...
> I am setting up replication for the first time and, as can be expected,
> running into problems. The scenario is a server in one domain that will be
a
> publisher using a remote distributor in a different domain. When trying to
> configure the publisher server, I keep getting a 'server does not exist or
> access denied' when it tries to configure the remote distributor. I know
the
> server exists, so I figure I have messed up something in the way they
> authenticate with each other. There is a 2-way trust between the domains
and
> none of the SQL or SQL Agent Services are running under the system
accounts.
> Any suggestions or links to good info would be most appreciated.
> Thanks,
> Bob Castleman
> DBA Poseur
>
|||THanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OZfKQG2SFHA.2128@.TK2MSFTNGP15.phx.gbl...
> have a look at this link
> http://support.microsoft.com/default...b;en-us;321822
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Bob Castleman" <nomail@.here> wrote in message
> news:OfeDgG1SFHA.3244@.TK2MSFTNGP15.phx.gbl...
> a
> the
> and
> accounts.
>

Authentication on 2003 server

Hi
I've installed 2003 server and RS with no problems. Also got Sharepoint
Services to run 100%. The only issue is that every time i click on a report a
username, password dialog appears. On XP this didn't happen. Everything is on
NT integrated security and my account is part of the admins group. Any ideas ?Does you Windows 2003 box belong to your login domain?
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Markus" <Markus@.discussions.microsoft.com> wrote in message
news:F0BD2F13-3140-403D-B823-FAA6E5CFD7C8@.microsoft.com...
> Hi
> I've installed 2003 server and RS with no problems. Also got Sharepoint
> Services to run 100%. The only issue is that every time i click on a
report a
> username, password dialog appears. On XP this didn't happen. Everything is
on
> NT integrated security and my account is part of the admins group. Any
ideas ?

Authentication

Hi! I have a problem enabling access to my 'sa' login to a couple of
application db's it fails each time. I think the problem lies herein:I am on
Sql 2005 BTW...When I go to server properties/security it shows,
appropriately, Mixed mode authentication. However, when I click on "View
Connection Properties" in the connection section of the server properties
page it shows windows authentiication. Therefore, I believe that when I try
to add the db's to the mapping for 'sa' the server is looking for a windows
account which of course does not exist. Any advice as to how I might proceed?
Thanks all...I do not want to create a windows account for sa...Adios...
Well, 'view connection properties' refers to your current conn props... so
that just means that you're connecting to the server with windows auth... it
has nothing to do with the server security setting.
Since you didn't really provide an error message I'll say one thing to check
would be whether sa owns the DB in question. You can't add an acct as a user
if that acct already owns the DB.
"Walt Herman" wrote:

> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I try
> to add the db's to the mapping for 'sa' the server is looking for a windows
> account which of course does not exist. Any advice as to how I might proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...
|||If you install SQL Server in Windows Authentication Mode, then change to
Mixed Authentication Mode, the sa account is disabled.
Use ALTER LOGIN to enable the account. For more information, see the topic
How to: Change Server Authentication Mode in Books Online.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walt Herman" <WaltHerman@.discussions.microsoft.com> wrote in message
news:4C4AED35-F285-477F-BE3C-8961098826E8@.microsoft.com...
> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
> on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I
> try
> to add the db's to the mapping for 'sa' the server is looking for a
> windows
> account which of course does not exist. Any advice as to how I might
> proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...

Authentication

Hi! I have a problem enabling access to my 'sa' login to a couple of
application db's it fails each time. I think the problem lies herein:I am on
Sql 2005 BTW...When I go to server properties/security it shows,
appropriately, Mixed mode authentication. However, when I click on "View
Connection Properties" in the connection section of the server properties
page it shows windows authentiication. Therefore, I believe that when I try
to add the db's to the mapping for 'sa' the server is looking for a windows
account which of course does not exist. Any advice as to how I might proceed
?
Thanks all...I do not want to create a windows account for sa...Adios...Well, 'view connection properties' refers to your current conn props... so
that just means that you're connecting to the server with windows auth... it
has nothing to do with the server security setting.
Since you didn't really provide an error message I'll say one thing to check
would be whether sa owns the DB in question. You can't add an acct as a use
r
if that acct already owns the DB.
"Walt Herman" wrote:

> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I tr
y
> to add the db's to the mapping for 'sa' the server is looking for a window
s
> account which of course does not exist. Any advice as to how I might proce
ed?
> Thanks all...I do not want to create a windows account for sa...Adios...|||If you install SQL Server in Windows Authentication Mode, then change to
Mixed Authentication Mode, the sa account is disabled.
Use ALTER LOGIN to enable the account. For more information, see the topic
How to: Change Server Authentication Mode in Books Online.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walt Herman" <WaltHerman@.discussions.microsoft.com> wrote in message
news:4C4AED35-F285-477F-BE3C-8961098826E8@.microsoft.com...
> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
> on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I
> try
> to add the db's to the mapping for 'sa' the server is looking for a
> windows
> account which of course does not exist. Any advice as to how I might
> proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...

Auotmated Stored Procedure

Hi
Is there is any way to run the stored procedure automatically from master
database on daily at particular time.
Thanks is advance
NizhamYes, create a SQL Server Agent job with a TSQL jobstep which executes the pr
ocedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Junkmn" <junkmn@.gmail.com> wrote in message news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl..
.
> Hi
> Is there is any way to run the stored procedure automatically from master
> database on daily at particular time.
> Thanks is advance
> Nizham
>|||Yes. Create a job and schedule it to run at a specific time. From EM, you
can do this by using the Jobs node under Management. ( Make sure the SQL
Agent service is enabled & started )
Anith|||Thanks Tibor Karaszi
I hope you are expert on that If possible will you please send me the sample
code to run the stored procedure sp_XYZ to be run at 1.00 am
Regards
Nizham
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Yes, create a SQL Server Agent job with a TSQL jobstep which executes the
> procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Junkmn" <junkmn@.gmail.com> wrote in message
> news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl...|||It's much easier to use the GUI than to write the code for you. Have a look
at SQL Server Agent topics in Books Online. You can also see a brief
example here:
http://www.aspfaq.com/2403
"Junkmn" <junkmn@.gmail.com> wrote in message
news:O03vlkLMGHA.668@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor Karaszi
> I hope you are expert on that If possible will you please send me the
> sample code to run the stored procedure sp_XYZ to be run at 1.00 am
> Regards
> Nizham
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
>

Auditing IN SQL 2k

Hello
I need to record the activities/changes in certain tables in my application
including who effected the change,time, the field that was changed, the new
and old value of the field. How do i go about doing this? I need to do this
without actually making code changes in my application.
Would be quite helpful if someone could help me with clear steps as to how
this is to be done as I'm a newbie to this field.
Thanks in advance
Regds
Rachel
Take a look at
http://www.microsoft.com/technet/sec.../sql2kaud.mspx
You could also do a Google search on "SQL Server 2000 Auditing" which will
turn up alternatives. For example:
http://www.akadia.com/services/sqlsr..._auditing.html
Hal Berenson, President
PredictableIT, LLC
"rachel" <rachel@.discussions.microsoft.com> wrote in message
news:7F547B93-A7FF-4200-93DB-8980A1E8A53E@.microsoft.com...
> Hello
> I need to record the activities/changes in certain tables in my
> application
> including who effected the change,time, the field that was changed, the
> new
> and old value of the field. How do i go about doing this? I need to do
> this
> without actually making code changes in my application.
> Would be quite helpful if someone could help me with clear steps as to how
> this is to be done as I'm a newbie to this field.
> Thanks in advance
> Regds
> Rachel
>
>

Auditing IN SQL 2k

Hello
I need to record the activities/changes in certain tables in my application
including who effected the change,time, the field that was changed, the new
and old value of the field. How do i go about doing this? I need to do this
without actually making code changes in my application.
Would be quite helpful if someone could help me with clear steps as to how
this is to be done as I'm a newbie to this field.
Thanks in advance
Regds
RachelTake a look at
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx
You could also do a Google search on "SQL Server 2000 Auditing" which will
turn up alternatives. For example:
http://www.akadia.com/services/sqlsrv_table_auditing.html
Hal Berenson, President
PredictableIT, LLC
"rachel" <rachel@.discussions.microsoft.com> wrote in message
news:7F547B93-A7FF-4200-93DB-8980A1E8A53E@.microsoft.com...
> Hello
> I need to record the activities/changes in certain tables in my
> application
> including who effected the change,time, the field that was changed, the
> new
> and old value of the field. How do i go about doing this? I need to do
> this
> without actually making code changes in my application.
> Would be quite helpful if someone could help me with clear steps as to how
> this is to be done as I'm a newbie to this field.
> Thanks in advance
> Regds
> Rachel
>
>

Auditing IN SQL 2k

Hello
I need to record the activities/changes in certain tables in my application
including who effected the change,time, the field that was changed, the new
and old value of the field. How do i go about doing this? I need to do this
without actually making code changes in my application.
Would be quite helpful if someone could help me with clear steps as to how
this is to be done as I'm a newbie to this field.
Thanks in advance
Regds
RachelTake a look at
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
You could also do a Google search on "SQL Server 2000 Auditing" which will
turn up alternatives. For example:
http://www.akadia.com/services/sqls...e_auditing.html
Hal Berenson, President
PredictableIT, LLC
"rachel" <rachel@.discussions.microsoft.com> wrote in message
news:7F547B93-A7FF-4200-93DB-8980A1E8A53E@.microsoft.com...
> Hello
> I need to record the activities/changes in certain tables in my
> application
> including who effected the change,time, the field that was changed, the
> new
> and old value of the field. How do i go about doing this? I need to do
> this
> without actually making code changes in my application.
> Would be quite helpful if someone could help me with clear steps as to how
> this is to be done as I'm a newbie to this field.
> Thanks in advance
> Regds
> Rachel
>
>

Auditing Failed Logins

Hi,

I need to be able to get info about failed login attempts which need to be running all the time and logging info such as hostname , application name, host machine, etc. The best way I can see to do this is by crerating a trace. Then loading the info from the trace into a table for later use by using fn_trace_gettable. But this requires that the trace be stopped and the definition removed for the trace file to be populated.

Is there a way to be able to log this info constantly into a table without having to stop and then recreate another trace. Also it requires that the table created by the function is dropped.

Anyone any idears?

Thanks

Hello,

What version of SQL Server are you using? If 2005, you have the option of using server-level triggers.

Cheers,

Rob

|||

Hi,

Thanks for your reply. We are using 2000.

Thanks

Bob

|||

With 2000, unless you are using a 3rd party product, then you can only get that information with Profiler. There's a reason that you don't see that information with SQL Server though: it's so easy to spoof/change the hostname/appname information that it's a joke. Those are just ADO connection string values; you can set them to anything you want so I wouldn't want to make critical decisions like "Should I fire Bob over his failed logins?" over it.

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/

|||

Hi Bob,

Actually, if this information is crucial, you can enable C2 auditing on the mssql instance...although this should not be done without a thorough understanding of the concequences (performance being one of those considerations).

Have a look at the below links to get started:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx

https://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

Cheers,

Rob

Audit Trace Files

I am using server side traces to enable SQL Server 2000 and 2005 auditing.
Every time I start the server, I define a new trace file for auditing. So
till now I have configured around 100 trace files.
Now I want to get the list of all the trace files (which are at different
location in my PC), using some mechanism. Please let me know if there is a
function or procedure which will give me all the trace files that have been
configured for auditing in SQL 2000 and 2005.
I found a function fn_trace_getinfo, which returns information about a
specified trace or all existing traces, but I want all the trace files, whic
h
have been created till now, irrespective of whether they are active or not.
Is there a function in SQL which would help me in this?Munish Narula (munish.narula@.wipro.com) writes:
> I am using server side traces to enable SQL Server 2000 and 2005 auditing.
> Every time I start the server, I define a new trace file for auditing. So
> till now I have configured around 100 trace files.
> Now I want to get the list of all the trace files (which are at
> different location in my PC), using some mechanism. Please let me know
> if there is a function or procedure which will give me all the trace
> files that have been configured for auditing in SQL 2000 and 2005.
> I found a function fn_trace_getinfo, which returns information about a
> specified trace or all existing traces, but I want all the trace files,
> which have been created till now, irrespective of whether they are
> active or not.
> Is there a function in SQL which would help me in this?
I don't think so. As I understand it, once a trace is done, SQL Server
forgets all about it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
In SQL Server 2005 take a look at sys.traces system table
"Munish Narula" <munish.narula@.wipro.com> wrote in message
news:4412799B-EE0B-431B-92E2-F51988471166@.microsoft.com...
>I am using server side traces to enable SQL Server 2000 and 2005 auditing.
> Every time I start the server, I define a new trace file for auditing. So
> till now I have configured around 100 trace files.
> Now I want to get the list of all the trace files (which are at different
> location in my PC), using some mechanism. Please let me know if there is a
> function or procedure which will give me all the trace files that have
> been
> configured for auditing in SQL 2000 and 2005.
> I found a function fn_trace_getinfo, which returns information about a
> specified trace or all existing traces, but I want all the trace files,
> which
> have been created till now, irrespective of whether they are active or
> not.
> Is there a function in SQL which would help me in this?|||Hi,
Thanks for the response.
But this again does not solve my problem. The sys.traces catalog view
contains the current running traces on the system. This view is intended as
a
replacement for the fn_trace_getinfo function.
I want to get all the trace files that have been created so far.
"Uri Dimant" wrote:

> Hi
> In SQL Server 2005 take a look at sys.traces system table
>
>
> "Munish Narula" <munish.narula@.wipro.com> wrote in message
> news:4412799B-EE0B-431B-92E2-F51988471166@.microsoft.com...
>
>|||Munish
I'm affraid you cannot. Once trace is stopped SQL Server does not keep any
info about it
"Munish Narula" <munish.narula@.wipro.com> wrote in message
news:07439C49-276C-4677-B975-0341882D2C0F@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for the response.
> But this again does not solve my problem. The sys.traces catalog view
> contains the current running traces on the system. This view is intended
> as a
> replacement for the fn_trace_getinfo function.
> I want to get all the trace files that have been created so far.
>
>
> "Uri Dimant" wrote:
>|||You can get the file path only for active and stopped traces. As Erland and
Uri mentioned, no record is kept after the trace is deleted.
It's not hard to develop a process to manage trace files, though. For
example, I have a daily SSIS package that restarts a trace and copies the
files to an archive folder.
Hope this helps.
Dan Guzman
SQL Server MVP
"Munish Narula" <munish.narula@.wipro.com> wrote in message
news:4412799B-EE0B-431B-92E2-F51988471166@.microsoft.com...
>I am using server side traces to enable SQL Server 2000 and 2005 auditing.
> Every time I start the server, I define a new trace file for auditing. So
> till now I have configured around 100 trace files.
> Now I want to get the list of all the trace files (which are at different
> location in my PC), using some mechanism. Please let me know if there is a
> function or procedure which will give me all the trace files that have
> been
> configured for auditing in SQL 2000 and 2005.
> I found a function fn_trace_getinfo, which returns information about a
> specified trace or all existing traces, but I want all the trace files,
> which
> have been created till now, irrespective of whether they are active or
> not.
> Is there a function in SQL which would help me in this?

Audit log not logging

Thank you for your time.
I have a database that i need to have auditing on. The database was moved
to a larger server. I had auditing on before the move, but now in my SQL
Sercer audit log I only have the error message 15457 Severity: 0, State: 1.
I need auditing on for this database, and have not been able to find
anything online that explaines what might have caused the logging to stop or
how to fix this issue. The users can still login, but SQL Server isn't
auditing it anymore.
Thank you again for your time.
What were you auditing? Perhaps there was a startup param set on your old
server. Perhaps you chose to auditing logins via Enterprise Manager (right
click on the server name, properties, Security tab).
Keith
"Marc M" <Marc M@.discussions.microsoft.com> wrote in message
news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> Thank you for your time.
> I have a database that i need to have auditing on. The database was moved
> to a larger server. I had auditing on before the move, but now in my SQL
> Sercer audit log I only have the error message 15457 Severity: 0, State:
1.
> I need auditing on for this database, and have not been able to find
> anything online that explaines what might have caused the logging to stop
or
> how to fix this issue. The users can still login, but SQL Server isn't
> auditing it anymore.
> Thank you again for your time.
|||Thank you for the suggestion to check the old server, I'll look at that to
see if there is anything running that we didn't get moved.
We were trying to audit the logins and logouts of the users of the database.
I already have in place the auditing through Enterprise Manager, and were
looking at it throught the logs associated with that. I really apprecitate
the level of detail provided on how to activeate it and would apprecitate
that level of detail on the solution for why we are only getting the error
15457 in those logs.
Thank you for your help.
"Keith Kratochvil" wrote:

> What were you auditing? Perhaps there was a startup param set on your old
> server. Perhaps you chose to auditing logins via Enterprise Manager (right
> click on the server name, properties, Security tab).
> --
> Keith
>
> "Marc M" <Marc M@.discussions.microsoft.com> wrote in message
> news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> 1.
> or
>

Audit log not logging

Thank you for your time.
I have a database that i need to have auditing on. The database was moved
to a larger server. I had auditing on before the move, but now in my SQL
Sercer audit log I only have the error message 15457 Severity: 0, State: 1.
I need auditing on for this database, and have not been able to find
anything online that explaines what might have caused the logging to stop or
how to fix this issue. The users can still login, but SQL Server isn't
auditing it anymore.
Thank you again for your time.As there is two types of auditing, have a look at the
following article
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_security_2ard.asp, if its
C2 auditing then it needs to be used in the same line as
starting your SQL Server service.
Peter
"Denial ain't just a river in Egypt."
Mark Twain
>--Original Message--
>Thank you for your time.
>I have a database that i need to have auditing on. The
database was moved
>to a larger server. I had auditing on before the move,
but now in my SQL
>Sercer audit log I only have the error message 15457
Severity: 0, State: 1.
>I need auditing on for this database, and have not been
able to find
>anything online that explaines what might have caused the
logging to stop or
>how to fix this issue. The users can still login, but
SQL Server isn't
>auditing it anymore.
>Thank you again for your time.
>.
>|||What were you auditing? Perhaps there was a startup param set on your old
server. Perhaps you chose to auditing logins via Enterprise Manager (right
click on the server name, properties, Security tab).
--
Keith
"Marc M" <Marc M@.discussions.microsoft.com> wrote in message
news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> Thank you for your time.
> I have a database that i need to have auditing on. The database was moved
> to a larger server. I had auditing on before the move, but now in my SQL
> Sercer audit log I only have the error message 15457 Severity: 0, State:
1.
> I need auditing on for this database, and have not been able to find
> anything online that explaines what might have caused the logging to stop
or
> how to fix this issue. The users can still login, but SQL Server isn't
> auditing it anymore.
> Thank you again for your time.|||Thank you for the article. That is what I was trying to audit. I had the
auditing working before the database was moved, but now I only get the error
message in the audit log. I didn't know about the SQL Profiler before.
Thanks for pointing that out.
"Peter The Spate" wrote:
> As there is two types of auditing, have a look at the
> following article
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/adminsql/ad_security_2ard.asp, if its
> C2 auditing then it needs to be used in the same line as
> starting your SQL Server service.
> Peter
> "Denial ain't just a river in Egypt."
> Mark Twain
>
> >--Original Message--
> >Thank you for your time.
> >I have a database that i need to have auditing on. The
> database was moved
> >to a larger server. I had auditing on before the move,
> but now in my SQL
> >Sercer audit log I only have the error message 15457
> Severity: 0, State: 1.
> >I need auditing on for this database, and have not been
> able to find
> >anything online that explaines what might have caused the
> logging to stop or
> >how to fix this issue. The users can still login, but
> SQL Server isn't
> >auditing it anymore.
> >Thank you again for your time.
> >.
> >
>|||Thank you for the suggestion to check the old server, I'll look at that to
see if there is anything running that we didn't get moved.
We were trying to audit the logins and logouts of the users of the database.
I already have in place the auditing through Enterprise Manager, and were
looking at it throught the logs associated with that. I really apprecitate
the level of detail provided on how to activeate it and would apprecitate
that level of detail on the solution for why we are only getting the error
15457 in those logs.
Thank you for your help.
"Keith Kratochvil" wrote:
> What were you auditing? Perhaps there was a startup param set on your old
> server. Perhaps you chose to auditing logins via Enterprise Manager (right
> click on the server name, properties, Security tab).
> --
> Keith
>
> "Marc M" <Marc M@.discussions.microsoft.com> wrote in message
> news:A89E9DBD-9C96-400B-8208-6AA7EF86141F@.microsoft.com...
> > Thank you for your time.
> > I have a database that i need to have auditing on. The database was moved
> > to a larger server. I had auditing on before the move, but now in my SQL
> > Sercer audit log I only have the error message 15457 Severity: 0, State:
> 1.
> > I need auditing on for this database, and have not been able to find
> > anything online that explaines what might have caused the logging to stop
> or
> > how to fix this issue. The users can still login, but SQL Server isn't
> > auditing it anymore.
> > Thank you again for your time.
>

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so muchYou may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Your problem seems to be @.options = 3. That is not documented. I tried with 2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that definition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much

Audit

I want to register into a table each time a user creates, modifies or deletes any object in a database. It's not possible to add a trigger to the sysobjects table. What can I do?SQL 2005 will have that feature...the best I can tell you is to not allow them DDL Priviliges, and have them run all the DDL request through you...

Attribute relation error

Hi! I have changed some attribut relations in a time dimension user hierarchy(SSAS2005 SP1), from flexible to rigid. When I try to deploy this change to the server the whole process stops with these strange error messages:

"Error 1 File system error:
Error opening file; \\?\D:\MSSQL\MSSQL.2\OLAP\Data\SalesStatistics.0.db\Sales Statistics 2.0.cub\Sales Master Cube.0.det\Sales Master Cube.0.prt\35.agg.rigid.data
is not a disk file or file is not accessible."

The next error repeats several times for all named sets I have built for the time dimension:

"Error 2 MdxScript(Sales Statistics Facilities) (174, 6)
The dimension '[RecentMonth]' was not found in the cube when the string, [RecentMonth], was parsed. 0 0 "

[RecentMonth] is actually a named set, that exist, with the following code:

Tail(Filter([Time].[Time_Calendar].[Month].Members,

([Time].[Time_Calendar].Currentmember,[Measures].[Sales SEK])>0))

My other named sets use this set as a reference like Last12Months and so on.

It is the first named set in a collection of named sets. All named sets are placed last in the scripting tab i BI-Dev Studio.

Any ideas?

Regards

Thomas Ivarsson

Problem is solved. I removed the named sets for the time dimension, processed the cube without them and processed again with the the same named sets.

SSAS 2005 probably use an internal identifier for the dimension that the named sets are mapped to. Changing attribut relation types probably means a new identifier/version of the dimension.

/Thomas Ivarsson

Attribute Key Cannot Be Found: Single Underlying Table!

I've spent quite a bit of time searching this topic and have not come up with an answer that would help my situation.

I'm building a cube from a single table. That table is used to build both the attributes and measures. I sometimes get the "attribute key cannot be found" error even though queries prove that the data indeed there. Often all that needs to happen to fix the problem is to simply reprocess the cube without making any changes.

Any ideas as to how I could fix this problem? It causes a problem as the cube is scheduled and that is failing. I can't be rebuilding the cube manually each day.

Hello! Even if you have fact table dimensions this problem can be related to that you must always process the dimensions first and the rest of the cube later. If you use MOLAP for storage the dimensions will be built as separate objects even if you use a single table as source.

If you process the cube and have activated "process related objects" in the advanced settings dialogue you can process the cube and the dimensions will be processed first.

HTH

Thomas Ivarsson

|||Perfect! That you so much for your help, Thomas. That makes good sense. I've set the Integration Service project to Process Related Objects.

Thanks Again,

Robin Sarac

edit: Spelling mistakes...