hello everyone
I have table that look like
CREATE TABLE GuestAccount
(
Id_Guest smallint,
SeqNo int IDENTITY(1,1),
account money Null
PRIMARY KEY(Id_Guest,SeqNo)
)
Q?
I want my SeqNo start from 1 for new Id_Guest
is it possible ?
tnanks
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
Hi all,
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
At the moment I already added the DataSet I'm gonna be using. I have 2 date parameters Start Time and End Time.
What I would like to do for the report is to only pull results greater than 48 hours to the report
How can I accomplish this?
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
Hello everybody,
i'm not sure if this is the right forum for my question but i didn't know where to post else.
I changed the starting value of a identity column to 30000000 and inserted about 800 values. Then i've deleted these values and set the starting value back to 1.
But the next value i inserted got the ID 30000801.
Is there a way to set this starting value back?
-------------------
using Server 2000
Hi.
I am doing a left join insert from two tables, and i know my ID is the Indentity column. I am doing something wrong and it has to do with the identity issue. I have set the identity to on, b/c i currently have data in the table (tblProduct) and I am inserting all the records not in that field from a table named Complete_Products. Can someone please examine this query and tell me what I am doing wrong? I think I need to set a start value for the identity but not totally confident on how to do that.
Thanks. Below is my query with error.
Set Identity_Insert tblProduct on
INSERT INTO tblProduct
(productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table 'tblProduct' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
I am using VS.net (2003) SQLcommand TEXT. with input params.
SQL server 2000
Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.
My code so far that works... but Where do I put @@IDENTITY ?
How do I call or assign the @@IDENTITY value to a value in my aspx.vb code page?
Question: how do I get the Identity value from the ID column.
Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help...
---------------------
INSERT INTO App
(AppName, Acronym, Description,bla bla bla bla........)
VALUES (@AppName, @Acronym, @Description, bla bla bla bla........)
-----------------------------
Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click
With cmdAddApp
'.Parameters("@app_id").Value = Session("App_ID")
.Parameters("@AppName").Value = txtAppName.Text
.Parameters( bla bla bla.............
.Parameters( bla bla bla.............
.Parameters( bla bla bla.............
End With
Try
cnAppKBdata.Open()
cmdAddApp.ExecuteNonQuery()
cnAppKBdata.Close()
''''''''''''''Session("App_ID") = whatever the @@IDENTITY is....'''''''''''''''''''''''''''??
Response.Redirect("AppUpdate.asp")
Catch ex As Exception
End Try
End Sub
Anyone have the lines of code that does this?
Any advise or examples :) thanks you.
Does anyone know how to get the indentity of the last updated record in a table. I need it for a cascading trigger.
Thank you,
Jody
i have a table with the following fiels ,
Column NameDataType
Sno intIdentity = Yes , Identity Speed =1 , Identity Increament =1
namechar
Now i entered data and coneected to database, worked.Now after cheking all the data entered with the form , now i have to send the table to client place.
The problem is , the sno column has the value which i entered last. now if i delete all records , then also the record no doesnot become 0.
what i have to do, to set the sno column to 1 again.
Hi, I have 2 tables in my database PrescriptionHeader and PrescriptionDetails.My PrescriptionHeader table has the following fields:PrescriptionID -identity fieldPatientID PatientfNamePatientlname PrescriptionDetails table has the following fields:PrescriptionDetailID -identityPrescriptionID -from PrescriptionHeader table MedicineDosage The function InsertPrescription inserts values into the table PrescriptionHeader. I want the same function to then insert the value of MedicineDosage into PrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?ThanksFunction InsertPrescription(ByVal PatientID As String, _ByVal PatientFname As String, _ByVal Patientlname As String, ByVal MedicineDosage as String)Dim DBAdapter As SqlDataAdapterDim DBDataSet As DataSetDim SQLString As StringDim DBCommandBuilder As SqlCommandBuilderSQLString = "SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"DBAdapter = New SqlDataAdapter(SQLString, DBConnection)DBDataSet = New DataSetDBAdapter.Fill(DBDataSet)Dim AddedRow As DataRow = DBDataSet.Tables(0).NewRow()AddedRow("PatientID") = PatientIDAddedRow("PatientfName") = PatientFnameAddedRow("Patientlname") = PatientlnameDBDataSet.Tables(0).Rows.Add(AddedRow)DBCommandBuilder = New SqlCommandBuilder(DBAdapter)DBAdapter.Update(DBDataSet) End Function
View 1 Replies View RelatedOk I've been researching this for a day now and I'm not coming up with much. I want to store the auto-incrementing ID of the last inserted record in a session variable, so that I may put it in a foreign key column in another table, if the user wishes to fill out a form on another page. I think my stored procedure is correct. But don't know what code to add to my aspx page. Any help will be greatly appreciated.
Here is my VB ScriptProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim personalContactDataSource As New SqlDataSource()personalContactDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DataConnectionString1").ToString()
personalContactDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
personalContactDataSource.InsertCommand = "PersonalContactInsert"
personalContactDataSource.InsertParameters.Add("FirstName", FirstName.Text)
personalContactDataSource.InsertParameters.Add("LastName", LastName.Text)personalContactDataSource.InsertParameters.Add("KeyPerson", KeyPerson.Checked)
personalContactDataSource.InsertParameters.Add("DayPhone", DayPhone.Text)personalContactDataSource.InsertParameters.Add("EveningPhone", EveningPhone.Text)
personalContactDataSource.InsertParameters.Add("Fax", Fax.Text)personalContactDataSource.InsertParameters.Add("Email", Email.Text)
personalContactDataSource.InsertParameters.Add("HomeAddress", HomeAddress.Text)personalContactDataSource.InsertParameters.Add("City", City.Text)
personalContactDataSource.InsertParameters.Add("State", State.Text)personalContactDataSource.InsertParameters.Add("Zip", Zip.Text)
personalContactDataSource.InsertParameters.Add("ReqEffectDate", ReqEffectDate.Text)personalContactDataSource.InsertParameters.Add("MRID", MRID.Text)
personalContactDataSource.InsertParameters.Add("CurrentPremium", CurrentPremium.Text)personalContactDataSource.InsertParameters.Add("CurrentCarrier", CurrentCarrier.Text)
personalContactDataSource.InsertParameters.Add("CurrentDeductible", CurrentDeductible.Text)personalContactDataSource.InsertParameters.Add("CurrentCoins", CurrentCoins.Text)personalContactDataSource.InsertParameters.Add("ReasonForQuote", ReasonForQuote.Text)
End Sub
And here is my Stored ProcALTER PROCEDURE dbo.PersonalContactInsert
@FirstName varchar(30),@LastName varchar(30),
@DayPhone varchar(14),@EveningPhone varchar(14),
@Fax varchar(14),@Email varchar(60),
@HomeAddress varchar(80),@City varchar(30),
@State char(2),@Zip char(5),
@KeyPerson bit,@ReqEffectDate smalldatetime,
@CurrentCarrier varchar(30),@CurrentPremium smallmoney,
@CurrentDeductible smallmoney,@CurrentCoins smallmoney,
@ReasonForQuote varchar(150),@MRID int,
@ClientNumber int OUT
AS
INSERT INTO PersonalContact(FirstName, LastName, DayPhone, EveningPhone, Fax, Email, HomeAddress, City, State, Zip, KeyPerson, ReqEffectDate, CurrentCarrier, CurrentPremium, CurrentDeductible, CurrentCoins, ReasonForQuote, MRID, DateTimeStamp)
VALUES(@FirstName,@LastName,@DayPhone,@EveningPhone,@Fax,@Email,@HomeAddress,@City,@State,@Zip,@KeyPerson,@ReqEffectDate,@CurrentCarrier,@CurrentPremium,@CurrentDeductible,@CurrentCoins,@ReasonForQuote,@MRID, GetDate())
SET @ClientNumber = SCOPE_IDENTITY()
RETURN
I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.
View 3 Replies View RelatedBest way to retrieve the identity key of the record just inserted?This question is for discussion purposes; the business process that spurred the question is currently working.Using SQL Server 2008 R2, a record is inserted from a stored procedure. Let's say the sp has something like this:
Code:
BEGIN
BEGIN TRANSACTION
INSERT INTO tblTools
([Desc],CreateDate,Model,CreatedBy,Notes)
[code]....
In Access, when you add a new record to the recordset, the identity field comes "pre-populated" making it east to get the actual, correct identity value assigned to the record you are inserting. In SQL Server, I know options include:
Code:
IDENT_CURRENT('tblX')
SCOPE_IDENTITY
@@IDENTITY
among other methods.
Each has pros and cons, such as user privileges (IDENT_CURRENT requires the user to have Select privileges on the table, and catches records created by other things, such as users and triggers), and the other two give you the last key inserted and don't allow specifying the object (which is a problem if the insert added records to multiple tables, or you have multiple inserts).
Hello,
I am using Access with tables linked to sql server 2005. I need the identity to be displayed directly when entering data so that i can enter enter data into other tables using this identity on the form without the need to go back and forward to other records to display the identity. Is there a feature in Sql server 2005 which permits this?
Thank you
There are a lot of questions posted on SQLTEAM asking how to find the beginning of various time periods. The script will create and demo 14 functions that return a datetime for the beginning of a time period relative to the datetime value passed in parameter @DAY.
I put together this script to create these functions for several reasons:
1. To allow people to find them on their own without having to post a question.
2. To allow posted questions to be answered with a reference to this script.
3. To document algorithms that work for the widest possible range of datetime values. All except for the Century and Decade functions work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997. The Century is limited to datetimes from 1800/01/01 forward, because 1700/01/01 is not valid in SQL Server. The Decade function is limited to datetimes from 1760/01/01 forward, because 1750/01/01 is not valid in SQL Server.
4. And last, you can actually use them on your application.
The function names created by this script are:
dbo.F_START_OF_CENTURY( @DAY )
dbo.F_START_OF_DECADE( @DAY )
dbo.F_START_OF_YEAR( @DAY )
dbo.F_START_OF_QUARTER( @DAY )
dbo.F_START_OF_MONTH( @DAY )
dbo.F_START_OF_DAY( @DAY )
dbo.F_START_OF_HOUR( @DAY )
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
dbo.F_START_OF_X_MIN( @DAY )
dbo.F_START_OF_MINUTE( @DAY )
dbo.F_START_OF_SECOND( @DAY )
There is a separate post for function dbo.F_START_OF_WEEK to find the first day of the week at this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
This script was tested with SQL Server 2000 only.
I posted a script for End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
Edit 2006-11-01:
Added dbo.F_START_OF_X_MIN( @DAY ) at the suggestion of Peter.
Edit 2007-02-24:
Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code:
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
/*
Functions created by this script:
dbo.F_START_OF_CENTURY( @DAY )
dbo.F_START_OF_DECADE( @DAY )
dbo.F_START_OF_YEAR( @DAY )
dbo.F_START_OF_QUARTER( @DAY )
dbo.F_START_OF_MONTH( @DAY )
dbo.F_START_OF_DAY( @DAY )
dbo.F_START_OF_HOUR( @DAY )
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
dbo.F_START_OF_MINUTE( @DAY )
dbo.F_START_OF_SECOND( @DAY )
*/
go
if objectproperty(object_id('dbo.F_START_OF_CENTURY'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_CENTURY end
go
create function dbo.F_START_OF_CENTURY
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_CENTURY
Finds start of first day of century at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes >= 1800-01-01 00:00:00.000
Returns null if @DAY < 1800-01-01 00:00:00.000
*/
begin
declare @BASE_DAY datetime
select @BASE_DAY = '18000101'
IF @DAY < @BASE_DAY return null
return dateadd(yy,(datediff(yy,@BASE_DAY,@DAY)/100)*100,@BASE_DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_DECADE'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_DECADE end
go
create function dbo.F_START_OF_DECADE
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_DECADE
Finds start of first day of decade at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes >= 1760-01-01 00:00:00.000
Returns null if @DAY < 1760-01-01 00:00:00.000
*/
begin
declare @BASE_DAY datetime
select @BASE_DAY = '17600101'
IF @DAY < @BASE_DAY return null
return dateadd(yy,(datediff(yy,@BASE_DAY,@DAY)/10)*10,@BASE_DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_YEAR'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_YEAR end
go
create function dbo.F_START_OF_YEAR
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_YEAR
Finds start of first day of year at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(yy,datediff(yy,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_QUARTER'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_QUARTER end
go
create function dbo.F_START_OF_QUARTER
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_QUARTER
Finds start of first day of quarter at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(qq,datediff(qq,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_MONTH'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_MONTH end
go
create function dbo.F_START_OF_MONTH
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_MONTH
Finds start of first day of month at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mm,datediff(mm,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_DAY'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_DAY end
go
create function dbo.F_START_OF_DAY
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_DAY
Finds start of day at 00:00:00.000
for input datetime, @DAY.
Valid for all SQL Server datetimes
*/
begin
return dateadd(dd,datediff(dd,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_HOUR'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_HOUR end
go
create function dbo.F_START_OF_HOUR
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_HOUR
Finds beginning of hour
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(hh,datediff(hh,0,@DAY),0)
end
go
if objectproperty(object_id('dbo.F_START_OF_30_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_30_MIN end
go
create function dbo.F_START_OF_30_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_30_MIN
Finds beginning of 30 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/30)*30,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_20_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_20_MIN end
go
create function dbo.F_START_OF_20_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_20_MIN
Finds beginning of 20 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/20)*20,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_15_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_15_MIN end
go
create function dbo.F_START_OF_15_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_15_MIN
Finds beginning of 15 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/15)*15,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_10_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_10_MIN end
go
create function dbo.F_START_OF_10_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_10_MIN
Finds beginning of 10 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/10)*10,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_05_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_05_MIN end
go
create function dbo.F_START_OF_05_MIN
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_05_MIN
Finds beginning of 5 minute period
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(mi,(datepart(mi,@Day)/5)*5,dateadd(hh,datediff(hh,0,@Day),0))
end
go
if objectproperty(object_id('dbo.F_START_OF_X_MIN'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_X_MIN end
go
create function dbo.F_START_OF_X_MIN
(
@DAY datetime,
@INTERVAL int
)
returns datetime
as
/*
Function: F_START_OF_X_MIN
Finds beginning of @INTERVAL minute period
for input datetime, @DAY.
If @INTERVAL = zero, returns @DAY.
Valid for all SQL Server datetimes.
*/
begin
-- Prevent divide by zero error
if @INTERVAL = 0 return @DAY
declare @BASE_DAY datetime
set @BASE_DAY = dateadd(dd,datediff(dd,0,@Day),0)
return dateadd(mi,(datediff(mi,@BASE_DAY,@Day)/@INTERVAL)*@INTERVAL,@BASE_DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_MINUTE'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_MINUTE end
go
create function dbo.F_START_OF_MINUTE
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_MINUTE
Finds beginning of minute
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(ms,-(datepart(ss,@DAY)*1000)-datepart(ms,@DAY),@DAY)
end
go
if objectproperty(object_id('dbo.F_START_OF_SECOND'),'IsScalarFunction') = 1
begin drop function dbo.F_START_OF_SECOND end
go
create function dbo.F_START_OF_SECOND
( @DAY datetime )
returns datetime
as
/*
Function: F_START_OF_SECOND
Finds beginning of second
for input datetime, @DAY.
Valid for all SQL Server datetimes.
*/
begin
return dateadd(ms,-datepart(ms,@DAY),@DAY)
end
go
/*
Start of test script
Load dates to test F_START_OF functions
*/
declare @test_dates table ( DT datetime not null primary key clustered )
declare @today varchar(10)
select @today = convert(varchar(10),getdate(),112)
declare @year varchar(4)
select @year = convert(varchar(4),year(getdate()))
declare @lyear varchar(10)
select @lyear = convert(varchar(10),getdate()-345,112)
insert into @test_dates (DT)
select DT = getdate()union all
select '17530101 00:00:00.000'union all
-- Test start of Decade cutoff
select '17591231 23:59:59.997'union all
select '17600101 23:04:59.997'union all
-- Test start of Century cutoff
select '17991231 23:59:59.997'union all
select '18000101 00:00:00.000'union all
-- Test start of Decade and Century
select '19000101 00:00:00.000'union all
select '19001231 23:59:59.997'union all
select '19400101 00:00:00.000'union all
select '19491231 23:59:59.997'union all
select '19900101 00:00:00.000'union all
select '19991231 23:59:59.997'union all
-- For start of Hour testing
select @lyear+' 00:00:00.000'union all
select @lyear+' 00:59:59.997'union all
select @lyear+' 01:00:00.000'union all
select @lyear+' 01:59:59.997'union all
select @lyear+' 12:00:00.000'union all
select @lyear+' 12:59:59.997'union all
select @lyear+' 17:00:00.000'union all
select @lyear+' 17:59:59.997'union all
select @lyear+' 23:00:00.000'union all
select @lyear+' 23:59:59.997'union all
-- For start of Month, Quarter, and Year testing
select @year+'0101 00:00:00.000'union all
select @year+'0131 23:59:59.997'union all
select @year+'0201 00:00:00.000'union all
select @year+'0228 23:59:59.997'union all
select @year+'0301 00:00:00.000'union all
select @year+'0331 23:59:59.997'union all
select @year+'0401 00:00:00.000'union all
select @year+'0430 23:59:59.997'union all
select @year+'0501 00:00:00.000'union all
select @year+'0531 23:59:59.997'union all
select @year+'0601 00:00:00.000'union all
select @year+'0630 23:59:59.997'union all
select @year+'0701 00:00:00.000'union all
select @year+'0731 23:59:59.997'union all
select @year+'0801 00:00:00.000'union all
select @year+'0831 23:59:59.997'union all
select @year+'0901 00:00:00.000'union all
select @year+'0930 23:59:59.997'union all
select @year+'1001 00:00:00.000'union all
select @year+'1031 23:59:59.997'union all
select @year+'1101 00:00:00.000'union all
select @year+'1130 23:59:59.997'union all
select @year+'1201 00:00:00.000'union all
select @year+'1231 23:59:59.997'union all
-- Test start of 5, 10, 15, 20, and 30 min testing
select @today+' 23:04:59.997'union all
select @today+' 23:09:59.997'union all
select @today+' 23:14:59.997'union all
select @today+' 23:19:59.997'union all
select @today+' 23:24:59.997'union all
select @today+' 23:29:59.997'union all
select @today+' 23:34:59.997'union all
select @today+' 23:39:59.997'union all
select @today+' 23:44:59.997'union all
select @today+' 23:49:59.997'union all
select @today+' 23:54:59.997'union all
select @today+' 23:59:59.997'union all
select '99991231 23:59:59.997'
order by
1
-- Convert dates in @test_dates table to test F_START_OF functions
select
TYPE = 'CENTURY' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_CENTURY( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'DECADE' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_DECADE( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'YEAR' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_YEAR( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'QUARTER' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_QUARTER( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'MONTH' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_MONTH( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'DAY' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_DAY( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'HOUR' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_HOUR( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '30_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_30_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '20_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_20_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '15_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_15_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '10_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_10_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = '05_MIN' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_05_MIN( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'MINUTE' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_MINUTE( DT ),121)
from
@test_dates
order by
DT
select
TYPE = 'SECOND' ,
DT = convert(varchar(23),DT,121),
FUNCTION_RESULT =
convert(varchar(23),dbo.F_START_OF_SECOND( DT ),121)
from
@test_dates
order by
DT
/*
End of test script
*/
CODO ERGO SUM
i recently found a little error in a stored procedure that was included in a project handed over to me....
the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)
so i changed to...
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = @@IDENTITY
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??
how can i ensure that i always get the timestamp of the record it has just inserted
any help greatly appreciated,
Cheers,
Craig
I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.
Sometime is necessary to stop MSSQLSeverOLAPServieces to do a full backup in my OLAP Server disks. After backup had finished and I tried to star MSSQLSeverOLAPServieces but it takes almost 30 minutes to the services starts.
What can it be causing that?
Paulo
Hi,
We want trace ALL activity on prod
server between 3:00 AM and 3:15 AM
Profiler has stop time option ,but
no start option.
I am looking for example how it could be done
Thank you
Alex
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
View 8 Replies View RelatedHi, All:Please help. I use sql server as back end and Access 2003 as front end(everything is DAO).A table on SQL server has an identity column as the key.We have trouble on adding records to this table using the following SQL.strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,E FROM myTableonAccessLocal"db.execute strSQLThe schema of the table "myTableOnSQLServer" and the schema of the table"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" andthe table "myTableonAccessLocal" does not have a key.When we try to run the query, it gives errors indicating the key is violatedor missing.Should I figure out the autonumber for it first and then add to the SQLserver table?Many thanks,HS
View 1 Replies View RelatedI have table contains more columns and first column have ID int not null primary key and auto increment by 1 seed by 1 the ID 165000 record and instant Jump to 166000 and increment by 1 ...
View 5 Replies View RelatedHello
Using Visual Studio 2005 Prof and SQL Server everywhere.
How do get the identity column value after insert record.
With SQL Server 2005, its quite easy to get by creating and insert statement on the tabledapter ( Insert statement followed by a select statement where identitycolumn = scope_identity())
How do this is sql everywhere??
regards
Is there anywhere in the SSMS to see the start time of a currently running job, currently running it's first step, without needing to run a query?
If not, why would Microsoft decide to not show it in the job activity monitor?
SSMS = Sql Server Management Studio
We have an issue where servers goes slow from morning 2 AM EST to 10 AM EST.
Using SP_WhoIsActive we somehow found that there is netbackup from Symantec which runs during that time phrase.
As per Symantec team there backup should get over by 5 AM EST, per their testing for almost 100 Dbs on the server( not big in size , all of them in total would be 60 GB).
Using SP_whoisactive we only see, start time of that virtual backup occurring on tape, for one database at time.
So is there a way we actually determine when does the above backup kicks and stops?Also, could SQL server be the problem in making these backups run slow or there is something else i need to monitor?
Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
Hi,
I have a package designed as bring data tables over to SQL Server. There are 9 data flow tasks that runs parallel, to bring 9 datatables over. In BIDS, when I execute the package, it runs like 8 minutes. Or if I start the scheduled job manually, it runs around 8 minutes too. But it runs about 30 minutes at the scheduled time at midnight.
I wonder what I can do to speed up the scheduled job.
Thanks
Say that I have employee data for 2010-2015. I have a pivot table that displays employee data for all the different months in 2015 I want each month to show all people who have been employed since the start of time until and including that month. So
for January 2015 it should show all people employed from January 2010 to January 2015.
for February 2015 it should show all people employed from January 2010 to February 2015.
for March 2015 it should show all people employed from January 2010 to March 2015.
How can I write a measure for this? I have a date table set up.
I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as the last parameter, I would choose true().
I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table.
I would like to return the latest time stamp as well as the earliest time stamp out of a series of records.The key would be the same ID so i need a function or query that can search through each record and find the earliest time for that ID and the latest time.
ID LatestTime EarliestTime
1 05:00:00 02:00:30
I have achieved the latest time by using a RowNumber function and returning the latest record. The stickler is how to find the earliest record as well.
Code:
CREATE TABLE [dbo].[DigiTracker](
[DigitrackID] [int] IDENTITY(1,1) NOT NULL,
[contact_id] [int] NOT NULL,
[Code]....
-- not accurate b/c it returns 0 for all TimePerPageSec
SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec
FROM DigiTracker
GROUP BY contact_id, ZinePageNumber
time spent (in seconds) between each record
I have about 20 scripts to run as
exec script1 ...
exec script20
I want to know how long does each of the script piece take. So how can I keep the records of time spent and save them as a file or save them into a table?