Tips & tricks

I was trying to link a Progress database to an SQL Express server few days ago. The connection has first worked fine, but at one moment, I was not able to run any queries and finally got this error message : DataDirect ODBC Progress OpenEdge Wire Protocol driver Broker rejects connection In my case, this error was caused by too many active connections from the user I used to connect from ODBC. But all the applications were closed. So I've used the utility proshut to list all connected users and to kill the unreleased connections: list connected users : [path to proshut] proshut.bat [path to your db] .db -C list kill a specific user :  [path to proshut] proshut.bat [path to your db] .db -C disconnect [user number] #1 #2

Run the following query : --tables select 'EXEC sp_dropextendedproperty @name = ''MS_Description'' ,@level0type = ''schema'' ,@level0name = ' + object_schema_name(extended_properties.major_id) + ' ,@level1type = ''table'' ,@level1name = ' + object_name(extended_properties.major_id) from sys.extended_properties where extended_properties.class_desc = 'OBJECT_OR_COLUMN' and extended_properties.minor_id = 0 and extended_properties.name = 'MS_Description' --columns select 'EXEC sp_dropextendedproperty @name = ''MS_Description'' ,@level0type = ''schema'' ,@level0name = ' + object_schema_name(extended_properties.major_id) + ' ,@level1type = ''table'' ,@level1name = ' + object_name(extended_properties.major_id) + ' ,@level2type = ''column'' ,@level2name = ' + columns.name from sys.extended_properties jo...

If you want to automatize the backup of your Azure database on your own backup server, you can develop your own utility in few lines of C# code : First you have to install SSMS 2012. After, in your project, reference the assembly Microsoft.SqlServer.Dac.dll located in C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin . Finally, you can export a bacpac file (containing both Schema and Data of your database) like this : var ds = new DacServices("Server=tcp:your_server.database.windows.net,1433;User ID=your_user@your_server;Password=your_password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"); ds.ExportBacpac("test.bacpac", "your_database_to_export"); The connection string can be found on the Windows Azure Portal >  SQL Databases > your_database > quick glance > Show connection strings > ADO.NET #1

If Analysis Services is repeatedly crashing with the following error on a Windows Server 2008 R2 x64 running SQL Server R2 x64 SP1 : 1 Log Name: Application 2 Source: MSSQLServerOLAPService 3 Date: 6/09/2012 08:58:13 4 Event ID: 2 5 Task Category: (289) 6 Level: Information 7 Keywords: Classic 8 User: N/A 9 Computer: rto-dwu.effigy.be 10 Description: 11 The system is out of memory. 12 Event Xml: 13 <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> 14 <System> 15 <Provider Name="MSSQLServerOLAPService" /> 16 <EventID Qualifiers="16673">2</EventID> 17 <Level>4</Level> 18 <Task>289</Task> 19 <Keywords>0x80000000000000</Keywords> 20 <TimeCreated SystemTime="2012-09-06T06:58:13.000000000Z" /> 21 <EventRecordID>332713</EventReco...