Getting the place number for a specifik ID, from a select list

Heres the thing

Im making a booking application where it is possible to be put on standby. When you book more than there is room fore, you will be put on standby. At the same time there should be a field in the databaserow that will be set to true if you start out with being put on standby. The order of the bookings is being set by the logdate, the one who books first gets the higher place.

When you book, my idea was to first insert the data, then check to see if the place of the booking has exceeded the maximum number

Im coding in vb2005 and can easily make it by coding with the sql:

Sql= "Select nr from booking where date=@.date and ClassID=" & theID

And then make a loop with a reader ( I havent inserted any of basic code in this example!)

while reader.read

X+= 1

If reader.item("Nr") = Thenumber then exit while

End while

If x> Max number then do the Update where Standby=true

But i would like to make it simpler with just making a simple call to database and not making a list to read. Ive put in the vb example to just explain what i would like to do..

If anybody have a better idea it is very welcome!!

Dan, you might try something like this...

insert into Booking
select flight_num, reservation_num,
case
when
(
Select Count(*)
From Booking
Where New.flight_num = Booking.flight_num
) >= MaximumSeats then 'Standby'
else 'Reserved'
end as reservation_status
From NewReservations New

The subquery in the case-when-else construct will determine the number of seats already on the Booking table, and insert the record by placing Standy or Reserved in the reservation_status column . The MaximumSeats for the flight needs to be known, also...

Not sure if you store the new apps in a separate table before inserting into your reservation table, but should give you an idea...

|||

You can use the following Logic...

Update/Insert query .. Where @.RequestedNumber <= (Select Count(Room) From booking Where date=@.date and ClassID=@.ClassID);

Select Case When @.@.RowCount <> 0 Then 'Updated' Else 'StandBy' End as Status

sql

getting the number pf rows in a query result

My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:

"In the forums there is X results for the word X
In the articles there is X results..."

And when the user click one of those lines, the list shows the specific results in that section.

My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…

Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)

Great thanksyou can do a

select count(*) from table where <condition>

to get the # of records matching your condition.

hthsql

getting the number of records with like values

I have a resultset that looks something like this:

Anzahl users_statdata_hobbies

499 Andere
266 Essen
60 Essen,Andere
127 Essen,Musik
10 Essen,Musik,Party,Andere
30 Essen,Party
4 Essen,Party,Andere
51 Kunst
4 Kunst,Andere
13 Kunst,Essen
4 Kunst,Essen,Andere

I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies

Of course this is not normalized but I can't change this.

Nevertheless I need to get the full number of each Hobby and not only the combination of them.

So instead or in addition to the existing recordset I need e.g

357 Essen which ist the sum of all records containing 'Essen' in the above example

The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.

Any ideas someone?

You could perform the initial select into a temp table, then count on that table.

For example:

SELECT into #temp COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies

Select sum(Anzahl), substring(users_statdata_hobbies, 1, 5)

from #temp

group by substring(users_statdata_hobbies, 1, 5)

drop table #temp

You may need to play with conversion or cast on the first column if implicit conversion won't use it as an integer.

Martin

|||

I think your best bet is writing a Table-valued user-defined function that receives in two variables, a delimited list and the delimiter character. Then split the values (i.e. Essen,Musik,Party,Andere) into a returned table. You can then either insert all your results into a temp table and count or you can use relationships with your Master Hobby table to get your counts. The benifit is you get exact counts for each of the Hobbies, not just Essen, which in my mind is like planning ahead for what you might need later.

Good luck.

|||

Hello,

I would consider splitting up the Hobby table. You should create one record per Hobby. This will make selecting and joining those records MUCH more efficient. It might look like a lot of work at first, but such a design would also allow comparisons across languages if you think about multi-language websites later on. And it will also make it possible to extract a exact number of "matching hobbies" in a single querry.

Another "problem" with storing the hobbies in a string like that would be indexing.Also is "Wein,Weib,Gesang" the same as "Gesang,Wein,Weib"? Any querry using a "like '%bla%'" wont be able to use an index on the table. The result would be that you have to scan ALL records for every time someone is searching for a "match"... And since that is most likely one of the main functions of your site, you should try to keep it as efficient as possible.

Getting the number of hours in a month

Hi,
I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.
Any ideas?
Thanks,
Skip.Hi Skip, this calculates the number of hours in the month containing the date 20040101.

select 24 * datepart("d", dateadd("d", -1, dateadd("m", 1, '20040101')))

It goes 1 month forward, then 1 day back to get the last day of the month. The 24 * converts days to hours.

This does not take into account daylight savings :-(|||declare @.month datetime
set @.month = getdate()

select datediff(hour, convert(char(7), @.month, 120)+'-01', dateadd(month, 1, convert(char(7), @.month, 120)+'-01'))|||At least for the United States, you could use:CREATE FUNCTION fHoursInMonth(@.pd1 DATETIME) RETURNS INT AS
BEGIN
DECLARE
@.dWork DATETIME

SET @.dWork = Convert(CHAR(8), @.pd1, 121) + '01'
RETURN 24 * DateDiff(day, @.dWork, DateAdd(month, 1, @.dWork))
+ CASE Month(@.dWork)
WHEN 4 THEN -1 -- Lose an hour to "Spring forward"
WHEN 10 THEN 1 -- Gain an hour from "Fall back"
ELSE 0
END
END-PatP|||I would think that datediff(hour...) would account for leap years and daylight savings.|||Not according to:SELECT a.d, dbo.fHoursInMonth(a.d), DateDiff(hour, a.d, DateAdd(month, 1, a.d))
FROM (
SELECT '2004-01-15' AS d
UNION SELECT '2004-02-15'
UNION SELECT '2004-03-15'
UNION SELECT '2004-04-15'
UNION SELECT '2004-05-15'
UNION SELECT '2004-06-15'
UNION SELECT '2004-07-15'
UNION SELECT '2004-08-15'
UNION SELECT '2004-09-15'
UNION SELECT '2004-10-15'
UNION SELECT '2004-11-15'
UNION SELECT '2004-12-15') AS aThe biggest problem is that the observance of Daylight Savings time, the dates of the changes, and even the amount of change (not everyone uses one hour) are location dependant.

-PatP|||Humph! :( :(

Getting the first period's data - OpeningPeriod function

Hello,

I have a Time hierarchy (FY - Period Num) as follows:

- Fiscal Year

- - Period Number

I am trying to get the number of just PeriodNumber 1 for each year for Actives. I have tried the following:

([Measures].[Active] , OpeningPeriod( [Time].[FY - Period Num].[Time].[FY - Period Num].currentmember ) )

That returns nothing.

([Measures].[Active] , OpeningPeriod( [Time].[FY - Period Num].[Period Number] , [Time].[FY - Period Num].currentmember ) )

That just returns the current period number.

I had also tried to do stuff like:

([Measures].[Active] , [Time].[Period Number] .currentmember)

But that again only gave me the current period data

([Measures].[Active] , [Time].[Period Number] .[&1])

Gives me a #VALUE! error

I also tried:

([Measures].[Active] , [Time].[Period Number] .[&1], [Time].[Fiscal Year] .currentmember)

I can do this:

([Measures].[Active] , [Time].[FY - Period Num].[Period Number].[&1]&[2006])

Here I get data, but it is only for 2006.

And on, and on. Obviously, I don't know what I am doing, but I think I am close.

How can I get the first period data from the current year?

Thank you.

-Gumbatman

Hello Again GumbatMan.

The answere to this question depends on if you have a time dimension that streches further away than the current year.

If you only have the current year as the last member on the year level you could write MDX like:

YourTimeDimension.(FY - Period Num).LastChild.FirstChild.

This will give you the first child member of the last year in your time dimension/hierarchy.

In the enterprise edition of SSAS2005 you have aggregation functions that will take care of this problem. Have a look at the properties for the measure in the relevant measure group.

Here is a link to another approach provided that you are looking for the current time member in reality:

http://blogs.conchango.com/christianwade/archive/2006/06/23/MDX-Script_3A00_-Current_2F00_Relative-Period.aspx

Think about if your problem is about aggregations(key wordemi additive measure) or the time member on the column or row axis in a client.

HTH

Thomas Ivarsson

|||

Thomas,

Once again you've been very, very helpful.

Thank you!

Getting the actual length for columns in a result set

Is there a way to get the length (i.e. number of characters, bytes ...
) in the metadata information for a resultset?
I'm using low level ODBC calls, and my problem is that both
SQLDescribeCol and SQLColAttribute return the column size for the
length. This doesn't sound bad, but when reading the data I need to
have allocated a buffer big enough to hold the data.
More specifically, if I'm using the Text datatype and the result set
contains the text "hello" the size will always = 2^31. Does anyone
know of a low level ODBC call which would return 5, prior to fetching
the data?
Thanks in advance, -geoffThe problem is that the length in the metadata describes the maximum length
of the column. A varchar(5) column will report 5 when you call
SQLDescribeCol(). The metadata for a column is the same for every row in
the table (instance of the column). What you are interested in is the
length of the actual data in the column (or the length of a particular
instance of the column). This information is considered part of the actual
data in the row, rather than part of the column's metadata.
For non-BLOB columns, you typically want to allocate a buffer that can hold
the maximum length of the column (sure there are exceptions). For BLOB
columns you really should use SQLGetData() (rather than SQLBindCol()). If
you call SQLGetData() with a NULL TargetValuePtr parameter, but a valid
StrLen_or_IndPtr parameter, then SQLGetData() will return the length of the
data that is available for the column. Then you can use that value to
allocate a buffer and call SQLGetData() again to read the column.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Geoff" <geoffpollard@.go.com> wrote in message
news:595a2518.0402111842.698363b7@.posting.google.com...
> Is there a way to get the length (i.e. number of characters, bytes ...
> ) in the metadata information for a resultset?
> I'm using low level ODBC calls, and my problem is that both
> SQLDescribeCol and SQLColAttribute return the column size for the
> length. This doesn't sound bad, but when reading the data I need to
> have allocated a buffer big enough to hold the data.
> More specifically, if I'm using the Text datatype and the result set
> contains the text "hello" the size will always = 2^31. Does anyone
> know of a low level ODBC call which would return 5, prior to fetching
> the data?
> Thanks in advance, -geoff|||I suspected there was another way. However, SQLGetData seems to fail
when I pass a NULL TargetValuePtr. The SQLRETURN == -1, and the
SQLGetDiagRec states "Invalid argument value". Here is a code snippet:
SQLRETURN sql_status_code;
SQLINTEGER namelen;
for (int i=0;i<numColumns;i++)
{
// fails
sql_status_code =
SQLGetData(hstmt,i+1,SQL_C_CHAR,NULL,0,&namelen);
// works
SQLCHAR test[255];
sql_status_code =
SQLGetData(hstmt,i+1,SQL_C_CHAR,test,255
,&namelen);
}
Prior to this the following statements returned SQL_SUCCESS:
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, hstmt);
// need to use server-side cursors to handle multiple queries
SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYP
E,
(SQLPOINTER)SQL_CURSOR_STATIC,SQL_IS_INT
EGER);
SQLPrepare(hstmt, (SQLCHAR*) s_SQL, SQL_NTS);
SQLExecute(hstmt);
for (int i=0;i<numColumns;i++)
SQLDescribeCol(...) //read column names
SQLFetch(hstmt)
--[ here is where I've been calling SQLGetData ]--
Any help would be greatly appreciated. Thanks in advance, -geoff
"Brannon Jones [MS]" <branjo@.nospam.microsoft.com> wrote in message news:<ureN$Ba8DHA.2760@.T
K2MSFTNGP09.phx.gbl>...
> The problem is that the length in the metadata describes the maximum lengt
h
> of the column. A varchar(5) column will report 5 when you call
> SQLDescribeCol(). The metadata for a column is the same for every row in
> the table (instance of the column). What you are interested in is the
> length of the actual data in the column (or the length of a particular
> instance of the column). This information is considered part of the actua
l
> data in the row, rather than part of the column's metadata.
> For non-BLOB columns, you typically want to allocate a buffer that can hol
d
> the maximum length of the column (sure there are exceptions). For BLOB
> columns you really should use SQLGetData() (rather than SQLBindCol()). If
> you call SQLGetData() with a NULL TargetValuePtr parameter, but a valid
> StrLen_or_IndPtr parameter, then SQLGetData() will return the length of th
e
> data that is available for the column. Then you can use that value to
> allocate a buffer and call SQLGetData() again to read the column.
> --
> Brannon Jones
> Developer - MDAC
> This posting is provided "as is" with no warranties and confers no rights.
>
> "Geoff" <geoffpollard@.go.com> wrote in message
> news:595a2518.0402111842.698363b7@.posting.google.com...|||Sorry I was slightly incorrect. You need to pass a valid pointer for
TargetValuePtr, but you pass a zero length. We will return the available
length in the StrLen_or_IndPtr parameter, and the return code will be
SQL_SUCCESS_WITH_INFO (SQLGetDiagRec will return: "[Microsoft][ODBC SQL
Server Driver]String data, right truncation", SQLState: 01004). Then you
allocate the buffer and call again.
Let me know if that doesn't help.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Geoff" <geoffpollard@.go.com> wrote in message
news:595a2518.0402140153.5a248edc@.posting.google.com...
> I suspected there was another way. However, SQLGetData seems to fail
> when I pass a NULL TargetValuePtr. The SQLRETURN == -1, and the
> SQLGetDiagRec states "Invalid argument value". Here is a code snippet:
> SQLRETURN sql_status_code;
> SQLINTEGER namelen;
> for (int i=0;i<numColumns;i++)
> {
> // fails
> sql_status_code =
> SQLGetData(hstmt,i+1,SQL_C_CHAR,NULL,0,&namelen);
> // works
> SQLCHAR test[255];
> sql_status_code =
> SQLGetData(hstmt,i+1,SQL_C_CHAR,test,255
,&namelen);
> }
> Prior to this the following statements returned SQL_SUCCESS:
> SQLAllocHandle(SQL_HANDLE_STMT, hdbc, hstmt);
> // need to use server-side cursors to handle multiple queries
> SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYP
E,
> (SQLPOINTER)SQL_CURSOR_STATIC,SQL_IS_INT
EGER);
> SQLPrepare(hstmt, (SQLCHAR*) s_SQL, SQL_NTS);
> SQLExecute(hstmt);
> for (int i=0;i<numColumns;i++)
> SQLDescribeCol(...) //read column names
> SQLFetch(hstmt)
> --[ here is where I've been calling SQLGetData ]--
> Any help would be greatly appreciated. Thanks in advance, -geoff
> "Brannon Jones [MS]" <branjo@.nospam.microsoft.com> wrote in message
news:<ureN$Ba8DHA.2760@.TK2MSFTNGP09.phx.gbl>...
length
in
actual
hold
If
the
rights.

getting sum group by page number

hi all,
i got a table, how can i get the sum of total ONLY for each page?(not sum of
the whole table)
thanks in advancegot the answer from the previous post...
please refer to thread "sum by page" if you guys have the same problem
"Jasonymk" wrote:
> hi all,
> i got a table, how can i get the sum of total ONLY for each page?(not sum of
> the whole table)
> thanks in advance

Getting Started Q - Can Datasets be shared?

Hi,
I'm just starting with RS. Can Datasets be shared or copy/pasted between
reports. I have a number of reports that are all based on the same source
query and would like to avoid coding in several places.
THanks in advance.
--
DougDoug,
You have a number of directions you can take with this.
One example would be that if the datasets are the same for two or more
reports, or even if the differ just slightly you can make a common list of
columns, not including the where clause, you could create a view on the
source SQL DB and refer to that for each report, just passing params as
required in a SELECT statement.
You could also use 'cut and paste' for each report, but this would mean
whenever the underlaying record schema changes, you would need to make the
same changes in more than one place on the RS reports.
Reports share data sources, being the location (catalogue) of the table
objects used to store the data, rather than the tables themselves.
Hope this assists,
Tony
"Doug Little" wrote:
> Hi,
> I'm just starting with RS. Can Datasets be shared or copy/pasted between
> reports. I have a number of reports that are all based on the same source
> query and would like to avoid coding in several places.
> THanks in advance.
> --
> Doug

Getting SQL result in a variable

Hi there

I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt.

how do we achieve that?

thanks and regards

Rahul Kuamr

Hello Rahul,

You can use a row count transformation just before the destination component in your dataflow, you a have to declare a variable of type int32 and assign this variable in the row count transformation.

Regards,

Raju

|||

on the same line,another query

If we wish to get result of some SQL query say select name from emp where id='234'

thanks and regards

Rahul kumar

|||

Hi,

To get the result of a query into a variable first you have to declare a variable of the same type which is returned by the query, and use an execute sql task in the control flow, Double click the execute sql task and click on the general tab and make the result set property to "Single Row" and write the query in this way: "Select name as empname from emp where empid = '234'. " Now you can assign the name alias "empname" used in the query to a result set. To do that click on the result set which is available in the execute sql task and give the name of the result set as "empname" and assign to a variable which is of the same type.

Note: property to "Single Row" only works when the query is returning a single row. If the query is returning mutiple rows, you have to declare a variable of the type object and select the property of the result set as "Full result set". and assign the result name to the variable which has been declared as object.

Regards,

Raju

|||

Thanks a lot buddy.

It works!!

Regards

Rahul Kumar

|||

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

Thanks for any advice you can give me, maybe I do have use an Object Variable.

Chera

|||

cboom wrote:

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

So far, so good.

cboom wrote:

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I think the problem is the way you are looking for the value. You may want to set a breakpoint and execute the whole package; then at the break point time, examinate the varibale by looking at the output window.

cboom wrote:

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

You don't need to use a Object type variable...

|||

Thanks Rafael, you were right, just needed to find a new place to check the value of the variable, so played more with variables in Script Tasks and Componenets and got quite a bit more figured out. Had to learn about specifying the ReadOnlyVariables and ReadWriteVariables for the scripts.

Thanks, appreciate you pointing me in the right direction.

Chera

Getting row position in a select

I need to get the row position in a select.
SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
How can i get the current row number in the result set like this?
MYFIELD1 MYFIELD2 ROW_POSITION
myfield1value1, myfield2value1, 1
myfield1value2, myfield2value2, 2
myfield1value3, myfield2value3, 3
...
Thanksexamnotes (checcouno@.discussions.microsoft.com) writes:
> I need to get the row position in a select.
> SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
> How can i get the current row number in the result set like this?
> MYFIELD1 MYFIELD2 ROW_POSITION
> myfield1value1, myfield2value1, 1
> myfield1value2, myfield2value2, 2
> myfield1value3, myfield2value3, 3
> ...
In SQL 2000, the best is to create a temp table with an identity column
and insert to that one. The IDENTITY column, will give you the value
you need.
If there is a unique key in the result set, you can also do something
like:
SELECT keycol, col1, col2,
rowno = (SELECT COUNT(*) FROM tbl t2
WHERE t1.keycol <= t2.keycol)
FROM tbl t1
But for large data sets, the performance may not be fantastic.
In SQL 2005 it's a lot easier, as it comes with a row_number() function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
SELECT
OT.*,
(SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
OT.pcolumn ) As row_no
FROM YOUR_TABLE OT
OT is Outer Table Allies and IT is Inner Table Allies
I think that it will help you.
"checcouno" wrote:

> I need to get the row position in a select.
> SELECT MYFIELD1, MYFIELD2, ROW_POSITION FROM MYTABLE
> How can i get the current row number in the result set like this?
> MYFIELD1 MYFIELD2 ROW_POSITION
> myfield1value1, myfield2value1, 1
> myfield1value2, myfield2value2, 2
> myfield1value3, myfield2value3, 3
> ...
>
> Thanks
>|||Hi
Read this thread
http://www.aspfaq.com/show.asp?id=2427
Regards
R.D
"Akbar khan is a Senior Database develope" wrote:
> Hi
> SELECT
> OT.*,
> (SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
> OT.pcolumn ) As row_no
> FROM YOUR_TABLE OT
> OT is Outer Table Allies and IT is Inner Table Allies
> I think that it will help you.
>
> "checcouno" wrote:
>|||It works only with order by pcolumn!
"Akbar khan is a Senior Database develope" wrote:
> Hi
> SELECT
> OT.*,
> (SELECT COUNT(1) FROM YOUR_TABLE IT WHERE IT.pcolume <
> OT.pcolumn ) As row_no
> FROM YOUR_TABLE OT
> OT is Outer Table Allies and IT is Inner Table Allies
> I think that it will help you.
>
> "checcouno" wrote:
>sql

Getting Row Number in the Db

Hi,
How to select a record which has the row number 2694 in the database?
Thanks
--
pmudBased on what criteria?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
Hi,
How to select a record which has the row number 2694 in the database?
Thanks
--
pmud|||Just to add to Tom's response some, SQL Server 2000 has no concept of a row
number. The order in which the rows are stored is totally up to SQL Server.
Further more the order in which they are returned is also totally up to SQL
Server unless you specify an ORDER BY clause. So row number xxxx means
nothing without any other context.
Andrew J. Kelly SQL MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
> Hi,
> How to select a record which has the row number 2694 in the database?
> Thanks
> --
> pmud|||> How to select a record which has the row number 2694 in the database?
How the heck are we supposed to determine what row number 2694 means? Can
you grab me the 82nd toothpick in that box of 250 toothpicks? You might
have a better chance if I tell you to order by length, or width, or hue. If
I don't tell you what my ordering method is, good luck!|||Yes. Thats my mistake. The actual problem I am facing is that I am trying to
convert a varchar field to a datetime field . The reason it was originally
varchar was that the data was initially imported from spreadsheets so the Db
automatically made it a varchar field.
So, i created another table and set the ActivationDate to a datetime type
instead of a varchar and then I try to import data to this table from the
existing one, but I am getting error:
"Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
error: Conversion invalid for datatypes on column pair 8 ( source column
ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
(DBTYPE_DBTIMESTAMP). "
So I was trying to find out how can I know which is row Number 2694 in the
table. Also, in the varchar activationdate field, all dates are in the forma
t
like:
May 4 2005 12:00 AM
Any ideas on this?
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> How the heck are we supposed to determine what row number 2694 means? Can
> you grab me the 82nd toothpick in that box of 250 toothpicks? You might
> have a better chance if I tell you to order by length, or width, or hue.
If
> I don't tell you what my ordering method is, good luck!
>
>|||Usually, these things are imported in the order in which they occurred in
the source files. Can you go to row 2694 in the spreadsheet itself?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
Yes. Thats my mistake. The actual problem I am facing is that I am trying to
convert a varchar field to a datetime field . The reason it was originally
varchar was that the data was initially imported from spreadsheets so the Db
automatically made it a varchar field.
So, i created another table and set the ActivationDate to a datetime type
instead of a varchar and then I try to import data to this table from the
existing one, but I am getting error:
"Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
error: Conversion invalid for datatypes on column pair 8 ( source column
ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
(DBTYPE_DBTIMESTAMP). "
So I was trying to find out how can I know which is row Number 2694 in the
table. Also, in the varchar activationdate field, all dates are in the
format
like:
May 4 2005 12:00 AM
Any ideas on this?
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> How the heck are we supposed to determine what row number 2694 means? Can
> you grab me the 82nd toothpick in that box of 250 toothpicks? You might
> have a better chance if I tell you to order by length, or width, or hue.
> If
> I don't tell you what my ordering method is, good luck!
>
>|||Oh. dont have the spreadsheet from which this was imported. Is there any
other way of finding out?
Thanks
--
pmud
"Tom Moreau" wrote:

> Usually, these things are imported in the order in which they occurred in
> the source files. Can you go to row 2694 in the spreadsheet itself?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
> Yes. Thats my mistake. The actual problem I am facing is that I am trying
to
> convert a varchar field to a datetime field . The reason it was originally
> varchar was that the data was initially imported from spreadsheets so the
Db
> automatically made it a varchar field.
> So, i created another table and set the ActivationDate to a datetime type
> instead of a varchar and then I try to import data to this table from the
> existing one, but I am getting error:
> "Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
> encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
> error: Conversion invalid for datatypes on column pair 8 ( source column
> ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
> (DBTYPE_DBTIMESTAMP). "
> So I was trying to find out how can I know which is row Number 2694 in the
> table. Also, in the varchar activationdate field, all dates are in the
> format
> like:
> May 4 2005 12:00 AM
> Any ideas on this?
> Thanks
> --
> pmud
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||Not that I know of.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:37A6EC15-7D5B-47D5-A169-BC8A5289D092@.microsoft.com...
Oh. dont have the spreadsheet from which this was imported. Is there any
other way of finding out?
Thanks
--
pmud
"Tom Moreau" wrote:

> Usually, these things are imported in the order in which they occurred in
> the source files. Can you go to row 2694 in the spreadsheet itself?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
> Yes. Thats my mistake. The actual problem I am facing is that I am trying
> to
> convert a varchar field to a datetime field . The reason it was originally
> varchar was that the data was initially imported from spreadsheets so the
> Db
> automatically made it a varchar field.
> So, i created another table and set the ActivationDate to a datetime type
> instead of a varchar and then I try to import data to this table from the
> existing one, but I am getting error:
> "Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
> encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
> error: Conversion invalid for datatypes on column pair 8 ( source column
> ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
> (DBTYPE_DBTIMESTAMP). "
> So I was trying to find out how can I know which is row Number 2694 in the
> table. Also, in the varchar activationdate field, all dates are in the
> format
> like:
> May 4 2005 12:00 AM
> Any ideas on this?
> Thanks
> --
> pmud
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||Look up the ISDATE() function in Books Online, and check which rows contain
invalid values before inserting them in the new table.
ML
http://milambda.blogspot.com/|||simple.
select id = identity(int,1,1), *
into #idRecord
from yourtable
later...
select *
from #idRecord
where id = idCriteria.
See you Later...
Nemir Labopaz
"pmud" <pmud@.discussions.microsoft.com> escribi en el mensaje
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
> Hi,
> How to select a record which has the row number 2694 in the database?
> Thanks
> --
> pmud

Getting RecordCount with TableDirect

Hi, I would like to know if there's any method to get the total number of records in a table without using query. I have the following codes.

Dim conn As SqlServerCe.SqlCeConnection
Dim cmd As SqlServerCe.SqlCeCommand
Dim rdr As SqlServerCe.SqlCeDataReader

conn = New SqlServerCe.SqlCeConnection("Data source=\My Documents\test.sdf")
conn.Open()

cmd = New SqlServerCe.SqlCeCommand("Staff_Table", conn)
cmd.CommandType = CommandType.TableDirect
cmd.IndexName = "Id_Ind"

rdr = cmd.ExecuteReader
rdr.Seek(SqlServerCe.DbSeekOptions.FirstEqual, int_seek_n)

If rdr.Read() Then
strResult = rdr.GetString(1)
End If

rdr.Close()
conn.Close()

I did quite a bit of searching and got no results. Thanks.No, there's no shortcut to getting the count of rows in a table. I recommend doing a SELECT COUNT(*) and use ExecuteScalar to get the best performance on this query.

-Darren Shaffer|||Thanks Darren. Looks like I have no other choice. :)

Getting number of rows

Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2|||Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?
if by "programmatically" you mean retrieving all the rows and returning them to your application program (asp, php, whatever), then the answer is a resounding yes!

use the COUNT() function:select count(*) from daTablethis query returns a single row consisting of a single column containing an integer which is the number of rows in the table

neat, eh? ;)|||if by "programmatically" you mean retrieving all the rows and returning them to your application program (asp, php, whatever), then the answer is a resounding yes!

use the COUNT() function:select count(*) from daTablethis query returns a single row consisting of a single column containing an integer which is the number of rows in the table

neat, eh? ;)

Just FYI,COUNT() ,when used in any form other than COUNT(*), ignores NULL values.So be cautious about that...|||ok..cool..i will try both techniques..my sql skills are pretty rusty anyway and need to learn different ways to do things

thanks everyone|||Selecting from sysindexes is technically faster than using count(*), as if a human would ever notice the difference, but the sysindexes value returned may not be accurate if statistics on the table are not up to date. And running UPDATE STATISTICS on all your tables will take a lot of time, so use the count(*) method if you need accuracy.|||if you have a large number of rows (say a few million) it's definitely better to go to sysindexes or call sp_spaceused (which hits sysindexes), as long as you don't need need the accuracy of count(*). count(*) is a pretty expensive way to get the count if there are many rows.|||the easiest way:
sp_spaceused <table name>|||I have been reviewing this thread here and have been trying to figure out how to use sysindex
rundra stated that I could use sysindex like this but would I use this command verbatim with exception of the 'table_name'?

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2|||You could use a cursor

Why do you need all the counts btw?

just kidding about the cursor|||if you have a large number of rows, but you don't actually need an accurate answer, then you can get away with not using COUNT(*)

... but then i have to ask, why do you care what the approximate number is?

why don't you just print "the answer is... um... very large"

i'm really curious under what circumstances you don't care about the actual count but do care about some other number that might not be anywhere near the correct answer|||I need the count for my paging system...

So, if I have a 1000 rows and my page size is 20 then my total pages will be 50. Is there a way that I can get an accurate count using the sysindex?|||an accurate count for a paging algorithm?

my advice: don't bother

nobody is gonna page through a paged result set all the way to the end|||an accurate count for a paging algorithm?

my advice: don't bother

nobody is gonna page through a paged result set all the way to the end

quite right. and even if you use count(*), it's only accurate at the instant the query is executed.

some other process can come along behind you and insert/delete, making the "accurate" value you fetched with the count(*) method no longer correct.

Getting Number of Logged On Users to Server

SQL Server 2000 - MSDE 2000

Is there a way to get the number of current users logged into a SQL 2000 Server (also MSDE)? Cant be distinct users as most users are logged into the database using the same login.

--
Tim Morrison

------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comTim Morrison (sales@.kjmsoftware.com) writes:
> SQL Server 2000 - MSDE 2000
> Is there a way to get the number of current users logged into a SQL 2000
> Server (also MSDE)? Cant be distinct users as most users are logged into
> the database using the same login.

select count(*) from master..sysprocesses where spid > 50 will give you
a rough number. This will include logins from SQL Agent. Note that the
same user can be using multiple connections from the same application,
and thus be counted more than once.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hmmm.... thanks for the info. Im surprised there isnt a more definate way
to get this information from SQL.

Tim Morrison

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9445430E3C3FYazorman@.127.0.0.1...
> Tim Morrison (sales@.kjmsoftware.com) writes:
> > SQL Server 2000 - MSDE 2000
> > Is there a way to get the number of current users logged into a SQL 2000
> > Server (also MSDE)? Cant be distinct users as most users are logged into
> > the database using the same login.
> select count(*) from master..sysprocesses where spid > 50 will give you
> a rough number. This will include logins from SQL Agent. Note that the
> same user can be using multiple connections from the same application,
> and thus be counted more than once.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I see that one of the fields is "program_name" in the sysprocesses table. I
have Crystal Reportsrunning so that is one of the processes. How do I get my
app to report itself as one of those processes like crystal?

Then I can do a 'SELECT count(*) FROM sysprocesses WHERE program_name =
'MyApp'.

TIA

Tim Morrison
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9445430E3C3FYazorman@.127.0.0.1...
> Tim Morrison (sales@.kjmsoftware.com) writes:
> > SQL Server 2000 - MSDE 2000
> > Is there a way to get the number of current users logged into a SQL 2000
> > Server (also MSDE)? Cant be distinct users as most users are logged into
> > the database using the same login.
> select count(*) from master..sysprocesses where spid > 50 will give you
> a rough number. This will include logins from SQL Agent. Note that the
> same user can be using multiple connections from the same application,
> and thus be counted more than once.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||You can specify it in the connection string, something like "Application
Name = etc" Its documented in the ADO/OLE DB/ODBC connection strings
documentation.

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:CxQyb.381793$HS4.3135751@.attbi_s01...
> I see that one of the fields is "program_name" in the sysprocesses table.
I
> have Crystal Reportsrunning so that is one of the processes. How do I get
my
> app to report itself as one of those processes like crystal?
> Then I can do a 'SELECT count(*) FROM sysprocesses WHERE program_name =
> 'MyApp'.
>
> TIA
> Tim Morrison
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns9445430E3C3FYazorman@.127.0.0.1...
> > Tim Morrison (sales@.kjmsoftware.com) writes:
> > > SQL Server 2000 - MSDE 2000
> > > > Is there a way to get the number of current users logged into a SQL
2000
> > > Server (also MSDE)? Cant be distinct users as most users are logged
into
> > > the database using the same login.
> > select count(*) from master..sysprocesses where spid > 50 will give you
> > a rough number. This will include logins from SQL Agent. Note that the
> > same user can be using multiple connections from the same application,
> > and thus be counted more than once.
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp

Getting next number

All,
Without using identity and assume i don't care if i have holes in my
numbers, is this the best way to get and set the next number?
update NUMID set @.x = NUMID.CS_ID_NUM = NUMID.CS_ID_NUM + 1 from numid
(nolock)
SteveAn update always obtains an exclusive lock on every row that is updated.
The only hint that is applicable here is WITH(ROWLOCK).
UPDATE NUMID WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1
"Steve Drake" <Steve@._NOSPAM_.Drakey.co.uk> wrote in message
news:udPNucYoFHA.568@.TK2MSFTNGP10.phx.gbl...
> All,
> Without using identity and assume i don't care if i have holes in my
> numbers, is this the best way to get and set the next number?
> update NUMID set @.x = NUMID.CS_ID_NUM = NUMID.CS_ID_NUM + 1 from numid
> (nolock)
> Steve
>|||Wouldn't this just increment each CS_ID_NUM by 1, is that what you want to
do?
Dan.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:eIvyelYoFHA.632@.tk2msftngp13.phx.gbl...
> An update always obtains an exclusive lock on every row that is updated.
> The only hint that is applicable here is WITH(ROWLOCK).
> UPDATE NUMID WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1
> "Steve Drake" <Steve@._NOSPAM_.Drakey.co.uk> wrote in message
> news:udPNucYoFHA.568@.TK2MSFTNGP10.phx.gbl...
>|||> Without using identity
Can you explain why?

> update NUMID set @.x = NUMID.CS_ID_NUM = NUMID.CS_ID_NUM + 1 from numid
> (nolock)
You can't use (nolock) here, this is a hint for retrieving data, and is not
applicable for an update (which needs to lock the row(s), for obvious
reasons).
If you are trying to avoid IDENTITY, I assume you are trying to avoid
anything that won't port, so how about the unproprietary:
BEGIN TRAN
SELECT @.x = MAX(CS_ID_NUM)+1 FROM NUMID
UPDATE NUMID SET CS_ID_NUM = @.x
COMMIT TRAN
I assume NUMID only has one row, you may consider a better design if there
are multiple columns, e.g.
CREATE TABLE NumIDs
(
NumType VARCHAR(32) PRIMARY KEY CLUSTERED,
NumValue INT - or BIGINT
)
Now you can insert a row for each number type you have, and there will be
less contention on the table because only that row has to be locked when
getting the next value.|||Aaron,
The unproprietary solution below is fraught with errors. Nothing prevents
another transaction from obtaining the same maximum value and attempting to
update the table with it. Worse yet, if the load on the server is heavy
enough, it is possible for several other transactions to update the table
between the select statement and the update statement. Statement execution
order is only deterministic within the same plan. The order in which
statements from different plans are executed depends on the load on each
processor, and thus cannot be determined with any acceptable level of
certainty. Using SELECT MAX to find the next number is a common error
usually committed by neophyte SQL Server developers. I'm surprised to see
it suggested by an MVP. I think you need to add WITH(UPDLOCK, HOLDLOCK) to
apply an update range-lock which is the only way to prevent both inserts
between the select and the update and deadlocks caused by shared locks being
held by another transaction while it's waiting to perform its update.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etNALXZoFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Can you explain why?
>
> You can't use (nolock) here, this is a hint for retrieving data, and is
not
> applicable for an update (which needs to lock the row(s), for obvious
> reasons).
> If you are trying to avoid IDENTITY, I assume you are trying to avoid
> anything that won't port, so how about the unproprietary:
> BEGIN TRAN
> SELECT @.x = MAX(CS_ID_NUM)+1 FROM NUMID
> UPDATE NUMID SET CS_ID_NUM = @.x
> COMMIT TRAN
> I assume NUMID only has one row, you may consider a better design if there
> are multiple columns, e.g.
> CREATE TABLE NumIDs
> (
> NumType VARCHAR(32) PRIMARY KEY CLUSTERED,
> NumValue INT - or BIGINT
> )
> Now you can insert a row for each number type you have, and there will be
> less contention on the table because only that row has to be locked when
> getting the next value.
>|||Whoa, whoa, whoa.
First of all, I asked why on earth they would be doing this anyway, instead
of IDENTITY.
Second, yes, if you apply the correct isolation level, you can certainly
achieve it the way I suggested. (I'm very sorry I displeased you by
forgetting that line.)
Third, I certainly don't think this confusing syntax (UPDATE NUMID
WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1) is any better a solution.
YMMV.
Fourth, when you're going to attack and demean someone in this way, it would
be appreciated if you do so in private mail.
A
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OXTIDxZoFHA.3068@.TK2MSFTNGP15.phx.gbl...
> Aaron,
> The unproprietary solution below is fraught with errors. Nothing prevents
> another transaction from obtaining the same maximum value and attempting
> to
> update the table with it. Worse yet, if the load on the server is heavy
> enough, it is possible for several other transactions to update the table
> between the select statement and the update statement. Statement
> execution
> order is only deterministic within the same plan. The order in which
> statements from different plans are executed depends on the load on each
> processor, and thus cannot be determined with any acceptable level of
> certainty. Using SELECT MAX to find the next number is a common error
> usually committed by neophyte SQL Server developers. I'm surprised to see
> it suggested by an MVP. I think you need to add WITH(UPDLOCK, HOLDLOCK)
> to
> apply an update range-lock which is the only way to prevent both inserts
> between the select and the update and deadlocks caused by shared locks
> being
> held by another transaction while it's waiting to perform its update.|||(1) I totally agree with you that IDENTITY is always a better solution than
rolling your own autonumber mechanism.
(2) Setting the isolation level is not enough, because two transactions
executing the same procedure can obtain shared locks on the same resource
during the select statement, and then deadlock on the update statement. If
you use SELECT MAX, then you have to use WITH(UPDLOCK, HOLDLOCK) to
serialize the select/update pairs as a unit to prevents this kind of
deadlock.
(3) The confusing syntax will not incur the problems caused by SELECT MAX
because the update statement will obtain an exclusive lock on the row while
it's being updated, and @.x will thus be different every time the statement
executes no matter how many concurrent users issue the statement. On the
other hand, I agree that this is not a good substitute for IDENTITY, because
it can also cause blocking and complicate deadlock avoidance if more than
one procedure can insert rows into the same table.
(4) I apologize for the neophyte remark. That was uncalled for. I think
it's necessary, however, to point out problems with code in public--rather
than leave it unchallenged--to save people with less experience the hassle
of finding the bugs the hard way, especially the kind that manifest
intermittently which as you know are the toughest to track down.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uTJuJ6ZoFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Whoa, whoa, whoa.
> First of all, I asked why on earth they would be doing this anyway,
instead
> of IDENTITY.
> Second, yes, if you apply the correct isolation level, you can certainly
> achieve it the way I suggested. (I'm very sorry I displeased you by
> forgetting that line.)
> Third, I certainly don't think this confusing syntax (UPDATE NUMID
> WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1) is any better a
solution.
> YMMV.
> Fourth, when you're going to attack and demean someone in this way, it
would
> be appreciated if you do so in private mail.
> A
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OXTIDxZoFHA.3068@.TK2MSFTNGP15.phx.gbl...
prevents
table
see
>|||> it's necessary, however, to point out problems with code in public--rather
> than leave it unchallenged--
Pointing it out to me in private would have allowed me the courtesy to
correct myself, instead of defending myself. Do you really think that if
you pointed it out to me that I would have ignored it? Instead I'm called a
neophyte in front of everyone... yeah, you're right, I should prefer your
methodology. :-(|||I did not set out this morning to embarass you. I assumed that you would
simply post "Whoops, brain freeze! Should've waited 'til I had my morning
coffee." and let it go at that. Instead, you decided to take offense. That
is your perogative. It is also my perogative to respond thus: Waaaah!!!
You chose to decorate your name with [SQL Server MVP] so that more people
will read and heed your advice. Are you so insecure that you must take
offense when someone publicly points out an incorrect or incomplete
response? Personally, I have no problem admitting that I'm not omniscient
or otherwise godlike (at least not until I've had my morning coffee): the
message I sent this morning to ten.xoc@.dnartreb.noraa bounced.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uebvT3aoFHA.3912@.TK2MSFTNGP10.phx.gbl...
public--rather
> Pointing it out to me in private would have allowed me the courtesy to
> correct myself, instead of defending myself. Do you really think that if
> you pointed it out to me that I would have ignored it? Instead I'm called
a
> neophyte in front of everyone... yeah, you're right, I should prefer your
> methodology. :-(
>|||> is your perogative. It is also my perogative to respond thus: Waaaah!!!
Ok, we're going to be mature about this; I'll remember that, all I was
asking for was a little courtesy. And my e-mail address is reversed to
thwart spam; hundreds of other people have managed to figure it out, I'm
sorry that you couldn't.

getting newly created id number(asap)

hello,

i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.

it works like:

insert into table() values()

select @.id = (newly created id number) from table

insert into table1() values(@.id)

something like that.

Hi lenerd,

You should usescope_identity for this!

Here are some links, please give it a look and try!

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

Hi lenerd3000

its too easy, i hope you know what command object is. execute scalar will return the primary key of newly generated record.

dim myID as integer =CType(myCommand.ExecuteScalar(),Integer)

hope it helps

getting length of 8 characters

Claim number (string)
CF060001
CF060001A
CF060001B
AV000001
AV000212F
AV000001F
FD232122
FD232122G
SD223213
SD223213H

I only want to get records, which have length of 8 characters.
So output will be CF060001, AV000001, FD232122, and SD223213

Anyone can help me to write this in sql?WHERE LEN(ClaimNumber) = 8

or possibly you will need

WHERE LEN(RTRIM(ClaimNumber)) = 8

Roy Harvey
Beacon Falls, CT

On 13 Dec 2006 12:55:20 -0800, "TGEAR" <ted_gear@.hotmail.comwrote:

Quote:

Originally Posted by

>Claim number (string)
>CF060001
>CF060001A
>CF060001B
>AV000001
>AV000212F
>AV000001F
>FD232122
>FD232122G
>SD223213
>SD223213H
>
>I only want to get records, which have length of 8 characters.
>So output will be CF060001, AV000001, FD232122, and SD223213
>
>Anyone can help me to write this in sql?

|||Roy Harvey (roy_harvey@.snet.net) writes:

Quote:

Originally Posted by

WHERE LEN(ClaimNumber) = 8
>
or possibly you will need
>
WHERE LEN(RTRIM(ClaimNumber)) = 8

Since len() does not count trailing blanks, rtrim is redudant here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 13 Dec 2006 22:43:30 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Since len() does not count trailing blanks, rtrim is redudant here.

Thanks for the education! 8-)

Roy|||Roy Harvey napisal(a):

Quote:

Originally Posted by

WHERE LEN(ClaimNumber) = 8
>
or possibly you will need
>
WHERE LEN(RTRIM(ClaimNumber)) = 8

.... or in case N data, and automaticly avoid problem with spaces:

where datalength(ClaimNumber)/2 = 8

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

Roy Harvey napisal(a):

Quote:

Originally Posted by

>WHERE LEN(ClaimNumber) = 8
>>
>or possibly you will need
>>
> WHERE LEN(RTRIM(ClaimNumber)) = 8

>
... or in case N data, and automaticly avoid problem with spaces:
>
where datalength(ClaimNumber)/2 = 8

Not really sure what you mean. len() counts characters and ignores
trailing spaces, so it is an ideal function to use in this case, as
it works the same with varchar and nvarchar data. (It does not work
with text/ntext though.)

datalength on the other hand counts bytes and includes trailing spaces,
so with datalength you need to trim and you need to know whether you
are working with varchar or nvarchar data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Getting error MS SQL2000

Hi ,

In one of our vendor's prog [VB], we always got this error when it try to assign seq number.

The whole process will run around 5 hrs and the last 5 minutes is the seq number assigning part.

[Microsoft][ODBC SQL Server Driver]Timeout expired

But using the same program etc..., when we re run the seq no assiigning only, it is fine.

Can some body help me to clear this.

thanks

rgs
ananth [Microsoft][ODBC SQL Server Driver]Timeout expiredHI ,

what did you set you connection time to , e.g. for the program to connect to the sql server?did you try setting it higher e.g. from 30 seconds to 60 seconds?