query

create table a
(
i varchar(10),
j varchar(11)
)
create table b
(
n varchar(10),
m varchar(11)
)
insert into a values(null, '1')
insert into a values(null, '2')
insert into b values(null, '3')
insert into b values(null, '4')
insert into a values('val1', '5')
insert into a values('val2', '6')
insert into b values('val3', '7')
insert into b values('val4', '8')
select * From a
inner join b on a.i = b.n
and a.i is null and b.n is null
hi i want to join table a and b and return the results
null, 1
null, 2
null, 3
null, 4
but i dont get this output. how do i do that?
thnx
ICHORThe query below should return the result you are after:
SELECT i, j
FROM a
WHERE i IS NULL
UNION
SELECT n, m
FROM b
WHERE n IS NULL
When you perform an INNER JOIN you only return the rows that satisfiy the
join of the first input with the second input. As a NULL is an unknown valu
e
NULL does not equal NULL hence the NULL records do not satisfy the join and
are not returned.
- Peter Ward
WARDY IT Solutions
"ichor" wrote:

> create table a
> (
> i varchar(10),
> j varchar(11)
> )
>
> create table b
> (
> n varchar(10),
> m varchar(11)
> )
> insert into a values(null, '1')
> insert into a values(null, '2')
> insert into b values(null, '3')
> insert into b values(null, '4')
>
> insert into a values('val1', '5')
> insert into a values('val2', '6')
> insert into b values('val3', '7')
> insert into b values('val4', '8')
>
> select * From a
> inner join b on a.i = b.n
> and a.i is null and b.n is null
>
> hi i want to join table a and b and return the results
>
> null, 1
> null, 2
> null, 3
> null, 4
> but i dont get this output. how do i do that?
> thnx
> ICHOR
>
>

Query

I'm trying to find a way to create a query to obtain the last 30 hours ran
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance on
both cases to see which one bring the data faster.
Thanks: Cesar Sandoval
There are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>
sql

Query

Hello folks,

Im new in database bussines. I will appreciate if someone can help me.
I need to insert new records to a table. First of all I dont know what is the syntax for insert command in SQL Server2000. Also one of the fields to insert is changing every second so I think I will need to include in the insert command a nested select statement.
For example,

The table has the ID field and this field is the one is changing every time. If in the n second I do a select to know what value such field has I could use the value equal to ID + 1. But while I build query, type and execute it will be the second n+1, so probably the value for ID now is already equal to ID + 1 and my query will fail because the duplicated record error.

I hope is clear what the proble I have,

Regards,You can look in BooksOnline (comes with the sql server) for the syntax on insert statements.
If your changing field is the ID for a record, you can use auto numbering on this field. This means that sql server itself will keep track of the value of the field and makes sure there are no duplicates.

hth|||So can I just use:

insert into table_name values(ID,'MNAME','F_NAME')...

I was thinking that some other query was needed to be introduced to get the new value for ID field, ie.

insert into table_name values(((select ID from TABLE)+1),'M_NAME','F_NAME')

Thanks|||Not completely correct yet, your insert statement. If the ID column is set to auto numbering, you don't include it in the insert statement. Your statement will be:
insert into table_name values('MNAME','F_NAME')|||Thanks, How I can know if the field is autonumbering?

How I can get the table fields with all it properties using SQL Command?

In db2 it was with command describe table...
It is the same?

Regards,|||in sql server, there is a stored proeedure for this information called 'sp_columns'. See BOL for more information. You can also find information about using auto numbering in BOL, just search on 'identity'.sql

query

drop table share
create table share
(
shid int identity ,
dt datetime,
price int
)
insert into share(dt,price) values('1-jan-2006',10)
insert into share(dt,price) values('2-jan-2006',11)
insert into share(dt,price) values('6-jan-2006',15)
insert into share(dt,price) values('4-jan-2006',9)
select * from share
output i want is
the difference in share prices from the previous day.
so
1 (11-10)
4 (15-11)
-6 (9-15)
select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
p2.price p2price
from
(select * from share
) as p2,
share p1
where p1.shid < p2.shid
order by p1.shid, p2.shid
thisis the closest i could come up with but it has duplicates.Try,
select b.price - a.price
from t1 as a inner join t1 as b
on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
[shid] then try:
select b.price - a.price
from t1 as a inner join t1 as b
on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
AMB
"ichor" wrote:

> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||u r right. sorry for the mistake.
the query works fine.
the first query is what i was after.
i guess my approach of writing the query was wrong
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
> Try,
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
> Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
> [shid] then try:
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
>
> AMB
> "ichor" wrote:
>|||I think this should work:
select shid, dt, price - isnull((Select price from share b where b.shid =
a.shid - 1), 0) from share a
Hope this help...
Ed
"ichor" wrote:

> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||can u explain why we need a b and c?
in the query?
"ichor" <ichor@.hotmail.com> wrote in message
news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>u r right. sorry for the mistake.
> the query works fine.
> the first query is what i was after.
> i guess my approach of writing the query was wrong
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
>|||We need to join a tabla to itself, so we need to alias the second reference
to it (b). The join condition is between a row in one side (b) and the a row
in the other side (a) where its [dt] value (in a) is equal to the previous of
current in b (the max([c.dt]) where [dt] < b.[dt]).
AMB
"ichor" wrote:

> can u explain why we need a b and c?
> in the query?
> "ichor" <ichor@.hotmail.com> wrote in message
> news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>
>|||could we have done this using only a and b and no c?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3AD01065-EBAA-4AAE-990C-E1F29FB8BD9B@.microsoft.com...
> We need to join a tabla to itself, so we need to alias the second
> reference
> to it (b). The join condition is between a row in one side (b) and the a
> row
> in the other side (a) where its [dt] value (in a) is equal to the previous
> of
> current in b (the max([c.dt]) where [dt] < b.[dt]).
>
> AMB
> "ichor" wrote:
>

Query

I'm trying to find a way to create a query to obtain the last 30 hours ran
for every single part in the table and insert this data into new tabel
Here is an example: Assuming that we need to extract the last 30 ran hours
for every single part in Table A and Insert them into Table B
Here is the data from table A
Record ID Part # HrsRan Date
1 ABC 23 8/1/04
2 DBC 15 8/1/04
3 ABC 20 8/2/04
4 ABC 22 8/2/04
5 123 15 8/3/04
6 123 40 8/3/04
7 DBC 30 8/3/04
8 DBC 15 8/4/04
9 ABC 10 8/4/04
10 DBC 20 8/4/04
11 DBC 10 8/5/04
12 123 20 8/5/04
Then the record that the query should extract should be those records for
the last 30 ranhrs then the results should be the following
Table B
Record ID Part HrsRan Date
12 123 20 8/5/04
6 123 10 8/3/04
11 DBC 10 8/5/04
10 DBC 20 8/4/04
9 ABC 10 8/4/04
4 ABC 20 8/2/04
As you could see the system drop the following records ID
1 ,2,3,5,7,8
I'll only need to write ID record in the new table then use this ID against
the history data, this will save disk space or maybe just dump all the
necesary data into the new table and save performance time. If I found a way
to create a Procedure, Query, DTS to create this I'll test the performance o
n
both cases to see which one bring the data faster.
Thanks: Cesar SandovalThere are probably better ways to accomplish this but the query below should
get you started.
CREATE TABLE TableA
(
RecordID int NOT NULL,
PartNumber char(3) NOT NULL,
HrsRan int NOT NULL,
RunDate smalldatetime NOT NULL
)
GO
INSERT INTO TableA VALUES(1, 'ABC', 23, '20040801')
INSERT INTO TableA VALUES(2, 'DBC', 15, '20040801')
INSERT INTO TableA VALUES(3, 'ABC', 20, '20040802')
INSERT INTO TableA VALUES(4, 'ABC', 22, '20040802')
INSERT INTO TableA VALUES(5, '123', 15, '20040803')
INSERT INTO TableA VALUES(6, '123', 40, '20040803')
INSERT INTO TableA VALUES(7, 'DBC', 30, '20040803')
INSERT INTO TableA VALUES(8, 'DBC', 15, '20040804')
INSERT INTO TableA VALUES(9, 'ABC', 10, '20040804')
INSERT INTO TableA VALUES(10, 'DBC', 20, '20040804')
INSERT INTO TableA VALUES(11, 'DBC', 10, '20040805')
INSERT INTO TableA VALUES(12, '123', 20, '20040805')
GO
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
CASE WHEN a.HrsRanRunningTotal > 30
THEN 30 - (a.HrsRanRunningTotal - a.HrsRan)
ELSE a.HrsRan END AS HrsRan,
a.HrsRanRunningTotal
FROM
(
SELECT
a.RecordId,
a.RunDate,
a.PartNumber,
a.HrsRan,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.RecordId = a.RecordId OR
(b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId > a.RecordId)))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
JOIN
(
SELECT
PartNumber,
MIN(HrsRanRunningTotal) AS HrsRanRunningTotal
FROM
(
SELECT
a.PartNumber,
SUM(b.HrsRan) AS HrsRanRunningTotal
FROM TableA a
JOIN TableA b ON
b.PartNumber = a.PartNumber AND
(b.RunDate > a.RunDate OR
(b.RunDate = a.RunDate AND b.RecordId >= a.RecordId))
GROUP BY
a.PartNumber,
a.RunDate,
a.RecordId,
a.HrsRan
) AS a
WHERE HrsRanRunningTotal >= 30
GROUP BY
PartNumber
) AS b ON
b.PartNumber = a.PartNumber AND
b.HrsRanRunningTotal >= a.HrsRanRunningTotal
ORDER BY
a.PartNumber,
a.RunDate DESC
Hope this helps.
Dan Guzman
SQL Server MVP
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:692430D8-D7B6-44EA-B758-1A27101F9A4B@.microsoft.com...
> I'm trying to find a way to create a query to obtain the last 30 hours ran
> for every single part in the table and insert this data into new tabel
> Here is an example: Assuming that we need to extract the last 30 ran hours
> for every single part in Table A and Insert them into Table B
> Here is the data from table A
> Record ID Part # HrsRan Date
> 1 ABC 23 8/1/04
> 2 DBC 15 8/1/04
> 3 ABC 20 8/2/04
> 4 ABC 22 8/2/04
> 5 123 15 8/3/04
> 6 123 40 8/3/04
> 7 DBC 30 8/3/04
> 8 DBC 15 8/4/04
> 9 ABC 10 8/4/04
> 10 DBC 20 8/4/04
> 11 DBC 10 8/5/04
> 12 123 20 8/5/04
> Then the record that the query should extract should be those records for
> the last 30 ranhrs then the results should be the following
> Table B
> Record ID Part HrsRan Date
> 12 123 20 8/5/04
> 6 123 10 8/3/04
> 11 DBC 10 8/5/04
> 10 DBC 20 8/4/04
> 9 ABC 10 8/4/04
> 4 ABC 20 8/2/04
>
> As you could see the system drop the following records ID
> 1 ,2,3,5,7,8
> I'll only need to write ID record in the new table then use this ID
against
> the history data, this will save disk space or maybe just dump all the
> necesary data into the new table and save performance time. If I found a
way
> to create a Procedure, Query, DTS to create this I'll test the performance
on
> both cases to see which one bring the data faster.
> Thanks: Cesar Sandoval
>sql

Queries going to Suspend state with wait type ASYNC_NETWORK_IO

Recently we observed a problem. We are running stored procedure through our c# code. Three machines access the server and update or insert in the required tables in the server. If there is no data in the server, on installing first time our application usually our database is clean. then stored procedure works fine, it takes around 10 to 15 sec to execute. Next time if execute the time goes up to minutes like 15 mins. Next time it goes for hours around 4 hrs. Even to update 4 or 5 records it takes time. Initially we thought it was because of the size of the data and we tried to re tune on indexes, it did not solve. But now what we observe is even with less number of records in server also it wouldn't come of the execution for hours.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.

When we comment one of the query is working fine.
Is this something to do with the query I am not sure. If that is the case it should not work every time.

The query makes any sence or is there any way to write it in better way

'UPDATE [server].[dbo].[DocumentMetadata] SET DocumentInfoID = b.DocumentInfoID, [Name] = b.[Name], MetadataType = b.MetadataType,
[Value] = b.[Value], ValueType = b.ValueType
FROM [server].[dbo].[DocumentMetadata] a WITH (UPDLOCK)
INNER JOIN (SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MACHINENAME.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + ''') b
ON a.DocumentInfoID = b.DocumentInfoID AND a.[Name] = b.[Name]'

'INSERT INTO [server].[dbo].[DocumentMetadata]
(DocumentInfoID, [Name], MetadataType, [Value], ValueType)
SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MYCLI.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + '''
AND Ltrim(rtrim(c.DocumentInfoID))+ ltrim(rtrim(c.[Name])) NOT IN
(SELECT Ltrim(rtrim(DocumentInfoID))+ ltrim(rtrim([Name])) FROM [server].[dbo].[DocumentMetadata])'

We have been fighting it out for so many days.
Can anybody help

Thanks
knvdssr

ASYNC_NETWORK_IO means SQL server is waiting for network...

How may rows your select statement for INSERT it is returning?

You are using ltrim and rtrim functions against column which result a table scan and it can't use index...

Try to change the query or just comment the query and test it to make it is the issue.

|||It appears you are using a LINKED SERVER to access another server?

If this is the case, try looking at activity monitor on the source. I would suspect you are getting a deadlock or lock problem or something on the linked server.

The wait you are seeing indicates it is waiting on the linked server to finish the query and send records.

|||When you use 4-part name, all of the data from the remote server is pulled across before the join is formulated. If the remote table is large, you will have to wait for a long time for all the data to cross over. Try using openquery() to only pull/update the necessary remote data.

You can lookup some examples here:
http://groups.google.com/groups?q=sqlserver%20oj%20openquery%20update|||

We commented the query which I mentioned earlier, to see if that one is causing the problem. Then the whole Stored procedure finished executing in minutes.

We changed the the query with our using the NOT IN in that query.

Now first time it is taking only 10 minutes for more than 100,000 records.

But the second one is not coming out at all. This time the server is having lot of rows because is already updated once.

All that we see is the process in suspend state or comes out as timed out.

If I am getting a dead lock on the linked server is there any way to solve it?

Could any body help please

|||

Again, take a look at openquery/openrowset. Bringing the entire resultset back is not recommended when the remote table is large.

QA limitation or simply I am doing anything wrong?

Dear all,
I am trying do a autocontained select but on the results panel appears the
line cut off:
select 'insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia,responsable,
dias,casoerror,servdesa,docu,marcaff
echa,ficherofecha,ficheronu
l,enespera,ana)
values(' + convert(char(2),idproceso) + ')'
from cargaprocesos where proceso in('ABS_RecuperarCajero')
As ouput:
----
----
----
--
-
insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia responsable,dias,casoerror,servdesa,docu
,marcaff
e
(1 filas afectadas)
stopped in "marcaffe" field.
Does anyone has suffered any experience with that? Is customizable?
Regards,Change the below setting.
Tools --> Options --> Results --> Maximum Character Per column.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:5619C2E7-B22F-4B59-AD0C-1FCF0B9595FD@.microsoft.com...
> Dear all,
> I am trying do a autocontained select but on the results panel appears the
> line cut off:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero')
>
> As ouput:
>
> ----
----
----
--
--
> insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimaca
rga,nota,dts,repositorio,servdts,idaplic
acion,duracionult,duracionrel,marcaf
nul,marcabak,horas,frecuencia
> responsable,dias,casoerror,servdesa,docu
,marcaffe
> (1 filas afectadas)
> stopped in "marcaffe" field.
> Does anyone has suffered any experience with that? Is customizable?
> Regards,|||Hi
The problem is with the buffer size:
just goto Tools > Options
on the 'Results' tab, change the maximum characters per column from 256 to
some 8000.
the u can see the expected result:
aslo modift the query as:
select 'insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia,responsable,
dias,casoerror,servdesa,docu,marcaff
echa,ficherofecha,ficheronu
l,enespera,ana)
values(' + convert(char(2),idproceso) + ')'
from cargaprocesos where proceso in('ABS_RecuperarCajero') as [Result]
to avoid the - across 3 lines
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Enric" wrote:

> Dear all,
> I am trying do a autocontained select but on the results panel appears the
> line cut off:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero')
>
> As ouput:
>
> ----
----
----
--
--
> insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimaca
rga,nota,dts,repositorio,servdts,idaplic
acion,duracionult,duracionrel,marcaf
nul,marcabak,horas,frecuencia responsable,dias,casoerror,servdesa,docu
,marca
ffe
> (1 filas afectadas)
> stopped in "marcaffe" field.
> Does anyone has suffered any experience with that? Is customizable?
> Regards,|||Thanks a lot Roji
"Roji. P. Thomas" wrote:

> Change the below setting.
> Tools --> Options --> Results --> Maximum Character Per column.
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:5619C2E7-B22F-4B59-AD0C-1FCF0B9595FD@.microsoft.com...
ronul,enespera,ana)
--
>
>|||oops,
select 'insert into
cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarg
a,nota,dts,repositorio,servdts,idaplicac
ion,duracionult,duracionrel,marcafnu
l,marcabak,horas,frecuencia,responsable,
dias,casoerror,servdesa,docu,marcaff
echa,ficherofecha,ficheronu
l,enespera,ana)
values(' + convert(char(2),idproceso) + ')' as [Result]
from cargaprocesos where proceso in('ABS_RecuperarCajero')
I made a mistake in the query
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Chandra" wrote:

> Hi
> The problem is with the buffer size:
> just goto Tools > Options
> on the 'Results' tab, change the maximum characters per column from 256 to
> some 8000.
> the u can see the expected result:
>
> aslo modift the query as:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero') as [Result]
> to avoid the - across 3 lines
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Enric" wrote:
>
ronul,enespera,ana)
--|||hi Chandra, thanks for your input but it doesn't works:
using as [result]
Servidor: mensaje 156, nivel 15, estado 1, l_nea 6
Incorrect syntax near the keyword 'as'.
"Chandra" wrote:

> Hi
> The problem is with the buffer size:
> just goto Tools > Options
> on the 'Results' tab, change the maximum characters per column from 256 to
> some 8000.
> the u can see the expected result:
>
> aslo modift the query as:
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')'
> from cargaprocesos where proceso in('ABS_RecuperarCajero') as [Result]
> to avoid the - across 3 lines
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Enric" wrote:
>
ronul,enespera,ana)
--|||It's ok. Take care yourself,
"Chandra" wrote:

> oops,
> select 'insert into
> cargaprocesos(idproceso,proceso,ruta,res
pfich,rutabak,estado,fechaultimacarga,no
ta
,dts,repositorio,servdts,idaplicacion,du
racionult,duracionrel,marcafnul,marcabak
,hor
as,frecuencia,responsable,dias,casoerror
,servdesa,docu,marcaffecha,ficherofecha,
fich
ero
nul,enespera,ana)
> values(' + convert(char(2),idproceso) + ')' as [Result]
> from cargaprocesos where proceso in('ABS_RecuperarCajero')
> I made a mistake in the query
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Chandra" wrote:
>
ronul,enespera,ana)
heronul,enespera,ana)
--

q; user running trigger

Is it possible fire an insert trigger in the context of a different user than
the user that does the insert.
In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
statement so security context other than the current user. However, in all
SQL Server versions, permission checking on objects referenced in the
trigger are bypassed as long as the ownership chain is unbroken. This means
that you only need to be concerned about the trigger security context if you
access objects owned by principals, use dynamic SQL or access objects
external to the current database.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
> Is it possible fire an insert trigger in the context of a different user
> than
> the user that does the insert.
|||We are using SQL2000. User insert table in one database T1 and trigger insert
the record into another database T2. In this scenario could you please be
more clear if I could use any user in T2 to be able to do this insertion.
Users in T1 should not be able to insert anything into T2.
"Dan Guzman" wrote:

> In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
> statement so security context other than the current user. However, in all
> SQL Server versions, permission checking on objects referenced in the
> trigger are bypassed as long as the ownership chain is unbroken. This means
> that you only need to be concerned about the trigger security context if you
> access objects owned by principals, use dynamic SQL or access objects
> external to the current database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
>
|||You might consider using cross-database ownership chaining to address your
security requirement. Assuming all objects are owned by dbo, you can do
this as follows:
1) Make sure both databases are owned by the same login. Execute
sp_changedbowner if necessary.
2) Make sure users are added to both databases so that they have a security
context. However, those users do not need object permissions in the second
database. Alternatively, you can enable the guest user in the second
database.
3) Enable the cross-database chaining ('db chaining' database option) for
both databases.
Note that you should understand the security implications of cross-database
chaining as described in the Books Online. Cross-database chaining should
be enabled in an sa-owned database when only sysadmin role members can
create dbo-owned objects in those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6EBDA8BD-9B96-4503-A1C8-95E8D872E34F@.microsoft.com...[vbcol=seagreen]
> We are using SQL2000. User insert table in one database T1 and trigger
> insert
> the record into another database T2. In this scenario could you please be
> more clear if I could use any user in T2 to be able to do this insertion.
> Users in T1 should not be able to insert anything into T2.
> "Dan Guzman" wrote:

q; user running trigger

Is it possible fire an insert trigger in the context of a different user tha
n
the user that does the insert.In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
statement so security context other than the current user. However, in all
SQL Server versions, permission checking on objects referenced in the
trigger are bypassed as long as the ownership chain is unbroken. This means
that you only need to be concerned about the trigger security context if you
access objects owned by principals, use dynamic SQL or access objects
external to the current database.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
> Is it possible fire an insert trigger in the context of a different user
> than
> the user that does the insert.|||We are using SQL2000. User insert table in one database T1 and trigger inser
t
the record into another database T2. In this scenario could you please be
more clear if I could use any user in T2 to be able to do this insertion.
Users in T1 should not be able to insert anything into T2.
"Dan Guzman" wrote:

> In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
> statement so security context other than the current user. However, in al
l
> SQL Server versions, permission checking on objects referenced in the
> trigger are bypassed as long as the ownership chain is unbroken. This mea
ns
> that you only need to be concerned about the trigger security context if y
ou
> access objects owned by principals, use dynamic SQL or access objects
> external to the current database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
>|||You might consider using cross-database ownership chaining to address your
security requirement. Assuming all objects are owned by dbo, you can do
this as follows:
1) Make sure both databases are owned by the same login. Execute
sp_changedbowner if necessary.
2) Make sure users are added to both databases so that they have a security
context. However, those users do not need object permissions in the second
database. Alternatively, you can enable the guest user in the second
database.
3) Enable the cross-database chaining ('db chaining' database option) for
both databases.
Note that you should understand the security implications of cross-database
chaining as described in the Books Online. Cross-database chaining should
be enabled in an sa-owned database when only sysadmin role members can
create dbo-owned objects in those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6EBDA8BD-9B96-4503-A1C8-95E8D872E34F@.microsoft.com...[vbcol=seagreen]
> We are using SQL2000. User insert table in one database T1 and trigger
> insert
> the record into another database T2. In this scenario could you please be
> more clear if I could use any user in T2 to be able to do this insertion.
> Users in T1 should not be able to insert anything into T2.
> "Dan Guzman" wrote:
>

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?
A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>
|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transactio
n
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?
SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possibl
e
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:

> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>
|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
> > Apparently I could not insert a text field to another table from INSERTED in
> > a trigger.
> > It seems the following is working, do you see any problem joining INSERTED
> > to the mySrcTable which is the table that has this trigger
> >
> > INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> > SELECT i.myTrID, i.myFirstName, p.myBigText
> > FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> > WHERE (i.myTrType = 'In')
> >
> > Thanks,
> >

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the origina
l
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:

> Apparently I could not insert a text field to another table from INSERTED
in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigT
ext)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if th
e
> compatibility level is equal to 70. The text, ntext, and image values in t
he
> inserted and deleted tables cannot be accessed. To retrieve the new value
in
> either an INSERT or UPDATE trigger, join the inserted table with the origi
nal
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update o
f
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
>

q; loop for insert into

Hello,
I have 10 tables (T1Orj,T2Orj,…T10Orj) and I need to find modified rows fr
om
each table and insert them to T1Bak, T2Bak, …T10Bak. Although original and
bak tables have the common fields , the original tables have more fields tha
n
bak tables, and T1Orj, T2Orj, … T10Orj tables have different table structu
res.
I can go head and write a insert into query for each table, I am just
wondering Is there any way I can do this in a loop for all tables?"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> Hello,
> I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> from
> each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> bak tables have the common fields , the original tables have more fields
> than
> bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> structures.
> I can go head and write a insert into query for each table, I am just
> wondering Is there any way I can do this in a loop for all tables?
>
Do you mean the Orj tables all have the same columns? Why? It would be a lot
simpler to combine them into one. Duplicating tables is almost always a
mistake.
If they are all different then I don't understand how you expect a loop
would help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply, some type of loop might help me if I can get column
names and table names created dynamically from a table. Then I will perform
only one insert in my code.
"David Portas" wrote:

> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> Do you mean the Orj tables all have the same columns? Why? It would be a l
ot
> simpler to combine them into one. Duplicating tables is almost always a
> mistake.
> If they are all different then I don't understand how you expect a loop
> would help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

q; loop for insert into

Hello,
I have 10 tables (T1Orj,T2Orj,â?¦T10Orj) and I need to find modified rows from
each table and insert them to T1Bak, T2Bak, â?¦T10Bak. Although original and
bak tables have the common fields , the original tables have more fields than
bak tables, and T1Orj, T2Orj, â?¦ T10Orj tables have different table structures.
I can go head and write a insert into query for each table, I am just
wondering Is there any way I can do this in a loop for all tables?"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> Hello,
> I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> from
> each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> bak tables have the common fields , the original tables have more fields
> than
> bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> structures.
> I can go head and write a insert into query for each table, I am just
> wondering Is there any way I can do this in a loop for all tables?
>
Do you mean the Orj tables all have the same columns? Why? It would be a lot
simpler to combine them into one. Duplicating tables is almost always a
mistake.
If they are all different then I don't understand how you expect a loop
would help you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply, some type of loop might help me if I can get column
names and table names created dynamically from a table. Then I will perform
only one insert in my code.
"David Portas" wrote:
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> > Hello,
> > I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> > from
> > each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> > bak tables have the common fields , the original tables have more fields
> > than
> > bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> > structures.
> > I can go head and write a insert into query for each table, I am just
> > wondering Is there any way I can do this in a loop for all tables?
> >
> Do you mean the Orj tables all have the same columns? Why? It would be a lot
> simpler to combine them into one. Duplicating tables is almost always a
> mistake.
> If they are all different then I don't understand how you expect a loop
> would help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>