Hi,
I would like to calculate field based on the entry in another in SQL server
2000 i.e.; field3 = field2 * field1
field1 field2 field3
10 2.5 25.00( =sum(field1*field2))
How do I go about this in the SQL DB itself, can it be done within field3?
Regards
SimonLook up "computed columns" in Books Online.
It's simple:
create table <table name>
(
Column1 <data type>
,Column2 <data type>
,Column3 as Column1 * Column2
)
go
or:
alter table <table name>
add Column3 as Column1 * Column2
go
ML
http://milambda.blogspot.com/
Thursday, March 29, 2012
Auto Failover Issues
I have been looking over the forum, and also other sites for information about my problem but cant seem to find what im looking for so I have decided to make a quick post.
Presently, I have 3 computers setup for mirroring. One is the principal, another the mirror and the third is the witness.
Im using SQL 2005 Enterprise Edition on all three, and creation of the mirror using SQL Studio works without problems. Manual failover (using the button in SQL Studio) also works fine.
When I start the "Mirror Monitor" application, and connect to the two DB servers the status is all green, they are connected to each other and the witness server can be contacted.
Now here is my issue; when its time for an automatic failover situation (pulling the plug from the current principal for example) it detects the fault and changes the status of the mirror to "principal" BUT keeps the other status to "Disconnected" (Principal, Disconnected) so no active connections to the failed over database will work.
When running the mirror monitor during the failed fail-over attempt the still online database reports that the connection to the witness is still present but the mirror is offline.
There are a few error notices in the logs, but from what I can tell they are normal for whats happening. But the codes would be 1479 (cant talk to the database; this would be the one we took offline) and 1474 (network name is no longer available; once again as it was taken offline). Note that these errors are also in the witness server logs, as I believe one should expect.
Any help or assistance with this problem would be appreciated.
Thanks,
Sean
Do you see any network related issues or warnings between the principal and mirror servers?|||Hate to state the obvious, but you don't mention that you re-connect server 1 in your scenario. What you are describing is exactly what I would expect to happen.sqlAuto export to Excel
Is there a way to export directly to an excel .csv file when the report is run without having to preview the report first and then click the export icon?
I want to be able to schedule a report to run and have it go directly to an excel .csv file.
You can use the file delivery subscription. This allows you to automaticly create a csv file at a UNC path.
See http://msdn2.microsoft.com/en-us/library/ms157386.aspx for more information.
|||You can specify the export format using the URL access syntax. Have a look in the BOL, there are some samples for that listed.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
I cannot get to the property tab. It's greyed out. Only the location shows.
|||Alright I found it. It's all done on the server side. I was on the client side.Auto export to Excel
Is there a way to export directly to an excel .csv file when the report is run without having to preview the report first and then click the export icon?
I want to be able to schedule a report to run and have it go directly to an excel .csv file.
You can use the file delivery subscription. This allows you to automaticly create a csv file at a UNC path.
See http://msdn2.microsoft.com/en-us/library/ms157386.aspx for more information.
|||You can specify the export format using the URL access syntax. Have a look in the BOL, there are some samples for that listed.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
I cannot get to the property tab. It's greyed out. Only the location shows.
|||Alright I found it. It's all done on the server side. I was on the client side.auto expanding Temp DB to fit large requests
I have run into a glitch in SQL 2000's ability to automatically expand the temp db when temp tables become very large.
In our business it is sometimes required that I alter a customer's configuration data without modifying any of their transaction data. This requires a rather complex procedure that creates a script of insert/update/delete statements that, when run on a customer's database, modifies their configuration to a replica of our in-house test environment.
While creating this script, sometimes a few lines are dropped. The real problem is that we have no error or indication that the script had dropped lines until we attempt to run it (which is usually on-site in a live environment.) Our solution is to manually increase the size of the temp db and it's transaction log. After we do this, the script is always created correctly.
This appears to be a bug in the ability for the temp db to auto expand. Is this fixed in SQL 2005?
Various tempdb defects have been fixed in SQL Server 2005. In addition there is a new feature allowing the "automatic space growth" without zeroing pages (makes auto growth much faster).
Without going into more details I expect that your problem will be fixed
SQL Server CTP15.
Please install the CTP15 release and test it. In addition I suggest to contact
MS-CSS (SQL Server 2000 Customer Service) and report your problem for SQL Server 2000
Please let me know the test results for SQL Server 2005 CTP15
Thanks
Mirek
Tuesday, March 27, 2012
Auto e-mails
Hello,
I have a SQL server though a hosting company and I am trying to send autoemails using xp_sendmail. The permissions were set and I used the following command to test it.
EXEC master.dbo.xp_sendmail
@.recipients='tracey@.yahoo.com',@.subject='test',@.me ssage='testing
sql stored procedure'
It gave me a message saying "Mail sent" but there none in my e-mail box.
How do I set yp the SQL Mail server, right? Please help. I don't know what is happening.
Thanks,
TraceyHave you checked whether the Mail Service has been turned on and the machine has MS Outlook installed(And it has to work)?|||And an Outlook need to have mail configured using the same account the SQL Server Agent runs as.|||Insead of all that, you could use CDO to send mail, which eliminates the need to have Outlook installed.
Check out this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_cdo_for_nts_library.asp
and this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_session_object_cdonts_library_.asp
I have a SQL server though a hosting company and I am trying to send autoemails using xp_sendmail. The permissions were set and I used the following command to test it.
EXEC master.dbo.xp_sendmail
@.recipients='tracey@.yahoo.com',@.subject='test',@.me ssage='testing
sql stored procedure'
It gave me a message saying "Mail sent" but there none in my e-mail box.
How do I set yp the SQL Mail server, right? Please help. I don't know what is happening.
Thanks,
TraceyHave you checked whether the Mail Service has been turned on and the machine has MS Outlook installed(And it has to work)?|||And an Outlook need to have mail configured using the same account the SQL Server Agent runs as.|||Insead of all that, you could use CDO to send mail, which eliminates the need to have Outlook installed.
Check out this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_cdo_for_nts_library.asp
and this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_session_object_cdonts_library_.asp
Auto Email Statistics from DB Table
Hi,
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
Chip
detail), in the next
message
you might want to check out xp_smtp_sendmail from www.sqldev.net.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...[QUOTE]
> sincere regards for the inputs.
> Chip
> detail), in the next
> message
> daily
> appreciated.|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
real pain to setup),
www.sqldev.net.
oi=djq&as_ugroup=microsoft.public.sqlserver
message
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
quote:|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
quote:|||sincere regards for the inputs.
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>
Chip
quote:
>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for
detail), in the next
quote:
>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||In case you don't have xp_sendmail configured (can be a real pain to setup),
>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
daily[QUOTE]
appreciated.[QUOTE]
>
>.
>
you might want to check out xp_smtp_sendmail from www.sqldev.net.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...[QUOTE]
> sincere regards for the inputs.
> Chip
> detail), in the next
> message
> daily
> appreciated.|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
quote:
>--Original Message--
>In case you don't have xp_sendmail configured (can be a
real pain to setup),
quote:
>you might want to check out xp_smtp_sendmail from
www.sqldev.net.
quote:
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
quote:
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
quote:sql
>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
various[QUOTE]
get[QUOTE]
does[QUOTE]
soon[QUOTE]
>
>.
>
Subscribe to:
Posts (Atom)