Tag: SQL Server

IIS 7+ Register and Execute ISAPI 32bit/64bit DLL
I resently had to move an old project that I had from an Windows 2003 Server to a brand new Windows 2012 Server R2. The concern that I wad was with some ISAPI dlls that I was using to sync data from an SQL Server to some Windows Mobile devices using SQL Server CE. I was using merge replication to sync the devices and was doing it through the web (using sqlcesa30.dll).

First I tried to simply copy the DLLs but that didn’t work. Then I decided to try to run the setup but that didn’t work either. That is when I started to play around with IIS to find out what is going on since I was getting that frustrating Internal Server Error (500) which gives you no clue as of what is going on!

Here are the steps you need to take in order to register and execute a DLL on IIS 7+.
1. Create a folder to put the the executable (this is not required but changes might affect other services running).
2. Make that folder an application and create a new application pool for it.
3. Open Internet Information Services (IIS) Manager and click on the server on the tree. Then click ISAPI and CGI Restrictions. There you need to add the executable you need to run and click Allowed.
4. Then find and click the application folder on the tree and then go to Handler Mappings and then Edit Feature Permissions (which is on the right pane). There enable the Execute setting.
5. Finally go to the application pool, select the application pool you crated and in advanced settings set Enable 32-Bit Applications to true. (this is only required if you are trying to run an x86 DLL but it does not do any harm to enable it either way)
Hope this helps you register and run any DLL so that is at least executed and you don’t get that annoying Internal Server Error 500 that you have no clue as of what is going on.
July 8, 2015

Umbraco Fix – Empty recycle bin using SQL if backend fails

-- Uncomment below to verify the number of nodes returned is the same as the number of nodes that is in the Recycle Bin
-- SELECT * FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

--Setup the temporary table
CREATE TABLE #temp ([id] int);
INSERT #temp select id from umbracoNode where path like '%-20%' and id!=-20

-- Delete all 'related' nodes and table contents...
DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM #temp)
DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoRelation  where parentId in (select id from #temp) or childId in (select id from #temp)

-- remove fk constraint so that umbracoNode can delete
ALTER TABLE umbracoNode DROP CONSTRAINT FK_umbracoNode_umbracoNode
-- delete the XML nodes...
DELETE FROM umbracoNode WHERE id in (SELECT id FROM #temp)
-- re add fk constraint to umbracoNode
ALTER TABLE umbracoNode WITH NOCHECK ADD CONSTRAINT FK_umbracoNode_umbracoNode
FOREIGN KEY (parentId)
REFERENCES umbracoNode (id)

--Cleanup temp
DROP TABLE #temp;

June 6, 2012

Check replication synchronization status using Transactional SQL (TSQL)

Below you will find a select that will help you check the state of all your publication on SQL server in order to know if all the subscribers are in synch or the syncronization agent has stopped running.

SELECT
(CASE
    WHEN mdh.runstatus =  '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
    ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db='ALL' then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
(CASE
    WHEN mda.subscription_type =  '0' THEN 'Push'
    WHEN mda.subscription_type =  '1' THEN 'Pull'
    WHEN mda.subscription_type =  '2' THEN 'Anonymous'
    ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],

mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],
mda.name [Pub - DB - Publication - SUB - AgentID]
FROM distribution.dbo.MSdistribution_agents mda
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN
    (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
    FROM distribution.dbo.MSrepl_commands t (NOLOCK)
    JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
    JOIN
        (SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
        FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
        JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
        FROM distribution.dbo.MSdistribution_history (NOLOCK)
        GROUP BY agent_id) AS h
        ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
        GROUP BY hist.agent_id, h.maxseq
        ) AS MaxAgentValue
    ON MaxAgentValue.agent_id = s.agent_id
    GROUP BY s.agent_id, MaxAgentValue.[time]
    ) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
--and mdh.runstatus='6' --Fail
--and mdh.runstatus<>'2' --Succeed
order by mdh.[time]

December 5, 2011

Change the SQL Server Name after Changing the Computer Name
Recently I changed the name of my SQL Server computer from the generic one given by the Windows installation to ‘SERVER’. In the beggining there was no apparent problem in the connections since I used IP addresses. After a while I wanted to use Replication and tried to create an SQL Server Publication only to find that the name of the SQL SERVER has not changed and I could not create a publication unless I connected with the correct computer name. It turns out that changing the computer name does not update the sql server name and you need to change it manually in order to be able to use replication or any other Sql Server feature that requires you to connect using the computer name!

Below is a simple code that you can use to change the name of the SQL Server.
```
sp_dropserver @@SERVERNAME
GO
sp_addserver 'newServerName', local
GO
```
You need to restart the SQL Server in order for the changes to take effect.
December 2, 2011
Umbraco Fix – Content tree not loaded on very large sites
Recently one of my sites that was created using umbraco has started to behave somewhat strange. There was nothing that seem to be wrong and all the errors produced were:
No Document exists with Version ‘00000000-0000-0000-0000-000000000000’

As it turned out the problem was due to extreme delay in the SQL Server. What made me look into that was that the tree in the content area of the backend was not loading for the folder with all the articles in it.

Also it seemed that the problem might have originated because I also use DateFolders extention. Adding the index below dramatically increased the speed of the sql that runs to generate the content trees (from 32sec to less that 2sec for more than 7000 records) and it seems to resolved all my other problems as well.
```
/****** Object:  Index [IDX_cmsDocument_nodeId_versionId]    Script Date: 09/20/2011 13:13:20 ******/
CREATE NONCLUSTERED INDEX [IDX_cmsDocument_nodeId_versionId] ON [cmsDocument]
(
	[nodeId] ASC,
	[versionId] ASC
)
INCLUDE ( [published]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
```
S3GCC5KF6B2M
September 20, 2011
Alter Schema Owner at Tables, Views and Stored Procedures
Here is a quick and easy way to alter the schema of any of the objects in your sql server that has been created with any other user than the dbo. This has been giving me troubles when using TSQL and has been difficult to change it using the sql managment studio.
```
SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');
```
August 8, 2011
Convert a comma separated nvarchar to a list that can be used in any T-SQL
Many times in many projects I had to filter some records in a list by selecting not one but multiple categories. This has been a nightmare to be done in a stored procedure since you had to have a limited numbers of parameters and it would be inefficient to have @category1, @category2, etc. What you wanted to do is have one value that you would name @categories and then pass there any number of values separated by a character.

Now this can be achieved by using an intermediate stored procedute that converts a string delimited by a char to a table list of values. This stored procedure is the following:
```
CREATE FUNCTION dbo.spListToTable
(
	@List VARCHAR(MAX),
	@Delim CHAR
) RETURNS @ParsedList TABLE ( item VARCHAR(MAX) )
AS
BEGIN
	DECLARE @item VARCHAR(MAX), @Pos INT

	SET @List = LTRIM(RTRIM(@List))+ @Delim
	SET @Pos = CHARINDEX(@Delim, @List, 1)

	WHILE @Pos > 0
	BEGIN
		SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
		IF @item <> ''
		BEGIN
			INSERT INTO @ParsedList (item)
			VALUES (CAST(@item AS VARCHAR(MAX)))
		END

		SET @List = RIGHT(@List, LEN(@List) - @Pos)
		SET @Pos = CHARINDEX(@Delim, @List, 1)
	END
	RETURN
END
GO
```
Then in an example as the one described above you would use it like this :
```
SELECT *
FROM [tblList]
WHERE [categoryID] IN (
	SELECT [item] FROM dbo.spListToTable('1,2,3',',')
)
```
That would return you all the items that are in categories 1,2,3 !
December 8, 2010

LINQ to SQL Get DateTime from Sql Server (getDate)

Ever wanted to get the current time and date from the SQL Server to avoid different times between yous server and the clients (for example having a centralized application running on many countries) ?

Using LINQ to SQL there is no way to get the time from the database build in the class. What you need to do is write your own code as the example below:

    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Data;
    using System.Collections.Generic;
    using System.Reflection;
    using System.Linq;
    using System.Linq.Expressions;
    using System.ComponentModel;
    using System;

    partial class dbGTOnlineDataContext
    {
        [Function(Name = "GetDate", IsComposable = true)]
        public DateTime GetSystemDate()
        {
            MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
            return (DateTime)this.ExecuteMethodCall(this, mi, new object[] { }).ReturnValue;
        }
    }

Then anywhere in your code you can simple use the code below to get the time from the SQL Server database:

myDatabaseDataContext db = new myDatabaseDataContext();
DateTime dtNow = db.GetSystemDate();

July 7, 2010

How to get day of the week from a datetime in SQL Server
The day of the week can be retrieved in SQL Server by using the DatePart function. The value returned by function is between 1 (Sunday) and 7 (Saturday). To convert this to a string representing the day of the week, use a CASE statement.

Method 1:
Create function running following script:
```
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO
```
Call this function like this:
SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek
ResultSet:
DayOfWeek
———-
Monday

Method 2:

SELECT DATENAME(dw, GETDATE())
December 3, 2009

Clear all your SQL Server database with just 6 lines of T-SQL

This is a SQL Script that Cleans your Database Records & resets Identity Columns, and it is all in 6 lines!

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

December 2, 2009