Correct way to create indices? SQLServer Express 2005

Hi,

Apologies if this has been asked before, i've done a search but can't find a definitive answer.

I've created a table in an SQLExpress 2005 db using Server Managment Studio Express.

My intention is to use GUID fields as surrogate PK's. I therefore wanted to add a additional index to prevent duplicate records being added to the table. Not having used SQLServer before could someone confirm or deny that this is the correct way to do this. The PK field [EPISODEID{unique identifier}] is set as a non-clustered index. And i've created a second clustered index using the two fields that create a unique record. I've added a screen shot if that is any help.

Thanks

Chris

You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

|||

Motley wrote:

You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

Thanks very much for your reply

I've read up quite a lot of posts and articles regarding the use of surrogate vs natural keys. My primary reason for choosing surrogate keys is the inability to guarantee access to values which would create a natural key that didn't need changing. For example a head injured patient arrives at hospital and is assessed by the team i am working for. This is common as i work for critical care services which include Neurologically impared patients. They need a unique id for that patient but have no access to a possible natural key, the patients NHS number. In this case it's easier to generate a surrogate PK like a GUID which can be used on all the joins required in the database and use an incremental value from a store to replace the NHS number and keep the record unique until the NHS number can be assertained. Even then if the patient is non-uk resident they will never have an NHS number. However if the NHS number is found at a later date the PK can remain intact it's just a case of updating the NHS number field rather than cascading a changing PK through the database.

Anyways,

If i've got this correct the purpose of clustering an index is to create some form of sorting on sequentially related values which helps with retrieving records i.e. dates

If that's correct then as a surrogate key doesn't [or shouldn't] contain data relating to the contents of the record it indentifies it wouldn't matter if it is clustered or not. I think that this would particularly be the case with GUIDs as there is no logical sequence to their creation that would be worth clustering as compared to a sequential integer that might provide a pseudo order of entry index.

The second index i've created i've set as a clustered index as that is the one that holds the data which is used for retrieval i.e. finding all patient episodes within a given date range, so sorting would be beneficial and the unique index constraint should prevent duplication of records.

I've tried to follow the advice given on msdn regarding creating indicies i just wasn't sure if i'd actually done it correctly in Express05. Of course i may have missed the point entirely

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8185.asp

Thanks again

Chris.

Correct syntax for an update stored procedure

This is probably a very simple question but i would appreciate some helpwith the correct syntax for andupdate stored procedure

I have created user form that allows the user toupdate thenameandaddress fields in adatatable called customers based on the input valuecustomer ID = ( datatable/Customers)customerID
I have got this far and then got lost:
Create SP_UpdateCustomer
(@.customerID, @.name, @.address)
As
Update customers ( name, address)
Where customerID = @.customerID

GO
Could anyone tell me what the correct sntax should be.
many thanks
MartinHi Martin,
You'll need to specify the data types in the create clause, and add a set clause to change the fields you want to update:
create proc sp_updatecustomer (@.customerid varchar(50), @.name varchar(50), @.address varchar(50))
as
update customers
setname=@.name,address=@.address
where customerID= @.customerID
Note - this might be a bit dangerous from a security standpoint, and you might also want to introduce some validation on the Customer ID field, to avoid anyone maliciously changing all the records by entering a customer ID of "a or 1=1"
|||

rJonas
Many thanks for your reply
I note the securtiy points you made
Thank you
martin

|||

rjonas wrote:

Note - this might be a bit dangerous from asecurity standpoint, and you might also want to introduce somevalidation on the Customer ID field, to avoid anyone maliciouslychanging all the records by entering a customer ID of "a or 1=1"

?? That is not physcially possible with the stored procedure the posteris using. The stored procedure is corerctly parameterized and thedanger you pointed out does not exist here.
Here are some articles on SQL injection and parameterized queries:

Please, please, please, learn about injection attacks!
How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

correct me??

I've created C#.net program (behind code style).

when I run it in Internet explorer, the following error occurs in IE window.

pls instruct me how to handle and correct this error.

And how to initialize the connectionstring... Great thank!

Server Error in '/' Application.

------------------------

The ConnectionString property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: The ConnectionString property has not been initialized.]
System.Data.SqlClient.SqlConnection.Open() +809
CodeBox.BehindCode.getSubject() +80
CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +29
System.Web.UI.Page.ProcessRequestMain() +724

------------------------

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0

Hi,
You haven't posted the code that you are trying to execute.
From the error, it seems that you havent set the connection string property for your connection object.
You can initialize the connection string as follows:-
SqlConnection objCon = new SqlConnection("put your connection string here");
Thanks.|||if you still have the problem show us the code !!|||

hi! thanks...
but I still have problem...
I want to show you my code and error message...
here is my code...
SqlDataReader getSubject(){
//create instance of connection and command object

//SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
constring ="DSN=fisca;User ID=sa;Password=admin;";
SqlConnection myconnection = new SqlConnection(constring);

SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}

here is error message. I sure that dsn is really existing and userid, password of sql server is true....
and I also sure that variable constring is declared.

Server Error in '/' Application.

---

Unknown connection option in connection string: dsn.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.ArgumentException: Unknown connection option in connection string: dsn.
Source Error:

Line 77: //SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);Line 78: constring ="DSN=fisca;User ID=sa;Password=admin;";Line 79: SqlConnection myconnection = new SqlConnection(constring);Line 80: Line 81: SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);

Source File:c:\inetpub\wwwroot\CourseRequisition.cs Line:79
Stack Trace:

[ArgumentException: Unknown connection option in connection string: dsn.] System.Data.SqlClient.ConStringUtil.ParseStringIntoHashtable(String conString, Hashtable values) +673 System.Data.SqlClient.ConStringUtil.ParseConnectionString(String connectionString) +55 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +375 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +164 CodeBox.BehindCode.getSubject() in c:\inetpub\wwwroot\CourseRequisition.cs:79 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\CourseRequisition.cs:36 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724

---

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0|||

here is my code...

SqlDataReader getSubject(){
//create instance of connection and command object
SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
but now I can find it out and I've corrected it myself.
cause I initialize connectionstring in web.config file. It can put away error.
so now I want to know that is there many ways to connect to sql server?...
how many way?
I just know two way,
1. Sqlconnection constring=new Sqlconnection("connection string");
2. SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
so pls let me know. cause I willingly want to know C#.net thoroughly.
I also learning it from book and creating C#.net application..
Have a bright and wonderful day!

|||the connection string that you provided is not correct !!!
look atwww.connstingSttings.com and see the proper contents of the connection string try to look the connetion string and if you still have problem get back to me

copying/ backing up a database

Hi. im shortly going to have to submit my project for uni which ive created using sql server. How can i copy everything that ive made so i can submit everything and it can be replicated if necessary. Do i use the backup database task in enterprise manager or do i have to do that and export data or..?

ive used tables and stored procedures and a diagram btw.

thanks for any advice

Look up sp_detach_db in Books Online. Detach the database, copyit, and submit it. Then they can re-attach it using sp_attach_db.

sql

copying views

I have created several views via Enterprise Manager in a
SQL database that supports software provided by an outside
source. These views will be effected during software
version upgrades and may have some nagative impact on the
execution of upgrades.
Is there some simple way to preserve these views during a
software upgrade?
I hoped that I would just be able to cut and paste the
views (perhaps into another database where they didn't
really belong) temporarily during an upgrade and then move
them back. I don't see any way to do this. I can right
click on the views and copy them, but I don't see any way
to paste it anywhere for temporary storage.
I would prefer not to copy the query text from each view,
save it somewhere temporarily, and then recreate each view
and paste the query text back into it. I have a lot of
views created and this would be a time consuming solution.
Thanks.generate scripts and run those after the update
"allen" <anonymous@.discussions.microsoft.com> wrote in message
news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
> I have created several views via Enterprise Manager in a
> SQL database that supports software provided by an outside
> source. These views will be effected during software
> version upgrades and may have some nagative impact on the
> execution of upgrades.
> Is there some simple way to preserve these views during a
> software upgrade?
> I hoped that I would just be able to cut and paste the
> views (perhaps into another database where they didn't
> really belong) temporarily during an upgrade and then move
> them back. I don't see any way to do this. I can right
> click on the views and copy them, but I don't see any way
> to paste it anywhere for temporary storage.
> I would prefer not to copy the query text from each view,
> save it somewhere temporarily, and then recreate each view
> and paste the query text back into it. I have a lot of
> views created and this would be a time consuming solution.
> Thanks.|||Thanks!
>--Original Message--
>generate scripts and run those after the update
>"allen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
>> I have created several views via Enterprise Manager in a
>> SQL database that supports software provided by an
outside
>> source. These views will be effected during software
>> version upgrades and may have some nagative impact on
the
>> execution of upgrades.
>> Is there some simple way to preserve these views during
a
>> software upgrade?
>> I hoped that I would just be able to cut and paste the
>> views (perhaps into another database where they didn't
>> really belong) temporarily during an upgrade and then
move
>> them back. I don't see any way to do this. I can right
>> click on the views and copy them, but I don't see any
way
>> to paste it anywhere for temporary storage.
>> I would prefer not to copy the query text from each
view,
>> save it somewhere temporarily, and then recreate each
view
>> and paste the query text back into it. I have a lot of
>> views created and this would be a time consuming
solution.
>> Thanks.
>
>.
>sql

Copying tables between two databases

I am trying to copy the chart of accounts from Fabrikam to a newly created
company. I am running SQL 2005 and Dynamics GP 9.0.
I am following the steps from article 866332 in the knowledge base. This
process has the user copying some database tables from one database to
another database.
I perform the steps but when I try to execute the process, I get the following
errors:
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc0202049: Data Flow Task: Failure inserting into the
read-only column "DEX_ROW_ID".
(SQL Server Import and Export Wizard)
* Error 0xc0202045: Data Flow Task: Column metadata validation
failed.
(SQL Server Import and Export Wizard)
* Error 0xc004706b: Data Flow Task: "component "Destination -
GL00100" (139)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
* Error 0xc004700c: Data Flow Task: One or more component failed
validation.
(SQL Server Import and Export Wizard)
* Error 0xc0024107: Data Flow Task: There were errors during task
validation.
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [FS].[dbo].[GL00100] (Stopped)
- Copying to [FS].[dbo].[GL00102] (Stopped)
- Copying to [FS].[dbo].[GL00103] (Stopped)
- Copying to [FS].[dbo].[GL00104] (Stopped)
- Copying to [FS].[dbo].[GL00105] (Stopped)
- Copying to [FS].[dbo].[GL40200] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
Any idea what the problem is?
Thanks.
Jerry FlattoOn Mar 24, 2:04 am, Jerry Flatto
<JerryFla...@.discussions.microsoft.com> wrote:
> I am trying to copy the chart of accounts from Fabrikam to a newly created
> company. I am running SQL 2005 and Dynamics GP 9.0.
> I am following the steps from article 866332 in the knowledge base. This
> process has the user copying some database tables from one database to
> another database.
> I perform the steps but when I try to execute the process, I get the following
> errors:
> Operation stopped...
> - Initializing Data Flow Task (Success)
> - Initializing Connections (Success)
> - Setting SQL Command (Success)
> - Setting Source Connection (Success)
> - Setting Destination Connection (Success)
> - Validating (Error)
> Messages
> * Error 0xc0202049: Data Flow Task: Failure inserting into the
> read-only column "DEX_ROW_ID".
> (SQL Server Import and Export Wizard)
> * Error 0xc0202045: Data Flow Task: Column metadata validation
> failed.
> (SQL Server Import and Export Wizard)
> * Error 0xc004706b: Data Flow Task: "component "Destination -
> GL00100" (139)" failed validation and returned validation status
> "VS_ISBROKEN".
> (SQL Server Import and Export Wizard)
> * Error 0xc004700c: Data Flow Task: One or more component failed
> validation.
> (SQL Server Import and Export Wizard)
> * Error 0xc0024107: Data Flow Task: There were errors during task
> validation.
> (SQL Server Import and Export Wizard)
> - Prepare for Execute (Stopped)
> - Pre-execute (Stopped)
> - Executing (Success)
> - Copying to [FS].[dbo].[GL00100] (Stopped)
> - Copying to [FS].[dbo].[GL00102] (Stopped)
> - Copying to [FS].[dbo].[GL00103] (Stopped)
> - Copying to [FS].[dbo].[GL00104] (Stopped)
> - Copying to [FS].[dbo].[GL00105] (Stopped)
> - Copying to [FS].[dbo].[GL40200] (Stopped)
> - Post-execute (Stopped)
> - Cleanup (Stopped)
> Any idea what the problem is?
> Thanks.
> Jerry Flatto
DEX_ROW_ID seems to be timestamp column which can not be importedsql

Copying tables between database

Hi
I have 2 user created databases in MS SQL Server 2000. Now I want to copy tables from one of this database to the other. The structure, relationships etc of these tables between these 2 databases are very similar. I searched high and low in BOL but could not find any info
Can some one help me in this please ...
Thanks
Harish MohanbabuHarish, you have many different solutions:
- SELECT col_list INTO db2.dbo.newtable FROM db1.dbo.oldtable - check
SELECT statement in Books OnLine
- Use Data Transformation Services (you can do it with the DTS Import/Export
Wizard) - check "DTS Import/Export Wizard" topic
- Create scripts, if you need metadata only, and implement scripts in the
new database - check "How to generate a script (Enterprise Manager)"
- ...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu|||Hi,
Since you have the schema in both the databases, Probably you need to copy
the data from database to another in same server, then you can use
1. Copy only data
insert into tablename select * from dbname..tablename
2. If we need to replicate all the tables / users / procedures in one
database to another then you can Backup and Restore
Backup database dbname1 to disk='c:\dbname.bak' with init,stats=10
go
Restore database dbname2 from disk='c:\dbname.bak' with move
'logicaldatafilename' to 'newphysicalfile.mdf',
move 'logicallogfilename' to 'newphysicalfile.ldf'
Thanks
Hari
MCDBA
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu|||Hi Dejan/Hari,
Thank you very much for your kind replies. Since the structure of tables between databases were same, I used DTS to copy data between tables.
Cheers,
Harish Mohanbabu

Copying tables between database

Hi,
I have 2 user created databases in MS SQL Server 2000. Now I want to copy t
ables from one of this database to the other. The structure, relationships e
tc of these tables between these 2 databases are very similar. I searched h
igh and low in BOL but coul
d not find any info.
Can some one help me in this please ...
Thanks,
Harish MohanbabuHarish, you have many different solutions:
- SELECT col_list INTO db2.dbo.newtable FROM db1.dbo.oldtable - check
SELECT statement in Books OnLine
- Use Data Transformation Services (you can do it with the DTS Import/Export
Wizard) - check "DTS Import/Export Wizard" topic
- Create scripts, if you need metadata only, and implement scripts in the
new database - check "How to generate a script (Enterprise Manager)"
- ...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu|||Hi,
Since you have the schema in both the databases, Probably you need to copy
the data from database to another in same server, then you can use
1. Copy only data
insert into tablename select * from dbname..tablename
2. If we need to replicate all the tables / users / procedures in one
database to another then you can Backup and Restore
Backup database dbname1 to disk='c:\dbname.bak' with init,stats=10
go
Restore database dbname2 from disk='c:\dbname.bak' with move
'logicaldatafilename' to 'newphysicalfile.mdf',
move 'logicallogfilename' to 'newphysicalfile.ldf'
Thanks
Hari
MCDBA
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu

copying sql table to computer hard drive

Hi,

I use sql server management express. I have created a table on my hosts remote database and i want to copy the table (or the data) in some format or other to my hard drive. does anyone have any good ideas how i may do this either through management express or other means.

thanks a lot

nick

http://www.cryer.co.uk/brian/sqlserver/howtoexportcsv.htm
http://www.codeproject.com/aspnet/ImportExportCSV.asp

|||

thanks mike, i shall give this a whirl

nick

Copying SQL Server website to CD

Hi!

Not sure if this is the right place to post. I need to copy a website that I created on a CD. It is using SQL Server as the database and VB, ASP as front end. However, the problem is that I need to copy it in such a way so that it can be viewed from the CD itseld without needing MSDE or SQL Server. Need help.

Thanks.

This is a fine place to post.
The problem here is that it is difficult to make a SQL Server databaseread-only, which it needs to be on a CD. I realize that you probablywant to use the same db as you use on a Web site, but it's a hassle.
That said, there are a few options. Check out the sp_create_removablesystem stored procedure, which lets you create a removable mediadatabase. It's a bit tricky to get to work right, but ask questionshere if you want to use that option. I think I have some sample codelaying around.
But wait...you don't want SQL Server or MSDE installed at all? Ifthat's what you mean, you're out of luck. You're going to have to useanother way to store and use the data on the CD. An XML file mightwork. You could maybe use another database engine, such as Access, butit's going to have to be installed on the machine. (Anyone know if theAccess runtime is still available? I'm not an Access guy.)
Please clarify what you want to do and we'll try to help.
Don
|||

Thanks for replying.

ACtually I just realized that if there is a way to get the MSDE on the CD itself and make it work that is fine. SO if that is the case then we would not need Access on the user machine and the user would not have to install the MSDE. You think this would work? How ?

Thanks again.

|||I'm confused. What do you mean that you can get MSDE on the CD? MSDE has to beinstalled on the computer in order to access the database. You can't just put a program on a CD and not have to install anything.
So no, it won't work. (Unless there is something I don't know about MSDE, which is quite possible!)
Don

Copying SQL Server Database on Clients Machine

Hello all,

I have installed a dummy SQL server in my office and created the database..now i want to copy this database on to clients machine but i dont have a remote access to the clients machine..so all i have to do is copy this database on a disk and restore it on clients machine..I dunoo exactly how this procedure works. I need any help ASAp. Thankyou very much.

RamOriginally posted by sriramkakani
Hello all,

I have installed a dummy SQL server in my office and created the database..now i want to copy this database on to clients machine but i dont have a remote access to the clients machine..so all i have to do is copy this database on a disk and restore it on clients machine..I dunoo exactly how this procedure works. I need any help ASAp. Thankyou very much.

Ram

You want to readup on attaching and detaching a database...also search this forum or read online help.|||If your using MS SQL 2000 , and your client is also using MS SQL 2000, you can do it via "Attach/Detach".

A very simple way. go to enterprise manager, right click on your database, under "All task".select detached database.

Make sure there are not connections to u'r database when u'r doing a detached, or else u can't do it.

Also , be sure that your login id has db owner rights for that table, or u'll have trouble attaching back at the client side.

After detached, you can go to your MS SQL folder and copy your .mdf and .log files into a diskkete. You can even zip it up.

At the client side, just attached it back.

Before u do this, create a test database and play around first.|||You can also just take a backup of the database and copy the .bak file. then restore. It isnt as fast as detatch and reattach, but it IMO less risky, and easier to do. Read up in BOL on backup and restore procedures. very simple.sql

Copying my SQL Database to another server

Hi guys,

I have created a new SQL database (named “DB”) on my personal pc at home.

I want to copy “DB” to my job’s computer, which works on a local net with SQL server.

What should I do?

Thanks a lot,

Aldo.

you need a connection between the machines first of. If you have VPN to your work's LAN you can use it or create a backup of the database or copy the database files in the sql server directories. If you take the offline/copy approach simply bring the file(s) via removable media into work and restore/attach the database file(s). If you can get a valid remote connection then you could use maintenance plans/SSIS to copy over the database between sql servers assuming you have valid login credentiuals to the remote "work" server.|||

Hi Derek, Which / where is the file I should copy in order to get an offline copy?

I've tried copying my ".mdf" file from this path: "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DB.mdf" but getting something strange...

Thanks in advance,

Aldo.

|||sorry, you need to detach the database first and then reattach it.|||Where can I see how to "detach" and "reatach" a dababase?|||

Hi ajliks,

I would suggest , first you take a back up of your Database (Db) from your personal pc . Copy the back up file to a CD or some portable storage device. Copy it to your Job's computer. And the restore the database from there on to your SQL Server directly. This would be the easiest way to do it.

Thanks & Regards

Mathew

|||

Hi Mathew, thanks for anwering. I want to know how to make the SQL database copy. I've tried copying the mdf file but getting some strange stuff. (I want to copy the table and views configuration).

Thanks,

Aldo.

|||

1. Open your SQL Server Enterprise Manager

2. Expand and Point to your database.

3. Right Click on your database (Db), and navigate to "All Task " -- "Backup Database" -- "Add" -- "File Name"

4. Give a proper file name and note the location where the backup file is being created.

5. Copy This file to your Job Computer

6. For Restoring on your Job computer check the following steps

7. Open Enterprise Manager on your job computer.

8. Point to Database Node and Right Click on "Restore Database"

9 . Type your Database Name "Db" on the drop down box "Restore Database as"

10 Navigate through "From device" -- "Select devices" -- "Add" - "File Name"

11 Select the backup file and click ok.

You would get ur database created on your machine

Does this makes sense to you now

|||Thanks a lot man!

Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.
HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de

Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.

My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?

Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.

Any advice would be greatly appreciated.

Nick.(nickwilson.nick@.gmail.com) writes:
> I have several stored procedures, created in a development environment,
> that I need to move to a 'QA' environment, and then in turn, to various
> production environments.
> When I move these stored procedures, I would like to encrypt them,
> using the 'WITH ENCRYPTION' clause.
> My question is, how do I copy these stored procedures from development
> to their target SQL server environment in an encrypted state?

The answer is that you don't. I think SQL 7 had a function for this, but
it was discontinued with SQL 2000.

> Up until now, we have been moving them by generating an SQL script and
> then executing that script on the target server. I have tried this
> using a script with 'WITH ENCRYPTION' specified within it, but it
> doesn't appear to work when I try and execute that script on the target
> server.

What "doesn't appear to work" mean?

You would have to write a tool that reads the script and shifts in
WITH ENCRYPTION before AS. (Assuming that the number of procedures are
too many to make manual editing an option.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
--
http://www.sqlerver2005.de
--

Copying encrypted stored procedures.

I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de
--

Copying a Datbase between two different servers

Hello Everyone,

I am new to SQl Server 2005. I have created a database on one machine and I tried copying it on to another machine where SQL Server 2005 is installed. Both machines have SQL Server 2005 installed. When I tried copying using copy database wizard its asking for the destination server. I have given the name of the destination server but its giving me error msg saying tht it cant connect. How to chk whether my destination server is online? If it is not how to make it online? Any help would be appreciated.

Srividya.

Remote connections are disabled by default for SQL Server. YOu will have to enable them first. For more information see my screencast on my site which covers the "Enable of remote connections for SQL Server 2005".

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de