Database Discussion Boards

Trying to copy a SQL server 2005 database

Jane Williams25-Sep-09 0:21

25-Sep-09 0:21

We have a database on a SQL 2005 server. We'd like to take an exact copy of it, on the same server (yes, I know this isn't the ideal place for a true dev system), giving the result a name such as mydbname_dev, so as to use it for test purposes.

When I right-click on the database, I see under Tasks an option called "Copy database", which sounds perfect. Only, it doesn't work. Examination of log files tells me that "The login already has an account under a different user name." OK. More Googling suggests that I need to "remove the default associated objects transfer for LOGINS". This means using the "Select Database Objects" screen. Which I can't find... and further investigation shows that it doesn't appear if you're copying to the same server.

Is it really impossible to use "copy database" to simply copy a database?

Any suggestions as to a better way of going about this? I want the new copy to be identical in every way except name to the production database, so we can use it to test out some scripts before we run them on the real thing, and I'd like the copy method to be as simple as possible, to avoid the chances of me introducing an error by typo, or by omitting some vital step.

Re: Trying to copy a SQL server 2005 database

Eddy Vluggen25-Sep-09 1:18

Eddy Vluggen

25-Sep-09 1:18

Make a backup, and restore it under a new name Smile | :)

Re: Trying to copy a SQL server 2005 database

Jane Williams25-Sep-09 2:04

Jane Williams

25-Sep-09 2:04

And that'll preserve all my access control and so on? OK, thanks - will do.

Date Problem

Syed Shahid Hussain24-Sep-09 22:51

Syed Shahid Hussain

24-Sep-09 22:51

hei all,

I'm now working in Norwegian environment where date formate is like this DD.MM.YYYY so when i write query in SQL 2000 like this

SELECT     joinDate<br />
FROM         myTable<br />
WHERE     (joinDate>= '01.01.2009') AND (joinDate<= '01.01.2010')

it gives error

And I cant convert date format. Any ideat will be most welcome.

Thanks

Syed Shahid Hussain

Re: Date Problem

J4amieC24-Sep-09 23:06

J4amieC

24-Sep-09 23:06

The answer is to always use ISO 8601[^] date format when dealing with databases.

So your query becomes:

SELECT joinDate
FROM myTable
WHERE (joinDate>= '2009-01-01') AND (joinDate<= '2010-01-01')

Re: Date Problem

Syed Shahid Hussain25-Sep-09 1:23

Syed Shahid Hussain

25-Sep-09 1:23

Thanks.

But the problem is that from application how can i change the date format, should i wirite a function that

get dd.mm.yyyy and then

return into yyyy-mm-dd

Any suggestion from u.

Thanks

Syed Shahid Hussain

Re: Date Problem

David Skelly25-Sep-09 2:26

David Skelly

25-Sep-09 2:26

If joinDate is a DateTime type, then this should work:

joinDate >= CONVERT(DATETIME, '01.01.2009', 104)

This is fully documented in the SQL Server documentation, which is all available on line.

Re: Date Problem

Syed Shahid Hussain25-Sep-09 2:37

Syed Shahid Hussain

25-Sep-09 2:37

oh really thanks David

its great Rose | [Rose]

Syed Shahid Hussain

Re: Date Problem

PIEBALDconsult26-Sep-09 4:47

PIEBALDconsult

26-Sep-09 4:47

Parse the strings to DateTime and use a parameterized query.

Re: Date Problem

Syed Shahid Hussain26-Sep-09 5:11

Syed Shahid Hussain

26-Sep-09 5:11

thanks

Syed Shahid Hussain

Queries On SQL Job [modified]

Payal_e_m24-Sep-09 14:04

Payal_e_m

24-Sep-09 14:04

Hi,

I am using SQl 2005 for setting up a job which has 2 steps:-

Step 1: Executes a SP which sends out result to a file
Type : T-SQL
Command :
DECLARE @cmd varchar(2000),
SET @cmd = 'osql -E -S CONDOR -s";" -w2000 -d Arion -Q"EXEC dbo.ENRSP_Applicants" -o D:\Applicants.txt'
EXEC master..xp_cmdshell @cmd

Step 2: Mailing the txt file to the user
Type : Operating System(CmdExec)
Command :
"D:\MSSQL\JOBS\EmailFile.cmd" D:\Applicants.txt aa@yahoo.com ss@yahoo.com "Applicants Report"

This works fine but the user want some modification to the current Job
* the email subject to include the date also
* Wants the result set to be dumped into a CSV file and to attach the file to the mail
* Can you also tell me how to include a message to the mail plz?

I know its easy to get this all done using a SSIS but Can these be done using an SQL Job , if so can you plz help me with it?

Thanks in advance.

Warm regards,
Payal

modified on Thursday, September 24, 2009 11:10 PM

SELECT INTO or CREATE TABLE

bxlorenz24-Sep-09 10:30

bxlorenz

24-Sep-09 10:30

I have a SQL 2005 database and a SQL 2008 database. I want a simple SQL statement that will create/copy a table and its contents from 2005 into 2008 that I can use in a C# program. I know I can export using Mgmt Studio but I want to do in a a program. I am able to perform a SELECT INTO [MyDatabase2].[dbo].[mytable] SELECT * FROM [Mydatabase1].[dbo].[mytable] when they exist in different databases in the same machine and only in the same Database Instance but how do I across multiple instances or multiple machines.

Can anyone help ?

Thanks

Re: SELECT INTO or CREATE TABLE [modified]

Blikkies24-Sep-09 19:22

Blikkies

24-Sep-09 19:22

Use this to connect to other server

EXEC sp_addlinkedsrvlogin 'server', 'false', 'userLocal', 'userRemote', 'remotePassword'

Code should look like this:

SELECT INTO [server].[MyDatabase2].[dbo].[mytable] FROM [Mydatabase1].[dbo].[mytable]

modified on Friday, September 25, 2009 2:06 AM

Re: SELECT INTO or CREATE TABLE

bxlorenz25-Sep-09 4:22

bxlorenz

25-Sep-09 4:22

Blikkies,

Thanks for the info. Just a couple follow up questions.

So on the 2005 SQLExpress Server I have an instance of SQLEXPRESS and on 2008 SQL I have an DB instance of MY2008DB I would do the following if I am running on the 2008 system.

EXEC sp_addlinkedsrvlogin '\\MY2005Server\SQLEXPRESS', 'false', 'sa', 'sa', 'sa'

SELECT INTO [MyDatabase2].[dbo].[mytable] FROM [\\MY2005Server\SQLEXPRESS].[Mydatabase1].[dbo].[mytable]

and if I was running this on the 2005 server it would be the opposite ?

EXEC sp_addlinkedsrvlogin '\\MY2008Server\MY2008DB', 'false', 'sa', 'sa', 'sa'

SELECT INTO [\\MY2008Server\MY2008DB].[MyDatabase2].[dbo].[mytable] FROM [Mydatabase1].[dbo].[mytable]

Or how does that work with the DB Instance name ?

thanks

Display few columns in datagridview.

jeshra27924-Sep-09 8:30

jeshra279

24-Sep-09 8:30

Hi everyone,
I have a table with 4 columns = "Name", "Age", "Place","Salary".
I want to display only "Name" and "Age" column with entries in Datagridview in my Form.

Cany anyone please tell me how to do this?

Re: Display few columns in datagridview.

Dave290924-Sep-09 12:49

Dave2909

24-Sep-09 12:49

I'll assume you're using VB.NET or something similar...

If you don't want to show certain columns, simply don't show them. This may entail turning off auto-generate columns in your grid and adding in your own custom column definitions (lots of examples of grids can be found on Google.)

The other alternative is to make your datasource come from a query or view rather than grabbing the whole table.

-Dave

Re: Display few columns in datagridview.

Syed Shahid Hussain24-Sep-09 22:19

Syed Shahid Hussain

24-Sep-09 22:19

simple just do it

gv.Cloumns["YourColumnsName4hide"].Visible = False

that's it

Syed Shahid Hussain

How do I automatically backup my sql server 2005 express database on another server?

Nada Adel24-Sep-09 2:15

Nada Adel

24-Sep-09 2:15

I can't seem to find any way to backup my database on another server every hour or so.
Is there a way to do that? I'm using sql server 2005 express edition.

Re: How do I automatically backup my sql server 2005 express database on another server?

Abhishek Sur24-Sep-09 3:33

Abhishek Sur

24-Sep-09 3:33

Why dont you use Windows Service to create backups. Generally Backup doesnt take network paths. so it is better to do this in your own server in a specific folder and a service will move the file from the place to where you want.

Smile | :)

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

Re: How do I automatically backup my sql server 2005 express database on another server?

Johan Hakkesteegt24-Sep-09 5:56

Johan Hakkesteegt

24-Sep-09 5:56

You can even do what abishek said, with a simple batch file, and a scheduled task.

My advice is free, and you may get what you paid for.

How to Optimize MySql Database?

Paramhans Dubey23-Sep-09 21:40

Paramhans Dubey

23-Sep-09 21:40

Hi Experts,
We have created an application in ASP.Net 2.0 and with MySQL 5.0.67 as Backend. The application was running great from months, but recently we have discovered that there is a performance degrades in the database. Actually database was designed by one of my colleague how didn’t have much of database background so database design is not a professional one. I would also like to mention that this is my first Project on MySQL. I have worked on SQL Server till now. When I started analyzing the database I found indexing was missing in the tables. Introduction of Indexes has improved the performance somewhat. Can any one please tell me what else can I do to improve the performance.

Please help me its urgent

Thanks and Regards,
Paramhans Dubey

Re: How to Optimize MySql Database?

Ashfield24-Sep-09 1:26

Ashfield

24-Sep-09 1:26

Paramhans Dubey wrote:
didn’t have much of database background so database design is not a professional one

So the design may be wrong to start with

Paramhans Dubey wrote:
indexing was missing in the tables

Another sign that he didn't know ehat he was doing

Paramhans Dubey wrote:
Please help me its urgent

Do what you should have done in the first place, employ someone who knows what they are doing. Without the schema, useage, etc its impossible to say what should be done anyway.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: How to Optimize MySql Database?

Paramhans Dubey24-Sep-09 2:15

Paramhans Dubey

24-Sep-09 2:15

Hi
Thanks for your reply and suggestion. We are in process to recruitin someone who knows the job, but meanwhile we just cant sit and wait for him to join and do the job. Anyways I was going through one article where I came to know about Table Engines and their usage. I also came to know there Using MyISAM table engine can improve the read perfomance of the tables but the author also wrote that Your tables will get corrupted eventually but there was no explaination about how and why it happens?
I fyou know the reason then kindly tell me

Thanks and Regards,
Paramhans Dubey.

Re: How to Optimize MySql Database?

Dave290924-Sep-09 12:45

Dave2909

24-Sep-09 12:45

OK, so your DB started performing poorly after it ran for awhile....that's a pretty broad problem, but here's a few things you might try:

1) I fully agree with Bob's post - if you want it to work correctly, get someone who knows what they're doing to set things up properly. (Though I understand that doesn't fix your problem today.)

2) Check for any tables that have grown excessively large. It's possible that you have some large logging / history tables and such that can easily be archived and deleted from the working tables.

3) If there's any way to narrow your problem down to a few areas (perhaps the top 2 or 3 stored procedures you're having trouble with?) you can look at modifying indexes specifically to help with those queries - just be sure you don't replace one problem with another.

4) You could always throw more hardware at it for a temporary fix...

If you have any more specifics about what the problem is, perhaps we could better help.

-Dave

Nesting a stored proc call in a where clause?

Dave290923-Sep-09 14:23

Dave2909

23-Sep-09 14:23

Here's the short question - how can I make this work?

create function callme()
returns TABLE AS
RETURN(exec MyExistingSP)

And the longer question on why I'd want it to...

* I've got several stored procs that return table result sets that are fairly large (or larger than I care to scroll through)
* Often, I'm doing ad-hoc queries where I only want to see one or two rows returned by the stored proc.
* If I need to whip up a function/stored proc to allow this, it would be handy if it would take the stored proc name as a parameter
* Would also be nice to take a subset of the column list in a select, or add an order by
* I'm most interested in SQL Server 2000+
* Performance is not an issue (within some reason...), as this is only for typed in ad-hoc queries

select column1, column2
from callme('MySP')
where id > 500
order by LastModified --> would be nice to see the subset...

I can do this easily with a multiline function for each stored proc, but it's not worth that much work. I keep hoping there's just a twist of syntax I'm missing, but I'm afraid that's not the case...

Any thoughts?

Thanks,

Dave

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.