more mystery on (local) vs localhost

ping localhost WORKS FINE
connectionString = "server=localhost;uid=sa;pwd=;database=pubs;"
FAILS
connectionString= "server=(local);uid=sa;pwd=;database=pubs;"
WORKS
C:\>osql -U sa -S localhost FAILS
Password:
[DBNETLIB]General network error. Check your network documentation.
[DBNETLIB]ConnectionRead (recv()).
C:\>isql -U sa -S localhost WORKS
Password:
1>>
> ping localhost WORKS FINE
> connectionString = "server=localhost;uid=sa;pwd=;database=pubs;"
> FAILS
> connectionString= "server=(local);uid=sa;pwd=;database=pubs;"
> WORKS
> C:\>osql -U sa -S localhost FAILS
> Password:
> [DBNETLIB]General network error. Check your network documentation.
> [DBNETLIB]ConnectionRead (recv()).
> C:\>isql -U sa -S localhost WORKS
> Password:
> 1>
--
There are known issues and bugs filed against the use of 'localhost'.
You should always use '(local)' in your connection string.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional

month ('jan','feb',...) string to date conversion fails

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]]

Error: The component "Derived Column" failed because error code

0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

srem wrote:

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

Its not an ideal situation but if you want to achieve this in a single derived column component what you will need to do is build some nested conditional operators. Its a bit of a fraught process to begin with but once you get the hang of it it isn't too bad.

I hope Microsoft give us the ability to extend the expression language in the next version by allowing us to build our own custom expression functions.

-Jamie

|||I suppose you could build a quick lookup table using a simple stored procedure. Then all you'd have to do is join on that "Jan 02 2005" column to get you a real date field in return.

Might even work better than doing all of the CASTs and conditional logic tests.|||

If you know exactly what the month strngs are, and they are all 3 chars long, you could do something like the following to get month number:

(FINDSTRING(month, "JANFEBMARAPR....DEC",1) + 2 ) / 3

If there are multiple options, or the lengths are irregular you could still do something similar, but it might not be worth it anymore over the nested conditionals.

Monitoring Clustered SQL Server

I am attempting to montior failovers, using Sitescope, so does anyone know
event what events are created when a node fails over?
Dave B
The SDK for Windows Clustering should give you a few.
http://msdn.microsoft.com/library/de...start_page.asp I
am not a developer, so I can't help you anymore with the SDK.
You can also create a failure and use the ones you want from that wonderful
experience.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Dave B" <DaveB@.discussions.microsoft.com> wrote in message
news:5C9DDBD9-4FF8-461D-BAEE-8C480EA9A713@.microsoft.com...
>I am attempting to montior failovers, using Sitescope, so does anyone know
> event what events are created when a node fails over?
> --
> Dave B
|||Dave B wrote:
> I am attempting to montior failovers, using Sitescope, so does anyone know
> event what events are created when a node fails over?
We use nagios to monitor if the sql service is running on the prefered
node. If not it must have failed over. sitescope can monitor services too.
Hans

monitor sql backups

I currently receive a notification if my sql backup fails through sql agent.
However, there is always an outside chance that the sql agent is not working
either in which case I have no notification if my backups fail.
Perhaps one can easily write a command prompt code to monitor the dates on a
folder and send out an email if the dates for the backup files are not
current. I dont
know how to write such a code. If anyone uses this or any other method to
monitor their backups, please email the code at scott-williams@.hotmail.comProbably an easiest and safest thing to do is to configure the job to send
the notification when it completes, even if it failed or was successful. The
n
you will need to read the message and verify that the job was successful.
In this scenario, if you do not receive an e-mail you will know that
something is wrong, and maybe not the SQL Server Agent, but the e-mail
software or something else.
The monitor that you propose will have the same limitation as your original
problem as it will also relay on e-mail, which could fail.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Scott" wrote:

> I currently receive a notification if my sql backup fails through sql agen
t.
> However, there is always an outside chance that the sql agent is not worki
ng
> either in which case I have no notification if my backups fail.
> Perhaps one can easily write a command prompt code to monitor the dates on
a
> folder and send out an email if the dates for the backup files are not
> current. I dont
> know how to write such a code. If anyone uses this or any other method to
> monitor their backups, please email the code at scott-williams@.hotmail.com|||Probably an easiest and safest thing to do is to configure the job to send
the notification when it completes, even if it failed or was successful. The
n
you will need to read the message and verify that the job was successful.
In this scenario, if you do not receive an e-mail you will know that
something is wrong, and maybe not the SQL Server Agent, but the e-mail
software or something else.
The monitor that you propose will have the same limitation as your original
problem as it will also relay on e-mail, which could fail.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Scott" wrote:

> I currently receive a notification if my sql backup fails through sql agen
t.
> However, there is always an outside chance that the sql agent is not worki
ng
> either in which case I have no notification if my backups fail.
> Perhaps one can easily write a command prompt code to monitor the dates on
a
> folder and send out an email if the dates for the backup files are not
> current. I dont
> know how to write such a code. If anyone uses this or any other method to
> monitor their backups, please email the code at scott-williams@.hotmail.com|||You might want to invest in a product from www.sqlsentry.net then. It will
do all your monitoring and more.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:7BBD606A-379C-4AF4-91C8-453DBB2F7F20@.microsoft.com...
>I currently receive a notification if my sql backup fails through sql
>agent.
> However, there is always an outside chance that the sql agent is not
> working
> either in which case I have no notification if my backups fail.
> Perhaps one can easily write a command prompt code to monitor the dates on
> a
> folder and send out an email if the dates for the backup files are not
> current. I dont
> know how to write such a code. If anyone uses this or any other method to
> monitor their backups, please email the code at scott-williams@.hotmail.com

monitor sql backups

I currently receive a notification if my sql backup fails through sql agent.
However, there is always an outside chance that the sql agent is not working
either in which case I have no notification if my backups fail.
Perhaps one can easily write a command prompt code to monitor the dates on a
folder and send out an email if the dates for the backup files are not
current. I dont
know how to write such a code. If anyone uses this or any other method to
monitor their backups, please email the code at scott-williams@.hotmail.comProbably an easiest and safest thing to do is to configure the job to send
the notification when it completes, even if it failed or was successful. Then
you will need to read the message and verify that the job was successful.
In this scenario, if you do not receive an e-mail you will know that
something is wrong, and maybe not the SQL Server Agent, but the e-mail
software or something else.
The monitor that you propose will have the same limitation as your original
problem as it will also relay on e-mail, which could fail.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Scott" wrote:
> I currently receive a notification if my sql backup fails through sql agent.
> However, there is always an outside chance that the sql agent is not working
> either in which case I have no notification if my backups fail.
> Perhaps one can easily write a command prompt code to monitor the dates on a
> folder and send out an email if the dates for the backup files are not
> current. I dont
> know how to write such a code. If anyone uses this or any other method to
> monitor their backups, please email the code at scott-williams@.hotmail.com|||Probably an easiest and safest thing to do is to configure the job to send
the notification when it completes, even if it failed or was successful. Then
you will need to read the message and verify that the job was successful.
In this scenario, if you do not receive an e-mail you will know that
something is wrong, and maybe not the SQL Server Agent, but the e-mail
software or something else.
The monitor that you propose will have the same limitation as your original
problem as it will also relay on e-mail, which could fail.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Scott" wrote:
> I currently receive a notification if my sql backup fails through sql agent.
> However, there is always an outside chance that the sql agent is not working
> either in which case I have no notification if my backups fail.
> Perhaps one can easily write a command prompt code to monitor the dates on a
> folder and send out an email if the dates for the backup files are not
> current. I dont
> know how to write such a code. If anyone uses this or any other method to
> monitor their backups, please email the code at scott-williams@.hotmail.com|||You might want to invest in a product from www.sqlsentry.net then. It will
do all your monitoring and more.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:7BBD606A-379C-4AF4-91C8-453DBB2F7F20@.microsoft.com...
>I currently receive a notification if my sql backup fails through sql
>agent.
> However, there is always an outside chance that the sql agent is not
> working
> either in which case I have no notification if my backups fail.
> Perhaps one can easily write a command prompt code to monitor the dates on
> a
> folder and send out an email if the dates for the backup files are not
> current. I dont
> know how to write such a code. If anyone uses this or any other method to
> monitor their backups, please email the code at scott-williams@.hotmail.com

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE./*
Was not able to have the normal way (using SQL Agent notification system) to
work with MSDE but it works great on SQL Server. I suggest you switch to
Windows scheduler and batch driven maintenance tasks. SQLExpress will not
come with SQL Agent service. If you want to have schedules you will have to
use Windows SCHTASKS or AT command.
Create a script with your backup command, then a batch file to execute it
using OSQL or SQLCMD, a net send command can be embeded into the batch file.
Create a Windows task to execute the batch file.
Example of a batch file that would be called by the Winmdows Scheduler
OSQL -b -h-1 -s~ -w8000 -E -SCHC-6X9VQ31-XP -dmaster
-i"YourScriptFileNameAndLocation" -o"ALogFileNameAndLocationToStoreErrors"
if errorlevel == 1 goto errhand
goto end
:errhand
net send chc-6x9vq31-xp "The database back up failed ..."
:end
*/
USE msdb
-- Create a message to store in the sysmessages table
exec sp_addmessage @.msgnum = 55001 ,
@.severity = 1 ,
@.msgtext = 'Error 55001 has occurred. The database back up failed ...'
-- Because the message must be logged for the net send to fire
exec sp_altermessage 55001, 'WITH_LOG', 'true'
-- define an operator and how it is going to get notified
exec sp_add_operator @.name = 'Your Name'
, @.enabled = 1
, @.netsend_address = 'chc-6x9vq31-xp'
-- create and alert for the message.
EXEC sp_add_alert @.name = 'Test Alert', @.message_id = 55001, @.enabled = 1
-- define the notofocation procedure
-- in facts it joins the operator to an alert and how to join it
exec sp_add_notification @.alert_name = 'Test Alert' ,
@.operator_name = 'Your Name' ,
@.notification_method = 4
-- this will do the netsend
raiserror(55001,1,1)
"inquisite" wrote:

> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What are th
e
> different notification possibilites with MSDE.
>

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE./*
Was not able to have the normal way (using SQL Agent notification system) to
work with MSDE but it works great on SQL Server. I suggest you switch to
Windows scheduler and batch driven maintenance tasks. SQLExpress will not
come with SQL Agent service. If you want to have schedules you will have to
use Windows SCHTASKS or AT command.
Create a script with your backup command, then a batch file to execute it
using OSQL or SQLCMD, a net send command can be embeded into the batch file.
Create a Windows task to execute the batch file.
Example of a batch file that would be called by the Winmdows Scheduler
OSQL -b -h-1 -s~ -w8000 -E -SCHC-6X9VQ31-XP -dmaster
-i"YourScriptFileNameAndLocation" -o"ALogFileNameAndLocationToStoreErrors"
if errorlevel == 1 goto errhand
goto end
:errhand
net send chc-6x9vq31-xp "The database back up failed ..."
:end
*/
USE msdb
-- Create a message to store in the sysmessages table
exec sp_addmessage @.msgnum = 55001 ,
@.severity = 1 ,
@.msgtext = 'Error 55001 has occurred. The database back up failed ...'
-- Because the message must be logged for the net send to fire
exec sp_altermessage 55001, 'WITH_LOG', 'true'
-- define an operator and how it is going to get notified
exec sp_add_operator @.name = 'Your Name'
, @.enabled = 1
, @.netsend_address = 'chc-6x9vq31-xp'
-- create and alert for the message.
EXEC sp_add_alert @.name = 'Test Alert', @.message_id = 55001, @.enabled = 1
-- define the notofocation procedure
-- in facts it joins the operator to an alert and how to join it
exec sp_add_notification @.alert_name = 'Test Alert' ,
@.operator_name = 'Your Name' ,
@.notification_method = 4
-- this will do the netsend
raiserror(55001,1,1)
"inquisite" wrote:
> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What are the
> different notification possibilites with MSDE.
>

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE.
/*
Was not able to have the normal way (using SQL Agent notification system) to
work with MSDE but it works great on SQL Server. I suggest you switch to
Windows scheduler and batch driven maintenance tasks. SQLExpress will not
come with SQL Agent service. If you want to have schedules you will have to
use Windows SCHTASKS or AT command.
Create a script with your backup command, then a batch file to execute it
using OSQL or SQLCMD, a net send command can be embeded into the batch file.
Create a Windows task to execute the batch file.
Example of a batch file that would be called by the Winmdows Scheduler
OSQL -b -h-1 -s~ -w8000 -E -SCHC-6X9VQ31-XP -dmaster
-i"YourScriptFileNameAndLocation" -o"ALogFileNameAndLocationToStoreErrors"
if errorlevel == 1 goto errhand
goto end
:errhand
net send chc-6x9vq31-xp "The database back up failed ..."
:end
*/
USE msdb
-- Create a message to store in the sysmessages table
exec sp_addmessage @.msgnum = 55001 ,
@.severity = 1 ,
@.msgtext = 'Error 55001 has occurred. The database back up failed ...'
-- Because the message must be logged for the net send to fire
exec sp_altermessage 55001, 'WITH_LOG', 'true'
-- define an operator and how it is going to get notified
exec sp_add_operator @.name = 'Your Name'
, @.enabled = 1
, @.netsend_address = 'chc-6x9vq31-xp'
-- create and alert for the message.
EXEC sp_add_alert @.name = 'Test Alert', @.message_id = 55001, @.enabled = 1
-- define the notofocation procedure
-- in facts it joins the operator to an alert and how to join it
exec sp_add_notification @.alert_name = 'Test Alert' ,
@.operator_name = 'Your Name' ,
@.notification_method = 4
-- this will do the netsend
raiserror(55001,1,1)
"inquisite" wrote:

> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What are the
> different notification possibilites with MSDE.
>

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE.
inquisite wrote:
> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What
> are the different notification possibilites with MSDE.
please do not multipost... if you really need it, do Xpost instead...
answered in .msde
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

monitor backup jobs

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE.
hi,
inquisite wrote:
> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What
> are the different notification possibilites with MSDE.
MSDE does not support SQLMail, so you have to resort on NetSend ... but...
:D
at http://sqldev.net/xp/xpsmtp.htm you can find a well known extended stored
procedure that is able to send mails via SMPT and you can take advantage of
that...
have a look at http://www.karaszi.com/sqlserver/info_no_mapi.asp , SQL
Server MVP Tibor Karaszi web site, for some info about how to set such a
process, in the section "Job notifications from SQL Server Agent"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MOM Alert when login failed for user 'sa'

I am trying to setup a MOM Alert when the SA account fails to login to SQL,
How can this be setup so SQL reports this into the windows event viewer and
not its own log?
Many thanks for any help,
AlastairIt does get logged in the application event log whenever login failure
happens for any account. (Standard install)
However, you can create a rule in MOM to capture error id:18456 with error
desc like "Login failed for user 'sa'".
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"alastairn" wrote:
> I am trying to setup a MOM Alert when the SA account fails to login to SQL,
> How can this be setup so SQL reports this into the windows event viewer and
> not its own log?
> Many thanks for any help,
> Alastair
>|||Thanks for the fast response and help :)
"Saleem Hakani" wrote:
> It does get logged in the application event log whenever login failure
> happens for any account. (Standard install)
> However, you can create a rule in MOM to capture error id:18456 with error
> desc like "Login failed for user 'sa'".
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
>
> "alastairn" wrote:
> > I am trying to setup a MOM Alert when the SA account fails to login to SQL,
> > How can this be setup so SQL reports this into the windows event viewer and
> > not its own log?
> >
> > Many thanks for any help,
> > Alastair
> >