Database

Re: Problem connecting to sql server 2008 database from sql server 2005 express..

25-Sep-09 2:43

thanks a lot, I'll install sql server 2008 and try again.

Nada Adel25-Sep-09 3:17

Re: Problem connecting to sql server 2008 database from sql server 2005 express..

25-Sep-09 3:17

I have a feeling that the server name is not correct.
How can I get the correct server name to connect to?
I have access to the server, can you tell me how to get the server name if I want to connect remotely??

Nisha Agrawal25-Sep-09 4:10

Nisha Agrawal

25-Sep-09 4:10

have u enabled TCP/IP Connection on the server? it's required to connect it remotely.

Re: Problem connecting to sql server 2008 database from sql server 2005 express..

Nada Adel25-Sep-09 4:15

Trying to copy a SQL server 2005 database

25-Sep-09 4:15

yes I did...but the default port is not 1433, it is set to another port - 1512.
So I added that port to firewall exceptions.
I just wanna make sure I did this right. I added a rule for the port 1512. It is a TCP not UDP right?

Jane Williams25-Sep-09 0:21

Jane Williams

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

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

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')

Syed Shahid Hussain25-Sep-09 1:23

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

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.

Syed Shahid Hussain25-Sep-09 2:37

25-Sep-09 2:37

oh really thanks David

its great Rose | [Rose]

Syed Shahid Hussain

PIEBALDconsult26-Sep-09 4:47

PIEBALDconsult

26-Sep-09 4:47

Parse the strings to DateTime and use a parameterized query.

Syed Shahid Hussain26-Sep-09 5:11

Queries On SQL Job [modified]

26-Sep-09 5:11

thanks

Syed Shahid Hussain

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

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

24-Sep-09 22:19

simple just do it

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

that's it

Syed Shahid Hussain

Nada Adel24-Sep-09 2:15