|
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
|
|
|
|
|
How do you know which is the first CustomerName? Is the 'first' name always the correct one?
|
|
|
|
|
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?
|
|
|
|
|
I recommend that you BACKUP the database instead of copying the MDF.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.”
|
|
|
|
|
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
|
|
|
|
|
Still no response after 18hrs. At this rate you'll be on your Hols before we know if the problem was resolved.
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.”
|
|
|
|
|
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
|
|
|
|
|
I'm a bit 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.
|
|
|
|
|
Assuming that your Database is MS SQL Server and that it is >= 2005, this[^] may be of interest to you.
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.”
|
|
|
|
|
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
|
|
|
|
|
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/
|
|
|
|
|
Vuyiswa Maseko wrote: i want to Store the File as "XMLDoc1.XML" in my C:\
And where is the problem?
|
|
|
|
|
In Management Studio this will show the results, but i want to store this results in my C:\. i want to BCP.
Thanks
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/
|
|
|
|
|
So whats the problem highlighting the XML in management studio right click>Save as... ?
Failing that, have a look at SqlCmd from a command prompt. It allows you to execute a query, and output the results to a file.
|
|
|
|
|
i understand what you are Saying. But i want to do that from the above Query. not in C# nor SQL Management Studio
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/
|
|
|
|
|
You can use bcp to create xml in shared folder with Everyone rights and then can use FILE class to move to C:\ drive
|
|
|
|
|
That is Good. Can you please Provide an Example based on the above query ?
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/
|
|
|
|
|
You can right_click in the results pane in SSMS and select save result as...
Change the filename and extension and save.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|