is it possible to pass array to stored procedure

Dear All,

I am using sql2000, I want to know whether in stored procedure we can pass

array. Or is there any other solution to pass array of records

Please Guide Me

thanks

Moving SQL Server forum.

Thanks.

|||

No... it is not posible to pass arryas to sp. BTW what is u r requirement , there are workaround for everything.. so pse specify u r requirement..

Madhu

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

Is it possible to do a bulk insert with a format file?

Hi,
Is it possible to do a bulk insert from one table to another with field
delimiter?
Example:
I have records in table A that means to be parsed into table B:
Table A
LineFeed Varchar(8000)
Table B
Customer_Number char(8)
First_Name varchar(25)
Last_name varchar(25)
DOB Timedate
The records in table A look like this..
1234567;Kevin;Smith;10/25/2005
123458;Eric;Joe M;11/10/2004
I know how to do this with a cursor and parsing the field by using charindex
and stuff, but i wonder if there's a faster way of doing it with a simple BC
P
utility.Yes, although it's a bit tricky, and the format file is a bit unforgiving
and error messages are cryptic.. You should check out previous posts in this
forum on Bulk Insert" (and Books OnLine as well)
"EricH" wrote:

> Hi,
> Is it possible to do a bulk insert from one table to another with field
> delimiter?
> Example:
> I have records in table A that means to be parsed into table B:
> Table A
> LineFeed Varchar(8000)
> Table B
> Customer_Number char(8)
> First_Name varchar(25)
> Last_name varchar(25)
> DOB Timedate
> The records in table A look like this..
> 1234567;Kevin;Smith;10/25/2005
> 123458;Eric;Joe M;11/10/2004
> I know how to do this with a cursor and parsing the field by using charind
ex
> and stuff, but i wonder if there's a faster way of doing it with a simple
BCP
> utility.|||Hi CBretana,
I've tried reading books online and it don't talk about moving data from one
table to another.
I have tried the following syntax and it's coming back as an error:
BULK INSERT tempdb.dbo.TableA
FROM (SELECT Linefeed from tempdb.dbo.TableB) S
WITH
(FIELDTERMINATOR = ';')
Anyone else seeing a problem here?|||Because Bulk Insert is for moving Data from EXTERNAL files into Sql Server,
not for moving data from One table to another. For this latter purpose you
should use standard SQL Insert/Update Statements, or DTS.
"EricH" wrote:

> Hi CBretana,
> I've tried reading books online and it don't talk about moving data from o
ne
> table to another.
> I have tried the following syntax and it's coming back as an error:
> BULK INSERT tempdb.dbo.TableA
> FROM (SELECT Linefeed from tempdb.dbo.TableB) S
> WITH
> (FIELDTERMINATOR = ';')
> Anyone else seeing a problem here?
>

Is it possible to conditionally skip steps in a job?

Is it possible to conditionally skip or jump steps in a job without failing a
step?
For example I don’t want to run a step if the are no records in a table.
I also need to loop based on a condition.
Sounds like a DTS package is a better choice for this type of work.
Andrew J. Kelly SQL MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:742CC080-17BE-4885-AFF4-D5769DE1AC0E@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
> failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>
|||Yes, you can... select the task. Right click and go to task properties ( I
could be mistaken and it might be in the workflow properties..) but you can
associate a script ( the button is on the lower left side of the dialog
box.)..
The script returns a value which indicates whether the step/task should run
or not...
Sorry I can't remember better details, and I only have Yukon installed on
this box...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:742CC080-17BE-4885-AFF4-D5769DE1AC0E@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>
|||If this is with a job you can generate failure inside that step with
RAISERROR and then on job properties choose on failure go to the next step.
Other way with a job is to combine these 2 steps in one and make
a condition inside.
If this is a DTS go to the sqldts.com - there is a very good example
of skipping steps inside DTS package.
Regards.
"Dave" wrote:

> Is it possible to conditionally skip or jump steps in a job without failing a
> step?
> For example I don’t want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>

Is it possible to conditionally skip steps in a job?

Is it possible to conditionally skip or jump steps in a job without failing
a
step?
For example I don’t want to run a step if the are no records in a table.
I also need to loop based on a condition.Sounds like a DTS package is a better choice for this type of work.
Andrew J. Kelly SQL MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:742CC080-17BE-4885-AFF4-D5769DE1AC0E@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
> failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>|||Yes, you can... select the task. Right click and go to task properties ( I
could be mistaken and it might be in the workflow properties..) but you can
associate a script ( the button is on the lower left side of the dialog
box.)..
The script returns a value which indicates whether the step/task should run
or not...
Sorry I can't remember better details, and I only have Yukon installed on
this box...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:742CC080-17BE-4885-AFF4-D5769DE1AC0E@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>|||If this is with a job you can generate failure inside that step with
RAISERROR and then on job properties choose on failure go to the next step.
Other way with a job is to combine these 2 steps in one and make
a condition inside.
If this is a DTS go to the sqldts.com - there is a very good example
of skipping steps inside DTS package.
Regards.
"Dave" wrote:

> Is it possible to conditionally skip or jump steps in a job without failin
g a
> step?
> For example I don’t want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>

Is it possible for a table to increase size when deleting records?

No transaction log involved, only the table itself.

Use sp_spaceused "table_name" to check the space used.

It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.

Recovery mode set to be simple, autoshrink turned on.

The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.

Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue

Thanks!

First of all, you really should turn auto-shrink off. It causes lots of problems with performance and fragmentation.

You probably are seeing index and table fragmentention during the delete. What I would do would be to turn autoshrink off, then if I was very concerned about table size, I would either ALTER INDEX REBUILD or ALTER INDEX REORGANIZE after a large delete to cleanup and shrink the indexes.

|||When you delete a record in SQL Server, the record normally does not get deleted immediately. What happens is that we set a bit in the record to indicate that the record is deleted (this is called the ghost bit).

There is a background process, ghost cleanup task, that runs every 15 seconds and tries to remove records from pages. For indexes, the ghost cleanup task will also remove pages that get empty. For heaps, the ghost cleanup task will not remove pages that get empty

So if your table is a heap, and you are doing a lot of deletes, it is possible that the size of your table does not get smaller. In case of heaps, we will only deallocate pages when we have a table lock on the heap.

There is one other corner scenario where during deletes a record can actually grow in size. For this scenario, you need to have snapshot isolation enabled on the database. When you delete a record during snapshot isolation, we will ghost the record, but we will add version related information (14 bytes) to the record so that snapshot isolation works correctly. Any ghosted records that are part of active transactions cannot be removed by the ghost cleanup task. So in this case, during a delete, you will actually need more space than before, as we need to store the version information for each record as well, potentially adding 14 bytes per record.

For heaps, this might even get worse. If a record gets ghosted and grows with 14 bytes, we might actually have to forward the record to another page if it does not fit on the current page anymore, meaning that you might see a lot of extra space being used. Again, this only happens when snapshot isolation is turned on and you have a lot of pages in the heap that are almost full. Also, the average record size needs to be less than 32 bytes for this to happen, otherwise we do some things in the engine to avoid the forwarding.

Hope this helps,

Is it a SQL's bug?

There is not a field named as 'CRSID' in the table TMatch but the SQL Server
would return all the records in table tcrsmgr:
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT CRSID
FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')))This is correct behavior because the TCRSMGR.CRSID is used in the subquery.
To avoid ambiguity, qualify column names in the subquery with the desired
table name or alias like the example below. In this case, you'll get an
error because the TMatch.CRSID column doesn't exist.
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
Hope this helps.
Dan Guzman
SQL Server MVP
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>|||This is expected behavior. The inner reference to CRSID does
not include a table alias or table name. As a result, it is resolved
as TMatch.CRSID if that column exists, and if not, to
TCRSMGR.CRSID, if that column exists (which it does -
if it did not, you would get an error).
You now have a correlated subquery, and for each row of
TCRSMGR, that correlated subquery is
SELECT TCRSMGR.CRSID FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')
So long as CRSID has at least one row for which
tDATE = dbo.fDateOf('2005-7-20'), then the WHERE
clause of the entire query is true, and so all rows of
TCRSMGR will be returned.
Outer references must always be valid in subqueries,
or it would be impossible to write a correlated subquery.
For example, no one thinks it's a bug that this works (to
select the biggest order for each employee)
select OrderID, OrderDate, OrderTotal
from Orders as O1
where OrderTotal = (
select max(OrderTotal)
from Orders as O2
where O2.EmployeeID = O1.EmployeeID
)
The reference to O1.EmployeeID is perfectly valid.
Here, the O1 alias is required to avoid ambiguity, but
aliases can be omitted when there is no chance of
ambiguity, and unfortunately in your case, omitting the
alias caused a programming error to go unnoticed.
Here's another example that might not seem so surprising
if not useful:
select * from T
where thisColumn = (
select T.thisColumn
)
You would expect this to return all rows of T with
non-null thisColumn values. Though there is not
even a table mentioned in the subquery, the reference
to T.thisColumn is valid and correlates with the rows
of the outer query. Since thisColumn would not be
ambiguous here, the same query can be written as
select * from T
where thisColumn = (
select thisColumn
)
or, if table X has at least one row,
select * from T
where thisColumn = (
select thisColumn from X
)
The moral of the story? In queries that refer to more
than one table, if not always, qualify columns with the
table you think they come from.
Had you done this here, and written
SELECT *
FROM dbo.TCRSMGR
WHERE (dbo.TCRSMGR.CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
you would have caught the programming error. Most all programming
languages are like this, in allowing inner declarations to override outer
ones, while allowing all outer declarations to be visible within sub-blocks,
if there is no shadowing inner declaration.
int i, j;
...
{
int i, k;
// you can refer to i, j and k here. j refers to the variables declared
// in the outer block, and i and k refers to the variable declared in
// the inner block.
Steve Kass
Drew University
Half Nitto wrote:

>There is not a field named as 'CRSID' in the table TMatch but the SQL Serve
r
>would return all the records in table tcrsmgr:
>SELECT *
>FROM dbo.TCRSMGR
>WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>
>|||Have a look at
http://toponewithties.blogspot.com/...es_archive.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>

Is IDENTITY_INSERT OFF safe?

Say I have a sproc that is occasionally used to restore deleted records to a
table.
--If I use
SET IDENTITY_INSERT myTable ON
--Then insert the records...
INSERT INTO myTable(ID, AnyField)
VALUES (2,'hello);
--Then I return table to original state
SET IDENTITY_INSERT myTable OFF
During that process, what would happen if another user was attempting to
insert records into the same table?
Even if my transaction only takes .2 milliseconds, is it possible another
user will get an error, or is SQL Server smart enough to delay their
transaction(s) [lock the table] until mine is complete?
Thanks,
ChrisOther updaters are not blocked by SET IDENTITY_INSERT ON. But this is not a
problem since the IDENTITY_INSERT ON applies only to the connection that
runs it. The identity column is handled normally in all other connections
(unless, of course, that connection has also set IDENTITY_INSERT ON.
So, yes, it is safe to use.
Tom
"Chris" <rooster575@.hotmail.com> wrote in message
news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Say I have a sproc that is occasionally used to restore deleted records to
> a table.
> --If I use
> SET IDENTITY_INSERT myTable ON
> --Then insert the records...
> INSERT INTO myTable(ID, AnyField)
> VALUES (2,'hello);
> --Then I return table to original state
> SET IDENTITY_INSERT myTable OFF
> During that process, what would happen if another user was attempting to
> insert records into the same table?
> Even if my transaction only takes .2 milliseconds, is it possible another
> user will get an error, or is SQL Server smart enough to delay their
> transaction(s) [lock the table] until mine is complete?
> Thanks,
> Chris
>|||Thanks Tom!
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:WsmdnSkiXZ4uqBvZnZ2dnUVZ_vqdnZ2d@.co
mcast.com...
> Other updaters are not blocked by SET IDENTITY_INSERT ON. But this is not
> a problem since the IDENTITY_INSERT ON applies only to the connection that
> runs it. The identity column is handled normally in all other connections
> (unless, of course, that connection has also set IDENTITY_INSERT ON.
> So, yes, it is safe to use.
> Tom
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>|||ACID
Isolation