Changing the Server Name of your SQL Server

In this day and age of virtualization, it’s not uncommon to copy a base VM, rename it and away you go.  But what if SQL Server 2005 is already installed on it?  How does renaming affect it?

At first glance it may appear that it has had no affect at all, queries will still work, logins still work.  However, there are a few subtle and not so subtle side affects.  The first is what SQL Server thinks it’s name is.  If you run the following statement:

SELECT @@SERVERNAME

You’ll notice that it returns the old server name, not the new one.  This happens even though the name shown in the Object Explorer in Management Studio is showing the correct new name.  This will cause a clean Dynamics GP 10 install to fail when attempting to create the “Remove Posted PJOURNALs From All Companies” SQL Agent job as it uses the @@SERVERNAME variable to set that up.

To configure SQL Server to know its new name you’ll have to run the following two statements:

EXEC sp_dropserver 'OLDSERVERNAME'
EXEC sp_addserver 'NEWSERVERNAME', local

Once you’ve run these statements, restart the SQL Server service.  This will cause SQL Server to now return the new server name from the @@SERVERNAME variable.

A more noticeable side affect of the server name change is that SQL Reporting Services (SSRS) will not work.  This is because it is looking to the old server name for the reporting services databases (ReportServer and ReportServerTempDB).  To modify this, perform the following steps:

1. Run the Reporting Services Configuration tool (Start/All Programs/Microsoft SQL Server 2005/Configuration Tools/Reporting Services Configuration).
2. Navigate to the “Database Setup” page.
3. Change the Server Name to the new server name and click Apply.

SSRS will now work.

Now what about changing domains?  That’ll have to be another post.

Run a Script Against Multiple Companies by Creating SQL from SQL

This subject isn't the grand topic I had envisioned for my initial post but is a neat little trick I think many would find useful. So here goes the blog!

We recently had to create users and assign SQL permissions to over 200 Dynamics GP companies for an integration. The integration is external to the Dynamics GP client and accesses the SQL databases directly rather than through the client, so SQL security had to be setup. There are a couple of options:

• Click through SQL Management, creating users and assigning to the appropriate role (very tedious).
• Create a SQL script that sets up the security and run it against each company database (slightly less tedious).

Let’s rule out the first option right away. With the second option, the script might look something like this:

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN ''MYDOMAIN\INTEGRATION USERS''

GO

EXEC sp_addrolemember [Integration Users], [MYDOMAIN\INTEGRATION USERS]

GO

Here we’re creating a user in the database and adding it to the appropriate role. However, we still have to run it against each company databases. Pick database from dropdown, click execute, wait, repeat.

If only there were an easy way to generate a script that would run against each database.

Well there is! With a simple SQL statement we can generate a script that will run against every company.

--  Don't forget to set Results to Text in your
--  SQL Management Studio query window.
--
--  Set NOCOUNT on so that row count doesn't show
--  up in the text results.
SET NOCOUNT ON;

SELECT
REPLACE(
'
USE {COMPANYDB}
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember ''Integration Users'', ''MYDOMAIN\INTEGRATION USERS''

GO
',
'{COMPANYDB}', INTERID)
FROM DYNAMICS.dbo.SY01500

In the SQL statement above we’ve taken our security script, added a USE statement at the top to set the company and placed it inside a SQL statement. Note that the USE statement has tag {COMPANYDB} instead of a SQL database name. The SELECT statement replaces this with the INTERID from the SY01500 GP companies table in the DYNAMICS database.

This statement will create a single row for each company containing a single column with the resulting security script. When concatenated together you get the following script (this was run against a GP instance with two companies):

USE AAA
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember 'Integration Users', 'MYDOMAIN\INTEGRATION USERS'

GO

USE TWO
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember 'Integration Users', 'MYDOMAIN\INTEGRATION USERS'

GO

To get this to work in SQL Management Studio we need to send the results to text instead of the grid. This can be set in the Query/Results To menu as shown here

or clicking on the To Text button in the tool bar shown below:

Note that by default, the maximum number of characters show column per row when sending results to text is 256. This can be changed in Tools/Options screen, navigating to Query Results/SQL Server/Results to Text and updating the Maximum number of characters in each column to the maximum number of characters in your company script.

This trick could be used for any number of tasks that need to be run against every company such as creating custom stored procedures or performing mass backups and restores into test environments.