You are hereProgramming & Web Design / Databases


Databases

warning: Creating default object from empty value in /home/hagrinad/public_html/modules/taxonomy/taxonomy.pages.inc on line 33.

Databases

SQL Reporting Services: Fixing the "No Letter Spacing" When Printing Problem

By hagrin - Posted on 07 June 2011

Seemingly out of nowhere, my users were complaining that while their SQL Reporting Services (SSRS) reports were displaying fine on screen once they printed them the font was all screwed up and possibly stretched horizontally. The font looked stretched and there was no spacing between the letters of a word. It happened on all machines using all printers to all users. It broke in a 3 hour window where no patching, reboots or changes were really made.

Our environment consists of -

  • SQL Server 2008 Standard Edition with SP2 64-bit
  • Windows Server 2008 64-bit
  • SQL Server Reporting Services
  • Virtualized using VMWare

After looking at program settings, print queues, etc., a very random internet search provided me with the starting blocks to how to fix this problem. It seems that the problem occurs when after the SQL Server is rebooted, the first person who logs in (usually via Remote Desktop) logs in with a session in 16:9 aspect ratio (widescreen). Weird right?

To fix this problem, you must do the following in this order -

  • Reboot the SQL Server
  • After the SQL Server has finished rebooting, Remote Desktop into the server using the following command - mstsc /v:SERVERNAME /admin /w:1024 /h:768 (this forces the needed 4:3 aspect ratio)
  • Now, try running your reports.

As a side note, just restarting the services did not fix this problem - a reboot was required. Hopefully, this helps some people and people are able to find this post (it was difficult to title this post to make it as helpful as possible). Good luck!

INNER JOIN on the Same Table

By hagrin - Posted on 04 January 2011

Here's something that always bugged me that I finally was able to figure out today (that in retrospect is so simple I don't understand why I struggled so much with it before).

If you've ever looked at the data stored in a SharePoint 2007 / WSS v3 database, you know that the AllUserData table stores an immense amount of list data. Say you had two lists - a list of Employees and a list of Tasks and in the Tasks list you had a Lookup field using the Employees list. You would have a setup like this to ensure data validation, manage a list of employees in a single location and other data structure reasons. So, how would you write a single, direct SQL query against the AllUserTable to return data in two different lists?

Look at the following query -

SELECT a.tp_ID, a.nvarchar1, a.nvarchar3, a.nvarchar4, a.nvarchar5, a.datetime1, b.nvarchar1, a.ntext3, a.tp_Modified
FROM AllUserData a
INNER JOIN AllUserData b ON a.int2 = b.tp_ID AND b.tp_ListID = '{a234a2a6-dbf2-4abf-b1d4-1544305b9cde}'
WHERE a.tp_ListId = '{4BE243EE-E100-4060-9921-840DEB0F54B0}' and a.tp_IsCurrent = '1' and a.nvarchar4 <> 'Completed'
and a.tp_Modified < '12/1/2010' and a.tp_ContentType = 'Item' and a.tp_DeleteTransactionID = 0x
ORDER BY b.nvarchar1, a.tp_Modified

There's two important aspects in the above query that you need to apply in your own queries. First, notice that there is an INNER JOIN statement that joins AllUserData a and AllUserData b. You effectively name the same table twice and designate it with an alias to differentiate the two lists you are trying to join. Second, notice in the INNER JOIN statement that there is an AND operator that allows you to focus your INNER JOIN on only relevant data inside that table.

Hopefully, this little code snippet helps you in your SharePoint development and SQL query writing.

How To: Setting Up Database Mirroring on SQL Server 2005

By hagrin - Posted on 22 June 2010

After fixing a SQL Server naming issue, I was ready to continue on with my SQL Server 2005 database mirroring project for my client. The resources for setting up database mirroring are very light across the web so I decided I would help others out by writing out a "how to" guide. First, here are some assumptions that this guide uses which mimics the environment my client had for this project -

  • All of the servers involved are joined to the same domain.
  • You will be using a Principal server, a Mirror server and a Witness.
  • The Principal and the Mirror servers are the same version of SQL Server 2005 (in this case, all Standard Edition with SQL Server SP3).
  • The Witness server can be a version of SQL Express and does not need to be a full-blown version of SQL Server.
  • The database you want to mirror is in Full recovery mode.

Now, your server don't have to be on the same domain as you can use SQL Certificates; however, you'll find that database mirroring is much easier to setup if all the machines are on the same domain. The first step is to make sure that all of the SQL instances are running underneath the same domain account as opposed to the Local Account. The easiest way to accomplish this is to open up SQL Server Configuration Manager on each SQL instance, click on SQL Server 2005 Services, right click the SQL Server option, choose Properties and on the Log On tab choose "This Account" and enter in the credentials of the domain account you created to run your SQL services. When you are finished, click OK and your SQL services will restart.

Next, on your Principal server, you want to perform a Full database backup on the database you want to mirror. To do this, connect to the Principal server in SQL Server Management Studio, right click on the database, choose Tasks -> Backup, make sure the Backup type is set to "Full" and make sure you choose a Destination with a file extension of .bak. On the Options page, select the "Overwrite all existing backup sets" and then click OK to start the backup process. Once the backup is finished, you will want to connect to the Mirror server and restore the backup by right clicking "Databases", selecting "Restore Database", choosing "From device", clicking the browse button to the right, selecting your backup, checking the checkbox for your database and going back to the top and selecting the name of your database in the "To database" dropdown. Once that is completed, select the Options page, check the "Overwrite the existing database" option and then choose the Recovery state option that has "RESTORE WITH NORECOVERY" at the end and click OK to being the restoring process.

Once the full backup restoring process has been completed, you need to go back to your Principal server and perform a Transaction log backup. To complete this, you will use the same steps as the full backup except that for the "Backup type" you will select "Transaction Log" and then in the Destination box you will first remove the current destination and then Add a destination where you use a filename with a .trn file extension. Once the transaction log backup has been completed, connect to your Mirror server and restore the transaction log. To do this, you will right click on your restored database, choose Tasks -> Restore -> Transaction Log, choose "From file or tape", browse to your .trn backup, select the checkbox for your backup and then on the Options page choose the option with "RESTORE WITH NORECOVERY" at the end.

At this point you are now ready to configure your database mirroring. Connect to your Principal server, right click on the database you would like to mirror, select Tasks -> Mirror and select the "Configure Security" button. This will launch a wizard where you have to specify the names of your Principal, Mirror and Witness servers, connect to all of them and when you get to the "Service Accounts" page, simply click Finish and click Start Mirroring once the wizard has completed. At this point you should have successfully created a quorum and your database should be successfully mirrored and synchronized.

Hopefully, this guide will help you make your databases more redundant and help your organization attain a higher level of availability.

Fixing the "SQL Server replication requires the actual server name to make a connection to the server." Error

By hagrin - Posted on 20 June 2010

Recently, I ran into a whole set of problems setting up a client's SQL 2005 database mirroring solution. I had successfully setup database mirroring numerous times and I couldn't figure out why I was having such a hard time with this instance. I loaded up the Database Mirroring Monitor, tried selecting my Principal server in the mirroring solution and I received the following error -

"SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported."

I was confident that the name I was entering was in fact the current machine name. I decided to run the following query against the master database on the SQL server in question -

SELECT @@SERVERNAME

To my surprise the name listed didn't match the computer name of the SQL Server! At this point, I realized that this server had a different computer name when SQL was installed and the server had been renamed since then. Fixing this problem isn't a trivial renaming of the SQL instance unfortunately. To fix this issue you need to run the following stored procedures -

sp_dropserver 'old_server_name'
GO
sp_addserver 'current_computer_name', 'local'

At this point, if you re-run the SELECT @@SERVERNAME query, you will get a NULL value for the server name even after a restarting of the SQL Service. Just restart the SQLSERVER service and when you re-run the query the new name will now appear.

How To: Fix "The report server database is an invalid version" Error

By hagrin - Posted on 04 August 2008

Recently, I decided to go through my work computer's event log to see if I was racking up any errors and found one error repeated quite frequently -

Source: Report Server Windows
Event ID: 117
Description: The report server database is an invalid version.

Now, I have Microsoft's SQL Server 2005 installed on my work machine for development purposes and the error seems to be directly related to the Reporting Services functionality. After looking at the help for this error in the Help files, Microsoft's web site and even a few search engine searches, I couldn't find the full answer anywhere. However, I finally fixed the error and here's how to do it.
Reporting Services Configuration Manager

  1. Open up the "Reporting Services Configuration Manager" by going to Start -> Programs ->Microsoft SQL Server 2005 -> Configuration Tools -> Reporting Services Configuration.
  2. On the left hand side, click on "Database Setup". (there should be a red "x" to the left of Database Setup.
  3. Choose the appropriate Server Name.
  4. For the Database Name, click "New" and do not use the default name "ReportServer" since it's possible a .MDF file was already created with this name. I used "ReportServer2".
  5. Make sure to choose the "Credentials Type" and enter the account information for a user who has create database permissions.
  6. Click "Upgrade". Make sure you get all green check marks (even a warning message means the process probably didn't complete properly).
  7. Click "Apply" at the bottom.

You should now be good to go and no longer receive this error in your Event Log.

Dynamic SQL Cursors

By hagrin - Posted on 15 May 2007

Today, at work, I was working on creating an application that would perform validated, bulk updates to our accounting system software (Deltek Vision). So, I create a very simple Windows Form, create a real quick GUI, write all the necessary validation and then started writing the stored procedures needed to perform each of the functions. I had already written the SQL syntax as before this application I was manually changing the variables to fit the needs of our business units. However, as I was trying to "generalize" the statements to allow for different types of queries, I started to run into a SQL problem. I was attempting to use dynamic cursors that were being fed by dynamic SQL statements. Yikes.

In English, I had created two cursors - one to read through all the "target" projects and one for all the "source" information. I also gave the user the ability to specify whether or not the target was "equal to", "starts with" or "ends with" a certain criteria. This causes a problem since if you know general cursor syntax you know that a cursor is fed with a select statement as such:

declare wbs_cursor cursor for
select wbs1 as w1 from PR WHERE wbs1 LIKE @targetproject
open wbs_cursor
declare @w1 varchar(30)
fetch next from wbs_cursor into @w1
while @@fetch_status = 0

Now, by giving users the ability to dynamically change the entire WHERE clause (including the operator), you suddenly run into a syntax issue because you can't just store the entire clause in a varchar variable and then have it EXEC SQL (@stmt) because of later references to the cursor. Since that eliminates one major way of preparing dynamic SQL statements, I developed a very dirty solution that revolved around IF statements -

if @targetcondition = 'starts with'
begin
      set @targetproject = @targetproject + '%'
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR WHERE wbs1 LIKE @targetproject
end
if @targetcondition = 'is equal to'
begin
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR WHERE wbs1 = @targetproject
end
if @targetcondition = 'ends with'
begin
      set @targetproject = '%' + @targetproject
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR where wbs1 LIKE @targetproject
end

Pretty dirty right?

Most of the time when I post code here, it's generally the non-dirty version, but I really couldn't find a non-dirty version to this problem and would really like to see if someoone has any ideas on a better, cleaner solution. It seems that all of the sample code I saw online when searching "dynamic cursors" only made table names or WHERE criteria (not the operator) dynamic and therefore fails to appropriately address my issue. I got away with this solution since the problem was simplistic, but what about if I had to deal with 1000 conditions - I wouldn't want to write 1000 IF statements.

Thoughts?

P.S. - As I read through the SQL Books Online, it seems that the PREPARE statement might be able to solve this problem, but I don't have much experience with PREPARE.

VB.Net 2.0, SQL Server 2000 & Windows Application SQL Error

By hagrin - Posted on 11 May 2007

Today, I started working on an application to perform bulk changes to our Deltek Vision database to change tasks, sub-tasks, budgets and billing terms. So, as I go to write the SQL connection string, something I have done 100s of times, I kept getting the following error from my app:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

While the error is pretty descriptive, there is one slight problem - I'm not trying to access a 2005 SQL Server, but a SQL Server 2000 box. So, I do what any good programmer does - he searches the major search engines for a solution, but most of the examples are of users trying to 1) build a Web application and have a web.config error or 2) are actually trying to access a SQL Server 2005 database. Yikes, now what?

I can't stress this enough - check your connection string. I was actually passing a blank Data Source (I was using SelectedValue as opposed to SelectedItem) and since I have SQLExpress running on my development machine, I assume that's why I was generating a 2005 error when trying, in theory, to connect to a SQL 2000 machine. The lesson is - verify your connection string when getting this error and trying to access a SQL 2000 machine. If you're trying to access a SQL 2005 machine, follow the enabling remote connections info that you'll find on the hundreds of pages that come up in he search engines for this error.

SharePoint v3: tp_DeleteTransactionID

By hagrin - Posted on 25 February 2007

When working with SharePoint v3 list data, it's important to understand the data structure used to store list items. List data is stored in the AllUserData table and the name should be your first indication that you might run into a problem if trying to pull or manipulate data stored in this table. Why? Well, "all" user data is stored in that table including deleted data and you will need to know how to filter out deleted items and that is where the tp_DeleteTransactionID column comes into focus.

The tp_DeleteTransactionID column is a varbinary field so you cannot readily read the contents of that column in SQL Server Enterprise Manager. This coulmn acts as a "deleted flag" and you will need to be able to know what items you have removed from your list, but you forgot to remove from your Recycle Bin. List items that you delete from your list, but that remain in your Recycle Bin will have the value of the tp_DeleteTransactionID column updated from the default value of 0x. Once you remove the items from your Recycle Bin, the item's corresponding rows will be deleted from the AllUserData, but until you do so those item's rows will remain in the AllUserData table.

So, if you want to filter out deleted content for the purpose of data manipulation or transformation, but you don't want to have to worry that the user has remembered to empty the Recycle Bin, add the following WHERE clause to your SQL statements -

WHERE tp_DeleteTransactionID = 0x

Notice, that the value 0x is not surrounded by value defining apostrophes since the value is not a true string, numeric, date, etc. value, but a hex representation for the varbinary field.

MySQL Replication

By hagrin - Posted on 12 February 2007

Hynek wrote a great article on MySQL replication and exactly how easy replication is to setup. I definitely enjoyed this article as I was only familiar with replication in MS SQL Server 2000 and 2005 and this article solves one more problem for database admins. Now, with stored procedures and replication introduced to MySQL, MySQL provides another database option for the corporate environment and for IT managers to avoid high priced licensing agreements.

Database Love Today

By hagrin - Posted on 04 August 2006

Today was a slower day than most due to the fact my work VPN seems to be down. Currently, I am in MS SQL 2000 Admin training and it's going extremely well so far. I've definitely learned a few tricks to bring back to work with me, but not enough to take the cert test I don't think. To accomplish that, looks like I will be reading the text on my own.

In other database news, MSFT has released a Community Technology Preview of SQL Server 2005. They released 32 and 64-bit versions which are encompassed over two CDs - one for the actual server and one for the tools. In addition, you can download a MSI for the September 2005 release of the SQL Books Online (easily MSFT's best help) for this specific SQL 2005 release. I'm looking forward to trying it out and providing some feedback here.