SQL Server 2014 :: Best Way To Pull Login Data For Auditing System Wide?
May 29, 2015
I am trying to import this years worth of failed logins and last successful login for each user out of the logs using master.dbo.xp_readerrorlog. The script essentially loops through the linked servers I have on my DBA box and reaches out for the log data. It works, but here is the error I am getting on most of our production servers:
OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" was unable to begin a distributed transaction.
I know how to enable distributed transactions on the servers that error out, but if it is not needed for anything other then my audit script, I doubt the business will approve me turning on distributed transactions at those locations (so I am not even going to ask).
I am attempting to setup a singular audit .rdl with the information I want to review quarterly.
CREATE PROC [dbo].[Import_Login_Data]
AS
IF EXISTS (
SELECT 1
FROM master.sys.servers
WHERE is_linked = 1
[Code] ....
View 2 Replies
ADVERTISEMENT
Apr 25, 2007
Hi all,
I have following problem:
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
engine.CreateDatabase();
}
serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";
using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(
Configuration.Default.SyncServerAddress, "", "", connStr))
{
rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
Everythink works fine, when I use 'sa' user account in serverConnStr.
But, when I change conn string to:
"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Please help.
Thank you.
Fipil.
View 10 Replies
View Related
Jul 27, 2015
I have been creating databases in SQL 2008 with a primary filegroup for the system objects and a secondary, marked Default, for the data.
We are preparing a migration to SQL 2014, and the administrator is complaining he won't adopt this structure on the new servers because 'there is no benefit' and 'a backup cannot be restored (!?)'.
View 2 Replies
View Related
Sep 30, 2004
While making a connection to a SQL server Enterprise Database using ASP.Net(C#), during execution of .aspx file i got the following error :-
Kindly help me if anybody knows the solution .
Thanks in advance
Login failed for user 'IT-CELLIWAM_IT-SERVER'.
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.Data.SqlClient.SqlException: Login failed for user 'IT-CELLIWAM_IT-SERVER'.
Source Error:
Line 52: //mycommand.SelectCommand.CommandType=CommandType.StoredProcedure;
Line 53: DataSet ds=new DataSet();
Line 54: mycommand.Fill(ds);
Line 55: DataTable dt;
Line 56: dt=new DataTable();
Source File: c:inetpubwwwrootetapplogin.aspx.cs Line: 54
Stack Trace:
[SqlException: Login failed for user 'IT-CELLIWAM_IT-SERVER'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +484
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
netapp.login.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootetapplogin.aspx.cs:54
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
View 2 Replies
View Related
Apr 26, 2015
Oracle and Teradata are able to capture the windows/client login that connects to their database with a functionnal ID.I've searched around and I don't think MS SQL can do this...I administer SQL 2014 server and also SAS Enterprise guide product. I've created sql server logins for several teams so everyone shares the same sql server login which is used to bind the SAS library to my SQL server database.
However, when looking at activity monitor, I can't drill down to which user is executing a particular query. I only see the SQL login which is share amongs 200 other folks.On the other hand, when they connect from SAS to Oracle or Teradata, I can see the shared login + additionally their windows NT login used to connect to SAS which is awesome....they can't fool me by using a shared/funcionnal ID....i still see who's using that ID...
View 2 Replies
View Related
May 17, 2006
Hi all,
Can someone explain it to me why I am getting the following error when I try to connect SQL server express with .NET 2.0?
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.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Here is my code and i am using windows authentication:
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim connAkaki As SqlConnection Dim cmdSelectAuthers As SqlCommand Dim dtrAuthers As SqlDataReader connAkaki = New SqlConnection("Server=.SQLEXPRESS;database=akaki") connAkaki.Open() cmdSelectAuthers = New SqlCommand("select Firstname from UserTableTest", connAkaki) dtrAuthers= cmdSelectAuthers.ExecuteReader() While dtrAuthers.Read() Response.Write("<li>") Response.Write(dtrAuthers("Firstname")) End While dtrAuthers.Close() connAkaki.Close() %>
View 2 Replies
View Related
Feb 28, 2007
Hi guys,
Everything seems to be fine, can browse the DB from VS, Preview data works fine in dataset, and the page works fine before adding any SQL DB code.
I draged n dropped a new DropDownList, Bind it to an object from dataset. And when I want to run the page I get this error !!!
I appreciate any help.
Thanks, Mehdi
View 4 Replies
View Related
Aug 12, 2005
when i browser the page on browser the following error dispaly
" System.Data.SqlClient.SqlException: Login failed for user xxxx "sqlAdaptor , sqlconnection and dataSet are all fine. i can even preivew dataset, but i don't know while it doesn't display in browser.
View 1 Replies
View Related
Jul 8, 2004
Here's the error stack:
System.Data.SqlClient.SqlException: Cannot open database requested in login 'MYKYTYNMDB'. Login fails.
Login failed for user 'OMM_IUSER'.
at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DreamweaverCtrls.DataSet.GetConnection(String strConnection)
at DreamweaverCtrls.DataSet.DoInit()
i have just exported all objects from one sql server to another and no matter how many times i check the password i keep getting this same error..
I KNOW for a fact that the password and username are correct and that they have all of the correct permissions..
what am i doing wrong?
View 6 Replies
View Related
Apr 3, 2008
hi guys,
i'm upgrading a web application designed in visual studio 2003 to visual studio 2005.
i want to start from the scratch.
I've restored the database from the older application in sql server 2000 from another machine.
now whenever i try to execute that application an error is displayed stating
"System.Data.SqlClient.SqlException: Login failed for user 'VASANTASPNET'."
my sql server uses windows authentication mode,and i've made changes in the connectionstring accordingly...
but still it shows this error...
can ny body help me get rid of it..?
View 5 Replies
View Related
May 2, 2008
I am having a very strange and frustrating problem with moving an application from Development to Production.
I have been working on this application on XP with VS2005 with SQL Express on my local machine. Throughout the development lifecycle I have moved it to the Server multiple times to test things and everything has been working fine until this last time.
What I have done since the last time:
Added some ASP.NET AJAX functionality
Added some views that I am having to fill a dataset from the dataset to manipulate the data before presentation.
Everything has worked fine without any issues on my local machine with my local database.
I just made backups of the databases and restored them on the server and have moved all my project files up to the server. I then installed the ASP.NET AJAX Extensions 1.0 on my 2.0 server.
NOW - The Problem:
The entire site works fine, I can read data from every table of every database, and I can also write data to all the databases. However, this one page has a GridView that I pull data from the database and fill a DataSet with the data. I then manipulate the data, adding some data from other databases and then present it to the browser.
This is working fine on my local machine, but since pushing this to the server I get an error:
System.Data.SqlClient.SqlException: Login failed for user 'ext_access'
Line 36: ad.Fill(ds, "CaskInfo")Line 37: Dim Table As DataTable = ds.Tables("CaskInfo")Line 38: Table.Columns.Add("Company", GetType(String))
I have checked the database and the SQL Server and the user listed above has the correct access to the database, which I would guess since everthing else is working fine. I use the same connection information throughout the app and it is stored in the web.config so I am certain nothing is wrong with my connection information. I access that information with the same code throughout the application as well so I know it is correct.
I know this may not be the most trimmed piece of SQL code, but I figured I would post it incase there is something inefficient in my SQL that could be causing this.
Here is my code leading up to the error.
Dim conn As SqlConnection = CreateConnection2()Dim connectionString As String = conn.ConnectionString
Dim myConnection As New SqlConnection(connectionString)Dim ad As New SqlDataAdapter("Select CaskID, CompanyID, SiteID, UnitIDVCC, UnitIDTSC, DATEPART(mm,DateLoaded)as Month, DATEPART(dd,DateLoaded)as Day, DATEPART(yyyy,DateLoaded)as Year, COCName, AmendmentName, HeatLoad, Dose, DryTime, Damaged, Debris FROM CaskInfo, COCInfo, AmendmentInfo WHERE CaskInfo.COC = COCInfo.COCID AND CaskInfo.Amendment = AmendmentInfo.AmendmentID", myConnection)
Dim ds As New DataSet()
ad.Fill(ds, "CaskInfo")
Using this connection code:Function CreateConnection2() As SqlConnection
Dim conn2 As SqlConnection = New SqlConnectionconn2.ConnectionString = ConfigurationManager.ConnectionStrings("MainWeb").ConnectionString
conn2.Open()Return conn2
End Function
And this is the line in the web.config:
<add name="MainWeb" connectionString="Data Source=N2K3APPS;Initial Catalog=NUTUGData;Integrated Security=false;Pooling=False;User Id=ext_access;password=zzzzzzz" providerName=".NET Framework Data Provider for SQL Server"/>
Thanks!
View 2 Replies
View Related
Apr 2, 2004
I have searched all the forums on this issue and tried the other options but nothing seems to work. I have an IIS server using asp.net to connect to a SQL database on a separate server which also happens to be a domain controller(i.e. no local accounts). I have tried changing the connection string, web.config, etc. but I still can't get connected. I believe the issue is with the SQL security but I'm not sure.
IIs is set up for Integrated Windows Auth.
Here is the web.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!-- application specific settings -->
<appSettings>
<add key="connectionString" value="Data Source=VGISQL2;Initial Catalog=PaymentApplication;Integrated Security=SSPI;" />
</appSettings>
<system.web>
<!-- DYNAMIC DEBUG COMPILATION
Set compilation debug="true" to enable ASPX debugging. Otherwise, setting this value to
false will improve runtime performance of this application.
Set compilation debug="true" to insert debugging symbols (.pdb information)
into the compiled page. Because this creates a larger file that executes
more slowly, you should set this value to true only when debugging and to
false at all other times. For more information, refer to the documentation about
debugging ASP.NET files.
-->
<compilation
defaultLanguage="c#"
debug="true"
/>
<!-- CUSTOM ERROR MESSAGES
Set customErrors mode="On" or "RemoteOnly" to enable custom error messages, "Off" to disable.
Add <error> tags for each of the errors you want to handle.
"On" Always display custom (friendly) messages.
"Off" Always display detailed ASP.NET error information.
"RemoteOnly" Display custom (friendly) messages only to users not running
on the local Web server. This setting is recommended for security purposes, so
that you do not display application detail information to remote clients.
-->
<customErrors
mode="Off"
/>
<!-- AUTHENTICATION
This section sets the authentication policies of the application. Possible modes are "Windows",
"Forms", "Passport" and "None"
"None" No authentication is performed.
"Windows" IIS performs authentication (Basic, Digest, or Integrated Windows) according to
its settings for the application. Anonymous access must be disabled in IIS.
"Forms" You provide a custom form (Web page) for users to enter their credentials, and then
you authenticate them in your application. A user credential token is stored in a cookie.
"Passport" Authentication is performed via a centralized authentication service provided
by Microsoft that offers a single logon and core profile services for member sites.
-->
<authentication mode="Windows" />
<!-- AUTHORIZATION
This section sets the authorization policies of the application. You can allow or deny access
to application resources by user or role. Wildcards: "*" mean everyone, "?" means anonymous
(unauthenticated) users.
-->
<authorization>
<allow users="*" /> <!-- Allow all users -->
<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>
<!-- APPLICATION-LEVEL TRACE LOGGING
Application-level tracing enables trace log output for every page within an application.
Set trace enabled="true" to enable application trace logging. If pageOutput="true", the
trace information will be displayed at the bottom of each page. Otherwise, you can view the
application trace log by browsing the "trace.axd" page from your web application
root.
-->
<trace
enabled="true"
requestLimit="10"
pageOutput="false"
traceMode="SortByTime"
localOnly="false"
/>
<!-- SESSION STATE SETTINGS
By default ASP.NET uses cookies to identify which requests belong to a particular session.
If cookies are not available, a session can be tracked by adding a session identifier to the URL.
To disable cookies, set sessionState cookieless="true".
-->
<sessionState
mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes"
cookieless="false"
timeout="20"
/>
<!-- GLOBALIZATION
This section sets the globalization settings of the application.
-->
<globalization
requestEncoding="utf-8"
responseEncoding="utf-8"
/>
<!-- ADDED BY YURI -->
<identity impersonate="true" />
</system.web>
</configuration>
HELP!!!!!!!!!!!!!!
View 4 Replies
View Related
Aug 21, 2006
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
View 28 Replies
View Related
Oct 19, 2015
how can I determine the relation between MS SQL Server user (SQL login ) and his session , when it connect to SQL Server
View 1 Replies
View Related
Jul 6, 2014
I am trying to learn how to store a web form password and than check it when the user log in. So far none of the code I can find works.
Why the following test does not work and what the correct code should be?
Insert Into [user]
values ('name', 'email', HashBytes('SHA1', 'bob'))
GO
Why does the following produce no rows?
SELECT *
From [user]
Where HashBytes('SHA1', password) = HashBytes('SHA1', 'bob')
View 8 Replies
View Related
Jun 27, 2014
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
View 1 Replies
View Related
Jan 12, 2015
I have the Image in FTP Server Folder and i need to insert that image into my local database.
How can i do this I tried with the below Query but i shows the errors as below.
--INSERT INTO AcademyStudents (ImageURL)
--SELECT BULKCOLUMN FROM OPENROWSET(BULK'https://iconic-solutions.net/OTA/test/images(1).jpeg',Single_Blob) AS BLOB
--Where StudentIdentificationNum = 2
--GO
GOt Error
;
Cannot bulk load because the file [URL] could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).
View 1 Replies
View Related
Mar 25, 2015
How can you install the System Databases to a drive other than the default?
I want the Data Files to be installed on D:MSSQLData and the log files to be stored on D:MSSQLLog.
For better, quicker answers on T-SQL questions, click on the following...
[URL]
For better answers on performance questions, click on the following...
[URL]
View 9 Replies
View Related
Jul 27, 2015
We installed SP1 for SQL Server 2014 this past weekend and got this error message in the logs. I found that if you set the db to read-write, it updates the system objects, even after SP1 has completed. Then you can set it back to read-only. I'm just posting this so other people can find it on the internet, as I wasn't able to find it specifically.
Error Log Entry:System objects could not be updated in database 'x' because it is read-only.
Problem: After installing SP1 for SQL Server 2014 you will find this message in the error logs saying read-only databases could not be updated.
Solution: Simply set the db to read-write and the system objects will get updated, long after SP1 was installed.
ALTER DATABASE [x] SET READ_WRITE WITH NO_WAIT
Then set it back to read-only:
ALTER DATABASE [x] SET READ_ONLY WITH NO_WAIT
You should then see these log entries:
System objects could not be updated in database 'x' because it is read-only.
Setting database option READ_WRITE to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:28.143 (local time). This is an informational message only; no user action is required.
Synchronize Database 'x' (129) with Resource Database.
Setting database option READ_ONLY to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:29.888 (local time). This is an informational message only; no user action is required.
View 0 Replies
View Related
Nov 28, 2014
I need to create a website which has login details and then shows a list of reports for my clients?
Clients are external so would be website based with a login screen.
View 1 Replies
View Related
Sep 1, 2015
I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "
Same problem facing on different servers.
Note : Environment is SQL 2012,SQL 2008 including cluster servers .
View 2 Replies
View Related
Jul 20, 2004
Hi,
Can any of you tell me how to create a login in SQL Server 6.5 with System Administrator privileges, like we can do with SQL 7 or SQL 2000 ? I don't want to use the sa login.
Thanks
View 1 Replies
View Related
Apr 23, 2015
SQL server job or SP to deny access to an AD login for certain period of time to SQL server instance...i.e. to deny access to login ADxyz from 12 PM to 10 PM and revoke access to same login at 10:01 PM...
View 3 Replies
View Related
Oct 18, 2013
I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
View 8 Replies
View Related
Oct 16, 2007
Hi,
In my application i am storing the data in a database(server A). And i have a scenario where in a middle server(server B) is there in which i need to create a table and pull the data from one of the tables from my original database server(server A) into second database server(server B). Where can i get the code to write a job script which can connect from server B to server A and pull the data from the table and delete that data from the table in server A. Can any give me the code or link where i can find the code. Please help.
Thanks
View 5 Replies
View Related
Apr 20, 2015
I run a query from Excel to pull the data from SQL server and I have created another worksheet to add my columns for different analysis and costs. When someone add another line in SQL against an order or a customer then manually added columns data goes out of sync.Is there anyway when I refresh the RAWDATA or anything changes on SQL table then it should sync my formatted worksheet.
View 4 Replies
View Related
Apr 16, 2008
Could someone give me the general syntax to pull xml data from a web server that I have access to? I do know that I can not use an ip address, I have to use the hostname, etc.
I was using something like
FROM OPENROWSET(BULK '\SERVERNAMESHAREPATHDATAFILE.XML', SINGLE_BLOB) as x
instead of using SERVERNAME I am user http:\www.mycompanyname.commycompanynamestoredata.xml
I do not understand why this does not work. I hope I gave enough information for this.
Please help.
Thank you
LadyDee
View 10 Replies
View Related
Aug 21, 2015
pulling data from a table in Server A and creating the same table on Server B.
From what I've read you can't export/import or use a wizard as the db is of the type dbo.md_*****.
View 3 Replies
View Related
May 3, 2007
Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
View 4 Replies
View Related
May 3, 2007
Hey guys, i finally connected to the db fine however for some reason i cannot pull the data. I was hoping somebody here might be able to help me. The code is below
Function MyQueryMethod() As System.Data.DataSet Dim connectionString As String = "server='xxxx'; user id='xxxx'; password='xxxxxx'; database='wel"& _ "sh_indiv'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [test12].[name], [test12].[grade] FROM [test12]" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Return dataSet End Function
im kinda new to programming with vb for asp.net pages so please bare with me.
View 2 Replies
View Related
Jul 1, 2015
We have a custom web C#/SQL2K8R2 workflow application that I need to pull Oracle data into a varchar(max) field as an XML DOM document. I have no problem pulling the Oracle data using OLEDB, but I'm not sure how to create the XML DOM doc. Once I get it into the DOM doc, I then need to assign metadata about the XML DOM doc and insert it all into a staging table:
CREATE TABLE [stg].[EtlImports](
[EtlImportId] [int] IDENTITY(1,1) NOT NULL,
[EtlSource] [varchar](50) NOT NULL,
[EtlType] [varchar](50) NULL,
[EtlDefn] [varchar](max) NULL, --Either a SQL statement or path to file on disk
[EtlData] [varchar](max) NULL, --BLOB field to hold the XML data or FILESTREAM path to file on disk
[EtlDateLanded] [datetime] NOT NULL,
[EtlDateProcessed] [datetime] NULL,
[EtlStatus] [varchar](50) NULL,
[Comments] [varchar](4000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I will have a separate SSIS package to pull the [XML/File] field and process the data into the workflow tables. Is there a wasy I can use the ADO Record-set Destination task to accomplish this, or do I have to create a custom C# script to create the XML DOM Doc?
View 0 Replies
View Related
Oct 10, 2007
Hello
I am more of a reporting person, recently I was asked to create stored procedure for an upcoming ASP.NET
application.
We have a problem that we are facing and any suggestion would be very helpfull.
The problem is that we have like 8 differtent tables each with 10 to 15 columns in it.
The front end application has pages with save , update , delete button which are insert,update,delete
for each of the 8 tables
i.e they save , update , delete these 8 tables
They want to a way to Update , this audit table which stores information
like ---
Date
User
Table
Column name
Previous column name
new column name
So for each row that was updated in those 8 tables , each column will have the above fields updated as a row of data
Initially we thought about triggers but it will be like 60 triggers ...Is there a better or other way of handling this?
Any help is appreciated
Thanks
View 3 Replies
View Related
Jun 14, 2007
I am using the SCD Wizard and it is working nicely. Can someone point be to an article/tuorial that would explain how you could create an "audit trail" on the items that may have been changed (type I and II)?
Basically, what I want to be able to do is run a query that tells me what data may have changed. I figured I would have to create an auditkey field in my table which would then link the key to the change detail?
View 7 Replies
View Related