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?
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.
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
Hey forum,
the .NET form controls make it extremely easy to manage database data, control + SQLdatasource et voilà , its there.
i have a web form, and a SQLdatasource with an insert query with parameters (works perfectly, only the Date picker Lite textbox ID as parameter wont register, but i have a thread runnin on their forums for that). when i open the Query builder and execute the query it works! woot! but now, how can i make it execute on a button press? i tried with several commands, like SQLdatasource1.execute, and several other commands i searched (can post later, not behind my test environment with my tools/webpage at the moment). so what is the command to let the SQLdatasource execute it's query? it's all i need to know to make several forms, please ghelp me! :)
gr. michael
Periodically an INSERT query takes anywhere from 30 to 90 sec's to execute. But once it completes, execution times then return to 0 or 1 sec. This has begun to occur more and more frequently, typically every thirty minutes or so now.
The only way I have found to clear the problem is to execute the INSERT from SQL Studio and just wait for it to complete. Then everything returns to normal, until it hangs again. The application that commands the INSERT can not be made to just wait for 90 seconds, so I need to know how to eliminate the intermittent delay.
What could be causing this problem, and how can it be corrected?
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);
MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1
UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence
RETURN @sequence_id
END
hi, I have a trigger on a table for insert, once there is new data into that table I want to run a nother store procedure by passing all input from inserted to the store procedure as input parameter. can I do that.
Thanks
Ali
I received this stored procedure that I modified to run on my system. Specifically, I only changed the database and filter text and left the rest alone. When I execute the stored procedure, I get the error:
Msg 515, Level 16, State 2, Procedure ObjectNotesInsert, Line 18
Cannot insert the value NULL into column 'RefRowPointer', table 'pSCI_App.dbo.ObjectNotes'; column does not allow nulls. INSERT fails. The statement has been terminated.
Here is the actual stored procedure I am running. I should add that I can execute each step and get results and if I hard code the resulting values into the procedure, the execution works.Â
USE [pSCI_App]
GO
/****** Object: StoredProcedure [dbo].[_JAMTestSp] Script Date: 09/21/2015 11:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedHi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View RelatedI 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
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
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
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
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,
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 RelatedI'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 RelatedHi 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
[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
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
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)
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?
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
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
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!
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.
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!
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.
Hi all,
I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.
The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"
I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).
The code I use to capture errors and trace information follows:
UPDATE PortfolioPerformance
SET PrevDate = @PrevDate,
DailyPerChg = GPP.DailyPerChg,
DailySumPriceChg = GPP.DailySumPriceChg,
SumCurrPrice = GPP.SumCurrPrice,
SumPrevPrice = GPP.SumPrevPrice,
StockCount = GPP.StockCnt,
AvgHighPriceRatio = GPP.AvgHighPriceRatio,
AvgLowPriceRatio = GPP.AvgLowPriceRatio,
Volume = GPP.Volume
FROMPortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP
WHERE PP.PortfolioID = GPP.PortfolioID AND
(PP.CreateDate = GPP.CreateDate AND
PP.CreateDate = @CreateDate) AND
PP.PrevDate IS NULL
SELECT @RowCount = LTRIM(STR(@@ROWCOUNT)) /* capture rowcount so @m_error select doesn't clobber it*/
SELECT @m_error = @@Error IF @m_error <> 0 GOTO ErrorHandler
SET @TraceMsg = 'Completed Daily Portfolio Performance calculations (updated ' + @RowCount + ' rows)'
EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Set_PortfolioPerformance', @TraceMsg
NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.
So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?
Thanks!
Paul
Hello group, I have a question regarding the OLE DB Command Stage. Currently, I am reviewing a Data Flow that runs in production. This Data Flow Inserts to the various dimension tables in our warehouse. For a particular dimension table, the flow is like this:
Read Source records for Product combinations
LookUp Product combinations against the current dimProduct table (cached in memory)
Rows not found are then subjected to another LookUp on the dimProduct table (not cached). This is to find any rows inserted during the current run
Rows not found are then Inserted to dimProduct using a Stored Procedure invoked by an OLE DB Command
Successful Inserts then continue on, Rejected Inserts should be captured to a Flat File on our server for review.
Apparently, this last step has never been successful at capturing Rejects. Obviously, we would want to review these records to find the reason for failure. We get an empty file.
Currently, in the Stored Procedure we are using logic like this:
IF @PRODUCTCOUNT <> 0
BEGIN
RAISERROR ('DUPLICATE PRODUCT!', 10, 1)
RETURN
END
Questions:
Is the RAISERROR command going to give us Output?
Can we implement the OUTPUT command in our Proc invocation?
I have not found any documentation that says the OLE DB Command Stage supports Error logging (Although columns are available to be added in the Input/Output columns tab??)
Should we be using another Stage to accomplish this?
Any thoughts are welcome, thanks for your time!
rg
IF @PRODUCTCOUNT <> 0
BEGIN
RAISERROR ('DUPLICATE PRODUCT!', 10, 1)
RETURN
END
Hi,
I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.
exec(select count(*) from @dynamictable where condition = @dynamiccond)
But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?
Thanks
I'm exploring the capabilities of the Execute SQL Task and I'm wondering how I can capture a message from a SQL command.
For example, the following command
dbcc checkdb(AdventureWorks)
returns a text message containing information about the database AdventureWorks. It is not a tabular result set so I can't use Single Row or Full Result Set options for the ResultSet property. So the only thing left is the XML option. But I have no idea what to do with it.
What I would like to do, ideally, is to write the result of the above command to a text file to be evaluated at a later step for certain information about the database.
Thanks for reading.