|
the 'dbo.sysconstraints' is a View that all SQL server needs, and it is automatic created when you create
a SQL Server.
I used the program "ApexSQL Diff" to update my Database from a another database, and in this event the
'sysconstraints' and 'syssegments' view somehow disappeard.
Lucky for me I had a backup and could get the views from there.
( actually I had to make a view with some bogus contents like "Select * from xlbsUsers", save it and close it. Then
copy the content of my backuped view and open the 'sysconstraints' which I created and paste it in. Did the same with 'syssegments' )
Thomas
|
|
|
|
|
Hi,
I work in an organization that has a new SQL 2005 server
when a person trys to view a pivot table report he get an error message "server is too busy"' but when the SQL 2005 is installed on the computer the user has no problem viewing the reports.
every computer got win2k+SP4+office 2003+frame work 1.1
which package need to be installed in the computers that get to user to connect to sql olap 2005 threw excel or frontpage 2003 without installing the full package of SQL2005.
thx Alex
|
|
|
|
|
Hi there.
I am using ADO datasets on VB 2005.
For some reason, I manage to add a row to my table, but can't delete one.
: Table("Users").NewRow()
: Table("Users").Rows.Add(myDataRow)
: Me.UsersTableAdapter.Update(Me.myDataSet.Users)
OK
but not :
myDataSet.Tables("Users").Rows.Remove(searchedDataRow)
NOT OK
(searchedDataRow btw, is a Datarow that is a result of a Find() I did on the table based on two primary keys.)
looking at the data object in the windows form, I see that the row I was looking for is deleted, but upon calling the myTableAdapter.Update(), changes are not saved to the mdb file.
No errors are given during runtime.
What could be the problem ?
Thanx in advance
N
-- modified at 10:36 Thursday 4th May, 2006
|
|
|
|
|
I think row.remove just removes the row from the datatable. If you want to remove it from the underlying database you need to call row.delete which wont remove it from the datatable but marks it as deleted.
This is all to do with the datatables getchanges method where you can call getchanges passing the deleted parameter to get a list of all the rows deleted (marked as deleted and will be deleted when you call the update method on the dataadapter). Removed rows are completely removed and therefore cannot be deleted when you call the dataadapters update method.
Jon
|
|
|
|
|
Thanx for the reply Jon !
I tried looking for myDataSet.Tables("Users").Rows.delete(searchedRow)
But a compilation error states that delete is :
not a member of the "System.Data.DataRowCCollection"
Btw, When I call the Clear() function, it also clears out the dataset that resides in the memory (I see it empty in the window), but the mdb is untouched still.
|
|
|
|
|
nirishere wrote: myDataSet.Tables("Users").Rows.delete(searchedRow)
The correct call would be myDataSet.Tables("Users").Rows(searchedRow).Delete
or if searchedRow as a Row object you can simply call searchedRow.Delete.
Jon
|
|
|
|
|
thank you very much !
I could erase one line as I typed searchedRow.Delete, (I am not a VB programmer, but I would guess that searchedRow actually points to a data record inside the table, calling a function of it to delete itself or mark itself for deletion (right ?))
But I could not execute table.rows(searchedRow).Delete
Another problem I had was to delete a part of a table that had Column A and Column B matching some specific values only that this table had no primary keys...
To make a long story short, I went into the XSD file and defined a stored procedure or whatever it is called, so I could call it from my VB app. It worked great, I wanted you to know.
Just to make things easier, just how could I get into all this in the first place just by reading a book, I spent days on finding that info, and I thought maybe there is a better way to learn it all, comments ?
Thanx alot
N
|
|
|
|
|
I have a table call it table1 with say 2 columns az and azz
az azz
1,23,.,56 2,..,3,76
I have to insert this data into a new table, table2 with 4 columns, a1,a2,a3,a4 such that
a1 a2 a3 a4
1 23 . 56
2 .. 3 76
but after running the stored procedure wich takes the columns in table az union azz into table2, I get 4 rows instead of the 2 rows as shown above. What am I doing wrong.
Help!
phokojoe
|
|
|
|
|
Union does not work for what you are trying to do. You will need join the tables. Union statement allows the results (rows) of 2 queries to be 'Unioned' together to create one query.
Your example above I don't understand how the records need to be entered into table4
Table 1
az | azz
-----------
1 | 5
4 | 9
8 | 11
15 | 22
What should table 2 look like after this query?
Mike Lasseter
|
|
|
|
|
To clarify
Table1
az azz
1 23
. 56
2 ..
3 76 You want the output to look like this:
Table2
a1 a2 a3 a4
1 23 . 56
2 .. 3 76
There is no obvious logical relationship between table1 and table2 other than rows 1 and 2 from table 1 become row 1 of table2 and rows 3 and 4 from table1 become row 2 of table2. Databases are set based (there is no intrinsic order of the rows in the database - obviously there appears to an order due to the implementation, but that is, strictly speaking, an illusion) so there needs to be some element in the source table that can be used to describe order.
phokojoe wrote: after running the stored procedure wich takes the columns in table az union azz into table2, I get 4 rows instead of the 2 rows as shown above. What am I doing wrong.
Without seeing your code I cannot tell you what you are doing wrong. However, I don't think I'd use a union to pivot the values in a table.
Please show us your code and describe the relationship between the first and second table.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi ,
I have a problem with getting content of a word, excel ,pdf document to be displayed as a report. The path of the file is stored in the database (sql server 2000 in my case) . I want the to get the path and show the contents of the file as a report ...this because i dont want to allow users to modify the contents of the file...can this be done with crystal reports or any other tool?
Please help me with any information about this...
Thanks in anticipation..
|
|
|
|
|
I have a stored procedure which extracts data from one table and insert it into another table taking the union.
For example:
Table1 has 2 columns:
az azz
1,23,4,56 2,34,5,67
and table2 has 4 columns a1,a2,a3,a4
after running the procedure table2 will have
a1 a2 a3 a4
1 23 4 56
2 34 5 67
that is I have taken the union of az and azz stopping at the ",".
As in above only 2 rows will be inserted in table2, but I get 4 rows, 2 of each record.
How is this possible?
Help!
phokojoe
|
|
|
|
|
Looking at your other post[^] I get the distinct impression you are hiding what you are really trying to achieve. You must help us to help you (to quote Dr. Cox from Scrubs). That means you must explain what you are actually trying to achieve rather than provide some absract concept that is missing some key elements.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Table1 has 3 columns and data is separetaed by "," like below.
a | az | azz
20 | 1,23,4,56,..,9,10 | 9,..,1,32,54,7,88
Table2 has 8 columns;a a1 a2 a3 a4 a5 a6 a7
----------------------------------------------
The code I write will look at the contents Table1 of 'az' and then read from left to right and when it finds the "," it takes the value to column a1 of Table2 and then continues to read and when it finds the second commna it takes the value to column a2 of Table2 and so on until it has reached "10" which is the last after the "," and puts it in column a7 of Table2 and then UNION it with the contents of column 'azz'.
Table1 has 3 columns but 1 row and finally Table2 will have 8 columns and 2 rows. column 'a' is common to all the tables, so it will look like this after running the procedure.
Table2:
a a1 a2 a3 a4 a5 a6 a7
20 1 23 4 56 .. 9 10
20 9 .. 1 32 54 7 88
NB. column is acts the id
It works like a traspose of Table1 to Table2.
The problem is, it does not give me 2 rows a s I expected, but it gives 4 rows especially when some parts of 'az' or 'azz' are not filled, that is I put a dot where there is no entry.
I used substring and patindex as shown below:
INSERT table2(a,a1,a2,a3,a4,a5,a6,a7)
SELECT a AS a,SUBSTRING(az,1,PATINDEX('%,%',az)- 1) AS a1,
SUBSTRING(az,3,PATINDEX('%,%',az)+ 0) AS a2,
SUBSTRING(az,6,PATINDEX('%,%',az)- 1) AS a3,
SUBSTRING(az,8,PATINDEX('%,%',az)- 0) AS a4,
SUBSTRING(az,11,PATINDEX('%,%',az)- 0) AS a5,
SUBSTRING(az,14,PATINDEX('%,%',az)- 1) AS a6,
SUBSTRING(az,16,PATINDEX('%,%',az)- 0) AS a7
FROM table1
UNION
SELECT a AS a, SUBSTRING(aaz,1,PATINDEX('%,%',aaz)- 1) AS a1,
SUBSTRING(aaz,3,PATINDEX('%,%',aaz)+ 0) AS a2,
SUBSTRING(aaz,6,PATINDEX('%,%',aaz)- 1) AS a3,
SUBSTRING(aaz,8,PATINDEX('%,%',aaz)- 0) AS a4,
SUBSTRING(aaz,11,PATINDEX('%,%',aaz)- 0) AS a5,
SUBSTRING(aaz,14,PATINDEX('%,%',aaz)- 1) AS a6,
SUBSTRING(aaz,16,PATINDEX('%,%',aaz)- 0) AS a7
FROM table1
I hope it is clear now.
why does it repeats the rows?
thankx 4 the help.
phokojoe
|
|
|
|
|
Can anybody help me with that!
phokojoe
|
|
|
|
|
Hi  all,
I am using delphi7 and ado(odbc) controls its developed in windows .
now if i want to use in linux how can i connect my database.
Gaddm kishore kumar
|
|
|
|
|
Delphi 7 includes Kylix which you would normally use to create either cross-platform applications or linux applications. You should be able to use "regular" database components including dbExpress but check them individually with the help file. However, also check that the components you intend to use are kylix enabled BUT to compile the application transfer the application to the linux machine. And use the help file, it is your friend.
modified 1-Aug-19 21:02pm.
|
|
|
|
|
Imagine a matrix,
<br />
S M L XL<br />
color1 10 20 30 0<br />
color2 4 5 45 50<br />
color3 1 0 4 5<br />
You a sample matrix what i work on. columns are quantity of sizes (some of them may equals to 0 ) . Rows are colors. rows count and columns count are variable. How to save them to data base.
rows are in a table, size are saved on other table. But i couldnt solve how to save values on table. what do you offer to me ?
|
|
|
|
|
A table containing three columns (to which you must add a primary key - unless size and colour are the primary key)
Size Colour Quantity
S 1 10
S 2 4
S 3 1
M 1 20
M 2 5
M 3 0
....
Your matrix is a pivoted varient of what I have shown above.
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hai,
The following is my queries.
1: Is it possible to have check constraint with a local variable?
2: Given the following scenario:
I have three columns in a table ,say ID int,name varchar, address varchar, and I added 10 records into that table keeping ID colums NULL.Later I want to add values to ID columns 1 thru 10 but with a single update query.(It doesn't means you are bound to use only 1 satement in the batch, no loop permitted )
Please show me a right way.Thanks very much.
Thank You,
Rahul.P.Menon.
|
|
|
|
|
Hi,
I'm creating a Win Form that uses a MS Access Database.
I'm new to database programming and wanted to know what advantages, besides referential integrity, is there to creating a Foreign Key? I'm wondering if creating a Foreign Key helps me with SQL statements in my code? Or would creating Foreign Keys not change SQL statements like this:
AND Products.CustomerID=Customers.CustomerID
thanks,
Ron
|
|
|
|
|
I can't think of another reason besides referential integrity. Foreign keys are constraints and do not change SQL statements. They just guarantee that Products.CustomerID will match the CustomerID in the Customers table. In other words, you shouldn't be able to delete Customer 1 from the Customers table if Customer 1 exists in the Products table.
|
|
|
|
|
Thanks Gerald,
I thought that, but wanted to ask anyway.
Ron
|
|
|
|
|
I'm trying to optimize some sql code, and I figure there is an easier way to do this, but since I'm still relatively new to SQL, I'd like some help if possible.
I have a table that has 4 relevant values, the table's id key (TKey, int), a parent key (PKey, int), a Type (Type, int), and a Locked value(Locked, bool).
I'm trying to return the locked value of the maximum local key of a certain set of types.
What I've come up with so far:
SELECT
Locked
TKey
FROM
Table
WHERE
TKey = (Select Max(TKey) from Table) where (Type = 0 or Type = 1) AND PKey = @Pkey)
Is there a better way to do this, or am I doing it correctly? It just seems that I shouldn't be querying a table within a query to that table. Maybe something with 'Top 1' or 'Group By'?
|
|
|
|
|
Drew McGhie wrote: Is there a better way to do this, or am I doing it correctly?
Other than the extra closing bracket it looks okay to me. If you need to optimising it then look at the query plan in Query Analyzer because it never ceases to amaze me what SQL Server thinks is optimal in a given situation - I've also had one change somewhere in the database cause an existing query to start running a lot slower than before. I feel that database optimisation is an on-going process rather than something you do once when you are doing your initial development.
Anyway, an alternative to your code:
SELECT TOP 1 Locked, TKey
FROM Table
WHERE Type IN (0,1)
AND PKey = @Pkey
ORDER BY TKey DESC
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
