June 28, 2014

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 Smile

March 8, 2014

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 Smile)
    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
  6. 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 Smile