How Do I Add Time with integer value in MSSQL ?

Hi, Expert

How Do I Add Time with integer value in MSSQL ??

For Example: 07:00:50 + 20 = 07:01:10

Note: 20 is in second

Thanks in advance

Check out the DATEADD function in BOL (documentation).|||

Hi,

You can play with DATEADD function for your question, for example:

SELECT dateadd(s, 20, getdate())

this one will add 20 seconds to now.

You can look up this function from Books Online to find more information. By the way, in SQL Server there is datatime data type, but not time or date only data type.

how do 2 active statements for a connection ?

SQL Server 2000
ODBC driver
The second statement receive the error: "Connection is busy with result for
another hstmt"
There are ODBC driver for SQL Server that suport server side cursor such as
to be possible 2 active statements for a connection ?You can only have one active statement per connection.
Mike O.
"Pluta Gabriel" <Gabriel.Pluta@.deuromedia.ro> wrote in message
news:O6y$oeF3DHA.3216@.TK2MSFTNGP11.phx.gbl...

quote:

---

> SQL Server 2000
> ODBC driver
>
> The second statement receive the error: "Connection is busy with result

---

for

quote:

---

> another hstmt"
> There are ODBC driver for SQL Server that suport server side cursor such

---

as

quote:

---

> to be possible 2 active statements for a connection ?
>

---

How difficult is it to uninstall/re-install a second SQL named instance?

Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
--
SpinIt's really no different from installing or un-installing the first
instance.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f06eF2fjee4U1@.mid.individual.net...
Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
--
Spin|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.|||None that I know of.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f4a5F2dle3mU1@.mid.individual.net...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.

How difficult is it to uninstall/re-install a second SQL named instance?

Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
Spin
It's really no different from installing or un-installing the first
instance.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f06eF2fjee4U1@.mid.individual.net...
Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
Spin
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.
|||None that I know of.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f4a5F2dle3mU1@.mid.individual.net...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.

How delete a log file

I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
Thanks
Try DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:

> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks
|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','empty file')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
sql

How delete a log file

I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Jean-Paul,
Can you try detaching the database, delete the log file and then attach
the database? There are packages available within sqlserver "sp_detach_db"
and
"sp_attach_single_file_db".? Take a backup to be safe before making this
structural change.
Thanks,
Chandan Dutta
"Jean-Paul" wrote:

> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_dele
te','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach[vbcol=seagreen]
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
>

How delete a log file

I have unfortunately create a second log file for a database.
So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
I would delete DataLog2.ldf, but it's now the active log file, and the
operation can't terminate.
ThanksTry DBCC SHRINKFILE with the EMPTYFILE option.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:56283E5B-FB80-46DA-8DEE-D446F940F43C@.microsoft.com...
> I have unfortunately create a second log file for a database.
> So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> I would delete DataLog2.ldf, but it's now the active log file, and the
> operation can't terminate.
> Thanks|||Hi Chandan,
sp_attach_single_file_db command might fail if you have multiple LDF files.
As well it is risky to do this command on a production server.
Jean_paul,
You could follow the Dan Guzmans option, I can detail you the steps to
remove 1 LDF file :-
Insted of detaching the database you can empty the second LDF and Remove the
file.
How to check the number of LDF files used by a database:-
sp_helpdb <dbname>
If you have 2 LDF files ,to remove a file , you must first have the data
moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHRINKFILE command. This
will empty the file and mark it as unavailable.
See the commands to remove a ldf :-
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_delete','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
--
Thanks
Hari
MCDBA
"Chandan Dutta" <ChandanDutta@.discussions.microsoft.com> wrote in message
news:25EC1FE9-F2AB-4AD4-BE70-48D06165A384@.microsoft.com...
> Hi Jean-Paul,
> Can you try detaching the database, delete the log file and then
attach
> the database? There are packages available within sqlserver "sp_detach_db"
> and
> "sp_attach_single_file_db".? Take a backup to be safe before making this
> structural change.
> Thanks,
> Chandan Dutta
>
> "Jean-Paul" wrote:
> > I have unfortunately create a second log file for a database.
> > So i have now 1 DataFile.mdf, 1 DataLog1.ldf and 1 DataLog2.ldf
> > I would delete DataLog2.ldf, but it's now the active log file, and the
> > operation can't terminate.
> >
> > Thanks

how create mining model

i have a problem, because i have 3 tables.
One name "CallPair" contain: id- identity number, from - phone number, to - phone number.
Second name "Talk" contain: id - identity number, callPairId - id from "CallPair" table, date
Third name "Transaction" contain: id - identity number is a number of transaction, idTalk - id from Talk table, callNr - number of talk in transaction

I want mining model to sequence clustering, but i don't know how build case and nested table

If you are trying to build a model across all phone numbers to see what number a customer is likely to call next, or use perform any anaysis across all numbers, sequence clustering is not likely to work very well.

Sequence Clustering works well when you have less than 100 possible sequence states. You could categorize calls potentially and sequence those - e.g. user recieves call, doesn't answer, sends text message, makes free call, etc. and then use Sequence Clustering.

In any case, I would need to know more about the problem you are trying to solve to advise how to build a model.

How can I use several resultsets from stored procedure?

I have create stored procedure that returns 2 resultsets. When I configure OLE DB Source to use this procedure I can not add second output for he source. I get following error:

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Source [1]]: An output cannot be added to the outputs collection.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

How to add second output?

The stock OLEDB source component supports one non-error output.

To populate multiple outputs from a source component (perhaps to avoid a double extract), you can write a script source transform with as many outputs as necessary. Building multi output transforms (script and custom) is not much more difficult than a single output source component.

Furthermore, you can write a custom source tranform if you wish to re-use the code (aka toolbox resident) more easily than cut/paste re-use of script components.

If a little coding is not up your alley, then you're stuck with a single output.

Of course there are probably workarounds to simulate two outputs, such as interleaving/marking the columns of the two result sets such that they form one result set you can later conditionally split, but that is likely beside the point.

How can i use format as HH:MM:SS from seconds within the SQL query

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.

cmd ="select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;

Please help me how to format this within the Query to display in GridView.

Hi tapan.behera,

Here is an example that does what you want:

declare @.durationintset @.duration = 1082587selectcast(@.duration / 60 / 60AS nvarchar) +'h:' +cast(@.duration / 60 % 60AS nvarchar) +'m:' +cast(@.duration % 3600 % 60AS nvarchar) +'s'

Kind regards,
Wim|||

Thanks for your suggestion.

But how can i embed your variable in my sql statement.

I don't want to use it as separate variable, i want to use it within my sql statement.

i.e Select amount,call_id,duration from my Table.

So how can i use your suggestion here, please help me.

|||

hi tapan.behera,

tapan.behera@.hotmail.com:

I don't want to use it as separate variable, i want to use it within my sql statement.

I was just making an example! When you use a select statement, that will eliminate the need of a variable.

tapan.behera@.hotmail.com:

i.e Select amount,call_id,duration from my Table.

It would be something like:

select amount, call_id,cast(duration / 60 / 60AS nvarchar) +'h:' +cast(duration / 60 % 60AS nvarchar) +'m:' +cast(duration % 3600 % 60AS nvarchar) +'s'as durationfrom myTable

Kind regards,
Wim