Hi all,
I am trying to retrieve tables name for a database diagram within SQL database 2000 using T-SQL .I did some search Found that using dtproperties table I can get the diagrams name. but there is no information about the tables within these diagrams.
I wonder if there is any one can help on that
Regards
Tamer
Hi,
I am trying to retrieve tables name for a database diagram within SQL database 2000 using T-SQL .I did some search Found that using dtproperties table I can get the diagrams name. but there is no information about the tables within these diagrams.
I wonder if there is any one can help on that
Regards
Tamer
I'm using Sql Server 2012 Enterprise and have a database with over 400 tables. I am looking at one diagram which contains about 20 (or so) tables and their relationships. When I right-click on a relationship line I only see information about which tables are connected on that line but not on which fields. Can I get that information from the Diagram or can I only get that from the sys tables?
View 2 Replies View RelatedHi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.
for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]
I tried it by using information_scheme but I was unsuccesfull.
The result should be:
A
C
D
B
E
Thank you,
Tom.
how do i copy a db diagram from a 2005 db to 2000 db? thx
View 2 Replies View RelatedI need to create a few select queries based on an er diagram.
These are my Create Table statements and import statements:
Create Table Agent (Aid integer primary key, Pid integer, aName text);
Create Table Product (Pid integer primary key, pName text);
Create Table Supplier (Sid integer primary key, sName text);
Create table Supplies (Sid integer, Pid integer, price decimal(8,2));
.import agent.txt Agent
.import product.txt Product
.import supplier.txt Supplier
.import supplies.txt Supplies
I think I got all my create table statements are correct.
I need to Find the number of agents for each supplier that has at least one agent. The result should be tuples of the form (sid, sName, number of agents)
-Select Sid, sName, count(Aid) from Agent A join Supplier S on (S.Sid = A.Sid) group by S.Sid, S.sName, Aid;
But it gives me this error: no such column: A.Sid
Im thinking I might have a problem with my create table statement and/or primary key statements?
In last week my database was crashed and some how i managed to restore it back on SQL2K12 but after restoration all the relationships are removed and sql server is showing below message when i open diagram of the database.Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.how to get back all the relationships of the tables.
View 2 Replies View RelatedI 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?
View 3 Replies View RelatedI have created a database with several tables. I want to create a database diagram to show the relationshipbetween the tables. Below are the steps from the SQL Server 2005 documentation on how to create a database diagram. The problem is that when I right click on the Database Diagrams folder I am only given two options. They are:"Working with SQL Server diagrams" and "Refresh"There is no menu choice to create a new database diagram. Can anyone tell me what the problem here is?
To create a new database diagramIn Object Explorer, right-click the Database Diagrams folder or any diagram in that folder.
Choose New Database Diagram on the shortcut menu.
The Add Table dialog box appears.
Select the required tables in the Tables list and click Add.
The tables are displayed graphically in the new database diagram.
Hi,
I was looking for system databases diagram(poster). If any one has this could you please point me to the URL where i can find it!!
Thanks in advance.
Hello --Is there a way to configure 2000 so it will save a view layout after Ichange it in the diagram pane?Thanks for any help.Larry Mehl
View 2 Replies View RelatedHello,
I've seen threads concerning this problem. I have followed the prescribed steps to fix the problem. I've set the db owner to sa, I've set compatibility to 2005. Still I can't get the diagram to function.
Can anyone out there help me?
Thanks, Bill
I'm trying to access data from one table by matching ids pulled from another table. I can't quite get the syntax though if anybody could help I would appericate it.
Here is where I'm at
select * from table1 where variable 1 = (select distinct(variable 2) from table 2 where variable 3 = variable 4)
I am new to sqlserver.
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
Any ideas thnx.
Hi,
I have two statements which when I join by a union statement give the folowing:
2005 11 0.000000
2005 12 0.000000
2006 1 0.000000
2006 1 50813.058500
2006 10 0.000000
2006 11 0.000000
2006 12 0.000000
2006 12 63224.511250
2006 2 0.000000
2006 2 59164.234500
2006 3 0.000000
2006 4 0.000000
2006 5 0.000000
2006 6 0.000000
2006 6 82442.570750
2006 7 0.000000
2006 7 61809.497750
2006 8 0.000000
2006 9 0.000000
2007 1 0.000000
2007 2 0.000000
2007 3 0.000000
2007 4 0.000000
2007 5 0.000000
2007 6 0.000000
2007 7 0.000000
2007 8 0.000000
What I want is to merge the values
2006 1 0.000000
2006 1 50813.058500
into one row, the months with zero figures are required.
Thanks
Hi there, I need an easy solution for the following problem:
I want to join two tables. The first table contains an internal system customer ID and the official customer ID. The second table contains parts of the internal customer ID from table 1 plus the receivers of the invoices for each customer, If necessary. A parent child hierarchy If you'll call it like that. Now, how do I get a result table that puts out the customer ID plus the customer ID of the invoice receiver in a seperate row, like shown in the example result table?
Many thanks in advance!!!
Code Block
Table1Int_Cust_ID Cust_ID
1 4711
2 4712
3 4713
4 47145 4715
Table2
Int_Cust_ID Inv_Int_Cust_ID
2 1
3 5
4 1
ResultTable
Cust_ID Inv_Cust_ID
4711 NULL
4712 4711
4713 4715
4714 4711
4715 NULL
Hi
Sorry if i am posting in the wrong section but i can't find a forum for MS Access.
Does Access know of a equivelent to SQL Servers INFORMATION_SCHEMA tables?
I am writing a application that needs to extract information from a access database like, table names, relationships etc..
Please note that i need to do this via SQL.
Thanks Wayne
Are there any decent articles,troubleshooting guides,monitoring guides, books out there, which provide information on setting up SQL 2000 on Windows 2000 using clustering.
thanks
SS 2005 64Bit SP2 Hello Chaps Intermittent problem with the SQL Agent job history not getting written to the history table. Background:Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs. There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it. Stuff run to try to track the job:EXEC sp_help_jobactivity @job_name = 'MyJob' EXEC sp_help_jobhistory @job_name = 'MyJob' SELECT *FROM dbo.sysjobhistory WHERE job_id = 'MyJob GUID'The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null). sp_help_jobhistory had some historical records but nothing since mid last month. sysjobhistory correlated with sp_help_jobhistory as you would expect. Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June). We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it. We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will. Ta!
View 7 Replies View RelatedHello Everyone,
I need to know which system tables hold the pkg information when you import a pkg into SQL Server 2005.
Thanks
Shabnam
I’m trying to create a modified catalog tree out from two tables in sql
The catalog is created from two tables.
Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog
MainCatalogId, ItemGroupId, ItemId
Example data:
(sorry for the tabs that won't work)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826
The catalog structure is in table 2. Here are the connections between the different
ItemGroupId, ParentId
Example data:
ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL
To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:
ItemGroupIdItemIdLevel 2 level 3Level4
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667
I have tried many ways, but I’m not getting the result I want.
Hope this was understandable, and that someone has an idea if this is manageable
Thanks :)
Hi,
These are my tables
auth:
id, level, base
info:
aid, name
store:
aid, itemID
aid is the ID that comes from the auth table.
The final output that I plan to get is
Name Number of Items
where name comes from the info table, and number of items comes from the store table.
Query something like:
show the total number of items for all those users (in the fashion Name, Number of items)from the store table, for all those whose level=something and base=something in the auth table.
For example,
if there are 10 users whom match level=something and base=something in the auth table,
What I want displayed is:
------------------------
Name Number of Items
------------------------
user1 10
user2 15
user3 20
....
user10 30
-----------------------
which if broken up is:
select name from info where aid=X
select count(*) from store where aid=X ..
for all those whose base=something and level=something from auth.
Thanks,
Shashi
Dear list,I am trying to get the names of the tables and the column names from thecode below for a database but it is not working. When I run the querybelow the column titles are delivered but there is no data. I think thismight be a premissions issue. Has anyone run into this before?Thanks in advance.Use Test_db/* Provides Table Name, Column Name, Extened Description */Select a.name as tbl_name, b.name as column_name, d.name as data_type,d.length as length, d.xprec as prec, d.scale as scale, b.usertype,b.scale, c.valuefrom sysobjects as a inner join syscolumns as b on a.id=b.id inner joinsysproperties as c on b.colid=c.smallid and a.id=c.idinner join systypes as d on b.xtype=d.xtype
View 2 Replies View RelatedHi,I have SQL SERVER 2000 SP4 Enterprise , Windows 2003Enterprise onXeon 4 Processors (now with multi-threading CPU) and I havequestions whichseem weirds to me (used to have the same config withoutMulti-Threading) as following:1. SQL Server see 8 CPUs. Is this because ofmulti-threading CPUs ?2. when use SP_WHO2 ACTIVE ,2.1 Result has user SPID , which blockitself(it is UPDATE and SELECTstatements, most often)2.2 Result has user SPID , show morethan one result rows withthe same processe and command3. when use Properties of SQL Server , it has anerror 15407 in log.many thanks in advanceNiponW
View 2 Replies View RelatedTill recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
Hi,
I have downloaed microsoft's Best Practice Analyser for SQL Server 2000. I would like to recomend this tool in our organisation. Before that i want to confirm that is there any restrictions to use this tool or it is an free ware tool.
I need to generate a report of DTS package results, i. e. succeed, fail, error, etc. What tables is information of this type stored for SQL 2000?
winniemax
Hello:
I have an old database created in SQL Server 2000,
now I can open, access it in SQL Server 2005 Management Studio, but I can't create Data Diagrams.
Please advise on how to make it work....
Thank you
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View 5 Replies View RelatedHi
I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0.
getting an Error "Header information is either corrupted or missing."
My Specification is follow :
Windows XP with SP2
Visual Studio 2005 Enterprise edition
SQL Server 2000 (SP3a)
SQL Server CE 2.0
Application is Smart Device application (Pocket PC 2003) using C#.
Microsoft ActiveSync 4.5
IIS 5.1
I followed steps as per given in this URL :
http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
private void button1_Click(object sender, EventArgs e)
{
getSyncReady();
}
SqlCeReplication loSqlCeReplication;
private void getSyncReady()
{
try
{
String lsDBFile = "Northwind.sdf";
loSqlCeReplication = new SqlCeReplication();
loSqlCeReplication.InternetUrl = "http://192.168.0.5/Northwind/sscesa20.dll";
loSqlCeReplication.Publisher = "C99";
loSqlCeReplication.PublisherSecurityMode = SecurityType.NTAuthentication;
loSqlCeReplication.Publication = "NorthwindProducts";
loSqlCeReplication.PublisherDatabase = "Northwind";
loSqlCeReplication.Subscriber = Dns.GetHostName();
loSqlCeReplication.SubscriberConnectionString = "Data Source=" + lsDBFile;
if (!System.IO.File.Exists(lsDBFile))
{
MessageBox.Show("The application requires synchronization", "Replicator",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
loSqlCeReplication.AddSubscription(AddOption.CreateDatabase);
}
loSqlCeReplication.Synchronize();
}
catch (Exception foException)
{
throw foException;
}
}
I am not getting whats going wrong.
My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
Hello,
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g.
java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
Can anyone help me with this problem?
Simon
I have 2 Tables one called Reps and the other called STORES
I need to use a UNION statement but my Server is saying that the "UNION" is ERROR The Query Designer does not support the UNION SQL construct.
What command would I use to put these 2 tables together?
HELP!
Hi,
Is there any way to open .TPS(TopSpeed) tables via SQL Server 2000???