return all rows function

in sql 2000 there is the return all rows function.. where is it in sql 2005 express or does it have that function?

thanksI'm a bit confused by your post; can you explain where this function is and how you use it? I have a feeling you're referring to a feature by a different name than is normally used. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <erd@.discussions.microsoft.com> wrote in message news:ddefbe4d-f12b-489e-9ccf-7422d416a908@.discussions.microsoft.com...in sql 2000 there is the return all rows function.. where is it in sql 2005 express or does it have that function?thanks|||Something like this function in Enterprise Manager for SQL Server 2000 should be available to you in Management Studio Express. Failing that, just run the query, SELECT * FROM table? It's really not that much more work than right-click, select... A wrote in message news:ddefbe4d-f12b-489e-9ccf-7422d416a908@.discussions.microsoft.com...
> in sql 2000 there is the return all rows function.. where is it in sql
> 2005 express or does it have that function? >
> thanks
>|||You can right click on your table or view in Object Explorer and select Open Table/View in the context menu. This open a query editor instance with all the data in your table or view.

Is this what you were looking for?|||yes.. thats the one.. thank you

i have another problem..

we are currently using an sql 2000 in server and installed sql server 2005 express on one client but whenever we right click the database diagram in of of our client machine we are having an error.

The system cannot find the specified path exception from HRESULT 0x800700002 (Microsoft.VisualStudio.VSHelp80)|||

Which server is the diagram on and which client is doing the clicking? Are you clicking on a particular diagram, or on the diagrams folder?

The database designer in SQL Server 2000 doesn't understand SQL Server 2005 diagrams. Conversely, the database designer in SQL Server 2005 can't work with diagrams on SQL Server 2000 servers.

|||

where is the import/export function in sql server 2005 express edition?

trying to import an access file or .mdb file to sql 2005 express but couldnt find how to import it.. and also the export file..

thanks

retrieving SQL Server roles and permissions

Hi,

I am developping an application using Windows forms(C#.net) and SQL Server 2005 Express edition. I would like to use SQL Server authentication. This is what I would like my application to do:

When a user logs in and is authenticated by SQL Server, the application to be able read the user's permissions/rights from SQL Server and use them to restrict to access what the user can do in the application. I have gone through lots of articles but all articles talk either of security in the .net environment or SQL Server security. None talks about integrating database security with application security.

Any leads will be appreciated.

Jakiiki

Hi Jakiiki,

Applications connect to SQL Server either via SQL Authentication or Windows Authentication. Once Autheticated the Applications identity in SQL Server is determined and access to resources is determined by the permissions the identity possess.

You can retrieve permission and role information about a user from sql server's catalog views.

sys.server_permissions and sys.database_permissions will list out all of the permissions granted to sql logins and sql user respectively.

sys.server_principals and sys.database_principals will list the sql logins for the instance and the sql users for the current database.

database and server roles will be listed in sys.database_principals and sys.server_principals

membership in these roles is tracked in the catalog view sys.database_role_members and sys.server_role_members

HTH,

-Steven Gott

SDE/T

SQL Server

sql

Retrieving data from an attached mdf file

I attach my SQL Server Express data file to my host. I would like to copy all of my member information back to my local computer. How can I do this? My host won't allow my to physically copy the data file over.

My host is discountasp.net.

Thanks,
Jeff

Port 80 is obviously open, so create a web service to read the data. Lock the site to respond only to your external IP address to avoid anybody else being able to get at the data.

Retrieving autonum / IDENTIFIER value from SQL table using DAO.

Hello,

I am in the midst of converting an Access back end to SQL Server Express.
The front end program (converted to Access 2003) uses DAO throughout. In
Access, when I use recordset.AddNew I can retrieve the autonum value for the
new record. This doesn't occur with SQL Server, which of course causes an
error (or at least in this code it does since there's an unhandled NULL
value). Is there any way to retrieve this value when I add a new record
from SQL server or will I have to do it programmatically in VB?

Any direction would be great.

Thanks!Try:

select
scope_identity()

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:1fB_f.527$7a.323@.pd7tw1no...
Hello,

I am in the midst of converting an Access back end to SQL Server Express.
The front end program (converted to Access 2003) uses DAO throughout. In
Access, when I use recordset.AddNew I can retrieve the autonum value for the
new record. This doesn't occur with SQL Server, which of course causes an
error (or at least in this code it does since there's an unhandled NULL
value). Is there any way to retrieve this value when I add a new record
from SQL server or will I have to do it programmatically in VB?

Any direction would be great.

Thanks!|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I am in the midst of converting an Access back end to SQL Server
> Express. The front end program (converted to Access 2003) uses DAO
> throughout. In Access, when I use recordset.AddNew I can retrieve the
> autonum value for the new record. This doesn't occur with SQL Server,
> which of course causes an error (or at least in this code it does since
> there's an unhandled NULL value). Is there any way to retrieve this
> value when I add a new record from SQL server or will I have to do it
> programmatically in VB?

It's better to use stored procedures to add data, rather than relying on
ADO generating code behind your back. It's easy for the Jet provider
to populate the Autonumber for you, because all operations are in your
process space. But since SQL Server is on the other end of the wire,
there is an extra roundtrip to get the value.

Also, with SQL Server, make sure that all your cursors are client-side.

A sample stored procedure:

CREATE PROCEDURE insert_tbl @.a int,
@.b datetime,
@.c varchar(23),
@.id int AS
INSERT tbl (a, b, c)
VALUES (@.a, @.b, @.c)
SELECT @.id = scope_identity

--
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|||Thanks Tom and Erland,

I wound up researching Scope_Identity and that lead me to @.@.identity. I
wound up changing my DAO code as follows;

Instead of...

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
db)
rst.Update

I changed the code to

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
rst.Update

MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)

This seems to work in every case, since the @.@.Identity line gets the last ID
created on your specific connection whether someone else updates the
database as the same time or not. In other words, if I update the database
at the same time another user updates the database, the @.@.Identity will
never pass me back the other users ID field since that wasn't created on my
connection.

Although my tests have proven successful, if anyone has exprience using this
with DAO and has had any failures, please let me know.

Erland, I wish I knew more about creating stored procedures, because I'd
like to centralize as much of this kind of thing as I can, but at this point
I have to stick with what I know. Thanks for the info.

Rick

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A2F243F7168Yazorman@.127.0.0.1...
> Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS)
> writes:
>> I am in the midst of converting an Access back end to SQL Server
>> Express. The front end program (converted to Access 2003) uses DAO
>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>> autonum value for the new record. This doesn't occur with SQL Server,
>> which of course causes an error (or at least in this code it does since
>> there's an unhandled NULL value). Is there any way to retrieve this
>> value when I add a new record from SQL server or will I have to do it
>> programmatically in VB?
> It's better to use stored procedures to add data, rather than relying on
> ADO generating code behind your back. It's easy for the Jet provider
> to populate the Autonumber for you, because all operations are in your
> process space. But since SQL Server is on the other end of the wire,
> there is an extra roundtrip to get the value.
> Also, with SQL Server, make sure that all your cursors are client-side.
> A sample stored procedure:
> CREATE PROCEDURE insert_tbl @.a int,
> @.b datetime,
> @.c varchar(23),
> @.id int AS
> INSERT tbl (a, b, c)
> VALUES (@.a, @.b, @.c)
> SELECT @.id = scope_identity
>
>
> --
> 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|||Don't use @.@.IDENTITY. You can have incorrect results if your INSERT fires a
trigger which itself inserts into a table with an identity. Use
SCOPE_IDENTITY().

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:sG9%f.5965$WI1.5577@.pd7tw2no...
Thanks Tom and Erland,

I wound up researching Scope_Identity and that lead me to @.@.identity. I
wound up changing my DAO code as follows;

Instead of...

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
db)
rst.Update

I changed the code to

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
rst.Update

MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)

This seems to work in every case, since the @.@.Identity line gets the last ID
created on your specific connection whether someone else updates the
database as the same time or not. In other words, if I update the database
at the same time another user updates the database, the @.@.Identity will
never pass me back the other users ID field since that wasn't created on my
connection.

Although my tests have proven successful, if anyone has exprience using this
with DAO and has had any failures, please let me know.

Erland, I wish I knew more about creating stored procedures, because I'd
like to centralize as much of this kind of thing as I can, but at this point
I have to stick with what I know. Thanks for the info.

Rick

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A2F243F7168Yazorman@.127.0.0.1...
> Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS)
> writes:
>> I am in the midst of converting an Access back end to SQL Server
>> Express. The front end program (converted to Access 2003) uses DAO
>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>> autonum value for the new record. This doesn't occur with SQL Server,
>> which of course causes an error (or at least in this code it does since
>> there's an unhandled NULL value). Is there any way to retrieve this
>> value when I add a new record from SQL server or will I have to do it
>> programmatically in VB?
> It's better to use stored procedures to add data, rather than relying on
> ADO generating code behind your back. It's easy for the Jet provider
> to populate the Autonumber for you, because all operations are in your
> process space. But since SQL Server is on the other end of the wire,
> there is an extra roundtrip to get the value.
> Also, with SQL Server, make sure that all your cursors are client-side.
> A sample stored procedure:
> CREATE PROCEDURE insert_tbl @.a int,
> @.b datetime,
> @.c varchar(23),
> @.id int AS
> INSERT tbl (a, b, c)
> VALUES (@.a, @.b, @.c)
> SELECT @.id = scope_identity
>
>
> --
> 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|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Don't use @.@.IDENTITY. You can have incorrect results if your INSERT
> fires a trigger which itself inserts into a table with an identity. Use
> SCOPE_IDENTITY().

Then again, there are cases where @.@.identity will give you the correct
result, and scope_identity() will not.

Now, I don't know how DAO works, but the suggestion to use scope_identity()
relies on the somewhat risky assumption that .AddNew performs a straight
insert. If DAO sets up a prepared query, run sp_executesql, or runs some
temporary stored procedure, scope_identity will not work. Since DAO is
a fairly old API, I would not expect it to be too sophisticated. Then
again, using scope_identity() means that you rely on the implementation
of something that could change with a service pack or a new release. (Not
that such are bloodly likely for DAO.)

Using @.@.identity is better, because it relies at least only on your
own application and schema which you have more control over.

--
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|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I wound up researching Scope_Identity and that lead me to @.@.identity. I
> wound up changing my DAO code as follows;
>...
> Erland, I wish I knew more about creating stored procedures, because I'd
> like to centralize as much of this kind of thing as I can, but at this
> point I have to stick with what I know. Thanks for the info.

Not only that, DAO is an API that has been deprecated for a long time.
The recommended API for an Access application today, I guess still is
ADO. (Which, I will have to admit, is an API that I don't like very
much at all.)

--
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|||It is enormously absurd to use DAO with MS-SQL Server.
It is enormously absurd for the OP to say he will not learn about
Stored Procedures.
It is enormously absurd to use ODBC and DAO with MS-SQL.
I KNOW, knowledgeable insiders say that is the route to take.
I say the knowledgeable insiders say so because they want to promote
Access as a front end for MS-SQL to those who are too lazy or and or
too stupid to learn MS-SQL and ADO.
Moreover, to those who are offended by this I say, "Get off you ass and
learn your trade and then you won't be!"|||Hi Erland

> Then again, there are cases where @.@.identity will give you the correct
> result, and scope_identity() will not.

Could you give an example of when this might occur?

--
-Dick Christoph
dchristo@.mn.rr.com
612-724-9282
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A3F2A2F1723Yazorman@.127.0.0.1...
> Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
>> Don't use @.@.IDENTITY. You can have incorrect results if your INSERT
>> fires a trigger which itself inserts into a table with an identity. Use
>> SCOPE_IDENTITY().
> Then again, there are cases where @.@.identity will give you the correct
> result, and scope_identity() will not.
> Now, I don't know how DAO works, but the suggestion to use
> scope_identity()
> relies on the somewhat risky assumption that .AddNew performs a straight
> insert. If DAO sets up a prepared query, run sp_executesql, or runs some
> temporary stored procedure, scope_identity will not work. Since DAO is
> a fairly old API, I would not expect it to be too sophisticated. Then
> again, using scope_identity() means that you rely on the implementation
> of something that could change with a service pack or a new release. (Not
> that such are bloodly likely for DAO.)
> Using @.@.identity is better, because it relies at least only on your
> own application and schema which you have more control over.
> --
> 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|||DickChristoph (dchristo99@.yahoo.com) writes:

>> Then again, there are cases where @.@.identity will give you the correct
>> result, and scope_identity() will not.
> Could you give an example of when this might occur?

CREATE TABLE #xyz(a int IDENTITY, b int NOT NULL)
go
EXEC sp_executesql N'INSERT #xyz(b) VALUES(@.b)', N'@.b int', 12
SELECT scope_identity(), @.@.identity
do
DROP TABLE #xyz

While the example may look contrived, many client API uses sp_executesql
or similar under the hood. scope_identity() returns the latest generated
identity value in the current scope, so if you call back a second time
from the client to get the value, you can only hope the both commands
excecuted in the top scope of the connection.

--
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|||I am in a similar situation to you and am trying the following:

theRecord.AddNew
' new data values
theRecord.Update
theRecord.Bookmark = theRecord.LastModified
theNewID = theRecord("ID")

I expect the experts will find this wanting but, so far, it seems to
work. I suppose that there might be a timing issue immediately after
the Update.|||Lyle, this isn't a ground up application, this is converting a clients
legacy application. The bean counters have better things to do with their
budget than build a new version of something they are already using.

I never said I wouldn't learn about stored procedures, but don't have the
time in this case.

"Lyle Fairfield" <lylefairfield@.aim.com> wrote in message
news:1144906536.025890.26030@.j33g2000cwa.googlegro ups.com...
> It is enormously absurd to use DAO with MS-SQL Server.
> It is enormously absurd for the OP to say he will not learn about
> Stored Procedures.
> It is enormously absurd to use ODBC and DAO with MS-SQL.
> I KNOW, knowledgeable insiders say that is the route to take.
> I say the knowledgeable insiders say so because they want to promote
> Access as a front end for MS-SQL to those who are too lazy or and or
> too stupid to learn MS-SQL and ADO.
> Moreover, to those who are offended by this I say, "Get off you ass and
> learn your trade and then you won't be!"|||Hi Tom,

Just so you know, triggers and other server side operations will not affect
the @.@.identity result and hence, will not return an incorrect result.

Rick

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Pwa%f.3830$L.26943@.news20.bellglobal.com...
> Don't use @.@.IDENTITY. You can have incorrect results if your INSERT fires
> a
> trigger which itself inserts into a table with an identity. Use
> SCOPE_IDENTITY().
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in message news:sG9%f.5965$WI1.5577@.pd7tw2no...
> Thanks Tom and Erland,
> I wound up researching Scope_Identity and that lead me to @.@.identity. I
> wound up changing my DAO code as follows;
> Instead of...
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
> I changed the code to
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> rst.Update
> MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)
> This seems to work in every case, since the @.@.Identity line gets the last
> ID
> created on your specific connection whether someone else updates the
> database as the same time or not. In other words, if I update the
> database
> at the same time another user updates the database, the @.@.Identity will
> never pass me back the other users ID field since that wasn't created on
> my
> connection.
> Although my tests have proven successful, if anyone has exprience using
> this
> with DAO and has had any failures, please let me know.
> Erland, I wish I knew more about creating stored procedures, because I'd
> like to centralize as much of this kind of thing as I can, but at this
> point
> I have to stick with what I know. Thanks for the info.
> Rick
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97A2F243F7168Yazorman@.127.0.0.1...
>> Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
>> CAPS)
>> writes:
>>> I am in the midst of converting an Access back end to SQL Server
>>> Express. The front end program (converted to Access 2003) uses DAO
>>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>>> autonum value for the new record. This doesn't occur with SQL Server,
>>> which of course causes an error (or at least in this code it does since
>>> there's an unhandled NULL value). Is there any way to retrieve this
>>> value when I add a new record from SQL server or will I have to do it
>>> programmatically in VB?
>>
>> It's better to use stored procedures to add data, rather than relying on
>> ADO generating code behind your back. It's easy for the Jet provider
>> to populate the Autonumber for you, because all operations are in your
>> process space. But since SQL Server is on the other end of the wire,
>> there is an extra roundtrip to get the value.
>>
>> Also, with SQL Server, make sure that all your cursors are client-side.
>>
>> A sample stored procedure:
>>
>> CREATE PROCEDURE insert_tbl @.a int,
>> @.b datetime,
>> @.c varchar(23),
>> @.id int AS
>> INSERT tbl (a, b, c)
>> VALUES (@.a, @.b, @.c)
>> SELECT @.id = scope_identity
>>
>>
>>
>>
>> --
>> 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|||"Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in news:caw%f.9156$P01.6110@.pd7tw3no:

> Hi Tom,
> Just so you know, triggers and other server side operations will not
> affect the @.@.identity result and hence, will not return an incorrect
> result.
> Rick

That seems to be the opposite of what this exert from SQL 2005 BOL says.
I have made two sections UpperCase.

----
"SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions
because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a
specified table. IDENT_CURRENT returns the value generated for a specific
table in any session and any scope. For more information, see
IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are
generated in any table in the current session. However, SCOPE_IDENTITY
returns values inserted only within the current scope; @.@.IDENTITY is not
limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is
defined on T1. WHEN A ROW IS INSERTED TO T1, THE TRIGGER FIRES AND
INSERTS A ROW IN T2. This scenario illustrates two scopes: the insert on
T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @.@.IDENTITY and
SCOPE_IDENTITY will return different values at the end of an INSERT
statement on T1. @.@.IDENTITY WILL RETURN THE LAST IDENTITY COLUMN VALUE
INSERTED ACROSS ANY SCOPE IN THE CURRENT SESSION. THIS IS THE VALUE
INSERTED IN T2. SCOPE_IDENTITY() will return the IDENTITY value inserted
in T1. This was the last insert that occurred in the same scope. The
SCOPE_IDENTITY() function will return the null value if the function is
invoked before any INSERT statements into an identity column occur in the
scope.

Failed statements and transactions can change the current identity for a
table and create gaps in the identity column values. The identity value
is never rolled back even though the transaction that tried to insert the
value into the table is not committed. For example, if an INSERT
statement fails because of an IGNORE_DUP_KEY violation, the current
identity value for the table is still incremented."

----

A session is described as:

By default, a session starts when a user logs in and ends when the user
logs off. All operations during a session are subject to permission
checks against that user.

--
Lyle Fairfield|||Hmmm,

My mistake. Never believe what you read the first time I guess. I got the
info from an MSDN forum page, but didn't bookmark the page, so I'll have to
find it again. I did find reference to something similar in the MSDN
library which mentions returning the expected Identity value after a trigger
has fired on a table without an identity field. Luckily there are no
triggers on this DB at this point, so that will at least buy me some time
until we can get something mapped out for the client.

Rick

"Lyle Fairfield" <lylefairfield@.aim.com> wrote in message
news:Xns97A48FC10EA65lylefairfieldaimcom@.216.221.8 1.119...
> "Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in news:caw%f.9156$P01.6110@.pd7tw3no:
>> Hi Tom,
>>
>> Just so you know, triggers and other server side operations will not
>> affect the @.@.identity result and hence, will not return an incorrect
>> result.
>>
>> Rick
> That seems to be the opposite of what this exert from SQL 2005 BOL says.
> I have made two sections UpperCase.
> ----
> "SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions
> because they return values that are inserted into identity columns.
> IDENT_CURRENT is not limited by scope and session; it is limited to a
> specified table. IDENT_CURRENT returns the value generated for a specific
> table in any session and any scope. For more information, see
> IDENT_CURRENT (Transact-SQL).
> SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are
> generated in any table in the current session. However, SCOPE_IDENTITY
> returns values inserted only within the current scope; @.@.IDENTITY is not
> limited to a specific scope.
> For example, there are two tables, T1 and T2, and an INSERT trigger is
> defined on T1. WHEN A ROW IS INSERTED TO T1, THE TRIGGER FIRES AND
> INSERTS A ROW IN T2. This scenario illustrates two scopes: the insert on
> T1, and the insert on T2 by the trigger.
> Assuming that both T1 and T2 have identity columns, @.@.IDENTITY and
> SCOPE_IDENTITY will return different values at the end of an INSERT
> statement on T1. @.@.IDENTITY WILL RETURN THE LAST IDENTITY COLUMN VALUE
> INSERTED ACROSS ANY SCOPE IN THE CURRENT SESSION. THIS IS THE VALUE
> INSERTED IN T2. SCOPE_IDENTITY() will return the IDENTITY value inserted
> in T1. This was the last insert that occurred in the same scope. The
> SCOPE_IDENTITY() function will return the null value if the function is
> invoked before any INSERT statements into an identity column occur in the
> scope.
> Failed statements and transactions can change the current identity for a
> table and create gaps in the identity column values. The identity value
> is never rolled back even though the transaction that tried to insert the
> value into the table is not committed. For example, if an INSERT
> statement fails because of an IGNORE_DUP_KEY violation, the current
> identity value for the table is still incremented."
> ----
> A session is described as:
> By default, a session starts when a user logs in and ends when the user
> logs off. All operations during a session are subject to permission
> checks against that user.
>
> --
> Lyle Fairfield|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> Lyle, this isn't a ground up application, this is converting a clients
> legacy application. The bean counters have better things to do with their
> budget than build a new version of something they are already using.

Nevermind the stored procedures, but not ripping out DAO while you're
at it, seems wrong to me. I don't know much about DAO, but since it is
a deprecated interface, there is risk that you will run into issues in
SQL Server that are not supported when you use DAO. (The most typical
example would be new data types.)

--
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|||Rico wrote:
> Thanks Tom and Erland,
> I wound up researching Scope_Identity and that lead me to @.@.identity. I
> wound up changing my DAO code as follows;
> Instead of...
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
> I changed the code to
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> rst.Update
> MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)

If you use an ADODB.Recordset with the correct property settings
the the new record will be added to the recordset you have open
and the newly added record will be the current record.|||!!!!!
LOL
What fun is there is you give good smart simple answers?

Retrieving a Value from a SQL Database in Code Behind

VWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:

SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @.Username)

The name of the data source is SqlDataSource2

Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?

I wouldn't use a SqlDataSource for this. I would use ADO.NET code and ExecuteScalar to obtain one value.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

Their are two potential equivalents to a RecordSet. One is using a DataReader, for forward only, read only access, and the other is a DataSet, in which you can move forwards and backwards. DataReader is the more common approach, but it depends on what you want to do.

http://msdn2.microsoft.com/EN-US/library/system.data.sqlclient.sqldatareader.aspx

|||

Thanks Mike. I looked at the link. The MSDN info is so cryptic to me that I cannot discern what to do. Could you provide a real VB code example of what you are talking about? Thanks for the help.

|||

Dim SystemUserId As Int32 = 0
Dim sql As String = "SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @.Username)"
Using conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@.UserName", TxtUserName.Text)
Try
conn.Open()
SystemUserId = Convert.ToInt32(cmd.ExecuteScalar())
Catch ex As Exception
'Do whatever with Exceptions
End Try
End Using

In the line that starts cmd.Parameters.AddWithValue, I have assumed that the value for the @.Username will come from a TextBox called TxtUserName. Of course, you would need to adjust this to reflect the actual source. Also, connString is a variable of type String that contains your connection string. You would obviously have to suuply this value as well.

|||

Mike. I tried the following using a code example from the link you provided. I get errors saying that types SqlConnection and SqlCommand are not defined. What do I need to do here? Thanks.

ProtectedFunction GetSystemUserId(ByVal UsernameAsString)AsString

Dim connStringAsString ="<%$ ConnectionStrings:GoodNews_IntranetConnectionString %>"

Dim UserIDAsString

Dim sqlAsString ="SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = '" + Username +"')"

Using connAsNew SqlConnection(connString)

Dim cmdAsNew SqlCommand(sql, conn)

Try

conn.Open()

UserID = Str(cmd.ExecuteScalar())

Catch exAs Exception

Console.WriteLine(ex.Message)

EndTry

EndUsing

Return UserID

EndFunction

|||

You need to add

Imports System.Data.SqlClient

at the top of the page. That makes the classes relating to connections and commands within the System.Data.SqlClient available to that page. The alternative is to use the full reference:

Using conn as new System.Data.SqlClient.SqlConnection

etc. Imports statements save a lot of typing in the long run.

|||

Thanks loads Mike!!! After I added the "Imports" at the top, all I had to do was modify my connection string (just copied from my web.config file) and she worked. You have now moved me from novice level 1 to novice level 1.1. Thanks and God bless.

|||

Novice level 1.1's will use:

Dim Conn as new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString)

and keep the connection string in the connection string section of the web.config file.

|||

I would be interested on the information on what you called "DataSet." Can you provide a link?

|||

Public Function GetDataSet(ByVal SQLString As String) As DataSet
Dim cmd As New SqlCommand(SQLString)
Return GetDataSet(cmd)
End Function

Public Function GetDataSet(ByVal cmd As SqlCommand) As DataSet
NullifyParameters(cmd)
Dim DS As New DataSet
Dim MyCommand As SqlDataAdapter
OpenConn(cmd)
cmd.CommandTimeout = m_CommandTimeout
MyCommand = New SqlDataAdapter(cmd)
'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want
MyCommand.Fill(DS, "DS")
CloseConn(cmd)
Return DS
End Function

Private Sub NullifyParameters(ByVal cmd As SqlCommand)
For Each p As SqlParameter In cmd.Parameters
If p.Value Is Nothing OrElse (TypeOf p.Value Is String AndAlso p.Value.Length = 0 AndAlso (p.SqlDbType = SqlDbType.DateTime OrElse p.SqlDbType = SqlDbType.Int OrElse p.SqlDbType = SqlDbType.Money OrElse p.SqlDbType = SqlDbType.Real OrElse p.SqlDbType = SqlDbType.Float OrElse p.SqlDbType = SqlDbType.Decimal OrElse p.SqlDbType = SqlDbType.BigInt OrElse p.SqlDbType = SqlDbType.UniqueIdentifier OrElse p.SqlDbType = SqlDbType.TinyInt)) Then
p.Value = DBNull.Value
End If
Next
End Sub

I can't really provide the OpenConn/CloseConn functions, but they do what you would expect them to basically. They just have transaction support in them, which then needs more functions, etc. So here are some ones that will do what you want:

Private OpenConn(cmd as SqlCommand)

dim conn as new SqlConnection(System.Configuration.ConfigurationManager("ConnectionString").ToString)

conn.open

cmd.Connection=conn

end Sub

Private CloseConn(cmd as SqlCommand)

cmd.Connection.Close

end sub

|||

Add those functions to any code file you want to use them in (or make them part of a class library), and then you can do things like:

Dim MyDataSet as dataset = GetDataSet("SELECT * FROM MyTable")

then you can iterate through the dataset rows like

For each dr as datarow in MyDataSet.tables(0).Rows

if dr("Column1")= ... then

' Do something here

end if

next

|||

I placed your code in a class module. I got the following errors. Any help in clearing these up would be appreciated. Thanks.

The following code generates the error, "Type 'DataSet not defined."

PublicFunction GetDataSet(ByVal SQLStringAsString)As DataSet

Dim cmdAsNew SqlCommand(SQLString)

Return GetDataSet(cmd)

EndFunction

The following code generates the error, "m_CommandTimeout not declared."

PublicFunction GetDataSet(ByVal cmdAs SqlCommand)As DataSet

NullifyParameters(cmd)

Dim DSAsNew DataSet

Dim MyCommandAs SqlDataAdapter

OpenConn(cmd)

cmd.CommandTimeout = m_CommandTimeout

MyCommand =New SqlDataAdapter(cmd)

'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want

MyCommand.Fill(DS,"DS")

CloseConn(cmd)

Return DS

EndFunction

The following code generates the error, "SqlDbType is not declared."

PrivateSub NullifyParameters(ByVal cmdAs SqlCommand)

ForEach pAs SqlParameterIn cmd.Parameters

If p.ValueIsNothingOrElse (TypeOf p.ValueIsStringAndAlso p.Value.Length = 0AndAlso (p.SqlDbType = SqlDbType.DateTimeOrElse p.SqlDbType = SqlDbType.IntOrElse p.SqlDbType = SqlDbType.MoneyOrElse p.SqlDbType = SqlDbType.RealOrElse p.SqlDbType = SqlDbType.FloatOrElse p.SqlDbType = SqlDbType.DecimalOrElse p.SqlDbType = SqlDbType.BigIntOrElse p.SqlDbType = SqlDbType.UniqueIdentifierOrElse p.SqlDbType = SqlDbType.TinyInt))Then

p.Value = DBNull.Value

EndIf

Next

EndSub

The following code generates the error, "Error 14 'ConfigurationManager' is a type in 'Configuration' and cannot be used as an expression."

Dim connAsNew SqlConnection(System.Configuration.ConfigurationManager("ConnectionString").ToString)

|||

Add

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

to the top of the file containing these methods.

Comment out the Timout line. The default value is good for most scenarios. You will know if you have to increase it, because you get Timeout errors.

|||

The changes that mike suggested should fix the errors. If they don't, please post again.

|||

Mike:

I put the routines you gave me into a class module and I defined them as Shared so that I may call them from other modules. However, when I change the code as shown below (adding the Shared) I get the error that follows. Also I am confused as to how you can have two routines called "GetDataSet." How does the code know which one you are calling?

Cannot refer to an instance member of a class from within a shared method or shared member initializer without an explicit instance of the class.

Public SharedFunction GetDataSet(ByVal SQLStringAsString)As DataSet

Dim cmdAsNew SqlCommand(SQLString)

Return GetDataSet(cmd)

EndFunction

PrivateFunction GetDataSet(ByVal cmdAs SqlCommand)As DataSet

NullifyParameters(cmd)

Dim DSAsNew DataSet

Dim MyCommandAs SqlDataAdapter

OpenConn(cmd)

'cmd.CommandTimeout = m_CommandTimeout

MyCommand =New SqlDataAdapter(cmd)

'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want

MyCommand.Fill(DS,"DS")

CloseConn(cmd)

Return DS

EndFunction