Data Type In Audit Record

Mar 30, 2007

Hi,

I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:

TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)

TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change

So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.

Any thoughts/recommendations/criticism would be greatly appreciated.

Thanks
Alex

View 5 Replies


ADVERTISEMENT

Any Type Of Audit Mechaninsm In SQL Server?

May 15, 2007

 Does anyone know if there is any type of audit trail mechanism used in SQl Server 2000 or 20005?
 In our production environment, some of our stored procedures, views and data in our tables has changed. It would be nice to have a footprint of what was changed by whom and one.
For our code we use source safe but for SQL Server I am not aware of any type of auditing mechanism.

View 1 Replies View Related

Index Creation Causes Error The Conversion Of A Char Data Type To A Datetime Data Type Resulted...

Jul 23, 2005

Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

System.Data.SqlClient.SqlException: The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Dec 14, 2005

After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.

System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.

Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.

At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.

anyone having this problem before?? hopefully you guys can help.

Thanks,

View 4 Replies View Related

Converting A Data Type Double To A Type Float Using A Data Adapter &&amp; SSCE 3.5

Feb 13, 2008

Hi,

I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:


this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);


This did not happen with VS2005/CE3.01.

I have tried changing all references to decimal (or money in CE3.5) without luck.

I'm beginning to think that string may be the way to go!!!!!!!

Can someone shed some light on my problem.

Thanks,

Later:

It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.

View 4 Replies View Related

COnverting Numeric Data Type (Oracle) To Date Data Type Using SSIS

Mar 7, 2007

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

View 3 Replies View Related

Database Audit Specification To Audit Select On Certain User And Table

Nov 1, 2014

I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query

"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"

It return a value at which time the query has done

after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on

View 1 Replies View Related

Audit Logon / Audit Logoff Problem With SQL 2K

Jan 18, 2006

I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???

View 6 Replies View Related

1 Record For Each Type

Mar 11, 2008

Ho do I get 1 record for each type of meter?

I have 251 distinct types and I wish to return 1 meternumber for each type of metertype?

So far I have the folloiwng query to return the 251 distinct metertypes but i also wish to return a meternumber for the 251 metertypes. Both columns are in this table.


SELECT DISTINCT MeterModel

FROM dbo.METERS

View 4 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type!!

May 13, 2008

hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
 
in my SQL database I have the date feild as datetime and the version as nvarchar(max)
this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient
Imports system.web.configuration

Partial Class Admin_emag_insert
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Record_DateTextBox.Text = DateTime.Now

End Sub

Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click
Me.VersionTextBox.Text = ""
End Sub

Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click
Dim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim ConnectionString As String
Dim record_date As Date
Dim version As String
Dim emagSQL As String

'save form values in variables
record_date = Record_DateTextBox.Text
version = VersionTextBox.Text

ConnectionString = WebConfigurationManager.ConnectionStrings("HRDBConnectionString").ConnectionString

'Create and open the connection
objConnection = New SqlConnection(ConnectionString)
objConnection.Open()
emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _
"values('" & record_date & "','" & version & "')"

'Create and execute the command
objDataCommand = New SqlCommand(emagSQL, objConnection)
objDataCommand.ExecuteNonQuery()
objConnection.Close()

AddMessage.Text = "A new emagazine was added successfully"

Me.VersionTextBox.Text = ""

End Sub
End Class
 

View 10 Replies View Related

Converting Numeric Data Type To Text Data Type

Jul 20, 2005

Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin

View 1 Replies View Related

Changing User Defined Data Type's Data Type

Sep 12, 2006

Hi,



I have a user defined data type which is called DEmployeeName,

it's length is varchar(20), how do i change it into varchar(30) without droping it?

I'm using SQL server 2005.

Thanks in advance..

View 1 Replies View Related

Convert Text Data Type To Smalldatetime Data Type

Oct 9, 2007

I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible?
I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.


SELECT rsbirthday CAST(rsbirthday AS smalldatetime)

FROM panelists


the error returned is "incorrect syntax near 'rsbirthday'.

I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.

Thanks for the help!

View 10 Replies View Related

Audit Data

Jan 18, 2005

Hi all,
I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:

AuditTable that have the following fields:
AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate

then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?

Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great.
Thanks,

View 6 Replies View Related

Unique Record Id Type

Jan 9, 2008

I am trying to make a where clause using the sql server unique record id that is set automatically when a record is inserted. It is the record id that is unique for one table.
In a where clause. What type should go where the _______ is below?
 Here is the program
<%@ page language="vb" %><%@ reference page="try7.aspx" %><% @import namespace="system.data"%><% @import namespace="system.data.sqlclient"%>
<html>   <head>      <title>      </title>      <script language="vb" runat="server">         sub page_load(sender as object,e as eventargs)            response.write(request.queryString("a"))            response.write(",")            response.write(request.queryString("b"))            get1.text=request.queryString("b")         end sub
         sub star(sender as object,e as eventargs)            dim connection1 as new sqlconnection("")
            const q1 as string="update tbltest set code=@code where dxid=@dxid"            dim command1 as sqlcommand=new sqlcommand(q1,connection1)            command1.parameters.add("@code",sqldbtype.varchar).value=get1.text            command1.parameters.add("@dxid",sqldbtype._________).value=request.queryString("a")
            command1.connection.open()            command1.executenonquery()            command1.connection.close()         end sub      </script>   </head>   <body>      <form runat="server">         <asp:textbox id="get1" columns="6" maxlength="6" runat="server" />         <asp:button id="buttonsave" text="click" onclick="star" runat="server" />      </form>   </body></html>

View 3 Replies View Related

Getting Error : : The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value

Jan 28, 2008

update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?

View 5 Replies View Related

Data Base Audit Options

Mar 12, 2008

Hi All,

Is there any option present in SSMS that ll audit all the changes(insert/update/delete) done to data in a database from SSMS( not through any application/front end).

I dont want to place any trace or triggers, is there any in-built option.

View 4 Replies View Related

Moving Data For Audit - Best Practice

Oct 17, 2005

Here is the situation:Tables:AccountAccountDocumentAccountTestThen we have the following "Activity" table refencing "Account" tableabove:Table: ActivityColumns: ActivityID, AccountNo,...when "Activity" with accountno is created, we'd like to able to takesnapshop or copy all Account related data records from tables: account,accountdocument, accounttestand store it somewhere.This way even if account, accountdocument and accounttest tables changelater, then "Activity" accountno integrity in maintained.Do we need to create or replicate similar tables to store data foraudit?or Which other ways can this be achieved?

View 3 Replies View Related

Sqlbulkcopy Error : The Given Value Of Type SqlDecimal From The Data Source Cannot Be Converted To Type Decimal Of The Specified

Apr 16, 2008

Hi,

The table in SQL has column Availability Decimal (8,8)

Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability
we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected
we need to store values as is


Error:
base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}

"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range.
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at MS.Internal.MS
COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"


Code in C#

SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default);
DataRow dr;
DataTable dt = new DataTable();
DataColumn dc;

try
{

dc = dt.Columns.Add("Availability", typeof(decimal));
€¦.

dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal
€¦€¦€¦.

}
bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]";
bulkCopy.WriteToServer(dt);



thx



View 8 Replies View Related

The Most Recent Record From A Datetime Type Column

Apr 5, 2007

I got some issues regarding selecting the most recent record from a datetime type column.
The probelm consists in following:
Table: News
Needed columns: Letter [varchar]
DateTimePublication [datetime]

Table News: Letter DateTimePublication
FR1_dd_mm_yy dd.mm.yy hh:mm:ss
FR1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
FR2_dd_mm_yy dd.mm.yy hh:mm:ss
FR2_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR3_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN2_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU3_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU4_dd_mm_yy dd.mm.yy hh-2h:mm:ss

The ideea is that there are letters with the same name [Letter] but different DateTimePublication ... and I need only the letters with the most recent DateTimePublication for every letter type like FR_dd_mm_yy/EN_dd_mm_yy/DU_dd_mm_yy.

Thank you for your time and consideration!

SenneK

View 4 Replies View Related

Bulk Insert Task Failing On Data Type Conversion For A Destination Column Of Type Bit

Jul 6, 2006

I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.

I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.

I hope someone can help me work through this.

Thanks in advance,

SK



SSIS package "Package3.dtsx" starting.

Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".

Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".

Task failed: Bulk Insert Task 1

Task failed: Bulk Insert Task

Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package3.dtsx" finished: Failure.

View 5 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Apr 19, 2008

Advance thanks ....... My table is  TimeSheet:-----------------------------------  CREATE TABLE [dbo].[TimeSheet](    [autoid] [int] IDENTITY(1,1) NOT NULL,    [UserId] [int] NOT NULL,    [starttime] [datetime] NOT NULL,    [endtime] [datetime] NOT NULL,    [summary] [nvarchar](50) NOT NULL,    [description] [nvarchar](50) NULL,    [dtOfEntry] [datetime] NOT NULL,    [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution  

View 5 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Aug 3, 2005

Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards

View 6 Replies View Related

The Conversion Of Char Data Type To Smalldatetime Data Type Resulted In An Out-of-range Smalldatetime Value

Mar 30, 2007

I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.



I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.

The code I'm using is:



Dim sqlcommand As New SqlCommand _

("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)



Try

conn.Open()

sqlcommand.ExecuteNonQuery()

conn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try



This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.



The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.

Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?













View 1 Replies View Related

Converting Audit Data To Create History

Apr 9, 2008

Hi

I have a table called my_history that has columns like this


column_name , old_value, new_value, key, date
bankaccount 30 50 1 01-Apr-2008
bankbalance 10 14 2 04-Apr-2008


and so on............

The history table is populated using triggers

The main table called my has a structure like


bankaccount bankbalance bankname name address key
50 50 xyz abc ford 1
30 14 abc xyz east 2


Now using this information can i reconstruct the records in my table before the update happened ?

I am finding it very difficult to do this is there a way to do this in t-sql ?

The problem is my_history table where the column_name keeps on varrying

regards
Hrishy

View 7 Replies View Related

Trouble With A Multi Record Type Flat File

Jun 19, 2007

I am currently working on project where I need to insert, delete and update data from a text file I receive into multiple tables. So the file has multiple recordset types(50 to be exact) and each record has a a code to perform an -Update, Insert, or Delete to the destination table. Also when I receive the records they are not sorted. I need to sort the sets for each destination table and then read the the sorted set sequentially and perform the correct action.



Currently I am importing the record via flatfilesource into one column. I am using a script component here that would consist I guess 50 outputs including the fields needed for each table. The outputs are sorted by sortkey field and when I add the record to the output I perform data type transforms needed for each field(most are strings but I need to convert some dates, numbers ect..).



***is there a better way of accomplishing this?***

(ps. I could use the conditional split and the derive column into the 50 different table but it was giving me errors that were almost forcing me to use a nvarchar type instead of a varchar type during some of the field transformations.)



At this point I would need to read through each of the outputs sequentially and perform the update, insert or delete into the needed table. Would I have to create 50 script components with an ADO.net recordset adapter to update the tables for each of the outputs? I am hoping you can help come up with a better way to accomplish all of this.



Also if I do need to update the tables with the script component could someone point me to an example of how to programmatically accomplish that. Thanks for any help of suggestions that you may provide as I am feeling kind of stuck.

View 4 Replies View Related

Connection Type Limitations When Using XML Data Type In SQL Task

Jul 23, 2007

I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:




SELECT
(



SELECT
MT.MessageId 'MessageId',
MT.MessageType 'MessageType',
FROM MessageTable MT
ORDER BY MT.messageid desc
FOR XML PATH('MessageStatus'), TYPE


)
FOR XML PATH('Report'), TYPE

For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:



<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>



Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?



Thanks

Walter

View 1 Replies View Related

Transact SQL :: DB Audit File That Gets Populated With Data As Activity On DB Goes On

Aug 25, 2015

writing a T_SQL query for the following scenario..I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015 Query for reading SQLDB Audit file

SELECT * FROM sys.fn_get_audit_file ('C:ackupAudit*',default,default)
order by event_time desc
GO
--DML for Audit table CREATE TABLE [dbo].[Audit_tbl_Aug2015]( [id] [bigint] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](7) NOT NULL, [sequence_number] [int] NULL, [action_id] [varchar](4) NULL, [succeeded] [bit] NOT NULL, [permission_bitmask] [bigint] NOT NULL, [is_column_permission] [bit] NOT NULL, [session_id] [smallint] NOT NULL, [server_principal_id] [int] NULL, [database_principal_id] [int]

[code]...

View 2 Replies View Related

Reporting Services :: SSRS Report Data Logging Process / Audit Trail

Jun 30, 2015

1. Does SSRS is J-Sox Compliant, an application must have audit trail feature. For Reporting printing, it should facilitate reports' data logging process. 

2. Information like WHO and WHEN printed the report and WHAT data was viewed?

View 5 Replies View Related

SqlDataSource.Select Error: Unable To Cast Object Of Type 'System.Data.DataView' To Type 'System.String'.

Oct 19, 2006

I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx    Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!

View 3 Replies View Related

Unable To Cast COM Object Of Type 'System.__ComObject' To Class Type 'System.Data.SqlClient.SqlConn

May 17, 2006

Dear all,

I am stuck with a SSIS package and I can€™t work out. Let me know what steps are the correct in order to solve this.
At first I have just a Flat File Source and then Script Component, nothing else.

Error:





[Script Component [516]] Error: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)



Script Code (from Script Component):



' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper


Public Class ScriptMain
Inherits UserComponent


Dim nDTS As IDTSConnectionManager90
Dim sqlConnecta As SqlConnection
Dim sqlComm As SqlCommand
Dim sqlParam As SqlParameter


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim valorColumna As String
Dim valorColumna10 As Double


valorColumna = Row.Column9.Substring(1, 1)

If valorColumna = "N" Then
valorColumna10 = -1 * CDbl(Row.Column10 / 100)
Else
valorColumna10 = CDbl(Row.Column10 / 100)
End If

Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100)
Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100)
Me.Output0Buffer.EJERCICIO = CInt(Row.Column2)
Me.Output0Buffer.CODPROV = CInt(Row.Column7)
Me.Output0Buffer.MODALIDAD = CInt(Row.Column8)
Me.Output0Buffer.NIFPERC = CStr(Row.Column3)
Me.Output0Buffer.NIFREP = CStr(Row.Column4)
Me.Output0Buffer.NOMBRE = CStr(Row.Column6)
Me.Output0Buffer.EJERDEV = CDbl(Row.Column13)

With sqlComm
.Parameters("@Ejercicio").Value = CInt(Row.Column2)
.Parameters("@NIFPerc").Value = CStr(Row.Column3)
.Parameters("@NIFReP").Value = CStr(Row.Column4)
.Parameters("@Nombre").Value = CStr(Row.Column6)
.Parameters("@CodProv").Value = CInt(Row.Column7)
.Parameters("@Modalidad").Value = CInt(Row.Column8)
.Parameters("@ImpBase").Value = valorColumna10
.Parameters("@PorcReten").Value = CDbl(Row.Column11 / 100)
.Parameters("@ImpReten").Value = CDbl(Row.Column12 / 100)
.Parameters("@EjerDev").Value = CDbl(Row.Column13)

.ExecuteNonQuery()
End With


End Sub
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

Dim nDTS As IDTSConnectionManager90 = Me.Connections.TablaMODELO80
sqlConnecta = CType(nDTS.AcquireConnection(Nothing), SqlConnection)

End Sub
Public Overrides Sub PreExecute()

sqlComm = New SqlCommand("INSERT INTO hac_modelo180(Ejercicio,NIFPerc,NIFReP,Nombre,CodProv,Modalidad,ImpBase,PorcReten,ImpReten,EjerDev) " & _
"VALUES(@Ejercicio,@NIFPerc,@NIFReP,@Nombre,@CodProv,@Modalidad,@ImpBase,@PorcReten,@ImpReten,@EjerDev)", sqlConnecta)
sqlParam = New SqlParameter("@Ejercicio", Data.SqlDbType.SmallInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@NIFPerc", Data.SqlDbType.Char)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@NIFReP", Data.SqlDbType.Char)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@Nombre", Data.SqlDbType.VarChar)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@CodProv", Data.SqlDbType.TinyInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@Modalidad", Data.SqlDbType.SmallInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@ImpBase", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@PorcReten", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@ImpReten", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@EjerDev", Data.SqlDbType.Decimal)
sqlComm.Parameters.Add(sqlParam)

End Sub


Public Sub New()

End Sub
Public Overrides Sub ReleaseConnections()
nDts.ReleaseConnection(sqlConnecta)
End Sub

Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class







Thanks a lot for your help


View 13 Replies View Related

@PARAM1 : Unable To Cast Object Of Type 'System.Data.SqlTypes.SqlInt32 To Type System.IConvertable

Apr 23, 2007

I get the following message in the vs2005 querybuilder when i do a preview:



***********************************
SQL Execution Error.

Executed SQL statement: SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = @PARAM1)



Error Source: SQL Server Compact Edition ADO.NET Data Provider


Error Message: @PARAM1 : Unable to cast object of type 'System.Data.SqlTypes.SqlInt32 to type System.IConvertable'.
****************************************

The same querypreview works fine without the parameter:



SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = 186)



Can anybody tell me why this is?
And tell me a way to get the tableadapter working?



Anne-Jan Tuinstra

View 3 Replies View Related

How To Convert To Regular Text, Data Stored In Image Data Type Field ????

Jul 20, 2005

Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved