Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIAYou could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrés
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrés
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>>Hi,
>>I'm new to MSSQL world and I have a little question:
>>I have just added a new field to a table containing some 3000 rows...
>>The new field has the same value for all the existent rows...
>>How can I automatically update all rows ?
>>I would like to put "EN" string in all rows... What tool do I need to
>>use ? (SQL query analyzer ?)
>>Any site, if this is to complex to explain here ?
>>TIA
>
>
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 29, 2012
Auto filling
Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIA
Something like (from Query Analyzer):
UPDATE tblname
SET colname = 'EN'
I suggest you purchase some entry level books about the SQL language, knowing basics of SQL is a
definite advantage whatever type you do with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Osawa" <posawa@.sun.es> wrote in message news:eKdOTXzjEHA.2908@.tk2msftngp13.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIA
Something like (from Query Analyzer):
UPDATE tblname
SET colname = 'EN'
I suggest you purchase some entry level books about the SQL language, knowing basics of SQL is a
definite advantage whatever type you do with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Osawa" <posawa@.sun.es> wrote in message news:eKdOTXzjEHA.2908@.tk2msftngp13.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
Auto filling
Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIA
You could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>
|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
sql
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIA
You could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>
|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
sql
Auto filling
Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIAYou could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
>
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIAYou could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
>
Auto fill in colums trough foreign key relationship
Hi,
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers WimYou want help creating a denormalized database?
[sigh...]
If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.
Why are you duplicating data like this?|||You want help creating a denormalized database?
[sigh...]
If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.
Why are you duplicating data like this?I keep trying to shoot myself it the foot, but the rifle wobbles when I pull the trigger. Can one of you guys hold it for me?
I actually thought about trying to formulate a reply to this question, but nothing I wrote seemed fit to post. I'm thinking that a VIEW would make life a lot easier in the long run, what do you think?
-PatP
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers WimYou want help creating a denormalized database?
[sigh...]
If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.
Why are you duplicating data like this?|||You want help creating a denormalized database?
[sigh...]
If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.
Why are you duplicating data like this?I keep trying to shoot myself it the foot, but the rifle wobbles when I pull the trigger. Can one of you guys hold it for me?
I actually thought about trying to formulate a reply to this question, but nothing I wrote seemed fit to post. I'm thinking that a VIEW would make life a lot easier in the long run, what do you think?
-PatP
Auto fill collumn
I have an existing collumn in a table that contains a 5 digit number used as
an employee id field.(datatype nvarchar) I would like to auto fill the rows
that are '00000' or Null with an number beginning with X0001 and incrementin
g
by 1 thereafter.( I want the X as a prefix). Any Ideas'
Thanks, RobOn Mon, 31 Jan 2005 13:37:23 GMT, Robb Mann wrote:
> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the ro
ws
>that are '00000' or Null with an number beginning with X0001 and incrementi
ng
>by 1 thereafter.( I want the X as a prefix). Any Ideas'
>Thanks, Rob
Hi Rob,
Define: "beginning" and "thereafter". Remember that tables are UNordered
sets of data, by definition. There is no "first" or "beginning" row. You
will have to define some ranking to base your numbers on.
Assuming you want to use the column date_entered to base the ranking, and
assuming you have no diuplicates in the date_entered, you could use the
following:
UPDATE MyTable
SET employee_id = 'X' + LEFT ('0000' +
CAST((SELECT COUNT(*)
FROM MyTable AS a
WHERE a.date_entered <= MyTable.date_entered
AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
WHERE COALESCE (employee_id, '00000') = '00000'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is the primary key of the table? SSN? Payroll number? Are you
saying that the table doesn't have a key right now? If you just want an
arbitrary surrogate key then use IDENTITY. If you requirements are more
complex then we'll need some more information: DDL, sample data,
required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||If your table does not have duplicated employees by first name and last name
,
then you can use this approach (if not, use also the department to calculate
the rank):
use northwind
go
create table t (
empid char(5) null,
fname varchar(25) not null,
lname varchar(25) not null
)
insert into t values ('00000', 'a', 'b')
insert into t values ('00000', 'c', 'd')
insert into t values (null, 'e', 'f')
insert into t values ('00001', 'v', 'w')
select * from t order by fname, lname
update
e
set
e.empid = 'x' + right('000' + ltrim(rank), 4)
from
t as e
inner join
(
select
a.fname,
a.lname,
count(*) as rank
from
t as a
inner join
t as b
on b.fname + b.lname <= a.fname + a.lname
and (a.empid is null or a.empid = '00000')
and (b.empid is null or b.empid = '00000')
group by
a.fname,
a.lname
) as f
on e.fname = f.fname and e.lname = f.lname
select * from t order by fname, lname
drop table t
go
AMB
"Robb Mann" wrote:
> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the r
ows
> that are '00000' or Null with an number beginning with X0001 and increment
ing
> by 1 thereafter.( I want the X as a prefix). Any Ideas'
> Thanks, Rob
>|||On Mon, 31 Jan 2005 14:54:43 +0100, Hugo Kornelis wrote:
>UPDATE MyTable
>SET employee_id = 'X' + LEFT ('0000' +
> CAST((SELECT COUNT(*)
> FROM MyTable AS a
> WHERE a.date_entered <= MyTable.date_entered
> AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
>WHERE COALESCE (employee_id, '00000') = '00000'
(snip)
Correction: I should have used RIGHT, not LEFT.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
an employee id field.(datatype nvarchar) I would like to auto fill the rows
that are '00000' or Null with an number beginning with X0001 and incrementin
g
by 1 thereafter.( I want the X as a prefix). Any Ideas'
Thanks, RobOn Mon, 31 Jan 2005 13:37:23 GMT, Robb Mann wrote:
> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the ro
ws
>that are '00000' or Null with an number beginning with X0001 and incrementi
ng
>by 1 thereafter.( I want the X as a prefix). Any Ideas'
>Thanks, Rob
Hi Rob,
Define: "beginning" and "thereafter". Remember that tables are UNordered
sets of data, by definition. There is no "first" or "beginning" row. You
will have to define some ranking to base your numbers on.
Assuming you want to use the column date_entered to base the ranking, and
assuming you have no diuplicates in the date_entered, you could use the
following:
UPDATE MyTable
SET employee_id = 'X' + LEFT ('0000' +
CAST((SELECT COUNT(*)
FROM MyTable AS a
WHERE a.date_entered <= MyTable.date_entered
AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
WHERE COALESCE (employee_id, '00000') = '00000'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is the primary key of the table? SSN? Payroll number? Are you
saying that the table doesn't have a key right now? If you just want an
arbitrary surrogate key then use IDENTITY. If you requirements are more
complex then we'll need some more information: DDL, sample data,
required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||If your table does not have duplicated employees by first name and last name
,
then you can use this approach (if not, use also the department to calculate
the rank):
use northwind
go
create table t (
empid char(5) null,
fname varchar(25) not null,
lname varchar(25) not null
)
insert into t values ('00000', 'a', 'b')
insert into t values ('00000', 'c', 'd')
insert into t values (null, 'e', 'f')
insert into t values ('00001', 'v', 'w')
select * from t order by fname, lname
update
e
set
e.empid = 'x' + right('000' + ltrim(rank), 4)
from
t as e
inner join
(
select
a.fname,
a.lname,
count(*) as rank
from
t as a
inner join
t as b
on b.fname + b.lname <= a.fname + a.lname
and (a.empid is null or a.empid = '00000')
and (b.empid is null or b.empid = '00000')
group by
a.fname,
a.lname
) as f
on e.fname = f.fname and e.lname = f.lname
select * from t order by fname, lname
drop table t
go
AMB
"Robb Mann" wrote:
> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the r
ows
> that are '00000' or Null with an number beginning with X0001 and increment
ing
> by 1 thereafter.( I want the X as a prefix). Any Ideas'
> Thanks, Rob
>|||On Mon, 31 Jan 2005 14:54:43 +0100, Hugo Kornelis wrote:
>UPDATE MyTable
>SET employee_id = 'X' + LEFT ('0000' +
> CAST((SELECT COUNT(*)
> FROM MyTable AS a
> WHERE a.date_entered <= MyTable.date_entered
> AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
>WHERE COALESCE (employee_id, '00000') = '00000'
(snip)
Correction: I should have used RIGHT, not LEFT.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Tuesday, March 27, 2012
Auto Email Statistics from DB Table
Hi,
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
Chip
detail), in the next
message
you might want to check out xp_smtp_sendmail from www.sqldev.net.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...[QUOTE]
> sincere regards for the inputs.
> Chip
> detail), in the next
> message
> daily
> appreciated.|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
real pain to setup),
www.sqldev.net.
oi=djq&as_ugroup=microsoft.public.sqlserver
message
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
quote:|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
quote:|||sincere regards for the inputs.
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>
Chip
quote:
>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for
detail), in the next
quote:
>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||In case you don't have xp_sendmail configured (can be a real pain to setup),
>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
daily[QUOTE]
appreciated.[QUOTE]
>
>.
>
you might want to check out xp_smtp_sendmail from www.sqldev.net.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...[QUOTE]
> sincere regards for the inputs.
> Chip
> detail), in the next
> message
> daily
> appreciated.|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
quote:
>--Original Message--
>In case you don't have xp_sendmail configured (can be a
real pain to setup),
quote:
>you might want to check out xp_smtp_sendmail from
www.sqldev.net.
quote:
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
quote:
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
quote:sql
>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
various[QUOTE]
get[QUOTE]
does[QUOTE]
soon[QUOTE]
>
>.
>
Auto Email Statistics from DB Table
Hi,
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||sincere regards for the inputs.
Chip
>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for
detail), in the next
>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> Hi,
>> I have a user database 'MyDatabase'. There are various
>> Tables in this db. After executing a complex query I get
>> some statistics , which I have to send to my boss on
daily
>> basis. I am failing to send statistics manually by
>> preparing it as email most of the times coz of other
>> prioity issues. I am looking for a automatic mailing
>> solution with SQL Server Machine ( the machine also does
>> have MS SMTP). I have the prototype in mind...
>> 1. I need to get the data into a temporary table as soon
>> as the clock moves to 12:00 AM
>> 2. Do Email Operations
>> 3. Clear the Contents of the table
>> I am not at all sure with what has to be done
>> programatically. Any guidance will be highly
appreciated.
>> Sincere Regards
>> Chip
>
>.
>|||In case you don't have xp_sendmail configured (can be a real pain to setup),
you might want to check out xp_smtp_sendmail from www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
> sincere regards for the inputs.
> Chip
> >--Original Message--
> >Create a job, in the job do xp_sendmail (see BOL for
> detail), in the next
> >job step clear the table.
> >
> >hth
> >
> >Quentin
> >
> >
> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> I have a user database 'MyDatabase'. There are various
> >> Tables in this db. After executing a complex query I get
> >> some statistics , which I have to send to my boss on
> daily
> >> basis. I am failing to send statistics manually by
> >> preparing it as email most of the times coz of other
> >> prioity issues. I am looking for a automatic mailing
> >> solution with SQL Server Machine ( the machine also does
> >> have MS SMTP). I have the prototype in mind...
> >>
> >> 1. I need to get the data into a temporary table as soon
> >> as the clock moves to 12:00 AM
> >>
> >> 2. Do Email Operations
> >> 3. Clear the Contents of the table
> >>
> >> I am not at all sure with what has to be done
> >> programatically. Any guidance will be highly
> appreciated.
> >>
> >> Sincere Regards
> >>
> >> Chip
> >>
> >
> >
> >.
> >|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
>--Original Message--
>In case you don't have xp_sendmail configured (can be a
real pain to setup),
>you might want to check out xp_smtp_sendmail from
www.sqldev.net.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
>> sincere regards for the inputs.
>> Chip
>> >--Original Message--
>> >Create a job, in the job do xp_sendmail (see BOL for
>> detail), in the next
>> >job step clear the table.
>> >
>> >hth
>> >
>> >Quentin
>> >
>> >
>> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I have a user database 'MyDatabase'. There are
various
>> >> Tables in this db. After executing a complex query I
get
>> >> some statistics , which I have to send to my boss on
>> daily
>> >> basis. I am failing to send statistics manually by
>> >> preparing it as email most of the times coz of other
>> >> prioity issues. I am looking for a automatic mailing
>> >> solution with SQL Server Machine ( the machine also
does
>> >> have MS SMTP). I have the prototype in mind...
>> >>
>> >> 1. I need to get the data into a temporary table as
soon
>> >> as the clock moves to 12:00 AM
>> >>
>> >> 2. Do Email Operations
>> >> 3. Clear the Contents of the table
>> >>
>> >> I am not at all sure with what has to be done
>> >> programatically. Any guidance will be highly
>> appreciated.
>> >>
>> >> Sincere Regards
>> >>
>> >> Chip
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||sincere regards for the inputs.
Chip
>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for
detail), in the next
>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> Hi,
>> I have a user database 'MyDatabase'. There are various
>> Tables in this db. After executing a complex query I get
>> some statistics , which I have to send to my boss on
daily
>> basis. I am failing to send statistics manually by
>> preparing it as email most of the times coz of other
>> prioity issues. I am looking for a automatic mailing
>> solution with SQL Server Machine ( the machine also does
>> have MS SMTP). I have the prototype in mind...
>> 1. I need to get the data into a temporary table as soon
>> as the clock moves to 12:00 AM
>> 2. Do Email Operations
>> 3. Clear the Contents of the table
>> I am not at all sure with what has to be done
>> programatically. Any guidance will be highly
appreciated.
>> Sincere Regards
>> Chip
>
>.
>|||In case you don't have xp_sendmail configured (can be a real pain to setup),
you might want to check out xp_smtp_sendmail from www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
> sincere regards for the inputs.
> Chip
> >--Original Message--
> >Create a job, in the job do xp_sendmail (see BOL for
> detail), in the next
> >job step clear the table.
> >
> >hth
> >
> >Quentin
> >
> >
> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> I have a user database 'MyDatabase'. There are various
> >> Tables in this db. After executing a complex query I get
> >> some statistics , which I have to send to my boss on
> daily
> >> basis. I am failing to send statistics manually by
> >> preparing it as email most of the times coz of other
> >> prioity issues. I am looking for a automatic mailing
> >> solution with SQL Server Machine ( the machine also does
> >> have MS SMTP). I have the prototype in mind...
> >>
> >> 1. I need to get the data into a temporary table as soon
> >> as the clock moves to 12:00 AM
> >>
> >> 2. Do Email Operations
> >> 3. Clear the Contents of the table
> >>
> >> I am not at all sure with what has to be done
> >> programatically. Any guidance will be highly
> appreciated.
> >>
> >> Sincere Regards
> >>
> >> Chip
> >>
> >
> >
> >.
> >|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
>--Original Message--
>In case you don't have xp_sendmail configured (can be a
real pain to setup),
>you might want to check out xp_smtp_sendmail from
www.sqldev.net.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
>> sincere regards for the inputs.
>> Chip
>> >--Original Message--
>> >Create a job, in the job do xp_sendmail (see BOL for
>> detail), in the next
>> >job step clear the table.
>> >
>> >hth
>> >
>> >Quentin
>> >
>> >
>> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I have a user database 'MyDatabase'. There are
various
>> >> Tables in this db. After executing a complex query I
get
>> >> some statistics , which I have to send to my boss on
>> daily
>> >> basis. I am failing to send statistics manually by
>> >> preparing it as email most of the times coz of other
>> >> prioity issues. I am looking for a automatic mailing
>> >> solution with SQL Server Machine ( the machine also
does
>> >> have MS SMTP). I have the prototype in mind...
>> >>
>> >> 1. I need to get the data into a temporary table as
soon
>> >> as the clock moves to 12:00 AM
>> >>
>> >> 2. Do Email Operations
>> >> 3. Clear the Contents of the table
>> >>
>> >> I am not at all sure with what has to be done
>> >> programatically. Any guidance will be highly
>> appreciated.
>> >>
>> >> Sincere Regards
>> >>
>> >> Chip
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
AUTO DROP OF SUBSCRIPTION
Hi,
I have merge replicated, remote server & local server.
The database has only one table. It was working fine.
More than 15 days, it was isolated without any transaction.
After some days (around 15 days) I found that the subscription is dropped.
What is the reason for this?
Is there any way to avoid this?
Thanks,
Soura.
Soura,
the Expired Subscription Cleanup agent has deleted the subscription.
Disabling this agent won't help, because you need to forcably prevent the
subscription from expiring. If it is transactional, make sure that the
subscription expiration period is sufficiently long eg 3 weeks, and history
retention period also the same length of time. If it was transactional, the
same would apply to the transaction retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was under the impression that transactional replication needs to be
re-established after 72 hours and that this is the limit as it can not be
changed past this value. Are you primarily discussing the removal of the
subscription or the expiration of the subscription ?
It has been a long held understanding with the DBA's at my organisation that
72 Hours was the maximum time before expiration of a subscription to
transactional replication required a rebuild of the subscritpion .
"Paul Ibison" wrote:
> Soura,
> the Expired Subscription Cleanup agent has deleted the subscription.
> Disabling this agent won't help, because you need to forcably prevent the
> subscription from expiring. If it is transactional, make sure that the
> subscription expiration period is sufficiently long eg 3 weeks, and history
> retention period also the same length of time. If it was transactional, the
> same would apply to the transaction retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Perhaps the quantity of information in your company makes this a practivcal
limit for the size of the distribution database, but there is no such
hardcoded limit in SQL Server - it is entirely configurable.
Rgds,
Paul Ibison
I have merge replicated, remote server & local server.
The database has only one table. It was working fine.
More than 15 days, it was isolated without any transaction.
After some days (around 15 days) I found that the subscription is dropped.
What is the reason for this?
Is there any way to avoid this?
Thanks,
Soura.
Soura,
the Expired Subscription Cleanup agent has deleted the subscription.
Disabling this agent won't help, because you need to forcably prevent the
subscription from expiring. If it is transactional, make sure that the
subscription expiration period is sufficiently long eg 3 weeks, and history
retention period also the same length of time. If it was transactional, the
same would apply to the transaction retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was under the impression that transactional replication needs to be
re-established after 72 hours and that this is the limit as it can not be
changed past this value. Are you primarily discussing the removal of the
subscription or the expiration of the subscription ?
It has been a long held understanding with the DBA's at my organisation that
72 Hours was the maximum time before expiration of a subscription to
transactional replication required a rebuild of the subscritpion .
"Paul Ibison" wrote:
> Soura,
> the Expired Subscription Cleanup agent has deleted the subscription.
> Disabling this agent won't help, because you need to forcably prevent the
> subscription from expiring. If it is transactional, make sure that the
> subscription expiration period is sufficiently long eg 3 weeks, and history
> retention period also the same length of time. If it was transactional, the
> same would apply to the transaction retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Perhaps the quantity of information in your company makes this a practivcal
limit for the size of the distribution database, but there is no such
hardcoded limit in SQL Server - it is entirely configurable.
Rgds,
Paul Ibison
Auto date through priority
Hi,
I have a table which contains a Create datetimefield which has a default on the current date , a priorityfield and another datetimefield which will be the due date and has to be calculated by the first date and the priority field,
How can i do this and what fields must i have.
Does someone does this?
can someone help me with this?
Kind regards Wimwhat whould happen to the due-date if the priority changed?|||what whould happen to the due-date if the priority changed?
The due date has to change to!|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO
INSERT INTO TAB1 (PRIO) VALUES (1)
GO
SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO
DROP TABLE TAB1
GO
EDIT: Just to be sure; either have a default prio or have a not null constraint|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO
INSERT INTO TAB1 (PRIO) VALUES (1)
GO
SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO
DROP TABLE TAB1
GO
EDIT: Just to be sure; either have a default prio or have a not null constraint
And what if i use the priority through an foreign key an have several prioritys which uses different time like:
prio 1 = 1day
prio 2 =1 week
prio3 = 1 month
How will it look like then?|||well, it could look like:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO|||well, it could look like:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO
Thanx alot man, that hit the spot!!
I really appreciated your help..
Cheers Wim
I have a table which contains a Create datetimefield which has a default on the current date , a priorityfield and another datetimefield which will be the due date and has to be calculated by the first date and the priority field,
How can i do this and what fields must i have.
Does someone does this?
can someone help me with this?
Kind regards Wimwhat whould happen to the due-date if the priority changed?|||what whould happen to the due-date if the priority changed?
The due date has to change to!|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO
INSERT INTO TAB1 (PRIO) VALUES (1)
GO
SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO
DROP TABLE TAB1
GO
EDIT: Just to be sure; either have a default prio or have a not null constraint|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO
INSERT INTO TAB1 (PRIO) VALUES (1)
GO
SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO
DROP TABLE TAB1
GO
EDIT: Just to be sure; either have a default prio or have a not null constraint
And what if i use the priority through an foreign key an have several prioritys which uses different time like:
prio 1 = 1day
prio 2 =1 week
prio3 = 1 month
How will it look like then?|||well, it could look like:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO|||well, it could look like:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO
Thanx alot man, that hit the spot!!
I really appreciated your help..
Cheers Wim
Auto Create trigger after re-initialization completed
Hi all,
Is it possible to create a trigger after creation of table during reinitialization? if so, how can I do that? Thanks in advance!
Hi, Stephanie,
If I understand your question correctly, you wan to create a trigger on subscriber db right after replication agent created replicating table on subscriber database. If so, you can use post snapshot script.
To specify a post snapshot script in Management Studio, right click a publication under replication node, choose Properties context menu to bring up Publication Properties dialog, on the left pane treeview, click snapshot, you should see the input field to specify Pre snapshot script and post snapshot script under "Run Additional scripts" section.
Hope that helps,
Zhiqiang
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thx for reply
I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
The replication from oracle to MSSQL2005 and the snapshot format is set to 'character' automatically. Is it possible to create trigger in such case? Thanks in advance
|||Stephanie,
You're right, for snapshot using Character mode, the "run additional scripts" option is not available under Publication Properties dialog (disabled in the UI).
In this case, you can add an extra step to create your subscriber table trigger after the distribution or merge agent step in your synchronization job. Following are the steps:
1. Find your sync job under Management Studio --> Object Explorer --> SQL Server Agent --> Jobs --> Your distribution/merge agent job name
2. Right click the sync job, pick Properties from context menu, this will bring up Job Properties dialog
3. On the left side pane under "Select a page", click Steps
4. Insert a new step right after step "Run Agent" to execute your script to create the subscriber table trigger,
Example:
step name: Run Post snapshot script to create subscriber table trigger
type: Operating System (cmdexec)
RunAs: SQL Server Agent Service Account
Command: sqlcmd.exe -E -Ssubscriberinstancename -dsubscriberdbname -Q"create trigger postsnapshottrigger on [subscriberdbname].[dbo].[subscribertable] for update,insert,delete AS select * from sys.databases"
5. Change the Advanced Option for the new step:
On Success action: Quit the job reporting success
On failure action: Go to the next step
6. Change the Advanced Option for step "Run Agent":
On success action: Go to the next step
Leave on failure action unchanged
The original setting for "On success action" is "quite the job reporting success", this will result in the new step you just added being ignored thus we need this change.
7. Reinitialize your subscription and rerun your sync job
I have tested this scenario on a character mode merge publication and it worked just fine (I'm not using Oracle in this test).
If you're not using SQL Server agent to run your jobs, for example, using a batch file to run sync command directly, you can just add the command to create trigger after sync command.
Let me know if this helps.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||The table is re-created after re-initialize but the trigger didn't be created. I found that the agent job is keeping 'running'...
Just want to confirm that the distribution job with the 1step 'Distribution agent startup message' , right?
Thanks in advance
|||
Hi, stephanie,
I just realized there is one issue with my approach if your distribution agent is set to run continuously (there is a -continuous prameter for the distribution command for step Run Agent), this way your new step will never get a chance to run since the Run Agent step is running continuously.
If your oracle publication can be re-created, you can walk through the New Oracle Publication Wizard and at the page of "Wizard Actions", uncheck "Create the publication" option and check "Generate a script file with steps to create the publication", once the script is created, modify the @.post_snapshot_script pointing to the location of your script to create the trigger, then run this generated script to create your oracle publication.
If you don't want to re-create your oracle publication, you can modify the generated snapshot files to create the trigger after the subscriber table is created (there should be a .sch file for your replicating table), then re-initialize your publication. Note this manual step need to be done each time you re-generated the snapshot files
You can also create a new SQL Server agent job to just create the trigger and have it to run continuously. The logic of your script should be: if subscriber table exist and if the trigger doesn't exist, then create the trigger. This will have some system overhead and your trigger may miss some transactions if updates to the table occurred and before the trigger is created.
Let me know if it helps.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
I prefer option 1 but i found that there is no '@.post_snapshot_script ' in the scripted generated... should I append this option into the script? Also, there are 3 parts in the script, running
sys.sp_addlogreader_agent, exec sp_addpublication,
exec sp_addpublication_snapshot and sp_addarticle
where should I append it? Thanks in advance.
|||
@.post_snapshot_script is a parameter of sp_addpublication so it should be appended to the sp_addpublication call, see Books Online help topic for sp_addpublication for details of this parameter.
Let me know if you encounter any issues.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks a lot, it perfectly solve our problem!
|||That's great!
By the way, I've submitted a feature request to have new publication wizard support the addition of pre/post snapshot script, this issue may be addressed in a future release of SQL Server so you configure pre/post snapshot script without needing the manual step of modifying and running the replication script as a workaround.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
Auto Create trigger after re-initialization completed
Is it possible to create a trigger after creation of table during
reinitialization? if so, how can I do that? Thanks in advance!
Do it through a post snapshot command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
> Is it possible to create a trigger after creation of table during
> reinitialization? if so, how can I do that? Thanks in advance!
>
|||What about if the replication from oracle to MSSQL2005 and the snapshot
format is set to 'character'? Thanks in advance.
"Hilary Cotter" wrote:
> Do it through a post snapshot command.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
>
>
|||A character format for the snapshot refers to the data.
A post snapshot command is a script file that you specify. Once the
snapshot completes, SQL Server will open the script file that you specify
and execute anything in the script.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...[vbcol=seagreen]
> What about if the replication from oracle to MSSQL2005 and the snapshot
> format is set to 'character'? Thanks in advance.
> "Hilary Cotter" wrote:
|||Thx for reply. I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
Please advise. Thanks
"Michael Hotek" wrote:
> A character format for the snapshot refers to the data.
> A post snapshot command is a script file that you specify. Once the
> snapshot completes, SQL Server will open the script file that you specify
> and execute anything in the script.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...
>
>
|||Forgot about that restriction. There was a reason for that restriction, but
I can't remember what it was right now.
One workaround is to create a DDL trigger like the following:
create trigger test
on database
for create_table
as
declare @.parentid int
select @.parentid = object_id from sys.tables where name = 'MyTable'
print @.parentid
if @.parentid is null
return
if exists (select 1 from sys.triggers where parent_id = @.parentid
and name = 'MyTestTrigger')
return
exec sp_executesql N'create trigger MyTestTrigger on MyTable for insert as
raiserror(''Test'',16,10)'
go
create table MyTable
(id int)
go
I can't think of any other workarounds at the moment.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:2343A716-0B57-4972-A9B2-C3B4FECA72D9@.microsoft.com...[vbcol=seagreen]
> Thx for reply. I can't find the option 'After applying the snapshot,
> execute
> this script text box' in Snapshot page of the Publication Properties as
> stated in Online Books. Also, I found a note in online book stating
> 'These
> options are not available if the Snapshot format option is set to
> Character.'
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
> Please advise. Thanks
> "Michael Hotek" wrote:
reinitialization? if so, how can I do that? Thanks in advance!
Do it through a post snapshot command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
> Is it possible to create a trigger after creation of table during
> reinitialization? if so, how can I do that? Thanks in advance!
>
|||What about if the replication from oracle to MSSQL2005 and the snapshot
format is set to 'character'? Thanks in advance.
"Hilary Cotter" wrote:
> Do it through a post snapshot command.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
>
>
|||A character format for the snapshot refers to the data.
A post snapshot command is a script file that you specify. Once the
snapshot completes, SQL Server will open the script file that you specify
and execute anything in the script.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...[vbcol=seagreen]
> What about if the replication from oracle to MSSQL2005 and the snapshot
> format is set to 'character'? Thanks in advance.
> "Hilary Cotter" wrote:
|||Thx for reply. I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
Please advise. Thanks
"Michael Hotek" wrote:
> A character format for the snapshot refers to the data.
> A post snapshot command is a script file that you specify. Once the
> snapshot completes, SQL Server will open the script file that you specify
> and execute anything in the script.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...
>
>
|||Forgot about that restriction. There was a reason for that restriction, but
I can't remember what it was right now.
One workaround is to create a DDL trigger like the following:
create trigger test
on database
for create_table
as
declare @.parentid int
select @.parentid = object_id from sys.tables where name = 'MyTable'
print @.parentid
if @.parentid is null
return
if exists (select 1 from sys.triggers where parent_id = @.parentid
and name = 'MyTestTrigger')
return
exec sp_executesql N'create trigger MyTestTrigger on MyTable for insert as
raiserror(''Test'',16,10)'
go
create table MyTable
(id int)
go
I can't think of any other workarounds at the moment.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:2343A716-0B57-4972-A9B2-C3B4FECA72D9@.microsoft.com...[vbcol=seagreen]
> Thx for reply. I can't find the option 'After applying the snapshot,
> execute
> this script text box' in Snapshot page of the Publication Properties as
> stated in Online Books. Also, I found a note in online book stating
> 'These
> options are not available if the Snapshot format option is set to
> Character.'
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
> Please advise. Thanks
> "Michael Hotek" wrote:
Auto create & load in SQL table
Hi does anyone know how to create a sql table and then import a list by just clicking on a button to call a procedure?
CREATE TABLE clients(ClientID VARCHAR(5), ClientName VARCHAR(30), PRIMARY KEY (ClientID));
LOAD DATA LOCAL INFILE 'C:/client.csv' INTO TABLE clients
LINES TERMINATED BY '\r\n';
You can create a stored procedure that create the table if it doesn't exist, and use 'BULK INSERT' to import the data from a file into the table, specifying FIELDTERMINATOR and ROWTERMINATOR, for example:
CREATE PROC sp_ImportData @.filename varchar(200)='C:\client.csv'
as
if exists(select * from sysobjects where name='clients')
drop table clients
exec('CREATE TABLE clients(ClientID VARCHAR(5) PRIMARY KEY, ClientName VARCHAR(30))')
exec('bulk insert clients from'''+@.filename+'''
with FIELDTERMINATOR = ''\r'', ROWTERMINATOR = ''\r\n''')
go
Auto Change Script
In Enterprise Manager (SQL2K) the Save Change Script
prompt that appears when you save the design of a table
has stopped appearing. Any ideas how to turn it back on
again?Click the "Save change script" button (third from the left), then check the
checkbox at the bottom of the dialog.
--
Jacco Schalkwijk
SQL Server MVP
"Mark Stipetic" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3f01c39897$01cfb8e0$a501280a@.phx.gbl...
> In Enterprise Manager (SQL2K) the Save Change Script
> prompt that appears when you save the design of a table
> has stopped appearing. Any ideas how to turn it back on
> again?|||If you press the "Save change script" button on the toolbar, is the checkbox to auto-save enabled?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Stipetic" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3f01c39897$01cfb8e0$a501280a@.phx.gbl...
> In Enterprise Manager (SQL2K) the Save Change Script
> prompt that appears when you save the design of a table
> has stopped appearing. Any ideas how to turn it back on
> again?|||Sorted. Many Thanks
>--Original Message--
>Click the "Save change script" button (third from the
left), then check the
>checkbox at the bottom of the dialog.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mark Stipetic" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a3f01c39897$01cfb8e0$a501280a@.phx.gbl...
>> In Enterprise Manager (SQL2K) the Save Change Script
>> prompt that appears when you save the design of a table
>> has stopped appearing. Any ideas how to turn it back on
>> again?
>
>.
>
prompt that appears when you save the design of a table
has stopped appearing. Any ideas how to turn it back on
again?Click the "Save change script" button (third from the left), then check the
checkbox at the bottom of the dialog.
--
Jacco Schalkwijk
SQL Server MVP
"Mark Stipetic" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3f01c39897$01cfb8e0$a501280a@.phx.gbl...
> In Enterprise Manager (SQL2K) the Save Change Script
> prompt that appears when you save the design of a table
> has stopped appearing. Any ideas how to turn it back on
> again?|||If you press the "Save change script" button on the toolbar, is the checkbox to auto-save enabled?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Stipetic" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3f01c39897$01cfb8e0$a501280a@.phx.gbl...
> In Enterprise Manager (SQL2K) the Save Change Script
> prompt that appears when you save the design of a table
> has stopped appearing. Any ideas how to turn it back on
> again?|||Sorted. Many Thanks
>--Original Message--
>Click the "Save change script" button (third from the
left), then check the
>checkbox at the bottom of the dialog.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mark Stipetic" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a3f01c39897$01cfb8e0$a501280a@.phx.gbl...
>> In Enterprise Manager (SQL2K) the Save Change Script
>> prompt that appears when you save the design of a table
>> has stopped appearing. Any ideas how to turn it back on
>> again?
>
>.
>
Thursday, March 22, 2012
Authentication not working!
Hey there,
I have a table [user] in a SQLExpress database [Database.mdf] in the App_Data folder. There are two columns. [userId] and [password]. I want to authenticate the user who wants to log in through a Login control. the user should have his userId and password in the [user] table. I digged and made my code. but it doesn't work it keeps giving me that the user is not authenticated. i guess it has no errors. please tell me if you find the error. here is the code:
1protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)2 {3 SqlConnection conExpress =new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Database;Integrated Security=True;User Instance=False");//Create the server connection45try6 {7string id_user = Login1.UserName.Trim();//Get the username from the control8string pass_user = Login1.Password;//get the Password from the control910 //These are a sql and connection Examples11string sql ="SELECT userId, password FROM user WHERE userId = @.param_Id AND password = @.param_Password";12 SqlCommand comSQL =new SqlCommand(sql, conExpress);//Create the sql command using sql string and sql connection1314 //Add the sql parameters15 comSQL.Parameters.AddWithValue("@.param_Id", id_user);//New on VS 2.016 comSQL.Parameters.AddWithValue("@.param_Password", pass_user);1718string cod_user ="";19string name_user ="";2021//Open database connection22 conExpress.Open();23 SqlDataReader dr = comSQL.ExecuteReader();24while (dr.Read())25 {26 cod_user = dr.GetValue(0).ToString();//The coduser is unique onto database table27 name_user = dr.GetValue(1).ToString();28 }29 conExpress.Close();//Close Database Connection30if (cod_user !="")//The user exist onto database31 {32//Create the session vars33 Session["coduser"] = cod_user;34 Session["nameuser"] = name_user;35 e.Authenticated =true;//Grant the access, Goes to DestinationPageUrl36 }37 }38catch(Exception)//On Login Error39 {40 e.Authenticated =false;//Confirm that you are out41 conExpress.Close();//On any error case, close the database connection42 }43 }The way I've used the Login control with custom authentication (without MembershipProvider) is
processing the event of the Login Control:
protected
void Login1_LoggingIn(object sender,LoginCancelEventArgs e)and if the credentials is valid i create the autheentication cookie:
FormsAuthentication.SetAuthCookie(userName,false);
and Redirect to the page user has requested.
If the credentials are wrong I set :
e.Cancel =
true;and
Login1.FailureText =
"Unable to validate credentials";I'm not sure this is the right way of doing that, but it works for me.
Hope this will help you.
Cheers,
Yani
sqlMonday, March 19, 2012
Authenticating from Active Directory Domain
We use a query that gets the suser_name parameter and
inserts it into a table.
A user converted to an Active Directory Domain and the
suser_name comes up as null.
Is this the way it works? Or is there some setting that
has to be changed?
Any help appreciated.
Thanks,
GeorgeDirectly from BOL
suser_name is obsolete and always returns NULL in S2K.
"George Galcik" <anonymous@.discussions.microsoft.com> wrote in message
news:005b01c3dab4$6bf0fdb0$a101280a@.phx.gbl...
George
wrote in message
inserts it into a table.
A user converted to an Active Directory Domain and the
suser_name comes up as null.
Is this the way it works? Or is there some setting that
has to be changed?
Any help appreciated.
Thanks,
GeorgeDirectly from BOL
suser_name is obsolete and always returns NULL in S2K.
"George Galcik" <anonymous@.discussions.microsoft.com> wrote in message
news:005b01c3dab4$6bf0fdb0$a101280a@.phx.gbl...
quote:|||Thanks for your help, Scott.
> We use a query that gets the suser_name parameter and
> inserts it into a table.
> A user converted to an Active Directory Domain and the
> suser_name comes up as null.
> Is this the way it works? Or is there some setting that
> has to be changed?
> Any help appreciated.
> Thanks,
> George
>
George
quote:
>--Original Message--
>Directly from BOL
>suser_name is obsolete and always returns NULL in S2K.
>"George Galcik" <anonymous@.discussions.microsoft.com>
wrote in message
quote:
>news:005b01c3dab4$6bf0fdb0$a101280a@.phx.gbl...
>
>.
>
Auotincrement without using autoincrement
Hi Lee,
have you thought about using a counter table. Normally something like
Create table Counter (TableName sysname, IDValue int)
What you do is for each table in your database that requires a increasing
number set the tablename and IDValue to 0 then in a transaction you update
the value then retrieve it do your inserts and then commit
i.e
begin transaction
declare @.ID as int
update Counter IDValue=IDVale +1 where TableName='mastertable'
select @.ID=IDValue from Counter where TableName='mastertable'
-- no do your inseertts
insert into other tbales using the @.ID
..
...
commit transaction
Because the counter table is locked during the update no one can get the
next value. If the transaction aborts the the update is rolled back. Also
it's quick as you don't have to go through the larger table to get the max
(even with an index). Obviously you would have a clustered index on the
TableName column or a covering index on both columns,...
Lee don't be too bothered about Joe's (CELKO) comments . He has been a bit
bashed lately (in the forum) and turned a bit rabid. His comments are way
off the mark. It would of been nice to see such expert in the field of SQL
give help instead of put down someone asking for help.
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Lee Gillie" <Lee@.nospam.odp.com> wrote in message
news:uxoaocRnFHA.2904@.TK2MSFTNGP14.phx.gbl...
>I have a master table which I have been updating in multiple queries to
>create a new row. It has a primary key, call it WorkID. I can't make it
>autoincrement. And it is more complicated because...
> WorkID can also appear in two other tables, as references to the master
> record, but I need to provide for the master record being gone, and as
> such the records in the other two tables are orphaned. That is fine.
> To create a new master record I select MAX(WorkID) unioned on all 3
> tables, and increment the result, and this is my new WorkID. However, even
> if in a transaction, this query work can result in duplicate WorkID
> numbers when new masters are created in extremely rapid succession. I see
> the flaw here. Don't think this approach can be salvaged when the MAX and
> INSERT queries are done separately.
> I have been racking my brain to find a way to deal with this WITHOUT
> resorting to auto-increment on the master table. I can think of no
> expression for an INSERT value which will take the MAX+1 of WorkID on the
> three tables, but it seems there must be a way to do this in a single
> statement. Something like:
> INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
> Maybe I need a new single row, single column table that arbitrates new
> WorkID values? Or maybe someone has a favorite trick?
> Thx - LeeGreg -
I like it!
I appreciate ALL the replies. I learned some stuff, so I got better at
my job, and my project is nearer the BRICK HOUSE I am striving for.
Thanks everyone for taking a moment to consider this and sharing your
thoughts.
Also... I never let anyone else's bad day bring me down. Life is way too
short. But even so, if you are open to it, you can learn something from
anyone.
Best regards - Lee
Pay it forward!|||The problem here (other than slightly incorrect syntax :) is that you are
single threading all inserts in your system. They all have to wait on the
transaction to complete to get the next value. Serious hog on concurrency.
An idea I have used just have a table for getting surrogate values if you
can afford to do this one row at a time and keep the table pared down:
create table counter
(
counterId int identity(1,1) primary key
)
go
insert into counter
default values
select scope_identity()
---
1
Here at least there will be no single threading. A delete job could keep
this table really small well over 1500 should fit on a single page so just
delete all but the last few hundred. Make sure you get a good plan on that
query because if you table scan you will make everyone wait on the clean up.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"GregO" <grego@.community.nospam> wrote in message
news:uUxOwCXnFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hi Lee,
> have you thought about using a counter table. Normally something like
> Create table Counter (TableName sysname, IDValue int)
> What you do is for each table in your database that requires a increasing
> number set the tablename and IDValue to 0 then in a transaction you update
> the value then retrieve it do your inserts and then commit
> i.e
> begin transaction
> declare @.ID as int
> update Counter IDValue=IDVale +1 where TableName='mastertable'
> select @.ID=IDValue from Counter where TableName='mastertable'
> -- no do your inseertts
> insert into other tbales using the @.ID
> ..
> ...
> commit transaction
> Because the counter table is locked during the update no one can get the
> next value. If the transaction aborts the the update is rolled back.
> Also it's quick as you don't have to go through the larger table to get
> the max (even with an index). Obviously you would have a clustered index
> on the TableName column or a covering index on both columns,...
> Lee don't be too bothered about Joe's (CELKO) comments . He has been a
> bit bashed lately (in the forum) and turned a bit rabid. His comments are
> way off the mark. It would of been nice to see such expert in the field
> of SQL give help instead of put down someone asking for help.
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Lee Gillie" <Lee@.nospam.odp.com> wrote in message
> news:uxoaocRnFHA.2904@.TK2MSFTNGP14.phx.gbl...
>|||Hi Louis,
I agree about the delay in my solution but sometimes you need to be sure and
wait for a transaction. In the solution I put forward you have the other
bonus of never missing a number. Unlike identity and doing without a
transaction (not that the user said this was an issue)
Either way if the transactions are small then the delay shouldn't be an
issue
kind regards
Greg O
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eZ04akinFHA.3316@.TK2MSFTNGP14.phx.gbl...
> The problem here (other than slightly incorrect syntax :) is that you are
> single threading all inserts in your system. They all have to wait on the
> transaction to complete to get the next value. Serious hog on
> concurrency.
> An idea I have used just have a table for getting surrogate values if you
> can afford to do this one row at a time and keep the table pared down:
> create table counter
> (
> counterId int identity(1,1) primary key
> )
> go
> insert into counter
> default values
> select scope_identity()
> ---
> 1
> Here at least there will be no single threading. A delete job could keep
> this table really small well over 1500 should fit on a single page so just
> delete all but the last few hundred. Make sure you get a good plan on
> that query because if you table scan you will make everyone wait on the
> clean up.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uUxOwCXnFHA.1148@.TK2MSFTNGP12.phx.gbl...
>|||That is true, just wanted to make sure that you realized that no more than
one user at a time could insert data into any table using your system. If
you can live with that, it is
.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"GregO" <grego@.community.nospam> wrote in message
news:OFavurinFHA.2444@.tk2msftngp13.phx.gbl...
> Hi Louis,
> I agree about the delay in my solution but sometimes you need to be sure
> and wait for a transaction. In the solution I put forward you have the
> other bonus of never missing a number. Unlike identity and doing without
> a transaction (not that the user said this was an issue)
> Either way if the transactions are small then the delay shouldn't be an
> issue
> kind regards
> Greg O
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eZ04akinFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
have you thought about using a counter table. Normally something like
Create table Counter (TableName sysname, IDValue int)
What you do is for each table in your database that requires a increasing
number set the tablename and IDValue to 0 then in a transaction you update
the value then retrieve it do your inserts and then commit
i.e
begin transaction
declare @.ID as int
update Counter IDValue=IDVale +1 where TableName='mastertable'
select @.ID=IDValue from Counter where TableName='mastertable'
-- no do your inseertts
insert into other tbales using the @.ID
..
...
commit transaction
Because the counter table is locked during the update no one can get the
next value. If the transaction aborts the the update is rolled back. Also
it's quick as you don't have to go through the larger table to get the max
(even with an index). Obviously you would have a clustered index on the
TableName column or a covering index on both columns,...
Lee don't be too bothered about Joe's (CELKO) comments . He has been a bit
bashed lately (in the forum) and turned a bit rabid. His comments are way
off the mark. It would of been nice to see such expert in the field of SQL
give help instead of put down someone asking for help.
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Lee Gillie" <Lee@.nospam.odp.com> wrote in message
news:uxoaocRnFHA.2904@.TK2MSFTNGP14.phx.gbl...
>I have a master table which I have been updating in multiple queries to
>create a new row. It has a primary key, call it WorkID. I can't make it
>autoincrement. And it is more complicated because...
> WorkID can also appear in two other tables, as references to the master
> record, but I need to provide for the master record being gone, and as
> such the records in the other two tables are orphaned. That is fine.
> To create a new master record I select MAX(WorkID) unioned on all 3
> tables, and increment the result, and this is my new WorkID. However, even
> if in a transaction, this query work can result in duplicate WorkID
> numbers when new masters are created in extremely rapid succession. I see
> the flaw here. Don't think this approach can be salvaged when the MAX and
> INSERT queries are done separately.
> I have been racking my brain to find a way to deal with this WITHOUT
> resorting to auto-increment on the master table. I can think of no
> expression for an INSERT value which will take the MAX+1 of WorkID on the
> three tables, but it seems there must be a way to do this in a single
> statement. Something like:
> INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
> Maybe I need a new single row, single column table that arbitrates new
> WorkID values? Or maybe someone has a favorite trick?
> Thx - LeeGreg -
I like it!
I appreciate ALL the replies. I learned some stuff, so I got better at
my job, and my project is nearer the BRICK HOUSE I am striving for.
Thanks everyone for taking a moment to consider this and sharing your
thoughts.
Also... I never let anyone else's bad day bring me down. Life is way too
short. But even so, if you are open to it, you can learn something from
anyone.
Best regards - Lee
Pay it forward!|||The problem here (other than slightly incorrect syntax :) is that you are
single threading all inserts in your system. They all have to wait on the
transaction to complete to get the next value. Serious hog on concurrency.
An idea I have used just have a table for getting surrogate values if you
can afford to do this one row at a time and keep the table pared down:
create table counter
(
counterId int identity(1,1) primary key
)
go
insert into counter
default values
select scope_identity()
---
1
Here at least there will be no single threading. A delete job could keep
this table really small well over 1500 should fit on a single page so just
delete all but the last few hundred. Make sure you get a good plan on that
query because if you table scan you will make everyone wait on the clean up.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"GregO" <grego@.community.nospam> wrote in message
news:uUxOwCXnFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hi Lee,
> have you thought about using a counter table. Normally something like
> Create table Counter (TableName sysname, IDValue int)
> What you do is for each table in your database that requires a increasing
> number set the tablename and IDValue to 0 then in a transaction you update
> the value then retrieve it do your inserts and then commit
> i.e
> begin transaction
> declare @.ID as int
> update Counter IDValue=IDVale +1 where TableName='mastertable'
> select @.ID=IDValue from Counter where TableName='mastertable'
> -- no do your inseertts
> insert into other tbales using the @.ID
> ..
> ...
> commit transaction
> Because the counter table is locked during the update no one can get the
> next value. If the transaction aborts the the update is rolled back.
> Also it's quick as you don't have to go through the larger table to get
> the max (even with an index). Obviously you would have a clustered index
> on the TableName column or a covering index on both columns,...
> Lee don't be too bothered about Joe's (CELKO) comments . He has been a
> bit bashed lately (in the forum) and turned a bit rabid. His comments are
> way off the mark. It would of been nice to see such expert in the field
> of SQL give help instead of put down someone asking for help.
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Lee Gillie" <Lee@.nospam.odp.com> wrote in message
> news:uxoaocRnFHA.2904@.TK2MSFTNGP14.phx.gbl...
>|||Hi Louis,
I agree about the delay in my solution but sometimes you need to be sure and
wait for a transaction. In the solution I put forward you have the other
bonus of never missing a number. Unlike identity and doing without a
transaction (not that the user said this was an issue)
Either way if the transactions are small then the delay shouldn't be an
issue
kind regards
Greg O
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eZ04akinFHA.3316@.TK2MSFTNGP14.phx.gbl...
> The problem here (other than slightly incorrect syntax :) is that you are
> single threading all inserts in your system. They all have to wait on the
> transaction to complete to get the next value. Serious hog on
> concurrency.
> An idea I have used just have a table for getting surrogate values if you
> can afford to do this one row at a time and keep the table pared down:
> create table counter
> (
> counterId int identity(1,1) primary key
> )
> go
> insert into counter
> default values
> select scope_identity()
> ---
> 1
> Here at least there will be no single threading. A delete job could keep
> this table really small well over 1500 should fit on a single page so just
> delete all but the last few hundred. Make sure you get a good plan on
> that query because if you table scan you will make everyone wait on the
> clean up.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uUxOwCXnFHA.1148@.TK2MSFTNGP12.phx.gbl...
>|||That is true, just wanted to make sure that you realized that no more than
one user at a time could insert data into any table using your system. If
you can live with that, it is
.----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"GregO" <grego@.community.nospam> wrote in message
news:OFavurinFHA.2444@.tk2msftngp13.phx.gbl...
> Hi Louis,
> I agree about the delay in my solution but sometimes you need to be sure
> and wait for a transaction. In the solution I put forward you have the
> other bonus of never missing a number. Unlike identity and doing without
> a transaction (not that the user said this was an issue)
> Either way if the transactions are small then the delay shouldn't be an
> issue
> kind regards
> Greg O
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eZ04akinFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
auditing the table
Hi,
I would like to audit table but I do not want to use
profiler. I need to know NT user name not just sql server
standard login for the user. The problem is that somebody
ocasssionally deletes the data and I would like to find
out who is doing it.
Any view is appreciated.You could create a TRIGGER on the table. eg :-
CREATE TRIGGER reminder
ON titles
FOR DELETE
AS
EXEC master..xp_sendmail 'ToYou',
'Someone just deleted a row'
GO
this could easily be enhanced to log to a table, you can capture the user
name of who did the deletion by capturing USER_NAME
HTH
Ryan Waight, MCDBA, MCSE
"Mirna" <mstojsic@.hotmail.com> wrote in message
news:2739901c38f3d$83e5d340$a601280a@.phx.gbl...
> Hi,
> I would like to audit table but I do not want to use
> profiler. I need to know NT user name not just sql server
> standard login for the user. The problem is that somebody
> ocasssionally deletes the data and I would like to find
> out who is doing it.
> Any view is appreciated.
>
I would like to audit table but I do not want to use
profiler. I need to know NT user name not just sql server
standard login for the user. The problem is that somebody
ocasssionally deletes the data and I would like to find
out who is doing it.
Any view is appreciated.You could create a TRIGGER on the table. eg :-
CREATE TRIGGER reminder
ON titles
FOR DELETE
AS
EXEC master..xp_sendmail 'ToYou',
'Someone just deleted a row'
GO
this could easily be enhanced to log to a table, you can capture the user
name of who did the deletion by capturing USER_NAME
HTH
Ryan Waight, MCDBA, MCSE
"Mirna" <mstojsic@.hotmail.com> wrote in message
news:2739901c38f3d$83e5d340$a601280a@.phx.gbl...
> Hi,
> I would like to audit table but I do not want to use
> profiler. I need to know NT user name not just sql server
> standard login for the user. The problem is that somebody
> ocasssionally deletes the data and I would like to find
> out who is doing it.
> Any view is appreciated.
>
Auditing tables
HI!
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swati
swati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>
|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot
>
|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>
|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>
|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table[vbcol=seagreen]
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot
>
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swati
swati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>
|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot
>
|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>
|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>
|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table[vbcol=seagreen]
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot
>
Auditing tables
HI!
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swatiswati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot[vbcol=seagreen]
>|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot[vbcol=seagreen]
>
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swatiswati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot[vbcol=seagreen]
>|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot[vbcol=seagreen]
>
Subscribe to:
Posts (Atom)