set affinity for SqlExpress: running

Showing posts with label running. Show all posts

Wednesday, March 28, 2012

How to show running totals

I have a cube with many dimensions including one called WorkDate YMD. I also have a measure called Hours.

At the moment I can see hours against any level of the WorkDate dimension, but I have a requirement now for running totals.

For example, the data at the moment is this

Jan 4 hours, Feb 5 hours March 6 Hours

As well as the actual Hours the users now want to see the running totals.

So the data will be

Jan 4 hours, Feb 9 hours March 15 hours

I know this should be easy as a calculated member in the cube using MDX and therefore visible in excel pivot tables but I cannot see how to accomplish this correctly ans easily.

TIA

Hi. If you're WorkDate dimension is of type "Time" you can use the YTD() function as follows:

YTD(WorkDate.WorkDate.CurrentMember, Measures.Hours)

If WorkDate is NOT a type Time dimension you can get the same functionality from:

SUM(PeriodsToDate([WorkDate].[WorkDate].<Level name>, [WorkDate].[WorkDate].[<level name>].CurrentMember), Measures.Hours)

Hope this helps.

PGoldy

|||

Thanks for the info, but I don't think I explained the issue properly, there is another dimension of project so the work date rather than being Year to Date cumulative totals it is cumalitive from the start of the project so for example Nov 5 hours, Dec 2 Hours, Jan 4 Hours Total 11 hours should be viewed as Nov 5 hours, Dec 7 hours, Jan 11 hours

btw, yes WorkDate is a time dimension of Year / Month / Day and my users want to see the cumulative totals on a monthly basis.

TIA

|||

Hi Thanks for further information. Sounds like what you want is a running total for each month which starts at the project begin date. Here's a query which November as the project start, and provides a running total through April (spanning the year boundary).

WITH MEMBER Measures.[Running Total] AS
'
SUM(WorkDate.WorkDate.[November]:WorkDate.WorkDate.CurrentMember, Measures.Hours)
'
SELECT
{WorkDate.WorkDate.November
,WorkDate.WorkDate.December
,WorkDate.WorkDate.January
,WorkDate.WorkDate.February
,WorkDate.WorkDate.March
,WorkDate.WorkDate.April} ON COLUMNS,
{Measures.[Running Total]} ON ROWS
FROM [Your Cube]

Hope this helps.

PGoldy

|||

Hi Paul,

I think I need to push my boss harder to let me go onto an MDX course.

The cube I have is against MS Project server data, so the users want to see cumulative hours for many selected of grouped projects or tasks, so I cannot hard code November to April unfortunately.

I guess if possible I'm after a simple mdx query that I can drop into the calculated section in SS2000 Analysis Services so that it appears as a measure that they can select with my normal measure of Hours back in Excel, and so that they can do a line graph and show how the projects hours are growing.

Thanks for your help

Neil.

|||

Hi Neil. Thanks for the explanation. I provided very query specific solution to your uestion - as if you are using Reporting Services. Your explanation shows that what I gave you isn't very useful - especially in Excel where you don't control the specific MDX. Sorry about that. In ieu of an MDX course you may want to pick up Spoffords book on MDX Solutions (http://www.amazon.com/s/ref=nb_ss_gw/102-1292512-3894526?url=search-alias%3Daps&field-keywords=MDX+spofford&Go.x=12&Go.y=7). This is the SL 2005 edition, bt the MDX basics didn't change from SQL 2000, and if you search the net you can probably find e SQL2000 edition.

Good Luck.

PGoldy

|||

And here's an old OLAP NewsGroup post from George Spofford, explaining how to set up a "time analysis" dimension in As 2000 - which is a precursor to the "Time Intelligence Calculations" in AS 2005. To show running totals from the beginning, you could add a [Time Series].[ATD] calculation, using PeriodsToDate ( [Time].[All], [Time].CurrentMember ) ...

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/4c59dc7518614323?hl=en&

microsoft.public.sqlserver.olap > Can MDX tell if a member is calculated member?

From:		George Spofford - view profile
Date:		Fri, Aug 23 2002 8:19 am
Email:		George Spofford <geo...@.dsslab.com>
Groups:		microsoft.public.sqlserver.olap

Not yet rated

Rating:

show options

The direct answer is: Sort of. However, your simplest solution is to implement a "time analysis utility
dimension", which moves your Current, MTD, QTD, YTD calculations into a separate dimension from time.

First, the interesting part. The expression

Intersect (Dimension.Members, { [Dimension].[@.X@.] }).Count

will return 0 if @.X@. refers to a calc member, 1 otherwise.

Second, the possibly better solution:

A time analysis utility dimension has no all level and 1 real member in a dimension table named something
like "Current" with a key value like 0 or 1. You can create and process the dimension as usual. Bring the
dimension into the cube, join it to the fact table on any column of the fact table, and set the member
key in the cube the constant 0 or 1 (matching Current's key).

Add calculated members to the cube on this dimension:

CREATE MEMBER [Time Series].[YTD] AS
'Aggregate (
PeriodsToDate (
[Time].[Year],
[Time].CurrentMember
),
([Time Series].[Current])
)'

CREATE MEMBER [Time Series].[QTD] AS
'Aggregate (
PeriodsToDate (
[Time].[Quarter],
[Time].CurrentMember
),
([Time Series].[Current])
)'

A query for YTD sales at August 2002 is a query for the tuple
([Measures].[Sales], [Time Series].[YTD], [Time].[Aug 2002])

|||

Thanks guys,

After all this help, I've been able to get what I'm after, the mdx for running totals is

sum(periodstodate([work date].[(all)]),[measures].[hours])

where [work date] is the date dimension and [hours] are the measure to be cumulated.

Thanks

Neil.

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
Ellis
Ellis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison
sql

Monday, March 26, 2012

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
EllisEllis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
EllisEllis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

Friday, March 23, 2012

How to setup Operator for sending alerts?

Hello,
I've setup an Operator using the management studio and properly configured
it's settings. However, when I test it by running a job that fires an alert
to the Operator, the alert fails and gives me back the following error.
The job succeeded. The Job was invoked by User XXX. The last step to run
was step 1 (X). NOTE: Failed to notify 'John Doe' via email. NOTE: Failed
to notify 'John Doe' via pager.
Not sure if this helps but I've properly setup Database Mail via the wizard
along with a default profile and have successfully sent e-mails using the
system sproc for sending emails.
Is there a guide for properly setting up and testing Operator alerts?
P.S - I am using SQL 2005 April CTP. Didnt post on the SQL 2005 forums cause
they are practically dead.
Johnny
I'd run profiler to see what is happening. Does it *try* to send email? If it does, does the TSQL it
submit execute correctly from a query window?
I had problems with Agent Mail in June CTP, so I decided to wait with Agent Mail testing to a later
build.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:E671A826-D5C3-45AA-95AA-E7EF913E84AE@.microsoft.com...
> Hello,
> I've setup an Operator using the management studio and properly configured
> it's settings. However, when I test it by running a job that fires an alert
> to the Operator, the alert fails and gives me back the following error.
> The job succeeded. The Job was invoked by User XXX. The last step to run
> was step 1 (X). NOTE: Failed to notify 'John Doe' via email. NOTE: Failed
> to notify 'John Doe' via pager.
> Not sure if this helps but I've properly setup Database Mail via the wizard
> along with a default profile and have successfully sent e-mails using the
> system sproc for sending emails.
> Is there a guide for properly setting up and testing Operator alerts?
> P.S - I am using SQL 2005 April CTP. Didnt post on the SQL 2005 forums cause
> they are practically dead.
> Johnny
|||Yahtzee!!!!!!! I found the problem, right before I was getting ready to give
up. I put together some instructions on how to get it to work for those
interested.
Note: With SQL Server Agent Alerts, you have the option of using "Database
Mail" (recommended) or the soon to be removed "SQL Mail" as the mail system.
These instructions show you how to use "Database Mail".
1. Enable "Database Mail" by using the "SQL Server 2005 Surface Area
Configuration" tool.
2. Install "Database Mail" objects in the msdb database.
3. Create a "Database Mail" profile and name it "Default Profile" or
whatever you like.
NOTE: The next step is to tell "SQL Server Agent" that you want to use
"Database Mail" as the mail system and specify a default profile to use. You
are suppose to do this by going to the Alert System page in the SQL Server
Agent Properties. However, the "Database Mail" option is not available in the
drop down menu and therefore you are unable to properly configure this, even
after rebooting the server. We will have to bypass the "UI" provided by the
SQL Server Management Studio and go straight to the registry to specify our
settings.
4. Open the registry editor by going to Start > Run > regedit.
5. Browse to the "SQLServerAgent" section. The path is something like
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microso ft SQL
Server\MSSQL.1\SQLServerAgent".
6. Modify the following entries with the corresponding values listed below.
KeyValue
UseDatabaseMail1
DatabaseMailProfileDefault Profile
7. Restart the "SQL Server Agent" service and voila!!
- Johnny
"Tibor Karaszi" wrote:

> I'd run profiler to see what is happening. Does it *try* to send email? If it does, does the TSQL it
> submit execute correctly from a query window?
> I had problems with Agent Mail in June CTP, so I decided to wait with Agent Mail testing to a later
> build.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:E671A826-D5C3-45AA-95AA-E7EF913E84AE@.microsoft.com...
>

How to setup Operator for sending alerts?

Hello,
I've setup an Operator using the management studio and properly configured
it's settings. However, when I test it by running a job that fires an alert
to the Operator, the alert fails and gives me back the following error.
The job succeeded. The Job was invoked by User XXX. The last step to run
was step 1 (X). NOTE: Failed to notify 'John Doe' via email. NOTE: Failed
to notify 'John Doe' via pager.
Not sure if this helps but I've properly setup Database Mail via the wizard
along with a default profile and have successfully sent e-mails using the
system sproc for sending emails.
Is there a guide for properly setting up and testing Operator alerts?
P.S - I am using SQL 2005 April CTP. Didnt post on the SQL 2005 forums cause
they are practically dead.
JohnnyI'd run profiler to see what is happening. Does it *try* to send email? If i
t does, does the TSQL it
submit execute correctly from a query window?
I had problems with Agent Mail in June CTP, so I decided to wait with Agent
Mail testing to a later
build.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:E671A826-D5C3-45AA-95AA-E7EF913E84AE@.microsoft.com...
> Hello,
> I've setup an Operator using the management studio and properly configured
> it's settings. However, when I test it by running a job that fires an aler
t
> to the Operator, the alert fails and gives me back the following error.
> The job succeeded. The Job was invoked by User XXX. The last step to run
> was step 1 (X). NOTE: Failed to notify 'John Doe' via email. NOTE: Faile
d
> to notify 'John Doe' via pager.
> Not sure if this helps but I've properly setup Database Mail via the wizar
d
> along with a default profile and have successfully sent e-mails using the
> system sproc for sending emails.
> Is there a guide for properly setting up and testing Operator alerts?
> P.S - I am using SQL 2005 April CTP. Didnt post on the SQL 2005 forums cau
se
> they are practically dead.
> Johnny|||Yahtzee!!!!!!! I found the problem, right before I was getting ready to give
up. I put together some instructions on how to get it to work for those
interested.
Note: With SQL Server Agent Alerts, you have the option of using "Database
Mail" (recommended) or the soon to be removed "SQL Mail" as the mail system.
These instructions show you how to use "Database Mail".
1. Enable "Database Mail" by using the "SQL Server 2005 Surface Area
Configuration" tool.
2. Install "Database Mail" objects in the msdb database.
3. Create a "Database Mail" profile and name it "Default Profile" or
whatever you like.
NOTE: The next step is to tell "SQL Server Agent" that you want to use
"Database Mail" as the mail system and specify a default profile to use. You
are suppose to do this by going to the Alert System page in the SQL Server
Agent Properties. However, the "Database Mail" option is not available in th
e
drop down menu and therefore you are unable to properly configure this, even
after rebooting the server. We will have to bypass the "UI" provided by the
SQL Server Management Studio and go straight to the registry to specify our
settings.
4. Open the registry editor by going to Start > Run > regedit.
5. Browse to the "SQLServerAgent" section. The path is something like
" HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL.1\SQLServerAgent".
6. Modify the following entries with the corresponding values listed below.
Key Value
----
UseDatabaseMail 1
DatabaseMailProfile Default Profile
7. Restart the "SQL Server Agent" service and voila!!
- Johnny
"Tibor Karaszi" wrote:

> I'd run profiler to see what is happening. Does it *try* to send email? If
it does, does the TSQL it
> submit execute correctly from a query window?
> I had problems with Agent Mail in June CTP, so I decided to wait with Agen
t Mail testing to a later
> build.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:E671A826-D5C3-45AA-95AA-E7EF913E84AE@.microsoft.com...
>

How to setup Operator for sending alerts?

Hello,
I've setup an Operator using the management studio and properly configured
it's settings. However, when I test it by running a job that fires an alert
to the Operator, the alert fails and gives me back the following error.
The job succeeded. The Job was invoked by User XXX. The last step to run
was step 1 (X). NOTE: Failed to notify 'John Doe' via email. NOTE: Failed
to notify 'John Doe' via pager.
Not sure if this helps but I've properly setup Database Mail via the wizard
along with a default profile and have successfully sent e-mails using the
system sproc for sending emails.
Is there a guide for properly setting up and testing Operator alerts?
P.S - I am using SQL 2005 April CTP. Didnt post on the SQL 2005 forums cause
they are practically dead.
JohnnyI'd run profiler to see what is happening. Does it *try* to send email? If it does, does the TSQL it
submit execute correctly from a query window?
I had problems with Agent Mail in June CTP, so I decided to wait with Agent Mail testing to a later
build.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:E671A826-D5C3-45AA-95AA-E7EF913E84AE@.microsoft.com...
> Hello,
> I've setup an Operator using the management studio and properly configured
> it's settings. However, when I test it by running a job that fires an alert
> to the Operator, the alert fails and gives me back the following error.
> The job succeeded. The Job was invoked by User XXX. The last step to run
> was step 1 (X). NOTE: Failed to notify 'John Doe' via email. NOTE: Failed
> to notify 'John Doe' via pager.
> Not sure if this helps but I've properly setup Database Mail via the wizard
> along with a default profile and have successfully sent e-mails using the
> system sproc for sending emails.
> Is there a guide for properly setting up and testing Operator alerts?
> P.S - I am using SQL 2005 April CTP. Didnt post on the SQL 2005 forums cause
> they are practically dead.
> Johnny|||Yahtzee!!!!!!! I found the problem, right before I was getting ready to give
up. I put together some instructions on how to get it to work for those
interested.
Note: With SQL Server Agent Alerts, you have the option of using "Database
Mail" (recommended) or the soon to be removed "SQL Mail" as the mail system.
These instructions show you how to use "Database Mail".
1. Enable "Database Mail" by using the "SQL Server 2005 Surface Area
Configuration" tool.
2. Install "Database Mail" objects in the msdb database.
3. Create a "Database Mail" profile and name it "Default Profile" or
whatever you like.
NOTE: The next step is to tell "SQL Server Agent" that you want to use
"Database Mail" as the mail system and specify a default profile to use. You
are suppose to do this by going to the Alert System page in the SQL Server
Agent Properties. However, the "Database Mail" option is not available in the
drop down menu and therefore you are unable to properly configure this, even
after rebooting the server. We will have to bypass the "UI" provided by the
SQL Server Management Studio and go straight to the registry to specify our
settings.
4. Open the registry editor by going to Start > Run > regedit.
5. Browse to the "SQLServerAgent" section. The path is something like
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\SQLServerAgent".
6. Modify the following entries with the corresponding values listed below.
Key Value
----
UseDatabaseMail 1
DatabaseMailProfile Default Profile
7. Restart the "SQL Server Agent" service and voila!!
- Johnny
"Tibor Karaszi" wrote:
> I'd run profiler to see what is happening. Does it *try* to send email? If it does, does the TSQL it
> submit execute correctly from a query window?
> I had problems with Agent Mail in June CTP, so I decided to wait with Agent Mail testing to a later
> build.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:E671A826-D5C3-45AA-95AA-E7EF913E84AE@.microsoft.com...
> > Hello,
> >
> > I've setup an Operator using the management studio and properly configured
> > it's settings. However, when I test it by running a job that fires an alert
> > to the Operator, the alert fails and gives me back the following error.
> >
> > The job succeeded. The Job was invoked by User XXX. The last step to run
> > was step 1 (X). NOTE: Failed to notify 'John Doe' via email. NOTE: Failed
> > to notify 'John Doe' via pager.
> >
> > Not sure if this helps but I've properly setup Database Mail via the wizard
> > along with a default profile and have successfully sent e-mails using the
> > system sproc for sending emails.
> >
> > Is there a guide for properly setting up and testing Operator alerts?
> >
> > P.S - I am using SQL 2005 April CTP. Didnt post on the SQL 2005 forums cause
> > they are practically dead.
> >
> > Johnny
>

How to SETUP MSDTC on Win2k3

I have documentation for Win2K which says run
comclust.exe to add MSDTC . Running the same brings up
the Admin window.
Please point me to some documentation which clearly
describes on how to setup MSDTC on win3K cluster
Thanks
http://msmvps.com/clustering/archive.../25/16672.aspx
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Alfred" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4bb81$6c5ac5c0$a301280a@.phx.gbl...
>I have documentation for Win2K which says run
> comclust.exe to add MSDTC . Running the same brings up
> the Admin window.
> Please point me to some documentation which clearly
> describes on how to setup MSDTC on win3K cluster
> Thanks
|||Hello Alfred,
Here are the two Microsoft Knowledge Base articles that you need to setup MSDTC on a Win2K3 cluster
How to configure Microsoft Distributed Transaction Coordinator (MSDTC)on a Windows Server 2003
http://support.microsoft.com/?kbid=301600
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?kbid=817064
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Wednesday, March 21, 2012

How to set up security to SQL 2005 Reporting Service?

I have Sql 2005 server (SSAS, SSRS, Database)installed on my workstation running on Windows XP Pro. SP2 for development only. However I need to migrate the reports to a "Production" enviroment next month which is set up with two servers running on Windows 2003 Server, one will serve as Data Server with Sql 2005 server and SSAS installed on it and the other will serve as Report Server with Sql 2005 Reporting Service. My question is how to set up the IIS on the Report Server to allow users from the domain to access the Report Manager (the reporting Service is intend to set up as an "Intranet" tool for the users ie No Internet access to the reports). A "dumpy" user will be set up at the Report Server for the data connection to the remote Sql 2005 Server. Also how to implement the security on the individual folder in the Report Manager. Currently I have folders set up for the department and I want to set up security only allow people from those depts to access their folder/subfolders. How to set up security on folder and subfolders in Report Manager? Can I set the security on my Windows XP machine to test it before I roll it out to the Production servers? Thanks.

here is a microsoft link that talks about role based security in reporting services...

http://msdn2.microsoft.com/en-us/library/ms156014.aspx

hope this helps...good luck!

Monday, March 19, 2012

How to set up connection to MS SQL remotely

If I let the pages (ASP) running on the third party server access my database (MS SQL) remotely, what should I do with my database server? I was told to open a port. If it is true, what is the number of the port I should open?
Thanks in advanceDANGER. Be careful. If you do this wrong you can open up your server for the world to access.

The default port is 1433, but if you are going to do this I reccomend changing that. You are also going to have to do stuff with your firewall. This is very dangerous and I would reccomend you explore other options. I imagine the peformance of the pages are going to suck as well.|||If you, against all advices, should decide to do this, you should definitely have a look at HTTP endpoints, and see if you could use a HTTP endpoint. If so, you should open ONE port (443) from ONE ip (the webserver) for ONE user, which authenticates using a rather secure method, as for instance a certificate.

This is the only solution I would think of that could come into consideration.

How to set up a report when a specific user makes changes to a database

Hi,
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
WarrenCan you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what you want to look into.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
>> 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||> Basically anything that would be considered a change to the database for auditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a combination of
server-side Profiler traces along with reading the transaction log (something which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I've listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for auditing purposes.
> Can you lead me in the right direction on setting up triggers or event notifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
>> when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly what type of changes you
want to be alerted for. Based on that decide whether you find a product that suit your need or if
you want to do it yourself. Depending on what type of changes you want to be alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an alert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot do
>> ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones I
>> know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am new
>> to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look
>> into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>> e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an
>> alert is sent when the dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server
>> once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They
>> use a combination of server-side Profiler traces along with reading the
>> transaction log (something which we cannot do ourselves) so performance
>> impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such
>> products, I've listed the ones I know of on my links page:
>> http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the
>> SQL Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>>
>|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that with a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it work. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>> Did you look at the product?
>> There's no pre-packaged solution for this. You need to determine exactly what type of changes you
>> want to be alerted for. Based on that decide whether you find a product that suit your need or if
>> you want to do it yourself. Depending on what type of changes you want to be alerted for, event
>> notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an alert is sent when the
>> dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot
>> do ourselves) so performance impact would be less than if doing it ourselves. Not to mention
>> the dev time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones
>> I know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am
>> new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE?
>> UPDATE? Something else? Triggers and possibly event notifications seems to be what you want
>> to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>>> e-mail when sending out alerts.
>>> I am however not able to find a way for an e-mail to be sent if a specific user makes a
>>> change to a database.
>>> Can somebody please point me in the right direction.
>>>
>>> Thank you,
>>> Warren
>>>
>>>
>>
>>
>>
>

How to set up a report when a specific user makes changes to a database

Monday, March 12, 2012

How to set the location of a linkedserver?

Hey folks,
I have a WinNT4 machine with the indexing service setup on it. It works.
Also I have a W2k machine running SQL Server 2000. I'd like to add the
indexing service on the WinNT4 machine as a linkedserver to the SQL2000 DB
on the W2k machine.
If the DB as well as indexing service are on the same machine I use this:
sp_addlinkedserver CVDOC, 'Index Server', 'MSIDXS', 'CV'
From reading online I understand that as a 5th parameter you can give the
storedproc a location for the linkedserver however I have not been able to
make this work.
What should that location parameter look like? UNC? Web?
On the NT4 server my documents to index are in C:\somefolder\CV\
The catalog name is CV and points to this location.
Any help would be very much appreciated.
Thanks for reading sofar,
Vincent.
From what I understand the catalogs and services are completely different,
so you can't query a NT 4.0 IS server or its catalog from Win2k.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
<koopman@.furore.com.removethis> wrote in message
news:eLi5QiUzEHA.1264@.TK2MSFTNGP10.phx.gbl...
> Hey folks,
> I have a WinNT4 machine with the indexing service setup on it. It works.
> Also I have a W2k machine running SQL Server 2000. I'd like to add the
> indexing service on the WinNT4 machine as a linkedserver to the SQL2000 DB
> on the W2k machine.
> If the DB as well as indexing service are on the same machine I use this:
> sp_addlinkedserver CVDOC, 'Index Server', 'MSIDXS', 'CV'
> From reading online I understand that as a 5th parameter you can give the
> storedproc a location for the linkedserver however I have not been able to
> make this work.
> What should that location parameter look like? UNC? Web?
> On the NT4 server my documents to index are in C:\somefolder\CV\
> The catalog name is CV and points to this location.
> Any help would be very much appreciated.
> Thanks for reading sofar,
> Vincent.
>
|||And what if I was using another w2k server instead of NT4?
What makes you think the catalogs and services are different? Something I
wrote or is that a fact because I use an NT4 server? I don't get this part
of your message...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eP6d5aXzEHA.2788@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> From what I understand the catalogs and services are completely different,
> so you can't query a NT 4.0 IS server or its catalog from Win2k.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> <koopman@.furore.com.removethis> wrote in message
> news:eLi5QiUzEHA.1264@.TK2MSFTNGP10.phx.gbl...
DB[vbcol=seagreen]
this:[vbcol=seagreen]
the[vbcol=seagreen]
to
>
|||Win2k to Win2k will work fine. Win2k to NT 4 or vice versa won't as the IS
server, the catalogs, and most importantly the providers are all different.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"news.micosoft.com" <koopman@.furore.com.removethis> wrote in message
news:eMAvcFYzEHA.2316@.TK2MSFTNGP15.phx.gbl...
> And what if I was using another w2k server instead of NT4?
> What makes you think the catalogs and services are different? Something I
> wrote or is that a fact because I use an NT4 server? I don't get this part
> of your message...
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eP6d5aXzEHA.2788@.TK2MSFTNGP15.phx.gbl...
> DB
> this:
> the
> to
>

Wednesday, March 7, 2012

How to set Merge Repl properties for multiple servers?

Hello...
Currently we have merge replication set up between SQL 2000 and mobile
devices running SQL CE 2.0...and some with SQL Mobile.
As part of the logic on the device, we have to set some properties for the
SqlCeReplication object. Some of these we make dynamic, like the
publication, login, etc.
However, we currently hard code the Publisher and InternetURL values to our
existing server like:
repl.Publisher = "MyMachineName"
repl.InternetURL =
"http://MyMachineName.domain.com/SqlRepl/sqlcesa20.dll"
My question or problem is this...what happens in the case of a failover? We
have a backup/redundant/mirrored server at a different geographic
location...obviously it has a different server name. For the devices in the
field, how do people handle this type of situation where the server failover
will occur automatically. Or, if we decide to move to a server farm...where
we have multiple Sql Servers...how can we make our mobile application
agnostic to the Publisher?
Thanks for any help or advice.
- will
The best way to do this is to put your publisher on a cluster.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:D241EC93-AFFF-4024-B342-99AF1C4E82D4@.microsoft.com...
> Hello...
> Currently we have merge replication set up between SQL 2000 and mobile
> devices running SQL CE 2.0...and some with SQL Mobile.
> As part of the logic on the device, we have to set some properties for the
> SqlCeReplication object. Some of these we make dynamic, like the
> publication, login, etc.
> However, we currently hard code the Publisher and InternetURL values to
our
> existing server like:
> repl.Publisher = "MyMachineName"
> repl.InternetURL =
> "http://MyMachineName.domain.com/SqlRepl/sqlcesa20.dll"
> My question or problem is this...what happens in the case of a failover?
We
> have a backup/redundant/mirrored server at a different geographic
> location...obviously it has a different server name. For the devices in
the
> field, how do people handle this type of situation where the server
failover
> will occur automatically. Or, if we decide to move to a server
farm...where
> we have multiple Sql Servers...how can we make our mobile application
> agnostic to the Publisher?
> Thanks for any help or advice.
> - will
|||Would that mean the Publisher and Distributor would be on different machines?
"Hilary Cotter" wrote:

> The best way to do this is to put your publisher on a cluster.
> --
>
|||dw wrote:
> Hello...
> Currently we have merge replication set up between SQL 2000 and mobile
> devices running SQL CE 2.0...and some with SQL Mobile.
> As part of the logic on the device, we have to set some properties for the
> SqlCeReplication object. Some of these we make dynamic, like the
> publication, login, etc.
> However, we currently hard code the Publisher and InternetURL values to our
> existing server like:
> repl.Publisher = "MyMachineName"
> repl.InternetURL =
> "http://MyMachineName.domain.com/SqlRepl/sqlcesa20.dll"
> My question or problem is this...what happens in the case of a failover? We
> have a backup/redundant/mirrored server at a different geographic
> location...obviously it has a different server name. For the devices in the
> field, how do people handle this type of situation where the server failover
> will occur automatically. Or, if we decide to move to a server farm...where
> we have multiple Sql Servers...how can we make our mobile application
> agnostic to the Publisher?
> Thanks for any help or advice.
> - will
Try using a DNS alias name and have your subscribers connect to that. When you change the server,
also change DNS to point to the new machine. Will be transparent to the subscribers. Make sure
that the server will respond to the alias name.
We use aliases at my company. Recently, the motherboard on our production server died. All the
data files are on an external disk array. We have an identical server as backup. We moved the C:
hard drive to the backup server and moved the cable from the disk array. We changed DNS to point
the alias to the new server. Back up in 10 minutes.
This also works when upgrading to a bigger server.
|||No, they should be on the same machine.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:09D18E34-DC6F-4EAE-82F4-07645EE1578E@.microsoft.com...
> Would that mean the Publisher and Distributor would be on different
machines?[vbcol=seagreen]
> "Hilary Cotter" wrote:

How to Set Max Memory?

I don't have much (if any) experiance running an SQL server. I
currently have Windows SBS2k installed with 2gb of memory for the
system. I would like to install more, but my budget doesn't allow for
it at the moment.
I know there is a way to set the max memory an SQL instance can use,
but I can't find a real step by step instruction.
The server is running SBS 2k3 standard, so its only the SQL desktop
edition.
First thing I need to know is how I know what instance is associated
w/ what PID.
Next, I need some hand holding on what I need to do to set the max
memory.
Any help would be appreciated.
Thanks!You could execute the following statements in Query
Analyzer, osql or whatever you get with that version of SBS:
select serverproperty('ProcessID')
will give you the PID for the instance you are logged into.
sp_configure 'max server memory', 1024
will set the max server memory to 1024 MB (1 GB)
-Sue
On 12 Feb 2007 20:40:26 -0800, trump26901@.gmail.com wrote:
>I don't have much (if any) experiance running an SQL server. I
>currently have Windows SBS2k installed with 2gb of memory for the
>system. I would like to install more, but my budget doesn't allow for
>it at the moment.
>I know there is a way to set the max memory an SQL instance can use,
>but I can't find a real step by step instruction.
>The server is running SBS 2k3 standard, so its only the SQL desktop
>edition.
>First thing I need to know is how I know what instance is associated
>w/ what PID.
>Next, I need some hand holding on what I need to do to set the max
>memory.
>
>Any help would be appreciated.
>Thanks!|||Adding to Sue we can use SP_WHO to find out in detail which user with with
pid and to which database and server instance there is an activity.
"Sue Hoegemeier" wrote:
> You could execute the following statements in Query
> Analyzer, osql or whatever you get with that version of SBS:
> select serverproperty('ProcessID')
> will give you the PID for the instance you are logged into.
> sp_configure 'max server memory', 1024
> will set the max server memory to 1024 MB (1 GB)
> -Sue
> On 12 Feb 2007 20:40:26 -0800, trump26901@.gmail.com wrote:
> >I don't have much (if any) experiance running an SQL server. I
> >currently have Windows SBS2k installed with 2gb of memory for the
> >system. I would like to install more, but my budget doesn't allow for
> >it at the moment.
> >
> >I know there is a way to set the max memory an SQL instance can use,
> >but I can't find a real step by step instruction.
> >
> >The server is running SBS 2k3 standard, so its only the SQL desktop
> >edition.
> >
> >First thing I need to know is how I know what instance is associated
> >w/ what PID.
> >
> >Next, I need some hand holding on what I need to do to set the max
> >memory.
> >
> >
> >Any help would be appreciated.
> >Thanks!
>|||On Feb 13, 12:20 am, Sue Hoegemeier <S...@.nomail.please> wrote:
> You could execute the following statements in Query
> Analyzer, osql or whatever you get with that version of SBS:
> select serverproperty('ProcessID')
> will give you the PID for the instance you are logged into.
> sp_configure 'max server memory', 1024
> will set the max server memory to 1024 MB (1 GB)
> -Sue
> On 12 Feb 2007 20:40:26 -0800, trump26...@.gmail.com wrote:
>
> >I don't have much (if any) experiance running an SQL server. I
> >currently have Windows SBS2k installed with 2gb of memory for the
> >system. I would like to install more, but my budget doesn't allow for
> >it at the moment.
> >I know there is a way to set the max memory an SQL instance can use,
> >but I can't find a real step by step instruction.
> >The server is running SBS 2k3 standard, so its only the SQL desktop
> >edition.
> >First thing I need to know is how I know what instance is associated
> >w/ what PID.
> >Next, I need some hand holding on what I need to do to set the max
> >memory.
> >Any help would be appreciated.
> >Thanks!- Hide quoted text -
> - Show quoted text -
well that sounds nice and simple. I"ve gotta make my way to work now
so I"ll try it out in about two hours and keep my fingers crossed.
Thanks for the help.

How to set fill factor?

Hellow, everyone"
I have a web online table that is inserted about 1500 record one day. Each night, a DST is running to pull all data to anther database. How to set fill factor on a one column index to get the best performance? Current fill factor is 80%.
Thanks
ZYTYou are having performance issues with just 1500 records per day?|||You are having performance issues with just 1500 records per day?

Yes, about 1500 records everyday. This table has not been maintained about five years. Someone created it that time. I take over and want to imporve performance.

ZYT|||I'm not really into the fill factor thing. In fact I'm still learing my way in SQL Server.

Also, I really don't know if setting a fill factor would benefit your particular case. However, as far as I know, you should be able to determine the fill factor (0-100) of a particular index by using the ALTER INDEX command.

Not really sure if this was the answer you were looking for. There's the chance that I didn't understand your question.

Best regards.|||Well, lets have the whole story then. Post the DDL for the table, along with any indexes on it.
Also, how many records are in it?|||Keep in mind fillfactor only comes into play at the time you create the index, or rebuild the index. It has absolutely nothing to do with how the data is maintained over time.|||Do you defrag/rebuild indexes regularly, let's say based on that volume, every couple of weeks ?|||You are having performance issues with just 1500 records per day?If I may - the nature of the records inserted bares some scrutiny. We had a guy on recently (I forget who) who's records averaged 4KB per record. As such I agree - we need to at least see the DDL to get an idea of what might be inserted.

We could also do with knowing if rows are likely to be updated regularly (and if those updates are likely to dramatically change the size of the rows).

...and finally - we need to know (as MCrowley says) what the reindexing processes are for this table.

@.OP - there is a lot of factors that determine the optimum fillfactor. Please provide the information requested :)

Wednesday, March 28, 2012

How to show running totals

How to setup two equal databases environment

Monday, March 26, 2012

How to setup two equal databases environment

How to setup two equal databases environment

How to setup two equal databases environment

Friday, March 23, 2012

How to setup Operator for sending alerts?

How to setup Operator for sending alerts?

How to setup Operator for sending alerts?

How to SETUP MSDTC on Win2k3

Wednesday, March 21, 2012

How to set up security to SQL 2005 Reporting Service?

Monday, March 19, 2012

How to set up connection to MS SQL remotely

How to set up a report when a specific user makes changes to a database

How to set up a report when a specific user makes changes to a database

How to set up a report when a specific user makes changes to a database

Monday, March 12, 2012

How to set the location of a linkedserver?

Wednesday, March 7, 2012

How to set Merge Repl properties for multiple servers?

How to Set Max Memory?

How to Set Max Memory?

How to Set Max Memory?

How to set fill factor?

set affinity for SqlExpress

Blog Archive

About Me