script to move all DYNAMICS databases

I frequently have to move data and log files after GP setup to get them into the best practices configuration.  I decided to write a script to move all the common dynamics related databases to new data and log file locations.
This script assumes the system database is named DYNAMICS.
It then handles DYNAMICS, all company databases, resco, report server, smartconnect, and nodebuilder.

--script to move all DYNAMICS databases
--set the new paths and growth rates before running

DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)

SELECT @NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'

DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)

DECLARE C_INTERID CURSOR FOR
SELECT RTRIM(INTERID)
FROM DYNAMICS.dbo.SY01500 D
INNER JOIN sys.sysdatabases S
ON D.INTERID=S.name
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME ='DYNAMICS'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'resco%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'ReportServer%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'SmartConnect%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'NodeBuilder%'

OPEN C_INTERID
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'

SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'

SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))

--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH


SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

END

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', 
 FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', 
 FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME

END

CLOSE C_INTERID
DEALLOCATE C_INTERID

VB script to send an ftp file after exporting the file with smartconnect

Dim  currentdate as Date= Date.Now()
Dim  DateStamp as String=currentdate.Year.ToString() + currentdate.Month.ToString().PadLeft(2,"0") + currentdate.Day.ToString().PadLeft(2,"0")
Dim TimeStamp as String=currentdate.Hour.ToString().PadLeft(2,"0") + currentdate.Minute.ToString().PadLeft(2,"0") + currentdate.Second.ToString().PadLeft(2,"0")

Const username As String = “username”
Const password As String = “password”
Const localFile As String = “\\localshare\filename.txt"

Dim remoteFile As String = “/ftpfolder/filename_" + DateStamp + ".txt”
Dim archiveFile as String = “\\archiveshare\filename_" + DateStamp + "_" + TimeStamp  +  ".txt"


Const host As String = “ftp://username:password@ftpipaddress"
Dim operation as string
Dim buffLength As Integer = 2048
Dim buff(buffLength - 1) As Byte
Try
 If not File.Exists(localFile)   then
return false
end if

Dim URI As String = host & remoteFile

operation="create ftp web request"
Dim ftp As System.Net.FtpWebRequest = CType(System.Net.FtpWebRequest.Create(URI), System.Net.FtpWebRequest)

ftp.Credentials = New System.Net.NetworkCredential(username, password)
ftp.KeepAlive = False
ftp.UseBinary = True
ftp.Timeout = 20000
ftp.Method=System.Net.WebRequestMethods.FTP.UploadFile

operation="getting fileinfo for local file"
Dim _FileInfo As New System.IO.FileInfo(localFile)
ftp.ContentLength = _FileInfo.Length

operation="opening filestream"
Dim _FileStream As System.IO.FileStream = _FileInfo.OpenRead()

operation="getrequeststream"
Dim _Stream As System.IO.Stream = ftp.GetRequestStream()

Dim contentLen As Integer = _FileStream.Read(buff, 0, buffLength)

Do While contentLen <> 0
operation="writing stream"
_Stream.Write(buff, 0, contentLen)
contentLen = _FileStream.Read(buff, 0, buffLength)
Loop

operation="closing"
_Stream.Close()
_Stream.Dispose()
_FileStream.Close()
_FileStream.Dispose()

System.IO.File.Move(localFile,archiveFile )

return true
Catch ex As Exception
'MessageBox.Show("operation=" + operation + "  " + ex.ToString(), "Upload Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
return false
End Try

Coalesce in C#

I ran into an interesting issue today with string math, which enlightened me to a shortcut function in C# that works similarly to the T-SQL function COALESCE.

I was appending strings to StringBuilders and adding strings using string math.  Something like this:

string header=getheader();
string footer=getfooter();
StringBuilder body=new StringBuilder ();
string value1=getvalue1();
string value2=getvalue1();

body.append(header);
body.append(value1 + value2);
body.append(footer);

return body.ToString();

I expected this to always return a string.
However, if value1 is null, there is no error thrown.
The return value is simply null...even if all the other variables have valid strings.

Normally, I would add a function that accepts a string, tests for null, and returns string.empty, as follows:

private static string TestNull(string value){
if(value==null){
return string.empty;
}
else{
return value;
}
}

But what if I want to make it some other value than string.empty?  And what if I need to test that value too?  Now it gets a bit messy.

I remembered the COALESCE operator in T-SQL.

COALESCE makes this clean by letting you do things like this:
select COALESCE(field1,field2,@myfield,'')
The above statement will roll through the values from left to right and use the first one that is not null.

I thought, maybe there is a built in function for handling this in C#.  Google brought forth the golden egg.

?? (null-coalescing operator)

In C#, this operator basically says if the string on the left is null, use the string on the right.  And it can be daisy-chained.

so I can say something like this:
 value1=value2 ?? header ?? string.empty;

Happy coalescing!

Conditionally stop a trigger from firing

There are instances when inserting records into a table that you might not want one or more triggers to fire.  There is an easy way to control this using Context_Info.

The trick is to add code to the trigger(s) that you want to control.

Context_Info() can be set to a hex value in your procedure and then read from the trigger.  It is a sneaky way to pass a parameter to a trigger.

At the beginning of your trigger logic, add the following three lines

IF Context_Info() = 0x20220121 RETURN  


Then in your procedure, just before you do your insert, update, or delete, add the following line

SET Context_Info 0x20220121 

Note that the value 0x20220121 can be any hex value you choose.  However, since it is global, I prefer to use a date for the numbers to avoid stepping on it with a later addition.

Also, initially, Context_Info() will return null until it has been set.

Remember to reset it after the insert, update, or delete

SET Context_Info 0x00000000 

The report server cannot open a connection to the report server database.

I ran into something interesting today with our VM template. 

When I change the machine name, I can run the SQL script on the desktop to correct the SQL server. After reboot, all seems fine. ReportServer services even start up with no error.

However, if I try to run an SSRS report, or even connect to http://localhost/reports, I get an error:

"The report server cannot open a connection to the report server database."

This is because the report server configuration records the SQL server that the ReportServer database resides on (and even the name of the ReportServer database, in case you want to really confuse people by changing it). 

If this happens to you, this is how you fix it:

1. Launch Reporting Services Configuration Manager
2. Click database from the menu at the left.
3. On the right, you will see you old SQL Server name.  Click change database.
4. Select Choose an existing report server database, then next
5. Enter your new SQL Server name and test connection, then click next
6. For Report Server Database, select ReportServer, then Next x3
7. Then finish

If you have WennSoft reports setup, you can run this to correct those paths:
WS_SetReplacementReportsForSRS

Moving the tempdb database

You don't actually have to move the tempdb files.  You just need to change the path to a new location and then restart SQL Server.  The files will automatically be rebuilt in th new location and you can delete the old files.

1. Verify logical name and physical path of the tempdb files
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

2. Change the tempdb paths
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')
GO

3. Verify the paths
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

4. Check for any open connections before we bounce SQL
SELECT DB_NAME(dbid) as DBName,
       COUNT(dbid) as NumberOfConnections,
       loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

5. Bounce SQL

6. Use windows explorer to delete the old tempdb files

Moving a SQL User Database

To move a SQL user database, follow these steps:

1. Take db offline
ALTER DATABASE database_name SET OFFLINE;
GO

2. Use windows explorer to move the data and log files to the new location while the database is offline

3. Change the paths to the files
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
GO

4. Bring db back online
ALTER DATABASE database_name SET ONLINE;
GO

5. Verify that the database is online
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'');