newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
RogerComments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>|||Thanks a lot Geoff and Johnny . Appreciate ur reply

newbie question on DTS

Hi,
I am trying to create a DTS package to transfer tables and views from SQL
Server to MySQL, the tables and views I want to transfer do not exist in
MySQL yet. In DTS designer I created the connections to SQL server and
MySQL server without problems, then tried to use "Data Transform Task" to
transfer, the problem I have, "Data Transform Task" tries to map tables
from SQL Server to the tables in MySQL, that defies my whole purpose.
Please help! I am using SQL Server 2000 Developer Edition.
I may be incorrect on this, but I don't think that you can transfer objects
(i.e., table schema) to other vendors' databases.
That would require that the DTS Import/Export Wizard have intimate knowledge
of each vendors permutation of the SQL DDL.
Of course, as inferred above, I haven't attempted to transfer objects to
MySQL. I would expect the DTS Export Wizard to provide me the option to
transfer the table as well as the data IF it were possible.
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
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:eTEI4moBHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am trying to create a DTS package to transfer tables and views from SQL
> Server to MySQL, the tables and views I want to transfer do not exist in
> MySQL yet. In DTS designer I created the connections to SQL server and
> MySQL server without problems, then tried to use "Data Transform Task" to
> transfer, the problem I have, "Data Transform Task" tries to map tables
> from SQL Server to the tables in MySQL, that defies my whole purpose.
> Please help! I am using SQL Server 2000 Developer Edition.
>

newbie question on DTS

Hi,
I am trying to create a DTS package to transfer tables and views from SQL
Server to MySQL, the tables and views I want to transfer do not exist in
MySQL yet. In DTS designer I created the connections to SQL server and
MySQL server without problems, then tried to use "Data Transform Task" to
transfer, the problem I have, "Data Transform Task" tries to map tables
from SQL Server to the tables in MySQL, that defies my whole purpose.
Please help! I am using SQL Server 2000 Developer Edition.I may be incorrect on this, but I don't think that you can transfer objects
(i.e., table schema) to other vendors' databases.
That would require that the DTS Import/Export Wizard have intimate knowledge
of each vendors permutation of the SQL DDL.
Of course, as inferred above, I haven't attempted to transfer objects to
MySQL. I would expect the DTS Export Wizard to provide me the option to
transfer the table as well as the data IF it were possible.
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
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:eTEI4moBHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am trying to create a DTS package to transfer tables and views from SQL
> Server to MySQL, the tables and views I want to transfer do not exist in
> mysql yet. In DTS designer I created the connections to SQL server and
> mysql server without problems, then tried to use "Data Transform Task" to
> transfer, the problem I have, "Data Transform Task" tries to map tables
> from SQL Server to the tables in MySQL, that defies my whole purpose.
> Please help! I am using SQL Server 2000 Developer Edition.
>sql

Newbie Question about parameters

Hi there,

i'm using HTTP to get XML from SQLserver 2000. I need to query the database with parameters. most of them are arrays.

i am however unable to get any data from my database using

WHERE name IN (@.param )

i then tried

exec (' ...

WHERE name IN (' + @.param + ')
')

and still nothing.

Does anybody know what i'm doing wrong?

Thank alot

Wim Horemans :confused:
p.s. in @.param there should be something like "jef, jan, gert, dunno"Your local variable should look like "'jef', 'jan', 'gert', 'dunno'"

and your where caluse should evaluate to

exec (' ...

WHERE name IN ('jef', 'jan', 'gert', 'dunno')
')

Newbie needs help with DBCC Shrinkfile

Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
142409647263409647256
Message posted via http://www.sqlmonster.com
Can you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com
|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.c om...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com

Newbie needs help with DBCC Shrinkfile

Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. When I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
--
Message posted via http://www.sqlmonster.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQLMonster.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.sqlmonster.com

Newbie needs help with DBCC Shrinkfile

Help SQLServer newbie..
Hello. I have a 32gb transaction log that I just backedup with truncate opti
on. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is used. Wh
en I run
use projectserverdev
go
dbcc shrinkfile (ProjectServer_log, 600)
I get the following output.
14 2 4096472 63 4096472 56
Message posted via http://www.droptable.comCan you take a look into Database properties and see what is a initial size
of Transaction Log?
If it is 4000, then this out put is normal as you cannot shrink under
initial size.
Danijel
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com|||Hi,
If that doesnt work, you may need to "wrap" the transaction log file. Run
this script on the database in question, then runn your DBCC command again:
(make sure to verify the database parameters on lines 6 and 8)
SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE ProjectServer -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'ProjectServer_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @.OriginalSize int
SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@.OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@.OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(255)
SELECT @.StartTime = GETDATE(),
@.TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)
EXEC (@.TruncLog)
-- Wrap the log if necessary.
WHILE @.MaxMinutes > DATEDIFF (mi, @.StartTime, GETDATE()) -- time has
not expired
AND @.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName) -- the log has not shrunk
AND (@.OriginalSize * 8 /1024) > @.NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @.Counter = 0
WHILE ((@.Counter < @.OriginalSize / 16) AND (@.Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @.Counter = @.Counter + 1
END -- update
EXEC (@.TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @.LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
"Jigar Lakhani via droptable.com" <forum@.droptable.com> wrote in message
news:1272918166e14bb7a78d12b1b16a30dd@.SQ
droptable.com...
> Help SQLServer newbie..
> Hello. I have a 32gb transaction log that I just backedup with truncate
> option. I wanted to shrink the 32b datafile to 600Megs. Only 481Megs is
> used. When I run
> use projectserverdev
> go
> dbcc shrinkfile (ProjectServer_log, 600)
> I get the following output.
> 14 2 4096472 63 4096472 56
> --
> Message posted via http://www.droptable.com

Newbie Howto:Import, update, delete legacy data to SQLServer db

Hi,
I have a SQL db which I need to update weekly with data from a legacy
db but only where the legacy data has changed. The reason for this is
I have to add a couple of fields to the SQL db and they need to remain
linked to thier respective records.
I'd need to delete from the SQLdb any records absent in the legacy db
but presant in the SQL db, insert any records presant in the legacy db
but absent from the SQLdb and update any records in the SQLdb that
have changed in the legacy db.
Can anyone give me an idea on how an SQL statement to do this would
look. Or any other way of doing this?
Thanks in advance[posted and mailed, please reply in news]

Hitcher (steveChambers66@.hotmail.com) writes:
> I have a SQL db which I need to update weekly with data from a legacy
> db but only where the legacy data has changed. The reason for this is
> I have to add a couple of fields to the SQL db and they need to remain
> linked to thier respective records.
> I'd need to delete from the SQLdb any records absent in the legacy db
> but presant in the SQL db, insert any records presant in the legacy db
> but absent from the SQLdb and update any records in the SQLdb that
> have changed in the legacy db.
> Can anyone give me an idea on how an SQL statement to do this would
> look. Or any other way of doing this?

DELETE target
FROM target t
WHERE NOT EXISTS (SELECT *
FROM source s
WHERE t.keycol = s.keycol)

UPDATE target
SET col1 = s.col1,
col2 = s.col2,
...
FROM target t
JOIN source s ON t.keycol = s.keycol

INSERT target(...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)

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

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

Newbie Help!

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:372757
I work for a company who have just lost the IT Manager, and we are in the
process of appointing another, I am a amature person (normally in sales,
don't hate me). However, we are using Sharepoint POrtal server with a SQL
2000 server for the databases.
There is the companyC1_SITE database which is now showing as suspect after a
server restart, and I notice the LDF is showing 0kb size.
The msdb.mdf is also showing as suspect.
The error log says that the SITE database was not detached cleanly.
I have a backup from the beginning of October, but nothing after that seems
to be valid. So I now must rely on whether or not it will be possible to
recover the corrupt site MDF?
The MDF's are on a RAID array with 270GB free space so the databases did not
become suspect due to space running out.
Any pointers would be very much appreciated.
P.
Your best bet if you want it fixed ASAP and with the least risk of data loss
is to contact MS PSS so they can work directly with you to see what can be
done. Is your data worth about $225.00? I suspect it is and that will be a
small price to pay to get it fixed properly.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"Paul Davies" <bob@.notachance.com> wrote in message
news:_9Kud.6383$Cp4.2422@.fe1.news.blueyonder.co.uk ...
>I work for a company who have just lost the IT Manager, and we are in the
>process of appointing another, I am a amature person (normally in sales,
>don't hate me). However, we are using Sharepoint POrtal server with a SQL
>2000 server for the databases.
> There is the companyC1_SITE database which is now showing as suspect after
> a server restart, and I notice the LDF is showing 0kb size.
> The msdb.mdf is also showing as suspect.
> The error log says that the SITE database was not detached cleanly.
> I have a backup from the beginning of October, but nothing after that
> seems to be valid. So I now must rely on whether or not it will be
> possible to recover the corrupt site MDF?
> The MDF's are on a RAID array with 270GB free space so the databases did
> not become suspect due to space running out.
> Any pointers would be very much appreciated.
> P.
>
|||I agree with Andrew. Call the experts. They can walk you throught the
steps most likely to get your data back. Right after that phone call, find
yourself a temporary IT guy from a reputable consulting company. You will
probably have to pay extra for the emergency coverage, but if your business
depends on your systems, you really don't have a choice.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eTcXXn%233EHA.2592@.TK2MSFTNGP09.phx.gbl...
> Your best bet if you want it fixed ASAP and with the least risk of data
loss
> is to contact MS PSS so they can work directly with you to see what can be
> done. Is your data worth about $225.00? I suspect it is and that will be
a[vbcol=seagreen]
> small price to pay to get it fixed properly.
>
> http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> --
> Andrew J. Kelly SQL MVP
>
> "Paul Davies" <bob@.notachance.com> wrote in message
> news:_9Kud.6383$Cp4.2422@.fe1.news.blueyonder.co.uk ...
after
>

Newbie : How to make Trigger for inserting/updating fields?

Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
HendrickCREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> > from
> > application, however since I new to SQLServer (used to use Access) I
want
> > to
> > insert/update these fields via trigger, the ModifyID should contain the
> > user
> > name logged on to the SQLServer and the time he modify or insert the
row.
> > Could this two insert/update containing in one trigger? Any help would
be
> > highly appreciated. TIA.
> >
> > Hendrick
> >
> >
>|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
> row.
>> > Could this two insert/update containing in one trigger? Any help would
> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>> Hendrick,
>> Also see 'Multirow Considerations' in the SQL Server Books Online.
>> HTH
>> Jerry
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help
>> > would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>>
>

Newbie : How to make Trigger for inserting/updating fields?

Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
HendrickCREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
want[vbcol=seagreen]
row.[vbcol=seagreen]
be[vbcol=seagreen]
>|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> want
> row.
> be
>|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techin...2000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>

Newbie : How to make Trigger for inserting/updating fields?

Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
Hendrick
CREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>
|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
want[vbcol=seagreen]
row.[vbcol=seagreen]
be
>
|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> want
> row.
> be
>
|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>
|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techinf...000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>

Newbie (to Triggers) Trigger Help....

Where can I find good information about how to write triggers? I'm using SQL
Server 2000.
Now, the task at hand: How can I write a trigger that reacts on both insert
and update to keep some fields in two similar (but not the same) tables in
sync. (I need to both insert and update the 2nd table).
Here's some sample DBs (is there a better way to describe the database?):
CREATE TABLE [dbo][tblUserMain](
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userPassword] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[dtLastActive] [datetime] NOT NULL ,
[GUID] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblUserExt] (
[CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userRefBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userProfession] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userBulkMail] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userSubspecialties] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[userTechnologies] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[dtRegDate] [datetime] NOT NULL ,
[userPopQuiz] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userAdvertise] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userBulkMailHtml] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userTitle] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userHomeTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[userWorkTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userDegree] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[needsUpdate] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SavedSearches] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userGroups] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userPersonalize] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userRememberMe] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[middleName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[needsUpdateMessage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[pwdQuestion] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pwdAnswer] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[homePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[language] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mailerEmailInvalid] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I need a trigger so that when the lastName, firstName and bill* fields
change (or insert) in the tblUserExt table they get updated (or inserted)
into the tblUserMain table. These triggers would be temporary until the
tblUserExt table is made obsolete...
Pseudocode--
On Insert,
Update the name and bill* fields in the tblUserMain table (the row will
already exist)
On Update,
Update the name and bill* fields (if they changed) in the tblUserMain
table
TIA,
OwenCREATE TRIGGER Triger_name ON tblUserExt
FOR INSERT
AS
DECLARE @.name NVARCHAR(50)
DECLARE @.billname NVARCHAR(50)
SELECT @.name=name FROM INSERTED
SELECT @.billname=billname FROM INSERTED
INSERT INTO tblUserMain (name,billname) VALUES (@.name,@.billname)
-- or INSERT INTO tblUserMain (the required fields) SELECT
ins.the_same_required_ fileds FROM INSERTED ins
----
CREATE TRIGGER Triger_name ON tblUserExt
FOR UPDATE
AS
DECLARE @.name NVARCHAR(50)
DECLARE @.billname NVARCHAR(50)
DECLARE @.Oldname NVARCHAR(50) --before updated
DECLARE @.Oldbillname NVARCHAR(50)--before updated
SELECT @.name=name FROM INSERTED
SELECT @.billname=billname FROM INSERTED
SELECT @.Oldname=name FROM tblUserExt
SELECT @.Oldbillname=billname FROM tblUserExt
UPDATE tblUserMain
SET name = @.name,
billname = @.billname
WHERE name = @.name AND billname = @.Oldbillname
Note that @.name has to be changed to all the fields like
firstname,lastname,..., same thong for billname.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> a crit dans le message de news:
elI550wVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Where can I find good information about how to write triggers? I'm using
> SQL Server 2000.
> Now, the task at hand: How can I write a trigger that reacts on both
> insert and update to keep some fields in two similar (but not the same)
> tables in sync. (I need to both insert and update the 2nd table).
> Here's some sample DBs (is there a better way to describe the database?):
> CREATE TABLE [dbo][tblUserMain](
> [userID] [int] IDENTITY (1, 1) NOT NULL ,
> [CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userPassword] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [dtLastActive] [datetime] NOT NULL ,
> [GUID] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[tblUserExt] (
> [CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userRefBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userProfession] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userBulkMail] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userSubspecialties] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [userTechnologies] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [dtRegDate] [datetime] NOT NULL ,
> [userPopQuiz] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [orgPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userAdvertise] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userBulkMailHtml] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userTitle] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userHomeTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [userWorkTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userDegree] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [needsUpdate] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SavedSearches] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userGroups] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userPersonalize] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userRememberMe] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middleName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [needsUpdateMessage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [pwdQuestion] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pwdAnswer] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [homePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [language] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [mailerEmailInvalid] [int] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> I need a trigger so that when the lastName, firstName and bill* fields
> change (or insert) in the tblUserExt table they get updated (or inserted)
> into the tblUserMain table. These triggers would be temporary until the
> tblUserExt table is made obsolete...
> Pseudocode--
> On Insert,
> Update the name and bill* fields in the tblUserMain table (the row will
> already exist)
> On Update,
> Update the name and bill* fields (if they changed) in the tblUserMain
> table
> TIA,
> Owen
>|||Regarding: "Here's some sample DBs (is there a better way to describe
the database?)" - What you have provided is fine, but any constraints
(primary key, unique and foreign keys) are also needed. If you
generated this thru Query Analyzer, you can set the options for DDL
generation.
Use the Tools-->Options menu item and then the scripts tab.
Here is part of an update trigger to syncronize the First and Last Name.
This assumes that, in both tables, the column named CN is unique and has
the same value. I have included the comparison logic for First and Last
Name allowing nulls.
CREATE TRIGGER tblUserExt_tua -- Trigger Update After
ON tblUserExt FOR UPDATE
AS
set nocount on
set xact_abort on
-- Check if any rows affected by the command
declare @.Rows integer
SELECT @.Rows = count(*) from inserted
IF @.rows = 0 return
UPDATE dbo.tblUserMain
SET firstName = inserted.firstName
, lastName = inserted.lastName
FROM inserted
WHERE dbo.tblUserMain.CN = inserted.CN
AND ( inserted.firstName <> dbo.tblUserMain.firstName
OR ( inserted.firstName IS NULL
and dbo.tblUserMain.firstName IS NOT NULL
)
OR ( inserted.firstName IS NOT NULL
and dbo.tblUserMain.firstName IS NULL
)
OR inserted.lastName <> dbo.tblUserMain.lastName
OR ( inserted.lastName IS NOT NULL
and dbo.tblUserMain.firstName IS NULL
)
OR ( inserted.lastName IS NULL
and dbo.tblUserMain.firstName IS NOT NULL
)
)
)
go
*** Sent via Developersdex http://www.examnotes.net ***|||On Thu, 12 May 2005 18:20:05 +0100, Berimi wrote:

>CREATE TRIGGER Triger_name ON tblUserExt
>FOR INSERT
>AS
>DECLARE @.name NVARCHAR(50)
>DECLARE @.billname NVARCHAR(50)
>SELECT @.name=name FROM INSERTED
>SELECT @.billname=billname FROM INSERTED
(snip)
Hi Berimi,
This trigger (and the trigger you wrote for UPDATE) will fail as soon as
an insert or update statement is executed that affects more than one
row. And it will fail even worse when a statement is executed that
affects no rows.
Always write triggers that can handle multi-row and zero-row operations!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 12 May 2005 10:30:54 -0700, Carl Federl wrote:
(snip)
>CREATE TRIGGER tblUserExt_tua -- Trigger Update After
> ON tblUserExt FOR UPDATE
If I understand the OP's requirements correct, one trigger can handle
both inserts and updates:
ON tblUserExt FOR INSERT, UPDATE

>-- Check if any rows affected by the command
>declare @.Rows integer
>SELECT @.Rows = count(*) from inserted
>IF @.rows = 0 return
This will waste unnecessary time when 1000s of rows were affected. Use
EXISTS instead:
IF NOT EXISTS (SELECT * FROM inserted) RETURN
Or, better yet, use @.@.ROWCOUNT (at the start of a trigger, this holds
the number of rows affected by the stmt that fired the trigger):
IF @.@.ROWCOUNT = 0 RETURN

>UPDATE dbo.tblUserMain
>SET firstName = inserted.firstName
>, lastName = inserted.lastName
>FROM inserted
>WHERE dbo.tblUserMain.CN = inserted.CN
>AND ( inserted.firstName <> dbo.tblUserMain.firstName
> OR ( inserted.firstName IS NULL
> and dbo.tblUserMain.firstName IS NOT NULL
> )
> OR ( inserted.firstName IS NOT NULL
> and dbo.tblUserMain.firstName IS NULL
> )
> OR inserted.lastName <> dbo.tblUserMain.lastName
> OR ( inserted.lastName IS NOT NULL
> and dbo.tblUserMain.firstName IS NULL
> )
> OR ( inserted.lastName IS NULL
> and dbo.tblUserMain.firstName IS NOT NULL
> )
> )
> )
The test for changed data in nullable columns can be done in a shorter
form. It's less intuitive on first sight, but it saves you lots of lines
of code (important if this has to grow to accomodate 50-odd columns!),
and it's easy once you get used to it:
UPDATE u
SET firstName = i.firstName
, lastName = i.lastName
FROM dbo.tblUserMain AS u
INNER JOIN inserted AS i
ON i.CN = u.CN
WHERE ( NULLIF (i.firstName, u.firstName) IS NOT NULL
OR NULLIF (u.firstName, i.firstName) IS NOT NULL)
AND ( NULLIF (i.lastName, u.lastName) IS NOT NULL
OR NULLIF (u.lastName, i.lastName) IS NOT NULL)
Of course, the check for unchanged data could be left out completely.
Without it, finding the rows to operate on would be much quicker, at the
cost of possibly updating the values in some rows to the values they
already had. To prevent updating when no first or last names have been
changed, you can add (before the UPDATE statement):
IF UPDATE(FirstName) OR UPDATE(LastName)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You're right Hugo,
Thanks,
T.Berimi
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> a crit dans le message de
news: l5l781lt68ctijp3ov37rjv7cd0m5l1ifu@.4ax.com...
> On Thu, 12 May 2005 18:20:05 +0100, Berimi wrote:
>
> (snip)
> Hi Berimi,
> This trigger (and the trigger you wrote for UPDATE) will fail as soon as
> an insert or update statement is executed that affects more than one
> row. And it will fail even worse when a statement is executed that
> affects no rows.
> Always write triggers that can handle multi-row and zero-row operations!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie - connection with a login not declared on the server

Hi,

I'm using a SQLServer 7 SP3.

I have noticed a connection of a login which is not declared on the
server (in NT authentification and SQLServer authentification).
I'm using the mixed mode authentification.
May be the login is affected to a administrator group.

Have you an idea of the way I could find why this person could login ?

FYA
RJPIf this is a domain user, using windows authentication he can log into the
system. If you are using SQL Server authentication/mixed mode you'll have to
add this user as a login to the Server.

--
- Anith
( Please reply to newsgroups only )

Newbee Table question

I am an MS Access developer who is writing his first SQL
Server app.
In MsAccess we have the AutoNumber data type to give
records a unique sequential value. There doesn't appear to
be an equivalent type in SQL Server.
I am adding records from a ASP.Net web page. What would be
the conventional means of giving each new record a
sequential value? I have gotten around this by querying
the table to find the value in the last record then adding
1 to it. I can't believe that there isn't a neater way to
achieve this.You can use something called an "identity" column:
CREATE TABLE #Table (SomeValue VARCHAR(20), AutoNum INT IDENTITY(1,1))
INSERT #Table VALUES ('A')
INSERT #Table VALUES ('B')
INSERT #Table VALUES ('C')
SELECT * FROM #Table ORDER BY SomeValue
A 1
B 2
C 3
"Ian Pendlebury" <anonymous@.discussions.microsoft.com> wrote in message
news:696f01c405c1$3a868d20$a601280a@.phx.gbl...
> I am an MS Access developer who is writing his first SQL
> Server app.
> In MsAccess we have the AutoNumber data type to give
> records a unique sequential value. There doesn't appear to
> be an equivalent type in SQL Server.
> I am adding records from a ASP.Net web page. What would be
> the conventional means of giving each new record a
> sequential value? I have gotten around this by querying
> the table to find the value in the last record then adding
> 1 to it. I can't believe that there isn't a neater way to
> achieve this.
>|||What is to be done in Sql Server to have an autonumber field
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Look at the IDENTITY() function
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"saurabh" <s_saur79@.yahoo.co.in> wrote in message
news:OzSjUpaEEHA.3408@.tk2msftngp13.phx.gbl...
> What is to be done in Sql Server to have an autonumber field
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Newb question

If I am using Ado.Net from a .Net Class library hosted outside SqlServer to do Sends and Receives do I have to use anything else other than queues? The reason I am asking is because I already have an abstraction layer for a couple of our queueing systems and I was hoping to put SSB in there as well but the abstraction layers' primitive is the Queue not Conversation/Dialog/Service, etc.

Thanks

CostasWhile you could build a layer that exposes just queues, you will soon discover that it will not be possible to expose all the richness of Service Broker (i.e. conversations, conversation group locking, etc) in that manner. But if you need to keep the existing interface in order to port your app over, you could certainly build a layer based on Queues like you describe.|||Rushi, thanks. The reason I asked is that almost all the examples I've seen use conversation where using the queues. Can you point me to an example where I can do send & receive solely with queues?

Thanks

Costas|||OK, I am getting the idea that I cannot just "send" a message to a queue. I think I have to create a queue and a service to go with it. Now, if my client needs to "send" on a queue, can I fake it with 1 stored procedure that takes the service name as a parameter?

Does this look reasonable?

create message type
[http://www.myblobcom/msg/BlobMsg]
validation = NONE;

create contract [http://www.myblobcom.com/contract/SaveBlob/v1.0]
(
[http://www.myblobcom.com/msg/BlobMsg] sent by initiator
);

create queue [Blob Queue];

create service [BlobSvc]
on queue [Blob Queue]
(
[http://www.myblobcom.com/contract/SaveBlob/v1.0]
);

CREATE PROCEDURE SaveBlob (

@.service varchar(36),

@.queue varchar(36),

@.payload VARBINARY(MAX))
AS BEGIN
declare @.dh uniqueidentifier;

-- HERE IS WHERE I HAVE MY CONCEPTUAL PROBLEM
-- HOW DO I BEGIN A DIALOG WITH A STORED PROCEDURE
-- CALLED BY AN EXTERNAL ADO.NET CLIENT?
-- begin dialog @.dh
-- from service [Inventory Client]
-- to service 'Inventory'
on contract [http://www.myblobcom.com/contract/SaveBlob/v1.0]
with encryption=off;

send on conversation @.dh message type [http://www.myblobcom/msg/BlobMsg] (@.payload );

end

Thanks

Costas|||Hi, can someone tell me if I am on the right track?

Thanks

Costas|||

Uncomment the begin dialog lines. Use the @.service parameter as the to service in that statement. That will allow you to begin a dialog and send a message in your stored proc. This proc can be called from ADO.Net.

|||

Rushi, thanks, I am not sure what to do with the [from service]. What's the from service set to when you're called from an extarnal ADO.NET client?

Cheers

Costas

|||

Dialog conversations are persistent sessions between two services -- the initiator and the target service. When beginning a dialog, you must specify the initiator service in the from argument and the target service in the to argument. Even if you use dialogs for one-way messaging, you still need a 'from' service where errors could be sent back as reponses. Each service has its own queue which serves as a backing store where messages are delivered until the service receives them.

Refer to Books Online to understand more about Service Broker architecture. You could also get a copy of Roger Wolter's book on Service Broker.

|||Rushi, thanks, I think that's where my ability to comprehend the service broker external call breaks down. All samples, articles, etc discuss it from the point of view of internal services talking to each other through dialogs which is not what I want to do or is it? Are you implying that my "from" service is really just a placeholder in my case just so that the dialog can take place? So, I'd call the stored proc from ado.net which will send a message in the queue from what I can call the "externalactivationservice" and the message will be sent to my handle blob service.

I am going to give it a shot, thanks

Costas