Want to drop all you database tables for MSSQL?
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:
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "
date formatting and SQL
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:
<!--- vars in: dateFrom, dateTo --->
<cfset createODBCDate(form.dateFrom)>
<cfset createODBCDate(form.dateTo)>
<cfquery name="qGetComments"datasource="#application.defaultProperties.datasourceName#">
SELECT comments.comment, comments.commentDate, tbluser.firstName, tbluser.lastName
FROM comments INNER JOIN
tbluser ON comments.staffID = tbluser.userID
WHERE (comments.studyID = #qStudyInfoList.studyId#) AND
comments.commentDate BETWEEN #LSParseDateTime(form.dateFrom)# AND #LSParseDateTime(form.dateTo)#
order by commentDate
</cfquery>
<cfset createODBCDate(form.dateFrom)>
<cfset createODBCDate(form.dateTo)>
<cfquery name="qGetComments"datasource="#application.defaultProperties.datasourceName#">
SELECT comments.comment, comments.commentDate, tbluser.firstName, tbluser.lastName
FROM comments INNER JOIN
tbluser ON comments.staffID = tbluser.userID
WHERE (comments.studyID = #qStudyInfoList.studyId#) AND
comments.commentDate BETWEEN #LSParseDateTime(form.dateFrom)# AND #LSParseDateTime(form.dateTo)#
order by commentDate
</cfquery>
This helps when people are using differnt date formats as well.
How to specify/change the port number in Studio Express
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,888
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
nVaChar, varchar and nText
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
The backed-up database has on-disk structure version 611
I have been trying to restore a database today from a backup file I was sent but i kept getting an error (id: 3169):
The backed-up database has on-disk structure version 611
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.
Change MSSQL table owner
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:
sp_changeobjectowner '[DATABASE_NAME].[TABLE_NAME]' , 'NEW_OWNER_NAME'
i.e.
sp_changeobjectowner '[SMS].[tblContactType]' , 'ntong'
String or binary data would be truncated
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
ORM - what the heck!
Mark Mandel has been interviewed over on cfFrameworks.com about his Object Relationship Model Transfer framework, covering where it sits within the application layer and he talks about some of the functions such as decorators.
If you're tired of creating all the SQL for you data abstraction then you need to check out Transfer. Pop over to the site to check the interview podcast out.
mySQL GUIs
I've been doing work with mySQL for the last week or so and forgot which GUIs I've used in the past to work with it. Apart from notepad here are some that I've looked at:
mySQL Administrator
linkThe update from mySQL control center; this is easy to use and has a host of features including query browser, and a command line client. It's also free so this has to go on your download list.
SQL yog
linkThis is great - again it's free and easy to use. One of the great things that this has is SQL compare - although this feature is only in the enterprise version.
Navicat
linkThis is mySQLs answer to MSSQLs Enterprise Manager. With a nice query builder it a quick query creator.
NB: It's worth noting that i've just glanced at these products but I like what i've seen so far.
Tables, Views and Stored Procedures via MSSQL information_schema
Recently I posted about using the INFORMATION_SCHEMA table to recover data from a a ISP that had shut me down (see link below). I thought I would just post a summary of how to get tables, views and stored procedures:
To get tables use:
select *
FROM INFORMATION_SCHEMA.TABLES
FROM INFORMATION_SCHEMA.TABLES
To get views use:
select *
FROM INFORMATION_SCHEMA.VIEWS
FROM INFORMATION_SCHEMA.VIEWS
To get stored procs use:
select *
FROM INFORMATION_SCHEMA.ROUTINES
FROM INFORMATION_SCHEMA.ROUTINES
To get columns from a table including defaults etc use:
select *
from information_schema.columns
where table_name = 'your_table_name'
from information_schema.columns
where table_name = 'your_table_name'
