I was creating some tables this evening in a SQL Server 2008 database this evening so that I could sandbox some MVC functionality for a current project. I had designed the tables according to the tutorial only to find out that this wasn't the case. I'll just log back in to SQL and make the necessary changes. Easy.
When I added the missing column I went ahead and reordered to match the same order of the tutorial because, well; I am a little OCD at times. I remember doing this in the past pre-SQL 2008 with no trouble. This time I was hit with this:
Weird. Again, I had never seen this before so I was a bit surprised.
According to SQL Server 2008 Books Online this can be caused by any of the following:
- Adding a new column to the middle of the table
- Dropping a column
- Changing column nullability
- Changing the order of the columns
- Changing the data type of a column
Really? These all seem like fairly common tasks when working within a database. As odd as this may seem (maybe there is a good reason for this?), there is a very easy fix.
From the Tools menu click on Options, expand Designers, click on Table and Database Designers. Select or clear the Prevent saving changes that require table re-creation option.
Much better. Back to work.
Tags:
Database,
SQL
Decided to install SQL 2008 recently because of it being 2009 and all. What an adventure. Although not near as bad as upgrading from Vista Home Premium to Vista Ultimate, it sucked pretty bad.
I downloaded the 120-day trial Developer Edition as a self-extracting executable because I didn't feel like burning a DVD / using Daemon Toolz. The download was the smoothest part of this whole deal.
From the installer screen I chose the Upgrade from SQL 2000 / 2005 option because I have SQL 2005 Developer Edition installed so this made the most sense. Miserable fail. I kept erroring out when trying to install SQL Reporting Services. The exact error(s) escape me but I remember it having something to do with authentication and the report service. Naturally I thought to manually stop and start the service to see if there was any issue. Good thing because manually starting the service failed. It then dawned on me that between the time I had initially installed SQL 2005 and that very instant I had changed my Windows password. Right-click, 'Properties' and changed it to my current password, restart the service: SUCCESS!
Now let's try upgrading again: FAIL!
After hitting the Google fairly hard I read a couple of blog entries detailing how uninstalling SQL Server 2005 entirely from the Control Panel would remedy the situation. I tried this and received the following error when attempting to uninstall SQL 2005 Reporting Services:
Awesome. Have no idea what this means so I searched Google for it. Apparently no one else does either. There were tons of suggestions as to how to go about remedy'ing this so I tried a couple. These solutions ranged from reconfiguring Reporting Services to uninstalling and reinstalling IIS7. I opted to try and reconfigure Reporting Services only to find that the configuration tools no longer existed on my machine seeing as how that was successfully uninstalled before receiving the error message above. I then tried to uninstall again and received this error message.
Same error, different error code. Weird.
The final solution was to install each component of SQL 2005 one-by-one from Control Panel. As crazy as that sounds it actually ended up being successful.
So, if you are getting these error messages try uninstalling each and every SQL 2005 component one-by-one and then do a clean install of SQL 2008. It worked for me.
Tags:
SQL
I tried to setup a database with full-text indexing today as part of an application I'm working on. I followed instructions to a T and I received the following error:
Awesome. The only difference I can think of is that the machine that this was successfully created on happened to be running Windows XP SP2. I am running Vista Ultimate.
Naturally I started Googling the error only to find nothing really helpful. It's all speculation. I just downloaded a 284MB .msi for SQL Server 2005 SP2 since "you have to install SP2 if you are running Vista and you want SQL Server 2005 to work properly."
At this point I'll try anything. If you have any suggestions, by all means...
UPDATE
I'm awesome. Not really (yes) but through extensive Google-searching, next-level blogging and waving a dead chicken over my laptop I have finally resolved this.
The culprit was a NTLMSSP (NT LAN Manager Security Support Provider aka NAMBLA)service dependency. TBQH, I don't really give a shit what it does, all I needed to know is that:
This service has been removed from Windows Vista and Windows Server 2008 in favor of the newer Kerberos authentication protocol.
So, a service is removed entirely from Windows Vista and Server 2008 and a dev team thought it would be a good idea to either a) take it out of the OS entirely or b) make SQL Server 2005 depend on it. That makes perfect sense.
All kidding aside, get to your registry settings by way of regedit.exe or Start -> Run -> regedit and look for:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesql
Remove NTLMSSP and reboot. Easy. Thanks Tanzim and some dba-type from a Google Groups thread!
Tags:
SQL
It's been awhile since I've posted anything to the SQL category. Yesterday I was working on some SQL queries and found something interesting. One of my queries had a subquery as part of a NOT IN. As the query logic became more complex, the time to execute the query increased as well. After some Google'ing on the subject I came across many blog entries mentioning how using this syntax is ill-advised. The suggestions varied, but the common denominator seemed to advise use EXISTS.
EXISTS simply returns true or false depending on the results of a subquery.
Here's a query using NOT IN. We only want to select fields where our PK isn't included in the subquery.
SELECT F FROM T WHERE PK NOT IN (SELECT PK FROM T2 WHERE F = 'bar')
Here is that very same query written utilizing NOT EXISTS.
SELECT F FROM T WHERE NOT EXISTS (SELECT PK FROM T2 WHERE F = 'bar' AND PK = T.PK)
The original article that inspired the change. One of the contributors mentioning EXISTS being more efficient only when the result set is larger than 12 rows. I'd like to see some test results. I will say that after implementing this change the response time of the query was almost instantaneous. We're talking about a database with 1000's of records.
Tags:
SQL
Optimistic concurrency is not fun at all. We are currently working on a project that has 2 mothers. By 2 mothers I mean there are 2 people CRUD`ing data at any given time. Imagine one person executing a series of stored procedures that result in setting the PrintedFlag to 1 and someone coming behind you executing a set of stored procedures that result in setting the PrintedFlag to 0. ddd
What was happening was I was executing a series of stored procedures and someone else was coming in behind me and executing the same stored procedures before I was finished. The simple solution was to use a StringBuilder and create SQL Transaction statements.
private
void UpdateDatabase(XmlTextReader reader)
{
// loops through an Xml file and builds a string
StringBuilder sql = new StringBuilder("BEGIN TRANSACTION\r\n");
while(reader.Read())
{
switch(reader.LocalName)
{
case"localName1":
sql.Append("exec StoredProcedure1 ");
sql.Append("@p1=" + Convert.ToInt32(attributes["one"]) + ",");
sql.Append("@p2=" + Convert.ToInt32(attributes["two"]) + ",");
sql.Append("@p3=" + Convert.ToInt32(attributes["three"]) + ",");
sql.Append("@p4=" + Convert.ToInt32(attributes["four"]));
sql.Append("\r\n");
break;
case"localName2":
sql.Append("exec StoredProcedure2 ");
sql.Append("@p1=" + Convert.ToInt32(attributes["one"]) + ",");
sql.Append("@p2=" + Convert.ToInt32(attributes["two"]) + ",");
sql.Append("@p3=" + Convert.ToInt32(attributes["three"]) + ",");
sql.Append("\r\n");
break;
}
}
sql.Append("COMMIT\r\n");
// this.cn is the application connection string
using ( OleDbConnection cn = new OleDbConnection(this.cn) )
{
using ( OleDbCommand cm = new OleDbCommand(sql.ToString(), cn) )
{
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
}
}
}
Tags:
C#,
Code,
Programming,
SQL
Just finished with a huge SQL build script for a project I`ve been working on. To most of you I`m sure it`s a pretty small build script; only about 5,500 lines of T-SQL! Any tips on troubleshooting or streamlining the SQL build process? I found that adding a print line right before each task was helpful in stepping through any errors. This took me back to the ASP Response.Write days of debugging.
Are there any `best practices` when it comes to creating build scripts or is it just another one of those tedious tasks that takes patience and persistence?
The only "error" I am getting now is telling me that the user already has select permissions on a certain table. In my opinion this really isn`t an error.
Tags:
Database,
SQL
I have been learning a lot lately in the arena of T-SQL from working on Reporting Services and what-not. Cursors, at least for me, have been sort of a recurring theme in the creation of SQL Reports. They are actually pretty handy when it comes updating multiple tables with multiple subsets of data.
For your enjoyment I have provided a very simple use of a cursor. The cursor will select a subset of data and then use that data to update fields in a different table. This is aimed more towards the beginners. In no way do I claim to be a cursor ninja.
DECLARE @foo nvarchar(20)
DECLARE @bar money
DECLARE crsFB CURSOR FOR
(
-- select Values for @foo & @bar
SELECT
foo_column,
bar_column
FROM
table_name
)
OPEN crsFB
FETCH NEXT FROM crsFB INTO @foo, @bar
WHILE @@FETCH_STATUS = 0
BEGIN
-- update another table with @foo & @bar
UPDATE
server.db.tbl.owner.tablename
SET
something = @bar,
something = GetDate()
WHERE
this = @bar
FETCH NEXT FROM crsFB INTO @foo, @bar
END
CLOSE crsFB
DEALLOCATE crsFB
Tags:
SQL
Key not valid for use in specified state.
Have you seen this error? I was in the middle of working on a SQL Reporting Service and was notified by Windows Update that there were some "critical updates" that needed to be applied. This was last week (~7/13/06 ). The operating system happens to be Windows Server 2003 SP1. After installing the updates I was prompted to to restart my computer (which I did) and was mortified when my SQL Reporting Services stopped working, all of them!
The steps below were taken by me to get my machine up and running again. SQL Reporting Services works beautifully now.
- Uninstall the recent Windows Updates
- <DRIVE>:\Documents and Settings\<machine_name>\ASPNET\Application Data\Microsoft\Crypto\RSA\
- Search for '1aedd7b5699f3d6a88e354100b596aae*'
- Delete this file
- Open command prompt
- rsactivate -r -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"
- Restart ReportServer
- Continue working on SQL Reports
Related:
Roman Rehak: Weekly SQL Server Tips
How to Activate SQL Server 2000 Reporting Service
Kate Gregory: Accidentally Killed my Reporting Services Installation
Tags:
JavaScript,
SQL
I am posting these for myself and others as reference for SRS issues.
Bob`s SQL Reporting Services Blog
Karen`s SQL Reporting Services Blog
Bryant Like`s Blog : Reporting Services
Have fun!
Tags:
JavaScript,
SQL
Had a big project today that consisted of a web interface and SQL Reporting Services. Exactly 9.5 hours after starting we finished the project and oh boy did we have a party configuring the servers during the whole process. If you haven`t used SQL Reporting Services let me start by saying that it is very interesting to say the least. Take a process as easy as generating a dataset from a large stored procedure to bind to a repeater having a nice report-like layout and then complicating that process to the point of insanity by adding role-based security, authentication and a web service or two and you will have had your own introduction to SQL Reporting Services. Maybe I`m full of it? I have only been using SRS for the past week or so and obviously I haven`t become a ninja yet. After getting the hang of it I realize that after the initial introduction it is a pretty fun and powerful tool to use.
Seriously though. As if Web Services weren't complicated enough. Now you have to worry about passing the proper credentials to a remote web service and also having role-based security configured. The fact that SRS is a combination of SQL Server and IIS components makes (at least for me) a frustrating experience at times to configure. This is mostly due to the fact that I haven't had all that much experience with configuring servers and permissions-related issues. I am learning though and look forward to contributing (hopefully) to the SRS community.
Related:
Authentication, Role-based Security, and SQL Reporting Services Web Services
Tags:
.NET,
Design,
SQL