Re: how to write querey return the unique CustomerId with the first CustomerName ? - Database Discussion Boards

Re: How to make unique, readable, varchar codes in a Guid ID table

εїзεїзεїз2-Nov-09 20:51

εїзεїзεїз

2-Nov-09 20:51

try this
http://www.yafla.com/dennisforbes/Sequential-GUIDs-in-SQL-Server/Sequential-GUIDs-in-SQL-Server.html[^]

Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

how to write querey return the unique CustomerId with the first CustomerName ?

AJ Hoge1-Nov-09 23:49

AJ Hoge

1-Nov-09 23:49

hii ,

i have question and i wont ask this question in form of the example.

if we have table named CUSTOMER:

CUSTOMER TABLE:
CustomerID : Integer (Not a primary Key)
CustomerName: TEXT

And This is the table's data:
CustomerId  |  CustomerName
   12       |          Jone   
   25       |          Mark   
   25       |          Marc
   10       |          AJ

we note that CustomerId number 25 has deffrint spiling of the CustomerName for the same Customer!

i need to return the unique CustomerId with the first CustomerName.

i write this querey :

select DISTINCT CustomerID , CustomerName
From Customer

but it got me the four records !! the 25 is dublicated twies becuse the spiling of the name are deffrint for the same Customer

how to write querey return the unique CustomerId with the first CustomerName?

Many Thanks

Re: how to write querey return the unique CustomerId with the first CustomerName ?

il_masacratore2-Nov-09 0:32

il_masacratore

2-Nov-09 0:32

Maybe it ins't the best solution but you can use a group by (CustomerId), then you have to choose wich CustomerName is the choosen (min(CustomerName), max(CustomerName...). Sql:

>select CustomerId, max(CustomerName) from Customer group by CustomerId.

Visit my blog at http://dotnetforeveryone.blogspot.com

Re: how to write querey return the unique CustomerId with the first CustomerName ?

AJ Hoge2-Nov-09 4:42

AJ Hoge

2-Nov-09 4:42

Ohhh...Yes it is work !! many thanks

Re: how to write querey return the unique CustomerId with the first CustomerName ?

J4amieC2-Nov-09 1:18

J4amieC

2-Nov-09 1:18

SELECT 
 @customerId as customerId,
 (SELECT TOP 1 customerName FROM Customer WHERE customerId=@customerID ORDER BY customerName) AS customerName

Whenever your data looks this bad, its a sure-fire signal to clean your data, and ensure your apps only insert clean data.

Re: how to write querey return the unique CustomerId with the first CustomerName ?

AJ Hoge2-Nov-09 4:51

AJ Hoge

2-Nov-09 4:51

hii ,sorry what do you mean by sure-fire signal ? Sniff | :^)

yes i agree with you that the data is look bad , but i work on a huge data that which is extracted by the Customers them self so i can not edit any thing. Frown | :(

many thanks

Re: how to write querey return the unique CustomerId with the first CustomerName ?

J4amieC2-Nov-09 6:35

J4amieC

2-Nov-09 6:35

Member 3018046 wrote:
hii ,sorry what do you mean by sure-fire signal

It means that it is time to examine why you have 2 customer records with the same Id, but different names.

Re: how to write querey return the unique CustomerId with the first CustomerName ?

Niladri_Biswas2-Nov-09 4:51

Niladri_Biswas

2-Nov-09 4:51

You can even try this

declare @tblcustomer table(customerid int,customername varchar(50))
insert into @tblcustomer
select 12,'Jone' union all select 25,'Mark' union all
select 25,'Marc' union all select 10,'AJ'

select x.customerid
		,x.customername
		from
		(
	select 
		ROW_NUMBER() over (partition by customerid order by customerid) as rn
		,customerid
		,customername
	from @tblcustomer)X(rn,customerid,customername)
	where rn = 1

Output:

customerid	customername
10	          AJ
12	          Jone
25	           Mark

Niladri Biswas

Re: how to write querey return the unique CustomerId with the first CustomerName ?

i.j.russell2-Nov-09 5:14

i.j.russell

2-Nov-09 5:14

How do you know which is the first CustomerName? Is the 'first' name always the correct one?

Detaching SQL Database .mdf file !

jeshra2791-Nov-09 18:36

jeshra279

1-Nov-09 18:36

I have a SQL database .mdf file.I want to detach this database mdf file and then copy the file into a new directory using VisualBasic code. How to do this?

Re: Detaching SQL Database .mdf file !

Mycroft Holmes1-Nov-09 20:01

Mycroft Holmes

1-Nov-09 20:01

I recommend that you BACKUP the database instead of copying the MDF.

Never underestimate the power of human stupidity
RAH

Re: Detaching SQL Database .mdf file !

jeshra2791-Nov-09 20:13

jeshra279

1-Nov-09 20:13

I was trying to backup the database file by copying it from one folder to another, but it is throwing error that file is in use. So I thought of detach it first and then copy.
So How to do this?

Re: Detaching SQL Database .mdf file !

Mycroft Holmes1-Nov-09 20:20

Mycroft Holmes

1-Nov-09 20:20

The file is in use because the (SQL) server is using it - always. There are some excellent examples of how to take a backup correctly[^] right here on CP

Never underestimate the power of human stupidity
RAH

Re: Detaching SQL Database .mdf file !

Abhishek Sur1-Nov-09 21:59

Abhishek Sur

1-Nov-09 21:59

Use this :

BACKUP DATABASE [DataBaseName] 
TO  DISK = N'D:\DataBases\BackupDB.bak' WITH 
NOFORMAT, 
NOINIT,  
NAME = N'DataBaseBackup-Name', 
SKIP, 
NOREWIND, 
NOUNLOAD,  
STATS = 10

Create an object of SqlCommand and pass this as CommandText.
Run this in master Database (I mean the connection object should point to master database)

Use ExecuteNonQuery to take backup to Disk location mentioned.
Rose | [Rose]

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Microsoft Bing MAP using Javascript
CLR objects in SQL Server 2005
Uncommon C# Keywords/xml>

Can't Create database

twsted f81-Nov-09 7:15

twsted f8

1-Nov-09 7:15

Hi

I am trying to create a database using T-SQL and i am getting the following error message.

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\saledat.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I am using SQL server express 2005 which I recently installed on a new desktop.

The script I am using to create the database is as follows.

USE master;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO

I don't know what could be causing this error, and I would appreciate any help granted.

Thanks in advance

Re: Can't Create database

Andy_L_J1-Nov-09 7:47

Andy_L_J

1-Nov-09 7:47

I'm not certain, but doesn't

twsted f8 wrote:
MSSQL10.MSSQLSERVER

refer to a SQL Server 2008 instance? (Does on my machine)

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: Can't Create database

Mycroft Holmes1-Nov-09 11:11

Mycroft Holmes

1-Nov-09 11:11

If you are creating this on a server then make sure that path exists ON THE SERVER.

Never underestimate the power of human stupidity
RAH

Re: Can't Create database

Henry Minute1-Nov-09 11:24

Henry Minute

1-Nov-09 11:24

On my system the path for SQL Express 2005 is:

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL1\MSSQL\DATA\saledat.mdf',

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: Can't Create database

Andy_L_J1-Nov-09 11:55

Andy_L_J

1-Nov-09 11:55

I ran the script and it created a Sql Server 2008 database in the specified folders. The path the op is using is definitely for 2008, and if he was using a 2005 instance then the default path should have been what you posted - notice no comments from the op for over 4 hours.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: Can't Create database

Henry Minute2-Nov-09 0:25

Henry Minute

2-Nov-09 0:25

Still no response after 18hrs. At this rate you'll be on your Hols before we know if the problem was resolved.

Update Master Database Daily comparing with old one

RAJAGOPAL PATABALLA30-Oct-09 5:14

RAJAGOPAL PATABALLA

30-Oct-09 5:14

Hi,

We are working with one of the client
We recieve daily updated database from client
We have to compare the old database and the newly records should be updated

Guide me how can we implement this logic in .net or any solution

Regards
RajaGopal
India

Re: Update Master Database Daily comparing with old one

David Mujica30-Oct-09 5:38

David Mujica

30-Oct-09 5:38

I'm a bit confused. Confused | :confused:

If you receive an updated database from your client on a daily basis, why don't you just replace the old database with the new one ?

Otherwise, you will need to create a reconciliation program that considers the following:
1) New records
2) Deleted records
3) Update records

You could apply this logic on a table by table basis, but the problem arises when there are constraints among dependent tables. If you try to insert a record in a table and the foreign key does not exist yet in the parent table you will get an insert error.

One method around this is to drop all constraints when performing this reconcilliation and re-apply the constraints when the process is complete.

Not an easy task.
Best of luck.

Re: Update Master Database Daily comparing with old one

Henry Minute30-Oct-09 10:30

Henry Minute

30-Oct-09 10:30

Assuming that your Database is MS SQL Server and that it is >= 2005, this[^] may be of interest to you.

Re: Update Master Database Daily comparing with old one

εїзεїзεїз31-Oct-09 20:10

εїзεїзεїз

31-Oct-09 20:10

You send whole database or part of the database means only those items which are updated or changed.

Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

SQL To XML

Vimalsoft(Pty) Ltd30-Oct-09 4:59

Vimalsoft(Pty) Ltd

30-Oct-09 4:59

Good Day All

i have the Following Query that Procudes an XML

DECLARE @CurrentTime DATETIME
SET @CurrentTime = CURRENT_TIMESTAMP
select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
tu.name [Requested by]
from tbl_booking tb inner join tbl_resource tr
on tb.resources = tr.id
inner join tbl_user tu on tu.id = tb.RequestedByUser
where (day(startdate) = day(@CurrentTime))and(month(startdate)=month(@CurrentTime))and(year(startdate)=year(@CurrentTime))and(tb.status=1)
order by [Room],[Start Time]
FOR xml raw

i want to Store the File as "XMLDoc1.XML" in my C:\

Kind Regards

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

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