|
I need to get the relationships that exist between the tables in a database. The database structure is unknown to me , i.e at runtime the program connects to any database be it Access or SqlServer. From that database i get the info. related to the tables such as the columns that exist in it, their datatypes,etc but not the table relationship to other tables. For SQL databse i have used this approach - I have used SQLDMO to get the table names. Then generating a select statement e.g: "SELECT * FROM Tablename" and then using DataAdapter.FillSchema method i get the structure of the table. But i also need the realtionships that exist between the tables. Could any one help me??! (Using C#)
|
|
|
|
|
Here's something that will make you scratch your head!
I have a table that has a UniqueIdentifier as its primary key, but also have an autoincrement column (InvoiceNumber) that is not part of the primary key.
This table contains about 3000 records. But for an unknown reason (UFOs???), the last 1500 rows or so have DUPLICATE autoincrement value! The incrementation is done automatically by SQL Server (MSDE).
Here's an example of the values in the InvoiceNumber column:
1
2
3
4
4
5
5
6
6
Any idea what it could be? How can I fix/prevent this?
Thank you very much for your help!
Carl
|
|
|
|
|
Either you have IDENTITY_INSERT on and a client is inserting bad values, or someone's used DBCC CHECKIDENT to reset the identity seed.
To fix, build a UNIQUE index on the column. Any client trying to insert a non-unique value will get an error. Use DBCC CHECKIDENT to check and fix the identity seed value. Ensure IDENTITY_INSERT is turned off.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Looks like someone's been playing with the database structure 
There WAS an index there, but the index has been removed and that person probably changed the identity seed.
I thought that an identity column was always unique... your information helped me find out where the problem was.
Thanks!
|
|
|
|
|
Does any body have idea where i call below Sql syntax in Qurey Builder or Enterprise Manager?
And How to Call In C#.Net
=======================
EXEC GenerateScript @server = 'Server_Name',
@uname = 'User_Name',
@pwd = 'Password',
@dbname = 'Database_Name',
@filename = 'c:\File_Name.sql'
==============================
Muhammad Waqas Butt
|
|
|
|
|
EXEC GenerateScript 'Server_Name',
'User_Name',
'Password',
'Database_Name',
'c:\File_Name.sql'
will work in query builder
SqlCommand sqlCommand = new SqlCommand("GenerateScript");
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@uname ", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, "User_Name"));
etc.
try
{
sqlCommand.Connection = connection;
SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
dataAdapter.Fill(dataSet);
return dataSet;
}
finally
{
connection.Close();
}
Should be a fair representation of how to run a stored proc in C#. Assuming you have a connection object.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
This could be one of those questions that is really silly which is why i haven't found an answer to it.
If i have a DataReader with a 10,000 odd recordset should i be able to insert, delete and update the same table via another connection.
i.e. i have some code that is syncronising a couple of tables and it sorts two datareaders, a master and a slave, and the goes through both either skipping, adding, amending or deleting rows in the slave table. It seems to be working but i'm having a problem with an update that is using 5 different keys, i.e.
update x ... where k1=1 and k2=2 and k3=3 and k4=4 and k5=5
it seems to be hanging the database sometimes, not all the time.
so im trying to isolate what could and couldn't be happening and one thought is that if im modifying the table as im going along that could be screwing up the datareader...?
So when a datareader performs it's query, and you start looping through what data are you actually looping through? does .net create pages of data and repeatedly queries the database, or is there something else happening?
thanks paul
|
|
|
|
|
A DataReader works in 'firehose' mode: SQL Server uses TCP's flow control mechanisms to control the flow of data to the client. I believe the SQL Server ADO.NET Provider uses a packet size option (which I think defaults to 4KB) to decide when to listen for more data.
SQL Server holds the locks associated with the command until all the data has been received by the client - which in effect is when DataReader.Read returns false. The default transaction isolation level is READ COMMITTED, which causes Shared locks to be held. When a Shared lock is held, an operation requiring an Exclusive lock (such as an update) waits until all Shared locks on that data are released. Your second connection may end up waiting for your first, if a record that is to be updated has already been read by the first connection.
I would recommend replacing your DataReader with a DataTable and using a SqlDataAdapter to fill that table. The DataTable is a cached copy of the source data - DataAdapter.Fill does not return until all data has been retrieved. You can then reuse your first connection to perform the updates.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank Mike for the info. That locking is something i was seeing. Im not sure a DataTable is a good idea for me with the amounts of information i have to deal with. I'll probably do the whole thing in chunks bit by bit.
thanks
paul
|
|
|
|
|
Hi,
I have a question about a sql query I need to do. I have a table with 3 fields, userID, entryDate and value. My query will have a date parameter called targetDate. What I was hoping to do was retrieve a single row for each user where the entryDate is the most recent entry for that user prior to the targetDate parameter. If there are no entries prior the the targetDate for the user, no rows will be returned for that user, if they have multiple entries with entryDate < targetDate, obviously only the most recent will be returned. If anyone was able to help me with the SQL for this, that'd be great.
Thanks,
Matt
|
|
|
|
|
You have do do this with a two pronged approach. Use can use max() and group by clause to get the newest date (only) for each customer, then you can join back on the table to get the rest of the data:-
select yourTable.* from (
select userID, max(entryDate) as entryDate
from yourTable
group by userid
) k
inner join yourTable
on yourTable.userId = k.userId and yourTable.entryDate = k.entryDate
using System.Beer;
|
|
|
|
|
Thanks Jon!
That works perfectly 
|
|
|
|
|
Thanks Jon,
That works perfectly 
|
|
|
|
|
What’s the proper way to do object/relation mapping now days, for various data sources?
Is there any nice solutions here at code project, or should I wait for Microsoft’s ObjectSpaces (if it will ever be released) to solve my issues?
|
|
|
|
|
Hi,
Is it possible to have report with dynamic (varying) columns?
Like in one scenario it may return 3 columns (col1, clo2, col3)
In another scenario it may return 2 columns (col2, col3)
Thanks in advance
|
|
|
|
|
"auto update statistics" -- is there any good reason to turn it off?
|
|
|
|
|
i really don't understand you question as i don't know (and i believe everyone here don't either) what "auto update statistics" is ???
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
|
The statistics are used by SQL Server's Query Optimiser to decide whether an index is suitable for use in a query. The statistics show how selective the index is - that is, how many rows will be returned for a particular set of key data. You can view the statistics with DBCC SHOW_STATISTICS.
For the statistics to be useful, they must be reasonably up-to-date. If they're out of date, or missing, the query optimiser may pick a poor execution plan. If 'auto update statistics' is on, SQL Server automatically updates the statistics after a certain number of changes (INSERT, UPDATE, DELETE) have occurred - a stored query plan may then be recompiled.
Statistics aren't just useful for deciding whether to use an index. They can also be used to decide which table should be scanned in the inner loop and which in the outer loop of a nested-loops join operation. For that purpose you can create statistics on a collection of columns. If the 'auto create statistics' option is on, SQL Server will automatically create statistics for columns used in queries that aren't indexed.
On the whole, I'd recommend leaving both options turned on. The cost of building the statistics is typically small.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I think I've heard people saying for OLAP database ... you should turn this off? Even then, I'm not sure why because application reads off OLAP cube, not staging database. Does it matter? OLAP Vs OLTP? I don't see ...
Norman Fung (c)
|
|
|
|
|
I thought it will work for a series of data in one column but it did not. Actually, if i have 1,2,3 in one column and would like to put 1 in column A, 2 in column B and 3 in coulumn C of another table.
I had something like:
insert test (a)
select substring(aa,1,patindex('%,%',aa)-1)as a from table1
insert test (b)
select substring(aa,3,patindex('%,%',aa)-1)as b from table1
insert test (c)
select substring(aa,5,patindex('%,%',aa)-1)as c from table1
but what happens is that it does not put data in 1 row. instead it gives me 3 rows like this:
1 NULL NULL
null 2 NULL
NULL NULL 3
How do I put this in one row
phokojoe
|
|
|
|
|
Each INSERT statement creates the number of rows selected - 1 in the case of an INSERT...VALUES statement, the number of rows returned by the SELECT in the case of INSERT...SELECT.
To put all the data in a single row, you simply need to select all the data you need for that row:
INSERT test (a, b, c)
SELECT
SUBSTRING(aa, 1, PATINDEX('%,%',aa)-1 ),
SUBSTRING(aa, 3, PATINDEX('%,%',aa)-1 ),
SUBSTRING(aa, 5, PATINDEX('%,%',aa)-1 )
FROM table1
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hello friends
Can anybody knows,it is possible to take sql server2000 database backup on another machine's disc.
I normally take backup on the disc of same machine where my database server is but if i want to take backup to another machine disc then that will possible???
T.I.A
Shashank
|
|
|
|
|
This is not possible in SQL Server 2000. I have a similar situation.
What I do is back the database in the current server programatically using SQL DMO and then FTP it. I know this information is not of much use. Still thought I should share it.
Thanks
Live Life King Size
Alomgir Miah
|
|
|
|
|
Hai
I am using SQl Server. in Which i Create a table Where Some of the Fields and set with default values.
+ When i try to generate SQL script using Enterprise manager(Database>All Task>>Generate SQL Script) . i didnt get Sql Script for default value and Some Constrants. Is there any way to generate the Script.
+ In one of my Table i Preset with some data . Is there any way to generate Insert SQL Script.
if these things are not done by Enterprise manage. please suggest me some third party utility.
Thanks & Regards
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
