Hi,
I have a sproc with 5 params that takes about 40 seconds to return.
But when I Create a Temp table and do a
Insert Into #temp
Exec sproc param1, param2, param3, param4, param5
it never returns...
any ideas?
Thanks,
Bill
Hi
When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....).
Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
o INSERT INTO #TMP_F_B (
o Field1 ,
o Field2,
o Field3 ,
o …,
o )
o EXEC dbo.ps_a5s_rpt_charge700 @util, @annee , @perimetre , @secteur , @poste , @fournisseur , @err , @lib_err
Many thanks
Sincerly
Hi I have the following Stored Proc which works in SQL Server 6.5 but not in SQL Server 7.0. All this Stored Proc does is Create a temp table, execute the DBCC ShowContig on a table and insert the results of the DBCC into a temp table. What am I missing. Thanks.
The code of the Stored Proc is:
/* This Stored Procedure Creates a temp table. (Step 1) */
/* Initializes a local variable @StirngToBeExecuted with */
/* a DBCC command. (Step 2) */
/* Step 3. The Command is Executed and the results of the */
/* DBCC command is inserted into Temp Table. */
/* Step 4. The results of the Temp table are shown on the */
/* Screen. */
/* This SQL Works Fine in SQL Server Version 6.5 */
/* In SQL Server 7.0 the results of the DBCC command is */
/* NOT getting inserted into the Temp table. WHY??? */
IF EXISTS (SELECT * from sysobjects where id = object_id('dbo.Test_sp') and sysstat & 0xf = 4)
drop procedure dbo.Test_sp
GO
CREATE PROCEDURE Test_sp
AS
DECLARE
@StirngToBeExecuted Varchar(100)
CREATE TABLE #temp( -- Step 1
OutputOfExecute Varchar(255)
)
-- Step 2
SELECT @StirngToBeExecuted = 'DBCC SHOWCONTIG (123456789)'
INSERT
INTO #temp exec (@StirngToBeExecuted) -- Step 3
SELECT * FROM #temp -- Step 4
DROP TABLE #temp --Drop the Temp Table
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
Hello,
I need to get the count into a local variable:
Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause
Exec(@SQL)
It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.
Any help greatly appreciated,
Judith
And no, not through EM or SSMS
I want to interogate the catalog, so I can hae a job execute and do the grants in case a developer forgets
I mean I guess I can do the all everytime, but I don't know what the impact would be. It appears to be none, because of already done that, but in DB2 that would be bad as it would keep adding rows to the system tables
I have this so far
select *
from syspermissions p
inner join sysusers u
on u.uid = p.grantee
inner join sysobjects o
on o.id = p.id
where u.name = 'mepuser'
and o.name not like 'dt_%'
order by p.id
The M$ catalog is a royal pain
Folks
Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity .....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.
update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID
FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.
Thanx
Hi,
I'm trying to capture the value returned from sprocs. I stored the sproc name in the table and use cursor to run each sproc. Now the question is how can I capture and store the return value in a variable?
Here is the scenario:
Table1 has 1 column varchar(50) called vchsprocname
count_A -- procedure, select count(*) from ...
count_B -- procedure, select count(*) from ...
count_C -- procedure, select count(*) from ...
here is my query:
----------------------------------------------------
DECLARE @vchsprocname varchar(50)
DECLARE @count int
DECLARE cur CURSOR FOR
SELECT vchsprocname from table1
OPEN cur
FETCH NEXT FROM cur
into @vchsprocname
WHILE @@FETCH_STATUS = 0
BEGIN
exec @count = @vchsprocname -- I know I cannot do this, the vchsprocname cannot be variable. What else can I do?
FETCH NEXT FROM cur
into @vchsprocname
END
--------------------------------------------------
I need to call a sproc about 1000 times and build a table from all the results.
How do I write an insert statement that will take the recordsets from the sproc and put it into a temp table?
I'm trying to do something like this:
Loop through #Temp_1
-Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter
-Store result set of Sproc_ABC into #Temp_2
-Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ
End Loop
It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
View 4 Replies View RelatedBeginning to learn sprocs.
I am having trouble finding a way to work with dates. Below is a very simplified sproc I am trying to get to work. It will later be expanded, but first things first:
CREATE PROCEDURE spCellarOp4
@ReportDate datetime = getdate
AS
SELECT FERMID, fldfermtank, fldfilltime, fldfermprod,
"Sample Test" = CASE
WHEN CONVERT(int, DATEDIFF(dd, fldfilltime, @ReportDate)) = 1 THEN ' it is 1 '
END
FROM vwUnfilteredFermenters
The error message is:
Server: Msg 241, Level 16, State 1, Procedure spCellarOp4, Line 0
Syntax error converting datetime from character string.
Help, anybody? TIA. D. Lewis
here's an example of what I am trying to do.
--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
In query analyzer the correct data is returned.
But when I run it in my application no records ae displayed.
In Sql profiler it says completed and I use the values in query analzyer it works fine. here is my stored proc:
CREATE PROCEDURE [dbo].oc_OnlineCaseOrder
@CategoryId int,
@Order varchar(25)
As
SET @CategoryId = @CategoryId
SET @Order = @Order
If @Order = 'Alpha'
Begin
SELECT
oc.[CaseId],
oc.[StatusId],
oc.[CategoryId],
oc.[Title],
oc.[CaseText],
oc.CourseId,
occ.Description AS CategoryDescription,
ocs.Description AS StatusDescription
FROM
[dbo].oc_OnlineCase oc WITH (nolock)
JOIN dbo.oc_OnlineCaseCategory occ WITH (nolock) ON oc.CategoryId = occ.CategoryId
JOIN dbo.oc_OnlineCaseStatus ocs WITH (nolock) ON oc.StatusId = ocs.StatusId
WHERE oc.CategoryId = ISNULL( @CategoryId, oc.CategoryId )
AND oc.StatusId = 100
Order by oc.[Title]
END
ELSE
Begin
DECLARE @TempCaseIds Table
(
CaseId int,
CategoryDescription varchar(30)
)
SELECT MIN(oca.AuditDate) as "Recent Case Publication", oca.CaseId, occ.[Description]
INTO #TempCaseIds
From
[dbo].oc_OnlineCaseAudit oca WITH (nolock)
JOIN dbo.oc_OnlineCase oc WITH (nolock) ON oc.CaseId = oca.CaseId
JOIN dbo.oc_OnlineCaseCategory occ WITH (nolock) ON oc.CategoryId = occ.CategoryId
JOIN dbo.oc_OnlineCaseStatus ocs WITH (nolock) ON oc.StatusId = ocs.StatusId
WHERE oc.CategoryId = ISNULL( @CategoryId, oc.CategoryId )
AND oc.StatusId = 100
Group by oca.CaseId,occ.[Description]
Select DISTINCT
CaseId, [Description]
From #TempCaseIds
End
GO
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
I am trying to take a table of Customer locations and making a single string out of them. Any help would be appreciated.
Example:
CustomerLocation = "Web"
CustomerLocation = "North Carolina"
function call..
getCustomerLocations()
output:
Web,North Carolina
:confused:
I am trying to insert a value with Insert statement in a sproc
create procedure AddNewPersonsFromALSHeadr
as
begin
insert into dbo.Persons(lngSSN,
strNamePrefix,
strFirstName,
lngHomeID,
lngMailID)
select lngFedTaxID,
strNamePrefix,
strFirstName,
exec @HomeId = GetAddress H.strAddress1,strAddress2,H.strCity,H.strState,H.s trZip,H.intZip4,
@HomeId
from dbo.ALSHeadr H
end
go
The probl is while inserting the fourth column values I have to execute a sproc
"GetAddress" and take the value and insert it in "lngHomeID" and the the same value in
"lngMailID". I tried all possible ways for the syntax but 'm getting the error
incorrect sytanx near exec and also syntax error near " . " at H.strAddress1 etc......
FYI this is the sproc GetAddress(I omitted few lines in this sproc)
CREATE procedure dbo.GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
------------------------------------------
------------------------------------------
--------------------------------------
EXEC @ZipID= dbo.GetZipID @City,@State,@Zip5
return(@AddrID)
print (@AddrID)
end
GO
Tks
I have been working for days to translate a report out of an old system and in SQL reporting services.
Getting the basic code down to get the required data was easy, however getting it to work in reporting services has turned into a nightmare.
Why, because I have been told that SQL reporting services does not allow temporary tables...HUH!
Ok, so how am I supposed to take three data queries and munge them together into a report.
Here is the query that does work, can anyone give me an idea of how to make this work given the limitations I have run up against.
I have already thought of using a store procedure, but we have ruled that out since would likely have to do it via linked servers, which would be expensive. We thought of having it just create and then link real tables and then delete them...not sure thats going to work, and again probably talking linked servers to get that to work.
Code:
select distinct al3.asg_location into ##tmp1
from dbo.probsummarym2 AL3
inner join dbo.probsummarym1 AL1 on AL3.number=AL1.number
where AL1.assignment='international client services'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
and al3.asg_location is not null
SELECT AL3.asg_location as asg_location, Count (AL3.resolve_met) as met_sla into ##tmp2
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.severity_code<>'Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
AND AL3.resolve_met='t'
AND AL1.assignment='international client services'
)
GROUP BY AL3.asg_location
SELECT AL3.asg_location as asg_location, Count (AL1.status) as sch_closed into ##tmp3
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.assignment='international client services'
AND AL1.severity_code='Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
)
GROUP BY AL3.asg_location
SELECT AL3.asg_location as asg_location, Count (AL1.status) as unsch_closed into ##tmp4
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.assignment='international client services'
AND AL1.severity_code<>'Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
)
GROUP BY AL3.asg_location
select ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed
from ##tmp1 left outer join ##tmp2 on ##tmp1.asg_location = ##tmp2.asg_location
left outer join ##tmp3 on ##tmp1.asg_location = ##tmp3.asg_location
left outer join ##tmp4 on ##tmp1.asg_location = ##tmp4.asg_location
group by ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed
order by ##tmp1.asg_location
drop table ##tmp1, ##tmp2, ##tmp3, ##tmp4
I am trying to write a sproc that will insert some values into a table. The values are going to be entered by the user and then stored into a variable. These will then be written to the database. I have done some research but can't find an example to work from. I keep getting hung up on getting the syntax right.
Can anyone help? Does anyone know of any good articles?
Here is what I have so far:
USE database
CREATE PROC spInsertNewRecord
@PetID int,
@CustID int,
@PetName varchar(20),
@BreedID int, these are the variables
@Gender char(1),
@Weight decimal,
@DOB smalldatetime,
@DateFixed smalldatetime
INSERT INTO Pets(PetID, CustID, PetName, BreedID, Gender, Weight, DOB, DateFixed)
VALUES(@PetID, @CustID, @PetName, @BreedID, @Gender, @Weight, @DOB, @DateFixed)
GO
...........................................Thanks to anyone that can help
Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDURE fetchpersondetails
AS
CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300),
StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200),
Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
Insert into #tempperson
SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName,
dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress,
dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress,
dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone,
dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2,
N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email,
dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category,
dbo.tblCountry.countryNameFull AS countryaddress
FROM dbo.tblperson INNER JOIN
dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN
dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
please let me know the solurion of this error.
I am trying to create a SQL data adapter via the wizard, however, I get
the error "Invalid object name #ords" because the stored procedure uses
a temp table. Anyway around this? Thanks.
I need to insert the results into a temp table and i recieve the "MSDTC on server 'servername' is unavailable error".
declare @thestringall varchar(1000)
set @thestringall = 'select statment here'
insert into #temptable exec (@thestringall)
Thanks
Wooanaz
I need get the o/p of a system sp into a table. I am doing the following,
insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher =
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'
Code is really not important. Any sys SP can replace the above code.
I am getting the following error
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.
I have seen the following link which discuss this issue,
http://www.sommarskog.se/share_data.html
But there is no solution there.
I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?
------------------------
I think, therefore I am - Rene Descartes
I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT.
Basiclly, I want to use a conditional on the Insert.
Code Block
CREATE TABLE TestTable
(Col1 int NULL,
Col2 varchar(50) NULL)
GO
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')
INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')
This results in an "Unknown column" error.
What am I missing here and how can I accomplish it.
I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.
Any help would be appreciated
Hi,
I have a stored procedure that transfers data from one database to another. I do a lot of manipulation to the data in between because although both databases are for the same purpose, the database designs are different. The two databases exist on two different servers. Till now my transfer procedure used a linked server to pull the data from one server to the other. This process takes a ton of time with complex queries across linked servers. I was researching on a faster method to transfer data and the only other working method seemed to be the insert..exec statement with which I can call a remote stored procedure which would run and return the results of the complex queries which I can then use locally as I wish.
My main question is, can I rely on insert..exec to work consistently and/or is there another method to achieve what I need that I'm totally ignorant of? I already know of inline/multi table value functions (they wont work across servers); openquery/openrowset (I need to be able to pass parameters); creating permanent tables to be used and then destroyed (not very appealing). I read an excellent article about these online by Erland Sommarskog (http://www.sommarskog.se/share_data.html) but I'm hoping someone can explain a little further.
Thanks all,
Anand
There must be some workaround for this. I need to grab the data from a stored proc on another server and place it into a temp table. I do not have the option to change the proc.
help if you can.
Hi,
has anyone come across deadlocks on sysindexes in tempdb where the insert/exec combination is used.
eg
create table #fred (IntColumn int)
insert into #fred exec ProcThatSelectsAnIntColumn
This is being done in a stored procedure, and is deadlocking with other procs which are doing vanilla #table work - creating, inserting into, updatind, selecting from, etc.
I have noticed similar deadlocks where a #table is created inside an explict transaction, and I wondered whether there is an implicit transaction created, but @@Nestlevel is not changing either before or after the insert/exec.
I can't find any references in knowledgebase.
Any pointers appreciated.
Cheers
Simon
________________________
Simon Davis
Bankers Trust Australia Limited
Asset Management Technology
Ph: 61 2 9259 9137
<mailto:Simon.Davis@Bankerstrust.com.au>
Hi all,
I have an sp that sends cdomail which requires 4 variables.
I want an after insert trigger that fills in the values for the sp from the record just submitted, how can i do that?
Sp code
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.bbeyond.nl'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
Hello,I want to share my experiences about using insert into exec which mayhelp others .Using SQL Server 2000, SP3 .Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicittransactions .I have defined a # table in Parent SP and calling a Child SP like thisinsert into #temp exec childsp ......Child SP has Select * from local # temp table ( local to child SP) as the last statement .When number of records are less ( around 1000 - 5000) Parent SPexecutes but slow .When the Child SP returns higher number of rows ( 1,00,000 or more )the SP will be running for hours with out completion .Although executing the child SP , with exec ChildSP .... with sameparameters it is completed in 2 mins for 3,00,000 rows .Resolution : - Define a temp table (say #tempChild ) in the Parent SP..In the Child SP instead of select * replace with insert into#tempChild select * from ...Also note that this problem is not noticed in SQL 2000 Server with SP4..This may be due to SP executing in implicit transactions .
View 1 Replies View RelatedI 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
Hi,
I have written a master proc which calls another proc (say proc1).
This proc1 has insert-exec statements, for eg insert into #temp exec proc1.
i.e. multiple times the proc would be nested.
This the err thrown :
An INSERT EXEC statement cannot be nested.
Is it possible to resolve it..