Changing a column to IDENTITY - Database Discussion Boards

Vimalsoft(Pty) Ltd12-Nov-09 3:10

12-Nov-09 3:10

Good Day all

i have the Following Query

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]

and in the [Start Time]and [End Time] it gives me time that is not Complete

it Gives this

14:0

instead of

14:00

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/

Re: Problem with Datepart

J4amieC12-Nov-09 3:52

J4amieC

12-Nov-09 3:52

Why in the world would you be formatting your time in SQL? SQL Is for extracting data (and it seems your decord is already of type DateTime) and a client application should be responsible for formatting that data.

As an aside, using spaces in fields or field alias' is generally bad form.

Re: Problem with Datepart

Not Active12-Nov-09 5:36

Not Active

12-Nov-09 5:36

Convert with style 108 will return the format as hh:mm:ss. If you don't want seconds, just strip it off.

I know the language. I've read a book. - _Madmatt

Re: Problem with Datepart

David Skelly12-Nov-09 5:42

David Skelly

12-Nov-09 5:42

Damn, pipped to the post again! Note to self: must type faster.

Re: Problem with Datepart

Not Active12-Nov-09 5:44

Not Active

12-Nov-09 5:44

Maybe you need to upgrade the 14.4 modem to broadband Poke tongue | ;-P

I know the language. I've read a book. - _Madmatt

Re: Problem with Datepart

David Skelly12-Nov-09 5:42

David Skelly

12-Nov-09 5:42

The reason you are seeing this is because the minute part of 14:00 is 0. So, you end up getting 14 for the hour part and 0 for the minute part. Hence a result of 14 Blush | :O

.

I agree with the previous poster, SQL is not the best place to reformat time, but sometimes the requirement is there and you have to do it. You should look into the format options of CONVERT. I think one option for you would be to convert starttime and endtime to strings using format 108 and then trim off the seconds if you don't want them.

http://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

I'm also not convinced by your WHERE clause at the end. That looks to me like a potential performance killer, converting dates to do a selection like that. I may be wrong but I doubt if it will use an index for that so you will end up scanning over every row in the table.

Re: Problem with Datepart

Vimalsoft(Pty) Ltd12-Nov-09 22:52

Vimalsoft(Pty) Ltd

12-Nov-09 22:52

Good Day

in have Resolved it by adding

right('0'+convert(varchar(2),datepart(hour,tb.starttime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.starttime)),2) AS[Start Time],
right('0'+convert(varchar(2),datepart(hour,tb.endtime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.endtime)),2) AS  [End Time],

Thanks

Changing a column to IDENTITY

garfield18511-Nov-09 22:34

garfield185

11-Nov-09 22:34

Hi everybody.

SQL Server 2005

I've got a table "Center (ID,Name)", it has some records registered.

I need to change the ID field, and make it IDENTITY = true.
I have been searching, and only found information about how to add a new identity column to an existing table, but I have to turn an existing field on a table to Identity.

I cannot create a new identity field and then delete the old one, because the registered IDs are not sequential...

Any ideas? Thanks a lot.

Time to come clean...

Vive y deja vivir / Live and let live

Javier

Re: Changing a column to IDENTITY

Niladri_Biswas12-Nov-09 1:16

Niladri_Biswas

12-Nov-09 1:16

Follow the steps

1)Go to the design view of your table

2)Select ID Column

3) In the down, you will find Column Properties

4)In that you will find Identity Specification whose first property is Identity Specification. The default will be set to "No".

Change that to yes.

5)Save the table.

Hope this helps
Smile | :)

Niladri Biswas

Re: Changing a column to IDENTITY

garfield18512-Nov-09 2:41

garfield185

12-Nov-09 2:41

Thanks Niladri, but I meant how to do it in SQL language...

Re: Changing a column to IDENTITY

WoutL12-Nov-09 3:05

WoutL

12-Nov-09 3:05

Wout Louwers

Re: Changing a column to IDENTITY

Niladri_Biswas12-Nov-09 3:22

Niladri_Biswas

12-Nov-09 3:22

This may help

a) http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column-sql-server[^]

b) http://www.mssqltips.com/tip.asp?tip=1397[^]
Smile | :)

Niladri Biswas

SqlConnection or what??????? i need help

Mohamed El-Wehishy11-Nov-09 9:36

Mohamed El-Wehishy

11-Nov-09 9:36

high guys
i have a problem
when i press F5 on visual studio to start debugging of my project, it runs good no errors
but
when i press ctrl+F5 on visual studio to start without debugging of my project,it gets error message
the error message is:

Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immedediately.
the type initializer for 'System.Data.SqlClient.SqlConnection'threw an exception.
Details..
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.PlatformNotSupportedException: This operation is only supported on Windows 2000 SP3 or later operating systems.
at System.Security.Principal.SecurityIdentifier..ctor(WellKnownSidType sidType, SecurityIdentifier domainSid)
at System.Diagnostics.SharedUtils.EnterMutexWithoutGlobal(String mutexName, Mutex& mutex)
at System.Diagnostics.SharedPerformanceCounter.GetCounter(String counterName, String instanceName, Boolean enableReuse, PerformanceCounterInstanceLifetime lifetime)
at System.Diagnostics.SharedPerformanceCounter..ctor(String catName, String counterName, String instanceName, PerformanceCounterInstanceLifetime lifetime)
at System.Diagnostics.PerformanceCounter.Initialize()
at System.Diagnostics.PerformanceCounter.set_RawValue(Int64 value)
at System.Data.ProviderBase.DbConnectionPoolCounters.Counter..ctor(String categoryName, String instanceName, String counterName, PerformanceCounterType counterType)
at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
at System.Data.SqlClient.SqlPerformanceCounters..ctor()
at System.Data.SqlClient.SqlPerformanceCounters..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnectionFactory..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection..ctor()
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Computer_Sales_System.Form2.button1_Click(Object sender, EventArgs e) in D:\My first project\Computer Sales System\Computer Sales System\Form2.cs:line 178
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
Computer Sales System
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///D:/My%20first%20project/Computer%20Sales%20System/Computer%20Sales%20System/bin/Debug/Computer%20Sales%20System.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
PresentationCore
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_32/PresentationCore/3.0.0.0__31bf3856ad364e35/PresentationCore.dll
----------------------------------------
WindowsBase
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/WindowsBase/3.0.0.0__31bf3856ad364e35/WindowsBase.dll
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
WindowsFormsIntegration
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/WindowsFormsIntegration/3.0.0.0__31bf3856ad364e35/WindowsFormsIntegration.dll
----------------------------------------
PresentationFramework
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/PresentationFramework/3.0.0.0__31bf3856ad364e35/PresentationFramework.dll
----------------------------------------
PresentationFramework.Aero
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/PresentationFramework.Aero/3.0.0.0__31bf3856ad364e35/PresentationFramework.Aero.dll
----------------------------------------
System.Management
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

the code is:

SqlConnection cn = new SqlConnection(@"Data Source='MOHAMEDEWEHISHY\SQLEXPRESS';Initial Catalog='Computer Sales System';integrated security=sspi");
try
{
SqlCommand cmd = new SqlCommand("backup_database", cn);
cmd.CommandType = CommandType.StoredProcedure;

if (System.IO.File.Exists(@"D:\SQLServerBackups\Computer Sales System.bak"))
{
System.IO.File.Delete(@"D:\SQLServerBackups\Computer Sales System.bak");
}

cn.Open();
int i = cmd.ExecuteNonQuery();

if (i < 0)
{
MessageBox.Show("Your database is backed up successfully");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("Sorry ... You can not back up your database");
}
finally
{
cn.Close();
}

so the problem is using SqlConnection with others not DML commands
i tried to use OLEDBConnection but it not gets result

So what is the solution????????

Thanks,
Mohamed El-Wehishy

Free Online SQL Server 2008 R2 Developer Training Available on MSDEV

brucedkyle11-Nov-09 5:56

brucedkyle

11-Nov-09 5:56

You can learn more about SQL Server 2008 R2 in free developer training videos on MSDEV[^]. This on demand series will walk through the new features of SQL Server 2008 R2. Topics included: Report Builder 3.0, building your ISV ETL application, ISV Operational application, looking at what R2 edition is right for you and more.

See SQL Server 2008 R2 Developer Training Series[^]

Learn more about Microsoft emerging techologies[^] on the Microsoft blog for independent software vendors (ISVs) in the United States.

Advice Needed - Regarding Data Transfer Between Databases On Seperate Servers [modified]

It_tech11-Nov-09 4:36

It_tech

11-Nov-09 4:36

Hi all,

Can any one advice on Data Transfer Between Same Databases On Seperate Servers ?

Database A on Server 197.23.xxx
Database B on server 197.23.xxxx

The run on the same network.

Any Information will be much appreciated.

Many thanks

modified on Wednesday, November 11, 2009 1:34 PM

Re: Advice Needed - Regarding Data Transfer Between Databases On Seperate Servers

_Damian S_11-Nov-09 13:34

_Damian S_

11-Nov-09 13:34

More info needed.

If they are on the same network and the servers can "see" each other, there's no reason why you couldn't transfer data... BUT... are they the same kind of database? What is it? Is it SQL Server? Access? Foxpro? DBase? A filing cabinet with a cat5 connection?

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: Advice Needed - Regarding Data Transfer Between Databases On Seperate Servers

It_tech12-Nov-09 0:44

It_tech

12-Nov-09 0:44

Thanks So Much for your response.Its Much appreciated.

Both databases are in SQL Server 2008 database with same table structures and design.
There are tables with the same structure in both database.
I would like to copy Data from table in One databse tothe other Databse.

Hennce ,I will have one Databse to put all my reporting etc.....

I would like to run it twice a day.As i am running my report twice a day.

Thanks

Re: Advice Needed - Regarding Data Transfer Between Databases On Seperate Servers

Mycroft Holmes11-Nov-09 13:43

Mycroft Holmes

11-Nov-09 13:43

Some further information is needed.

What is you database (SQL Server, Oracle, MySQL ???)
Why is the transfer needed (development sync, replicated data, integrated query)
Is this a one off operation?
Frequency of transfer
How do you expect the transfer to be initieated?

Have you investigated the following to see if they suit your needs
Linked Servers
Replication

You have some work to do before you can ask a sensible question.

Re: Advice Needed - Regarding Data Transfer Between Databases On Seperate Servers

It_tech12-Nov-09 0:39

It_tech

12-Nov-09 0:39

Re: Advice Needed - Regarding Data Transfer Between Databases On Seperate Servers

Mycroft Holmes12-Nov-09 1:22

Mycroft Holmes

12-Nov-09 1:22

I would look into replication as you seem to be able to identify 1 database as the "master". It is not a trivial subject, you will need to spend some time reading the BOL and understanding the implications of replication.

Never underestimate the power of human stupidity
RAH

Re: Advice Needed - Regarding Data Transfer Between Databases On Seperate Servers

It_tech12-Nov-09 4:12

It_tech

12-Nov-09 4:12

Sorry to bother you again.Can you please get me some links in getting this specific problem.I seem to get a general database copy procedures.

Many thanks for your help.

Efficient searching

mjackson1111-Nov-09 3:28

mjackson11

11-Nov-09 3:28

We have very large tables of price data that need to be searched efficiently. The table consists of

id - bigint (Primary Key w/ clustered index)
iCurve - bigint - links to a small table defining price curves
dtFwdDate - forward date
dtEffDate - effective date
fValue - price

The usual search is
SELECT fValue FROM table WHERE iCurve = 1 AND dtFwdDate='01/01/2010' AND dtEffDate='11/11/2009'

I created an index that keys on iCurve, dtFwdDate, dtEffDate and includes fValue.

It works okay but the index size is now larger than the data.

We have to update this table daily with very large numbers of data points (100,000+)

Questions -

What is the best way to index this table for fast searches?
How can we keep the index size small?
How can we optimize this so insertions are not painful?

In terms of performance, I would rather have performance problems inserting data.

thx

Mark Jackson

Re: Efficient searching

Covean11-Nov-09 5:02

Covean

11-Nov-09 5:02

Do you use a sql-server so I would say try to use the sql-profiler and the optimizion tool
in combination and let the optimizer make a proposal for. But the problem will be
that you will not be able to create a small index and have a good performance on it.

At last one idea is to remove the clustered index of the primary key and make it to a not
clustered one and use the clustered index on iCurve, but only if iCurve differs very often.
I read this tip some days ago in the book SQL Server 2008, but its efficent if you do not
often query for the id and if the new clustered column doesn't have too often the same
value.

Greetings
Covean

Re: Efficient searching

mjackson1111-Nov-09 8:19

mjackson11

11-Nov-09 8:19

This is in MS-SQL server BTW

Re: Efficient searching

Mycroft Holmes11-Nov-09 13:51

Mycroft Holmes

11-Nov-09 13:51

Tuning a database is an art and there is no 1 silver bullet, you can only try a number of things and see if they are worth the changes.

For a start the size of your indexes should not concern you, on a heavily indexed table this is not unusual.

If you insert is a bacth job (with 100k records is should be) then it may be worth dropping the indexes for the insert and recreating them. This is a DRASTIC measure and should be tested for cost on another server.

Consider the changes to the clustered index suggested

Consider moving the indexes to another physical drive

Partitioning your data (think archiving but you can query across partitions)

These are but some of your options, I assume you have use query profiler to asses the cost of your queries.

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