|
Hi All,
I want to write a script/tool which would update the schema of the existing database (in SQL Server 2005)
i.e Add/drop a table, column, constraint etc. on a ongoing basis.
The input to my tool would be the latest_schema script which creates the database with all the latest tables, columns, constraints etc.
So the tool that I am going to write should perform a schema-diff with my db Vs the script provided and add/drop the tables, cols, constraints on my database.
I have a couple of solutions in my mind but feel that they are not very efficient.
1. Create a new database running the provided script, write a stored procedure to loop thro all the tables to figure out the diff between the existing db and the current db and update the current db: Problem here is my current db has data and lots of constraints, which might pose a problem.
2. Use some third party tools to generate the sync script and execute it against my current db but I am not sure about the free tools available out there.
Can somebody please provide me some guidelines/tips on how to go ahead about this.
Thanks in advance.
PJ
|
|
|
|
|
hi,
where dataset tables stored ?
|
|
|
|
|
wrote: where dataset tables stored ?
Do you mean how do I get to the DataTables in a dataset? If so, DataSet exposes a Tables collection:
DataSet ds = GetDatasetFromDb();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow row in ds.Tables[0])
{
}
}
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
I would like to use a C# windows application to determine the layout of a MS Access database. What I am trying to do is to specify an Access database by using an OpenFileDialog and then I want to retrieve a list of all the tables in the database. This will then populate a ComboBox. When a table name is selected in the ComboBox it must go and retrieve the data in the database and display it in a ListView. Displaying it is not a problem, but at the moment I hardcode the table name and when I want to look at another database with a different structure, I need to first find out what the table names are before I can retrieve the data.
Is there a way to dynamically get the database table names?
Thanks
Kobus
|
|
|
|
|
You could try something like
SELECT MSysObjects.Name<br />
FROM MSysObjects<br />
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1));<br />
Hope this helps out.
"That's no moon, it's a space station." - Obi-wan Kenobi
|
|
|
|
|
I use:
<br />
foreach <br />
( <br />
System.Data.DataRow dr <br />
in <br />
((System.Data.OleDb.OleDbConnection) this.dbc ).GetOleDbSchemaTable<br />
(<br />
System.Data.OleDb.OleDbSchemaGuid.Tables<br />
,<br />
new object[] { null , null , null , "TABLE" }<br />
).Rows <br />
)<br />
|
|
|
|
|
Hi,
as the title says it, I've a question about deploying such assemblies to the MSSQL Server (2005). I know that you can deploy it in Visual Studio, but I wondered if there isn't any other way to deploy them WITHOUT Visual Studio ? That would be very neat. Theoretically there must be some possibilities to do this. Maybe someone has experience with it and can give me some hints
|
|
|
|
|
You would normally use the command:
CREATE ASSEMBLY MyAssembly FROM 'c:\dev\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
Obviously, this would need to be run using sqlcmd or SQL Server Management Studio.
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,
thank you that was helpful. But maybe I'm to stupid but it doesn't work correct for me. If I execute this e.g. in sqlcmd then I see the deployed assembly, but I don't see the Stored Procedures in my database (as I said I just see the deployed assembly).
I tried this with a new clean database, but on my sql server I already have another database with the same assembly (and stored procedures). Could this be the reason that I don't see the Stored Procs in my first mentioned database ? (I think I heard time ago something like, that all Stored Procs are saved globally in the sql server)
|
|
|
|
|
That's because the command I showed before is used to make the assembly available to SQL Server. What you need to do is to create the stored procedure that references this. Here is an example of a stored function:
CREATE FUNCTION MyFunction(@Name NVARCHAR(30))
RETURNS INT
AS EXTERNAL NAME [MyAssembly].[MyDll.ClassName].[MyMethod]
I hope that this helps.
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.
|
|
|
|
|
Mh yes it helps again But I think that I am somehow to stupid... I get always an error saying, that the return types of the t-sql- and clr-types aren't equal. I tried it e.g. this way:
create function MyStoredProc(@name nvarchar(30)) RETURNS uniqueidentifier as external name [MyAssembly].[MyClass].[MyStoredProc]
In my c#-code I see that the implemented storedprocedure MyStoredProc has an output parameter with type of SqlGuid (which is a uniqueidentifier (I think)).
I have also another stored procedure that hasn't any output parameter, but which return value should I specify there ? NULL doesn't work
|
|
|
|
|
FreeCastle wrote: Mh yes it helps again But I think that I am somehow to stupid...
Don't think like that. These are all legitimate questions and they don't reflect on you at all.
FreeCastle wrote: In my c#-code I see that the implemented storedprocedure MyStoredProc has an output parameter with type of SqlGuid (which is a uniqueidentifier (I think)).
I have also another stored procedure that hasn't any output parameter, but which return value should I specify there ? NULL doesn't work
It's hard to say what's going on without seeing your code. If you want to email me it then feel free and I will take a look at it.
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,
did you receive my email ?
|
|
|
|
|
Hi Guy's
I want to Create a trigger on 'Table1' by which at the time of insertion the same data will be insert in to another table 'Table2' of same database please help me.
Sasmi
|
|
|
|
|
Try this:
CREATE TRIGGER [InsertIntoTable2] ON [Table1]
FOR INSERT
AS
INSERT INTO Table2
SELECT (Field1, Field2, (and so on)) FROM INSERTED
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,
Problem: Get all records from MyTable where mydate value is equal to ‘12/31/2006’. Note: Disregard TimeStamp
I know the solution in T-SQL but in PL-SQL i'm just a newbie.
Can you please help me to convert the below T-SQL comannd to PL-SQL.
---TSQL Solution 1: Use 24hrs timespan
SELECT * FROM MyTable
WHERE MyDate BETWEEN '12/31/2006 12:00:00.000 AM' AND '12/31/2006 12:59:59.999 PM'
---TSQL Solution 2: REMOVE TimeStamp
SELECT * FROM MyTable
WHERE RTRIM(CONVERT(VARCHAR(11),MyDate,101)) = ‘12/31/2006’
Thanks,
|
|
|
|
|
Doesn't PL/SQL still have a Truncate or DateTruncate function?
(Haven't used Oracle for many years.)
|
|
|
|
|
I want develop a database managment module in a DLL that is dynamically loaded at run time , i thought i have problems using ADO beacuse it's contained in another DLL .. which should i use ADO, ODBC or OLE DB ..??
|
|
|
|
|
I'm having a stupid problem:
when I try to use INSERT sentance
INSERT INTO .... VALUE(N 'walla',x);
the first parameter (walla) is ging well
the second parameter (x) is making my prog. stack!
I checked the atrribute of the collumns
and it's like the variable x (int).
can anyone help me?
|
|
|
|
|
It would be better if you showed us the code you are actually using because your description appears to be missing some vital bit of information.
My guess, based on the limited information you've provided, is that x is not a valid column name available to the INSERT statement. However, I get the impression that you think x is a variable name. It isn't.
If it is what I think it is then show some code and I can show you how to fix it.
|
|
|
|
|
I wrote:
int x = 10;
int y = 20;
str= "INSERT INTO table1 (C1,C2) VALUES (x,y)";
I called the sqlCommand object with this str santence.
and then the ExecuteNonQuery(); method.
the collumns C1 and C2 declared as int;
|
|
|
|
|
groundzero111 wrote: int x = 10;
int y = 20;
str= "INSERT INTO table1 (C1,C2) VALUES (x,y)";
There's your problem then. As x and y are inside the quotes, they are treated as literals. You would need to do this as:
str= string.Format("INSERT INTO table1 (C1,C2) VALUES ({0},{1})",x,y);
Before you go any further with this though, I would like to point out that it is a very bad idea to build your SQL like this and execute it. While you are only using local variables here, you will eventually want to move onto inserting data that has been input by a user and this is open to SQL Injection Attacks. See this article for more information:
SQL Injection Attacks[^]
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.
|
|
|
|
|
Hello,
I want to write a query something like the following
UPDATE INCOMETABLE SET NAME ='PETER',AGE=30 WHERE RECORDNO = 10;
In the above what should be the keyword of RECORDNO
or there is another way of writing it
THANKS
Prithaa
|
|
|
|
|
Hi,
There is not such a keyword in Sql Server.
(There is one in Oracle, but it's a bad practice to use it in such a case)
What you should do is to use a primary key in the INCOMETABLE table.
UPDATE INCOMETABLE SET NAME ='PETER',AGE=30 WHERE EMPLOYEEID = 10
Uri
|
|
|
|
|
Hello,
Thanks,I got it.
Prithaa
|
|
|
|