Pattern Search and Replace function–Simple SQL

I have recently got a request from my insurance client to replace the non acceptable character with space in the PolicyHolder name before sending to Employers' Liability Tracing Office (ELTO).  Also, it should allow a quick search incase of an investigation from ELTO.
I always like the wildcard search facility in the SQL server as they are quick and can avoid huge amount of coding.  E.g. let us say we want to ignore all the special character expect “@” character

   1: Create table #temp


   2: (policyholdername varchar(30))


   3:  


   4: insert into #temp values ('Nishar/123')


   5: insert into #temp values('Nishar@123')


   6:  


   7: select * from #temp where policyholdername not like '%[^@a-zA-Z1-9]%'


The above code will only pick the second row and delivers it out of the box.  But unfortunately SQL server doesn’t give a Pattern Replace function out of the box so the only option is to create an user defined function. 

Below function has been developed with below goals in mind

• Create a function which should be generic
• uses the natural SQL functions rather than costly cursors or loops.

   1: ALTER FUNCTION PatReplace ( @sInput VARCHAR( max),@NonAllowedPattern varchar(max ),@ReplaceWith Varchar (1))

   2: RETURNS VARCHAR (max)

   3: AS

   4: BEGIN

   5: ---- =============================================

   6: ---- Author:            <Mohamed Nishar>

   7: ---- Create date: <05/06/14>

   8: ---- Description: Replace all characters matching with NonAllowedPattern based on supplied replacewith character

   9: ---- =============================================

  10:        DECLARE @Clenoutput AS VARCHAR( max);

  11:  

  12:        WITH CTE_patreplace

  13:        AS (

  14:        -- Check for any non allowed character and repalce it with new cahracter specified

  15:              SELECT CASE

  16:                          WHEN substring (@sInput, N, 1 ) LIKE @NonAllowedpattern

  17:                                THEN @ReplaceWith

  18:                          ELSE substring (@sInput, N, 1 )

  19:                          END sPolicyHolderNameCleanChar

  20:              FROM

  21:              -- A dummy table which generates long list of key numbers to strip the supplied string into individual character set

  22:              (

  23:                    SELECT row_number () OVER (

  24:                                ORDER BY sc1.NAME

  25:                                ) AS n

  26:                    FROM master .dbo. syscolumns sc1

  27:                          ,master. dbo.syscolumns sc2

  28:                    ) AS tal

  29:              WHERE n <= LEN( @sInput)

  30:              )

  31:        SELECT @Clenoutput = (

  32: -- FOR XML is used here to combine multiple rows (list of character from previous query) into single value

  33:                    SELECT isnull (stuff((

  34:                                            (

  35:                                                  SELECT '' + sPolicyHolderNameCleanChar

  36:                                                  FROM CTE_patreplace

  37:                                                  FOR XML PATH( '')

  38:                                                        ,root( 'MyString')

  39:                                                        ,type

  40:                                                  ).value( '/MyString[1]', 'varchar(max)')

  41:                                            ), 1, 0, ''), '')

  42:                    )

  43:  

  44:        RETURN @Clenoutput

  45: END

Happy reading

SSRS Deployment–Complete Automation–2012 & 2008

 

One of the most demanding and long awaiting feature in SSRS is “Automation of Deployment”. So in this blog I am going to share simple steps to completely automate the deployment and will share the tested vb.net code, but before getting into the automated script let us explore the available options

1. BIDS Development environment(or SQL data tools in 2012) can deploy your packages to the desired server
   1. Pros
      1. Very easy to configure
   2. Cons
      1. Not practical for production rollout, as most companies follows segregation of duties. So developer won’t have production access and DBA shouldn’t open the package in development tools.
      2. Most often you want to deploy only one or two changes in the specific report rather than the whole package
      3. Always deploy the latest version of code. We can’t specify the version or Branching in TFS/subversion.
2. Report Manager
   1. pros
      1. Can be used by DBA with some knowledge in SSRS
      2. Can deploy specific objects
   2. Cons
      1. Completely manual and mistake can easily happen
      2. Some organisation don’t have dedicated DBAs and they don’t have much knowledge about the SSRS
      3. Deployment instruction will be very lengthy and will consume significant amount of developer’s time incase of multiple reports and shared dataset
3. RS.exe ( via web service) – 2005 name space
   1. Pros
      1. Deployment can be  automated (partially)
      2. Famous RSscripter (was written by Jasper Smith) is developed based on 2005 namespace and was very helpful to produce automated scripts
   2. Cons
      1. The scripts are very tedious to edit and develop.
      2. There is no build tool available from Microsoft
      3. 2005 namespace doesn’t support linking of items(e.g. report to dataset and so on)
4. RS.Exe (via web service) – 2010 Name space
   1. Pros
      1. Deployment can be fully automated include linking of items
   2. Cons
      1. No tools available in the market to automate the deployment
      2. RSscripter doesn’t get it’s update for 2010 namespace

So the best option is RS.exe with 2010 namespace. I was searching for the automated script and found the article from  John Desch in the Microsoft blog Click here

it is one of the best automated script available in the market now as it uses the latest namespace. But it has it own limitations so in the blog i have expanded the functionality and given the revised code and steps to automate your deployment.

Enhancement

1. The code doesn't work with 2008Sp1 and above. It requires the buffer error fix.
2. The code tries to link all the reports in the deployed report folder from the servers to their Data source. This will create a problem, if you want to deploy only two reports in the existing 8 report pack. So created an additional method (UpdateDataSources_report) to link only the deployed report into their respective sources
3. The code Doesn't link the Shared data set to Report. so created an additional method (UpdateDataSet_report) which will automatically link your report to their respective datasets
4. The existing code doesn't overwrite the files if they exist. changed the flags (for dataset and report only) as the deployment usually requires overwriting the existing objects.
5. At last it would be easy if you have a single deployment script with command prompt variable so that you can pass to DBA. SO created a deploy.bat with variables

Automation Steps

1. You can download the code and batch file from my skydrive (or one drive )
1. CommonScript.rss
2. Deploy.bat
2. Save both file in same folder in your local drive
3. Create a folder named “Reports” under this new folder
4. Now copy all the reports, datasource and dataset into the “Reports” folder
5. Now Deploy.bat has the below variables amend them accordingly

varServerPath=http://local/reportserver    ---- Desired Server URL
set varReportFolder=Test/Testdeploy          ---- Server folder path --from the main
Set varDatasetFolder=Datasets                   ---- Dataset folder path
set varDataSourceFolder=Data Sources        ---- Datasource path
Set varDataSourcePath=Data Sources          ---- Datasource path again (Yes this is Redundant)
set varReportName=                                  -----If you want to restrict the script to one report then mention it’s name without

                                                                       extension else leave it blank
set varReportFilePath=%\Reports                ---- Local folder path where you stored your reports and dependant objects.

 

Now, you can zip the folder and send it to DBA (Note: please test the script in your development server first)

 

FAQ

• Can I refer two different datasource folder for different dataset?
• NO, you need to declare a collection object and modify the code. This is very rare occasion.
• Report is not able to link my dataset.
• Check the name in your report. Shared dataset should be creaed with same name
• Can I deploy report in multiple folder path
• Yes, you need divide them into multiple deployment package

 

Revised Code (save the script as Commonscript.rss)

 

   1: 'Begin Script

   2:  

   3: Dim definition As [Byte]() = Nothing

   4:  

   5: Dim bytedefinition as [Byte]() = nothing

   6:  

   7: Dim warnings As Warning() = Nothing

   8:  

   9:  

  10:  

  11: 'Main Entry point of utility

  12:  

  13: Public Sub Main()

  14:  

  15: Console.WriteLine()

  16:  

  17: Console.WriteLine("Initiating Deployment")

  18:  

  19: rs.Credentials = System.Net.CredentialCache.DefaultCredentials

  20:  

  21: Try

  22:  

  23: 'Create the shared data source

  24:  

  25: CreateFolders(DataSourceFolder,"/","Data Sources","Visible")

  26:  

  27: 'Create the folder that will contain the shared data sets

  28:  

  29: CreateFolders(DataSetFolder, "/", "Data Set Folder", "Visible")

  30:  

  31: 'Create the folder that will contain the deployed reports

  32:  

  33: CreateFolders(ReportFolder, "/", "Report Folder","Visible")

  34:  

  35: Catch goof As Exception

  36:  

  37: Console.WriteLine(goof.Message)

  38:  

  39: End Try

  40:  

  41: ReadFiles(filepath, "*.rds")

  42:  

  43: ReadFiles(filepath, "*.rsd")

  44:  

  45: ReadFiles(filepath, "*.rdl")

  46:  

  47: 'Publish the report

  48:  

  49: 'PublishReport(ReportName)

  50:  

  51: 'UpdateDataSources(ReportFolder, DataSourcePath)

  52:  

  53: End Sub

  54:  

  55:  

  56:  

  57: 'Utility for creation of folders

  58:  

  59: Public Sub CreateFolders(ByVal folderName as string, ByVal parentPath as string, ByVal description as String, ByVal visible as string)

  60:  

  61: Console.WriteLine()

  62:  

  63: Console.WriteLine("Checking for Target Folders")

  64:  

  65: 'CatalogItem properties

  66:  

  67: Dim descriptionProp as new [Property]

  68:  

  69: descriptionProp.Name = "Description"

  70:  

  71: descriptionProp.Value= description

  72:  

  73: Dim visibleProp as new [Property]

  74:  

  75: visibleProp.Name = "Visible"

  76:  

  77: visibleProp.value= visible

  78:  

  79: Dim props(1) as [Property]

  80:  

  81: props(0) = descriptionProp

  82:  

  83: props(1) = visibleProp

  84:  

  85: Try

  86:  

  87: rs.CreateFolder(folderName,parentPath,props)

  88:  

  89: Console.WriteLine("Folder {0} successfully created", foldername)

  90:  

  91: Catch goof as SoapException

  92:  

  93: If goof.Message.Indexof("AlreadyExists")>0 Then

  94:  

  95: Console.WriteLine("Folder {0} already exists",foldername)

  96:  

  97: End If

  98:  

  99: End Try

 100:  

 101:  End Sub

 102:  

 103:  

 104:  

 105: 'Utility for reading files from the Report Sevices Project

 106:  

 107: Public sub ReadFiles(filepath as string, fileextension as string)

 108:  

 109: Console.WriteLine()

 110:  

 111: Console.WriteLine("Reading Files from Report Services Project")

 112:  

 113: Dim rptdirinfo As System.IO.DirectoryInfo

 114:  

 115: rptdirinfo = New System.IO.DirectoryInfo(filepath)

 116:  

 117: Dim filedoc As FileInfo()

 118:  

 119: filedoc = rptdirinfo.GetFiles(fileextension)

 120:  

 121: Try

 122:  

 123:     For rptcount As Integer = 0 To filedoc.Length-1

 124:  

 125:         If Not filedoc(rptcount).Name.ToString.Trim.ToUpper.Contains("BACKUP") Then

 126:  

 127:             SELECT Case fileextension

 128:  

 129:                 Case "*.rds"

 130:  

 131:                     CreateDataSource(filedoc(rptcount).tostring.trim)

 132:  

 133:                 Case "*.rsd"

 134:  

 135:                     CreateDataSet(filedoc(rptcount).tostring.trim)

 136:  

 137:                 Case "*.rdl"

 138:  

 139:                     PublishReport(filedoc(rptcount).tostring.trim)

 140:  

 141:             End Select

 142:  

 143:         End If

 144:  

 145:     Next

 146:  

 147: Catch goof as Exception

 148:  

 149:     Console.WriteLine("In ReadFiles " + goof.message)

 150:  

 151: End Try

 152:  

 153: End Sub

 154:  

 155:  

 156:  

 157: 'Utility for Creating Shared Data Sets contained in the project

 158:  

 159: Public Sub CreateDataSet(ByVal filename as string)

 160:  

 161: Dim valstart as integer

 162:  

 163: Dim valend as integer

 164:  

 165: Dim DSDefinitionStr as string

 166:  

 167: Dim DataSourceName as string

 168:  

 169: Dim QueryString as string

 170:  

 171: Try

 172:  

 173:     Dim stream As FileStream = File.OpenRead(filePath + "\" + filename )

 174:  

 175:     definition = New [Byte](stream.Length-1) {}

 176:  

 177:     stream.Read(definition, 0, CInt(stream.Length))

 178:  

 179:     stream.Close()

 180:  

 181:     For i As Integer = 0 To definition.Length - 1

 182:  

 183:         DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(definition(i).ToString)))

 184:  

 185:     Next

 186:  

 187:     valstart=DSDefinitionStr.ToString.Indexof("<DataSourceReference>")

 188:  

 189:     If valstart > 0 Then

 190:  

 191:         valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceReference>") + 21

 192:  

 193:         valend = DSDefinitionStr.ToString.IndexOf("</DataSourceReference>")

 194:  

 195:         DataSourceName=DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

 196:  

 197:         Console.WriteLine(DataSourceName)

 198:  

 199:     End If

 200:  

 201:     Catch e As IOException

 202:  

 203:     Console.WriteLine(e.Message)

 204:  

 205: End Try

 206:  

 207: filename=filename.tostring.replace(".rsd","")

 208:  

 209: Console.WriteLine("Attempting to Deploy DataSet {0}", filename)

 210:  

 211: Try

 212:  

 213: Dim item as CatalogItem

 214:  

 215: item=rs.CreateCatalogItem("DataSet",filename, "/" + DataSetFolder, True, definition, nothing, warnings)

 216:  

 217: If Not (warnings Is Nothing) Then

 218:  

 219: Dim warning As Warning

 220:  

 221: For Each warning In warnings

 222:  

 223: if warning.message.tostring.tolower.contains("refers to the shared data source") then

 224:  

 225: Console.WriteLine("Connecting DataSet {0} to Data Source {1}",filename, DataSourceName)

 226:  

 227: Dim referenceData() as ItemReferenceData = rs.GetItemReferences("/" + DataSetFolder + "/" + filename,"DataSet")

 228:  

 229: Dim references(0) as ItemReference

 230:  

 231: Dim reference as New ItemReference()

 232:  

 233: Dim datasourceURL = DataSourcePath + "/" + DataSourceName

 234:  

 235: reference.name=referenceData(0).Name

 236:  

 237: Console.WriteLine("Reference name = " + reference.name)

 238:  

 239: reference.Reference=datasourceURL

 240:  

 241: references(0)=reference

 242:  

 243: rs.SetItemReferences("/" + DataSetFolder + "/" + filename, references)

 244:  

 245: else

 246:  

 247: Console.WriteLine(warning.Message)

 248:  

 249: end if

 250:  

 251: Next warning

 252:  

 253: Else

 254:  

 255: Console.WriteLine("DataSet: {0} published successfully with no warnings", filename)

 256:  

 257: End If

 258:  

 259: Catch goof as SoapException

 260:  

 261: If goof.Message.Indexof("AlreadyExists")>0 Then

 262:  

 263: Console.WriteLine("The DataSet {0} already exists",fileName.ToString)

 264:  

 265: Else

 266:  

 267: If goof.Message.IndexOf("published")=-1 Then

 268:  

 269: Console.Writeline(goof.Message)

 270:  

 271: End If

 272:  

 273: End If

 274:  

 275: End Try

 276:  

 277: 'UpdateDataSetSources(filename,DataSetFolder, DataSourceFolder,DataSourceName)

 278:  

 279: End Sub

 280:  

 281:  

 282:  

 283: 'Utility for creating Data Sources on the Server

 284:  

 285: Public Sub CreateDataSource(filename as string)

 286:  

 287: 'Define the data source definition.

 288:  

 289: Dim dsDefinition As New DataSourceDefinition()

 290:  

 291: Dim DataSourceName as string

 292:  

 293: Dim valstart As Integer

 294:  

 295: Dim valend As Integer

 296:  

 297: Dim ConnectionString As String

 298:  

 299: Dim Extension As String

 300:  

 301: Dim IntegratedSec As String

 302:  

 303: Dim DataSourceID As String

 304:  

 305: Dim PromptStr As String

 306:  

 307: PromptStr=""

 308:  

 309: Dim DSDefinitionStr As String

 310:  

 311: DSDefinitionStr = ""

 312:  

 313: DataSourceName=filename.tostring.trim.substring(0,filename.tostring.trim.length-4)

 314:  

 315: Console.WriteLine("Attempting to Deploy Data Source {0}", DataSourceName)

 316:  

 317: Try

 318:  

 319: Dim stream As FileStream = File.OpenRead(filepath + "\" + filename)

 320:  

 321: bytedefinition = New [Byte](stream.Length-1) {}

 322:  

 323: stream.Read(bytedefinition, 0, CInt(stream.Length))

 324:  

 325: stream.Close()

 326:  

 327: For i As Integer = 0 To bytedefinition.Length - 1

 328:  

 329: DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(bytedefinition(i).ToString)))

 330:  

 331: Next

 332:  

 333: Catch goof As IOException

 334:  

 335: Console.WriteLine(goof.Message)

 336:  

 337: End Try

 338:  

 339: If DSDefinitionStr.ToString.Contains("<ConnectString>") And DSDefinitionStr.ToString.Contains("</ConnectString>") Then

 340:  

 341: valstart = DSDefinitionStr.ToString.IndexOf("<ConnectString>") + 15

 342:  

 343: valend = DSDefinitionStr.ToString.IndexOf("</ConnectString>")

 344:  

 345: ConnectionString = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

 346:  

 347: End If

 348:  

 349: If DSDefinitionStr.ToString.Contains("<Extension>") And DSDefinitionStr.ToString.Contains("</Extension>") Then

 350:  

 351: valstart = DSDefinitionStr.ToString.IndexOf("<Extension>") + 11

 352:  

 353: valend = DSDefinitionStr.ToString.IndexOf("</Extension>")

 354:  

 355: Extension = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

 356:  

 357: End If

 358:  

 359: If DSDefinitionStr.ToString.Contains("<IntegratedSecurity>") And DSDefinitionStr.ToString.Contains("</IntegratedSecurity>") Then

 360:  

 361: valstart = DSDefinitionStr.ToString.IndexOf("<IntegratedSecurity>") + 20

 362:  

 363: valend = DSDefinitionStr.ToString.IndexOf("</IntegratedSecurity>")

 364:  

 365: IntegratedSec = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

 366:  

 367: End If

 368:  

 369: If DSDefinitionStr.ToString.Contains("<DataSourceID>") And DSDefinitionStr.ToString.Contains("</DataSourceID>") Then

 370:  

 371: valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceID>") + 14

 372:  

 373: valend = DSDefinitionStr.ToString.IndexOf("</DataSourceID>")

 374:  

 375: DataSourceID = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

 376:  

 377: End If

 378:  

 379: If DSDefinitionStr.ToString.Contains("<Prompt>") And DSDefinitionStr.ToString.Contains("</Prompt>") Then

 380:  

 381: valstart = DSDefinitionStr.ToString.IndexOf("<Prompt>") + 8

 382:  

 383: valend = DSDefinitionStr.ToString.IndexOf("</Prompt>")

 384:  

 385: PromptStr = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

 386:  

 387: End If

 388:  

 389: dsdefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

 390:  

 391: dsdefinition.ConnectString = ConnectionString

 392:  

 393: dsdefinition.Enabled = True

 394:  

 395: dsdefinition.EnabledSpecified = True

 396:  

 397: dsdefinition.Extension = extension

 398:  

 399: dsdefinition.ImpersonateUser = False

 400:  

 401: dsdefinition.ImpersonateUserSpecified = True

 402:  

 403: 'Use the default prompt string.

 404:  

 405: If PromptStr.ToString.Length=0 Then

 406:  

 407: dsdefinition.Prompt = Nothing

 408:  

 409: Else

 410:  

 411: dsdefinition.Prompt = PromptStr

 412:  

 413: End if

 414:  

 415: dsdefinition.WindowsCredentials = False

 416:  

 417: Try

 418:  

 419: rs.CreateDataSource(DataSourceName, "/" + DataSourceFolder, False, dsdefinition, Nothing)

 420:  

 421: Console.WriteLine("Data source {0} created successfully", DataSourceName.ToString)

 422:  

 423: Catch goof as SoapException

 424:  

 425: If goof.Message.Indexof("AlreadyExists")>0 Then

 426:  

 427: Console.WriteLine("The Data Source name {0} already exists",DataSourceName.ToString)

 428:  

 429: End If

 430:  

 431: End Try

 432:  

 433: End Sub

 434:  

 435:  

 436:  

 437: 'Utility to Publish the Reports

 438:  

 439: Public Sub PublishReport(ByVal reportName As String)

 440:  

 441: Try

 442:  

 443: Dim stream As FileStream = File.OpenRead(filePath + "\" + reportName )

 444:  

 445: definition = New [Byte](stream.Length-1) {}

 446:  

 447: stream.Read(definition, 0, CInt(stream.Length))

 448:  

 449: stream.Close()

 450:  

 451: Catch e As IOException

 452:  

 453: Console.WriteLine(e.Message)

 454:  

 455: End Try

 456:  

 457: reportname=reportname.tostring.replace(".rdl","")

 458:  

 459: Console.WriteLine("Attempting to Deploy Report Name {0}", reportname.tostring)

 460:  

 461: Dim item as CatalogItem

 462:  

 463:  

 464: Try

 465:  

 466:     item=rs.CreateCatalogItem("Report",reportname, "/" + ReportFolder, True, definition,nothing, warnings)

 467:  

 468:     'warnings = rs.CreateCatalogItem(reportName, "/" + ReportFolder, False, definition, Nothing)

 469:  

 470:     If Not (warnings Is Nothing) Then

 471:  

 472:         If item.Name <> "" then 

 473:  

 474:             Console.WriteLine("Report: {0} published successfully with warnings", reportName)

 475:             UpdateDataSources_report(reportName)

 476:             UpdateDataSet_report(reportName)

 477:         else

 478:     

 479:             Dim warning As Warning

 480:  

 481:             For Each warning In warnings

 482:  

 483:                 Console.WriteLine(warning.Message)

 484:  

 485:             Next warning

 486:             

 487:         end if

 488:  

 489:     Else

 490:  

 491:         Console.WriteLine("Report: {0} published successfully with no warnings", reportName)

 492:         UpdateDataSources_report(reportName)

 493:         UpdateDataSet_report(reportName)

 494:     End If

 495:  

 496: Catch goof as SoapException

 497:  

 498: If goof.Message.Indexof("AlreadyExists")>0 Then

 499:  

 500: Console.WriteLine("The Report Name {0} already exists",reportName.ToString)

 501:  

 502: Else

 503:  

 504:     If  goof.Message.IndexOf("published")=-1 Then

 505:  

 506:     Console.WriteLine(goof.Message)

 507:  

 508:     End If

 509:  

 510: End If

 511:  

 512: End Try

 513:  

 514: End Sub

 515:  

 516:  

 517:  

 518: 'Utility to Update The Data Sources on the Server

 519:  

 520: Public Sub UpdateDataSources(ReportFolder as string, DataSourcePath as string)

 521:  

 522: rs.Credentials = System.Net.CredentialCache.DefaultCredentials

 523:  

 524: Dim item as CatalogItem

 525:  

 526: Dim items as CatalogItem()

 527:  

 528: Try

 529:  

 530:  

 531:  

 532: items=rs.ListChildren("/" + ReportFolder, False)

 533:  

 534: For Each item in items

 535:  

 536: 'Console.WriteLine("          update date source called     --------"+ item.Path + " -----------")

 537:  

 538: If item.path.Indexof("rdl")>0 and ReportName = "" Then

 539:  

 540: 'Console.WriteLine("          update date source called     --------"+ item.path.Indexof("rdl").tostring() + " -----------")

 541:  

 542:     Dim dataSources() as DataSource = rs.GetItemDataSources(item.Path)

 543:  

 544:     For Each ds as DataSource in dataSources

 545:  

 546:         Dim sharedDs(0) as DataSource

 547:  

 548:         sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)

 549:  

 550:         rs.SetItemDataSources(item.Path, sharedDs)

 551:  

 552:         Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report")

 553:  

 554:         'end if

 555:     Next

 556:  

 557: End IF

 558:  

 559: Next

 560:  

 561: if ReportName = "" Then

 562:  

 563:     Console.WriteLine("Shared data source reference set for reports in the {0} folder.", ReportFolder)

 564:  

 565: End if

 566:  

 567:  

 568: If ReportName <> "" then

 569:  

 570: '    Console.WriteLine("               " + "/" + ReportFolder + "/" + ReportName + "  -------------      second  update called        ---------------------- ")

 571:  

 572:     Dim dataSources() as DataSource = rs.GetItemDataSources( "/" +  ReportFolder + "/" + ReportName)

 573:     

 574:     For Each ds as DataSource in dataSources

 575:  

 576:         Dim sharedDs(0) as DataSource

 577:  

 578:         sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)

 579:  

 580:         rs.SetItemDataSources("/" +  ReportFolder + "/" + ReportName, sharedDs)

 581:  

 582:         Console.WriteLine("Set " & ds.Name & " datasource for " & "/" +  ReportFolder + "/" + ReportName & " report")

 583:  

 584:         'end if

 585:     Next

 586:  

 587:     Console.WriteLine("All the shared data source reference set for report {0} ", "/" + ReportFolder + "/" + ReportName)

 588:     

 589: end if    

 590:  

 591: Catch goof As SoapException

 592:  

 593: Console.WriteLine(goof.Detail.InnerXml.ToString())

 594:  

 595: End Try

 596:  

 597: End Sub

 598:  

 599:  

 600:  

 601:  

 602: 'Utility to Update The Data Sources on the Server

 603:  

 604: Public Sub UpdateDataSources_report(ReportName as string)

 605:  

 606: rs.Credentials = System.Net.CredentialCache.DefaultCredentials

 607:  

 608: Dim item as CatalogItem

 609:  

 610: Dim items as CatalogItem()

 611:  

 612: Try

 613:  

 614: 'If ReportName <> "" then

 615:  

 616: '    Console.WriteLine("               " + "/" + ReportFolder + "/" + ReportName + "  -------------      second  update called        ---------------------- ")

 617:  

 618:     Dim dataSources() as DataSource = rs.GetItemDataSources( "/" +  ReportFolder + "/" + ReportName)

 619:     

 620:     For Each ds as DataSource in dataSources

 621:  

 622:         Dim sharedDs(0) as DataSource

 623:  

 624:         sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)

 625:  

 626:         rs.SetItemDataSources("/" +  ReportFolder + "/" + ReportName, sharedDs)

 627:  

 628:         Console.WriteLine("Set " & ds.Name & " datasource for " & "/" +  ReportFolder + "/" + ReportName & " report")

 629:  

 630:         'end if

 631:     Next

 632:  

 633:     Console.WriteLine("All the shared data source reference set for report {0} ", "/" + ReportFolder + "/" + ReportName)

 634:     

 635: 'end if    

 636:  

 637:  

 638: Catch goof As SoapException

 639:  

 640: Console.WriteLine(goof.Detail.InnerXml.ToString())

 641:  

 642: End Try

 643:  

 644: End Sub

 645:  

 646:  

 647:  

 648: 'Utility to link The Dataset with the Report

 649:  

 650: Public Sub UpdateDataSet_report(ReportName as string)

 651:  

 652: rs.Credentials = System.Net.CredentialCache.DefaultCredentials

 653:  

 654:  

 655: Try

 656:  

 657:     Dim dataSets As ItemReferenceData() = rs.GetItemReferences("/" +  ReportFolder + "/" + ReportName, "DataSet")

 658:     

 659:     If dataSets IsNot Nothing AndAlso dataSets.Length > 0 AndAlso Not String.IsNullOrEmpty(dataSets(0).Name) Then

 660:     

 661:         For i as integer = 0 to dataSets.Length -1

 662:     

 663:             Dim references(0) as ItemReference

 664:             Dim sharedDataSet = New ItemReference() 

 665:             sharedDataSet.Name = dataSets(i).Name

 666:             Console.WriteLine("Attempting to Link Dataset {0}", dataSets(i).Name)

 667:             sharedDataSet.Reference = "/" + DataSetFolder + "/" + dataSets(i).Name 

 668:             references(0)=sharedDataSet

 669:             rs.SetItemReferences("/" + ReportFolder + "/" + ReportName, references)

 670:             Console.WriteLine("Report " + ReportName + " Linked to data set " + "/" + DataSetFolder + "/" + Convert.ToString(sharedDataSet.Name))

 671:         Next

 672:     

 673:     End If

 674:  

 675: Catch goof As SoapException

 676:  

 677:     Console.WriteLine(goof.Detail.InnerXml.ToString())

 678:  

 679: End Try

 680:  

 681: End Sub

 682:  

 683:  

 684:  

 685:  

 686: 'Function to Reference Data Sources

 687:  

 688: Private Function GetDataSource(sharedDataSourcePath as string, dataSourceName as String) as DataSource

 689:  

 690:     Dim reference As New DataSourceReference()

 691:  

 692:     Dim ds As New DataSource

 693:  

 694:     reference.Reference = sharedDataSourcePath & "/" & dataSourceName

 695:  

 696:     ds.Item = CType(reference, DataSourceDefinitionOrReference)

 697:  

 698:     ds.Name = dataSourceName

 699:  

 700:     Console.WriteLine("Attempting to Link Data Source {0}", ds.Name)

 701:  

 702:     GetDataSource=ds

 703:  

 704: End Function

Happy reading