Access Denied error in SQL Server 2000 db

Hi!
I have stored procedure that executes BULK INSERT command inside.
Recently I migrated my sql 7 database to sql 2000 SP2.
Now when running this stored procedure from both application and Query
Analyzer I'm getting this message:
Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
not be opened. Operating system error code 5(Access is denied.).
I found article on Microsoft web site that this is a known bug and it
only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
Why am I getting this error in SQL Server 2000?
Should I apply the latest SP to SQL 2000 to fix this security issue?
Thank you,
YelenaI checked all permissions.
SQL Server service is running under domain account and this user has
access to the share where the file is located.
Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
as sa.
I can't figure out why I'm getting Access Denied error.
Please help.
enaglozman@.yahoo.com (Lena) wrote in message news:<fc1e8071.0403160709.2dfad366@.posting.goo
gle.com>...
> Hi!
> I have stored procedure that executes BULK INSERT command inside.
> Recently I migrated my sql 7 database to sql 2000 SP2.
> Now when running this stored procedure from both application and Query
> Analyzer I'm getting this message:
> Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
> because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
> not be opened. Operating system error code 5(Access is denied.).
> I found article on Microsoft web site that this is a known bug and it
> only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
> Why am I getting this error in SQL Server 2000?
> Should I apply the latest SP to SQL 2000 to fix this security issue?
> Thank you,
> Yelena|||Does it fail if you are logged on as the account running MSSQLServer
service? Also, that account will need read permissions to the share/file
that you reference:
'\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close'
Steve
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:fc1e8071.0403230855.57f26b9a@.posting.google.com...
> I checked all permissions.
> SQL Server service is running under domain account and this user has
> access to the share where the file is located.
> Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
> as sa.
> I can't figure out why I'm getting Access Denied error.
> Please help.
> enaglozman@.yahoo.com (Lena) wrote in message
news:<fc1e8071.0403160709.2dfad366@.posting.google.com>...sql

Access Database in other server

Hi.

I have a stored procedurein my database and I want to access table in otherserver. can I do that in Stored procedure?

kojoh:

I have a stored procedurein my database and I want to access table in otherserver. can I do that in Stored procedure?

as long as you SQL Server that has the stored procedured has the link server configured, it would work.

|||

Thank's for replay , I will give you an example to explane what I want.

CREATE PROCEDURE [dbo].[MySP]ASinsert into memberselect *from member1

the table member in the current server and the table member1 in other server.

Regards.

|||

As long as you have configured the link server to the member1 table then it would work as you need linked servers to run distributed queries.

Here are some articles on linked servers and distributed queries

http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx

http://msdn2.microsoft.com/en-us/library/ms188279.aspx

http://www.databasejournal.com/features/mssql/article.php/3085211

|||

Thank's alotjimmy

I try to implement the setpes but I can't get it .

CREATE PROCEDURE [dbo].[link]ASexecsp_addlinkedserver @.server ='Srv' , @.srvproduct ='' , @.provider='SQLOLEDB' ,@.datasrc ='Srv'select invcompfrom [Srv].[DB].glcomGO

when I run the script it give me

(1 row(s) affected)

(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure link, Line 4
Invalid object name 'Srv.DB.glcom'.

I use sql server 2000.

Access crashes when updating a stored procedure

Hello,

I am having a problem when using access xp as a frontend for sql server
2000.
I have been trying to update a number of stored procedures (Just simple
adding fields etc) which results in access crashing with event ID 1000 and
1001.
Does anyone have any ideas as to what could be the problem?

Thanks in advance..On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:

>Hello,
>I am having a problem when using access xp as a frontend for sql server
>2000.
>I have been trying to update a number of stored procedures (Just simple
>adding fields etc) which results in access crashing with event ID 1000 and
>1001.
>Does anyone have any ideas as to what could be the problem?
>Thanks in advance..

Access could have corrupted metadata stored in the extended attributes of the
stored procedure. Try copying the SQL from the stored procedure, deleting the
procedure, and creating it again. Paste the SQL back in to recover the old
procedure definition (whic will not include the old extended attributes).|||Thanks for the advice. I have done this in the past when things go wrong,
unfortunately this time Access crashes again when I try to save the new
stored procedure.

"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:sgdlm05d9otcv31ms4lbdenfvnsdvog2m4@.4ax.com...
> On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:
>>Hello,
>>
>>I am having a problem when using access xp as a frontend for sql server
>>2000.
>>I have been trying to update a number of stored procedures (Just simple
>>adding fields etc) which results in access crashing with event ID 1000 and
>>1001.
>>Does anyone have any ideas as to what could be the problem?
>>
>>Thanks in advance..
>>
> Access could have corrupted metadata stored in the extended attributes of
> the
> stored procedure. Try copying the SQL from the stored procedure, deleting
> the
> procedure, and creating it again. Paste the SQL back in to recover the
> old
> procedure definition (whic will not include the old extended attributes).|||Thanks for your help, I have tried this in the past when things have gone
astray. However on trying this Access crashes when I try to save the new
stored procedure.

"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:sgdlm05d9otcv31ms4lbdenfvnsdvog2m4@.4ax.com...
> On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:
>>Hello,
>>
>>I am having a problem when using access xp as a frontend for sql server
>>2000.
>>I have been trying to update a number of stored procedures (Just simple
>>adding fields etc) which results in access crashing with event ID 1000 and
>>1001.
>>Does anyone have any ideas as to what could be the problem?
>>
>>Thanks in advance..
>>
> Access could have corrupted metadata stored in the extended attributes of
> the
> stored procedure. Try copying the SQL from the stored procedure, deleting
> the
> procedure, and creating it again. Paste the SQL back in to recover the
> old
> procedure definition (whic will not include the old extended attributes).sql

Access ComboBox Wont Populate

I'm using SQLServer2000/Access 2000 .adp. The .adp has a form with 2 combo boxes. The 1st combo box is bound to a stored procedure and loads fine when the form loads. I then set the 2nd combo box's RowSource passing the value of the first into the stored proc:

Me.secondComboBox.RowSource = "EXEC dbo.proc_My2ndProc '" & Me.cboFirstComboBox & "'"
Me.cbosecondComboBox.reQuery

Why won't it work? I've set a form's RecordSource using this methof, and it works great?

Thanks,
CarlDummy me, I didn't have the RowSourceType set to Table/View/StoredProc, all's well. I figured it out by creating a new form, and it was working, so I went to see what was different.

Access another SQL Server inside of a stored procedure

Is it possible to access another SQL server when inside a stored
procedure? I would like to select some data in one database and then
insert that data into another database on a different SQL server. Is
that possible? Examples?
Thanks!
-JohnYes, it is possible. See "sp_addlinkedserver" in BOL.
AMB
"John Baima" wrote:

> Is it possible to access another SQL server when inside a stored
> procedure? I would like to select some data in one database and then
> insert that data into another database on a different SQL server. Is
> that possible? Examples?
> Thanks!
> -John
>

Access another database from sp

I've looked around on how to do this and haven't had any luck so far. I have a stored procedure that needs to update tables in the current database (db1) and in another database (db2) on the same server. The user executing the stored procedure on db1 does not have permissions to access db2, but does know the username, password, and connection string of db2. Is there any way to create a connection to db2 in the stored procedure using the credentials I have? I've seen sp_addlinkedserver, but I don't think I need to use that because that database is on the same server? I just can't figure out how to 'switch' users to update the other database from the one stored procedure.

If I haven't made myself clear just let me know and I'll do my best to give a better explanation. Any help would be greatly appreciated.

Option 1:

Create a credential which has access on both db1 & db2 database. It will tune your query performance, bcs it is a direct access & you need not to stick with Ad-Hoc Remote quires.

Option 2:

Using Ad-Hoc remote quires using OPENROWSET or OPENDATASOURCE.

|||

As Mani indicates, the user will, in some way, require permission to access db2. However, it may not be necessary to provide the user direct access permissions. You may wish to explore the new 'EXECUTE AS' functionality (SQL 2005).

Refer to Books Online, Topic: "EXECUTE AS'

|||

Arnie,

I am not sure EXECUTE AS will help here.

As per my understanding, EXECUTE AS will affect the entire scope, if we need to change the context then we have to use the Execute AS & revert.

I created 2 users,

user1 only have access on db1

user2 only have access on db2

Without direct access permission on both db, user1 can't able to access the db2 rite? (even with execute as). If it allows then i can create any sp with Execute As target db user name, and i can access all the data.

Am i missing something here?

|||

Mani,

As in the Example , in Books Online, Topic: 'EXECUTE AS (Transact-SQL)', inside the stored procedure, the security context can be changed, the UPDATE statement can be executed in db2, and then the security context can revert -leaving the entirety of the context switching 'hidden' from the application users' view.

And, if necessary, the sproc can be encrypted locally to obfuscate the context switching from developers.

Access 2003 Database Upgrade to SQL Server 2005

Is there a wizard or established procedure to upgrade an Access 2003 database to SQL Server 2005 (either standard or express)?

Hi.

I would use the import wizard (right-click a database, tools->Import data).
Also, the MS Access Upsizing wizard should work.

Happy sizing.|||Hi,

I am new to VB2005 and SQL Server 2005 Express, so please be patient. I want to upgrade one of my MS Access 2003 databases. I have the Express version of SQL server. Could you be more specific to explain how to convert it to SQL Server 2005 Express version please? Thank you.|||

Hi Athena,

There are a bunch of resources about migrating from Access to SQL Server that you should probably look over before you do this. A good page to start on is http://www.microsoft.com/sql/solutions/ssm/access/accessmigration.mspx. One of the questions you need to answer is why you want to move to SQL Server.

Specific information about using the Upsizing Wizard, which the tool I suggest you use is available in this paper: http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx as well as in the Access help. Even though the paper talks about SQL 2000, the Upsizing Wizard is the same and all the information in the paper still applies to SQL 2005.

One special consideration for SQL 2005 Express is that you will need to enable TCP/IP connection in order for Access to talk to SQL Server. This is off by default in Express Edition. You can configure this using the SQL Configuration Manager as documented at http://msdn2.microsoft.com/en-us/library/ms181035.aspx. At the same time you will likely also want to Start the SQL Browser as this will let you find the named instance of your server. (Assuming you did a default installation, your server is named <machinename>\SQLEXPRESS.)

It is best to do all this with Access and SQL Express installed on the same computer. If you are working between two different computers you have to consider if the computer with SQL Express installed on it is running a firewall, such as the Windows XP Firewall. The firewall will likely block communication between SQL Express and other computers, so you need to create an Exception for both SQL Server and the SQL Browser. You can find more information about how to do this in the SQL Express team blog at http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx.

Hope this helps,

Mike

sql

Access 2003 Database Upgrade to SQL Server 2005

Is there a wizard or established procedure to upgrade an Access 2003 database to SQL Server 2005 (either standard or express)?

Hi.

I would use the import wizard (right-click a database, tools->Import data).
Also, the MS Access Upsizing wizard should work.

Happy sizing.|||Hi,

I am new to VB2005 and SQL Server 2005 Express, so please be patient. I want to upgrade one of my MS Access 2003 databases. I have the Express version of SQL server. Could you be more specific to explain how to convert it to SQL Server 2005 Express version please? Thank you.
|||

Hi Athena,

There are a bunch of resources about migrating from Access to SQL Server that you should probably look over before you do this. A good page to start on is http://www.microsoft.com/sql/solutions/ssm/access/accessmigration.mspx. One of the questions you need to answer is why you want to move to SQL Server.

Specific information about using the Upsizing Wizard, which the tool I suggest you use is available in this paper: http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx as well as in the Access help. Even though the paper talks about SQL 2000, the Upsizing Wizard is the same and all the information in the paper still applies to SQL 2005.

One special consideration for SQL 2005 Express is that you will need to enable TCP/IP connection in order for Access to talk to SQL Server. This is off by default in Express Edition. You can configure this using the SQL Configuration Manager as documented at http://msdn2.microsoft.com/en-us/library/ms181035.aspx. At the same time you will likely also want to Start the SQL Browser as this will let you find the named instance of your server. (Assuming you did a default installation, your server is named <machinename>\SQLEXPRESS.)

It is best to do all this with Access and SQL Express installed on the same computer. If you are working between two different computers you have to consider if the computer with SQL Express installed on it is running a firewall, such as the Windows XP Firewall. The firewall will likely block communication between SQL Express and other computers, so you need to create an Exception for both SQL Server and the SQL Browser. You can find more information about how to do this in the SQL Express team blog at http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx.

Hope this helps,

Mike

acces multiple servers

Hi,

I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...

Is there a way to do that...

I need to connect to two servers from the same procedure to get data..

Please get back

Thanks,

Hi Swapna,

Yes, there are a few methods, however the most straightforward would be to create a linked server.

Have a look at BOL or http://www.databasejournal.com/features/mssql/article.php/3085211 for more info.

Cheers,

Rob

|||Thanks a lot Rob...It was very helpful

accept all directory in sql query analizer

Well I try to find why I can not run dtsrun with xp_cmdschell
but I try to put all the directory c:\program files\microsoft SQL\...
and the procedure response that he dont find c:\program command
is there a instruccion for the large names on directories that make
the correct functionality of the path?

Regards. Mirtha.I use double-quoted names in other places, maybe it works here... also, as a turn around you can use the ~ short names form.
Hope it helps,
Csar|||There is a convention for shortening a long name to an 8.3 convention but I find it hard to remember.

You can just put your pathname and executable in double quotes, i.e. "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"|||Originally posted by Cesar Fraustro
I use double-quoted names in other places, maybe it works here... also, as a turn around you can use the ~ short names form.
Hope it helps,
Csar
Yes , I do but it does not work. thanks.|||Originally posted by Paul Young
There is a convention for shortening a long name to an 8.3 convention but I find it hard to remember.

You can just put your pathname and executable in double quotes, i.e. "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"

Yes it works, thanks and regards.

abt Stored Procedure ...

hello guys ..

today i came with a Common question...

i am developing a website in that, I am using Stored procedure for all queries ( select, insert, update, delete )

is it good to run all queries in stored procedure ... or we should run only the SELECT queries by SP ... i need ur valuable suggestion ...

cheers ...i am waiting for some experienced person's answer ... is anybody here ??|||

Quote:

Originally Posted by rameshcse2005

i am waiting for some experienced person's answer ... is anybody here ??

hi,
You can use any sql statement inside your stored procedure...

According to me if you use stored procedures in your applications it will be good, if you need any database change just manipulate your sp, no need of rebuild or recompile your application.. all you need is organizing your application and stored procedures. better the design you have better the result you get|||You can use all the DML statments inside a procedure.|||Thank you very much for ur valuable suggestion ... :-)

and i have another doubt ?

how to pass multiple value in same parameter in SQL stored procedure ??

for example i have to write stored procedure for this query

DELETE FROM tableName where ID in (id1,id2,id3) ?|||You need to pass a refcursor to the procedure to dynamically pass any number of parameters for the IN operator..|||Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)|||

Quote:

Originally Posted by rameshcse2005

Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)

hi
you can do this as follows

send the id as a string seperated by ,(comma)

in your stored procedure use the following query

DELETE FROM tableName where ID in
( select id from tableName
where (','+isnull(@.parameter,convert(varchar(10),Id) )+',') like
('%,'+convert(varchar(20),Id)+ ',%')
)

In the above query if you pass null value to the @.Parameter then all the records will be deleted, if you send the id values seperated by coma then specific records only will be deleted

About TRAN, CURSOR and @@ERROR

Please take a look at my code below:
CREATE PROCEDURE ...
....
@.userId int,
....
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
Henri
Why use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:

> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>
|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...[vbcol=seagreen]
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
...[vbcol=seagreen]
@.confKey[vbcol=seagreen]
an
>
|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>
|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
David Portas
SQL Server MVP
|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:

> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
>
>
|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...[vbcol=seagreen]
when[vbcol=seagreen]
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
that my[vbcol=seagreen]
specified[vbcol=seagreen]
when[vbcol=seagreen]
if[vbcol=seagreen]
updated.[vbcol=seagreen]
no[vbcol=seagreen]
this
>
|||It is certainly better than using a cursor.
"Henri" wrote:

> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> when
> that my
> specified
> when
> if
> updated.
> no
> this
>
>

About TRAN, CURSOR and @@ERROR

Please take a look at my code below:
CREATE PROCEDURE ...
...
@.userId int,
...
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
HenriWhy use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
> > Please take a look at my code below:
> >
> > CREATE PROCEDURE ...
> > ....
> > @.userId int,
> > ....
> >
> > AS
> >
> > BEGIN TRAN
> >
> > DECLARE @.confKey varchar(20), @.confValue varchar(20)
> > DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
...
> > OPEN curs_conf
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> >
> > WHILE @.@.FETCH_STATUS = 0 BEGIN
> >
> > DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key =@.confKey
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> >
> > IF @.confValue <> '' BEGIN
> > INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> > (@.userId, @.confKey, @.confValue)
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> > END
> >
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> > END
> >
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> >
> > COMMIT
> >
> > This code works but is the way it catches errors correct?
> > Do I have to test @.@.ERROR elsewhere?
> > Is it necessary to close and deallocate the cursor before returning when
an
> > error occurs?
> > Is it better to rollback before or after closing and deallocating the
> > cursor?
> >
> > Thanks for answering my questions.
> >
> > Henri
> >
> >
> >
> >
>|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
--
David Portas
SQL Server MVP
--|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > In a transaction you will usually want to test for @.@.ERROR after every
> > statement that manipulates data. As Nigel has said however, there is no
> > obvious reason to use a cursor here at all. I'm not clear just what this
> > code is supposed to do so if you need more help we'll need some more
> > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >
>
>|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> >> I thought it would be clever to delete the keys and insert them again
when
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
> > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > from client
> > and a function converts it in a table variable of key value records,
that my
> > cursor uses.
> >
> > The aim of this procedure is to update an "user configuration" table,
> > removing, updating and inserting configuration keys and values as
specified
> > by the client.
> > I thought it would be clever to delete the keys and insert them again
when
> > needed with the updated values. That prevents from testing for each key,
if
> > it is already present in the base, and if it has to be inserted or
updated.
> >
> > As I said to nigel, the cursor is used so that I don't have to build the
> > key-value table variable twice.
> > Am I missing something?
> >
> > Henri
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > In a transaction you will usually want to test for @.@.ERROR after every
> > > statement that manipulates data. As Nigel has said however, there is
no
> > > obvious reason to use a cursor here at all. I'm not clear just what
this
> > > code is supposed to do so if you need more help we'll need some more
> > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> > >
> >
> >
> >
> >
>|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> > >> I thought it would be clever to delete the keys and insert them again
> when
> > needed with the updated values.
> >
> > That's usually not a good idea. Only update data when necessary or you can
> > get into problems with RI, triggers and tr logs.
> >
> > You can use the function to insert into a temp table for the data updates.
> > Then delete any records that are not needed then insert any new ones.
> >
> > "Henri" wrote:
> >
> > > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > > from client
> > > and a function converts it in a table variable of key value records,
> that my
> > > cursor uses.
> > >
> > > The aim of this procedure is to update an "user configuration" table,
> > > removing, updating and inserting configuration keys and values as
> specified
> > > by the client.
> > > I thought it would be clever to delete the keys and insert them again
> when
> > > needed with the updated values. That prevents from testing for each key,
> if
> > > it is already present in the base, and if it has to be inserted or
> updated.
> > >
> > > As I said to nigel, the cursor is used so that I don't have to build the
> > > key-value table variable twice.
> > > Am I missing something?
> > >
> > > Henri
> > >
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > > In a transaction you will usually want to test for @.@.ERROR after every
> > > > statement that manipulates data. As Nigel has said however, there is
> no
> > > > obvious reason to use a cursor here at all. I'm not clear just what
> this
> > > > code is supposed to do so if you need more help we'll need some more
> > > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > > >
> > > > --
> > > > David Portas
> > > > SQL Server MVP
> > > > --
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
>|||Performance is always something you should test out for yourself with your
data - it's rarely possible to generalize. For strings of trivial size the
efficiency of client code vs TSQL is probably negligible but your comments
on the performance of your function suggested this wasn't a negligible
problem. My point was that you should start from the assumptions of correct
design - performance optimization comes later.
--
David Portas
SQL Server MVP
--

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
> >
> >
>