Investigating the table structure of a website to supplement an existing module. One approach is to login to the server, where possible. A second is to run an SQL query from the website. If you have access to the SQL server either directly of via a Remote Desktop login to the server. The table structure […]
Category: SQL
Get List of Tables in Database Using t-sql
I have worked previously purely within the confines of a website to analyse the structure of the database. Wishing to make changes to, or simply understand the content of the tables storing information for a CMS website module, the initial task is to understand what tables exist. On a recent occasion I was lucky enough […]
SQL Entries Matching End Date not Selected
Extending an SQL select with date bounds I found that the end date wasn’t including the given date, rather only entries upto the day before were being selected. […]
Extend DNN SQL Connection Timeout
I was having issues with SQL timing out on a DotNetNuke website. I looked at adding extra code, modifying the SQL server, and amending the web.config file I found that I was able to resolve the timeout error by editing the connection string within the web.config file, and adding connect timeout with a value. DotNetNuke […]
SQL add a Default to an Existing Table Column
When a previously created database table requires the addition of a default value. Can the amendment be made easily? And without recourse to the SQL Configuration Manager. I had configured a database table. Subsequent testing showed that one of the fields required a default value. I was testing for a value of less than 0, […]
Read More… from SQL add a Default to an Existing Table Column
Get nth Row From Database
For a parent-child database table relationship – could retrieving the nth child row be used? I had a project comprising a main table with an associated secondary table. Similar in style to a manufacturers parent table with associated products child table. In this particular example I knew that there would only be two child entries […]
Update and Replace Field Value with New Value
We can use SQL to replace the value in field with a new value. There’s no need to search for those rows affecting, downloading each in turn, making the amendment and finally making the update. For the update of the given field in the table we can use one SQL statement, changing all matching entries. […]
Read More… from Update and Replace Field Value with New Value
Verify Database Backup
I was working with a new computer system. I had made a backup of the database. But could I rely on it? To confirm the first run of a database backup I wanted to test/verify it was OK. What I didn’t want to do was to restore the database over the existing one, without the […]
Update Row or Insert if Doesn’t Exist
When adding an entry to a database it would be nice to combine the addition with an update. For example adding a new entry if it already exists then that entry can be updated. This means putting more of the sequence within the database stored procedure. But it will save on a database query, with […]
Composite SQL Field with null Value Returns Blank
The intent was to join the address fields as a single returned value from a database query select. But I found that if one of these fields was null, the whole returned result was empty. I had an address, in the usual way, across a number of database fields: address; town ; postcode and county. […]
Read More… from Composite SQL Field with null Value Returns Blank
