Is it possible to capture the contents of THROW within a CATCH into a variable so that it can be written to a logging table?
I can capture ERROR_MESSAGE() to a variable but unfortunately it only writes the last error rather than the previous one that is informational.
I realize that THROW can be multiple lines so it may not be possible to do through TSQL but thought I should ask.
I am trying to use change data capture to load the data into the secondtable from table 1 which is coming from UI.
What will be the minimum latency??? Can we use incase of latency less than 5 seconds.
I have a matrix table. These status can be changed by the user and I want to capture each change in database with out updating the earlier status
Pending
Activated
In PROGRESS
Submitted
Completed
Pending can be changed to submitted or completed. For one form there can be different status at different time. And each status must be saved in the database table. How can I design a table...
I would like to have a dynamic trigger on INSERT, UPDATE and DELETE. (each their own trigger).
What i want, is the trigger to log what has happened with the record. For the insert trigger, i would like to select all columns and put them in 1 column to my logging table. For the update trigger, i need the same, however for both of the old and new items.
For example:
CREATE TABLE [dbo].[TRIGGER_TEST](
[COLUMNA] [int] IDENTITY(1,1) NOT NULL,
[COLUMNB] [nvarchar](50) NOT NULL,
[COLUMNC] [nvarchar](20) NULL,
[COLUMND] [date] NULL,
[COLUME] [decimal](2, 2) NULL,
[Code] ....
When I insert an item in TRIGGER_TEST, i would like to see what is inserted in the TRIGGER_LOGGING table.
INSERT INTO [dbo].[TRIGGER_TEST]
([COLUMNB]
,[COLUMNC]
,[COLUMND]
,[COLUME])
VALUES
('test'
,'hello'
,getdate()
,0.1)
GO
I would like to see in my TRIGGER_LOGGING table in NEW:
COLUMNB='test'|COLUMNC='hello'|COLUMND='2015-10-13 16:04'|COLUME=0,10
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
I would like to fire a pre execution event, grab the name of the stored procedure (source of the sql task), insert a record with the name and datetime, and then fire a post event that would update the record with a modified dated.
What is the best way to capture the source value name in the execute sql task.
I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
I'm trying to get the application logging to work, but nothing is happening. I'm using SQLServer2014.
I've followed the instructions on [URL] .... , even copying and pasting the snippet into web.config, but nothing happens, no log is created in the default directory.
Is there something version specific? Does 2014 behave differently?
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
This is my code connect to SQL Server
SqlConnection con = new SqlConnection("Data Source=OIT;Initial Catalog=big_db;User ID=sa; Password=");
SqlDataAdapter cmd = new SqlDataAdapter("select * from myDB", con);
SqlCommand sqlCmd = new SqlCommand();
DataTable dt = new DataTable();
cmd.Fill(dt); // It throw exception
When myDB table have a lot of data, it throw exception like this : "It reached the time-out. Did the time-out period pass before completing the operation or the server doesn't respond. ". I config TCP/IP for SQL Server is Enable, but it throws SqlException too.
How can I do? Help me please!!!
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
Best regards,
Stefoon
I am using SQL Server 2005 Express and one of the fields in my table has the UNIQUE constraint. When I try inserting a duplicate nothing gets inserted in the table, but no error is thrown. Where can I do a check for this? I would like to popup a message to the user indicating that the insert failed to the the unique constraint. I have configured the SQLDataSource with Insert/Update/Delete so the actual insert is done behind the scenes. Thanks in advanceEric
View 2 Replies View RelatedI'm looking for help on how to throw a page so that when printing duplex (both sides) a group will start on an odd page number eg 1, 3, 7, 9 etc. My large report needs to be split up into departments. I don't want a department starting on the reverse side of a sheet of paper.
Your help would be appreciated.
I have what looks to be a very simple Stored Procedure as follows:
CREATE PROCEDURE SOE_OFMUpdateStatus_v1
@sOrderNumber
numeric(7,0),
@sOrderType
char,
@sSupCode
char,
@sOrderStatus
varchar(15),
[code]...
When I execute the SQL Script in SQL Server Management Studio, everything is just fine. I have included this SP in a VS 2012 Database Project, and when I build, I get the error 'Incorrect Syntax near Throw'. The Database project is set to SQL 2012. I did try putting a ';' in front of the THROW as has been suggested on other threads. is this a case where we will have to abandon use of VS 2012 Database integration?
I'm trying to run a DTS package, and cant find an easy direct way to run it. It seems like the easiest solution would be to throw a custom error- then the server notices the error and runs a custom job, and the custom job runs the DTS. This is a roundabout way, but seems like it would be the simplest solution.Anyways, how do I throw the error in my code? Do I just write a throw 3829 statement? Also maybe this is a very bad way to do this- any suggestions?
View 2 Replies View RelatedI'm using Decimal.MinValue in SqlParameter( SqlDbType.Decimal ) to execute a stored procedure.
But, I receive this stack trace exception:
System.OverflowException: Conversion overflows. at System.Data.SqlTypes.SqlDecimal.ToDecimal() at System.Data.SqlTypes.SqlDecimal.get_Value() at System.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I have changed Decimal.MinValue to Decimal.Zero to resolve my trouble. But, Why Decimal.MinValue throw OverflowException? Isn't Decimal.MinValue valid SqlDbType.Decimal type?
Hi everybody,
I have a good question. If anybody know help me.
Every body know that SQL Server has SQL SERVER LOG 1- Current and 6 - Archives
I'm shure every DBA look inside every day. And we can note inside a lot of many adds looks like this one:
2000-09-28 10:23:42.85 spid10 Starting up database 'pubs'.
So, we can see that 'spid10' had some activity at that day, but
HOW TO GET INFORMATION about WHO IS IT - Login name or Username at least.
Any idea will be appreciated
Dmitri (DBA)
I have a report runs without any problem (Url access) and export to other format also without any problem.
Only when I export the same report to Excel, the following error occurs in the reportserver log and its throws an error on screen.
I have found out it is down to a table grouping on a data field.
The report conatins a list and the list has a details group, which grouping the data by week.
A table is sit inside the list to report the data.
Within the table, there is a group list which is grouped by a field from the dataset.
I have the actual RDL and RDL.data file if anyone like the challenge.
Here is the error from the report server log:
---------------------------------------------------------------------------------------------------------------------------------------------
aspnet_wp!library!1!01/11/2008-14:54:49:: i INFO: Call to RenderNext('/ExportExcelProblemReport' )
aspnet_wp!reportrendering!1!01/11/2008-14:54:50:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An error occurred during rendering of the report., ;
Info:
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An error occurred during rendering of the report. --->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
error occurred during rendering of the report. --->
System.InvalidOperationException: Operation is not valid due to the current
state of the object.
at
Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32
index)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows
header, TableHeaderFooterRows footer, TableGroupCollection tableGroups,
TableRowsCollection detailRows, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList
indexPath, PageReportItems& pageRI)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem
parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout,
PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable&
rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates,
OutlineRenderStates[]& horizontalOutlineStates)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem
parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32
currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout
pageLayout, Int32& currentPageNumber, Stack& stack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack&
renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout&
lastPageLayout, Action action)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
--- End of inner exception stack trace ---
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
renderer, CreateReportChunk createChunkCallback, RenderingContext rc,
GetResource getResourceCallback)
--- End of inner exception stack trace ---
aspnet_wp!webserver!1!01/11/2008-14:54:50:: e ERROR: Reporting Services
error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An
error occurred during rendering of the report. --->
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An error occurred during rendering of the report. --->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
error occurred during rendering of the report. --->
System.InvalidOperationException: Operation is not valid due to the current
state of the object.
at
Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32
index)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows
header, TableHeaderFooterRows footer, TableGroupCollection tableGroups,
TableRowsCollection detailRows, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList
indexPath, PageReportItems& pageRI)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem
parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout,
PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable&
rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates,
OutlineRenderStates[]& horizontalOutlineStates)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem
parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32
currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout
pageLayout, Int32& currentPageNumber, Stack& stack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack&
renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout&
lastPageLayout, Action action)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
--- End of inner exception stack trace ---
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
renderer, CreateReportChunk createChunkCallback, RenderingContext rc,
GetResource getResourceCallback)
--- End of inner exception stack trace ---
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
renderer, CreateReportChunk createChunkCallback, RenderingContext rc,
GetResource getResourceCallback)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
at Microsoft.ReportingServices.Library.RenderSnapshotAction.Render()
at
Microsoft.ReportingServices.Library.RSService.RenderFromSessionNoCache(CatalogItemContext
reportContext, ClientRequest session, RenderingResult& result)
at
Microsoft.ReportingServices.Library.RSService.RenderFromSession(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderNext(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effecectiveParameters, String[]&
secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderNextCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
--- End of inner exception stack trace ---
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderNextCancelableStep.RenderNext(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobType type,
Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory
streamFactory)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation
operation)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType
itemType)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
---------------------------------------------------------------------------------------------------------------------------------------------
Thanks in advance !
Regards,
pysw
Maybe I am mistaken ( most likely ). But I am missing a dataflow task, which would do something similar like Throw Execption.
The project I am currently working on needs to validate a lot of different data. And sometimes incorrect data ( corrupted, incomplete or unexepected ) is coming from the source system. In case of this I need to trigger an error and discard the complete row or batch depending on the situation.
For example:
In our source system we have some flag fields. And certain combinations of flags are not logical (business rules) but the system allows data to be inputted in this unlogical ways. And because we are creating the system I am expecting some combinations flags which are allowed but not properly defined in the specification.
So when I use a conditional split, I want the default output to trigger an error instead of an output.
Hi,
I'm trying to use a stored procedure to encrypt data but it dosent work fine, this is how I proceeded and that worked well
Code Snippet
CREATE PROCEDURE [dbo].[UpdateUser]
@CardNumber nvarchar(max),
@UserID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SecretData varbinary(max)
OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY
DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')
SET @SecretData = encryptbykey( @KeyGuid, @CardNumber)
UPDATE User
SET
CardNumber=@SecretData
Where UserID=@UserID
CLOSE SYMMETRIC KEY MY_SYMMETRIC_KEY
END
but Now I use my Stored Procedure to encrypt the data, but I'm getting bad data when I decrypt.
Code Snippet
CREATE PROCEDURE [dbo].[UpdateUser]
@CardNumber nvarchar(max),
@UserID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SecretData varbinary(max)
exec EncryptData @CardNumber, @SecretData output
UPDATE User
SET
CardNumber=@SecretData
Where UserID=@UserID
END
Code Snippet
CREATE PROCEDURE [EncryptData]
@ClearData varchar(max),
@SecretData varbinary(max) output
WITH EXECUTE AS 'DBO'
AS
BEGIN
OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY
DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')
SET @SecretData = encryptbykey( @KeyGuid, @ClearData)
CLOSE SYMMETRIC KEY My_SYMMETRIC_KEY
END
Any Idea how to fix this issue
Thanks in advance.
Hi please Help Me Out.
I am using Data flow object in that I take OleDb as Source and Sql Server As destination. Without enabling Package configuration everything is working fine data is moving from Source to destination.
But When i apply Package Confiiguration It throw Error And My OleDb Source object Convert in to RED Color. I cant able to track This errror
Please Help me Out .
Thanks
Sandeep
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View 4 Replies View RelatedI am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
Is there any data type in SQL Server 2005 which captures only the TIME in default DATETIME type?
For example, if the datatime field has a value 2007-12-11 12:31:00.000, i need a datatype which can capture 12:31:00.000 alone. The data type should be in a fashion so that i can find differences in time also...
Any ideas??
When using Change Data Capture on SQL Server 2012 I have researched that you cannot truncate data in a table. Is this also true if one wanted to delete data from the table? Getting a little confused about what DDL statements can be ran against a table with CDC enabled. Does CDC have to be disabled before performing certain DDL statements against a table?
I would like to safeguard the truncation and dropping of certain tables within the dbo schema. Wondering if I could do this with one fail swoop with CDC enabled on those tables. The other option would be to use a DDL trigger to prevent certain DDL statements to be performed.
I have a dataset that is between 40-50K records that has to go through a process that is pre-defined. SSIS works just fine with the smaller sets even up to 20K but this job keeps blowing up saying something along the lines of cannot write to recordset destination. Does this make sense to anyone? The sever is a 2 processor with 2GB of ram. Physical memory usage spikes to about 1.6GB during the run but the processor never really gets above 30% usage. Does this product just not scale yet?
View 1 Replies View RelatedSQL Server 2008 R2. Visual Studio 2013.We have a third-party application that manages a SQL database. Within that application we can create queries (these are not SQL queries, but higher level queries made through a GUI). Although the user does not see the SQL code, the application nevertheless turns the query in SQL code and passes to SQL Server to retrieve the data requested. Is there a way using some SQL tool, some tool within the management studio, or some other method to capture the SQL code that is passed from the application to SQL Server?
View 7 Replies View RelatedI have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.
View 2 Replies View RelatedI setup data collection on a production server to capture growth rates.
When I run the dis usage report, it shows a daily growth rate of over 500 megs. This seems excessive to me.
As a troubleshooting step I then ran sp_space used and got these results:
database_namedatabase_sizeunallocated space
rgc_prod 273442.63 MB3648.48 MB
reserved data index_size unused
265345488 KB164385384 KB99826072 KB1134032 KB
What should my next steps be to try and determine why there is so much growth? And isn't the index size rather large?
Hi, I have a stored procedure running on our monitoring server to monitor the backup jobs on other sql servers running on our network. This sp loops thorugh the list of server names and connects remotely to other servers using linked servers. However, if one of the servers is down, the sp stops after raising 42000 error ("SQL Server does not exist") and fails to carry on processing the next server in the list.
Is there any way to capture this "SQL Server does not exist" error within the sp so that the sp can carry on with the processing?
I am using SQL Server 2000 SP4.
Thanks in advance for any replies.
Opal
Hi All,
I am now working on the design phase of my project, we are looking to implement Change Data Capture (CDC) but i need some help if you guys has implemented before using the SSIS 2005 componets. I am trying to use the Following:
Source---------Derived Column---------Lookup---------------Conditional Split (to split New records and Updated Records)-----------Destination. Respectively.
Lets make it clear, my source holds (Old records and newly added or Updated records), the Derived Column is to Derive new columns called Insert_Date and Update_Date. The Lookup i am Using is to look the Fact_Table(the Old Records) as Reference, and then based on this lookup i will split the records on timely based using the Conditional Split. My question is
1. Am i using the right components?
2. what consideration should i have to see to make it true (some Logics on the conditional split)?
3. Any script which helps in this strategy?
4. If you have a better idea please try to help me, i need you help badly.
Thank you,
SamiDC
Or can it record before and after column changes based on the LSN only?
An extract from a file based legacy accounting system is performed every night. The system does not have a primary key because transactions are managed through program code. (the more things change...). The extract is copied to text in Unix and FTP'd to Windows, where the file is loaded into SQL Server by kill & fill. Because of the expense of modifying the source system, there is enormous inertia/resistance to injecting a primary key at the source, so kill & fill it stays.
In reading about Change Data Capture, it seemed to me that column level insert update and delete are stored in tables that remember the before and after content of each column tracked. In my reading I have seen many references to the LSN to decide when and what to record as changed, but I have not seen any refereference to the necessity of a primary key for Change Data Capture to work. This is in contrast to replication, where the requirement for the existence of a primary key is made plain.
Is it possible to use Change Data Capture against a table without a primary key? How to use it to change the extract from kill and fill to incremental.