Why The Log File Is Growing Too Much Upon Running Nested Stored Procedures (MS SQL 2K)
Feb 11, 2006
Hi there,
I have a data manipulation process written in a Nested Stored procedure
that have four levels deeper. When I run these individual procedures
individually they all seems to be fine. Where as when I run them all
together as Nested proces (calling one in another as sub-procedures) Log
file is growing pretty bad like 25 to 30GB.. and finally getting kicked
after running disk space. This process is running around 3hrs on a SQL
serever Standard Box having dual processer and 2gb ram.
This procedures have bunch of bulk updates and at least one cursor in
eacch procedure that gets looped through.
I was wondering if anybody experienced this situation or have any clue
as to why is this happening and how to resolve this?
I am in a pretty bad shape to deliver this product and in need of urgent
help.
Any ideas would be greatly appreciated..
Thanks in advance
*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
ADVERTISEMENT
Nov 25, 2001
I cannot seem to get nesting of stored procedures to work. I must have the syntax wrong. Should the following work:
SELECT *
FROM spWod_rptWoStatusSummary
Where spWod_rptWoStatusSummary is a Stored Procedure.
The above does not work. I get an error telling me that spWod_rptWoStatusSummary is an object that does not exit.
Does anyone know what the correct syntax is?
View 1 Replies
View Related
Nov 25, 2001
I cannot seem to get nesting of stored procedures to work. I must have the syntax wrong. Should the following work:
SELECT *
FROM spWod_rptWoStatusSummary
(Where spWod_rptWoStatusSummary is a Stored Procedure).
The above does not work. I get an error telling me that spWod_rptWoStatusSummary is an object that does not exit.
Does anyone know what the correct syntax is?
View 1 Replies
View Related
Dec 5, 2005
Hi Out There
The attempt to create the following stored procedure, which is supposed to call the previously created stored procedures inside it,
causes the following errors.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDTwo'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDThree'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Create Procedure spPrepareOfferSimulation
@OfferIDOne int
As
exec spPopulateOfferTables @OfferID
go
exec spPopulateOfferProduct @OfferID
go
exec spPopulateOfferDictionary @OfferID
go
exec spPopulateOfferCondition @OfferID
go
exec spPopulateOfferError @OfferID
go
exec spPopulateOfferLimit @OfferID
go
exec spPopulateOfferQA @OfferID
go
Can someone provide me with some productive input to fix this mess?
View 10 Replies
View Related
Apr 14, 2008
I have a caller stored procedure (sp_A) which calls several stored procedures (sp_1,sp_2,sp_3,sp_4, sp_5)
The statements in the called stored procedures sp_1 to sp_5 are all contained within BEGIN TRY -- BEGIN TRAN and BEGIN CATCH blocks which ensure that any errors in the SQL statements are rolled back when an error occurs.
However, I have noticed that when the called stored procedure fails, the caller procedure does not fail Rather it continues processing the remaining sps. I want to add code in my calling sp to stop this fom happening. Any ideas?
I have used this statement within my calling sp but no joy yet.
set @ErrCode = 0
exec @ErrCode = sp_executesql @Sql
if @ErrCode <> 0
begin
return 1
end
Any one with ideas? Thanks
View 12 Replies
View Related
Sep 28, 2007
I need the output of a stored procedure in another SP. That's simple (using a temporary table). But there's a small (big error ) problem.
Since I have to manually define the temporary table with it's fields and datatype to recieve the output from the nested SP , this approach would fail if, in the future more parameters are required to be returned . Is it possible to immunise my SP to such a consequence at creation time, rather them having to amend the temporary table later ?
Any idea how to overcome the above problem ? Is there a way that the temporary table can be automatically created like we have in a SQL statement with the INTO keyword. Any ideas ?
Thanks,
Alec
View 9 Replies
View Related
Apr 30, 2008
I have a stored procedure that returns a scalar value and I want to use that value (among other places) within another stored procedure.
-- The general purpose stored procedure is far too long to include here but I've included the last several lines of code at the bottom to show general gist of it and how it exits.--
The only way I can find to use that returned value "criteria" in a stored procedure is to define a temporary table, INSERT it into the table and then extract it from the temprary table into the variable where I actually wanted it.... i.e.
DECLARE @CriteriaTable TABLE ( Criteria VARCHAR(8000) )INSERT INTO @CriteriaTable (Criteria) EXEC psp_WRP_DisplayCriteria @UserID, 'Dealers, Prefix, Serial Range, Models, Makes, Sold Date', @UserGroup SELECT @Criteria=Criteria FROM @CriteriaTable
This seems like a ridiculously long winded and confusing way of doing things, especially since Im doing it in a dozen different procedures, half a dozen reports and 1 (so fasr) asp.net/VB web site - and I can't help thinking there must be a better way using just one or even zero extra lines of code to do this.
e.g. SELECT@Criteria = (EXEC dbo.psp etc...)
... or some variation thereof with the correct syntax.
or even better going to where that variable is used and changing ...
EXEC dbo.[psp_InsertWarrantyReportTracking]
@UserID = @UserID ,
@ReportName = 'rptChassisTrackExtdWarranty', -- <------
@ReportCriteria = @Criteria
to ...
EXEC dbo.[psp_InsertWarrantyReportTracking] @UserID = @UserID , @ReportName = 'rptChassisTrackExtdWarranty', -- <------ @ReportCriteria = (EXEC dbo.psp_ etc...)... or some variation thereof with the correct syntax.
But no matter how I try and how I search I can't find any way other than in what is otherwise a completely supefluous temporary table.
Am I missing something or is that REALLY the only way to get a hold of that returned value?
------ Last portion of the general purpose routine I'm trying to call ---------------------If Patindex('%RO Audit%',@Parmlist) > 0
Begin
set @Criteria = @Criteria + '- RO Audit date'
select @All=ROAuditAll, @From = ROAuditFrom, @To = ROAuditTo
from dbo.tblWRParameters where @UserID = UserName
If @All = 1
set @Criteria = @Criteria + '- ALL' + @NL
else
set @Criteria = @Criteria + 'is between ' + rtrim(@From) + ' and ' + rtrim(@to) + @NL
END
--Return the combined parameter field
select @Criteria as Criteria
END
View 12 Replies
View Related
Mar 22, 2001
Has anyone faced problems in calling one stored procedure from within another stored procedure ? I am considering using nested SPs, and would appreciate any inputs on it.
Thks,
SC
View 2 Replies
View Related
Mar 21, 2008
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
Is there any way to do either of those?
View 1 Replies
View Related
Jan 7, 2008
For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0
Thanks,
Russ
-----------------------------------------------------------
Below is what I have
Step 1)
ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS
DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY
if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment
update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Step 2)
/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS
Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)
end
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID
STEP 3
ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS
declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED
BEGIN TRY
PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
STEP 4
ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)
AS
declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/
select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id
set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor
BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'
,@recipients = 'russ@test.com'
print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT
print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))
update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID
--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
View 5 Replies
View Related
Jun 8, 2001
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
Thanks,
Glen Smith
View 1 Replies
View Related
Jul 23, 2005
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
View 5 Replies
View Related
Apr 16, 2007
Hi all,
I had developed all my reports using CRXI and stored procedures of MSSQL 2000. Now I am migrating from CRXI to SSRS. But i have a problem because of my report stored procedures. These stored procedures are complex and nested. When i try to use procedures in a report, it gives a error. When i comment out my EXEC line in my procedures, it goes OK.
Please help me to do this ?
View 1 Replies
View Related
Sep 15, 2014
Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.
View 8 Replies
View Related
Jul 9, 2006
Hi,
I would like to use a stroed procedure within another stored procedure ( nested sp )
in a SQL project in VS.NET 2005. Since I have to use "context connection = true" as
connection string, I wont be able to use this connection for another sqlconnection
object. Because its already open. and If i try to use a regular connection string
("server=localhost;...") it will raise a security permission error. Having this
problem, Im not able to use nested stored procedures. Would anyone please give me a
hint how to resolve this issue?
View 4 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Apr 17, 2000
I need to run stored procedures which are currently run on server A(located in another city),by connecting myself to it through my laptop running on SQL server 7.0(only client components installed)on NT workstaion4.0. Can anyone tell all the steps involved to run those stored procedures everyday.Even I need to monitor Server A from my laptop.Please advice...Urgent!!
View 1 Replies
View Related
Jul 20, 2005
I have two similar stored procedures which I'm running. One runs andone doesn't. I can run both with no problems in SQL Enterprise (7.0standard) and have checked the permissions and am happy with them.Whilst the statements in each sp are different, I'm calling them inexactly the same way (using Delphi 5 Windows 2000). I don't get anytrappable errors.I ran a trace on what was happening and I get two different set ofresults. This is what I'm trying to get my head around.Client Trace (runs)1 09:09:44 Log started for: Swift Client Import Utility2 09:09:48 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftClient;13 09:09:48 SQL Misc: MSSQL - Set stored procedure on or off4 09:09:48 SQL Data In: MSSQL - Param = 1, Name = Result, Type= fldINT32, Precision = 0, Scale = 0, Data = NULL5 09:09:48 SQL Misc: MSSQL - Set statement type6 09:09:48 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftClient;17 09:09:48 SQL Stmt: MSSQL - Close8 09:09:53 SQL Connect: MSSQL - Disconnect NEW9 09:09:53 SQL Connect: MSSQL - Disconnect NEW10 09:09:53 SQL Connect: MSSQL - Disconnect PASSTHRUEnquiry Trace (Doesn't run)1 09:08:21 Log started for: Swift Client Import Utility2 09:08:24 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftEnquiries;13 09:08:24 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftEnquiries;14 09:08:50 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftEnquiries;15 09:08:50 SQL Misc: MSSQL - Set stored procedure on or off6 09:08:50 SQL Data In: MSSQL - Param = 1, Name = Result, Type= fldINT32, Precision = 0, Scale = 0, Data = NULL7 09:08:50 SQL Misc: MSSQL - Set statement type8 09:08:50 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftEnquiries;19 09:08:50 SQL Vendor: MSSQL - dbrpcinit10 09:08:50 SQL Vendor: MSSQL - dbrpcexec11 09:08:50 SQL Vendor: MSSQL - dbsqlok12 09:08:50 SQL Vendor: MSSQL - dbresults13 09:08:50 SQL Vendor: MSSQL - dbnumcols14 09:08:50 SQL Vendor: MSSQL - dbcount15 09:08:50 SQL Stmt: MSSQL - Close16 09:08:50 SQL Vendor: MSSQL - dbdead17 09:08:50 SQL Vendor: MSSQL - dbcancel18 09:08:56 SQL Connect: MSSQL - Disconnect NEW19 09:08:56 SQL Connect: MSSQL - Disconnect NEW20 09:08:56 SQL Connect: MSSQL - Disconnect PASSTHRU21 09:08:56 SQL Vendor: MSSQL - dbdead22 09:08:56 SQL Vendor: MSSQL - dbfreelogin23 09:08:56 SQL Vendor: MSSQL - dbcloseI would have thought that these would be nearly identical. They callsp's on the same servers in the same way, so the call to do thisshould (in my mind) be the same.I can post the sp's if anyone thinks they are of relevance.I can of course set up these stored procedures to run at a certaintime, but I'd like to try and understand this a little more.Thanks in advance.Ryan
View 1 Replies
View Related
Dec 13, 2004
Hi,
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?
View 4 Replies
View Related
Jul 15, 2007
Hi allI am looking for the best method to automate a website's database management. Lets say I have a user registration database and the users register. This sends an automated email to the user with a link to activate the users registration. If the user does not register within 24 hours, his registration must be automatically deleted from the database using a stored procedure.I know how to do this using the global.aspx file, however there must be an alternative way of doing this, especially if the database is an SQL database. I do not know how much MSSQL server access is given to a developer by an as ISP who hosts the website.Can anyone tell me what would be the best method to use.ThxWarren
View 1 Replies
View Related
Nov 24, 1999
Developers have complained that some of their jobs are taking too long to
run. I used Profiler to trace one really bad performer and could see that
the code was making one pass through its loop in about 7 seconds. I found
one select statement that was using the majority of the time, plugged it into
query analyzer and looked at the execution plan. It was using an index but
not very efficiently. I then ran it through index analysis and it
recommended a new index. This was cool! The new index helped.
But, the job continued to run very, very slowly - over a week before it died!
I ran another trace, and it was hanging for 10+ seconds in random locations.
Sometimes it would be on a database call, sometimes it would be on a simple
SET @variable = value statement. There was no pattern. According to the
sys admin, the server itself did not look taxed. It does not appear to be a
locking problem because of the random location of the stall. The application
and server are only lightly used at this point, so I would hate to see how
bad it could be if it was busy.
I am still fairly new to SQL, though I have years of DBA experience. I would
appreciate any ideas - especially the obvious things that I am probably not
considering. I have logged a call with Microsoft but they haven't gotten
back with an answer. I sent them a trace file and a perfmon file, neither
of which showed them anything.
Thanks!
View 2 Replies
View Related
Jul 14, 2015
I have a strange situation when I try to execute the same Stored Procedure on servers with different processors. Both servers are running the SQL SERVER 2008 R2 version with all updates.
All updates bios, disk controller, firmware, were applied on the new server.
New Server: (considerable difference in processing time)
Stored Procedure without encryption, runs at about 02:16hs
Stored Procedure with encryption, runs at about 08:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.60GHz E5-2697 v3
Old Server: (There Are No difference in processing time)
Stored Procedure without encryption, runs at about 01:00hs
Stored Procedure with encryption, runs at about 01:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.7GHz E5-2697 V2
In terms of configuration, the server 2, have lower technology, lower bus, lower number of processors.
I believe that combination of Hardware/SO/Sql Server has a potential performance loss when running encrypted SP.
View 3 Replies
View Related
Jan 6, 2007
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error
'80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from
TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server
or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error
Many thanks, Please help, appreciated
View 4 Replies
View Related
Jun 16, 2014
The TEMPDB transaction log file keeps growing.The database server is new and the transaction log was presized to 1 GB on installation. After installing a number of databases, the log file grew over a day to 38GB. Issuing a manual checkpoint was the only way to free some space to allow it to be shrunk back to a usable size. The usage of the file is still going up.
I am struggling to find what process is causing the log to be used so heavily. Looking at the log reuse wait desc for tempdb returns "Nothing" and tempdb itself isn't being used very much or growing in size.
View 9 Replies
View Related
Feb 5, 2008
i have sql server 2005 and sqlserver management studio;I want to get all stored procedures in my db as a filewhat can I do to get all stored procedures together as a single file
View 3 Replies
View Related
Mar 2, 2000
May i know whether i can write some text/string to
a file from inside a stored procedures?
Currently i can retrieve data from a query and use bcp
to write it to a file but i need to add some others text
to the same file.
Thanks for any reply!
View 1 Replies
View Related
Mar 1, 2000
Can anyone tell me whether i can create/open a file inside a stored procedures??
I need to retrieve data from my database and then send these data to other users by
using xp_sendmail. I plan to arrange the data in a text file format.
Thanks for any reply.
View 1 Replies
View Related
Mar 2, 2005
hi,
I am new to Sql Server and I want export the stored procedures of my SQL Server 2000 database
to a flat file, so I can import it later to another SQL Server 2000 database (it is not anywhere in the network).
Can anyone explain me the easiest way to do that ?
Thanks !
View 2 Replies
View Related
May 31, 2006
I have a need to export all of the stored procedures in a database to files on the server dirve. I know that this can be done through the management interface but I need a way to do it programatically. I need to have a script or stored proc that dumps all of the procedures to a defined location on disk. Does anyone know how this can be done?
Thanks!
View 3 Replies
View Related
Feb 19, 2008
Hi
I wrote a clr storred procedure that use fileopen, fileput an fileclose to write a report from a sql 2005 database but I don't know how to give it permissions to do its job.
The Code is
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Security.Permissions
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure> Public Shared Sub PriceSum( ByVal intFecha as sqlInt32 )
dim strFecha as string
strFecha = cstr(intFecha)
Dim f As New FileIOPermission(PermissionState.none)
f.AllLocalFiles = FileIOPermissionAccess.write
f.Demand()
FileOpen(1, "G:AuVeJAPParchivo.txt", OpenMode.Random, OpenAccess.ReadWrite, OpenShare.LockReadWrite, 10)
Using connection As New SqlConnection("context connection=true")
Connection.Open()
dim strConsulta as string
strConsulta = "declare cur_unidades cursor for select id_tda_vdp from tda_venta_departamental_vdp where date_operacion_vdp = " & strFecha & " group by id_tda_vdp order by id_tda_vdp"
Dim command As New SqlCommand(strConsulta, connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
using reader
Dim intKon as integer
intKon = 0
While reader.Read()
fileput(1, reader.GetSqlstring(0) & chr(0), intKon)
intKon = intKon + 1
End While
End Using
End Using
fileClose(1)
End Sub
End Class
As you could see, the report needs a chr(0) at the end of each line, thats why I spent a lot of time learning clr/sql, but when I finally success in crate the assembly an procedure in sql 2005 it gives me this frustrating error:
Msg 6522, Level 16, State 1, Procedure prueba1, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'prueba1':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at StoredProcedures.PriceSum(SqlInt32 intFecha)
I use
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I really appreciate your help, it could be a different way to do this or a simple step by step "how to grant permission"
thank you very much
Adolfo Ponce
View 5 Replies
View Related
Jan 20, 2006
Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
View 1 Replies
View Related
Sep 18, 2006
I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.
I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:
Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.
Any ideas.
Thanks,
View 2 Replies
View Related
Jun 27, 2001
Is there a way to run/call a batch file from a stored procedure?
Or, is there a way to run/call a batch file from a trigger?
View 2 Replies
View Related
