Script to log/record the users who accessed my database

Hi,

I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.

Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.

Also, are there any things that I should take care with my database from outside users.

Thanks
-SudhakarThe only way I can think of to log user access (especially read access) is to constantly run a Profiler trace. This will very likely not be possible on a third party server, even if you have admin access to the server.
A lesser option is to have the third party turn on login successful auditing, which will put an entry in the errorlog every time someone logs into the server. This will not tell you what database they log into, nor will it tell you what they did there.sql

script to create replication

Is it possible to create distribution, replication... from script? I would
like to do this on a new sql server. Is there a macro to record the step I
do and I can bring that to production server and run it? Thanks.
The easiest method is to create the distributor and publications using EM
then get EM to script it out. Edit the resulting script with the new
computername and run it there.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK. I know how to create distributor and publications using EM but I don't
know how to use EM to script it out. Please advice. Thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23EmyenJFHA.3336@.TK2MSFTNGP10.phx.gbl...
> The easiest method is to create the distributor and publications using EM
> then get EM to script it out. Edit the resulting script with the new
> computername and run it there.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You can right-click the replication folder (or an individual publication)
and select 'generate sql script'.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Script Source: Error on truncation etc

I have a script source to deal with a source that has different "record types" (first 3 columns are the same then the remaining 2 to 30 columns are different based on the record type).

Script source was working fine... then one of the columns that I had set to String with length of 2 came in with a length of 3 (which is not per spec)... instead of failing - all the columns after the one that had the bad value were null and the script just stopped as soon as it hit that.. AND said it was success. Which means it imported the data incorrectly and since the script says it was a sucess you'd never know anything went wrong and it only imported 30 rows instead of 10k+

Any ideas on how to capture this error?

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports System.Convert

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim oCurrentFile As File

Dim oStreamReader As StreamReader

Dim sCurrentLine As String

Dim aCurrentLine() As String

Try

oStreamReader = oCurrentFile.OpenText(Me.Variables.SourceName)

sCurrentLine = oStreamReader.ReadLine()

Do While sCurrentLine IsNot Nothing

aCurrentLine = sCurrentLine.Split(Chr(44))

Select Case aCurrentLine(2) ' This is the 3rd column.. "Record Type" which tells us what type of record it is and how many columns etc (Chr(44) is a comma)

Case "BF"

BFRecordsBuffer.AddRow()

BFRecordsBuffer.TimeStamp = aCurrentLine(0)

BFRecordsBuffer.EyeCatcher = aCurrentLine(1)

BFRecordsBuffer.RecordType = aCurrentLine(2)

BFRecordsBuffer.Sym = aCurrentLine(3)

....

BFRecordsBuffer.RecordCount = Convert.ToInt32(aCurrentLine(24))

...

If aCurrentLine.GetLength(0) >= 30 Then

BFRecordsBuffer.SeqNo = aCurrentLine(29)

End If

Case "QF"

QFRecordsBuffer.AddRow()

QFRecordsBuffer.Timestamp = aCurrentLine(0)

QFRecordsBuffer.EyeCatcher = aCurrentLine(1)

QFRecordsBuffer.RecordType = aCurrentLine(2)

...

....

End Select

sCurrentLine = oStreamReader.ReadLine()

Loop

BFRecordsBuffer.SetEndOfRowset()

QFRecordsBuffer.SetEndOfRowset()

....

Catch ex As Exception

Me.ComponentMetaData.FireError(1, "Script Component", ex.Message, "", 0, True)

Finally

oStreamReader.Close()

End Try

End Sub

End Class

Chris Honcoop wrote:

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

I can help with that. Go here: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/

Yuo can see an example of the result here:

http://blogs.conchango.com/jamiethomson/archive/2006/12/19/SSIS-Nugget_3A00_-Extract-last-word-from-a-sentance-using-regular-expressions.aspx

-Jamie

|||

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)

Any ideas on the script source truncation/data type mismatch "non error"?

|||

Chris Honcoop wrote:

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)

Any ideas on the script source truncation/data type mismatch "non error"?

It doesn't work in VSA unfortunately so copy and paste it into a regular VB.Net project and copy as html from there.

Not sure about the error you're getting i'm afraid.

-Jamie

|||Its something with the way I do the error trapping.. I comment out the try / catch etc and the script source will fail when the columns are bad. Of course getting a useful error message about which column etc is experiencing the problem is an entirely different headache.|||To have the standard SSIS options of ignore,redirect, and fail

component in your own custom script component, there are a couple of

changes to consider.

1. Add a try/catch inside your loop. As written, when the first error

happens (e.g. "ABC" rather than "AB"), the exception will be caught

outside of the loop, and no rows beyond that will be processed. This

change will allow the implementation of the ignore and/or redirect

options, and of course, continued processing.

2. In the newly added try/catch, add an catch for the

DoesNotFitBufferException. This will "catch" the error. You can also simply ignore this particular

exception type, and the string will be truncated, or re-throw the

exception, and the component will fail, or lastly, redirect it (

essentially adding a row to an "error" output on your component)

3. To report which column name/index is experiencing the problem will

require a cache of column information (names, types,lengths, etc) by

overriding the PreExecute function, and then grabbing a reference to

the the base buffers by overriding the PrimeOutput function. The

column cache and base buffers can then be referenced in your

CreateNewOutputRows implementation.

Hopefully this will help you along your way. If not, I'll find some script component code and post that as well.

scrip help

I wanna to have a script that will read a sertain table in a database. If a csrtain record that is being added exists in the table already I want it not to be added into teh specific tables. If it does not exist, i want it to be added.

Here is the logic that I wanna have:

READ OPCSHTO
GET CUST_CODE + LOC_CODE
IF EXIST CUST_CODE + LOC_CODE RECORD IN DPTORGANIZATIONSLOCATIONS
GO TO READ OPCSHTO

ELSE
DO ADD TO DPT.ORGANIZATIONLOCATIONS TABLE
DO ADD TO DPTORGANIZATIONSyou can have a stored procedure with CUST_CODE & LOC_CODE as input parameters. It will first check the table for this record by doing a count(*). If this count is zero it will insert the new record.|||Originally posted by rohitkumar
you can have a stored procedure with CUST_CODE & LOC_CODE as input parameters. It will first check the table for this record by doing a count(*). If this count is zero it will insert the new record.

That sounds cool. Can you be a little more specific cause I really don't know how I would do a sp.

thanks.|||I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

===================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

OPEN cur_OPCSHTO

FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END


FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO
======================================|||Originally posted by rohitkumar
I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

===================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

OPEN cur_OPCSHTO

FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END


FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO
======================================

Thanks a bunch...I will see how it turns out. How long have you been doing this for? Thanks for understanding......pretty new at this.|||Originally posted by rohitkumar
I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

===================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

OPEN cur_OPCSHTO

FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END


FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO
======================================

You think you would be able to right a descripting by each command, thatway I could understand what is going on and I can understand it better?
thanks for your help.|||I've tried writing a short description, let me know if something is missing

=========================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
/* variable declaration */
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

/* this will fetch CUST_CODE , LOC_CODE from cur_OPCSHTO and store it in an cursor "cur_OPCSHTO" (sort of an array) */
DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

/* open this cursor for fetching the data */
OPEN cur_OPCSHTO

/* fetch first of the stored values and put them in these variables */
FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

/* repeat the process till last record in the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN

/* count the number of recs in DPTORGANIZATIONSLOCATIONS having CUST_CODE , LOC_CODE. If this count is zero then insert this as a new record in the DPTORGANIZATIONSLOCATIONS table*/
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END

/* fetch next of the stored values in the cursor and put them into variables*/
FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END /* END corresponding to WHILE, the process between WHILE and END will repeat till there are no more records in the cursor*/

/* close the cursor and deallocate the resources*/
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO

SCOPE_IDENTITY() returning the ID value of an inserted record.

There are loads of postings on the net about this problem but none I have found explain the cause.

Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.

Has anyone come across the solution for this issue?

Thanks

Can you provide your code?|||

Here is the Stored Proc

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[InsertNewEnquiry_Client]

(

@.Salutation

varchar(10),

@.Name

varchar(60),

@.Address1

varchar(60),

@.Address2

varchar(60),

@.Address3

varchar(60),

@.Town

nvarchar(50),

@.PostCode

char(10),

@.County

char(60),

@.Telephone1

char(12),

@.Telephone2

char(12),

@.email

char(30)

)

AS

SETNOCOUNTOFF;

INSERT

INTO tblClient(Salutation,Name, Address1, Address2, Address3, Town, PostCode, County, Telephone1, Telephone2, email)

VALUES

(@.Salutation,@.Name,@.Address1,@.Address2,@.Address3,@.Town,@.PostCode,@.County,@.Telephone1,@.Telephone2,@.email);

SELECT

ClientID, Salutation,Name, Address1, Address2, Address3, PostCode, County, Telephone1, Telephone2, Telephone3, emailFROM tblClientWHERE(ClientID=SCOPE_IDENTITY())|||And can you show us the code which calls this stored procedure?|||int ClientID = Convert.ToInt32(clientTableAdapter.InsertStoredProc(cboClientSalutation.SelectedItem, txtClientName,text ......) );|||

The 1 your getting is most likely the default result of an Insert which is to return the number of records that have been entered.

I think you need to set the ExecuteMode property on your function to scalar.

|||

Actually, I've just looked at your sql again and your selecting an entire record at the end, not the identity on its own.

Simply put SELECT SCOPE_IDENTITY() if you want just the id returned, plus make sure you set the executemode on the function to scalar as I said before.

Hope that helps.

SCOPE_IDENTITY()

Trying to insert record in Parent table and return value of that rows IDENTITY, to pass back to app for use inserting child rows into other table. Looks clean against tutorial samples(http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/050207-1.aspx). Works with Insert until *** lines are added - HELP?

ERROR:

Msg 201, Level 16, State 4, Procedure qc_submitInsertQCparentReturningID_2, Line 0

Procedure or function 'qc_submitInsertQCparentReturningID_2' expects parameter '@.newQCparent_ID', which was not supplied.

Based on this Procedure:

ALTER PROCEDURE qc_submitInsertQCparentReturningID_2

(@.newQCparent_ID INT OUTPUT) ***

AS

-- Insert New QCparent RETURNING qcParentID

INSERT INTO app.dbo.a1_qcParent

([rowCreatedBy]

,[rowNotes]

,[rowLastAction]

,[rowLastActionBy]

,[rowLastActionNote])

-- Using Parameter Variables

VALUES('Anonymous Submit'

,'By qc_submitInsertQCparentReturningID'

,'Insert'

,'Guest'

,'show donald')

-- Read the qcParient_ID back for Items2fix Insert

SET @.newQCparent_ID = SCOPE_IDENTITY() ***

- Try New SPROC

EXEC qc_submitInsertQCparentReturningID_2

Thanks, unfortunately neither worked...

snippet 1 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.newQCparent_ID):

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@.newQCparent_ID".

snippet 2 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=scope_identity())

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

|||

Sorry, I got it wrong; Leave your original procedure code as it was. Change your call of the stored procedure from

Code Snippet

EXEC qc_submitInsertQCparentReturningID_2

to

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

and if you wish to see the results something like:

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

select @.outputParm as [@.outputParm]

|||

Parameters are by default NULLable in SQL Server. But in case of OUTPUT parameters, you will have to always pass a value. So if you want to ignore the output value you can do:

exec qc_submitInsertQCparentReturningID_2 NULL;

If you need to retrieve the output value then do:

declare @.id int;

exec qc_submitInsertQCparentReturningID_2 @.id OUTPUT;

|||

THat allowed it to run, insert was successful, but the following resulted in 9 as scope_Identity but 14 rows listed:

- Try New SPROC

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

a) I was expecting to see the same scope as the last inserted rows ID (14) ?

and

b) can I use the @.id to populate another procedure insert ?

|||Removed the GO between statements, but Scope_Identity remained 9 while rows increased|||

In the statement:

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

The SCOPE_IDENTITY() function will not work as you are thinking -- because the IDENTITY column is assigned inside of the stored procedure and what happened there is NOT in the scope of the CALLING query. As I said above, you will need to do something like

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

Select @.id

Select * from a1_qcParent

SCOPE_IDENTITY( ) in distributed transaction [:S]

i am inserting new record in linked server and i need to get the id (which is of course autonumber) of newly added record. can't i get it using SCOPE_IDENTITY( ) ? SCOPE_IDENTITY( ) seems to be returning null. so SCOPE_IDENTITY( ) doesn't work in distributed transaction?

Hi Keeara,

As far as I know, the SCOPE_IDENTITY() can return the identity in a distributed transaction if the column in the table has been set as an identity.

The column has to be a number or the identity will not be returned.

|||

apparently, it returns NULL. i tried adding record in TEST table in distributed transaction and got NULL.

is there any way to get recently added identity in distributed transaction?

any help will be appreciated.

scope_identity Vs Parameters

Sorry for double posting but I screwed the last one up. The following code
successfully inserts a record in the Sections table, but the
scope_identity() returns DbNull. If I remove the parameter 'pSectionName'
and replace it with dummy value, it works fine. Alternatively, if I use
@.@.IDENTITY with or withour the parameter, that works fine too.
It seems there is a problem with using scope_identity() when parameters are
involved, but I do need to use parameters. Does anyone know why this would
happen and how to circumvent it ?
Dim sqlConnection As New SqlConnection(getConnectionString())
Dim sqlString As String
Dim result As Integer
Dim pSectionName As New SqlParameter("@.pSectionName",
SqlDbType.NVarChar)
pSectionName.Value = sectionRow.SectionName
sqlString = "INSERT INTO SECTIONS " & _
"VALUES (" & _
" '" & sectionRow.ArticleID.ToString & "'," & _
" @.pSectionName ," & _
" '" & sectionRow.SectionNumber.ToString & "'," & _
" '" & sectionRow.SectionFollowing.ToString & "'," & _
" '" & sectionRow.Attachments.ToString & "'," & _
" '" & sectionRow._Text & "'," & _
" ''," & _
" '" & sectionRow.pictureName & "'," & _
" '" & sectionRow.pictureType & "'," & _
" '" & sectionRow.pictureFilePath & "'," & _
" '" & sectionRow.SectionType & "');"
Dim sqlIDQuery As String
sqlIDQuery = "SELECT scope_identity();"
Dim sqlCommand As New SqlCommand(sqlString)
sqlCommand.Connection = sqlConnection
'Add Parameters
sqlCommand.Parameters.Add(pSectionName)
Dim SectionID As Integer
Try
sqlConnection.Open()
sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
INSERTS RECORD.
sqlCommand.CommandText = sqlIDQuery
SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
FAILS HERE WITH AN EXCEPTION.
Catch ex As Exception
SectionID = 0
Finally
sqlConnection.Close()
End Try
Return SectionID
Best Regards
The Inimitable Mr NewbieHave you considered using a relational design, which will never have
IDENTITY in its tables? These exposed physical locators have nothign to
do with RDBMS or a valid logical model.
Have you considered using a stored procedure inside the database instead
of building a query on the fly at run time? Why do you think that a row
and record are anything alike?
A spec that tells us what you are trying to do, long with some DDL would
be very helpful. Do you program from things this vague and imcomplete?
Us, neither.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||If you have nothing useful to say, just save it for someone who might care
that you try and make yourself feel good at the expense of others.
Especially those who are new like me.
Best Regards
The Inimitable Mr Newbie
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||I see you are reusing the same SqlCommand object but I don't see where you
are clearing the parameters collection from the first statement.
Consequently, the command will still contain the unneeded @.pSectionName
parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
sqlCommand.Parameters.Clear()
sqlCommand.CommandText = sqlIDQuery
Also, consider parameterizing the entire INSERT statement or executing a
proc passing parameters. Parameterized values are more secure and avoid the
need to worry about things like embedded quotes in strings and date formats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
>
> Dim sqlConnection As New SqlConnection(getConnectionString())
> Dim sqlString As String
> Dim result As Integer
> Dim pSectionName As New SqlParameter("@.pSectionName",
> SqlDbType.NVarChar)
> pSectionName.Value = sectionRow.SectionName
> sqlString = "INSERT INTO SECTIONS " & _
> "VALUES (" & _
> " '" & sectionRow.ArticleID.ToString & "'," & _
> " @.pSectionName ," & _
> " '" & sectionRow.SectionNumber.ToString & "'," & _
> " '" & sectionRow.SectionFollowing.ToString & "'," & _
> " '" & sectionRow.Attachments.ToString & "'," & _
> " '" & sectionRow._Text & "'," & _
> " ''," & _
> " '" & sectionRow.pictureName & "'," & _
> " '" & sectionRow.pictureType & "'," & _
> " '" & sectionRow.pictureFilePath & "'," & _
> " '" & sectionRow.SectionType & "');"
> Dim sqlIDQuery As String
> sqlIDQuery = "SELECT scope_identity();"
> Dim sqlCommand As New SqlCommand(sqlString)
> sqlCommand.Connection = sqlConnection
> 'Add Parameters
> sqlCommand.Parameters.Add(pSectionName)
> Dim SectionID As Integer
> Try
> sqlConnection.Open()
> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
> INSERTS RECORD.
> sqlCommand.CommandText = sqlIDQuery
> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
> FAILS HERE WITH AN EXCEPTION.
> Catch ex As Exception
> SectionID = 0
> Finally
> sqlConnection.Close()
> End Try
> Return SectionID
>
> --
> Best Regards
> The Inimitable Mr Newbie
>|||Thanks for your reply.
I tried clearing the Params, but that had no effect. so I created an
entirely new sqlCommandID object which used the same connection as the other
one for the scope_identity() but this had no effect.
I know I should parameterise the other parts of the sql query, but they dont
really need it because the contain no user input. Only the section title
carries this on creation.
Any other ideas. This is really bugging me.
Best Regards
The Inimitable Mr Newbie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OyzuingGGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I see you are reusing the same SqlCommand object but I don't see where you
>are clearing the parameters collection from the first statement.
>Consequently, the command will still contain the unneeded @.pSectionName
>parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
> sqlCommand.Parameters.Clear()
> sqlCommand.CommandText = sqlIDQuery
> Also, consider parameterizing the entire INSERT statement or executing a
> proc passing parameters. Parameterized values are more secure and avoid
> the need to worry about things like embedded quotes in strings and date
> formats.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mr Newbie" <here@.now.com> wrote in message
> news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
Have you consider going one step further than the 'logical model' and create
an implementation or do you just deal with theory?
The IDENTITY property is a very useful way to create surrogate keys and gain
good performance and is a solution for the really big problem of when a
primary key value changes.

> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
How do you think report builders work? Do you have a stored procedure with
1000 parameters and 1000 if else.
Actually, let me bring you up on this point, you posted a w or two ago
that you shouldn't use IF ELSE in the database.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You need to run it as a single statement, it would be better if you used a
stored procedure if possible.
Anyway, the problem lies in that SCOPE_IDENTITY() is only for the
connection, your connection will have been reset between calls.
You can concatenate sqlString and sqlIDQuery just put a semi column ; after
the first statement.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
>
> Dim sqlConnection As New SqlConnection(getConnectionString())
> Dim sqlString As String
> Dim result As Integer
> Dim pSectionName As New SqlParameter("@.pSectionName",
> SqlDbType.NVarChar)
> pSectionName.Value = sectionRow.SectionName
> sqlString = "INSERT INTO SECTIONS " & _
> "VALUES (" & _
> " '" & sectionRow.ArticleID.ToString & "'," & _
> " @.pSectionName ," & _
> " '" & sectionRow.SectionNumber.ToString & "'," & _
> " '" & sectionRow.SectionFollowing.ToString & "'," & _
> " '" & sectionRow.Attachments.ToString & "'," & _
> " '" & sectionRow._Text & "'," & _
> " ''," & _
> " '" & sectionRow.pictureName & "'," & _
> " '" & sectionRow.pictureType & "'," & _
> " '" & sectionRow.pictureFilePath & "'," & _
> " '" & sectionRow.SectionType & "');"
> Dim sqlIDQuery As String
> sqlIDQuery = "SELECT scope_identity();"
> Dim sqlCommand As New SqlCommand(sqlString)
> sqlCommand.Connection = sqlConnection
> 'Add Parameters
> sqlCommand.Parameters.Add(pSectionName)
> Dim SectionID As Integer
> Try
> sqlConnection.Open()
> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
> INSERTS RECORD.
> sqlCommand.CommandText = sqlIDQuery
> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
> FAILS HERE WITH AN EXCEPTION.
> Catch ex As Exception
> SectionID = 0
> Finally
> sqlConnection.Close()
> End Try
> Return SectionID
>
> --
> Best Regards
> The Inimitable Mr Newbie
>|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
>
No, there is no spec needed. Instead of preteding like you are a bad
AI program let loose, learn how ADO .Net works and you will be answer
the question without any further spec.
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|||Mr Newbie (here@.now.com) writes:
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
scope_idenity() returns the most recently generated identity value
in the current scope. Scope here is a stored procedure, or the top-
level scope. @.@.identity, on the other hand, returns the most recently
generated identity value for the connection, independent on scope.
When you don't use parameters, the INSERT command is submitted as-is,
and thus in the same scope as you later fetch scope_identity().
But when you use parameters, SqlClient submits the command through
sp_executesql. This is because SqlClient does not build the expanded
command string, but instead passes the parameters in an RPC call.
This is usually good for performance. The side effect is that the
INSERT statement no longer is in the top-level scope, and thus you
can get the identity value with scope_identity().
There are two possible ways to do:
o Use @.@.identity. This is fine as long as the table you are inserting
to does not have a trigger which in its turn insert into a table
with an identity column. In this case, @.@.identity will report the
value generated for that table. (It is to avoid this trap, that
scope_identity() was introduced.)
o As Tony suggested, add the SELECT on scope_identity() to the
batch with the INSERT statement.
The latter has the advantage of saving you a network roundtrip, which
is usally good for performance.
In fact, I would like to take you even a step further and do this:
sqlString = "INSERT INTO SECTIONS " & _
"(ArticleID, SectionName, SectionNumber, ... ) " & _
"VALUES (@.ArticleID, @.pSectionName, @.SectionNumber, ...) " & _
"SELECT @.id = scope_identity()"
1) Include the columns you are inserting into. If you leave out the column
list, and the table is later changed, you query will blow up.
2) Pass all values as parameters, don't embed values in the SQL String
(constants are OK). This protects you against SQL injection, and
other problems that could occur if the value includes an '. It also
saves you from hassle when using datetime values.
3) Make the value from scope_identity() an output parameter from
the batch. For this you need to specify the direction as InputOuput.
(SQL Server does not have any output-only parameters.)
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|||>> If you have nothing useful to say, just save it for someone who might car
e that you try and make yourself feel good at the expense of others. Especia
lly those who are new like me. <<
So when I tell you think about stored procedures, it is a bad thing.
But when other people mention stored procedures as an answer, it is
good thing. Interesting.

scope_identity question

I have a app that is inserting data into a SQL 2005 database and I would like to return the UniqueID of the inserted record.

I am using

Dim queryStringAsString ="INSERT INTO dbo.DATATABLE (FIELD) VALUES (@.FIELD);SELECT Scope_Identity()"

Dim sIDAsString = comSQL.ExecuteScalar()

This isn't working - it says the value returned is DBNull...

Any ideas on how to make this work?

You can convert the adhoc SQL to a stored proc and use a proper OUTPUT parameter and retrieve the ID. Also, is that your entire code? Where are you opening the connection? where is it define/initialized etc?

|||

hi mate,

The syntax used for retriving identity value is worn...It will be as follows

SELECT @.@.SCOPE_IDENTITY

Otherwise you can use

SELECT @.@.Identity

Hope now i can see ur smile

Thanx

Vijay

|||

Your use of scope_identity() is correct. Perhaps your insert is failing. Can you make sure that new row are actually being added to the table?

|||

elegantkvc:

The syntax used for retriving identity value is worn...It will be as follows

SELECT @.@.SCOPE_IDENTITY

This is incorrect. The correct way to use it is SCOPE_IDENTITY().

SCOPE_IDENTITY Problem I dont know how to use it.

Hi,

I am using following code to insert some record in to database and after that i want the id of new added record , so what kind of change i have to with my code plz anyone can do some change my code as it return id by using SCOPE_IDENTITY in my code

--------------------
Dim strconn As String = "server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx"
'Create a connection
Dim MyConn_member As New SqlConnection(strconn)
MyConn_member.Open()

'Start the transaction
Dim myTrans As SqlTransaction = MyConn_member.BeginTransaction()

Try
'Specify the first statement to run...
Dim MySQL_member As String = "Insert Into article ([articleCategoryId],[articleTitle],[articleDescription],[articleContent],[articlePostBy],[articleStatus],[addDate],[lastUpdate]) Values (@.category_id, @.article_title, @.article_description,@.article_content,@.article_postby,@.article_status,@.add_date, @.last_update)"

'Create the SqlCommand object, specifying the transaction through
Dim cmd_member As New SqlCommand(MySQL_member, MyConn_member, myTrans)
cmd_member.Parameters.Add(New SqlParameter("@.category_id", article_category.SelectedValue))
cmd_member.Parameters.Add(New SqlParameter("@.article_title", article_title.Text))
cmd_member.Parameters.Add(New SqlParameter("@.article_description", article_description.Text))
cmd_member.Parameters.Add(New SqlParameter("@.article_content", article_content.Text))
cmd_member.Parameters.Add(New SqlParameter("@.article_postby", post_by))
cmd_member.Parameters.Add(New SqlParameter("@.article_status", article_status))
cmd_member.Parameters.Add(New SqlParameter("@.add_date", last_update))
cmd_member.Parameters.Add(New SqlParameter("@.last_update", last_update))

cmd_member.ExecuteNonQuery()
myTrans.Commit()

Catch ex As Exception
'Something went wrong, so rollback the transaction
myTrans.Rollback()
MyConn_member.Close()
Throw 'Bubble up the exception
Finally
'Finally, close the connection
MyConn_member.Close()
End Try

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

PLease help me
Thanks in advance

Create an additional parameter, set the parameter direction to Output.

Append this to the end of your SQL statement:

SELECT <Your Parameter Name> = SCOPE_IDENTITY;

Then read the vale of the output parameter.

|||Sorry , But I really don't understand where and how to append it

Can you please do for me...

Very Very Thanks in Advance|||

Kunal Mehta:

Sorry , But I really don't understand where and how to append it

Can you please do for me...

We're not here to do your work for you. The previous poster has demonstrated what you need to do. If you have a question that is more specific to an issue you are having vs. please do my work for me, then feel free to resubmit your question.

|||

I would be very happy to do it for you. Please send me a contract for employment. I will charge you $200.00 US per hour, minimum of 1 hour.

Maybe you could send me your client's contact information and I will do the whole project for you?

SCOPE_IDENTITY plzzzzz help me

this is my code, i really want a basic easy way without using procedures to do a simple task to response.write(last record added) using scope_identity...
asp.net/VB
===================================================================================
Sub click_addnew(sender as object, e as system.eventargs)

Dim oDR as System.Data.SQLClient.SQLDataReader
Dim oCom As System.Data.SQLClient.SqlCommand
Dim oConn as System.Data.SQLClient.SQLConnection
Dim recnumber as integer
try
oConn = New System.Data.SQLClient.SQLConnection ("server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx")
oConn.Open()
oCom = New System.Data.SQLClient.SqlCommand()
oCom.Connection = oConn

oCom.CommandText = "INSERT INTO hooliganproducts (hooligantitle, hooliganprice, hooligandescription, hooliganpartno, hooligancata) VALUES ('test' , '100' , 'test' , 'test' , 'test') select scope_identity"
oDR = oCom.ExecuteReader()
response.Write(oDR) 'THIS IS WHERE I WANT TO DISPLAY THE NUMBER OF THE LAST RECORD

catch
Response.Write("Error:" & err.Description)
Finally
oDR = Nothing
oCom = Nothing
oConn.Close()
oConn = Nothing
end try

End Sub
=============================================================================

Please can someone help, feel like i have been banging my head up a brick wall all day, have spent the whole day trying to find an answer.
Thank you in advance
Darren

Multiple SQL commands in a batch should be separated by semicolons (;). Try putting a semicolon between theINSERT statement and theselect scope_identity statement.
|||(1) You need brackets after SCOPE_IDENTITY as in SCOPE_IDENTITY().
(2) You might want to use ExecuteScalar since you are only getting backone value. So you can also avoid the overhead of creating a datareader.
So your code would look something like :
oConn = New System.Data.SQLClient.SQLConnection ("server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx")
'oConn.Open() - OPEN ONLY WHEN YOU NEED AND CLOSE IMMEDIATELY.
oCom = New System.Data.SQLClient.SqlCommand()
oCom.Connection = oConn
oCom.CommandText= "INSERT INTO hooliganproducts (hooligantitle, hooliganprice,hooligandescription, hooliganpartno, hooligancata) VALUES ('test' ,'100' , 'test' , 'test' , 'test') select SCOPE_IDENTITY()"
Dim newId as Integer
try
oConn.Open()
newId = oCom.ExecuteScalar()
response.Write(newId) 'THIS IS WHERE I WANT TO DISPLAY THE NUMBER OF THE LAST RECORD

catch
Response.Write("Error:" & err.Description)
Finally
oCom = Nothing
oConn.Close()
oConn = Nothing
end try
|||Thank you both. feel much better now, got it working.