Capturing Component Throughput
Dec 19, 2007
[Microsoft follow-up]
I've just been reading this thread by a guy asking about capturing the throughput of a dataflow. I suggested that there is no real notion of capturing throughput of a dataflow but I believe there IS a notion of capturing the throughput of a component or an execution tree.
I believe all the information that one would need to capture the throughput of a component (apart from the name of the component that is) is available in the OnPipelineRowsSent event. If there were a OnPipelineRowsSent eventhandler and the OnPipelineRowsSent event contained the name of the component then I think we would be able to capture the throughput of a component. So, some questions:
Why is there no eventhandler for the OnPipelineRowsSent event?
Can the name of the component be added to the information in the OnPipelineRowsSent event?
Following on from this... I once had a conversation here with Kirk Haselden about capturing pipeline throughput. He thought it was a good idea and suggested I raised a DCR for it which I did but that was in the old pre-Connect days and it seems as though that DCR (like SO many other things) didn't make it across to Connect. So, some more questions:
Can you find any Connect DCRs relating to capturing throughput? I've found this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=152162 that I raised 18 months ago but which hasn't even had a single comment from anyone at Microsoft.
Do you think that capturing throughput would be useful? I can foresee huge advantages by capturing this in the debugger. (Note that Informatica does this and has done for years. It has a very nice GUI that shows the throughput of each destination in the mapplet.)
I'd welcome any thoughts around this. Its a big ask and it fits in very nicely with my constant, nay INCESSENT, requests for debugging enhancements so maybe this is one for Darvey to have a read of???
Thanks
Jamie
View 16 Replies
ADVERTISEMENT
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Nov 16, 2006
Hi Team,
I have a SQL 2000 instance with 46 Databases (all databases put together will be 15 GB in size). I am running a SQL Backup using a third party software.
My full backup of the SQL instance which backs up 15 GB of data finishes within 30-45 minutes. But, my differential backup of the same instance which backs up only 150 -250 MB of data takes 12 hours to backup.
I found a knowledge article from MS Support site which says, differntial backup would take more time than full in few scenarios.
http://support.microsoft.com/default.aspx?scid=kb;en-us;196658
But the above document is for SQL 7.0. Will it be the same for SQL 2000 and 2005 too? If yes, can you please tell if I can increase the speed of differential backup in my environment. Should I modify any SQL Parameters?
Please let me know your thoughts on this..
Thanks
Santhosh
View 4 Replies
View Related
Feb 3, 2005
I am used to writing applications that hit the database "every so often" and am happy with opening and closing the connection to SQL Server for each one.
I am now writing an application that monitors a table where rows are written to it by a 3rd party application at possibly several rows per second. My job is to "pickup" those rows, analyse the data and move them to different tables. This will be done with a timer which is currently set to tick every second.
My question is: At what stage should I start to think about keeping open a permanent connection to the database ?
1 row per second ?
100 rows per second ?
Any suggestions appreciated.
Steve.
View 2 Replies
View Related
Apr 9, 2007
OK, so assume I am recycling dialogs in my client code, and assume I am doing something similar to get a dialog handle in my TSQL. What should the activated stored procedure that is processing my queue look like if I am expecting thousands of messages per second? Assume also that there is a small bit of logic need to process each individual message? I am building for a high-throughput scenario and would like to get as much as possible out of each second-tier service broker server as possible before the aggregated data is moved up the chain to a master. The first tier is Express on a web server and exists primarily only as a forwarding mechanism.
View 1 Replies
View Related
Aug 31, 2007
Hello!! guys..
I am using sql server 2005 enterprise edition in the clustered environment with netapp storage (one server) I monitor the server performance, basically using windows performance monitor counters such as avg.disk queue length,avg. disk reads/sec, avg disk writes/sec, processor time, available memory Mbytes, cache hit ratio etc. and using some sql server dmvs .
At this point all looks good.
My problem i dont know how much load sql server can handle more .How much it being utilized now? whehet it has reached its limits...how do i know it is just about to reach its linits??
how much throughput sql server is handling at present, how much more it can handle if incoming traffic increases.
Basically i want to find performance baselines , how much more it can handle so that i can plan for the future ..
How do i measure all this?
what are different methods available if i want to handle increased incoming traffic? (e.g. adding multiple servers etc.)
It will be really great if someone can share his experience on this..
Is there any article/white paper on this ..
Any suggestion/help appreciated
Thanks
View 1 Replies
View Related
Apr 28, 2006
I'm new to integration services.
I want to create a centralized reporting system for our customers. Some customers have up to 1,000 sites and some are expected to grow past 5,000 sites. The sites are running POS applications and I want to extract the POS sales data from these sites. Is it practical to expect that SSIS can handle the extraction of data from this many sites and load the data into a central
SQL database? The POS sales data at the sites is stored in SqlExpress databases but the data is also available in XML format.
If it's practical for Integration Services to do this, what frequency is it possible to pull this data?
I realize that the amout of data is relative but just wondering if anyone is attempting to do this with integration services.
If not with integration services, then what method(s) are available and used to extract data from this many remote sites?
View 3 Replies
View Related
Oct 20, 2007
Over the past week and a half we started experiencing a sporadic slowdown in our production x64 SQL 2005 Ent. Edition server. Users started complaining of slowness then they started getting timeouts. In looking at sp_who2 and perfmon we saw the following during the slow/frozen periods:
* Dramatic increase in Perfmon Active Transactions
* CPU higher than norm, but not dramatically so
* sp_who2 shows a number of spids in SUSPENDED state (and not running waits)
* no blocking indicated from sp_who2
* active connections slowly increasing
* no disk queuing (or at most some spikes to 1)
After a couple of minutes of this we would then see the following:
* no more spids in SUSPENDED state
* Logins per second spikes dramatically
* Active transactions spikes down to "normal levels"
* CPU goes high then levels out at moderately higher than normal
* active connections slowly decreases back towards normal levels
* large spike in lock wait time
We turned on the Async Auto Update Statistics option (after testing in our staging environment) on the primary database about a week before we saw this problem. By turning it off we can visually see the problem go away by watching the above metrics. So my question is, What metrics can I use to see the "blocking" or resouce locking that is causing these problems?
Anyone?
Thx
Ron
View 3 Replies
View Related
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Aug 13, 2007
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
View 4 Replies
View Related
Mar 30, 2006
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
View 7 Replies
View Related
Sep 26, 2005
I need to first capture the max version associated with a file name here is the meat
SELECT
fileName,
f.folderName,
masterID,
iterationId,
iterationNumber version
FROM
fileIteration fi
JOIN iteration i on (i.iterationID = fi.fileIterationID)
JOIN vw_folderFileLink fl on (fl.fileMasterID = i.masterID)
JOIN folder f on (f.folderID = fl.folderID)
WHERE
fileName LIKE '429020652.idw'
--GROUP BY masterID, fi.FileName, f.FolderName, fileName, i.iterationID
ORDER BY masterID, version DESC
--*******end code ********--
that returns these results:
429020652.idwSubPumps71575217
429020652.idwSubPumps71575116
429020652.idwSubPumps71574975
429020652.idwSubPumps71574654
429020652.idwSubPumps71573983
429020652.idwSubPumps71573142
429020652.idwSubPumps71573131
First I need help just getting that top row but then I need to get all expect the top row selected.. I've tryed using MAX in several ways but I'm not getting what i want here :/ any help is appreciated
View 5 Replies
View Related
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
Aug 12, 2004
Hi all,
I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.
Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.
It seems easy to output the record, but how do I store it as a variable !!
This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable....
Function higher_manager_ein() As System.Data.IDataReader
Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
View 2 Replies
View Related
May 22, 2000
Is there any way(other than Profiler) to capture a full SQL Query statment. I've used dbcc inputbuffer and the current activity screen, both return 255 characters. I'd like to capture the whole query. Thanks
Pete Karhatsu
View 3 Replies
View Related
Sep 20, 2004
Hi y'all....long time, I know...
I have what OUGHT to be a simple question...or so I think (which really hurts, mind you...)
I am writing a stored proc that will reside on several different databases and be used to write a row to a "wait table" that is used to control processing in the various databases...
Essentially, the stored proc already exists, and writes what is essentially a note (or process semaphore) that says "Hey, Process XYZ is waiting on the completion of process 123"
Problem is...process XYZ has the same name on a number of different servers, so I have to come up with a way to differentiate the process name that's waiting on a job on a single server (in other words, 5 or more XYZ's can be waiting on a single job on a single server in the network, and the wait table resides on that single server).
So...my thought (again, think pain) is that I will put a process name of "SERVER.XYZ" into the wait table.
The SP I will use to write the "waiting on" semaphore is a common one, so - long story short(er) I need a way to capture the name of the current server (like db_name(), only server_name() - or something like it).
Any suggestions? Thanks in advance...
Paul
View 5 Replies
View Related
Jun 3, 2008
Hi
Please let me know which DMV is best to cpature total system CPU utilization. There are plenty of views so i am a lil bit confused
thanks in advance,
View 3 Replies
View Related
Jan 2, 2008
I have a bit of code that executes an INSERT statement to add a record to an existing table. This table of course has an automatically incrementing ID field, and I'd like to somehow have my INSERT statement return the value of that ID field so that I can automatically show the user the record they've added.
Is there a clean way to do this?
View 4 Replies
View Related
Jul 20, 2005
This doesn't work because the first INSERT is creating multiplerecords for multiple projects. @@IDENTITY, then, contains the Identitycolumn value for the last tblWeekReportedLine record inserted.Consequently, all the hours records are then associated withthat last value.The source work table, #EstimateLines, is a pivoted representationwith a Begin/End date and some Hours for each of six periods - a lineper project that gets pushed up to the DB by some VB code.Definition below the sample coding.The "@WeekReportedID" value was successfully captured whenprevious coding inserted six records into that table: one foreach date range (i.e. column in the UI screen)Sounds like I'm approaching this wrong.Suggestions on the right way to go about it?---------------------INSERT INTO tblWeekReportedLine(WeekReportedID,RelativeLineNumber,ProjectID)SELECT@WeekReportedID1,#EstimateLines.RelativeLineNumber,#EstimateLines.ProjectIDFROM#EstimateLines;SET@CurWeekReportedLineID = @@IDENTITY;INSERT INTO tblHour(WeekReportedID,WeekReportedLineID,HoursDate,Hours,HoursTypeID,HoursType,TaxCodeID,TaxCode)SELECT@WeekReportedID1,@CurWeekReportedLineID,@BeginDate1,Estimate1,@DummyHoursTypeID,@DummyHoursType,@DummyTaxCodeID,@DummyTaxCodeFROM#EstimateLines;------------------------The #Temp table create via VB:------------------------1030 .CommandText = "CREATE TABLE #EstimateLines " & _" ( " & _" PersonID int, " & _" ProjectID int, " & _" RelativeLineNumber int, " & _" Available1 decimal(5,2) Default 0, Estimate1decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _" Available2 decimal(5,2) Default 0, Estimate2decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _" Available3 decimal(5,2) Default 0, Estimate3decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _" Available4 decimal(5,2) Default 0, Estimate4decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _" Available5 decimal(5,2) Default 0, Estimate5decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _" Available6 decimal(5,2) Default 0, Estimate6decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _" );"--------------------------PeteCresswell
View 2 Replies
View Related
Jul 20, 2005
I'm trying to add functionality to a VB 6 application allowingcustomer service to add a customer number to a new customer.Customers are added to the database by sales personnel, and aprospective customer may have multiple rows due to projected ordersfor multiple products. Customer numbers are assigned when a newcustomer makes their first order.I'm using a DataGrid connected to an ADO Data Control. The datacontrol is connected to a view in SQL Server using the 'distinct'directive (I know it's not updatable) to show only one line per newcustomer. What I wish to do is capture the update event (probablythrough the FieldChangeComplete routine of the data control), manuallyassign the newly entered customer number to all appropriate rows inthe database, and cancel the update event with no notifications.I'm having two problems:1. I can't capture the newly entered customer number. The Textproperty of the DataGrid returns the old value of the cell instead ofthe newly entered value. How do I get the edited value?2. Even though I set adStatus = adStatusCancel in theFieldChangeComplete routine, I get a Microsoft DataGrid Control dialogstating 'Operation was Canceled'. How do I avoid this notification?
View 1 Replies
View Related
Nov 13, 2006
Hi All
When running an SSIS Package from BIDS, we get to see the "Progress" tab which explains us the progress of the SSIS Package. During production, is there any way to capture this log. I am interested in using this as a log file for each run of my SSIS package.
Thanks,
S Suresh
View 9 Replies
View Related
Nov 30, 2007
My question is this.....
I inherited an application that creates reports and each report pulls data from a SQL database. The reports are for each School district in the state and pull the number of students that have Asthma conditions, simple enough. However what happens is this... Each initial report is saved with a code of 2 for initial. As long as there aren't and revisions I am fine. If a school makes a revision (Code of 4) I now have two records in the database and I need the one from the revision (Code 4). Not every record will have a revision, and sometimes there may be multiple revisions. How can I code my stored procedure to go through the database and check to see if there is a code of 4 or multiple codes of 4 and if so grab that data, if not grab the initial data? Below is the code currently being used. Frank
CREATE PROCEDURE dbo.usp_Rpt_Asthma
(@WhereClause VARCHAR(400))
AS
SET NOCOUNT ON
DECLARE @WhereClause_STR VARCHAR(400)
SELECT @WhereClause_STR = CONVERT(VARCHAR(400),@WhereClause)
Exec ('Select D.Code, C.Code, InstCtgyFK, C.[Description] as County, D.DistrictName, A.InstName,
Sum(K4+K+G1+G2+G3+G4+G5+G6+G7+G8+G9+G10+G11+G12+UnGrSpEd+Other) as TotalADM, Asthma, Asthmatics
FROM dbo.tblAnnualReports AR
Inner Join dbo.tblAddresses A on A.InstitutionFK = AR.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblInstitution] I on A.InstitutionFK = InstitutionPK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblActiveInstCtgy] AIC on A.InstitutionFK = AIC.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCounties (PA Standard)] C on C.Code = I.DOHCountyCode
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCommunityHealthDistricts] D on D.Code = C.CommunityHealthDistrictCode
Inner Join dbo.tblAverageDailyMemberships ADM on AR.[ID] = ADM.AnnualReportID
Inner Join dbo.tblChronicConditionsInjuries CC on AR.[ID] = CC.AnnualReportID
Inner Join dbo.tblMedicationAdministration MA on AR.[ID] = MA.AnnualReportID
Where (A.StartDate <= AR.DOHDateProcessed and (A.EndDate >= AR.DOHDateProcessed or A.EndDate is Null or A.EndDate = ''1/1/1900''))
and ReportTypeCode = 2
and IndOrdStandOrd = ''I''
'
+ @WhereClause_STR +
'
Group By D.Code, C.Code, InstCtgyFK, C.[Description], D.DistrictName, A.InstName,
Asthma, Asthmatics
')
GO
View 15 Replies
View Related
Aug 31, 2007
I'm creating a sproc to to count the dates and times users log on to a page. I want the date and time broken out into two columns.
When I attempt create the sproc I get the following error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.Create PROCEDURE [dbo].[LoginCounter]
@Username Nvarchar(50),
@IPAddress Nvarchar(50),
@BuilderID, INT
As
INSERT INTO LoginCount ([UserName], [IPAddress], [Date], [Time], [BuilderID])
VALUES (@Username, @IPAddress,
(SELECT CONVERT(VarChar(2), MONTH(GETDATE()))+ '/'+ CONVERT(VarChar(2), DAY(GETDATE()))+ '/'+ CONVERT(VarChar(4), YEAR(GETDATE()))),
(SELECT Convert (varchar (2), DATEPART(hour, GETDATE())+ 3) +':'+ CONVERT(VarChar(2), DATEPART(minute, GETDATE()))),
@BuilderID)
View 3 Replies
View Related
Sep 15, 2003
Hello.
I would like to analyze how a particular 3rd party business application interacts with SQL server.
Specifically, I want to capture the "exact" SQL commands (transact-sql statements) that this application issues whenever it completes an operation for the user of this application.
In other words, suppose the application issues the SQL command "INSERT INTO table (a,b,c) values (1,2,3)" to add a new entry to the database. Then, my understanding is that if I instead issue the exact same command with a tool like "SQL query analyzer", the database will be updated in the exact same manner. Hence, I have effectively accomplished the same job that the 3rd party application does without even using it (by idependantly issuing the same command to the database it uses).
Is this possible? Can I obtain all the information I need just by running SQL Profiler?
View 3 Replies
View Related
May 26, 2004
Hi,
Can someone help me out with capturing the bulk insert error.I have a job which calls a procedure in which I used the bulk insert command .If the bulk insert is failing due to some reason as wrong delimitor,wrong path etc then the job fails.I need to track that error and see that the job doesnt stop and goes onto the next cursor record.
Thanks,
Nodbek
View 8 Replies
View Related
Apr 1, 2002
I'd like to capture the avg. # of user logins and # db hits per a 5 interval for a weeks time. I'm guessing there are sys tables containing this info. and by using temporary tables and/or creating/modifying SPROCS this info. can be retrieved. If I'm on the right track, a little direction would be very appreciated. If I'm not on track, please assist this rookie dba.
Thank you,
Eoin
View 2 Replies
View Related
Jun 14, 1999
I currently have a need to dynamically build an sql statement that always returns a single value when executed. The sql statement is always the same except the database name reference in the statment.
What I need is to be able to capture that value for later use in the procedure. Since this is a stored procedure, I can't use the "USE" statment to switch databases and I haven't been able to figure this out using the Execute statement. I can execute the string, but I can't capture the value.
I'm simply trying to execute the same set of sql statements in a stored procedure without hardcoding database names or build an identical stored procedure in all our databases.
Any help is appreciated.
View 2 Replies
View Related
Nov 25, 1998
Can I declare variable and assign a user defined table name in Sql server 6.5. if so how can I do that ,
Thanks
Ali
View 1 Replies
View Related
Jul 23, 2004
Hey y'all...
Anyone know how to capture SQL Warnings so I can write them to an error log? I can't seem to find any info on it in Books Online...
I can capture the errors just fine by using @@ERROR after a select, but what about warnings such as "Warning: Null value is eliminated by an aggregate or other SET operation."
Thanks!
View 4 Replies
View Related
Oct 27, 2005
Hi,
Create Table tb_mismatch
(x int)
Create Procedure proc_mismatch
as
begin
insert into tb_mismatch values('s')
if @@error<>0
begin
print ' entered error loop'
end
print 'successfully exited'
end
exec proc_mismatch --executing the proc
Now, when i try to capture the above error its not getting trapped..its directly going to the final end statement.
I have even tried calling subprocedures so that it comes out of the inner procedure and by some means i can move forward in the outer proc,but even that failed.
The proc. is able to capture all the other errors like primary key violation,binary data truncated etc but not the datatype mismatch error (mainly int with varchar...)
any ideas are highly appreciated.
Thanks & regards,
Pavan.
View 8 Replies
View Related
Jan 23, 2004
How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?
For example,
CREATE PROCEDURE dbo.sp_test AS
--- returns all words not in Mastery Level 0
EXEC sp_anothertest
--- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults
SELECT * tblFinalResults
GO
Thanks!
View 1 Replies
View Related
Feb 13, 2004
Hi All,
I have a requirement where I need to find the list of noise words from a set of words in a SP.
PS: Too common words are said to be noise words and SQL maintains list of noise words on its own.
Say I have a sentence like "I am a software engineer”. Here I need to get the list of noise words (I, am, a).I have written a logic where I will split the sentence into words and process word by word. I will first take one word and I have a select statement which will throw SQL noise word error exception if it is noise word.
Logic is
1. Take a word from the sentence.
2. Write a select statement like "select * from job where contains (jobdescription,' extracted word')"
3. If the word is noise word then SQL will throw a noise word exception.
4. I try to capture this error and based on that I have some logic.
5. If noise error thrown (I am using @@error)
do this;
do this;
else
do this;
do this;
6. Now my problem is, when the SQL throws noise exception, the execution of the SP stops immediately and the rest of the logic is not executed.
7. But some how I need to capture the exception and continue with the program flow.
I have different logic where I can achieve my requirements. (Instead of capturing SQL exception, maintain the noise words in a table and check with the table). but my question is there any way where I can capture the SQL exception and continue the program flow?
Please reply to my mail id.
TIA,
Varada.
View 1 Replies
View Related
