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… Read Article →
Posts Tagged: SQL Server
Updated thanks to jeffreypriebe — 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… Read Article →
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… Read Article →
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… Read Article →
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… Read Article →
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… Read Article →
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… Read Article →
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… Read Article →
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)… Read Article →
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… Read Article →