|
I am working on a database create script for SQL server 2005. It's getting to be pretty large (20 tables so far) and the gist of the script is to dropall the tables, create the tables, and then add in any default data. I have the last two pieces working correctly, and previously I was dropping each table individually. I found some code to help drop all tables and modified it.
DECLARE @Count int
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
WHILE @Count > 0
BEGIN
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
END
IF @Count = 0
BEGIN
SET @Message = '--- Tables dropped successfully ---'
END
ELSE
BEGIN
SET @Message = '--- Error dropping tables ---'
END
PRINT @Message
The problem is that because I have foreign keys, the scrip throws errors. I tried a try-catch but then the query hangs. Ideally, I want the while loop to ignore errors, or at least not display them so that any errors displayed are errors with the create script. I do not want the exec within the while to rollback, which is what I believe the try-catch is doing. Any ideas how to do this?
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
Try the following SQL statement:
First drop all the child tables and then go for parent tables:
USE NORTHWIND
<br />
SELECT <br />
OBJECT_NAME(c.parent_object_id) ParentTableName,<br />
OBJECT_NAME(c.referenced_object_id) ChildTableName<br />
FROM <br />
sys.foreign_key_columns c <br />
INNER JOIN <br />
sys.columns a ON a.object_id = c.referenced_object_id AND a.column_id = c.referenced_column_id <br />
Hope this helps!!
Harini
|
|
|
|
|
I used your solution, however, you must drop the parent tables first. The Child tables are the ones that error when you try to drop them. Here is the code in case anyone else wants to know how to do this. Just change {DatabaseName} to the name of the database you want to drop all the tables in.
USE {DatabaseName}
SET NOCOUNT ON
DECLARE @Message varchar (1000)
DECLARE @TableName varchar (100)
DECLARE @Count int
SELECT @Count = Count(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id
WHILE @Count > 0
BEGIN
DECLARE tableCursor cursor FOR
SELECT OBJECT_NAME(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC ('DROP TABLE ' + @TableName)
PRINT '--- Dropped Table ' + @TableName + ' ---'
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SELECT @Count = Count(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id
END
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
WHILE @Count > 0
BEGIN
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
END
IF @Count = 0
BEGIN
SET @Message = '--- Tables dropped successfully ---'
END
ELSE
BEGIN
SET @Message = '--- Error dropping tables ---'
END
PRINT @Message
GO
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
an overnight datasync has failed because of a missnig table in SQL Server, does anyone know if there is a log anywhere which might show where it went or who dropped it?
Thanks
|
|
|
|
|
What recovery model are you using?
|
|
|
|
|
Simple
not my decision I've only been here a week...
|
|
|
|
|
Is it SQL Server 2005?
If it is, you can try this[^].
|
|
|
|
|
that is EXACTLY what i was looking for...
THANKS!
|
|
|
|
|
Hi all,
I want to build an application in which I have an XML files which contains the connection string and credentials to connect to multiple databases. I want to connect to these databases and run a stored procedure on these database and close the connection. Can anyone guide how to proceed on this. I want to have front end which will fetch the data from the XML file for database connection, make the connection, run the stored procedure and Close the connection. Any Idea How can this be implemented.
Sankalp
|
|
|
|
|
What have you done so far?
What exactly is the problem? Can't you read an XML file or connect to a database or execute a stored procedure?
|
|
|
|
|
Are you using .Net? Siunce you didn't specify, I'm going to respond as if you are.
You should ask google about the connection strings section of the web.config. The web.config is an xml file that is already prepared with the necessary reading functionality, and the connection strings section is built to store connection strings. This is a built in feature of ASP.NET
Also ask him about ADO.Net. Ado.NET is a library for connecting to a database. There are many articles on CP about connecting to databases from .NET.
This really belongs in a programming board corresponding to the language you are going to write the application in.
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
Hey everybody!
I created a table-value function, so I will be able to perform a SELECT with parameters:
SELECT * FROM get_user (1) -- returns user with userID "1"
The problem is that I can't find a way to call this function from C++ using ADO (not ADO.Net).
I've been searching the net for 2 days now, and I'm pretty close to give up...
Thanks in advance!!!
|
|
|
|
|
Have you tried actually running "SELECT * FROM get_user(1) " from an ADO command? It should work fine - although most people tend to use stored procedures for this type of thing.
modified on Friday, April 25, 2008 6:02 AM
|
|
|
|
|
Are you getting an error of some sort? I can't say I can help with the C++ part, but your query probably won't even work in QueryAnalyzer from the looks of it. UDFs and TVFs must be schema-qualified, like this:
SELECT * FROM dbo.get_user(1)
So what you're missing is "dbo."
I hope that helps.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
HI all,
I am facing a problem that occurs with a specific database "X" only and not with the others in the SqlSever 2000.
Actually, while running an Insert/Update statement in the query analyzer for db X, it happens that the execution of the Insert/Update query is taking too much time the 1st time only. whereas for a Select query, I'm not facing this problem.
can anyone help?
Regards,
|
|
|
|
|
Without any further detail such as tables defs and code its hard to say. The first time a query is run it will always be a little slow as the optimiser has to work out the query plan. Have you looked at the query plan to see what is happening? If the update statment is auto generated often the unique primary key is being updated which slows things considerably.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Actually this is happening for only one db and for an insert query as well as for update , whereas it was normal before.And i'm running this query on a table which has no relations with other tables that's why it sounds weird for me! The time taken the 1st time to be executed is 30-60sec!
so if a user wants to insert a new record for this table, it will give him a timeout expired.
so to resolve this problem temporarily, I run an insert query in the query analyzer (wich will take a long time) and by then the user will able to insert or update normally(bcoz data pages are in memory buffer) and so the query will run faster.
But this is not a final issue, so any other suggestion?
Thx in advance
|
|
|
|
|
Maira K wrote: whereas it was normal before
Have any changes top the server/database been made? Thats about all I can think of, sorry.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
private void UpdateDB()
{
if (lblBilledItemTotal.Text == "")
{
lblBilledItemTotal.Text ="0";
}
double billedTotal = Convert.ToDoubl(lblBilledItemTotal.Text);
double billDisc = Convert.ToDouble(txtBillDiscCash.Text);
double netPayable = Convert.ToDoubl(lblNetPayableAmount.Text);
po.AddBillData(billedTotal, billDisc, netPayable, DateTime.Now.ToShortDateString());
ClearDS();
ds = po.GetLatestBillNo();
int billNo = Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray.GetValue(0));
foreach (DataGridViewRow dgR in dgItems.Rows)
{
if (dgR.Cells["dgColProdCode"].Value != null)
{
itemCode = dgR.Cells["dgColProdCode"].Value.ToString();
itemName = dgR.Cell["dgColItemDescription"].Value.ToString();
itemPrice = Convert.ToDouble(dgR.Cells["dgColPrice"].Value);
itemQty = Convert.ToInt32(dgR.Cells["dgColQuantity"].Value);
itemDiscCash = Convert.ToDouble(dgR.Cells["dgColItemDiscCash"].Value);
itemDiscPercent = Convert.ToDouble(dgR.Cells["dgColItemDiscPercent"].Value);
itemTotal = Convert.ToDouble(dgR.Cells["dgColTotal"].Value);
po.AddSalesData(billNo, itemCode, itemName, itemQty, itemDiscCash, itemDiscPercent, itemPrice, billedTotal, DateTime.Now);
}
}
IDictionaryEnumerator myEnum = ht_ItemQty.GetEnumerator();
int qty;
while (myEnum.MoveNext())
{
qty = Convert.ToInt32(po.GetProdQty(myEnum.Key.ToString()).Tables[0].Rows[0].ItemArray.GetValue(0));
qty = qty - Convert.ToInt32(ht_ItemQty[myEnum.Key]);
po.UpdateStock(myEnum.Key.ToString(), qty);
}
}
|
|
|
|
|
And your question is?
You need to provide more than just the source code. I'm guessing there is something wrong with it. So what is wrong with it? What should it do? What does it do? What error are you getting? Where is the error occurring?
|
|
|
|
|
hello everyone , iam sorry for being far from this forum from along time but it is out of my hands . ok !
now there is a problem or a difficulty for me to completely design this database , using this case study
"we have 50 materials available for students to study.
each term , every student has the ability to sign only 3 materials only of his choice from the above 50 materials , suppose that this student failed in one of them so the next term will only sign two with the material he failed in so the total is also 3 materials in each term , and there are some materials if he failed in them render him from taking some other materials which are related to the materials he failed in , explain further .
suppose that student failed in material B and material G is built in knowledge upon material B thus he can't choose material G except if he succeeded in B first .
so i am suppose to design this database for student result examination i don't know really how the database could be designed ???
any help please .
Human knowledge belongs to the world.
|
|
|
|
|
hi
i am trying to create linked server on sql server with mysql database that is replicated (slave) database. is it possible??
|
|
|
|
|
yes--please use google for general questions such as these
|
|
|
|
|
I am starting on a new project and see how powerful LINQ is. One thing I need to know before we begin is if there will be any problems using LINQ to run queries against a Sun (Oracle) Database server. Any input is welcome.
Thanks!
|
|
|
|
|
LINQ does not currently work directly with Oracle. However, you can use LINQ to Datasets if that is of any help.
|
|
|
|