Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.
Time cannot be summed directly in T-SQL. In order to sum two times they first need to be assigned a date. When a time data type is cast as a datetime data type, as it does not have a date element, the value defaults to the date of 1900-01-01.
As T-SQL does have the functionality to sum datetime and as the date element will be the same only the time value will be summed. This functionality allows us to sum time.
Below is example T-SQL:
This is a very common activity in the data world, i.e. there’s some data in a text string you need and the rest of the data in the string is just in your way. Some use cases might be you have a reference in a filename you need to extract, or you may need a snippet of data to create a composite key, or there’s an order number surrounded by other data that is not relevant to your needs etc.
The following is some simple T-SQL that will extract the data you want from a text string providing the data has specific delimiting characters on each side of it.
If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.
The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.
Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.
If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.
The script below will provide SQL Server Instance login details such as:
- The login name
- The role membership the login is part of such as sysadmin etc.,
- Whether or not the login is enabled
- Whether or not the login is a SQL Server Login or Windows Login etc.
- Login created date
- Login modified date
The following script will create a job that will run every minute to test if database mail can be sent from a job scheduled to run by the Sql Server Agent.
Simply find and replace the email address below with the email address you want to target:
Then run the script.
The operator ‘Test Operator’ and job ‘MailTest’ will be created.
The job is disabled by default, enable it to begin testing.
When you are finished run the commented out section at the bottom of the script to remove the test operator and job.
If you have just setup database mail for the first time the SQL Server Agent will need to be restarted.
If you have a password protected SSIS package you may encounter this error.
First off this seems like a very buggy problem and the solution may not work for everyone. Also, as typical, you may find that you are trying to solve more than one problem at once, for instances going from one domain to another seems to confuse matters. So if this solution doesn’t work it may be just part of the solution to your particular puzzle.
If you go to the job step properties you actually see that there are two spaces between /DECRYPT and /CHECKPOINTING.
If you reduce the space to one and run the job again you might get this error:
“Enter decryption password: Missing argument for option “decrypt””
This is because you’ve removed the password from the job associated with the package.
I’ve found that when you edit the command manually by first removing the spaces and then retyping the command with the package password this solves the error.
/DECRYPT YourPackagePassword /CHECKPOINTING
If this doesn’t work try scripting out the job, deleting it, and then running the script again.
The following code will work for a remote client request to SQL 2008 and newer.
Note: The local machine address (local_net_address) is that of the SQL Server while client_net_address is the address of the remote computer you have used to make the request.
Here’s a quick script to set a database to single user and then back to multi user.
You’ll need to do this when altering the database in situations like database name changes, file name changes or even restores
So the vendors for the call system software ran the code below on the live system on a Friday night. Thanks guys. I’ve changed the name of the tables in the code for security reasons if anyone would like to use this it as an example of how not to write T-SQL code!
The vendors claim the code came from the company that developed the call system and if this is the case I think someone in head office is trying to get fired or get someone fired.
Scroll down for a review of why the code is such a mess.
Here are some of the issues with the above in no particular order.
The command is running against a 312 million row table, a 55 million row table and a 22 million row table. Although is was only planned to run once IT STILL NEEDS TO BE EFFICIENT!!!!!
It uses Select Star or (Select *) which raises the probability that SQL Server will query the whole table rather than accessing the data through indexes. Only an Id is needed to begin with.
There are subqueries used when a join would be much faster.
The subqueries are completely unnecessary when WHERE conditions could have been listed in an IN statement.
Correlated subquery!!! Literally designed to bring a server to its knees! Evaluated once for each row processed. See more on correlated sub queries here https://en.wikipedia.org/wiki/Correlated_subquery
There are EXISTS Statements used due to the poor design of the command.
A ridiculously giant case statement used! Typically the comparison data should be written to a temp table and assessed as a join when there are an excessive number of case statements.
Needless to say this query ballooned the tempdb and I’ll let you guess what happened next.
The lesson for today’s posting, trust no one!