I do get frustrated sometimes by the very poor user
experience you get with management studio.
My most recent annoyance comes when looking at job histories, if you want to
look at the details of the step that failed you have to go and find the job,
open it, find the job step and open it. If you've got loads of jobs this can
take some time. Especially with the popup window behaviour of management
studio.
This is a connect item that would allow us to double click on an item in the
job history and open the job properties directly. I reckon for a moderately busy
DBA this would save days of effort per year.
http://connect.microsoft.com/SQLServer/feedback/details/531889/allow-access-to-job-details-from-the-log-file-viewer-to-improve-manageability
If you think this would be a good suggestion then please vote for
it.
I've encountered this erro message a few times. One of
the reasons this occurs is that the server can't verify and account with a
domain controller. This happens when a user has access to a server through group
membership. SQL doesn't know all the individuals in a group and so has to go off
and find out. If the service account doesn't have the correct permissions or you
have multiple domains then this can occur.
However the situation I had this week was that this was failing and none of
the above were true.
The reason was that "Agent XPs" were disabled. Agent XPs are the extended
stored procedures SQL Agent uses in its day to day job. The account
verification process is an extended stored proc. That meant that when SQL Agent
tried to verify a user it couldn't.
Annoyingly the error about "Agent XPs" didn't appear in the log for the job history but appeared in the SQL
Agent error log. Mainy people don't know there is such a thing. Well there is. Since
2005 its easier to find but many still don't look there. This is a completely
different log to that of the main SQL Server service.
I thought I could get around this by using a SQL account, but no luck. It
still uses the extended stored proc.
So if you are running SQL Agent then you need to enable the "Agent XPs"
using, the following. BOL states that this is done for you if you start SQL
Agent through management studio. That could be a new feature in SQL 2008.
Note: If you have advanced options turned on then you don't have to turn it
on again.
exec sp_configure 'advanced
options',
1
reconfigure
exec sp_configure 'Agent xps', 1
reconfigure
exec sp_configure 'advanced
options',
0
reconfigure
I pointed out yesterday that the EntityFramework doesn't
like smallints. Well it does and it doesn't The model handles them fine and the
objects have datatypes of int16 which is great.
However the queries that are generated do this odd thing with any predicates
on such columns. It applies a cast to column.i.e
where
cast(col2
as int)= @d
At first glance I thought oh now not another screw up with the SQL from
Entity framework, but on inspection of a query plan where the column in question
was indexed I still got a seek operation. Very nice, well done optimisation
team.
When I pointed this out, I was asked whether this worked on SQL2005. Having
only got a SQL2008 instance on my laptop I took the question away for testing.
The reason of the question could be related to the fact that in SQL 2008 the
introduced the ability to cast a datetime to a date to be able to look for
specific days of data and still use an index.
I have now tested and can state that it does work on SQL
2005.
I was asked by a client about the new Time datatype in
SQL 2008. They wanted to store the duration of some work so they could use the
nice date time functions to get the hours, minutes, seconds etc.
However you can't use the Time date type for that, because it can only hold
time up to midnight after that it roles around to 0. Not much use if something
takes longer than 24 hrs to run.
If you would like to have such a feature then vote for the
reccomendation for an interval data type. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432281
If you can't wait until fro a nother 3 or 4 years then you could cheat.
If you use the datetime data type then you can add these together and get the
behaviour you want. So you can get 04:53 and add it to 20:52.
To find the hours, months etc you perform a datedfif with 0. like this
declare @time
datetime
set @time
= cast('4:53' as datetime ) + cast('20:52' as datetime)
select DATEDIFF (minute,0,@time)
NOTE: You can't do this type of calculation with the new date and
time data types you have to use the date add functions. This IMHO is one of
the poor aspects of the new data types
I've been workig with XML in SQL Server Integration services this week and to say
the XML task in SQL Server Integration services sucks is an understatement. Its buggy, the documentation
is woefully inadequate in some cases wrong. I believe this was one of the first
components written back for SQL 2005 so you can imagine it was written along
time ago, XML wasn't as standardised.
So what if you want to validate an XML document against a XSD Schema. Well in
true SQL Server Integration services mode the answer is to do it yourself with a script task.
There are two ways of doing it, the first uses an XmlDocument object and the
other is the XmlReader. If you were on my XML course at SQLBits you will
remember that I pointed out the importance of straeming when dealing with large
data and this is where these two differ. The XmlDocument loads the document into
memory, whereas the XmlReader is a streaming interface to the XML document you
have. So if you have a large document you should use the XmlReader.
In my case however I wanted to pass the XML to a stored procedure after it
had been validated and so I used the XmlDocument.
The first step is to create a XmlSchemaSet and put your schema into it.
string Filepath = Dts.Variables["User::FullNewFilename"].Value.ToString();
string Schema= Dts.Variables["User::SchemaFilepath"].Value.ToString();
// Create a schema set
XmlSchemaSet schemaset =
new XmlSchemaSet();
// Add the schema to the
collection.
schemaset.Add(XmlSchema.Read(XmlReader.Create (Schema),null));
Here Schema is a variable to the path for the schema and Filepath is the
variable to the Xml file to be validated.
After that you have to either use the XmlDocument or XmlReader. For the
XmlDocument we are passing back the contents of the file using the InnerXml
property.
//clear our errors string
builder
ValidationErrors
= new StringBuilder();
//Use this if you need to read the document for use
elsewhere
XmlDocument doc = new XmlDocument();
doc.Schemas
= schemaset ;
doc.Load(Filepath
);
doc.Validate(new ValidationEventHandler(ValidationCallBack));
//Load the contents of file into the variable for later
processing.
Dts.Variables["ImportFileContents"].Value =
doc.InnerXml;
//clear our
errors string builder
or use the XmlReader, this requires another object the XmlReaderSettings
object
//set the validation settings.
XmlReaderSettings settings = new XmlReaderSettings();
settings.ValidationType
= ValidationType.Schema;
settings.Schemas
= schemaset ;
settings.ValidationEventHandler
+= new ValidationEventHandler(ValidationCallBack);
using (XmlReader
xr = XmlReader.Create(Filepath,settings
))
{
//This reads the whole document through the reader and does
the validation
while (xr.Read()) ;
//Close the reader
xr.Close();
}
In both you will see that they have references to the ValidationCallBack. The
validation model is a callback model and so you need this extra function. This
is why we are using a global variable ValidationErrors to store the errors and
then be able to access them after the validation succeeds. The validation
callback function is described here
// Display
any validation errors.
private void
ValidationCallBack(object sender, ValidationEventArgs e)
{
ErrorOccurred = ErrorOccurred || e.Severity == XmlSeverityType.Error;
Warning= Warning||
e.Severity == XmlSeverityType.Error;
if (ValidationErrors.Length >
0)
{
ValidationErrors.AppendLine();
}
ValidationErrors.Append(e.Message);
}
Complete Code
So putting it all together we get
StringBuilder
ValidationErrors;
bool ErrorOccurred= false;
bool Warning = false;
public void
Main()
{
string Filepath =
Dts.Variables["User::FullNewFilename"].Value.ToString();
string Schema= Dts.Variables["User::SchemaFilepath"].Value.ToString();
// Create a schema set
XmlSchemaSet schemaset = new XmlSchemaSet();
// Add the schema to the
collection.
schemaset.Add(XmlSchema.Read(XmlReader.Create (Schema),null));
//clear our errors string
builder
ValidationErrors =
new StringBuilder();
//Use this if you need to read the document for use
elsewhere
XmlDocument doc = new XmlDocument();
doc.Schemas = schemaset
;
doc.Load(Filepath
);
doc.Validate(new ValidationEventHandler(ValidationCallBack));
//Load the contents of file into the variable for later
processing.
Dts.Variables["ImportFileContents"].Value =
doc.InnerXml;
/* //Otherwise
use the following
// Set the validation
settings.
XmlReaderSettings settings = new XmlReaderSettings();
settings.ValidationType
= ValidationType.Schema;
settings.Schemas =
schemaset ;
settings.ValidationEventHandler
+= new ValidationEventHandler(ValidationCallBack);
using (XmlReader xr =
XmlReader.Create(Filepath,settings ))
{
//This reads the whole document through the reader and does the
validation
while
(xr.Read()) ;
//Close
the reader
xr.Close();
}*/
if (ValidationErrors.Length >
0)
Dts.Variables["XMLError"].Value =
ValidationErrors.ToString();
Dts.Variables["Validation"].Value = ErrorOccurred?"Error":(Warning?"Warning":"");
}
// Display any validation
errors.
private void
ValidationCallBack(object sender, ValidationEventArgs e)
{
ErrorOccurred = ErrorOccurred || e.Severity == XmlSeverityType.Error;
Warning= Warning||
e.Severity == XmlSeverityType.Error;
if (ValidationErrors.Length >
0)
{
ValidationErrors.AppendLine();
}
ValidationErrors.Append(e.Message);
}
Enjoy.
