On one of our DB servers when we installed it we went with the default setup - what this did though was configure the databases load and unload when they were not being utilised (an option called autoclose).
This option should be disabled on all DBs to increase performance. Here is a nice scirpt from the helpful people at Rackspace:
Ok - so im not ashamed to admit it so yes I destroyed my MSSQL tempdb table. So what is this table?
Tempdb is a system database used by SQL Server to store temporary tables and temporary stored procedures, for sorting, subqueries, and aggregates with GROUP BY, ORDER BY, for cursors and so on. Tempdb database contains only temporary objects, so if you want to create a permanent object, do not create it in the tempdb database.
If you have done the same and need to restore your tempdb table then simple follow these steps:
A lot of the time i get given data in excel formats that i need to insert into tables. The quickest way (I find) to do this is to save the excel as a CSV, upload the file to the same place SQL server and then run this script:
I've just needed to clear a database to test a setup SQL script. Here is the script just in case anyone out there needs to do the same:
I've been having a few issues with dates and SQL recently so I thought I would quickly post my solution. When searching in SQL with dates this is what I tend to use:
This helps when people are using differnt date formats as well.
Just a quick post to say how you can specify the port number you want to connect through when connecting to a server in MS SQL Studio Express:
123.456.789.012 is the IP address and 888 is my port number I want to connect on - the comer (,) is the important part.
Thanks jonese
I've been asked what the differences are between nVaChar, varchar and nText. The answer is:
NVARCHAR uses 2-byte characters so only half the "max" data (4000 vs 8000) characters can be stored. If you must support UTF-8 character sets, NVARCHAR is the way to go. If not, stick with VARCHAR for single-character character sets.TEXT is intended for data in excess of 8000 bytes, but it can't be indexed, so you'll have to design a lookup scheme other than searching for matches in the TEXT column.
Source: SQL Team.com
I have been trying to restore a database today from a backup file I was sent but i kept getting an error (id: 3169):
This error indicates that the the file was built for SQL 2000 not SQL 2005. Database files are not backward compatible.
Therefore I had to drop back into enterprise manager, update the file and continue.
I've just had an issue with permission access to stored procedures on my local machine VS my dev machine. The reason being was that when I created the SPs the server made the owner dbo. luckily you can use a MSSQL stored procedure to change this./ Jump into query analyser and type in:
i.e.
Tonight I've been working on migrating some data from a CSV file to a MS SQL database. This is quite easy to do. Simply copy the CSV file into the database and then create a query to copy the data over... errr... okay, not so easy then when you get this error:
String or binary data would be truncated
The main problem is that you don't know which filed will be truncated. The way I managed to solve this was
