execution progress: field

Showing posts with label field. Show all posts

Friday, March 30, 2012

How to get Top 3 records in the group level?

Hi all,

I have a query which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. I tried using 'select top 3....', but the result is not what I wanted. It doesn't consider the grouping. Is there any way to achieve this task?

Please let me know if some body has any idea.

Thx.

The answer depends on whether or not you have SQL Server 2005 or SQL Server 2000. If you are using SQL Server 2005 you can use the ROW_NUMBER function. What is also needed is to know what field you are ordering your groups by (to get the top 3).

Dave

|||Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

How about this:

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(3), text_data NVARCHAR(50));

INSERT INTO t1 VALUES ('A', 'Text for A1');
INSERT INTO t1 VALUES ('A', 'Text for A2');
INSERT INTO t1 VALUES ('A', 'Text for A3');
INSERT INTO t1 VALUES ('A', 'Text for A4');
INSERT INTO t1 VALUES ('B', 'Text for B1');
INSERT INTO t1 VALUES ('B', 'Text for B2');
INSERT INTO t1 VALUES ('B', 'Text for B3');
INSERT INTO t1 VALUES ('B', 'Text for B4');
INSERT INTO t1 VALUES ('C', 'Text for C1');
INSERT INTO t1 VALUES ('C', 'Text for C2');
INSERT INTO t1 VALUES ('C', 'Text for C3');
INSERT INTO t1 VALUES ('C', 'Text for C4');

-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)

|||

Amde:

I set up a mock-up to compare Lucky's query with a query based on ROW_NUMBER. I created the T1 table with 32767 distinct R_ID entries and 30 distinct TEXT_DATA entries for each R_ID -- nearly 1,000,000 rows. When I ran Lucky's query with the table without any indexes it took about 102 seconds with 6.6 million logical IOs. I then added an index based on R_ID only and repeated the run. Again, it took about 102 seconds with 6.6 million logical IOs. I then looked at the SHOWPLAN and verified that the plan was still using a TABLE scan and was not using the index. This is expected because use of the index would require a prohibitive amount of bookmark lookups. I lastly dropped that index and added a cover index based on both R_ID and TEXT_DATA and repeated the run. This time it took 17 seconds with 3.1 million logical IOs.

Next, I repeated this using a query based on ROW_NUMBER. I first ran the query without any indexes. It took 7263 ms with 7709 logical IOs. I added an index based on R_ID only and it again took about 7600 ms with 7709 logical IOs. I then dropped that index and added an index based on both R_ID and TEXT_DATA. This query then took 2406 ms with 7706 logical IOs. This is the script I used for testing:

set nocount on
use tempdb
drop table t1
go

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(5), text_data NVARCHAR(50));
--exec sp_help t1

insert into t1
select convert (nvarchar (5), i.iter),
'Text for ' + convert (nvarchar(5), i.iter)
+ '-' + convert (nchar(2), j.iter)
from mugambo.dbo.small_iterator (nolock) i
inner join mugambo.dbo.small_iterator (nolock) j
on j.iter <= 30
--select * from t1

--drop index t1.t1_ndx
--create index t1_ndx on t1(r_id)
create index t1_ndx on t1(r_id, text_data)
update statistics t1
go

declare @.begDt datetime set @.begDt = getdate()

/*
--insert into @.receiver
-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)
*/

set statistics io on

select r_id,
text_data
from ( select r_id,
row_number () over
(partition by R_ID order by text_data)
as seq,
text_data
from t1
) x
where seq <= 3

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- r_id text_data
-- -- --
-- 1 Text for 1-1
-- 1 Text for 1-10
-- ...
-- 9999 Text for 9999-11

-- Table 't1'. Scan count 1, logical reads 7706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2220

|||

Hi,

keeping same table as Lucky_p has dipicted , just try this

select *from

(SELECT i.r_id, i.text_data,

DENSE_RANK() OVER (PARTITION BY i.r_id order by text_data) as RANK

FROM t1 i) A where rank<=3

Madhu

|||

That's all nice and i agree my solution is not fast when the table is large.

Do you have a fast solution for SQL 2000 too? All your solutions are for SQL 2005.

|||

Lucky:

I cannot come up with a method for SQL Server 2000 that is any faster than your version. Hopefully, one of the senior members can shed some light; I struck out.

Dave

|||

I found an alternative that might be of some use:

declare @.top3 table
( r_id nvarchar (5) not null,
seq integer not null,
text_data nvarchar (50) not null,

primary key (r_id, seq)
)

insert into @.top3
select r_id,
1,
min(text_data)
from t1
group by r_id

insert into @.top3
select r_id,
2,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a

insert into @.top3
select r_id,
3,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a
where seq = 2

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- Table '#33ADF722'. Scan count 0, logical reads 66286, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 1, logical reads 7640, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 97120, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98558, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 100528, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98552, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 2766

|||

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

|||

Lucky P wrote:

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

again with little modification u can fine tune this script.. eliminate Temp table

Select *from

(SELECT top 100 R_ID , text_data,

(SELECT COUNT(*) FROM T1 AS b

WHERE b.R_ID = a.R_ID

AND (b.text_data < a.text_data

OR (b.text_data = a.text_data))) AS rownum

FROM T1 AS a

ORDER BY R_ID, text_data) A

where rownum<4

Madhu

|||

I tested these other queries too with my "million-row" mockup and here is what I think:

If running SQL Server 2005 use either Madhu's or my first query|||

Your getting a partial result because of these line:

(SELECT top 100 R_ID , text_data,

If You change it to

(SELECT top 100 PERCENT R_ID , text_data,

You should get all the rows....

|||

Thanks for catching that lucky; I tested this one too and your original query is still much faster.

Dave

|||

ms sql server

data:

idno Email Category
1 a@.a.com c
2 b@.b.com c
3 b@.b.com c
4 b@.b.com c

only unique email address record are requierd.
1 a@.a.com c
2 b@.b.com c

what could be the query for such requirements.

How to get Top 3 records in the group level?

Hi all,

Please let me know if some body has any idea.

Thx.

Dave

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

How about this:

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(3), text_data NVARCHAR(50));

-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)

|||

Amde:

set nocount on
use tempdb
drop table t1
go
-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(5), text_data NVARCHAR(50));
--exec sp_help t1
insert into t1
select convert (nvarchar (5), i.iter),
'Text for ' + convert (nvarchar(5), i.iter)
+ '-' + convert (nchar(2), j.iter)
from mugambo.dbo.small_iterator (nolock) i
inner join mugambo.dbo.small_iterator (nolock) j
on j.iter <= 30
--select * from t1
--drop index t1.t1_ndx
--create index t1_ndx on t1(r_id)
create index t1_ndx on t1(r_id, text_data)
update statistics t1
go
declare @.begDt datetime set @.begDt = getdate()
/*
--insert into @.receiver
-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)
*/
set statistics io on
select r_id,
text_data
from ( select r_id,
row_number () over
(partition by R_ID order by text_data)
as seq,
text_data
from t1
) x
where seq <= 3
set statistics io off
print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:
-- r_id text_data
-- -- --
-- 1 Text for 1-1
-- 1 Text for 1-10
-- ...
-- 9999 Text for 9999-11

-- Table 't1'. Scan count 1, logical reads 7706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2220

|||

Hi,

keeping same table as Lucky_p has dipicted , just try this

select *from

(SELECT i.r_id, i.text_data,

DENSE_RANK() OVER (PARTITION BY i.r_id order by text_data) as RANK

FROM t1 i) A where rank<=3

Madhu

|||

That's all nice and i agree my solution is not fast when the table is large.

Do you have a fast solution for SQL 2000 too? All your solutions are for SQL 2005.

|||

Lucky:

I cannot come up with a method for SQL Server 2000 that is any faster than your version. Hopefully, one of the senior members can shed some light; I struck out.

Dave

|||

I found an alternative that might be of some use:

declare @.top3 table
( r_id nvarchar (5) not null,
seq integer not null,
text_data nvarchar (50) not null,
primary key (r_id, seq)
)
insert into @.top3
select r_id,
1,
min(text_data)
from t1
group by r_id
insert into @.top3
select r_id,
2,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a
insert into @.top3
select r_id,
3,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a
where seq = 2

set statistics io off
print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:
-- Table '#33ADF722'. Scan count 0, logical reads 66286, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 1, logical reads 7640, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 97120, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98558, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 100528, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98552, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 2766

|||

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

|||

Lucky P wrote:

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.
-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;
-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

again with little modification u can fine tune this script.. eliminate Temp table

Select *from

(SELECT top 100 R_ID , text_data,

(SELECT COUNT(*) FROM T1 AS b

WHERE b.R_ID = a.R_ID

AND (b.text_data < a.text_data

OR (b.text_data = a.text_data))) AS rownum

FROM T1 AS a

ORDER BY R_ID, text_data) A

where rownum<4

Madhu

|||

I tested these other queries too with my "million-row" mockup and here is what I think:

If running SQL Server 2005 use either Madhu's or my first query|||

Your getting a partial result because of these line:

(SELECT top 100 R_ID , text_data,

If You change it to

(SELECT top 100 PERCENT R_ID , text_data,

You should get all the rows....

|||

Thanks for catching that lucky; I tested this one too and your original query is still much faster.

Dave

|||

ms sql server

data:

idno Email Category
1 a@.a.com c
2 b@.b.com c
3 b@.b.com c
4 b@.b.com c

only unique email address record are requierd.
1 a@.a.com c
2 b@.b.com c

what could be the query for such requirements.

Wednesday, March 28, 2012

How to get the system date and time into database

Hi,

I m using ASP.NET with C#.
I m having one field which should store the datetime of the system.

The datetime should be automatically stored for that entry when the user submits that record on the click event.
Then it should display the date time on the gridview from database.

I m using sqlserver 2005 and i have created the stored procedure for the insert command.

Thisis the sample sp what should be written here to insert system date timeautomatically when the user submits the asp.net form ?

Is there any code for writing directly in stored procedure or asp.net coding page...


ALTER PROCEDURE [dbo].[StoredProcedure1]@.salesid INT OUTPUT,
@.salesdate datetime,
@.customername varchar(20)
AS
BEGIN
SET NOCOUNT ON
BEGIN
 INSERT INTO sales (customername)
 VALUES (@.customername)
 SELECT @.companyid = SCOPE_IDENTITY()
END
SET NOCOUNT OFF
END

Thanxs in advance...

Hi,

use getdate() function of sql server.

for more info look here

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

|||

You have a choice as to where to use the getdate() function (which returns the current system date and time).

You can actually define it on a column in the table itself so that you don't have to set it programmatically. Here is sample syntax:

create table sales (sales_id int identity, customer_name varchar(30) not null,sales_date datetime default getdate() not null)

Alternatively, within the stored procedure assign your variable with the function call. Here is sample syntax:

select @.SystemDate = getdate()

Monday, March 26, 2012

how to get the primary key from the field of the row ive just inserted

I need to insert a row of data and return the value of the primary key id of the row.
I thought that something like this would work


int Key = (int)command.ExecuteScalar();

where command is SqlCommand object.

It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.ExecuteScalar() returns the first row/first column of the resulet set. This should work IF part of the command contains something like SELECT Scope_IDentity() or SELECT @.@.IDENTITY after the insert, and the table has an IDENTITY column.sql

How to get the max length of numeric field in a DataSet?

How to get the max length of numeric field in a DataSet?
I have a DataSet bound to an Access database. Is it possible to get the maximum length of numeric field of a table in the DataSet? Many fields in the database tables have maximum length values set in ...

A VB.net or ADO.Net forum would be a better start, but just walk down the object hierarchy-

myDataSet.Tables(0).Columns(0).Length

Wednesday, March 21, 2012

How to get the current date in C# and how do I pass this date to SQL Server

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

you can get the current date directly in SQL using getdate()|||Doing it in C# will return you the client system date. Getting the current date in SQL will ensure all datetime recorded in your application are in sync. If you are using the date for some sort of comparison determine the sequence of event, using the date from the client might pose some timing issue. Unless what you want is really the client's local time, you should use the Server time.|||

brgdotnet:
I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.
So I have a question
1.) How do I get today's date in C# and how should this be passed to SQL server?

to get current date in c# use DateTime.Now.ToString()......... there is also some different methods under Now... use what is appropriate for u .

if u r using a procedure it is easier.... say proc. name saveCurrentDate

String s=DateTime.Now.ToString();

String queryString="saveCurrentDate '"+s+"'"; //// or String queryString="insert int myTable(mydate) values ('"+s+"')";

hope it will hellped u

|||

Actually I am processing some records from a comma delimited .txt file. Each item seperated by a comma, will map to a field in a SQL Server database table.

The date is in the format: 20070423, that is YYMMDD

So I need to take the date value and then write it into SQL server. So this brings us to another question. Do I need to change this date format to comply with that of SQL server? If so what format does it need to be in? I need some specifics so if you could even produce a code sample for C#, that would be great.

|||You can pass in the date in format YYYYMMDD as a string. SQL Server will implicitly convert it to string. As long as you are using Universal format YYYYMMDD, it is fine. Other format like MMDDYYYY or DDMMYYYY will be depending on the language setting that you used. So stick to YYYYMMDD and you will not go wrong.|||

I am confused about one thing though? I am reading from a text file where the format is in YYMMDD. Should I store this in SQL server as a data type of datetime or smalldatetime?

If so, I definitely have a date that, I can read from the text file, but I don't have a time? What do you think? If I did use datetime, would it just append the current time to the date I entered?

|||

you should always use proper data type for the data. In this case, you should use datetime or smalldatetime to store the date.

"I can read from the text file, but I don't have a time?"
You can still use datetime data type. Just set the time to 00:00:00. For your case, as your date string is in YYYYMMDD format without time, when you insert into table, the time will be stored as 00:00:00

How to get table records position in comparison to other records based on numeric field?

Hi,

Let's say I have 1000 registered users in database table and each of them has numeric ranking value.

How can I get the position of each user in comparison to other users ranking value?

If you're using SQL Server 2005, you can use the row_number() function. Are you?

Don

|||Hi,|||

Hi,

Yes, I am. Can you post some example code?

|||maybe you can post your table DDL, some sample data and the result that you want ?|||

Hi,

Solution found! Here's the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>"
SelectCommand="SELECT [usernickname], ROW_NUMBER() OVER(ORDER BY userrankavg DESC) AS 'position' FROM [dbusers]"
</asp:SqlDataSource>

|||

Hi,

That doesn't actually solve my problem.

The code does display position for each record when all records are selected from table, but doesn't when WHERE attribute is specified.

How can I get position for a specific record that I specify with WHERE attribute?

Monday, March 19, 2012

How to get size of an image data type field stored in my SQL Server Database

Hi,
I have a problem and don't know how to solve it.
I am storing files (jpg, etc) in a table as binaries (image data type)
and would like to be able to show progress bar when I am loading it
from database.
But to do that, I need somehow to get the size of that file that is
stored in database (as image data type). Unfortunatelly my table does
not have a 'file_size' column from where I could get the size of image
and I am not in position to change that.
So far I am reading or writing to databasse successfully using
FileStream or BinaryREader/Writer (in VB.NET), so that is not the
problem.
Does anybody know how can I get the size of an file stored in
database?
Any help will be greatelly appreciatedThis is a multi-part message in MIME format.
--030403020708070709060401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
SELECT DATALENGTH(MyImageColumn), MyImageColumn, ... FROM TableOwner.MyTable
WHERE ...
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Dino Buljubasic wrote:
>Hi,
>I have a problem and don't know how to solve it.
>I am storing files (jpg, etc) in a table as binaries (image data type)
>and would like to be able to show progress bar when I am loading it
>from database.
>But to do that, I need somehow to get the size of that file that is
>stored in database (as image data type). Unfortunatelly my table does
>not have a 'file_size' column from where I could get the size of image
>and I am not in position to change that.
>So far I am reading or writing to databasse successfully using
>FileStream or BinaryREader/Writer (in VB.NET), so that is not the
>problem.
>Does anybody know how can I get the size of an file stored in
>database?
>Any help will be greatelly appreciated
>
>
--030403020708070709060401
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>SELECT DATALENGTH(MyImageColumn), MyImageColumn, ... FROM
TableOwner.MyTable 
WHERE ... 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Dino Buljubasic wrote:
<blockquote cite="midj74v21t1saqrl2b25cr16dnc93mo00emv3@.4ax.com"
type="cite">
<pre wrap="">Hi,
I have a problem and don't know how to solve it.
I am storing files (jpg, etc) in a table as binaries (image data type)
and would like to be able to show progress bar when I am loading it
from database.
But to do that, I need somehow to get the size of that file that is
stored in database (as image data type). Unfortunatelly my table does
not have a 'file_size' column from where I could get the size of image
and I am not in position to change that.
So far I am reading or writing to databasse successfully using
FileStream or BinaryREader/Writer (in VB.NET), so that is not the
problem.
Does anybody know how can I get the size of an file stored in
database?
Any help will be greatelly appreciated
</pre>
</blockquote>
</body>
</html>
--030403020708070709060401--|||Try the following function
select datalength(imagecolumn) from table where x=y
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:j74v21t1saqrl2b25cr16dnc93mo00emv3@.4ax.com...
> Hi,
> I have a problem and don't know how to solve it.
> I am storing files (jpg, etc) in a table as binaries (image data type)
> and would like to be able to show progress bar when I am loading it
> from database.
> But to do that, I need somehow to get the size of that file that is
> stored in database (as image data type). Unfortunatelly my table does
> not have a 'file_size' column from where I could get the size of image
> and I am not in position to change that.
> So far I am reading or writing to databasse successfully using
> FileStream or BinaryREader/Writer (in VB.NET), so that is not the
> problem.
> Does anybody know how can I get the size of an file stored in
> database?
> Any help will be greatelly appreciated
>

How to get size of an image data type field stored in my SQL Server Database

Monday, March 12, 2012

How to get rid of time in a date/time field?

I am using vb.net 2005 with crystal and I am wanting to only show the date on the record being grabbed from SQL. currently it is showing the date along with the time stamp. Is there an easy way to do this in Crystal?I figured it out...format object :-) Easy enough!

How to get rid of dbo in field names?

Hello,
I just converted a website database from MS Access to SQL 2000, and many of
the pages are not working because SQL now wants fields to be ref'd as
"dbo.<<fieldname>>"... I've fixed this before, but cannot remember how... If
I cannot fix it through the database / connection, I'll have hundreds of
lines of code to modify.
Any suggestions?
Thanks in advance!!!!
--Jon
What are the login credentials of your application? Is it qualifying objects
with a different owner name? If so, s_changeobjectowner might help you.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"code" <code@.community.nospam> wrote in message
news:3BD82441-93C0-4CC9-A466-DD1E76291669@.microsoft.com...
> Hello,
> I just converted a website database from MS Access to SQL 2000, and many
> of
> the pages are not working because SQL now wants fields to be ref'd as
> "dbo.<<fieldname>>"... I've fixed this before, but cannot remember how...
> If
> I cannot fix it through the database / connection, I'll have hundreds of
> lines of code to modify.
> Any suggestions?
> Thanks in advance!!!!
> --Jon

Friday, March 9, 2012

How to get Reference from CompositePrimaryKey field ??...

I have one table nameMagazine_Details,it has a composite primary key

Create table Magazine_Details(MagazineTitlevarchar(60),MagazineTypevarchar(20),IssueTypevarchar(25),IssueNumbervarchar(10),Qntyint,Qnty_in_Handint,[Date]datetimeconstraint PK_Magazine_Details_Compositeprimary key(MagazineTitle,IssueNumber))

Another table isMagazine_Issued and i want reference from Magazine_Details table. but i am auable to create foreign key constaraint inMagazine_Issued table.

create table Magazine_Issued(Member_IDvarchar(100),MagazineTitlevarchar(60)references Magazine_Details(MagazineTitle),IssueNumbervarchar(10)references Magazine_Details(IssueNumber),IssueDatedatetime,Magazine_Statusvarchar(10))

Hi,

If you need to create the foreign key constraint, you also need to add the CONSTRAINT clause and constraint name.

create table Magazine_Issued
(
Member_ID varchar(100),
MagazineTitle varchar(60) CONSTRAINT FK_MagazineTitle_Details_Issued FOREIGN KEY (MagazineTitle) references Magazine_Details(MagazineTitle),
IssueNumber varchar(10) CONSTRAINT FK_IssueNumber_Details_Issued FOREIGN KEY (IssueNumber) references Magazine_Details(IssueNumber),
IssueDate datetime,
Magazine_Status varchar(10)
)

|||

after trying to create Magazine_Issued Table following error comes.

primary or candidate keys in the referenced table 'Magazine_Details' that match the referencing column list in the foreign key 'FK_MagazineTitle_Details_Issued'.

Could not create constraint. See previous errors.

|||

miazaidi:
after trying to create Magazine_Issued Table with above script following error comes.
primary or candidate keys in the referenced table 'Magazine_Details' that match the referencing column list in the foreign key 'FK_MagazineTitle_Details_Issued'.
Could not create constraint. See previous errors.

Wednesday, March 7, 2012

How to get NTEXT data via SP to vb.net app?

i have a table with ntext field and i need to get some fields including the
ntext value to a vb.net application (vs2003)
i have this routine in vb:
Public Function GetMsgs2Email() As DataTable
Dim da As New SqlDataAdapter
Dim result As New DataTable
Try
cmdSelect.CommandText = "SP_name_here"
cmdSelect.CommandType = CommandType.StoredProcedure
da.SelectCommand = cmdSelect
da.Fill(result)
Return result
Catch ex As SqlException
EDTUtils.LogFiles.Log(ex)
Catch ex As Exception
EDTUtils.LogFiles.Log(ex)
End Try
Return Nothing
End Function
How to write the SP that will return as a result set the ntext value with
other fields?
any other techniques will do as well
thanks in advance
raficreate proc dbo.boo as
select ntext_field, someotherfield, anotherone
from dbo.sometable s
where s.somefield=somevalue
"Rafi" <Rafi@.discussions.microsoft.com> wrote in message
news:5BEDBDC2-D19C-4F03-BEFE-391CA4696768@.microsoft.com...
>i have a table with ntext field and i need to get some fields including the
> ntext value to a vb.net application (vs2003)
> i have this routine in vb:
> Public Function GetMsgs2Email() As DataTable
> Dim da As New SqlDataAdapter
> Dim result As New DataTable
> Try
> cmdSelect.CommandText = "SP_name_here"
> cmdSelect.CommandType = CommandType.StoredProcedure
> da.SelectCommand = cmdSelect
> da.Fill(result)
> Return result
> Catch ex As SqlException
> EDTUtils.LogFiles.Log(ex)
> Catch ex As Exception
> EDTUtils.LogFiles.Log(ex)
> End Try
> Return Nothing
> End Function
> How to write the SP that will return as a result set the ntext value with
> other fields?
> any other techniques will do as well
> thanks in advance
> rafi

Friday, February 24, 2012

how to get LIST of fields in a report (designer)

Hi All,

When I'm building a report in report designer and get error message that says --error like --field textbox25--whats the easiest way to see all my report fields and quickly access them instead of clicking every field in a report?

Ideally a fields map with links to these fields.

thanks

Sonny

Hello Sonny,

If you press F4, it will open your Properties window; from here, you can select the report object you are looking for and it will select that object in your layout.

Hope this helps.

Jarret

how to get latest record from a table?

hi guys.
i have a table that store all transactions. In this table, a member can has multiple records, there is a field to store the date.
1. the problem i'm facing is, i need to retrieve only the latest record. for example, the transaction table contains of 20 records for 5 members, i need to retrieve the latest record for 5 members.
2. another problem that i face is, how can i make comparison with the date. let say i want to retrieve the record that more than 90 days?

thanks for all advises.

Engloon:

For #1 you need to make sure that you have an index on "member id" so that you can efficiently retrieve records based on the "member id."

For #2 you need to have an index baded on the date and you need to make sure that your field is has a "datetime" datatype.

declare @.member table
( memberId integer,
tranDT datetime
)
insert into @.member
select 5, '1/9/6' union all
select 5, '5/8/6' union all
select 5, '12/15/6' union all
select 5, '3/12/7'
--select * from @.member

/*
memberId tranDT
-- --
5 2006-01-09 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-12-15 00:00:00.000
5 2006-03-12 00:00:00.000
*/

select top 1
memberId,
tranDt
from @.member
order by tranDt desc

/*
memberId tranDt
-- --
5 2006-12-15 00:00:00.000
*/

select memberId,
tranDt
from @.member
where tranDt < getdate() - 90
order by tranDt desc

/*
memberId tranDt
--
5 2006-12-15 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-01-09 00:00:00.000
*/

|||

1. This should give you an idea about how to get the latest entry for each member. (I assume you have a MemberID column.)

Code Snippet

SELECT
MemberID,
max( TransactionDate )
FROM MyTable
GROUP BY MemberID
ORDER BY MemberID

2. Add a WHERE clause, something like this:

Code Snippet

WHERE TransactionDate < ( dateadd( day, -90, getdate() ))

|||many thanks to Arnie Rowland and Kent Waldrop. I appreciate your help.
but the @.member table contains not only 1 member. I'm thinking to use distinct function to get only 1 record for each member.
Will let you know if I can solve the problem.

|||here is the result of my sql query
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
8 18-Sep-2005
8 18-Sep-2005
187 16-May-2006
187 14-May-2006
187 06-Jun-2006
187 29-Jul-2005
187 05-Jan-2007
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
303 19-Dec-2006
306 03-Oct-2005
306 16-Dec-2005

the result that i desire would be like this
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
187 16-May-2006
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
306 03-Oct-2005
|||

Use the following query...

Code Snippet

Select Identity(Int,1,1) as UID,Id,Date Into #Temp from Members;

Select Mem.Id,Mem.Date From #Temp Mem
Join (Select Min(UId) UID,Id from #Temp Group By Id) as LastRec On LastRec.UID = Mem.UID;

Drop Table #Temp;

|||

Strange, it 'sounds' like you are 'blowing off' the solution that I provided. Too bad, because using the data you posted, and the query I provided, the exact resultset you asked for is produced.

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
TransactionDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005' )

SELECT
MembershipID,
TransactionDate = max( TransactionDate )
FROM @.MyTable
GROUP BY MembershipID
ORDER BY MembershipID

MembershipID TransactionDate
3 2007-01-03 00:00:00.000
8 2006-08-05 00:00:00.000
187 2007-01-05 00:00:00.000
195 2006-03-14 00:00:00.000
239 2005-08-29 00:00:00.000
275 2005-08-07 00:00:00.000
303 2006-12-19 00:00:00.000
306 2005-12-16 00:00:00.000

|||

But your query doesn't give the last row, it gives the max tran date. He wants the row with the max tran date:

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
Date datetime,
someOtherColumn int default (100),
yetAnotherColumn int default (100)
)

SET NOCOUNT ON

INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '16-Dec-2005' )

select membershipId, date, someOtherColumn, yetAnotherColumn
from (select membershipId, date, someOtherColumn, yetAnotherColumn,
row_number() over (partition by membershipId order by date desc) as rowNum
from @.MyTable) as myTable
where rowNum = 1

|||

You are right Louis -but in defense, I was working with the data he provided, assuming that we were working with the 'first step'. (It's so much easier when folks provide us with an adequate explanition and sample data...)

The query that I provided should be used as a derived table and then would provide the requested data -and it works in both SQL 2000 and SQL 2005.

(However, there is one significant issue -how to deal with duplicate MembershipID/Date records -and of course, your proposed solution has that same problem).

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
[Date] datetime,
MyOtherColumn int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007', 1 )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006', 2 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 3 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 4 )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006', 5 )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006', 6 )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006', 7 )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005', 8 )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007', 9 )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006', 10 )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005', 11 )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005', 12 )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005', 13 )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006', 14 )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005', 15 )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005', 17 )

SELECT
t.MembershipID,
t.[Date],
t.MyOtherColumn
FROM @.MyTable t
JOIN ( SELECT
MembershipID,
[Date] = max( [Date] )
FROM @.MyTable
GROUP BY MembershipID
) dt
ON ( t.MembershipID = dt.MembershipID
AND t.[Date] = dt.[Date]
)
ORDER BY t.MembershipID

MembershipID Date MyOtherColumn
-
3 2007-01-03 00:00:00.000 1
8 2006-08-05 00:00:00.000 2
187 2007-01-05 00:00:00.000 9
195 2006-03-14 00:00:00.000 10
239 2005-08-29 00:00:00.000 11
275 2005-08-07 00:00:00.000 12
303 2006-12-19 00:00:00.000 14
306 2005-12-16 00:00:00.000 17

|||i'm so sorry, i should give an example of the result i need in the begining.
and many thanks to Arnie and all programmers who tried to help me.
i really appreciate it.

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> glsD:uem$6elfGHA.5088@.TK2MSFTN
GP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> glsD:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>> Image (and text/ntext) data types cannot be declared as local variables.
>> These are mostly intended to be transferred to/from application code
>> directly.
>> What do you plan to do with the image value in the proc? You can use
>> SUBSTRING to assign 'chunks' of the image value to a local varbinary
>> variable.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "angi" <angi@.news.microsoft.com> wrote in message
>> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>>
>

Sunday, February 19, 2012

How to get identity field value after recordset update

I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work.

This is what I have:

rsRec.AddNew
rsRec("RequisitionID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
intItemId = rsRec("ReqChildID")
rsRec.Close

Forgive me if this has already been posted, but I couldn't find it.

[edit to add:]

rsRec is an ADODB.Recordset in case that wasn't clear.Look at @.@.IDENTITY and SCOPE_IDENTITY in BOL.|||1 intItemId set identity for replication
2 use keyset recordset type|||I appreciate the quick replies.

I am using @.@.Identity and it works well enough for my purposes.

[edit] I found the BOL. Never even looked at it before. Thanks for pointing it out

Thanks again all.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.

Agree. But it's good to know what differs those cases. Let the user choose what he needs. Give him fishing rod, not fish ;) .|||Due to time constraints I went with @.@.Identity. The database hits aren't very common so I will assume a certain small probability that there will be an error from time to time.

That being said, I would prefer Scope_Identity for 100% accuracy. I am continuing to work with SCOPE_IDENTITY when I can to try to make it work for me. As of this morning I got it to supply the correct ID using enterprise manager to test. I'll eventually get it to work in my code.

Thank you guys for helping me solve this problem.|||@.@.identity may acquire a value of anything else only when the target table contains a trigger that performs an insert into another table with identity field. if this is the case then scope_identity() needs to be used.

on another thought, you should put dml statements into a stored procedure and call it from your front end.|||I inherited this app. I would love to convert it to using stored procedures. I just need to get this section done so I can have a completed app, and a happy boss. Then I can convert as I go to make it all easier to work with.

Grrrr... I can't seem to get SCOPE_IDENTITY() to happen on the page.

I have:

set cn = Server.CreateObject("ADODB.Connection")
set rsRec = Server.CreateObject("ADODB.Recordset")
set rsIdent = Server.CreateObject("ADODB.Recordset")
cn.Open dbConnection

rsRec.Open sSQL,cn, 3 ,3
rsRec.AddNew
rsRec("ReqID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
rsRec.Close

sSQL = "SELECT SCOPE_IDENTITY() as Ident"
rsIdent.Open sSQL,cn, 3 ,3
intItemId = rsIdent("Ident")
rsIdent.Close

I have no idea why this doesn't work. I thought as long as I keep the connection(cn) open then this would work. Guess I'm wrong? Do I need to use the same recordset?|||sSQL="insert tbl (...) values (...) select ident=scope_identity()"
set rsRec=cn.execute(sSQL)|||I have simplified the statements that are actually in the code. Although that suggestion would likely make it work(as it works when I test it in the Query Analyzer), there are quite a few conditions that would affect the SQL statement and at this point I don't have the time to switch it to that style.

Its' done for the time being. I pushed it out for beta.

I'm only sorry I couldn't get it all to work fast enough, but that's what updates are for.

Thanks again for the help and pointing me in the right direction.

How to get ID into field upon insert (SCOPE_IDENTITY() ?)

I have two fields:

ID - primary key and identity field (seed 1 increment 1)
GroupID - int

Upon INSERT I want groupID to be the value of ID

The only way I can figure out how to do this (being inexperienced with sql) is to INSERT with GroupID = 0 and then do a SELECT @.iGroupID=SCOPE_IDENTITY() and then UPDATE the record. But even inside a transaction this seems error prone and not the right way. Can anyone tell me the correct way to do this. Thank you in advance.You can try using a trigger.


CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[GroupID] [int] NOT NULL ,
[AnotherColumn] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]CREATE TRIGGER [InsertTrigger] ON [dbo].[Table1]
AFTER INSERT
AS
declare theCursor cursor for SELECT ID FROM inserted
open theCursor
declare @.ThisID int
select @.ThisID = -1
while 1 = 1
begin
fetch next from theCursor into @.ThisID
if @.@.fetch_status <> 0
 break
Update Table1
Set GroupID = @.ThisID
WHERE ID=@.ThisID
end
close theCursor
deallocate theCursor

|||I hope you are using SQL Server 2000 ... If that being the case we can use UDFs to accomblish this task ... A typical code snippet for the same looks like below:

CREATE FUNCTION dbo.GetIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('vin_test'))
END

CREATE TABLE vin_test
(
colAINT IDENTITY(1,1) NOT NULL,
colBINT DEFAULT dbo.GetIdentity(),
colCVARCHAR(10)
)

INSERT INTO vin_test (colC) VALUES ('Test')
SELECT * FROM vin_test|||Yes the ISP is server 2000 and my MSDE seems to support functions but prompts me to select from Inline, Table-values, Scalar-valued, can you please tell me which to select. Also I have about 20 tables I need to do this with. Do I need to write a function for each table?

THANKS!|||Yes, we need to create one for each table. I am sure it is better than creating 10 triggers ... :)

Select the scalar-valued function ...|||My final question:

Is the UDF better/safer/more stable/reliable then what I am doing in the stored procedure where inside a transaction I insert, get the scope_identity and then update the just inserted record. I understand my (hack/kludge?) method may take more server processing but I besides that I am wondering if it is any less reliable then the UDF method. (last question :) again, thanks.|||I can say it with certain that UDFs are quite stable ... There is no second thoughts on that statement. We have used this in our production code and have tested the same with concurrent users ... Works like a gem ... :) ...

Hmm ... the second part of the question has a couple of interesting points to backup:
1. Keep the transaction short. Which means if you have a big routine to do the same it is quite not advisible.
2. CPU cycles are not cheaper and so is memory. If you were to use SPs in such a fashion then we are using both ... :) ... And I dont recommend the same strongly ...

I know this is a delicate topic to answer and can raise a couple of eye-brows who are reading this ... But these are my views on the same. I am open to hear from others if they think otherwise ...|||stored procedures return only integers...UDF can return other datatypes like float...etc
other than that i guess both are similar...in terms of security/performance...etc|||Hmm ... Stored Procedures can also return more than one value using the OUTPUT parameter types ... And I feel that we can do things in UDF that are quite not possible with Stored Procedures ... Read my article on UDF at :http://www.extremeexperts.com/sql/articles/UDFFunctions.aspx ...

And for the present problem description ... I feel UDF is a cleaner and neater way rather than the conventional UDF approach ...

Friday, March 30, 2012

How to get Top 3 records in the group level?

How to get Top 3 records in the group level?

Wednesday, March 28, 2012

How to get the system date and time into database

Monday, March 26, 2012

how to get the primary key from the field of the row ive just inserted

How to get the max length of numeric field in a DataSet?

Wednesday, March 21, 2012

How to get the current date in C# and how do I pass this date to SQL Server

How to get table records position in comparison to other records based on numeric field?

Monday, March 19, 2012

How to get size of an image data type field stored in my SQL Server Database

How to get size of an image data type field stored in my SQL Server Database

How to get size of an image data type field stored in my SQL Server Database

Monday, March 12, 2012

How to get rid of time in a date/time field?

How to get rid of dbo in field names?

Friday, March 9, 2012

How to get Reference from CompositePrimaryKey field ??...

Wednesday, March 7, 2012

How to get NTEXT data via SP to vb.net app?

Friday, February 24, 2012

how to get LIST of fields in a report (designer)

how to get latest record from a table?

How to get Image value in SP

How to get Image value in SP

Sunday, February 19, 2012

How to get identity field value after recordset update

How to get ID into field upon insert (SCOPE_IDENTITY() ?)

execution progress

Blog Archive

About Me