Concatenating the same column from different rows in a sql query

SELECT DISTINCT a.Author,
 Books = STUFF(( SELECT ', ' + BookTitle
   FROM Books as b
   WHERE b.Author=a.Author 
   ORDER BY BookTitle
   FOR XML PATH('')
   ), 1, 1, '')
FROM Books as a
INNER JOIN (
 SELECT Author, COUNT(BookTitle) as BookCount
 FROM Books
 WHERE NOT Author IS NULL
 GROUP BY Author
 HAVING COUNT(BookTitle) > 5) as c ON a.Author=c.Author

Query for suggesting potentially useful indexes for the database

Here is a query I found useful for determining which indexes should be created on a table. It is only good for the tables that have been in use, not the brand new ones.

SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) 
AS [index_advantage] ,
migs.last_user_seek , 
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns , 
mid.inequality_columns , 
mid.included_columns , migs.unique_compiles , 
migs.user_seeks , 
migs.avg_total_user_cost , 
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) 
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) 
ON migs.group_handle = mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) 
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC

Reading data from Azure Storage Blob into a string

The code below I used to read the text from the file located on Azure blob into a string:

string filename ="mytargetfile.txt"
string blobConn = CloudConfigurationManager.GetSetting("BlobConn");
CloudStorageAccount storageAcct = CloudStorageAccount.Parse(blobConn);
CloudBlobClient blobClient = storageAcct.CreateCloudBlobClient();
CloudBlobContainer blobContainer = blobClient.GetContainerReference("container1");
if (blobContainer.Exists(null, null))
{
    CloudBlockBlob blockBlob = blobContainer.GetBlockBlobReference(filename);
    if (blockBlob.Exists(null, null))
    {
         //read data from the blob into a stream and get it into an array
         using (var ms = new MemoryStream())
        {
             blockBlob.DownloadToStream(ms);
             ms.Position = 0;
             using (var reader = new StreamReader(ms, Encoding.Unicode))
             {
                   var fileText = reader.ReadToEnd();
             }
        }
    }
}

Saving file from Azure Storage Blob onto a hard drive using C#

Recently started working with Azure storage blob. So far I am really enjoying it so I wanted to share some code snippet I have wrote so far for various purposes. The one below I have used to save the file from the blob onto my hard drive.

//reading blob file data into a file on your hard drive
string filename ="mytargetfile.txt"
string blobConn = CloudConfigurationManager.GetSetting("BlobConn");
CloudStorageAccount storageAcct = CloudStorageAccount.Parse(blobConn);
CloudBlobClient blobClient = storageAcct.CreateCloudBlobClient();
CloudBlobContainer blobContainer = blobClient.GetContainerReference("container1");
if (blobContainer.Exists(null, null))
{
     CloudBlockBlob blockBlob = blobContainer.GetBlockBlobReference(filename);
     if (blockBlob.Exists(null, null))
    {
          string tempFile = Path.Combine(@"C:\BlobFiles",filename);
          blockBlob.DownloadToFile(tempFile, FileMode.Create);
    }
}

How to loop through multiple cookies from an HttpWebResponse

Dim request As HttpWebRequest = CType(WebRequest.Create(args(0)), HttpWebRequest)
request.CookieContainer = New CookieContainer()
Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)

' Loop through the cookie collection and display each property 
Dim _cookie As Cookie
For Each _cookie In  response.Cookies
     Console.WriteLine("Cookie:")
     Console.WriteLine("{0} = {1}", _cookie.Name, _cookie.Value)
     Console.WriteLine("Domain: {0}", _cookie.Domain)
     Console.WriteLine("Path: {0}", _cookie.Path)
     Console.WriteLine("Port: {0}", _cookie.Port)
     Console.WriteLine("Secure: {0}", _cookie.Secure)

     Console.WriteLine("When issued: {0}", _cookie.TimeStamp)
     Console.WriteLine("Expires: {0} (expired? {1})", _cookie.Expires, _cookie.Expired)
     Console.WriteLine("Don't save: {0}", _cookie.Discard)
     Console.WriteLine("Comment: {0}", _cookie.Comment)
     Console.WriteLine("Uri for comments: {0}", _cookie.CommentUri)
     Console.WriteLine("Version: RFC {0}", IIf(_cookie.Version = 1, "2109", "2965"))

     ' Show the string representation of the cookie.
     Console.WriteLine("String: {0}", _cookie.ToString())

     ' Show sessionId of the cookie.
     Console.WriteLine("SessionId: {0}", _cookie.Value)
Next _cookie

Windows 8 Hell or why my Internet Explorer does not function properly

Have you ever opened your Internet Explorer (on Windows 8 computer) and find that it has an address bar at the bottom and is always maximized? Well, I did. I have been using my laptop for a while but only a few days ago I came to worked, opened IE and saw this new and unwelcome change. I tweaked it around for a while until magically things were back to normal. And here is what I found - apparently Windows 8 had two versions of Internet Explorer, the hellish one (tile version) and... well, the normal one. To open the normal one, open it by clicking on the Desktop tile and then click IE icon in the taskbar. If it is not there, go to all programs, right-click on Internet Explorer and select "Pin to Taskbar". Hope this will help someone

How to pass multiple variables to the Arguments property of a Execute Process Task

1. Right-click anywhere in the Control Flow window and select "Variables"
2. Create whatever number of variables you will need to pass to your task
3. Double-click on the Execute Process Task and open it in Editor
4. Select "Expressions". On the right-hand side, click elipse to open Property Expressions Editor
5. In Property dropdown select "Arguments", and in Expressions click on elipse and to open Expression Builder
6. In expression window type your expression. (Example: @[User::Variable1] + " " + @[User::Variable2] + " " @[User::Variable3])
7. When done click "Evaluate Expression" button then click OK to close the editor

Note: If variables you create are not of type String, you will have to cast them to String in your expression

How to rename and/or modify table column in SQL Server

Column rename is done using sp_rename stored procedure:

sp_RENAME '<tableName>.<oldColumnName>' , '<newColumnName>', 'COLUMN'

And column data type and/or nullability is modified the following way:

ALTER TABLE <schemaName>.<tableName>
ALTER COLUMN <columnName> nvarchar(200) [NULL|NOT NULL];

Escaping underscore or other special characters in SQL query WHERE clause

There are two ways to escape underscore in SQL Server: square brackets and specifying escape character

SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%\_%' ESCAPE '\'

or

SELECT * FROM TABLE1 WHERE FIELD1 LIKE '%[_]%' 

Retrieving all the user created users, roles, and associated permissions in Azure SQL

I have been struggling every time I needed to retrieve all the users, roles, and the associated permisisons that I have created since Azure SQL is a little different from SQL Server I am used to when it comes to user administration. So I came up with this query that I'd like to share:

SELECT p.[name] as 'Principal_Name',
   CASE WHEN p.[type_desc]='SQL_USER' THEN 'User'
   WHEN p.[type_desc]='DATABASE_ROLE' THEN 'Role' END As 'Principal_Type',
   --principals2.[name] as 'Grantor',
   dbpermissions.[state_desc] As 'Permission_Type',
   dbpermissions.[permission_name] As 'Permission',
   CASE WHEN so.[type_desc]='USER_TABLE' THEN 'Table'
   WHEN so.[type_desc]='SQL_STORED_PROCEDURE' THEN 'Stored Proc'
   WHEN so.[type_desc]='VIEW' THEN 'View' END as 'Object_Type',
   so.[Name] as 'Object_Name'
   FROM [sys].[database_permissions] dbpermissions
   LEFT JOIN [sys].[objects] so ON dbpermissions.[major_id] = so.[object_id] 
   LEFT JOIN [sys].[database_principals] p ON dbpermissions.  [grantee_principal_id] = p.[principal_id]
   LEFT JOIN [sys].[database_principals] principals2  ON dbpermissions.[grantor_principal_id] = principals2.[principal_id]
   WHERE p.principal_id > 4

Adding principal_id > 4 ensures removal of dbo, public etc...

How to enable the function key on Windows

Enable function key -> Control Panel -> Hardware and Sound (Category) -> Windows Mobility Center -> Adjust Commonly Used Mobility Settings Look Under Function Key Behavior and change the dropdown value to "Function Key" to enable Fn key. To disable it, select "Multimedia Key"

How to export data into SQL Azure database

1. From Management Studio right-click on the database you'd like to export data from
2. Go to Tasks -> Export Data
3. Select your source database - regular SQL Server 2008 or 2012
4. For the destination database (your Azure database), select .NET Framework Data Provider for SQL Server
5. Enter your Azure server name [serverName.database.windows.net], also fill in User ID and Password, Set Encrypt property to True and Integrated Security to False
6. Click on Next and select the tables you are looking to import into SQL Azure

ORA-01031: insufficient privileges

Error: ORA-01031: insufficient privileges

Details: Occurred when attempting to shutdown the database using SHUTDOWN ABORT while logged in as oracle user.

Solution:
The solution was quite simple: exit sqlplus and login again as sysdba

ORA-12537: TNS:connection closed - standby error in DGMGRL

Error: ORA-12537: TNS:connection closed

Details: Occurred on a standby database after switchover from primary to standby. Error came up when checking DataGuard broker configuration.

Cause: The mrp process not running on standby
Solution:
Checked if MRP process was running on standby by executing:

ps -ef | grep mrp

Nothing was running.
Performing regular maintenance on standby database fixed the issue

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Error: Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Details: Gotten this warning after switching over from primary to standby. All redo logs were applied and no errors showed up in alert log, but DataGuard shown the warning in standby

Cause:
Solution:
When I executed:

SELECT DEST_NAME,DATABASE_MODE,RECOVERY_MODE,PROTECTION_MODE,STATUS FROM V$ARCHIVE_DEST_STATUS
WHERE DATABASE_MODE='MOUNTED-STANDBY';

I'd get:

--------------------------------------------------------------------------
DEST_NAME           DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE      STATUS
--------------- ----------------------- -------------------- --------------
LOG_ARCHIVE_DEST_3  MOUNTED-STANDBY    MANAGED               MAXIMUM AVAILABILITY VALID

So RECOVERY_MODE came up as "MANAGED", meanwhile it should have been "MANAGED REAL TIME APPLY". Restarting MRP, as it was suggested by every source I could find, did not help and the next solution was to do that with NODELAY keyword:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

That did not help either. I waited until next logfile switch but the warning was still there. Finally I decided to remove the standby database and then re-add and re-enable it but gotten an error on the attempt to remove it:

DGMGRL> remove database 'Stby';

Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode So my last resort was to disable and re-enable Data Broker configuration:

DGMGRL>disable configuration;

DGMDRL>enable configuration;

Then

DGMGRL>show configuration;

finally returned SUCCESS.

How to fix a DataGuard issue - logs do not ship from primary to standby

One way to check if logs get shipped and applied from primary to standby is by running the following query on both primary and standby databases:

select al.thrd "Thread",
almax "Last Seq Received",
lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

If there is a discrepancy between primary and standby, one way to fix it is by rolling forward the physical standby using RMAN incremental backups of the primary. Here is a document on how to do that.
On Standby:

1. Retrieve the current SCN number to be used for backing up database later on

   
   SQL> SELECT to_char(current_scn, '999999999999999999') FROM V$DATABASE;
   
   TO_CHAR(CURRENT_SCN
   -------------------
          XXXXXXXXXXX
   

2. Cancel the managed recovery on standby

   SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   

Primary:
Connect to RMAN

rman

RMAN> connect target /

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN XXXXXXXX DATABASE FORMAT '/tmp/DBNameStandby_%U' tag 'DBNAMESTANDBY';

Standby:
Connect standby via RMAN and then connect to the catalog:

rman 

RMAN>connect target /

RMAN>connect catalog username/pwd@catalogname

RMAN>catalog start with '/tmp/DBNameStandby';

RMAN>REPORT SCHEMA;


RMAN>STARTUP FORCE NOMOUNT;
RMAN>RESTORE STANDBY CONTROLFILE FROM TAG 'DBNAMESTANDBY';
RMAN>ALTER DATABASE MOUNT;
RMAN>RECOVER DATABASE NOREDO;

If error occurs "ORA-19573: cannot obtain exclusive enqueue for datafile 5" when executing RECOVER DATABASE NOREDO; Run ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; via sqlplus on Standby
And finally run the following on the standby database via sqlplus:

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

ORA-29283: invalid file operation

Error: ORA-29283: invalid file operation

Details: Error occurs when running a full data pump export

Cause:DATA_PUMP_DIR is not defined or defined incorrectly for that database Solution:Login to sqlplus as sysdba and execute:

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR as 'your dir path here';

SSIS Error : Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state."

Error:Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.

Details: Error occurs after deploying SSIS package to another server

Cause:Incorrect package protection level specified - by default it is EncryptSensitiveWithUserKey
Solution:Change package protection level property to to EncryptSensitiveWithPassword, or EncryptAllWithPassword and provide the password

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR

Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider OraOLEDB.Oracle.1 is not registered -- perhaps no 64-bit provider is available.

Details: Error occurs when attempting to run a SQL Agent job that runs SSIS package that opies data from Oracle to SQL Server

Cause:64 bit OLE DB provider Microsoft.Jet.OLEDB.4.0 is not installed on a 64-bit machine.
Solution:Run the SQL Agent job in 32 bit mode:

1. Right-click on the job name and select Properties
2. Go to Steps
3. Select the step your job fails on and click Edit
4. Select "execution optons" tab and check a "use 32 bit runtime" checkbox

[OLE DB Source [45]]: Cannot retrieve the column code page info from the OLE DB provider

Error: [OLE DB Source [45]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Details: This error occurs attempting to view colmuns of an OleDB data source in a Data Flow task inside the Business Intelligence Development Studio, while attempting to do a data transfer between Oracle and SQL Server databases


Cause:It is actually only a warning that means that the data provider does not publish code pages used for text columns and also that SSIS does not do implicit conversions between Unicode and non-unicode strings

Solution:We can get rid of this warning by setting AlwaysUseDefaultCodePage property of the OLE DB Source Data Flow Component to True. It is set by default to False but can be changed easily from the Properties window in Business Intelligence Development Studio project.