retrieve Datetime: update

Showing posts with label update. Show all posts

Friday, March 30, 2012

return an id while doing an insert\update to a table

Hi people,

i Have a small issue. I need to be able to retrive an id number of a new row to a table using the the insert into command. I was able to do this in sql 2000 but the same sql does not work now in 2005. here is the code

"Set NoCount On; select user_id from users insert into users (username) values('" & CurrentUser & "')"

This used to work in sql2000,

I am woundering if anyone could help me or point me in the right direction for doing this with SQL 2005

Best regards

RBowden

Did you try putting a semincolumn between the statements (before the insert) ?

HTH, jens Suessmeyer.

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

I tried putting the ; before the insert function it is still returns 0

any other ideas?

|||

Ah, ok now I know what you mean. You are refering to the OUTPUT clause in SQL Server 2005.

"Set NoCount On; DECLARE @.Somevar VARCHAR(10);insert into users (username) OUTPUT user_id INTO @.SomeVar values('" & CurrentUser & "')"; SELECT @.SomeVar"

Look in the BOL, there should me some examples around that. If you are using an IDENTITY Column for the userid cou can also query the SCOPE_IDENTITY() function for the new identity value.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cheers,

thank you very much for your help, that worked a treat.

keep up the good advice

all the best

Friday, March 23, 2012

retrieving user's permissions for each table

Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1

Wednesday, March 7, 2012

retrieve the primary keys with SQL DMO and vb.net

Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier
Each Table object has a Keys collection. Each Key has a Type property.
Failing that, you can use T-SQL:
select
*
from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
1 in (
objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsClustKey')
, objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsNonclustKey')
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"oLiVieR" <ocheneson@.hotmail.com> wrote in message
news:OMUoOMCyFHA.3556@.TK2MSFTNGP12.phx.gbl...
Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier

Retrieve REDO Information

I need a way to retrieve INSERT, DELETE and UPDATE information from SQL
Server, which needs to include basically a redo statement such as the actual
INSERT and DELETE statement and an UPDATE statement with the new (set
values) and original values. I know SQL Server has log files and there are
third party applications that an retrieve this information, however some of
them have problems getting the correct or even getting any UPDATE
information, plus I do not need a UI or any of their features, just the
information.
I need to get the INSERT, DELETE and UPDATE information, new and old values
using C++ code. These UI applications are of no use. All I need is the
information.
Oracle has Logminer where you can query the log information based on
operation type and timestamp as well as other useful parameters. DB2 can
even send this INERT, DELETE and UPDATE information to a message queue.
I need a way to get this information from SQL Server without using database
triggers but using C++ code. It would be nice to be able to query for this
information, similar to Oracle's implementation. Can anyone point me in the
right direction? Thanks in advance for any help you can provide.
Charles ParkerThe only commands you have to work with are DBCC LOG and fn_dblog. However,
these doesn't return
information in any type of clear text, and there is not information on how t
o decode the information
they return (or even if they contains what you need).
You can talk to MS and ask them for information on how to do this, which wou
ld put you on the same
level as the companies that wrote these applications, but there is no public
ly available API or
command for getting "meaningful" information from the transaction log.
Consider putting a request at http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:Othcy1qcGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I need a way to retrieve INSERT, DELETE and UPDATE information from SQL Ser
ver, which needs to
>include basically a redo statement such as the actual INSERT and DELETE sta
tement and an UPDATE
>statement with the new (set values) and original values. I know SQL Server
has log files and there
>are third party applications that an retrieve this information, however som
e of them have problems
>getting the correct or even getting any UPDATE information, plus I do not n
eed a UI or any of their
>features, just the information.
>
> I need to get the INSERT, DELETE and UPDATE information, new and old value
s using C++ code. These
> UI applications are of no use. All I need is the information.
>
> Oracle has Logminer where you can query the log information based on opera
tion type and timestamp
> as well as other useful parameters. DB2 can even send this INERT, DELETE a
nd UPDATE information to
> a message queue.
>
> I need a way to get this information from SQL Server without using databas
e triggers but using C++
> code. It would be nice to be able to query for this information, similar t
o Oracle's
> implementation. Can anyone point me in the right direction? Thanks in adva
nce for any help you can
> provide.
>
> Charles Parker
>|||Tibor,
Thanks for the quick reply. I will try the feedback link you suggested below
but I do not understand why Microsoft let Oracle and DB2 get ahead of them
in terms of this feature. Could it be in SQL Server 2005?
Charles...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HAfv8qcGHA.536@.TK2MSFTNGP02.phx.gbl...
> The only commands you have to work with are DBCC LOG and fn_dblog.
> However, these doesn't return information in any type of clear text, and
> there is not information on how to decode the information they return (or
> even if they contains what you need).
> You can talk to MS and ask them for information on how to do this, which
> would put you on the same level as the companies that wrote these
> applications, but there is no publicly available API or command for
> getting "meaningful" information from the transaction log.
> Consider putting a request at
> http://lab.msdn.microsoft.com/productfeedback/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Charles Parker" <charles.parker@.whamtect.com> wrote in message
> news:Othcy1qcGHA.3888@.TK2MSFTNGP02.phx.gbl...
>|||> Could it be in SQL Server 2005?
Unfortunately, no. I guess that there haven't been enough customer request t
o warrant spending time
on doing this compared to other feature request MS has on the product. But o
f course, only people
sitting in the product planning meetings can say for sure... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:%23rNuBhscGHA.4312@.TK2MSFTNGP05.phx.gbl...
> Tibor,
> Thanks for the quick reply. I will try the feedback link you suggested bel
ow but I do not
> understand why Microsoft let Oracle and DB2 get ahead of them in terms of
this feature. Could it
> be in SQL Server 2005?
> Charles...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23HAfv8qcGHA.536@.TK2MSFTNGP02.phx.gbl...
>

Retrieve REDO Information

retrieve records affected count from ADO?

Hello,

If I run an action SP from MS Access using ADO:
...
cmd.execute

where the SP is something like Create...
Update tbl1 set fld1 = 'something' where...

how can I retrive the count of records affected like from Query
analyzer?

Thanks,
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rich Protzel (rpng123@.aol.com) writes:
> If I run an action SP from MS Access using ADO:
> ..
> cmd.execute
> where the SP is something like Create...
> Update tbl1 set fld1 = 'something' where...
> how can I retrive the count of records affected like from Query
> analyzer?

The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.

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

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

>>The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.
<<

May I ask how I go about retrieving the Count of records affected back
into MS Access?

Dim CountRecsAffected As Long
...
cmd.Parameters("@.bDate").Value = sDate
cmd.Execute
CountRecsAffected = cmd.?
or
CountRecsAffected = ?
or
CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
the count of parameters being used?

Thanks again for your reply.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Rich Protzel (rpng123@.aol.com) writes:
> May I ask how I go about retrieving the Count of records affected back
> into MS Access?
> Dim CountRecsAffected As Long
> ..
> cmd.Parameters("@.bDate").Value = sDate
> cmd.Execute
> CountRecsAffected = cmd.?
> or
> CountRecsAffected = ?
> or
> CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
> the count of parameters being used?

cmd.Execute CountRecsAffected

Assuming that Access works like Visual Basic.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 3 Aug 2003 16:08:14 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar@.algonet.se>
wrote:

>Assuming that Access works like Visual Basic.

For most things, including ADO, it does.

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)|||Thank you all for your replies. I think I get the idea now about how to
retrieve the count of records affected from an action sp.

One more question if I may:

If I set my sp to

SET NOCOUNT ON

would that improve the performance of my sp? It is not critical for me
to retrieve the count of records affected, mostly just a check. But if
the sp works consistently, and setting

SET NOCOUNT ON

significantly improve performance, then maybe I should consider that.
Most of my action sp's are affecting over 100,000 records of tables with
nearly 200 fields (no redundant fields) with over 1,000,000 records.

Thanks again,

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Retrieve record just updated

I want to add an update trigger to keep a LastDateModified column up to date. I also want to make the trigger as generic as possible so I can cut&paste into others, the column has the same name throughtout the system. How can I retrieve the record I just updated so I can change the date? Also, if my update trigger updates a record, will this cause a loop?

In the context of a TRIGGER, there are two virtual tables, known as inserted and deleted.

To update the [LastDateModified] column, your TRIGGER action would be something like this:

Code Snippet

UPDATE MyTable

SET LastDateModified = getdate()

FROM inserted i

JOIN MyTable m
ON m.PKColumn = i.PKColumn

And no, the TRIGGER does not cause a loop.

|||The trigger "should not" cause a loop, unless recursive triggers are on in the database.|||This works perfectly except for one thing, the "architect" created several table without keys. Now, I get to go back and fix the mistakes of someone that thought they were "Super SQL Designer".

retrieve Datetime

Friday, March 30, 2012

return an id while doing an insert\update to a table

Friday, March 23, 2012

retrieving user's permissions for each table

retrieving user's permissions for each table

Wednesday, March 7, 2012

retrieve the primary keys with SQL DMO and vb.net

Retrieve REDO Information

Retrieve REDO Information

Retrieve REDO Information

retrieve records affected count from ADO?

Retrieve record just updated

Followers

Blog Archive

About Me