Showing posts with label function. Show all posts
Showing posts with label function. Show all posts

Friday, March 30, 2012

ID Creation/ASP/SQL Server

Using SQL Server with ASP for an intranet website.
I'm generating a ID using a user defined function. (getting the Maximum and
adding 1 to get a new ID )
During testing i have got 2 similar ids (example: 31000,31000) for 2 records
when there is concurrent access to a website
here i need to track the ID generated,manipulate and convert into string
format (example: U31000_CTF_FILE)
so im generating it thru function.
is there any other best way to generate & track IDs.
Note: im not using any stored procedures moreover i want to use normal ASP
code only
please help
-dnkHi
You can try somethimg like that
CREATE PROC spNewIds
AS
DECLARE @.new_id INT
BEGIN TRANSACTION
SELECT @.new_id =COALESCE(MAX(Id)+1,0) FROM MyTable WITH (UPDLOCK,HOLDLOCK)
INSERT INTO AnotherTable (colname) VALUES (@.new_id )
COMMIT TRANSACTION
"DNKMCA" <dnk@.msn.com> wrote in message
news:uPeCLiOFGHA.916@.TK2MSFTNGP10.phx.gbl...
> Using SQL Server with ASP for an intranet website.
> I'm generating a ID using a user defined function. (getting the Maximum
> and
> adding 1 to get a new ID )
> During testing i have got 2 similar ids (example: 31000,31000) for 2
> records
> when there is concurrent access to a website
> here i need to track the ID generated,manipulate and convert into string
> format (example: U31000_CTF_FILE)
> so im generating it thru function.
> is there any other best way to generate & track IDs.
> Note: im not using any stored procedures moreover i want to use normal
> ASP
> code only
> please help
> -dnk
>|||Why don't you just have a dummy table with an IDENTITY column?
CREATE TABLE dbo.MyTable(ID INT IDENTITY(1,1))
Now In the ASP code,
set rs = conn.execute("SET NOCOUNT ON; INSERT dbo.MyTable DEFAULT VALUES;
SELECT SCOPE_IDENTITY()")
Response.Write rs(0)
Though I'm not sure I understand the objection to using stored procedures,
or what you mean by "track IDs"...
"DNKMCA" <dnk@.msn.com> wrote in message
news:uPeCLiOFGHA.916@.TK2MSFTNGP10.phx.gbl...
> Using SQL Server with ASP for an intranet website.
> I'm generating a ID using a user defined function. (getting the Maximum
> and
> adding 1 to get a new ID )
> During testing i have got 2 similar ids (example: 31000,31000) for 2
> records
> when there is concurrent access to a website
> here i need to track the ID generated,manipulate and convert into string
> format (example: U31000_CTF_FILE)
> so im generating it thru function.
> is there any other best way to generate & track IDs.
> Note: im not using any stored procedures moreover i want to use normal
> ASP
> code only
> please help
> -dnk
>sql

Monday, March 26, 2012

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5
Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>
|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> so if I did combin(35,3) the result would be 6545 and the combinations
>> are
>> listed below. I know how to do it by creating three nested loops in c#
>> but
> I
>> think there's a way to do it in sql by using a cross join or self join
>> but
> I
>> don't see it.
>> Thanks
>>
>> 1 2 3
>> 1 2 4
>> 1 2 5
>> 1 2 6
>> 1 2 7
>> 1 2 8
>> 1 2 9
>> 1 2 10
>> 1 2 11
>> 1 2 12
>> 1 2 13
>> 1 2 14
>> 1 2 15
>> 1 2 16
>> 1 2 17
>> 1 2 18
>> 1 2 19
>> 1 2 20
>> 1 2 21
>> 1 2 22
>> 1 2 23
>> 1 2 24
>> 1 2 25
>> 1 2 26
>> 1 2 27
>> 1 2 28
>> 1 2 29
>> 1 2 30
>> 1 2 31
>> 1 2 32
>> 1 2 33
>> 1 2 34
>> 1 2 35
>> 1 3 4
>> 1 3 5
>> 1 3 6
>> 1 3 7
>> 1 3 8
>> 1 3 9
>> 1 3 10
>> 1 3 11
>> 1 3 12
>> 1 3 13
>> 1 3 14
>> 1 3 15
>> 1 3 16
>> 1 3 17
>> 1 3 18
>> 1 3 19
>> 1 3 20
>> 1 3 21
>> 1 3 22
>> 1 3 23
>> 1 3 24
>> 1 3 25
>> 1 3 26
>> 1 3 27
>> 1 3 28
>> 1 3 29
>> 1 3 30
>> 1 3 31
>> 1 3 32
>> 1 3 33
>> 1 3 34
>> 1 3 35
>> 1 4 5
>>
>sql

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>

Friday, March 23, 2012

I want to write a query return particular dates of the previous we

I want to write a query return particular dates of the previous week. Is
there some function where I can pass the weeknumber (I think last week was
37?) and get it to return these dates, regardless of what day of the week I
run the query?
Mon,9/11/06
Tues, 9/12/06
Wed, 9/13/06
Thurs, 9/14/06
Fri, 9/15/06SELECT
Column1
, Column2
, etc
FROM MyTable
WHERE datepart( wk, MyDateColumn ) = 37
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>I want to write a query return particular dates of the previous week. Is
> there some function where I can pass the weeknumber (I think last week was
> 37?) and get it to return these dates, regardless of what day of the week
> I
> run the query?
> Mon,9/11/06
> Tues, 9/12/06
> Wed, 9/13/06
> Thurs, 9/14/06
> Fri, 9/15/06
>sql

I want to write a query return particular dates of the previous we

I want to write a query return particular dates of the previous week. Is
there some function where I can pass the weeknumber (I think last week was
37?) and get it to return these dates, regardless of what day of the week I
run the query?
Mon,9/11/06
Tues, 9/12/06
Wed, 9/13/06
Thurs, 9/14/06
Fri, 9/15/06
SELECT
Column1
, Column2
, etc
FROM MyTable
WHERE datepart( wk, MyDateColumn ) = 37
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>I want to write a query return particular dates of the previous week. Is
> there some function where I can pass the weeknumber (I think last week was
> 37?) and get it to return these dates, regardless of what day of the week
> I
> run the query?
> Mon,9/11/06
> Tues, 9/12/06
> Wed, 9/13/06
> Thurs, 9/14/06
> Fri, 9/15/06
>

I want to write a query return particular dates of the previous we

I want to write a query return particular dates of the previous week. Is
there some function where I can pass the weeknumber (I think last week was
37?) and get it to return these dates, regardless of what day of the week I
run the query?
Mon,9/11/06
Tues, 9/12/06
Wed, 9/13/06
Thurs, 9/14/06
Fri, 9/15/06SELECT
Column1
, Column2
, etc
FROM MyTable
WHERE datepart( wk, MyDateColumn ) = 37
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>I want to write a query return particular dates of the previous week. Is
> there some function where I can pass the weeknumber (I think last week was
> 37?) and get it to return these dates, regardless of what day of the week
> I
> run the query?
> Mon,9/11/06
> Tues, 9/12/06
> Wed, 9/13/06
> Thurs, 9/14/06
> Fri, 9/15/06
>|||Arnie,
I don't have a table, I only have the system or getdate to work with...
Perhaps I should rephrase the question:
I want to write a query return the workdays (Mon-Fri) of the previous week
base on getdate(). Ist here some function/select statement I can use where I
can pass the weeknumber (which I would get via the query (Select LAST_WEEK =(datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
Wed, Thurs, Fri, regardless of what day of the week I run the query?
"Arnie Rowland" wrote:
> SELECT
> Column1
> , Column2
> , etc
> FROM MyTable
> WHERE datepart( wk, MyDateColumn ) = 37
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
> >I want to write a query return particular dates of the previous week. Is
> > there some function where I can pass the weeknumber (I think last week was
> > 37?) and get it to return these dates, regardless of what day of the week
> > I
> > run the query?
> > Mon,9/11/06
> > Tues, 9/12/06
> > Wed, 9/13/06
> > Thurs, 9/14/06
> > Fri, 9/15/06
> >
>
>|||You may find the use of a Calendar table to be invaluable for exercises such
as this one.
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Datetime -How to count the number of business days
http://www.aspfaq.com/show.asp?id=2453
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <DianeF@.discussions.microsoft.com> wrote in message
news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
> Arnie,
>
> I don't have a table, I only have the system or getdate to work with...
> Perhaps I should rephrase the question:
> I want to write a query return the workdays (Mon-Fri) of the previous week
> base on getdate(). Ist here some function/select statement I can use
> where I
> can pass the weeknumber (which I would get via the query (Select LAST_WEEK
> => (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
> Wed, Thurs, Fri, regardless of what day of the week I run the query?
>
> "Arnie Rowland" wrote:
>> SELECT
>> Column1
>> , Column2
>> , etc
>> FROM MyTable
>> WHERE datepart( wk, MyDateColumn ) = 37
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
>> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>> >I want to write a query return particular dates of the previous week.
>> >Is
>> > there some function where I can pass the weeknumber (I think last week
>> > was
>> > 37?) and get it to return these dates, regardless of what day of the
>> > week
>> > I
>> > run the query?
>> > Mon,9/11/06
>> > Tues, 9/12/06
>> > Wed, 9/13/06
>> > Thurs, 9/14/06
>> > Fri, 9/15/06
>> >
>>|||I understand, but is there a query I can write that will accomplish the same
thing?
"Arnie Rowland" wrote:
> You may find the use of a Calendar table to be invaluable for exercises such
> as this one.
> Datetime -Calendar Table
> http://www.aspfaq.com/show.asp?id=2519
> Datetime -How to count the number of business days
> http://www.aspfaq.com/show.asp?id=2453
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Diane F." <DianeF@.discussions.microsoft.com> wrote in message
> news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
> > Arnie,
> >
> >
> > I don't have a table, I only have the system or getdate to work with...
> > Perhaps I should rephrase the question:
> > I want to write a query return the workdays (Mon-Fri) of the previous week
> > base on getdate(). Ist here some function/select statement I can use
> > where I
> > can pass the weeknumber (which I would get via the query (Select LAST_WEEK
> > => > (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
> > Wed, Thurs, Fri, regardless of what day of the week I run the query?
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> SELECT
> >> Column1
> >> , Column2
> >> , etc
> >> FROM MyTable
> >> WHERE datepart( wk, MyDateColumn ) = 37
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
> >> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
> >> >I want to write a query return particular dates of the previous week.
> >> >Is
> >> > there some function where I can pass the weeknumber (I think last week
> >> > was
> >> > 37?) and get it to return these dates, regardless of what day of the
> >> > week
> >> > I
> >> > run the query?
> >> > Mon,9/11/06
> >> > Tues, 9/12/06
> >> > Wed, 9/13/06
> >> > Thurs, 9/14/06
> >> > Fri, 9/15/06
> >> >
> >>
> >>
> >>
>
>|||I don't think that any such 'function' exists, and a query to accomplish
your goal would be quite Byzantine.
I suppose that you could create your own user defined function, but since
using a Calendar table makes the process so easy, I don't waste my time
trying to find kludges like that -and I recommend that you don't as well.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <DianeF@.discussions.microsoft.com> wrote in message
news:BA5F9A11-0879-4F0A-B84E-B844D68C945C@.microsoft.com...
>I understand, but is there a query I can write that will accomplish the
>same
> thing?
> "Arnie Rowland" wrote:
>> You may find the use of a Calendar table to be invaluable for exercises
>> such
>> as this one.
>> Datetime -Calendar Table
>> http://www.aspfaq.com/show.asp?id=2519
>> Datetime -How to count the number of business days
>> http://www.aspfaq.com/show.asp?id=2453
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Diane F." <DianeF@.discussions.microsoft.com> wrote in message
>> news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
>> > Arnie,
>> >
>> >
>> > I don't have a table, I only have the system or getdate to work with...
>> > Perhaps I should rephrase the question:
>> > I want to write a query return the workdays (Mon-Fri) of the previous
>> > week
>> > base on getdate(). Ist here some function/select statement I can use
>> > where I
>> > can pass the weeknumber (which I would get via the query (Select
>> > LAST_WEEK
>> > =>> > (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon,
>> > Tues,
>> > Wed, Thurs, Fri, regardless of what day of the week I run the query?
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> SELECT
>> >> Column1
>> >> , Column2
>> >> , etc
>> >> FROM MyTable
>> >> WHERE datepart( wk, MyDateColumn ) = 37
>> >>
>> >> --
>> >> Arnie Rowland, Ph.D.
>> >> Westwood Consulting, Inc
>> >>
>> >> Most good judgment comes from experience.
>> >> Most experience comes from bad judgment.
>> >> - Anonymous
>> >>
>> >>
>> >> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
>> >> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>> >> >I want to write a query return particular dates of the previous week.
>> >> >Is
>> >> > there some function where I can pass the weeknumber (I think last
>> >> > week
>> >> > was
>> >> > 37?) and get it to return these dates, regardless of what day of the
>> >> > week
>> >> > I
>> >> > run the query?
>> >> > Mon,9/11/06
>> >> > Tues, 9/12/06
>> >> > Wed, 9/13/06
>> >> > Thurs, 9/14/06
>> >> > Fri, 9/15/06
>> >> >
>> >>
>> >>
>> >>
>>

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
Message posted via http://www.sqlmonster.com
amit
start on www.sqldts.com
"amit mota via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQLMonster.c om...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.sqlmonster.com
|||What type of server are you connecting to in the DTS package?
Message posted via http://www.sqlmonster.com

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
--
Message posted via http://www.sqlmonster.comamit
start on www.sqldts.com
"amit mota via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQLMonster.com...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.sqlmonster.com|||What type of server are you connecting to in the DTS package?
--
Message posted via http://www.sqlmonster.com

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
Message posted via http://www.droptable.comamit
start on www.sqldts.com
"amit mota via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQ
droptable.com...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.droptable.com|||What type of server are you connecting to in the DTS package?
Message posted via http://www.droptable.comsql

Wednesday, March 21, 2012

I want to make Store procedure

I want to make Store procedure. it has function for import data from foxpro.
but I want it can be flexible to set location data.
CREATE PROCEDURE #SP_Import_BC
@.Lokasidata varchar(100)='E:\agr_absen',
@.Tg1 smalldatetime,
@.Tg2 smalldatetime
AS
--
if exists (select 1 from sysobjects
where id = object_id('"TTEMP_BC"') and type = 'U')
DROP TABLE TTEMP_BC
--
CREATE TABLE TTEMP_BC (
RecID int IDENTITY (1, 1) Primary Key,
FDDATE smalldatetime NULL ,
FCTIME smalldatetime NULL ,
FCIDNUMBER nvarchar (6) NULL ,
FCSTATUS nvarchar (3) NULL ,
Bagian nvarchar (3) NULL ,
Oprt nvarchar (8) NULL ,
FNTRNUM smallint NULL ,
FNPROSES smallint NULL
)
insert into TTEMP_BC
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="'+@.Lokasidata+'";Extended properties=DBase III')...cardraw
where fctime is not null and fddate between @.Tg1 and @.Tg2
GO
But it cannot be compiled. How to make stored procedure that I want?You need to execute your query with EXECUTE('yourquery') to be able to conca
t
a variable to a query.
I suggest also to create a linked server.
Jonathan
"Bpk. Adi Wira Kusuma" wrote:

> I want to make Store procedure. it has function for import data from foxpr
o.
> but I want it can be flexible to set location data.
> CREATE PROCEDURE #SP_Import_BC
> @.Lokasidata varchar(100)='E:\agr_absen',
> @.Tg1 smalldatetime,
> @.Tg2 smalldatetime
> AS
> --
> if exists (select 1 from sysobjects
> where id = object_id('"TTEMP_BC"') and type = 'U')
> DROP TABLE TTEMP_BC
> --
> CREATE TABLE TTEMP_BC (
> RecID int IDENTITY (1, 1) Primary Key,
> FDDATE smalldatetime NULL ,
> FCTIME smalldatetime NULL ,
> FCIDNUMBER nvarchar (6) NULL ,
> FCSTATUS nvarchar (3) NULL ,
> Bagian nvarchar (3) NULL ,
> Oprt nvarchar (8) NULL ,
> FNTRNUM smallint NULL ,
> FNPROSES smallint NULL
> )
> insert into TTEMP_BC
> SELECT * FROM
> OpenDataSource('Microsoft.Jet.OLEDB.4.0',
> 'Data Source="'+@.Lokasidata+'";Extended properties=DBase III')...cardraw
> where fctime is not null and fddate between @.Tg1 and @.Tg2
> GO
> But it cannot be compiled. How to make stored procedure that I want?
>
>|||Please, I dont understand with your explaining. Can u give so detail?
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:939E0500-5A6B-4D19-AD18-03676F885598@.microsoft.com...
> You need to execute your query with EXECUTE('yourquery') to be able to
concat
> a variable to a query.
> I suggest also to create a linked server.
> Jonathan
> "Bpk. Adi Wira Kusuma" wrote:
>
foxpro.|||Do you have an error message ?
I think that you probably have one because I don't think that this query is
working fine.
Normally, when you concat a variable in a query, you need to execute them
with the EXECUTE fonction, like that.
EXECUTE('insert into TTEMP_BC
SELECT * FROM
OpenDataSource(Microsoft.Jet.OLEDB.4.0,
'Data Source='+@.Lokasidata+';Extended properties=DBase III)...cardraw
where fctime is not null and fddate between @.Tg1 and @.Tg2')
... if I understand ...
Jonathan
"Bpk. Adi Wira Kusuma" wrote:

> Please, I dont understand with your explaining. Can u give so detail?
>
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:939E0500-5A6B-4D19-AD18-03676F885598@.microsoft.com...
> concat
> foxpro.
>
>

Monday, March 19, 2012

i want help

hi

i want to check that only string in textbox. the textbox shoould enterd the strings so is there IsString() function in vb.net

plz help me....

Hi,
first of all your subject does say nothing about your problem, so many people won't read it..
secondly try to use the forum search, your problem has been discusses many times..
for example:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=432801&SiteID=1

i want a function like ifNull function to use in expression builder

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )

i hope to have the answer because i need it so much.

Maylo

dont know if understood very well...

but....

You can use the function IIF(condition, TRUE, FALSE)

And can use IsEmpty function...

explain better!

Regards!

|||

Maylo wrote:

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )

i hope to have the answer because i need it so much.

Maylo

Have you even opened up the expression builder in SSIS? Have you looked at the list of available functions on the right-hand side? Specifically the "NULL Functions" folder?

To answer your question, use: ISNULL(expression)

|||

i try IIF(condition, TRUE, FALSE) and it said that IIF is not recognized ,

let me more clear in explaination

iam using Conditional Split Component and i want to be in its condition some like that Condition

( ( case ColumnX is null then zero else then ColumnX ) ==ColumnY )

then underline part is what i want to translate to a right experision to be used in the Conditional Split Component cases.

i hope you can help me soon

Best wishes

Maylo

|||

Maylo wrote:

i try IIF(condition, TRUE, FALSE) and it said that IIF is not recognized ,

let me more clear in explaination

iam using Conditional Split Component and i want to be in its condition some like that Condition

( ( case ColumnX is null then zero else then ColumnX ) ==ColumnY )

then underline part is what i want to translate to a right experision to be used in the Conditional Split Component cases.

i hope you can help me soon

Best wishes

Maylo

Yeah, Pedro wasn't on the right page for SSIS expressions.

This is what you want:

ISNULL([ColumnX]) ? 0 : [ColumnX]|||

Yeah you are right, I was thinking in SSAS...

Sorry... I was sleeping! :-)

Regards!

|||Wink , ok any one know it in the SSIS

Friday, March 9, 2012

I need to Give DBA full admin rights to SQL 2005 without OS windows rights, can anyone help plea

The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\\localhost\reports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:

Title-Reporting services configuration manager

Error-There was an error refreshing the UI. bla bla bla

A WMI error has occurred and no additional error information is availiable

Title-Reporting services configuration manager

Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla

A WMI error has occurred and no additional error information is availiable

then when he's in sql server 2005 surface area configuation

Title-Surface Area Configuration

Error-Access denied (system.management)

Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS?

Please help!!

Thanks for any time anyone has taken to review this thread!!

There's no issue in giving him/her admin rights to the box. In most cases, he'll/she'll need it. Don't confuse local admin on the box with network admin rights.

Adamus

|||

That's the problem in my environment i cant give local admin rights to any box. This is per PCI requirements which is a credit card VISA format amongst other regulations i'm under.

Do you have any other info that can provide assistance?

|||I

The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\\localhost\reports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:

Title-Reporting services configuration manager

Error-There was an error refreshing the UI. bla bla bla

A WMI error has occurred and no additional error information is availiable

Title-Reporting services configuration manager

Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla

A WMI error has occurred and no additional error information is availiable

then when he's in sql server 2005 surface area configuation

Title-Surface Area Configuration

Error-Access denied (system.management)

Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS?

|||Moving to the SQL Server Security Forum.|||

ok i think i figured out the surface area config issue, theres a link that states : Add new administrator in which i did and i can access all pages now, but it wont obviously let a non admin restart services, since there's a handful of services i think i'll just have/set the services under the SQL admins credentials, unless someone knows of a better way, in which i can allow the sql admin to restart services?

On the other issue i still cant access the reporting service configuration page, still recieving a WMI error?//

Please anyone HELP!!

|||

These sources should help you understand and configure the appropriate account/security for your DBA.

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx

Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557

|||

Ok i was looking over the attached documents. It pretty much looks from what you've given me, if i setup the services on the local machine to run under the SQL DBA's credentials that i wont need to do anything further, this will in turn give the SQL DBA access without any errors?

Personally i dont think the service items are the issues but if you're positive this will do what i dneed to do i'll try it. Please reconfirm my understanding of the situation before i attempt to run changes please.

Again thanks for your assistance.

Wednesday, March 7, 2012

I need the function to return a srting

Hii every one
When i use the function of (select) from the data bass it return dataset or some thing else
But I need it to return string or the data element which in the query not all the query

like

I dont need that
_____________
| Id | Name |
------
| 1 | Bill |
-------
I dont need All of that to display But I need to display the name only in Label or textbox
like
Bill

Thanks
MaroRead about "OUTPUT paramaters"|||I think the easiest way for you to get the value you're looking for, would be to capture the output results of your function to a dataset and then set the value of your label or textbox to an item in the dataset.

Dim myDataset As New Dataset()
myDataset.Datasource = yourFunctionName(parms)

Label1.text = myDataset.Tables(0).Rows(0).Item("myColumnHeaderName")|||Thanks that is what I need
Maro