How to insert time in MS SQL DB

hi..

i want to store value of time in the database i.e. the 'time in' and 'time out' value for a particular entry of an employee. i have take datetime as a datatype in MS SQL 2000 database and my language is vb.net. How can i store time value in my database?

There is no datatype strictly for the time. I suggest storing the entire date and time value. What if your employee clocks in at 7:00 PM and clocks out at 3:00 AM?|||You told me write to store time in datetime data type along with date. But in what format should I enter the values if I want to store the values in 24 hrs. time format?|||Well, I didn't tell you to store the data in a datetime data type, but I did suggest it. You can see from thedatetime and smalldatetime documentation that SQL Server stores the data in two 4-byte integers, the first 4 bytes store the offset from the base date (January 1, 1900). The other 4 bytes store the number of milliseconds after midnight.

You might use something like this to set a date/time as a parameter value:

myCommand.Parameters.Add("@.DateTimeIn", SqlDbType.DateTime).Value = DateTime.Parse("2006-03-04 23:01:00")

|||

Datetime values by their nature do not have a "format". It's when you convert the datetime to a string that the string has a "format". (format is quoted because, yes, of course datetime has a format, as tmorton pointed out, but it's not what most people mean)

Just to point this out further, in tmorton's MyCommand.Parameters.Add statement above, you are giving the .value a datetime object. Datetime.Parse("some string"), takes the string in a format that datetime can covert to a valid datetime object, based on your current culture's standard date and time formats. The format tmorton chose (YYYY-MM-DD HH:MM:DD.SSS) is the one least likely to give any problems because it is universally available in all cultures, unlike say MM/DD/YYYY which is available in en-US, or (guessing) DD/MM/YYYY in en-GB, or DD.MM.YYYY in fr-FR (Sorry if these aren't the correct formats for England and/or France).

|||

Hi..

I got my problem soluation but a small problem still remailns.

I am displaying my records in datagrid with code as...

<asp:Label ID="timeinlbl" Width = 30 Runat = server text='<%#DataBinder.Eval(Container,"DataItem.timein","{0:hh:mm}")%>'>

It shows me time in '08:30' format. I want to also display am/pm format. What should I add to display the format 'am/pm'?

|||

geek.net wrote:

What should I add to display the format 'am/pm'?

Starting from here:BoundColumn.DataFormatString Property, I followed the link to:Formatting Overview. From there I followed the link to:Date and Time Format Strings, and then followed the final link to:Custom DateTime Format Strings. This page should help you discover the exact format string you need.

How to Insert or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to insert multiple values to a column at a time?

Hi all,

I was looking to insert multiple values to a single column at a time in SQl.

Any help pleas!

Thank you

Ephi:

What exactly do you mean by "multiple values"? Different data types? A vector? An array? What exactly.

Dave

|||

Lets say I have a single Column called X and I want to insert multiple values into X like (1,2,3) at a time through using the insert statement.

Thank you in advance.

|||Hi,

In reality it is not acceptable according to data normalization rules.
I.e. if you want to have such behavior you should better create an additional table and have foreign constraints mapping to it.
Some example:

table_x(
field_1 .....,
field_2 .....,
field_in_which_you_want_to_have_multiple_values.....
);

table_y(
value_identifier .....,
value nvarchar(1024) ....
);

So you firstly insert several values to table 'table_y' and then just add
value_identifier to a 'table_x.field_in_which_you_want_to_have_multiple_values'|||

do you mean you want to create multiple rows, with one row for each value, using a single Insert statement?

This is not possible. Insert only creates one row in the table.

Unless, of course, you are inserting into one table using the values from another table, in which case you can use the insert...select syntax.

Normal insert syntax:

insert table_name
(col1, col2, col3)
values
(val1, val2, val3)

only inserts one row.

|||I would recommend you use Itzik Ben-Gan's Split function:

CREATE FUNCTION dbo.fn_SplitTSQL
(@.arr NVARCHAR(MAX), @.separator NVARCHAR(1) = N',') RETURNS TABLE
AS
RETURN
SELECT
n - LEN(REPLACE(LEFT(@.arr, n), @.separator, '')) + 1 AS pos,
SUBSTRING(@.arr, n,
CHARINDEX(@.separator, @.arr + @.separator, n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@.arr)
AND SUBSTRING(@.separator + @.arr, n, 1) = @.separator;Once you've got this, you could do something like:

insert into mytable (col1, col2, col3)
select 'Val1', 'Val2', element
from dbo.fn_SplitTSQL(N'1,2,3',N',')

This should handle it nicely for you. Oh yes, and you'll need a table called Nums with a field called 'n', which you have populated from 1 to some arbitrarily large number. 1000 might be big enough for most of your uses...

There's more on this at:
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt

Robsql

How to insert more then one order into customers orders table

Hi there,
How do I insert into Customers and Orders table in Northwind database like
One customer and three orders at the same time like
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
Insert into Orders (CustomerID,OrderDate)
VAULES
('abc','1/1/2006')
('abc','2/1/2006')
('abc','3/1/2006')
somthing like that -> multiple insert statement to one to many relationship
table
Thanks,
Oded DrorOded, shalom
Create a stored procedure and issue
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060101')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060102')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060103')
COMMIT TRANSACTION
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
> Hi there,
> How do I insert into Customers and Orders table in Northwind database like
> One customer and three orders at the same time like
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate)
> VAULES
> ('abc','1/1/2006')
> ('abc','2/1/2006')
> ('abc','3/1/2006')
> somthing like that -> multiple insert statement to one to many
> relationship table
> Thanks,
> Oded Dror
>
>|||I did something like this
Insert into Orders (CustomerID,OrderDate)
select 'abc','1/1/2006' union
select 'abc','2/1/2006' union
select 'abc','3/1/2006'
not tested but it should work.
Grant
Who gives a {censored} if I am wrong.
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
> Hi there,
> How do I insert into Customers and Orders table in Northwind database like
> One customer and three orders at the same time like
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate)
> VAULES
> ('abc','1/1/2006')
> ('abc','2/1/2006')
> ('abc','3/1/2006')
> somthing like that -> multiple insert statement to one to many
> relationship table
> Thanks,
> Oded Dror
>
>|||Uri,
The problem is when you enter a new Customer with Identity Key set to yes
How do you assign the CustomerID (you don't know what is it!) to the
Orders.CustomerID table
What I did is after finishing inserts command I added
update tblOrders set CustomerID =(select top 1 CustomerID from tblCustomers
order by CustomerID Desc)
where CustomerID IS NULL
That way I'm assigning the tblCustomer.CustomerID to tblOrders.CustomerID
It work but I'm not sure if is it the right solution?
Thanks,
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23vaqvKTGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Oded, shalom
> Create a stored procedure and issue
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060101')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060102')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060103')
> COMMIT TRANSACTION
>
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
>|||Hi, Oded
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DECLARE @.new_custid INT
BEGIN TRANSACTION
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
SELECT @.new_custid=SCOPE_IDENTITY()
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
(@.new_custid,'abc','20060101')
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
(@.new_custid,'abc','20060102')
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES (@.new_custid
,'abc','20060103')
COMMIT TRANSACTION
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OjOoLxOTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Uri,
> The problem is when you enter a new Customer with Identity Key set to yes
> How do you assign the CustomerID (you don't know what is it!) to the
> Orders.CustomerID table
> What I did is after finishing inserts command I added
> update tblOrders set CustomerID =(select top 1 CustomerID from
> tblCustomers order by CustomerID Desc)
> where CustomerID IS NULL
> That way I'm assigning the tblCustomer.CustomerID to tblOrders.CustomerID
> It work but I'm not sure if is it the right solution?
> Thanks,
> Oded
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e%23vaqvKTGHA.1728@.TK2MSFTNGP11.phx.gbl...
>|||Uri,
When I did
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DECLARE @.new_custid int
BEGIN TRANSACTION
Insert into tblCustomers (FirstName,LastName,State)
Values('Bill','Gates','2')
SELECT @.new_custid = SCOPE_IDENTITY()
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('2','@.new_custid','111')
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('4','@.new_custid','222')
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('23','@.new_custid','333')
COMMIT TRANSACTION
I got an error message:
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '@.new_custid' to data
type int.
Thanks,
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxqp06OTGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi, Oded
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> DECLARE @.new_custid INT
> BEGIN TRANSACTION
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> SELECT @.new_custid=SCOPE_IDENTITY()
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
> (@.new_custid,'abc','20060101')
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
> (@.new_custid,'abc','20060102')
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES (@.new_custid
> ,'abc','20060103')
> COMMIT TRANSACTION
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:OjOoLxOTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>|||Yes, sure .

> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2','@.new_custid','111')
Why did you put an quotation on '@.new_custid'? Is it VARCHAR(n) or INTEGER?
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('2',@.new_custid,'111')
"Oded Dror" <odeddror@.cox.net> wrote in message
news:u404$kVTGHA.4520@.TK2MSFTNGP10.phx.gbl...
> Uri,
> When I did
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> DECLARE @.new_custid int
> BEGIN TRANSACTION
> Insert into tblCustomers (FirstName,LastName,State)
> Values('Bill','Gates','2')
> SELECT @.new_custid = SCOPE_IDENTITY()
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2','@.new_custid','111')
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('4','@.new_custid','222')
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('23','@.new_custid','333')
> COMMIT TRANSACTION
> I got an error message:
> Msg 245, Level 16, State 1, Line 5
> Conversion failed when converting the varchar value '@.new_custid' to data
> type int.
> Thanks,
> Oded
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxqp06OTGHA.1688@.TK2MSFTNGP11.phx.gbl...
>|||Uri
It works!
Thanks you very much
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uzbR9oXTGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Yes, sure .
>
> Why did you put an quotation on '@.new_custid'? Is it VARCHAR(n) or
> INTEGER?
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2',@.new_custid,'111')
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:u404$kVTGHA.4520@.TK2MSFTNGP10.phx.gbl...
>sql

How To Insert into a table with a uniqueidentifier as primary key?

I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId

My VB code is this.

ProtectedSub btncreate_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btncreate.Click'set connection stringDim errstrAsString =""Dim conn =New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")'set parameters for SPDim cmdcommand =New SqlCommand("sprocInsertNewReport", conn)

cmdcommand.commandtype = CommandType.StoredProcedure

cmdcommand.parameters.add(

"@.UserName", Session("UserName"))

cmdcommand.parameters.add(

"@.Week", vbNull)

cmdcommand.parameters.add(

"@.Date", vbDate)

cmdcommand.parameters.add(

"@.StartTime", vbNull)

cmdcommand.parameters.add(

"@.EndTime", vbNull)

cmdcommand.parameters.add(

"@.HeatTicket", vbNull)

cmdcommand.parameters.add(

"@.Description", vbNull)

cmdcommand.parameters.add(

"@.TakenAs", vbNull)

cmdcommand.parameters.add(

"@.Dinner", vbNull)

cmdcommand.parameters.add(

"@.Hours", vbNull)

cmdcommand.parameters.add(

"@.Rate", vbNull)

cmdcommand.parameters.add(

"@.PayPeriod", vbNull)

cmdcommand.parameters.add(

"@.LastSave", vbNull)

cmdcommand.parameters.add(

"@.Submitted", vbNull)

cmdcommand.parameters.add(

"@.Approved", vbNull)

cmdcommand.parameters.add(

"@.PagerDays", vbNull)

cmdcommand.parameters.add(

"@.ReportEnd", vbNull)Try'open connection here

conn.Open()

'Execute stored proc

cmdcommand.ExecuteNonQuery()

Catch exAs Exception

errstr =

""'An exception occured during processing.'Print message to log file.

errstr =

"Exception: " & ex.MessageFinally'close the connection immediately

conn.Close()

EndTryIf errstr =""Then

Server.Transfer(

"TimeSheetEntry.aspx")EndIf

My SP looks like this

ALTER PROCEDURE

sprocInsertNewReport

@.UserNamenvarchar(256),

@.Week

Int,

@.Date

Datetime,

@.StartTime

Datetime,

@.EndTime

DateTime,

@.HeatTicket

int,

@.Description

nvarchar(max),

@.TakenAs

nchar(10),

@.Dinner

Nchar(10),

@.Hours

Float,

@.Rate

Float,

@.PayPeriod

int,

@.LastSave

Datetime,

@.Submitted

Datetime,

@.Approved

DateTime,

@.PagerDays

int,

@.ReportEnd

DateTimeASINSERT INTO

ReportDetails

(

rpUserName,

rpWeek,

rpDate,

rpStartTime,

rpEndTime,

rpHeatTicket,

rpTicketDescription,

rpTakenAs,

rpDinnerPremium,

rpHours,

rpRate,

rpPayPeriod,

rpLastSaveDate,

rpSubmittedDate,

rpApprovedDate,

rpPagerDays,

rpReportDueDate

)

VALUES

(

@.Username,

@.Week,

@.Date,

@.StartTime,

@.EndTime,

@.HeatTicket,

@.Description,

@.TakenAs,

@.Dinner,

@.Hours,

@.Rate,

@.PayPeriod,

@.LastSave,

@.Submitted,

@.Approved,

@.PagerDays,

@.ReportEnd

)

RETURN

Any Ideas?

thx!

I am not sure what you mean by "go up one each time." Uniqueidentifiers don't work that way. You have three choices:

1 - generate a guid on the front end and pass it to the proc as the id

2 - Generate the guid within the proc

3 - set the default value for the key field to newid()

|||Why not use an identity column?|||

mpswaim:

Why not use an identity column?

Good question because a GUID is 16bytes binary data type to be used with care, while IDENTITY is INT and a property to the column.

|||

I am using VS2005 Server Explorer, is there an option to set and say int to an identity?

I do not see any ways to do so

|||

I changed it to an int and found the as Identity property and it works!

Thanks for the help!

|||

You can do it with SQL Management Studio (I believe that there's a free version available), or Enterprise Manager. Unfortunately I don't see a good way to alter a table to make an existing column an identity column.

|||You just do ANSI SQL ALTER table SET IDENTITY because IDENTITY is defined in ANSI SQL. Hope this helps.

How to insert auto increment?

Hi Expert,
How can I do this without enter 100 time every day?
insert into [tablename] (column1) values(1)
insert into [tablename] (column1) values(2)
....
insert into [tablename] (column1) values(100)
Thank you all for reply-MN
MN wrote:
> Hi Expert,
> How can I do this without enter 100 time every day?
> insert into [tablename] (column1) values(1)
> insert into [tablename] (column1) values(2)
> ...
> insert into [tablename] (column1) values(100)
> Thank you all for reply-MN
Why do you want to generate 100 rows per day if that's the only column?
Just increment a single value instead. If there are other columns
involved then maybe you can use an IDENTITY column.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi David,
Thank for reply. Yeah, some day I increate 100 rows, someday 90, or 10...the
value is vary. And there are no other column involved except IDENTITY column.
How can I do that? Regards-MN
"David Portas" wrote:

> MN wrote:
> Why do you want to generate 100 rows per day if that's the only column?
> Just increment a single value instead. If there are other columns
> involved then maybe you can use an IDENTITY column.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||MN wrote:
> Hi David,
> Thank for reply. Yeah, some day I increate 100 rows, someday 90, or 10...the
> value is vary. And there are no other column involved except IDENTITY column.
> How can I do that? Regards-MN
Don't. There are two good reasons. 1. It's inefficient (because a
single row will do the same thing). 2. It may be unreliable (an
IDENTITY sequence can have gaps so the maximum value doesn't
necessarily match the number of rows).
Instead, use a single row:
CREATE TABLE tbl (x INTEGER PRIMARY KEY DEFAULT (1) CHECK (x=1) /*
single row constraint */, col1 INTEGER NOT NULL);
INSERT INTO tbl (col1) VALUES (0);
GO
Then keep updating it like this:
UPDATE tbl SET col1 = col1 + 100 ;
In case you do find it useful again, you can populate a table with
default values only or an IDENTITY column only using the DEFAULT VALUES
clause:
INSERT INTO tbl DEFAULT VALUES;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Tue, 4 Apr 2006 13:16:01 -0700, MN wrote:

>Hi Expert,
>How can I do this without enter 100 time every day?
>insert into [tablename] (column1) values(1)
>insert into [tablename] (column1) values(2)
>...
>insert into [tablename] (column1) values(100)
>Thank you all for reply-MN
Hi MN,
I'd very much like to know why you need to do that....
But the asnwer is: make a permanent table of numbers in your database
(see http://www.aspfaq.com/show.asp?id=2516), and use that:
INSERT INTO tablename (column1)
SELECT Numbers.Number
FROM dbo.Numbers
WHERE Numbers.Number BETWEEN 1 AND 100
Hugo Kornelis, SQL Server MVP
sql

How to insert a datetime to sql sever

Hi everyone,

I need help in inserting a date time string to sql sever. For example, how do you insert textbox1.text="2006-08-30 09:00:00" into a datatable column names starttime (type datetime) in sql sever? How do I covert the format of this string before I do the insert?

Thanks.

a123.

You don't have to pass that value. In the colum starttime set the data type to 'starttime' then for the default value enter getdate()

That should fix your problem.

|||

That would be an acceptable format (YYYY-MM-DD HH:mm:ss), of course you lose the sub-second precision though. If you always want to insert the current datetime, then as the previous poster said, change your insert statement to use getdate() rather than take the value from a passed parameter.

How to increment field after selecting it

I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?

This is my existing code:

alterPROCEDURE dbo.SP_FeaturedClassifieds

@.PageIndexINT,

@.NumRowsINT,

@.FeaturedClassifiedsCountINTOUTPUT

AS

BEGIN

select @.FeaturedClassifiedsCount=(SelectCount(*)From classifieds_AdsWhere AdStatus=100And Adlevel=50)

Declare @.startRowIndexINT;

Set @.startRowIndex=(@.PageIndex* @.NumRows)+ 1;

With FeaturedClassifiedsas(

SelectROW_NUMBER()OVER(OrderBy FeaturedDisplayedCount*(1-(Weight-1)/100)ASC)as

Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

classifieds_Ads

Where

AdStatus=100And AdLevel=50

)

Select

Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

FeaturedClassifieds

Where

Rowbetween

@.startRowIndexAnd @.startRowIndex+@.NumRows-1

END

Hello rfurdzik,

Am I correct that you want to update the counter in the table Classified_Ads? Try to add an update statement before the last select statement :

UPDATE Classified_Ads SET FeaturedDisplayedCount = FeaturedDisplayedCount + 1

FROM FeaturedClassifieds

WHERE FeaturedClassifieds.Id = Classified_Ads.Id

AND Row between (@.startRowIndex AND @.startRowIndex + @.NumRows - 1)

How to improve report performance

Hi,
I have created reports in CRXI with Dynamic & Cascading Prompt. My reports are taking long time 15-20 mins to refressh.
Can sombdy help me to improve my reports performance ?Can Sombdy help me?|||Can anybdy answer this question|||how many records is it selecting? it shows you in the bottom right corner|||When I run the foolowing Query. It runs fine & gives 199 records

SELECT distinct clients.uid,max(da_answer.date_effective) as date,entry_exit.provider_id as provider,
datediff(yyyy,da_answer_val_unsec.val_date,getdate()) as age,
convert(varchar,DATEDIFF ( yyyy,val_date, getdate())) vage,
Case
when DATEDIFF ( yyyy,val_date, getdate()) between 5 and 12 then '5-12'
when datediff(yyyy,val_date,getdate()) between 13 and 17 then '13-17'
when datediff(yyyy,val_date,getdate()) between 18 and 44 then '18-44'
when datediff(yyyy,val_date,getdate()) between 45 and 64 then '45-64'
else 'Over 65'
end as Agegroup

FROM (((clients INNER JOIN da_answer ON clients.uid=da_answer.client_id) INNER JOIN
(da_assessment_question INNER JOIN da_question ON
da_assessment_question.question_id=da_question.uid) ON
da_answer.question_id=da_question.uid) INNER JOIN entry_exit ON
da_answer.client_id=entry_exit.client_id) INNER JOIN da_answer_val_unsec ON da_answer.uid=da_answer_val_unsec.answer_id

WHERE da_answer.date_effective IN( select date_effective FROM da_answer a
WHERE date_effective = (select max(b.date_effective)
FROM da_answer b WHERE b.uid=a.uid) and da_question.uid=893 AND clients.inactive=0 AND
da_assessment_question.assessment_id=21 AND entry_exit.inactive=0)
AND
(((entry_exit.entry_date IS NOT NULL)
AND
(entry_exit.entry_date>={ts '2005-01-01 00:00:00'} AND
entry_exit.entry_date<={ts '2005-03-31 23:59:00'})) and
((entry_exit.exit_date is null) or not
((entry_exit.exit_date >{ts '2004-07-01 00:00:00'} and entry_exit.exit_date <{ts
'2004-12-31 23:59:00'}))))
group by clients.uid,da_answer_val_unsec.val_date,entry_exit.provider_id

After this query I got the clients who have same date_effective but I need distinct clients with max(date_effective) so I create the above query as view(AGE) & applied the following query to this view

select a.uid,a.agegroup,a.provider
from age a
where date in(select max(b.date)
from age b
where a.uid=b.uid
group by uid)
order by a.uid
This query takes 10-15mins to run in sql designer & gives 192 records.|||Have you analysed the execution of your query. It seems that the query optimizer in your DBMS does not work properly when you make the query using the view. Check the plan and make sure query uses indexes and does not perform full table joins.

- Jukka|||Hi

My performance problem is caused by ineffiecient generated HTML - for each item in the reports' table a <DIV><TABLE><TR><TD><TABLE><TR><TD> is generated (??!!) hard to believe !!

Multiply this by a few thousand items to be displayed and you've got a catastrophe.

How can I work around this ??

Thanks

David|||The HTML generated by Crystal is really ugly. We have worked around it by using the pdf format.

How to implement paging

My report is taking long time to display hundered of records.So we want to display 15 records per page and Next page link at the end of report.Please help me how to do this.

Not sure about the next page link but to control the number of rows per page

put your table control inside a list

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,
Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:

> Is there a way to identify a specific query - such as by begin time or user
> account - and then be able to kill the query programaticaly. We have a web
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table). I
> would like a way a user can kill their query.
> Thanks,
|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
[vbcol=seagreen]
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement .
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas

"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:

> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
..[vbcol=seagreen]
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
user[vbcol=seagreen]
web[vbcol=seagreen]
table). I[vbcol=seagreen]

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:
> Is there a way to identify a specific query - such as by begin time or user
> account - and then be able to kill the query programaticaly. We have a web
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table). I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement .
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or user
> > account - and then be able to kill the query programaticaly. We have a web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one table). I
> > would like a way a user can kill their query.
> >
> > Thanks,|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or
user
> > account - and then be able to kill the query programaticaly. We have a
web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one
table). I
> > would like a way a user can kill their query.
> >
> > Thanks,

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:

> Is there a way to identify a specific query - such as by begin time or use
r
> account - and then be able to kill the query programaticaly. We have a w
eb
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table).
I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done b
y
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
[vbcol=seagreen]
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:

> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.[vbcol=seagreen]
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>
user[vbcol=seagreen]
web[vbcol=seagreen]
table). I[vbcol=seagreen]