Retrieve output value from store procedure

I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.

The item is added into the db but the output param is not modified.

Here is the code:

SP

CREATE procedure dbo.AddItem
(
@.Desc nvarchar(100),
@.intItemID int output
)
as
insert into RR_Item (
desc
)
values (
@.Desc
)

select @.intItemID = SCOPE_IDENTITY()
GO

I have tried in the last line of the SP

select @.intItemID = SCOPE_IDENTITY()

select @.intItemID = @.@.IDENTITY

select @.intItemID = max(itemid) from RR_Item

but nothing seems to work.

I'm calling the store procedure as follows from asp.net:

Dim intItemIDAsNew SqlParameter("@.intItemID", SqlDbType.Int)

intItemID.Direction = ParameterDirection.Output

SqlHelper.ExecuteDataset(objConn.ConnectionString,"AddItem", desc, intItemID)

MsgBox(intItemID.Value.ToString)

Any help would be appreciated.

Thanks

(1) Use SCOPE_IDENTITY().

(2) check the 2nd portion ofthis articleto retrieve the OUTPUT value.

Retrieve output parameter from stored procedure

Hi,

I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error

An SqlParameter with ParameterName '@.slideshowid' is not contained by this SqlParameterCollection

code as follows:

int publicationId = (int) Session["PublicationId"];
string title = txtTitle.Text;
int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand =new SqlCommand("sp_be_addSlideshow", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@.publicationid", SqlDbType.Int));
myCommand.Parameters["@.publicationid"].Value = publicationId;
myCommand.Parameters.Add(new SqlParameter("@.title", SqlDbType.NVarChar));
myCommand.Parameters["@.title"].Value = title;
myCommand.Parameters.Add(new SqlParameter("@.categoryid", SqlDbType.Int));
myCommand.Parameters["@.categoryid"].Value = categoryid;
myConnection.Open();
myCommand.ExecuteNonQuery();
string slideshowId = myCommand.Parameters["@.slideshowid"].Value.ToString();
myConnection.Close();

my stored procedure:

CREATE PROCEDURE sp_be_addSlideshow
(
@.publicationid int,
@.title nvarchar(50),
@.categoryid int,
@.slideshowid int = NULL OUTPUT
)
AS

INSERT INTO
Slideshow(publicationid,title,slideshowcategoryid,deleted)

VALUES
(@.publicationid,@.title,@.categoryid,0)

SELECT @.slideshowid = SCOPE_IDENTITY()
GO

Supergirl_gem,

You need to specify to your command that that parameter is an output param. Try this.

myCommand.Parameters.Add("@.slideshowid", SqlDbType.Int);
myCommand.Parameters["@.slideshowid"].Direction = ParameterDirection.Output;

Then after the execute, you can set the id

string slideshowId = myCommand.Parameters["@.slideshowid"].Value.ToString();

Hope this helps. If you are still having problems. Please let me know.

Regards.

Retrieve ONLY the Report Items that a user has permissions for

Using ListChildren(@."\",true) returns all items in the report server that a
user has access to, however if the user does not have access to view the home
folder, the call fails.
How do I retrieve ALL the folders/reports that a user has permission for
from an application running with the users default credentials?You would have to write or run the program with an admin account & then once
you get a list of all the children,
you'll have to loop through all of them to determine which one belong to the
X-User.
Edgar,
"DaveH" wrote:
> Using ListChildren(@."\",true) returns all items in the report server that a
> user has access to, however if the user does not have access to view the home
> folder, the call fails.
> How do I retrieve ALL the folders/reports that a user has permission for
> from an application running with the users default credentials?

retrieve only new customers with orders

I have a table with orders and customer info. I want to retrieve only customers with new orders only. These are the available fields: customer Id, Order Id, Order Date, and Order Subtotal. I need help with the "where" section of the query.

Hi Ray, this mainly depends on how you define "new" relative to the date values in your data. But assuming "new" means orders placed today, you could use something like "where datediff(dd,OrderDate,getdate()) = 0". You can review the SQL Server books online for more information about getdate(), datediff(), or the other date functions you could use here.

Thanks, Dean

Retrieve only last 4 records. Date descendent ordered.

Hello,
I want to load only the last 4 records of a MSSQL table considering they
are ordered descendent by date. What should my SQL be?
Thank You,
MiguelHi
You can use the TOP function to do this. See Books online for more details
SELECT TOP 4 MyDate
FROM MyTable
ORDER BY MyDate DESC
John
"Shapper" <mdmoura*NOSPAM*@.gmail.*DELETE2SEND*com> wrote in message
news:eGCRmjmTFHA.2172@.tk2msftngp13.phx.gbl...
> Hello,
> I want to load only the last 4 records of a MSSQL table considering they
> are ordered descendent by date. What should my SQL be?
> Thank You,
> Miguel
>|||DECLARE @.intMyCount int
SET @.intMyCount = 4
SET ROWCOUNT @.intMyCount
SELECT MyDate
FROM MyTable
ORDER BY MyDate DESC
-- disable rowcount
SET ROWCOUNT 0
Message posted via http://www.webservertalk.com

Retrieve ONLY first/max

David Portas wrote:

Quote:

Originally Posted by

Alternatively you can do the following using standard ANSI SQL, which should
work on many different platforms:
>
SELECT type
FROM ord, product
WHERE ord.id = product.id
GROUP BY type
HAVING SUM(units) >= ALL
(SELECT DISTINCT SUM(units)
FROM ord, product
WHERE ord.id = product.id
GROUP BY type);
>
(untested)

This could return multiple values if there's a tie for most
common type.On 18 Sep, 08:30, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

>
This could return multiple values if there's a tie for most
common type.

Correct. So could the version using TOP WITH TIES. Mark said: "I want
to know the type that has the max units in any group". If there is
more than one such type then the specification is incomplete because
Mark doesn't say which one should come "first". Rather than pick a
random row or make the assumption that there is only one row I decided
it was safer to return everything - that way Mark can decide for
himself whether he needs to refine his spec.

--
David Portas|||On Sep 18, 5:27 am, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

On 18 Sep, 08:30, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>

Quote:

Originally Posted by

This could return multiple values if there's a tie for most
common type.

>
Correct. So could the version using TOP WITH TIES. Mark said: "I want
to know the type that has the max units in any group". If there is
more than one such type then the specification is incomplete because
Mark doesn't say which one should come "first". Rather than pick a
random row or make the assumption that there is only one row I decided
it was safer to return everything - that way Mark can decide for
himself whether he needs to refine his spec.
>
--
David Portas

Thanks guys. You'd think they'd have a "standard" (and simple) method
for doing this eh?

retrieve only 1 row for each ID (was "Help on SQL! Urgent..")

i have a table called tblpictures which look something like this..

filename|ID
----
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3

is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..Which row would you want?

If you just want a distinct list of IDs, then this will do:

select distinct ID from YourTable|||erm no i want to show 1 filename and 1 ID for each ID|||You could probably get away with :

select max(filename), id from tblPictures group by id

if this is what you are looking for ...

:cool:

Retrieve One Row at a time

Hi,

I am going to be difficult here... How do I retrieve one row at a
time from a table without using a cursor?

For example, I have a table with 100 rows. I want to retrieve the
data from row 1, do some stuff to it and send it on to anther table,
then I want to grab
row 2, do some stuff to it and send it to another table.

Here is how I am envisioning it:

WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
rowcount)
BEGIN
SELECT * FROM [TABLE] BUT ONLY ONE ROW
... MANIPULATE THE DATA
INSERT into another table
END

Other notes, I am using SQL Sever 2000...
Thanks and in advance and as always the help is greatly appreciated.

Regards,

CLRI don't know why you don't want to use a cursor which is probably the most
suitable means to solve your problem. But anyway, you have some other
options like these:

1. Add a flag to your table. After proccessing each record set the flag and
select the next nonprocessed record (using select top 1).

2. Copy all the records you want into a temporary table and again using
selectp top 1 read them one by one and delete them after processing.

3. Use a temporary table as a list of processed records, after processing
each record add its key to this list and select next record where its key
does not belong to this list.

If you give us more information about what you are exactly looking for and
what your problem is, you'll have a better chance to get the solution.

Shervin

"Chris" <chris@.dagran.com> wrote in message
news:736fadb1.0309301643.572f3730@.posting.google.c om...
> Hi,
> I am going to be difficult here... How do I retrieve one row at a
> time from a table without using a cursor?
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
> Here is how I am envisioning it:
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> ... MANIPULATE THE DATA
> INSERT into another table
> END
> Other notes, I am using SQL Sever 2000...
> Thanks and in advance and as always the help is greatly appreciated.
> Regards,
> CLR|||Chris (chris@.dagran.com) writes:
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
> Here is how I am envisioning it:
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> ... MANIPULATE THE DATA
> INSERT into another table
> END
> Other notes, I am using SQL Sever 2000...
> Thanks and in advance and as always the help is greatly appreciated.

SELECT TOP 1 @.key = keycol, @.var1 = col1, @.var2 = col2'
FROM tbl
WHERE keycol > @.key
ORDER BY keycol

If you have a multi-column, you can still do this, but logic becomes
hairier.

--
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 want to thank you both for your answers they have helped
tremendously. We are going to use a cursor for our problem as well, I
just wanted another way of handling what we are triyng to accomplish.
We have a table with over a million rows, which from one row we will
query about 5 other tables to extract more information which will be
sent to a new table, then we grab the next row and so on and so forth.
We want to try using a cursor and anther method to see which way
would be more CPU friendly. I feel it doesn't really matter which way
we go, they both will take over my computer. Thanks though for your
responses, it has helped us out a lot!

Regards,

CLR|||Chris (chris@.dagran.com) writes:
> I want to thank you both for your answers they have helped
> tremendously. We are going to use a cursor for our problem as well, I
> just wanted another way of handling what we are triyng to accomplish.
> We have a table with over a million rows, which from one row we will
> query about 5 other tables to extract more information which will be
> sent to a new table, then we grab the next row and so on and so forth.

A million rows iteratively? That could take a couple of days! Sometimes
this can be justified, if it's sort of a one time operation. (Actually,
I was recently involved in writing a task that took 3 days to complete.)

But if you can find a set-based operation, you can win lots of
performance.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Retrieve next lowest number in mixed set

Let's say I have a result set (records) containing numbers such as:

0
1
2
3
9
16
21
45
100
101
102
103
104
105
106

How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.

Thoughts? Thanks..."Zamdrist" <zamdrist@.gmail.comwrote in message
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

Let's say I have a result set (records) containing numbers such as:
>
0
1
2
3
9
16
21
45
100
101
102
103
104
105
106
>
How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.
>
Thoughts? Thanks...
>

SELECT MIN(x)+1 x
FROM tbl t
WHERE NOT EXISTS
(SELECT x
FROM tbl
WHERE x = t.x+1);

--
David Portas|||On Oct 4, 3:31 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

"Zamdrist" <zamdr...@.gmail.comwrote in message
>
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...
>
>
>

Quote:

Originally Posted by

Let's say I have a result set (records) containing numbers such as:

>

Quote:

Originally Posted by

0
1
2
3
9
16
21
45
100
101
102
103
104
105
106

>

Quote:

Originally Posted by

How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.

>

Quote:

Originally Posted by

Thoughts? Thanks...

>
SELECT MIN(x)+1 x
FROM tbl t
WHERE NOT EXISTS
(SELECT x
FROM tbl
WHERE x = t.x+1);
>
--
David Portas

Thanks!|||"Zamdrist" <zamdrist@.gmail.comwrote in message
news:1191525448.343516.292440@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

Let's say I have a result set (records) containing numbers such as:
>
0
1
2
3
9
16
21
45
100
101
102
103
104
105
106
>
How might someone write a procedure to get the next lowest number in
this sequence? In this case it would be: 4. Some combination of
Select, Min & When, I am sure, but it escapes me at the moment.
>
Thoughts? Thanks...
>

Another method (SQL Server 2005 only):

SELECT MIN(x) x
FROM
(SELECT x+1 FROM tbl
EXCEPT
SELECT x FROM tbl) t(x);

--
David Portas|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules.

Quote:

Originally Posted by

Quote:

Originally Posted by

>How might someone write a procedure to get the next lowest number in this sequence? In this case it would be: 4. <<

No; read your own specs! The answer is -1. Or maybe -0.0000...1 if
the column is a DECIMAL or a FLOAT. If you had posted DDL that
limited the column to non-negative integers, then Dave's answer would
work.|||On Oct 4, 2:17 pm, Zamdrist <zamdr...@.gmail.comwrote:

Quote:

Originally Posted by

In this case it would be: 4.

SELECT MIN(t2.x +1)
FROM t FULL JOIN t t2 ON t.x = t2.x +1
WHERE t.x IS NULL;

Retrieve new values of a database

Hello, I have the view from a table that keeps incrementing with new
values, I have no control of this database, but I need to retrieve the
new values to insert it on my table (sql server) and send some
parameters of the database to another application, I'm using C# to get
access to both databases, so does anybody know of an efficient way to
retrieve only the new values of the database?

Thanks a lot

Luis Saavedra

Luis,
I don't think there is any general solution to your problem. I usually use triggers, but since you said you have no control on the db that won't work.

The only solution I can think of is to see it your table contains some identity column. If that is the case (and it often is), then you may just select data from the table with an identity column greater than the last you found. This will only work to get new additions to the db. It cannot detect deletions.

HTH
--mc

|||

the problem is that the identity column appears to be autoincremental, but is not in all of the cases, for example I have something like

1001

1002

1003

1005

1007

1006

1004

what I have considered is using a counter (of the inserted registers) in a table of my database, then use a count of all the registers of the informix table and get a top (of the difference between these two tables), the problem is that if in the lapse between the count and the selection of the registers they insert something in the table of informix, I will be missing some values, any ideas to solve this or use another solution...

Thanks

Luis Saavedra

|||

I think you're on the right track here. You might want to store the same values in that field from the source system in your own column in a table on your system. You can then compare based on a join. That might not work for you if there are a lot of values or it is updated a lot. In that case, is there a "Row Number" in your source database you can get at pprogramatically? If so, you could store the last value of that column, which will increment, in one row of your table and update based on it. Does that make sense? Something like this:

Source DB:

(Hidden RowNumber feature) IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

(5) 1007

(6) 1006

Your DB:

Tracking Number IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

Buck Woody

http://www.buckwoody.com

Retrieve name of running stored procedure

Hello everybody,

I would like to know if there is a function to retrieve the name of the running stored procedure, like the built-in function "DB_NAME()" to retrieve the name of the database.

I need to retrieve the name for logging possibilities.

Thank you in advance!

Bastiaan Molsbeck.

Bastiaan:

There is no direct way to do this; however, if what you have in mind is to be able to get the name of an executing stored procedure in a trigger so that you can retain this information you can (1) use SET CONTEXT_INFO in the stored procedure to store the name of the calling proc and then (2) use the CONTEXT_INFO() function from the trigger to retrieve this information. Keep in mind that CONTEXT_INFO is BINARY(128) so you will need to convert the name to binary to execute SET CONTEXT_INFO and will need to translate from BINARY back to VARCHAR when using the CONTEXT_INFO() function. Take the time to look up these two topics in books online. Here are a couple of previous threads in which there was a similar discussion:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=485119&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1234752&SiteID=1

You will need to include something like this in the procedure:

declare @.contextBinary varbinary (128)
set @.contextBinary = ( select convert(varbinary(128), 'MyProcedureName') )

set context_info @.contextBinary

and something like this in the trigger:

declare @.callingProcedure varchar(128)

set @.callingProcedure = ( select convert(varchar(128), context_info()) )

|||

Hm, I was trying to find a way to accomplish that WITHOUT having to write the name of the stored procedure in it.

But thanks anyway. :-)

|||

If youi are talking about retrieving it from within the executing stored procedure, use object_name(@.@.proc_id) as in:

create procedure test

as

select object_name(@.@.procid)

go

exec test

This will return:

test

|||

Yes, that's exactly what I need.

Many thanks!

Retrieve multiple variables from Stored Procedure (SQLHelper)

Hi all,

I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:

ie:

SELECT @.Hits = COUNT(DISTINCT StatID) FROM Stats

...etc...

The SP is currently called as below:

SqlParameter[] sqlParams = new SqlParameter[]
 {
 new SqlParameter("@.FromDate", Request["FromDate"]),
 new SqlParameter("@.ToDate", Request["ToDate"]),
 };

My question is this: How do I retrieve these variables?

I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.

Thanks in advance,

PeteHi.

Could u please expain how you are returning the values.. all 3 as output parameter?

as the code will depend on the way the values are returned..|||my full Stored Procedure looks as so:

CREATE PROC GetGeneralStats
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
Declare @.uniqueHits int
Declare @.noOfSearches int
SELECT @.uniqueHits = COUNT(DISTINCT StatID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
-- No of times Searchresults Shown
SELECT @.NoOfSearches = COUNT(SectionID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
AND SectionID = 3
Go

Cheers,

Pete|||This procedure is retieving and storing values in 2 variables and returning nothing.

you need to add 2 output parameters to the procedure..

CREATE PROC GetGeneralStats
@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.uniqueHits int OUTPUT,
@.noOfSearches int OUTPUT
AS
SELECT @.uniqueHits = COUNT(DISTINCT StatID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
-- No of times Searchresults Shown
SELECT @.NoOfSearches = COUNT(SectionID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
AND SectionID = 3

|||Hi,

Thanks for the reply.

As you said (and now having looked in the documentation) this is how it works but....

Im getting

Procedure 'GetGeneralStats' expects parameter '@.uniqueHits', which was not supplied.

So its thinking that I want them as Input variables? Even though I have them as:

@.uniqueHits int OUTPUT,
@.noOfSearches int OUTPUT
AS

pete|||Arrgghhh!!! Everywhere I look Im told to do it this way but Im getting this stupid error message!

Im tearing my hair out over this!!!!!!!

Retrieve list of tables without triggers

I need to do some db cleaning, how can I get a list of tables within the
database that do not have any triggers.
Thanks in advanceHi scuba79,
Try the following on your database:
select name from sysobjects
where xtype = 'U'
and id not in
(
select parent_obj from sysobjects
where xtype = 'TR'
)
xtype = 'U' will return all the user tables within your database
xtype = 'TR' are trigger objects.
Every trigger belongs to a table/parent and the parent object id is store in
sysobjects.parent_obj. By using this info we can filter out all the user
tables that have triggers.
Hope this help.
"scuba79" wrote:

> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance|||Here is one way
SELECT * FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
FROM
sysobjects o
WHERE
OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
http://sqlservercode.blogspot.com/|||Little correction I forgot to filter out the views
SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
FROM
sysobjects o
WHERE
OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
http://sqlservercode.blogspot.com/|||assuming sql2000:
select user_name(uid) as table_schema, name as table_name
from sysobjects so
where xtype='U'
and objectproperty(id,'IsMSShipped')=0
and not exists (
select *
from sysobjects
where xtype='TR'
and parent_obj = so.id
)
order by table_name
scuba79 wrote:
> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance

retrieve list of tables

How can I get a list of table names or SPs in my database?
Not having much luck with BOL.
thanksHi
They are stored in sysobjects. BOL has info on that.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||Query system views information_schema.'
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
select
*
from
information_schema.routines
where
routine_type = 'procedure'
go
AMB
"shank" wrote:

> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>
>|||Go to the index tab in Books Online and type in "INFORMATION_SCHEMA". That
should get you a great start.
You can find information on how to view them by typing this in: "meta data,
obtaining with information schema views"
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||SELECT Objects.name FROM dbo.sysobjects Objects
WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
This will give you the list of all tables, views and stored procedures of
your database.
Thank you
Baiju
"shank" <shank@.tampabay.rr.com> wrote in message
news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
> How can I get a list of table names or SPs in my database?
> Not having much luck with BOL.
> thanks
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use the SQL Standard views in the INFORMATION_SCHEMA.
Tables (and Views in some systems):
SELECT TABLE_TYPE, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_TYPE, TABLE_NAME
--Views:
SELECT TABLE_NAME as view_name
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME NOT LIKE 'sys%' -- avoid system views
ORDER BY TABLE_NAME
--Stored Procedures:
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = 'dbo'
AND ROUTINE_NAME NOT LIKE 'dt_%' -- avoid system routines
ORDER BY ROUTINE_TYPE, ROUTINE_NAME
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjzMAYechKqOuFEgEQIEBwCfaOyb9ABeCNUL
hWCStiSNslhLlc4AoNAB
bbFnxkmvwTs6FwJ73wVNPqRV
=+MLw
--END PGP SIGNATURE--
Baiju wrote:
> SELECT Objects.name FROM dbo.sysobjects Objects
> WHERE ((OBJECTPROPERTY(id, 'IsView') = 1) OR (OBJECTPROPERTY(id,
> 'IsProcedure') = 1) OR (OBJECTPROPERTY(id, 'IsUserTable') = 1))
> This will give you the list of all tables, views and stored procedures of
> your database.
> Thank you
> Baiju
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:uCjpNQCLFHA.3332@.TK2MSFTNGP15.phx.gbl...
>

Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve list of largest tables in a database

Hello,
is there an easy way to retrieve a list of the largest tables in a
given database? I know how to use stored procedures to retrieve the
size of a single table, but I was wondering if there was a query that
could be used to obtain a list of the 20 (for instance) top largest
tables. I know this can be coded on the client side in the application
by retrieving the size of all the tables in a database and then
selecting only the largest n tables, but I was looking for something
more efficient and just a query if possible.
Thanks in advance.
If you don't mind missing out on 100% accuracy,
select top 20 object_name(id),rowcnt
from sysindexes
where indid in (0,1)
order by rowcnt desc
<ozonefilter@.gmail.com> wrote in message
news:1166050186.506361.21040@.80g2000cwy.googlegrou ps.com...
> Hello,
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
> Thanks in advance.
>
|||Aaron Bertrand [SQL Server MVP] wrote:
> If you don't mind missing out on 100% accuracy,
> select top 20 object_name(id),rowcnt
> from sysindexes
> where indid in (0,1)
> order by rowcnt desc
Hi Aaron,
thank you for your answer. If I'm not mistaken, the query above would
only provide me with the list of the tables having the greatest
cardinality. While I do not need 100% accuracy, very large tables (in
bytes) may have a relatively small amount of rows, while smaller tables
may have many tiny records. In fact I've tested it against a real
database and only 40% of the top 20 tables seems to be reported. Any
idea how to get a better approximation?
Thanks.
|||> thank you for your answer. If I'm not mistaken, the query above would
> only provide me with the list of the tables having the greatest
> cardinality.
Maybe I'm not sure what you mean by "cardinality" because the query I
provided has nothing to do with cardinality.
I may also be confused by what you mean by "largest". The query I provided
gives you the top 20 tables in terms of # of rows. If you want those that
are occupying the largest amount of space, maybe try this:
create table #foo
(
[name] sysname,
rows INT,
reserved nvarchar(32),
data nvarchar(32),
index_size nvarchar(32),
unused nvarchar(32)
);
set nocount on;
exec sp_msForEachTable 'INSERT #foo EXEC sp_spaceused ''?'';';
select top 20 *
from #foo
order by
convert(int, replace(reserved, N' KB', '')) desc;
drop table #foo;
|||Take a look at
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
<ozonefilter@.gmail.com> wrote in message
news:1166050186.506361.21040@.80g2000cwy.googlegrou ps.com...
> Hello,
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
> Thanks in advance.
>
|||ozonefilter@.gmail.com wrote:
> [...] a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. [...]
SELECT TOP 20
[TableSchema] = info.table_schema,
[TableName] = info.table_name,
[Used] = si.used
FROM
sysindexes si,
INFORMATION_SCHEMA.TABLES info
WHERE
object_name(si.id) = info.table_name
AND
info.table_type = 'BASE TABLE'
ORDER BY
si.used DESC;
Cheers,
Antonio

Retrieve list of largest tables in a database

Hello,
is there an easy way to retrieve a list of the largest tables in a
given database? I know how to use stored procedures to retrieve the
size of a single table, but I was wondering if there was a query that
could be used to obtain a list of the 20 (for instance) top largest
tables. I know this can be coded on the client side in the application
by retrieving the size of all the tables in a database and then
selecting only the largest n tables, but I was looking for something
more efficient and just a query if possible.
Thanks in advance.If you don't mind missing out on 100% accuracy,
select top 20 object_name(id),rowcnt
from sysindexes
where indid in (0,1)
order by rowcnt desc
<ozonefilter@.gmail.com> wrote in message
news:1166050186.506361.21040@.80g2000cwy.googlegroups.com...
> Hello,
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
> Thanks in advance.
>|||Aaron Bertrand [SQL Server MVP] wrote:
> If you don't mind missing out on 100% accuracy,
> select top 20 object_name(id),rowcnt
> from sysindexes
> where indid in (0,1)
> order by rowcnt desc
Hi Aaron,
thank you for your answer. If I'm not mistaken, the query above would
only provide me with the list of the tables having the greatest
cardinality. While I do not need 100% accuracy, very large tables (in
bytes) may have a relatively small amount of rows, while smaller tables
may have many tiny records. In fact I've tested it against a real
database and only 40% of the top 20 tables seems to be reported. Any
idea how to get a better approximation?
Thanks.|||> thank you for your answer. If I'm not mistaken, the query above would
> only provide me with the list of the tables having the greatest
> cardinality.
Maybe I'm not sure what you mean by "cardinality" because the query I
provided has nothing to do with cardinality.
I may also be confused by what you mean by "largest". The query I provided
gives you the top 20 tables in terms of # of rows. If you want those that
are occupying the largest amount of space, maybe try this:
create table #foo
(
[name] sysname,
rows INT,
reserved nvarchar(32),
data nvarchar(32),
index_size nvarchar(32),
unused nvarchar(32)
);
set nocount on;
exec sp_msForEachTable 'INSERT #foo EXEC sp_spaceused ''?'';';
select top 20 *
from #foo
order by
convert(int, replace(reserved, N' KB', '')) desc;
drop table #foo;|||Take a look at
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
<ozonefilter@.gmail.com> wrote in message
news:1166050186.506361.21040@.80g2000cwy.googlegroups.com...
> Hello,
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
> Thanks in advance.
>|||You can use the dpages, used or reserved columns instead of rowcnt, in the s
ysindexes table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ozonefilter@.gmail.com> wrote in message
news:1166055677.196013.32640@.l12g2000cwl.googlegroups.com...
> Aaron Bertrand [SQL Server MVP] wrote:
> Hi Aaron,
> thank you for your answer. If I'm not mistaken, the query above would
> only provide me with the list of the tables having the greatest
> cardinality. While I do not need 100% accuracy, very large tables (in
> bytes) may have a relatively small amount of rows, while smaller tables
> may have many tiny records. In fact I've tested it against a real
> database and only 40% of the top 20 tables seems to be reported. Any
> idea how to get a better approximation?
> Thanks.
>|||ozonefilter@.gmail.com wrote:
> [...] a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. [...]
SELECT TOP 20
[TableSchema] = info.table_schema,
[TableName] = info.table_name,
[Used] = si.used
FROM
sysindexes si,
INFORMATION_SCHEMA.TABLES info
WHERE
object_name(si.id) = info.table_name
AND
info.table_type = 'BASE TABLE'
ORDER BY
si.used DESC;
Cheers,
Antonio

Retrieve list of largest tables in a database

Hello,
is there an easy way to retrieve a list of the largest tables in a
given database? I know how to use stored procedures to retrieve the
size of a single table, but I was wondering if there was a query that
could be used to obtain a list of the 20 (for instance) top largest
tables. I know this can be coded on the client side in the application
by retrieving the size of all the tables in a database and then
selecting only the largest n tables, but I was looking for something
more efficient and just a query if possible.
Thanks in advance.If you don't mind missing out on 100% accuracy,
select top 20 object_name(id),rowcnt
from sysindexes
where indid in (0,1)
order by rowcnt desc
<ozonefilter@.gmail.com> wrote in message
news:1166050186.506361.21040@.80g2000cwy.googlegroups.com...
> Hello,
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
> Thanks in advance.
>|||Aaron Bertrand [SQL Server MVP] wrote:
> If you don't mind missing out on 100% accuracy,
> select top 20 object_name(id),rowcnt
> from sysindexes
> where indid in (0,1)
> order by rowcnt desc
Hi Aaron,
thank you for your answer. If I'm not mistaken, the query above would
only provide me with the list of the tables having the greatest
cardinality. While I do not need 100% accuracy, very large tables (in
bytes) may have a relatively small amount of rows, while smaller tables
may have many tiny records. In fact I've tested it against a real
database and only 40% of the top 20 tables seems to be reported. Any
idea how to get a better approximation?
Thanks.|||> thank you for your answer. If I'm not mistaken, the query above would
> only provide me with the list of the tables having the greatest
> cardinality.
Maybe I'm not sure what you mean by "cardinality" because the query I
provided has nothing to do with cardinality.
I may also be confused by what you mean by "largest". The query I provided
gives you the top 20 tables in terms of # of rows. If you want those that
are occupying the largest amount of space, maybe try this:
create table #foo
(
[name] sysname,
rows INT,
reserved nvarchar(32),
data nvarchar(32),
index_size nvarchar(32),
unused nvarchar(32)
);
set nocount on;
exec sp_msForEachTable 'INSERT #foo EXEC sp_spaceused ''?'';';
select top 20 *
from #foo
order by
convert(int, replace(reserved, N' KB', '')) desc;
drop table #foo;|||Take a look at
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
<ozonefilter@.gmail.com> wrote in message
news:1166050186.506361.21040@.80g2000cwy.googlegroups.com...
> Hello,
> is there an easy way to retrieve a list of the largest tables in a
> given database? I know how to use stored procedures to retrieve the
> size of a single table, but I was wondering if there was a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. I know this can be coded on the client side in the application
> by retrieving the size of all the tables in a database and then
> selecting only the largest n tables, but I was looking for something
> more efficient and just a query if possible.
> Thanks in advance.
>|||You can use the dpages, used or reserved columns instead of rowcnt, in the sysindexes table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ozonefilter@.gmail.com> wrote in message
news:1166055677.196013.32640@.l12g2000cwl.googlegroups.com...
> Aaron Bertrand [SQL Server MVP] wrote:
>> If you don't mind missing out on 100% accuracy,
>> select top 20 object_name(id),rowcnt
>> from sysindexes
>> where indid in (0,1)
>> order by rowcnt desc
> Hi Aaron,
> thank you for your answer. If I'm not mistaken, the query above would
> only provide me with the list of the tables having the greatest
> cardinality. While I do not need 100% accuracy, very large tables (in
> bytes) may have a relatively small amount of rows, while smaller tables
> may have many tiny records. In fact I've tested it against a real
> database and only 40% of the top 20 tables seems to be reported. Any
> idea how to get a better approximation?
> Thanks.
>|||ozonefilter@.gmail.com wrote:
> [...] a query that
> could be used to obtain a list of the 20 (for instance) top largest
> tables. [...]
SELECT TOP 20
[TableSchema] = info.table_schema,
[TableName] = info.table_name,
[Used] = si.used
FROM
sysindexes si,
INFORMATION_SCHEMA.TABLES info
WHERE
object_name(si.id) = info.table_name
AND
info.table_type = 'BASE TABLE'
ORDER BY
si.used DESC;
Cheers,
Antonio

Retrieve length of image or BLOB

I use the data type image to store BLOB's on the database. Now I would
like to Query the length of the BLOB with T-SQL to calculate the used
disk-space for this item.
Has some one a good idea?Check out the DATALENGTH function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroups.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>

Retrieve length of image or BLOB

Check out the DATALENGTH function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroup
s.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>I use the data type image to store BLOB's on the database. Now I would
like to Query the length of the BLOB with T-SQL to calculate the used
disk-space for this item.
Has some one a good idea?|||Check out the DATALENGTH function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroup
s.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give an
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so much
Does this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
David Portas
SQL Server MVP
|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:

> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the data
> or to add a key in the correct order and then use that to drive your query.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give a
n
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so muchDoes this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
David Portas
SQL Server MVP
--|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:

> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the dat
a
> or to add a key in the correct order and then use that to drive your query
.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give an
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so muchDoes this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
--
David Portas
SQL Server MVP
--|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:
> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the data
> or to add a key in the correct order and then use that to drive your query.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>

Retrieve last inserted or updated record

Hi

I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation.

On the event handler I am usin select ID,Name from my [table];

this will return all record from database.

I just want to get the record which is inserted or updated.

Can u help me in that.

Take care

Bye

You need no event to get the same. It can be easily done like:-

Dim sql As String = "INSERT INTO myTable (col2,col3) VALUES (bb,cc); SELECT * FROM jobs WHERE col1 = @.@.IDENTITY"

@.@.Identity carries the identity value of the last inserted record. Above statement can be executed with ExecuteReader method.

Hope this helps.

|||

Similarly code can be written for update.

|||

In the query you can use

SELECT@.ID=IDENT_CURRENT('TableName') for inserting

|||

Plz click "Mark as Answer" on the post that helped you.

Retrieve insertiondate

Hi folks,
Does anyone know how to retrieve the date when data has been inserted
thnx in advanceYou can create a column of the datetime data type. Set the default for that column to "getDate()". This should give you a value of the current date at the time of the insert.|||thnx for your reply,

I know that I can solve it like you suggested, but I want to know the insertiondate of a excisting record..

Can't I retrieve this info with the system tables or something like that

thnx in advance|||Generally, no.

I suppose you could restore an old copy of the database and just start restoring transaction logs until the record you are looking for pops up, but I'm guessing you don't have a full-time employee to devote to this for the next week.|||I wish I had,

But thnx for sharing that with me, now I can leave it and go further

thnx

retrieve information from before a transaction

I have a report and it retrieves information from an etl logging table which contains this info: etl_process_name, active_since_date, last_run_date.

When the packages are executing, the table cant be queried because it is in a transaction. Therefore the report keeps generating :s
The thing I want is that: If I query that table (with the report to show the logging table information) and the packages are executing, I want to see the values of the table before the transaction.

What I tried:
From sql management studio:

ALTER DATABASE MyDataBase
SET ALLOW_SNAPSHOT_ISOLATION ON
set transaction isolation level snapshot
go
SELECT etl_process, last_run, active_since FROM config.etl_settings

I did this while the packages were executing and it works but it won't work when I execute the same SELECT statement from the Dataset in the report. How is that possible? Does it have something to do with setting the database options for all users?

Additional info:
I'm database owner
The report solution has a shared datasource which refers to the same database

What is the error message are you getting?|||

Hmmm, I still don't know why it didn't work yesterday but today the report gets generated and shows the data from before a the transaction that is busy at the moment.

All I did was alter my database to edit the transaction isolation level:

ALTER DATABASE MyDataBase
SET ALLOW_SNAPSHOT_ISOLATION ON
set transaction isolation level snapshot
go

Retrieve Index names

What is the query that list out all the index names used in the tables?
MadhivananTry this
select Name from sysindexes where id = object_id('Table_Name')
Prad
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115102277.855498.76730@.f14g2000cwb.googlegroups.com...
> What is the query that list out all the index names used in the tables?
> Madhivanan
>|||Hi
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115102277.855498.76730@.f14g2000cwb.googlegroups.com...
> What is the query that list out all the index names used in the tables?
> Madhivanan
>|||Thanks for the replies
This also gives the same
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Madhivanan|||That only list indexes created through constraints (PK and UNIQUE). The quer
y doesn't list indexes
created using CREATE INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115116082.106013.258960@.z14g2000cwz.googlegroups.com...
> Thanks for the replies
> This also gives the same
> select *
> from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> Madhivanan
>

Retrieve Images from SQL db: Code Problem.

Hi,
I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005.
All I've done is:

// and Page Display_image.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
try
{

SqlConnection Con = new SqlConnection(
"server=localhost;" +
"database=;" +
"uid=;" +
"password=;");

System.String SqlCmd = "SELECT img_data FROM Image WHERE business_id = 2";

System.Data.SqlClient.SqlCommand SqlCmdObj = new System.Data.SqlClient.SqlCommand(SqlCmd, Con);

Con.Open();

System.Data.SqlClient.SqlDataReader SqlReader = SqlCmdObj.ExecuteReader(CommandBehavior.CloseConnection);

SqlReader.Read();

System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";

// I write this:
System.Web.HttpContext.Current.Response.BinaryWrite((byte[])SqlReader["img_data"]);

// Or this:
//System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream((byte[])SqlReader["img_data"]));

//System.Drawing.Image _newimage = _image.GetThumbnailImage(100, 100, null, new System.IntPtr());

//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);

Con.Close();

}
catch (System.Exception Ex)
{

System.Web.HttpContext.Current.Trace.Write(Ex.Message.ToString());
}
}

Both work the same way. I mean the image is displayed well but when I view code of the web page I see this:

...
GIF89aå o ÷ó å݉97)HS ¢ ?x L£0¬B´ç¬D(áâK? ?ô² –I€8`È® û l1ã?#K?L( ?*\X±‰ Î"«Mè± ??
...

TOO MUCH characters before the html tag. And any code of the master page used for this page does not work as well!

Can anyone help me with this? I've tried for 2 days but I still fail.

Thanks.

This should help:

Dim

drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader(

"Content-type", dr("MimeType"))

Response.AddHeader(

"Content-Disposition","inline; filename=""" & dr("Filename") &"""")Dim buffer()AsByte = dr("Data")Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

|||Hello Motley,

I've tried your comment, but it stays the same. The image is displayed, but plenty of charaters in the page source still appears.

I guess it's because of the 'response.outputstream.write()' command, so all the image's byte data has been writen to the page's code.

Can you find any way to replace 'outputstream.write()' or some changes to stop those disgusting characters?

Thank you for your help,
maivangho.

Retrieve Image from SqlServer

hi all

i hav a database with some images...images r stored in binary data form

i want to retrieve that image from database and display it in a details View

im inserting images to database like this

public

void OnUpload(Object sender,EventArgs e)

{

int len = Upload.PostedFile.ContentLength;byte[] pic =newbyte[len];

Upload.PostedFile.InputStream.Read (pic, 0, len);

SqlConnection connection =newSqlConnection ("integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbAsoftWeb;Data Source=ASP");try

{

connection.Open ();

SqlCommand cmd =newSqlCommand("insert into tblStock " +"(Image, Image_Data,[DESC],PRICE,BAL_QTY,PV) values (@.pic, @.text, @.lblProd, @.lblPrice, @.lblPV, @.lblQty) ", connection);

cmd.Parameters.Add(

"@.pic", pic);

cmd.Parameters.Add(

"@.text", Comment.Text);

cmd.Parameters.Add(

"@.lblProd", txtProdName.Text);

cmd.Parameters.Add(

"@.lblPrice", txtPrice.Text);

cmd.Parameters.Add(

"@.lblPV", txtPV.Text);

cmd.Parameters.Add(

"@.lblQty", txtQty.Text);

cmd.ExecuteNonQuery ();

}

finally

{

connection.Close ();

}

im able to get the image in to form but i want to display it in a detailsview

how cani do it..can any one explain me

thanks in advance

Hari

Hi

Check those articles:

Image_In_DetailsView

Image_in_Details_View_part_2

Hope it helps.

Retrieve Identity on insert: what if table has 2 identity columns

I have a table with 2 identity columns:
ID int identity(200100,1) not null,
OrderNo int Identity(550000,1) not null,
OrderName varchar(60) not null,
etc.
How do I get the values set by SQL server for ID and OrderNo when I insert
a new row in the table ?
Thanks in advance,Eve
If you are sitting on SQL Server 2000 then perform after inserting
SELECT SCOPE_IDENTITY()
Otherwise SELECT @.@.IDENTITY
"Eve" <Eve@.discussions.microsoft.com> wrote in message
news:F1824CDF-BC5D-4E89-98DA-6364A036B577@.microsoft.com...
> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I
insert
> a new row in the table ?
> Thanks in advance,
>|||It seems that there is a relationship between the two numbers.Seeing as you
can't have two identity columns. Why don't you use a formula for the ordern
o
Alter table <table> add OrderNo As ID + 300000.
"Eve" wrote:

> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I inser
t
> a new row in the table ?
> Thanks in advance,
>|||And on another note. I can't see why you need and ID since the OrderNo is
already unique...
Why is this ?
"Eve" wrote:

> I have a table with 2 identity columns:
> ID int identity(200100,1) not null,
> OrderNo int Identity(550000,1) not null,
> OrderName varchar(60) not null,
> etc.
> How do I get the values set by SQL server for ID and OrderNo when I inser
t
> a new row in the table ?
> Thanks in advance,
>|||I think you are mistaken. Only one IDENTITY column per table is permitted.
Please post the CREATE TABLE statement for your table so that we can
understand what you mean.
In SQL Server 2000 use SCOPE_IDENTITY() to retrieve the last inserted
IDENTITY value.
David Portas
SQL Server MVP
--

Retrieve Identity Field Value from a stored procedure into a form

OK first time poster, so hello everyone in advance. Right i'm sure this is a simple problem to solve, but I'm just getting the hang of SQL 2000. What I've got is a form where I input the values and then through a procedure these values are inserted into a table. That's fine. However I now need to open a subform which is linked by the ID field created through the first procedure. How do I retrieve that value back into the form??

The procedure code is

CREATE PROCEDURE InsertFamilyDetails

@.CarerIDvarChar(6),
@.FamilyNamevarChar(30),
@.Address1varChar(30),
@.Address2varChar(30),
@.Address3varChar(30),
@.PostCodeMain varChar(4),
@.PostCodeSubvarChar(30),
@.PhoneNovarChar(16),
@.LocalTransport varChar(200),
@.Leisure varChar(200),
@.School varChar(200),
@.RulesvarChar(250),
@.Resultint OUTPUT

AS

DECLARE @.FamilyID int;

BEGIN TRANSACTION

-- Insert New Family

SELECT @.FamilyID=@.@.Identity

INSERT INTO tblWfsFamilyDetails

(intCarerID,txtFamilyName,txtAddress1,txtAddress2, txtAddress3,txtPostCodeMain,txtPostCodeSub,txtPhon eNo,memoLocalTransport, memoLeisure, memoSchool,memoRules)

VALUES

(@.CarerID,@.FamilyName,@.Address1,@.Address2,@.Address 3,@.PostCodeMain,@.PostCodeSub,@.PhoneNo,@.LocalTransp ort,@.Leisure,@.School,@.Rules)

IF @.RESULT<1

BEGIN

ROLLBACK TRANSACTION;

RETURN -1

END

SET @.RESULT=1

COMMIT TRANSACTION
GO

Whereas, the VB Code is

Private Sub cmdInsert_Click()
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "insertFamilyDetails"

' FieldNames Assigned to FormControls

cmd.Parameters.Append cmd.CreateParameter("CarerID", adInteger, adParamInput, 6, txtCarerID)
cmd.Parameters.Append cmd.CreateParameter("FamilyName", adVarChar, adParamInput, 30, txtFamilyName)
cmd.Parameters.Append cmd.CreateParameter("Address1", adVarChar, adParamInput, 30, txtAddress1)
cmd.Parameters.Append cmd.CreateParameter("Address2", adVarChar, adParamInput, 30, txtAddress2)
cmd.Parameters.Append cmd.CreateParameter("Address3", adVarChar, adParamInput, 30, txtAddress3)
cmd.Parameters.Append cmd.CreateParameter("PostCodeMain", adVarChar, adParamInput, 4, txtPostCodeMain)
cmd.Parameters.Append cmd.CreateParameter("PostCodeSub", adVarChar, adParamInput, 4, txtPostCodeSub)
cmd.Parameters.Append cmd.CreateParameter("PhoneNo", adVarChar, adParamInput, 12, txtPhoneNo)
cmd.Parameters.Append cmd.CreateParameter("LocalTransport", adVarChar, adParamInput, 200, memoLocalTransport)
cmd.Parameters.Append cmd.CreateParameter("Leisure", adVarChar, adParamInput, 200, memoLeisure)
cmd.Parameters.Append cmd.CreateParameter("School", adVarChar, adParamInput, 200, memoSchool)
cmd.Parameters.Append cmd.CreateParameter("Rules", adVarChar, adParamInput, 200, memoRules)
cmd.Parameters.Append cmd.CreateParameter("Result", adInteger, adParamOutput) ' OutPut Returns Result Parameter a Value

cmd.Execute

Res = cmd("Result")

If (Res = 1) Then
MsgBox "Family Inserted Successfully", , "Insert Family"
End If

Set cmd.ActiveConnection = Nothing

End Sub

Apologise for wasting your time if this something obvious, but like I say I'm learning.pretty simple - in your stored procedure immediately after the line values(....) do this: set @.retval = @.@.IDENTITY

that will automatically put the new ID into the variable @.retval. Then you can do
Set @.RESULT = @.retval|||

Quote:

Originally Posted by scripto

pretty simple - in your stored procedure immediately after the line values(....) do this: set @.retval = @.@.IDENTITY

that will automatically put the new ID into the variable @.retval. Then you can do
Set @.RESULT = @.retval

scope_identity() function is a better choice than @.@.IDENTITY as there might be some triggers defined on tblWfsFamilyDetails table.

Retrieve Identity after Insert

I am a "newbie" and have been struggling with this for days! I have users enter their residence information and insert which generates houseid. I want to use/display that houseid on next page/step. I am VERY FRUSTRATED and would appreciate any assistance!

<

scriptrunat="server">ProtectedSub SqlDataSource1_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Inserting

e.Command.Parameters(

"@.house").Size = 5EndSubProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.InsertedDim house = e.Command.Parameters("@.house").Value

Response.Write(house)

EndSubProtectedSub Wizard1_FinishButtonClick(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.WizardNavigationEventArgs)EndSubProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)EndSub</script>

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ic_registerConnectionString %>"oninserted="SqlDataSource1_Inserted"oninserting="SqlDataSource1_Inserting"DeleteCommand="DELETE FROM [household] WHERE [householdid] = @.original_householdid AND [housenum] = @.original_housenum AND [streeraddr] = @.original_streeraddr AND [aptnum] = @.original_aptnum AND [city] = @.original_city AND [state] = @.original_state AND [zipcode] = @.original_zipcode AND [HHPhone] = @.original_HHPhone AND [timedate] = @.original_timedate"InsertCommand="INSERT INTO [household] ([housenum], [streeraddr], [aptnum], [city], [state], [zipcode], [HHPhone], [timedate]) VALUES (@.housenum, @.streeraddr, @.aptnum, @.city, @.state, @.zipcode, @.HHPhone, { fn NOW() }); SELECT @.house = SCOPE_IDENTITY()"OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT * FROM [household]"UpdateCommand="UPDATE [household] SET [housenum] = @.housenum, [streeraddr] = @.streeraddr, [aptnum] = @.aptnum, [city] = @.city, [state] = @.state, [zipcode] = @.zipcode, [HHPhone] = @.HHPhone, [timedate] = @.timedate WHERE [householdid] = @.original_householdid AND [housenum] = @.original_housenum AND [streeraddr] = @.original_streeraddr AND [aptnum] = @.original_aptnum AND [city] = @.original_city AND [state] = @.original_state AND [zipcode] = @.original_zipcode AND [HHPhone] = @.original_HHPhone AND [timedate] = @.original_timedate"OnSelecting="SqlDataSource1_Selecting"><InsertParameters><asp:ParameterName="housenum"Type="String"/><asp:ParameterName="streeraddr"Type="String"/><asp:ParameterName="aptnum"Type="String"/><asp:ParameterName="city"Type="String"/><asp:ParameterName="state"Type="String"/><asp:ParameterName="zipcode"Type="String"/><asp:ParameterName="HHPhone"Type="String"/><asp:ParameterType="String"Name="house"Direction="Output"/></InsertParameters>

Next question is this easier to do using a Wizard Control and DetailsView on a "step" or using seperate pages and FormView? Or does it matter?

Can anybody help me with this please?|||

I believe that you want to handle the Inserted event. Here's a snippet from the help that should lead you on the right direction.

Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim command As DbCommand
command = e.Command

' The label displays the primary key of the recently inserted row.
Label1.Text = command.Parameters("@.PK_New").Value.ToString()

' Explicitly call DataBind to refresh the data
' and show the newly inserted row.
GridView1.DataBind()
End Sub 'On_Inserted

Retrieve id of newly added row in stored procedure

Hi,

I am trying to return the id of the newly added row after the insertstatement in my stored procedure. I was told to "RETURNSCOPE_IDENTITY()", which i did. The problem is i do not know how to getthis value in asp.net?

I added this code below in my business layer. myDAL refers to an objectof my DAL layer. In my DAL layer, i have a addPara() method which willhelp me dynamically add as many parameters. Someone please advise me onwhat to do. Thanks

myDAL.addPara("@.RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue)

Stored Procedure:
CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)

)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()
GOTry @.@.identity

Refer :http://tinyurl.com/5sdht|||

SCOPE_IDENTITY() is definetely a more accurate function than @.@.IDENTITY. Check out Books On Line for more info. To get your code working, create an OUTPUT parameter in the stored proc:

CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)
@.MSTRPROMID INT OUTPUT
)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)
SELECT @.MSTRPROMID = SCOPE_IDENTITY()
-- Now return the InventoryID of the newly inserted record

GO

Now change your front end code to retrieve the ID

check the (2) section ofthis article for sample code.

|||

Look at the @.RETURN_VALUE parameter after you call the stored procedure.

Normally (Without any Dal stuff):

debug.print cmd.parameters("@.RETURN_VALUE).value

Where cmd is your sqlcommand/oledbcommand etc

Retrieve ID of Last Insert (Scope_Identity)

(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks.

Session["snCoDeptRowID"] = SqlDataSource1.Select();

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT Scope_Identity"

</asp:SqlDataSource>

try

SelectCommand="SELECT Scope_Identity()"

|||

Thank you for the suggestion. Now I am having trouble with the following line of code which assigns the Scope_Identity() to a session variable. The error msg is: does not recognise the word "command". How can I get the value of the @.CoDeptRowID (Scope_Identity) into my session variable? Thanks

Session["snCoDeptRowID"] = Convert.ToInt32(e.command.parameters("@.CoDeptRowID").value);

|||

Check thate argument has a command object

only if its there in event argument you will be able to use it. And Check the Direction of the @.CoDeptRowId is set to Output in Command as well

|||

Hi,

There are many ways to get the SCOPE_IDENTITY() from stored procedure.

You can use SELECT SCOPE_IDENTITY() or RETURN SCOPE_IDENTITY() or SET @.PARAM = SCOPE_IDENTITY(). All is fine, but the way to get it is different.

SELECT will return the SCOPE_IDENTITY() as the first col and first row of a result set. RETURN will return is as a return parameter. While SET will set the value to a parameter and you have to specify it as an OUTPUT parameter when declaring it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Retrieve id after adding a new record

How do I retrieve the id of the record after I INSERT it?

Hi,

Take a look @.Input and Output Parameters, and Return Values

HTH

|||Just to add to above..you need to use SCOPE_IDENTITY() instead of@.@.IDENTITY in the sample. Checkout the Books online for the differences.
|||How would you accomplish this without using stored proceedures?|||

you could use parameterized SQL

you can issue multiple sql statements without using stored procs. you just need to seperate them with a semi-colon.

i.e.

INSERT INTO blah blah blah; SELECT scope_identity();

Retrieve GUID or SID FROM Active Directory via ADSI and T-SQL only?

Hi all,

is there a way to get an object's SID or GUID using T-SQL only?

Up to now I got the following code of Active Directory Service Interfaces working,

but GUID or SID are not among the parameters known to me.

Code Snippet

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

I do not want to use anything other then SQL Server 2000 to get an AD-object's primary key.

Any comments would be appreciated.

Thank you!

Regards,

caracol

Windows 2000 Server, SQL Server 2000, AD in W2K only mode

This is not an SSIS question. Moving to the Transact-SQL Forum.|||

Hi all,

just in case there should be someone else looking for identifiable data to be extracted from Active Directory:

A complete list with all attributes can be found at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/adschema/attributes_all.asp

For every attribute the LDAP-Display-Name is given which can be accessed by ADSI.

My SELECT from above should be

Code Snippet

SELECT * FROM OpenQuery(ADSI, 'SELECT objectGUID, title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

then.

Regards,

caracol

Retrieve GUID from SQL 2005 Stored Procedure

I have a stored procedure that returns GUID and BIT datatypes (see
below). I am using the VS 2005 TableAdapter.GetData to execute and
return the values. BUT, I can't pass a null value for the GUID output
parameter.
- Why is it required to pass a value for an output value?
- How do I retrieve the GUID value? If I remove the GUID field from
the SP, I can retrieve the BIT fields...so I am confident the SP works
fine.
Thanks for any ideas.
-KB
STORED PROCEDURE:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_UserLogin]
(
@.UserName nvarchar(50),
@.Password nvarchar(50),
@.Authorized bit output,
@.UserID uniqueidentifier output,
@.Security_Expeditor bit output,
@.Security_Tech bit output,
@.Security_WS bit output,
@.Security_RN bit output,
@.Security_MD bit output,
@.Security_SuperUser bit output,
@.Security_ReportAccess bit output,
@.Security_Admin bit output
)
AS
SET NOCOUNT ON;
BEGIN
Select @.Authorized = 'False'
Select @.Security_Expeditor = 'False';
Select @.Security_Tech = 'False';
Select @.Security_WS = 'False';
Select @.Security_RN = 'False';
Select @.Security_MD = 'False';
Select @.Security_SuperUser = 'False';
Select @.Security_ReportAccess = 'False';
Select @.Security_Admin = 'False';
END
BEGIN
select @.UserID = (select top 1 UserID from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and
IsApproved = 'True');
if @.@.rowcount > 0
set @.Authorized = 'True'
END
Begin
select * from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and IsApproved =
'False'
end
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'expeditor' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Expeditor = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'tech' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Tech = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'ws' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_WS = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'rn' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_RN = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'md' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_MD = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'superuser' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_SuperUser = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'reportaccess' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_ReportAccess = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'adminsecurity' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Admin = 'True'
END(corsspost to unofficial group removed)
On 19 Apr 2006 10:09:21 -0700, kb wrote:

>I have a stored procedure that returns GUID and BIT datatypes (see
>below). I am using the VS 2005 TableAdapter.GetData to execute and
>return the values. BUT, I can't pass a null value for the GUID output
>parameter.
Hi kb,
How do you attempt to pass NULL? For an OUTPUT variable, you have to
pass in a variable, never a constant - but that variable can be NULL
(see code example below).

>- Why is it required to pass a value for an output value?
Not a value, but a variable - because an output variable can be changed
from the stored proc. You can't change a constant!

>- How do I retrieve the GUID value?
See this example:
CREATE PROC Test @.guid uniqueidentifier OUTPUT
AS
SET @.guid = NEWID()
go
DECLARE @.x uniqueidentifier
SET @.x = NULL
SELECT @.x
EXEC Test @.guid = @.x OUTPUT
SELECT @.x
go
DROP PROC Test
go
Hugo Kornelis, SQL Server MVP

Retrieve Guid after inserting recrod with NEWID()

Hi There,

I'm having a problem retreiving the auto generated Guid after inserting anew record with NEWID(), my stored proc is as follows:

SET @.uiTransactionID = NEWID()INSERT INTO Transactions (uiTransactionID) VALUES (@.uiTransactionID)IF @.@.ERROR = 0 AND @.@.ROWCOUNT = 1BEGIN SELECT @.uiTransactionID AS'@.@.GUID' RETURN 0END

And the return on my insert statement is:

command.ExecuteNonQuery();
m_uiTransactionID = (Guid

)command.Parameters["RETURN_VALUE"].Value;

I can never retreive the newly generated Guid, can onyone spot where i'm going wrong?

Many thanks

Ben

Hi,

i have tested your stored procedure and i think the stored procedure is correct. Please check your calling code for the stored procedure:

protected void CallStoredProcedure(){ Guid m_uiTransactionID; SqlCommand cmd =new SqlCommand("Test",new SqlConnection(ConfigurationManager.ConnectionStrings["DataBase"].ToString()));cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@.uiTransactionID", SqlDbType.UniqueIdentifier);cmd.Parameters["@.uiTransactionID"].Direction = ParameterDirection.Output;try { cmd.Connection.Open(); cmd.ExecuteNonQuery();m_uiTransactionID = (Guid)cmd.Parameters["@.uiTransactionID"].Value; }catch (Exception exc) { }finally { cmd.Connection.Close(); }}

And you can also check the stored procedure execute permissons.

Hope this can help you.

Regards
Marc André

|||

Hi Marc,

Thanks for your reply, you've helped me agreat deal and it is now working.

Many thnks

Ben

Retrieve good records from a bad record table

I have a situation where I need a table if bad items to match to. For
example, The main table may be as:

Table Main:
fd_Id INT IDENTITY (1, 1)
fd_Type VARCHAR(100)

Table Matcher:
fd_SubType VARCHAR(20)

Table Main might have a records like:
1 | "This is some full amount of text"
2 | "Here is half amount of text"
3 | "Some more with a catch word"

Table Matcher:
"full"
"catch"

I need to only get the records from the main table that do not have
anything in the match table. This should return only record 2.Verticon:: (miben@.miben.net) writes:
> I have a situation where I need a table if bad items to match to. For
> example, The main table may be as:
> Table Main:
> fd_Id INT IDENTITY (1, 1)
> fd_Type VARCHAR(100)
> Table Matcher:
> fd_SubType VARCHAR(20)
> Table Main might have a records like:
> 1 | "This is some full amount of text"
> 2 | "Here is half amount of text"
> 3 | "Some more with a catch word"
> Table Matcher:
> "full"
> "catch"
> I need to only get the records from the main table that do not have
> anything in the match table. This should return only record 2.

SELECT mn.fd_id, mn.fd_Type
FROM tablemain mn
WHERE NOT EXISTS (SELECT *
FROM tablematcher mt
WHERE md.fd_Type LIKE '%' + mt.fd_SubType + '%')

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

retrieve from db then write to text file

basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.

i read a thread here which is similar to what i am trying to do but it was in xml format..

hope someone anwers me...i really need help!

thanks!

I'm not sure what the difference is between the thread you mentioned and what you want to do. I would expect that the challenging thing is to retrieve the fields that were just saved. Once you have the information, changing it to the appropriate format is potentially tedious, but not difficult.

Do you have a reference to the thread?

Retrieve first row only in many-to-many relationship

I have a db with three tables - books, sections, and a joining table.
The normal way of getting a many to many relationship (i.e. one book
may belong to many sections, and one section may contain many books)

I want to extract the data with a single row for each book so that I
only retrieve the first section description for any book. (e.g. title,
author, section, description)

Structure as follows:

tbl_book
book_id, title, author, description etc...

tbl_section
section_id, section_desc

tbl_book_section
book_id, section_id

DBA is away and I can't figure this out at all...any help gratefully
received.Try this. I'm assuming that by "first section" you mean the lowest numbered
section_id.

SELECT B1.book_id, B1.title, B1.author, B1.description,
S2.section_id, S2.section_desc
FROM tbl_book AS B1
JOIN
(SELECT book_id, MIN(section_id) AS section_id
FROM tbl_book_section
GROUP BY book_id) AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id

--
David Portas
----
Please reply only to the newsgroup
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Since SQL is a set-oriented language, there is no such concept as a
first row in a table. The next basic principle is that all
relationships are shown as values in a column. Therefore, you must
have a section number of some kind in the DDL that you did not post
for this to make sense.

Book_id ought to be an ISBN, but we have no idea what section_id is
like and if it has an ordering.

When the DBA gets back, ask him to read and use ISO-11179 naming
standards. What he ias given you says that you only have one book
about furniture, specifically tables.|||David

That did the trick thanks.

Gareth

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<ceydnc72CbDgi5XdRVn-gQ@.giganews.com>...
> Try this. I'm assuming that by "first section" you mean the lowest numbered
> section_id.
> SELECT B1.book_id, B1.title, B1.author, B1.description,
> S2.section_id, S2.section_desc
> FROM tbl_book AS B1
> JOIN
> (SELECT book_id, MIN(section_id) AS section_id
> FROM tbl_book_section
> GROUP BY book_id) AS S1
> ON B1.book_id = S1.book_id
> JOIN tbl_section AS S2
> ON S1.section_id = S2.section_id|||Joe

I don't know what you mean by DDL, but the other guy who posted a
reply clearly understood what I was asking about.

The database in question existed before the DBA (female by the way)
joined the company and the reason I want the query is to extract the
data for a new ecommerce system.

Naming conventions are indeed a good thing...

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0401161012.58f5d22a@.posting.google.com>...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications.
> Since SQL is a set-oriented language, there is no such concept as a
> first row in a table. The next basic principle is that all
> relationships are shown as values in a column. Therefore, you must
> have a section number of some kind in the DDL that you did not post
> for this to make sense.
> Book_id ought to be an ISBN, but we have no idea what section_id is
> like and if it has an ordering.
> When the DBA gets back, ask him to read and use ISO-11179 naming
> standards. What he ias given you says that you only have one book
> about furniture, specifically tables.|||> I don't know what you mean by DDL, but the other guy who posted a
> reply clearly understood what I was asking about.

I guessed what you wanted but it is useful to post DDL for questions like
this:
www.aspfaq.com/5006

--
David Portas
----
Please reply only to the newsgroup
--|||"Gareth" <gareth900@.hotmail.com> wrote in message
news:c105346f.0401170201.51b5e4b1@.posting.google.c om...
> Joe
> I don't know what you mean by DDL, but the other guy who posted a
> reply clearly understood what I was asking about.

DDL - Data Description Language.

Basically the SQL commands to create the tables with keys, constraints, etc.
that you want. This allows folks answering your question to create a test
setup on their own servers. Generally you'll get answers that have been
fully tested that way.

Joe Celko is a bit of curmudgeon, but he's also arguably one of the better
experts on the SQL language out there. He has several books to his name and
knows his stuff. And yes, he's opinionated. :-)

> The database in question existed before the DBA (female by the way)
> joined the company and the reason I want the query is to extract the
> data for a new ecommerce system.
> Naming conventions are indeed a good thing...|||Greg - DDL - makes sense now...

In future I'll do this - didn't realise the conventions in the group.

Thanks

Gareth|||>> I don't know what you mean by DDL .. <<

Data Definition Language. SQL has three sublanguages and this is one of
them. It is also the minimal netiquette in SQL newsgroups.

>> the other guy who posted a reply clearly understood what I was asking
about. <<

No, he guessed lucky. What if section_id had been a title, like
"Introduction" or "preamble" which was not in alphabetic order?

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> Joe Celko is a bit of curmudgeon, ... <<

Hey, if I had any friends, they'd tell you what a great guy I am!|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0401191734.1252bc8e@.posting.google.c om...
> >> Joe Celko is a bit of curmudgeon, ... <<
> Hey, if I had any friends, they'd tell you what a great guy I am!

Hey, you say that like I was saying something that wasn't nice. :-)