Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

Can someone point me in the right direction?

Used Trigger instead of SP

http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp

thanks,Arnie Rowland

http://forums.microsoft.com/technet/ShowPost.aspx?postid=1199319&isthread=true&siteid=17&authhash=bf39638b468336d0c6fe8e12045e034125b540e1&ticks=633064241797780343

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

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

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Trigger -Audit Trail
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

sql

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

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

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Trigger -Audit Trail
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...[vbcol=seagreen]
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...[vbcol=seagreen]
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> Hi,
>> I'm new using Application Role. I use SQL Query Analyzer to try some
>> example.
>> I have create a Application role call "ABC" and have set permission to
>> all
>> tables & stored procedures.
>> In Query Analyzer, after setting the role using sp_setapprole,
>> i) I try to run the stored procedure but it give me 0 result. (it
>> should return me some records which it doesn't)
>> ii) I try to run a select statement for a table, it return me all
>> records.
>> Since Application Role allows me to set permission for Stored Procedures
>> &
>> tables, I thought both should be the same.
>> Does anyone know why is this so?
>> Regards
>> Vanessa
>>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> >
> > Do you mean that ii is working and i is not? Do you receive any errors?
> >
> > Try executing the stored procedure code manually.
> >
> > Or post both i and ii code here.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Vanessa" wrote:
> >
> >> Hi,
> >>
> >> I'm new using Application Role. I use SQL Query Analyzer to try some
> >> example.
> >>
> >> I have create a Application role call "ABC" and have set permission to
> >> all
> >> tables & stored procedures.
> >>
> >> In Query Analyzer, after setting the role using sp_setapprole,
> >> i) I try to run the stored procedure but it give me 0 result. (it
> >> should return me some records which it doesn't)
> >> ii) I try to run a select statement for a table, it return me all
> >> records.
> >>
> >> Since Application Role allows me to set permission for Stored Procedures
> >> &
> >> tables, I thought both should be the same.
> >>
> >> Does anyone know why is this so?
> >>
> >> Regards
> >> Vanessa
> >>
> >>
> >>
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> For stored procedure, i did not receive any errors but it return me zero
>> result. If i didn't set application role and run the SP, it return me 55
>> rows of record.
>> In the Query Analyzer, the statement i run is
>> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
>> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
>> Step 2 Normal Select Statement) select * from aneadm.tblvisit
>> The SP code is
>>
>> CREATE PROCEDURE aneadm.uspGetAppAllSetting
>> AS
>> -- version 1.0
>> SET NOCOUNT ON
>> DECLARE @.ReturnCode INTEGER
>> DECLARE @.IsActiveBit BIT
>> DECLARE @.Site VARCHAR(80)
>> DECLARE @.SiteID INTEGER
>> SELECT @.ReturnCode = 1 -- not ok
>> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
>> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
>> DECLARE @.tSettingDesc VARCHAR(255)
>> SELECT @.tSettingDesc = NULL
>> SELECT SettingKey,
>> SettingDesc,
>> NoOfCopies,
>> IsPreview
>> FROM tblAppSetting (nolock)
>> WHERE SiteID = @.SiteID
>> AND IsActive = 1
>> GO
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>> >
>> > Do you mean that ii is working and i is not? Do you receive any errors?
>> >
>> > Try executing the stored procedure code manually.
>> >
>> > Or post both i and ii code here.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "Vanessa" wrote:
>> >
>> >> Hi,
>> >>
>> >> I'm new using Application Role. I use SQL Query Analyzer to try some
>> >> example.
>> >>
>> >> I have create a Application role call "ABC" and have set permission to
>> >> all
>> >> tables & stored procedures.
>> >>
>> >> In Query Analyzer, after setting the role using sp_setapprole,
>> >> i) I try to run the stored procedure but it give me 0 result.
>> >> (it
>> >> should return me some records which it doesn't)
>> >> ii) I try to run a select statement for a table, it return me
>> >> all
>> >> records.
>> >>
>> >> Since Application Role allows me to set permission for Stored
>> >> Procedures
>> >> &
>> >> tables, I thought both should be the same.
>> >>
>> >> Does anyone know why is this so?
>> >>
>> >> Regards
>> >> Vanessa
>> >>
>> >>
>> >>
>>

Application Role permissions report

Can I generate the report for each application role with their objects &
their permissions using sql stored procedure ?
Let me know.
Thanks for all the help.
Manoj
Answered your question in another thread.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:1FF02616-9454-47DC-951D-14586999778F@.microsoft.com...
> Can I generate the report for each application role with their objects &
> their permissions using sql stored procedure ?
> Let me know.
> Thanks for all the help.
> Manoj

Application Role permissions report

Can I generate the report for each application role with their objects &
their permissions using sql stored procedure ?
Let me know.
Thanks for all the help.
ManojAnswered your question in another thread.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:1FF02616-9454-47DC-951D-14586999778F@.microsoft.com...
> Can I generate the report for each application role with their objects &
> their permissions using sql stored procedure ?
> Let me know.
> Thanks for all the help.
> Manoj

AppendStored Procedure

I got this stored procedure I'm running that inserts data from one table to another, but it keeps telling me that I'm violating the primary key rule which means one those records or 12 for all I know, already exsist in the other table. How do I add a IF ALREADY EXSIST CLAUSE TO MY STORED PROCEDURE, is that the correct word for it?? I'll look it up also. Heres the stored procedure I have so far.

ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS]
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
GOYou are almost there. Try this (it will need a little bit of work from you, but you should get it quickly)

ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
and dbo.corovan_table.(primary key field) is null
Go

You will have to supply the (primary key field)'s. Hope this helps.|||ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
and not exists (select <primary key field> from dbo.corovan_table)Go|||Problem solved guys thanks so much for your help, I sure do appreciate it, was struggling with that all day. At least it was a nice learning experience for me.

Thanks guys :)|||Hi guys its me again I'm having problems with my stored procedure again it executes as long as theres no primary key rule violations. But there is a violation Of the primary key because the the stored procedure was executed yesterday, but today I have discovered that since this is based on the 2003 terms date not all the 2003 terms were dated as 2003 (user error) so now I need to edit the stored procedure, which of course I'm not doing correctly.

Help? Again Please

ALTER PROCEDURE dbo.[03Corovans]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[03TermsWithoutMatchingCorovan]
and not exists (select <TM #> from dbo.Corovan_table)Go|||Oh good Grief Never mind I looked at the script again and did the necessary corrections now it works just fine. AND I FEEL LIKE AN IDIOT

SORRY GUYS|||I'm so ashamed and embarrassed.....DUH!!!!!!!!!!!!!!!!!!!!!!

Merry Christmas and
Happy Holidays everyone|||AND I FEEL LIKE AN IDIOTDoes that mean "with your hands" ?

-PatP|||That means with my hands and My SO CALLED BRAIN|||Did that answer your question Pat or did I miss the point??|||Did that answer your question Pat or did I miss the point??You are assuming that my questions have a point... Not everyone would jump on that bus!

Yes, an idiot normally feels things with their hands, so if you are "feeling like an idiot", I'd assume that you were wandering about with a goofy expression, putting your hands on everything you could.

Nevermind... I never claimed that it made any sense, I just found it humorous!

-PatP|||You know Pat I know alot of people with very High IQ's who have a simular sense of humor as yourself, so I should have known better. Next time I'll just let that bus go right by me. :)

Appending Stored Procedure

I need to make sure I'm doing this correctly can you help me out guys please?? This is an Appending Stored procedure it should move values from the EmployeeGamingLicense table when the status is turned into TERMINATED to the GCEmployeeTerms table. Heres what I have so far, having problems with the rest of the script getting errors

CREATE PROCEDURE [insert_GCEmployeeTerms_1]
(@.TM_#_1 [int],
@.FirstName_2 [nvarchar](50),
@.LastName_3 [nvarchar](50),
@.SocialSecurityNumber_4 [int],
@.DateHired_5 [datetime],
@.Status_6 [nvarchar](50),
@.TerminationDate_7 [datetime],
@.Title_8 [nvarchar](50),
@.DepartmentName_9 [nvarchar](50),
@.TermReason_10 [ntext],
@.VoluntaryInvoluntary_11 [nvarchar](50))

AS INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]
( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[TerminationDate],
[Title],
[DepartmentName],
[TermReason],
[VoluntaryInvoluntary])

SELECT
( @.TM_#,
@.FirstName,
@.LastName,
@.SocialSecurityNumber,
@.DateHired,
@.Status,
@.TerminationDate,
@.Title,
@.DepartmentName,
@.TermReason,
@.VoluntaryInvoluntary)
FROM EmployeeGamingLicense
WHERE STATUS = 'TERMINATED'
GOMethinks thou wants not both tables and variables in yon SELECT list. There be dragons in that mix!

-PatP|||Thank You pat for that very old English/shakesperan/King Arthur answer..lol. Let me see if I interpreted that correctly...Your saying that my select list is incorrect, refering of both tables...I need to go look it up..sorry this is my first append stored procedure.|||If you stop and think about it, you are passing a whole flock (that would be a really technical term, eh?) of parameters to the procedure. This would be a good thing if you wanted to insert those parameters one time. You are also using a table with a where clause, which would be a good thing if you wanted to insert columns from that table for every row that qualified. However, doing both means that you want to insert one copy of the parameters that you are passing for every row that qualifies... Which is probably NOT what you intended!

You probably want to either use the table with column names, or the parameters without the table, but only one or the other!

-PatP|||I'm just being nosy now, but did you ever get this one sorted out to your satisfaction?

-PatP|||Sniff Sniff Sniff ...No :(

I just want to insert data from one table to another automatically through stored procedures. I can make part of the procedure (that Inserts), need to tell it to pull from another table...I dont know, keep getting errors its like a puzzle I can only get part of it but not all (Banging head against wall)|||a whole flock of parameters

Pat. I thought it was a bevy of parameters. Hmm. May need to add it to the lists...

http://encyclopedia.thefreedictionary.com/List%20of%20collective%20nouns%20by%20collective%2 0term%20A-K|||ok so your saying its just a bunch of parameters..a horde of parameters|||Sniff Sniff Sniff ...No :(Now, now M'Dear! You musn't get too upset. Might I suggest trying something like:CREATE PROCEDURE [insert_GCEmployeeTerms_1]
AS

INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms] (
[TM #], [FirstName], [LastName]
, [SocialSecurityNumber], [DateHired], [Status]
, [TerminationDate], [Title], [DepartmentName]
, [TermReason], [VoluntaryInvoluntary]
) SELECT
a.TM_#, a.FirstName, a.LastName
, a.SocialSecurityNumber, a.DateHired, a.Status
, a.TerminationDate, a.Title, a.DepartmentName
, a.TermReason, a.VoluntaryInvoluntary)
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'

RETURN
GO-PatP|||I did what you said pat I got some Errors but what you put up gave me a great start I was not Referring to the Table.Fields that will do the inserting for me Fields in that Table are not exactly the same..(the column names) OK I GET IT A LIGHT HAS GONE OFF AND I TOTALLY GET IT NOW..Now I see why SQL was like WHAT THE HELL ARE YOU TALKING ABOUT..OMG I Die a Happy Woman now...Thank you so much Pat Your Truly a SQL GOD..I totally Get it now. :)

THANK YOU|||and Mccowley was correct I see his point now|||Now that you grok (http://searchsmallbizit.techtarget.com/sDefinition/0,,sid44_gci212216,00.html) how this works, you'll need to think a bit about how to limit the carnage. You won't always want to turn that procedure loose to copy every terminated employee row, or you'll end up with a gazillion copies of each row in GCEmployeeTerms (one for every time the procedure is run while that terminated row is present), which probably isn't what you want!

-PatP|||Ok...what happens is our dept gets a list of terminated employees, so when we get this list we go to the [GamingCommissiondb].[dbo].[EmployeeGamingLicense] which is the active table look up the employee and change their status to 'TERMINATED'. Now there can be as much as 20-30 terminations. The Stored Procedure will then take those employees that have been changed to Terminated and APPEND (Insert) them into the TERMINATION.tbl. One of the purposes of doing this is if the employee comes back as a rehire we can check to see if there was any negative action taken against their gaming license. Does that make sense??|||Oh heavens yes, but unless you delete the row from the active table when this happens, that row will be copied every time the procedure runs (at least as it is written right now). The only criteria that you are using to select rows is if they are flagged as terminated... You might want to be a wee bit more selective than that, maybe throwing in a test on the termination date or something wild and crazy like that!

-PatP|||I understand what your saying, I dont want my users to delete a record in error so I didnt want to delet the record from the active table through the stored procedure. I have a view that shows only the terminated employees from the active table that I look at everyone 2 weeks, just a QC thing. also both the tables have the TM# as a primary key, the first time I tried to execute the stored procedure I got "Pk constraint error duplicates"..something like that, so once I found out what records had already been appended to the TERMINATION table and which ones had not been the SP executed successfully. Does that make sense?|||Yep, that it does. You could also use a WHERE NOT EXISTS clause to test for the presence of the TM# while you were doing the INSERT operation. This might be simpler.

-PatP|||Yes your absolutely right, I'll alter the procedure then thank you again for your help Pat I sure do appreciate it :)

Appending a Field

I know this must be simple, but I am stumpted, please help!

I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".

I do not know how to append the text in a field. How is that best done?

The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.

What is the best way to do this? Can anyone give me an example?This one should be quite easy:

UPDATE table SET Filed = Field + @.Value WHERE ID = @.ID|||Thank you very much!

It is working now.

Appending a Field

I know this must be simple, but I am stumpped, please help!

I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".

I do not know how to append the text in a field. How is that best done?

The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.

What is the best way to do this? Can anyone give me an example?update tablename set fieldname = fieldname + @.incomingtext where condition ...|||Also, if you haven't already, you may want to look at the data type of the "comments" field and make sure when you are appending the next text the maximum length for that data type is not being exceeded.|||Originally posted by Donner
Also, if you haven't already, you may want to look at the data type of the "comments" field and make sure when you are appending the next text the maximum length for that data type is not being exceeded.

create table test(id int identity,code varchar(50))
go
insert test(code) values('a')
go
update test set code=code+'b' where datalength(code+'b')<51
go
select * from test

append xml nodes

I'm searching for a way to insert nodes from an xml document into an other.
My solution to the problem is something like this:
ALTER PROCEDURE [dbo].[insertOrder]
@.Customer xml (dbo.schemaCustomer),
@.Product xml (dbo.schemaProduct)
AS
BEGIN
declare @.app varchar(1024)
declare @.x xml
set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
'</Product>'+ CAST(@.Customer as varchar(1024)) +
'<id_order>0</id_order></Order>'
insert into xOrder (doc) values (@.app)
END
but I think there's a better way to solve the problem.
Is it possible to do this witout any casting operation?
Thank you, Luca.Hello luca,

> I'm searching for a way to insert nodes from an xml document into an
> other. My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
> END
> but I think there's a better way to solve the problem. Is it possible
> to do this witout any casting operation? Thank you, Luca.
Not that I've found. Something like this would be ideal if doc was XML-typed
as X1 is here:
declare @.x1 xml,@.x2 xml
set @.x1 = '<pet id="1" ownerID="1"><name>Jack</name></pet>'
set @.x2 = '<descr>Grey<descr>'
set @.x1.modify('insert sql:variable("@.x2") into /pet[1]')
However, the modify method won't work with XML-type variables. Nor does trea
ting
@.x2 as a varchar(x) really get you any place since when you do try that,
you get:
XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string
?".
.modify() also requires the use of string literal as the query. So, Ick.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Try:
CREATE TABLE xOrder(doc xml)
go
CREATE PROCEDURE [dbo].[insertOrder]
@.Customer xml, --(dbo.schemaCustomer),
@.Product xml --(dbo.schemaProduct)
AS
BEGIN
insert into xOrder (doc) select (
select @.Customer, @.Product as "Product", 0 as "id_order"
FOR XML PATH('Order'), TYPE
)
END
go
exec insertOrder '<a/>', '<b/>'
select * from xOrder
Best regards
Michael
"luca" <luca@.discussions.microsoft.com> wrote in message
news:1F06F5E3-E9C9-4B40-ACFE-2784345D1F40@.microsoft.com...
> I'm searching for a way to insert nodes from an xml document into an
> other.
> My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
>
> END
> but I think there's a better way to solve the problem.
> Is it possible to do this witout any casting operation?
> Thank you, Luca.
>

append xml nodes

I'm searching for a way to insert nodes from an xml document into an other.
My solution to the problem is something like this:
ALTER PROCEDURE [dbo].[insertOrder]
@.Customer xml (dbo.schemaCustomer),
@.Product xml (dbo.schemaProduct)
AS
BEGIN
declare @.app varchar(1024)
declare @.x xml
set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
'</Product>'+ CAST(@.Customer as varchar(1024)) +
'<id_order>0</id_order></Order>'
insert into xOrder (doc) values (@.app)
END
but I think there's a better way to solve the problem.
Is it possible to do this witout any casting operation?
Thank you, Luca.
Hello luca,

> I'm searching for a way to insert nodes from an xml document into an
> other. My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
> END
> but I think there's a better way to solve the problem. Is it possible
> to do this witout any casting operation? Thank you, Luca.
Not that I've found. Something like this would be ideal if doc was XML-typed
as X1 is here:
declare @.x1 xml,@.x2 xml
set @.x1 = '<pet id="1" ownerID="1"><name>Jack</name></pet>'
set @.x2 = '<descr>Grey<descr>'
set @.x1.modify('insert sql:variable("@.x2") into /pet[1]')
However, the modify method won't work with XML-type variables. Nor does treating
@.x2 as a varchar(x) really get you any place since when you do try that,
you get:
XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string
?".
..modify() also requires the use of string literal as the query. So, Ick.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Try:
CREATE TABLE xOrder(doc xml)
go
CREATE PROCEDURE [dbo].[insertOrder]
@.Customer xml, --(dbo.schemaCustomer),
@.Product xml --(dbo.schemaProduct)
AS
BEGIN
insert into xOrder (doc) select (
select @.Customer, @.Product as "Product", 0 as "id_order"
FOR XML PATH('Order'), TYPE
)
END
go
exec insertOrder '<a/>', '<b/>'
select * from xOrder
Best regards
Michael
"luca" <luca@.discussions.microsoft.com> wrote in message
news:1F06F5E3-E9C9-4B40-ACFE-2784345D1F40@.microsoft.com...
> I'm searching for a way to insert nodes from an xml document into an
> other.
> My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
>
> END
> but I think there's a better way to solve the problem.
> Is it possible to do this witout any casting operation?
> Thank you, Luca.
>

append recordset returned from a stored procedure

I have a recordset returned from a stored procedure executed in the form open event. Could this recordset append to the form's recordsource property in the form's open event in VB? if so, what's the syntax?

Private Sub Form_Open(Cancel As Integer)
dim ...

Set add_bag_results = Nothing
With add_bag_results
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "spSampling_add_bag_results"
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
'.Parameters.Append .CreateParameter("@.lotnum", adInteger, adParamInput, 4, rs_add_bag_results(1))
.Parameters.Append .CreateParameter("@.lotnum", adInteger, adParamInput, 4, lot_n)
..
Set rs_add_bag_results = .Execute
End With

Me.RecordSource = rs_add_bag_results ?

Thanks!I think this is a very insightfull question but your on the wrong forum for this I think. Try Extreme, VB.com, the response is just as quick and it's the same format as this forum. Frankly, I don't know vb but when I had to use it with asp.net I used Extreme vb forum for my code.|||Thanks for your reply. I did figured it out. You are right and I have posted my questions on vb extreme forum before. Thanks!

Append only unique records in SQL table

I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.

The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?

Thanks in advance.
SMc

Lets say your destination table is called Dest and you temporary table is called Temp.

Both tables have two fields, A and B where A is the one that needs to be unique in Dest, but it's not necesarily unique in Temp (or Temp contains rows with a value of A that already exists in Dest).

insertinto Dest

select Temp.A, Temp.Bfrom Temp

where Temp.Anotin(select Dest.Afrom Dest)

|||

I was just fighting with similar, but a lot of simpler problem. Maybe these posts will help.

http://forums.asp.net/t/1185782.aspx

And IF NOT EXISTS(SELECT * FROM table WHERE .....

Hope it helps

Leif

apostrophes, etc. in WHERE statements?

How do I handle and apostrophes and other punctuation in stored procedure Sql statements:

SELECT L_ID, L_Name
FROM tblHVACContractors
WHERE (L_Name = 'Mare's Heating & Cooling Services')

You have to double it to escape it.

SELECT

L_ID, L_Name

FROM

tblHVACContractors

WHERE

(L_Name='Mare''s Heating & Cooling Services')

|||

Yup, that will work if we know the value before hand, but what if we are using parameters. Is there a replace function in SQL?

bullpit

|||

Bullpit has better worded my situation.

|||

It depends on where the data is coming from. If your application is sending the data with special characters like that and you are using parameterized queries, you dont need to do any additional coding. The parameterized queries will take care of it. If you are concatenating value as

" SELECT .. FROM WHERE col1 = " + sometxtbox.text

then you have to do a replace on the value in the text. which is one more reason why you should use parameterized queries, if you are not already coding that way.

|||

That works.

bullpit

API to create table, stored procedure, etc.

In SQL Manager I can right click on a stored procedure, table, etc. and I am presented with a list of options one of which is "Create >". Clicking this I can get a script that will create the respective stored procedure or table and write the create script to the clipboard, a file, or an SQL query window. I want to automate this to essentially selectively "back up" our data base by creating one large script that will create the tables and stored procedures from the database. Is there an API, tool, or library call that I can get access to the code that implements these menu selections?

Thank you.

Kevin

Yes. Look up the SMO reference in BOL. It has all of the capabilities and the full class library to do what you need.

API functions

hi
How can I use an API function in a stored procedure in sql server 2000?
tanxHi
Not directly! You may call a COM object using the sp_OA* procedures or write
an extended stored procedure. Depending on what you may want to do there may
be a procedure that already provides the functionality or an alternative
method of untaking the task.
John
"mtgoli" wrote:

> hi
> How can I use an API function in a stored procedure in sql server 2000?
> tanx|||
"John Bell" wrote:
> Hi
> Not directly! You may call a COM object using the sp_OA* procedures or wri
te
> an extended stored procedure. Depending on what you may want to do there m
ay
> be a procedure that already provides the functionality or an alternative
> method of untaking the task.
> John
>
> "mtgoli" wrote:
>
Dear John
Can u please introduce som references about using COM object in sql? I've
never worked with COM object.
tanx|||Hi
This is an example:
http://msdn.microsoft.com/library/d...r />
_2ktw.asp
Searching for sp_OACreate on msdn.microsoft.com or google should turn
up more!
John

API function

Hi
How can I use an API function in a stored procedure in sql server 2000?2 ways. First you can write a wrapper in C and compile it into a DLL and bring it into SQL as an extended stored procedure (see examples in BOL). Second, if an API belongs to an ActiveX/COM object, - you can use it directly through sp_OAxxx. If it doesn't, - write a wrapper in C++ or VB and compile it into an ActiveX component.

API calls from within a Stored Procedure?

Folks, I would like to convert some macros into a stored procedure. The ide
a
is to read file names out of a database and copy them from their current
location to specified directories. Can an API call be made from a SP to
prompt for a root directior (via file dialog), create a directory, and copy
a
file as its steping through a record set?
Suggestions will be greatly appreciated on how to do this.You can use extended stored procedures, although I believe you're not
supposed to have any UI on an XP. You could write a stored proc to read the
filenames from your database, and take advantage of the xp_cmdshell stored
procedure to move them to a destination directory. UI implementation is
really a whole 'nother layer from SQL Server, and you'd probably do better
to write a little .NET or other program to select the directory; although
you could create a stored proc to do the actual copy for you.
"Crisp" <Crisp@.discussions.microsoft.com> wrote in message
news:5E816E07-01D6-43F6-8E6C-E54E02BDB943@.microsoft.com...
> Folks, I would like to convert some macros into a stored procedure. The
> idea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and
> copy a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||You want to use SQL Server to move files around ?
including prompting a user for input?
This would be a gross misuse of the tool, it would be more appropriate and
much easier to do this in any ordinary programming language. You can still
put the file list in SQL if you want, and have application code get the list
from the SQL database, but having SQL do the File operations, or prompt user
s
for input, would be much too difficult, and inappropriate.
In fact, the only solution I can think of, is really a combination of having
SQL kick off an external process that does the actual "prompting and gather
user input" function, and then does the FIle IO... to do that you need to
write a COM-Component tool in a COM-Capable language, and call it from SQL
using built-in specially designed System procesdures. (Investigate the set
of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
you to create, use, and destroy COM components from inside SQL.)
But don't use these for any application where scaleability is a concern,
because they are notoriously NOT scaleable.
"Crisp" wrote:

> Folks, I would like to convert some macros into a stored procedure. The i
dea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and cop
y a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||I agree with you that the user input function and prompting would be a
misuse of SQL Server. The OP didn't specify this, but I was thinking along
the lines of administering the file move from a client machine? I.e., on my
desktop computer I want to move files on my SQL Server from C:\test_data to
D:\new_data. SQL Server makes it relatively easy to get a directory listing
and perform command shell operations via extended proc's, without setting up
and administering shares all over your network, or creating your own
specialized client/server app.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
> You want to use SQL Server to move files around ?
> including prompting a user for input?
> This would be a gross misuse of the tool, it would be more appropriate and
> much easier to do this in any ordinary programming language. You can
> still
> put the file list in SQL if you want, and have application code get the
> list
> from the SQL database, but having SQL do the File operations, or prompt
> users
> for input, would be much too difficult, and inappropriate.
> In fact, the only solution I can think of, is really a combination of
> having
> SQL kick off an external process that does the actual "prompting and
> gather
> user input" function, and then does the FIle IO... to do that you need to
> write a COM-Component tool in a COM-Capable language, and call it from SQL
> using built-in specially designed System procesdures. (Investigate the
> set
> of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
> you to create, use, and destroy COM components from inside SQL.)
> But don't use these for any application where scaleability is a concern,
> because they are notoriously NOT scaleable.
>
> "Crisp" wrote:
>|||Well then it is certainly possible to write a SP that you would call from
your desktop, passing in acomplete source FileSpec, and destination FileSpec
,
as Local (Local to SQL Serevr) File Specfications, that would copy the file
from one folder \FileName to aspecified Folder\Filename... S(Sounds like yo
u
already know about xp_cmdShell)
You would still need to ensure that the Process ID the SQL Server was
running under had the appropriate permissions on the local file system.
The only thing this approach saves you is creating of user access
permissions and controlled network shares on the SQL Server across the
network...
"Michael C#" wrote:

> I agree with you that the user input function and prompting would be a
> misuse of SQL Server. The OP didn't specify this, but I was thinking alon
g
> the lines of administering the file move from a client machine? I.e., on
my
> desktop computer I want to move files on my SQL Server from C:\test_data t
o
> D:\new_data. SQL Server makes it relatively easy to get a directory listi
ng
> and perform command shell operations via extended proc's, without setting
up
> and administering shares all over your network, or creating your own
> specialized client/server app.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:12A44395-01F8-44C7-996E-EDAC39490BCF@.microsoft.com...
> Well then it is certainly possible to write a SP that you would call from
> your desktop, passing in acomplete source FileSpec, and destination
> FileSpec,
> as Local (Local to SQL Serevr) File Specfications, that would copy the
> file
> from one folder \FileName to aspecified Folder\Filename... S(Sounds like
> you
> already know about xp_cmdShell)
> You would still need to ensure that the Process ID the SQL Server was
> running under had the appropriate permissions on the local file system.
> The only thing this approach saves you is creating of user access
> permissions and controlled network shares on the SQL Server across the
> network...
Designated network shares restrict the OP's solution to copying files from
pre-defined Point A's to pre-defined Point B's, and he seemed to hint that
he wanted to be able to have a choice of source and destination; although
I'm not sure exactly how many choices he wants. For only one or two
destinations, the network shares solution would definitely be the way to go.
But if you know there are going to be a lot of sources and destinations,
like each user has his/her own set of source and destination folders, a more
dynamic approach might be needed. A front-end file selection utility like
the File Attach interface in EM is not that hard to create in VB.NET or
C#.NET using SQL Server xp's. I guess it all really depends on a more
specific set of requirements from the OP to decide what solution would be
best for his/her needs. I think we both agree that trying to use SQL Server
as a UI engine is a horrible idea.