Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

ALTER FUNCTION [dbo].[fnGetPipelineProductsCarried]
  @GIS_PipelineMasterId INT,
  @EnglishOrFrench NVARCHAR(20) = 'English'
  DECLARE @ReturnEnglish VARCHAR(1000) = ''
  DECLARE @ReturnFrench VARCHAR(1000) = ''
  DECLARE @ProductCarriedName NVARCHAR(255) = ''
  DECLARE @ProductCarriedNameFrench  NVARCHAR(255) = ''
  DECLARE @TempTable TABLE (
          PipelineName NVARCHAR(255),
          ProductCarriedName NVARCHAR(255),
          ProductCarriedNameFrench NVARCHAR(255),
          SUBABC NVARCHAR(5),
          PercentH2S DECIMAL(18,3),
       GIS_ProductCarriedTypeId INT,
       GIS_PipelineMasterId INT,

  INSERT INTO @TempTable  
    FROM ((GIS_PipelineSegment ps
   INNER JOIN GIS_PipelineSegmentProductCarried pspc ON ps.GIS_PipelineSegmentId = pspc.GIS_PipelineSegmentId)
   INNER JOIN GIS_PipelineMaster pm ON ps.GIS_PipelineMasterId = pm.GIS_PipelineMasterId)
   INNER JOIN GIS_ProductCarriedType pct ON pspc.GIS_ProductCarriedTypeId = pct.GIS_ProductCarriedTypeId
   WHERE pm.GIS_PipelineMasterId = @GIS_PipelineMasterId
   ORDER BY pm.PipelineName,

  DECLARE @count INT
  DECLARE @count_max INT
  SET @count = 1
  SELECT @count_max = COUNT(DummyID) FROM @TempTable

  WHILE(@count <= @count_max)
   SELECT @ProductCarriedName = ProductCarriedName,
             @ProductCarriedNameFrench = ProductCarriedNameFrench
     FROM @TempTable
    WHERE DummyID = @count
   SET @ReturnEnglish = @ReturnEnglish + @ProductCarriedName + ', '
   SET @ReturnFrench =  @ReturnFrench  + @ProductCarriedNameFrench + ', '
   --Must Always be at the end of the Loop
   SET @count = @count + 1
  IF @EnglishOrFrench = 'English'
   SET @ReturnEnglish = CASE @ReturnEnglish
                        WHEN NULL THEN NULL
                        ELSE (CASE LEN(@ReturnEnglish)
                              WHEN 0 THEN @ReturnEnglish
                              ELSE LEFT(@ReturnEnglish, LEN(@ReturnEnglish) - 1)
                              END )
   RETURN @ReturnEnglish
   SET @ReturnFrench = CASE @ReturnFrench
                       WHEN NULL THEN NULL
                       ELSE (CASE LEN(@ReturnFrench)
                             WHEN 0 THEN @ReturnFrench
                             ELSE LEFT(@ReturnFrench, LEN(@ReturnFrench) - 1)
                             END )

   RETURN @ReturnFrench
  RETURN @ReturnEnglish  

The Actual Error for me was

Msg 7347, Level 16, State 1, Line 2

OLE DB provider 'SQLNCLI10' for linked server 'Common_Pts' returned data that does not match expected data length for column '[Common_Pts].[Pts].[dbo].[veRegulatoryInstrument].prefix'. The (maximum) expected data length is 4, while the returned data length is 5. 

After creating a linked server in ms sql express. and running the view I got the following error. to solve the problem run sp_refreshview command from the server that is hosting the view.

sp_refreshview [ @viewname = ] 'viewname' 

This is so simple compared with the older versions.

To remove an old connection that you don't want from the dropdown list you simply click the dropdown list, use your up down keys to highlight the one you want to remove and then hit the delete key! GONE! No need to go find a configuration fiile like you used to in the older versions. 

I had an issue recently where I was not able to remove an instance of SQl Server, or so I thought. I kept on trying to remove the instance via Microsoft SQL Server 2014 RATHER THAN Microsoft SQL Server 2014 (64-bit). Once I selected the 64 bit option in Add-Remove Features I was able to finally gett rid of the instance I didn't want! Pretty dumb of me to not see the 64 bit option right away but there you go, sometimes you can't see the forest for the trees. 

This error usually happens if you have two SQL Server instances on the same box and someone removed one at some point.


To work around this problem, open a command prompt, type the following command, and then press ENTER:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"

Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

The value of number depends on the version of SQL Server:nnn

 Collapse this tableExpand this table
Microsoft SQL Server 2012 110
Microsoft SQL Server 2008 R2 100
Microsoft SQL Server 2008 100
Microsoft SQL Server 2005 90

After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation.

Sometimes you need to refresh the local cache in SQL Server when writting code to remove that annoying red squiggly line.

Edit -> Intellisense - Refresh Local Cache


The SSRS Reports website security can sometimes be troublesome when first configured and trying to connect to it from a remote machine. The RSReportServer.config file has a AuthenticationType section that required attention if you are prompted for authentication when accessing the site remotely. By default you may get all 3 types, however if you are just using regular Windows authentication you should only have RSWindowsNTLM in the list. Removing the other 2 will allow you into the site, provided you have access to the database.


             <RSWindowsNegotiate />
             <RSWindowsKerberos />
             <RSWindowsNTLM />

Replace Diameter with your own search.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
WHERE c.name LIKE '%Diameter%'
ORDER BY schema_name, table_name;

List ALL fields in a Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
ORDER BY schema_name, table_name;

Hope this helps.

For SQL 2005, delete the file:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

For SQL 2008, the file location, format and name changed:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

How to clear the list:

  • Shut down all instances of SSMS
  • Delete/Rename the file
  • Open SSMS

If you are working with SQL Stored Procedures and Functions, it's simple to add the db_owner role to your own login. However, when you want to release your database to a production environment you really want to take away the db_owner role as it has the ability to add, edit and remove database objects in a production environment. The proper way of assigning permissions is to give the user\group in question db_reader and db_writer privilages ONLY and remove the db_owner role. You then need to GRANT the user\group EXECUTE permission on all the Stored Procedures.

To do this you may need to run the following script in order to generate the script so this may be done quickly rather than applying the permission one by one. Once run, simply copy the results to a new window and Execute all lines.

DECLARE @User varchar(50)
SELECT @User = '[domain\userid]'

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0

OR if you want to GRANT to PUBLIC

DECLARE @User varchar(50)

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0

This link had more details regarding the objects you might need to query.

The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca


<<  November 2015  >>

View posts in large calendar

Sign in