I have a real-time reporting server with a database which is basically a copy of my production database, but it's populated using transactional replication and replication only copies the primary keys over, i.e. none of the secondary indexes are included. So what I do is every time I need to recreate replication--which is every deployment with schema changes--I script out DROPs and CREATEs for all the reporting indexes I've created and then run the script after replication has been recreated. The script I use is a modified conglomeration of scripts I've found on the Internet, but you may find it useful. Note that it excludes primary keys and some other things.
PRINT '------------------------- DROP INDEXES ----------------------------------------------------'
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR
SELECT indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
AND indexes.index_id < 255
AND objects.is_ms_shipped = 0
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY indexes.name
--SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
SET @sql = N'IF EXISTS (SELECT 1 FROM sys.indexes i WHERE i.name = ''' + @indexname + ''') DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
PRINT @sql
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes
PRINT CHAR(10) + CHAR(13) + '------------------------- CREATE INDEXES ----------------------------------------------------'
declare
@object_id int,
@index_id tinyint,
@schema_name sysname,
@table_name sysname,
@index_name sysname,
@type tinyint,
@uniqueness bit,
@indexed_column sysname,
@included_column sysname,
@indexed_columns varchar(max),
@included_columns varchar(max),
@has_included_cols bit,
@is_descending_key bit,
@stmt varchar(max),
@crlf char(2)
set @crlf = char(13) + char(10)
declare indexes cursor
for
select
schema_name = s.name,
table_name = t.name,
index_id = i.index_id,
index_name = i.name,
type = i.type,
uniqueness = i.is_unique
from
sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.indexes i on t.object_id = i.object_id
where
i.type > 0 -- none -heap
AND I.index_id > 0 -- ignore PKs
AND I.index_id < 255
AND i.type = 2
AND INDEXPROPERTY (I.object_id,I.NAME,'ISCLUSTERED') =0
AND left(I.object_id,3) not in ('sys', 'dt_')
AND left(I.name,3) not in ('PK_', 'UQ_','nci','ucm','ci_','uk_')
order
by i.name
open indexes
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
while @@fetch_status<>(-1)
begin
select @object_id = object_id(@schema_name + '.' + @table_name)
set @indexed_columns = '('
declare indexed_columns cursor
for
select
c.name,
ic.is_descending_key
from
sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id
and ic.object_id = c.object_id
where
ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 0
order by
ic.index_column_id
open indexed_columns
fetch indexed_columns
into @indexed_column, @is_descending_key
while @@fetch_status<>(-1)
begin
set @indexed_columns = @indexed_columns + @indexed_column +
case @is_descending_key when 1 then ' DESC ' else '' end + ', '
fetch indexed_columns
into @indexed_column, @is_descending_key
end
close indexed_columns
deallocate indexed_columns
set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')'
if exists
(select object_id
from sys.index_columns
where object_id = @object_id
and index_id = @index_id
and is_included_column = 1 )
begin
set @included_columns = 'INCLUDE ('
declare included_columns cursor
for
select
c.name,
ic.is_descending_key
from
sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id
and ic.object_id = c.object_id
where
ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 1
order by
ic.index_column_id
open included_columns
fetch included_columns
into @included_column, @is_descending_key
while @@fetch_status<>(-1)
begin
set @included_columns = @included_columns + @included_column +
case @is_descending_key when 1 then ' DESC ' else '' end + ', '
fetch included_columns
into @included_column, @is_descending_key
end
close included_columns
deallocate included_columns
set @included_columns = left(@included_columns, len(@included_columns)-1) + ') '
end
set @stmt =
'CREATE ' + case @uniqueness when 1 then 'UNIQUE ' else ' ' end + case @type when 1 then 'CLUSTERED ' else '' end +
'INDEX ' + @index_name + ' ON ' + @schema_name + '.' + @table_name + @indexed_columns + ' ' + isnull(@included_columns,'') + @crlf
set @included_columns = ''
print @stmt
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
end
close indexes
deallocate indexes
If you're like me you've been using SSRS 2008 for a long time with TFS and have constantly had to delete the bin folder in order for builds to work. This has irritated me to no end. Well today is my lucky day! There is a hotfix that actually fixes the bug! Go here: http://support.microsoft.com/kb/2438347
I recently discovered that creating indexes in one of my production databases was causing deadlocks. My problem was that I wasn't using ONLINE=ON when I was creating the index. Check out the BOL for more information.
If you're like me you had at one time connected one of your Reporting Services instances to a report server database that was already in use by another instance. This allows the instance to show up in the Scale-out Deployment section of the Reporting Services Configuration Manager. My problem was that the server that got joined to the original server was no longer available as it had been repurposed, and when I clicked Remove Server to remove it from my scale-out it would fail because it couldn't contact the server. After searching for a solution for quite some time I decided to look around in the report server database tables, and voila! All I had to do was remove the old server from the Keys table. I can't guarantee there won't be any side effects to this method, but it worked like a charm for me.
For those of you who, like me, transitioned from Subversion and TortoiseSVN to TFS, you might be confused as to how to view the history of a folder recursively. My problem was that I was wanting to see the recursive history in the Solution Explorer rather than the Source Control Explorer.
If there's already a way to get a List<int> of consecutive integers without a loop in C#, I don't know what it is, so I created a method for it.
public static List<int> GetIntegerListFromRangeUsingLoop(int start, int end) {
if (end < start) {
throw new ArgumentException("Faulty parameter(s) passed: lower bound cannot be less than upper bound.");
}
List<int> returnList = new List<int>(end - start + 1);
for(int i = start; i <= end; i++) {
returnList.Add(i);
}
return returnList;
}
UPDATE:
I was pointed to Enumerable.Range(), so I updated my code.
public static List<int> GetIntegerListFromRangeUsingEnumerableRange(int start, int end) {
IEnumerable<int> list = Enumerable.Range(start, end - start + 1);
return list.ToList();
}
Then I used the StopWatch class in my unit tests to compare the two methods.
Results:
GetIntegerListFromRangeUsingEnumerableRange averaged 6.5 milliseconds.
GetIntegerListFromRangeUsingLoop averaged 0.43 milliseconds.
Wow. Apparently Enumerable.Range() is a lot slower than using a loop.
I really like Windows 7 so far, amazingly, but I was about ready to hate it because my computer kept freezing when I would try to copy large files to the network. After about a week I realized that even though Windows Update is telling me it has no updates for me, if I force it to check for new updates it will find them (until I install the ones it finds). After installing the latest driver for my Atheros NIC, the problem seems to have disappeared (I could recreate it until now, so I'm pretty sure that fixed it).
How is it that you still can't save workspaces in SQL Server Management Studio as of version 2008? It seems like such a simple thing to implement. Quite often I create a bunch of different queries during a short period that are specific to a task I'm working on and that won't be needed once the task is completed, and rather than having to save each one it would be nice if I could just save a workspace file that knows which queries were open.
For anybody that bought the
SQL Server 2008 Self-Paced Training book for 70-433, if you are running a full version of SQL Server 2008 rather than the express edition, you will have problems attaching the AdventureWorks2008 database (file activation error). The trick is to use a SQL command, like so:
USE [master]
GO
CREATE DATABASE [AdventureWorks2008] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks2008_Log.ldf' ),
FILEGROUP Documents CONTAINS FILESTREAM (NAME = Documents, FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Documents')
FOR ATTACH
GO
I spent the past day trying to figure out why I couldn't log in as myself, Sharepoint would always just say I was logged in as System Account. After a ton of research and changing things I ran into this:
To fix this, you must run the following commands: stsadm -o updatefarmcredentials -identitytype NetworkService
followed by: iisreset
http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/8d8d9b30-0995-47ba-96e8-7b52872664f3
That solved my problem. I'm using NTLM authentication and I set up a new Active Directory user called sharepointuser to be the identity for the app pool (changing the app pool from myself to that user probably helped, but that isn't what fixed it ultimately). I'm pretty sure if I had set up sharepointuser before I installed MOSS 2007 on the Windows 2003 server and specified that user during the installation, I wouldn't have had the auth problems to begin with.
Here's a Powershell script I whipped up to copy all bak files from C:\TEMP to Y:\DEST. It checks to make sure the file is present at the destination before it deletes it from the source. I placed it in a ps1 (the extension for Powershell files) file and scheduled a task to execute a batch file that executes the ps1 file. The reason I had to check for null is that Powershell is kinda dumb and if the source folder is empty it picks it up as a child and performs the Copy-item.
$oldPath = "C:\TEMP"
$newPath = "Y:\DEST"
$GciFiles = get-childitem $oldPath
foreach ($file in $GciFiles){
Write-host $file.Name
if(!($file.Name -eq $null) -and $file.Name.EndsWith(".bak")){
Write-host "copying item "+($newPath+'\'+$file)
Copy-item $oldPath\$file $newPath
if (Test-path ($newPath+'\'+$file)) {
Write-host "removing item "+($oldPath+'\'+$file)
Remove-item ($oldPath+'\'+$file) -recurse
}
}
}
I spent hours trying to figure out why my ASP.Net page, which uses a master page with a RadGrid surrounded by a RadAjaxPanel with an AjaxLoadingPanel, would not show the loading message. I would click on the link to the page in the site menu, then IE would sit and spin for a while until it loaded the entire page, rather than loading the page right away but showing the loading spinner until the data was returned. What I had to do was add this javascript (w/jQuery) to the user control that the grid was in:
<script type="text/javascript">
$(document).ready(function() {
setTimeout(loadGrid, 500);
});
function loadGrid() {
window['<%=RadAjaxPanel1.ClientID %>'].AjaxRequest('');
}
</script>
I'm not sure why I had to make it sleep for half a second, but it wouldn't work until I added that.
I set up a server-side event (OnAjaxRequest="RadAjaxPanel1_AjaxRequest") that fires when AjaxRequest is called in the javascript:
protected void RadAjaxPanel1_AjaxRequest(object sender, AjaxRequestEventArgs e) {
Presenter.InitializeView();
BindGrid();
}
Even though the email server I'm running at home only sends and receives around 10 emails per month and has never been open for relay, it seems as though Comcast has started blocking port 25. The technician I've chatted with says otherwise, but either he was wrong or not telling the truth. I'm just putting that out there in case others have the same problem.
I just discovered something rather surprising. If you return a tinyint from a stored procedure as part of a dataset, and bind that dataset to a dataview, and then do something like this:
<asp:Label runat="server" id="statusLabel" Text='<%# GetStatusText( (int)DataBinder.Eval(Container, "DataItem.status")) %>'>
</asp:Label>
You will get an error about an invalid cast. If the stored procedure returns an int instead of tinyint, it works just fine. How can ASP.Net 3.5 not be able to cast from a tinyint (or whatever its C# equivalent is) to an int??