Databases
StackOverflow2013
Postsdbo
POFind a dynamic database name in a view
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFind a dynamic database name in a view
    primarykey
    data
    text
    <p>In a SQL server database I have have this kind of table :</p> <pre><code>EVENT201201.dbo.EvAcc EVENT201202.dbo.EvAcc EVENT201203.dbo.EvAcc EVENT201204.dbo.EvAcc EVENT201205.dbo.EvAcc ... EVENTYYYYMM.dbo.EvAcc </code></pre> <p>I also have <code>DBACCES1.dbo.*</code>. I created a query like this :</p> <pre><code>DECLARE @SQL varchar(511) DECLARE @SQL_ACC varchar(250) DECLARE @SQL_UClass varchar(250) DECLARE @SQL_UClassDef varchar(250) DECLARE @TABLENAME varchar(250) SELECT @TABLENAME ='EVENT' + CONVERT(char(4), YEAR(GETDATE() - 1)) + CONVERT(char(2), MONTH(GETDATE() - 1)) SELECT @SQL_ACC = '' + QuoteName(@TABLENAME) + '.dbo.EvAcc ON EvAcc.fpointeur = cards.fpointeur' SELECT @SQL_UClass = 'dbacces1.dbo.UClass ON UClass.fpointeur = cards.fpointeur AND MClass = 1' SELECT @SQL_UClassDef = 'dbacces1.dbo.UClassDef ON UClassDef.SClass = UClass.SClass' SELECT @SQL = 'SELECT cards.FPointeur, Ref, Peri, cout, cin, edate FROM dbacces1.dbo.cards INNER JOIN ' + @SQL_ACC + ' INNER JOIN ' + @SQL_UClass EXEC(@SQL) </code></pre> <p>It works but I need to put this in a view. It doesn't work. I have this error (sorry for the french) :</p> <pre><code>La construction ou l'instruction SQL Déclarer un curseur n'est pas prise en charge. </code></pre> <p>It say than declare is not allowed in a view.</p> <p>I tried to create a valued table function and it doen't work because I can't use the function <code>exec</code> with a string in a function. I have this error :</p> <pre><code>Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function. </code></pre> <p>I also tried to create procedure but I can't use it as a table in my query. I tried to create a function which return the database name only but it doen't work again.</p> <p>Do you have any solution?</p> <p><strong>EDIT 1 - Solution</strong></p> <p>I create a script executed each month. This is my script :</p> <pre><code>DECLARE @start_date DATETIME DECLARE @end_date DATETIME DECLARE @sql VARCHAR(MAX) DECLARE @dbname VARCHAR(15) DECLARE @year VARCHAR(4) DECLARE @month VARCHAR(2) --Start the SQL request SET @sql = 'CREATE VIEW dbo.viewEvAcc AS ' SET @start_date = convert(DATETIME, '01/01/2011', 101) SET @end_date = GETDATE() --Loop from the start date to now WHILE @start_date &lt; @end_date BEGIN --Find new year and month SET @year = CONVERT(CHAR(4), YEAR(@start_date)) SET @month = RIGHT('0' + RTRIM(MONTH(@start_date)), 2); --Create the db name with year and month SET @dbname = 'EVENT' + @year + @month --Concat the SQL Request SET @sql = @sql + 'SELECT * FROM ' + @dbname + '.dbo.EvAcc' --Update the start date for the month after SET @start_date = CONVERT(VARCHAR(8),DATEADD(MONTH,1,CONVERT(VARCHAR(8),@start_date,112)),112) --If the date is not the last date, it add a union IF @start_date &lt; @end_date BEGIN SET @sql = @sql + ' UNION ALL ' END END -- drop and create a view with new information USE dbacces1 IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewEvAcc')DROP VIEW dbo.viewEvAcc; EXEC(@sql) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDougui
    1. USDougui
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFind a dynamic database name in a view
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload