SQL Server

I Am Speaking at Desert Code Camp April 2nd, 2011 in Chandler, AZ

Posted in SQL Server on March 31st, 2011 by admin – Be the first to comment

If you happen to be in the Phoenix metro area this weekend make sure to come out to Desert Code Camp (http://apr2011.desertcodecamp.com). I will be speaking on SQL Server Development focusing on Stored Procedures, Function and Common Language Runtime (CLR).

This will be the first time I have attended/presented at Desert Code Camp myself and I am extremely excited. After speaking at the first SQL Saturday #47 back in February I got the buzz supporting the local tech community and taking advantage of the opportunity to share and network with other professionals that work close to me. Twitter, Facebook and all of the other social options are great but nothing compares to the in person interaction. The best part of all is that these events are free to the public allowing anyone to come and learn no matter the level of experience.

Desert Code Camp is of particular interest as it offers a wide variety of sessions in all sorts of technologies giving others that would like to learn SQL a chance to branch out and also so that I can attend sessions and meet people in other areas of IT that I have not experienced.

I hope to see you there. If you do come you can hit me up on twitter (@speedracer) or just drop by my session at 11:30 in room IRN-124.

Report Builder 2.0

Posted in SQL Server, SSRS, Tools on November 4th, 2008 by speeddba – Be the first to comment

For any of you out there that have embrased or at the very least attempted to embrase the SQL Server 2005 Reporting Services Modeling and Report Builder you should definitly check out the newest release of Report Builder.

Report Builder 2.0 offers many new features including the Tablix table formating, multiple data sets and data sources. Many of the features of Business Intelligence Studio’s Report Designer were also brought over allowing for any report to be opened not just those designed in Report Builder.

One thing to not is that Report Builder 1.0 is still the Click Once application and will continue until possibly the release of the next Service Pack or possibly not til Kilimanjaro (SQL Server 2010?). For now it is available as a standalone client install available for download here: http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&DisplayLang=en

Microsoft SQL Server Management Pack for Operations Manager 2007

Posted in Replication, SQL Server, SSIS, SSRS on November 4th, 2008 by speeddba – Be the first to comment

Looking for a good way to monitor your SQL Servers. Microsoft released the Management Pack (MP) for Operations manager 2007. This MP is capable of monitoring many of the key elements of SQL and Windows Server for CPU, Drive/File Space, Memory, SQL Agent Jobs, Blocking and tons of other goodies.

Check out more and download here: http://www.microsoft.com/downloads/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&DisplayLang=en

Developing and Running SQL Server Integration Services 2008 (SSIS) on Windows Server 64 bit Editions

Posted in SQL Server, SSIS on November 3rd, 2008 by speeddba – Be the first to comment

I recently ran into an issue with developing SSIS packages on a development server running SQL Server 2008 64 bit (X64). After intalling the 64 bit 10G Instant Client and Data Access Components (DAC), I was able to create a linked server and connect to as one would expect to an Oracle server.

The fun started when trying to develop against the same Oracle Servers in SQL Server Integration Services 2008 (SSIS). After recieving a slue of Driver and TNS Errors I found some compelling information. As it turned out (and I should have realized) the Business Intelligence Development Studio (i.e. BIDS, Visual Studio 2008) is a 32 bit application. It runs under the “Program Files (x86)” directory.

In my adventure to get through this I installed the 32 bit Oracle Instant Client and DAC side by side with the 64 bit varient. But the fun doesn’t stop here. As it turns our Oracle for one reason or another seems not to like the “Program Files (x86)” directory. Specifically it seems not to like the ‘(‘ and ‘)’ characters.

To get around this you need to run any programs from the directory using the old school methods of Progra~2. In other words to run and develop in BIDS from the following command: C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe

As it turns out SSIS when installed on a 64bit Server has several components that include:

  • Visual Studio (BIDS) is a 32bit application.
  • SQL Server Management Studio is a 32bit application.
  • dtexecui.exe is a 32bit application.
  • dtexec.exe has both 32bit and 64bit versions.
  • SQLAgent is a 64bit process. 
  • When running a package from BIDS the process will use the 32 bit edition and the x86 Oracle drivers. the same holds true when running the package interactively from Managment Studio (SSMS). However, when scheduled the package will run in 64 bit and utilize the 64 bit drivers.

    Now with this one could assume that you could call the command direct running the 32 bit dtexec.exe for other 32 bit drivers and I plan to test in the very near future with a legacy driver (Rumba/AS400).

    I hope this helps some one. A big thanks goes out to Greg Galloway and Steve McHugh for the help and information around this issue.

    Failed sending mail: The user or group name 'Domain\User' is not recognized

    Posted in SQL Server, SSRS on October 17th, 2008 by speeddba – Be the first to comment

    For any of you that work closely with Reporting Services, I am sure you can vouch that the system offers a great amount of flexibility and capability. At the same time though you also have probably encountered your fair share of strange, random and undocumented issues. One today I found happened with a Subscription.

    Failed sending mail: The user or group name ‘Domain\UserNoMore’ is not recognized

    Recently, we had a user leave the company. For the sake of this article we will happily refer to him as UserNoMore. When you create a subscription you are not propted to define an owner. You get the happy assignement automagically. When the user account is deactivated authentication ceases and emails and file archives begin to fail.

    In my experience here it took a little finess and hackery to correct. Now let me first say I niether condone nore dismiss the need of making direct changes to data. So in this case after attempting any known fix or finding any interface I determined the fix would be in a bit of DML. Really the fix was pretty simple. Find the subscription, determine a different user to own, update the subscriptions. In this case I selected the service account for Reporting Services so I wouldn’t have to worry later (and I guess technically you could do this for all subscriptions if thats how you shop is set security wise).

    In the ReportServer$InstanceName database for reporting services you will find 3 key tables:

    Catalog – Reports, Folders and basically all Items
    Subscriptions – The scheduled report processes
    User – Well…any user or group in Reporting Services

    Step 1- Get the Report ID in Question

    SELECT [ItemID], [Name]
    FROM [ReportServer$InstanceName].[dbo].[Catalog]
    WHERE [Name] ='MonthlySalesReport'
    and [Type] = 2 --Report Item Type
    

    Step 2 – Find the Subscription

    SELECT [SubscriptionID],[OwnerID],[Report_OID]
    FROM [ReportServer$InstanceName].[dbo].[Subscriptions]
    WHERE [Report_OID] = '1551AA5B-0FF1-494B-8725-73E22AA34D9F'
    

    Step 3 – Select another User

    SELECT [UserID],[UserName]
    FROM [ReportServer$InstanceName].[dbo].[Users]
    WHERE [UserName] = 'Domain\RSServiceAccount'
    

    Step 4 – Update the Subscription

    UPDATE [ReportServer$InstanceName].[dbo].[Subscriptions]
    SET [OwnerID] = '6BDB4964-EE28-4E96-BCB2-E4509FAC909B'
    WHERE [OwnerID] = '3D272CE2-A9B3-4B89-8993-C72E8D43A6EE'
    AND [Report_OID] = '1551AA5B-0FF1-494B-8725-73E22AA34D9F'
    

    Really an easy fix…not clean but it worked for me. Feel free to provide feedback.

    Enjoy!