Adding reference in SSIS package

Hello,

The following blog will help users to deploy referenced dll in SSIS package while deploying to Integration Services.

Environment:

1. SQL Server 2008 
2. Visual Studio 2008 (Business Intelligence Projects)

To add reference of any dll in SSIS package, you go to project properties and browse to dll to add as a reference. It should also be in your local machine GAC else it wont work.

SSIS project doesn’t have bin folder so it always look for assemblies from GAC.

Now to run on sql server (using Integration Services), the same dll must be added to GAC on SQL Server machine.

Note: For assembly to be added in GAC, it  must have strong name.

HTH.

The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required.

Hello,
While I was trying to send an email from a website using gmail account, I didn't have any issues testing on my local machine but getting to work from production server was a pain.
I was keep getting error message:

"The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required."

3 things will fix it:

1. Go to security settings at the following link https://www.google.com/settings/security/lesssecureapps and enable less secure apps. This will enable google to login from all apps.
2. Enable other ip/timezone for your google account by clicking on following link and click continue to give access: https://g.co/allowaccess
3. Go to account settings 
• Under Recent Activity section, click on "Notifications and Alerts" 
• Now there you will see "Unusual Activity". 
• Select your related event and approve it by clicking "Yes, That was me!".   This way google will add this clear this activity from suspicious list of activities.

HTH

Why my website is running in 32 bit mode in Visual Studio?

I was working on a website in Visual Studio 2013 in 64 bit machine. The properties of solution/project was set to run as Any CPU which means to run the app as 64 bit when 64 bit processor found else 32 bit.

In my 64 bit machine, the website was being starting as 32 bit and the reason was that the process hosting the website was "IISExpress" and it was being run in 32 bit mode.

Again, the Any CPU flag also follow the caller of the app. 

If the caller of app is running in 32 bit, the application would run in same as well.

Anyways, to change the IISExpress to run as 64 bit, check the box under :

Tools | Options | Projects and Solutions | Web Projects | Use the 64 bit version of IIS Express

HTH

SQL Server Stored Procedure Alter / View / Execute Permissions

There are different ways to grant user permission to alter, view or execute stored procedures.
The easiest approach I found was to create a "Database Role".
Name anything you like. I liked "db_executor" role.
Now give this role permissions and then add users to this role.

So..

/* Create a new role for stored procedure permissions */
CrEATE ROLE db_executor

/* Grant stored procedure Alter/View/Execute rights to the role */
GRANT ALTER, VIEW DEFINITION, EXECUTE TO [db_executor]

/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', [domain\userid]  -- No single quotes around user.

Reboot Server remotely

From command prompt, type this with proper credentials:  shutdown /r /m \\servername /t 0

Brother Printer Replace Toner Message

If you ever get in a state where your printer hold you as hostage because of low toner message, follow following steps to reset the toner flag:

1. First find the IP address of your printer.
    To find IP address,

•    -> Go to control panel -> Devices and Printers
•    -> On you printer, right Click on printer and click on Printer Properties
•    -> Select Ports tab
•    -> Click Configure Port
•    -> In the window, you will see Printer Name
•    -> Go to command prompt, type ping <printerName>
•    -> Ping command will display IP address.

2. Open your browser window. Type in the IP address and hit Enter.

3. This will default page of printer. 

4. On the Right side, Click on "Replace Toner" link. This will take you to replace toner page.

5. Click "Continue" and hit Submit.

6. Done.

HTH.

How to find tables in FileGroup in SQL Server?

To find all the objects / tables / Indexes  that exist in a Filegroup, use following query:

SELECT
      f.[name] AS [FileGroupName]
    , OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.object_id = o.object_id
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND p.index_id = i.index_id
WHERE o.type = 'U' -- User Created Tables
-- and f.name = '<filter for interested filegroup using name>'
order by p.rows