Showing posts with label express. Show all posts
Showing posts with label express. Show all posts

Monday, March 19, 2012

[SQL Serevr 2005 Express] Loading data from an Excel sheet

Hi,

Is it possible to load data from an Excel sheet column, into a DB column within SQL Server Express Edition? I'm able to copy data from the DB talbe column into the Excel sheet, but not the other way, which forces me to enter data row by row, manually.

Thanks,

Alon

I describe one way I can do:

Create your table with the datastructure you want from VWD IDE for your batabase in the App_Data folder.

1.Select data from your excel columns and copy.

2.Open your table, highlight one blank row, right-click the highlighted area, from the manu select paste.

You already know how to copy data from SQL2005 Express to excel. It is sort of the same thing.

You may need to watch your column datatype.

|||

Hi limno,

Thanks for the detailed answerSmile [:)].

I tried it before, but unfortunately VWD doesn't support Paste of multiple cells. This means that if you'll copy several cells (within 1 column) from the Excel sheet, and try to paste it in the DB table (in VWD), as you described in step #2, you'll notice that only 1 cell, the one you highlighted, is affected.

This is, unless I'm missing something very basic...

Thanks,

Alon

|||

You paste over the highlighted row. That means all clomns in your table are highlighted.

If you highlight only one cell, it is true that everything goes in that cell.

Hope this helps.

Sunday, March 11, 2012

[SQL 2005 Express] Question on Business Intelligence Development Studio (VS2005)

I am trying to create a report using BI Development Studio.

In Pivot Table, I can change the presentation of the data area to align with row or column, just like this:
http://img164.imageshack.us/img164/2055/untitledsi4.png

However, I can't seem to do that in BI Development Studio while designing the report. Does any of you know how to change the presentation of the data area of a report?

This is how it looks like now, the data area is sticking to the COLUMN, but I need them to stick with ROW so that I can handle more columns...
http://img103.imageshack.us/img103/4066/untitlednc8.pngmanage to resolve it by inserting just 1 details, then insert "Add ROW" at the details. This thing doesn't come with the Wizard. :D

[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'

Hello,

I am new to SQL Server, sorry for my ignorance !!!

I have installed SQL Server 2005 Express on my desktop (Win 2000 Pro.)

I have created a new database.

I just created a new login/user (SQL Authentication) and when I try to login, it

errors out:

[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'

I believe the error is from SQL Server and not from ODBC driver.

I am also only able to connect by using my own userid (the one that I am connected to my desktop): domain\userid, and then I am only able to connect to 'master' db:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/usr/local/etc>isql SQLSERVER_POS -v
++
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
++
SQL> use pcspos
[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Could not locate entry in sysdatabases for database 'pcspos'. No entry found with that name. Make sure that the name is entered correctly.
[ISQL]ERROR: Could not SQLExecute
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Hello,

While I browse the SSMSE (SQL Server Management Studion Express) , I see that the database state (from the

Database Properties Windows) is NORMAL | SHUTDOWN !!!!!!

Is this OK ?

In the meantime when I login via SSMSE, I can see all the tables and data in the database !!!!!!!!!

Thanks,

Tom

|||

Hello, I was able to change the Database State to NORMAL (I changed the 'Auto Close' option to False).

BUT I am still not able to connect:

/usr/local/etc>isql SQLSERVER_POS -v
[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'.
[ISQL]ERROR: Could not SQLConnect

|||If you are using Windows Authentication only, you cannot use SQL Server logins to connect to the server. (see how to switch the authentication mode on my website under the Screencasts). If you want to use WIndows authentication you will have to be aware that you cannot explicitly pass a user name within the connectionstring, the username is passed implicitly through Windows. If you have enabled Mixed authentication already, make sure that you are using the right password.


Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thank you for your reply.

I am using the SQL Authentication mode, see below I am able to connect from the DOS prompt (localy),

but I can NOT connect from a Linux server by using the ODBC driver:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

C:\>isql -S (local)\SQLEXPRESS -U pos_sql05 -P passwd
1> select db_name()
2> go

--

--

--
POS

(1 row affected)
1> select * from pcs_lookup_active_status;
2> go
ACTIVE_STATUS_KEY ACTIVE_STATUS_DESC
-- --
1 Active
2 Inactive
3 Deleted

(3 rows affected)

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

|||I don′t know how the tool for Linux works but if you do not pass a username using SQL Server authentication there has to be somewhere a setting file where these information is stored.
|||

I am using a config file by the name odbc.ini.

It looks like the following:

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

[SQLSERVER_POS]
Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
Description = Easysoft SQL Server ODBC driver (unixODBC 2.2.12)
Server = 10.20.30.40\SQLEXPRESS
Port = 1433
Database = POS
User = pos_sql05
Password = passwd
Mars_Connection = No
Logging = 31
LogFile = /tmp/sqlserver.log
QuotedId = Yes
AnsiNPW = Yes
Language =
Version7 = No
ClientLB = No
Failover_Partner =
VarMaxAsLong = No

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

As you cann tell, the user id and password are there.

Thanks again.

Regards,

Tom

|||

Make sure that you are using the right password for the mentioned user. In addition make sure taht the user is priviledged for the POS database, cause authentication or the connection will also fail if the initial database is not one of the allowed ones. if you are not sure, try to connect to the master database. A user which is granted access to the server should have access to the master database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thanks again.

The userid and password are correct (I am able to connect via isql from the DOS prompt).

I think the problem relies on the fact that I am coming remotetly (from a Linux server), BUT I have

enabled the TCP/IP protocol connection properties. What else can I look for ?

Thank you.

Tom

|||

How are you authenticating? Is your server set to authenticate using Windows authentication only or integrated authentication? Perhaps this unix driver is trying to use SQL authentication and your SQL installation only accepts windows authenticated connections. That would explain this problem.

Hope this helps!

John (MSFT)

|||

Hi John & Jens, the issue has been resolved. I contacted the ODBC driver's vendor and they provided me with an updated copy of it.

Thank you for your help and suggestions.

Keep up the GREAT job.

Best regards,

Tom

Thursday, March 8, 2012

[news] free copy of SQL Prompt

so last week i finally downloaded and installed SQL Server 2005, and along with that, i downloaded Studio Express (i had previoulsy been using MSDE with WinSQL by Synametrics)

anyhow, today i got an alert from google (you guys use those, right?) that there's a free copy of something called SQL Prompt available from Red Gate

i tried it, and i like it!!

see http://builder.com.com/5100-6388_14-6085249.html?part=rss&subj=bldrEDIT - Coo - my first post did stick|||anyhow, today i got an alert from google (you guys use those, right?) that there's a free copy of something called SQL Prompt available from Red Gate

i tried it, and i like it!!Harrumph. I paid $25 USD for my copy before Red Gate bought the product. So long as they keep developing it though (doesn't pick up functions, can't handle subqueries and derived tables etc yet) then I am happy :)

Tuesday, March 6, 2012

[MS Sql] Data type nvarchar

Hi everybody,

I'm using SQL Server Management Studio Express.
I'm trying to create a field which contains a text entered by the user. So, it should be able to contain at least 500 characters.

I used the type "nvarchar(MAX)". The problem is that the type contains about 50 characters max!!
I couldn't find out where and how to fix that.
If you have any idea :)

Thanks a lotType over the the MAX with the desired length of 500.|||Hi tmorton
Of course I tried nvarchar(500) (forgot to tell that). But it's not working either.
It sounds like nvarchar(MAX) is the max length type, and limited to about 50...
Thanks for your reply. And if someone has any idea :)|||That's all it takes from the SQL Server end. If you are only managing to store 50 characters in that column, then I would venture a guess that your data is being truncated before it hits the database. What does your code look like that is adding the data to your table?|||I believe that SQL Management Studio truncates text that it displays. Use something else to view/edit the column.|||Hi
It's weird, it's working now. And yet I didn't change anything particular. Maybe a "bug" on SQL Server... weird though. Thanks a lot to you guys :)

Friday, February 24, 2012

[help] installation problems with sql server express 2005

hello everybody
I've downloaded a free version of ms sql server express 2005
and while I lauch the install file, I Have a bug :
Something like :
"you don't have write and read permission" - conctact administator
but i'm logged on a administator session

if anyone can help me ..
thnx in advance ..
hzben

Hi,

Ensure you have Read & Write permission on the folder/drive you are trying to install SQL Server , i.e. Program Files folder!!! www.windowsecurity.com/articles/Understanding-Windows-NTFS-Permissions.html FYI.

Hemantgiri S. Goswami

|||thank you for this reply
Do i can export a sql 2000 database to sql 2005 express only with a .sql file or do i need the .mdf and .ldf files of my old database ?
I need to export only the structure (tables ,columns and triggers) of my database to sql express 2005 .
have a nice day !!
hzben|||

Hi hzben,

you can export the structure of your database with your .sql file (Script of Database Objects) and for that you don't need .mdf/.ldf files. But in case you wants to attach your database to sql server you require .mdf and .ldf.

HTH

Hemantgiri S. Goswami

Sunday, February 19, 2012

[DBNETLIB][ConnectionWrite (send()).] General network error.

Hi,
in our environment
- Windows XP
- SQL Server Express 2005
- Internet connection via TCP/IP protocol

The described network error occurs, when we unplug the network cable:
[DBNETLIB][ConnectionWrite (send()).] General network error.

The affected database resides on the locally installed SQLExpress, it doesn't use any network connections. The error doesn't occur immediately, in most cases there are up to 30 seconds where data can be (correctly!) written to the recordset, but after that timeout the connetion is broken.

We already tried out the described workarounds:
- disable named pipes protocol
- set SynAttackProtect=0 in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

The error doesn't occur with MSDE (SQL 2000) or under Vista.

Thanks for help!

Mathias

I suggest using shared memory to connect locally or making sure your server specification is one that correctly gets resolved to use the loopback (e.g. .\ or (local) or localhost). You may also want to see if your hosts file has an entry for the machine to make sure it resolves its own name. Right now, it appears as though your communications are going out over the network. You may have some amount of data transfer after unplugging the cable because the network connection is buffered, so the server still has that much data in its network buffer before it runs out. I think the connection timeout is consuming the bulk of that 30 seconds. Vista most likely has its own hostname in its hosts file, or has some other trick for self-resolving its computer name. More than likely, your instance of SQL 2000 is using shared memory to connect or using a different specification of the hostname.

Hope that helps,

John

Monday, February 13, 2012

[2005 Express]Add SPs to a Schema?

I have SQL Server 2005 Express set up on a PC. I imported a backup of an MSDE database. All of the database objects are there. However, I need to assign permissions to my Stored Procedures. So, using Management Studio Express, I saw absolutely no way of adding a stored procedure to a schema or to a user. So I downloaded SQL Server 2005 Developer from my MSDN account. I installed the client tools to get the full version of Management Studio. Same thing. No way to add permissions to a SP. Some web articles say to right-click the SP and select Properties. However, there is no "Properties" in the right-click menu!!! There is only Rename, Delete, Modify, etc. How can something so easy with an old product be so difficult with a new one? Does anyone know how to accomplish adding permissions to SPs?

I can right-click a table or a View and get the popup window with the list view and contents to set permissions to tables and views.On my build of SSMS, I do have a properties context menu off of any sproc in OE, and when I click it I can add perms to the sproc. I have this build: 9.00.2047.00. which do you have?

I am thinking perhaps this is due to a bad reaction between SSMS and SSMSE perhaps. EDIT: Is that redundant? :)

If nothig else works, I would try unistalling them both and the reistalling only SSMS.|||I have build 9.00.1399.00, so it is an older version. This build was from MSDN Subscription Downloads, on Disk 2 for SQL Server 2005 Developer. Do you know where I could download an update/the latest version?

UPDATE: I uninstalled and then reinstalled all of the SQL Server Workstation stuff. Then, I installed an instance of SQL Server 2005 Developer on my test PC. Now, with SSMS, I can right-click and get "Properties" for stored Procedures in the SQL Server 2005 instance, but I still can't get the right-click "Properties" in the Express instance. Is this a limitation of Express?|||Yes, I think this is a limitation of ssmse.

The sql express GUI is very limited in what it does. it's meant to be a teaser to make it more attractive for people to start using sql server instead of mysql, but then upgrade to a pay version once they are addicted. :)

EDIT: I am running sp1 - I think that's the difference between our two version numbers. You can download sp1 from here: http://www.microsoft.com/sql/sp1.mspx|||Oh, I see. Even the Management Studio updates are included in the SQL Server service pack, huh? I'll have to apply that. Thanks.|||oh yea. UI devs put bugs in their code too. :)|||Well, overall, I can get the Express instance to run the SPs from my VB.NET code. I used ALTER to put them into a new schema, and gave the user account that connects from my intranet app permissions on that schema. This allows the app to run the SPs. It's odd, what use is setting permissions on the specific SP (in the Developer instance, or course) when just using the schema allows execution? Is it to be more granular with permissions and, say, deny permissions on a specific SP within a schema to a schema user?|||that's one of the points of using schemas.

note that an explicit deny on a sproc should override any indirect grant (thru a schema).

Saturday, February 11, 2012

@@identity not working using SQL Express 2005 Sept CTP

I have just inherited some code from another company which uses SQL Server.
The logic is dependent on @.@.IDENTITY to retrieve the record just inserted -
so that more info can be added to the record. kind of a silly implementation
- but it uses a bunch of generic code to insert a new row and generate a
GUID, etc.. and it would be significant work to rewrite the entire
application.
The problem is the even though the table has an id column that is defined
with IDENTITY - and through queries I can easily see that each row added has
the proper values for the id column - @.@.IDENTITY inside the .asp vb scripting
app is returning NULL... I am using SQL Server Express 2005 Sept CTP set up
using a system DSN with the SQL Native Client (2005) driver.
here is the code:
Function NewRow(con,tab,col,val)
set nrconn = Server.CreateObject("ADODB.Connection")
nrconn.open SiteConnectionString
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
Set rs = nrconn.Execute(cmd)
cmd = "select @.@.IDENTITY as 'newid' from "&tab
Set rs = nrconn.Execute(cmd)
set NewRow = rs
End Function
After returning the code uses NewRow("newid") to fetch the record and update
it. of course, it returns NULL and no record is retrieved... Any help
would be much appreciated.
-Steve-o
Hi
You are submitting it as 2 batches, so it is not available to the 2nd one.
Any reason why you are not using stored procedures to do this? Dynamic SQL
is asking for security problems.
SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
@.@.identity's value if it does another insert.
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
SCOPE_IDENTITY as 'newid' from "&tab "
Set rs = nrconn.Execute(cmd)
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/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>I have just inherited some code from another company which uses SQL Server.
> The logic is dependent on @.@.IDENTITY to retrieve the record just
> inserted -
> so that more info can be added to the record. kind of a silly
> implementation
> - but it uses a bunch of generic code to insert a new row and generate a
> GUID, etc.. and it would be significant work to rewrite the entire
> application.
> The problem is the even though the table has an id column that is defined
> with IDENTITY - and through queries I can easily see that each row added
> has
> the proper values for the id column - @.@.IDENTITY inside the .asp vb
> scripting
> app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> up
> using a system DSN with the SQL Native Client (2005) driver.
> here is the code:
> Function NewRow(con,tab,col,val)
> set nrconn = Server.CreateObject("ADODB.Connection")
> nrconn.open SiteConnectionString
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> Set rs = nrconn.Execute(cmd)
> cmd = "select @.@.IDENTITY as 'newid' from "&tab
> Set rs = nrconn.Execute(cmd)
> set NewRow = rs
> End Function
> After returning the code uses NewRow("newid") to fetch the record and
> update
> it. of course, it returns NULL and no record is retrieved... Any help
> would be much appreciated.
> --
> -Steve-o
|||Hi
Strange ... the code works on older SQL server version. Nonetheless I
changed as you suggested and same result. I did a little more digging and
the execute line is return rs as type of 'Fields' instead of RecordSet AND
the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
help !
-Steve-o
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> You are submitting it as 2 batches, so it is not available to the 2nd one.
> Any reason why you are not using stored procedures to do this? Dynamic SQL
> is asking for security problems.
> SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> @.@.identity's value if it does another insert.
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> SCOPE_IDENTITY as 'newid' from "&tab "
> Set rs = nrconn.Execute(cmd)
> 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/
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>
>
|||solved the "Fields" problem - I had erroneously removed the "set" fro the rs
= stmt (WHOOPS !). So now the type is returned as Recordset, however,
rs.Fields.Count is 0 ... so still nothing is being returned...
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> Hi
> Strange ... the code works on older SQL server version. Nonetheless I
> changed as you suggested and same result. I did a little more digging and
> the execute line is return rs as type of 'Fields' instead of RecordSet AND
> the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> help !
> --
> -Steve-o
>
> "Mike Epprecht (SQL MVP)" wrote:
|||more info ...
If I follow the original sequence of insert followed by select query I do
get a recordset back with a field count of 1. the name of the item is indeed
'newid' but the value is NULL.
strangely enough when using SQLCMD and doing this sequence of operations by
hand - SQLCMD outputs one line for each record in the table with the newid
field value for each being the "latest" value (i.e. value of 23 if the last
inserted record had and identity value of 23). so it seems like it is
working there, but not in the scripted app...
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> rs.Fields.Count is 0 ... so still nothing is being returned...
> --
> -Steve-o
>
> "steve-o" wrote:
|||Try adding SET NOCOUNT ON before the INSERT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...[vbcol=seagreen]
> more info ...
> If I follow the original sequence of insert followed by select query I do
> get a recordset back with a field count of 1. the name of the item is indeed
> 'newid' but the value is NULL.
> strangely enough when using SQLCMD and doing this sequence of operations by
> hand - SQLCMD outputs one line for each record in the table with the newid
> field value for each being the "latest" value (i.e. value of 23 if the last
> inserted record had and identity value of 23). so it seems like it is
> working there, but not in the scripted app...
>
> --
> -Steve-o
>
> "steve-o" wrote:
|||YAY !!! Mike gave me the first half of the solution and Tibor the second
half. Thanks !!!
-Steve-o
"Tibor Karaszi" wrote:

> Try adding SET NOCOUNT ON before the INSERT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
>

Thursday, February 9, 2012

@@identity not working using SQL Express 2005 Sept CTP

I have just inherited some code from another company which uses SQL Server.
The logic is dependent on @.@.IDENTITY to retrieve the record just inserted -
so that more info can be added to the record. kind of a silly implementatio
n
- but it uses a bunch of generic code to insert a new row and generate a
GUID, etc.. and it would be significant work to rewrite the entire
application.
The problem is the even though the table has an id column that is defined
with IDENTITY - and through queries I can easily see that each row added has
the proper values for the id column - @.@.IDENTITY inside the .asp vb scriptin
g
app is returning NULL... I am using SQL Server Express 2005 Sept CTP set up
using a system DSN with the SQL Native Client (2005) driver.
here is the code:
Function NewRow(con,tab,col,val)
set nrconn = Server.CreateObject("ADODB.Connection")
nrconn.open SiteConnectionString
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
Set rs = nrconn.Execute(cmd)
cmd = "select @.@.IDENTITY as 'newid' from "&tab
Set rs = nrconn.Execute(cmd)
set NewRow = rs
End Function
After returning the code uses NewRow("newid") to fetch the record and update
it. of course, it returns NULL and no record is retrieved... Any help
would be much appreciated.
-Steve-oHi
You are submitting it as 2 batches, so it is not available to the 2nd one.
Any reason why you are not using stored procedures to do this? Dynamic SQL
is asking for security problems.
SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
@.@.identity's value if it does another insert.
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
SCOPE_IDENTITY as 'newid' from "&tab "
Set rs = nrconn.Execute(cmd)
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/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>I have just inherited some code from another company which uses SQL Server.
> The logic is dependent on @.@.IDENTITY to retrieve the record just
> inserted -
> so that more info can be added to the record. kind of a silly
> implementation
> - but it uses a bunch of generic code to insert a new row and generate a
> GUID, etc.. and it would be significant work to rewrite the entire
> application.
> The problem is the even though the table has an id column that is defined
> with IDENTITY - and through queries I can easily see that each row added
> has
> the proper values for the id column - @.@.IDENTITY inside the .asp vb
> scripting
> app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> up
> using a system DSN with the SQL Native Client (2005) driver.
> here is the code:
> Function NewRow(con,tab,col,val)
> set nrconn = Server.CreateObject("ADODB.Connection")
> nrconn.open SiteConnectionString
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> Set rs = nrconn.Execute(cmd)
> cmd = "select @.@.IDENTITY as 'newid' from "&tab
> Set rs = nrconn.Execute(cmd)
> set NewRow = rs
> End Function
> After returning the code uses NewRow("newid") to fetch the record and
> update
> it. of course, it returns NULL and no record is retrieved... Any help
> would be much appreciated.
> --
> -Steve-o|||Hi
Strange ... the code works on older SQL server version. Nonetheless I
changed as you suggested and same result. I did a little more digging and
the execute line is return rs as type of 'Fields' instead of RecordSet AND
the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
help !
-Steve-o
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> You are submitting it as 2 batches, so it is not available to the 2nd one.
> Any reason why you are not using stored procedures to do this? Dynamic SQL
> is asking for security problems.
> SCOPE_IDENTITY is the better way to retrieve is as a trigger will change t
he
> @.@.identity's value if it does another insert.
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> SCOPE_IDENTITY as 'newid' from "&tab "
> Set rs = nrconn.Execute(cmd)
> 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/
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>
>|||solved the "Fields" problem - I had erroneously removed the "set" fro the rs
= stmt (WHOOPS !). So now the type is returned as Recordset, however,
rs.Fields.Count is 0 ... so still nothing is being returned...
--
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> Hi
> Strange ... the code works on older SQL server version. Nonetheless I
> changed as you suggested and same result. I did a little more digging and
> the execute line is return rs as type of 'Fields' instead of RecordSet AND
> the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> help !
> --
> -Steve-o
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||more info ...
If I follow the original sequence of insert followed by select query I do
get a recordset back with a field count of 1. the name of the item is indee
d
'newid' but the value is NULL.
strangely enough when using SQLCMD and doing this sequence of operations by
hand - SQLCMD outputs one line for each record in the table with the newid
field value for each being the "latest" value (i.e. value of 23 if the last
inserted record had and identity value of 23). so it seems like it is
working there, but not in the scripted app...
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> solved the "Fields" problem - I had erroneously removed the "set" fro the
rs
> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> rs.Fields.Count is 0 ... so still nothing is being returned...
> --
> -Steve-o
>
> "steve-o" wrote:
>|||Try adding SET NOCOUNT ON before the INSERT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...[vbcol=seagreen]
> more info ...
> If I follow the original sequence of insert followed by select query I do
> get a recordset back with a field count of 1. the name of the item is ind
eed
> 'newid' but the value is NULL.
> strangely enough when using SQLCMD and doing this sequence of operations b
y
> hand - SQLCMD outputs one line for each record in the table with the newid
> field value for each being the "latest" value (i.e. value of 23 if the las
t
> inserted record had and identity value of 23). so it seems like it is
> working there, but not in the scripted app...
>
> --
> -Steve-o
>
> "steve-o" wrote:
>|||YAY !!! Mike gave me the first half of the solution and Tibor the second
half. Thanks !!!
--
-Steve-o
"Tibor Karaszi" wrote:

> Try adding SET NOCOUNT ON before the INSERT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
>

@@identity not working using SQL Express 2005 Sept CTP

I have just inherited some code from another company which uses SQL Server.
The logic is dependent on @.@.IDENTITY to retrieve the record just inserted -
so that more info can be added to the record. kind of a silly implementation
- but it uses a bunch of generic code to insert a new row and generate a
GUID, etc.. and it would be significant work to rewrite the entire
application.
The problem is the even though the table has an id column that is defined
with IDENTITY - and through queries I can easily see that each row added has
the proper values for the id column - @.@.IDENTITY inside the .asp vb scripting
app is returning NULL... I am using SQL Server Express 2005 Sept CTP set up
using a system DSN with the SQL Native Client (2005) driver.
here is the code:
Function NewRow(con,tab,col,val)
set nrconn = Server.CreateObject("ADODB.Connection")
nrconn.open SiteConnectionString
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
Set rs = nrconn.Execute(cmd)
cmd = "select @.@.IDENTITY as 'newid' from "&tab
Set rs = nrconn.Execute(cmd)
set NewRow = rs
End Function
After returning the code uses NewRow("newid") to fetch the record and update
it. of course, it returns NULL and no record is retrieved... Any help
would be much appreciated.
--
-Steve-oHi
You are submitting it as 2 batches, so it is not available to the 2nd one.
Any reason why you are not using stored procedures to do this? Dynamic SQL
is asking for security problems.
SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
@.@.identity's value if it does another insert.
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
SCOPE_IDENTITY as 'newid' from "&tab "
Set rs = nrconn.Execute(cmd)
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/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>I have just inherited some code from another company which uses SQL Server.
> The logic is dependent on @.@.IDENTITY to retrieve the record just
> inserted -
> so that more info can be added to the record. kind of a silly
> implementation
> - but it uses a bunch of generic code to insert a new row and generate a
> GUID, etc.. and it would be significant work to rewrite the entire
> application.
> The problem is the even though the table has an id column that is defined
> with IDENTITY - and through queries I can easily see that each row added
> has
> the proper values for the id column - @.@.IDENTITY inside the .asp vb
> scripting
> app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> up
> using a system DSN with the SQL Native Client (2005) driver.
> here is the code:
> Function NewRow(con,tab,col,val)
> set nrconn = Server.CreateObject("ADODB.Connection")
> nrconn.open SiteConnectionString
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> Set rs = nrconn.Execute(cmd)
> cmd = "select @.@.IDENTITY as 'newid' from "&tab
> Set rs = nrconn.Execute(cmd)
> set NewRow = rs
> End Function
> After returning the code uses NewRow("newid") to fetch the record and
> update
> it. of course, it returns NULL and no record is retrieved... Any help
> would be much appreciated.
> --
> -Steve-o|||Hi
Strange ... the code works on older SQL server version. Nonetheless I
changed as you suggested and same result. I did a little more digging and
the execute line is return rs as type of 'Fields' instead of RecordSet AND
the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
help !
--
-Steve-o
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> You are submitting it as 2 batches, so it is not available to the 2nd one.
> Any reason why you are not using stored procedures to do this? Dynamic SQL
> is asking for security problems.
> SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> @.@.identity's value if it does another insert.
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> SCOPE_IDENTITY as 'newid' from "&tab "
> Set rs = nrconn.Execute(cmd)
> 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/
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> >I have just inherited some code from another company which uses SQL Server.
> > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > inserted -
> > so that more info can be added to the record. kind of a silly
> > implementation
> > - but it uses a bunch of generic code to insert a new row and generate a
> > GUID, etc.. and it would be significant work to rewrite the entire
> > application.
> >
> > The problem is the even though the table has an id column that is defined
> > with IDENTITY - and through queries I can easily see that each row added
> > has
> > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > scripting
> > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > up
> > using a system DSN with the SQL Native Client (2005) driver.
> >
> > here is the code:
> >
> > Function NewRow(con,tab,col,val)
> > set nrconn = Server.CreateObject("ADODB.Connection")
> > nrconn.open SiteConnectionString
> > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > Set rs = nrconn.Execute(cmd)
> > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > Set rs = nrconn.Execute(cmd)
> > set NewRow = rs
> >
> > End Function
> >
> > After returning the code uses NewRow("newid") to fetch the record and
> > update
> > it. of course, it returns NULL and no record is retrieved... Any help
> > would be much appreciated.
> >
> > --
> > -Steve-o
>
>|||solved the "Fields" problem - I had erroneously removed the "set" fro the rs
= stmt (WHOOPS !). So now the type is returned as Recordset, however,
rs.Fields.Count is 0 ... so still nothing is being returned...
--
-Steve-o
"steve-o" wrote:
> Hi
> Strange ... the code works on older SQL server version. Nonetheless I
> changed as you suggested and same result. I did a little more digging and
> the execute line is return rs as type of 'Fields' instead of RecordSet AND
> the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> help !
> --
> -Steve-o
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > You are submitting it as 2 batches, so it is not available to the 2nd one.
> >
> > Any reason why you are not using stored procedures to do this? Dynamic SQL
> > is asking for security problems.
> >
> > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> > @.@.identity's value if it does another insert.
> >
> > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> > SCOPE_IDENTITY as 'newid' from "&tab "
> > Set rs = nrconn.Execute(cmd)
> >
> > 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/
> >
> > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> > >I have just inherited some code from another company which uses SQL Server.
> > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > > inserted -
> > > so that more info can be added to the record. kind of a silly
> > > implementation
> > > - but it uses a bunch of generic code to insert a new row and generate a
> > > GUID, etc.. and it would be significant work to rewrite the entire
> > > application.
> > >
> > > The problem is the even though the table has an id column that is defined
> > > with IDENTITY - and through queries I can easily see that each row added
> > > has
> > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > > scripting
> > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > > up
> > > using a system DSN with the SQL Native Client (2005) driver.
> > >
> > > here is the code:
> > >
> > > Function NewRow(con,tab,col,val)
> > > set nrconn = Server.CreateObject("ADODB.Connection")
> > > nrconn.open SiteConnectionString
> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > > Set rs = nrconn.Execute(cmd)
> > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > > Set rs = nrconn.Execute(cmd)
> > > set NewRow = rs
> > >
> > > End Function
> > >
> > > After returning the code uses NewRow("newid") to fetch the record and
> > > update
> > > it. of course, it returns NULL and no record is retrieved... Any help
> > > would be much appreciated.
> > >
> > > --
> > > -Steve-o
> >
> >
> >|||more info ...
If I follow the original sequence of insert followed by select query I do
get a recordset back with a field count of 1. the name of the item is indeed
'newid' but the value is NULL.
strangely enough when using SQLCMD and doing this sequence of operations by
hand - SQLCMD outputs one line for each record in the table with the newid
field value for each being the "latest" value (i.e. value of 23 if the last
inserted record had and identity value of 23). so it seems like it is
working there, but not in the scripted app...
-Steve-o
"steve-o" wrote:
> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> rs.Fields.Count is 0 ... so still nothing is being returned...
> --
> -Steve-o
>
> "steve-o" wrote:
> > Hi
> >
> > Strange ... the code works on older SQL server version. Nonetheless I
> > changed as you suggested and same result. I did a little more digging and
> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> > help !
> >
> > --
> > -Steve-o
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
> > >
> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
> > > is asking for security problems.
> > >
> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> > > @.@.identity's value if it does another insert.
> > >
> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> > > SCOPE_IDENTITY as 'newid' from "&tab "
> > > Set rs = nrconn.Execute(cmd)
> > >
> > > 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/
> > >
> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> > > >I have just inherited some code from another company which uses SQL Server.
> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > > > inserted -
> > > > so that more info can be added to the record. kind of a silly
> > > > implementation
> > > > - but it uses a bunch of generic code to insert a new row and generate a
> > > > GUID, etc.. and it would be significant work to rewrite the entire
> > > > application.
> > > >
> > > > The problem is the even though the table has an id column that is defined
> > > > with IDENTITY - and through queries I can easily see that each row added
> > > > has
> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > > > scripting
> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > > > up
> > > > using a system DSN with the SQL Native Client (2005) driver.
> > > >
> > > > here is the code:
> > > >
> > > > Function NewRow(con,tab,col,val)
> > > > set nrconn = Server.CreateObject("ADODB.Connection")
> > > > nrconn.open SiteConnectionString
> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > > > Set rs = nrconn.Execute(cmd)
> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > > > Set rs = nrconn.Execute(cmd)
> > > > set NewRow = rs
> > > >
> > > > End Function
> > > >
> > > > After returning the code uses NewRow("newid") to fetch the record and
> > > > update
> > > > it. of course, it returns NULL and no record is retrieved... Any help
> > > > would be much appreciated.
> > > >
> > > > --
> > > > -Steve-o
> > >
> > >
> > >|||Try adding SET NOCOUNT ON before the INSERT statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
> more info ...
> If I follow the original sequence of insert followed by select query I do
> get a recordset back with a field count of 1. the name of the item is indeed
> 'newid' but the value is NULL.
> strangely enough when using SQLCMD and doing this sequence of operations by
> hand - SQLCMD outputs one line for each record in the table with the newid
> field value for each being the "latest" value (i.e. value of 23 if the last
> inserted record had and identity value of 23). so it seems like it is
> working there, but not in the scripted app...
>
> --
> -Steve-o
>
> "steve-o" wrote:
>> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
>> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
>> rs.Fields.Count is 0 ... so still nothing is being returned...
>> --
>> -Steve-o
>>
>> "steve-o" wrote:
>> > Hi
>> >
>> > Strange ... the code works on older SQL server version. Nonetheless I
>> > changed as you suggested and same result. I did a little more digging and
>> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
>> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
>> > help !
>> >
>> > --
>> > -Steve-o
>> >
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> > > Hi
>> > >
>> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
>> > >
>> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
>> > > is asking for security problems.
>> > >
>> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
>> > > @.@.identity's value if it does another insert.
>> > >
>> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
>> > > SCOPE_IDENTITY as 'newid' from "&tab "
>> > > Set rs = nrconn.Execute(cmd)
>> > >
>> > > 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/
>> > >
>> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
>> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>> > > >I have just inherited some code from another company which uses SQL Server.
>> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
>> > > > inserted -
>> > > > so that more info can be added to the record. kind of a silly
>> > > > implementation
>> > > > - but it uses a bunch of generic code to insert a new row and generate a
>> > > > GUID, etc.. and it would be significant work to rewrite the entire
>> > > > application.
>> > > >
>> > > > The problem is the even though the table has an id column that is defined
>> > > > with IDENTITY - and through queries I can easily see that each row added
>> > > > has
>> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
>> > > > scripting
>> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
>> > > > up
>> > > > using a system DSN with the SQL Native Client (2005) driver.
>> > > >
>> > > > here is the code:
>> > > >
>> > > > Function NewRow(con,tab,col,val)
>> > > > set nrconn = Server.CreateObject("ADODB.Connection")
>> > > > nrconn.open SiteConnectionString
>> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
>> > > > Set rs = nrconn.Execute(cmd)
>> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
>> > > > Set rs = nrconn.Execute(cmd)
>> > > > set NewRow = rs
>> > > >
>> > > > End Function
>> > > >
>> > > > After returning the code uses NewRow("newid") to fetch the record and
>> > > > update
>> > > > it. of course, it returns NULL and no record is retrieved... Any help
>> > > > would be much appreciated.
>> > > >
>> > > > --
>> > > > -Steve-o
>> > >
>> > >
>> > >|||YAY !!! Mike gave me the first half of the solution and Tibor the second
half. Thanks !!!
--
-Steve-o
"Tibor Karaszi" wrote:
> Try adding SET NOCOUNT ON before the INSERT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
> > more info ...
> > If I follow the original sequence of insert followed by select query I do
> > get a recordset back with a field count of 1. the name of the item is indeed
> > 'newid' but the value is NULL.
> >
> > strangely enough when using SQLCMD and doing this sequence of operations by
> > hand - SQLCMD outputs one line for each record in the table with the newid
> > field value for each being the "latest" value (i.e. value of 23 if the last
> > inserted record had and identity value of 23). so it seems like it is
> > working there, but not in the scripted app...
> >
> >
> >
> > --
> > -Steve-o
> >
> >
> > "steve-o" wrote:
> >
> >> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> >> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> >> rs.Fields.Count is 0 ... so still nothing is being returned...
> >> --
> >> -Steve-o
> >>
> >>
> >> "steve-o" wrote:
> >>
> >> > Hi
> >> >
> >> > Strange ... the code works on older SQL server version. Nonetheless I
> >> > changed as you suggested and same result. I did a little more digging and
> >> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
> >> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> >> > help !
> >> >
> >> > --
> >> > -Steve-o
> >> >
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> > > Hi
> >> > >
> >> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
> >> > >
> >> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
> >> > > is asking for security problems.
> >> > >
> >> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> >> > > @.@.identity's value if it does another insert.
> >> > >
> >> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> >> > > SCOPE_IDENTITY as 'newid' from "&tab "
> >> > > Set rs = nrconn.Execute(cmd)
> >> > >
> >> > > 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/
> >> > >
> >> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> >> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> >> > > >I have just inherited some code from another company which uses SQL Server.
> >> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> >> > > > inserted -
> >> > > > so that more info can be added to the record. kind of a silly
> >> > > > implementation
> >> > > > - but it uses a bunch of generic code to insert a new row and generate a
> >> > > > GUID, etc.. and it would be significant work to rewrite the entire
> >> > > > application.
> >> > > >
> >> > > > The problem is the even though the table has an id column that is defined
> >> > > > with IDENTITY - and through queries I can easily see that each row added
> >> > > > has
> >> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> >> > > > scripting
> >> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> >> > > > up
> >> > > > using a system DSN with the SQL Native Client (2005) driver.
> >> > > >
> >> > > > here is the code:
> >> > > >
> >> > > > Function NewRow(con,tab,col,val)
> >> > > > set nrconn = Server.CreateObject("ADODB.Connection")
> >> > > > nrconn.open SiteConnectionString
> >> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> >> > > > Set rs = nrconn.Execute(cmd)
> >> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> >> > > > Set rs = nrconn.Execute(cmd)
> >> > > > set NewRow = rs
> >> > > >
> >> > > > End Function
> >> > > >
> >> > > > After returning the code uses NewRow("newid") to fetch the record and
> >> > > > update
> >> > > > it. of course, it returns NULL and no record is retrieved... Any help
> >> > > > would be much appreciated.
> >> > > >
> >> > > > --
> >> > > > -Steve-o
> >> > >
> >> > >
> >> > >
>