I was surprised to find that this SQL statement does not return .36 as I had expected:
select 36/100 as ShowPercent
From some online searching I found a post which commented that a decimal result from a division operation would not display properly if both of the values were integers. I have no idea why, but that does seem to be true. The change I made to get the correct result was:
select 36/cast(100 as float) as ShowPercent
~Brad
Tuesday, December 23 2008 by Brad Kingsley | 1 comment(s)
Tagged as: net, asp, asp-net, sql, sql-express
If an application requires the use of Report Viewer Redistributable and needs access to specific .dlls for the application to run, you’ll find this post helpful, http://drowningintechnicaldebt.com/blogs/dennisbottjer/archive/2006/10/16/Hacking-Report-Viewer-Redistributable.aspx.
It explains how to extract the necessary .dll files so you can reference them in your application and avoid GAC security errors (e.g., Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The module was expected to contain an assembly manifest.)
I will outline the steps below to avoid another click. ;) I have also added links for the most recent versions of Report Viewer.
I hope this makes someone’s life a little easier!
Tuesday, November 25 2008 by Desirée Harris | 0 comment(s)
Tagged as: asp-net, sql, sql-express
Hi,
Here is a script that will truncate the database log while maintaining the database recovery model.
--Notes: Be sure to update the variable @sDbName to the
--correct database name.
DECLARE @sDbName VarChar(40)
SET @sDbName = 'DB_Name' --Add database name here
DECLARE @sDbRecovery varchar(40)
DECLARE @sLogName varchar(40)
DECLARE @sDBIsSimple_Shrink varchar(4000)
DECLARE @sSetRecoverySimple varchar(4000)
DECLARE @sShrinkDBLog varchar(4000)
DECLARE @sReSetRecovery varchar(4000)
DECLARE @sGetLogName varchar(4000)
-- the @dbrecovery variable is set to the recovery
-- model of the database
Set @sDbRecovery = CAST(DATABASEPROPERTYEX(@sDbName, 'Recovery') AS varchar(40))
-- this variable is used to get the logical log file
-- name of the database
set @sGetLogName =('USE ' + @sDbName + '
select name from sys.database_files where type = 1
')
-- a temporary table is created to hold the logical
-- file name of the database and then sets the @LogName
-- variable to that value. The table is then dropped.
-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultSet]') AND type in (N'U'))
DROP TABLE [dbo].[ResultSet]
-- create table and set the @LogName varable
CREATE TABLE ResultSet (SetLogName VarChar(400))
INSERT INTO ResultSet EXEC(@sGetLogName)
SET @sLogName = (select SetLogName from ResultSet)
Drop Table ResultSet
-- the next set of statements truncate the log,
-- but also ensures the recovery model of the
-- database is maintained.
IF @sDbRecovery = 'Simple'
BEGIN
set @sDBIsSimple_Shrink ='USE ' + @sDbName + '
DBCC SHRINKFILE (' + @sLogName + ', 1)
'
EXEC(@sDBIsSimple_Shrink)
END
ELSE IF @sDbRecovery = 'BULK_LOGGED'
BEGIN
-- Truncate the log by changing the
-- database recovery model to SIMPLE.
set @SsetRecoverySimple = 'ALTER DATABASE ' + @sDbName + '
SET RECOVERY SIMPLE'
EXEC (@sSetRecoverySimple)
-- Shrink the truncated log file to 1 MB.
set @sShrinkDBLog ='USE ' + @sDbName + '
DBCC SHRINKFILE (' + @sLogName + ', 1)
'
EXEC(@sShrinkDBLog)
-- Reset the database recovery model.
set @sReSetRecovery = 'ALTER DATABASE ' + @sDbName + '
SET RECOVERY BULK_LOGGED'
EXEC (@sReSetRecovery)
END
ElSE
BEGIN
-- Truncate the log by changing the database
-- recovery model to SIMPLE.
set @sSetRecoverySimple = 'ALTER DATABASE ' + @sDbName + '
SET RECOVERY SIMPLE'
EXEC (@sSetRecoverySimple)
-- Shrink the truncated log file to 1 MB.
set @sShrinkDBLog ='USE ' + @sDbName + '
DBCC SHRINKFILE (' + @sLogName + ', 1)
'
EXEC(@sShrinkDBLog)
-- Reset the database recovery model.
set @sReSetRecovery = 'ALTER DATABASE ' + @sDbName + '
SET RECOVERY FULL'
EXEC (@sReSetRecovery)
END
Friday, October 31 2008 by Desirée Harris | 0 comment(s)
Tagged as: databases, sql, sql-express
With the introduction of Microsoft's SQL Server 2005 came the introduction of Express Edition. Replacing Microsoft's MSDE from the SQL Server 2000 product line Express Edition removed the throttling limitation and provided an appropriate free SQL Server product for small to mid-sized websites.
The new Express Edition of SQL Server brought many of the new features of SQL Server 2005 into the arena for those looking for a stable database environment but with only the need for a single processor database server and smaller database sizes. But Microsoft chose to remove the SQL Agent from their new free version of SQL Server, consequently eliminating the ability to schedule jobs such as database backups.
Luckily for those looking to implement the power and stability of SQL Server 2005 Express Edition, backups of your database can be automated with very little work using Windows Task Scheduler and the new SQLCMD.EXE.
First you'll need to download and install Microsoft's SQL Server 2005 Express Edition and the SQL Server Management Studio Express-both which can be found here: http://msdn.microsoft.com/vstudio/express/sql/. (SQL Server Management Studio Express is not required for setting up the automated backups, but it is a good tool for managing your SQL Server 2005 Express Edition. The steps below can be easily done without any kind of GUI, but the commands to do so are outside of the scope of this blog.)
Once you have these products installed we'll need to setup a login with the authorization to perform database backups. Open SQL Server Management Studio Express (SSMSE) and connect to your installation of SQL Server 2005 Express Edition (SQL Express). Select the security folder and then click "New à Login..." The New Login dialog box will appear and this will give you a chance to setup a login to use for you backup jobs. For login name use something like ‘backupadmin', click "SQL Server authentication" and then type in a password. Because of the nature of this login, in most scenarios you will want to uncheck "Enforce password policy" which will automatically uncheck the other password policy options.
Under "Select a page," go to the "User Mappings". Select the database you will be backing up and click "Map". The role selections at the bottom of the screen will now be active. Select the "db_backupoperator". Now click "OK". This user will be the user within SQL Server to execute the backup command.
Now we will need to setup a T-SQL script that will actually execute the database backup-luckily SSMSE makes this nice and simple for us. Select the database that you will be backing up and click "Tasks à Backup..." Select the appropriate backup options for you database and click "Ctrl+Shift+F" to save the options to a SQL server script file. You'll want to save this file to a location on the server such as D:\admin\sql\SQLExpressBackups.sql. (Note: The machine user that SQL Express is running under, such as NETWORK SERVICE, needs read permissions to this file.)
Your SQL server script file should look something like this:
BACKUP DATABASE [DatabasetoBackup] TO DISK = N'D:\backup\sql\DatabasetoBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
(Note: The machine user that SQL Express is running under needs write/modify permissions to the directory your database backup file will be located. D:\backup\sql\ in the example above.)
You can log into SQL Express using your ‘backupadmin' user (that we created above) and try running the script that you've just create. If there are any permissions issues in SQL Express or on disk you will receive an error. Testing the script now can help us ensure our backups will run successfully.
Now all we have to do is setup the command line to execute our script using SQLCMD.EXE in the Windows Task Scheduler. Go through the setup wizard to setup a new Task-your command should look something like this:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -S [SERVER] -U bakupadmin -P [PASSWORD] -i D:\backup\sql\SQLExpressBackups.sql
(For more information on SQLCMD.EXE visit: http://shrinkster.com/jqd. For more information on Windows Task Scheduler visit: http://shrinkster.com/jqe.)
Once your task has been setup you can run it immediately to make sure it will run successfully. That's it! Allow the task scheduler to do its job and you'll have a backup of your SQL Express database automatically created for you.
Monday, November 06 2006 by Jeremy Hodges | 0 comment(s)
Tagged as: backups, databases, sql-express
I was recently working with a SQL database from the Commerce Starter Kit and needed to make a change to one of the stored procedures. I opened up the database using SQL Server Management Studio Express, drilled down to the stored procedures, right mouse clicked on the one I needed to edit and then clicked “Modify”. The code then appears in the query writer on the right hand side. I made the modifications and then went to “Save” and it just wanted to save the SQL query.
After some searching on Google, thanks to Jim Little’s post I learned instead of clicking “Save” you need to click “Execute”. The query that is shown is actually a query for modifying the stored procedure so you need to execute that query to have it make the changes in the database. It took me a minute but once I understood that clicking “Execute” would execute the SQL statement and not execute the stored procedure it all made since.
You can now “Save” the SQL and execute it on your production database as well.
Saturday, June 10 2006 by Jeremy Hodges | 0 comment(s)
Tagged as: sql-express