|
Hi
How to get all the tables with their schema name in sql server 2005.
I am using adventures database. I know how to display all the tables in the database.
select * from information_schema.tables where table_type='base table'
I want to append the owner name (dbo.sales, sales.product )
can any one help me to solve my problem please?
kesavan
|
|
|
|
|
Hi
Use :
select Table_Schema+'.'+Table_Name as fullname from information_schema.tables where table_type='base table'
|
|
|
|
|
Hello,
We're developing this small helpdesk application in we run into this problem:
I have a table of tickets and table of user, each ticket has in the database the field user_id where nulls are allowed. I'm using typed dataset generetad using the Visual Studio, so there is also the relation FK_users_tickets generated.So in my myDataSet.tickets table a method addTicketsRow() was created with several arguments, one of them being of type myDataSet.usersRow. I know that's the parent usersRow, so it's ok, the problem is when i want to add the new row with the user_id field null.
How to provide usersRow parameter which is gona be null? Well ofcourse i tried providing the null parameter but im just getting nullreference exception. I also tried to find some constructor or method to create empty usersRow, but i found just the method myDataSet.users.newUsersRow(), but this one will just create another new usersRow and will set it's id, because there is autoincrement on the user_id field and obviously i don't want to do that.
Looks litle confused, but hope the explanation is understoodable.
Actually I'm almost sure it should work with the null parameter, because i had a piece of code like this before and that time it worked, but now i've already spend two days on solving and googling and still didn't come up with answear...so any help would be realy appriciated.
thnx Honga
|
|
|
|
|
You have to ask yourself, "what is the reason for having the ForeignKeyConstraint"? The purpose of a foreign key constraint is to enforce the data integrity of the relationship. Depending on what it is you're trying to accomplish here's some suggestions:
1) Set the EnforceConstraints property of the Dataset to false. However, this will affect all your constraints, not just the specific foreign key constraint. So if you have other constraints you need to be aware of what the affect will be of turning them off.
2) Use a DataRelation instead. The trick is that it will add a foreign key constraint which you will have to remove. This will work if the purpose of your constraint is simply to indicate a relation between the tables and not to enforce data integrity. Then you can use methods like DataRow.GetChildRows().
3) If you don't want to enforce data entegrity and you aren't using methods like DataRow.GetChildRows() then you may not need the constraint at all
|
|
|
|
|
Thanks for answer,
the DataSet is generated from the database using the VisualStudio so there is a DataRelation between those 2 tables already. So I can simply erase this relation and I guess it is gonna work fine after that. The problem that I don't understand,is however, little more general.
The generated addRow method parameters looks something like this:
ticketsTable.addTicketsRow(DateTime ticket_date,string ticket_title,....,MyDataSet.usersRow user_id)
and I know that in the database running on SQL Server, the field user_id in table tickets allows Nulls(and also in the generated DataSet). So when Visual Studio created the DataSet for me with this wonderful method, what parameter should I supply to this method for the usersRow, so the field in the database would be Null? When I supply null, which was my first idea, I just get "object reference not set to instance..." exception.
Honga
|
|
|
|
|
Thanks for your answer,
The DataSet is generated from Visual Studion from SQL Server so there already is a DataRelation between those 2 tables "tickets" and "users". So I guess that when I delete this Relation it is gonna work fine. The problem I don't understand is however more general.
My addRow method on table "tickets" looks something like this:
myDataSet.ticketsTable.addTicketsRow(string ticket_title,DateTime ticket_created,...,MyDataSet.usersRow parentusersRowbyFK_ticket_users)
In the DataSet the FK_tickets_users is set to "Relation only". In the database there is field user_id of type int and this field I'm setting by the MyDataSet.usersRow parentusersRowbyFK_ticket_users parameter.
This field user_id allows Nulls. So my question is what parameter should I provide to the addTicketsRow method so the field user_id in the database would be Null?
As I said before, null was my first try, but than I'm getting NullReferenceObjectException.
|
|
|
|
|
What I'd recommend is creating a new class which inherits from the generated dataset so you can add an overloaded method which does not contain the parameters which are causing you null reference exceptions. Your inherited class will have direct access to the table object itself which will allow you direct access to the table object so you can add a row w/ null values.
|
|
|
|
|
Thank you,
we solved it yesterday similary to the way that you suggested. We just didn't create new class inheriting from DataSet but we added the new method without the problem parameter to our class which is providing some other data operations.
Honga
|
|
|
|
|
Hi all
is there any perfomance issues when using table alias's in a select statement, as in the below example?
SELECT p.PeriodNo, omf.MemberPK, omf.FieldNo, omf.FieldValue, omf.FieldEffectiveDate
FROM Payrolls p
INNER JOIN Members m
ON p.PayrollPK = m.PayrollPK
INNER JOIN OriginalMemberFields omf
ON m.MemberPK = omf.MemberPK
WHERE p.PayrollPK = @i_intPayrollPK
Thanks in advance for your help.
Matthew Rowe
|
|
|
|
|
Unlikely. It may cause a little extra work for the parser but in the grand scheme of things it would be negligible.
|
|
|
|
|
The only time you would see a hit is in the initial compilation of the query (very, very negligible). The "compiler" uses this information to work out what the execution plan is going to be and the parser translates the references back into full table descriptions.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hi
how i can convert RMS database to XML File or SQL Server or any type of database ?
and ho i can connect my application witt RMS Files ?
|
|
|
|
|
Hi There
You can get an ODBC Driver for RMS Files. Easysoft is the one that I used to use(www.easysoft.com). You need to specify the File layouts to it via parameter files which can take some time to prepare. The package can take a cobol Program or Powehouse Data Dictionary and generate the layouts.
Once completed, you treat the easysoft odbc connector as a standard OLEDB Source.
Paul
|
|
|
|
|
Hi all,
I have a sql 2000 table like this:
ID col1 col2
7777 itemx 12/02/07 00:00:10
7777 itemy 12/02/07 10:00:00
7777 itemz 12/02/07 12:10:60
8888 itemA 12/02/07 01:01:00
888 itemB 12/02/07 02:00:00
.......................................................
I like to combine all rows with the same ID together like the followings:
7777 itemx itemy itemz 12/02/07
888 itemA itemB.........................
The question has 3 parts:
1) what is the best way to do this? using trigger/stored procedure or just a vb sub with somesort of datarepeater, datalist controls
2) can you show me some examples of each way if any?
3) Can crystal report do something like this?
I am open to create another table or just plain writting them out on page.
Thanks
|
|
|
|
|
This is a technique to return items names a comma-separated values for each ID
create function GetItems(@id int)
returns varchar(1000)
As
Begin
declare @items varchar(1000)
Set @items=''
Select @items=@items+','+col1 from Items where ID=@ID
return @items
End
Go
this UDF returns a comma separated values for a given ID
then select the values with distinct IDs:
Select distinct ID,dbo.GetItems(ID) as items from items
results should look like:
777 ,itemx,itemy,itemz
888 ,itemx,itremy
you can display these results the way you want in your code.
|
|
|
|
|
Thanks. It works fine.
|
|
|
|
|
One more question> How do I incorporate that with datalist or datarepeater meaning how can make them as seperate columns so I can have appropriate headers for them. Thanks
|
|
|
|
|
I will be describing an electrical system. Option(1) is proposed by someone else, and I proposed option(2). I am using the term 'node' as a point on a electrical bus and 'devices' repersents switches or breakers...
Two option:
(1)(a)1:M<---(b)M:M--->(c)M:1
(2)(a)1:M<===(b)1:M<---(c)1:M
(1)
Table (a)&(c) have a many to many relationship. Table (b) is used to make this relationship.
This is to say that many nodes can connect to many devices. This does not show how different devices might relate to each other nor does it give connectivity. To set up a query to get the rating of a bus can be done with some work.
(2)
Table (a)&(b) have a double one to many and table, meaning the PK of (a) is a FK in (b) twice.
Table (b)&(c) have a one to many.
The (a)&(b) relationship creates a 'section'(Point A to Point B). Then from that a device in table (c) sits on a specific 'section' in which that 'section' is tied to only two nodes
(or records in table (a)). This does give connectivity to the devices and getting the rating of a bus would be much easier.
Option(2) is my opion if one of yall is able to understand what I am presenting. Can I get some feed back on both options. Don't worry I do not get offened by constructive replies. I see how option(1) works but I believe option(2) is more effectent.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
The second design will perform better and is easier to get a report with the relations you need.
But I think it's not very flexible, what if a device is connected to more than 2 nodes? Or is this an impossible case?
|
|
|
|
|
hspc wrote: what if a device is connected to more than 2 nodes
We have decided that a device will connect to one or two nodes but not more than two.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
jason_lakewhitney wrote: We have decided that a device will connect to one or two nodes but not more than two
Does that reflect reality?
I worked on a project a couple of years ago where there was an assumption that we only needed three connections between certain things. Then the client came along and asked 10 connections which broke our design and we (or rather I) spent 3 months re-writing the code due to a bad assumption being made.
|
|
|
|
|
Okay - I'm not familiar with nodes and devices in an electrical system. However option one looks like it describes a true many-to-many relationship. i.e. one node connects to many devices, one device connects to many nodes, therefore many devices connect to many nodes.
I just want to clarify what you are describing
(1) Node Table:
PK: NodeId
(3) Device Table:
PK: DeviceId
(2) Intermediate Table:
PK: NodeId, DeviceId
jason_lakewhitney wrote: This does not show how different devices might relate to each other nor does it give connectivity. To set up a query to get the rating of a bus can be done with some work.
How devices relate to each other is a different relationship. You can set up that relationship too.
If it is heirarchical then the Device table will contain a ParentDeviceId column as a foreign key that links to another row. If you only ever need to get the immediate parent or child this is quite efficient. If you need to get all children then you need to set up a separate table to denormalise the relationship. This adds duplicate information in a more efficient format for the queries you will run.
If Devices relate in a many-to-many way, then you can set up another intermediate table that links the two devices together.
Does this help?
|
|
|
|
|
Yes and no, it can be a little diffcult to explain. I work in the planning department along side the P.E.s they have describe the theoretical, physical and bussiness models of this system. This is the part way the helpdesk tech. is not getting it.
I should mention that a node is just a 'point in space' not physical but more of a location.
Theoretical Model - many devices are connected to many node.
Physical Model - many devices are connected to the physical bus where nodes are located.
Bussiness Model - many device are connected to one physical bus 'section' where nodes are the end points of the 'section'.
I have ranted many times about my situation. I, a developer, was hired to do a job but a helpdesk tech. has somehow worked his way to be in charge of the database design. His problem, I believe, is he is concetrated only on the theoretical model and he can't (or won't) look further into the data models.
Well, thanks for everyones input.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
I agree with Colin, a real world DB setup would be the Many to Many relationship with an Associative Table between the two. Even if you never plan on having to utilize the many connections to many nodes, at least it would be setup if anything ever arose like that.
Cleako
|
|
|
|
|
The query I'm working on a query that selects all introduced leads. I have, of course, managed this part without a problem but the query needs to select only those that were introduced within the past month.
I have found the GETDATE keyword but how can i use this to find introduced leads. The leads have a date time stamp when they were created.
?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
