Absolutely new to databases and incharge of creating one..Please help

Hi,
We have never worked on databases and are currently developing our
vb.net windows application where we want to use some database to store
data. Our application will be geared towards desktop users (single
user using our app and saving data on his computer), a group of users
(who share data stored at a central location) and should be scalable
to support 100 users or so in future.
I think I will have to use SQL Server 2000 but what should I do about
desktop clients who do not have sql server.. can i create the database
in sql server 2000 and then ship it using msde.. Is it possible. Also
we are a small company with simple win xp pro machines.. so I cannot
use enterprise and developer editions of sql server (as they need
server machines to install). So can I install the developer edition
of sql server 2000, create database, manage it etc using the gui tools
and then ship out the app distributing msde for desktop users.. does
it make sense, is it possible. I have enterprise architect version of
vs.net.
Please throw some light
Thanks
nnch> we are a small company with simple win xp pro machines.. so I cannot
> use enterprise and developer editions of sql server (as they need
> server machines to install).
SQL Server Developer Edition runs on XP Pro and is licensed for development
and testing.
> So can I install the developer edition
> of sql server 2000, create database, manage it etc using the gui tools
> and then ship out the app distributing msde for desktop users.. does
> it make sense, is it possible. I have enterprise architect version of
> vs.net.
You should be fine with this, although keep in mind that a desktop edition
database is limited to 2GB.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Developer" <nnch97@.hotmail.com> wrote in message
news:e83edb5a.0401301342.578b9285@.posting.google.com...
> Hi,
> We have never worked on databases and are currently developing our
> vb.net windows application where we want to use some database to store
> data. Our application will be geared towards desktop users (single
> user using our app and saving data on his computer), a group of users
> (who share data stored at a central location) and should be scalable
> to support 100 users or so in future.
> I think I will have to use SQL Server 2000 but what should I do about
> desktop clients who do not have sql server.. can i create the database
> in sql server 2000 and then ship it using msde.. Is it possible. Also
> we are a small company with simple win xp pro machines.. so I cannot
> use enterprise and developer editions of sql server (as they need
> server machines to install). So can I install the developer edition
> of sql server 2000, create database, manage it etc using the gui tools
> and then ship out the app distributing msde for desktop users.. does
> it make sense, is it possible. I have enterprise architect version of
> vs.net.
> Please throw some light
> Thanks
> nnch

About this store procedure..

Hi,
I have a serial numbers like follow and each of number is a record.
000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
000010
000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
000020
000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
000030
000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
000040
000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
000050
000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
000066
000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
000080
000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
000103 ...
Now, I use store procedure as follow and print the result
SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
73@.74@.76@.80@.85@.87@.89@.90@.92@.95
96@.100@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
100@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.89-90@.92@.95-96@.
100@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 100@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 100@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
000001000051@.000055@.000057000058@.
000060000064@.
000066000068@.000071@.000073000074@.
000076000080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.89-90@.92@.95-96@.
> 100@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
>

About this store procedure..

Hi,
I have a serial numbers like follow and each of number is a record.
000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
000010
000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
000020
000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
000030
000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
000040
000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
000050
000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
000066
000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
000080
000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
000103 ...
Now, I use store procedure as follow and print the result
SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
73@.74@.76@.80@.85@.87@.89@.90@.92@.95
96@.100@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X96@.
100@.103
Any advice, thanks!
Angi
Do you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.89-90@.92@.95-96@.
100@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001000051@.000055@.000057000058@.00006000 0064@.
000066000068@.000071@.000073000074@.00007600 0080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.89-90@.92@.95-96@.
> 100@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
>

About this store procedure..

Hi,
I have a serial numbers like follow and each of number is a record.
000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
000010
000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
000020
000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
000030
000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
000040
000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
000050
000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
000066
000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
000080
000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
000103 ...
Now, I use store procedure as follow and print the result
¡°SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
¡°The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
96¡@.100¡@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
100¡@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
100¡@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
--
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '¡@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '¡ã' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '¡@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001¡ã000051¡@.000055¡@.000057¡ã000058¡@.000060¡ã000064¡@.
000066¡ã000068¡@.000071¡@.000073¡ã000074¡@.000076¡ã000080¡@.
000085¡ã000087¡@.000089¡ã000090 ....
"wei xiao" <weix@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
> 100¡@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have a serial numbers like follow and each of number is a record.
> >
> > 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> > 000010
> > 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> > 000020
> > 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> > 000030
> > 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> > 000040
> > 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> > 000050
> > 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> > 000066
> > 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> > 000080
> > 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> > 000103 ...
> >
> > Now, I use store procedure as follow and print the result
> >
> > ¡°SQL Store Procedure
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > DECLARE @.front INT
> > DECLARE @.frontsav INT
> > DECLARE @.frontbk INT
> > DECLARE @.key INT
> >
> > SET @.key = 0
> >
> > DECLARE spDashCursor CURSOR FOR
> > SELECT DISTINCT srno
> > FROM all
> > WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> > AND pqty IN ('Q','X') AND cate = 'IV'
> > ORDER BY srno
> > OPEN spDashCursor
> >
> > FETCH NEXT FROM spDashCursor INTO @.front
> > WHILE @.@.FETCH_STATUS = 0
> >
> > BEGIN
> >
> > IF @.key = 0
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > PRINT CAST(@.front AS VARCHAR(6))
> > SET @.key = @.key + 1
> > END
> > ELSE
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > END
> >
> > IF @.frontsav - @.front > 1
> > BEGIN
> > IF @.frontbk = @.front
> > BEGIN
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> > ELSE
> > BEGIN
> > PRINT CAST(@.front AS VARCHAR(6))
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> >
> > SET @.frontbk = @.frontsav
> > END
> >
> > SET @.front = @.frontsav
> > END
> >
> > CLOSE spDashCursor
> > DEALLOCATE spDashCursor
> > GO
> >
> > ¡°The Result
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> > 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> > 96¡@.100¡@.103
> >
> > So, How could I make result like follow..
> > It's mean when numbers were continuous then use "DASH" to link.
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> > 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> > 100¡@.103
> >
> > Any advice, thanks!
> > Angi
> >
> >
>

about system variable

i write a store procedure and it include
a lot of
insert into table1(col1,col2..)
select clo1,col2.. from table2
and i want get the @.@.error and @.@.rowcount
but if i write set @.myErr=@.@.error the @.@.rowcount will
turn to 1
and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
will ture into the error of 'set @.myRowcount=@.@.rowcount'
how can i get then both?thanksTry: SELECT @.myErr = @.@.ERROR, @.myRowCount = @.@.ROWCOUN T
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>|||Hi Frank
This was already answered in another newsgroup. Please do not post the same
question independently to multiple groups, so someone doesn't waste time
answering something that has already been answered.
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>

About storing and receiving a location from SQL SERVER.....................

hi friends,

i want to store an image in DB. but most of my friends told that, to store an image in web server then store tat location in a DB and access it anywhere. this is for asp.net in C#-code behind. how to do this? i've a table with a text field. then ......? waiting for ur reply.........

note: i need coding. not by using controls. pls...

Hi,

if you want store the image on DB, you can pass it asSqlDbType.Image, and assign a byte array to the field to be saved.

cmd.Parameters.Add("@.Image",SqlDbType.Image, 0,"Image_Data");

DataRow row = new ...

row.Image_Data =File.ReadAllBytes(pathToFile); //assuming you uploaded it and saved it somewhere.

//your update command

Hope this help

About Store Produre

Hi Every Body.
Its Biswajit I want to know about use of Store Procedure in
SQL.What is benifit of store procedure and we r using it."Biswajit Barik" <biswajitbarik@.rediffmail.com> wrote in message
news:311aa9ac.0404030003.4ac2d5ad@.posting.google.c om...
> Hi Every Body.
> Its Biswajit I want to know about use of Store Procedure in
> SQL.What is benifit of store procedure and we r using it.

http://www.sommarskog.se/dynamic_sql.html#Why_SP

Also see "Stored Procedures" in Books Online.

Simon

about Store Procedure

Hi i have store procedure called runontime suppose... i want to run that
store procedure mid night from monday to friday automatically . is their any
facility available in sql server 2005 to do that like timer thanksCreate job? Have you check it out?
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||Use the SQL Server agent - that's what it's there for! :)
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||hi can u direct me to any artical or help about it thanks
"Uri Dimant" wrote:

> Create job? Have you check it out?
>
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
>
>|||Ok, open BOL and put it in URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/079c2984-0052-4a37-a2b8-4ece
56e6b6b5.htm
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:CF8B8E49-E0E0-4585-BD03-72D423092AA1@.microsoft.com...
> hi can u direct me to any artical or help about it thanks
> "Uri Dimant" wrote:
>

about store procedure

i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you

about store proc

Hi i have store proc where i am excuting a query look like
Select A.* From A where a.Col1=0 Or A.Col2='ABC'
in this query the only problem is if A.Col1 result is null then it give me
error message that timeout expired. the timeout period elapsed prior to
completion of the operation or the server is not responding ....
i dont know why its behaving like that while with And operator its not
giving that problem ... any body has any idea what it is about thankstry thi instead...
Select A.* From A where a.Col1=0
union
Select A.* From A where A.Col2='ABC'|||Amjad
Be careful using UNION clause ,because it removes a "duplicate " rows
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Hi Uri,
I suggested UNION because he was using an OR, so anyways there wouldn't
have been any duplicates, unless the table didn't have a primary key.
Anyways, thanks for the disclaimer, I should have given it :)|||Uri Dimant (urid@.iscar.co.il) writes:
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> Amjad
> Be careful using UNION clause ,because it removes a "duplicate " rows
So does OR.
Then again, given the WHERE condition, there cannot be any duplicates, so
UNION ALL is a good idea.
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|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Uri Dimant (urid@.iscar.co.il) writes:
> So does OR.
> Then again, given the WHERE condition, there cannot be any duplicates, so
> UNION ALL is a good idea.
Eh, I didn't read the query well enough, but thought it was the same column
in both cases.
Given that, you should use UNION and not UNION ALL, if you try this
workaround.
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|||examnotes (amjad@.discussions.microsoft.com) writes:
> Hi i have store proc where i am excuting a query look like
>
> Select A.* From A where a.Col1=0 Or A.Col2='ABC'
> in this query the only problem is if A.Col1 result is null then it give me
> error message that timeout expired. the timeout period elapsed prior to
> completion of the operation or the server is not responding ....
> i dont know why its behaving like that while with And operator its not
> giving that problem ... any body has any idea what it is about thanks
There is a very big difference between AND and OR.
Are both columns indexed? And is the distribution in the column selective
enough?
Say that there is only a column on Col2 and you run:
Select A.* From A where a.Col1=0 AND A.Col2='ABC'
SQL Server can find the rows by using the index on Col2, and then check Col1
to see if the row is to be included.
But with OR, SQL Server must check all rows for Col1, which means that it
has to scan the table. Which can take a long time if the table is huge.
If both columns are indexed, SQL Server can use both indexes, but in such
case it needs to add an extra operator to the query plan to sort out the
duplicates.
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|||Hi i got different result using union operator
like if i run Select A.* From A Where a.col1=0 give me 133 record and then
if i run Select A.* From A where a.col2='abc' it give me 186 but when i run
with union it gave me 169 records i dont know why thanks
"Omnibuzz" wrote:

> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Thats because union removes the duplicates.
if you use UNION ALL, then you will get the total right.
But I guess you would want to eiliminate the duplicates|||thanks for all of you. its working now with Union All
"Omnibuzz" wrote:

> Thats because union removes the duplicates.
> if you use UNION ALL, then you will get the total right.
> But I guess you would want to eiliminate the duplicates
>

About DB files?

Not sure if it is appropriate to place my question in here

1.when mssql use heap file to store data and when use direct file?

2.what does index file depend on to group in mssql?

thanks in advance!

i am not sure i understand the question correctly or not.

SQL Server stored the data in table in the form of rows and columns not in files. Tables which do not have clustered index is called heap. So the answer to your first question may be, when you create a table in sql server if you add a primary key by default clustred index is created and the data stored in the table which have CI is in a sorted order.

Second question is not clear to me...

Madhu

|||

DBMS need to first group on index file in order to optimize search

How does MSSQL depend on to group index?

About DB files?

Not sure if it is appropriate to place my question in here

1.when mssql use heap file to store data and when use direct file?

2.what does index file depend on to group in mssql?

thanks in advance!

i am not sure i understand the question correctly or not.

SQL Server stored the data in table in the form of rows and columns not in files. Tables which do not have clustered index is called heap. So the answer to your first question may be, when you create a table in sql server if you add a primary key by default clustred index is created and the data stored in the table which have CI is in a sorted order.

Second question is not clear to me...

Madhu

|||

DBMS need to first group on index file in order to optimize search

How does MSSQL depend on to group index?