SQL Tools :: Tool To Create Graphical Layout Of Tables?
Nov 6, 2015
SQL Server 2008 R2. SQL Server Management Studio 2016 CTP2. I am looking for a tool where I could create and save a nice graphical layout of some tables in a database and their relation to each other (how they join or connect). I get something like this in the Query Editor, where it shows the fields and joins of tables as I select them. However, I cannot save this representation to show say in a PowerPoint or Word document (Visio, etc.).
Is there a tool that would enable me to create a nice document showing my tables and their relationships, which would also allow me to annotate each table with notes about the fields, etc.?
I searched and found a reference to "Visual Database Tools" which is supposed to be part of Management Studio 2016. However, I cannot find such a set of tools listed anywhere in my menus.
Do you guys use a graphical tool for building SQL - like MS Query? I know its lazy but it does save lots of typing for long multiple-table queries. Also, the graphical drag-drop method of adding relationships in Access would seem much quicker than adding rules thru sql.
Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
I would like to be able to view the complete SQL command that is sent from a Microsoft Access query which has linked SQL Server tables through ODBC, and the query also has some VBA functions manipulating and filtering data.I tried using the SQL Profiler to view the sql command send to SQL Server but all I see is the simple query format and some of the fields. I don't see the fields that have a VBA function such as trim(fieldname) etc in the column. And If I use a VBA fucntion in the Criteria then nothing shows up in SQL Profiled under the TextData column.How can I see the complete SQL command sent to SQL Server?
I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.
SELECT DISTINCT CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END + CONVERT(CHAR(1), '') + (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber)) + CONVERT(CHAR(1), '') + (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))
I designed a report in reporting services 2005. When i look at the view layout for the report everything is aligned correctly but if i go to print preview one matrix is shifted up or down. I have tried to put the matrix inside a rectangle but the issue is still present. Does anyone know why this is happening or any suggestions on what to do to fix it.
I'm a newbie with SQL so excuse my igorance. I'm running an application that uses MSDE2000 as its source db and would like to create a small [10 field] db that would also use MSDE. I want to use a separate db though and not share the first one. What I'm looking for is suggestions on a development tool to create the database with as I'm not familiar with SQL tools. I have a lot of Access experience though so the concepts are not new.
Database requirements are pretty basic.
10 fields [8 text, 2 numeric] A button field to Add New Record A search field Column sorting.
That's it. I could do this in a pinch with Access so knowing what tool to use should make it easy. Any chance that I could do it with Access and then port it to a SQL db?
Just a general question about any recommendations for a third party tool that would allow setting up a data-dictionary against SQL Server tables and a user could go in and build their own queries, reports. Perhaps they could save their query/report definitions for reuse. Other nice to haves - ability to output to PDF and export to XLS. Would prefer web interface, but can consider desktop as well. Any immediate recommendations?
Has anyone created a report that shows something like performance monitor, but shows only SQL CPU use. I would be interested to see which users cause high CPU use, at what moments in time is SQL processing high and what queries are causing the processor to spike.
I have many tables, and I just want to print the relationships between them. The ones without foreign keys to primary key relations are irrelevant. I made a diagram of all tables in sql server management studio, and it shows the key relations, but its a very large diagram horizontally and vertically. Is there a way to print the whole thing so that it doesn't take endless pages that I don't know how to piece together?
Dear SQL Server users, I would like to build a Graphical User Interface for my MS SQL Server 7.0 database. Should I use Visual Studio, Visual Basic, or Dalphi? I am new to this community. Any advice will be appreciated. Thank You.
I am trying to load the data from one data base to another using the Task->Generate Script->tables (data only). So I saved the script of the data and when tried to run the script to the destination DB, getting the below error when ran the script.
Error: Visual Studio has encountered an exception. This may be caused by an extension.You can get more information by running the application together with the /log parameter on the command line, and then examining the file'C:UsersKK67AppDataRoamingMicrosoftAppEnv10.0ActivityLog.xm'. When tried opening the folder using the above path... don't see anything for AppData folder from that path.
I want to be able to load and unload, byte-for-byte, a file to and from a field of data type IMAGE in a SQL Server CE database. The files are larger than 8000 bytes so IMAGE appears to be the way to go data type wise.
I'm using VB 2008 but pseudocode for any VS language would be awesome.
I was able to do this without much effort using ADODB and its stream object, fetching a recordset object and updating the appropriate data field one "chunk" of bytes at a time. I cannot for the life of me find a way of doing this using, for example, a SqlCeResultSet.
I've tried using ADODB to connect to a SqlCe SDF database file, using a connection string that I found online, and am able to write action and select queries successfully against the database... but when I try and return a recordset that is updatable, it returns an error. So no help there.
I've searched other threads in the forums, and online in general, and I've found information on loading/unloading graphic files using a memoryStream and the toArray method thereof, but this will not work as many of the files I want to load and unload as BLOBs are not graphical in nature. FileStreams do not implement the toArray method, so I can't just stream an arbitrary file's worth of bytes that way. I also do not want to change the original file in any fashion--e.g., a hash of the source file should be identical to the file after it has been loaded into the data field and then unloaded into a new file.
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
When adding/removing tables in SSMS 2016 CTP2, I keep tripping into:
Index was outside the bounds of the Array
When editing a SQL Server 2014 Database.
If I use the SSMS for SQL 2014, the error is not happening. Possibly a reintroduced bug. Tried to use the Connect site to submit, but it says I am not enabled to use this area.
Power pivot graph. I do have different transaction names, dates, count, exec time with me, I need to display each transaction execution day, execution time in graph format. Suppose execution time is on Y axis and Exec time is on X-axis then to display the which transaction takes more time for execution.I have Query just I need to display in graphical format
Is there a way to leave the graphical 'Include Execution Plan' on by default in SSMS? I don't know how many times I run a long-running query, say to myself, "wow, that took a while; I wonder what the execution plan looks like?" only to realize that I left it turned off. Now I have to turn it on, and wait for the query to run again. I'm guessing there's a setting in the options somewhere to always leave it on, but I'm not sure where
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
The installation SQL 2008 R2 Management Tools on a Windows 7 workstation fails with the error, The specified account already exists.
Final result: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
I am running SQL Server 2005 x64 Enterprise under Windows 2003 x64 Enterprise. My current backup strategy uses T-SQL jobs run by SQL Agent (writes out *.bak files) and then I have an Integration Services job that copies the *.bak files to our NAS device. I have performed a restore without issue. The jobs are all automated every four hours via SQL Agent. Is this a sound strategy or are there additional benefits to using 3rd party tools? If so, what are the advantages and which tool provides them?
Login failed for user 'TOSHIBA-USERASPNET' I know that the file persmission for the web application have to include aspnet, so i keep resetting the folder permission for aspnet in file manager, but the login failed keeps coming back every day or two problem is after working with VS05 Pro, SQL Server Management Studio CTP, somehow the aspnet persmission get changed, use alot of sqldatasource wizards and often there is a conflict/hang between the datasource wizard and the need to have the mdf in a dettached state within VS server explorer, not sure but the procedure to fix this seems to be to reboot, detach and re-attach the mdf in the Sql server Studio tool, re-apply the aspnet file permission on the web app folders (wonder should i be doing this in IIS instead), make sure the mdf within server explorer is detached, the it works anyway, getting real tired of the resulting delays and design time derailment, clues greatly appreciated, thanks sometimes i can use View in Browser when in VS05 form view and i wont get the aspnet folder permission error and other times i do. last thing, is it a bad idea to give aspnet full permission for the entire web applicaiton??
Is there - apart from the notorious RESTORE HEADERONLY - an tool which is able to tell which SQL Server version created a specific BAK file? I'm looking for a tool that can be used w/o an available/running SQL Server installation.
Alternatively, is there any documentation about what is read with RESTORE HEADERONLY so I could write a tool myself?
Where would I find the version "bytes" in a BAK file?
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
