Design Issues - Dynamic Templates

Mar 16, 2007

I'm currently working on a project that I'm having some design difficulties with and I was hoping that some of you could help me out. What I'm designing is sort of a webbased directory service based on different custom made templates. Imagine the yellow pages with different column requirements for each branch. Example:

Template: Restaurant
Columns: Name, Adress, TakeAway

Template: DBAForHire
Columns: Name, Adress, YearsExperience

I have designed a few tables that look like this:
DECLARE @templates TABLE
(TemplateID INT IDENTITY(1, 1), TemplateName VARCHAR(50))
DECLARE @attributes TABLE
(AttributeID INT IDENTITY(1, 1), AttributeName VARCHAR(50))
DECLARE @template_attributes TABLE
(TemplateID INT, AttributeID INT)
DECLARE @values TABLE
(ValueID INT IDENTITY(1, 1), TemplateID INT, AttributeID INT, Value VARCHAR(200))

INSERT INTO @templates
SELECT 'Restaurant' UNION ALL SELECT 'DBAForHire'

INSERT INTO @attributes
SELECT 'Name' UNION ALL SELECT 'Adress' UNION ALL
SELECT 'TakeAway' UNION ALL SELECT 'YearsExperience'

INSERT INTO @template_attributes
SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 3 UNION ALL
SELECT 2, 1 UNION ALL SELECT 2, 2 UNION ALL SELECT 2, 4

INSERT INTO @values
SELECT 1, 1, 'Lumbagos TexMex' UNION ALL SELECT 1, 2, 'Mainstreet Oslo' UNION ALL
SELECT 1, 3, 'Yes' UNION ALL SELECT 2, 1, 'Peso' UNION ALL
SELECT 2, 2, 'Somewhere IN Sweden' UNION ALL SELECT 2, 4, '10'

SELECT TemplateName, AttributeName, Value
FROM @templates a
INNER JOIN @template_attributes b
ON a.TemplateID = b.TemplateID
INNER JOIN @attributes c
ON b.AttributeID = c.AttributeID
INNER JOIN @values d
ON b.TemplateID = d.TemplateID AND c.AttributeID = d.AttributeIDThe problem I need help with is that with the current design all "Values" are defined as varchar(200)...is it possible to change the design so that it's equally flexible but also fasilitates different datatypes? In some cases I would like to have a description column with way more than 200 characters, and I'd also like to save numbers as actual numbers...not like my example above with "YearsExperience". Is it at all possible?? I would appreciate your insight on this...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

View 4 Replies


ADVERTISEMENT

How To Design Dynamic Reports Based On User's Choice

Dec 13, 2006

Hi all,

I'm a beginner to Report Services, and have tons of questions.

Here's the first one:

if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?

For example,

the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.

Could someone tell me how to do it (create and deploy the reports)?

Thanks a million!

Jonee

View 1 Replies View Related

Design Question- Dynamic Configuration Of Metadata In Dataflow

Mar 20, 2007

I have a design question that I'd like some input on.  I am trying to archive data from an extremely large production database.  The tables to be archived changes quite often.  It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there. 

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table.  I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS.  I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework.  (using this link as a guide:  http://msdn2.microsoft.com/en-us/library/ms345167.aspx). 

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

View 22 Replies View Related

DB Design :: Make Dynamic Columns When Duplicated Values Appear

Jul 17, 2015

I'm trying to run the following sql

SELECT DISTINCT
Anvendelseskoder.[Usage Code] AS [Building Code],
Anvendelseskoder.[Usage Code Value] AS [Building Description],
HeleDanmark_DAWA.KVHx
FROM Anvendelseskoder
RIGHT JOIN HeleDanmark_DAWA

[Code] ...

It gives me the following error:
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "aa.KVHx" could not be bound.

When clicked, it marks the first time i call "aa.KVHx" 
Which I do in: "WHERE S2.Nr=2 AND s2.KVHx=aa.KVHx) AS Kode2,"

View 2 Replies View Related

Templates

Aug 25, 2006

I'm new to SQL and am looking to see if there are simple templates.

I need a personel database where I can attach documents and a log to certain people in the database.



Any ideas?



thanks

View 1 Replies View Related

Question About Sql Templates

Feb 26, 2007

Hi

i try to find on internet ready made sql tables but i still struggling,do enyone know some advices from websites where i can find sql files?

thank you

Regards gigi

View 3 Replies View Related

Package Templates

Nov 27, 2006

Greetings SSIS friends,

I am currently reading Ralph Kimball's "The Microsoft Data Warehouse Toolkit". In the book it mentions creating template packages to speed up the creation of future packages. In the book they also mention creating solutions and projects.

All sounds fair enough but I am confused a little.

Where should the template package be stored?! I created a Project and renamed the default package as "TemplatePackage". I setup some standard data sources and package variables. Do I now create a new package and place it in the same project? Or should my template package be deleted from the project?!

Hope my questions makes sense.






View 4 Replies View Related

Design Patterns Research - Dynamic Error Count Manipulation To Determine On What Type Of Error To Stop Job

Jan 31, 2008



I would like to fail a package depending on the error. The package extracts data from Excel files. I would like to continue processing if an Excel file is badly formatted, but stop processing if there is a serious issue. like the file server hosting the Excel files crashed.
I was thinking about dynamically changing the MaxeErrorCount property based on the Error ID or description.


Any ideas on an intelligent/simple way to do this

View 1 Replies View Related

Need Help With Dynamics SL (Solomon) Templates

Apr 26, 2007

i hear of a feature called TEMPLATES where you can save the data thats entered into any particular Dynamics SL screen and paste the data in to the same screen at a later time (saves time if there is data that is constantly inputed into the same screen over and over). my question is, where are the templates saved at on the hard drive? and can it be opened/edited in notepad or something else if say we want to make changes to the data before pasting/loading it back?

View 3 Replies View Related

Changing The Default SQL Templates

Jan 23, 2008



Hi,

I've created a new Stored Procedure template and saved it to .Microsoft SQL Server90ToolsShellTemplatesSqlStored Procedure. I can view (and use) the template via the Template Explorer, however, I want this new template to become my default template.

What do I mean by this? - I want to be able to right-click on stored procedures in my object explorer, click New Stored Procedure, and be forced to use my new template, rather than Create Procedure (New Menu).SQL.

Thanks

Jon Derbyshire

View 5 Replies View Related

Where Are The Sql 2005 Templates Stored?

Oct 16, 2006

I can't locate them on disk.



TIA,



barkingdog

View 1 Replies View Related

Automatic Values In Templates

Feb 5, 2008

I've got several templates that we use for creating stored procs and such and they all include a standard header for author and version information. One of the fields is the creation date. Is it possible to have the template automatically populate that field with the current date when a new file is generated from the template?

Thanks.

J

View 1 Replies View Related

VSI Templates Install Failed

Mar 6, 2006

I just installed SQL2005 (and VS2005). When I run VSSQLCLRTemplates.vsi from the SQLCLR Project all the templates fail to install. It gives the error "Installation stopped because the directory for the ProjectType value did not exist. The project type is invalid for your installation of Visual Studio."


I do have a my documentsvisual studio 2005 directory. I am trying to start writting user defined aggregates. If I can install the templates manually, that'd be fine.

Thanks in advance.

View 7 Replies View Related

OLE DB Consumer Templates - Updating DB With DynamicAccessor

Feb 26, 2008

Hello,
I'd like to use OLE DB consumer templates, but I have troubles with changing values in my test DB. I've created a simple MS access DB 'ZK2Test.mdb' with one table 'pokus'. This table consist of one column with number (short integer).
I can read values, thats works fine. But when I try to write something, it is not propagated into DB.
I call SetValue and Update on cTable object and every call returns S_OK. When I try to read value of first row after my write, I get my new value of first column. But after moving cursor somewhere else and back to first row, reading first column of first row returns again the previous value.

In code example is my attempt to change value 1 from first row to 421
Sequence of commands is (C1 means Column 1):


MoveFirst
GetValue(C1) = 1
SetValue(421 to C1)
Update()
GetValue(C1) = 421

MoveNext()
MoveFirst()
GetValue(C1) = 1

Could anybody please tell me, what is wrong?

Thanks for your answers






Code Snippet

CDataSource DTSrc;
CSession DTSession;
CTable<CDynamicAccessor, CRowset> cTable;

// Open DB
res = DTSrc.Open (L"Microsoft.Jet.OLEDB.4.0", "..\ZK2Test.mdb");
CHECK_RES(res);
res = DTSession.Open(DTSrc);
CHECK_RES(res);
CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IRowsetUpdate, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
res = cTable.Open(DTSession, L"pokus", &propset);
CHECK_RES(res);

// Move to first row
res = cTable.MoveFirst();
CHECK_RES(res);

dataPtr = cTable.GetValue(1);
x = *((SHORT*)dataPtr);
// Now the value of x is 1

x = 421;
if (!cTable.SetValue(1, x)) {
return FALSE;
}
res = cTable.Update();
CHECK_RES(res);

dataPtr = cTable.GetValue(1);
x = *((SHORT*)dataPtr);
// Now the value of x is 421

// Move cursor to the second row and back
res = cTable.MoveNext();
res = cTable.MoveFirst();
dataPtr = cTable.GetValue(1);
x = *((SHORT*)dataPtr);
// Now the value of x is 1 (the original value, WHY!?)

View 5 Replies View Related

Missing Projects/templates Options?

Mar 30, 2007

I have a limited selection of projects.

I want to be able to create business intelligence project for reporting but the business intelligence project option is not there when I go to file/new/project.

When I first installed visual studio I chose visual basic/web development as the preference would this prevent other project types from being displayed?

View 1 Replies View Related

OleDb Provider Templates Problem

Jan 7, 2008



Hello,

I have a question regarding oledb templates in Visual Studio 2005. For some time I was trying to get through but I can't. Can anyone tell me how can I read the properties of the DataSource class being inside Rowset or Command class?

I mean: DataSource class is creating Session class. Session is creating Rowset or Command and so on.. But how can I read the INIT_DATASOURCE property of the DataSource class while for example a Command is called?

Could you give me a sample of code which retrieves the INIT_DATASOURCE property in the Execute method of the

CRowsetImpl class?



class ColedbRowset :


public CRowsetImpl< ColedbRowset, ColedbWindowsFile, ColedbCommand>

{



public:



HRESULT Execute(DBPARAMS * params/*pParams*/, LONG* pcRowsAffected)

{


????
}
}

View 3 Replies View Related

Find Templates For Global Or System Functions And Sp In 05

Jul 28, 2006

In 2000 you could look at templates and find templates for global or system functions where are they in 05?

View 1 Replies View Related

Integration Services :: Missing Project Templates

Oct 25, 2015

I have installed Visual Studio 2013 Express for Windows Desktop and Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013  yet there are no project templates for Analysis Services, Integration Services, and Reporting Services available when I attempt to create a new project.

Details of install are:
Microsoft Visual Studio Express 2013 for Windows Desktop
Version 12.0.40629.00 Update 5
Microsoft .NET Framework
Version 4.5.51209
Installed Version: Desktop Express
Team Explorer for Visual Studio 2013   06157-004-0441005-02546

[code]....

View 9 Replies View Related

Updating Report Templates Without Losing Subscriptions

Feb 14, 2007

Hello,

We are using Reporting services 2005 and have been for some time. Recently we have started using the subscriptions as well. However we have come across a problem with this.

Currently we are updating our report templates by removing them and then creating them again. This worked fine until we started using subscriptions. Now, when the reports are removed, the subscriptions that are associated with the reports are also removed.

Is there a way to update a report template, without having to remove the report first? And can this be done programmatically? The code is currently publishing the reports by calling ReportingService2005.CreateReport(name, "/" + reportFolder, false, template, null);

Thanks,
Denise.

View 1 Replies View Related

Changing Variable Scope In Package Templates?

Jun 15, 2006

Is there any way to change variable scope while using package templates?

I have created a package template that has several variables, a "typical" control flow and data flow. My goal was to try and use this as a starting point to create other packages within the same project and edit as required in the new package. I couldn't find any way (yet) to change scope of variables...these still show as belonging to the scope of package used to create the template.

Appreciate any help...thanks.

View 4 Replies View Related

Reporting Service Project Templates With VS 2008

Feb 3, 2008

I could not find The Reporting Service Project Template with Visual Studio 2008. It was available with Visual Studio 2005.
How i can add the reporting service project into VS2008?

any post will be appreciated.
thanks

View 1 Replies View Related

Intergation Services Templates For Visual Studio 2005

May 9, 2006

We are setting up developer machines with Visual Studio 2005 and SQL SERVER 2005 Client tools. My question is how do I get the templates for Integration Services into Visual Studio 2005 without installing BIDS?

View 1 Replies View Related

Can Not See Report Model Template In Business Intelligent Project Templates

Mar 31, 2007

Hi all,

I desperately need to setup the AD-HOC reporting on SQL server 2005.
I think one of the templates that I need is in Business Intelligent Project Templets Reporting Model Template in Visual Studio Templates.

But I do not see it in there.
Can some one please let me know where can I get the Reporting Model Template, or a link to where I can download or how to create it.

Thanks or any help.


Abrahim

View 3 Replies View Related

Reporting Services :: Application That Uses Report Builder To Edit Templates

May 1, 2015

I have an application that uses SQL Report Builder to edit templates.  When I go to add a dataset to add a custom field to the report, I get a permissions error (screenshot Error).  When I check the credentials for the shared datasource, they are all grayed out, so I can’t verify the credentials being used (screenshot Error 2).  According to SQL Management Studio, the user credentials have the right permissions.  The weird thing is, I can access the datasets and shared datasource embedded in the report just fine.  I just get a permissions error whenever trying to create a new dataset.

View 2 Replies View Related

MovePrev After MoveToBookmark In OLEDB Consumer Templates Doesn't Work Properly

Mar 3, 2008

Hello,
I tried to use bookmarks in OLEDB consumer templates and I have problems with using MoveToBookmark. This method sets cursor position correctly on bookmarked row, but it seems to me it doesn't set the right cursor position in DB. When I call MovePrev after MoveToBookmark, I will not get previous row of the bookmarked row.
Simplified Example - I get bookmark of the first row, then move to the 3th row. Call MoveToBookmark (current row is no the first row). But when I call MovePrev I'will not get DB_S_ENDOFROWSET but the second row:


MoveFirst()
bk = GetBookmark()
MoveNext()
MoveNext() // Now I'm on third row
MoveToBookmark(bk)
GetData() - Now I get data from 1st row
MovePrev()
GetData() - Now I get data from the second row!!


All calls to OLEDB returs S_OK.
Does somebody now, whats wrong?

Thanks

View 1 Replies View Related

DB Design :: Database Design For Matrix Representation

May 13, 2015

I have a scenario like below

Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1

How to design tables in SQL Server for the above.

View 2 Replies View Related

Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

Hi Craig/Kamal,

I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.

Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.

We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.

I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.

Your help in this respect is highly appreciated!

Thanks,


Hi Anthony, I am glad the Web cast was helpful.

Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.

I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

Thanks



Craig Guyer
SQL Server Reporting Services

View 12 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?

May 2, 2007

If anyone could confirm...

SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.

E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.

First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.

However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.

Question: I thought the filters would remain dynamic and be applied on each sync?

I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!

Question: I wonder why if parent records are supplied, why not child records?

If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...

Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?

View 4 Replies View Related

Database Design/query Design

Feb 13, 2002

Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:

-------
Fixture
-------
fix_id
fix_date
fix_played

----
Team
----
tem_id
tem_name

-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals

It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.

Fixture contains the details about the fixture like date and fixture id and has it been played

Team contains team info like team id, name, associated graphic

TeamFixture is the table which links the fixture to it's home and away team.

TeamFixture exists to prevent a many to many type relationship.

Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!

View 2 Replies View Related

DB Design :: Table Design For Packages

Aug 18, 2015

I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show.  How to design the table.. 

Product name   :  
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |

CTN, NOs
Price:
50,20,5 |
40,6

(Remarks for your reference):CTN=10nos, OTR=4 nos  
| CTN=8 Nos

View 3 Replies View Related

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?

Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),

[code]....

View 1 Replies View Related

Mixing Dynamic SQL With Non-Dynamic In Stored Proc

Mar 24, 2007

I have a Stored Procedure for processing a Bill of Material.

One column on the Assembly Table is a Function Name that contains some busniess rules.

OK, now I'm doing a Proof of Concept and I'm stumped.

Huuuuh!

I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.

Note: The function just returns a bit.

So; here's what I had in mind ...

if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output




CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS

declare @SQL varchar(8000)

set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'

exec (@SQL)
GO



Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.


So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?


My many thanks to anyone who can solve this riddle for me.
Thank You!


Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.

------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END

------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved