Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and executes the contents a number of other "children" .sql files
Would anybody know how to do this
What happens if one of the .sql files fails? Will the others keep on running
ThankDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
--
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and
executes the contents a number of other "children" .sql files.
Would anybody know how to do this?
What happens if one of the .sql files fails? Will the others keep on runnin
g?
ThanksDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getdate() in UDF column workaround

I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:

>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql

Get week number with changed @@datefirst question

I want to get the week number (according to ISO rule i.e. if most of the working days fall in the JAN set it to as week number 1).

I know one stored procedure is available at MSDN to achieve this task. It works fine for me if the @.@.datefirst is set to 1 (i.e. Monday) but i have a strange requirement of calculating the week number according to ISO rule but the start date of week can be any day for example Saturday. When I try to run that procedure with my unique criteria I get wrong week number for some years

Can any one tell me the more generic solution?

Your help is appreciated

thanx

Maybe this helps:

SET DATEFIRST 6

Can you post the code of your stored Procedure, please?

|||

What I understood is,

1. Need to find the Week Number for the Given Date

2. If the Jan-01 of the year fall after Wednesday then it will not be considered as Week1 & it will be counted as Week-52 of previous Year

3. If the Jan-01 of the Year fall before or on Wed then it will be considered as Week1 of the current Year

4. The same adjustment will be taken place for every date..

If I understand correctly then the following query will help you

Create Function dbo.MyWeekNo(@.DateValue as DateTime) Returns Int
As
Begin
Declare @.Date as Datetime
declare @.Date2 as Datetime
Declare @.Week as int
Select @.Date = Convert(Varchar,Year(@.DateValue)) + '-01-01', @.Date2=DateAdd(DD,-1,@.Date)

Select @.Week = Case When WeekNo=0 Then dbo.MyWeekNo(@.Date2) Else WeekNo End
From
(
Select
Case When DatePart(W,@.Date) >= 3 Then DatePart(WW,@.DateValue) -1
Else DatePart(WW,@.DateValue) End WeekNo
) as Weeks

Return @.Week;
End

Go

select dbo.MyWeekNo('2003-01-05')

|||

ManiD wrote:

What I understood is,

1. Need to find the Week Number for the Given Date

2. If the Jan-01 of the year fall after Wednesday then it will not be considered as Week1 & it will be counted as Week-52 of previous Year

3. If the Jan-01 of the Year fall before or on Wed then it will be considered as Week1 of the current Year

4. The same adjustment will be taken place for every date..

If I understand correctly then the following query will help you

Create Function dbo.MyWeekNo(@.DateValue as DateTime) Returns Int
As
Begin
Declare @.Date as Datetime
declare @.Date2 as Datetime
Declare @.Week as int
Select @.Date = Convert(Varchar,Year(@.DateValue)) + '-01-01', @.Date2=DateAdd(DD,-1,@.Date)

Select @.Week = Case When WeekNo=0 Then dbo.MyWeekNo(@.Date2) Else WeekNo End
From
(
Select
Case When DatePart(W,@.Date) >= 3 Then DatePart(WW,@.DateValue) -1
Else DatePart(WW,@.DateValue) End WeekNo
) as Weeks

Return @.Week;
End

Go

select dbo.MyWeekNo('2003-01-05')

i have the same problem as ur code prevails i.e. if I try your code following values

set datefirst 6

select dbo.MyWeekNo('1983-12-31')

It give me week 53 but as Saturday (Day 6) is the sarting day of week it shoud be set to week 1

ne more solution

|||

Zadoras wrote:

set datefirst 6

select dbo.MyWeekNo('1983-12-31')

In this function we are not changing the DATEFIRST option.. We are using the default value 1. The function will find the Rite values for you...(The logic will take care this..)

Try the following query..

Set DateFirst 1

Select dbo.MyWeekNo('1983-12-31')

|||

Can you try this Procedure?

CREATE PROCEDURE CustomWeekNr
( @.DateToCheck SMALLDATETIME
, @.WeekStart INT
, @.WeekNr INT OUT
)
AS
BEGIN
DECLARE @.Offset INT;
DECLARE @.YearOfCheck VARCHAR(10);
DECLARE @.DayNr INT;
SET DATEFIRST @.WeekStart;
-- Get YEAR-01-01
SELECT @.YearOfCheck = CAST(DATEPART(YEAR, @.DateToCheck) AS VARCHAR(4)) + '-01-01';
-- Define Offset for First Week of the Year
SELECT @.Offset = CASE
WHEN DATEPART(WEEKDAY, @.YearOfCheck) > 4 THEN 8 - DATEPART(WEEKDAY, @.YearOfCheck)
ELSE 1 - DATEPART(WEEKDAY, @.YearOfCheck)
END
-- Day/Year from the given Date - Offset
SELECT @.DayNr = DATEPART(DAYOFYEAR, @.DateToCheck) - @.Offset
-- Calculate the WeekNr
SELECT @.WeekNr = CASE
WHEN @.DayNr % 7 = 0 THEN @.DayNr/7
ELSE @.DayNr/7+1
END
-- When Week Nr = 53: Possible? If not Set Week = 1
-- Only if (Offset -3) or (Offset < -1 And Days/Year = 366)
IF @.WeekNr = 53
BEGIN
IF @.Offset > -2 OR (DATEPART(DAYOFYEAR, CAST(DATEPART(YEAR, @.DateToCheck) AS VARCHAR(4)) + '-12-31') = 366 AND @.Offset > -3)
BEGIN
SET @.WeekNr = 1
END
END
END

-- Returning the Week-Nr for a Specific Date AND Startday of Week (In this case, Saturday (6)):
DECLARE @.MyWeek INT;
EXEC CustomWeekNr '1983-12-31', 6, @.MyWeek OUT
SELECT @.MyWeek

|||

I gave a try but found the following problem such as

3 January 1986
2 January 1987
1 January 1988 etc are set to 0 (should be 1 for first two and 52 for last one)

|||

Maybe it's better to use a CLR (C# or VB .NET Assembly), because all the logic is computed faster in these Languages. Code can be found here:

http://konsulent.sandelien.no/VB_help/Week/

The calculation is made for First Day of Week is Monday => the decisive Day is Thursday. So for your Problem you have to calculate it for Tuesday.

Or you can port the code to SQL....

|||

thannx for your support... I had a breif look to your link but I think it is not the generic code as it is hard coding the day name (i.e. thursday in your case) it is perfectly alright

but my scenario is different. chances are more that in our database they will be more than 1 starting day of the week it may be saturday , monday or even sunday.... so is it possible that i can define a single procedure to do that or I have to define for every datefirst value?

|||

You can put something like that to your procedure:

SELECT CASE
WHEN (@.@.datefirst + 4) < 8 THEN @.@.datefirst + 4
ELSE @.@.datefirst + 4 - 7
END

Then you have the flexibility you need.

|||

Assuming that all guesses haven't solved your problem (because no hint is marked as answer), here's the complete function (its independent from the setting of @.@.datefirst, because it's used for calculate the first day of the year).

I have tested it with your dates:

3 January 1986: Week 1
2 January 1987: Week 1
1 January 1988: Week 53

CREATE FUNCTION dbo.IsoWeek
(
@.SearchDate AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @.FirstDay DATETIME, @.SearchYear INT, @.WeekNo INT;
-- Get the Year of the Searched Date and the First Day of WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
-- Calculate WEEK for Easy Dates (Exclude 29/30/31 Dec and 01/02/03 Jan When its not equal to @.FirstDay)
IF @.SearchDate > CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-03' AS DATETIME) AND @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-12-29' AS DATETIME)
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
ELSE
BEGIN
-- Calculate WEEK for 01/02/03 Jan
IF @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME)
BEGIN
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Calculate WEEK using the last Year
SELECT @.SearchYear = YEAR(@.SearchDate) - 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END
-- Calculate WEEK for 29/30/31 Dec
ELSE
BEGIN
-- If @.SearchDate >= @.StartDay of Next Year => WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate) + 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Normal Calculation
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END

END
RETURN @.WeekNo;
END

|||

PLEASE GIVE ME SOME TIME TO CHECK THIS OUT

I HOPE IT WORKS

THANX VERY MUCH FOR YOUR SUPPORT

|||

Lucky P wrote:

Assuming that all guesses haven't solved your problem (because no hint is marked as answer), here's the complete function (its independent from the setting of @.@.datefirst, because it's used for calculate the first day of the year).

I have tested it with your dates:

3 January 1986: Week 1
2 January 1987: Week 1
1 January 1988: Week 53

CREATE FUNCTION dbo.IsoWeek
(
@.SearchDate AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @.FirstDay DATETIME, @.SearchYear INT, @.WeekNo INT;
-- Get the Year of the Searched Date and the First Day of WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
-- Calculate WEEK for Easy Dates (Exclude 29/30/31 Dec and 01/02/03 Jan When its not equal to @.FirstDay)
IF @.SearchDate > CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-03' AS DATETIME) AND @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-12-29' AS DATETIME)
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
ELSE
BEGIN
-- Calculate WEEK for 01/02/03 Jan
IF @.SearchDate < CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME)
BEGIN
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Calculate WEEK using the last Year
SELECT @.SearchYear = YEAR(@.SearchDate) - 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END
-- Calculate WEEK for 29/30/31 Dec
ELSE
BEGIN
-- If @.SearchDate >= @.StartDay of Next Year => WEEK 1
SELECT @.SearchYear = YEAR(@.SearchDate) + 1;
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
IF @.SearchDate >= @.FirstDay
SELECT @.WeekNo = 1;
ELSE
BEGIN
-- Normal Calculation
SELECT @.SearchYear = YEAR(@.SearchDate);
SELECT @.FirstDay = CAST(CAST(@.SearchYear AS VARCHAR(4)) + '-01-04' AS DATETIME) - DATEPART(WEEKDAY, CAST(@.SearchYear AS VARCHAR(4)) + '-01-04') + 1;
SELECT @.WeekNo = (CAST(@.SearchDate as INT) - CAST(@.FirstDay AS INT)) / 7 + 1
END
END

END
RETURN @.WeekNo;
END

Thanx for your time Lucky

Still doesnt solve my problem

for example, for some years it is setting Friday week number as 0 for 1 January 1982 if the DateFirst is set to 6 (Saturday) and second problem, I noticed , for dates 28 December 1985, 29 December 1985, 30 December 1985, 31 December 1985 it is setting the value week 53 (if date first is 6, saturday) but it should be week 1 as most of the working days are in that week and next week is 1 which should be week 2

I have tried many things but nothing is working for me

i guess i have to desing different procedures for different datefirst

thanx for all those who contributed in this thread

Get Version of Database

Hi,
Does a SQL Server database have a version number assoicated with it.
We are going to be continually updating a database and would like to
associate a database version with each update e.g (1, 1.1, 1.2...etc)
Is there a version property of the database I can use or will I have to
do it with a new table
Thanks
Jerry
New Table. :-)
HTH, Jens Suessmeyer.
|||You have to customize this using a table but there is no built in information.
"JeremiahOSullivan@.gmail.com" wrote:

> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>
|||Hi,
You have to create a new table and need to write a new stored procedure to
store the versions. ALl manual.
Thanks
Hari
SQL Server MVP
<JeremiahOSullivan@.gmail.com> wrote in message
news:1128502216.266668.98490@.g43g2000cwa.googlegro ups.com...
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>

Get Version of Database

Hi,
Does a SQL Server database have a version number assoicated with it.
We are going to be continually updating a database and would like to
associate a database version with each update e.g (1, 1.1, 1.2...etc)
Is there a version property of the database I can use or will I have to
do it with a new table
Thanks
JerryNew Table. :-)
HTH, Jens Suessmeyer.|||You have to customize this using a table but there is no built in information.
"JeremiahOSullivan@.gmail.com" wrote:
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>|||Hi,
You have to create a new table and need to write a new stored procedure to
store the versions. ALl manual.
Thanks
Hari
SQL Server MVP
<JeremiahOSullivan@.gmail.com> wrote in message
news:1128502216.266668.98490@.g43g2000cwa.googlegroups.com...
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>

Get Version of Database

Hi,
Does a SQL Server database have a version number assoicated with it.
We are going to be continually updating a database and would like to
associate a database version with each update e.g (1, 1.1, 1.2...etc)
Is there a version property of the database I can use or will I have to
do it with a new table
Thanks
JerryNew Table. :-)
HTH, Jens Suessmeyer.|||You have to customize this using a table but there is no built in informatio
n.
"JeremiahOSullivan@.gmail.com" wrote:

> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>|||Hi,
You have to create a new table and need to write a new stored procedure to
store the versions. ALl manual.
Thanks
Hari
SQL Server MVP
<JeremiahOSullivan@.gmail.com> wrote in message
news:1128502216.266668.98490@.g43g2000cwa.googlegroups.com...
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>

Get unique sequential number- best practice

I need a number generator. (e.g. for Receipt number, or transaction number,
etc.) in a multiuser high volume envoironment. What is the best way to get
one from SQLserver2005? (no duplicates allowed)
1) I've seen a StoredProc that will get value, value++, then save back,
enclosed in a Transaction. This will work, but locks the table. A little
concerned about the blocking here.
2) Should I do the same without the Transaction and check for changed value
(optimistic lock?)
3) better way ?
Thanks!Look up identity columns. That should satisfy most of your requirements.
Anith|||Assuming you're not happy with the identity column property and for your own
reasons need this to be implemented with a stored procedure...
The locking part of the technique you are talking about is essential if you
need to guarantee no gaps in the sequence. You queue requests for a new
sequence value by locking it for the duration of the transaction. Here's an
example for an implementation of a blocking sequence:
-- Sequence Table
USE tempdb;
GO
IF OBJECT_ID('dbo.SyncSeq') IS NOT NULL
DROP TABLE dbo.SyncSeq;
GO
CREATE TABLE dbo.SyncSeq(val INT);
INSERT INTO dbo.SyncSeq VALUES(0);
GO
-- Sequence Proc
IF OBJECT_ID('dbo.usp_SyncSeq') IS NOT NULL
DROP PROC dbo.usp_SyncSeq;
GO
CREATE PROC dbo.usp_SyncSeq
@.val AS INT OUTPUT
AS
UPDATE dbo.SyncSeq
SET @.val = val = val + 1;
GO
-- Get Next Sequence
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
The UPDATE statement in the stored procedure locks the sequence exclusively
and maintains the lock for the duration of the transaction. If running in
the context of an explicit transaction, the lock is maintained until the
explicit transaction finishes.
As an example, suppose connection 1 requests a new sequence value in an
explicit transaction:
BEGIN TRAN
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
And gets the sequence value 1
Connection 2 requests a new sequence value and is blocked:
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
Connection 1 issues a rollback:
ROLLBACK
Connection 2 gets the sequence value 1 because it was ultimately not used by
connection 1.
You see, if you want to guarantee that there won't be any gaps, you must
queue requests for new sequence values by locking the sequence for the
duration of the transaction.
If you don't care about gaps, rather only want to guarantee uniqueness of
sequence values, you can use a different sequencing logic, based on
identity. You can rely on the fact if a transaction is rolled back, identity
increment is not rolled back as it's not considered part of an explicit
transaction. Here's how you can implement the sequencing mechanism:
-- Sequence Table
USE tempdb;
GO
IF OBJECT_ID('dbo.AsyncSeq') IS NOT NULL
DROP TABLE dbo.AsyncSeq;
GO
CREATE TABLE dbo.AsyncSeq(val INT IDENTITY);
GO
-- Sequence Proc
IF OBJECT_ID('dbo.usp_AsyncSeq') IS NOT NULL
DROP PROC dbo.usp_AsyncSeq;
GO
CREATE PROC dbo.usp_AsyncSeq
@.val AS INT OUTPUT
AS
BEGIN TRAN
SAVE TRAN S1;
INSERT INTO dbo.AsyncSeq DEFAULT VALUES;
SET @.val = SCOPE_IDENTITY();
ROLLBACK TRAN S1;
COMMIT TRAN
GO
-- Get Next Sequence
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
The purpose of the transaction in the stored procedure is to allow defining
a savepoint and rolling back to it without effecting an external
transaction.
The rollback's purpose is to undo the insertion to the sequence table,
preventing the need to clear it from time to time for maintenance. Remember
that the identity increment is not effected by the rollback.
Back to the original example, suppose connection 1 requests a new sequence
value in an explicit transaction:
BEGIN TRAN
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
And gets the sequence value 1
Connection 2 requests a new sequence value and is not blocked, rather gets
the value 2:
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
Connection 1 issues a rollback:
ROLLBACK
At this point you have a gap in your sequence values since the value 1 was
ultimately not used, while 2 was. If you don't care about gaps, this
mechanism provides better concurrency.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Ronj" <Ronj@.discussions.microsoft.com> wrote in message
news:C1D283D7-2849-4E9A-8F93-6C1F7048128F@.microsoft.com...
>I need a number generator. (e.g. for Receipt number, or transaction number,
> etc.) in a multiuser high volume envoironment. What is the best way to get
> one from SQLserver2005? (no duplicates allowed)
> 1) I've seen a StoredProc that will get value, value++, then save back,
> enclosed in a Transaction. This will work, but locks the table. A little
> concerned about the blocking here.
> 2) Should I do the same without the Transaction and check for changed
> value
> (optimistic lock?)
> 3) better way ?
> Thanks!|||>> I need a number generator. (e.g. for Receipt number, or transaction numbe
r, etc.) in a multiuser high volume envoironment. <<
What kidn of check digit and validatoin are you using? Is this number
exposed in such a way that your need a SOX audit trail? People think
this can be done on one machine with IDENTITY and it really is not that
esy, if you give a damn about doing it right. What IDENTITY says is
that you are planning on never being a large company with many stores
on purpose! The gps will not matter because nobody will ever invest in
the company so there is no need for good auditing and SOX compliance!
Not a great business plan.
Not a problem, really. You can issue blocks of invoice numbers to
stores/salesmen or you can have a generator rule that adds the store,
cash register, timestamp and a sequence number to the sales ticket
(works for Home Depot, et al).
(optimistic lock?) <<
With a computed key like the Home Depot (they are on my mind today --
I just bought some keys), optimistic concurrency control (it is not
really locking) works great. But SQL Server is a pessimistic system by
nature. What to use Firebird or Innerbase instead?
Look up additive congruence generators if you need a random number that
will not repeat. There are some games you can play with those that are
fun.
Again, there is no "Magic, Universal one-size-fits-all" answer. Ever
wonder why each industry has different standards? Different problems!

get the number of rows exported using bcp

Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KR
Why not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegro ups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>
|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf .googlegroups.com...
>
>
>
>
>
> - Show quoted text -

get the number of rows exported using bcp

Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -

get the number of rows exported using bcp

Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.
123200@.e9g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -

get the number of reports per day

I have a table that has a DateTime field that stores something like this "2004/01/19 16:16:15" which lets me know the date and time of the report. Now I need an sql statement that would let me know how many reports are being made per day. Can someone help me out?
This is what I've been doing(see sql), but I have to change the date and then run the query for a certain date. But I want one query that will give me the count on every date. I hope someone understands what I'm talking about. Thanks.

select * from Table where
DateTime >= '2004/03/01' and DateTime < '2004/03/01 23:59:59';Replace hard-coded date reference with a function call:

select * from Table where
DateTime >= convert(char(10), getdate(), 101)
and DateTime < dateadd(day, 1, convert(char(10), getdate(), 101))

get the number of days it has been since a record was inserted

Hi

when inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.

when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.
eg: 3 days

if the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours.
e.g. 22 hrs

i dont want hours to be displayed if the days is >= 1.

please can anyone guide me with this?

thanks!

use the query like this

Declare @.MyVarasDateTime

Set @.Myvar='22/05/2007'

Select'satya', MyTime=

CASE

WHENDATEDIFF(hh,@.Myvar,GetDate())> 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate()))+' days'

ELSE

Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate()))+' hours'

END

Use the appropriate fields according to your database and tables

|||

Thanks Satya, this was really useful. Can you help me modify this so that it returns 1 day and 1 hour instead of 1 days and 1 hours

Appreciate the help!

|||

Sure change the code where its + "days" or + "hours"

1Declare @.MyVaras DateTime23Set @.Myvar='22/05/2007'45Select'satya', MyTime=67CASE8WHENDATEDIFF(hh,@.Myvar,GetDate()) > 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'910ELSE1112Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'1314END1516

|||

Sorry, i dont think i explained what i meant properly...

I need it to say 'days' and 'hours' all the time but the only exceptions are when days = 1 and when hours = 1...in them cases it should say 1 day and 1hour.

so as an example it could out the following :

11 days

21 days

1 day

...and

22 hours

6 hours

1 hour.

Thanks again!

|||

Declare @.MyVaras DateTime Set @.Myvar='05/22/2007'Select'satya', MyTime=CASEWHENDATEDIFF(hh,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'WHENDATEDIFF(hh,@.Myvar,GetDate()) < 23THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hours'WHENDATEDIFF(d,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'WHENDATEDIFF(d,@.Myvar,GetDate()) > 1THENConvert(varchar(10),DATEDIFF(dd,@.Myvar,GetDate())) +' days'END

|||

Great, worked perfectly :)

Thanks.

|||

You are welcome... Answer it if solved

Get the Highest value.

Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!select max(number) as m from daTable|||thanks a bunch!!!!|||select max(number) as m from daTable
Truly, an elegant piece of coding. Sheer genious for its blend of brevity and functionality. I shall have to use this in my next project.

Get the column name of dynamical SQL?

I need to execute a lot of dynamical SQL (select only). These select
statement have different column number and name.
Any easy way to get the column names of a select statement string?How did your dynamic SQL get the column names in the first place?
"nick" <nick@.discussions.microsoft.com> wrote in message
news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>I need to execute a lot of dynamical SQL (select only). These select
> statement have different column number and name.
> Any easy way to get the column names of a select statement string?|||What I want to implement is:
A function with parameter of SQL statement string,
return the column names.
I am trying to avoid parsing the string. I guess SQL server may have some
internal stored procedure to get these information.
"Aaron Bertrand [SQL Server MVP]" wrote:

> How did your dynamic SQL get the column names in the first place?
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>
>|||not so easily. you could:
1. dump the result into a temp table and look up its definition -
e.g.
exec ('select top 0 * into mytmp from sysobjects')
select column_name
from information_schema.columns
where table_name='mytmp'
2. extract the stuff between "select" and "from".
-oj
"nick" <nick@.discussions.microsoft.com> wrote in message
news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>I need to execute a lot of dynamical SQL (select only). These select
> statement have different column number and name.
> Any easy way to get the column names of a select statement string?|||yes, both way are cumbersome.
or any easy way to get the number of columns?
"oj" wrote:

> not so easily. you could:
> 1. dump the result into a temp table and look up its definition -
> e.g.
> exec ('select top 0 * into mytmp from sysobjects')
> select column_name
> from information_schema.columns
> where table_name='mytmp'
> 2. extract the stuff between "select" and "from".
> --
> -oj
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>
>|||nick (nick@.discussions.microsoft.com) writes:
> What I want to implement is:
> A function with parameter of SQL statement string,
> return the column names.
> I am trying to avoid parsing the string. I guess SQL server may have some
> internal stored procedure to get these information.
So what is your real business problem? This sort of thing is somewhat
easy to do from a client program, but not from within SQL itself. Which
is not so strange. This sort of information is not so interesting to
the server-side which delivers data. It is of course interesting on
the client-side, as the client needs to be able to investigate what data
it's getting from the server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||if the columns are exploded (i.e. separated by a comma and no * used), you
can count the number of commas.
-oj
"nick" <nick@.discussions.microsoft.com> wrote in message
news:BBB38F4E-4C5D-4D54-BF58-9CAEF8F7966A@.microsoft.com...
> yes, both way are cumbersome.
> or any easy way to get the number of columns?
> "oj" wrote:
>

get text after a delimiter

In a table I have a column named "Installment" which stores
installment like 9/15, 2/10 etc., where first number is the last
installment deducted and the last number is the total number of
installments. I want to pick any text after "/" using SQL Query. Which
function to use? I am using SQL Server 2005 Express.See funtions LEFT, RIGHT, CHARINDEX, PATINDEX, REPLACE, PARSENAME in BOL.
Example:
select parsename(replace('2/10', '/', '.'), 1)
go
AMB
"RP" wrote:
> In a table I have a column named "Installment" which stores
> installment like 9/15, 2/10 etc., where first number is the last
> installment deducted and the last number is the total number of
> installments. I want to pick any text after "/" using SQL Query. Which
> function to use? I am using SQL Server 2005 Express.
>

Get statistics on db objects

I'm using something like:

SELECT COUNT(*) 'Number of System Tables' FROM dbo.sysobjects

WHERE xtype = 's'

to get statistics on database objects but I seem to remember that theres a more efficient way. For example, is there some way to add something like a where clause to the first table returned by sp_help (where Object_type = 'view', for example)?

Thanks,

Dave

SP_TABLES can be used in this csae with parameters for '

Table, system table, or view.'

Get return value of stored procedure in Query Analyzer

Hi. We've got a stored procedure on SQL Server 2000 with a Return statement,
and sending back a number. Is there a way to view the Return value when
executing this procedure in Query Analyzer? Right now, it's just displaying
how many rows were affected. Thanks.What about a PRINT statement prior to your Return statement?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"dw" <cougarmana_NOSPAM_@.uncw.edu> wrote in message
news:u3NDF0dSGHA.792@.TK2MSFTNGP10.phx.gbl...
> Hi. We've got a stored procedure on SQL Server 2000 with a Return
statement,
> and sending back a number. Is there a way to view the Return value when
> executing this procedure in Query Analyzer? Right now, it's just
displaying
> how many rows were affected. Thanks.
>|||example
create proc prTestReturnValue
as
select getdate()
return 5
GO
declare @.i int
exec @.i =prTestReturnValue
select @.i
http://sqlservercode.blogspot.com/|||dw,
declare @.rv int
exec @.rv = dbo.p1 ...
select @.rv
go
See "execute" command/statement in BOL.
AMB
"dw" wrote:

> Hi. We've got a stored procedure on SQL Server 2000 with a Return statemen
t,
> and sending back a number. Is there a way to view the Return value when
> executing this procedure in Query Analyzer? Right now, it's just displayin
g
> how many rows were affected. Thanks.
>
>|||Thank you all for the answers. That's what I needed and it worked
beautifully :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AF2BF839-61D5-48FE-9845-11532AFB82CF@.microsoft.com...
> dw,
> declare @.rv int
> exec @.rv = dbo.p1 ...
> select @.rv
> go
> See "execute" command/statement in BOL.
>
> AMB
> "dw" wrote:
>

get records count

i have this function

it return 0 but the sql statement in the sql query return the right number?how is that

i want to get the number of records any other idea or fix?

PublicFunction UserAlbumPhotoQuota(ByVal userIDAsInteger)AsBoolean

Dim ConnAsNew SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)

Dim strSQLAsString

Dim drAs SqlDataReader

strSQL ="SELECT *, (select count(*) from userAlbumPic where userID=" & userID &") as rec_count from userAlbumPic "

Dim cmdAsNew SqlCommand()cmd =New SqlCommand(strSQL, Conn)

Conn.Open()

dr = cmd.ExecuteReader()

dr.Read()

userQuota = dr("rec_count").ToString

Conn.Close()

EndFunction

PublicFunction UserAlbumPhotoQuota(ByVal userIDAsInteger)AsBoolean
Dim ConnAsNew SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQLAsString ="select count(*) from userAlbumPic where userID=" & userID
Dim cmdAsNew SqlCommand()cmd =New SqlCommand(strSQL, Conn)
Conn.Open()
userQuota = cmd.ExecuteScalar()
Conn.Close()
EndFunction

Jos

|||

i don't know why it returns 0 ? ?

|||try this:strSQL = "SELECT *, rec_count from userAlbumPic PICSleft join (select userID, count(*) rec_count from userAlbumPic group by userID) usercountson PICS.userID = userCounts.userID"I did not test it but it should work|||

Check whether userID is having the value you are expecting.

Jos

Get RecordNumber with output

Hi everyone,
How can get the record number as column with my query output.
I dont want to insert the values in #temp table with IDENTITY function.
Any other trick...
RiyazYou don't state which version of SQL Server you are using. If you are using SQL Server 2005 you can use the new ROW_NUMBER() (http://msdn2.microsoft.com/en-us/library/ms186734.aspx) function.|||You don't state which version of SQL Server you are using. If you are using SQL Server 2005 you can use the new ROW_NUMBER() (http://msdn2.microsoft.com/en-us/library/ms186734.aspx) function.

Sorry for that

I am using SQL Server 2000