Error 29506 | SQL Server 2005 Management Studio Express

When i binged (yes I’m using bing :P), i found the following fix:

  • Search for Command Prompt in the Start Window (don’t press enter)
  • Right click on the shortcut found and select ‘Run as Administrator’
  • In the command prompt change the current directory to the folder where the SSMSEE installer is stored
  • Enter the following command .SQLServer2005_SSMSEE_x64.msi if you are running Windows 7/Vista 64 bit
  • Enter the following command .SQLServer2005_SSMSEE.msi if you are running Windows 7/Vista 32 bit
  • Press enter to start the installation

Happy Sql Server Managementing 🙂

Resources: here

Could not execute stored procedure ‘master.dbo.sp_enable_sql_debug’

This morning I found this error when trying to debug a stored procedure from Visual Studio 2005:

User ‘dbo’ could not execute stored procedure ‘master.dbo.sp_enable_sql_debug’

After googling a bit, I found that to solve this problem, the user accessing the DB must be part of the sysadmin server roles. To add a user to this role: (click more for the steps) Continue reading

MS Access 2007 | #Deleted

This might come in handy if you are accessing SQL Server 2005 tables from Access:

Problem:

Using Microsoft Access (2003 and 2007) to link to tables on a SQL 2005 SP2 server is working most of the time. On some particular tables (MS Dynamics AX database) the results of the linked table for all rows and columns is #Deleted.

Solution:

Do these tables have a primary key of data type BIGINT? I was receiving the same #error on certain tables and it turns out Access can’t handle tables with a primary key column of BIGINT. These are some possible work arounds for that scenario:

1) Change the datatype of the primary key to INT.
OR
2) Create a view of the table using “CAST as INT” for the BIGINT field.

Click here for the original post.

Disable Triggers

Last time, I had some calculation problems in one of my tables, and I needed to make a simple batch Update on the table but the following error was loading…

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

After several trial and errors, I finally found the problem: it was that I had a trigger on the table and it didn’t let me batch Update. After googling i found that you can disable any trigger using the following code in you SQL Statement:

DISABLE TRIGGER triggername ON tablename

add batch update or batch insert command;

ENABLE TRIGGER triggername ON tablename

And now the batch Update worked flawlessly. Hope this works for you too.

(PS. It’s important to insert a semi-colon (;) after your update or insert command, as it would give an error and wouldn’t re-enable the Trigger.)

There is already an open DataReader…

Sometimes, when I try to get data from the SQL Server the following exception is loaded:

“There is already an open DataReader associated with this Command which must be closed first.”

This occurs when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don’t close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection).

This is due to a change in the default setting for MARs (Multiple Active Result Sets).  By default, It used to be set as True but it was changed and was set to False by default post RC1.

So to remove this exception, you just need to change the following: Continue reading