|
Do a complete backup of the database then restore it with a different name.
"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
|
|
|
|
|
Create a database with new name and export all data of existing database to new one.
Saqib
|
|
|
|
|
But if you do that you don't get the stored procedures, UDFs, users, etc. In other words, it is not a copy of the database, it is just a copy of the data.
"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
|
|
|
|
|
get backup of existing database, create new database and restore that backup to new created database, this would now contain all things in it.
Saqib
|
|
|
|
|
the best thing what i suggest is , right click on the database for ehich u need a copy .. then generate the sql script then follow the steps like showall->scriptall..then u find it will create a file with all sql statements...after that u do..
1. copy the complete sql statments to query analyzer...and in first line give the command
create database (new database name )
...copy all the sql statements
then execute ( i mean f5) that's it
every problem has a solution...
|
|
|
|
|
I search some detailed information about some aspect of commands that are generated in VS8 for TableAdapters:
How are calculated @IsNull_<columnname> parameters that are present in the update query?
Does anybody know something about it?
I understand the use of these parameters but I can't find where the parameters are calculated.
Why this question?
I need to change the automatically generated query because in the table that I use there is a calculated column.
The automatic gerated UpdateCommand include the calculated column in the set section of the query so this doesn't work.
So I changed the query of UpdateCommand using the Query Builder.
When I exit from the query Builder the parameters collection all parameters of type @IsNull_<columnname> change, DbType change from Int to AnsiString and ProviderType is cleared (then the Update method doesn't work). Probably this is a bug.
I can define manually the update command, but I would like to know more about the method used by the DataAdapter to initialize the various @IsNull_<columnname> inserted automatically.
Environment: VS8, SQL Server 2000, .Net Framework 2.0
Thanks in advance
Roberto Ferraris
|
|
|
|
|
Searching in MSDN and in the automatic generated code I find some more info, that I attach to the original question, wishing this could help someone else.
The automatic generated code for the parameters of update command is like this:
new SqlParameter(
"@IsNull_DtClassification",
System.Data.SqlDbType.Int,
0,
System.Data.ParameterDirection.Input,
0,
0,
"DtClassification",
System.Data.DataRowVersion.Original,
true,
null,
"",
"",
"");
From SqlParameter.SourceColumnNullMapping documentation I find that the @IsNull_FieldName is used directly by the SqlCommandBuilder (that I think is the same used by the TableAdapter Configuration Wizard.
Here I find "@IsNull_FieldName contains 1 if the source field contains null, and 0 if it does not. This mechanism allows for a performance optimization in SQL Server, and provides for common code that works across multiple providers."
The problem at this point is that if I try to modify the query manually removing the calculated field from the query, the resulting command generate parameters like the following, where there is no source column and data types is wrong:
new System.Data.SqlClient.SqlParameter(
"@IsNull_DtClassification",
System.Data.SqlDbType.VarChar,
1024,
System.Data.ParameterDirection.Input,
0,
0,
"",
System.Data.DataRowVersion.Original,
false, null, "", "", "")
I thing this is a bug of VS
Bye
Roberto Ferraris
|
|
|
|
|
Hi ppl,
I have created an ODBC connection using the ODBC wizzard.
I am using VB.NEt and SQL server
The DSN name : Rch1
Description of Data source : SqlRch
SQL Server Connection "MT1"
This code is not working
Public conn As New OdbcConnection("Provider=sqlodbc;server=Rch1 ;Database=Rch;Trusted_Connection=YES;")
But wont connect.
I get the following error:
An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll
Additional information: System error.
Tnx
-- modified at 11:01 Wednesday 17th May, 2006
|
|
|
|
|
hi
i want to add a column to table using alter statement and i want it to be added after a particular column.
this is done using after clause in oracle but i want to know its eqivalent in sql server
Sanjeev
|
|
|
|
|
How does the column order make a difference?
|
|
|
|
|
yes u are right , it makes no difference.
But still i want to know how can i do that ???
If any body knows ?
|
|
|
|
|
Ok, given that, you made me interested in how...
Try this[^].
|
|
|
|
|
it was all about swaping existing columns.
columns can be added at desired place using enterprises manager but i m tryng to do it using alter table or ..... some thin that i dont know.
sanjeev
|
|
|
|
|
I think if you read it through, they were saying that behind the scenes enterprise manager copies all your data into a temp table, drops the current table, then recreates it with your new table order and copies the data back over.
Dead easy...
|
|
|
|
|
thanks for your reply.
but you r not geting wat i mean to say ?
|
|
|
|
|
No, you want a simple way within an alter statement to place a column at a particular index within a table. What i'm saying to you here, is that there isn't a simple alter table predicate...
Why would enterprise manager go through the whole copy/drop/recreate routine for moving a column (as it all has to be done through t-sql), if there was a simple way to do it using the alter statement...?
|
|
|
|
|
thanks a lot for taking interest in my doubt.
so the answer is no , i cant do it.
sanjeev
|
|
|
|
|
Well, you can do it, but you'll have to write a script to do the copy/drop/recreate routine.
|
|
|
|
|
It can be done, but it isn't pretty.
Whenever there is a way to do something in the Enterprise Manager, and I want to know the TSQL, I just:
1) turn on the SQL Profiler
2) do my thing in Enterprise Manager
3) turn off the SQL Profiler
4) look for the lines that did what I want to know how to do
5) copy them into SQL Query Analyzer
6) tweak it to my heart's content
--create a table with three columns
CREATE TABLE dbo.tblMyTable
(
Col1 int NOT NULL,
Col2 varchar(50) NULL,
Col3 int NULL
) ON [PRIMARY]
CREATE TABLE dbo.Tmp_tblMyTable
(
Col1 int NOT NULL,
InsertedCol int NULL,
Col2 varchar(50) NULL,
Col3 int NULL
) ON [PRIMARY]
--Take all the data from the old table, and put it in the new one
INSERT INTO dbo.Tmp_tblMyTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM dbo.tblMyTable
DROP TABLE dbo.tblMyTable --drop the old table
EXECUTE sp_rename 'dbo.Tmp_tblMyTable', 'tblMyTable' --rename new one
ALTER TABLE dbo.tblMyTable ADD CONSTRAINT
PK_tblMyTable PRIMARY KEY CLUSTERED
(
Col1
) ON [PRIMARY]
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
|
How to use the currency manager please?? I am trying to make new record with the currency manager. I have a relational database and I want to make new records with the currency manager. How can i use it please. i am not using any array. i want to make position + 1 as well with it! how can i use it please??
Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.
|
|
|
|
|
rfebin2001 wrote: But i want to pass the error to the DAL Factory Class and we throw as a errror
I don't quite understand how you would do that. The DAL Factory class would instantial a DAL object and it would be your DAL object that communicates with the database. The DAL Factory Class' sole purpose in life should just be to construc DAL objects. If it does anything else then its responsibility is overloaded.
I'll assume for the rest that you meant DAL class/object
rfebin2001 wrote: that is the SQL exception error plus our company's validation engine error
When you create a class derived from Exception[^] there are a number of constructors[^] that you can override. One of them[^] accepts a message (which your validator engine can produce) and another Exception object. This will be exposed as the InnerException of the Exception that is being thrown.
Is this what you are looking for?
"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
-- modified at 7:20 Wednesday 17th May, 2006
|
|
|
|
|
how will get last affectecd row in a table
if u using @@identity it is working in SP
Without using SP or any
while we insert as a sql command as a string, and use executenonquery method will executed
this scenario how will get the last affected row from a table
thanx
-
|
|
|
|
|
This will still work whether you are using a stored procedure or not. Try it in Query Analyzer and see.
rfebin2001 wrote: while we insert as a sql command as a string, and use executenonquery method will executed
this scenario how will get the last affected row from a table
I don't understand. Do you mean you want to get value from @@IDENTITY returned to the .NET code that executed the SQL?
You could use SELECT @@IDENTITY and call ExecuteScalar() in the .NET code.
"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
|
|
|
|
|
Without using any extra query, or SP how will get the last affected row in that table
-
|
|
|
|
|