logo
down
shadow

Filter msdb.dbo.sysjobsteps.command into seperate columns


Filter msdb.dbo.sysjobsteps.command into seperate columns

By : user3099510
Date : January 11 2021, 03:32 PM
will help you You can use transform your data to xml to perform a "split" on backslashes and then you can extract only the data you need with an XQuery using the value() xml method (more info here on MS Docs).
In the following code I created a mock table called @tmp with your data:
code :
declare @tmp table (package_path nvarchar(max))

insert into @tmp values
     ('/ISSERVER "\"\SSISDB\VWGroup_Packages\AudiME SSIS Continuous Load\DealerLoad_GX_FM_WW.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\VWGroup_Packages\VWG IN SSIS Continuous Load\ABC to DW - Unapproved.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\Mercedes_Packages\Mercedes Cars SSIS Continuous Load\Submissions Load.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\Mercedes_Packages\Mercedes Cars SSIS Continuous Load\Validations Load.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\AGCO_Packages\agco SSIS Continuous Load\Metis to ABC - agco UK.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\AGCO_Packages\agco SSIS Continuous Load\Metis to ABC - agco ie.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\VWGroup_Packages\VWG PL SSIS Continuous Load\ABC to DW - Approved.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\Yamaha Packages\Yamaha SSIS Packages and WareHouse_Loads\ABCtoDWLoadModifiedCnt.dtsx\""')
    ,('/ISSERVER "\"\SSISDB\JLR\JLR SSIS Continuous Load\Submissions Load.dtsx\""')

;with splitted_packages
as (
    select 
        cast('<x>' + REPLACE(package_path, '\', '</x><x>') + '</x>' as xml) as package_frament
    from @tmp
    )
select
     package_frament.value(N'/x[4]', 'nvarchar(max)') as Folder
    ,package_frament.value(N'/x[5]', 'nvarchar(max)') as Projects
    ,package_frament.value(N'/x[6]', 'nvarchar(max)') as Package
from splitted_packages


Share : facebook icon twitter icon
MSDB database is in recovery_pending state and I don`t have backup of msdb?

MSDB database is in recovery_pending state and I don`t have backup of msdb?


By : Sohan Singh Shekhawa
Date : March 29 2020, 07:55 AM
This might help you There's an article in BOL for this: Rebuilding System Databases.
how i can join msdb.dbo.sysoperators and msdb.dbo.sysalerts?

how i can join msdb.dbo.sysoperators and msdb.dbo.sysalerts?


By : aruna
Date : March 29 2020, 07:55 AM
I wish did fix the issue. i use sql server 2008 r2. , you most use of this query
code :
Select * 
From sysalerts As Ale
    Inner Join sysnotifications As Noti on ( Ale.id = Noti.alert_id )
    Inner Join sysoperators As Ope On ( Noti.operator_id = ope.id )
Can I restore an MSDB.bak as a non-system database without affecting the active MSDB?

Can I restore an MSDB.bak as a non-system database without affecting the active MSDB?


By : W. Tabar
Date : March 29 2020, 07:55 AM
it should still fix some issue So, to answer my own question, yes it is possible to restore a backup of MSDB as a non-system database without affecting the active MSDB. I used the T-SQL below, but the SSMS GUI can accomplish the same.
code :
restore database MsdbTest from disk = '<DriveLetter>:\<PathToBackup>\msdb.bak'
with
    move 'MSDBData' to '<DriveLetter>:\<Path>\MsdbTest.mdf',
    move 'MSDBLog' to '<DriveLetter>:<Path>\MsdbTest.ldf'
Is there a way to seperate 2 values in column into two seperate columns?

Is there a way to seperate 2 values in column into two seperate columns?


By : Chris Hamilton
Date : March 29 2020, 07:55 AM
seems to work fine The problem is to sort the movies by movie Id and to split the title and year into separate columns in MySQL. Sorting the movies is not an issue but I am trying to separate a column with two sets of data in it. One being the movie title and the other being the year that the movie was released. , We can try using SUBSTRING_INDEX and INSTR here:
code :
SELECT
    SUBSTRING(title, 1, INSTR(title, '(') - 2) AS name,
    REPLACE(REPLACE(SUBSTRING_INDEX(title, ' ', -1), '(', ''), ')', '') AS year
FROM yourTable;
Filter non zero value in a cell and copy values in seperate columns / sheet for a large data

Filter non zero value in a cell and copy values in seperate columns / sheet for a large data


By : user2788038
Date : March 29 2020, 07:55 AM
hop of those help? I am trying to copy only values which are not zero values in a separate sheet / column. This needs to be done for large data. , Try this. considering the columns have just numbers.
code :
Sub FilterZeros()
Dim i As Long, lr As Long, lc As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = ThisWorkbook.Worksheets("Sheet1")
Set sh2 = ThisWorkbook.Worksheets("Sheet2")

'Last column
lc = Cells(1, Columns.Count).End(xlToLeft).Column

'Loops between column in Sheet1 to filter valus <>0
For i = 1 To lc
   sh1.Activate
   Range("A1").AutoFilter Field:=i, Criteria1:="<>0"
   n = Cells(Rows.Count, i).End(xlUp).Row
   Range(Cells(2, i), Cells(n, i)).SpecialCells(xlCellTypeVisible).Copy

  'find the last column and adds the copied data in Sheet2
  sh2.Activate
  lc2 = Cells(2, Columns.Count).End(xlToLeft).Column + 1
  Cells(2, lc2).PasteSpecial Paste:=xlValues
  sh1.Activate
  Range("A1").AutoFilter
Next


End Sub
Sub FilterZeros()
Dim i As Long, lr As Long, lc As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = ThisWorkbook.Worksheets("Sheet1")
Set sh2 = ThisWorkbook.Worksheets("Sheet2")

'First Column to be filtered
   sh1.Activate
   i = "Enter your column no. 1 here"
   Range("A1").AutoFilter Field:=i, Criteria1:="<>0"
   n = Cells(Rows.Count, i).End(xlUp).Row
   Range(Cells(2, i), Cells(n, i)).SpecialCells(xlCellTypeVisible).Copy

  sh2.Activate
  Range("A2").PasteSpecial xlPasteValues

'Second Column to be filtered
  sh1.Activate
  Range("A1").AutoFilter
  i = "Enter your column no. 2 here"
  Range("A1").AutoFilter Field:=i, Criteria1:="<>0"
  n = Cells(Rows.Count, i).End(xlUp).Row
   Range(Cells(2, i), Cells(n, i)).SpecialCells(xlCellTypeVisible).Copy

  sh2.Activate
  Range("B2").PasteSpecial xlPasteValues

  sh1.Activate
  Range("A1").AutoFilter

  sh2.Activate
  Range("A2").Select

End Sub
Related Posts Related Posts :
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • shadow
    Privacy Policy - Terms - Contact Us © festivalmusicasacra.org