Automating copy between 3 servers

I'm developing a small app (Access), based on a SQL Server 2000 DB.
Development occurs on 3 DBs:
- Office DB - (MS SQL server 2000)
- Customer DB - (MS SQL server 2000)
- Portable DB - (MSDE 2000)
Since moving continuosly, as a daily routine I have to copy the latest DB,
and currently I use Enterprise manager to do so (backup/restore); boring,
'cause db paths are different on the 3 machines hosting the db servers.
Is there an easy way to automate the exchange between the 3 servers?

if all of your data is moving only in one direction and all of your tables have PK's on them, think about transactional replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sql

automatically update another field based on other parts of the record

Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS
UPDATE CartItems Set quantity = @.newQuantity
WHERE cartItemID = @.cartItemID
GO

sure. you can update as many fields as you want within the UPDATE statement.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS

SET NOCOUNT ON

UPDATE
CartItems
Set
quantity = @.newQuantity
,lineTotal =pounds * itemSell
WHERE
cartItemID = @.cartItemID

SET NOCOUNT OFF

GO

|||Or make the lineTotal a computed column. Just modify the table and tell it the lineTotal column is equal to pounds*itemSell.sql

Automatically Delete Snapshots

Does anyone know of a way to set up the Report Server to automatically delete
snapshots based on age.
I know I could write code to do this. I was hoping there might be a setting
you could manage that said to remove all snapshots in this folder older then
30 days everyday at midnight.
DaveDo you mean execution snapshots? Why do you want to delete them? Or are you
taking about aging of history snapshots? If so, this one is on the feature
list for a future version but in the meantime you can write some code to do
it yourself.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
news:29A9EA76-A6C3-45D4-9EDB-6F8A45215C34@.microsoft.com...
> Does anyone know of a way to set up the Report Server to automatically
> delete
> snapshots based on age.
> I know I could write code to do this. I was hoping there might be a
> setting
> you could manage that said to remove all snapshots in this folder older
> then
> 30 days everyday at midnight.
> Dave|||Thanks Brian...
I know I can write code to do it. I was hoping there might be a way to
configure the Report Server itself to remove reports older than a configured
number of days.
I'll do it myself and wait for the feature in another release.
Dave
"Brian Welcker [MS]" wrote:
> Do you mean execution snapshots? Why do you want to delete them? Or are you
> taking about aging of history snapshots? If so, this one is on the feature
> list for a future version but in the meantime you can write some code to do
> it yourself.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
> news:29A9EA76-A6C3-45D4-9EDB-6F8A45215C34@.microsoft.com...
> > Does anyone know of a way to set up the Report Server to automatically
> > delete
> > snapshots based on age.
> >
> > I know I could write code to do this. I was hoping there might be a
> > setting
> > you could manage that said to remove all snapshots in this folder older
> > then
> > 30 days everyday at midnight.
> >
> > Dave
>
>sql

AUtomatic mail alert

HI,

I have to send an automatic e-mail based on database table .Could any body help me

how to write stored procedure and where to execute it.How to send an automatic e-mail through sql server?

Thanks in advance.

Regards,

Raja.

Hi Raja,

To send an email in a stored procedure in SQL Server, you can call xp_sendmail directly.

http://msdn2.microsoft.com/en-us/library/ms189505(SQL.90).aspx

Before this, you have to configure an extended MAPI mail profile. Here is a link for how to do it.

http://msdn2.microsoft.com/en-us/library/ms175189(SQL.90).aspx

However, you can also read data from the database table and do this in your .net application, using the System.Net.Mail.MailMessage class.

http://msdn2.microsoft.com/en-us/library/system.net.mail.mailmessage.aspx

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
Erik
Hi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:
> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created indexes
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexes
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

Automatic index (Statistics) vs Manually created indexes

Hi,
I noticed SQL Server, version 2000 in my case, automatically created indexes
(WA_Sys_... indexes) based on its query optimization functionality.
My question is; would my database performance increase if I created indexes
manually instead of depending on the (correct) created automatic indexes?
ErikHi
WA_ are not indexes, but statstics.
A real index is much better than statistics. A statistic is there to help
the query optimiser decide how to process a query, and not used for data
access.
http://www.sql-server-performance.com
Regards
Mike
"Erik Tamminga" wrote:

> Hi,
> I noticed SQL Server, version 2000 in my case, automatically created index
es
> (WA_Sys_... indexes) based on its query optimization functionality.
> My question is; would my database performance increase if I created indexe
s
> manually instead of depending on the (correct) created automatic indexes?
> Erik
>
>

automated emails based on SQL Queries

Hello.
Does anyone know where there is an example of emails that are automatically
generated based on query results and have certain variables from the query
populate the email?
More Detail -
A query runs daily that identifies sales in certain locations. For each
location (row) an email is sent to the location manager with hard coded
verbiage and the sales figures pulled from the query for that specific
location.
I imagine this has been done somewhere, but I'm not sure where to start
looking.
Any suggestions are appreciated.
Thank you!
ChrisIf the query is done through a stored procedure, you can easily add SQL Mail
calls to issue emails.
Thomas
"chris" <chris@.discussions.microsoft.com> wrote in message
news:61C33544-A332-4DDB-90E1-11F2209CD396@.microsoft.com...
> Hello.
> Does anyone know where there is an example of emails that are automaticall
y
> generated based on query results and have certain variables from the query
> populate the email?
> More Detail -
> A query runs daily that identifies sales in certain locations. For each
> location (row) an email is sent to the location manager with hard coded
> verbiage and the sales figures pulled from the query for that specific
> location.
> I imagine this has been done somewhere, but I'm not sure where to start
> looking.
> Any suggestions are appreciated.
> Thank you!
> Chris
>|||Thank you! I will look into that.
"Thomas Coleman" wrote:

> If the query is done through a stored procedure, you can easily add SQL Ma
il
> calls to issue emails.
>
> Thomas
>
> "chris" <chris@.discussions.microsoft.com> wrote in message
> news:61C33544-A332-4DDB-90E1-11F2209CD396@.microsoft.com...
>
>|||You might want to take a look at microsofts new Notification Services. Comes
with SQL 2k5,
Download for sql 2k I believe.
http://www.microsoft.com/sql/ns/default.asp
"chris" wrote:

> Hello.
> Does anyone know where there is an example of emails that are automaticall
y
> generated based on query results and have certain variables from the query
> populate the email?
> More Detail -
> A query runs daily that identifies sales in certain locations. For each
> location (row) an email is sent to the location manager with hard coded
> verbiage and the sales figures pulled from the query for that specific
> location.
> I imagine this has been done somewhere, but I'm not sure where to start
> looking.
> Any suggestions are appreciated.
> Thank you!
> Chris
>

automate rebuilding indexes

Hi,
I was wondering if someone had a method to rebuild indexes
automatically, based on the results of 'dbcc showcontig'.
I want to automate index rebuilding by using the results of 'dbcc
showcontig with tableresults' into a table.
Then i want to query the results which indexes have a logical and extent
scan fragmentation of >10%.
Finally execute a 'create index <indexname> on <tablename>(columnname)
with drop_existing'
Should i be doing this automatically or manually and have a general
maintenance plan where i do a rebuild with default fillfactor?You find just such an example in Books Online, DBCC SHOWCONTIG. Also see
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotmail.com> wrote in message news:uQWgItQdGHA.4576@.TK2MSFTNGP05.phx.gb
l...
> Hi,
> I was wondering if someone had a method to rebuild indexes automatically,
based on the results of
> 'dbcc showcontig'.
> I want to automate index rebuilding by using the results of 'dbcc showcont
ig with tableresults'
> into a table.
> Then i want to query the results which indexes have a logical and extent s
can fragmentation of
> Finally execute a 'create index <indexname> on <tablename>(columnname) wit
h drop_existing'
> Should i be doing this automatically or manually and have a general mainte
nance plan where i do a
> rebuild with default fillfactor?

Automate partition creation

Hi,

Is there any example out there on how to automate the creation of time based partitions?
As an example, I would like to create 1 partition for each quarter and when a new quarter start have a new partition automatically added with the same attributes than the previous one.

I would also have the oldest 4 partitions automatically deleted as soon as the total number of partitions reaches 13.

Any thoughts or links?

Thanks,

Philippe

Hi Philippe,

The Project REAL Analysis Services Technical Drilldown discusses one implementation of such automation:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

>>

Project REAL: Analysis Services Technical Drilldown

By Dave Wickert, Microsoft Corporation

SQL Server Technical Article
Published: September 2005

Appendix A: Automating Partition Creation

The Project REAL design uses partitioning quite heavily. The production system has more than 220 extremely large partitions. The sample data uses over 125 partitions that are only tens of thousands of records per partition. The full production system has 180 to 200 million records per partition. With so many partitions, extensive typing was required to create each partition every time we generated a new schema.

So, as the saying goes, “When the going gets rough, a programmer writes a program.”

This appendix documents the BuildASPartition SQL Server 2005 Integration Services package that we created to automate the building of Analysis Services measure group partitions in SQL Server 2005 Analysis Services databases. This package synchronizes the relational partition scheme with the Analysis Services partition scheme. It loops through the relational database looking for a weekly fact table partition (by using a table naming convention). If a relational table is found, it looks to see if an Analysis Services measure group partition already exists (using the same naming convention). If not, it constructs and executes a XMLA script that creates it.

>>

|||

Philippe,

Add your comments here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=461211&SiteID=1

-Jamie

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

Auto-Generate MDX Query from Cube OWC Settings?

Can I somehow auto-generate an MDX query based on current settings in the AS OWC cube browser? Why? If so, I will want to use the AS OWC cube browser to filter the data down to < 66K rows, then auto-generate an MDX query from the current browsed settings, copy and paste the MDX script into MS Excel's OLAP query, and use it. I'm trying to avoid the 66K row limitation in Excel 2002.

If this is innappropriate, is there another way, besides raw MDX hand-coding, to accomplish the same thing?

You can start a trace using the SQL Server Profiler, and see the MDX query created by OWC. Then use that query in Excel?

Chris.

Auto URL Redirect

Hi,Does anyone know how to redirect the report to another URL automatically conditionally based on some checking, without user interaction with the report?I am trying to perform validity checking on user's input value and redirect user to a centralise error page if the input value is invalid.Thanks,J LimYou can't do this using the standard parameter toolbar. You will need to code your own textbox for accepting user input for the parameter value and pass that to the report if is valid and redirect to the error page if not valid.

Auto SQL Mail

Hi,

I have to generate mails automatically based on databse (SQL SERVER) table,In that table we have expirydate as one column and

based on expirydate I have to generate the mails automatically,Please guide me to solve this issue.

where we have to run the stored procedure.

Do we have to use jobscheduler?

please guide me how to use it

Thanks in avance

regards,

Raja.

Hi,

check following links ...

you have to schedule store procedure ...which will send mail ...

http://support.microsoft.com/kb/312839

http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054928,00.html

Auto increment based on date

I have a Primary Key column that I would like to have formatted as follows: 'yyyymmdd[auto increment]' . I would also like the auto increment to restart at the beginning of a new month. Any idea's on how to achieve this?

Thanks

You will have to manually control all the inserts via stored procs. Have a separate column with identity property, do the insert, get the Id via SCOPE_IDENTITY() then update your column with the concatenated value. Only drawback is you cannot have it as PK since the value has to be calculated. You can however have a unique constraint on the column. Alternatively if you have a separate table that has just one column with identity values, you can do an insert there first, get the seed value and then use that to insert into your regular table with the concatenation.

|||

An interesting concept for sure but doesn't this smack of a normalization violation which may be the least of your problems with this idea. I'd keep the date in a separate column and use a normal auto increment. From that you should be able to accomplish just about everything you're trying to do with this unusual idea including presenting the user with a computed column that looks like 'yyyymmdd[order number].'

If I just had to succeed with this idea I'd consider writing a function that returns your special key but you'd probably have to input your last increment used from an embedded SELECT statement.

Something like INSERT tblName (pKey,...) VALUES( dbo.fnGetPrimary((SELECT MAX(pKey) from tblName WHERE (pKey > (DATEPART(.. hand build the earliest possible key for the current month which could be a separate function.)) ), .....)

If dbo.fnGetPrimary is fed a null then it would know it is the first entry of the current month. If dbo.fnGetPrimary is fed a value such as 2007110510 then you would just increment it by 1 and return it.

I'd be a little concerned with performance and concurrency with this idea - two records trying to insert and both trying to use the same primary key. You might be able to avoid that with a transaction.

Any way it was fun playing around with it. Good Luck.