After you create a new login for a database in your SQL Server and try to use it to connect to your database, you may encounter a “login for ‘your_username’ failed” error. The reason for this common error is that by default MS SQL Server runs in “Windows Authentication mode”. And you will need to change it to “SQL Server and Windows Authentication mode” by following the steps below: Launch SQL Server Management Studio Log […]
MS SQL Server
13 posts
I installed MS SQL Server Denali CTP3 (and its management studio) installed parallel to SQL Server 2008 R2, and everything works fine until I decided to uninstall Denali. The uninstallation of Denali was smooth and gave no error, however, after the uninstallation, MS SQL Server 2008 R2 Management Studio could not be launched. It didn’t give any error message and just didn’t launch at all. I had to reinstall MS SQL Server 2008 R2 to […]
Here is the table structure that I need to query: UserID Type Name Department Status Classification Enrollment_Status Where “Type” and “Department” are foreign keys of the other two tables. The query criteria are: If the user type is “Student” (type ID = 1), then only retrieve enrolled students (Enrollment_Status = ‘EN’); If the user type is not “Student” (type ID <> 1), then check Classification field and only retrieve data with Classification <> ‘R’ It […]
This tutorial demonstrates the use of Microsoft SQL Server Management Objects with a custom action in Visual Studio Setup project to deploy a database for your application. It is based on my development of Good Steward Express Edition application which can be downloaded at http://www.sardonyxtech.com/products.aspx References: MSDN article: Walkthrough: Using a Custom Action to Create a Database at Installation Handling “GO” Separators in SQL Scripts – the easy way Step 1. Create an installer class […]
Take a look at the following MS SQL query: SELECT MT.MeetingTypeName, COUNT(DISTINCT(A.MemberId)) AS AttendanceTotal FROM MeetingAttendance AS MA INNER JOIN MeetingType AS MT ON MA.MeetingTypeId = MT.MeetingTypeId WHERE (MA.MeetingDate = @meetingDate) AND (MT.MeetingTypeId IN (1,2,3)) What if the IN clause in the above query needs to be dynamic? Can you use a parameter for the IN clause like this? SELECT MT.MeetingTypeName, COUNT(DISTINCT(A.MemberId)) AS AttendanceTotal FROM MeetingAttendance AS MA INNER JOIN MeetingType AS MT ON MA.MeetingTypeId […]
If you use XML string as parameter to update your SQL database table as I discussed in my last post, if the server is SQL Server 2000 and the XML string contains a datetime string, you will get a SQL error as this: Syntax error converting datetime from character string Cause: When generating XML string from DataTable object, all DateTime values are converted into strings in ISO 8601 format. However, ISO 8601 format is support […]
In my previous post, I discussed how to use TransactionScope to update DataTable objects in a transaction. The biggest shortcoming of using TransactionScope is that several security settings on the server need to be modified in order to make it work, and this may not be feasible for many companies because of the security concern. Today, I will discuss how to use XML and stored procedure to update DataTable objects in transaction. The basic idea […]
We all know that if you need to insert/update data in several database tables that have relationship with each other, then you will need to make sure the insert/update process is completed in a transaction to ensure the data integrity. With stored procedure, it is not hard to perform transaction, but what if you are using DataTable? In this post, I will show how to use TransactionScope to implement transaction for DataTable update. Suppose we […]
I got this error today when I tried to install SQL Server Express 2008 Service Pack 1 on Windows 7: “SQL Server Setup error: Invoke or BeginInvoke cannot be called on a control until the window handle has been created” It is a bug according to this post. The fix is simple: close the dialog and then rerun the installer.
When you run a SELECT query in your ASP.NET code to fill a DataSet, you may encounter this error: IErrorInfo.GetDescription Failed with E_FAIL(0x80004005) This error is usually caused by the fact that the table name or some column name happens to be a keyword, such as “name”, “first”, and “date”, etc. If it is the case, then use square brackets around those column names as [name], [first], and [date], and it should fix the error.