CREATE TABLE employees (
employee_number char(6) NOT NULL
, known_as char(20)
, surname char(20)
, job_title char(20)
, manager_number char(6) NULL
, unique_identifier char(6) NOT NULL PRIMARY KEY
)
--unique_identifier is in the format 000123456789
Now I have a conundrum when trying to create organization charts in Visio, so I figured I'd try reproduce the format that a bunch of walkthroughs suggest, which is with the first column being an int identity(1,1) column as the employeeID with the managerID being an int column also, referencing the employeeID.
Hope I've not lost you just yet ;)
So here's what I figured - create a new table with the new integer columns, slap my current data into it and then update the managerID as necessary...
Except I can't work out the update statement for this!
CREATE TABLE gvVisioTest (
employeeID int identity(1,1)
, employee_number char(6)
, job_title char(40)
, department_reference char(10)
, managerID int
, manager_number char(6)
)
GO
INSERT INTO gvVisioTest(employee_number, job_title, department_reference, manager_number)
SELECT employee_number, job_title, department_reference, manager_number
FROM employees
GO
--Update managerID with relevant employeeID
GO
DROP TABLE gvVisioTest
Any ideas?
Oh and this is legacy so the design is flawed, modified over time (the manager field is a bodge put in 5 years ago), so yeah unfortunately I have to work with what I've got :(
Hello,I'm facing with a problem though it may look very simple for many ofu it is a bit complicated for me So kindly please help me outi have a table emp with employee id,name and mgridI need to display the managers and the employee working under thatmanager . How should this be done.Thanking uBroken Arrow
I am in the process of creating another database for one of our departments, I'm creating a diagram of the structure in visio 2003. I'm done with the diagram but when I try to export to sql it wont let me. Anyone ever had this problem before?? This is the Error message I'm getting
ODBC Error: 01000 [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'Audit_Tbl' has been created but its maximum row size (20070) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I am trying to generate a report from the database. I am using Visio 2010. I would like to include the table names, column names , column types and the comments that I entered in the NOTES field associated with the tables and columns. Basically, I would like to generate a data dictionary report.
I am having data model diagram containing 150 odd tables in MS Office Visio 2007.The Datamodel Diagram even has the Relationships between the Tables.
Do we have any Procedure to export the Datamodel Diagram from the Visio to the Sql Server carrying all the relationships.
We are having the reverse engineer procedure to export database diagrams from Sql server to the datamodel diagram in the Visio in the same way, do we have any procedure to Export DataModel diagram from Visio to SQL Server to create tables with all the relationships?
Is it possible to import the SSIS workflow diagram into visio? Is there a way to extract the images and import them into SSIS for use with creating a workflow, or better yet, is there a way to re-engineering the workflow in SSIS with Visio?
For an initial step, I would like to re-create the SSIS workflow in Visio for presentations.
I'm running Visio 2003 on Windows XP with SQL Server 2005 Express rather than MSDE. Is this configuration supported? If yes, how does the SQL database get built since SSADMIN.exe (delivered with the Visio SDK) does not detect an MSDE compatible database when SQL Server 2005 Express is not installed?
Hi, i want to do the physical modeling using MS Visio. Whether is it availble with Microsoft Visual Studio 2005 or not?? If Yes, Could you please asssit what necessary loaded with Studio and Version Details.
Data mining add-in for Office Visio 2007 is great. But what I am concerning is-to end users (to technical users) how could we deliver the data mining models results in a better way (e.g. integrate with other possible clients ) to make it dynamically and easily undertood?
I€™m new at SSIS and would like to try the follow easy function. Just get some data from my database und put into a text-file. All function going well. Right now I€™ve got just one problem I can€™t resolve. At the window €śConnection Managers€? are the connections I used in my project. But how can I make this connection more dynamical? E.g. I have a connection.txt file and the Connection Manager should set his properties after read this txt-file. Have someone an idea?
I'll try to explain what I'd like to do. On my SQL Server 2005, SSAS contains a mining model (In fact a cluster one). I'd like to show a detailed diagram build from this model on a web site. This diagram (and this is why I need automation) would depend on the user who's consulting it. For example, a firm A will see the number of its customers in each cluster, and this information will be different for another firm B
So, I thought about several steps to perform: 1) Feed the model with the data for each firm 2) Build a Visio diagram from the previous data using the DM addins for Visio 2007 3) Generate HTML using the Visio export wizard 4) Publish HTML And (important) this should be done automatically.
I made some experiments: Step 1 is easy to perform with SSIS Step 3 is also easy to do with the Visio SDK (using, among others, the exportAsHtml control) and Step 4 is trivial
I failed to perform stage 2, even with the SDK, since creating a diagram from a DM template requires the user to fill a wizard. By code, I'm able to create a document from a DM template, Drop a DM Stencil but when the wizard appears I'm unable to get a handle on it. And even if I was, I dunno if it would be a "clean" way of doing.
So my question is: first of all: isn't there an easier way to generate HTML from Mining Models automatically? And if my approach to this problem is the best (or the "least bad"), how to generate datamining Visio diagrams from code automatically?
I am unsure if i am managing my connections in the correct way... but as it stands we have them in a .dtsConfig file that each package references in order to get the relevant details..
This is working fine, except that in total our ETL project uses 6 different connections, and none of the packages utilise all 6.
This leads to an error message upon opening up EVERY package stating that there are connection strings in the .dtsConfig that are unused, yeah, thats the idea!
The way i have gotten around this, is to just include all 6 connection managers in every package, but my question is, is there a better way to achieve this same result??
are we going about this in a bit of a cackhanded way?
Programmatically generating HTML visualizations of models using DM Add-ins the thread in sqlserverdatamining.com contains a code block listed below. This code doesn't work it fails in the statment : "helper.DrawDecisionTree(helper.ActivePage, parameter);" . It says "Visio rendering failed"..
I didn't understand the part "parameter.ConnectionName = "AdventureWorksConnection";" in the code block. Which type or object is expected by parameter.ConnectionName ? An Adomdconnection or Oledbconnection. Type is string but does it expect us the name of connection? Please help me because i didn't figure out the solution.
By the way i'm using Visio2007 and installed everything which has told in the article and added as reference.
Code Block using System; using System.Collections.Generic; using System.Text; using System.IO; using Microsoft.SqlServer.DataMining.Office.Visio.Programability;
namespace DMDigramUsingViso { class Program { //This is the folder where the data mining addins have been installed. public const string DMAddinInstallPath = @"C:Program Files (x86)Microsoft SQL Server 2005 DM Add-Ins";
//This is the name of the Visio template file for the data mining addins. public const string VisioTemplateName = @"Microsoft Data Mining.vst";
//This is the name of the target HTML file. public const string WebFileName = @"TargetMail.htm";
//---------------------------------------------------- // Create the parameter object for the decision tree // drawing and populate the required parameters //---------------------------------------------------- ParameterDecisionTree parameter = new ParameterDecisionTree(); //Friendly name for the connection parameter.ConnectionName = "AdventureWorksConnection"; //Name of the Analysis Server parameter.DataSourceName = "localhost"; //Name of database parameter.CatalogName = "AdventureWorks"; //Name of the mining model parameter.ModelName = "Target Mail"; //Name of the decision tree predictable parameter.TreeName = "Bike Buyer"; //Flag to hide the progress dialog parameter.Silent = true;
//---------------------------------------------------- // Optional properties for the parameter object //---------------------------------------------------- //Shade tree nodes using support parameter.GradientType = GradientType.SupportGradient; //Show support in the nodes parameter.ShowSupport = false; //Fill color for the nodes parameter.FillColorArgb = System.Drawing.Color.Aqua.ToArgb(); //Fill pattern color for the nodes parameter.PatternColorArgb = System.Drawing.Color.Azure.ToArgb();
//---------------------------------------------------- // Drawing helper object which performs the actual rendering //---------------------------------------------------- DMDrawingHelper helper = new DMDrawingHelper(visioFile, false); //Start the Visio application helper.Start();
/* Helper object allows access to Visio object model using the following methods: helper.CreateNewDocument(); helper.CreateNewPage(); helper.ActiveDocument; helper.ActivePage; helper.Application; */
//Render the decision tree on the current active page Console.WriteLine("Rendering decision tree using the visio engine..."); helper.DrawDecisionTree(helper.ActivePage, parameter);
//Convert all the pages of the drawing to HTML Console.WriteLine("Converting to HTML using the visio engine..."); helper.SaveDocumentAsWebPage(helper.ActiveDocument, -1, -1, webFile, DMDrawingHelper.ShowNavigationBar | DMDrawingHelper.ShowPanAndZoom | DMDrawingHelper.ShowPropertiesWindow | DMDrawingHelper.ShowSearchTool);
//Close the Visio App helper.Close(); Console.WriteLine("HTML File is generated at : {0}", webFile);
I am using SqlServer 2000 with asp.net 2.0, I have a table tbl_employees, with fields (empId, empName, empManagerId), with following data...
empId empName empManagerId
1 A
2 B 1
3 C 2
4 D 2
5 E 4 Now the question is that what should be the single line query or best solution if i want to get all employess under a perticular manager ?For example; Employees under 'A' are (B,C,D,E) //(C,D,E are also indirectly under A)Emplloyess under 'B' are (C,D & E; E is also under B as his because his managwer 'D' is himself under 'B') Please advise..Thanks alot.
I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .
The table structure is defined and i cannot edit it .
Please let me know if we could have a single query to do this .
I'm trying to create a new package and keep getting different errors. The package uses the same .NET ODBC provider as another package.
I can test the connection fine.
When I add a DataReader Source and select my connection I get the following warning:
Not all connection managers have been set. Set all connection managers.
If I click the refresh button I get this warning message:
Error at Data Flow Task [DataReader Source [6338]]: The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID.
All my collections have ID's and they are all read only.
If I ignore those warning I can enter my SQL command and go to the column mappings tab and see all the columns I selected but I then see several warnings all saying the same thing.
Driver does not support this function
I'm not able to close the Advanced Editor at this point.
I'm trying to create a custom connection manager and I have ConnectionType in the DtsConnection attribute set to "MSMDM". When I tried to create one in a package I got:
The connection type "MSMDM" specified for connection manager "{A8543B4B-41D9-4E69-9580-3D1A491AD719}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. (TestHarness1)
So, a few questions:
What are the valid connection manager types?
Why can I not create a new connection manager type of my own?
If I cant create new connection manager types then the purpose of being able to build custom connecton managers is defeated. Hence, what's the point in giving us the ability to create custom connection managers? -Jamie
This is probably a silly question but what exactly is the purpose of creating Data Sources when it is possible to set up individual connection managers?
I am not seeing an option to use the OraOLEDB.Oracle driver when defining connections in ssis. This driver shows up in management studio when trying to create linked servers so I don't know why it wouldn't show up here. All the client tools are installed fine and the linked server works. Any ideas?
When I brought up the BI DEV studio first tiem the Connection Managers area showed up, but after I closed it by mistake from next time on wards it is not coming up.
Can some one from this form help me how to get the Connection Managers area(pane) back?
I have a query that returns a result that looks like this:
amount count steve 122000 12 jim 145213 13 paul 62325 7
I need to add 2 columns to this query that rank the employees by count and amount with a number, so for example:
amount amount_rank count count_rank steve 122000 2 12 2 jim 145213 1 13 1 paul 62325 3 7 3
I was going to paste the entire query in but I thought I'd try this first to see if I could get by with a hint. So essentially I need to evaluate with an order by or something and then insert a number as amount_rank and number as count_rank.
