Gray Wolf Blog

SSMS was crashing for no apparent reason. Tried re-installing, but that did not resolve the issue.

Ended up re-naming the existing windows.prf file which seems to have fixed the issue.

C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\windows.prf

More Detail

After the installation of MySQL, it is only installed but it is not running. To make it run, the so called daemon for MySQL (mysqld) must be running. mysqld can be started manually by calling

# rcmysql start

with root privileges. You can check whether it is running by using

# rcmysql status

Another option is to start the daemon is during the boot.

Go to YaST -> System -> System Services (Runlevel). Wait a few seconds before YaST has examined all the Runlevels. Then select mysqld and press the enable button.

SUSE wiki

I always seem to forget these steps when setting up the Database Mail config for job notifications.

Database mail is a 3 step process if you want it to be used to send notifications.

First set up database mail.

Second, modify the Sql Server Agent properties. Select “Alert System” and then check “enable email profile”. Select “database mail” and the profile.

Third, restart the Sql Server Agent.

Add subkeys Place0,Place1,etc

Under each subkey add two REG_SZ values

Name = “Shortcut Name”
Path = “Folder location”

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\Open Find\Places\UserDefinedPlaces

I needed to allow access to file create and delete to a specific user in sql server 2008. This advanced option is now disabled by default.

To enable:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO

I was getting an error saying the .Net provider could not be found when attempting to connect to a MySql db in hosted environment. If the MySql.Data.MySqlClient is not loaded in the GAC on the hosted server, you will need to add a reference in your web.config, if you want to use the SqlDataSource control.

1.  
2. web.config - make sure the version matches your MySqlClient version
3.  
4. <system.data>
5. <DbProviderFactories>
6. <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=5.2.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
7. </DbProviderFactories>
8. </system.data>
9.  
10. SqlDataSource reference
11.  
12. <asp:SqlDataSource ProviderName="MySql.Data.MySqlClient" ID="dsData" runat="server" ></asp:SqlDataSource>
13.  
14. Download this code: MySqlRemote.txt

Too bad the MySql GUI can’t handle this, but it is what it is:

If your text file lines end with carriage return / line feed, you will need \r in addition to \n to have clean data import:


LOAD DATA LOCAL INFILE 'c:\\www\\package.txt'
INTO TABLE businesscategory
LINES TERMINATED BY '\r\n'
(businesscategory);


I think SSIS is great, but date formatting still remains a bit tricky. Would be nice to have a straight format function, but this is what I needed to use in the expression portion of the Derived Column Transformation Editor in order to get the date format YearMonthDay :

I started getting this error out of the blue when trying to access a hosted 2005 db through SSMS 2008.

The server principal “db1” is not able to access the database “db2” under the current security context. (Microsoft SQL Server, Error: 916)

No databases would display under the databases tab and every refresh would result in the same error. I could see the dbs in SSMS 2005.

The fix is pretty weird. Click on the Databases folder, then View / Object Explorer Details. Then right click in the column headings and un-select Collation. Refresh the folder and the dbs should be there.

To delete the user go to Sql Server Management Studio, expand your database -> Security and select Schemas. In the Object Explorer Details (if not visible go to the View Menu select Object Explorer Details) you can see a list of the schemas and the owners.

Now locate the schema(s) of the user you want to delete, right click and select properties. In General you can see the schema owner, change it to the new owner (dbo for example).

When the user you want to delete has no schemas, you can delete it.

When adding an identity column to a table, I would sometimes get the following error:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

In order to prevent this warning from occurring, Go to Tools > Options > Designers > Table and Database Designers > Uncheck the checkbox ‘Prevent saving changes that require the table to be re-created check box’

Screenshots

I was automating SSIS packages through .Net and would get this error when trying to run some packages when shelling out to dtexec.

The task “Script Task” cannot run on this edition of Integration Services. It requires a higher level edition.

It turns out that you need to have SSIS installed on the machine where you are running the app, as dtexec is not a client tool, like dts was.

Detailed explanation

SSIS: Change an existing log file through dtexec

dtexec
/f “C:\Share.dtsx”
/l “DTS.LogProviderXMLFile;log.xml”
/Set “\package.Connections[log.xml].Properties[ConnectionString];C:\log2.xml”

Note: The log file (i.e. log.xml) must already be configured in the package

For Sql 2008, you need to add “.1” to the log provider type – DTS.LogProviderXMLFile.1

Here is a sql server script I found that will change all tables to the dbo owner.

sp_MSforeachtable @command1=”EXEC sp_changeobjectowner ‘?’,’dbo'”

I configured a Sql 2005 Maintenance clean-up task to delete all .bak files older than five days, but the historical backups were never deleted.

The only way I could get the clean-up task to function was to change the .bak extension to *.*. Now, all files older than five days are removed. Go figure.

To enter a NULL value into a table through Management Studio, use ctrl+0 (zero)

I was looking for a way to return an Identity value from a dynamic sql statement.

The simplest way is to pass an output parameter along with the sql:

Dim arrParams(0) As SqlParameter
arrParams(0) = New SqlParameter("@iSCID", SqlDbType.Int)
arrParams(0).Direction = ParameterDirection.Output


Then add the following to the end of the sql statement: ” ; Set @iSCID = @@Identity ”

You can then reference the value in this manner (I use the SqlHelper class from MS):

This page has a lot of great information.

The Curse and Blessings of Dynamic SQL

UPDATE: I ran into a situation where I was using triggers for an audit log that caused the above method to return weird @@Identity values. Turns out, if your trigger inserts into another table, that @@Identity will be returned, not the @@Identity from the intended table.

Detailed info – Excerpt below

on the side note: you should NEVER be using @@IDENTITY to get the last inserted identity from a table. this has been shouted down from the havens numerous times. @@IDENTITY will return the last inserted identity from ANY table of TRIGGER within the transaction session. you MUST use SCOPE_IDENTITY(). even IDENT_CURRENT is dangerous from the BOL about the dangers of using anything but SCOPE_IDENTITY():

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Usually, setting Recovery Model to Simple in Options is enough to get started in shrinking a Sql Server db.

However, you may also need to rebuild clustered indexes on large tables. This reorganizes the index and releases the space.

I was building a large, dynamic pivot table which was getting the above error.

The solution turned out to be simple, but a little bit irritating.

I was calling exec @sql, which resulted in the error.

Changing the above to exec(@sql) worked for sql statements greater than 500 characters.