Styjun

When will the last unambiguous evidence of mankind disappear?

Will copper pour help on my single-layer PCB?

Locked-up DOS computer beeped on keypress. What mechanism caused that?

Why do jet engines sound louder on the ground than inside the aircraft?

Linux ext4 restore file and directory access rights after bad backup/restore

Why is Google approaching my VPS machine?

Could a US citizen born through "birth tourism" become President?

Why aren't there any women super Grandmasters (GMs)?

What does <recently read> etc. mean in TeX's error message

What image should I install on VirtualBox for practising dev ops

Manager is asking me to eat breakfast from now on

Legendre Polynomial Integral over half space

How to change word under cursor to upper case in command mode with shortcut?

Equality of complex numbers in general

🍩🔔🔥Scrambled emoji tale⚛️🎶🛒 #2️⃣

Discontinuous Tube visualization

How to belay quickly ascending top-rope climbers?

Does unblocking power bar outlets through short extension cords increase fire risk?

Is it possible to have a career in SciComp without contributing to arms research?

Could Europeans in Europe demand protection under UN Declaration on the Rights of Indigenous Peoples?

Why is regex [0-9]0,2 not greedy in sed?

How to tell readers that I know my story is factually incorrect?

Why isn't a binary file shown as 0s and 1s?

Is encryption still applied if you ignore the SSL certificate warning for self-signed certs?

How to create a stored procedure in SQL Server which creates dynamic partitions and process for the tabular model

How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?What is the best way to auto-generate INSERT statements for a SQL Server table?Function vs. Stored Procedure in SQL Serversql server invalid object name - but tables are listed in SSMS tables listHow do I escape a single quote in SQL Server?How do I UPDATE from a SELECT in SQL Server?Search text in stored procedure in SQL ServerHow to Delete using INNER JOIN with SQL Server?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

How to create a stored procedure in SQL Server which creates dynamic partitions and process?

I have below code which creates a partition every time, and does process at one time in liked server.

Using below code I would like to create dynamic multiple partitions and process by passing partition name and fiscal period id to the procedure dynamically.

Please suggest.

declare @xmla varchar(max) = '

 "createOrReplace": 
 "object": 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 ,
 "partition": 
 "name": "FORECAST_FLASH_368",
 "source": 
 "query": "select * from FORECAST_FLASH_ARCHIVE_V where fiscal_period_id=368",
 "dataSource": "itv.world"
 
 
 

';

EXEC (@xmla) AT SSAS;



declare @xmlap varchar(max) = '

 "refresh": 
 "type": "automatic",
 "objects": [
 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 
 ]
 

';
exec (@xmlap) at SSAS;

sql-server sql-server-data-tools ssas-tabular xmla

share|improve this question

edited Mar 26 at 11:10

marc_s

597k135135 gold badges11481148 silver badges12841284 bronze badges

asked Mar 26 at 10:14

saijsaij

744 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So basically you want to create a stored proc that takes a fiscal period, which is a number, for example, 368, and creates a partition and refreshes that partition. It would appear that all you need to do is concatenate this number into the correct part of the string. Do you know how to concatenate strings in T-SQL?
  
  – Nick.McDermaid
  Mar 26 at 11:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Nick.McDermaid I want to pass partition name and fiscal period dynamically to a stored procedure.
  
  – saij
  Mar 26 at 11:39
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If the fiscal period is 368 then the partition name is just FORECAST_FLASH_368 right? so you only need to pass in the fiscal period. Your first task is to work out how to create a stored prcoedure that takes a VARCHAR parameter
  
  – Nick.McDermaid
  Mar 26 at 11:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Nick.McDermaid I have created stored procedure,which takes VARCHAR parameter.I am unable to find that how to fix above code in procedure and execute in linked server.
  
  – saij
  Mar 26 at 11:59
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you edit your question and post the entire stored procedure. If your parameter name is @FiscalPeriod then all you have to do is concatenate that to your existing strings. If you are having issues please explain in detail.
  
  – Nick.McDermaid
  Mar 26 at 13:01
  

  
  
  
  

add a comment | 

0

How to create a stored procedure in SQL Server which creates dynamic partitions and process?

I have below code which creates a partition every time, and does process at one time in liked server.

Using below code I would like to create dynamic multiple partitions and process by passing partition name and fiscal period id to the procedure dynamically.

Please suggest.

declare @xmla varchar(max) = '

 "createOrReplace": 
 "object": 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 ,
 "partition": 
 "name": "FORECAST_FLASH_368",
 "source": 
 "query": "select * from FORECAST_FLASH_ARCHIVE_V where fiscal_period_id=368",
 "dataSource": "itv.world"
 
 
 

';

EXEC (@xmla) AT SSAS;



declare @xmlap varchar(max) = '

 "refresh": 
 "type": "automatic",
 "objects": [
 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 
 ]
 

';
exec (@xmlap) at SSAS;

sql-server sql-server-data-tools ssas-tabular xmla

share|improve this question

edited Mar 26 at 11:10

marc_s

597k135135 gold badges11481148 silver badges12841284 bronze badges

asked Mar 26 at 10:14

saijsaij

744 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So basically you want to create a stored proc that takes a fiscal period, which is a number, for example, 368, and creates a partition and refreshes that partition. It would appear that all you need to do is concatenate this number into the correct part of the string. Do you know how to concatenate strings in T-SQL?
  
  – Nick.McDermaid
  Mar 26 at 11:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Nick.McDermaid I want to pass partition name and fiscal period dynamically to a stored procedure.
  
  – saij
  Mar 26 at 11:39
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If the fiscal period is 368 then the partition name is just FORECAST_FLASH_368 right? so you only need to pass in the fiscal period. Your first task is to work out how to create a stored prcoedure that takes a VARCHAR parameter
  
  – Nick.McDermaid
  Mar 26 at 11:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Nick.McDermaid I have created stored procedure,which takes VARCHAR parameter.I am unable to find that how to fix above code in procedure and execute in linked server.
  
  – saij
  Mar 26 at 11:59
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you edit your question and post the entire stored procedure. If your parameter name is @FiscalPeriod then all you have to do is concatenate that to your existing strings. If you are having issues please explain in detail.
  
  – Nick.McDermaid
  Mar 26 at 13:01
  

  
  
  
  

add a comment | 

How to create a stored procedure in SQL Server which creates dynamic partitions and process?

I have below code which creates a partition every time, and does process at one time in liked server.

Using below code I would like to create dynamic multiple partitions and process by passing partition name and fiscal period id to the procedure dynamically.

Please suggest.

declare @xmla varchar(max) = '

 "createOrReplace": 
 "object": 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 ,
 "partition": 
 "name": "FORECAST_FLASH_368",
 "source": 
 "query": "select * from FORECAST_FLASH_ARCHIVE_V where fiscal_period_id=368",
 "dataSource": "itv.world"
 
 
 

';

EXEC (@xmla) AT SSAS;



declare @xmlap varchar(max) = '

 "refresh": 
 "type": "automatic",
 "objects": [
 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 
 ]
 

';
exec (@xmlap) at SSAS;

sql-server sql-server-data-tools ssas-tabular xmla

share|improve this question

edited Mar 26 at 11:10

marc_s

597k135135 gold badges11481148 silver badges12841284 bronze badges

asked Mar 26 at 10:14

saijsaij

744 bronze badges

declare @xmla varchar(max) = '

 "createOrReplace": 
 "object": 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 ,
 "partition": 
 "name": "FORECAST_FLASH_368",
 "source": 
 "query": "select * from FORECAST_FLASH_ARCHIVE_V where fiscal_period_id=368",
 "dataSource": "itv.world"
 
 
 

';

EXEC (@xmla) AT SSAS;



declare @xmlap varchar(max) = '

 "refresh": 
 "type": "automatic",
 "objects": [
 
 "database": "FlashArchive",
 "table": "FORECAST_FLASH",
 "partition": "FORECAST_FLASH_368"
 
 ]
 

';
exec (@xmlap) at SSAS;

sql-server sql-server-data-tools ssas-tabular xmla

sql-server sql-server-data-tools ssas-tabular xmla

share|improve this question

edited Mar 26 at 11:10

marc_s

597k135135 gold badges11481148 silver badges12841284 bronze badges

asked Mar 26 at 10:14

saijsaij

744 bronze badges

share|improve this question

edited Mar 26 at 11:10

marc_s

597k135135 gold badges11481148 silver badges12841284 bronze badges

asked Mar 26 at 10:14

saijsaij

744 bronze badges

edited Mar 26 at 11:10

marc_s

597k135135 gold badges11481148 silver badges12841284 bronze badges

edited Mar 26 at 11:10

marc_s

597k135135 gold badges11481148 silver badges12841284 bronze badges

asked Mar 26 at 10:14

saijsaij

744 bronze badges

asked Mar 26 at 10:14

saijsaij

744 bronze badges