An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

an unkown insert type by me(!)

Hi Dear Coder Friends;

i just discovered below insert type in MSSQL 2005 Automatic creating insert sentence but i couldn't use because there is a syntax error.

INSERT INTO [KimlikBilgileri]

([CvId]

,[KimlikNo]

,[Ad]

,[Soyad]

,[Cinsiyet]

,[DogumTarihi]

,[UlkeId]

,[DogumYeri]

,[MedeniDurumu])

VALUES

(<CvId, int,>

,<KimlikNo, char(11),>

,<Ad, varchar(50),>

,<Soyad, varchar(50),>

,<Cinsiyet, char(5),>

,<DogumTarihi, smalldatetime,>

,<UlkeId, int,>

,<DogumYeri, varchar(50),>

,<MedeniDurumu, varchar(8),>)

Question for above;

-- What's this type called?

-- does it make any security bug like injections?

i want to use this one as a stored proc to add "create proc KimlikBilgilerInsert as ". So i think i don't have to declare one by one

am i right?

Thank you for your valuable knowledge

The syntax error is due to the datatypes. Remove all the datatype indicators.

This is called an INSERT statement.

Yes, it is susceptible to SQL Injection -there are very long varchar(50) fields, and there is no data validation. I would consider where the values are gathered, and if from textboxes on a form, then I would put this statement in a stored procedure and add some data validation code.

An interesting Qn

Hi,
My table has the following structure
create table CompanyRights (
CompanyID int, RightID int )
Insert into CompanyRights select 1,1
Insert into CompanyRights select 1,2
Insert into CompanyRights select 1,3
Insert into CompanyRights select 2,1
Insert into CompanyRights select 2,1
and i want to choose all the companies having rights 1 and 2
select * from CompanyRights where CompanyID = 1 And RightID = 1 AND RightID
= 2
but this didn't work
Regards
LaraIs it possible without a join
With Join I have the answer
SELECT t1.CompanyID,T1.RightID
FROM CompanyRights t1
INNER join CompanyRights t2
ON t1.CompanyID =t2.CompanyID
WHERE t1.RightID = 1 AND t2.RightID = 2
regards Lara|||select * from CompanyRights where CompanyID = 1 And RightID = 1 AND RightID
= 2
The key RightID cant be 1 AND 2 at the same time, this must be
select * from CompanyRights where CompanyID = 1 And (RightID = 1 OR RightID
= 2)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lara" <aneeshattingal@.hotpop.com> schrieb im Newsbeitrag
news:O$6pQLaSFHA.248@.TK2MSFTNGP15.phx.gbl...
> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Sure,
Select * From CompanyRights
Where RightID In (1,2)
-- which is same as
Select * From CompanyRights
Where RightID = 1 Or RightID = 2
"Lara" wrote:

> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Try,
SELECT
CompanyID
FROM
CompanyRights
WHERE
RightID = 1
or RightID = 2
group by
CompanyID
having
count(distinct RightID) = 2;
Relational Division
http://www.dbazine.com/ofinterest/o...br />
division
AMB
"Lara" wrote:

> Is it possible without a join
> With Join I have the answer
> SELECT t1.CompanyID,T1.RightID
> FROM CompanyRights t1
> INNER join CompanyRights t2
> ON t1.CompanyID =t2.CompanyID
> WHERE t1.RightID = 1 AND t2.RightID = 2
> regards Lara
>
>|||Select Distinct CompanyID
From CompanyRights R
Where Exists
(Select * From CompanyRights
Where CompanyID = R.CompanyID
And RightID = 1)
And Exists
(Select * From CompanyRights
Where CompanyID = R.CompanyID
And RightID = 2)
"Lara" wrote:

> Hi,
> My table has the following structure
> create table CompanyRights (
> CompanyID int, RightID int )
> Insert into CompanyRights select 1,1
> Insert into CompanyRights select 1,2
> Insert into CompanyRights select 1,3
> Insert into CompanyRights select 2,1
> Insert into CompanyRights select 2,1
> and i want to choose all the companies having rights 1 and 2
> select * from CompanyRights where CompanyID = 1 And RightID = 1 AND Right
ID
> = 2
> but this didn't work
> Regards
> Lara
>
>

An Insert Trigger with CDOSYS generated email

Hello,
I've got an insert trigger defined on a table.
Everything seems to work perfectly excpet the body section of my e-mail
message is delivered empty.
Here the relavent code segment...
Begin
DECLARE @.CaseCounter varchar(50)
If (SELECT Count(*) FROM inserted WHERE PI_ID = '0000') >0
SELECT @.CaseCounter = RTRIM(CAST(IDENT_CURRENT('inserted') AS varchar(50))
)
SELECT @.body = 'The Case Number is: ' + @.CaseCounter
Set @.vet_email = 'valid@.to.email.address'
exec dbo.sp_send_cdosysmail 'valid@.from.email.address, @.vet_email, 'An
Unlisted PI was submitted with this case', @.body
End
As you can tell from the my trigger code I'm trying to concatenate the
inserted Identity value onto a Character string. As I stated above, the
e-mail arrive fine but the Body section is blank. In case you're wondering,
the @.vet_mail variable is DECLARED earlier in the trigger.
I previously had all variables DECLARE at the top of the code but I still
got the same results.
All suggestions are welcomed!
Thanks
Application Engineer / DBA
UCLA SOM(a) please, please, please... do NOT send e-mail from a trigger!
(b) why don't you look at the base table instead of inserted. inserted is a
virtual table and I think you will find that IDENT_CURRENT() will return
NULL (which, when concatenated to your @.body value, makes the whole
parameter NULL). To prove it, try:
SET @.body = 'The Case Number is: ' + COALESCE(@.CaseCounter, 'NULL');
"Marcial" <no_spam@.antispammer.com> wrote in message
news:1B796F05-D8E3-4F0A-AA9F-A1859F94FE58@.microsoft.com...
> Hello,
> I've got an insert trigger defined on a table.
> Everything seems to work perfectly excpet the body section of my e-mail
> message is delivered empty.
> Here the relavent code segment...
> Begin
> DECLARE @.CaseCounter varchar(50)
> If (SELECT Count(*) FROM inserted WHERE PI_ID = '0000') >0
> SELECT @.CaseCounter = RTRIM(CAST(IDENT_CURRENT('inserted') AS
> varchar(50)))
> SELECT @.body = 'The Case Number is: ' + @.CaseCounter
> Set @.vet_email = 'valid@.to.email.address'
> exec dbo.sp_send_cdosysmail 'valid@.from.email.address, @.vet_email, 'An
> Unlisted PI was submitted with this case', @.body
> End
>
> As you can tell from the my trigger code I'm trying to concatenate the
> inserted Identity value onto a Character string. As I stated above, the
> e-mail arrive fine but the Body section is blank. In case you're
> wondering,
> the @.vet_mail variable is DECLARED earlier in the trigger.
> I previously had all variables DECLARE at the top of the code but I still
> got the same results.
> All suggestions are welcomed!
> Thanks
> --
> Application Engineer / DBA
> UCLA SOM|||Hi Marcial,
just to add to Aarons post:
Its REALLY=B2 NOT=B3 recommended to send Emails in a trigger...
WHY
=3D=3D=3D=3D
1=2E Triggers behave synchronously, that means the trigger will block the
current transaction till the whole code in it was executed. So in any
cases that your mail server is unreachable, taking long for
communcation etc. the transaction will be blocked the data / pages /
tables (depending on your locking level) will be blocked and your
application or frontend or whatever another transaction wants to
manipulate the data will be on hold.
2=2ETriggers can cause the transcation to rollback due to a non-business
error. Although when the data and the transaction which is executed is
valid and should be commited to the database, if the sending EMail
procedure will bring back an error and you don=B4t handle it, or it is
of a certain severity which causes the transaction to rollback, your
whole BUSINESS is on hold, only because of sending an email !!!
You don=B4t want that, erh ?
I would suggest (as this is not time critical) to write the data in a
table which is regulary checked for content to be sent.
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--|||Thanks Very Much Jens for the expanded explanation. And thanks to Aarron fo
r
the initial Alert anbd Reply.
...You may consider me anevangelized user who has been convinced to find
another way beside triggers to send e-mail. Toward that end might anyone
have an example of Stored Proc code that searches a table and sends e-mail
accordingly.
Cheers~
--
Application Engineer / DBA
UCLA SOM
"Jens" wrote:

> Hi Marcial,
> just to add to Aarons post:
> Its REALLY2 NOT3 recommended to send Emails in a trigger...
> WHY
> ====
> 1. Triggers behave synchronously, that means the trigger will block the
> current transaction till the whole code in it was executed. So in any
> cases that your mail server is unreachable, taking long for
> communcation etc. the transaction will be blocked the data / pages /
> tables (depending on your locking level) will be blocked and your
> application or frontend or whatever another transaction wants to
> manipulate the data will be on hold.
> 2.Triggers can cause the transcation to rollback due to a non-business
> error. Although when the data and the transaction which is executed is
> valid and should be commited to the database, if the sending EMail
> procedure will bring back an error and you don′t handle it, or it is
> of a certain severity which causes the transaction to rollback, your
> whole BUSINESS is on hold, only because of sending an email !!!
> You don′t want that, erh ?
> I would suggest (as this is not time critical) to write the data in a
> table which is regulary checked for content to be sent.
>
> HTH, Jens Suessmeyer
> --
> http://www.sqlserver2005.de
> --
>|||Hi Marcial,
I thinkk I will write one for you that take as a template. If I don=B4t
come back to the thread please send me a reminder that I will keep
track of that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

An INSERT EXEC statement cannot be nested.

I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;

declare @.dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @.dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'

Select doccode, docdate, debtoraccount
From @.dtReturnData

Inside the GetPickList It will do like this, but most of the code I not included;

ALTER PROCEDURE GETPICKINGLIST
@.doctype nvarchar(2),
@.datefrom datetime,
@.dateto datetime,
@.includegrn char(1),
@.includesa char(1),
@.includedata nvarchar(5)
AS
BEGIN
declare @.dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))

IF (@.DOCTYPE = 'SI')
BEGIN
Insert Into @.dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
ELSE
BEGIN
Insert Into @.dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
Select doccode,docdate,debtoraccount From @.dtReturnData

END

So how can I select a nested store procedure? can someone help me

Jeremy,

This is a problem that comes up from time to time in SQL Server. My first suggestion when this comes up is to look at both points in which you are using the INSERT ... EXEC syntax. See if it is possible to convert at least one of the procedures into a user defined function. There is a "Plan C" for this but it is not nearly as clean as the option of converting to a function (if possible).

Also, for future keep in mind that it is a good idea to consider using functions -- especially inline functions -- instead of stored procedure when it is the intent to load the output from a procedure into a table.

See if SalesInvoiceRegister and DeliveryOrderRegister can be converted to functions.

Kent

An INSERT EXEC statement cannot be nested.

i wanted to store the output of my store proc in a temp table and i wsa doin
d
this:
INSERT #temp EXEC sproc
and it turned out i cannot do this if my sproc has another insert...exec
thing going on within it.
Is there any way i can store the output of my sproc somehow ?
thanks in advanceAbhishek Pandey (AbhishekPandey@.discussions.microsoft.com) writes:
> i wanted to store the output of my store proc in a temp table and i wsa
> doind this: >
> INSERT #temp EXEC sproc
> and it turned out i cannot do this if my sproc has another insert...exec
> thing going on within it.
> Is there any way i can store the output of my sproc somehow ?
Answered in comp.databases.ms-sqlserver. Please to do not post to multiple
newsgroups independently.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

An INSERT command with a subquery

Hi champs!
I am trying to make a short INSERT INTO -command With an subquery SELECT
statement, and it seems that it is not possible; or is it?
INSERT INTO [PeopleInGroups]([PeopleREF], [GroupREF])
VALUES ((SELECT [PeopleID] FROM [People] WHERE initials = 'SSS' ) ,20)
This query goes wrong..
Any suggestion?
/Thanks youINSERT INTO [PeopleInGroups]([PeopleREF], [GroupREF])
VALUES ((SELECT [PeopleID], 20 FROM [People] WHERE initials = 'SSS' )
Regards
R.D
--Knowledge gets doubled when shared
"Kurlan" wrote:

> Hi champs!
> I am trying to make a short INSERT INTO -command With an subquery SELECT
> statement, and it seems that it is not possible; or is it?
> INSERT INTO [PeopleInGroups]([PeopleREF], [GroupREF])
> VALUES ((SELECT [PeopleID] FROM [People] WHERE initials = 'SSS' ) ,20)
> This query goes wrong..
> Any suggestion?
> /Thanks you|||Thanks!
I also found that replacing the "VALUES" with an "SELECT" will also work
INSERT INTO [PeopleInGroups]([PeopleREF], [GroupREF])
SELECT ((SELECT [PeopleID] FROM [People] WHERE initials = 'SSS' ) ,20)
thanks!
"R.D" wrote:
> INSERT INTO [PeopleInGroups]([PeopleREF], [GroupREF])
> VALUES ((SELECT [PeopleID], 20 FROM [People] WHERE initials = 'SSS' )
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Kurlan" wrote:
>

an insert

Hello,

I need to realize an insert something like the following:

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTO MyTable2 (MyId,MyName)

SELECT @.MyId,MyName

FROM MyTable1

Here I am getting MyID from a stored procedure and I need to insert this to MyTable2, however I need to get a new MyID for each row in MyTable1. How can I do that?

I think you will have to do a loop here and do the EXEC for each loop.|||

You could try wrapping the stored procedure in a user defined function, then referencing it in your insert. So if the function was fn_NewKey, it'd be something like

Insert into MyTable2(MyID, MyName)

select fn_NewKey() as fred, MyName

from MyTable1

Incidentall, why don't you just use an identity column for MyID? It would be a lot easier.

|||I think you will have to do a loop here and do the EXEC for each loop.|||aaaaaaaaaaaa

an insert

Hello,

I need to realize an insert something like the following:

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTOMyTable2 (MyId,MyName)

SELECT@.MyId,MyName

FROMMyTable1

Here I am getting MyID from a stored procedure and I need to insert this to MyTable2, however I need to get a new MyID for each row in MyTable1. How can I do that?

you could write a view which uses a cursor to step through all the names and inserts the names and the result of that proc into a table, and then insert that.

What does the proc do ? Where does the ID come from that you need to use the proc ?

|||Please don't go the route of using cursors or writing procedural code. SQL is a set-based language and you will get the best performance if you utilize it. What does the SP GetMyID do? Why can't you use IDENTITY column on the table to generate automatic sequential numbers? This is a much more efficient mechanism. You can get the multiple ids generated by SQL Server using OUTPUT clause in SQL Server 2005 or via a trigger that dumps the rows from inserted table in SQL Server 2000 or requery base-table using the alternate key values from the rows that you inserted.|||

I would concur that a cursor is a last resort, but I was assuming there was a reason that the proc needs to be called on a line by line basis ( hence my questions about the nature of hte proc also ).

I was also assuming he needs to do a single insert, not that this code is going to be run regularly. b/c if the insert needs to happen on an ongoing basis, it should happen as each record is created.

|||

GetMyID Return an Id which is unique. This is SQL 2000, can you give me an example of trigger. I am just trying to get a new id for each row in my table.

|||

It generates an ID ? Ideally, I would have an identity column in the first table, and just insert the ID from the first table into the second ( so the string is only in your database once, and you can change it there to see it change throughout the database ).

A trigger is a proc that is fired when you perform a specific action such as an insert into a specific table. The help has lots of info on how they work.

|||

Hi cgraus,

That is true it is an id, however it is not identity, the way that the system is designed it creates a specific id. Is there any trigger example you can give me to accomplish to call the stored procedure for each row inserted into MyTable2 meaning accomplishes the following for each row.

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTO MyTable2 (MyId,MyName)

SELECT @.MyId,MyName

FROM MyTable1

|||

I guess you could do a trigger that runs when you insert a name in to MyTable2 ( I assume these names are contrived ), which calls the proc to generate the ID.

If an ID exists, why don't you store it in the table and use it throughout the system, regardless of where it comes from ?

http://www.codeproject.com/database/SquaredRomis.asp

Basically, the syntax is

CREATE TRIGGER invUpdate ON [Table2]
FOR INSERT

AS

...

Inside an insert trigger, the 'inserted' table will give you access to the items that were inserted. I am not sure if it's called once per line. If not, then a bulk insert leaves you with the same problem, but just within the trigger.

Ambiguous column name on Insert

I am getting an Ambiguous column name on this insert for all fields.
Insert Into Agentlocatordata ([Agent],
[AgentLoc],
[AppointmentDate],
[BusinessName],
[BusinessPhone1],
[BusinessPhone2],
[BusinessType],
[CommercialProp],
[Contact],
[ContactEmail],
[CPRepGotit],
[Crossstreet],
[DateCreated],
[DateLastChanged],
[DirectConnect],
[Direction1],
[Direction2],
[Direction3],
[DisplayName],
[E_Mail],
[FaxByEmail],
[FaxPhone],
[License],
[MailAddress1],
[MailAddress2],
[MailCity],
[MailName],
[MailState],
[MailZip],
[MKTGRep],
[NoOfAgents],
[OfficeAddress1],
[OfficeAddress2],
[OfficeCity],
[OfficeState],
[OfficeZip],
[OfficeZipp4],
[Owner],
[Profile],
[RepGotit],
[ShortName],
[ShowPage],
[Specialty],
[StarfishGroup],
[State],
[TaxPayerShortName],
[TaxType],
[Unit],
[Website],
[Zone])
Select Distinct
[Agents]
,[AgentLoc]
,[AppointmentDate]
,[BusinessName]
,[BusinessPhone1]
,[BusinessPhone2]
,[BusinessType]
,[CommercialProp]
,[Contact]
,[ContactEmail]
,[CPRepGotit]
,[Crossstreet]
,[DateCreated]
,[DateLastChanged]
,[DirectConnect]
,[Direction1]
,[Direction2]
,[Direction3]
,[DisplayName]
,[EMail]
,[FaxByEmail]
,[FaxPhone]
,[License]
,[MailAddress1]
,[MailAddress2]
,[MailCity]
,[MailName]
,[MailState]
,[MailZip]
,[MKTGRep]
,[NoOfAgents]
,[OfficeAddress1]
,[OfficeAddress2]
,[OfficeCity]
,[OfficeState]
,[OfficeZip]
,[OfficeZipp4]
,[Owner]
,[Profile]
,[RepGotit]
,[ShortName]
,[ShowPage]
,[Specialty]
,[StarfishGroup]
,[State]
,[TaxPayerShortName]
,[TaxType]
,[Unit]
,[Website]
,[Zone]
from Agent full outer
join Agent0315 on Agent.Agent = Agent0315.agents
where Agent.agent = Agent0315.agents
ANy Help would be great!Lontae Jones wrote:
> I am getting an Ambiguous column name on this insert for all fields.
> Insert Into Agentlocatordata ([Agent],
> [AgentLoc],
> [AppointmentDate],
> [BusinessName],
> [BusinessPhone1],
> [BusinessPhone2],
> [BusinessType],
> [CommercialProp],
> [Contact],
> [ContactEmail],
> [CPRepGotit],
> [Crossstreet],
> [DateCreated],
> [DateLastChanged],
> [DirectConnect],
> [Direction1],
> [Direction2],
> [Direction3],
> [DisplayName],
> [E_Mail],
> [FaxByEmail],
> [FaxPhone],
> [License],
> [MailAddress1],
> [MailAddress2],
> [MailCity],
> [MailName],
> [MailState],
> [MailZip],
> [MKTGRep],
> [NoOfAgents],
> [OfficeAddress1],
> [OfficeAddress2],
> [OfficeCity],
> [OfficeState],
> [OfficeZip],
> [OfficeZipp4],
> [Owner],
> [Profile],
> [RepGotit],
> [ShortName],
> [ShowPage],
> [Specialty],
> [StarfishGroup],
> [State],
> [TaxPayerShortName],
> [TaxType],
> [Unit],
> [Website],
> [Zone])
> Select Distinct
> [Agents]
> ,[AgentLoc]
> ,[AppointmentDate]
> ,[BusinessName]
> ,[BusinessPhone1]
> ,[BusinessPhone2]
> ,[BusinessType]
> ,[CommercialProp]
> ,[Contact]
> ,[ContactEmail]
> ,[CPRepGotit]
> ,[Crossstreet]
> ,[DateCreated]
> ,[DateLastChanged]
> ,[DirectConnect]
> ,[Direction1]
> ,[Direction2]
> ,[Direction3]
> ,[DisplayName]
> ,[EMail]
> ,[FaxByEmail]
> ,[FaxPhone]
> ,[License]
> ,[MailAddress1]
> ,[MailAddress2]
> ,[MailCity]
> ,[MailName]
> ,[MailState]
> ,[MailZip]
> ,[MKTGRep]
> ,[NoOfAgents]
> ,[OfficeAddress1]
> ,[OfficeAddress2]
> ,[OfficeCity]
> ,[OfficeState]
> ,[OfficeZip]
> ,[OfficeZipp4]
> ,[Owner]
> ,[Profile]
> ,[RepGotit]
> ,[ShortName]
> ,[ShowPage]
> ,[Specialty]
> ,[StarfishGroup]
> ,[State]
> ,[TaxPayerShortName]
> ,[TaxType]
> ,[Unit]
> ,[Website]
> ,[Zone]
> from Agent full outer
> join Agent0315 on Agent.Agent = Agent0315.agents
> where Agent.agent = Agent0315.agents
> ANy Help would be great!
You didn't qualify any of your columns in the SELECT portion. If you use
a join, you need to qualify your columns as a matter of good practice.
You probably have the same column name in the Agent and Agent0315
tables.
David Gugick
Imceda Software
www.imceda.com|||My select follows my insert but I am still getting ambiguos errors on all
columns.
Insert Into Agentlocatordata ([Agent],
[AgentLoc],
[AppointmentDate],
[BusinessName],
[BusinessPhone1],
[BusinessPhone2],
[BusinessType],
[CommercialProp],
[Contact],
[ContactEmail],
[CPRepGotit],
[Crossstreet],
[DateCreated],
[DateLastChanged],
[DirectConnect],
[Direction1],
[Direction2],
[Direction3],
[DisplayName],
[E_Mail],
[FaxByEmail],
[FaxPhone],
[License],
[MailAddress1],
[MailAddress2],
[MailCity],
[MailName],
[MailState],
[MailZip],
[MKTGRep],
[NoOfAgents],
[OfficeAddress1],
[OfficeAddress2],
[OfficeCity],
[OfficeState],
[OfficeZip],
[OfficeZipp4],
[Owner],
[Profile],
[RepGotit],
[ShortName],
[ShowPage],
[Specialty],
[StarfishGroup],
[State],
[TaxPayerShortName],
[TaxType],
[Unit],
[Website],
[Zone])
Select Distinct [Agents] ,[AgentLoc],[AppointmentDate]
,[BusinessName]
,[BusinessPhone1]
,[BusinessPhone2]
,[BusinessType]
,[CommercialProp]
,[Contact]
,[ContactEmail]
,[CPRepGotit]
,[Crossstreet]
,[DateCreated]
,[DateLastChanged]
,[DirectConnect]
,[Direction1]
,[Direction2]
,[Direction3]
,[DisplayName]
,[EMail]
,[FaxByEmail]
,[FaxPhone]
,[License]
,[MailAddress1]
,[MailAddress2]
,[MailCity]
,[MailName]
,[MailState]
,[MailZip]
,[MKTGRep]
,[NoOfAgents]
,[OfficeAddress1]
,[OfficeAddress2]
,[OfficeCity]
,[OfficeState]
,[OfficeZip]
,[OfficeZipp4]
,[Owner]
,[Profile]
,[RepGotit]
,[ShortName]
,[ShowPage]
,[Specialty]
,[StarfishGroup]
,[State]
,[TaxPayerShortName]
,[TaxType]
,[Unit]
,[Website]
,[Zone]
from Agent full outer
join Agent0315 on Agent.Agent = Agent0315.agents
where Agent.agent = Agent0315.agents
"David Gugick" wrote:

> Lontae Jones wrote:
> You didn't qualify any of your columns in the SELECT portion. If you use
> a join, you need to qualify your columns as a matter of good practice.
> You probably have the same column name in the Agent and Agent0315
> tables.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||For each column that is in both the Agent and Agent0315 tables,
you must specify whether you want Agent.theColumn or Agent0315.theColumn.
Your column names are ambiguous because they appear in two different tables
in the FROM clause, but you don't say which you want in your result.
SK
Lontae Jones wrote:
>My select follows my insert but I am still getting ambiguos errors on all
>columns.
>Insert Into Agentlocatordata ([Agent],
> [AgentLoc],
> [AppointmentDate],
> [BusinessName],
> [BusinessPhone1],
> [BusinessPhone2],
> [BusinessType],
> [CommercialProp],
> [Contact],
> [ContactEmail],
> [CPRepGotit],
> [Crossstreet],
> [DateCreated],
> [DateLastChanged],
> [DirectConnect],
> [Direction1],
> [Direction2],
> [Direction3],
> [DisplayName],
> [E_Mail],
> [FaxByEmail],
> [FaxPhone],
> [License],
> [MailAddress1],
> [MailAddress2],
> [MailCity],
> [MailName],
> [MailState],
> [MailZip],
> [MKTGRep],
> [NoOfAgents],
> [OfficeAddress1],
> [OfficeAddress2],
> [OfficeCity],
> [OfficeState],
> [OfficeZip],
> [OfficeZipp4],
> [Owner],
> [Profile],
> [RepGotit],
> [ShortName],
> [ShowPage],
> [Specialty],
> [StarfishGroup],
> [State],
> [TaxPayerShortName],
> [TaxType],
> [Unit],
> [Website],
> [Zone])
>Select Distinct [Agents] ,[AgentLoc],[AppointmentDate]
> ,[BusinessName]
> ,[BusinessPhone1]
> ,[BusinessPhone2]
> ,[BusinessType]
> ,[CommercialProp]
> ,[Contact]
> ,[ContactEmail]
> ,[CPRepGotit]
> ,[Crossstreet]
> ,[DateCreated]
> ,[DateLastChanged]
> ,[DirectConnect]
> ,[Direction1]
> ,[Direction2]
> ,[Direction3]
> ,[DisplayName]
> ,[EMail]
> ,[FaxByEmail]
> ,[FaxPhone]
> ,[License]
> ,[MailAddress1]
> ,[MailAddress2]
> ,[MailCity]
> ,[MailName]
> ,[MailState]
> ,[MailZip]
> ,[MKTGRep]
> ,[NoOfAgents]
> ,[OfficeAddress1]
> ,[OfficeAddress2]
> ,[OfficeCity]
> ,[OfficeState]
> ,[OfficeZip]
> ,[OfficeZipp4]
> ,[Owner]
> ,[Profile]
> ,[RepGotit]
> ,[ShortName]
> ,[ShowPage]
> ,[Specialty]
> ,[StarfishGroup]
> ,[State]
> ,[TaxPayerShortName]
> ,[TaxType]
> ,[Unit]
> ,[Website]
> ,[Zone]
>from Agent full outer
>join Agent0315 on Agent.Agent = Agent0315.agents
>where Agent.agent = Agent0315.agents
>"David Gugick" wrote:
>
>

Am having script Problems with Duplicates and Inserts

Ok, here is the situation, I have a view in one database and I want to insert all the data into a table on the same server but in a different database. With a no duplicate insert, cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

So I first wrote a script that looked for duplicates, this worked.

FROM Coffee.dbo.vueProductCase a
JOIN (SELECT ProductCode, COUNT(*) AS cnt
FROM coffee.dbo.vueProductCase
GROUP BY ProductCode
HAVING COUNT(*) > 1) b
ON a.ProductCode = b.ProductCode

It displayed a list of Duplicates, so I then tried to enter this script which doesnt seem to work at all, but it could be that it is because I dont know how to combine the scripts to insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

insert dbo.tblInItem
(ItemId,Descr,ProductLine,SalesCat,UomBase,UomDflt )
select
t1.ProductCode,
t1.[Description],
t1.'COFFEE',
t1.'CS',
t1.WeightMeasurement,
t1.'EACH'
from COFFEE.dbo.vueProductCase t1 left join dbo.tblInItem t2 on t1.ProductCode = t2.itemid
where t2.itemid is null

Can I get some help please??cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

Doesn't make sense.

insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

Nope...this one doesn't make sense either.

Give us a sample table create statement with insert statements for the data. Then, show us what you want the data to look like when complete. We should be able to help you pretty quickly then. Right now, someone else might be able to help you if they understand you. I'm not getting it though. [:)]|||My question is "What error message or incorrect results are you getting".

Because I don't see anything syntactically wrong with your insert statement, and "It don't work fer nuffin at all" doesn't give us a lot of clues...

Aluminum block with steel liner

What are the amnufacturing problem associated with cast in steel line
with aluminum dia casting process. Will it be better to insert the steel
liners after the machining process on the aluminum block.
*** Sent via Developersdex http://www.examnotes.net ***Since SQL Server is set associative it shouldn't matter the order in which
you do the join.
"joe inciong" wrote:

> What are the amnufacturing problem associated with cast in steel line
> with aluminum dia casting process. Will it be better to insert the steel
> liners after the machining process on the aluminum block.
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||On Wed, 15 Feb 2006 09:26:27 -0800, "Matthew Speed"
<MatthewSpeed@.discussions.microsoft.com> wrote:
>Since SQL Server is set associative it shouldn't matter the order in which
>you do the join.
>"joe inciong" wrote:
>
LOL.
J.|||This guy is way past lost.
Is there really a news group for aluminum die casting?
Sorry... I just answered my own question.
rec.crafts.metalworking
"Matthew Speed" <MatthewSpeed@.discussions.microsoft.com> wrote in message
news:42AA28A6-B5C5-4E7F-AFA5-327514E3D5E5@.microsoft.com...
> Since SQL Server is set associative it shouldn't matter the order in which
> you do the join.
> "joe inciong" wrote:
>|||Steel liners in an aluminum block sounds like a piston engine to me...
"Raymond D'Anjou" wrote:

> This guy is way past lost.
> Is there really a news group for aluminum die casting?
> Sorry... I just answered my own question.
> rec.crafts.metalworking
> "Matthew Speed" <MatthewSpeed@.discussions.microsoft.com> wrote in message
> news:42AA28A6-B5C5-4E7F-AFA5-327514E3D5E5@.microsoft.com...
>
>|||Yes. Diesel engine especially also small engine like in lawn tractors. Most
cars engine are either cast iron or alumnin. Wait a miniute am i replyin in
the wrgon group?
Yes. Diesel engine and small engine like in lawn tractors. Most cars engine
is either cast iron or aluminum with no sleeves but there are some.
"Matthew Speed" <MatthewSpeed@.discussions.microsoft.com> wrote in message
news:5788352E-D44A-4BED-8C80-D67CB3644CB4@.microsoft.com...
> Steel liners in an aluminum block sounds like a piston engine to me...
> "Raymond D'Anjou" wrote:
>|||
"Grant" wrote:

> Yes. Diesel engine especially also small engine like in lawn tractors. Mos
t
> cars engine are either cast iron or alumnin. Wait a miniute am i replyin i
n
> the wrgon group?
>
It depends. Are you a SQL Server user or was this a response to a very
badly cross-posted message. My original response was just to have fun with
the fact that I could make an association between RDBMS and reciprocating
engine technology. If you are trying to give a real answer to the original
question take it to the correct group. This group is going to take it
absolutely nowhere.|||Opps. I was having fun until I offended you and yes I am a sql programmer.
Every one has a different sense of humor. It's a big world and it revolves
around the sun not you.
Cheer and now I will run away....
"Matthew Speed" <MatthewSpeed@.discussions.microsoft.com> wrote in message
news:C1FDD56B-F659-4A91-9C91-57538D9F6EF8@.microsoft.com...
>
> "Grant" wrote:
>
> It depends. Are you a SQL Server user or was this a response to a very
> badly cross-posted message. My original response was just to have fun
> with
> the fact that I could make an association between RDBMS and reciprocating
> engine technology. If you are trying to give a real answer to the
> original
> question take it to the correct group. This group is going to take it
> absolutely nowhere.|||"Grant" <email@.nowhere.com> wrote in message
news:uLAFGpvMGHA.3264@.TK2MSFTNGP11.phx.gbl...
> Opps. I was having fun until I offended you and yes I am a sql programmer.
> Every one has a different sense of humor. It's a big world and it revolves
> around the sun not you.
Not nearly as much as you offended me. You completely left out the plastic
lawn mower engines. You know, the ones with the little balls that bounce
around when you mow the lawn?

>
> Cheer and now I will run away....
>|||LAMO!!! my ten month old son has that.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23dtGE0wMGHA.516@.TK2MSFTNGP15.phx.gbl...
> "Grant" <email@.nowhere.com> wrote in message
> news:uLAFGpvMGHA.3264@.TK2MSFTNGP11.phx.gbl...
> Not nearly as much as you offended me. You completely left out the
> plastic
> lawn mower engines. You know, the ones with the little balls that bounce
> around when you mow the lawn?
>
>

Alternatives when trying to keep two databases in sync...

I have two production SQL Server 2005 databases that I want to keep in
sync ever few hours. One database is an OLTP database with high
INSERT activity, the other is strictly for reporting. The OTLP
database will contain data for 24 months of data. The reporting
database will contain that same 24 months plus an additional 8 years of
data.
The tables in each database are identical but I would like to have
different indexing strategies in each database. On OLTP side, indexing
would be minimal to facilitate rapid insert activity. The reporting
database would need many indexes in order to process queries in a
reasonable amount of time.
Is it possible to keep these two databases in sync with log shipping?
>From what I can see, log shipping really wants the two database to be
identical. Having the two different indexing strategies would not be
easy. Keeping more data in the reporting database would also present
some challenges.
Is there a better alternative? One-way transaction level replication
seems like an alternative but seems like it would require a great deal
of maintenance.
I have given Triggers some thought but the developers push back saying
that there are over 50 tables that need to be synchronized and that
maintaining the triggers is to much work.
Any suggestions would be appreciated.
Thank you.
Jim Maurer
DBA
Harleysville InsuranceI think you need to look at Replications, because triggers are hurting
performance and
log shipping is intended to different proposes.
I'm thinking what if you could take one big massive transferring of data
let me say once a day at night by using DTS package , is it accetable at
you company?
<jmaurer@.harleysvillegroup.com> wrote in message
news:1166023880.609621.98730@.79g2000cws.googlegroups.com...
>I have two production SQL Server 2005 databases that I want to keep in
> sync ever few hours. One database is an OLTP database with high
> INSERT activity, the other is strictly for reporting. The OTLP
> database will contain data for 24 months of data. The reporting
> database will contain that same 24 months plus an additional 8 years of
> data.
> The tables in each database are identical but I would like to have
> different indexing strategies in each database. On OLTP side, indexing
> would be minimal to facilitate rapid insert activity. The reporting
> database would need many indexes in order to process queries in a
> reasonable amount of time.
> Is it possible to keep these two databases in sync with log shipping?
>>From what I can see, log shipping really wants the two database to be
> identical. Having the two different indexing strategies would not be
> easy. Keeping more data in the reporting database would also present
> some challenges.
> Is there a better alternative? One-way transaction level replication
> seems like an alternative but seems like it would require a great deal
> of maintenance.
> I have given Triggers some thought but the developers push back saying
> that there are over 50 tables that need to be synchronized and that
> maintaining the triggers is to much work.
> Any suggestions would be appreciated.
> Thank you.
> Jim Maurer
> DBA
> Harleysville Insurance
>|||Thank you. We are begining to realize this is not going to be as easy
as it orginally sounded. Thank you again for your suggestions...
Uri Dimant wrote:
> I think you need to look at Replications, because triggers are hurting
> performance and
> log shipping is intended to different proposes.
> I'm thinking what if you could take one big massive transferring of data
> let me say once a day at night by using DTS package , is it accetable at
> you company?
>
>
> <jmaurer@.harleysvillegroup.com> wrote in message
> news:1166023880.609621.98730@.79g2000cws.googlegroups.com...
> >I have two production SQL Server 2005 databases that I want to keep in
> > sync ever few hours. One database is an OLTP database with high
> > INSERT activity, the other is strictly for reporting. The OTLP
> > database will contain data for 24 months of data. The reporting
> > database will contain that same 24 months plus an additional 8 years of
> > data.
> >
> > The tables in each database are identical but I would like to have
> > different indexing strategies in each database. On OLTP side, indexing
> > would be minimal to facilitate rapid insert activity. The reporting
> > database would need many indexes in order to process queries in a
> > reasonable amount of time.
> >
> > Is it possible to keep these two databases in sync with log shipping?
> >>From what I can see, log shipping really wants the two database to be
> > identical. Having the two different indexing strategies would not be
> > easy. Keeping more data in the reporting database would also present
> > some challenges.
> >
> > Is there a better alternative? One-way transaction level replication
> > seems like an alternative but seems like it would require a great deal
> > of maintenance.
> >
> > I have given Triggers some thought but the developers push back saying
> > that there are over 50 tables that need to be synchronized and that
> > maintaining the triggers is to much work.
> >
> > Any suggestions would be appreciated.
> >
> > Thank you.
> >
> > Jim Maurer
> > DBA
> > Harleysville Insurance
> >

Alternative to Temporary table to store stored procedure results

I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.
Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>
|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...

Alternative to Temporary table to store stored procedure results

I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...

Alternative to Temporary table to store stored procedure results

I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
> >I have come across the error "INSERT EXEC statement cannot be nested"
> > when trying to store the results of a stored procedure in a temporary
> > table. I understand why this is happening - because there is already
> > an INSERT EXEC in the stored procedure I am executing - but I need to
> > be able to store the results in some way.
> >
> > Unfortunately re-writing the stored procedure I am calling is not an
> > option, and I was wondering if there is another way for me to evaluate
> > the results from my stored procedure.
> >
> > I have looked into table variables and functions, but they do not work
> > here either.
> >
> > I would appreciate anyone's input on this.
> >